子查询
- 子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式
子查询返回的数据分类
- 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
- 单行多列：返回一行数据中多个列的内容
- 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
- 多行多列：查询返回的结果是一张临时表
子查询常出现的位置
- 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)
