第四章 高级查询(一)

本章节需要掌握子查询的用法，掌握IN查询的用法，掌握EXISTS子查询的用法，以及了解子查询的关键点。

4.1 任务1：使用简单子查询查询考试成绩

任务目标：了解子查询的用用场景及什么是子查询，会使用简单子查询
前面学习了MySQL中如何使用SELECT、INSERT、UPDATE和DELETE 语句对于数据进行查询和更新。在此基础上，开始学习子查询，在下表中

查询年龄比‘gjc’肖的学生，要求显示这些学生的信息
分析如下：在student表中进行查询，查询的条件是年龄比‘gjc’小，如何实现呢
(1)先查询‘gjc’的出生日期
(2)再利用WHERE语句删选出生日日期比‘gjc’出生日期小的学生
实现方法1：根据以上思路，问题可以分两步实现
 

#先查找出“gjc”的出生日期
SELECT birthday FROM student WHERE studentName='gjc';
#利用WHERE语句删选出出生日期在‘李斯文’之后的学生
SELECT studentNo,studentName,sex,birthday,address FROM student WHERE birthday>'1998-08-08';

方法一中共使用了两个查询语句。首先，通过第一条SELECT语句从表student中查询‘gjc’的出生日期是‘1998-08-08’；然后，利用第二条SELECT语句查询出生日期在‘1998-08-08’之后的学生记录，即可得到出生日期在此之后的学生记录，那么有没有比这更加简洁的代码呢。
实现方法2：采用子查询实现实现，
 

SELECT * FROM student
WHERE birthday>(SELECT birthday from student WHERE studentName='gjc');

从方法二的代码中可以发现，方法1代码中的两条查询语句已经合并为一条SQL语句，方法1的第一步是查询语句SELECT * FROM student WHERE studentName='gjc'就是子查询，因为它嵌入外层查询语句SELECT * FROM student中的WHERE条件的一部分，子查询在WHERE子句中的一般语法格式如下SELECT ...FROM 表1 WHERE 字段1 比较运算符(子查询);
        其中，子查询语句必须放置在一对圆括号内，比较运算符包括>,=,<,+=>=等，习惯上外层成为子查询，圆括号中嵌入的称为子查询。执行SQL语句时，先执行子查询部分，再执行整个夫查询，返回最后的结果。子查询作为WHERE条件的一部分，还可以和UPDATE,INSERT,DELETE 一起使用。
        注意(1)将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。
        (2)SELECT 语句中使用SELECT * FROM student语句的执行效率会低于SELECT studentNo,studentName,sex,birthday,address FROM student,因为前者获得表中所有字段值所占的资源将大于后者获得的制定字段值所占的资源，且后者的可维护性高于前者。

【示例2】查询'Logic JAVA'（课程1）至少一次考试刚好及格的学生名字，分析如下：
分析如下(1)查询课程表subject,获得‘Logic Java’课程的课程编号。
        (2)根据课程编号，查询成绩表中results中成绩为60分的学生的学号
        (3)根据学号，查询学生表student获得学生姓名
          SELECT studentName from student WHERE studentNo=
(SELECT studentNo FROM RESULTS WHERE studentResult=60 AND subjectNo=(
SELECT subjectNo FROM subject WHERE subjectName='Logic Java'));
其中括号中的子查询刚好查询出'Logic Java'课程考试成绩为60分的学生学号

注意，在示例2中在子查询语句中又嵌套了一个子查询，用于查询'Logic Java'课程的课程编号。因此，示例2代码是一个三层嵌套的子查询结构。在实际的软件开发中，程序员可能经常需要通过使用多层嵌套的子查询来实现复杂的查询功能。

