MYSQL进阶部分知识点汇总~

事务：

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撒销操作请求，即这些操作要么同时成功，要么同时失败。

默认MyS0L的事务是自动提交的，也就是说，当执行一条DML语句，MYSQL会立即隐式的提交事务。

事务操作：

查看/设置事务提交方式

//输出结果为1表示开启事务自动提交
select @@autocommit;
//关闭事务自动提交
set @@autocommit=0;

//提交事务
commit;
//回滚事务
rollback;

start 

事务隔离级别就是为了解决并发事务产生的问题的

左连接和右连接：

左连接：

左表中的记录都会被查询出来

注意：这里的Max.Degree指的是指针的个数，并不是元素的个数，n哥元素有n+1个指针

当name字段的值都是英文时，B+树会根据英文字母的ASCII码值进行排序。ASCII码值小的英文字母会被存储在B+树的左侧，而ASCII码值大的英文字母会被存储在B+树的右侧。这样可以保证在进行查找操作时，可以快速定位到需要的数据，提高查询效率。

创建索引：

索引名格式：idx_表名_字段名

-- 唯一索引：该字段中的值不允许重复，例如phone，id等
create unique index 索引名 on 表名(列名);
-- 默认索引：该字段中的值可以重复，例如name等
create  index 索引名 on 表名(列名);
-- 全文索引只可针对字符串类型的，例如email等
create fulltext index 索引名 on 表名(列名);

查看索引：

show index from 表名;

删除索引：

drop index 索引名 on 表名;

修改MySQL的密码：

-- 修改本机MySQL的密码
alter user 'root'@'localhost' identified by'new_password';
--修改远程机的密码
alter user 'root'@'%' identified with mysql_native_password by'112899';

SQL性能分析：

MYSQL客户端连接成功后，通过show global status like "Com___";命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

慢查询日志：

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

slow_query_log=1
long_query_time=2

-- 查看是否开启慢查询？
show variables like '%query%';
-- 设置慢查询为开启
set global slow_query_log='ON';
-- 显示慢查询的输出格式
show variables like '%log_output%';
-- 设置慢查询的输出格式
set global log_output='FILE';
set global log_output='TABLE';
set global log_output='FILE,TABLE';
-- 设置睡眠时间
select sleep(11);
select * from user;
-- 查询慢查询日志中的内容，这里面包含了所有慢查询的SQL语句详情
select * from mysql.slow_log;

Profile:

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作:

select @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:

set profiling=1;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定queny_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

-- 查看是否支持profiling
select @@have_profiling;
-- 查看profiling是否开启
select @@profiling;
-- 手动开启profiling
set @@profiling=1;
-- 查看所有SQL语句的耗时情况
show profiles;
-- 查看profiles中指定的SQL语句各个阶段的耗时情况
show profile for query  3;
-- 查看profiles中指定的SQL语句各个阶段的CPU的使用情况
show profile cpu for query 3;

explain执行计划：

EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT语句执行过程中表如何连接和连接的顺序。

explain/desc select语句;

explain执行计划各字段含义：

id:select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下:id不同，值越大，越先执行)

select_type:表示 SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMAR(主查询，即外展的查询)、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 的值越大越好。

虽然说连接性能null时最好的，但是我们一般不会优化到此步，只有当我们不访问任何表的时候连接类型才为null，例如（如下所示）：

索引使用：

验证索引效率：我们可以在未创建索引之前执行一条SQL语句，查看它的耗时，然后建立索引之后再去查看一次。

创建索引的过程就是创建B+树的过程，如果该字段涉及的数据量很大的话，创建的过程也是非常耗时的。

索引的使用—最左前缀法则：

如果索引了多列(联合索引)，要遵守最左前掇法则，最左前级法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)

举例：

-- 创建由id,name,age字段组成的联合索引
create index  idx_id_name_age on user (id,name,age);
-- 使用到了联合索引
explain select * from user where id=1 and name="张三" and age=21;
explain select * from user where id=1 and age=21;
explain select * from user where id=1 and name="张三";
explain select * from user where name="张三" and id=1  and age=21;
-- 未使用到联合索引
explain select * from user where  name="张三" and age=21;

