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

一、子查询概念

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

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

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

扩展：下面与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'的任意工资 
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'

查询其它工种的员工号、姓名、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';

方式二
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

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

/* @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：简单示例

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