需求:有一张银行账户表,有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;
分享到:
2016-03-23 18:49
浏览 961
分类:数据库
评论