'''
视图是一个虚拟表，使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显的效率问题，并且视图是存放在数据库中的，如果程序中使用的sql过分依赖数据库中的视图，即强耦合，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，就意味着扩展sql极为不便。
'''
1.创建视图
# 语法：create view 视图名称 as sql语句
create view teacher_view as select tid from teacher where tname='ldb';
# 使用
select cname from course where teacher_id=(select tid from teacher_view);
2.修改视图
# 语法：alter view 视图名称 as sql语句
alter view teacher_veiw as select * from course where cid>3;
3.删除视图
# 语法：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
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') # no代表执行失败
);
-- 错误日志表
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
('ldb','0755','ls -l /etc',now(),'yes'),
('ldb','0755','cat /etc/passwd',now(),'no'),
('ldb','0755','useradd xxx',now(),'no'),
('ldb','0755','pa aux',now(),'yes');
-- 查询错误日志，发现有两条
select * from errlog;
'''
特别的：new表示即将插入的数据行，old表示即将删除的数据行
'''
3.删除触发器
drop trigger tri_after_insert_cmd;
三存储过程
'''
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
'''
1.创建简单存储过程(无参)
-- 创建存储过程(无参)
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())
2.创建存储过程(有参)
# 对于存储过程，可以接收参数，有三类
in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当做返回值
-- in:传入参数
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
begin
select * from blog where id>n1;
end //
delimiter;
# 在mysql中调用
call p2(2,1)
# 在python中基于pymysql调用
cursor.callproc('p2',(2,1))
print(cursor.fetchall())
-- out:返回值
delimiter //
create procedure p3(
in n1 int,
out res int
)
begin
select * from blog where id>n1;
set res=1;
end //
delimiter;
# 在mysql中调用
set @res=0; # 0代表执行失败，1代表执行成功
call p3(3,@res);
select @res;
# 在python中基于pymysql调用
cursor.callproc('p3',(3,0))
print(cursor.fetchall())
cursor.execute('select @_p3_0,@_p3_1;') # @_p3_0代表第一个参数，@_p3_1代表第二个参数，即返回值
print(cursor.fetchall())
-- inout:既可以传入又可以返回
delimiter //
create procedure p4(