注意：联合索引中最左侧的字段[第一个字段]必须存在，而不是位置必须在最左侧

如果要查看某个字段是以什么开头或者什么结尾的，我们除了使用模糊查询之外，还可以使用字符串截取函数，如下所示：

//substring函数用于截取字符串的部分内容，其语法为substring(str, start, length)，其中str为要截取的字符串，start为起始位置（从1开始计数），length为要截取的长度。
select * from user where substring(name,1,1)="王";

导致索引失效的情况：

1：不要在索引列上进行函数运算操作，否则索引将失效，注意无论是由该字段形成的单列索引还是该字段与其他字段形成的覆盖索引，都会失效

select * from user where substring(name,1,1)="王";

2：当进行头部模糊匹配时，会导致索引失效，而进行尾部模糊匹配时，不会导致索引失效

//头部模糊匹配
select * from user where name like "%三";
//尾部模糊匹配
select * from user where name like "张%";

3:假设where条件中包含or运算，那么必须or前后的字段都有索引，否则所有的索引都会失效

//name列有索引，而age列没有索引，因此name列的索引也不会被使用到
select * from user where name="张三" or age=22;

4：数据分布影响

如果MYSQL评估使用索引比全表更慢，那么则不使用索引，例如表中的数据都不满足当前where后面的条件，假设我们需要判断某个字段的值是否为null，而表中的数据大都不是null，那么此时会走索引，反之才会走全表扫描

SQL提示：

是优化数据库的一个重要的手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的

-- 使用idx_age索引 默认情况
explain select  * from user where age=21;
-- 建议使用idx_age索引，最终是否使用还需要MySQL进行评估
explain select  * from user use index(idx_age) where age=21;
-- 忽略使用idx_age索引
explain select  * from user ignore index(idx_age) where age=21;
-- 强迫使用idx_age索引
explain select  * from user force index(idx_age) where age=21;

覆盖索引：

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经被全部找到），减少select *

explain执行计划结果中的extra列：using index condition表示查找使用了索引，但是需要回表查询数据，using where ,using index表示查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。

除非你创建的联合索引包含了表中的所有字段，否则很容易出现回表查询

针对username和password创建联合索引，那么其叶子节点中对应的值就是id，此时我们就能通过覆盖索引找到对应的值，而不是根据username创建单列索引，因为这样会进行回表查询

前缀索引：

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

//如下所示，我们可以将phone的前四位建立索引
create index idx_email on user(phone(4));

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高,唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

//如下所示：计算name字段的索引选择性
select count(distinct name)/count(*) from user;
//查看截取部分name字段的索引选择性
select count(distinct substring(name,1,2))/count(*) from user;

我们可以通过尝试截取不同长度的字符串来查看其索引选择性的大小，如果我们想选择性越高越好，那么可能需要截取的字符串会更长一些，如果选择性要求不是很高，那么我们可以选择截取截取较短的，截取前五个和前9个小的效果是一致的，我们需要尽可能的保证它是比较小的

前缀索引:

单列索引与联合索引：

单列索引:即一个索引只包含单个列。

联合索引:即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

多条件联合查询时，MySOL优化器会评估哪个字段的索引效率更高，会速择该索引完成本次查询。

之所以选择联合索引是因为它的性能比较高，并且我们查询某些字段的时候，避免了回表查询，需要注意的是我们在创建联合索引的时候，需要考虑它的顺序问题，因为联合索引遵循最左前缀法则。

索引设计原则：

1：针对于数据量较大，且查询比较频繁的表建立索引

例如：如果一张表的数据量超过100万，那么我们就要考虑去创建索引。

2:针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引

3:尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

例如：用户名，用户身份证号等，而对于用户的性别，表示状态的字段，逻辑制除的字段，区分度是比较低的

