MySQL的进阶操作

一，MySQL存储过程和函数

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

存储过程和函数的好处：

        提高代码的复用性

        减少数据在数据库和应用服务器之间的传输，提高效率

        减少代码层面的业务处理

存储过程和函数的区别

        存储函数必须有返回值

        存储过程可以没有返回值

1，存储过程的创建和调用

创建存储过程

--修改结束分隔符
delimiter $
 
--创建存储过程
create procedure 存储过程名称（参数列表）
begin
        SQL语句列表;
end $

--修改结束分隔符
delimiter ；

 调用存储过程

call 存储过程名称（实际参数）;

存储过程的查看和删除

查看数据库中所有的存储过程

select * from MySQL.proc where db = '数据库名称';
--查看数据库中的存储过程
show procedure status where DB = '数据库名称';

删除存储过程

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

 2，存储过程语法-变量

定义变量

--定义变量
declare 变量名 数据类型 [default 默认值] ； 
--变量赋值方式之一
set 变量名 = 变量值；
--变量赋值方式之二
select 列名 into 变量名 from 表名 [where 条件];

 3，存储过程语法--if语句

if 判断条件 then 执行的sql语句1；
[elseif 判断条件 then 执行语句2；]
...
[else 执行的sql语句n；]
end if；

4，存储过程语法-参数传递

存储过程的参数和返回值

create procedure 存储过程名称（in|out|inout）参数名 数据类型）
begin
    sql语句；
end $

 in:代表输入参数，需要由调用者传递实际数据（默认）

out：代表输出参数，该参数可以作为返回值

inout：单表即可以作为输入参数，也可以作为输出参数

5，存储过程语法-while循环

while循环语法

初始化语句；
while 条件判断语句 do
        循环体语句；
        条件控制语句；
end while；

6，存储函数

存储函数和存储过程是非常相识的，区别在于存储函数必须有返回值

创建存储函数

create function 函数名称（参数列表）
returns 返回值类型
begin 
        sql语句列表；
        return 结果；
end$

 调用存储函数

select 函数名称（实际参数）；

删除存储函数

drop function 函数名称；

二，MySQL触发器

触发器是与表有关的数据库对象，可以在insert，update，delete之前或之后触发并执行触发器中定义的sql语句。

这种特征可以协助应用系统在数据库端确保数据的完整性，日志记录，数据校验操作

使用别名new 和old来引用触发器中发生变化的内容记录

1，触发器分类

触发器类型	old	new
insert 型触发器	无（因为插入前无数据）	new标识将要或者已经新增的数据
updae 型触发器	old 标识修改之前的数据	new表示将要或已经修改后的数据
delete 型触发器	old 表示将要或者已经删除的数据	无（因为删除后状态无数据）

2，创建触发器

delimiter$
create trigger 触发器名称
before|after insert|update|delete
on 表名
for each row
begin
    触发器要执行的功能；
end$
delimiter;

 3,触发器的操作

查看触发器

show triggers；

删除触发器

drop trigger 触发器名称；

三，MySQL事务

事务：一条或多条sql语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败

单元中的每条sql语句的相互依赖，形成一个整体

如果某条sql语句执行失败或者出现错误，那么整个单元就会撤回到事务最初的状态

如果单元中所有的sql语句都执行成功，则事务就顺利执行

1，事务操作

开启事务

start transaction；

回滚事务

rollback；

提交事务

commit；

2，事务的四大特征

原子性：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库中有任何影响

一致性：一致性是指事务必须使数据库从一个一执性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

隔离性：隔离性使当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

持久性：持久性是指一个事务一但被提交了，那么对数据库中的数据的改变就是永久性的，即使是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

3，事务的隔离级别

事务的隔离级别

        多个客户操作时，各个客户端的事务之间应该时隔离的，相互独立的，不受影响的

        而如果多个事务操作同一批数据时，就会产生不同的问题

隔离级别分类

隔离级别	名称	会引发的问题
read uncommitted	读未提交	脏读，不可重复读，幻读
read committed	读已提交	不可重复读，幻读
repeatable read	可重复读	幻读
serializable	串行化	无

引发的问题

问题	现象
脏读	在一个事务处理过程中读取到另一个未提交事务中的数据，导致两次查询结果不一致，
不可重复读	在一个事务处理过程中读取到另一个事务中修改并提交的数据，导致两次查询结果不一致
幻读	查询某数据不存在，准备插入此记录，但执行插入时发现此纪录已经存在，无法插入，或者查询数据不存在执行删除操作，却发现删除成功。

查询数据库隔离级别

select @@TX_ISOLATION;

修改数据库隔离级别

set global transaction isolation level 级别字符串；

隔离级别总结

序号	隔离级别	名称	脏读	不可重复读	幻读	数据库默认隔离级别
1	read uncommitted	读未提交	是	是	是
2	read committed	读已提交	否	是	是	Oracle
3	repeatable read	可重复读	否	否	是	MySQL
4	serializable	串行化	否	否	否

隔离级别从小到大安全性越高，但是效率越来越低，所以不建议修改数据库默认的隔离级别。