MySQL的基础指令DML指令三（查询完结）

前言

以下内容，只考虑sql99语法

一、合并查询结果-UNION关键字

前言

• 使用UNION和UNION ALL关键字，要求两个表对应的列数和数据类型必须相同，并且相互对应。

1.UNION的分类

1.1 UNION

1.1.1 基本概念

• UNION操作符返回两个查询的结果集的并集，去除重复记录

1.1.2 基本的写法

• 查询中国用户中男性的信息以及美国用户中年男性的用户信息

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

1.2 UNION ALL

1.2.1 基本概念

• UNION ALL操作符返回两个查询的结果集的并集，不去除重复记录

1.2.2 基本的写法

• 查询部门编号>90或邮箱包含a的员工信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

1.3 两者的使用

• 执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率

二、多表查询（sql99语法）

分类一：等值连接和非等值连接

1.等值连接

• 假设有一张员工表和一张部门表，员工表中有两个字段分别为姓名和部门id，部门表中有两个字段为部门id和部门名称，我们需要查出，员工名和员工所在的部门名

SELECT e.last_name, d.department_id
FROM employees e
JOIN department d
ON e.department_id = d.department_id;

2.非等值连接

• 假设有一张员工表和一张工资等级表，员工表中有两个字段分别为姓名和工资，工资等级表中有三个字段为工资等级、该等级最低工资和该等级最高工资，我们需要查出员工名和对应工资等级

SELECT e.last_name, g.job_grade
FROM employees e
JOIN grades g
ON e.salary between low_salary and high_salary;

分类二：自连接和非自连接

1.自连接

• 假设有一个员工表，表中有三个字段，分别是员工id、员工领导的id、员工姓名，要求查出员工和其领导的姓名

SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.id

2.非自连接

• 不是自己和自己进行连接的就是非自连接

分类三：内连接和外连接

前言

• sql92和sql99都存在外连接的写法
• MySQL不支持sql92中外连接写法
• MySQL不支持sql99中的全外连接写法

1.内连接

1.1 基本概念

• 内连接的查询结果为主表和从表中可以匹配的上的记录

1.2 基本的写法

SELECT 字段列表
FROM A表  INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

• INNER关键字可以省略

2.外连接

2.1 左外连接

2.1.1 基本概念

• 左外连接的查询结果为左表中的所有记录，如果右表中有和它匹配的，则右表显示匹配的值；如果右表中没有和它匹配的，则右表显示null。
• 总结：左外连接查询结果=内连接的结果+左表中有而右表中没有的记录

2.1.2 基本的写法

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

• OUTER关键字可以省略

2.2 右外连接

2.2.1 基本概念

• 右外连接的查询结果为右表中的所有记录，如果左表中有和它匹配的，则左表显示匹配的值；如果左表中没有和它匹配的，则左表显示null。
• 总结：右外连接的查询结果=内连接的结果+右表中有而左表中没有的记录

2.2.2 基本的写法

#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

• OUTER关键字可以省略

2.3 全外连接

2.3.1 基本概念

• 全外连接的结果包含两个表中全部记录
• 总结：全外连接的结果为：内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的

2.3.2 sql99提供的写法

SELECT 字段列表
FROM A表 FULL OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

2.3.3 MySQL实现的写法

SELECT 字段列表 FROM A表 WHERE 等其他子句
UNION
SELECT 字段列表 FROM B表  WHERE 等其他子句;

三、SQL执行流程

1.SELECT语句的完整结构（sql99语法）

SELECT ...
FROM...
(LEFT / RIGHT)JOIN...
ON...多表连接的条件
WHERE...分组前的过滤条件
GROUP BY...
HAVING...分组后的过滤条件
ORDER BY...(ASC/DESC)
LIMIT...

2.SQL语句的执行过程

FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT的字段 -> DISTINCT -> ORDER BY -> LIMIT

3. SQL语句的执行解析

• 第一步：执行FROM，通过CROSS JOIN求笛卡尔积，相当于得到虚拟表vt（virtual table）1-1；
• 第二步：执行ON，通过ON进行筛选，在虚拟表vt1-1的基础上进行筛选，得到虚拟表vt1-2；
• 第三步：执行JOIN，如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2的基础上增加外部行，得到虚拟表 vt1-3。
• 第四步：执行WHERE，我们对最终得到的虚拟表vt1，就可以在此基础上进入WHERE阶段，在这个阶段中，会根据vt1表的结果进行筛选，得到虚拟表vt2
• 第五步：执行GROUP， 在这个阶段中，实际上是在虚拟表vt2的基础上进行分组得到中间的虚拟表vt3。
• 第六步：执行HAVING，在这个阶段中，对虚拟表vt3经过过滤得到vt4。
• 第七步：执行SELECT，当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT阶段，SELECT 阶段会提取想要的字段，得到虚拟表vt5-1。
• 第八步：执行DISTINCT，在DISTINCT阶段会过滤掉重复的行，得到虚拟表vt5-2。
• 第九步：执行ORDER BY，当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段 ，得到虚拟表 vt6 。
• 第十步：最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段，得到最终的结果，对应的是虚拟表vt7。

