select 高级查询之子查询

╰+攻爆jí腚メ 2023-02-22 10:42 224阅读 0赞

![在这里插入图片描述][20200702221315811.jpg]

## 1.1 概述 ##

在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的 WHERE 字句或 HAVING 短语的条件中查询块称为子查询或内层查询。外部的 SELECT 查询语句，称为主查询或外查询。按结果集的行列数不同子查询可以分为 4 种  
 ♞ 标量子查询(结果集只有一行一列)  
 ♞ 列子查询(结果集只有一列多行)  
 ♞ 行子查询(结果集有一行多列)  
 ♞ 表子查询(结果集一般为多行多列)

## 1.2 数据准备 ##

# 员工表
    mysql> select * from emp;
    +------+--------+--------+------+------------+----------+----------+---------+
    | id   | ename  | job_id | mgr  | joindate   | salary   | bonus    | dept_id |
    +------+--------+--------+------+------------+----------+----------+---------+
    | 1001 | 孙悟空 |      4 | 1004 | 2000-12-17 | 8000.00  | NULL     |      20 |
    | 1002 | 卢俊义 |      3 | 1006 | 2001-02-20 | 16000.00 | 3000.00  |      30 |
    | 1003 | 林冲   |      3 | 1006 | 2001-02-22 | 12500.00 | 5000.00  |      30 |
    | 1004 | 唐僧   |      2 | 1009 | 2001-04-02 | 29750.00 | NULL     |      20 |
    | 1005 | 李逵   |      4 | 1006 | 2001-09-28 | 12500.00 | 14000.00 |      30 |
    | 1006 | 宋江   |      2 | 1009 | 2001-05-01 | 28500.00 | NULL     |      30 |
    | 1007 | 刘备   |      2 | 1009 | 2001-09-01 | 24500.00 | NULL     |      10 |
    | 1008 | 猪八戒 |      4 | 1004 | 2007-04-19 | 30000.00 | NULL     |      20 |
    | 1009 | 罗贯中 |      1 | NULL | 2001-11-17 | 50000.00 | NULL     |      10 |
    | 1010 | 吴用   |      3 | 1006 | 2001-09-08 | 15000.00 | 0.00     |      30 |
    | 1011 | 沙僧   |      4 | 1004 | 2007-05-23 | 11000.00 | NULL     |      20 |
    | 1012 | 李逵   |      4 | 1006 | 2001-12-03 | 9500.00  | NULL     |      30 |
    | 1013 | 小白龙 |      4 | 1004 | 2001-12-03 | 30000.00 | NULL     |      20 |
    | 1014 | 关羽   |      4 | 1007 | 2002-01-23 | 13000.00 | NULL     |      10 |
    +------+--------+--------+------+------------+----------+----------+---------+
    14 rows in set (0.15 sec)
    
    # 部门信息表
    mysql> select * from dept;
    +----+--------+------+
    | id | dname  | loc  |
    +----+--------+------+
    | 10 | 教研部 | 北京 |
    | 20 | 学工部 | 上海 |
    | 30 | 销售部 | 广州 |
    | 40 | 财务部 | 深圳 |
    +----+--------+------+
    4 rows in set (0.15 sec)
    
    # 工作职责表
    mysql> select * from job;
    +----+--------+--------------------+
    | id | jname  | description        |
    +----+--------+--------------------+
    |  1 | 董事长 | 管理整个公司，接单 |
    |  2 | 经理   | 管理部门员工       |
    |  3 | 销售员 | 向客人推销产品     |
    |  4 | 文员   | 使用办公软件       |
    +----+--------+--------------------+
    4 rows in set (0.16 sec)
    
    # 薪资等级表
    mysql> select * from salarygrade;
    +-------+----------+----------+
    | grade | losalary | hisalary |
    +-------+----------+----------+
    |     1 |     7000 |    12000 |
    |     2 |    12010 |    14000 |
    |     3 |    14010 |    20000 |
    |     4 |    20010 |    30000 |
    |     5 |    30010 |    99990 |
    +-------+----------+----------+
    5 rows in set (0.14 sec)

## 1.3 select 后的子查询 ##

### 1.3.1 查询每个部门员工个数 ###

mysql> select dept.*, 
    	-> ( select count(*) from emp where emp.dept_id = dept.id ) as 员工个数
    	-> from dept;
    +----+--------+------+----------+
    | id | dname  | loc  | 员工个数 |
    +----+--------+------+----------+
    | 10 | 教研部 | 北京 |        3 |
    | 20 | 学工部 | 上海 |        5 |
    | 30 | 销售部 | 广州 |        6 |
    | 40 | 财务部 | 深圳 |        0 |
    +----+--------+------+----------+
    4 rows in set (2.02 sec)

### 1.3.2 查询 1002 号员工的姓名和部门名称 ###

mysql> select ename as 姓名, 
    	-> (select dname from dept where emp.dept_id = dept.id) as 部门名 
    	-> from emp 
    	-> where id = 1002;
    +--------+--------+
    | 姓名   | 部门名 |
    +--------+--------+
    | 卢俊义 | 销售部 |
    +--------+--------+
    1 row in set (0.15 sec)

