MySQL进阶篇3-视图/存储过程/触发器、锁

视图/存储过程（函数）/触发器

视图：由表动态生成，虚拟的表，保存的是sql的逻辑。

创建视图：

create [or replace] view viewName【列名列表】 as select 语句 [with [cascaded|local] check option]

修改视图：

1、【or replace】必须存在。

2、alter view viewName as .....

删除视图：

drop view 【if exists】 viewName;

with [cascade|local] check option

操作视图：

1、添加数据。

【insert into view1 values(.....)】数据添加到了基表中。

例如，视图是id < 30的进去视图。但是通过视图添加id为40的数据，数据添加成功，但是视图查询不到。因为id<30。

解决： with cascaded check option；增加关联。

通过视图1创建视图2,如果添加了限制条件，两个视图都要检查。给依赖的视图也添加了限制条件。

2、 with local check option :

当前的视图限制，递归查找之前的。但是限制不进行传递操作，依赖的视图有限制就有，没有限制就没有。

3、视图更新条件：

视图更新必须与基础表存在行记录1:1的关系。

例如：sum min max count distinct group by、having、union、union all

视图作用：

1、简化操作，直接查询视图就行

2、安全。【数据库可以进行用户授权，但是不能授权到特定行和特定列上。通过视图，用户只能查询和修改他们能见到的数据】

3、数据独立。视图可以帮助用户屏蔽真实表结构变化带来的影响。

存储过程

特点：

1、封装、复用

2、可以接收参数，也可以返回数据

3、减少网络交互，效率提升

创建

create procedure name(args)

begin

--sql

end;

create procedure p1()
begin
    select count(*) from students;
end;

调用

call procedure_name(args);

查看存储过程

select * from infomation_schema.routines where routine_schema = 'xxx'; --查询指定数据库的存储过程及状态信息
show create procedure p1;--查询某个存储过程的定义

删除存储过程

drop procedure [if exists] p1;

语法结构

变量

1、系统变量：是MySQL服务器提供，不是由用户定义的，属于服务器层面。分为全局变量Global、会话变量Session。

查看系统变量：

show [session|global] variables [like '_ _ _'];
select @@[session|global] 系统变量名

设置系统变量

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

虽然设置的是全局的，但是服务器重启后，又会初始化为默认值。

2、用户自定义变量

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

赋值操作

--赋值操
set @var_name = xxxx
set @var_name := xxxx
select @var_name:= xxx
select 字段名 into @var_name from 表；
--使用
select @var_name;

随便一变量不会报错，是null。

3、局部变量

begin----end之内，用declare 变量名 变量类型 来声明。

create procedure p2()
begin
    declare myCount int default 0;
    set myCount = 1;
    select myCount;
end;
call p2();

语法if elseif end if

if score > 85 then 
    set result = '优秀'
elseif score > 60 then
    set result = '及格'
else
    set result = '不及格'
end if;sql

存储过程参数

create procedure p4(in score int,out result varchar(10))
.......
call p4(55,@result);
select @result;
​
inout
set @score = 75;
call p5(@score);
select @score;
​

case函数

case when value1='1' then '1'
    when value1='2' then '2'
    else 3
end case;

while循环

while 1=1 do 
        sql逻辑  set ...
end while;

repeat循环

repeat
    sql逻辑 set ...
    util 条件满足，退出循环。【满不满足，至少执行一次】
end repeat;

loop循环

loop实现简单的循环，如果不在sql逻辑中增加推出循环的条件，可以实现死循环。

leave：配合循环使用，退出循环。

iterate：必须在循环中，作用是跳过当前循环剩下的语句，直接进入到下一次循环。

create procedure p1(in n int)
begi
    declare total int default 0;
    sum:loop
        if n<=0 then
            leave loop;
        end if;
        if n%2=1 then
            set n = n-1;
            iterate sum;
        end if;
        set total = total+1;
        set n = n-1;
    end loop sum;
end;

sum相当于loop循环的名称。

游标

用来存储，查询结果集的。在存储过程和函数中，可以使用游标对结果集进行循环处理【因为是结果集，需要循环处理每一行】。

使用：declare 声明，open、fetch、close

decalre cursor_name cursor for 查询语句；

open cursor_name;

fetch cursor_name into 变量

close cursor_name;

条件处理程序

条件处理程序，就是当遇到sql异常时，需要做的事情。

declare exit handler for SQLSTATE '02000' close u_cursor;

捕获异常02000时，退出exit，并且执行close cursor操作。

for not found 也可以，处理的是02开头的状态码；

create procedure p1(int uage int)
begin
    
    delcare uname varchar(100);
    declare upr varchar(100);
    declare u_cursor cursor for select name , profession from user where age <= uage; --游标必须放在后边
    declare exit handler for SQLSTATE '02000' close u_cursor;--捕获异常
    drop table if exists tb_user_pro;
    create table 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 ;

存储函数

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

create function p1(args...)

returns type [characteristic...]

begin

--sql语句

return ...;

end;

characteristic说明:

deterministic：相同的输入参数，总是能产生相同的输出结果。

no sql：不包含sql语句。

reads sql data：包含读取sql的语句，但是不包含写入树的语句。

create function f1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 then
        set total:= total + n;
        set n:= n-1;
    end while;
    return total;