四、子查询

1. 基本概念

• 子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
• SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较

2. 子查询分类

2.1 分类一：单行子查询和多行子查询

2.1.1 单行子查询

• 概念：子查询结果为一行数据

2.1.2 多行子查询

• 概念：子查询结果为多行数据

2.2 分类二：相关子查询和不相关子查询

2.2.1 相关子查询

• 概念：子查询的条件与主查询无关
• 需求引入：查询工资大于本部门平均工资的员工信息
• 需求分析：子查询的平均工资与主查询的员工部门有关

2.2.2 不相关子查询

• 概念：子查询的条件与主查询有关
• 需求引入：查询工资大于平均工资的员工信息

2.2.3 关键字的使用-EXISTS和NOT EXISTS（相关子查询）

关联子查询通常也会和EXISTS操作符一起使用，用来检查在子查询中，是否存在满足条件的行：

• EXISTS：如果在子查询中不存在满足条件的行，则条件返回FALSE，并继续在子查询中查找；如果在子查询中存在满足条件的行，则不再在子查询中继续查找，条件返回TRUE
• NOT EXISTS：如果在子查询中不存在满足条件的行，则条件返回TRUE；如果在子查询中存在满足条件的行，则不再在子查询中继续查找，条件返回FALSE

3. 注意事项

• 子查询（内查询）在主查询（外查询）之前一次执行完成
• 子查询（内查询）的结果被主查询（外查询）使用
• 子查询要包含在括号内
• 将子查询放在比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查询
• 子查询的书写位置：在SELECT中，除了GROUP BY和LIMIT之外，其他位置都可以声明子查询

4. 子查询案例

4.1 单行子查询

4.1.1 单行操作符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

4.1.2 上案例

1.查询工资大于149号员工工资的员工信息

SELECT * 
FROM employees 
WHERE salary > (SELECT salary 
				FROM employees 
				WHERE  employee_id = 149);

2.查询job_id与141号员工相同，salary比143号员工多的员工信息

SELECT * 
FROM employees 
WHERE salary > (SELECT job_id
				FROM employees
				WHERE  employe_id = 141
)
AND job_id = (SELECT salary
				FROM employees
				WHERE	employee_id = 143
);

3.查询最低工资大于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
);

4.2 多行子查询

4.2.1 多行操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，和ANY作用相同

4.2.2 上案例

IN：查询公司中员工工资为每个部门的最低工资的员工信息

SELECT * 
FROM employees
WHERE salary IN (SELECT MIN(salary)
				FROM employees
				GROUP BY department_id
				);

ANY：查询其他job_Id中比job_id为’IT_PROG’部门任一员工资低的员工信息

SELECT * 
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY ( SELECT salary
				FROM employees
				WHERE job_id = 'IT_PROG'
				);

ALL：查询其他job_Id中比job_id为’IT_PROG’部门所有员工资低的员工信息

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

4.3 难度飙升：关联子查询

1.查询员工中工资大于本部门平均工资的员工信息

• 回顾：查询员工中工资大于平均工资的员工信息

SELECT *
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
)

本次查询的实际执行步骤如下：
1.子查询查询到平均工资
2.主查询时，表中的每条记录的工资都和平均工资进行对比，大于的保留，其余的丢弃
3.返回最终结果

• 进行：当前的查询

SELECT *
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employee e2
		WHERE e2.department_id = e1.department_id
);

本次查询步骤如下：
1.主查询时，每次传入一条数据进入子查询中
2.子查询根据传入的数据的员工号算出指定部门的平均工资
3.主查询的该条数据的员工工资和子查询查询出来的数据的平均工资进行对比，符合要求的返回，不符合的抛弃

2.查询公司管理者的employee_id，last_name，job_id，department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (SELECT *
			FROM employees e2
			WHERE e2.manager_id = e1.employee_id
);

3.查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
				FROM employees
				WHERE department_id = d.department_id
);