视图、触发器、事务、存储过程

一 视图

视图是一个虚拟表（非真实存在），其本质是根据sql语句获取动态的数据集，并为其命名，
用户使用时只需要使用名称即可获得结果集，可以将该结果集当做表来使用。

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。

mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+

mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师          |
|   2 | 李平老师          |
|   3 | 刘辉老师          |
|   4 | 朱云海老师        |
+-----+-----------------+

#查询李平老师的课程名

select cname from course where 
teacher_id=(select tid from teacher where tname='李平老师' )
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+

#子查询出临时表，作为teacher_id等判断依据
select tid from teacher where tname='李平老师'

1 创建视图

#语法： 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  |
+--------+
| 物理   |
| 美术   |
+--------+

#注意
1.使用视图以后就无需每次都重写子查询的sql，但是这么做效率不高，还不如我们写子查询的效率高

2.而且一个致命的问题：视图是存放到数据库中的，如果我们程序中的sql过分依赖，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便。

2 使用视图

mysql>CREATE VIEW cou2tea as SELECT course.*,teacher.tname FROM course 
INNER JOIN teacher on course.teacher_id=teacher.tid;

mysql> select * from cou2tea;
+-----+--------+------------+--------------+
| cid | cname  | teacher_id | tname        |
+-----+--------+------------+--------------+
|   1 | 生物   |          1 | 张磊老师     |
|   2 | 物理   |          2 | 李平老师     |
|   3 | 体育   |          3 | 刘辉老师     |
|   4 | 美术   |          2 | 李平老师     |
+-----+--------+------------+--------------+

mysql> update cou2tea set teacher_id=4 where cid=1; 
+-----+--------+------------+-----------------+
| cid | cname  | teacher_id | tname           |  #视图发生改变
+-----+--------+------------+-----------------+
|   1 | 生物   |          4 | 朱云海老师      |
|   2 | 物理   |          2 | 李平老师        |
|   3 | 体育   |          3 | 刘辉老师        |
|   4 | 美术   |          2 | 李平老师        |
+-----+--------+------------+-----------------+

mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |   #原始表也同时发生改变
+-----+--------+------------+
|   1 | 生物   |          4 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+

#更新视图中的数据也将同时更新原始表中的记录，准确来说是直接改动原始表中的记录，
#在mysql中视图存储的只有整个表的结构没有数据，之所有有完整数据出来，是因为映射了相应原始表的数据。

#删除同理

注意：
一般创建视图多数只为查询，如果更改视图的内容，则影响了背后多个原始表的数据内容，
所以在涉及多个表的情况下是根本无法修改视图中的记录的。

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

#准备表
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的条件决定是否插入错误日志
mysql> SELECT * FROM errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2020-09-09 16:56:40 |
|  2 | useradd xxx     | 2020-09-09 16:56:40 |
+----+-----------------+---------------------+

#结果有两条错误日志写入

2 使用触发器

触发器无法由用户直接调用，而是由于对表的【增/删/改】操作被动引发的。

3 删除触发器

drop trigger tri_after_insert_cmd;

三 事务

1 什么是事务？

数据库事务是指作为单个逻辑工作单元执行的一系列操作(SQL语句)。
这些操作要么全部执行，要么全部不执行。

2 为什么需要事务？

经典的银行转账行为，A账户转给B账户100元，数据库操作需要两步，第一步A账户减100元，
第二步B账户加100元，如果没有事务并且两步中间发生异常，那么A账户已经发出减100元指令，
然后中间异常，而B账户没有接收到要加100元的指令，那么A账户少了100元而B账户没有加钱，
所以需要这两步的操作统一，即执行成功A减钱B加钱，执行不成功，则A账户的钱没有动或是减了
之后迅速得到返回。

3 事务特性 （4种）

• 原子性：强调事务的不可分割
• 一致性：事务的执行的前后数据的完整性保持一致
• 隔离性：一个事务执行的过程中，不应该受到其他事务的打扰
• 持久性：事务一旦结束，数据就持久到数据库

4 事务运行模式 （3种）

​ 1.自动提交事务：默认事务管理模式。如果一个语句成功地完成，则提交该语句；
​ 如果遇到错误回滚该语句(返回成原来的数据)。
​ 2.显式事务：以begin transaction显示开始，以commit或rollback显示结束。
​ 3.隐式事务：当连接以此模式进行操作时，sql将在提交或回滚当前事务后自动启动新事务，
​ 无序描述事务的开始，只需提交或回滚每个事务，它生成连续的食物链。

create table userinfo(
id int primary key auto_increment,
name char(32),
balance int
);