4：如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5：尽量使用联合索引，减少单列索引，查询时，联合索引很多时可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6:要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增别改的效率。

7:如果案引列不能存储NULL值，请在创建表时使用NOTNULL约来它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

insert优化：

使用批量插入方法：

insert into user values  ("王源","男",24,"重庆市","7896521@qq.com",6,21831793)
						,("易烊千玺","男",24,"怀化市","3842有9742@qq.com",7,213618)
						,("蔡徐坤","男",25,"北京市","5657687564@qq.com",8,21632131);

使用手动提交事务的方法：

start transaction;
insert into user values  ("郑凯","男",42,"北京市","对213131313qq.com",9,23231793);
insert into user values ("何炅","男",45,"北京市","38429742@qq.com",10,213618);
insert into user values ("张元英","女",19,"韩国","23121331564@qq.com",11,22112331);
commit;

大批量插入数据：

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用Mysql数据库提供的load指令进行插入

步骤如下：

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data1.txt'
into table user
fields terminated by ',' 
lines terminated by "\n"
(name,sex,age,address,email,id,phone);

主键顺序插入的性能高于乱序插入，因为顺序的话避免了索引树中的多次比较

主键优化：

页分裂：

页合并：

主键设计原则：

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

主键索引是唯一的只有一个的，但是二级索引可以有多个，而二级索引下面对的就是主键值，因此如果主键长度太长的话，会使得二级索引占据更大的内存空间。

order by优化：

using filesort :通过表的索引或者全表扫描，读取满足的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

Using index:通过有序索引顺序扫描直接返回有序数组，这种情况即为Using index,不需要额外排序，操作效率高。

-- 创建索引前全表扫描，创建索引后使用了索引
explain select id,age,phone from user order by age,phone;
-- 使用了索引并且使用了回表查询
explain select id,age,phone from user order by age desc,phone desc;

-- 一个升序，一个降序:使用索引，并且进行全表查询
explain select id,age,phone from user order by age ,phone desc;
-- 创建对应的索引
create index idx_user_age_phone on user(age asc,phone desc);
-- 重新进行查询:只使用了索引
explain select id,age,phone from user order by age ,phone desc;

//查看缓冲区的大小
show variables like "sort_buffer_size";

1：根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

2：尽量使用覆盖索引。

3：多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

4:如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort buffersize(默认256k)。

在分组操作时，我们可以通过索引来提高效率，此时索引的使用也是满足最左前缀法则的

-- 未创建索引前,执行下述SQL语句，结果是Using temporary
 explain select name,count(*) from user group by name,age;
 -- 创建对应的索引
  create index idx_user_name_age on user(name,age);
 -- 创建索引之后执行下述SQL语句，结果是：using  index
 explain select name,count(*) from user group by name,age;

limit优化：

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

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

 explain select  * from user t,(select id from user order by id limit 1,5) a where t.id=a.id;

count优化：

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高;

InnoDB 引擎就麻烦了，它执行 count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

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

用法:count(*)、count(主键)、count(字段)、count(1)

count(主键)：

InnoDB 引擎会遍历整张表，把每一行的 主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。

count(字段)：

没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加。

有not null 约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count(1)：

InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count(*)：
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话：

count(字段)<count(主键id)<count(1)=çouni()，所以尽量使用 count()

update优化：

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

因此未创建索引的字段如果被当做where条件，那么就会导致整张表被锁住

视图：

视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

-- 如果视图存在那么会被替换，如果不存在则会被创建
-- with：指定视图的安全选项，包括cascaded和local。 cascaded：表示对视图的更新操作会级联到基表。local：表示对视图的更新操作只会影响到视图本身。
-- check option：表示对视图进行更新时要检查WHERE子句中的条件。
create or replace view 视图名称 as select语句 with|cascaed|local|check option]
-- 查询视图语句：用于查询视图中的所有数据。
select * from 视图名称;
-- 查询创建视图数据：用于查询创建视图的SQL语句，即包含创建视图时使用的select语句和其他选项。
show create view 视图名称;
-- 创建或替换视图tb_user，查询语句为从user表中选择name字段。如果视图已经存在，则会被替换。
create or replace view 视图名称 as select语句 with|cascaed|local|check option
-- 修改视图的查询语句为新的Select语句。这种方式会覆盖原有的视图定义。
alter view 视图名称 as 新的select语句
-- 如果该视图存在，那么就删除它
drop view if exists tb_user;

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检査正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySOL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项CASCADED和LOCAL，默认值为CASCADED。