end;
​
select f1(100);

弊端，必须得有返回值。

但是，存储过程也能有返回值。所以，存储过程可以完全替代存储函数。

触发器

触发器与表有关，在insert/update/delete之前或者之后触发，执行触发器中定义的sql语句集合。

协助应用在数据库确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的内容。现在触发器只支持行级触发，不支持语句级触发。【update影响了5行，触发5次触发器。而不是触发一次。】

定义触发器

create trigger trigger_name
before/after insert/update/delete
on table_name for each row --行级触发器
begin
    trigger_stmt;
end;
--查看
show triggers;
--删除
drop trigger [schema_name].trigger_name; --如果没有指定schema_name，默认是当前数据库。

触发器案例

 create trigger user_insert_trigger
    after insert on user for each row
 begin
    insert into user_logs values(null,'insert',now(),new.id,concat("插入的内容为:",new.id,new.name,new.phone,new.email));
 end;

锁

概述

保证并发访问数据库数据的一致性和有效性等。

全局锁-库锁

加锁后，整个数据库实例就处于只读状态，后续的DML语句，DDL语句，以及更新操作的事务提交语句都将会被阻塞。

典型使用场景：

对全库做逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

flush tables with read lock;

widnows：命令行，mysqldump -h localhost -uroot -p 1234 userDB > userDB.sql;

unlock tables;

特点：

如果在主库上备份，那么备份期间都不能执行更新，业务基本上就得停摆。

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

所以：在InnoDB引擎中，我们可以在备份时，加上参数 --single-transaction来完成不加锁的一致性数据备份。【通过快照读来实现。】

mysqldump --single-transaction -uroot -p 1234 userDB > d:/userDb.sql;

表级锁

表级锁在InnoDB、MyISAM、BDB等存储引擎中，都是支持的。

表锁：

1、表读锁

lock tables 表名 read/write；

unlock tables;

当前会话，报错。其他会话，阻塞到解锁。

2、表写锁

当前会话：

lock tables score write;

select * from score;没问题

update score set chinese = 100 where id = 1; 没问题

=================================================

其他窗口会话：

select * from score ；会阻塞；

update 会阻塞到表锁释放。

元数据锁（meta data lock,MDL）：

元数据锁是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。【事务开启的时候，没有元数据锁。只有执行了增删改查之后，才有了元数据锁。】[避免DML语句和DDL语句的冲突。]

当对一张表进行增删改查的时候，增加MDL读锁（共享锁）；当对表结构进行变更的时候，加MDL写锁（排他）。

【会话1开启事务查询之后，会话2修改表结构，阻塞。当事务1提交之后，阻塞解除。因为会话1查询数据之后，会产生共享锁，与会话2的排他锁冲突。】

查看元数据锁：

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

意向锁：

作用，某行记录会话1加了行锁，会话2想要添加表锁，需要一行行检查记录。为了避免DML语句在执行时，加的行锁和表锁冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

加入意向锁：

会话1开始事务，给表记录增加行锁，同时对表增加意向锁。

会话2要给表增加表锁，需要检查表锁和意向锁冲不冲突。

意向共享锁IS：由语句 select ... lock in share mode添加。

意向排他锁IX：由insert 、update、delete、 select for upate添加。

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

行级锁

InnoDB的数据，是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录的加锁。

1、行锁：锁定单行记录，防止其他事务对此行进行update、delete。在RC RR隔离级别下支持。

2、间隙锁：锁定索引记录的间隙，不包含记录，确保索引间隙不变，防止其他事务在间隙进行insert操作，进而产生幻读。在RR隔离级别下支持。

3、临键锁：行锁和间隙锁的组合。会锁住数据，并锁住数据前面的Gap，在RR隔离级别下支持。

行锁：

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

1、针对于唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

2、InnoDB的行锁是针对于索引加的锁，不通过索引检索数据，那么InnoDB将对表中所有记录加锁，此时就会升级为表锁。

--可以通过以下sql，查看意向锁和行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

间隙锁和临键锁：

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

1、索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。、

譬如：会话1id搜索5，上一个是3，下一个是9，就把3和9之间的间隙锁住了，此时另外一个客户端会话2要插入id为4，插入不了阻塞了，因为间隙被锁住了，只有搜索5提交之后，才会插入成功。

2、索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。

譬如：普通索引又不是唯一索引，等值查询之前或者是之后，还会有值，索引要锁住其间隙。还有锁住其本身的行。

3、索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止。

譬如：会锁住行，还有其间隙。包括正无穷。

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

总结：

概述：

在并发访问时，解决数据访问的一致性、有效性问题。

全局锁、表级锁、行级锁。

全局锁：

对整个数据库实例加锁，加锁后，整个数据库实例 处于只读状态。

性能较差，数据逻辑备份时使用。【single transaction】

表级锁：

操作锁住整张表，锁定粒度大，发生锁冲突的概率高。

表锁、元数据锁（避免DML语句和DDL语句冲突）、意向锁（避免表锁和行锁在加锁时的冲突问题，避免在加表锁的时候，逐行去检索行锁的情况，不用手动去家，自动完成）。

行级锁：

操作锁住对应的行数据，锁定粒度较小，发生锁冲突的概率最低。

行锁，间隙锁（解决多个事务并发时，出现的幻读现象）、临键锁。