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

1.视图

1.1介绍

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

1.2语法

1）创建

 CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]

2)查询

 查看创建视图语句：SHOW CREATE VIEW 视图名称;
 查看视图数据：   SELECT * FROM 视图名称 ...... ;

3)修改

 方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
 方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

4）删除

 DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

视图的插入与图表插入语法相同，通过视图插入的表会保存在图表中

1.3检查选项

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

1）cascaded ——级联

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

如果在创建视图时定义了 WITH CHECK OPTION 子句，则对于所有被级联引用的视图均会执行级联检查。这意味着，修改任何一个视图所涉及的行时，如果这些修改不符合此视图本身以及被它级联引用的其他视图对应的检查条件，则操作将被拒绝。

2）local ——本地

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

1.4视图的更新

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

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

1.5视图的作用

1）简单

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

2）安全

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

3）数据独立

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

如：基础表的列名发生改变，视图可以给其取别名，不会影响数据查询

2.存储过程

2.1介绍

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

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

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

2.2基础语法

1）创建

 CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
 BEGIN
 -- SQL语句
 END ;

2）调用

  CALL 名称 ([ 参数 ]);

3）查看

 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息
 SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

4）删除

 DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

在命令行中，‘；’定以时会被当作结束符而无法定义结束符，因此执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

演示示例：

 -- 存储过程基本语法
 -- 创建
 create procedure p1()
 begin
 select count(*) from student;
 end;
 -- 调用
 call p1();
 -- 查看
 select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
 show create procedure p1;
 -- 删除
 drop procedure if exists p1;

2.3变量

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

系统变量

系统变量是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;

用户变量

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

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;

局部变量

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

2.4 if

1）介绍

if 用做条件判断

 IF 条件1 THEN
 .....
 ELSEIF 条件2 THEN -- 可选
 .....
 ELSE -- 可选
 .....
 END IF;

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

2)演示示例

 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();

2.5参数

1）介绍

类型	含义	备注
in	该类参数作为输入，也就是需要调用时传入值	默认
out	该类参数作为输出，也就是该参数可以作为返回值
inout	既可以作为输入参数，也可以作为输出参数

2）用法

 CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
 BEGIN
 -- SQL语句
 END ;

3）样式实例

根据传入参数score，判定当前分数对应的分数等级，并返回。

 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;

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

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

2.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)演示示例

 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 进行连接。

2.7 循环

1.while

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

 -- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
 WHILE 条件 DO
     SQL逻辑...
 END WHILE;

示例

 -- 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);

2.repeat

1)介绍

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

 -- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
 REPEAT
     SQL逻辑...
     UNTIL 条件
 END 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);

3.loop

1)介绍

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

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

 [begin_label:] LOOP
 SQL逻辑...
 END LOOP [end_label];

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

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

示例1

 -- 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);

示例二

 -- 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);

2.8 游标

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);

2.9 条件处理程序

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

示例：

 declare [exit] handler for [sqlstate '02000' ] [close 游标名];
 
 declare [exit] handler for [not found] [close u_cursor];

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);

3.存储函数

1）介绍

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

 CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
 RETURNS type [characteristic ...]
 BEGIN
     -- SQL语句
     RETURN ...;
 END ;

characteristic说明：(存储参数类型)

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

2）案例

 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特性，否则就会报如下错误：

 MySQL中的binlog是一种二进制日志文件，记录了MySQL服务器上对于数据更改的所有操作，如插入、更新或删除等操作。binlog主要用于数据库备份、恢复和复制等方面。
 
 通过启用binlog，可以把所有修改数据的操作记录下来，在出现故障或者数据丢失时，还原到某个特定时间点的状态。此外，binlog还被用于数据库主从复制（replication）的实现，减轻高负载条件下单一服务器的负载压力。
 
 binlog可以按照不同的格式输出，有基于语句的复制格式、基于行的复制格式以及混合复制格式。每种格式都有各自的优缺点，并且它们的输出方式和记录内容也不尽相同。
 
 总之，binlog是MySQL中一个非常重要的特性，可以在多个方面为数据库管理员提供支持和帮助，提高数据库的可靠性和性能。

4.触发器

4.1介绍

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

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW和OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

4.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，默认为当前数据库 。

4.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 (26,'三皇子','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.概述

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

按锁的粒度分：

全局锁：锁定数据库中的所有表。
表级锁：每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据。

2.全局锁

2.1介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

2.2语法

1）加全局锁

 flush tables with read lock;

2）数据备份

 mysqldump -uroot -p1234 itcast.sql

3）释放锁

 unlock tables ;

2.3特点

问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

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

 mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

3.表级锁

3.1介绍

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

表锁
元数据锁
意向锁

3.2表锁

表共享读锁
表独占写锁

语法：

 加锁：lock tables 表名... read/write
 释放锁：unlock tables /客户端断开连接

结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

3.3元数据锁

meta data lock , 元数据锁，简写MDL。

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

这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

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

对应SQL	锁类型	说明
lock tables 表名 read /write(主动加锁)	SHARED_READ_ONLY /SHARED_NO_READ_WRITE
select 、select ...lock in share mode（共享锁）	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select ... for update	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table ...	EXCLUSIVE（排他锁）	与其他的MDL都互斥

我们可以通过下面的SQL，来查看数据库中的元数据锁的情况：

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

当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ /SHARED_WRITE），之间是兼容的。

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

3.4意向锁

1）介绍

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

因为意向锁的存在：

客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

2）分类

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

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

 select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

4.行级锁

4.1介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

4.2行锁

1）介绍

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL	行锁类型	说明
INSERT ...	排他锁	自动加锁
UPDATE ...	排他锁	自动加锁
DELETE ...	排他锁	自动加锁
SELECT（正常）	不加任何锁
SELECT ... LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

2）演示

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

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

可以通过以下SQL，查看意向锁及行锁的加锁情况：

 select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
 performance_schema.data_locks;

A. 普通的select语句，执行时，不会加锁。

B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

C. 排它锁与排他锁之间互斥、共享锁与排他锁之间互斥。

D. 无索引行锁升级为表锁（我们根据索引字段进行更新操作，就可以避免行锁升级为表锁的情况。如果没有建立索引，那么，查找数据需要遍历表结构，会自动生级为表锁）

4.3间隙锁&临键表

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

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。（）
- 主要目的是防止幻读现象。
索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止
- 可理解为间隙锁和行锁的结合

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

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

B. 索引上的等值查询(非唯一普通索引) ，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。

介绍分析一下：

我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁。

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

查询的条件为id>=19，并添加共享锁。此时我们可以根据数据库表中现有的数据，将数据分为三个部分：

[19]

(19,25]

(25,+∞]

所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

数据库进阶总结（二）｜ 青训营

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

1.视图

1.1介绍

1.2语法

1.3检查选项

1.4视图的更新

1.5视图的作用

2.存储过程

2.1介绍

2.2基础语法

2.3变量

2.4 if

2.5参数

2.6 case

2.7 循环

1.while

2.repeat

3.loop

2.8 游标

2.9 条件处理程序

3.存储函数

4.触发器

4.1介绍

4.2语法

4.3案例

五、锁

1.概述

2.全局锁

2.1介绍

2.2语法

2.3特点

3.表级锁

3.1介绍

3.2表锁

3.3元数据锁

3.4意向锁

4.行级锁

4.1介绍

4.2行锁

4.3间隙锁&临键表

数据库进阶总结（二）｜青训营