Mysql查询相关知识（进阶七：子查询， 进阶八：分页查询，进阶九：联合查询）

---

前言

上次说到了MySQL的查询进阶五和六，这次继续讨论查询的相关的知识
链接: https://pan.baidu.com/s/1a_wstxPVhxeliZkt6-BTiw
提取码: gaj8

进阶七：子查询

含义：

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句称为主查询或外查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面*
标量子查询（单行）√
列子查询（多行）√
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一行多列）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

一、where或having后面

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

特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着单行操作符（>,<,=等等）使用

列子查询，一般搭配这多行操作符（in,any/some,all）使用
4.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/

1.标量子查询

#案例1、谁的工资比Abel高？
#①查询Abel的工资
SELECT salary
FROM `employees`
WHERE `last_name`='Abel';
#②查询员工的信息，满足salary>①结果
SELECT *
FROM `employees`
WHERE salary>(
	SELECT salary
	FROM `employees`
	WHERE `last_name`='Abel'
);
#案例2：返回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
);
#案例3：返回公司工资最少的员工的姓名，`job_id`和`salary`
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE salary=(
	SELECT MIN(salary)
	FROM `employees`
);
#案例4、查询最低工资大于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
);

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

#案例1、返回`location_id`是1400或1700的部门中的所有的员工的姓名
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
	SELECT DISTINCT department_id
	FROM `departments`
	WHERE `location_id` IN (1400,1700)
);
#案例2、返回其他工种中比`job_id`为‘IT＿PROG’工种任意工资低的员工的：工号，姓名，`job_id`以及`salary`
SELECT `employee_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';
#案例3、返回其他工种中比`job_id`为‘IT＿PROG’工种所有工资低的员工的：工号，姓名，`job_id`以及`salary`
SELECT `employee_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';
或者
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE salary < (
	SELECT MIN(`salary`)
	FROM `employees`
	WHERE `job_id`='IT_PROG'
)
AND `job_id`<>'IT_PROG';

3.行子查询

（结果集一行多列或多行多列）(使用此方法的时候是所有的信息都可以用一个判断符号表示，此方法也可用其他的子查询得到)
#案例、查询员工编号最小且工资最高的员工的信息
SELECT *
FROM `employees`
WHERE (`employee_id`,`salary`)=(
	SELECT MIN(`employee_id`),MAX(salary)
	FROM `employees`
);

二、放在SELECT的后面(仅仅支持标量子查询（也就是一行一列）)

#案例、查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM `employees` e
	WHERE e.`department_id`=d.`department_id`
)个数
FROM `departments` d;
#案例、查询员工号=102的部门名
SELECT `department_name`
FROM `departments` d,`employees` e
WHERE d.`department_id`=e.`department_id`
AND e.`employee_id`=102;

三、放在FROM的后面(意思就是把查询的结果当作表来进行使用（要求必须起别名）)

#案例、查询每个部门的平均工资的等级
SELECT biao.*,j.`grade_level`
FROM (
	SELECT  AVG(salary) ag,`department_id`
	FROM `employees` 
	GROUP BY `department_id`
)biao
INNER JOIN `job_grades` j
ON biao.ag BETWEEN j.`lowest_sal` AND `highest_sal`;

四、EXISTS后面（相关子查询）

/*
语法：
	exists（完整的查询语句）
结果：
	1或0

*/
#案例、查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
	SELECT *
	FROM `employees` e
	WHERE e.`department_id`=d.`department_id`
);

进阶八：分页查询

相关概念

/*
应用场景：
当要显示的数据，一页显示不全，需要分页提交SQL请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset】，size；
offset 要显示条目的起始索引（起始索引从0开始）
size要显示的条目个数
特点：
1.limit语句放在查询语句的最后
2.公式：
要显示的页数 page，每页的条目数size

	select 查询列表
	from 表
	limit （page-1）*size，size

*/

案例

 #案例，查询前五条的员工的信息
 SELECT *
 FROM `employees`
 LIMIT 0,5
 或者
 SELECT *
 FROM `employees`
 LIMIT 5
 #要查询11到25条
 SELECT *FROM `employees` LIMIT 10,15;
 #有奖金的员工的信息，并且显示出较高的工资的前面10条的信息
 SELECT *
 FROM `employees`
 WHERE `commission_pct` IS NOT NULL
 ORDER BY salary DESC 
 LIMIT 10;

进阶九：联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果

语法： 查询语句1 union 查询语句2 union …

应用场景： 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致 需要注意的点：
1.要求多条查询语句的列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重，如果使用union all可以包含重复项