MySQL进阶 —— 超详细操作演示！！！（中）

数据库系列文章：

关系型数据库 — MySQL:

• 1、基础语法大全（上）—— MySQL概述、SQL
• 2、基础语法大全（中）—— 函数、约束
• 3、基础语法大全（下）—— 多表查询
• 4、基础语法大全（下） ——事务
• 5、MySQL进阶（上）—— 存储引擎、 索引
• 6、MySQL进阶（中）—— SQL 优化、视图/存储过程/触发器
• 7、MySQL进阶（下）—— 锁、InnoDB 引擎、MySQL 管理

非关系型数据库 — Redis:

• 1、Redis 的安装与配置
• 2、Redis 基本命令（上）
• 3、Redis 基本命令（下）
• 4、Redis 持久化
• 5、Redis 主从集群
• 6、Redis 分布式系统
• 7、Redis 缓存
• 8、Lua脚本详解
• 9、分布式锁

三、SQL 优化

3.1 插入数据

⭐️ 1) 、 insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

每执行一个 insert 都要与数据库建立连接进行网络传输，性能相对较低！

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....

• 1). 优化方案一 —— 批量插入数据
  • （500条 ~ 1000 条）

insert into tb_test values(1,'Tom'), (2,'Cat'), (3,'Jerry');

• 2). 优化方案二 —— 手动控制事务
  • （几万条，分割为多条 insert ）
  • mysql 中的事务提交方式默认为自动提交，意味着执行一条 insert 语句后，事务就提交了，会出现频繁的事务开启和提交！

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;

• 3). 优化方案三 —— 主键顺序插入，性能要高于乱序插入。
  • （取决于mysql 的数据组织结构)

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

⭐️ 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' ;

• • fields terminated by ',' : 每个字段使用 , 分隔；
  • lines terminated by '\n' ： 每一行使用 \n 分隔。

上传并查看 数据脚本文件 ：load_user_100w_sort.sql

主键顺序插入性能高于乱序插入 !

示例演示:

• A. 创建表结构

