数据库实验四——DQL简单查询

实验要求

• 查询性别为男的所有学生的学号、姓名和班级号，结果按照班级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