开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Mysql基础巩固 --- 多表查询和聚合函数的一些细节，SELECT的执行过程 -> 正文阅读

[大数据]Mysql基础巩固 --- 多表查询和聚合函数的一些细节，SELECT的执行过程

一、多表查询

多表查询分类：

等值连接 vs 非等值连接
自连接 vs 非自连接
内连接 vs 外连接

1.1 笛卡儿积(交叉连接)

在数据库原理里学过笛卡尔积，就是把两个表的所有记录做一个全排 ，例如：

会造成笛卡儿积的情况如下：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

即：在不使用on语法时，join、inner join、逗号、cross join结果相同，都是取2个表的笛卡尔积。

解决方法：

使用JOIN...ON子句创建连接限定条件，或者使用WHERE过滤数据

1.2 等值连接 vs 非等值连接

等值连接就是 WHERE语句最后是判断两个值是否相等
非等值连接就是 WHERE语句不是严格的判断是否相等，可以是大于小于，或者IN一个范围等等

例如：

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id; #等值连接

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`  #非等值连接

1.3 自连接 vs 非自连接

非自连接就是连接两个不同的表
自连接就是把一张表看作两张表连接

题目：查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

1.4 内连接 vs 外连接

1.4.1 内连接(INNER JOIN)

内连接就是求两个表严格相交的部分，只要有一方为NULL就把这条记录丢掉

关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都是内连接

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

1.4.2 外连接(OUTTER JOIN)

外连接分为：左外连接，右外连接，全外连接

1.4.2.1 左外连接(LEFT OUTTER JOIN)

左外连接就是保证左表记录数量不减少，求相交的部分，如果没有相交的部分，就在左表对应右表的字段上置NULL

结果 = 左右表匹配的数据 + 左表没有匹配到的数据

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

1.4.2.2 右外连接(RIGHT OUTTER JOIN)

右外连接就是保证右表记录数量不减少，求相交的部分，如果没有相交的部分，就在右表对应左表的字段上置NULL

结果 = 左右表匹配的数据 + 右表没有匹配到的数据

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

1.4.2.3 满外连接(FULL OUTTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据

注意：Mysql不支持FULL JOIN，但是可以用LEFT JOIN UNION RIGHT JOIN代替

1.4.2 UNION

合并查询结果，两个表对应的列数和数据类型必须相同，并且相互对应。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION ：返回两个查询结果集的并集，并去重
UNION ALL ：返回两个查询结果集的并集，不去重

执行UNION ALL语句时所需要的资源比UNION语句少，因为少了去重操作。

所以，在知道没有重复元素的情况下，尽量用UNION ALL提高效率

1.5 图解7种JOIN方式

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#左下图：左上 + 右中   或者  右上 + 左中
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

总结：

实现第一行的，只需要简单的 LEFT JOIN 或者 RIGHT JOIN

实现第二行，只需要在 LEFT JOIN 或者 RIGHT JOIN 基础上加上WHERE 从表关联字段 is null

左外连接去掉相交部分
右外连接去掉相交部分

实现全外连接：

左外连接 UNION 右外连接去掉相交部分
右外连接 UNION 左外连接去掉相交部分

1.6 SQL99新特性

1.6.1 自然连接(NATURAL JOIN)

它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接。

在SQL92中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在SQL99中可以

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

1.6.2 USING连接

SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用 。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

等价于

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

总结：

表连接的约束条件可以有三种方式：WHERE ，ON ，USING

WHERE：适用于所有关联查询
ON：只能和JOIN一起使用，只能写关联条件
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致 ，而且只能表示关联字段值相等

二、聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值

AVG() 
SUM() 
MAX() 
MIN() 
COUNT()

问题：

①用count(*)，count(1)，count(列名)谁好呢？

MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb引擎的表用count(*)，count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

②能不能使用count(列名)替换count(*)?

不要使用 count(列名)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2.1 GROUP BY分组

格式：

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

可以使用GROUP BY子句将表中的数据分成若干组，例如：

1、根据单列分组

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

2、使用多列分组

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

使用多列分组时，如果select中出现了group by的一个字段，就要把所有group by中的字段都写在select中，否则会报错

即：要么都省略，要么都写上

拓展：WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

2.2 HAVING

作用：过滤分组

使用条件：

行已经被分组。
使用了聚合函数。
满足HAVING 子句中条件的分组将被显示。
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

举例：

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

2.3 WHERE和HAVING对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。

三、SELECT的执行过程

3.1 查询的结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

需要记住的两个顺序：

1、关键字顺序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2、SELECT 语句的执行顺序

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

举例：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

3.2 SQL的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表vt1-3。当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2 。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表 vt7。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。