Mysql 知行必会 “子查询”

发布于：2023-01-18 ⋅ 阅读:(514) ⋅ 点赞:(0)

Mysql 知行必会 “子查询”

在这里插入图片描述

每博一文案

后半生看透了人心，也看透了这世间所有的得失，不值得的人，不再
托付真心，不值得的感情，不会苦苦挽留，这世上有太多的人，
人前口服蜜饯，人后搬弄是非。老话说人难猜，心难测，下雨了，才能知晓，
谁会给我们送伞，遇难处才能分辨谁对我们是真心，我们在至暗时刻，
才能看清谁是真情，谁是假意。
真正的朋友不是在顺风顺水时，称兄道弟的人，
而是我们一穷二白时，依然能够不离不弃的人，走过半身，渐渐看透了人，
看透了心，看透了人生百态，慢慢的不再轻易为谁付出真心。
怕受到忽视，更怕深交后的陌生，只想守住自己的生活，把精力
放在重要的事情上。把爱留给最值得人，愿你能在这个苍茫的世界，
诗意的活着。在流年里，淡看，尘世烟火，细品，岁月静好，宠辱不惊。
                                  ——————  一禅心灵庙语

文章目录

一. 子查询的概念

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 Mysql 4.1 开始引入的。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候，查询需要从结果集中获取数据，或者需要从同一个表中先计算得出结果，然后与这个数据结果 (可能是某个标量，也可能是某个集合) 进行比较上的操作判断。

1.1 子查询的使用

  • 子查询(内查询) 在查询之前一次执行完成，子查询作为内层查询会首先执行
  • 子查询 的结果被主查询(外查询) 使用

注意事项

  1. 子查询要包含在括号内
  2. 将子查询放在比较条件的右侧，当然放在左侧也是可以的，但是放在右侧更有助于阅读
  3. 子查询 除了不能在 GROUP BY分组 和 LIMIT分页 之外，还有就是 Mysql 中的分组函数(MIN, MAX,SUM,AVG,COUNT) ,中不能使用子查询，其他地方都可以使用子查询，不过不同的地方使用子查询存在一定的其他的约束条件。
  4. 单行操作符 对应单行子查询 ，多行操作符 对应多行子查询

子查询的格式

SELECT xxx  -- 主查询的内容
FROM xxx
WHERE (  -- 子查询的内容
	SELECT xxx
	FROM xxx
	)
/* 子查询的内容一定要放在括号内*/

从结构格式上看就是简单的 查询语句之间的嵌套 ，与我们的 Java 中的 if, for, 嵌套 是一个差不多的意思。

子查询同样也是可以多层嵌套的

SELECT last_name, salary   -- 主查询
FROM employees
WHERE salary = (       -- 第一层子查询嵌套
	SELECT MAX(salary)
	FROM employees
	WHERE department_id = (  -- 第二层子查询嵌套
			SELECT department_id
			FROM departments
			WHERE location_id =  2500)
			);

1.2 子查询的分类

不同的角度分类的结果是不一样的

角度1

从内查询返回的结果的条目数上分类

  • 单行子查询
  • 多行子查询

角度2

从内查询是否被执行多次上分类

  • 关联子查询
  • 不关联子查询

在这里插入图片描述

二. 单行子查询

单行子查询 ：有一个限定条件，那就是 子查询的结果必须而且只能返回一行一列的结果 ，不可以返回多行多列数据，单行子查询的操作符无法处理多行多列的数据会报错 ，所以千万不要这么做。

在这里插入图片描述

2.1 单行操作符

单行操作符 对应 单行子查询 的查询条件的筛选

操作符 作用
= 等于
!= ,<> 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于

注意: 在单行操作符中 是无法处理： 子查询返回多条数据的判断比较的，非要这么做的话时，是会报错的，如下:

SELECT last_name,salary
FROM employees
WHERE salary > (  /* 使用单行操作符 对子查询中的多条数据的判断比较，报错*/
		SELECT salary
		FROM employees);

在这里插入图片描述

2.2 单行子查询的演示实例

在这里插入图片描述

子查询的编写技巧(或步骤)

  1. 从里往外写: 就是先写子查询，再写主查询
  2. 从外往里写: 就是先写主查询，再写子查询

题目:查询工资大于 149 号员工的工资的员工的信息

