Mysql数据库-子查询

14. Mysql数据库-子查询

1. 什么是子查询

---

# 子查询定义
 ## 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

下面我们来举个简单的例子：

-- 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. 子查询分类

在上面的执行示例中，我们已经了解到了什么是子查询。那么子查询可以按照查询返回的不同结果，进行一些简单的分类，如下：

#按结果集的行列数不同
    1. 标量子查询: 返回的结果是一个数据(单行单列)
    2. 列子查询: 返回的结果是一列(多行单列)
    3. 行子查询: 返回的结果是一行(单行多列)
    4. 表子查询: 返回的结果是一张表(多行多列)

当然也有按照子查询出现的位置进行区分：

#按子查询出现的位置
 1. select 后面: (少见)
  a. 仅支持标量子查询
   子查询的结果直接出现在结果集中
 2. from 后面:(有用)
  a. 支持表子查询
 3. where或having后面: (重要)
     a. 标量子查询(单行单列)  常见
     b. 列子查询(多行单列)  常见
     c. 行子查询
    4. exists后面(相关子查询: 有用)
     都支持, 一般是表子查询

最后还有按照关联性区分：

#按关联性分(扩展)
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之后

# where或having之后, 可以跟的子查询类型
1. 标量子查询（一个数据） 常见
2. 列子查询（一列） 常见

3. 行子查询（一行） 少见

# 特点：
1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 使用注意点
 a. 标量子查询，一般搭配着单行操作符使用
  > < >= <= = <>
 b. 列子查询，一般搭配着多行操作符使用
  in、any/some、all

下面我们来写一下示例。

3.1 标量子查询(单行单列)

#标量子查询(单行单列)
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);

执行如下：

-- 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 列子查询(多行单列)

#列子查询(多行单列)
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 ('开发部','财务部'));

执行如下：

-- 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之后

# 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之后

# 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;

执行如下：

-- 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 的基本语法格式：

# 语法: 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() 的基本用法。那么下面我们来具体查询一下：

# 查询工资大于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 的区别：

-- exists和in的区别
 -- in 后面一般直接跟 非相关子查询 (子查询执行完毕,再执行主查询)
 -- exists 后面一般都要跟 相关子查询(主查询查询一条,子查询执行一次)
-- exists和in的效率,哪个高? 视情况而定
 -- 其他条件相同(有索引)
 -- 1. 主查询的结果集数量 比 子查询的多 用 in
 -- 2. 主查询的结果集数量 比 子查询少 用 exists