-- 先根据第一句的SQL语句创建出v1视图
create view v1 as select * from user where age>=20  and age<=25 with cascaded check option;
-- 第二句SQL语句创建v2视图是在V1视图的基础之上
create view v2 as select * from v1 where age<=30  with cascaded check option;

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新
1.聚合函数或窗口函数SUM()、MIN()、MAX()、COUNT()等
2.DISTINCT
3.GROUP BY
4.HAVING
5.UNION 或者 UNION ALL

视图的作用：

简单：

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

安全：

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

数据独立：

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

存储过程：

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

特点：

封装，复用,可以接收参数，也可以返回数据,减少网络交互，效率提升

存储过程的创建与调用：

-- 创建存储过程,DELIMITER定义SQL语句结束的标识符号，如果未定义默认是;结束，因此如果我们习惯性的使用;作为结束符来编写存储过程，很有可能被Mysql认为出现语法上的错误
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
	SELECT * FROM user;
END //
DELIMITER ;
-- 调用存储过程
call p1()

-- 1. 查看特定数据库中的所有存储过程：
SHOW PROCEDURE STATUS WHERE Db = 'wjr';
-- 2. 查看某个存储过程的定义：
SHOW create procedure p1;
-- 3.删除
drop procedure if exists p1;

变量：

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

-- 查看所有会话/全局系统变量
show session|global variables;
-- 可以通过like模糊匹配方式查找变量,例如如下所示
show global variables like "auto%";

-- 查看指定的变量
select @@session  系统变量名;

-- 设置系统变量，默认为session，当前会话中有效，global：表示全局有效，但是一旦服务器重启，global级别的设置也会失效，如果我们想实现即使服务器重启也生效，那么可以在MYSQL的配置文件中进行设置
set session|global 系统变量名=值
set @@session|global 系统变量名=值

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

在MYSQL中"="即可作为赋值运算符，也可以作为比较运算符。

-- 定义一个或者多个变量
-- 方法1：使用set实现赋值功能
set @myname="蔡徐坤";
set @myage:=23;
set @myaddress:="北京市朝阳区",@myemail="123456@qq.com";
-- 方法2：使用select语句进行赋值
select @myhooby := "吃饭";
select count(*) into @mycount from user;


-- 查看一个或多个变量名的值
select @myhobby;
select @myname,@myage,@myaddress,@myemail,@mycount;

注：如果对用户定义的变量没有进行声明或者初始化，那么获取到的值为null

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN...END块。

局部变量：

DELIMITER &&
-- 创建存储过程
create procedure p2()
-- mycount变量的作用域是在begin和end之间
-- 定义局部变量  declare 变量名 变量类型 default 默认值;
begin
 declare mycount int default 0;
 select count(*) from user into mycount;
 select @mycount;
end&&
-- 调用存储过程
call p2()

if语句的使用：

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

if语句的简单使用：

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

-- 定义存储过程
DELIMITER &&
create procedure p3()
begin
    -- 判定58默认的归属等级--这里就表示判断58属于哪个级别
	declare score int default 58;
    declare grade varchar(20);
    if score >= 85  then
        set grade := "优秀";
	-- else和if之间不可以有空格，否则mysql就会将二者作为不同的语句进行处理，而不是当成elseif进行处理
	elseif score >= 60 then
		 set grade := "及格";
	else 
		set grade := "不及格";
	end if;
	select grade;
end &&
-- 调用存储过程
call p3();

