【SQL】嵌套查询与子查询

前言

sql的嵌套查询可以说是sql语句中比较复杂的一部分，但是掌握好了的话就可以提高查询效率。下面将介绍带in的子查询、带比较运算符的子查询、带any/all的子查询、带exists的子查询以及基于派生表的子查询。很多数据库是不区分关键字大小写的，并且关键字还会有高亮，所以我为了写语句方便（不要频繁切换大小写或者按shift键）和看着方便（大写一般还要在大脑中转换下）关键字是使用小写。

什么是SQL嵌套查询

嵌套查询指的是一个查询语块可以嵌套在另外一个查询语句块的where子句或者having子句中，前者为子查询或内查询，后者为父查询或外查询。

表的定义

例子使用的表的定义为：

create table `student`(
	`sno` char(12) collate utf8_bin not null primary key comment '学号',
	`sname` char(30) collate utf8_bin not null comment '姓名',
	`birthday` Date collate utf8_bin comment '出生日期'
)engine=InnoDB default charset=utf8 collate=utf8_bin comment '学号信息表';

create table `course`
(
	`cno` char(4) collate utf8_bin not null primary key comment '课程号',
	`cname` char(40) collate utf8_bin not null comment '课程名',
	`ceredit` smallint not null default 0 comment '学分'
)engine=InnoDB default charset=utf8 collate=utf8_bin comment '课程表';

create table `sc`
(
	`sno` char(12) collate utf8_bin not null comment '学号',
	`cno` char(4) collate utf8_bin not null comment '课程号',
	`score` smallint not null default 0 comment '成绩'
)engine=InnoDB default charset=utf8 collate=utf8_bin comment '学生课程表';
#为sc表添加主键和外键
alter table `sc` add primary key (`sno`,`cno`);
alter table `sc` add foreign key(`sno`) references `student`(`sno`);
alter table `sc` add foreign key(`cno`) references `course`(`cno`);

带in的子查询

in关键字主要用于判断表达式是否在多值列表中。返回在多值列表中的记录。

#列出选修了C001课程的学生的学号、姓名
select sno, sname
from student
where sno in (select sno from sc where cno='C001');

这里子查询里面没有依赖父查询，此种查询也叫做不相关子查询。

若子查询条件依赖于父查询，则为相关子查询。

带比较运算符的子查询

带比较运算符的子查询指父查询与子查询之间通过比较运算符连接，并且子查询返回的是单值，才可以用 = 、<、 >、 != 、>=、 <=等比较运算符连接。

#选出学号为2016110129的同学所选课程中的成绩大于他平均成绩的课程的课程号
select cno from sc as x
where score > 
(
	select avg(score) from sc as y where x.sno=y.sno and x.sno = '2016110129'
)
and sno = '2016110129';

这个子查询依赖于父查询，属于相关子查询。

因为这里将同一张表既作为父查询的表又作为子查询的表，所以将这张表取了两个别名，以便区分。

【查询过程】：将父查询中的sno代入子查询中sno进行匹配，然后判断该记录中的课程成绩是否大于该学生的平均成绩，符合条件则返回该记录，否则继续在子查询中匹配该学生的下一条记录。

带any（some）或all的子查询

子查询返回单值时的比较，可以用上面的比较运算符，当返回多值时需要比较，就要使用any(some)或者all。

若是在与多值序列的比较中，只需要满足与多值序列中的一个值满足比较关系就返回true，则用any(some)。

若是在与多值序列的比较中，需要满足与多值序列中的全部值满足比较关系才返回true，则用all。

#查询选课人数最多的课程号
select cno from sc
group by cno
having COUNT(*) >= all(select COUNT(*) from sc group by cno);

【查询过程】：将sc表中的记录按照cno进行分组，筛选记录数最多的课程号。

 all(select COUNT(*) from sc group by cno)

是找出所有以cno分组的记录数，是一个多值集合。使用>=也就是选出最大的值。

带exists的子查询

exists代表存在量词，带有EXISTS的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑假值“false”。
使用exists的嵌套语句，若子查询结果不为空，则exists返回true，否则返回false。

使用exists引出的子查询，其目标表达式列都使用*，因为带exists的子查询只返回真值或假值，给出列名无实际含义。

#列出选修了C001课程的学生的学号、姓名
select sno,sname from student
where exists(
select * from sc where sc.sno=student.sno and cno='C001'
);

【查询过程】：从student的第一条记录开始查询，将第一条记录代入子查询中，在sc表中匹配该学生选课记录，若匹配到则立刻返回真，父查询中输出该记录；若匹配完后结果仍为空，否则返回假，继续父查询继续代入下一条记录到子查询中查询。

与in子查询的区别：
在带in的子查询中，会遍历sc表中所有记录进行筛选，带exists的查询找到一条记录就返回，不会遍历整个表，所以带EXISTS的查询是一个优质查询。

附加一题作为exists的练习
“查询选了所有课程的学生”
这里需要使用双层带not exists(即不存在)关键词的查询。具体查询语句如下：

#查询选了所有课程的学生
select sno,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
	)
);

这个相当于一个进行一个双重循环，因为是选出学生的信息，所以student表作为“外层循环”，course表作为“内循环”，在sc表中查询学生的选课记录是否存在。

把student表中第一个学生代入“内循环”，然后开始，在sc表中查询该学生是否选了course表中所有课程。

如果遍历了course表后，不存在没有被选的课程（课程在sc表中没有记录），则说明该学生选了所有课程，内部not exists就会返回假，外部not exists返回为真，说明该学生不存在没有选的课程，外部查询输出该学生的信息，然后开始下一个学生的查询。

在遍历course表时，若有一个课程没有被选，则内部就会立刻返回真（不会继续看下一门课程是否被选），外部查询返回为假，说明该学生没有选完所有课程，外部查询就会开始下一个学生的查询。

再附加一个练习
“找出被所有学生选了的课程的课程号和课程名”

select cno,cname from course where not exists(
	select * from student where not exists (
		select * from sc where sc.sno=student.sno and sc.cno=course.cno
	)
);

基于派生表的查询

select 查询的结果也是一张表，可以作为出现在from子句后面作为派生表进行查询。

#求学分获得8分以上学生的学号 平均分  以及总学分  
#需要注意此处的作用域不同，只有该课程的成绩大于60才会获得该课程的学分，平均分包括了所有课程（不及格和及格）
#思路：先将查询到的总学分结果看做是一张表 再与sc表连接进行查询平均分
select sc.sno,total_cre,avg(score) from
	(select sno,SUM(ceredit) as total_cre from sc,course where sc.cno=course.cno 
	and score >= 60
	group by sno 
	having SUM(ceredit) >= 8) as temptable, sc
where temptable.sno=sc.sno 
group by sc.sno,sum_cre;

小结

回顾了学习数据库时的嵌套查询，简单总结了一番。当时觉得其中比较难理解的exists查询，现在梳理了一下理解起来也没有那么困难。sql语句还是要常写，不然一些逻辑复杂点的语句就会理解不到。