这是 MySQL 系列第 12 篇.
环境: mysql5.7.25,cmd 命令中进行演示.
本章节非常重要.
子查询
出现在 select 语句中的 select 语句, 称为子查询或内查询.
外部的 select 查询语句, 称为主查询或外查询.
子查询分类
按结果集的行列数不同分为 4 种
标量子查询 (结果集只有一行一列)
列子查询 (结果集只有一列多行)
行子查询 (结果集有一行多列)
表子查询 (结果集一般为多行多列)
按子查询出现在主查询中的不同位置分
select 后面: 仅仅支持标量子查询.
from 后面: 支持表子查询.
where 或 having 后面: 支持标量子查询 (单列单行), 列子查询 (单列多行), 行子查询 (多列多行)
exists 后面 (即相关子查询): 表子查询 (多行, 多列)
准备测试数据
测试数据比较多, 放在我的个人博客上了.
浏览器中打开链接: http://www.itsoku.com/article/209
MySQL 中执行里面的 javacode2018_employees 库部分的脚本.
成功创建 javacode2018_employees 库及 5 张表, 如下:
表名 | 描述 |
---|---|
departments | 部门表 |
employees | 员工信息表 |
jobs | 职位信息表 |
locations | 位置表(部门表中会用到) |
job_grades | 薪资等级表 |
select 后面的子查询
子查询位于 select 后面的, 仅仅支持标量子查询.
示例 1
查询每个部门员工个数
- SELECT
- a.*,
- (SELECT count(*)
- FROM employees b
WHERE b.department_id = a.department_id) AS 员工个数
FROM departments a;
示例 2
查询员工号 = 102 的部门名称
- SELECT (SELECT a.department_name
- FROM departments a, employees b
- WHERE a.department_id = b.department_id
AND b.employee_id = 102) AS 部门名;
from 后面的子查询
将子查询的结果集充当一张表, 要求必须起别名, 否者这个表找不到.
然后将真实的表和子查询结果表进行连接查询.
示例 1
查询每个部门平均工资的工资等级
-- 查询每个部门平均工资
- SELECT
- department_id,
- avg(a.salary)
- FROM employees a
- GROUP BY a.department_id;
-- 薪资等级表
- SELECT *
- FROM job_grades;
-- 将上面 2 个结果连接查询, 筛选条件: 平均工资 between lowest_sal and highest_sal;
- SELECT
- t1.department_id,
- sa AS '平均工资',
- t2.grade_level
- FROM (SELECT
- department_id,
- avg(a.salary) sa
- FROM employees a
- GROUP BY a.department_id) t1, job_grades t2
- WHERE
- t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
运行最后一条结果如下:
- MySQL> SELECT
- t1.department_id,
- sa AS '平均工资',
- t2.grade_level
- FROM (SELECT
- department_id,
- avg(a.salary) sa
- FROM employees a
- GROUP BY a.department_id) t1, job_grades t2
- WHERE
- t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
- +---------------+--------------+-------------+
| department_id | 平均工资 | grade_level |
- +---------------+--------------+-------------+
- | NULL | 7000.000000 | C |
- | 10 | 4400.000000 | B |
- | 20 | 9500.000000 | C |
- | 30 | 4150.000000 | B |
- | 40 | 6500.000000 | C |
- | 50 | 3475.555556 | B |
- | 60 | 5760.000000 | B |
- | 70 | 10000.000000 | D |
- | 80 | 8955.882353 | C |
- | 90 | 19333.333333 | E |
- | 100 | 8600.000000 | C |
- | 110 | 10150.000000 | D |
- +---------------+--------------+-------------+
- 12 rows in set (0.00 sec)
where 和 having 后面的子查询
where 或 having 后面, 可以使用
标量子查询 (单行单列行子查询)
列子查询 (单列多行子查询)
行子查询 (一行多列)
特点
子查询放在小括号内.
子查询一般放在条件的右侧.
标量子查询, 一般搭配着单行单列操作符使用 >,<,>=,<=,=,<>,!=
列子查询, 一般搭配着多行操作符使用
in(not in): 列表中的 "任意一个"
any 或者 some: 和子查询返回的 "某一个值" 比较, 比如 a>some(10,20,30),a 大于子查询中任意一个即可, a 大于子查询中最小值即可, 等同于 a>min(10,20,30).
all: 和子查询返回的 "所有值" 比较, 比如 a>all(10,20,30),a 大于子查询中所有值, 换句话说, a 大于子查询中最大值即可满足查询条件, 等同于 a>max(10,20,30);
子查询的执行优先于主查询执行, 因为主查询的条件用到了子查询的结果.
MySQL 中的 in,any,some,all
in,any,some,all 分别是子查询关键词之一.
in:in 常用于 where 表达式中, 其作用是查询某个范围内的数据
any 和 some 一样: 可以与 =,>,>=,<,<=,<> 结合起来使用, 分别表示等于, 大于, 大于等于, 小于, 小于等于, 不等于其中的任何一个数据.
all: 可以与 =,>,>=,<,<=,<> 结合是来使用, 分别表示等于, 大于, 大于等于, 小于, 小于等于, 不等于其中的其中的所有数据.
下文中会经常用到这些关键字.
标量子查询
一般标量子查询, 示例
查询谁的工资比 Abel 的高?
- /*1查询 abel 的工资 [改查询是标量子查询] */
- SELECT salary
- FROM employees
- WHERE last_name = 'Abel';
- /*2查询员工信息, 满足 salary>1的结果 */
- SELECT *
- FROM employees a
- WHERE a.salary> (SELECT salary
- FROM employees
- WHERE last_name = 'Abel');
多个标量子查询, 示例
返回 job_id 与 141 号员工相同, salary 比 143 号员工多的员工, 姓名, job_id 和工资
- /* 返回 job_id 与 141 号员工相同, salary 比 143 号员工多的员工, 姓名, job_id 和工资 */
- /*1查询 141 号员工的 job_id*/
- SELECT job_id
- FROM employees
- WHERE employee_id = 141;
- /*2查询 143 好员工的 salary*/
- SELECT salary
- FROM employees
- WHERE employee_id = 143;
- /*3查询员工的姓名, job_id, 工资, 要求 job_id=1 and salary>2*/
- SELECT
a.last_name 姓名,
a.job_id,
a.salary 工资
- FROM employees a
- WHERE a.job_id = (SELECT job_id
- FROM employees
- WHERE employee_id = 141)
- AND
- a.salary> (SELECT salary
- FROM employees
- WHERE employee_id = 143);
子查询 + 分组函数, 示例
查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资 [having]
- /* 查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资 [having] */
- /*1查询 50 号部门的最低工资 */
- SELECT min(salary)
- FROM employees
- WHERE department_id = 50;
- /*2查询每个部门的最低工资 */
- SELECT
- min(salary),
- department_id
- FROM employees
- GROUP BY department_id;
- /*3在2的基础上筛选, 满足 min(salary)>1*/
- SELECT
- min(a.salary) minsalary,
- department_id
- FROM employees a
- GROUP BY a.department_id
- HAVING min(a.salary)> (SELECT min(salary)
- FROM employees
- WHERE department_id = 50);
错误的标量子查询, 示例
将上面的示例3中子查询语句中的 min(salary) 改为 salary, 执行效果如下:
- MySQL> SELECT
- min(a.salary) minsalary,
- department_id
- FROM employees a
- GROUP BY a.department_id
- HAVING min(a.salary)> (SELECT salary
- FROM employees
- WHERE department_id = 500000);
- ERROR 1242 (21000): Subquery returns more than 1 row
错误提示: 子查询返回的结果超过了 1 行记录.
说明: 上面的子查询只支持最多一列一行记录.
列子查询 (子查询结果集一列多行)
列子查询需要搭配多行操作符使用: in(not in),any/some,all.
为了提升效率, 最好去重一下 distinct 关键字.
示例 1
返回 location_id 是 1400 或 1700 的部门中的所有员工姓名
- /* 返回 location_id 是 1400 或 1700 的部门中的所有员工姓名 */
- /* 方式 1*/
- /*1查询 location_id 是 1400 或 1700 的部门编号 */
- SELECT DISTINCT department_id
- FROM departments
- WHERE location_id IN (1400, 1700);
- /*2查询员工姓名, 要求部门是1列表中的某一个 */
- SELECT a.last_name
- FROM employees a
- WHERE a.department_id IN (SELECT DISTINCT department_id
- FROM departments
- WHERE location_id IN (1400, 1700));
- /* 方式 2: 使用 any 实现 */
- SELECT a.last_name
- FROM employees a
- WHERE a.department_id = ANY (SELECT DISTINCT department_id
- FROM departments
- WHERE location_id IN (1400, 1700));
- /* 拓展, 下面与 not in 等价 */
- SELECT a.last_name
- FROM employees a
- WHERE a.department_id <> ALL (SELECT DISTINCT department_id
- FROM departments
- WHERE location_id IN (1400, 1700));
示例 2
返回其他工种中比 job_id 为'IT_PROG'工种任意工资低的员工的员工号, 姓名, job_id,salary
- /* 返回其他工种中比 job_id 为'IT_PROG'工种任一工资低的员工的员工号, 姓名, job_id,salary*/
- /*1查询 job_id 为'IT_PROG'部门任 - 工资 */
- SELECT DISTINCT salary
- FROM employees
- WHERE job_id = 'IT_PROG';
- /*2查询员工号, 姓名, job_id,salary,slary<1的任意一个 */
- SELECT
- last_name,
- employee_id,
- job_id,
- salary
- FROM employees
- WHERE salary <ANY (SELECT DISTINCT salary
- FROM employees
- WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
- /* 或者 */
- SELECT
- last_name,
- employee_id,
- job_id,
- salary
- FROM employees
- WHERE salary < (SELECT max(salary)
- FROM employees
- WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
示例 3
返回其他工种中比 job_id 为'IT_PROG'部门所有工资低的员工的员工号, 姓名, job_id,salary
- /* 返回其他工种中比 job_id 为'IT_PROG'部门所有工资低的员工的员工号, 姓名, job_id,salary*/
- SELECT
- last_name,
- employee_id,
- job_id,
- salary
- FROM employees
- WHERE salary < ALL (SELECT DISTINCT salary
- FROM employees
- WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
- /* 或者 */
- SELECT
- last_name,
- employee_id,
- job_id,
- salary
- FROM employees
- WHERE salary < (SELECT min(salary)
- FROM employees
- WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
行子查询 (子查询结果集一行多列)
示例
查询员工编号最小并且工资最高的员工信息, 3 种方式.
- /* 查询员工编号最小并且工资最高的员工信息 */
- /*1查询最小的员工编号 */
- SELECT min(employee_id)
- FROM employees;
- /*2查询最高工资 */
- SELECT max(salary)
- FROM employees;
- /*3方式 1: 查询员工信息 */
- SELECT *
- FROM employees a
- WHERE a.employee_id = (SELECT min(employee_id)
- FROM employees)
- AND salary = (SELECT max(salary)
- FROM employees);
- /* 方式 2*/
- SELECT *
- FROM employees a
- WHERE (a.employee_id, a.salary) = (SELECT
- min(employee_id),
- max(salary)
- FROM employees);
- /* 方式 3*/
- SELECT *
- FROM employees a
- WHERE (a.employee_id, a.salary) in (SELECT
- min(employee_id),
- max(salary)
- FROM employees);
方式 1 比较常见, 方式 2,3 更简洁.
exists 后面 (也叫做相关子查询)
语法: exists(完整的查询语句).
exists 查询结果: 1 或 0,exists 查询的结果用来判断子查询的结果集中是否有值.
一般来说, 能用 exists 的子查询, 绝对都能用 in 代替, 所以 exists 用的少.
和前面的查询不同, 这先执行主查询, 然后主查询查询的结果, 在根据子查询进行过滤, 子查询中涉及到主查询中用到的字段, 所以叫相关子查询.
示例 1
简单示例
- MySQL> SELECT exists(SELECT employee_id
- FROM employees
- WHERE salary = 300000) AS 'exists 返回 1 或者 0';
- +----------------------+
| exists 返回 1 或者 0 |
- +----------------------+
- | 0 |
- +----------------------+
- 1 row in set (0.00 sec)
示例 2
查询所有员工的部门名称
- /*exists 入门案例 */
- SELECT exists(SELECT employee_id
- FROM employees
- WHERE salary = 300000) AS 'exists 返回 1 或者 0';
- /* 查询所有员工部门名 */
- SELECT department_name
- FROM departments a
- WHERE exists(SELECT 1
- FROM employees b
- WHERE a.department_id = b.department_id);
- /* 使用 in 实现 */
- SELECT department_name
- FROM departments a
- WHERE a.department_id IN (SELECT department_id
- FROM employees);
示例 3
查询没有员工的部门
- /* 查询没有员工的部门 */
- /*exists 实现 */
- SELECT *
- FROM departments a
- WHERE NOT exists(SELECT 1
- FROM employees b
- WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);
- /*in 的方式 */
- SELECT *
- FROM departments a
- WHERE a.department_id NOT IN (SELECT department_id
- FROM employees b
- WHERE b.department_id IS NOT NULL);
上面脚本中有 b.department_id IS NOT NULL, 为什么, 有大坑, 向下看.
NULL 的大坑
示例 1
使用 not in 的方式查询没有员工的部门, 如下:
- SELECT *
- FROM departments a
- WHERE a.department_id NOT IN (SELECT department_id
- FROM employees b);
运行结果:
- MySQL> SELECT *
- -> FROM departments a
- -> WHERE a.department_id NOT IN (SELECT department_id
- -> FROM employees b);
- Empty set (0.00 sec)
not in 的情况下, 子查询中列的值为 NULL 的时候, 外查询的结果为空.
建议: 建表是, 列不允许为空.
总结
本文中讲解了常见的子查询, 请大家务必多练习
注意 in,any,some,any 的用法
字段值为 NULL 的时候, not in 查询有大坑, 这个要注意
建议创建表的时候, 列不允许为空
MySQL 系列目录
第 1 篇: MySQL 基础知识
第 2 篇: 详解 MySQL 数据类型 (重点)
第 3 篇: 管理员必备技能 (必须掌握)
第 4 篇: DDL 常见操作
第 5 篇: DML 操作汇总 (insert,update,delete)
第 6 篇: select 查询基础篇
第 7 篇: 玩转 select 条件查询, 避免采坑
第 8 篇: 详解排序和分页 (order by & limit)
第 9 篇: 分组查询详解 (group by & having)
第 10 篇: 常用的几十个函数详解
第 11 篇: 深入了解连接查询及原理
MySQL 系列大概有 20 多篇, 喜欢的请关注一下, 欢迎大家加我微信 itsoku 或者留言交流 MySQL 相关技术!
来源: https://www.cnblogs.com/itsoku123/p/11577799.html