MySQL学习总结（四）DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询

2023-01-07 14:53:22

一、子查询

（一）概念

出现在其他语句内部的SELECT语句，称为子查询或内查询；内部嵌套其他SELECT语句的查询，称为外查询或主查询

（二）分类

按子查询出现的位置：
- SELECT后面：仅支持标量子查询
- FROM后面：支持表子查询
- WHERE或HAVING后面：支持标量子查询、列子查询、行子查询
- EXISTS后面（相关子查询）：支持表子查询
按子查询结果集的行列数不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集为多行多列或一行多列）
- 表子查询（结果集一般为多行多列）

（三）WHERE或HAVING后面的子查询

支持标量子查询、列子查询、行子查询

特点：

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用（> < >= <= = <>）
列子查询，一般搭配着多行操作符使用（in/not in、any/some、all）
子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询

一般搭配着单行操作符使用（> < >= <= = <>）

案例1：谁的工资比Abel高？

#1.查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
#2.查询员工的信息，满足salary>1中的结果
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'#子查询没有分号
);

案例2：返回工种号与141号员工相同，且工资比143号员工多的员工姓名，工种号和工资

#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.查询员工的姓名，工种号和工资，要求工种号=1中结果并且salary>2中结果
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：返回公司工资最低的员工的last_name,job_id和salary

#1.查询公司的最低工资
SELECT MIN(salary)
FROM employees;
#2.查询last_name,job_id和salary，要求salary=1中结果
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

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

#1.查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#2.查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#3.在2的基础上筛选，满足MIN(salary)>1中结果的
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
)

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

返回多行
使用多行比较操作符

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

案例1：返回位置编号是1400或1700的部门中的所有员工姓名

#1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
#2.查询员工姓名，要求部门号是1中结果中的任意一个
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#方式二：也可以用=ANY代替IN
SELECT last_name
FROM employees
WHERE department_id =ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

案例2：返回位置编号不是1400，也不是1700的部门中的所有员工姓名

SELECT last_name
FROM employees
WHERE department_id NOT IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#方式二：NOT IN也可以用<>ALL替换
SELECT last_name
FROM employees
WHERE department_id <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

案例3：查询其他工种中比工种号为’IT_PROG’的工种中某一工资要低的员工的员工号、姓名、工种号以及工资

#1.查询job_id为'IT_PROG'部门所有工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
#2.查询其他工种中比工种号为'IT_PROG'的工种中某一工资要低的员工的员工号、姓名、工种号以及工资，满足salary<1中的任意一个结果
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	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';

案例4：查询其他部门中比工种号为’IT_PROG’的部门所有工资都要低的员工的员工号、姓名、工种号以及工资

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

小结：
IN可以用=ANY替换
NOT IN可以用<>ALL替换
<ANY可以用<MAX替换
>ANY可以用>MIN替换
<ALL可以用<MIN替换
>ALL可以用>MAX替换

3.行子查询

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

#1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
#2.查询最高工资
SELECT MAX(salary)
FROM employees;
#3.查询员工信息，满足员工编号=1中结果且工资=2中结果
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
);

（四）SELECT后面的子查询

仅支持标量子查询

案例：查询每个部门的部门信息以及员工个数

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.department_id
) 个数
FROM departments d;

（五）FROM后面的子查询

支持表子查询：将子查询结果充当一张表，要求必须起别名

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

#1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.连接1的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_level
FROM(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

（六）EXISTS后面的子查询

支持表子查询

EXISTS（即是否存在）
语法：EXISTS(完整的查询语句)，结果返回1或0

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

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id=e.department_id
);
#方式二：
SELECT department_name
FROM departments d
WHERE d.department_id IN(
	SELECT department_id
	FROM employees 
);

练习题1：查询各部门中工资高于本部门平均工资的员工的员工号、姓名和工资

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2.连接1中的结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id=ag_dep.department_id
WHERE salary>ag_dep.ag;

练习题2：查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

#1.查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
#2.查询部门号=1中结果的任意一个员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

练习题3：查询姓为K_ing的领导的手下的员工姓名和工资

