数据库实验五——DQL复杂查询

实验要求

• 查询审批通过人数最多的课程名称和教师姓名；（连接、排序）
• 查询只选了课程《C++编程》的学生姓名；（子查询）
• 查询选修了课程3的学生学号、姓名、身份证号；（多表）
• 对于请过假的学生，查询其学号，姓名和请假次数；（自然连接）
• 查询所有学生的学号，姓名和请假次数（无论该学生是否请过假）；（左连接）
• 查询所有学生对所有课程的选课情况，输出学号与课程号（无论该学生是否选课，无论该课程是否被选）；（全外连接）
• 总结自然连接、左连接、右连接查询在产生的结果上面有什么区别；
• 查询选课人数最多和第二多的课程名称和任课教师姓名；（函数、连接）
• 查询选修了全部课程的学生姓名；（子查询）
• 查询课程3获得最高分的学生的姓名和分数；（多表）
• 对lesson表进行查询，字段包括课程号、课程名称、学分、先修课程号、先修课程名称、先修课程学分；（自连接）
• 查找每门课的最大applytime请假单
• 查找每个月每门课的最大applytime请假单
• 查看没有选修‘C语言’课程的学生
• 查看成绩都不高于80分的学生
• 查看每个学生的最高分

实验过程

Step1：查询审批通过人数最多的课程名称和教师姓名

将lesson，teacher，leave_apply三张表通过等值连接后进行分组，降序排列后取第一个元组，也就是审批通过人数最多的课程名称和教师姓名

SELECT lessonName, teacherName FROM lesson le, teacher te, leave_apply la
	WHERE la.state = '审批已通过' AND la.teacherNum = te.teacherNum AND la.lessonNum = le.lessonNum
	GROUP BY lessonName, teacherName
	ORDER BY COUNT(1) desc

方法二：直接使用top

SELECT TOP 1 lesson.lessonName, teacher.teacherName, COUNT(*) number
	FROM leave_apply, lesson, teacher	
	WHERE leave_apply.lessonNum = lesson.lessonNum and leave_apply.teacherNum = teacher.teacherNum and state = '审批已通过'
	GROUP BY lesson.lessonName, teacher.teacherName
	ORDER BY number desc

Step2: 查询只选了课程《C++编程》的学生姓名

首先选出只选修了一门课的学生，然后判定选的课程是否是C++编程

SELECT studentName FROM student, lesson
	WHERE studentNum IN (
		SELECT sc2.studentNum FROM stu_choose_lesson sc2
		GROUP BY studentNum HAVING COUNT(lessonNum) = 1) 
			AND lessonNum IN (SELECT lessonNum FROM lesson WHERE lessonName = 'C++编程')

查询结果为空，说明不存在只选修了C++编程的学生

Step3：查询选修了课程3的学生学号、姓名、身份证号

直接使用等值连接

SELECT st.studentNum, st.studentName, st.IDcard FROM student st, stu_choose_lesson sc
	WHERE sc.lessonNum = 3 AND st.studentNum = sc.studentNum

Step4：对于请过假的学生，查询其学号，姓名和请假次数

先对两张表进行内连接，连接之后进行分组，去除冗余元组，并通过聚集函数count来计算请假次数

SELECT st.studentNum, st.studentName, COUNT(la.studentNum) AS 请假次数  
    FROM student st INNER JOIN leave_apply la ON st.studentNum = la.studentNum  
    GROUP BY st.studentNum, st.studentName  

Step5：查询所有学生的学号，姓名和请假次数（无论该学生是否请过假）

直接左连接

SELECT st.studentNum, st.studentName, COUNT(la.studentNum) AS 请假次数
	FROM student st LEFT JOIN leave_apply la ON st.studentNum = la.studentNum
	GROUP BY st.studentNum, st.studentName

Step6：查询所有学生对所有课程的选课情况，输出学号与课程号（无论该学生是否选课，无论该课程是否被选）

在学生表和选课表中使用全外连接

SELECT st.studentNum, sc.lessonNum
	FROM student st FULL JOIN stu_choose_lesson sc
		ON st.studentNum = sc.studentNum