-- 题目:查询工资大于 149 号员工的工资的员工的信息
SELECT last_name, employee_id, salary   -- 主查询的内容
FROM employees 
WHERE salary = (   -- 子查询的内容
		SELECT salary
		FROM employees
		WHERE employee_id = 149);

在这里插入图片描述

题目: 查询 job_id 与 141 号员工相同，salary 比 141 号员工的多的员工的姓名，job_id, 和工资

-- 题目: 查询 job_id 与 141 号员工相同，salary 比 141 号员工的多的员工的姓名，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 = 141);

在这里插入图片描述

题目: 查询与141 号员工的 manager_id,和 department_id 相同的其他员工的 employee_id, manager_id, department_id

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id = (
		SELECT manager_id
		FROM employees
		WHERE employee_id = 141)
AND department_id = (
		SELECT department_id
		FROM employees
		WHERE employee_id = 141)
AND employee_id != 141 /*除去它本身*/

在这里插入图片描述

或者方式 二，大家可以作为了解

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id,department_id) = (
			SELECT manager_id, department_id
			FROM employees
			WHERE employee_id = 141);

在这里插入图片描述

2.3 子查询出现的常见位置的实例

一. 在WHERE 条件的筛选中使用 子查询

题目:查询谁的工资比 Abel 的工资高

SELECT last_name, salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel');

在这里插入图片描述

二. 在 SELECT 中使用子查询

注意： 对于 SELECT 后面的子查询来说，只能一次返回一条结果，多于 1 条结果，就报错了，如下:

SELECT last_name, (SELECT salary FROM employees)
FROM employees;

在这里插入图片描述

在 SElECT 中一个返回值结果

SELECT last_name, (SELECT SUM(salary) FROM employees)
FROM employees;

在这里插入图片描述

三. 在 HAVING 中条件筛选使用子查询

题目: 查询最低工资大于 50 号部门最低工资的部门id 和 其最低工资

SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL  /* 除去为 NULL 的*/
GROUP BY department_id
HAVING MIN(salary) > (
		SELECT MIN(salary)
		FROM employees
		WHERE department_id = 50);

在这里插入图片描述

四. 在 FROM子句中使用子查询

在 FROM 后面的子查询，可以将子查询的查询结果，当做为一张临时表使用，而该子查询临时表的分组函数 要使用 别名，否则无法使用子查询中的临时表，索引出的字段，而是被当作函数处理，没有该函数，从而报错

题目 查询平均工资最低的部门 id

SELECT last_name, AVG(salary), department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) = (
		SELECT MIN(min_sal)
		FROM (
			SELECT AVG(salary) min_sal  /* 字段别名 */
			FROM employees
			GROUP BY department_id) t_dapt_avg_sal);  /* 设定表别名*/

在这里插入图片描述

如若没有设定表别名，报错

-- 题目 查询平均工资最低的部门 id
 
SELECT last_name, AVG(salary), department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) = (
		SELECT MIN(min_sal)
		FROM (
			SELECT AVG(salary) min_sal  /* 字段别名 */
			FROM employees
			GROUP BY department_id) );  /* 不设定表别名*/

在这里插入图片描述

五. 在 CASE … WHEN … THEN 子句中使用子查询

在 CASE … WHEN … THEN 子句中使用子查询 注意 使用上括号

题目: 显示员工的 employee_id, last_name 和 location 其中，department_id ，与location_id 为 1800 的depatment_id 相同，则 location 为 ’Canada’ , 其余则为 ‘USA’

-- 题目: 显示员工的 employee_id, last_name 和 location 其中，department_id ，
-- 与location_id 为 1800 的depatment_id 相同， 则 location 为 ’Canada' , 其余则为 ‘USA'
SELECT employee_id, last_name,
CASE department_id WHEN (
	SELECT department_id
	FROM departments
	WHERE location_id = 1800) THEN 'Canada'
	ELSE 'USA' END  location /* 别名*/
FROM employees;

在这里插入图片描述

2.3 单行子查询空值问题

子查询中存在空值 NULL ，不会报错，而是没有结果显示，如下:

SELECT last_name, job_id
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE last_name = 'Hello' ); /* 该表中不存在该姓名的员工*/

在这里插入图片描述

三. 多行子查询

多行子查询 也称为是 集合比较子查询 ，对于子查询中多行数据返回的处理判断比较，使用 多行比较操作符

3.1 多行操作符

多行操作符 对应 多行子查询 的查询条件的筛选，对于子查询中的多个返回值的处理

