感觉是SQL查询中最难的一部分
一个 select-from-where 语句称为一个查询块,将一个查询块嵌套在另一个查询块的where子句或having短语的查询称为嵌套查询
外部的查询称为父查询,下层查询称为子查询
1、带in谓词的子查询
in谓词后接集合,表示查询某属性值在某个集合中的元组,可加not
select Sname,Sdept
from Student
where Sno in
(select Sno
from Student
where Sdept='cs'); --查询cs系的学生,不相关子查询
select Sname,Sdept
from Student
where Sdept='cs'; --与上述等价
select Sname,Sdept
from Student
where Sno in
(select Sno
from SC
where Cno in
(select Cno
from Course
where Cname='数据结构')
); --查询选修了数据结构课的学生,不相关子查询
select Student.Sname,Student.Sdept
from Student,SC
where Student.Sno=SC.Sno and SC.Cno=
(select Cno
from Course
where Cname='数据结构'); --与上述等价
如上为不相关子查询,子查询的条件不依赖于父查询,称为不相关子查询。从下向上分析
select Sname,Cno
from Student,SC as x
where Student.Sno=x.Sno and x.Grade>=(
select avg(Grade)
from SC as y
where y.Sno=x.Sno); --查询选修课成绩高于平均成绩的学生信息,相关子查询
子查询的条件依赖于父查询,称为相关子查询。从下上向下分析
2、any和all谓词
对查询结果进行筛选,any指查询结果中的某个值,all指查询结果中的所有值。可用其他形式代替
= | <>或!= | < | <= | > | >= | |
---|---|---|---|---|---|---|
any | in | – | <max | <=max | >min | >=min |
all | – | not in | <min | <=min | >max | >=max |
select max(Sage) as 最大年龄
from Student
where Sdept='cs';
select Sname,Sage
from Student
where Sdept='cs' and Sage<any
(select Sage
from Student
where Sdept='cs'); --查询cs系中比任意一个学生年龄小的学生
select min(Sage) as 最小年龄
from Student
where Sdept='cs';
select Sname,Sage
from Student
where Sage<all
(select Sage
from Student
where Sdept='cs'); --查询比cs系中所有学生年龄小的学生
3、exists谓词
exists代表存在量词∃,带有exists的子查询不返回数据,只产生逻辑真值true或false,可使用not exists
select Sname
from Student
where exists
(select *
from SC
where Sno=Student.Sno and Cno='1'); --查询选修了1号课的学生
针对外层查询的每一个元组,判断内层查询结果,true则输出,false则不输出
一些带exists或not exists谓词的子查询不能被其他形式的子查询等价替代,但所有带in谓词、比较运算符、any和all谓词的子查询都能用带exists谓词的子查询等价替代
select Sname,Sdept
from Student
where Sdept in
(select Sdept
from Student
where Sname='王伯成'); --与王伯成同一个系的学生
select Sname,Sdept
from Student as s1
where exists
(select *
from Student as s2
where s1.Sdept=s2.Sdept and s2.Sname='王伯成'); --与上述等价
select Sname
from Student
where not exists
(select *
from Course
where not exists
(select *
from SC
where Sno=Student.Sno and Cno=Course.Cno)
); --查询选修了所有课的学生
SQL中没有全称量词,(∀x)P = ┐(∃x(┐)P)
选修了所有课的学生,即没有一门课是该学生不选修的。对每一个Studnet表中的元组,判断他是否没有选修Course表中存在的所有课
select distinct Sno
from SC as scx
where not exists
(select *
from SC as scy
where scy.Sno='2019102' and not exists
(select *
from SC as scz
where scx.Sno=scz.Sno and scz.Cno=scy.Cno)
); --查询至少选修了2019102学号学生选修的所有课的学生
p:学生2019102选修了课程y
q:学生x选修了课程y
(∀y)p → q = ┐(∃y(p∧┐q))
至少选修了2019102学号学生选修的所有课的学生,即不存在这样一门课,2019102学生选了而该学生没有选
SQL提供了这么多的数据查询方法,感觉嵌套查询是最难的,不过很多场景下可以用不同的方法实现同样的目的,SQL语言只是提供了许多的实现方法