05_DQL 的子查询 + 案例

今天的这篇博客为大家介绍一下数据查询语言中的子查询。

子查询

子查询的含义

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

子查询的分类

• 按子查询出现的位置：
  • select 后面（仅仅支持标量子查询）
  • from 后面 （支持表子查询）
  • where 或 having 后面（标量子查询、列子查询）
  • exists 后面 （支持表子查询）
• 按结果集的行列数不同：
  • 标量子查询（查询结果只有一行一列）
  • 列子查询（结果集只有一列多行）
  • 行子查询（结果集可以有一行多列）
  • 表子查询（结果集，无所谓，一般为多行多列）

where 或 having 后面

特点：

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

标量子查询

案例1： 谁的工资比 Abel的高？

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

案例2：返回 job_id 与 141号员工相同，salary比143号员工多的员工姓名，job_id 和 工资。

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

案例3：返回员工工资最少的员工的 last_name, job_id 和 salary.

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

案例4：查询最低工资大于 50 号部门的最低工资的部门id和最低工资。

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

列子查询

返回多行，使用多行比较操作符。

操作符	含义
IN / NOT IN ☆	等于列表中的任意一个
ANY / SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

案例1：返回 location_id是1400或1700的部门的所有员工。

select last_name
from employees e
where department_id in 
	(select DISTINCT department_id
	from departments
	where location_id IN (1400, 1700));

案例2：返回其他部门中比 job_id 为 ‘IT_PROG’ 部门任一工资低的员工的员工号，姓名，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 <> 'IT_PROG';  # 要求其他部门

ANY 是指比条件中的任意一个小即可以，即小于条件中的最大值就可以 可以把 ANY 换为 MAX。

案例3：返回其他部门中比job_id为‘IT_PROG’所有工资都低的员工的员工号、姓名、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 <> 'IT_PROG';

行子查询

结果为一行多列，或多行多列

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

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

select 后面

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

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

案例2：查询员工号等于102的部门名。

select 
	(select department_name
	from departments d
	inner join employees e
	on d.department_id = e.department_id
	where e.employee_id = 102) 部门名;

该查询结果只能是一行一列，仅仅支持标量子查询。

from 后面

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

select *, g.grade_level
from (
	select avg(salary) ag, department_id
	from employees
	group by department_id ) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal

exists 后面

select exists(select employee_id from employees);

判断是否存在，布尔类型。返回的结果是 1 或 0

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

select department_name
from departments d
where exists(
	select *
	from employees e
	where d.department_id = e.department_id);

select department_name
from departments d
where d.department_id in (
	select department_id
	from employees);

案例2：查询没有女朋友的男神信息

select bo.*
from boys bo
where not exists(
	select *
	from beauty b
	where b.boyfriend_id = bo.id);

子查询的练习题

1. 查询和 Zlotkey 相同部门的员工姓名和工资

select last_name, salary
from employees
where department_id = (
	select department_id
	from employees
	where last_name = 'Zlotkey');

2. 查询工资比公司平均工资高的员工的员工号、姓名和工资。

select employee_id, last_name, salary
from employees
where salary > (
	select avg(salary) from employees );

3. 查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资。

select employee_id, last_name, salary
from employees e
inner join (
	select avg(salary) ag, department_id
	from employees
	group by department_id ) as ag_dep
on e.deaprtment_id = ag_dep.department_id
where salary > ag_dep.ag;

4. 查询和姓名中包含字母 u 的员工在相同的部门的员工的员工号和姓名。

select employee_id, last_name
from employees
where department_id in (
	select distinct department_id
	from employees 
	where last_name like '%u%);

5. 查询部门的location_id为1700的部门工作的员工和员工号。

select employee_id
from employees
where department_id in (
	select distinct department_id
	from departments
	where location_id = 1700); # 这里的 in = any

6. 查询管理者是king的员工姓名和工资

select last_name, salary
from employees
where manager_id in (
	select employee_id
	from employees 
	where last_name = 'King');

7. 查询工资最高的员工姓名，要求 first_name 和 last_name 显示为一列，列名为姓.名

select concat(first_name, '.', last_name)
from employees 
where salary = (
	select max(salary) from employees);