INSERT into userinfo(name,balance) 
values
('买家',1000),
('第三方',1000),
('卖家',1000);

#原子操作
START TRANSACTION; #声明以下sql语句为一个业务不可分割
UPDATE userinfo set balance=800 where name='买家'; #买家扣200元购买商品
UPDATE userinfo set balance=1100 where name='第三方';#第三方收取100元手续费	
UPDATE userinfo set balance=1100 where name='卖家'; #卖家最后得款100元
commit;   #提交此业务
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | 买家      |     800 |
|  2 | 第三方    |    1100 |
|  3 | 卖家      |    1100 |
+----+-----------+---------+

#出现异常，回归到初始状态
START TRANSACTION; #声明以下sql语句为一个业务
UPDATE userinfo set balance=900 where name='买家'; #买家扣200元购买商品
UPDATE userinfo set balance=1100 where name='第三方';#第三方收取100元手续费	
UPDATE userinfo set balance=1100 where name='卖家'; #卖家最后得款100元
ROLLBACK;
commit;   #提交此业务
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | 买家      |    1000 |
|  2 | 第三方    |    1000 |
|  3 | 卖家      |    1000 |
+----+-----------+---------+

利用python捕捉异常后进行事务回滚，没有异常则直接提交事务

import pymysql

conn = pymysql.connect(host='hostlocal', user='root', password='giaogiao', database='db')

cursor = conn.cursor()
sql_1 = 'UPDATE userinfo set balance=900 where name="买家";'
sql_2 = 'UPDATE userinfo set balance=1100 where name="第三方";'
sql_3 = 'UPDATE userinfo set balance=1100 where name="卖家"; '

try:
    cursor.execute(sql_1)
    cursor.execute(sql_2)
    cursor.execute(sql_3)
except Exception as e:
    conn.rollback()  # 出现异常直接返回到原来的数据状态
    print('事务处理失败')

else:
    conn.commit()  # 没有异常直接提交到库中保存
    print('事务处理成功')

cursor.close()
conn.close()

总结：事务用于将某些操作的多个sql作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

四 存储过程

1 介绍

存储过程包含了一系列可执行的sql语句，存储过程存放于mysql中，通过调用它的名字可以执行其内部的一对sql

使用存储过程的优点：

​ 1.用于替代程序写的sql语句，实现程序与sql的解耦合

​ 2.基于网络传输，传别名的数据量小，而直接传sql数据量大

使用存储过程的缺点：

​ 程序员扩展功能不方便

补充：程序与数据库结合使用的三种方式

#方式一：
	mysql：存储过程
	程序：调用存储过程
	（解耦合）
	
#方式二：
	mysql：
	程序：纯sql语句
	（耦合）
	
#方式三：
	mysql：
	程序：类和对象，即ORM（本质还是纯sql语句）

2 创建简单储存过程（无参）

#创建blog表
CREATE TABLE blog(
id int PRIMARY KEY auto_increment,name VARCHAR(10),sub_time datetime not null DEFAULT now());

#制造存储过程对象名为p
delimiter //
CREATE PROCEDURE p()
BEGIN
	SELECT * FROM blog;
	INSERT INTO blog(name,sub_time) values('xxx',now());
END //
delimiter;

#在mysql中通过p调用存储过程中的sql语句(里面sql语句全部执行一遍)
call p()

#在python中基于pymysql调用
import pymysql

conn = pymysql.connect(host='hostlocal', user='root', password='giaogiao', database='db')
cursor = conn.cursor()
cursor.callproc('p')
print(cursor.fetchall())

3 创建存储过程(有参)

对于存储过程，可以接收参数，其参数有三类：

#in    仅用于传入参数用
#out   仅用于返回值用
#inout 既可以传入又可以当做返回值

#in：传入参数

delimiter //
CREATE PROCEDURE p2(in n int)
BEGIN
	SELECT * FROM blog where id>n;
	
END //
delimiter;

#在mysql中调用
call p2(3)

#在python中基于pymysql调用
cursor.callproc('p2',(3,)) #必须以元组的形式传参
print(cursor.fetchall())

#out：返回值

delimiter //
CREATE PROCEDURE p3(
		in n int,
		out res INT
)
BEGIN
	SELECT * FROM blog where id>n;
	set res=1;
	
set @res=1; #0代表假，1代表真 
call p3(2,@res);
SELECT @res; #产生第二结果是@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())

# ((3, 1),) 参数3为第一个参数，1是第二个参数设定的返回值

#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())

4 执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(参数1,参数2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(参数1,参数2,@t1,@t2)

5 删除存储过程

drop procedure proc_name;