Mysql——子查询（非常重要）（十二）

本篇非常重要。

一、子查询概念

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

• 外部的select查询语句，称为主查询或外查询

二、子查询分类

1、按照结果集的行列数不同分为4种

• 标量子查询（结果集为一行一列）
• 列子查询（结果集为一列多行）
• 行子查询（结果集为一行多列）
• 表子查询（结果集为多行多列）

2、按照子查询出现主查询中的不同位置分为4种

• select后面 ：仅仅支持标量子查询（一行一列）
• from后面 ： 支持表子查询（多行多列）
• where或having后面 ： 支持标量子查询（一行一列）、列查询（一列多行）、行子查询（一行多列）
• exists后面（即相关子查询） ：支持表子查询（多行多列）

3、准备测试数据

测试数据比较多，读者可以去路人甲Java的个人博客上得到执行脚本。

浏览器中打开链接：http://www.itsoku.com/article/209

mysql中执行里面的 javacode2018_employees 库部分的脚本。

成功创建此库后，会有以下5张表：

表名	描述
departments	部门表
employees	员工信息表
jobs	职位信息表
locations	位置表（部门表会用到）
job_grades	薪资等级表

selec后面的子查询

子查询位于select后面的，仅仅支持标量子查询（即一行一列）

1、示例1

• 查询每个部门员工个数
  

2、示例2

• 查询员工号=102的部门名称
  

from后面的子查询

将子查询的结果集充当一张表，要求必须 起别名，否则这个表找不到。

然后将真实的表和子查询结果表进行连接查询。

1、示例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,
	avg(a.salary) sa,
	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 highest_sal;
	

运行结果如下：

where和having后面的子查询

1、where或having后面的子查询，可以使用：

• 标量子查询（单行单列子查询）
• 列子查询（单列多行子查询）
• 行子查询（单行多列子查询）

2、特点

• （1）子查询放在小括号内
• （2）子查询一般放在条件的右侧
• （3）标量子查询，一般与单行操作符搭配使用。单行操作符：>、<、>=、<=、=、<>、!=
• （4）列子查询，一般与多行操作符搭配使用

1)、in (not in): 列表中"任意一个"
2)、any或者some: 和子查询返回的"某一个值"比较。例如：a>some(10,20,30)，a大于子查询中的任意一个即可，a大于子查询中最小值即可，其等同于a>min(10,20,30)。
3)、all: 和子查询返回的"所有值"比较。例如：a>all(10,20,30)，a大于子查询中的所有值，即a大于子查询中的最大值，其等同于a>max(10,20,30)。

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

3、Mysql中的in、any、some、all

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

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

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

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

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

4、标量子查询

(1)、一般标量子查询，示例

• 查询谁的工资比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');

(2)、多个标量子查询，示例

• 返回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 as '姓名',
	a.job_id,
	a.salary as '工资'
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);

(3)、子查询+分组函数，示例

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

/* @1:查询50号部门的最低工资 */
SELECT
	min(salary)
FROM
	employees
WHERE
	department_id = 50;

/* @2:查询每个部门的最低工资 */
SELECT
	department_id,
	min(salary) as '最低工资'
FROM
	employees
GROUP BY
	department_id;

/* @3:在@2的基础上，满足min(salary)>@1 */
SELECT
	a.department_id,
	min(a.salary) as '最低工资'
FROM
	employees a
GROUP BY
	a.department_id
HAVING
	min(a.salary) > (SELECT
						min(salary)
					FROM
						employees
					WHERE
						department_id = 50);

(4)、错误的标量子查询，示例

• 将上面的示例@3中子查询语句中的min(salary)，执行效果如下：
  
  错误提示：子查询返回的结果超过了1行记录。
  说明：上面的子查询只支持最多一行一列记录，即标量子查询。

5、列子查询

列子查询需要搭配多行操作符使用：in(not in)、any/some、all。

为了提升效率，最好使用关键字 distinct 去重一下。

(1)、示例1

• 返回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));

/* 方式3：使用some实现*/
SELECT
	a.last_name
FROM
	employees a
WHERE 
	a.department_id = SOME (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)、示例2

• 返回其他工种中比job_id为’IT_PROG’工种任意工资低的员工的员工号、姓名、job_id、salary

/* 方式1 */
/* @1:查询job_id为'IT_PROG'的任意工资 */
SELECT
	DISTINCT salary
FROM
	employees
WHERE
	job_id = 'IT_PROG'

/* @2:查询其它工种的员工号、姓名、job_id、salary < @1的任意一个 */
SELECT
	last_name,
	employee_id,
	job_id,
	salary
FROM
	employees	
WHERE
	salary < ANY/SOME (SELECT
						 	DISTINCT salary
						FROM
							employees
						WHERE
							job_id = 'IT_PROG')
AND
	job_id != ''IT_PROG;

/* 方式2 */
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)、示例3

• 返回其他工种中比job_id为’IT_PROG’工种所有工资低的员工的员工号、姓名、job_id、salary

/* 方式1 */
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;

/* 方式2 */
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;

6、行子查询

(1)、示例1

• 查询员工编号最小并且工资最高的员工信息，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 
	a.salary = (SELECT
					max(salary)
			  	FROM
					employees);

/* @4:方式2 */
SELECT 
	*
FROM
	employees a
WHERE
	(a.employee_id, a.salary) = (SELECT
									min(employee_id),
									max(salary)
								 FROM employees);

/* @5:方式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)、示例1

• 简单示例

mysql> select exists (select employee_id
     				  from employees
     				  where salary = 30000000) as 'exists返回1或0';
+-------------------+
| exists返回1或0    |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

(2)、示例2

• 查询所有员工的部门名称

/* exists入门案例 */
mysql> select exists (select employee_id
     				  from employees
     				  where salary = 30000000) 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)、示例3

• 查询没有员工的部门

/* exists实现 */
SELECT
	department_name
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
	department_name
FROM
	departments a
WHERE 
	a.department_id NOT IN (SELECT department_id
			  	       FROM employees
			  	       WHERE b.department_id IS NOT NULL);

上面脚本中有 b.department_id IS NOT NULL，为什么？有大坑，往下看。

三、NULL的大坑

1、示例1

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

SELECT
	 *
FROM
	 departments a
 WHERE
 	 a.department_id NOT IN (select department_id from employees);

运行结果如下：

mysql> select *
    -> from departments a
    -> where a.department_id not in (select department_id from employees);
Empty set (0.02 sec)

in的情况下，子查询中列的值为NULL的时候，外查询的结果为空。

建议：建表时，列不允许为空

总结
1、本文中讲解了常见的子查询，请大家务必多练习
2、注意in、any、some、any的用法
3、字段为空的时候，in查询有大坑，这个要注意
4、建议创建表的时候，列不允许为空