一、视图
视图是一个虚拟表并不是（正实存在的）
创建老师表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 cascadeon 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 ROWBEGIN...END# 插入后CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN...END# 删除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN...END# 删除后CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN...END# 更新后CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN...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 ROWBEGINIF 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()beginselect * 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)beginselect * 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)beginselect * from user where id between m and n;set res=1;end //delimiter ;#调用存储过程#在mysql中set @x=11111111111call p5(3,7,@x); #在mysql中调用, 查看结果：select @x;
#在python中res=cursor.callproc('p5',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123print(cursor.fetchall()) #只是拿到存储过程中select的查询结果cursor.execute('select @_p5_0,@_p5_1,@_p5_2')print(cursor.fetchall()) #可以拿到的是返回值#===============================================#创建有参存储过程之inout的使用delimiter //create procedure p6(inout m int)beginselect * 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)beginselect * 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)BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset p_return_code = 2;rollback;END;START TRANSACTION;insert into user(name,balance) values('egon',1);DELETE from tb1111111; #执行失败COMMIT;-- SUCCESSset 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 ()BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END //delimiter ;
