常用SQL语句：子查询

子查询

• 子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式

子查询返回的数据分类

• 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
• 单行多列：返回一行数据中多个列的内容
• 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
• 多行多列：查询返回的结果是一张临时表

子查询常出现的位置

• select之后：仅支持单行单列
• from之后：支持多行多列
• where或having之后：支持单行单列、单行多列、多行单列

子查询实例

单行单列

• 查询运维部所有员工信息

  • 分析：

  • 首先从departments表中查出运维部的编号

    mysql> select dept_id from departments where dept_name='运维部';
    +---------+
    | dept_id |
    +---------+
    |       3 |
    +---------+
    1 row in set (0.00 sec)
    

  • 再从employees表中查找该部门编号和运维部编号相同的员工

    mysql> select *
        -> from employees
        -> where dept_id=(
        ->   select dept_id from departments where dept_name='运维部'
        -> );
    

• 查询2018年12月所有比100号员工基本工资高的工资信息

  • 分析：

  • 首先查到2018年12月100号员工的基本工资

    mysql> select basic from salary
        -> where year(date)=2018 and month(date)=12 and employee_id=100;
    +-------+
    | basic |
    +-------+
    | 14585 |
    +-------+
    1 row in set (0.00 sec)
    

  • 再查询2018年12月所有比100号员工基本工资高的工资信息

    mysql> select * from salary
        -> where year(date)=2018 and month(date)=12 and basic>(
        ->   select basic from salary
        ->   where year(date)=2018 and month(date)=12 and employee_id=100
        -> );
    

• 查询部门员工人数比开发部人数少的部门

  • 分析：

  • 查询开发部部门编号

    mysql> select dept_id from departments where dept_name='开发部';
    +---------+
    | dept_id |
    +---------+
    |       4 |
    +---------+
    1 row in set (0.00 sec)
    

  • 查询开发部人数

    mysql> select count(*) from employees
        -> where dept_id=(
        ->   select dept_id from departments where dept_name='开发部'
        -> );
    +----------+
    | count(*) |
    +----------+
    |       55 |
    +----------+
    1 row in set (0.00 sec)
    

  • 分组查询各部门人数

    mysql> select count(*), dept_id from employees group by dept_id;
    +----------+---------+
    | count(*) | dept_id |
    +----------+---------+
    |        8 |       1 |
    |        5 |       2 |
    |        6 |       3 |
    |       55 |       4 |
    |       12 |       5 |
    |        9 |       6 |
    |       35 |       7 |
    |        3 |       8 |
    +----------+---------+
    8 rows in set (0.01 sec)
    

  • 查询部门员工人数比开发部人数少的部门

    mysql> select count(*), dept_id from employees group by dept_id
        -> having count(*)<(
        ->   select count(*) from employees
        ->   where dept_id=(
        ->     select dept_id from departments where dept_name='开发部'
        ->   )
        -> );
    +----------+---------+
    | count(*) | dept_id |
    +----------+---------+
    |        8 |       1 |
    |        5 |       2 |
    |        6 |       3 |
    |       12 |       5 |
    |        9 |       6 |
    |       35 |       7 |
    |        3 |       8 |
    +----------+---------+
    7 rows in set (0.00 sec)
    

• 查询每个部门的人数

  • 分析：

  • 查询所有部门的信息

    mysql> select d.* from departments as d;
    +---------+-----------+
    | dept_id | dept_name |
    +---------+-----------+
    |       1 | 人事部    |
    |       2 | 财务部    |
    |       3 | 运维部    |
    |       4 | 开发部    |
    |       5 | 测试部    |
    |       6 | 市场部    |
    |       7 | 销售部    |
    |       8 | 法务部    |
    |       9 | NULL      |
    +---------+-----------+
    9 rows in set (0.00 sec)
    

  • 查询每个部门的人数

    mysql> select d.*, (
        ->  select count(*) from employees as e
        ->   where d.dept_id=e.dept_id
        -> ) as amount
        -> from departments as d;
    +---------+-----------+--------+
    | dept_id | dept_name | amount |
    +---------+-----------+--------+
    |       1 | 人事部    |      8 |
    |       2 | 财务部    |      5 |
    |       3 | 运维部    |      6 |
    |       4 | 开发部    |     55 |
    |       5 | 测试部    |     12 |
    |       6 | 市场部    |      9 |
    |       7 | 销售部    |     35 |
    |       8 | 法务部    |      3 |
    |       9 | NULL      |      0 |
    +---------+-----------+--------+
    9 rows in set (0.00 sec)
    

多行单列

• 查询人事部和财务部员工信息

  • 分析：

  • 查询人事部和财务部编号

    mysql> select dept_id from departments
        -> where dept_name in ('人事部', '财务部');
    +---------+
    | dept_id |
    +---------+
    |       1 |
    |       2 |
    +---------+
    2 rows in set (0.00 sec)
    

  • 查询部门编号是两个部门编号的员工信息

    mysql> select * from employees
        -> where dept_id in (
        ->   select dept_id from departments
        ->   where dept_name in ('人事部', '财务部')
        -> );
    

