14. Mysql数据库-子查询
1. 什么是子查询
代码语言：javascript复制# 子查询定义
## B语句作为A语句的一部分,B语句是select查询语句,那么B语句称之为子查询,内层查询(子集,subquery)
-- 1. A语句可以是select ,update,delete等语句,其中最常见的是select语句
-- 2. 如果A语句也是select语句, 称之为主查询,外层查询(main query)
-- 3. B语句可以写在 select,from,where/having,exists 后面,其中最常见是where
下面我们来举个简单的例子：
代码语言：javascript复制-- 1. 首先我们需要有准备好的数据emp表，下面来查询一下这张表中：工资salary最高的员工
-- 对于这种需求，我们一般是分为两个步骤来进行查询的：首先查询emp表中的最高工资salary，然后基于最高工资salary再去查询该员工
mysql> select * from emp;
---- ----------- -------- -------- ------------ ---------
| id | name | gender | salary | join_date | dept_id |
---- ----------- -------- -------- ------------ ---------
| 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 |
| 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 |
| 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 |
| 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 |
| 6 | 沙僧 | 男 | 6666 | 2013-02-24 | NULL |
---- ----------- -------- -------- ------------ ---------
6 rows in set (0.00 sec)
-- 2. 查询emp表的最高工资salary，然后再根据最高工资salary查询员工的信息
-- 2.1 通过max(salary)可以查询出emp表的最高工资为9000
mysql> select max(salary) from emp;
-------------
| max(salary) |
-------------
| 9000 |
-------------
1 row in set (0.00 sec)
-- 2.2 根据salary=9000的条件，再查询出员工的信息。
mysql> select name from emp where salary = 9000;
--------
| name |
--------
| 唐僧 |
--------
1 row in set (0.00 sec)
mysql>
-- 3. 在上面的操作中，虽然查询出了结果。但是却分开了两个SQL语句来执行，这就导致效率不高的问题了。
-- 那么怎么解决呢？ 可以使用子查询。
-- 可以将 select max(salary) from emp 作为一条子查询语句，然后直接进行条件判断即可。示例如下：
mysql> select name from emp where salary = (select max(salary) from emp);
--------
| name |
--------
| 唐僧 |
--------
1 row in set (0.00 sec)
-- 可以看到通过子查询，就不需要拆分SQL来查询了。
2. 子查询分类
在上面的执行示例中，我们已经了解到了什么是子查询。那么子查询可以按照查询返回的不同结果，进行一些简单的分类，如下：
代码语言：javascript复制#按结果集的行列数不同
1. 标量子查询: 返回的结果是一个数据(单行单列)
2. 列子查询: 返回的结果是一列(多行单列)
3. 行子查询: 返回的结果是一行(单行多列)
4. 表子查询: 返回的结果是一张表(多行多列)
当然也有按照子查询出现的位置进行区分：
代码语言：javascript复制#按子查询出现的位置
1. select 后面: (少见)
a. 仅支持标量子查询
子查询的结果直接出现在结果集中
2. from 后面:(有用)
a. 支持表子查询
3. where或having后面: (重要)
a. 标量子查询(单行单列) 常见
b. 列子查询(多行单列) 常见
c. 行子查询
4. exists后面(相关子查询: 有用)
都支持, 一般是表子查询
最后还有按照关联性区分：
代码语言：javascript复制#按关联性分(扩展)
1. 非相关子查询
a. 含义: 独立于外部查询的子查询 (子查询可以独立运行)
b. 执行: 子查询的执行优先于主查询执行,并且只执行一次，执行完将结果传递给外部查询
c. 效率: 较高
举例: select * from A where A.id in （select id from B）
2. 相关子查询
a. 含义: 依赖于外部查询的数据的子查询
b. 执行: 子查询的执行的次数依赖于外部查询，外部查询每执行一行，子查询执行一次
解释: 子查询中查询条件依赖于外层查询中的某个值，所以子查询的处理不只一次，要反复求值，以供外层查询使用. 所以相关子查询执行时, 子查询的执行和外层查询的执行是相互交叉的.
c. 效率: 较低
举例: select * from emp e1 where exists (select * from emp e2 where e1.empno=e2.mgr);
3. 子查询在 where或having之后
代码语言：javascript复制# where或having之后, 可以跟的子查询类型
1. 标量子查询（一个数据） 常见
2. 列子查询（一列） 常见
3. 行子查询（一行） 少见
# 特点：
1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 使用注意点
a. 标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
b. 列子查询，一般搭配着多行操作符使用
in、any/some、all
下面我们来写一下示例。
3.1 标量子查询(单行单列)
代码语言：javascript复制#标量子查询(单行单列)
1. 查询工资最高的员工是谁？
-- a. 求最高工资
-- b. 根据最高工资找出对应员工
select max(salary) from emp; -- 结果是9000
select * from emp where salary = 9000;
-- 通过子查询，将两个查询SQL二合一
select * from emp
where salary = (select max(salary) from emp);
2. 查询工资小于平均工资的员工有哪些？
-- a. 查询平均工资
-- b. 根据工资小于平均工资条件,查询员工信息
select avg(salary) from emp;
select * from emp
where salary < (select avg(salary) from emp);
3. 查询部门平均工资超过全公司平均工资的部门id和部门平均工资
-- a. 先查询公司平均工资
-- b. 再查询符合条件的部门id和对应的平均工资
select avg(salary) from emp;
select dept_id,avg(salary) from emp
group by dept_id
having avg(salary) > (select avg(salary) from emp);
执行如下：
代码语言：javascript复制-- 2. 查询工资小于平均工资的员工有哪些？
-- 2.1 首先执行一下平均工资的查询
mysql> select avg(salary) from emp;
-------------------
| avg(salary) |
-------------------
| 5994.333333333333 | -- 标量子查询(单行单列)
-------------------
1 row in set (0.00 sec)
-- 2.2 根据查询的平均工资，再查询员工信息
mysql> select * from emp
-> where salary < (select avg(salary) from emp);
---- ----------- -------- -------- ------------ ---------
| id | name | gender | salary | join_date | dept_id |
---- ----------- -------- -------- ------------ ---------
| 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 |
| 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 |
---- ----------- -------- -------- ------------ ---------
3 rows in set (0.00 sec)
mysql>
-- 3. 查询部门平均工资超过全公司平均工资的部门id和部门平均工资
-- 3.1 先查询公司平均工资
mysql> select avg(salary) from emp;
-------------------
| avg(salary) |
-------------------
| 5994.333333333333 |
-------------------
1 row in set (0.00 sec)
-- 3.2 再查询符合条件的部门id和对应的平均工资
-- 注意：聚合函数的条件必须放在 having 后进行条件处理，所以子查询也要放在 having 后处理
mysql> select dept_id,avg(salary) from emp
group by dept_id
having avg(salary) > (select avg(salary) from emp);
--------- -------------
| dept_id | avg(salary) |
--------- -------------
| NULL | 6666 |
| 2 | 6300 |
--------- -------------
2 rows in set (0.00 sec)
-- 3.3 最后将dept_id为null的数据去除
mysql> select dept_id,avg(salary) from emp
where dept_id is not null
group by dept_id
having avg(salary) > (select avg(salary) from emp);
--------- -------------
| dept_id | avg(salary) |
--------- -------------
| 2 | 6300 |
--------- -------------
1 row in set (0.00 sec)
mysql>
3.2 列子查询(多行单列)
代码语言：javascript复制#列子查询(多行单列)
1. 查询工资大于5000的员工，来自于哪些部门的名字
-- a. 查询部门,条件是id
-- b. 查询出来的id,要符合对应的员工,工资大于5000
select dept_id from emp where salary > 5000; -- 结果是1,2
select name from dept where id in (select dept_id from emp where salary > 5000);
-- 扩展 : 下面两种执行结果同上
select name from dept where id = any(select dept_id from emp where salary > 5000);
select name from dept where id = some(select dept_id from emp where salary > 5000);
2. 查询开发部与财务部所有的员工信息
-- a. 员工信息 emp表, 条件: dept_id
-- b. 开发部 财务部 id , dept表
select id from dept where name = '开发部' or name = '财务部';
select id from dept where name in ('开发部','财务部');
select * from emp where dept_id in(select id from dept where name in ('开发部','财务部'));
执行如下：
代码语言：javascript复制-- 1. 查询工资大于5000的员工，来自于哪些部门的名字
-- 1.1 首先查询salary>5000的部门ID，列子查询(多行单列)
mysql> select dept_id from emp where salary > 5000;
---------
| dept_id |
---------
| 1 |
| 2 |
| NULL |
---------
3 rows in set (0.00 sec)
-- 1.2 查询出来的部门id,再查询对应的部门名称
mysql> select name from dept where id in (select dept_id from emp where salary > 5000);
-----------
| name |
-----------
| 开发部 |
| 市场部 |
-----------
2 rows in set (0.00 sec)
-- 1.3 类似 in 方法，而 any 与 some 方法也可以达到同样的查询效果
mysql> select name from dept where id = any(select dept_id from emp where salary > 5000);
-----------
| name |
-----------
| 开发部 |
| 市场部 |
-----------
2 rows in set (0.00 sec)
mysql> select name from dept where id = some(select dept_id from emp where salary > 5000);
-----------
| name |
-----------
| 开发部 |
| 市场部 |
-----------
2 rows in set (0.00 sec)
-- 2. 查询开发部与财务部所有的员工信息
-- 2.1 查询出 开发部 与 财务部 的部门ID
mysql> select id from dept where name = '开发部' or name = '财务部';
----
| id |
----
| 1 |
| 3 |
----
2 rows in set (0.00 sec)
mysql> select id from dept where name in ('开发部','财务部');
----
| id |
----
| 1 |
| 3 |
----
2 rows in set (0.00 sec)
-- 2.2 根据部门ID来查询员工信息
mysql> select * from emp where dept_id in(select id from dept where name in ('开发部','财务部'));
---- ----------- -------- -------- ------------ ---------
| id | name | gender | salary | join_date | dept_id |
---- ----------- -------- -------- ------------ ---------
| 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 |
| 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 |
---- ----------- -------- -------- ------------ ---------
3 rows in set (0.00 sec)
4. 子查询在 select之后
代码语言：javascript复制# select之后, 可以跟的子查询类型
仅支持标量子查询(一个数据)，子查询的结果直接出现在结果集中
1. 查询每个部门的id,name和对应的员工个数
-- 方案一: 连接查询：通过部门连接查询，然后分组统计来查询员工的个数（常用解法）
select dept.*,count(dept_id) 个数
from dept inner join emp
on emp.dept_id = dept.id
group by emp.dept_id;
-- 执行如下：
mysql> select dept.*,count(dept_id) 个数 from dept inner join emp on emp.dept_id = dept.id group by emp.dept_id;
---- ----------- --------
| id | name | 个数 |
---- ----------- --------
| 1 | 开发部 | 2 |
| 2 | 市场部 | 2 |
| 3 | 财务部 | 1 |
---- ----------- --------
3 rows in set (0.00 sec)
mysql>
-- 方案二: 子查询：直接在select后增加一个统计员工个数的子查询来统计员工个数（了解就好）
-- 注意: 这个是相关子查询
-- 相关子查询 分析:
-- 1. 主查询: 3条结果
-- 2. 第一次执行:
-- 主查询先查出 id=1,name='开发部'
-- 子查询执行一次: 满足dept_id=1的两个员工,进行聚合
-- count(*) = 2
-- 3. 第二次执行 : 同理
-- 4. 第三次执行 : 同理
select dept.*,(
-- 我们可以发现这个子查询是无法单独执行的..因为这是子查询分类中的 【相关子查询：子查询的执行是依赖外部的查询，外部执行一行，子查询则会执行一次，导致效率低下。所以我们一般不会去用。】
select count(*)
from emp
where emp.dept_id = dept.id
) 个数
from dept;
-- 执行如下：
mysql> select dept.*,(select count(*) from emp where emp.dept_id = dept.id) 个数 from dept;
---- ----------- --------
| id | name | 个数 |
---- ----------- --------
| 1 | 开发部 | 2 |
| 2 | 市场部 | 2 |
| 3 | 财务部 | 1 |
| 4 | 销售部 | 0 |
---- ----------- --------
4 rows in set (0.00 sec)
mysql>
5. 子查询在 from之后
代码语言：javascript复制# from后面, 可以跟的子查询类型
支持表子查询(一张表)
# 特点
1. 子查询要求必须起别名(相当于把子查询的结果当成一张表,取个名字,方便被引用)
2. 若子查询中使用了聚合函数,必须取别名, 外部语句引用时会报错
1. 查询每个部门的id,name和对应的员工个数(需求同上,换种写法)
-- a. 从员工表按dept_id分组得到对应的员工个数
-- b. 把上一次查询结果当成一张表进行连接查询,得出结果
select dept_id,count(dept_id) as 人数 from emp group by dept_id;
-- 注意: 这里count(dept_id) 必须取别名
-- 如果不取别名, temp.count(dept_id) 这样的写法是错误的
select dept.*,temp.人数
from (select dept_id,count(dept_id) as 人数 from emp group by dept_id) as temp
inner join dept
on temp.dept_id = dept.id;
执行如下：
代码语言：javascript复制-- 1. 查询每个部门的id,name和对应的员工个数(需求同上,换种写法)
-- 1.1 从员工表按dept_id分组得到对应的员工个数
mysql> select dept_id,count(dept_id) as 人数 from emp group by dept_id;
--------- --------
| dept_id | 人数 |
--------- --------
| NULL | 0 |
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
--------- --------
4 rows in set (0.00 sec)
mysql> select dept_id,count(dept_id) as 人数 from emp where dept_id is not null group by dept_id;
--------- --------
| dept_id | 人数 |
--------- --------
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
--------- --------
3 rows in set (0.00 sec)
-- 1.2 把上一次查询结果当成一张表进行连接查询,得出结果
mysql> select dept.*,temp.人数 from
(select dept_id,count(dept_id) as 人数 from emp group by dept_id) as temp inner join dept
on temp.dept_id = dept.id;
---- ----------- --------
| id | name | 人数 |
---- ----------- --------
| 1 | 开发部 | 2 |
| 2 | 市场部 | 2 |
| 3 | 财务部 | 1 |
---- ----------- --------
3 rows in set (0.00 sec)
6. 子查询在 exists 之后
在熟悉 exists 的子查询使用之前，我们首先要了解一下 exists 的基本语法格式：
代码语言：javascript复制# 语法: exists(完整的查询语句)
# 特点: exists子查询 往往属于 相关子查询
# 结果：返回1或0 (true或false)
# 结果解释: 其实可以把exists看成一个if判断, 判断的是子查询是否有结果,有结果返回1,没有结果返回0
# 举例
-- 当前emp表的数据
mysql> select * from emp;
---- ----------- -------- -------- ------------ ---------
| id | name | gender | salary | join_date | dept_id |
---- ----------- -------- -------- ------------ ---------
| 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 |
| 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 |
| 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 |
| 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 |
| 6 | 沙僧 | 男 | 6666 | 2013-02-24 | NULL |
---- ----------- -------- -------- ------------ ---------
6 rows in set (0.00 sec)
mysql>
-- 子查询有结果返回1(相当于true)
select exists(select * from emp where salary > 1000);
-- 执行如下：可以从表的数据来看，存在salary>1000的数据，所以exists返回1
mysql> select exists(select * from emp where salary > 1000);
-----------------------------------------------
| exists(select * from emp where salary > 1000) |
-----------------------------------------------
| 1 |
-----------------------------------------------
1 row in set (0.00 sec)
mysql>
-- 子查询没有结果返回0(相当于false)
select exists(select * from emp where salary > 10000);
-- 执行如下：可以从结果看到，不存在salary>10000的数据，所以exists返回0
mysql> select exists(select * from emp where salary > 10000);
------------------------------------------------
| exists(select * from emp where salary > 10000) |
------------------------------------------------
| 0 |
------------------------------------------------
1 row in set (0.00 sec)
mysql>
-- 应用 : 这里的案例没有实际意义,就是让大家掌握语法和产生的结果
-- 1. 当条件为0的时候,不返回查询结果
select * from emp where 0;
-- 执行如下：
mysql> select * from emp where 0;
Empty set (0.00 sec)
-- 2. 那么这里的 0，我们可以使用 exists() 方法来替换，如下：
select * from emp where exists(select * from emp where salary > 10000);
-- 执行如下：
mysql> select * from emp where exists(select * from emp where salary > 10000);
Empty set (0.00 sec)
-- 3. 当条件为1的时候,返回查询结果
select * from emp where 1;
-- 执行如下：
mysql> select * from emp where 1;
---- ----------- -------- -------- ------------ ---------
| id | name | gender | salary | join_date | dept_id |
---- ----------- -------- -------- ------------ ---------
| 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 |
| 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 |
| 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 |
| 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 |
| 6 | 沙僧 | 男 | 6666 | 2013-02-24 | NULL |
---- ----------- -------- -------- ------------ ---------
6 rows in set (0.00 sec)
-- 4. 同上， where 条件后面的 1，我们也可以使用 exists() 方法来替换，如下：
select * from emp where exists(select * from emp where salary > 1000);
-- 执行如下：
mysql> select * from emp where exists(select * from emp where salary > 1000);
---- ----------- -------- -------- ------------ ---------
| id | name | gender | salary | join_date | dept_id |
---- ----------- -------- -------- ------------ ---------
| 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 |
| 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 |
| 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 |
| 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 |
| 6 | 沙僧 | 男 | 6666 | 2013-02-24 | NULL |
---- ----------- -------- -------- ------------ ---------
6 rows in set (0.00 sec)
好了，在上面的操作中，我们已经大概理解了 exists() 的基本用法。那么下面我们来具体查询一下：
代码语言：javascript复制# 查询工资大于5000的员工，来自于哪些部门的名字
-- 用in关键字
-- a. 查询emp 满足 salary > 5000的dept_id
-- b. 根据dept_id,查询dept
select dept_id from emp where salary > 5000; -- 1,2
select name from dept where id in (select dept_id from emp where salary > 5000);
-- 执行如下：
-- a.查询emp 满足 salary > 5000的dept_id
mysql> select dept_id from emp where salary > 5000 and dept_id is not null;
---------
| dept_id |
---------
| 1 |
| 2 |
---------
2 rows in set (0.00 sec)
-- b. 根据dept_id,使用in方法查询dept
mysql> select name from dept where id in (select dept_id from emp where salary > 5000 and dept_id is not null);
-----------
| name |
-----------
| 开发部 |
| 市场部 |
-----------
2 rows in set (0.00 sec)
-- 用exists
-- 执行顺序:
-- 主查询结果: 1,2,3
-- 第一次执行:
-- a. 先执行主查询: id=1 name=开发部
-- b. 接着执行子查询: 满足条件的数据不为null,返回1
-- c. 子查询返回1,主查询就保留当前行记录
-- 第二次执行: 也是返回1,保留 市场部
-- 第三次执行: 返回0,不保留 财务部
mysql> select name from dept d where exists( select * from emp e where e.salary > 5000 and e.dept_id = d.id);
-----------
| name |
-----------
| 开发部 |
| 市场部 |
-----------
2 rows in set (0.00 sec)
mysql>
-- 从上面的两个执行结果中，我们已经知道了 exists 与 in 都可以实现查询结果，那么 exists 与 in 在操作上有什么区别呢？
exists 与 in 的区别：
代码语言：javascript复制-- exists和in的区别
-- in 后面一般直接跟 非相关子查询 (子查询执行完毕,再执行主查询)
-- exists 后面一般都要跟 相关子查询(主查询查询一条,子查询执行一次)
-- exists和in的效率,哪个高? 视情况而定
-- 其他条件相同(有索引)
-- 1. 主查询的结果集数量 比 子查询的多 用 in
-- 2. 主查询的结果集数量 比 子查询少 用 exists
