9.子查询

#DQL语言
##八、子查询
###1.什么是子查询？
####子查询的分类
按结果集的行列数不同分类：
　　　　 ·标量子查询（结果集只有一行一列，也称“单行子查询”）
　　　　 ·列子查询（结果集只有一列多行，也称“多行子查询”）
　　　　 ·行子查询（结果集只有一行多列）
　　　 　·表子查询（结果集一般为多行多列）

按子查询出现的位置分类：　　　　
　　　　 ·select后面：仅仅支持标量子查询；
　　　　 ·from后面：支持表子查询
　　　　 ·where或having后面：
　　　　 　　　　　标量子查询(★)
　　　　 　　　　　列子查询(★)
　　　　 　　　　　行子查询
　　　　 ·exist后面：（也称为“相关子查询”）
　　　　 　　　　　表子查询
　　　　 　　　　　列子查询
　　　　 　　　　　行子查询
　　　　 　　　　　标量子查询

标了(★)的是最用的最多、最重要的子查询，下面我们一一介绍，先介绍最重要的！

###2.子查询语法
####①where或having后面的子查询

先说特点:
　　　　　a.子查询放在小括号内
　　　　　b.子查询一般放在条件的右侧
　　　　　c.标量子查询，一般搭配着单行操作符使用(这里的所谓单行操作符即指基本运算符，如：> < >= <= = <>)
　　　　　d.列子查询，则一般搭配着多行操作符使用(如：in、any/some、all)
　　　　　e.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

#####⑴标量子查询(结果集为一行一列)

案例一：谁的工资比Abel高

分两步走：
Step1：查询Abel的工资

SELECT 
    salary 
FROM
    employees 
WHERE last_name = 'Abel'

结果如下↓

大家看，标量子查询的结果集的确只有一行一列吧~

Step2：查询工资比Step1结果高的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary > 
  (SELECT                              //接下来这几行都是Step1的语句
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;

OK，最终结果如下↓

案例二：返回job_id与141号员工相同，salary比143号员工多的员工姓名、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=143
);

案例三：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT
	MIN(salary)
FROM
	employees
	
);

那么，前三个案例都是在where后面跟着的标量子查询，接下来的案例就是跟在having后面的标量子查询

案例四：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id =50
);

#####⑵列子查询(结果集为一列多行)
我们先前讲特点的时候已经说过：列子查询，则一般搭配着多行操作符使用(如：in、any/some、all)
这三者区别如下：

操作符	含义
IN/NOT IN	等于列表中的任意一个
ANY/SOME	和子查询返回的某一个值进行比较
ALL	和子查询返回的所有值进行比较

具体来说
　　　　a.若字段a in(10,20,30)表示只要a为10或20或30之间的某一个既可以成功查询，那么现在针对于列子查询而言，in()括号里面的参数只不过是替换成了一个列表而已，即“一列多行”；
　　　　b.若字段a<ANY(10,20,30)的话表示a比ANY参数中任意一个数小即可成功查询，“比其中任意一个数小”相当于a不能超过参数列表的最大值，我们也可以用a<max(10,20,30)表示；同理，若字段a>ANY(10,20,30),也即是意味着a>min(10,20,30) //这一点很容易混淆，希望大家仔细琢磨琢磨
　　　　c.若字段a>ALL(10,20,30)即是意味着a>max(10,20,30);若字段a<ALL(10,20,30)即是意味着a<min(10,20,30)，这应该很好理解

下面请看案例

案例一：返回location_id是1400或1700的部门中的所有员工姓名
分两步走:

Step1 →查询location_id是1400或1700的部门

SELECT DISTINCT department_id                //一般为了去重我们加一下distinct
FROM departments
WHERE location_id IN(1400,1700)

结果如下

大家可见，结果集显然是一列多行吧！

Step2

