数据库中的数据查询——嵌套查询

发表于 更新于 分类于

数据查询-嵌套查询

嵌套查询概述:

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

例如:

1
2
3
4
5
6
7
8
#查询选修了二号课程的学生的姓名
SELECT Sname    #外层查询 / 父查询
FROM Student
WHERE Sno IN (
  SELECT Sno    #内层查询 / 子查询
  FROM SC
  WHERE Cno='2'
);

注意: ORDER BY子句不允许出现在内层查询中, 因为其是对最终结果集的操作.

嵌套查询基本规则/定义:

  • 上层查询块被称为外层查询 / 父查询;
  • 下层查询块被称为内层查询 / 子查询;
  • SQL语言允许多层嵌套查询;
  • 子查询中不允许使用ORDER BY子句;

嵌套查询可分为两类: 不相关子查询 与 相关子查询.

不相关子查询:

子查询的查询条件不依赖于父查询

步骤:

  • 由里向外逐层处理.
  • 每个子查询在上一级查询处理之前求解.
  • 子查询的结果用于建立父查询的查找条件.

相关子查询:

子查询的查询条件依赖于父查询

步骤:

  • 取外层查询中表的第一个元组, 根据它与内层查询相关的属性值处理内层查询.
  • 若WHERE子句返回值为真, 则取此元组放入结果表.
  • 然后再取外层表的下一个元组, 重复这一过程, 直到外层表全部被检查完为止.

带有IN谓词的子查询

IN谓词表名集合之间的所属关系
如最上面的例子中, Sno属于子查询的结果集里的Sno.

例子1-1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查询与刘晨在同一个系学习的学生学号, 姓名
SELECT Sno, Sname     #第二步: 根据刘晨的系名查询所有学生的学号, 姓名
FROM Student
WHERE Sdept IN (      #若这里Sdept确定只有唯一的值, 那么IN也可以用=代替,这涉及到带有比较运算符的子查询
  SELECT Sdept        #第一步: 确定刘晨所在系名
  FROM Student
  WHERE Sname = '刘晨'
);
#此查询为不相关子查询
#将上述例子转变为连接实现,而不是嵌套实现
SELECT S1.Sno, S1.Sname
FROM Student S1, Student S2
WHERE S1.Sdept = S2.Sdept
  AND S2.Sname = '刘晨';

嵌套查询允许多重嵌套(理论上无限套娃):

例子1-2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#查询选修了课程名为"信息系统"的学生学号和姓名
SELECT Sno, Sname #第三步: 找出这些学号的学生名字
FROM Student
WHERE Sno IN (
  SELECT Sno      #第二步: 找出选修该课程号学生的学号
  FROM SC
  WHERE Cno IN (
    SELECT Cno    #第一步: 找出信息系统课程的课程号
    FROM Course
    WHERE Cname = '信息系统'
  )
);
#此查询为不相关子查询
#将上述例子转变为连接实现,而不是嵌套实现
SELECT Student.Sno, Student.Sname
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno
  AND SC.Cno = Course.Cno
  AND Course.Cname = '信息系统';

带有比较运算符的子查询

  • 当能确切直到内层查询返回单值时,可用比较运算符 (>, <, =, >=, <=, !=, <>)

例子2-1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#将例子1-1由 带有IN谓词的子查询 改造为 带有比较运算符的子查询(查询与刘晨在同一个系学习的学生学号, 姓名)
SELECT Sno, Sname     #第二步: 根据刘晨的系名查询所有学生的学号, 姓名
FROM Student
WHERE Sdept = (       #由于刘晨只可能在一个系学习,因此可用用=代替IN
  SELECT Sdept        #第一步: 确定刘晨所在系名
  FROM Student
  WHERE Sname = '刘晨'
);
#找出每个学生超过他所 选修课程的平均成绩 的课程号.(课程平均成绩,不是他的平均成绩)
#-----方式1: 不相关子查询-----
SELECT Sno, Cno
FROM SC
WHERE Grade >= (      #错误, 返回值不唯一
  SELECT AVG(Grade)
  FROM SC
  GROUP BY Cno
);
#-----方式2: 相关子查询-----
SELECT Sno, Cno
FROM SC x
WHERE Grade >= (      #正确, 返回值唯一
  SELECT AVG(Grade)
  FROM SC y
  WHERE y.Cno = x.Cno #子查询的Cno依赖父查询的Cno
)
ORDER BY Sno ASC, Cno ASC;
#方式2中子查询的依赖父查询的一个值Cno,因此是相关子查询

相关子查询可能的执行过程:

  • 从外层查询中取出SC的一个元组x, 将元组x的Cno值传给内层查询.
  • 在内层查询中得到值, 用该值替代内层查询, 得到外层查询
  • 再从外层查询中取一个元组, 如此往复, 直到外层表的所有元组遍历完毕.
  • (可以视作一个循环)

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