## 1.4 from 后的子查询 ##

### 1.4.1 查询每个部门的平均工资 ###

mysql> select avg(salary) as avg, dept_id from emp group by dept_id;
    +--------------+---------+
    | avg          | dept_id |
    +--------------+---------+
    | 29166.666667 |      10 |
    | 21750.000000 |      20 |
    | 15666.666667 |      30 |
    +--------------+---------+
    3 rows in set (0.11 sec)
    
    
    mysql> select dept.dname as 部门, t.avg as 平均工资 from 
    	-> ( select avg(salary) as avg, dept_id from emp group by dept_id ) as t, dept 
    	-> where t.dept_id = dept.id;
    +--------+--------------+
    | 部门   | 平均工资     |
    +--------+--------------+
    | 教研部 | 29166.666667 |
    | 学工部 | 21750.000000 |
    | 销售部 | 15666.666667 |
    +--------+--------------+
    3 rows in set (0.18 sec)

## 1.5 where 后的子查询 ##

### 1.5.1 查询工资高于 30 号部门平均工资的员工信息 ###

mysql> select avg(salary) from emp where dept_id = 30;
    +--------------+
    | avg(salary)  |
    +--------------+
    | 15666.666667 |
    +--------------+
    1 row in set (0.10 sec)
    
    
    mysql> select * from emp where ( select avg(salary) from emp where dept_id = 30 ) < salary;
    +------+--------+--------+------+------------+----------+---------+---------+
    | id   | ename  | job_id | mgr  | joindate   | salary   | bonus   | dept_id |
    +------+--------+--------+------+------------+----------+---------+---------+
    | 1002 | 卢俊义 |      3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 |      30 |
    | 1004 | 唐僧   |      2 | 1009 | 2001-04-02 | 29750.00 | NULL    |      20 |
    | 1006 | 宋江   |      2 | 1009 | 2001-05-01 | 28500.00 | NULL    |      30 |
    | 1007 | 刘备   |      2 | 1009 | 2001-09-01 | 24500.00 | NULL    |      10 |
    | 1008 | 猪八戒 |      4 | 1004 | 2007-04-19 | 30000.00 | NULL    |      20 |
    | 1009 | 罗贯中 |      1 | NULL | 2001-11-17 | 50000.00 | NULL    |      10 |
    | 1013 | 小白龙 |      4 | 1004 | 2001-12-03 | 30000.00 | NULL    |      20 |
    +------+--------+--------+------+------------+----------+---------+---------+
    7 rows in set (0.16 sec)

### 1.5.2 查询有工资高于 29000 的部门 ###

mysql> select dept_id from emp where salary > 29000 group by dept_id;
    +---------+
    | dept_id |
    +---------+
    |      10 |
    |      20 |
    +---------+
    2 rows in set (0.11 sec)
    
    
    mysql> select dname as 部门名 
    	-> from dept 
    	-> where id in (select dept_id from emp where salary > 29000 group by dept_id);
    +--------+
    | 部门名 |
    +--------+
    | 学工部 |
    | 教研部 |
    +--------+
    2 rows in set (0.14 sec)

### 1.5.3 查询部门号最小，工资最高的员工信息 ###

mysql> select min(dept_id), max(salary) from emp;
    +--------------+-------------+
    | min(dept_id) | max(salary) |
    +--------------+-------------+
    |           10 | 50000.00    |
    +--------------+-------------+
    1 row in set (0.11 sec)
    
    
    mysql> select * from emp where (dept_id, salary) = (select min(dept_id), max(salary) from emp);
    +------+--------+--------+------+------------+----------+-------+---------+
    | id   | ename  | job_id | mgr  | joindate   | salary   | bonus | dept_id |
    +------+--------+--------+------+------------+----------+-------+---------+
    | 1009 | 罗贯中 |      1 | NULL | 2001-11-17 | 50000.00 | NULL  |      10 |
    +------+--------+--------+------+------------+----------+-------+---------+
    1 row in set (19.54 sec)

## 1.6 注意 ##

① 子查询放在小括号内，且一般放在条件的右侧。  
 ② 标量子查询，一般搭配着单行操作符使用，列子查询，一般搭配着多行操作符使用。  
 ③ 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。  
 ④ select 后面的子查询，仅仅支持标量子查询，即结果集只有一行一列。  
 ⑤ from 后面的子查询是将子查询的结果集充当一张临时表，要求必须起别名，否者这个表找不到。然后将真实的表和子查询结果表进行连接查询。  
 ⑥ where 或 having 后面可以有：标量子查询、 列子查询(一般搭配着多行操作符使用 in、any、some、all 等)、行子查询。

--------------------

关注公众号一起学习 Java 开发，跟大家分享我的学习成长过程

![在这里插入图片描述][2020060821282046.jpg_pic_center]

[20200702221315811.jpg]: https://img-blog.csdnimg.cn/20200702221315811.jpg
[2020060821282046.jpg_pic_center]: https://img-blog.csdnimg.cn/2020060821282046.jpg#pic_center