实验七 嵌套查询（V2.0）
实验七 嵌套查询
[实验目标]
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 snamefrom studentwhere 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>结束。
>>说明：
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。与ANY或ALL谓词配合使用
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 ’;
>>谓词语义
ANY：任意一个ALL：所有值
4）带有EXISTS谓词的子查询
例5.查询所有选修了1号课程的学生姓名。
解题思路：
本查询涉及Student和SC关系 Student中依次取每个元组的Sno值，用此值去检查SC关系 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= '1'，则取此Student.Sname送入结果关系
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1')
>>说明：
1.EXISTS谓词——存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHERE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 2.NOT EXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值
例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 Student = _______
AND SC.Cno = _______
AND ___________=‘信息系统’;
3. 查询至少选修了学生200215122选修的全部课程的学生号码。
[实验总结]
1.相关子查询与不相关子查询。
2.带有IN谓词，带有比较运算，带有ANY或ALL，带有Exists或Not Exists谓词的嵌套查询。