mysql学习四之事务、变量、触发器、函数、存储过程

需求：有一张银行账户表，有A用户给B用户转账；A账户先减少，B账户增加，但是A操作完之后断电了。

解决方案：A减少钱，但是不要立即修改数据表；B收到钱之后，同时修改数据表


事务安全

事务：transaction,一系列要发生的连续的操作
事务安全：一种保护连续操作同时满足（实现）的一种机制

事务安全的意义：保证数据操作的完整性


--创建一个账户表
create table my_account(
number char(16) not null unique comment '账户',
name varchar(20) not null,
money decimal(10) default 0.0 comment '账户余额'
)charset utf8;

insert into my_account values('0000000000000001','张三',1000),('0000000000000002','李四',1000);

alter table my_account add id int primary key auto_increment first;

update my_account set money = money - 1000 where id= 1; 


事务操作
事务操作分为两种：自动事务（默认的），手动事务 

手动事务：操作流程
1、开启事务：告诉系统以下所有的操作（写）不要直接写入到数据表，先存到事务日志
--开启事务
start transaction;

2、进行事务操作：一系列操作
	a)李四账户减少
update my_account set money = money - 1000 where id = 2; 
	b)张三账户增加
update my_account set money = money + 1000 where id = 1; 

3、关闭事务：选择性的将日志文件中操作的结果保存到数据表（同步）或者说
直接清空事务日志（原来操作全部清空）
	a)提交事务：同步数据表（操作成功）commit;
	b)回滚事务；直接清空（操作失败）  rollback;
--提交事务 
commit;


事务原理
开启事务后，后续的所用操作（写）；会先写到临时日志文件，
接收SQL语句，执行SQL语句；结果先写入到临时日志文件；
查询操作：会从数据表查询，经过临时日志文件结果加工后返回
事务结束：commint或rollback,清空临时日志文件，commit会同步到数据表，
rollback是直接清空
如果断开连接，临时事务文件会自动清空。


回滚点
在某个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管
成功还是失败，前面操作都已经成功；可以在当前成功的位置，设置一个点；
可以供后续失败操作返回该位置，而不是返回所有操作，这个点称为回滚点。

设置回滚点语法：savepoint 回滚点名字

回到回滚点语法：rollback to 回滚点名字 

--回滚点操作

--开启事务 
start transaction;
--事务处理1:张三加钱
update my_account set money=money+10000 where id = 1;
--查看结果
select * from my_account;
--设置回滚点 
savepoint sp1;
--银行扣税
update my_account set money=money-10000*0.05 where id=2; --错误
--查看结果
select * from my_account;
--回滚到回滚点
rollback to sp1;
--查看结果
select * from my_account;
--继续操作
update my_account set money=money-10000*0.05 where id=1; 
--查看结果
select * from my_account;
--提交事务
commit;


事务特性：四大特性
	A：原子性，事务整个操作是一个整体，不可分割，要么全部成功，要么全部失败
	C：一致性，事务操作的前后，数据表的数据没有变化
	I：隔离性，事务操作是相到隔离不受影响的
	D：持久性，数据一旦提交，不可改变，永久的改变数据表数据
	
锁机制：innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引，
那么系统会自动全表检索数据，自动升级为表锁。
	行锁：只有当前行被锁住，别的用户不能操作
	表锁：整张表被锁住，别的用户都不能操作。



变量
变量分为系统变量和自定义变量

系统变量：系统自定义好的变量，系统变量是用来控制服务器的表现的，如
autocommit,auto_increment_increment等

查看系统变量
show variables ; --查看所有系统变量
	
查看具体变量值：任何一个有数据返回的内容都是由select查看
select @@变量名;

--查看变量值
select @@version,@@autocommit,@@auto_increment_offset;

修改系统变量
分为两种方式:会话级别和全局级别和全局级别

会话级别：临时修改

--修改会话级别变量 ，当前客户端当次连接有效
set 变量名 = 值 ;  set @@变量名 = 值;
set autocommit = 0 ;

全局级别：一次修改，永久生效（对所有客户端生效）
set global 变量名 = 值;


自定义变量
系统为了区分系统变量，规定用户自定义变量必须使用一个@符号
set @变量名=值;

set @name = '张三';

select @name;

mysql定义的一个赋值符号:=

set @age := 19;

select @age;


mysql允许从数据表中获取数据，然后赋值给变量：两种方式

1、边赋值，边查看结果(只能用:=，=号会解析为比较)
select @变量名 := 字段名 from 数据源;  --从字段中取值赋值给变量名

--从表中获取数据赋值给变量
select @name := name from my_student;

2、只有赋值不看结果，要求很严格：数据记录只能有一条记录
select 字段表 from 数据源 into @变量名
select name,age from my_student where id=2 into @name,@age;

所有自定义的变量都是会话级别：当前客户端当次连接有效
所有自定义变量不区分数据库（用户级别）


