SQL语言使用第五次实验（嵌套查询）

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
需要注意！子查询的SELECT语句不能使用ORDER BY子句，因为它只能对最终的查询结果进行排序。

一、带有IN谓词的子查询

子查询的结果是一个集合时使用
例3.55 查询与“刘晨”在同一个系学习的学生
(1)先分布进行查询，先找到刘晨所在系，再查找在该系学生。

SELECT Sdept
FROM Student
WHERE Sname='刘晨';/*先确定刘晨所在系，结果为CS*/

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept='CS';

（2)将第一步查询嵌入到第二步查询的条件中，构造嵌套循环

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨');

（3）将子查询的结果用于建立其父查询的查找条件，就是将分布查询第二步的=改为IN，再加个括号就行了

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN('CS');

（4）用自身条件来完成

SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';

例3.56查询选修了课程名为“信息系统”的学生学号和姓名
先通过课程名在课程表里找到这门课的课程名，再通过SC表找到选这门课的学号，再通过学生表找到学生姓名，所以需要三个嵌套循环。

SELECT Sno,Sname/*题目让求的在最外面*/
FROM Student
WHERE Sno IN
(SELECT Sno/*再通过课程号找到学号*/
FROM SC
WHERE Cno IN
(SELECT Cno/*先找到课程号，在最内层*/
From Course
WHERE Cname='信息系统'
)
);

用连接查询实现

SELECT Student.Sno,Sname/*注意这里的Sno一定要加前缀，因为他是在几个表中选择的，有重复会混淆*/
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Cname='信息系统';

上述两个例子中子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。反之，子查询的条件依赖于父查询，则称为相关子查询，整个查询语句称为相关嵌套查询。

二、带有比较运算符的子查询

带比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当内查询的结果是单个值时，可以用>,<,=,>=,<=,!=,<>等比较运算符。
例3.57找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

SELECT AVG(Grade)/*先从外层查询中取出SC的一个元组x,将元组x的Sno值（202115126）传送给内层查询。*/
FROM SC y
WHERE y.Sno='202115126';

SELECT Sno,Cno/*执行内层查询，得到值94，再用该值代替内层查询，得到外层查询。*/
FROM SC x
WHERE Grade>=94;
/*接着外层查询再取出下一个元组重复做上述的步骤的处理，直到外层的SC元组全部处理完毕。

这道题就是求解相关子查询，它不能像求解不相关子查询一样一次将子查询结果求解出来，然后求解父查询，内层查询由于与外层查询有关，因此必须反复求值。

三、带有ANY（SOME)或ALL谓词的子查询

子查询返回多值时要用ANY（SOME)或ALL谓词修饰符。而使用ANY或ALL时则必须同时使用比较运算符
ANY是某个值，ALL是全部值
例3.58查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。
注！：这里的任意是指某个值，因此要用ANY

SELECT Sage
FROM Student
WHERE Sdept='CS'

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage/*首先处理子查询，找出CS系中所有学生的年龄，构成一个集合*/
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';/*然后处理父查询，找所有不是CS系且年龄小于该集合任意一个年龄的学生*/

也可使用聚集函数实现，即将ANY的谓词修改为年龄小于CS系中的最大值

SELECT Sage
FROM Student
WHERE Sdept='CS'

SELECT Sname,Sage
FROM Student
WHERE Sage<(SELECT MAX (Sage)/*修改这一部分即可，因为是小于一个就行，所以小于最大的就可以了*/
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';

例3.59查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄

SELECT Sage
FROM Student
WHERE Sdept='CS'

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL(SELECT Sage/*题目是所有，所以要用ALL*/
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';

用聚集函数实现

SELECT Sage
FROM Student
WHERE Sdept='CS'

SELECT Sname,Sage
FROM Student
WHERE Sage<(SELECT MIN (Sage)/*所有就是小于最小的学生就可以了*/
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';

一些ANY,ALL谓词与聚集函数，IN谓词的等价转换关系

	=	<>或！=	<	>
ANY	IN	–	<MAX	>MIN
ALL	–	NOT IN	<MIN	>MAX

四、带有EXISTS谓词的子查询

EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值和逻辑假值
例3.60查询所有选修了1号课程的学生的姓名

SELECT Sname
FROM Student
WHERE EXISTS
(SELECT */*由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值*/
FROM SC
WHERE Sno=Student.Sno AND Cno='1');

该例因为子查询条件依赖于外层父查询的某个属性值，所以它也是相关子查询。具体过程是：首先取外层查询中Student表中的第一个元组，根据它与内层查询相关的属性值（Sno值）处理内层查询，若WHERE子句返回值为真，则取外层查询中该元组的Sname放入结果表，接着取Student表的下一个元组，直到外层Student表全部检查完为止。
例3.61 查询没有选修1号课程的学生的学生姓名

SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');

例3.62 查询选修了全部课程的学生的学生姓名
SQL中没有全称量词，因此需要把带有全称量词的谓词转换为等价的带有存在量词的谓词
这要用到离散的知识，将题目变为没有一门课程是他不选修的。

SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT*
FROM SC
WHERE Sno=Student.Sno
AND Cno=Course.Cno));

这道题我的理解是把它看成一个嵌套循环，所有学生是第一层循环，从第一个学生往下执行，所有课程是第二层循环，然后第一个学生和第一门课程匹配，如果在SC中找到了第二层循环的条件就不满足，继续匹配下一个课程，当所有课程都匹配完后，如果在SC中都能找到那么第二层循环的NOT EXISTS就不满足，也就是在第一层循环里找不到满足条件的，那么第一层循环的条件就满足了，就可以将结果保存了。反之，如果在第二层循环里匹配时并没有在SC里找到，说明这个学生有一个课程没有选，就满足了第二层循环的不存在条件，那么第一层循环就是可以找到了，也就不满足它的条件就不保存结果了，接着继续执行第二个学生，一直到所有学生执行完毕。

有点绕，但是我也理解了很长时间，也就是双重否定表肯定，最后感觉把它看成一个双重循环还好理解一点，题目要求的结果放在最外面，满足条件的放在最中间，产生关系的放在最里面。

例3.63 查询至少选修了学生201215122选修的全部课程的学生号码。
先将题目转化为不存在这样的课程y，学生202115128选修了y,而学生x没有选。

SELECT DISTINCT Sno/*DISTINCT是不允许重复*/
FROM SC SCX/*SCX是SC中的一个元组*/
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno='202115128'AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));

先从SC中选出一个元组往下进行，找到学号为202115128的开始匹配他所选的课程，先是第一个进入条件的学号，如果SCY所选的课程它在SC中都能找到，那么第二层嵌套就不满足条件，则第一层找不到就满足条件，结果就可以保存了，接着进行第二个元组的判断，一旦出现找不到的第二层嵌套就可以满足，则第一层就满足不了，即把该元组舍弃继续判断即可。

这节课讲的其实上课没有听懂，课下又好好看了看书才差不多，最后的部分主要是条件的转换，以及多重嵌套，把它看成循环嵌套也就好理解些了。