SELECT last_name
FROM employees
WHERE department_id IN(                      //只要部门id是Step1结果中的某一个即可
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

PS:语句中的IN可以用“=ANY”替换！(道理大家简单想想就好了)

案例二：返回其他job_id中比job_id为’IT_PROG’工种任一工资低的员工的：工号、姓名、job_id以及salary

分两步走:
Step1 →查询job_id为’IT_PROG’工种员工的工资

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'

Step1

SELECT job_id,last_name,job_id,salary
FROM employees
WHERE salary <ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG';         //注意是返回其他job_id...所以我们添加一个筛选条件

那假如我们不用ANY去表示，我们也可以这样表示

SELECT job_id,last_name,job_id,salary
FROM employees
WHERE salary <(              //去掉ANY
SELECT MAX(salary)           //小于其中任意一个值，那我们大于最大值好不好？明显不好，那我们只需要小于最大值是不是就可以了~
FROM employees
WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG';

案例三：返回其他job_id中比job_id为’IT_PROG’工种所有工资低的员工的：工号、姓名、job_id以及salary

(这题显然用ALL就解决啦，和上题差不多，查询语句如下)

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

同样我们不用ALL的话也可以这样写

SELECT job_id,last_name,job_id,salary
FROM employees
WHERE salary <(
SELECT min(salary)             //小于最小值ok啦
FROM employees
WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG';

#####⑶行子查询(结果集为一行多列)

先抛出案例

案例：查询员工编号最小并且工资最高的员工信息

我们一般的写法是这样的

SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);

那我们也可以用行子查询语句

SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);

结果如下

显然结果集是一行多列吧~

PS：大家也可以看到这样做存在局限性，那就是只有当employee_id和salary同时后面跟的是同一种运算符时才行，这道题是“=”。

####②select后面的子查询(仅支持标量子查询，结果集只能为一行一列)

直接给出案例
案例一：查询每个部门的员工个数（这道题也可以用分组查询，但我们在这儿用子查询解决）

SELECT department_id,(
SELECT COUNT(*)                         //注意：select后的子查询的结果集必须是一行一列！！！
FROM employees e 
WHERE e.`department_id`=d.`department_id`
) 个数
FROM departments d;

案例二：查询员工号=102的部门名

SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 结果;

####③from后面的子查询(支持表子查询)

from后面的子查询：将我们的子查询结果充当一张表，要求必须起别名！！！
以例题为例讲解from后面的子查询：

案例：查询每个部门的平均工资的工资等级

两步走
Step1：查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

执行结果如下

那么大家可以看到我们得到了一个新的表，这非常关键

Step2：查询步骤1生成的表的工资等级

SELECT 新表.*,g.grade_level
FROM (
	SELECT AVG(salary) 平均工资,department_id
	FROM employees
	GROUP BY department_id
) 新表                               //★★★一定要给新生成的表起别名！！！否则就不知道该表怎么表示
INNER JOIN job_grades g
ON 新表.平均工资 BETWEEN g.lowest_sal AND highest_sal;

ok啦，结果如下：

####④exist后面的子查询/相关子查询(支持表子查询、行子查询、列子查询、标量子查询)

先提前说一下，凡是能用exist子查询的，都可以用in（列子查询）去查

案例一：查询有员工的部门名

用exist

SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE e.`department_id`=d.`department_id`
);

假如用in怎么写？

SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees e
WHERE e.`department_id`=d.`department_id`
);

之所以说exist后面都支持表子查询、行子查询、列子查询、标量子查询这几种查询是因为它只返回有无结果，查到了就返回，查不到就不返回，所以和后面是什么类型的查询都无关

下面是子查询的几道例题

案例一：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

SELECT employee_id,last_name,salary
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id              //一定要为AVG(salary)起别名，否则无法判断是employees表中的还是“表”中的
	FROM employees m
	GROUP BY department_id
) 表
ON e.`department_id`=表.department_id 
WHERE e.salary > 表.ag;

案例二：查询和姓名中包含字母u的员工在相同部门的员工的员工名和姓名

SELECT DISTINCT employee_id,e.last_name
FROM employees e
INNER JOIN (
	SELECT department_id,last_name
	FROM employees 
	WHERE last_name LIKE '%u%' 
) tab
ON e.department_id=tab.department_id;

案例三：查询在部门的location_id为1700的部门工作的员工的员工号

SELECT e.employee_id
FROM employees e
WHERE department_id IN(
SELECT department_id
FROM departments d
WHERE d.`location_id`=1700
);

以上就是子查询的内容，下一节将介绍分页查询！