在上述的小案例中，我们是将我们需要判断的值58以硬编码的形式写在了declare定义变量的地方，这通常并不是我们想要的结果，那么下面我们就在存储过程中，通过传递值返回值方式。

因此我们想将上述需求进行修改，如下所示：

根据传入参数score，判定当前分数对应的分数等级，并返回
score >= 85分，等级为优秀。
score >=60分且score<85分，等级为及格。
score<60分，等级为不及格。

语法为:

create procedure 存储过程的名字(in|out|inout 参数名  参数类型)
begin
	--SQL语句
end;

写法如下所示：

DELIMITER &&
-- in表示score为我们传入的参数,out表示result为我们输出的参数
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&&
-- 定义用户变量用来接收存储过程返回的结果
call p4(99,@result);
-- 查看存储过程的结果
select @result;

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

DELIMITER &&
-- 表示score既是输入又是输出
create procedure p5(inout score double)
begin
	set score:=score*0.5;
end&&

-- 先为score赋值传入的值
set @score=198;
call p5(@score);
-- 查看存储过程的结果
select @score;

case语法结构完成判定操作：

根据传入的月份，判定月份所属的季节(要求采用case结构)
1.1-3月份，为第一季度
2.4-6月份，为第二季度
3. 7-9月份，为第三季度
4.10-12月份，为第四季度

DELIMITER &&
create procedure p6(in month int)
begin
	declare result int ;
    case 
     when month >=1 and month <=3 then
		set result:=1;
	  when month >=4 and month <=6 then
		set result:=2;
       when month >=7 and month <=9 then
		set result:=3;
       when month >=10 and month <=12 then
		set result:=4;
      end case;
      select concat("你输入的月份为",month,"它是属于",result,"季度");
end&&
call p6(5);

while语法结构：

语法：

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

小案例：

计算从1累加到n的值，n为传入的参数值。

DELIMITER &&
create procedure p9(in n int)
begin 
    --定义局部变量sum用来记录累加值
   --如果sum没有默认值，那么在存储过程中对sum进行累加操作时，sum的初始值为NULL。在对NULL进行数学运算时，
-- 结果通常也会是NULL。因此，如果sum没有默认值，最终的结果将会是NULL。
--给sum设定默认值为0是为了确保sum在开始累加之前已经有一个初始值，
-- 这样在进行累加操作时就不会出现NULL参与数学运算的情况，从而可以正常输出最终结果。因此，在定义局部变量时，最好给变量一个默认值，以避免出现意外的结果。
	declare sum int default 0;
	while n>0 do
		set sum:=sum+n;
        set n:= n-1;
	end while;
    select sum;
end&&
call p9(100);

Repeat:

repeat是有条件的循环控制语句，当满足条件的时候退出循环，具体语法为：

先执行一次循环，然后判断until中的条件是否满足，如果满足，则退出，如果不满足，则继续下一次循环

repeat
	SQL逻辑...
	until条件
end repeat;

Repeat的简单使用：

计算从1累加到n的值，n为传入的参数值。

