mysql基础_子查询
一、概述
1. 含义
- 出现在其他语句中的select语句,称为子查询或内查询
- 外部的查询语句,称为主查询或外查询
2. 分类
2.1 按子查询出现的位置:
- select后面:仅仅支持标量子查询
- from后面:支持表子查询
- where或having后面:
- 标量子查询(单行)
- 列子查询 (多行)
- 行子查询
- exists后面(相关子查询)
- 表子查询
2.2 按结果集的行列数不同:
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
3. 特点
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询,一般搭配着单行操作符使用 ( > < >= <= = <> )
- 列子查询,一般搭配着多行操作符使用 (in、any/some、all )
- 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
二、 标量子查询
- 结果集只有一行一列
1. where后面
例:谁的工资比 Abel 高?
SELECT *
FROM employees
WHERE salary>(
SELECT salary #查询到的结果只能有一行一列
FROM employees
WHERE last_name = 'Abel'
);
- 子查询的结果
2. select后面
查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
- 查询的结果
三、列子查询 (多行子查询)
- 子查询返回 一列多行
- 多行比较操作符
操作符 | 含义 |
---|---|
IN / NOT IN | 等于列表中的任意一个 |
ANY / SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
- 一般 ANY/SOME/ALL 都可以使用其他方式代替,比如 a > all (10,20,30) 可以换成 a> max(10,20,30)
1. where后面
例:返回location_id是1400或1700的部门中的所有员工姓名 和部门编号
SELECT last_name ,department_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
子查询结果
查询结果
四、 行子查询
- 结果集有一行多列
例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#等价于下面
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
子查询结果
查询结果
五、表子查询
- 跟在 from 后面
- 将查询的结果充当一个数据源 的表 ,这个表必须起别名。
例: 查询每个部门的平均工资的工资等级
SELECT j.* , department_id ,avg_salary
FROM (
SELECT AVG(salary) avg_salary,department_id
FROM employees e
GROUP BY department_id
) ag_dep # 表必须起别名
JOIN job_grades j
ON ag_dep.avg_salary BETWEEN lowest_sal AND highest_sal;
子查询结果
查询结果
六、 相关子查询 exists
- exists 判断查询结果是否 存在 ,查询结果是否为空
- 可以使用 IN 代替
存在
SELECT EXISTS(
SELECT *
FROM employees
) result
不存在
SELECT EXISTS(
SELECT *
FROM employees
WHERE last_name = '111'
) result
案例:
查询所有有员工的部门名
# 使用 EXISTS
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
# 使用 IN
SELECT department_name
FROM departments
WHERE department_id IN (
SELECT DISTINCT department_id
FROM employees
)