实验要求

  • 查询性别为男的所有学生的学号、姓名和班级号,结果按照班级ID倒序排列;(order by)
  • 查询学号以2002开头的学生信息,字段包括学号、姓名、班级号;(like)
  • 查询状态为“未审核”或“审批已通过”,且申请时间在2013年9月4日之后的请假申请单的信息,包含申请时间为9月4日的申请单;(单表多条件查询)
  • 查询时间在2013年8月31日和2013年9月2日之间所提交的申请单ID,请假原因;(between、时间)
  • 查询方燕燕老师所教课程的的选课人数(一人选两门课程,算一人);(distinct)
  • 查询刘波老师对请假单审核不通过的请假原因与学生姓名;
  • 查询选课人数超过3人的课程号,并按课程号倒序排列;(group by,having,order,desc)
  • 统计每门课的学生人数(集合函数)。
  • 了解集合函数对空值的处理情况。插入表STU_CHOOSE_LESSON中score列的值,并保留至少一行的score为空,然后统计每个学生的平均成绩、分数总和以及有成绩的课程数.分析结果,看看平均成绩是否包括没有成绩的课程。
  • 查找每个学生所选的最小课程号。(考虑能否用多于一种方法)
  • 查找每个学生所选的最小课程号及成绩。(考虑能否用多于一种方法)

实验过程

Step1: 询性别为男的所有学生的学号、姓名和班级号,结果按照班级ID倒序排列

SELECT studentNum, studentName, class, sex FROM student
	WHERE sex = '男'
	ORDER BY class desc

Step2: 查询学号以2002开头的学生信息,字段包括学号、姓名、班级号

SELECT studentNum, studentName, class FROM student
	WHERE studentNum like '2002%'

Step3: 查询状态为“未审核”或“审批已通过”,且申请时间在2013年9月4日之后的请假申请单的信息

SELECT * FROM leave_apply 
	WHERE (state = '未审核' OR state = '审批已通过') 
		AND (applyTime >= '2013-09-04')

Step4:查询时间在2013年8月31日和2013年9月2日之间所提交的申请单ID,请假原因

SELECT applyNum, reason FROM leave_apply  
        WHERE applyTime BETWEEN '2013-08-31' AND '2013-09-02'  

Step5: 查询方燕燕老师所教课程的的选课人数(一人选两门课程,算一人)

SELECT count(DISTINCT sc.studentNum)  选课人数 
	FROM lesson le, stu_choose_lesson sc, teacher te
		WHERE te.teacherName = '方燕燕' and te.teacherNum = le.teacherNum 
			and le.lessonNum = sc.lessonNum and sc.studentNum <> '000000'
学生选课表

Step6:查询刘波老师对请假单审核不通过的请假原因与学生姓名

SELECT la.reason, st.studentName 
	FROM leave_apply as la, student as st, teacher as te
		WHERE te.teacherName = '刘波' and la.state = '审批未通过' 
			and te.teacherNum = la.teacherNum and la.studentNum = st.studentNum

Step7:查询选课人数超过3人的课程号,并按课程号倒序排列

SELECT lessonNum FROM stu_choose_lesson AS sc
	GROUP BY lessonNum HAVING COUNT(*) > 3
	ORDER BY lessonNum DESC

Step8:统计每门课的学生人数

SELECT lessonNum AS 课程号, COUNT(studentNum) AS 人数 
	FROM stu_choose_lesson
		WHERE studentNum <> '000000'
			GROUP BY lessonNum

Step9:了解集合函数对空值的处理情况。插入表STU_CHOOSE_LESSON中score列的值,并保留至少一行的score为空,然后统计每个学生的平均成绩、分数总和以及有成绩的课程数。分析结果,看看平均成绩是否包括没有成绩的课程。

首先我们对原始表中的数据,统计每个学生的平均成绩、分数总和以及有成绩的课程数:

SELECT studentNum, AVG(score) AS 平均成绩, SUM(score)AS 分数总和, COUNT(score) AS 课程数
	FROM stu_choose_lesson
		GROUP BY studentNum HAVING studentNum <> '000000'		

现在插入一组数据后,再进行查询,插入的数据中,成绩为空值:

INSERT INTO stu_choose_lesson VALUES (200103, 4, null)

SELECT studentNum, AVG(score) AS 平均成绩, SUM(score)AS 分数总和, COUNT(score) AS 课程数
	FROM stu_choose_lesson
		GROUP BY studentNum HAVING studentNum <> '000000'

可以看出,200103号同学虽然有一门课程的成绩为空,但是平均值,分数总和,课程数都没有改变,这说明,集合函数会自动忽略掉空值。

Step10:查找每个学生所选的最小课程号。(考虑能否用多于一种方法)

--法一
SELECT studentNum 学生号, MIN(lessonNum) 最小课程
	FROM stu_choose_lesson  
		GROUP BY studentNum HAVING studentNum <> '000000'
--法二
SELECT studentNum 学生号, lessonNum 最小课程号
	FROM stu_choose_lesson sc
		WHERE lessonNum = (
			SELECT MIN(lessonNum) FROM stu_choose_lesson 
				WHERE studentNum = sc.studentNum and studentNum <> '000000')

Step11:查找每个学生所选的最小课程号及成绩考(虑能否用多于一种方法)

SELECT studentNum 学生号, lessonNum 最小课程号, score 分数
	FROM stu_choose_lesson sc
		WHERE lessonNum = (
			select MIN(lessonNum) from stu_choose_lesson 
				WHERE studentNum = sc.studentNum and studentNum <> '000000')

方法二:使用相关子查询,使用相关子查询,依赖于外部的sc表进行,通过内部表与外部表的studentNum进行匹配,求出每个学生的最小课程号和分数。

--法二
SELECT sc.studentNum 学生号, sc.lessonNum 最小课程号, sc.score 分数
	FROM stu_choose_lesson sc,
	(SELECT studentNum, MIN(lessonNum) AS lessonNum 
		FROM stu_choose_lesson GROUP BY studentNum HAVING studentNum <> '000000') as sc2
			WHERE sc.studentNum = sc2.studentNum and sc.lessonNum = sc2.lessonNum

实验四Over


立志做一名攻城狮