MySQL查询语句执行顺序以及各关键字的详解，并实战分组后每组显示前几条数据

推荐好文：
2.5万字讲解DDD领域驱动设计，从理论到实践掌握DDD分层架构设计

使用高并发利器redis—解决淘宝/微博的【热门搜索】和【最近搜索】的功能

SpringCloud核心组件概述（五大神兽）

1. 前言

无论什么语言，不管什么框架，在这个大数据的时代，最终都会和数据库打交道，所以对于数据库的深入了解显得尤为重要。
今天我们一起讨论一下mysql查询语句的执行顺序，这对项目开发中编写sql语句非常重要，知道sql语句里各个关键字的作用，从此写sql不是问题！！！

2. sql语句的查询格式

Select [distinct] 字段名，[fun(字段名)] from 表1 [<join类型>join 表2 on <join条件> where <where条件> group by <字段> with having <having条件> order by <排序字段> limit <起始偏移量,行数>]
注：[]表示可选，没有在语句中指定某一个子句，那么将会跳过相应的步骤。
fun表示聚合函数，例如最大值max(),最小值min(),平均值avg(),计数count(),求和sum()

3. sql语句的执行顺序

（9）Select
（10）distinct 字段名1,字段名2，
（7）[fun(字段名)]  
（1）from 表1
（3）<join类型>join 表2 
（2）on <join条件> 
（4）where <where条件> 
（5）group by <字段> 
（6）with <cube|rollup> 
（8）having <having条件> 
（11）order by <排序字段> 
（12）limit <起始偏移量,行数>

4. 各个关键字的含义

1.FORM:
对FROM的左边的表和右边的表计算笛卡尔积。产生虚表1

2.ON:
对虚表1进行ON筛选，只有那些符合<join条件>的行才会被记录在虚表2中。

3.JOIN：
如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3
连接可分为：

①left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
②right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
③inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
④full join:全连接，返回两个表中的并集，left join + right join。
⑤cross join:结果是笛卡尔积，就是第一个表的行数与第二个表的行数相乘，
结果集中的每一行都是第一个表中的行与第二个表中的行的组合，结果集列是两个表列数相加。

特别注意：

①在使用left join时，on和where条件的区别如下：
1、 on条件是在生成临时表前使用的条件，它不管on中的条件是否为真，都会返回左边表中所有的记录，但是这里注意右表的数据，如果on中对右表的条件是false的数据，在生成临时表中右表的数据将显示为null。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义了，
左边表的记录条件不为真的就全部被过滤掉了。
②在使用 inner join 时，不管是对左表还是右表进行筛选，on and和on where都会对生成的临时表进行过滤，inner join 在执行的时候回自动选择最小的表做基础表，效率高
③当使用 left join 时，sql尽量使用数据量小的表做主表，这样效率高，无论on的条件是否满足，都会返回左表的所有记录，对于满足的条件的记录，两个表对应的记录会连接起来，对于不满足条件的记录，那右表字段全部是null
④当使用 right join 时，on与where类似，只不过是全部返回右表的所有记录
⑤当使用 inner join 时，on与where完全相同

