MySQL进阶（UNION和UNION ALL的区别、HAVING、CASE WHEN、自定义排序、SQL关键字、SQL语句执行顺序、自增主键不一定连续）

UNION和UNION ALL的区别

UNION和UNION ALL的区别主要是以下几方面：
（1）UNION ALL是取得所有值，结果结果可能有重复
UNION是取得唯一值，查询结果记录没有重复
（2）UNION会按照字段的顺序进行排序
UNION ALL只是简单的将两个结果合并后返回

HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

例如：查找总访问量大于 200 的网站

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

where 和having之后都是筛选条件，但是是有区别的：

• where在group by前， having在group by 之后
• 聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

CASE WHEN

MySQL 的 case when 的语法有两种：
简单函数 ：CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;

搜索函数 ：CASE WHEN [expr] THEN [result1]…ELSE [default] END

# when 表达式中可以使用 and 连接条件
SELECT
    NAME '英雄',
    age '年龄',
    CASE
        WHEN age < 18 THEN
            '少年'
        WHEN age < 30 THEN
            '青年'
        WHEN age >= 30
        AND age < 50 THEN
            '中年'
        ELSE
            '老年'
    END '状态'
FROM
    user_info;

自定义排序

• case when 转换实现自定义排序；
• mysql排序函数filed 实现自定义排序（推荐）；MySQL中的field（）函数，可以用来对SQL中查询结果集进行指定顺序排序。field(str,str1,str2,str3…)字段str按照字符串str1，str2，str3…的顺序返回查询到的结果集。如果表中str字段值不存在于str1，str2，str3中的记录，放在结果集最前面返回。
• 还可以利用locate函数来实现排序；locate(subStr,string) ：判断字符串（string）中是否包含另一个字符串（subStr），函数返回的是subStr在string中出现的位置；
• INSTR(STR,SUBSTR) 在一个字符串(STR)中搜索指定的字符(SUBSTR),返回发现指定的字符的位置(INDEX);同locate函数大致一样，不过函数内参数一个在前，一个在后；

SQL关键字执行顺序

要想优化慢SQL语句首先需要了解SQL语句的执行顺序，SQL语句中的各关键词执行顺序如下：

• 首先执行from、join 来确定表之间的连接关系，得到初步的数据。
• 然后利用where 对数据进行初步筛选。
• group by 分组
• 各组分别执行having 中的普通筛选或者聚合函数筛选。
• 然后把再根据我们要的数据进行select，可以是普通字段查询也可以是获取聚合函数的查询结果，如果是集合函数，select的查询结果会新增一条字段
• 将查询结果去重distinct
• 最后合并各组的查询结果，按照order by的条件进行排序

SQL关键字的顺序

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

SELECT语句的执行顺序（在MySQL和Oracle中，SELECT执行顺序基本相同）：

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

在SELECT语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在SQL的执行过程中，对于我们来说是不可见的。

MySQL 语句执行的步骤

Server 层按顺序执行 SQL 的步骤为：

• 客户端请求 -> 连接器（验证用户身份，给予权限）
• 查询缓存（存在缓存则直接返回，不存在则执行后续操作）
• 分析器（对 SQL 进行词法分析和语法分析操作）
• 优化器（主要对执行的 SQL 优化选择最优的执行方案方法）
• 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

MySQL自增主键是否一定递增？

自增主键不能保证连续递增。

自增主键存储在哪？

• 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。
• 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log恢复重启之前的值。

自增主键修改机制：先获取自增主键当前值，主键值自增，然后执行insert语句。发生异常时主键自增不回退。

自增主键不连续的情况：

• 唯一主键冲突：如果第二次插入时出现唯一键冲突，会导致新增失败，但是根据自增值的修改机制，会出现自增主键不连续。
• 事务回滚：其实事务回滚原理也和上面一样，都是因为异常导致新增失败，但是自增值没有进行回退。
• 批量插入：批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：

1. 语句执行过程中，第一次申请自增 id，会分配 1 个；
2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
3. 2 个用完以后，还是这个语句， 第三次申请自增 id，会分配 4 个；
4. 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

执行以下SQL语句（在表t中先新增了4条数据，在创建表tt把表t数据进行批量新增）

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table tt like t;
insert into tt(c,d) select c,d from t;
 
insert into tt values(null, 5,5);

第一次申请到了 id=1，第二次被分配了 id=2 和 id=3， 第三次被分配到 id=4 到 id=7。当我们再执行 insert into t2 values(null, 5,5)，实际上插入的数据就是（8,5,5)，出现了自增主键不连续的情况。