SQL Server 数据库实验课第六周——嵌套查询

一、带有IN谓词的子查询

一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
上层查询块称为外层查询，下层查询块称为内层查询或子查询
例 3.55查询与“刘晨”在同一个系学习的学生。
第一种方法：分布完成
① 确定“刘晨”所在系名

SELECT  Sdept  
FROM     Student                            
WHERE  Sname= '刘晨';

② 查找所有在CS系学习的学生。

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

第二种方法：将第一步查询嵌入到第二步查询的条件中

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

第三种方法：用自身连接完成

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

例 3.56查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname
FROM Student
WHERE Sno IN
	(SELECT Sno--选修3号课程的学号
	FROM SC
	WHERE Cno IN(
	SELECT Cno --查出信息系统课程号3
	FROM Course
	WHERE Cname='信息系统'
	)
	);

可用连接查询实现

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
	  SC.Cno=Course.Cno AND
	  Course.Cname='信息系统';

不相关子查询：子查询的查询条件不依赖于父查询。
由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询：子查询的查询条件依赖于父查询，如下面例题3.57
由外向里
①取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
②再取外层表的下一个元组
③重复这一过程，直至外层表全部检查完为止

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

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。如在例 3.55中，由于一个学生只可能在一个系学习，则可以用 = 代替IN
例 3.57找出每个学生超过他选修课程平均成绩的课程号。

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

x是表SC的别名，又称为元组变量，可用来表示SC的一个元组。
执行过程
从外层查询中取出SC的一个元组x，将元组x的Sno值（201215121）传送给内层查询。

SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121‘;

执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：

SELECT Sno,Cno
FROM     SC x
WHERE  Grade >=88; 

执行这个查询，得到
（201215121,1）
（201215121,3）
然后外层查询取出下一个元组重复做上述①至③步骤，直到外层的SC元组全部处理完毕。结果为:
（201215121,1）
（201215121,3）
（201215122,2）

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

使用ANY或ALL谓词时必须同时使用比较运算
语义为：

符号	释义
> ANY	大于子查询结果中的某个值
> ALL	大于子查询结果中的所有值
< ANY	小于子查询结果中的某个值
< ALL	小于子查询结果中的所有值
>= ANY	大于等于子查询结果中的某个值
>= ALL	大于等于子查询结果中的所有值

例 3.58查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
				FROM Student
				WHERE Sdept='CS'
				)
AND Sdept<>'CS';--父查询中除去计算机系的元组

本查询也可用聚集函数实现首先用MAX找出CS系最大年龄

SELECT Sname,Sage
FROM   Student
WHERE Sage < 
			(SELECT MAX(Sage)
             FROM Student
             WHERE Sdept= 'CS ')
AND Sdept <> 'CS';

例 3.59查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词

SELECT Sname,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（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系

四、带有EXISTS谓词的子查询（重点、难点）

存在量词∃
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
①若内层查询结果非空，则外层的WHERE子句返回真值
②若内层查询结果为空，则外层的WHERE子句返回假值
由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
NOT EXISTS谓词
①若内层查询结果非空，则外层的WHERE子句返回假值
②若内层查询结果为空，则外层的WHERE子句返回真值
例 3.60查询所有选修了1号课程的学生姓名。

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

本例为相关子查询

例 3.61查询没有选修1号课程的学生姓名。

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

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换

所有带IN谓词、比较运算符、ANY和ALL谓词的子查询
都能用带EXISTS谓词的子查询 等价替换
例 3.55查询与“刘晨”在同一个系学习的学生。
用带EXISTS谓词的子查询替换IN：

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

用EXISTS / NOT EXISTS实现全称量词，SQL语言中没有全称量词∀
全称量词∀转换为等价的 存在量词（∀ x） P ≡ —（∃ x（— P））
例 3.62查询选修了全部课程的学生姓名。

SELECT Sname
FROM Student
WHERE NOT EXISTS
                (SELECT *
                 FROM Course
                 WHERE NOT EXISTS
                                 (SELECT *
                                  FROM SC
                                  WHERE Sno= Student.Sno
                                  AND Cno= Course.Cno
                                  )
                 );
 --转义后的表达：没有一门课程是他不选修的

因为没有学生选修全部课程，故查询结果为空
例 3.63查询至少选修了学生201215122选修的全部课程的学生号码。
因为p →q ≡ ┐p ∨ q
题目要求即为：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
                (SELECT *
                FROM SC SCY
                WHERE SCY.Sno = '201215122'  AND
                      NOT EXISTS
                       (SELECT *
                        FROM SC SCZ
                         WHERE SCZ.Sno=SCX.Sno AND
                               SCZ.Cno=SCY.Cno));

五、遇到的问题

实验例题3.55的时候

SELECT  Sdept  
FROM     Student                            
WHERE  Sname= '刘晨';

把 Sname= ‘刘晨’，打成了 Sname= ’ 刘晨 ’ 多打了空格导致查不出来了