需求：有两张，一张订单表，一张商品表，每生成一个订单，意味着商品的库存要
减少

触发器
事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候（增删改）系统
会自动触发代码执行。

事件类型，触发时间，触发对象
	事件类型：增删改，三种类型 insert delete update 
	触发时间：前后，两种 before after
	触发对象：表中的每一条记录（行）

一张表中只能拥有一种触发时间的一种类型的触发器；最多一张表能有6个触发器


创建触发器
在mysql高级结构中：没有大括号，都是用对应的字符符号代替

基本语法
--临时修改语句结束符
delimiter 自定义符号：后续代码中只有碰到自定义符号才结束

create trigger 触发器名字 触发时间 事件类型 on 表名 for each row 
begin   --代表左大括号；开始
	...里面就是触发器的内容：每行内容都必须使用语句结束符，分号
end     --代表右带括号：结束
--语句结束符号
自定义符号 

--将临时修改修正过来
delimiter 


--创建表
create table my_goods(
	id int primary key auto_increment,
	name varchar(20) not null,
	price decimal(10,2) default 1,
	inv int comment '库存'
)charset utf8;

insert into my_goods values(null,'iphone6s',5288,100),(null,'s6',5300,100);

create table my_order(
	id int primary key auto_increment,
	g_id int not null comment '商品ID',
	g_number int comment  '商品数量'
)charset utf8;


--触发器：订单生成一个，商品库存减少
--临时修改语句结束符
delimiter $$

create trigger after_order after insert on my_order for each row
begin
	update my_goods set inv = inv - 1 where id = 2;
end 
$$
delimiter  ;


查看触发器

查看所有触发器或者模糊匹配
show triggers\G; 

--查看触发器创建语句
show create trigger 触发器名字;

show create trigger after_order\G


所有触发器都会存到一个系统表中information_schema.triggers 

select * from information_schema.triggers


使用触发器
不需要手动调用，而是当某种情况发生时，会自动触发

--订单插入记录会自动触发
--触发器工作了，订单生成之后，对应商品数量减少了
--当前商品减少的，不是订单中产生的商品；而是固定的商品（不合理）
--插入订单
insert into my_order values(null,1,2);

修改触发器&删除触发器

触发器不能修改，只能先删除后新增

drop trigger 触发器名字;
--删除触发器
drop trigger after_order;


触发器记录
不管触发器是否触发了，只要当前某种操作准备执行，系统就会将当前要操作的记录的当前状态
和即将执行之后新的状态给分别保留下来，供触发器使用：其中，要操作的当前状态保存到old中，
操作之后的可能形态保存给new

old代表的是旧记录，new代表的是新记录
	删除的时候没有new，插入的时候没有old
old和new代表记录本身，任何一条记录除了数据，还有字段名字
使用方式old.字段名/ new.字段名（new代表假设发生之后的结果）


delimiter $$
create trigger after_order after insert on my_order for each row
begin
	update my_goods set inv = inv - new.g_number where id = new.g_id;
end
$$
delimiter  ;

insert into my_order values(null,1,2);


代码执行结构

三种：顺序结构 、分支结构、循环结构

分支结构
实现准备多个代码块，按照条件选择性的执行某段代码

在mysql中只有if分支

基本语法 
if 条件判断  then
	--满足条件要执行的代码
else
	--不满足条件要执行的代码
end if;

触发器结合If分支，判断商品库存是否足够，不够不能生成商品订单


--库存不够：触发器没有提供一个能够阻止事件发生的能力，暴力报错
delimiter %%
create trigger before_order before insert on my_order for each row
begin
		select inv from my_goods where id = new.g_id into @inv;
		if @inv < new.g_number then 
			insert into XXX values(XXX);
		end if; 
end 
%%
delimiter   ;

--插入订单
insert into my_order values(null,1,1000);


循环结构
某段代码在指定条件重复执行
while循环

while 条件判断 do
	--满足条件要执行的代码
	--变更循环条件
end while;

循环控制：在循环内部进行循环判断和控制
mysql中没有对应的continue和break;但是有替代吕
iterate 迭代，类似continue ,后面的代码不执行，循环重新来过
leave 离开 ，类似break;

使用方式 iterate/leave 循环名字;

--定义循环名字
循环名字：while 条件 do
	--循环体
	--循环控制
		leave/iterate 循环名字
end while;


函数 
将一段代码块封装到一个结构中，在需要执行代码的时候，调用结构执行即可（代码复用）

分为两类：系统函数和自定义函数

系统函数：直接调用即可
任何函数都有返回值，因此函数的调用是通过select调用。

mysql中字符串的基本单位，最常用的是字符
substring  字符串截取,字符为单位substring(str,pos,len);
char_length 字符长度
length		字节长度
instr		判断字符串是否在某个具体字符串中存在
lpad		左填充，将字符串，按照某个指定的填充方式，填充指定长度
insert      字符串替换
strcmp  		字符串比较