3、mysql中没有full join，需要使用union或者union all，union会自动去重，而union all 不会去重
```sql
select t1.*,t2.* from a left join b on t1.id = t2.id
union
select t1.*,t2.* from a right join b on t1.id = t2.id

4.WHERE：
对虚拟表3进行WHERE条件过滤。只有符合<where条件>的记录才会被插入到虚拟表4中。
①where后面的条件可以用>、<、>=、<=、!=等多种比较运算符，多个条件之间可以用or、and等逻辑运算符

5.GROUP BY:
按照某个字段进行分组
①开始使用select中的别名，后面的语句中都可以使用
②根据group by子句中的列，对虚拟表4中的记录进行分组操作，产生虚拟表5.
③group by关键字 表示要进行分类聚合的字段。比如要按照部门分类统计员工数量，部门就应该写在group by 后面。

6.CUBE | ROLLUP:
对虚拟表5进行cube或者rollup操作，产生表虚拟表6.
①with rollup 是可选语法，表明是否对分类聚合后的结果进行再汇总

7.fun：
聚合函数：最大值max(),最小值min(),平均值avg(),计数count(),求和sum()插入到虚拟表6
聚合函数无法用在where子句中

8.HAVING：
having 关键字表示对group by分组之后的（求和，平均值等）结果再进行条件过滤。
对虚拟表6应用having过滤，只有符合<having条件>的记录才会被 插入到虚拟表7中。

注意：
on、where、having的区别
on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。
on是在生成中间的临时表时起作用的，where，having是生成临时表之后对连接表再进行过滤
on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的
where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。

9.SELECT：
执行select操作，选择指定的列，插入到虚拟表8中。

10.DISTINCT：
对虚拟表8中的记录进行去重。产生虚拟表9.
①.distinct必须放在最开头
②.distinct只能使用需要去重的字段进行操作。 ----也就是说我sidtinct了name,age两个字段，我后面想根据id进行排序，是不可以的，因为只能name,age两个字段进行操作.
③.distinct去重多个字段时，含义是：几个字段 同时重复 时才会被 过滤。

11.ORDER BY:
将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表10.
①.desc 降序排列，asc 升序排列
②.order by 后面可以跟多个不同的排序字段，每个排序字段都可以有不同的排序顺序。
③.如果排序字段的值一样，则相同的字段按照第二个排序字段进行排序。
④.如果只有一个排序字段，则字段相同的记录将会无序排列。

12.LIMIT：
取出指定行的记录，产生虚拟表11, 并将结果返回。
默认情况下，起始偏移量为0，只写记录行数就可以。

5. 子查询的执行顺序

子查询分为【相关子查询】和【非相关子查询】。

5.1 非相关子查询

1. 【非相关子查询】是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询,并且它是优先于外部查询先执行的，他执行了再执行外部，因此通常比联表查询执行效率更高。

SELECT * FROM t1 WHERE t1.user_id IN (SELECT user_id  FROM t2);

5.2 相关子查询

2. 【相关子查询】是先执行主查询，再针对主查询返回的每一行数据执行子查询，如果子查询能够返回行，则这条记录就保留，否则就不保留。说白了也就是子查询根据主查询的结果进行遍历并过滤。相对于联表查询而言，通常联表查询效率更高。

SELECT * FROM t_order t1 WHERE t1.order_amount > (SELECT max(order_amount) FROM t_order t2 WHERE t2.user_id = t1.user_id);

4. 通常情况下，【非相关子查询性能】>【联表查询性能】>【 相关子查询性能】；

5. 当子查询的数据量很大时，或者需要访问多个表进行计算时，【联表查询性能】>【非相关子查询性能】> 【相关子查询性能】，因为非关联子查询可能会返回大量的结果集，导致查询效率变慢。此时，使用联表查询可以减少返回结果集的数量，提高查询效率。

5. in，exists，not in，not exists

5.1 in原理：

SELECT * FROM t1 WHERE t1.id IN (SELECT user_id  FROM t2);

1. 先执行子查询，再执行主查询，当前查询是【t2】表驱动【t1】表
2. MySQL先将【t2】表的数据查询到内存
3. 遍历内存中【t2】表的数据，根据【t2】表的每一行再去查【t1】表，所以会用到主查询的索引
4. 如果【t1】表有100行数据，【t2】有10000行数据，两张表会交互10000次；如果【t1】有10000行数据，【t2】有100行数据，两张表只会发生100次交互。
5. 交互次数越少效率越高，因此，in适用于【t1】主表大而【t2】子表小的场景

5.2 exists原理：

用于检查子查询是否返回任何结果。如果子查询返回至少一行记录，则条件成立，返回True；否则，条件不成立，返回False。

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.id);

1. 先执行主查询，再执行子查询，当前查询是【t1】表驱动【t2】表
2. MySQL将【t1】表的数据查询到内存，因此【t1】表的记录数决定了数据库的交互次数
3. 遍历内存中【t1】表的数据，根据【t1】表的每一行再去查【t2】表，所以会用到子查询的索引
4. 如果【t1】表有100行数据，【t2】有10000行数据，两张表会交互100次；如果【t1】有10000行数据，【t2】有100行数据，两张表只会发生10000次交互。
5. 交互次数越少效率越高，因此，exists适用于【t1】主表小而【t2】子表大的场景

5.3 not in 和 not exists

如果查询语句使用 not in，那么内外表都进行全表扫描，没有用到索引；
而 not exists 的子查询依然能用到表上的索引。
所以无论那个表大，用 not exists 都比not in 要快。

5.4 索引区别：

in：in 与子查询一起使用的时候，只能针对主查询使用索引。
exist：exist会针对子查询的表使用索引。
not in：not in 则不会使用任何索引。
not exists：not exist会针对子查询的表使用索引。

5.2 exist和联表性能比较

5.2.1 EXISTS 子查询可能比连接查询性能更高的场景：

1. 处理主表小子表大数据集：当处理主表小数集，子表大数据集时，EXISTS 子查询通常比 JOIN 更有效率。EXISTS 子查询只需要找到满足条件的第一个匹配项，然后立即停止搜索，而 JOIN 查询会继续搜索整个数据集。
2. 避免重复：EXISTS 子查询在处理可能产生重复记录的查询时，通常比 JOIN 更有效。EXISTS 子查询只检查是否存在至少一个匹配的记录，而不需要处理重复记录。

5.2.1 连接查询可能比EXISTS 子查询性能更高的场景：

1. 需要从多个表中提取信息：如果你需要从多个表中提取信息，JOIN 通常比 EXISTS 子查询更高效。JOIN 可以在一个查询中处理多个表，而 EXISTS 子查询通常只能处理一个表。

6. 分组后每组显示前几条数据

方式一

1. 表结构：
   
2. 表数据：
   
3. 求：查询每门课程的成绩位居前2名的学生，怎么解？

	SELECT a.*
	FROM t_grade a
	WHERE (
	    SELECT COUNT(1)
	    FROM t_grade b
	    WHERE b.grade <= a.grade AND b.class_id = a.class_id
	    ) <= 2
	ORDER BY a.class_id;

4. 结果如下：
   
5. sql分析：
   （1）要查询出每门课程的成绩位居前2名的学生，可以转换成：算出比每一个学生成绩高的学生数量。
   比如，有一名学生A，比他成绩高的学生数量是1个（B学生），那么B学生就是第一名，就没其它人成绩比B学生更好。
   比如，有一名学生A，比他成绩高的学生数量是2个（B学生和C学生），那么B学生和C学生就是前两名。
   因此，where语句可以理解为，把表中的每一行记录，都去与给定的where条件作对比，满足的再查出来。也就是主查询遍历所有的学生成绩，子查询根据主查询的class_id当作条件，并且找出要比每一学生成绩高的学生数量，如果要找出成绩位居前2名的学生，那么子查询就比较比每一个学生成绩高的学生数量<=2的条件。
   （2）要查询出每门课程的成绩倒数后2名的学生，就是找出要比每一学生成绩低的学生数量。

	SELECT a.*
	FROM t_grade a
	WHERE (
	    SELECT COUNT(1)
	    FROM t_grade b
	    WHERE b.grade >= a.grade AND b.class_id = a.class_id
	    ) <= 2
	ORDER BY a.class_id;

方式二

MySQL不支持使用ROW_NUMBER()窗口函数导致的。ROW_NUMBER()是SQL标准中的窗口函数之一，但是并不是所有数据库都支持它。MySQL中没有内置的ROW_NUMBER()函数，但可以使用其他方法来模拟实现它，例如使用MySQL变量或者子查询等方式。

SELECT * FROM (
  SELECT *,
         @row_num := IF(@group_id = class_id,@row_num + 1,1) AS row_num,
         @group_id := class_id AS group_id
  FROM t_grade, (SELECT @row_num := 0, @group_id := '') AS temp
  ORDER BY class_id
) t WHERE row_num <= 2;

sql解析：

1. 子查询：首先，这个查询使用子查询来计算每个班级中成绩排名。具体地，我们在子查询中使用了变量 @group_id 来记录上一个班级的编号，并使用变量 @row_num 来计算每个班级中成绩排名。我们通过将变量初始化为 ‘’ 和 0，来确保在查询开始时变量的值为初始值。
2. 主查询：接下来，我们在主查询中对子查询的结果进行筛选。具体地，我们选择排名小于等于 2 的记录。由于在子查询中已经按照班级编号和成绩进行了排序，因此我们可以确保在主查询中选择的记录是每个班级中前两个成绩最高的学生。
3. IF(@group_id = class_id,@row_num + 1,1)作用：判断当前班级是否与上一个班级相同。如果当前班级 class_id 与上一个班级相同（即 @group_id 的值等于 class_id），则将该班级的排名 @row_num 加1；否则重置排名 @row_num 为1。
4. 在 ORDER BY 子句中，我们使用 class_id 来指定按照班级编号进行排序，以便在计算排名时能够正确地分组。

如果看到这里，说明你喜欢这篇文章，请转发，点赞。关注微信公众号微信搜索[老板来一杯java]回复[进群]或者扫描下方二维码即可进入无广告技术交流私有群！【加群】即可获取【java基础经典面试】一份 和【DDD领域驱动设计实战落地解惑】PDF一份！

推荐好文：
2.3万字讲解设计模式
netty搭建websocket实现服务器主动推送
优雅的消除代码中的if else通过公司代码审核