实验七 嵌套查询
[实验目标]
1. 掌握不相关子查询
2. 掌握相关子查询
[实验内容]
1. 基础知识
一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
可以参看例1。
>> 根据求解过程分子查询主要有
(1)不相关子查询
子查询的查询条件不依赖于父查询；
由里向外，逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
(2)相关子查询
子查询的查询条件依赖于父查询。
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；
然后再取外层表的下一个元组；
重复这一过程，直至外层表全部检查完为止。
>>根据嵌套的子句类型分嵌套查询主要类型
1）带有IN谓词的子查询
2）带有比较运算符的子查询
3）带有ANY（SOME）或ALL谓词的子查询
4）带有EXISTS谓词的子查询
2. 实验案例
1）带有IN谓词的子查询。
例1.查询选修了2号课程的学生姓名。
--外查询：查询学号在子查询结果内的学生姓名。
select sname
from student
where sno in (
--子查询：查询选修了2号课程的学生学号。
select sno
from sc
where cno = '2'
)
>>说明：
（1）以上查询中子查询跟外查询没关系，所以可以先做子查询，把结果作为外查询的where子句的一部分，再进行外查询的筛选条件得到结果，这种与外查询没关系的子查询称为为“不相关子查询”。
（2）子查询的限制“不能使用ORDER BY子句”。为什么？子查询是个中间结果，而排序只能对查询结果进行。
（3）层层嵌套方式反映了 SQL语言的结构化。
（4）有些嵌套查询可以用连接运算替代。把例1改成连接运算？
select sname
from student,sc
where student.sno = sc.sno and sc.cno = '2'
2）带有比较运算符的子查询
例2.查询每个学生学号和超过他选修课程平均成绩的课程号。
SELECT Sno,Cno
FROM SC x
WHERE Grade >=
(SELECT AVG(Grade)
FROM SC y
WHEREy.Sno=x.Sno);
>>执行过程
<1> 从外层查询中取出SC的游标指向的（首次为第一行）元组x，将元组x的Sno值（200215121）传送给内层查询。
SELECTAVG(Grade)
FROM SC y
WHERE y.Sno='200215121';
<2> 执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询的查询条件，判断外层查询的当前元组是否满足条件：（满足，则该元组进入结果集，不满足时则不进入结果集）。
SELECTSno， Cno
FROM SC x
WHERE Grade >=88；
<3>外查询表游标下移，如果到了表的尾部，进入下一步结束。否则，返回第一步。
<4>结束。
<5>流程图如图所示：
>>说明：
3）带有ANY（SOME）或ALL谓词的子查询
例3.查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECT _______ , _______
FROM ___________
WHERE Sage < ________(SELECT _________
FROM Student
WHERE Sdept= 'CS ')
ANDSdept<> ‘CS ' ; /*父查询块中的条件 */
例4.查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词
SELECTSname，Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= 'CS ')
AND Sdept<> ' CS ’;
方法二：用聚集函数
SELECT Sname，Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= 'CS ')
AND Sdept<>' CS ’;
>>谓词语义
4）带有EXISTS谓词的子查询
例5.查询所有选修了1号课程的学生姓名。
解题思路：
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1')
>>说明：
例6：查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1')；
> （难点）例7：查询选修了全部课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
（SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno= Student.Sno
AND Cno= Course.Cno
）
）
命题一：该同学选修了全部课程
命题二：存在一门课程该同学没选修是假的。
命题三：该同学不存在一门课程（全部课程中）不选修。
命题一与命题二等价。
[实验测试]
1. 查询与“刘晨”在同一个系学习的学生。
方法一、用带有IN的谓词。
SELECT Sno，Sname，Sdept
FROM Student
WHERE Sdept IN
(SELECT________
FROM ___________
WHERE Sname= '刘晨');
方法二、用自身连接。
SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE ________________ AND
S2.Sname = '刘晨';
2. 查询选修了课程名为“信息系统”的学生学号和姓名。
SELECT ______ , _____ ③最后在Student关系中
FROM _______ 取出Sno和Sname
WHERE Sno IN
(SELECT Sno ②然后在SC关系中找出选
FROM ____ 修了3号课程的学生学号
WHERE Cno IN
(SELECT _____ ①首先在Course关系中找出
FROM Course “信息系统”的课程号，为3号
WHERE _______= ‘信息系统’
)
);
用连接方法实现：
SELECTSno, Sname
FROM ________,____,________
WHERE ________________
AND SC.Cno = _______
AND ___________=‘信息系统’;
3. 查询至少选修了学生200215122选修的全部课程的学生号码。（画出查询过程的流程图，写出查询语句）
[实验总结]
1.相关子查询与不相关子查询。
2.带有IN谓词，带有比较运算，带有ANY或ALL，带有Exists或Not Exists谓词的嵌套查询。