正文
mysql:视图，触发器，事务，存储过程，函数
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
一、视图
视图是一个虚拟表并不是（ 正实存在的 ）
创建老师表 create table teacher(
id int primary key auto_increment,
tname varchar()
); 创建课程表
create table course(
id int primary key auto_increment,
cname varchar(),
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
('生物',),
('物理',),
('体育',),
('美术',);
两张有关系的表
#查询李平老师教授的课程名
#子查询出临时表，作为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 (),
priv CHAR (),
cmd CHAR (),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
); CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (),
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','','ls -l /etc',NOW(),'yes'),
('egon','','cat /etc/passwd',NOW(),'no'),
('egon','','useradd xxx',NOW(),'no'),
('egon','','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(),
balance int
); insert into user(name,balance)
values
('wsb',),
('egon',),
('ysb',); #原子操作
start transaction;
update user set balance= where name='wsb'; #买支付100元
update user set balance= where name='egon'; #中介拿走10元
update user set balance= where name='ysb'; #卖家拿到90元
commit; #出现异常，回滚到初始状态
start transaction;
update user set balance= where name='wsb'; #买支付100元
update user set balance= where name='egon'; #中介拿走10元
uppdate user set balance= 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',);
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(,); #在mysql中调用
cursor.callproc('p4',args=(,)) #在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=;
end //
delimiter ;
#调用存储过程
#在mysql中
set @x=
call p5(,,@x); #在mysql中调用, 查看结果：select @x;
#在python中
res=cursor.callproc('p5',args=(,,)) #@_p3_0=,@_p3_1=,@_p3_2=
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=;
end //
delimiter ; #在mysql中
set @x=;
call p6(@x);
select @x;
delimiter //
create procedure p8(
inout m int
)
begin
select * from user111 where id > m;
set m=;
end //
delimiter ; set @x=;
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 = ;
rollback;
END; DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = ;
rollback;
END; START TRANSACTION;
insert into user(name,balance) values('egon',);
DELETE from tb1111111; #执行失败
COMMIT; -- SUCCESS
set p_return_code = ; #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代表执行成功
except sqlexception:
set p_return_code = ;
rollback;
except sqlwaring:
set p_return_code = ;
rollback; mysql> show procedure status like 'p3%'; #查看某一类存储过程
五、流程控制
#函数中不要写sql语句，它仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
#while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN DECLARE num INT ;
SET num = ;
WHILE num < DO
SELECT
num ;
SET num = num + ;
END WHILE ; END //
delimiter ;