DELIMITER &&
create procedure p10(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 p10(10);

loop:

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环，loop可以配合以下两个语句使用：

leave:配合循环使用，退出循环。
iterate:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

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

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

loop语句的简单使用：

计算从1累加到n的值，n为传入的参数值。

DELIMITER &&
create procedure p13( 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;

计算从1到n的偶数值累加，n为传入的参数值。

DELIMITER &&
create procedure p14( 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;

游标：

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理，游标的使用包括游标的声明，open,fetch和close,其语法如下：

声明游标：

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

打开游标：

open 游标名称;

获取游标记录：

fetch 游标名称 into 变量;

关闭游标：

close 游标名称;

游标的简单使用：

根据传入的参数uage,来查询用户表user中，所有的用户年龄大于等于uage的用户姓名和手机号，并将用户姓名和手机号插入到所建的一张新表(id,name,phone)中

实现逻辑：

A.声明游标，存储查询结果集
B.准备：创建表结构
C.开启游标
D.获取游标中的记录
E.插入数据到新表中
F.关闭游标

DELIMITER &&
create procedure p17(in uage int )
begin
	-- 先声明普通变量，再声明游标
    declare us_name varchar(20);
    declare us_phone varchar(20);
	declare usr_cusor cursor for select name,phone from user where age>=uage;
    create table usr_phone(
		us_id  int  primary key auto_increment,
		us_name varchar(20),
		us_phone varchar(20)
    );
    open usr_cusor;
    while true do
      fetch usr_cusor into us_name,us_phone;
      insert into usr_phone values (null,us_name,us_phone);
    end while;
    close usr_cusor;
end&&

我们调用当前的存储过程，会发现一个问题，虽然MYSQL报错，但是我们的新表已经生成，并且表中的数据也满足我们的题意

要解决上述这个现象，我们就需要用到MYSQL中的条件处理程序

条件处理程序：

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤，具体语法为：

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代码的简写

我们只需要在刚才那个存储过程中的游标定义语句后面加上如下所示的处理程序即可

-- 下述语句为一个条件处理程序，当状态码为1329的时候执行退出操作，在退出时，需要关闭游标
    declare exit handler for sqlstate "02000" close usr_cusor;

或者我们可以使用NOT FOUND，因为它可以处理所有以02开头的报错程序

declare exit handler for not found close usr_cusor;

存储函数：

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

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

characteristic说明：

Deterministic:相同的输入参数总是产生相同的结果

NO SQL：不包含SQL语句

Reads SQL Data：包含读取数据的语句，但不包含写入数据的语句

存储函数的简单应用：

计算从1累加到n的值，n为传入的参数值。

DELIMITER &&
create function fun_sum(n int)
returns int no sql
begin
  declare total int default 0;
  while n>0 do
	  set total:=total+n;
	  set n:=n-1;
  end while;
  return total;
end&&

select fun_sum(100);

存储函数在实际开发中使用的是比较少的，因为存储函数能够实现的功能，存储过程也能够实现。

触发器：

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。使用别名 OLD和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

当我们执行一条update语句，假设它影响了5行记录，行级触发器会被触发5次，而语句触发器则是不管你影响了多少行，一个语句我只触发一次。

//创建触发器
create trigger 触发器名字
before/after insert/update/delete
on tbl_name for each row  --行级触发器
begin
	trgger_stmt;
end;

//查看触发器
show triggers;

//删除触发器
drop trigger [schema_name]trigger_name;
-- 如果没有指定schema_name，默认当前数据库

触发器的简单使用：

通过触发器记录user表的数据变更日志，将变更日志插入到user_log中，包含增加，修改，删除：

第一步：

//创建数据变更日志表
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",
 opreate_params varchar(500) comment "操作参数",
 primary key(id)
)engine=innodb default charset=utf8;

DELIMITER &&
create trigger tb_user_insert_trigger
    -- 给对应的表定义插入操作的触发器
	after insert on user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,opreate_params) values
    (null,"insert",now(),new.id,concat('插入的数据为:id=',new.id,'name=',new.name,"age=",new.age,"phone=",new.phone,"email=",new.email));
end&&
-- 第三步:查看当前数据库中存在的触发器，确认我们上述创建的是否存在
show triggers;
-- 第四步:向数据表中添加数据，然后查询user_logs看是否成功触发了触发器
insert into user values(3,"Lisa",19,"12345678910","21786@163.com");
-- 第五步:查看日志记录
select * from user_logs;
-- 删除触发器
drop trigger tb_user_insert_trigger;

DELIMITER &&
create trigger tb_user_update_trigger
    -- 给user这张表定义修改操作触发器
	after update on user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,opreate_params) values
    (null,"update",now(),new.id,concat('更新之前的数据为:id=',old.id,'name=',old.name,"age=",old.age,"phone=",old.phone,"email=",old.email,
    '更新之后的数据为:id=',new.id,'name=',new.name,"age=",new.age,"phone=",new.phone,"email=",new.email));
