视图,触发器,事务,存储过程
视图是由一条sql语句的查询结果构成的虚拟表
其不是物理存在的 使用方式与普通表相同
视图的作用
1.简化sql语句的编写
2.可以限制可查看的数据
可以使用权限来完成 权限某一个库的某一个表的某一个字段
例如:一张表存储了所有的工资 但是每个员工只能看到自己的工资信息,那就可以用视图来限制
创建视图的 语法
create view [or replace] view_name as sql语句
视图的特点:
对于视图的增删改查 都会同步到原始表
每一次对视图的查询 本质上都是执行了创建视图时指定的sql语句
对于原始表的修改 也能在视图中查看到 前提是你修改的数据 包含在创建视图时指定的sql语句中
修改视图 alter view
触发器***
是一段与某个表有关的mysql程序
当达到某种条件时,触发某个东西的执行
条件:
时间点 before | afer
具体事件
update | delete | insert
new old old new
达到条件后:自动执行一段mysql程序
作用:
总的来说就是可以帮你在一个表被修改时 做一些额外的操作
语法:
create trigger name after delete on tablename for each row
begin
#具体要执行的操作
end
再触发器中有两个隐藏对象
案例:
创建一个博库表
id title content commit_time
更新记录表
id content update_time b_id
创建触发器
在blog 表发生了update事件时 要自动将更新后的数据插入 记录表
#重定义结束符为//因为触发器中包含;而分号是默认的结束符
delimiter // create trigger t1 after update on blog for each row begin insert into update_log values(null,new.content,now(),new.id); end// delimiter; select * from users//
#准备数据 CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum ('yes', 'no') #0代表执行失败 ); #错误日志表 CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime ); # 创建触发器 delimiter // create trigger trigger1 after insert on cmd for each row begin if new.success = "no" then insert into errlog values(null,new.cmd,new.sub_time); end if; end// delimiter ; #往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('jerry','0755','ls -l /etc',NOW(),'yes'), ('jerry','0755','cat /etc/passwd',NOW(),'no'), ('jerry','0755','useradd xxx',NOW(),'no'), ('jerry','0755','ps aux',NOW(),'yes'); # 查看错误日志表中的记录是否有自动插入 select *from errlog;
delimiter
注意:
1.外键不能触发事件 主表删除了某个主键 从表也会相应删除 但是并不会执行触发器
2.触发器中不能使用事务,不能使用select 查询数据
3.相同时间点的相同事件的触发器 不能同时存在
删除触发器
cmd表
存储的是什么 事件 执行了什么指令 结果是什么
errlog表 存储的是所有执行失败的指令信息
当数据插入cmd表时 判断 如果success 为no 就将这个信息插入errlog 表中
在cmd 执行失败时自动将信息存储到错误日志表中
客户端要执行一条指令 要发送给服务器 服务器要把执行记录插入到cmd这个表中
delimiter || create trigger t3 after insert on cmd for each row begin if new.success ='no' then insert into errlog values(null,new.cmd,now()); end || delimiter;
触发器不会影响原表的操作 只是增加一些额外的操作
mysql 最想干的事情就是把数据相关的所有逻辑代码全部放mysql中
这样应用程序的操作减少了
事务******
事务是逻辑上一组sql语句的集合
特点是:一个事物的所有sql语句 要么全部执行成功 要么全部失败
一个转账操作
从a账户 减去100
在给b账户 加上100
#开启事务
start transaction;
#一堆sql
#如果发生了一些错误 可以回滚
rollback;是全部撤销(回滚)
savepoint 可以创建保存点(游戏的存档) 可以选择性的回滚一部分
rollback to ..;
#如果没有问题就提交 只有执行了提交操作你的修改才会生效
commit
事务最强大的地方在于 (原子性)
当发生了一些不可控因素时(火山爆发,断电,系统崩溃,网络中断) 可以保证数据是完整的
pymysql 封装了事务的开启 我们需要做的就是 执行完成后提交事务
一些客户端中 默认是没有事务的 例如cmd中的mysql客户端
注意:如果在创建连接时 将自动提交设置为了True 则不会在开启事务 一般不要改这个默认选项
事务的四大特征:
原子性
一个事物是一个整体 不可拆分 要么都成功 要么都失败
一致性
一个事物无论成功或失败 相关数据的约束一定是完整的
隔离性
与效率息息相关
两个事物之间相互独立
read_uncommitted
read_committed
对于同一条记录 同一事物的两次查询结果不一致
repeatable_read
serializable
永久性
一个事务一旦提交,就是永久性的 无法回滚
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='root', database='db02' ) cursor=conn.cursor(pymysql.cursors.DictCursor) try: sql1='update account set money=money -100 where id =1' sql2='update account set money=money +100 where id =2' cursor.execute(sql1) cursor.execute(sql2) conn.commit() print('转账成功') except Exception as e: print(type(e),e) conn.rollback()#发生错误就回滚
4 存储过程
一个存储过程中 包含任意sql语句,以及流程控制,事物等等
简答的就说 将一系列较为复杂的逻辑封装到mysql中
好处:
例如要是注册功能
接受用户名 密码
判断用户名是否存在 需要编写一条sql语句 传给服务器 服务器执行完毕返回结果
客户端在发起请求 把用户名 和密码传给服务器
经历了至少两次网络传输
如果使用存储过程 就可以一次性将用户名和密码传给服务器 等到接受结果即可
降低了网络传输 提高了效率
弊端:学习成本高 运营成本高 沟通成本高
正常开发中有三种常见方式完成数据库相关操作
1.应用程序开发者 只关注业务逻辑 数据相关的逻辑交给存储过程
优点:又花了网络传输 耦合度降低
弊端:学习成本高,运营成本高,沟通成本高 存储过程移植性非常差 每种数据库语法都不同
2.应用程序开发者 不仅关注业务逻辑 还需要编写原生的sql语句
优点:一个人负责所有 没有沟通成本
缺点:sql语句编写繁琐
3.应用程序开发者 仅关注业务逻辑 把sql语句相关的交给ORM(对象关系映射 帮你封装了增删改查 自动生成了sql语句)
优点:开发效率高
缺点:执行效率较低
1.创建存储过程
create procedure p1(type 参数名 数据类型) type: in输入参数 out输出参数 inout技能输出又能输入 需求 编写一个存储过程 查询 account表 可以指定工资的区间范围 select * from account where money >=a and money <=b; delimiter // create procedure p1(in a double,in b double) begin select * from account where money >=a and money <=b; end// delimiter; call p1(500,1000) delimiter ; 用于修改默认的行结束符 ,由于在触发器中有多条sql语句他们需要使用分号来结束, 但是触发器是一个整体,所以我们需要先更换默认的结束符,在触发器编写完后在将结束符设置回分号 drop procedure过程名称 删除p1
存储过程与函数的区别
函数仅仅是一个单纯的工具 与数据无关 所以函数中不能出现sql语句
存储过程 既可以包含mysql的逻辑代码 也能包含sql语句
delimiter // create procedure p1(in a double,in b double,out res char(20)) begin select * from account where money >=a and money <=b; set res='success'; end// delimiter;
#输出参数 必须是一个变量 用@定义变量
pymysql中调用存储过程 #pymysql import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='root', database='db02' ) cursor=conn.cursor(pymysql.cursors.DictCursor) #调用存储过程 如果是一个输出out参数 随便给个值就行 cursor.callproc('p2',(500,1000,0)) #pymysql会自动定义变量@_存储过程名称_参数索引 #分别是:@_p2_0 @_p2_1 @_p2_2 #获取结果 print(cursor.fetchall()) cursor.execute('select @_p2_1') print(cursor.fetchall())
delimiter // create PROCEDURE p5(OUT p_return_code char(20)) BEGIN DECLARE exit handler for sqlexception BEGIN # ERROR set p_return_code = "出现异常,回滚!"; rollback; END; # exit 也可以换成continue 表示发生异常时继续执行 DECLARE exit handler for sqlwarning BEGIN # WARNING set p_return_code = "出现警告,回滚!"; rollback; END; START TRANSACTION; update account set money = money - 1000 where id = 1; update account set moneys = money - 1000 where id = 1; # moneys字段导致异常 COMMIT; # SUCCESS set p_return_code = 0; #0代表执行成功 END // delimiter ; #在mysql中调用存储过程 call p5(@res); select @res;
