MySQL学习笔记之多表查询的子查询

今天来学习MySQL中的子查询。子查询就是指在一个查询之中嵌套了其他的若干查询，即在一个select查询语句的where和from子句中包含另一个select查询语句，外层select查询语句称为外层查询或父查询，where子句中的select语句称为内层查询或子查询。有时把上面这种情况也称为嵌套查询。用户可以使用多个简单的查询构成复杂的查询。本篇文章使用的数据和上一篇文章(MySQL学习笔记之对单表数据记录的查询操作)是一样的。

简单子查询

最简单的子查询是返回一个值，通常和比较运算符连用。如查询与“刘晨”在同一个系学习的学生：

# 查询与刘晨在同一个系学习的学生
select s_id, s_name, s_dept
from tab_student
where s_dept = (
	select s_dept
	from tab_student
	where s_name = '刘晨'
);

本样例中，子查询的查询条件是不依赖父查询，这称为不相关子查询，如果子查询的查询条件依赖于父查询，就被称为相关子查询。

子查询和IN、ANY、ALL、EXISTS 关键字的组合

一般当子查询返回的数据记录为多条时，一般会和IN、ANY、ALL和EXISTS等关键字组合使用。

子查询和IN关键字的组合

in关键字主要用于查找属性值是否属于指定的集合，在《MySQL学习笔记之对单表数据记录的查询操作》文章中 已经学习了。因为子查询的结果往往是一个集合，所以in关键字是经常使用的。

# 查询选修了 2 号课程学生的姓名
select s_name
from tab_student
where s_id in (
	select s_id
	from tab_sc
	where c_id = 2
);

子查询和ANY关键字的组合

关键字any用来表示父查询的条件为满足子查询返回结果中任意一条数据。可以和比较运算符进行组合：

• >any或>=any：大于或大于等于子查询结果中的某个值。
• <any或<=any：小于或小于等于子查询结果中的某个值。
• =any：等于子查询结果中的某个值。

通过样例进行理解：查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄，也可以理解成查询非计算机系中年龄比计算机系中最大年龄小的学生的姓名和年龄。

# 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
select s_name, s_age
from tab_student
where s_age < any (
	select s_age
	from tab_student
	where s_dept = 'CS'
) and s_dept != 'CS';

子查询和ALL关键字的组合

关键字all和关键字any在用法上比较相似，表示主查询的条件为满足子查询返回结果中所有数据记录。同样也可以和比较运算符组合使用：

• >all 或 >= all：大于或大于等于子查询结果中的所有值。
• <all 或 <= all：小于或小于等于子查询结果中的所有值。

通过样例进行理解：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄,也可以理解成非计算机系中学生的年龄比计算机系中最小年龄小的学生的姓名和年龄。

# 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄
select s_name, s_age
from tab_student
where s_age < all(
	select s_age
	from tab_student
	where s_dept = 'CS'
) and s_dept != 'CS';

any、all、in和统计函数的等价代换

	=	!=	<	<=	>	>=
any	in	–	<max	<=max	>min	>=min
all	–	not in	<min	<=min	>max	>=max

通过上表我们知道同一个查询请求可以有多种方法，不同的方法其执行效率是不一样的，我们应根据自己的实际情况进行选择。用统计函数实现子查询通常比直接用all或any查询效率高。注： =all 一般为永假，!=any 一般为永真。

子查询和EXISTS关键字

关键字exists用来判断子查询是否返回结果集，有结果集时为true否则为fasle。如：查询所有选修了 3 号课程的学生姓名

# 查询所有选修了 3 号课程的学生姓名
select s_name
from tab_student
where exists (
	select * 
	from tab_sc
	where s_id = tab_student.s_id and c_id = 3
);

在上述代码中子查询结果的值，要用到tab_student.s_id的值，与父查询有关，也就是我们上面刚说的相关子查询。
与exists关键字对应的是not exists关键字，表示如果子查询结果为空，则外层的where子句返回真值，否则返回假值。

在from子句中的子查询

放在from子句里面的子查询，会被当作一张临时表成为父查询的查询对象，必须为临时表指定一个别名。如：找出每个学生超过他自己选修课程平均成绩的课程号。

# 找出每个学生超过他自己选修课程平均成绩的课程号
select s_id, c_id
from tab_sc sc, (select s_id avg_id, avg(sc_grade) avg_grade 
				 from tab_sc group by s_id
				) as avg_sc
where sc.s_id = avg_sc.avg_id and sc.sc_grade >= avg_sc.avg_grade;

小结

今天学习了MySQL中关于子查询的一些基础操作。介绍了简单的子查询，学习了in, any, all, exists关键字的使用和临时表基础操作等。今天的学习告一段落，本文中有不对或不准确的地方，欢迎在评论区指定出来。