mysql:视图,触发器,事务,存储过程,函数
一、视图
视图是一个虚拟表并不是(正实存在的)
创建老师表 create table teacher( id int primary key auto_increment, tname varchar(10) ); 创建课程表 create table course( id int primary key auto_increment, cname varchar(10), teacher_id int, foreign key(teacher_id) references teacher(id) on delete cascade on update cascade ); 插入数据 insert into teacher(tname)values ('张磊老师'), ('李平老师'), ('刘海燕老师'), ('朱云海老师'), ('李杰老师'); insert into course(cname,teacher_id)values ('生物',1), ('物理',2), ('体育',3), ('美术',2);
两张有关系的表
#查询李平老师教授的课程名
#子查询出临时表,作为teacher_id等判断依据
select tid from teacher where tname='李平老师'
(1)创建视图
#语法:CREATE VIEW 视图名称 AS SQL语句
#于是查询李平老师教授的课程名的sql可以改写为
#!!!注意注意注意: #1. 使用视图以后就无需每次都重写子查询的sql,但是这么效率并不高,还不如我们写子查询的效率高 #2. 而且有一个致命的问题:视图是存放到数据库里的,如果我们程序中的sql过分依赖于数据库中存放的视图,
那么意味着,一旦sql需要修改且涉及到视图的部分,则必须去数据库中进行修改,而通常在公司中数据库有专门的DBA负责,
你要想完成修改,必须付出大量的沟通成本DBA可能才会帮你完成修改,极其地不方便
(2)、使用视图
#修改视图,原始表也跟着改
(3)修改视图
语法:ALTER VIEW 视图名称 AS SQL语句
(4)删除视图
语法:DROP VIEW 视图名称
DROP VIEW teacher_view
二、触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,注意:没有查询
(1)创建触发器
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
#准备表 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 tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN IF NEW.success = 'no' THEN #等值判断只有一个等号 INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号 END IF ; #必须加分号 END// delimiter ; #往表cmd中插入记录,触发触发器,根据IF的条件决定是否插入错误日志 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('egon','0755','ls -l /etc',NOW(),'yes'), ('egon','0755','cat /etc/passwd',NOW(),'no'), ('egon','0755','useradd xxx',NOW(),'no'), ('egon','0755','ps aux',NOW(),'yes');
#查询错误日志,发现有两条
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。
(2)、 使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。
(3)、 删除触发器
drop trigger tri_after_insert_cmd;
三、事务
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,
即可回滚到原来的状态,从而保证数据库数据完整性。
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); #原子操作 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 commit; #出现异常,回滚到初始状态 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到 rollback; commit;
四、存储过程
存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,
通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点
#1 程序与数据实现解耦
#2 减少网络传输的数据量
#=============================================== #创建无参存储过程 delimiter // create procedure p3() begin select * from user; insert into user(name,balance) values('wsb1',2); end // delimiter ; #调用存储过程 call p3(); #在mysql中调用
cursor.callproc('p3') #在python中通过pymysql模块调用 #=============================================== #创建有参存储过程之in的使用 delimiter // create procedure p4( in m int, in n int ) begin select * from user where id between m and n; end // delimiter ; #调用存储过程 call p4(3,7); #在mysql中调用
cursor.callproc('p4',args=(3,7)) #在python中通过pymysql模块调用 #=============================================== #创建有参存储过程之out的使用 delimiter // create procedure p5( in m int, in n int, out res int ) begin select * from user where id between m and n; set res=1; end // delimiter ; #调用存储过程 #在mysql中 set @x=11111111111 call p5(3,7,@x); #在mysql中调用, 查看结果:select @x;
#在python中 res=cursor.callproc('p5',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123 print(cursor.fetchall()) #只是拿到存储过程中select的查询结果 cursor.execute('select @_p5_0,@_p5_1,@_p5_2') print(cursor.fetchall()) #可以拿到的是返回值 #=============================================== #创建有参存储过程之inout的使用 delimiter // create procedure p6( inout m int ) begin select * from user where id > m; set m=1; end // delimiter ; #在mysql中 set @x=2; call p6(@x); select @x;
delimiter // create procedure p8( inout m int ) begin select * from user111 where id > m; set m=1; end // delimiter ; set @x=2; call p8(@x); select @x; #====================捕捉异常+事务=========================== delimiter // create PROCEDURE p9( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; insert into user(name,balance) values('egon',1); DELETE from tb1111111; #执行失败 COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功 END // delimiter ; #用python模拟 try: START TRANSACTION; DELETE from tb3; #执行失败 insert into blog(name,sub_time) values('yyy',now()); COMMIT; set p_return_code = 0; #0代表执行成功 except sqlexception: set p_return_code = 1; rollback; except sqlwaring: set p_return_code = 2; rollback; mysql> show procedure status like 'p3%'; #查看某一类存储过程
五、流程控制
#函数中不要写sql语句,它仅仅只是一个功能,是一个在sql中被应用的功能
#若要想在begin...end...中写sql,请用存储过程
#while循环 delimiter // CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ; END // delimiter ;