mysql系列——子查询（非常重要）（八）

一、子查询概念

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

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

二、子查询分类

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

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

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

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

3、准备测试数据

浏览器中打开链接：http://www.itsoku.com/course/3/196

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

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

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

 

selec后面的子查询

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

1、查询每个部门员工个数

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

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,  t1.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、特点

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般与单行操作符搭配使用。单行操作符：>、<、>=、<=、=、<>、!=
• 列子查询，一般与多行操作符搭配使用
• 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果

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

　　in (not in) : 列表中"任意一个"，in常用于where表达式中，其作用是查询某个范围内的数据

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

4、标量子查询

一般标量子查询，示例：查询谁的工资比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和工资

查询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=@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);

子查询+分组函数，示例：查询最低工资大于50号部门最低工资的部门id和其最低工资

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

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

在查询二的基础上，满足查询一
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);

错误的标量子查询，示例：将上面示例中,子查询语句中的min(salary)改成salary，执行效果如下：

错误提示：子查询返回的结果超过了1行记录。
说明：上面的子查询只支持最多一行一列记录，即标量子查询。

列子查询

列子查询需搭配多行操作符使用：in(not in)、any/some、all，为提升效率，最好用distinct 关键字去重一下。

示例1：返回location_id是1400或1700的部门中所有员工姓名方式一

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

方式二
SELECT a.last_name FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id FROM  departments
			     WHERE location_id IN (1400, 1700));

方式三
SELECT a.last_name FROM  employees a
WHERE a.department_id = SOME (SELECT DISTINCT department_id FROM departments
			      WHERE location_id IN (1400, 1700));

示例2：返回其他工种中比job_id为’IT_PROG’工种任意（任何一个）工资低的员工的员工号、姓名、job_id、salary

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

查询其它工种的员工号、姓名、job_id、salary <方式一的任意一个
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';

方式二
SELECT last_name,employee_id,job_id,salary from employees
WHERE salary < (SELECT max(salary) FROM employees e WHERE job_id = 'IT_PROG')
AND job_id != 'IT_PROG';

示例3：返回其他工种中比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';

行子查询

示例1：查询员工编号最小并且工资最高的员工信息，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   a.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 = 30000000) as 'exists返回1或0';
+-------------------+
| exists返回1或0    |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

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

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

三、NULL的大坑

示例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、建议创建表的时候，列不允许为空