学习完了分组查询，我们本篇文章来学习子查询那些事，本篇文章整体脉络为：
一、我们遇到了什么新问题？
比方有这么一个需求：需要查询在student_info表中一班学生中成绩超过平均分的有哪些同学。
结合之前的学习，我们可以分析出SQL应该按照如下思路写：
-
第一步：从student_info表中，按照班级分组，筛选出班级是一班的分组信息并计算出该班的平均分；
-
第二步：再次从student_info表中查出一班学习成绩中大于平均分的学生列表；
第一步的SQL应当这么写：
mysql> select avg(score) from student_info where class = '一班' group by class;
+------------+
| avg(score) |
+------------+
| 71.2500 |
+------------+
1 row in set (0.01 sec)
计算出一班的平均分数为71.25，下面我们就可以轻易地筛选出一班中分数超过71.25的学生信息：
mysql> select * from student_info where score > 71.25 and class = '一班';
+----+------+-------+--------+-----------+
| id | name | score | class | campus |
+----+------+-------+--------+-----------+
| 1 | stu1 | 100 | 一班 | 南校区 |
| 2 | stu2 | 85 | 一班 | 南校区 |
+----+------+-------+--------+-----------+
2 rows in set (0.01 sec)有没有办法只用一条SQL来实现如上需求呢？可能第一反应是这么写：
mysql> select * from student_info where class = '一班' and score > avg(score);
ERROR 1111 (HY000): Invalid use of group function报错说聚集函数不可用，错误的原因比较容易理解，where条件中不能包含聚集函数，因为WHERE子句是以记录为单位来执行过滤操作的，而不能按照分组过滤，这一点在上篇文章中也进行过说明，这里不再赘述，那么到底应该怎么用一个SQL来实现如上需求呢？
二、引入子查询
我们仔细分析刚才的两句SQL，其实可以看到，第二条查询语句的搜索条件其实是用到了第一条查询语句的查询结果。
所以能不能在一个SQL中同时写两条select呢？前面的select中where条件的值来自后面select的查询结果，我们可以写为如下：
mysql> select * from student_info where class = '一班'
and score > (select avg(score) from student_info where class = '一班' group by class);
+----+------+-------+--------+-----------+
| id | name | score | class | campus |
+----+------+-------+--------+-----------+
| 1 | stu1 | 100 | 一班 | 南校区 |
| 2 | stu2 | 85 | 一班 | 南校区 |
+----+------+-------+--------+-----------+
2 rows in set (0.00 sec)这一句可以执行成功，并且真的拿到了我们希望的结果，注意，我们把第二条查询语句用小括号()扩起来作为一个操作数放到了第一条的搜索条件处，这样就起到了合并两条查询语句的作用，小括号中的查询语句也被称为子查询或者内层查询。
这就是子查询的概念和基本用法。
三、继续深入子查询
截止到目前为止我们介绍的查询语句都是作用于单个表的，但是有时候会有从多个表中查询数据的需求。
我们重新设计一个场景，一张表管理用户，一张表管理用户的收货地址信息，显然，一个用户可能会设置多个收获地址，用户地址表中会关联用户id，我们就可以根据用户id找到该用户下的收货地址列表信息，从而实现查看收获地址这个小功能。
/*用户信息表，这里假设业务逻辑上已确保用户名唯一不重复*/
CREATE TABLE `users` (
`id` varchar(64) NOT NULL COMMENT '主键id 用户id',
`username` varchar(32) NOT NULL COMMENT '用户名 用户名',
`password` varchar(64) NOT NULL COMMENT '密码 密码',
`realname` varchar(128) DEFAULT NULL COMMENT '真实姓名',
`face` varchar(1024) NOT NULL COMMENT '头像',
`mobile` varchar(32) DEFAULT NULL COMMENT '手机号 手机号',
`email` varchar(32) DEFAULT NULL COMMENT '邮箱地址 邮箱地址',
`sex` tinyint(11) DEFAULT NULL COMMENT '性别 性别 1:男 0:女 2:保密',
`birthday` date DEFAULT NULL COMMENT '生日 生日',
`created_time` datetime NOT NULL COMMENT '创建时间 创建时间',
`updated_time` datetime NOT NULL COMMENT '更新时间 更新时间',
PRIMARY KEY (`id`),
KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表 ';
/*用户收货地址信息表*/
CREATE TABLE `user_address` (
`id` varchar(64) NOT NULL COMMENT '地址主键id',
`user_id` varchar(64) NOT NULL COMMENT '关联用户id',
`receiver` varchar(32) NOT NULL COMMENT '收件人姓名',
`mobile` varchar(32) NOT NULL COMMENT '收件人手机号',
`province` varchar(32) NOT NULL COMMENT '省份',
`city` varchar(32) NOT NULL COMMENT '城市',
`district` varchar(32) NOT NULL COMMENT '区县',
`detail` varchar(128) NOT NULL COMMENT '详细地址',
`extand` varchar(128) DEFAULT NULL COMMENT '扩展字段',
`is_default` int(11) DEFAULT NULL COMMENT '是否默认地址',
`created_time` datetime NOT NULL COMMENT '创建时间',
`updated_time` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户地址表 ';我们准备一些数据，为下面的学习做准备：
INSERT INTO `users` (`id`, `username`, `password`, `realname`, `face`, `mobile`, `email`, `sex`, `birthday`, `created_time`, `updated_time`)
VALUES
('190815GTKCBSS7MW','fossi','Qpf0SxOVUjUkWySXOZ16kw==',NULL,'http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png',NULL,NULL,2,'1900-01-01','2022-08-15 22:11:58','2022-08-15 22:11:58'),
('190816HH9RDPD6Y8','我去个地方','Qpf0SxOVUjUkWySXOZ16kw==',NULL,'http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png',NULL,NULL,2,'1900-01-01','2022-08-16 23:14:12','2022-08-16 23:14:12'),
('1908189H7TNWDTXP','alite','Qpf0SxOVUjUkWySXOZ16kw==',NULL,'http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png',NULL,NULL,2,'1900-01-01','2022-08-18 13:25:30','2022-08-18 13:25:30'),
('200425CN4W2HZFK4','yummy','4QrcOUm6Wau+VuBX8g+IPg==',NULL,'http://bloghello.oursnail.cn/avatar.png',NULL,NULL,2,'1900-01-01','2022-04-25 17:46:25','2022-04-25 17:46:25');
INSERT INTO `user_address` (`id`, `user_id`, `receiver`, `mobile`, `province`, `city`, `district`, `detail`, `extand`, `is_default`, `created_time`, `updated_time`)
VALUES
('190825CG3AA14Y3C','190815GTKCBSS7MW','fossi','13333333333','北京','北京','东城区','幸福小区12号街',NULL,1,'2022-08-25 17:34:14','2022-08-25 17:34:14'),
('190825CG4ZCSSWM8','190816HH9RDPD6Y8','我去个地方','13666666666','北京','北京','海淀区','阳光里街道',NULL,1,'2022-08-25 17:34:24','2022-08-25 17:34:24'),
('20032900B7TGDRKP','1908189H7TNWDTXP','alite','18118888652','江苏','南京','浦口区','江浦街道',NULL,1,'2022-03-29 00:01:13','2022-03-29 00:05:40'),
('200329023962Y5AW','200425CN4W2HZFK4','yummy','18118777652','江苏','南京','玄武区','洪武路',NULL,1,'2022-03-29 00:06:22','2022-03-29 00:20:01'),
('2006148CRWB3FMCH','190815GTKCBSS7MW','fossi3','15895967012','江苏省','南京','浦口区','南京市浦口区江浦街道',NULL,0,'2022-06-14 11:50:56','2022-06-14 11:50:56');好了，下面进入正题。
第一个话题：有时，返回的结果并不是一个单独的值，可能是一个列表，通过之前的学习，我们知道，IN和NOT IN操作符正好是用来匹配列表的。
下面我们提一个需求，假设我们需要获取用户为fossi和yummy的收货地址列表，最终结果只需要展示：用户ID、用户名、收获地址信息、收获人姓名和收货人手机号码这些关键信息。
显然，我们需要先从users表中找到用户名叫做fossi和yummy的人，由于我们业务上要求用户名唯一，所以如果能查到，必然可以获取到两个用户id，我们再拿这两个用户id去user_address查询对应的信息，我们可以先分开写：
第一步，查询用户名叫做fossi的用户id：
mysql> select id from users where username in('fossi','yummy');
+------------------+
| id |
+------------------+
| 190815GTKCBSS7MW |
| 200425CN4W2HZFK4 |
+------------------+
2 rows in set (0.01 sec)获取到用户id为190815GTKCBSS7MW，我们再来查询收货地址列表：
mysql> select user_id as '用户id',receiver as '收货人',mobile as '手机号码',concat("省份：",province,"，城市：",city,"，区县：",district,"，详细地址：",detail) as '收货地址' from user_address where user_id in ('190815GTKCBSS7MW','200425CN4W2HZFK4');
+------------------+-----------+--------------+-----------------------------------------------------------------------------------------------------------+
| 用户id | 收货人 | 手机号码 | 收货地址 |
+------------------+-----------+--------------+-----------------------------------------------------------------------------------------------------------+
| 190815GTKCBSS7MW | fossi | 13333333333 | 省份：北京，城市：北京，区县：东城区，详细地址：幸福小区12号街 |
| 200425CN4W2HZFK4 | yummy | 18118777652 | 省份：江苏，城市：南京，区县：玄武区，详细地址：洪武路 |
| 190815GTKCBSS7MW | fossi3 | 15895967012 | 省份：江苏省，城市：南京，区县：浦口区，详细地址：南京市浦口区江浦街道 |
+------------------+-----------+--------------+-----------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)接下来使用子查询将其组合起来即可：
mysql> select user_id as '用户id',receiver as '收货人',mobile as '手机号码',concat("省份：",province,"，城市：",city,"，区县：",district,"，详细地址：",detail) as '收货地址' from user_address where user_id in (select id from users where username in('fossi','yummy'));
+------------------+-----------+--------------+-----------------------------------------------------------------------------------------------------------+
| 用户id | 收货人 | 手机号码 | 收货地址 |
+------------------+-----------+--------------+-----------------------------------------------------------------------------------------------------------+
| 190815GTKCBSS7MW | fossi | 13333333333 | 省份：北京，城市：北京，区县：东城区，详细地址：幸福小区12号街 |
| 200425CN4W2HZFK4 | yummy | 18118777652 | 省份：江苏，城市：南京，区县：玄武区，详细地址：洪武路 |
| 190815GTKCBSS7MW | fossi3 | 15895967012 | 省份：江苏省，城市：南京，区县：浦口区，详细地址：南京市浦口区江浦街道 |
+------------------+-----------+--------------+-----------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)第二个话题：上面返回的结果中只有一个字段，那么有没有可能一个结果有多个字段呢？
我们写一个sql：
mysql> select user_id as '用户id',receiver as '收货人',mobile as '手机号码',concat("省份：",province,"，城市：",city,"，区县：",district,"，详细地址：",detail) as '收货地址' from user_address where (user_id,receiver) in (select id,username from users);
+------------------+-----------------+--------------+----------------------------------------------------------------------------------------------+
| 用户id | 收货人 | 手机号码 | 收货地址 |
+------------------+-----------------+--------------+----------------------------------------------------------------------------------------------+
| 190815GTKCBSS7MW | fossi | 13333333333 | 省份：北京，城市：北京，区县：东城区，详细地址：幸福小区12号街 |
| 190816HH9RDPD6Y8 | 我去个地方 | 13666666666 | 省份：北京，城市：北京，区县：海淀区，详细地址：阳光里街道 |
| 1908189H7TNWDTXP | alite | 18118888652 | 省份：江苏，城市：南京，区县：浦口区，详细地址：江浦街道 |
| 200425CN4W2HZFK4 | yummy | 18118777652 | 省份：江苏，城市：南京，区县：玄武区，详细地址：洪武路 |
+------------------+-----------------+--------------+----------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)可以看到，我们是拿user_address表中user_id和receiver两个字段去users表中匹配，如果user_address中user_id等于users中的id，并且user_address中receiver等于users中的username，那么该记录加入结果集。
第三个话题：有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集，这时可以用到下边这两个操作符：
| 操作符 | 示例 | 描述 |
|---|---|---|
| EXISTS | EXISTS (SELECT …) | 当子查询结果集不是空集时表达式为真 |
| NOT EXISTS | NOT EXISTS (SELECT …) | 当子查询结果集是空集时表达式为真 |
我们举个例子：
mysql> select * from user_address where exists (select * from users where username = 'mysql');
Empty set (0.01 sec)这里的子查询的意思是从users表中查询username等于mysql的用户，很显然并没有这条记录，所以子查询的结果集是一个空集，于是EXISTS表达式的结果为FALSE，所以外层查询也就不查了，直接返回了一个Empty set，表示没有结果。
第四个话题：最后说一说不相关子查询和相关子查询。
前边介绍的子查询和外层查询都没有依赖关系，也就是说子查询可以独立运行并产生结果之后，再拿结果作为外层查询的条件去执行外层查询，这种子查询称为不相关子查询。比如我们上面所演示的，先从user表中查询出来用户id，再根据用户id去user_address表中查询收货地址信息，子查询语句中查询用户id并不会使用到user_address表，我们认为称为不相关子查询。
而有时候我们需要在子查询的语句中引用到外层查询的值，这样的话子查询就不能当作一个独立的语句去执行，这种子查询被称为相关子查询。
举个例子，比方说我们需要查询用户一些基本信息，但是前提是他要有对应的收货地址信息，我们才认为是个有效用户。
mysql> select id,username from users where exists (select * from user_address where users.id = user_address.user_id);
+------------------+-----------------+
| id | username |
+------------------+-----------------+
| 1908189H7TNWDTXP | alite |
| 190815GTKCBSS7MW | fossi |
| 200425CN4W2HZFK4 | yummy |
| 190816HH9RDPD6Y8 | 我去个地方 |
+------------------+-----------------+
4 rows in set (0.01 sec)这条查询语句可以分成这么两部分来理解
-
我们要查询用户表users的一些基本信息。
-
这些用户必须符合这样的条件：必须有相应的用户收货地址记录保存在user_address表中。
sql查询的过程是这样的：
-
首先执行外层查询，查询users表的第一条记录，发现它的id为1908189H7TNWDTXP，把1908189H7TNWDTXP作为参数传入子查询，子查询查询user_address中的user_id是否有1908189H7TNWDTXP这条记录，若有，则整个EXISTS表达式的值为TRUE，那么users表的第一条记录可以被加入到结果集。
-
继续查询users表的第二条记录，按照上面的步骤判断即可，直到判断到最后一条记录。
四、总结
子查询是非常常用的一类查询，我们必须完全掌握其用法，有一些重点需要注意：
-
为什么要使用子查询，子查询的概念和写法？
-
子查询返回的结果集是否支持一条记录中同时有多个字段？
-
有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集，此时如何做？
-
不相关子查询和相关子查询的概念、区别和SQL执行流程？
原文始发于微信公众号（幕后哈土奇）：13｜第十三话：基础篇-MySQL之子查询
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/112820.html