• 查询人事部2018年12月所有员工工资

  • 分析：

  • 查询人事部部门编号

    mysql> select dept_id from departments where dept_name='人事部';
    +---------+
    | dept_id |
    +---------+
    |       1 |
    +---------+
    1 row in set (0.00 sec)
    

  • 查询人事部员的编号

    mysql> select employee_id from employees
        -> where dept_id=(
        ->   select dept_id from departments where dept_name='人事部'
        -> );
    +-------------+
    | employee_id |
    +-------------+
    |           1 |
    |           2 |
    |           3 |
    |           4 |
    |           5 |
    |           6 |
    |           7 |
    |           8 |
    +-------------+
    8 rows in set (0.00 sec)
    

  • 查询2018年12月人事部所有员工工资

    mysql> select * from salary
        -> where year(date)=2018 and month(date)=12 and employee_id in (
        ->   select employee_id from employees
        ->   where dept_id=(
        ->     select dept_id from departments where dept_name='人事部'
        ->   )
        -> );
    +------+------------+-------------+-------+-------+
    | id   | date       | employee_id | basic | bonus |
    +------+------------+-------------+-------+-------+
    | 6252 | 2018-12-10 |           1 | 17016 |  7000 |
    | 6253 | 2018-12-10 |           2 | 20662 |  9000 |
    | 6254 | 2018-12-10 |           3 |  9724 |  8000 |
    | 6255 | 2018-12-10 |           4 | 17016 |  2000 |
    | 6256 | 2018-12-10 |           5 | 17016 |  3000 |
    | 6257 | 2018-12-10 |           6 | 17016 |  1000 |
    | 6258 | 2018-12-10 |           7 | 23093 |  4000 |
    | 6259 | 2018-12-10 |           8 | 23093 |  2000 |
    +------+------------+-------------+-------+-------+
    8 rows in set (0.00 sec)
    

单行多列

• 查找2018年12月基本工资和奖金都是最高的工资信息

  • 分析：

  • 查询2018年12月最高的基本工资

    mysql> select max(basic) from salary
        -> where year(date)=2018 and month(date)=12;
    +------------+
    | max(basic) |
    +------------+
    |      25524 |
    +------------+
    1 row in set (0.00 sec)
    

  • 查询2018年12月最高的奖金

    mysql> select max(bonus) from salary
        -> where year(date)=2018 and month(date)=12;
    +------------+
    | max(bonus) |
    +------------+
    |      11000 |
    +------------+
    1 row in set (0.00 sec)
    

  • 查询

    mysql> select * from salary
        -> where year(date)=2018 and month(date)=12 and basic=(
        ->   select max(basic) from salary
        ->   where year(date)=2018 and month(date)=12
        -> ) and bonus=(
        ->   select max(bonus) from salary
        ->   where year(date)=2018 and month(date)=12
        -> );
    +------+------------+-------------+-------+-------+
    | id   | date       | employee_id | basic | bonus |
    +------+------------+-------------+-------+-------+
    | 6368 | 2018-12-10 |         117 | 25524 | 11000 |
    +------+------------+-------------+-------+-------+
    1 row in set (0.01 sec)
    

多行多列

• 查询3号部门及其部门内员工的编号、名字和email

  • 分析

  • 查询3号部门和员工的所有信息

    mysql> select d.dept_name, e.*
        -> from departments as d
        -> inner join employees as e
        -> on d.dept_id=e.dept_id;
    

  • 将上述结果当成一张临时表，必须为其起别名。再从该临时表中查询

    mysql> select dept_id, dept_name, employee_id, name, email
        -> from (
        ->   select d.dept_name, e.*
        ->   from departments as d
        ->   inner join employees as e
        ->   on d.dept_id=e.dept_id
        -> ) as tmp_table
        -> where dept_id=3;
    +---------+-----------+-------------+-----------+--------------------+
    | dept_id | dept_name | employee_id | name      | email              |
    +---------+-----------+-------------+-----------+--------------------+
    |       3 | 运维部    |          14 | 廖娜      | liaona@guodong.com  |
    |       3 | 运维部    |          15 | 窦红梅    | douhongmei@guodong.com|
    |       3 | 运维部    |          16 | 聂想      | niexiang@guodong.com|
    |       3 | 运维部    |          17 | 陈阳      | chenyang@guodong.com|
    |       3 | 运维部    |          18 | 戴璐      | dailu@guodong.com   |
    |       3 | 运维部    |          19 | 陈斌      | chenbin@guodong.com |
    +---------+-----------+-------------+-----------+--------------------+
    6 rows in set (0.00 sec)