Step7：总结自然连接、左连接、右连接查询在产生的结果上面有什么区别

自然连接中：如果有共同属性的值有不匹配的情况，则此元组会被忽略。

左连接中：如果共同属性的值有不匹配的情况，会将左表的元组保留，右表对应位置设置为空。

右连接中：如果共同属性的值有不匹配的情况，会将右表的元组保留，左表对应位置设置为空。

Step8：查询选课人数最多和第二多的课程名称和任课教师姓名

首先将三张表进行连接，连接后对课程名称和任课教师进行分组，按出现的次数进行降序排列，最后在选择的时候，使用top 2语句，表示查询选课次数前两名的课程名称和教师姓名

SELECT top 2 lessonName, teacherName, COUNT(*) AS 选课次数
	FROM stu_choose_lesson sc, teacher te, lesson le
		WHERE sc.lessonNum = le.lessonNum AND le.teacherNum = te.teacherNum
			GROUP BY lessonName, teacherName
			ORDER BY COUNT(1) desc

Step9：查询选修了全部课程的学生姓名

使用IN子查询，首先我们需要求出一共有多少门课程，然后在选课表中按学号给学生分组，在每一组中，如每一组中的数量等于总课程数，则说明该学生选修了全部的课程

SELECT studentName FROM student st
	WHERE studentNum IN (
		SELECT studentNum FROM stu_choose_lesson sc 
			GROUP BY studentNum 
			HAVING count(*) = (SELECT COUNT(*) FROM lesson))

Step10：查询课程3获得最高分的学生的姓名和分数

SELECT st.studentName, sc.score 
	FROM stu_choose_lesson sc, student st
		WHERE sc.studentNum = st.studentNum 
			AND sc.lessonNum = 3 
			AND  sc.score = (SELECT MAX(score) 
				FROM stu_choose_lesson sc2 WHERE sc2.lessonNum = 3) 

Step11: 对lesson表进行查询，字段包括课程号、课程名称、学分、先修课程号、先修课程名称、先修课程学分

为了调试代码，我们将之前的实验：删除的指定教工号（teacherNum）的教师的所有相关记录重新加回数据库

INSERT INTO lesson VALUES (2,'C++编程',2013, '上', 3,  2,  1)  
INSERT INTO teacher VALUES (2, '许明', '副教授', '男')  

然后使用左外连接将两张lesson表进行连接：

SELECT le1.lessonNum, le1.lessonName, le1.credit, le1.beforeLessonNum, le2.lessonName, le2.credit
	FROM lesson le1 LEFT JOIN lesson le2 
		ON le1.beforeLessonNum = le2.lessonNum

Step12：查找每门课的最大applytime请假单

分组后求最大值

SELECT lessonNum, MAX(applyTime) AS Max_applyTime
	FROM leave_apply
	GROUP BY lessonNum

Step13：查找每个月每门课的最大applytime请假单

SELECT  MONTH(MAX(applyTime)) AS Max_Month, lessonNum, MAX(applyTime) AS Max_applyTime
	FROM leave_apply 
	GROUP BY lessonNum, MONTH(applyTime)

Step14：查看没有选修‘C语言’课程的学生

在所有学生中，使用EXCEPT除去选修过C语言的学生，就得到没有选修C语言课程的学生

SELECT studentName FROM student WHERE studentNum IN (
	SELECT studentNum FROM student
	EXCEPT
	SELECT studentNum FROM stu_choose_lesson sc
		WHERE studentNum = sc.studentNum AND lessonNum = 1
)

Step15：查看成绩都不高于80分的学生

只要有一科高于80分，就排除掉

SELECT studentName FROM student
	WHERE studentNum NOT IN (
		SELECT studentNum FROM stu_choose_lesson 
			WHERE score > 80)

Step16：查看每个学生的最高分

我们先在stu_choose_lesson中选出每个学号对应的最高分，并将其作为一张表与student表进行连接后选择我们需要的属性

SELECT studentName, highestScore FROM student, 
	(SELECT studentNum, MAX(score) AS highestScore FROM stu_choose_lesson sc
		GROUP BY sc.studentNum) AS sc2
	WHERE student.studentNum = sc2.studentNum

实验五Over