end&&

DELIMITER &&
create trigger tb_user_delete_trigger
    -- 给user这张表定义删除触发器
	after delete on user for each row
begin
-- 需要注意的是这里一定要将字段中引用的new变量全部替换成old
	insert into user_logs(id,operation,operate_time,operate_id,opreate_params) values
    (null,"delete",now(),old.id,concat('删除之前的数据为:id=',old.id,'name=',old.name,"age=",old.age,"phone=",old.phone,"email=",old.email));
end&&

需要注意的是，MYSQL当前只支持行触发器，因此我们执行一次SQL语句，就会触发一次对应的触发器

锁：

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、I/O)的争用以外，数据也是-种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MYSQL锁的分类：

MySQL中的锁，按照锁的粒度分，分为以下三类:
1.全局锁:锁定数据库中的所有表。
2.表级锁:每次操作锁住整张表。
3.行级锁:每次操作锁住对应的行数据。

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

如下所示：

假设我们的数据库没有加锁，那么一边备份，一边新加入数据的过程就会导致备份完成的数据和数据库中实际的数据不一致的问题

mysqldump并不是SQL语句，因此我们不能在MYSQL中执行，需要在Windows命令提示行下执行

全局锁的特点：

数据库中加全局锁，是一个比较重的操作，存在以下问题:

如果在主库上备份，那么在备份期间都不能执行更新，业务在此期间就处于停滞状态。

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟

在InnoDB引擎中，我们可以在备份时加上参数–single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot -p 密码 需要被备份的数据库 > 目标sql文件

表级锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MYISAM、InnoDB、BDB等存储引擎中

对于表级锁，主要分为以下三类:

表锁
元数据锁(meta data lock，MDL)
意向锁

表锁：

对于表锁，分为两类:

1.表共享读锁(read lock)
2.表独占写锁(writewock)

语法:

加锁:locktables 表名... read/write。
释放锁:unlock tables/客户端断开连接

读锁：

读锁不会阻塞其他客户端的读，但是会阻塞其他客户端的写

写锁：

写锁既会阻塞其他客户端的读，也会阻塞其他客户端的写

元数据锁：

元数据锁(meta data lock，MDL)，MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

查看元数据锁：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁：

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

如下所示：

假设线程A要对如下所示表中的id为3的记录进行更新，那么在默认的Mysql隔离级别下，就会对该行记录加行锁，与此同时线程B想对该表进行加锁操作，由于在此之前线程A已经对id为3的记录行加了行锁，而此时线程B如果直接加表锁，就会造成锁的冲突，因此在加锁之前线程B必须逐行检查记录看是否有记录行持有锁，持有的锁的类型是什么？是否可以加表锁，但是这样逐行检查性能是很低的，那么为了解决该问题，MYSQL就引入了意向锁。

使用意向锁之后的加锁过程如下所示：

当线程B想要加锁的时候，它会去判断要加的锁是否和表中已有的意向锁兼容，如果兼容直接加锁，如果不兼容，那么需要等线程A事务提交之后，释放意向锁和行锁

//意向共享锁(IS):与表锁共享锁(read)兼容，与表锁排它锁(write)互斥。
意向共享锁(IS):由语句 select....lock in share mode添加。
//意向排他锁(IX):与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。
意向排他锁(IX):由insert、update、delete、select...for update 添加。

//查看意向锁及其行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁:

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类。
行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

间隙锁(GapLock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持.

临键锁(Next-KeyLock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

InnoDB实现了以下两种类型的行锁:

共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

排他锁(X):允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

默认情况下，InnODB在REPEATABLE READ事务隔离级别运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。

1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

间隙锁和临键锁：

默认情况下，InnODB在 REPEATABLE READ事务隔离级别运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁 。

2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁 退化为间隙锁。

3.索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁锁的是间隙，不包含具体的记录，而临键锁不仅会锁该记录还会锁住该记录前包含的信息，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。