Day 48 MySql 学习笔记：视图、触发器、事务、存储过程、函数
Part 1: 视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。
#语法：CREATE VIEW 视图名称 AS SQL语句 create view teacher_view as select tid from teacher where tname='李平老师'; #于是查询李平老师教授的课程名的sql可以改写为 mysql> select cname from course where teacher_id = (select tid from teacher_view); +--------+ | cname | +--------+ | 物理 | | 美术 | +--------+ 2 rows in set (0.00 sec) #！！！注意注意注意： #1. 子查询不能作为 视图 否则会报错。只能把 join 联表作为视图保存；保存后会在硬盘上新建一个frm表框架文件，没有数据文件。因为这是一张虚拟表，该表的数据全部来自原表 #2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便
PS：我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的；若想要修改视图，推荐先把原视图删除，再重新创建视图。
例如：DROP VIEW teacher_view
Part 2: 触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
#准备表 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'); #查询错误日志，发现有两条 mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2017-09-14 22:18:48 | | 2 | useradd xxx | 2017-09-14 22:18:48 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec)
PS：NEW表示即将插入的数据行，OLD表示即将删除的数据行。
触发器无法由用户直接调用，而是由于对表的【增/删/改】操作被动引发的。
删除触发器：drop trigger tri_after_insert_cmd;
Part 3: 事务
事务用于将某些操作的多个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; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | egon | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.00 sec)
Part 4: 存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql。
使用存储过程的优点：
#1. 用于替代程序写的SQL语句，实现程序与sql解耦
#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
使用存储过程的缺点：
# 程序员扩展功能不方便
Part 4.2: 创建一个存储过程（无参）
delimiter // create procedure p1() BEGIN select * from blog; INSERT into blog(name,sub_time) values("xxx",now()); END // delimiter ; #在mysql中调用 call p1() #在python中基于pymysql调用 cursor.callproc('p1') print(cursor.fetchall())
Part 4.3: 创建一个存储过程（传参 in）
delimiter // create procedure p2( in n1 int, in n2 int ) BEGIN select * from blog where id > n1; END // delimiter ; #在mysql中调用 call p2(3,2) #在python中基于pymysql调用 cursor.callproc('p2',(3,2)) print(cursor.fetchall())
Part 4.4 创建一个存储过程（传参 out)
delimiter // create procedure p3( in n1 int, out res int ) BEGIN select * from blog where id > n1; set res = 1; END // delimiter ; #在mysql中调用
#mysql> call p3(3,0);
#ERROR 1414 (42000): OUT or INOUT argument 2 for routine day48.p3 is not a variable or NEW pseudo-variable in BEFORE trigger
set @res=0; #0代表假（执行失败），1代表真（执行成功） call p3(3,@res); select @res; #在python中基于pymysql调用 cursor.callproc('p3',(3,0)) #0相当于set @res=0 print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值 print(cursor.fetchall())
Part 4.5 创建一个存储过程（传参 inout）
delimiter // create procedure p4( inout n1 int ) BEGIN select * from blog where id > n1; set n1 = 1; END // delimiter ; #在mysql中调用 set @x=3; call p4(@x); select @x; #在python中基于pymysql调用 cursor.callproc('p4',(3,)) print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p4_0;') print(cursor.fetchall())
PS：删除一个存储过程 drop procedure proc_name;
Part 5：事务 与 存储过程 的 组合使用
delimiter // create procedure p4( out status int ) BEGIN 1. 声明如果出现异常则执行{ set status = 1; rollback; } 开始事务 -- 由秦兵账户减去100 -- 方少伟账户加90 -- 张根账户加10 commit; 结束 set status = 2; END // delimiter ; #实现 delimiter // create PROCEDURE p5( 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; DELETE from tb1; #执行失败 insert into blog(name,sub_time) values('yyy',now()); COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功 END // delimiter ; #在mysql中调用存储过程 set @res=123; call p5(@res); select @res; #在python中基于pymysql调用存储过程 cursor.callproc('p5',(123,)) print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p5_0;') print(cursor.fetchall())
Part 6: 函数（date_format）
#1 基本使用 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00' #2 准备表和记录 CREATE TABLE blog ( id INT PRIMARY KEY auto_increment, NAME CHAR (32), sub_time datetime ); INSERT INTO blog (NAME, sub_time) VALUES ('第1篇','2015-03-01 11:31:21'), ('第2篇','2015-03-11 16:31:21'), ('第3篇','2016-07-01 10:21:31'), ('第4篇','2016-07-22 09:23:21'), ('第5篇','2016-07-23 10:11:11'), ('第6篇','2016-07-25 11:21:31'), ('第7篇','2017-03-01 15:33:21'), ('第8篇','2017-03-01 17:32:21'), ('第9篇','2017-03-01 18:31:21'); #3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组 SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m'); #结果 +-------------------------------+----------+ | DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) | +-------------------------------+----------+ | 2015-03 | 2 | | 2016-07 | 4 | | 2017-03 | 3 | +-------------------------------+----------+ 3 rows in set (0.00 sec)
Part 7: 条件语句
delimiter // CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END // delimiter ;
Part 8: 循环语句
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 ;
浙公网安备 33010602011771号