玩转Mysql系列 - 第12篇：子查询（非常重要，高手必备）(1)

| 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后面，可以使用

1. 标量子查询（单行单列行子查询）

2. 列子查询（单列多行子查询）

3. 行子查询（多行多列）

特点

1. 子查询放在小括号内。

2. 子查询一般放在条件的右侧。

3. 标量子查询，一般搭配着单行操作符使用，多行操作符   >、<、>=、<=、=、<>、!=

4. 列子查询，一般搭配着多行操作符使用

in(not in)：列表中的“任意一个”

any或者some：和子查询返回的“某一个值”比较，比如a>som(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。

all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

5. 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

mysql中的in、any、some、all

in，any，some，all分别是子查询关键词之一。

in：in常用于where表达式中，其作用是查询某个范围内的数据

any和some一样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

下文中会经常用到这些关键字。

标量子查询

一般标量子查询，示例

查询谁的工资比Abel的高？

/①查询abel的工资【改查询是标量子查询】/

SELECT salary

FROM employees

WHERE last_name = ‘Abel’;

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

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和工资/

/①查询141号员工的job_id/

SELECT job_id

FROM employees

WHERE employee_id = 141;

/②查询143好员工的salary/

SELECT salary

FROM employees

WHERE employee_id = 143;

/③查询员工的姓名、job_id、工资，要求job_id=① and salary>②/

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】/

/①查询50号部门的最低工资/

SELECT min(salary)

FROM employees

WHERE department_id = 50;

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

SELECT

min(salary),

department_id

FROM employees

GROUP BY department_id;

/③在②的基础上筛选，满足min(salary)>①/

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);

错误的标量子查询，示例

将上面的示例③中子查询语句中的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/

/①查询location_id是1400或1700的部门编号/

SELECT DISTINCT department_id

FROM departments

WHERE location_id IN (1400, 1700);

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

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/

/①查询job_id为’IT_PROG’部门任-工资/

SELECT DISTINCT salary

FROM employees

WHERE job_id = ‘IT_PROG’;

/②查询员工号、姓名、job_id、salary，slary<①的任意一个/

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种方式。

/查询员工编号最小并且工资最高的员工信息/

/①查询最小的员工编号/

SELECT min(employee_id)

FROM employees;

/②查询最高工资/

SELECT max(salary)

FROM employees;

/③方式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后面（也叫做相关子查询）

1. 语法：exists(玩转的查询语句)。

2. exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。

3. 一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。

4. 和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。

示例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

使用in的方式查询没有员工的部门，如下：

SELECT *

FROM departments a

WHERE a.department_id NOT IN (SELECT department_id

FROM employees b);

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

总结

在这里，由于面试中MySQL问的比较多，因此也就在此以MySQL为例为大家总结分享。但是你要学习的往往不止这一点，还有一些主流框架的使用，Spring源码的学习，Mybatis源码的学习等等都是需要掌握的，我也把这些知识点都整理起来了

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。**[外链图片转存中…(img-sHZ9kSTV-1713756103040)]

[外链图片转存中…(img-nOHB1BvB-1713756103040)]

[外链图片转存中…(img-Qq3qYWqR-1713756103040)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

总结

在这里，由于面试中MySQL问的比较多，因此也就在此以MySQL为例为大家总结分享。但是你要学习的往往不止这一点，还有一些主流框架的使用，Spring源码的学习，Mybatis源码的学习等等都是需要掌握的，我也把这些知识点都整理起来了

[外链图片转存中…(img-mjOIh0bw-1713756103041)]

[外链图片转存中…(img-IYcjahek-1713756103041)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！