前言
这篇文章记录的是MySQL进阶相关的一些知识点，包含MySQL的索引、存储引擎、存储过程、触发器、锁、InnoDB引擎。
文章中多数笔记来源黑马程序员教程中的内容，少数是结合自己之前工作中的想法和实际操作时的感触进行记录。视频链接：https://www.bilibili.com/video/BV1Kr4y1i7ru
一、MySQL存储引擎
1.1 分类
MySQL存储引擎分三种，分别为InnoDB、Memory、MyISAM。
InnoDB ：支持行锁，使用B+树索引，支持事务，支持外键约束；
逻辑存储结构如下图所示
MyISAM：5.5及版本之前的默认引擎，支持表锁，使用B+树索引，不支持事务，不支持外键；
Memory：支持表锁，使用B+树索引和hash索引，默认为hash索引，数据存放在内存中，读写速度高，但只能用于缓存或临时表，不支持事务，不支持外键，现在多用Redis替代
1.2 选择依据
InnoDB:
是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MyISAM ：
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：
将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
二、索引
2.1 索引概述
索引是帮助MySQL高效获取数据的一种数据结构
这里主要记录两种索引：B+树索引和hash索引。
在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
为什么InnoDB存储引擎选择使用B+tree索引结构?
A. 相对于二叉树，层级更少，搜索效率高；
B.对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
C. 相对Hash索引，B+tree支持范围匹配及排序操作；
2.2 索引分类
2.2.1 索引分类
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免表中某列的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个，不加关键字修饰时创建的索引就是该索引 | |
| 联合索引 | 查找的事文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
2.2.2 聚集索引 & 非聚集索引（二级索引）
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(ClusteredIndex) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(SecondaryIndex) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引的具体结构如下：
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值。
当我们执行select * from user where name='Arm'SQL语句时具体过程如下:
①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查
找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最
终找到10对应的行row。
③. 最终拿到这一行的数据，直接返回即可。
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
2.3 索引语法
1）创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,… ) ;
示例：create unique index idx_user_id on user(id);
2）查看索引
SHOW INDEX FROM table_name ;
示例：show index from user;
3）删除索引
DROP INDEX index_name ON table_name;
示例：drop index idx_user_id on user;
2.4 SQL性能分析
2.4.1 SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
– session 是查看当前会话 ;
– global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE ‘Com_______’;
Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数
2.4.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
开启后重新启动数据库，查看localhost-slow.log文件，当有慢查询的语句执行时将会被记录进日志文件中
2.4.3 profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;
可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在
session/global级别开启profiling：
SET profiling = 1;
开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去
了。 我们直接执行如下的SQL语句：
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条SQL的耗时情况:
查看指定SQL各个阶段的耗时情况：
2.4.4 explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain 执行计划中各个字段的含义:
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 |
| possible_key | 显示可能应用在这张表上的索引，一个或多个。 |
| key | 实际使用的索引，如果为NULL，则没有使用索引。 |
| key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 |
| rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。 |
2.5 索引使用
2.5.1 最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效
2.5.2 范围索引
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <
2.5.3 索引失效情况
2.5.3.1 索引列运算
不要在索引列上进行运算操作， 索引将失效。比如substring函数
2.5.3.2 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。
2.5.3.3 模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
like 'A%'不失效
like '%A'失效
2.5.3.4 or连接条件
当用or连接多个查询条件时，or的两边只要存在一方使用了没建立索引的字段，则会导致另一边索引失效
2.5.3.5 数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
2.5.4 SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1).use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2). ignore index ： 忽略指定的索引。
select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
3). force index ： 强制使用索引。
select * from tb_user force index(idx_user_pro) where profession = '软件工程';
2.5.5 覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并
且需要返回的列，在该索引中已经全部能够找到。原理是取消MySQL二级索引的回表操作。
2.5.6 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让
索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法（相较于正常的创建索引步骤，仅多了在字段后指定n的过程）：
create index idx_xxxx on table_name(column(n)) ;
2.5.7 单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
联合索引结构示意图如下：
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
2.6 索引设计原则
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
三、SQL优化
3.1 插入数据
3.1.1 insert
如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....
优化方案一
批量插入数据
Insert into tb_test values(1,'Tom'),(2,'1 Cat'),(3,'Jerry');
优化方案二
手动控制事务（当一条SQL语句无法插入完，需拆分成多条时，手动控制事务的开关，可以减少事务开关的次数，提高效率）
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
优化方案三
主键顺序插入，性能要高于乱序插入。
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
3.1.2 大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：
可以执行如下指令，将数据脚本文件中的数据加载到表结构中：
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log'
into table tb_user
fields terminated by ','
lines terminated by '\n' ;
主键顺序插入性能高于乱序插入，可先将文件中的数据按照主键升序排列后再进行load操作
注意，当表中存在自增主键时，提供的数据文件中不需要包含该列，并要在load命令的最后面按照文件中字段的顺序指定字段，示例：
load data local infile 'C:/Users/dreaming/Desktop/1.txt'
into table t_user
fields terminated by ','
lines terminated by '\n'
(username,password,age,sex,email)
3.2 主键优化
主键设计原则：
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
3.3 order by优化
order by优化原则:
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
3.4 group by优化
group by优化原则：
- 在分组操作时，可以通过索引来提高效率。
- 分组操作时，索引的使用也是满足最左前缀法则的。
3.5 limit优化
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。（MySQL5.6之前的版本不支持在子查询中使用limit，因此最好是使用下面这种语句）
select * from tb_sku t , (select id from tb_sku order by id
limit 2000000,10) a where t.id = a.id;
3.6 count优化
| count用法 | 含义 |
|---|---|
| count(主键) | InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) |
| count(字段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 |
| count(数字) | InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 |
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。
3.7 update优化
在更新数据时要根据索引字段进行更新，当where后面的字段不是索引字段时，锁会从行锁变为表锁，降低了效率。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
当索引是联合索引时，同样遵循最左前缀法则，如不满足最左前缀法则，那依然会导致从行锁升级为表锁！
四、视图
4.1 视图的创建、查询、修改、删除
视图的创建语句：
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]
视图的查询语句：
--查看视图创建语句
show create view 视图名称;
--查询语句
select * from 视图名称;（与表的查询语句相同）
视图的修改语句;
--方式一：
同创建语句，加上or replace；
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]
--方式二：
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]
视图的删除语句：
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
4.2 创建视图时的约束
约束在通过视图对数据进行修改时生效，但本身不建议通过视图对数据进行修改，因此以下内容知道就可以了。
4.2.1约束的用法
在创建视图时，在语句的最后方有[WITH [CASCADED | LOCAL] CHECK OPTION]可选项，该可选项的作用就是用于为视图添加约束，不加CASCADED和LOCAL时，默认值为CASCADED 。
该约束的用法是当给视图插入或更新数据时按照视图的创建语句进行校验，当创建语句中添加了条件限制、而向视图中添加或修改的数据不满足该条件限制，则不允许添加或修改数据。示例（以插入示例。更新数据同理）：
create view user_adult_view as select userid,username,age from user_table where age>=18 with check option;
--上方的视图在创建时指定了age>=18的条件，并添加了with check option，因此当执行下方的插入语句向视图中插入数据时，数据库会报错：
INSERT INTO USER_ADULT_VIEW(USERNAME,AGE) VALUES ('ADMIN',17);
4.2.2 CASCADED和LOCAL的区别
CASCADED
校验该视图以及它所依赖的视图的创建条件，当依赖条件不满足时，插入数据失败。
示例如下（以插入示例。更新数据同理）：
USER_ADOLESCENCE_VIEW（以下简称ADO）视图基于USER_MINOR_VIEW（以下简称MIN）视图进行创建。在MIN中没有使用校验，而在ADO视图中使用了CASCADED校验（因为默认就是CASCADED）。当向ADO中插入age值为19的数据时，满足ADO本身的规则，但不满足MIN中的规则，因此数据库提示校验失败，不允许插入数据。
LOCAL
校验该视图本身的创建条件，当满足该视图本身时，若其依赖视图存在校验，则检查依赖视图的条件，若依赖视图不存在校验，则允许插入
情况一：依赖视图不存在校验
情况二：依赖视图存在校验
五、存储过程 & 触发器
5.1 存储过程
5.1.1 存储过程语法
创建
create procedure 存储过程名称([参数列表])
begin
--sql语句
end;
注意：当使用DBVisualizer工具时，要在create上一行加上--/，在end下一行加上/
示例：
--/
create procedure p1()
begin
select userid,username from user_table;
select userid,age from user_table;
end;
/
查看
show create procedure 存储过程名称;
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
示例：
show create procedure p1;
使用
call 存储过程名称([存储过程参数[,存储过程参数]]);
示例：
call p1();
删除
drop procedure [if exists] 存储过程名称;
示例：
drop procedure p1;
5.1.2 变量
在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
5.1.2.1 系统变量
1）查看系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
2）设置系统变量
默认是SESSION，会话变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
5.1.2.2 用户定义变量
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。
1). 赋值
方式一:
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
赋值时，可以使用 = ，也可以使用 := 。
方式二:
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
2). 使用
SELECT @var_name ;
注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
演示示例:
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;
5.1.2.3 局部变量
局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。
1). 声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
2). 赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
演示示例:
-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
call p2();
5.1.3 参数
1). 介绍
参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下：
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
2）.用法
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;
3）.案例
将传入的200分制的分数，进行换算，换算成百分制，然后返回。
create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score = 198;
call p5(@score);
select @score;
5.1.4 条件判断语句
5.1.4.1 if
语法
if用作条件判断，具体语法如下
在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;
案例
根据传入参数score，判定当前分数对应的分数等级，并返回。
- score >= 85分，等级为优秀。
- score >= 60分 且 score < 85分，等级为及格。
- score <60分，等级为不及格。
create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;
5.1.4.2 case
语法
语法一：
-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;
语法二：
-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成立时，执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;
案例
根据传入的月份，判定月份所属的季节（要求采用case结构）。
- 1-3月份，为第一季度
- 4-6月份，为第二季度
- 7-9月份，为第三季度
- 10-12月份，为第四季度
create procedure p6(in month int)
begin
declare result varchar(10);
case
when month >= 1 and month <= 3 then
set result := '第一季度';
when month >= 4 and month <= 6 then
set result := '第二季度';
when month >= 7 and month <= 9 then
set result := '第三季度';
when month >= 10 and month <= 12 then
set result := '第四季度';
else
set result := '非法参数';
end case ;
select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end;
call p6(16);
注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。
5.1.4.3 while
介绍
while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：
-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;
案例
计算从1累加到n的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
create procedure p7(in n int)
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n - 1;
end while;
select total;
end;
call p7(100);
5.1.4.4 repeat
介绍
repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。具体语法为：
-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;
案例
计算从1累加到n的值，n为传入的参数值。(使用repeat实现)
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n - 1;
until n <= 0
end repeat;
select total;
end;
call p8(10);
call p8(100);
5.1.4.5 loop
介绍
LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。
LOOP可以配合一下两个语句使用：
- LEAVE ：配合循环使用，退出循环。
- ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
上述语法中出现的 begin_label，end_label，label 指的都是我们所自定义的标记。
案例
案例一
计算从1累加到n的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
create procedure p9(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p9(100);
案例二
计算从1到n之间的偶数累加的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
create procedure p10(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
if n%2 = 1 then
set n := n - 1;
iterate sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p10(100);
5.1.4.6 游标
介绍
游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。
A. 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
B. 打开游标
OPEN 游标名称 ;
C. 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
D. 关闭游标
CLOSE 游标名称 ;
案例
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=uage;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);
上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。但是此时，tb_user_pro表结构及其数据都已经插入成功了。
要想解决这个问题，就需要通过MySQL中提供的 条件处理程序 Handler 来解决。
5.1.4.7 条件处理程序
介绍
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：
DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;
handler_action 的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
案例
我们继续来完成在上一小节提出的这个需求，并解决其中的问题。
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
A. 通过SQLSTATE指定具体的状态码
-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);
B. 通过SQLSTATE的代码简写方式 NOT FOUND
02 开头的状态码，代码简写为 NOT FOUND
create procedure p12(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出
declare exit handler for not found close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p12(30);
具体的错误状态码，可以参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
5.2 存储函数
介绍
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;
characteristic说明：
- DETERMINISTIC：相同的输入参数总是产生相同的结果
- NO SQL ：不包含 SQL 语句。
- READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。
案例
计算从1累加到n的值，n为传入的参数值。
create function fun1(n int)
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n - 1;
end while;
return total;
end;
select fun1(50);
在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定characteristic特性，否则就会报如下错误：
5.3 触发器（TRIGGER）
介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
个人感想：工作中会用到，可以对重要的数据表进行监控，比如用户表、权限表等，这样可以防止有时数据变动了但不知道相较于上次变动的是哪些地方。但是，触发器是会消耗数据库资源，影响数据变更性能的，因此如非必要，尽量不要对数据变更频繁的表加触发器
| 触发器类型 | NEW 和 OLD |
|---|---|
| INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
语法
创建
关于before和after的说明：
before：在数据操作之前触发，因此即使数据未成功变更，该触发器依然生效，多用于对将要变更的数据进行一些判断和处理。
after：在数据操作之后触发，因数据已变更，因此该触发器无法对已变更的数据进行处理（除非自己再写个变更语句），一般用于记录对数据的操作日志。
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;
查看
--方式一
SHOW TRIGGERS ;
--方式二（个人喜欢，常用字段为trigger_name,event_manipulation,event_object_table,action_orientation,action_timing,action_statement）：
select * from information_schema.triggers where trigger_name='trigger_4_user_before_insert'
删除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。
案例
现有一张用户表t_user，要求当在插入数据前校验age字段的值，当age<18时，将age的值改为18再插入，并在t_user_op_log表中记录插入的日志
用户表t_user建表语句为：
CREATE TABLE
t_user
(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(20),
password VARCHAR(20),
age INT,
sex CHAR(1),
email VARCHAR(20),
PRIMARY KEY (id),
INDEX idx_user_name_pwd (username, password)
)
操作日志表t_user_op_log建表语句为：
CREATE TABLE
t_user_op_log
(
id INT NOT NULL AUTO_INCREMENT,
op_log text,
PRIMARY KEY (id)
)
触发器语句如下：
create trigger trigger_4_user_before_insert
before insert
on t_user for each row
begin
set @o_age := new.age;--自定义变量o_age用于记录age原始值
if new.age<18 then
set new.age=18;
end if;
insert into t_user_op_log(op_log) values (
concat('操作时间：',now(),
' 操作内容：插入',
'id:',new.id,
' username:',new.username,
' password:',new.password,
' old_age:',@o_age,
' new.age:',new.age)
);
end;
在information_schema.triggers表中查看触发器详细信息，可以看到是一个insert类型的触发器，在事件开始之前触发
此时向t_user表中插入数据，age分别为17和19，看看有什么区别
insert into t_user (username,password,age) values ('dreaming','zym',17),('dreaming2','zym2',19)
可以看到，age为17的值在插入到表中时值已经变为18了
此时去查看t_user_op_log表，发现日志也已经记录了。
但是，由于t_user的id字段是自增主键，在向t_user表中插入日志时未指定id的值，因此before触发器是无法获取到插入到数据库中的t_user.id的值的。因此如果确实需要记录对数据的操作的话，个人建议还是将日志通过after触发器进行记录。
六、事务
6.1 定义
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
6.2 操作
6.2.1 未控制事务
当未指定事务时，每个语句都作为一个事务
6.2.2 控制事务方式一（关闭事务的自动提交）
1). 查看/设置事务提交方式
SELECT @@autocommit ;
SET @@autocommit = 0 ;
2). 提交事务
COMMIT;
3). 回滚事务
ROLLBACK;
注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
6.2.3 控制事务方式二（手动开启和提交事务）
1). 开启事务
START TRANSACTION 或 BEGIN ;
2). 提交事务
COMMIT;
3). 回滚事务
ROLLBACK;
转账案例
-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
6.3 事务的四大特性
事务四大特性
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性，简称ACID。
6.4 事务的并发问题
脏读 --> 不可重复读 --> 幻读
1). 赃读：一个事务读到另外一个事务还没有提交的数据。
比如B读取到了A未提交的数据。
2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
事务A两次读取同一条记录，但是读取到的数据却是不一样的。
3). 幻读：一个事务先后以同样的查询条件读取数据，但两次读取的数据条数不同。
在MySQL中因为MVCC机制的存在，基本解决了读的时候数据条数不一致的问题，但当执行插入或更新或删除时，还是会存在。
不可重复读和幻读，本质上都是两次查询结果前后不一致。
一般认为：
不可重复读指的是数据条数不变的情况下，该查询结果的行数据中的内容发生了变化；对应的是update操作
幻读指的是该查询结果对应的数据条数发生了变化。对应的是insert 和 delete操作
也有另一种说法认为：
不可重复读对应update和delete操作，因为这两个本身是已存在的数据，可以对相应的数据加锁
幻读对应insert操作，因为是不存在的数据，所以没法加锁
对此我个人的见解倾向于第一种，一是好记，二是InnoDB引擎是有间隙锁机制存在的，即使是暂未存在的数据也可以通过间隙锁进行加锁。当然本人理解很片面，如果您看到了这里觉得有异议的话欢迎指出。
补充：MySQL官方文档中关于幻读的定义如下
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
个人建议读一下MySQL的官网文档中关于这几个隔离级别的介绍：
MySQL官网文档-隔离级别
下面这个文章写的也很好，讲了MySQL在默认隔离级别Repeatable Read的情况下是如何规避幻读现象的，以及什么情况下还是会产生幻读现象：
并行事务会引发什么问题
6.5 事务的隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(默认) | × | × | √ |
| Serializable | × | × | × |
1). 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION; --8。0版本及之后
SELECT @@tx_isolation;--8.0版本之前
2). 设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意：事务隔离级别越高，数据越安全，但是性能越低。
SQL执行顺序
sql 语句关键词的执行顺序：
FROM 子句, 组装来自不同数据源的数据；
WHERE 子句, 基于指定的条件对记录进行筛选
GROUP BY 子句, 将数据划分为多个分组
使用聚合函数进行计算
使用 HAVING 子句筛选分组
计算所有的表达式
使用 ORDER BY 对结果集进行排序
即：from—>where—>group by—>having—>计算所有的表达式—>order by—>select 输出
766
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
