sql数据查询之———嵌套查询

一、嵌套查询概述

  • 一个 SELECT-FROM-WHERE 语句称为一个查询块。
  • 将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为 嵌套查询。

例如:

1
2
3
4
5
6
7
SELECT Sname	                           /*外层查询/父查询*/
FROM Student
WHERE Sno IN
( SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');

注意:子查询的SELECT语句中不能使用 ORDER BY 子句,因为 ORDER BY 子句只能对最终查询结果排序。

  • 不相关子查询:子查询的查询条件不依赖于父查询
  • 相关子查询:子查询的查询条件依赖于父查询

(后续在对二者详细解释)

二、嵌套查询

1.带有in谓词的子查询

例1:查询与“刘晨”在同一个系学习的学生。

分部完成:

确定“刘晨”所在系名

1
2
3
4
SELECT  Sdept  
FROM Student
WHERE Sname= ' 刘晨 ';

假设结果为CS

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

1
2
3
4
SELECT   Sno, Sname, Sdept     
FROM Student
WHERE Sdept= ' CS ';

第一部就是子查询,第二步为父查询

那么可以写成:

1
2
3
4
5
6
7
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');

注:这是不相关查询,即子查询条件与父查询条件无关

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

1
2
3
4
5
6
7
8
9
10
11
12
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= '信息系统'
)
);

当然也可以用连接查询实现:

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

2.带有比较运算符的子查询

当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。

对于第一个例子来说,由于一个学生只可能在一个系里学习,所以可以用 = 代替 in

1
2
3
4
5
6
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');

3.带有ANY(SOME)或ALL谓词的子查询

子查询返回单值时可以用比较运算符,但返回多值时要用ANY(有的系统用SOME)或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义如下:

>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ANY 小于子查询结果中的某个值
<ALL 小于子查询结果中的所有值
>=ANY 大于等于子查询结果中的某个值
>=ALL 大于等于子查询结果中的所有值
<=ALL 小于等于子查询结果中的所有值
<=ANY 大于等于子查询结果中的某个值
=ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值(通常没有实际意义)
!=(或<>)ANY 不等于子查询结果中的某个值
!=(或<>)ALL 不等于子查询结果中的任何一个值

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

1
2
3
4
5
6
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY (SELECT Sage
FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';

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

1
2
3
4
5
6
7
SELECT Sname,Sage
FROM Student
WHERE Sage<ALL
(SELECT Sage
FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';

提示:本查询同样可以用聚集函数实现

1
2
3
4
5
6
7
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept='CS')
AND Sdept <>'CS';

截屏2020-04-02下午9.25.09.png

4.带有EXISTS谓词的子查询

带有EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。

例1: 查询所有选修了1号课程的学生姓名

1
2
3
4
5
6
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
  • 本查询涉及Student和SC关系
  • 在Student中依次取每个元组的Sno值,用此值去检查SC表
  • 若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果表

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

直接在EXISTS前加NOT

重难点

例3:查询选修了全部课程的学生姓名

1
2
3
4
5
6
7
8
9
10
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=Course.Cno));

用EXISTS/NOT EXISTS实现全称量词(难点)

  • SQL语言中没有全称量词$\forall$(For all)
  • 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:$ (\forall x)P = ﹁(\exist x(﹁P))$

例4 :查询至少选修了学生201215122选修的全部课程的学生号码

1
2
3
4
5
6
7
8
9
10
11
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));

三、总结

不同形式的查询间的替换

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

所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换