上机练习1查询指定学生的考试成绩
查询参加最近一次‘Logic Java’考试的小学生最高成绩和最低成绩
(1)查询获得‘Logic Java’课程的编号。
(2)查询获得'Logic Java'课程的最近一次的考试日期
(3)根据课程编号和最近一次考试的时期查询学生的最低和最高成绩
SELECT subjectNo FROM subject WHERE subjectName='Logic Java';
SELECT examData FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java';) ORDER BY examData DESC LIMIT 1;
SELECT MIN(studentResult),Max(studentResult) FROM RESULTS WHERE examData=(SELECT examData FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java';) ORDER BY examData DESC LIMIT 1);

4.2 任务2：使用IN子查询查询课程

任务目标，了解IN子查询的应用场景，会使用IN查询，会使用NOT IN进行子查询。

4.2.1 IN子查询

        在第三章中，介绍了IN关键字，用来在列举值内进行查询，因此，使用IN关键字可以使父查询匹配子查询返回的多个单字段值。
        在MySQL中，使用>,=等比较运算符时，要求子查询只能返回一条或空的记录。若子查询跟随在=，！=,<,<=等之后，则不允许子查询返回一条记录。例如示例二种查询Logic Java课程至少一次考试刚好为60分的学生名单，刚好只有一条满足，若有多条满足，采用上述子查询将会出现运行错误。
        在使用示例2的查询代码之前，确保成绩单results中刚好有两条或两条以上60分的记录
如果我们使用以下代码:UPDATE RESULTS SET studentResult=60 WHERE subjectNo=1 AND studentNo=1;
 会出现错误

【示例3】

查询'Logic Java'课程至少一次考试刚好为60分的学生名单
SELECT subjectNo FROM subject WHERE subjectName='Logic Java';
SELECT studentNo FROM RESULTS WHERE studentResult=60 AND subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java');
SELECT studentName FROM student WHERE studentNo IN (SELECT studentNo FROM RESULTS WHERE studentResult=60 AND subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java'));

从示例3的代码中代码可以看出，IN后面的子查询可以返回多条记录，用于限制学号的筛选范围，下面再来看一个新问题

【示例4】查询参加‘Logic Java’课程最近一次考试的学生的在读名单
(1)首先获得‘Logic Java’课程的课程号
SELECT subjectNo FROM subject WHERE subjectName='Logic Java';
(2)获得最近一次‘Logic Java’的考试时间
SELECT max(examData) FROM results WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java') 
(3)根据课程编号和最近一次的考试日期查询学生信息。
实现这个需求需要查询三个表：课程表subject、成绩表results、学生表result，具体的SQL语句如下
SELECT * FROM student WHERE studentNo IN (
SELECT studentNo FROM RESULTS WHERE studentNo=(
SELECT subjectNo FROM subject WHERE subjectName='Logic Java') AND examData=(SELECT max(examData) FROM results WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java') ));
仔细阅读示例4的代码可以发现，这是一个包含了四层嵌套子循环的查询语句。第四层子查询用于获得'Logic Java'课程的课程编号，对应的语句是：
SELECT studentNo FROM subject WHERE subjectName='Logic Java';
第三层用于在第4层子查询获得'Logic Java'课程编号的基础上，获得该课程最近一次的考试时间，代码如下：
SELECT MAX(examData) FROM student WHERE studentNo=(SELECT studentNo FROM subject WHERE subjectName='Logic Java');
有了'Logic Java'课程最近一次的考试日期后，，执行第二层子查询：

SELECT studentNo FROM RESULTS WHERE studentNo=(
SELECT subjectNo FROM subject WHERE subjectName='Logic Java') AND examData=(SELECT max(examData) FROM results WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java') );

4.2.2 NOT IN 子查询

学习了IN子查询，下面再来看一个新问题

【示例5】查询未参加'Logic Java'课程最近一次考试的再读学生名单。
SELECT studentName FROM student WHERE studentNo NOT IN (
SELECT studentNo FROM RESULTS WHERE subjectNo=(SELECT studentNo FROM subhect) AND examDATA=
(SELECT MAX(examData) FROM RESULTS WHERE subjectNO=
(SELECT subjectNO from subject WHERE subjectName='Logic Java' )))

【示例6】查询开设‘Logic Java’课程所在年级(即年级ID为1)中未参加这门课程最近一次考试的在读学生名单：
SELECT studentName FROM student WHERE studentNo NOT IN (
SELECT studentNo FROM RESULTS WHERE subjectNo=(SELECT studentNo FROM subhect) AND examDATA=
(SELECT MAX(examData) FROM RESULTS WHERE subjectNO=
(SELECT subjectNO from subject WHERE subjectName='Logic Java' ))) AND gradeID=(SELECT gradeId FROM subject WHERE subjectNasme='Logic Java')；

上机练习2 查询某年级开设的课程
使用IN子查询年级名称为S1的年级所开设的课程
(1)首先查询年级名称为S1的年级开设的所有课程编号，
(2)根据课程编号查询课程表得到课程名称
SELECT subjectName FROM subject WHERE subjectNO IN( SELECT subjectNo FROM subject WHERE gradeId=S1);

4.3 按指定条件查询考试成绩

4.3.1 EXISTS子查询

        EXISTS子查询用来确认后边的查询是否继续进行，返回值是TRUE或false，例如如果存在数据表temp，则先删除它，然后创建:
DROP TABLE IF EXISTS temp;
除以上已发之外，EXISTS也可以作为WHERE语句的子查询，其基本格式如下
SELECT ...FROM 表名 WHERE EXSITS(子查询)
EXISTS关键字后面的参数是一个任意的的子查询，如果该子查询有返回行，则EXISTS子查询的结果为true，此时再执行外层的查询，如果子查询没有返回行，则EXISTS子查询的结果为FALSE，此时不在执行外层语句

【示例7】

查询‘Logic Java’课程最近一次考试成绩，如果有80分以上者，则显示成绩排名前5名的学生学号和分数：
(1)使用EXISTS检测是否有学生考试成绩子啊80分以上
(2)如果有成绩在80分以上者，则使用SELECT 语句按成绩从高到低排序，显示前5名学生的学号和成绩；
1 找到‘Logic Java’的课程编号
SELECT subjectNo FROM subject WHERE subjectName='Logic Java';
2 找到‘Logic Java’最近一次的考试时间
SELECT MAX(examData) FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java';)
3 查询‘Logic Java’最近一次的考试时间的考试学生学号和成绩以及考试日期
SELECT studentNO,studentResult,examData FROM RESULTS WHERE examData=(SELECT MAX(examData) FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java')) AND subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java')
4 判断是否存在超过80分的
SELECT * FROM results WHERE subjectNo=(SELECT studentNO,studentResult,examData FROM RESULTS WHERE examData=(SELECT MAX(examData) FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java')) AND subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java')) AND studentResult>80
5 判断是否存在，若存在则显示前五个
SELECT * FROM results WHERE EXISTS(SELECT * FROM results WHERE subjectNo=(SELECT studentNO,studentResult,examData FROM RESULTS WHERE examData=(SELECT MAX(examData) FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java')) AND subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java')) AND studentResult>80) AND subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java') ORDER BY studentResult DESC LIMIT 5;

4.3.2 NOT EXISTS子查询

EXISTS和IN一样，同样允许添加NOT关键字实现取反操作NOT EXISTS表示不存在对应查询条件的记录

【示例8】查询'Logic Java'课程最后一次考试成绩。如果学生全部没有通过考试(60及格)，则认为本次考试骗难，计算该次考试平均成绩，并添加10分。
(1) 找到'Logic Java'的课程编号
SELECT subjectNo FROM results WHERE subjectName='Logic Java';
(2) 根据课程编号找到该课程最后一次考试的时间
SELECT MAX(examData) FROM results WHERE subjecNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java');
(3)课程、时间以及是否都及格查询满足要求的学生
SELECT * FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java') AND EMAMData=(SELECT MAX(examData) FROM results WHERE subjecNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java')) AND studentResult>=60
(4)用EXISTS判断3是否满足进行下一步
SELECT AVG(studentResult)+10 AS AVERAGESCORE FROM student WHERE NOT EXISTS(SELECT * FROM RESULTS WHERE subjectNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java') AND EMAMData=(SELECT MAX(examData) FROM results WHERE subjecNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java')) AND studentResult>=60) AND subjectNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java') AND examData=(SELECT MAX(examData) FROM results WHERE subjecNo=(SELECT subjectNo FROM results WHERE subjectName='Logic Java'));
注意EXISTS和NOT EXISTS的结果只取决于是否有返回记录，不取决于这些记录的内容，所以EXISTS子查询或NOT EXISTS子查询后SELECT语句中的字段列表通畅都无关紧要。
上机练习4 查询所在年级ID为2的学生的考试成绩信息
查询参加年级ID为2的课程考试的成绩信息
查询参加年级ID为2的可成考试的学生学号、课程编号、考试成绩、考试信息
(1)从学生表中查询是否存在年级ID为2的学生信息。
(2)如果存在，从成绩表中查询年级ID为2的可成的学生成绩信息，年级ID为2可能有多门课程
SELECT studentNo,subjectNo,studentResult,examData FROM results WHERE EXISTS(SELECT * FROM RESULTS WHERE gradeId=2) WHERE gradeId=2;

4.4 任务4：统计某门课程考试信息

任务目标
了解子查询语句可以出现的位置
掌握嵌套在SELECT语句的SELECT子句中的子查询
掌握嵌套在SELECT语句中FROM 子句中的查询
在前面的部分章节中，我们已经学习了使用简单子查询，并掌握了使用IN，NOT IN,EXISTS和NOT EXISTS子查询的方法，编写子查询语句时，要注意如下事项

1 子查询语句出现的位置

子查询语句可以嵌套在SQL语句中任何表达式出现的位置。在SELECT 语句中，子查询可以被嵌套在SELECT语句的列、表和查询条件中，即SELECT子句、FROM子句、WHERE子句，GROUP BY子句和HAVING子句中。GROUP BY 关键字和HAVING子句中，GROUP BY 关键字和HAVING关键字在后面章节会进行讲解。前面介绍了WHERE子句中嵌套子查询的使用方法，下面介绍子查询在SELECT子句和FROM子句中的使用方法

2 嵌套在SELECT 语句的SELECT子句中的查询

SELECT (子查询) FROM 表名；

【示例9】

在数据库myshcool中，查询咱家‘Logic Java’课程最近一次考试的相关信息，输出学生新明、课程名称、考试时间以及考试成绩
分析如下
(1)获得‘Logic Java’课程的课程编号
(2)根据课程编号查询‘Logic Java’课程最近一次的考试日期
(3)通过查询考试表，可以获得参加Logic Java课程最近一次考试的考试信息和学生ID。根据需求，需要输出学生的姓名和课程名称。因此需要通过学生ID在学生表中查询出学生姓名
SELECT studentName FROM student WHERE results.studentNo=student.studentNo AS 学生姓名；需要通过课程编号在课程表中查询出课程名称并输出，代码如下
SELECT  subjectName FROM subject WHERE RESULTS.subjectNo=subject.subjectNo AS 课程名称，
注意以上代码中的WHERE条件，这里使用.操作来引用表中某个字段值，下面采用SELECT语句的SELECT子句嵌套子查询来实现完整需求
关键代码：
#查询学生姓名并输出
SELECT (SELECT studentName FROM student WHERE results.studentNo=student.studentNo) AS 学生姓名,(SELECT subjectName FROM subject WHEREresults.subjectNo=subject.subjectNo) AS 课程名称,
results.examData AS 考试时间 student.result AS 考试成绩
FROM RESULTS
#查询获得Logic Java课程编号
WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java') AND 
#查询最近一次考试的事件
examDate=(SELECT MAX(examDat) FROM results WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java'))

3 嵌套在SELECT语句的FROM子句中的子查询
SELECT * FROM （子查询） AS表的别名
当子查询嵌套在FROM子句中时，必须为表指定别名，一般返回多行多列数据，可以当作一张临时表

【示例10】输出年级ID为1的所有女生信息

SELECT * FROM (SELECT * FROM student WHERE sex='f AND gradeId='1') AS tmp;
以上代码使用AS关键字指定表别名为TEMP，如果忘记指定表别名，就会出现错误
因此应该为FROM子查询的结果集指定别名，正确代码如下：
SELECT *FROM （SELECT * FROM results） AS Temp;

4 关于子查询的输出列的说明
只出现在子查询中二没有出现咋子父查询中的表不能包含在输出列中。
多层嵌套子查询的最终数据集只包含父查询的SELECT中出现的字段，而子查询的输出结果通畅会作为其外层子查询数据源或作为数据判断条件

上机练习5 统计某门课程的考试信息

(1)统计'Logic Java'课程最近一次考试学生应到人数、实到人数和缺考人数。
(2)提取‘Logic Java’课程最近一次考试成绩前三名的学生信息并保存结果。学生的信息包括学生信命、学号、成绩

提示：
(1)使用子查询统计考试情况，包括应道人数、实到人数和缺考人数
(2)将查询后的结果保存到tempResult中

参考解决方案如下

(1)查询获得‘Logic Java’课程的课程编号和最近一次的考试日期
SELECT subjectNo FROM subject WHERE subjectName='Logic Java';
#查询最近一次的考试日期
SELECT max(examData) FROM results WHERE subjectNo=(#SELECT subjectNo FROM subject WHERE subjectName='Logic Java')
(2)查询获得‘Logic Java’课程所在的年级编号
SELECT gradeId FROM subject WHERE subjectName='Logic Java';
(3)使用子查询统计缺考人数
1 应到人数
SELECT count(*) FROM student WHERE gradeId=(SELECT gradeId FROM subject WHERE subjectName='Logic Java');#条件：年级编号为‘Logic Java’课程所在年级编号。
2 实到人数
SELECT count(*) FROM results WHERE subjectNo=(SELECT subjectNo FROM subject WHERE subjectName='Logic Java') AND examData=(SELECT max(examData) FROM results WHERE subjectNo=(#SELECT subjectNo FROM subject WHERE subjectName='Logic Java'));
3 缺考人数：应到人数-实到人数
(4)提取成绩前三名的学生信息并保存结果，包括学生姓名、学号和考试成绩
1 提取的成绩信息包含两个表的数据，所以考虑使用SELECT的子查询。提取成绩前三名使用OEDER BY 和LIMIT子句
SELECT (SELECT student.studentName FROM student WHERE student.studentNo=results.studentNo) AS 学生姓名,
results.studentNo AS 学号，studentResult AS 考试成绩
FROM results
WHERE examData=(省略代码) AND subjectNo=(省略代码)
ORDER BY studentResult DESC
LIMIT 3；
2 将查询后的结果保存在tempResult中
DROP TABLE IF EXISTS tempResult；
CREATE TABLE tempResult(
第一步的代码
)；