MySQL学习六：子查询

1.数据库来源：Mysql 基础教程
2.数据库说明：
本文使用的第一个数据库名称为 myemployees，其中有 8 张表，分别为 customers 表，departments 表，employees 表，job_grades 表，jobs 表，locations 表，orders 表，salespeople 表。

一、子查询分类

1.1 按结果集的行列数不同

① 标量子查询（结果集只有一行一列）
② 列子查询（结果集只有一列多行）
③ 行子查询（结果集一般为一行多列，也可以为多行多列）
④ 表子查询（结果集一般为多行多列）

1.2 按子查询出现的位置不同

where 或 having 后面：标量子查询（单行），列子查询（多行），行子查询（多列）。前两个子查询为重点
select 后面：仅仅支持标量子查询
from 后面：支持表子查询
exists 后面：支持表子查询

二、子查询特点

① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配单行操作符（>，<，>=，<=，=，<>）使用
列子查询，一般搭配多行操作符（in/not in，any/some，all）使用
④ 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

三、子查询相关案例（仅展示有限部分）

3.1 where 或 having 后面（重点）

3.1.1 标量子查询

案例一：查询工资比 Abel 高的所有员工信息

select * from employees
where salary > (
	select salary from employees
	where last_name = 'Abel'
)

案例二：查询 job_id 与141号员工相同，且 salary 比143号员工多的员工姓名，job_id 和工资

select last_name, job_id, salary from employees
where job_id = (
	select job_id from employees
	where employee_id = 141
) and salary > (
	select salary from employees
    where employee_id = 143
)

案例三：查询公司工资最少的员工的 last_name，job_id 和 salary

select last_name, job_id, salary from employees
where salary = (
	select min(salary) from employees
)

案例四：查询最低工资 > 50 号部门最低工资的非空部门 id 和其最低工资

select department_id, min(salary) from employees
where department_id is not null
group by department_id
having min(salary) > (
	select min(salary) from employees
    where department_id = 50
)

3.1.2 列子查询

案例一：返回 location_id 是 1400 或 1700 的部门中的所有员工姓名

方法一：子查询
select last_name from employees
where department_id in(
	select distinct department_id from departments
	where location_id in(1400, 1700)
)

方法二：内连接
select last_name
from employees e
join departments d
on e.department_id = d.department_id
where location_id in(1400, 1700)

案例二：查询其它工种中比 job_id 为 ‘IT_PROG’ 工种任一工资低的员工的employee_id、姓名、job_id 以及 salary

方法一：
select employee_id, last_name, job_id, salary from employees
where salary < any(
	select distinct salary from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG'

方法二：
select employee_id, last_name, job_id, salary from employees
where salary < (
	select max(salary) from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG'

案例三：查询其它工种中比 job_id 为 ‘IT_PROG’ 工种所有工资都低的员工的employee_id、姓名、job_id 以及 salary

方法一：
select employee_id, last_name, job_id, salary from employees
where salary < all(
	select distinct salary from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG'

方法二：
select employee_id, last_name, job_id, salary from employees
where salary < (
	select min(salary) from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG'

3.1.3 行子查询（有局限性，了解即可）

案例一：查询员工编号最小并且工资最高的员工信息

select * from employees
where (employee_id, salary) = (
	select min(employee_id), max(salary) from employees
) 

3.2 select 后面

案例一：查询每个部门的员工个数

方法一：子查询
select d.department_id,(
	select count(*) from employees e
	where e.department_id = d.department_id
 ) 员工个数
from departments d

方法二：外连接 + 分组查询
select d.department_id, count(employee_id) 员工个数
from employees e
right join departments d
on e.department_id = d.department_id
group by d.department_id

案例二：查询员工号 = 102的部门名

方法一：子查询
select (
	select d.department_name
	from employees e
	join departments d
	on e.department_id = d.department_id
	where employee_id = 102
) 部门名

方法二：内连接
select d.department_name as 部门名
from employees e
join departments d
on e.department_id = d.department_id
where employee_id = 102

3.3 from 后面

案例一：查询每个非空部门的平均工资的工资等级

select av_dep.*, j.grade_level
from(
	select department_id, round(avg(salary),0) 平均工资 from employees
	group by department_id
) av_dep
join job_grades j
on av_dep.平均工资 between j.lowest_sal and j.highest_sal
where av_dep.department_id is not null

3.4 exists 后面

案例一：查询有员工的部门名

方法一：exists 查询
select department_name
from departments d
where exists(
	select * from employees e
	where d.department_id = e.department_id
)

方法二：内连接
select distinct d.department_name
from employees e
join departments d
on e.department_id = d.department_id
where employee_id is not null