CREATE TABLE `tb_user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL,
	`password` VARCHAR(50) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`birthday` DATE DEFAULT NULL,
	`sex` CHAR(1) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

• B. 设置参数

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

• C. load加载数据

load data local infile '/data/sql/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

我们看到，插入100w的记录，57s就完成了，性能很好；若使用 insert 插入，至少要十来分钟！

在 load 时，主键顺序插入 性能高于乱序插入

3.2 主键优化

主键顺序插入 的性能是要高于乱序插入的。 这一小节，就来介绍一下具体的原因，然后再分析一下主键又该如何设计。

1). 数据组织方式

• 在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为 索引组织表(index organized table IOT)。

• 行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。
那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

2). 页分裂

• 页可以为空，也可以填充一半，也可以填充100%。每个页包含了 2-N 行数据(如果一行数据过大，会行溢出)，根据主键排列。

A. 主键顺序插入效果

• ①. 从磁盘中申请页， 主键顺序插入

• ②. 第一个页没有满，继续往第一页插入

• ③. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

• ④. 当第二页写满了，再往第三页写入

B. 主键乱序插入效果

• ①. 假如 1#,2# 页都已经写满了，存放了如图所示的数据

• ②. 此时再插入 id 为 50 的记录，我们来看看会发生什么现象
  • 会再次开启一个页，写入新的页中吗？

• • 不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在 47 之后。

• • 但是 47 所在的 1# 页，已经写满了，存储不了 50 对应的数据了。 那么此时会开辟一个新的页 3#。

• • 但是并不会直接将 50 存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在3#页，插入50 。

• • 移动数据，并插入 id 为 50 的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1# 的下一个页，应该是 3# ， 3# 的下一个页是 2# 。 所以，此时，需要重新设置链表指针。

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

3). 页合并

目前表中已有数据的索引结构(叶子节点)如下：

当我们对已有数据进行删除时，具体的效果如下:

• 当删除一行记录时，实际上记录 并没有被物理删除，只是记录被标记（ flaged）为删除并且它的空间变得允许被其他记录声明使用。

• 当我们继续删除 2# 的数据记录

• 当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（ 前或后）看看是否可以将两个页合并以优化空间使用。
  • MERGE_THRESHOLD：合并页的阈值，可以自己设置，在 创建表 或者 创建索引 时指定。

• 删除数据，并将页合并之后，再次插入新的数据 21 ，则直接插入 3#页

这个里面所发生的合并页的这个现象，就称之为 “页合并”。

4). 索引设计原则

• 满足业务需求的情况下，尽量降低主键的长度。
• 插入数据时，尽量选择 顺序插入，选择使用 AUTO_INCREMENT 自增主键。
• 尽量 不要使用UUID 做主键或者是 其他自然主键，如 身份证号。(无序且较长)
• 业务操作时，避免对主键的修改。

3.3 order by 优化

MySQL的排序，有两种方式：

• Using filesort : 通过 表的索引 或 全表扫描，读取满足条件的数据行，然后在 排序缓冲区sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index， 不需要 额外排序，操作效率高。

对于以上的两种排序方式，Using index 的性能高，而 Using filesort 的性能低，我们在优化排序操作时，尽量要优化为 Using index。

接下来，我们来做一个测试：

A. 数据准备

• 把之前测试时，为 tb_user 表所建立的部分索引直接删除掉

drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;

B. 执行排序SQL

explain select id, age, phone from tb_user order by age ;

explain select id, age, phone from tb_user order by age, phone ;

• 由于 age, phone 都没有索引，所以此时 再排序 时，出现 Using filesort， 排序性能较低。

C. 创建索引

-- 创建索引
create index idx_user_age_phone on tb_user(age,phone);

D. 创建索引后，根据 age, phone进行升序排序

explain select id, age, phone from tb_user order by age;

explain select id, age, phone from tb_user order by age , phone;

• 建立索引之后，再次进行排序查询，就由原来的 Using filesort， 变为了 Using index，性能就是比较高的了。

E. 创建索引后，根据 age, phone进行降序排序

explain select id, age, phone from tb_user order by age desc, phone desc ;

也出现 Using index， 但是此时 Extra 中出现了 Backward index scan，这个代表 反向扫描索引，

• 因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，
• 而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。
• 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。

F. 根据 phone，age进行升序排序，phone在前，age在后。

explain select id, age, phone from tb_user order by phone, age;

• 排序时,也需要满足最左前缀法则, 否则也会出现 filesort。
• 因为在创建索引的时候， age是第一个字段，phone 是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。

G. 根据 age, phone进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc, phone desc ;

• 因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现 Using filesort。
• 为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。

H. 创建联合索引( age 升序排序，phone 倒序排序)

create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

I. 然后再次执行如下SQL

explain select id, age, phone from tb_user order by age asc, phone desc ;

升序/降序联合索引结构图示:

由上述的测试,我们得出 order by 优化原则:

• A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• B. 尽量使用覆盖索引。
  
• C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
• D. 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size (默认256k)。
  

3.4 group by 优化

分组操作，我们主要来看看索引对于分组操作的影响。

首先我们先将 tb_user 表的索引全部删除掉 。

drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone on tb_user;
drop index idx_user_age_phone_ad on tb_user;

接下来，在没有索引的情况下，执行如下SQL，查询执行计划：

explain select profession, count(*) from tb_user group by profession ;

• Using temporary ：临时表，这个性能也比较低。

然后，我们在针对于 profession ， age， status 创建一个联合索引。

create index idx_user_pro_age_sta on tb_user(profession, age , status);

紧接着，再执行前面相同的SQL查看执行计划。

explain select profession, count(*) from tb_user group by profession ;

再执行如下的分组查询SQL，查看执行计划：

explain select age, count(*) from tb_user group by age ;

explain select profession, age, count(*) from tb_user group by profession, age ;

• 我们发现，如果仅仅根据 age 分组，就会出现 Using temporary ；
• 而如果是 根据 profession, age 两个字段同时分组，则不会出现 Using temporary。
• 原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

• A. 在分组操作时，可以通过索引来提高效率。
• B. 分组操作时，索引的使用也是满足最左前缀法则的。

3.5 limit 优化

在数据量比较大时，如果进行 limit 分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行 limit 分页查询耗时对比：

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

• 因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过 覆盖索引 + 子查询 形式进行优化。

explain 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 优化

⭐️ 1) 、概述

select count(*) from tb_user ;

在之前的测试中，我们发现，如果数据量很大，在执行 count 操作时，是非常耗时的。

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是 带条件 的 count，MyISAM 也慢。
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升 InnoDB 表的 count 效率，主要的优化思路：

• 自己计数(可以借助于 redis 这样的数据库进行, 但是如果是带条件的 count 又比较麻烦了)。

⭐️ 2) 、count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

• 用法：count（*）、count（主键）、count（字段）、count（数字）

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以 尽量使用 count(*)。

3.7 update 优化

我们主要需要注意一下 update 语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

• 当我们在执行删除的SQL语句时，会锁定 id 为 1 这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

• 当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该 update 语句的性能
  大大降低。

InnoDB 的==行锁是针对索引加的锁==，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

🚀🚀🚀 SQL优化 快速食用：---------------------------------------------------------------------------------------------------------------------------------------------------------------------------->

----------------------------------------
--大部分是根据索引优化
----------------------------------------

## 1. 插入数据
insert : 批量插入、手动控制事务、主键顺序插入
大批量插入 : load data local infile

## 2. 主键优化
主键长度尽量短、顺序插入  AUTO_INCREMENT(主键自增) ~~UUID~~(不要使用) 

## 3. order by 优化
using index : 直接通过索引返回数据， 性能高
using filesort : 需要将返回的结果在排序缓冲区排序

## 4. grop by 优化
索引，多字段分组满足最左前缀法则

## 5. limit 优化
覆盖索引 + 子查询

## 6. count 优化
性能 : count(字段) < count(主键 id ) < count(2) ~= count(*)

## 7. update 优化
尽量根据主键/索引字段进行数据更新

四、视图/存储过程/触发器

MySQL 中的存储对象 ：视图、存储过程、存储函数、触发器

4.1 视图

⭐️ 1) 介绍

• 视图（ View ）是一种 虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表(基表)，并且是在使用视图时动态生成的。
• 通俗的讲，视图 只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

⭐️ 2) 语法

• a. 创建

create [OR replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]

• b. 查询

查看创建视图语句：show create view 视图名称;
查看视图数据：select * from 视图名称 ...... ;

• c. 修改

方式一：create [OR replace] view 视图名称[(列名列表)] as select语句 [ with [cascaded | local] check option]
方式二：alter view 视图名称[(列名列表)] as select语句 [ with [cascaded | local] check option]

• d. 删除

drop view [if exists] 视图名称 [, 视图名称] ...

演示示例：

student、course、student_course 三个数据表：

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;

-- 查询视图
show create view stu_v_1; // 查看创建视图的语句

select * from stu_v_1; //视图是一张虚拟存在的表，表怎么查，视图怎么查（可以像操作表一样操作视图）
select * from stu_v_1 where id < 3;

-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;

alter view stu_v_1 as select id,name from student where id <= 10;

-- 删除视图
drop view if exists stu_v_1;

创建视图：

修改视图：

上述我们演示了，视图应该如何 创建、查询、修改、删除，那么我们能不能通过视图来 插入、更新 数据 呢？ 接下来，做一个测试。

create or replace view stu_v_1 as select id,name from student where id <= 10 ;

select * from stu_v_1;

insert into stu_v_1 values(6,'Tom');

insert into stu_v_1 values(17,'Tom22');

执行上述的SQL，我们会发现，id 为 6 和 17 的数据都是可以成功插入的。 但是我们执行查询，查询出来的数据，却没有 id 为 17 的记录。

• 因为我们在创建视图的时候，指定的条件为 id<=10, id 为 17 的数据，是不符合条件的，所以没有查询出来，但是这条数据确实是已经成功的插入到了基表中。
• 如果我们定义视图时，如果指定了条件，然后我们在 插入、修改、删除 数据时，是否可以做到必须满足条件才能操作，否则不能够操作呢？
  • 答案是可以的，这就需要借助于 视图的检查选项 了。

⭐️ 3) 检查选项

当使用 with check option 子句创建视图时，MySQL会 通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。

• MySQL允许基于另一个视图 创建视图，它还会检查依赖视图中的规则以保持一致性。
• 为了确定检查的范围，mysql提供了两个选项：
  • CASCADED 和 LOCAL，默认值为 CASCADED 。

(1). CASCADED

• 级联：
  • 比如，v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 cascaded，但是v1 视图创建时未指定检查选项。
  • 则在执行检查时，不仅会检查 v2，还会级联检查 v2 的关联视图 v1。

递归检查，如果有一个不满足，则插入失败！

(2). LOCAL

• 本地：
  • 比如，v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 local ，但是 v1 视图创建时未指定检查选项。
  • 则在执行检查时，只会检查 v2，不会检查 v2 的关联视图 v1。
    

⭐️ 4) 视图的更新

要使视图可更新，视图中的行 与 基础表中的行 之间必须存在 一对一的关系 。如果视图包含以下任何一项，则该视图不可更新：

• A. 聚合函数 或 窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
• B. DISTINCT
• C. GROUP BY
• D. HAVING
• E. UNION 或者 UNION ALL

示例演示:

create view stu_v_count as select count(*) from student;

上述的视图中，就只有一个单行单列的数据，如果我们对这个视图进行更新或插入的，将会报错。

insert into stu_v_count values(10);

⭐️ 5) 视图作用

(1). 简单

• 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。
• 那些被 经常使用的查询 可以被定义为 视图，从而使得用户不必为以后的操作每次指定全部的条件。

(2). 安全

• 数据库可以授权，但不能 授权 到数据库 特定行 和 特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

(3). 数据独立

• 视图可帮助用户 屏蔽 真实表结构变化带来的影响。

⭐️ 6) 案例

(1). 为了保证数据库表的安全性，开发人员在操作 tb_user 表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。

create view tb_user_view as select id, name, profession, age, gender, status, createtime from tb_user;

select * from tb_user_view;

(2). 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;

select * from tb_stu_course_view;

4.2 存储过程

⭐️ 1) 介绍

存储过程 是 事先经过编译 并 存储在数据库中的一段 SQL 语句的集合 :

• 调用存储过程可以 简化 应用开发人员的很多工作，减少 数据在 数据库 和 应用服务器 之间的传输，对于提高数据处理的效率是有好处的。
• 存储过程思想上很简单，就是数据库 SQL 语言层面的 代码封装 与 重用。

• 封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
• 可以 接收参数，也可以 返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。
• 减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要 网络交互一次 可能就可以了。

⭐️ 2) 基本语法

(1). 创建

create procedure 存储过程名称 ([ 参数列表 ])
begin
	-- SQL 语句
end ;

(2). 调用

call 名称 ([ 参数 ]) ;

(3). 查看

select * from information_schema.routines where routines_schema = 'xxx'; -- 查询指定数据库的存储过程及状态信息

show create procedure 存储过程名称 ; -- 查询某个存储过程的定义

(4). 删除

drop procedure [ if exists ] 存储过程名称 ;

注意:

• 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。
  
  改回原来的结束符，再执行 delimiter ; 即可；

演示示例:

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
	select count(*) from student;
end;

-- 调用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'rmzh';

show create procedure p1;

-- 删除
drop procedure if exists p1;

⭐️ 3) 变量

• 在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

a 、系统变量

• 系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为 全局变量（ GLOBAL ）、会话变量（SESSION）。

(1). 查看系统变量

show [ session | global ] variables ; -- 查看所有系统变量

show [ session | global ] variables like '......'; -- 可以通过 like 模糊匹配方式查找变量

select @@[session | global] 系统变量名; -- 查看指定变量的值

(2). 设置系统变量

set [ session | global ] 系统变量名 = 值 ;
set @@[ session | global ] 系统变量名 = 值 ;

注意:

• 如果没有指定 SESSION / GLOBAL，默认是 SESSION，会话变量。

mysql服务 重新启动 之后，所设置的  全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

• A. 全局变量( GLOBAL ): 全局变量针对于 所有的会话。
• B. 会话变量( SESSION ): 会话变量针对于 单个会话，在另外一个会话窗口就不生效了。

演示示例:

-- 查看系统变量
show session variables ;

show session variables like 'auto%';
show global variables like 'auto%';

select @@global.autocommit;
select @@session.autocommit;

-- 设置系统变量
set session autocommit = 1;

insert into course(id, name) VALUES (6, 'ES');

set global autocommit = 0;

select @@global.autocommit;

b 、用户定义变量

• 用户定义变量 是用户 根据需要自己定义的变量，用户变量 不用提前声明，在用的时候直接用 “@变量名” 使用就可以。
• 其 作用域 为 当前连接。

(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 = 'rmzh';
set @myage := 18;
set @mygender := '男',@myhobby := 'sleep';

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;

select @abc;

c 、局部变量

• 局部变量 是根据需要定义的在局部生效的变量，访问之前，需要 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();

⭐️ 4) if

(1). 介绍

• if 用于做条件判断，具体的语法结构为：

if 条件1 then
	.....
elseif 条件2 then 	-- 可选
	.....
else				-- 可选
	.....
end if;

• 在 if 条件判断的结构中，else if 结构可以有多个，也可以没有。 else 结构可以有，也可以没有。

(2). 案例

• 根据定义的分数 score 变量，判定当前分数对应的分数等级。
  • score >= 85分，等级为优秀。
  • score >= 60分 且 score < 85 分，等级为 及格。
  • score < 60 分，等级为 不及格。

create procedure p3()
begin
	declare score int default 58;
	declare result varchar(10);
	
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
	
	select result;
end;

call p3();

• 上述的需求我们虽然已经实现了，但是也存在一些问题，比如：score 分数我们是在存储过程中定义死的，而且最终计算出来的分数等级，我们也仅仅是最终查询展示出来而已。
• 那么我们能不能，把 score 分数 动态的传递进来，计算出来的 分数等级 是否可以作为 返回值 返回呢？
  • 答案是肯定的，我们可以通过接下来所讲解的 参数 来解决上述的问题。

⭐️ 5) 参数

(1). 介绍

• 参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下：

用法：

create procedure 存储过程名称 ([ in/out/inout 参数名 参数类型 ])
begin
	-- SQL语句
end ;

(2). 案例一

• 根据传入参数 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;

(3). 案例二

• 将传入的 200 分制的分数，进行换算，换算成百分制，然后返回。

create procedure p5(inout score double)
begin
	set score := score * 0.5;
end;

set @score = 198;
call p5(@score);

select @score;

⭐️ 6) case

(1). 介绍

• case 结构及作用，和我们在基础篇中所讲解的 流程控制函数 很类似。有两种语法格式：

语法1：

-- 含义： 当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;

语法2：

-- 含义： 当条件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;

(2). 案例

根据传入的月份，判定月份所属的季节（要求采用 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 进行连接。

⭐️ 7) while

(1). 介绍

• while 循环是 有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
	SQL逻辑...
end while;

(2). 案例

• 计算从 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);

⭐️ 8) repeat

(1). 介绍

• repeat 是 有条件的循环控制语句, 当 满足 until 声明的条件 的时候，则 退出循环 。具体语法为：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
	SQL逻辑...
	until 条件
end repeat;

(2). 案例

• 计算从 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);

⭐️ 9) loop

(1). 介绍

• LOOP 实现简单的循环，如果 不在SQL逻辑中增加退出循环的条件，可以用其来实现 简单的死循环。
• LOOP 可以配合一下两个语句使用：
  • LEAVE ：配合循环使用，退出循环。
  • ITERATE：必须用在循环中，作用是 跳过 当前循环 剩下的语句，直接进入下一次循环。

[begin_label:] loop
	SQL逻辑...
end loop [end_label];

leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环

上述语法中出现的 begin_label，end_label，label 指的都是我们所 自定义的标记。

(2). 案例一

• 计算从 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);

(3). 案例二

• 计算从 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);

⭐️ 10) 游标

(1). 介绍

• 游标（CURSOR）是用来 存储查询结果集 的数据类型 , 在 存储过程和函数 中可以使用游标对结果集进行 循环的处理。
• 游标的使用包括游标的 声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

A. 声明游标

declare 游标名称 cursor for 查询语句 ;

B. 打开游标

open 游标名称 ;

C. 获取游标记录

fetch 游标名称 into 变量 [, 变量 ] ;

D. 关闭游标

close 游标名称 ;

(2). 案例

• 根据传入的参数 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循环中，并没有退出条件。
• 当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。

⭐️ 11) 条件处理程序

(1). 介绍

• 条件处理程序（ 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代码的简写

(2). 案例

• 我们继续来完成在上一小节提出的这个需求，并解决其中的问题。
• 根据传入的参数 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

4.3 存储函数

⭐️ 1) 介绍

• 存储函数 是 有返回值 的 存储过程，存储函数的参数只能是 IN 类型的。具体语法如下：

create function 存储函数名称 ([ 参数列表 ])
returns type [characteristic ...]
begin
	-- SQL语句
	return ...;
end ;

characteristic 说明：

• determinstic：相同的输入参数总是产生相同的结果
• NO SQL ：不包含 SQL 语句。
• reads SQL data：包含读取数据的语句，但不包含写入数据的语句。

⭐️ 2). 案例

• 计算从 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 特性，否则就会报如下错误 !

4.4 触发器

⭐️ 1) 介绍

• 触发器 是与 表 有关的 数据库对象，指在 insert/update/delete 之前( BEFORE )或之后( AFTER )，触发 并 执行触发器中定义的SQL语句集合。
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

• 现在触发器还只 支持 行级触发，不支持 语句级触发。

⭐️ 2) 语法

(1). 创建

create trigger trigger_name
before/after insert/update/delete on tbl_name for each row -- 行级触发器
begin
	trigger_stmt ;
end;

(2). 查看

show triggers ;

(3). 删除

drop trigger [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。

⭐️ 3) 案例

• 通过触发器记录 tb_user 表的 数据变更日志，将变更日志插入到日志表 user_logs 中, 包含 增加, 修改 , 删除 ;

表结构准备:

-- 准备工作 : 日志表 user_logs
create table user_logs(
	id int(11) not null auto_increment,
	operation varchar(20) not null comment '操作类型, insert/update/delete',
	operate_time datetime not null comment '操作时间',
	operate_id int(11) not null comment '操作的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(`id`)
)engine=innodb default charset=utf8;

A. 插入数据触发器

create trigger tb_user_insert_trigger
	after insert on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

• 测试:

-- 查看
show triggers ;

-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (25,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

• 测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

B. 修改数据触发器

create trigger tb_user_update_trigger
	after update on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
(null, 'update', now(), new.id, concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession, ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

• 测试:

-- 查看
show triggers ;

-- 更新
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;

• 测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

C. 删除数据触发器

create trigger tb_user_delete_trigger
	after delete on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'delete', now(), old.id, concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;

• 测试:

-- 查看
show triggers ;

-- 删除数据
delete from tb_user where id = 26;

• 测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

🚀🚀🚀 视图/存储过程/触发器 快速食用：---------------------------------------------------------------------------------------------------------------------------------------------------------------------------->

## 1、视图（view）
-- 虚拟存在的表，不保存查询结果，只保存查询的SQL逻辑
-- 简单、安全、数据独立

# a. 创建
create [OR replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]

# b. 查询
show create view 视图名称; # 查看创建视图语句
select * from 视图名称 ...... ; #查看视图数据

# c. 修改
--方式一：
create [OR replace] view 视图名称[(列名列表)] as select语句 [ with [cascaded | local] check option]
--方式二：
alter view 视图名称[(列名列表)] as select语句 [ with [cascaded | local] check option]

# d. 删除  
drop view [if exists] 视图名称 [, 视图名称] ...



## 2、存储过程（procedure）
-- 事先定义并存储在数据库中的一段SQL语句的集合
-- 减少网络交互，提高性能、封装重用
-- 变量、if、case、参数(in/out/inout)、while、repeat、loop、cursor、handler

# a. 创建
create procedure 存储过程名称 ([ 参数列表 ])
begin
	-- SQL 语句
end ;

# b. 调用
call 名称 ([ 参数 ]) ;

# c. 查看
select * from information_schema.routines where routines_schema = 'xxx'; -- 查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称 ; -- 查询某个存储过程的定义

# d. 删除 
drop procedure [ if exists ] 存储过程名称 ;



## 3、存储函数（function）# 用的不多
-- 存储函数是有返回值的存储过程，参数类型只能为 in 类型
-- 存储函数可以被存储过程替代
create function 存储函数名称 ([ 参数列表 ])
returns type [characteristic ...]
begin
	-- SQL语句
	return ...;
end ;



## 4、触发器（trigger）
-- 可以在表数据进行 insert、update、delete 之前或之后触发
-- 保证数据完整性、日志记录、数据校验

# a. 创建
create trigger trigger_name
before/after insert/update/delete on tbl_name for each row -- 行级触发器
begin
	trigger_stmt ;
end;

# b. 查看
show triggers ;

# c. 删除 
drop trigger [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。

注：仅供学习参考，如有不足，欢迎指正！！！