--定义两个变量
set @cn = '世界你好';
set @en = 'hello world';

--字符串截取,mysql中字符串下标从1开始，截取单位为字符
select substring(@cn,1,1);
select substring(@en,1,1);

--字符长度
select char_length(@cn),char_length(@en),length(@cn),length(@en); 

--字符串寻找,0代表没有找到
select instr(@cn,'界'),instr(@en,'ll'),instr(@cn,'知道');

--字符串填充
select lpad(@cn,20,'欢迎'),lpad(@en,20,'hello');

--字符串替换
select insert(@en,3,3,'y'),@en;



自定义函数
函数要素：函数名、参数列表（形参和实参），返回值，函数体

创建语法
create function 函数名([形参列表]) returns 数据类型  --规定要返回的数据类型
begin
	--函数体
	--返回值 return类型（指定数据类型）
end

--创建函数
create function display1() returns int
return 100;

--调用函数
select display1();

--查看所有函数
show function status [like ''];

函数属于指定数据，只有在对应数据库下调用 

查看函数的创建
show create function 函数名;

show create function display1;

修改函数&删除函数
函数不能修改，先删除后新增
drop function 函数名;

drop function display1;

函数参数
参数分为两种：定义时的参数叫形参，


--函数：计算1-指定数之间的和
-- 求和：任何变量要修改必须使用set关键字
--@定义的变量是全局变量，没有的是局部变量
delimiter $$
create function display1(int_1 int) returns int
begin
	set @i = 1 ;
	set @res = 0 ;
	while @i <= int_1 do
		set @res =@res+ @i;
		set @i = @i+1;
	end while;
	return @res;
end
$$
delimiter  ;

在函数内部使用@定义的变量在函数外部也可以访问 

作用域
mysql中的作用域与JS中的作用域一样
	全局变量可以在任何地方使用；局部变量只能在函数内部使用
全局变量：使用set关键字定义，使用@符号标志
局部变量：使用declare关键字声明，没有@符号；所有的局部变量的声明


--求和：1-指定数之间的和，要求5的倍数不加
delimiter $$
create function display2(int_1 int) returns int 
begin
	declare i int default 1 ;
	declare res int default 0 ;
	mywhile:while i <= int_1 do
		if i%5=0 then 
			set i = i+1;
			iterate mywhile;
		end if;
		set res = res +i;
		set i = i+1;
	end while;
	return res;
end 
$$
delimiter  ;


存储过程
存储过程是一种没有返回值的函数

创建过程

create procedure 过程名字([参数列表])
begin
	--过程体
end

--创建过程
--假设过程中需要显示数据：使用select
create procedure pro1()
	select * from my_student;
	
查看过程

查看所有过程

show procedure status [like ''];

--查看过程创建语句
show create procedure pro1;

调用过程
过程没有返回值，select是不能访问的
过程有一个专门的调用关键字 call

call pro1();


修改过程&删除过程
过程不能修改，只能先删除后新增
drop procedure 过程名;

drop procedure pro1;

过程参数
函数的参数需要数据类型指定，过程比函数更严格

过重还有自己的类型限定：三种类型
	in:数据只是从外部传入内部使用（值传递）；可以是数值，也可以是变量
	out:只允许过程内部使用（不用外部数据），给外部使用的（引用传递，外部的数据会被
		先清空才会进入到内部），只以是变量
	inout:外部可以在内部使用，内部修改也可以给外部使用；典型的引用传递，只能是变量

基本使用：
create procedure 过程名(in 形参名字 数据类型,out 形参名字 数据类型,inout 形参名字 数据类型)


--过程参数
--int_2的值一定是null，Out数据会被先清空
delimiter $$
create procedure pro1(in int_1 int,out int_2 int,inout int_3 int)
begin
	select int_1,int_2,int_3;
end
$$
delimiter  ;

调用 :out 和inout类型的参数必须传入变量而不是数值
set @int_1=1;
set @int_2=2;
set @int_3=3;
select @int_1,@int_2,@int_3;
call pro1(@int_1,@int_2,@int_3);

--局部变量和全局变量没有关系
delimiter $$
create procedure pro2(in int_1 int,out int_2 int,inout int_3 int)
begin
	select int_1,int_2,int_3;
	
	set int_1 = 10 ;
	set int_2 = 100;
	set int_3 = 1000;
	
	select int_1,int_2,int_3;
	select @int_1,@int_2,@int_3;
	
	set @int_1 = 'a';
	set @int_2 = 'b';
	set @int_3 = 'c';
	
	select int_1,int_2,int_3;
	select @int_1,@int_2,@int_3;
end
$$
delimiter  ;
select @int_1,@int_2,@int_3;
最后：在存储过程调用结束之后，系统会将局部变量重新返回给全局变量，只有out,inout;