MySQL子查询

含义：

出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。

分类：

按子查询出现的位置：
select后：仅支持标量子查询
from后：支持表子查询
where或having后(重点)：：支持标量子查()、列子查询()、行子查询（使用较少）
exists后（相关子查询）：表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

一、where和having后面：

1、标量子查询（单行子查询）:
#案例1：谁的工资比Abel高？
① 查询Abel的工资：

select salary
from employees where lastName = 'Abel';

② 查询员工的信息，满足salary > ①的结果：

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

#案例2：返回jobId与141号员工相同，salary比143号员工多的员工姓名、jobId和工资：
① 查询141号员工的jobId;

select jobId
from employees
where employeeId = '141';

② 查询143号员工的salary:

select salary
from employees
where employeeId = '143';

③ 查询员工姓名、jobId和工资，要求jobId = ①，salary > ②的结果：

select lastName, jobId, salary
from employees
where jobId = (
	select jobId
	from employees
	where employeeId = '141'
) and salary > (
	select salary
	from employees
	where employeeId = '143'
);

#案例3：查询公司工资最少的员工的lastName，jobId和salary：
① 查询公司的最少工资：

select min(salary)
from employees;

② 查询工资满足①的员工的lastName，jobId和salary:

select lastName,jobId,salary
from employees
where salary = (
	select min(salary)
	from employees
);

#案例4：查询最低工资大于50号部门最低工资的部门ID和其最低工资：
① 查询50号部门的最低工资：

select min(salary)
from employees
where departmentId = '50';

② 查询每个部门的最低工资：

select min(salary), departmentId
from employees
group by departmentId;

③ 筛选②的结果，要求min(salary) > ①：

select min(salary), departmentId
from employees
group by departmentId
having min(salary) > (
	select min(salary)
	from employees
	where departmentId = 50
);

2、列子查询（多行子查询）:
#案例1：查询locationId是1400或1700的部门中的所有员工姓名
① 查询locationId是1400或1700的部门编号

select departmentId
from departments
where locationId in(1400，1700);

② 查询员工姓名，要求部门号是①中的某一个

select lastName
from employees
where departmentId in (
	select distinct departmentId
	from departments
	where locationId in(1400，1700)
);

#以上语句等价于：
select lastName
from employees
where departmentId = any (
	select distinct departmentId
	from departments
	where locationId in(1400，1700)
);

#案例2：查询其他工种中比jobId为‘IT_PROG’工种任一工资低的员工的工号、姓名、jobId和salary
① 查询jobId为‘IT_PROG’部门的任一工资；

select distinct salary
from employees
where jobId = 'IT_PROG'

② 查询员工的工号、姓名、jobId和salary，要求salary<①中的任一一个

select employeeId, lastName, jobId, salary
from employees
where salary < any(
	select distinct salary
	from employees
	where jobId = 'IT_PROG'
) and jobId <> 'IT_PROG';

# 以上语句等价于：
select employeeId, lastName, jobId, salary
from employees
where salary < (
	select max(salary)
	from employees
	where jobId = 'IT_PROG'
) and jobId <> 'IT_PROG';

#案例3：查询其他工种中比jobId为‘IT_PROG’工种所有工资低的员工的工号、姓名、jobId和salary

select employeeId, lastName, jobId, salary
from employees
where salary < all(
	select distinct salary
	from employees
	where jobId = 'IT_PROG'
) and jobId <> 'IT_PROG';

# 以上语句等价于：
select employeeId, lastName, jobId, salary
from employees
where salary < (
	select min(salary)
	from employees
	where jobId = 'IT_PROG'
) and jobId <> 'IT_PROG';

3、行子查询（结果集一行多列或多行多列）:
#案例：查询员工编号最小并且工资最高的员工信息：
普通查询：
① 查询最小的员工编号：

select min(employeeId)
from employees

② 查询最高的工资：

select max(salary)
from employees

③ 查询员工信息：

select *
fromm employees
where employeeId = (
	select min(employeeId)
	from employees
) and salary = (
	select max(salary)
	from employees
);

行子查询：

select *
fromm employees
where (employeeId, salary) = (
	select min(employeeId), max(salary)
	from employees
);

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

二、select后面：

仅支持标量子查询。
#案例1：查询每个部门的员工个数

select d.*, (
	select count(*)
	from employees
	where e.departmentId = d.departmentId
)
from departments d;

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

select (
	select departmentName, e.departmentId
	from departments d
	inner join employees e
	on d.departmentId = e.departmentId
	where e.employeeId = 102
) 部门名

三、from后面：

将子查询结果充当一张表，要求必须起别名。
#案例：查询每个部门的平均工资的工资等级
① 查询每个部门的平均工资：

select AVG(salary) ag, departmentId
from employees
group by departmentId

② 连接①的结果集和jobGrades表，筛选条件：平均工资 between lowestSalary and highestSalary

select agDep.*, g.gradeLevel
from (select AVG(salary)，departmentId
from employees
group by departmentId) agDep
inner join jobGrades g
on avgDep.ag between lowestSalary and highestSalary

四、exists后面（相关子查询）：

exists (完整的查询语句)
结果：1或0。

select exists(
	select employeeId from employees
);

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

select departmentName 
from departments d
where exists (
	select * 
	from employees e
	where d.departmentId = e.departmentId
);
// 以上查询语句等价于：
select departmentName 
from departments d
where departmentId in (
	select * 
	from employees
);