在谓词逻辑中, 由存在量词和全称量词的概念, 在SQL中没有对对应的表达, 统一采用”谓词”来表达.

SQL中实现全称量词和存在量词概念的方式:

  1. 引入 ANY 和 ALL 谓词, 其对象为某个查询结果, 表示其中任意一个值或者全部值.
  2. 引入 EXIST 谓词, 其对象也是某个查询结果, 表示这个结果是否为空, 返回真值.

使用ANY或ALL谓词时必须同时使用比较运算符, 其语义解释如下:

谓词结合运算符语法 语义
> ANY / < ANY 大于 / 小于 子查询结果中的某个值
> ALL / < ALL 大于 / 小于 子查询结果中的所有值
>= ANY / <= ANY 大于等于 / 小于等于 子查询结果中的某个值
>= ALL / <= ALL 大于等于 / 小于等于 子查询结果中的所有值
= ANY / !=(或<>) ANY 等于 / 不等于 子查询结果中的某个值
= ALL / !=(或<>) ALL 等于 / 不等于 子查询结果中的所有值 (通常没有实际意义)

例子3-1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (  #思路: 年龄要小于任意一个计算机系学生的年龄
  SELECT Sage
  FROM Student
  WHERE Sdept = 'CS'
)
AND Sdept != 'CS';  #父查询块中的条件: 只要查非计算机系的人.
#重构上述SQL语句, 使得不用ANY / ALL谓词实现相同功能
SELECT Sname, Sage
FROM Student
WHERE Sage < (      #思路: 年龄要小于任意一个计算机系学生的年龄 => 年龄小于计算机系年龄最大的学生的年龄
  SELECT MAX(Sage)
  FROM Student
  WHERE Sdept = 'CS'
)
AND Sdept != 'CS';   #父查询块中的条件: 只要查非计算机系的人.

可见, ANY / ALL谓词与运算符相组合处, 通常可以用聚集函数 / IN谓词等价代换.

ANY/ALL谓词 与 聚集函数/IN谓词的等价代换关系:

= !=或<> < <= > >=
ANY IN – <MAX <=MAX >MIN >=MIN
ALL – IN <MIN <=MIN >MAX >=MAX

带有EXISTS谓词的子查询

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

  • 若内层查询结果 非空 , 则外层的WHERE子句返回 true
  • 若内层查询结果 为空 , 则外层的WHERE子句返回 false

由EXISTS引出的子查询, 其目标列表达式通常都用*, 因为带EXISTS的子查询只返回true或false, 给出列名无实际意义.

例子4-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS (      #子查询结果返回true或false
  SELECT *
  FROM SC
  WHERE SC.Sno = Student.Sno
    AND SC.Cno = '1';
);
#查询没有选修1号课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS (  #子查询结果返回true或false
  SELECT *
  FROM SC
  WHERE SC.Sno = Student.Sno
    AND SC.Cno = '1';
);

所有带IN谓词, 比较运算符, ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换.
但反过来则不成立, EXISTS可表达的逻辑更多.

EXISTS可以实现全称量词

例子4-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#将例子1-1的实现由 使用IN谓词 改为 使用 EXISTS谓词 (查询与刘晨在同系学习的学生的学号, 姓名)
#----------使用IN谓词实现----------
SELECT Sno, Sname     #第二步: 根据刘晨的系名查询所有学生的学号, 姓名
FROM Student
WHERE Sdept IN (
  SELECT Sdept        #第一步: 确定刘晨所在系名
  FROM Student
  WHERE Sname = '刘晨'
);
#----------使用EXISTS谓词实现----------
SELECT Sno, Sname
FROM Student S1
WHERE EXISTS (        #思路:针对每个学生查询其所在的系是否有刘晨这个人
  SELECT *
  FROM Student S2
  WHERE S2.Sdept = S1.Sdept
    AND S2.Sname = '刘晨'
);

例子4-3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查询选修了所有课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS (    #思路: 对于这个学生, 不存在任何一门课
  SELECT *
  FROM Course
  WHERE NOT EXISTS (  #思路: 对于这个课程, 不被该学生选中
    SELECT *
    FROM SC
    WHERE SC.Sno = Student.Sno
      AND SC.Cno = Course.Cno
  )
);
#关系的代数里使用除法实现, 这里用NOT EXISTS也实现了

用EXISTS实现逻辑蕴涵:
可以把所有带有全称量词的谓词转换为等价的带有存在量词的谓词

例子4-4:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查询至少选修了学生201215122选修的全部课程的学生号码
SELECT Sno
FROM SC SCX
WHERE NOT EXISTS (    #对于这个学生, 不存在任何一个被201215122选修的课程
  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
  )
);