#1.查询姓为K_ing的员工编号
SELECT employee_id
FROM employees
WHERE last_name='K_ing'
#2.查询哪个员工的manager_id=1中结果
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name='K_ing'
);

练习题4：查询工资最高的员工的姓名，要求first_name和last_name显示为一列，用“_”隔开

#1.查询最高工资
SELECT MAX(salary)
FROM employees;
#2.查询工资=1中结果的姓名
SELECT CONCAT(first_name,'_',last_name) 姓名
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

练习题5：查询平均工资高于公司平均工资的部门有哪些

#1.查询平均工资
SELECT AVG(salary)
FROM employees;
#2.查询每个部门的平均工资,满足>1中结果
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);

练习题6：查询出公司中所有领导的详细信息

#1.查询所有领导的编号manager_id
SELECT DISTINCT manager_id
FROM employees
#2.查询领导的详细信息，满足employee_id=1中结果
SELECT *
FROM employees
WHERE employee_id=ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

练习题7：查询每个专业的男生人数和女生人数分别是多少
已知表：

SELECT majorid,
(SELECT COUNT(*) FROM student WHERE sex='男' AND majorid=s.majorid) 男,
(SELECT COUNT(*) FROM student WHERE sex='女' AND majorid=s.majorid) 女
FROM student s
GROUP BY majorid;

二、分页查询

应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
格式：

SELECT 查询列表
FROM 表1
【连接类型 JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段】
LIMIT offset,size;

offset表示要显示条目的起始索引（这里的起始索引从0开始）
size表示要显示的条目个数

特点：

LIMIT语句要放在查询语句的最后
公式：假设已知要显示的页数page和每页的条目数size
那么就是LIMIT (page-1)*size,size;

案例1：查询前五条员工信息

SELECT * FROM employees LIMIT 0,5;
#当要显示的起始索引是0时可以省略
SELECT * FROM employees LIMIT 5;

案例2：查询第11条—第25条

SELECT * FROM employees LIMIT 10,15;

案例3：将有奖金的且工资较高的前10名员工的员工信息显示出来

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

练习题1：说出查询语句中的执行先后顺序

SELECT 查询列表			七
FROM 表1				一
连接类型 JOIN 表2		二
ON 连接条件				三
WHERE 筛选条件			四
GROUP BY 分组字段		五
HAVING 分组后的筛选		六
ORDER BY 排序的字段		八
LIMIT 偏移,条目数;		九

练习题2：查询平均工资最低的部门信息
方式一：子查询

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.查询1中结果的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep;
#3.查询哪个部门的平均工资=2中结果
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	)ag_dep
);
#4.查询部门信息
SELECT d.*
FROM departments d
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		)ag_dep
	)
);

方式二：分页查询+子查询

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#3.查询部门信息
SELECT *
FROM departments 
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

练习题3：查询平均工资最低的部门信息和该部门的平均工资

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#3.查询部门信息和平均工资
SELECT d.*,ag
FROM departments d
JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1;
) ag_dep
ON d.department_id=ag_dep.department_id;

练习题4：查询平均工资最高的工种信息

#1.查询平均工资最高的工种编号
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2.查询工种信息
SELECT *
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
)

练习题5：所有部门中最高工资最低的那个部门的最低工资是多少

#1.查询各部门的最高工资最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
#2.查询1中结果的那个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

练习题6：查询平均工资最高的部门的领导的详细信息

#1.查询平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#2.将employees和departments连接查询，筛选条件是1中结果
SELECT *
FROM employees e
INNER JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

三、联合查询

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

格式：

查询语句1
UNION
查询语句2
UNION
...
查询语句n

案例1：查询部门编号>90或邮箱包含a的员工信息
方式一：

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

方式二：

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：

要求多条查询语句的查询列数是一致的！
要求多条查询语句的每一列类型和顺序最好一致
UNION关键字默认去重，如果使用UNION ALL可以包含重复项

案例2：查询中国用户中男性的信息以及外国用户中
已知有如下表：

SELECT c_id,c_name,c_sex FROM c_user WHERE c_sex='男'
UNION 
SELECT f_id,f_name,f_sex FROM f_user WHERE f_sex='male';

码农公寓