操作符 作用
IN 等于子查询返回列表中的任意一个
ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME 作用和 ANY一样，实际上是 ANY的别名，一般使用 ANY

3.2 多行子查询的多行操作符使用实例

一. IN 在多行子查询中的使用

IN 在多行子查询中的使用和在其他的地方的使用以及作用都是一样的，只要该查询的字段的值在 IN(在括号中出现了) 就返回 true , 没有出现返回 false .

  • NOT IN : 返回不在该 IN(括号的值)

题目: 查询各个部门中的员工的最低工资 的 employee_id, last_name

SELECT salary , employee_id, last_name
FROM employees
WHERE salary IN (
		SELECT MIN(salary)
		FROM employees
		GROUP BY department_id);

在这里插入图片描述

SELECT salary , employee_id, last_name
FROM employees
WHERE salary NOT IN (  /* 使用 NOT IN 不在该括号中的值 */
		SELECT MIN(salary)
		FROM employees
		GROUP BY department_id);

在这里插入图片描述

二. ANY在子查询当中的使用

ANY 在子查询当中的使用：需要配合单行操作符一起使用，其中单行操作符在多行操作符的前面(这里的前面以左边作为参考) , 表示： 返回子查询中的任意一个的值满足条件的值

其中 SOME 其作用以及使用都是一样的，当作是 ANY 的一个别名吧

  • < ANY : 返回主查询中的结果集,中 小于 子查询中的 任意 一个值，如果该值不存在，则返回 false
  • > ANY : 返回主查询中的结果集,中 大于 子查询中的 任意 一个值，如果该值不存在，则返回 false
  • = ANY : 返回主查询中的结果集,中 等于 子查询中的 任意 一个值，如果该值不存在，则返回 false
  • != ANY/ <>ANY : 返回主查询的结果集,中 等于 子查询中的 任意 一个值，如果该值不存在，则返回 false

题目: 查询其它 job_id中比, job_id 为“IT_PBOG" 部门中任意工资都低的员工号，姓名，job_id以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG');

在这里插入图片描述

