MySQL事务、触发器、视图与存储过程
事务
# 事务(重要)
ACID
A:原子性
C:一致性
I:隔离性
D:持久性
原子性（atomicity）
一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）
持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响
事务相关操作
start transcation; # 开启事务
诸多SQL操作
rollback # 回滚到操作之前的状态
commit # 确认事务操作 之后不能回滚
案例:
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
视图
1.什么是视图?
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可。
2.为什么要用视图？
如果要频繁使用一张虚拟表，可以不用重复查询。
3.如何使用视图
正常两张表连接情况(拼接)查询
select * from emp inner join dep on emp.id=dep.id;
使用内连接:
inner join : 连接两表中都存在(对应关系)的数据
dep : 部门表 emp : 员工详细表
4.反复拼接的繁琐(引入视图的作用)
反复拼接繁琐之处:
当我们频繁的去查询连表，反复拼接，会出现繁琐的现象，影响效率。
5.解决方法
如果要频繁使用一张虚拟表，可以不用重复查询，而使用视图来进行操作，将拼接的连表保存起来。
二:视图的应用
1.创建视图的格式:
create view 创建视图名字 as 执行连表的SQL语句
2.删除视图表
删除视图表格式:
drop view 视图名;
触发器
1.什么时触发器？
在满足对某张表数据的增，删，改的情况下，自动触发的功能称之为触发器
2.为何要用触发器?
触发器专门针对我们对某一张表的增insert，删delete，改update的行为，这类行为一旦执行
就会触发触发器的执行，既自动运行另外一段sql语句。
3.触发器trigger
触发器:满足特点条件之后自动执行
在MySQL只有三种情况下可以触发
1.针对表的增
增前 增后
2.针对表的改
改前 改后
3.针对表的删
删前 删后
4.触发器语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
5.触发器名字在命名的时候推荐使用下列的方式(见名知意)
tri_after_insert_t1 tri_before_delete_t1
6.触发器三种状态(六种演示)
增加:
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
sql语句
end # 增加之后触发
create trigger tri_before_insert_t1 before insert on 表名 for each row
begin
sql语句
end # 增加之前触发
修改:
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql语句
end # 修改之后触发
create trigger tri_before_insert_t1 before insert on 表名 for each row
begin
sql语句
end # 修改之前触发
删除:
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql语句
end # 删除之前触发
create trigger tri_before_delete_t1 defore delete on 表名 for each row
begin
sql语句
end # 删除之后触发
7.注意事项(临时更换结束符)
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束符也是;
这就会出现语法冲突 需要我们临时修改结束符号
临时修改结束符格式:
delimiter 名字
delimiter $$
临时修改sql语句结束符，该语法只在当前窗口有效
四:触发器实战案例
1.1.MySQL中NEW作用
在MySQL中NEW特指数据对象可以通过点的方式获取字段对应的数据
id name pwd hobby
1 jason 123 read
NEW.name >>> jason
2.创建cmd命令表与报错日志
# 模拟cmd命令表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64), # cmd命令字段
sub_time datetime, # 提交时间
success enum ('yes', 'no') #0代表执行失败
);
# 报错日志表
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
3.创建触发器
1.创建之前修改结束符
delimiter $$ # 将mysql默认的结束符由;换成$$
# 创建触发器
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
# 触发器启动 判断cmd中success字段内容记录是否为no，为no则执行以下代码
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
2.创建之后修改结束符
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了。
4.cmd表内插入记录(数据)
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('tony','0755','ls -l /etc',NOW(),'yes'),
('tony','0755','cat /etc/passwd',NOW(),'no'),
('tony','0755','useradd xxx',NOW(),'no'),
('tony','0755','ps aux',NOW(),'yes');
5.验证触发器(查询结果)
查询errlog表记录
select * from errlog;
解析:
1.当cmd命令行后台被插入数据时，if判断出现为‘on’ 报错的数据时
2.会自动触发 触发器会执行代码，将为‘on’错误代码 插入到《errlog报错日志》
6.查看触发器
show triggers\G; # 格式化展示
7.删除触发器
drop trigger tri_after_insert_cmd;
存储过程
1.什么是存储过程？
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，类似于python中的自定义函数。
2.存储过程格式
关键字: procedure
格式:
create procedure 函数名(参数)
begin
功能体代码
end
调用其内部sql代码格式
call 函数名()
- 类似于python中的自定义函数
3.无参存储过程
delimiter $$ # 修改结束符
create procedure p1()
begin
select * from user;
end $$
delimiter ; # 修改回来结束符
# 通过p1()调用其内部sql代码
call p1();
4.有参存储过程
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select * from user where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要提前定义
set @res=10; # 定义/赋值 # 指定res=10
select @res; # 查看
call p2(1,3,@res); # 调用
select @res; # 调用
5.查看存储过程-查看所有存储过程-删除存储过程
查看存储过程具体信息
show create procedure status;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro2;
6.pyMySQL代码调用存储过程
import pymysql
# 创建链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='123',
db='db6',
charset='utf8',
autocommit=True # 涉及到增删改，二次确认
)
# 生成一个游标对象(操作数据库)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 调用存储过程固定语法callproc('p2',(1,3,10))
cursor.callproc('p2',(1,3,10)) # 内部原理 @_p1_0=1,@_p1_1=3,@_p1_2=10;
# 查看结果
print(cursor.fetchall())
隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
