嵌套查询
概述：一个SELECT-FROM-WHERE语句称为一个查询块；将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
( SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');
#括号里面相当于是一个整体，两个sno是相同的
注：
- 上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询;
- 子查询需要用圆括号括起来;
- 子查询不支持ORDER BY 子句,不支持LIMIT;
- 子查询结果值必须与WHERE 子句要求的数据类型匹配,不能包含text 或blob (二进制)数据类型的字段;
- 子查询中也可以再包含子查询,嵌套可以多至32 层;
- 子查询在FROM 子句中时,基于派生表的查询，必须为其定义一个别名;
- SELECT 关键字后面也可以定义子查询;相当于增加一列
- 在一般情况下不推荐使用子查询，建议多用连接查询。
一、带有IN谓词的子查询
1.查询与“张三”在同一个系学习的学生。
此查询要求可以分步来完成
① 确定“张三”所在系名
SELECT Sdept
FROM Student
WHERE Sname= ' 张三 ';
结果为： CS
② 查找所有在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= ' 张三 ');
2.查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno,Sname ③ 最后在Student关系中
FROM Student 取出Sno和Sname
WHERE Sno IN
(SELECT Sno ② 然后在SC关系中找出选
FROM SC 修了3号课程的学生学号
WHERE Cno IN
(SELECT Cno ① 首先在Course关系中找出
FROM Course “信息系统”的课程号，为3号
WHERE Cname= '信息系统'
)
);
#使用连接查询
SELECT Student.Sno, Student.Sname
FROM Student,SC, Course
WHERE Student.Sno= SC.Sno and SC.Cno = Course .Cno and Course .Cname= '信息系统';
二、带有比较运算符的子查询
比较运算符（>，<，=，>=，<=，!=或< >）。
1.查询与“张三”在同一个系学习的学生
由于宇哥学生只可能在一个系学习，则可以用=代替IN
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
WHERE Sname= '张三');
2.找出每个学生超过(大于)他选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC x
WHERE Grade >(SELECT AVG（Grade）
FROM SC y
WHERE y.Sno=x.Sno);
（1）x，y都是sc的别名。
（2）WHERE y.Sno=x.Sno，实际上是自身连接，必须用，否则就是与所有学生的平均成绩比较，没法比较。
可的执行过程：
（1）从外层查询中取出SC的一个元组x，将元组x的Sno值（20210827）传送给内层查询。
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='20210827';
（2）执行内层查询，得到值88.33（近似值），用该值代替内层查询，得到外层查询：
SELECT Sno,Cno
FROM SC x
WHERE Grade >88.33;
（3）执行这个查询，得到
（20210827,1）
然后外层查询取出下一个元组重复做上述①至③步骤，直到外层的SC元组全部处理完毕
三、带有ANY（SOME）或ALL谓词的子查询
使用ANY或ALL谓词时必须同时使用比较运算
语义为：
> ANY 大于子查询结果中的某个值
> ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值
1.查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ‘CS ' ; /*父查询块中的条件 */
执行过程：
（1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
（2）处理父查询，找所有不是CS系且年龄小于20 或 19的学生
使用聚集函数来实现
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MAX（Sage）
FROM Student
WHERE Sdept= 'CS ')
AND Sdept <> ' CS ';
2.查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用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 ';
四、带有EXISTS谓词的子查询
（1）EXISTS谓词（找得到就向外层提交结果）
- 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
- 若内层查询结果非空，则外层的WHERE子句返回真值
- 若内层查询结果为空，则外层的WHERE子句返回假值
- 由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
（2）NOT EXISTS谓词（找不到就向外层提交结果） - 若内层查询结果非空，则外层的WHERE子句返回假值
- 若内层查询结果为空，则外层的WHERE子句返回真值
（3）不同形式的查询间的替换
- 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
- 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
- 建议能用IN谓词、比较运算符、ANY和ALL谓词的子查询，就尽量使用，而不建议用EXISTS，EXISTS复杂，而且不太好理解。
1.查询所有选修了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 ');
2.查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
3.查询与“张三”在同一个系学习的学生。
可以用带EXISTS谓词的子查询替换：
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND S2.Sname = '张三');
4.查询选修了全部课程的学生姓名
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表中，要想知道某个学生是否选修了全部课程：（1）至少我们需要知道一共有几门课程（2）其次，学生选修了与否，我们又要扫描SC全表，统计出选修了所有课程的学生号（3）最后，在STUDENT表中根据学生号输出姓名 。
select Sname
from student
where Sno IN(select Sno
from SC
group by Sno //根据Sno分组，统计每个学生选修了几门课程。如果等于course的总数，就是我们要找的Sno
having count(*)=(select count(*) from course)); //统计course中共有几门课程
5.查询至少选修了学生20210827选修的全部课程的学生号码。
分析：查询学号为x的学生，对所有的课程y，只要20210827学生选修了课程y，则x也选修了y；不存在这样的课程y，学生20210827选修了y，而学生x没有选。
用NOT EXISTS谓词表示：
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));
也可以这样
查询学号和姓名
SELECT * FROM student
WHERE sno IN
(#查询至少选修了该学生选修所有课程的学生信息
SELECT sno FROM sc WHERE cno IN
(#查询20210827学生选修的课程
SELECT cno FROM sc WHERE sno= ' 20210827'
));
嵌套在select字句中的子查询
从sc 表中查找所有学生的平均成绩,以及与’201215121’号学生的平均成绩差距。
SELECT sno, AVG(grade), AVG(grade)-
(SELECT AVG(grade)
FROM sc
WHERE sno= ’ 20210827’)
AS 成绩差距 FROM sc GROUP BY sno;
五、集合查询
1.查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
UNION：将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL：将多个查询结果合并起来时，保留重复元组
2.查询选修了课程1或者选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno=' 1 '
UNION
SELECT Sno
FROM SC
WHERE Cno= ' 2 ';
六、SELECT语句的一般格式
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
[ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
扫一扫
1820
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