替换成 SOME 也是一样的

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < SOME ( /* 替换成 some 是一样的*/
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG');

在这里插入图片描述

三. ALL在子查询当中的使用

ALL在子查询中的的使用：同样需要单行操作符的配合，其中单行操作符在该多行操作符的前面(这里的前面是以左边为参考) ，表示：子查询中的所有值都满足条件

  • < ALL : 返回主查询的结果集 ,中 小于 子查询中所有 (每一个)的值，才为 true ,但凡存在一个不满足都返回 false
  • > ALL : 返回主查询的结果集,中 大于 子查询中 所有 (每一个)的值，才为 true ,但凡存在一个不满足都返回 false
  • = ALL : 返回主查询的结果集, 中 等于 子查询中 所有 (每一个)的值，才为 true ,但凡存在一个不满足都返回 false
  • != ALL / <>ALL : 返回主查询的结果集, 中 不等于 子查询中 所有 (每一个)的值，才为 true ,但凡存在一个不满足都返回 false

题目:查询其它 job_id中比, job_id 为“IT_PBOG" 部门中 所有 工资都低的员工号，姓名，job_id以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG');

在这里插入图片描述

3.3 多行子查询空值问题

不会报错，只是没有结果的显示

SELECT last_name
FROM employees
WHERE employee_id NOT IN(
		SELECT manager_id
		FROM employees);

在这里插入图片描述

SELECT last_name
FROM employees
WHERE employee_id NOT IN(
		SELECT manager_id
		FROM employees
		WHERE manager_id IS NOT NULL);

在这里插入图片描述

四. 关联子查询

如果子查询的执行依赖于外部的主查询，通常情况下都是因为 子查询中的表用了外部主查询的表中的数值，并进行了条件的关联 ，就像表连接之间的关联条件一样，因此每执行一次外部的主查询，内部的子查询都要重新计算一次，因为 内部的子查询关联条件取自外部的主查询，这样的子查询就被称之为了 关联子查询或者称为 相关子查询

关联子查询中的关联条件写在子查询中 WHERE 子句中

具体格式如下：

SELECT xxx
FROM xxx outers  -- 表别名
WHERE xxx = (
SELECT xxx
FROM xxx inside  -- 表别名
WHERE outers.xxx = inside.xxx  /* 关联子查询的关联条件*/ )
/* 对于关联子查询，基本上都要使用表别名，因为
关联条件的筛选，基本上是多个表共同拥有的字段，存在冲突，字段不明确是来自那个表中的信息
从而导致关联条件的筛选存在错误*/

关联子查询 于 普通子查询的区别在于：

  1. 关联子查询引用了外部查询(主查询)的列。
  2. 执行顺序的不同。对于普通子查询是先执行普通子查询，再执行外层(主)查询
    而对于关联子查询，是先执外层(主)查询，然后对所有通过，过滤条件的记录执行内层(子)查询 总的来说就是先执行外层(主查询)，中间再根据关联执行内层(子)查询，自外到内

关联子查询的执行逻辑

在关联子查询中，对于外部(主)查询返回的每一行数据，内部(子)查询都要执行一次。另外，在关联子查询中是 信息流是双向的 ，外部(主)查询的每一行数据传递一个值给子查询，然后，子查询为每一行数据执行一次并返回它的记录。然后，外部(主)查询根据返回的记录做出决策

4.1 关联子查询的执行步骤

关联子查询主要分为三步进行处理：

  1. 先从 外部(主)查询 中 得到 一条记录并传递 到 内部(子)查询中
  2. 内部(子)查询 基于输入值执行，并将返回值 传递 到 外部(子)查询 中
  3. 外部(子)查询 基于这个返回值，再进行查询，并做出决策，是否要

在这里插入图片描述

在这里插入图片描述

4.2 关联子查询的使用实例

题目1: 查询员工中工资大于所在对应部门的平均工资的员工的 last_name, salary 和其 department_id

SELECT e1.`last_name`, e1.`salary`, e1.`department_id`
FROM employees e1 /* 表别名 */
WHERE e1.`salary` > (
		SELECT AVG(salary)
		FROM employees e2  /* 表别名*/
		WHERE e1.`department_id` = e2.`department_id`); 
		/*子查询与主查询之间的关联数值。

在这里插入图片描述

注意: 在关联子查询中，对于表之间的字段存在冲突时，使用表的别名，明确表示对应表中的字段，避免冲突错误的发生 ，会导致查询的数据是不正确的，

如下：表中存在字段上的冲突，使用表别名，明确表示对应表中的字段

SELECT last_name, salary, department_id
FROM employees 
WHERE salary > (
		SELECT AVG(salary) 
		FROM employees   /* 没有使用表别名，关联字段在多个表中存在，冲突报错*/
		WHERE department_id = department_id); 
		/*子查询与主查询之间的关联数值。*/

在这里插入图片描述

从查询结果上看我们可以明确的感觉到，其没有使用表别名，查询的结果比使用了正确的表别名，查询的结果上明显更多，可能别没有做到一个关联条件的筛选，导致的吧

题目2: 查询员工的id, salary ,按照 department_name 排序

在这里插入图片描述

SELECT emp.employee_id, emp.salary
FROM employees emp
ORDER BY (
SELECT department_name
FROM departments dep
WHERE dep.`department_id` = emp.`department_id`) ASC ;
/*子查询与主查询之间的关联数值。*/

在这里插入图片描述

题目3: 查询employees 表中employee_id 与 job_history 表中 employee_id 相同的数目 不小于 2，输出这些相同 id 的员工的 employee_id,last_name 和其 job_id

在这里插入图片描述

SELECT emp.`employee_id`, emp.`last_name`, emp.`job_id`
FROM employees emp 
WHERE 2 <= (
		SELECT COUNT(*)
		FROM job_history job
		WHERE job.`employee_id` = emp.employee_id);
		/*子查询与主查询之间的关联数值。*/

在这里插入图片描述

4.3 关联子查询的注意事项

  • 在关联子查询中使用上表别名

因为在关联子查询中的，关联条件基本上都是多个表中共同拥有的属性(字段)，如果不使用表别名，加以区分，Mysql 无法确定字段信息是来自那个表中的数据，从而导致报错，或者是返回的数值错误，这是明显的字段冲突的错误

如下：查询员工中工资大于所在对应部门的平均工资的员工的 last_name, salary 和其 department_id

首先我们先使用表别名，正确的查询看看结果是：

SELECT e1.`last_name`, e1.`salary`, e1.`department_id`
FROM employees e1 /* 表别名 */
WHERE e1.`salary` > (
		SELECT AVG(salary)
		FROM employees e2  /* 关联条件中的字段，多个表中都存在该字段,使用表别名,避免冲突 */
		WHERE e1.`department_id` = e2.`department_id`);

在这里插入图片描述

SELECT last_name, salary, department_id
FROM employees 
WHERE salary > (
		SELECT AVG(salary) 
		FROM employees   /* 没有使用表别名，关联字段在多个表中存在，冲突，数据查询错误*/
		WHERE department_id = department_id); 
		/*子查询与主查询之间的关联数值。*/

在这里插入图片描述

从对比查询结果上看，没有使用表别名的，并没有报错，但是查询的结果却比正确使用了表别名的查询到的结果多了 51 -38 = 13 条数据，

从这里我们可以知道了，关联子查询，存在表之间的字段(属性)上的冲突，如果没有使用表别名明确对应字段的来源，可能不会报错，但是所查询到的结果可能是不正确的。

  • 关联子查询的关联条件一定要写在子查询中

我们再次使用，上面的表别名错误的 Mysql 查询语句，把其中的 关联条件移动到主查询中 ，运行看看结果

SELECT e1.`last_name`, e1.`salary`, e1.`department_id`
FROM employees e1 
WHERE e1.`department_id` = e2.`department_id` /* 将子查询中的关联条件移动到了主查询当中使用*/
AND e1.`salary` > (
		SELECT AVG(salary)
		FROM employees e2 );

在这里插入图片描述

运行结果报错，说 Mysql 不认识 e2.department_id 这个东西。

为什么会报出这样的错误

这里就涉及到了 关联名称的作用域 了，同 Java 一样 Mysql也是存在作用域的，我们从Java中的学习过可以知道，一个变量出了作用域，就不可以再使用该变量了。被系统回收了。

同样我们的 Mysql也是一样的，一个子查询中的字段出了作用域，就无法再使用了，就是说子查询中的 关联名称(字段属性) 只能在子查询当中的括号内使用，出了括号使用子查询内部的字段的话，就会报错，在关联子查询中，子查询的结果是作为一个临时表存在的，当子查询的结果，返回给了主查询，其该子查询创建的临时表也就不存在了。因此你也就无法在外部(主)查询中使用该子查询的字段(属性)了，因为该子查询临时表已经不存在了，你又如何可以访问该子查询临时表中的字段呢

在这里插入图片描述

4.4 关联子查询中 : EXISTS 与 NOT EXISTS关键字

关联子查询 通常也会和 EXISTS 操作符一起来使用，用来检查在子查询中是否存在满足条件的行，在子查询中存在满足该条件的行，返回 true ,如果在子查询中不存在满足条件的行：返回 false ,

EXISTS : 在关联子查询中，满足关联条件的返回 true ,不满足条件的，返回 false ,基本上可以使用表连接代替使用

NOT EXISTS : 在关联子查询中，返回不满足关联条件的结果

实例: 题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

SELECT employee_id, last_name, job_id,department_id
FROM employees e1
WHERE EXISTS (  /* 返回满足关联条件的结果*/
        SELECT *
        FROM employees e2
        WHERE e2.`employee_id` =e1.`employee_id` );

在这里插入图片描述

方法二可以使用自连接

SELECT e1.employee_id, e2.last_name, e2.job_id, e2.department_id
FROM employees e1
JOIN employees e2
ON e1.`employee_id` = e2.`employee_id`;

在这里插入图片描述

NOT EXISTS 的使用 题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT dep.`department_id`, dep.`department_name`
FROM departments dep
WHERE NOT EXISTS (
		SELECT *
		FROM employees emp
		WHERE emp.`department_id` = dep.`department_id`);

在这里插入图片描述

同样我们也可以使用表连接的方式2

SELECT dep.`department_id`, dep.`department_name`
FROM departments dep
LEFT JOIN employees emp
ON dep.`department_id` = emp.`department_id`
WHERE emp.`department_id` IS NULL;   /* emp.`department_id` IS NULL 为空表连接就不会取到该字段*/

在这里插入图片描述

建议：在可以使用子查询，或者使用自连接都可以解决问题的情况下，建议使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。可以这样理解：子查询实际上是通过未知表进行查询后的条件的判断，而自连接时通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化

五. 子查询的关联练习实例

由于涉及的可能篇幅过多，所以想要了解的大家可以移步到 🔜🔜🔜 子查询相关练习

最后:

限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布