视图、事务、存储过程

一、视图

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

不推荐使用的原因：

使用视图可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作临时表的数据时，就不需要重复写复杂的sql了，直接去视图中查找即可，但是视图有明显的效率问题
并且视图是存放于数据库中的，如果程序中的SQL过分依赖数据库中的视图，即强耦合，即意味着不好拓展SQL

1、创建视图

语法：

create view视图名称 as SQL语句;

示例：

# 创建查看陆老师id的视图
create view teacher_view as select tid from teacher where tname="陆老师";
#查询陆老师教授的课程名的sql可以写为
select cname from course where teacher_id =(select tid from teacher_view);

# 值得注意的是：这么写还没有写子查询效率高
# 另外SQL一旦需要修改涉及视图的部分，则必须要去数据库中进行修改，及其不容易

2、使用视图

- 修改视图，原始表也得跟着改，所以我们不应该修改视图中的记录
	create view course_view as select * from course; #创建表course的视图
    update course_view set cname='xxx'; #更新视图中的数据
    insert into course_view values(5,'yyy',2); #往视图中插入数据
    select * from course; #发现原始表的记录也跟着修改了
- 在涉及多个表的情况下无法修改视图中的记录

3、修改视图

语法：
alter view 视图名称 as sql语句
示例：
alter view teacher_view as select * from teacher where tid >3;

4、删除视图

语法：

drop view 视图名称

二、触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，不包含【查】

1、创建触发器

• 增=》insert

# 前触发
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
	sql语句;
end

# 后触发
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
	sql语句;
end

• 删=》delete

# 前触发
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
	sql语句;
end

# 后触发
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
	sql语句;
end

• 修改=》update

示例：

insert into tt1 values(1,'jason','male');

delimiter //
create trigger tri_before_insert_tt1 before insert on tt1 for each row
begin
	insert into tt2 values(NEW.name);
end //
delimiter;

2、使用触发器

触发器无法由用户直接调用，只能由于 对表的【增、删、改】操作被动引发

3、删除触发器

drop trigger tri_after_insert_cmd;

三、事务

1、什么是事务？
数据库事务是指作为单个逻辑工作单元执行的一些列操作（SQL语句），这些操作要么全部执行，要么全部不执行。
2、为什么需要事务
为了保证数据操作的过程中遇到一些异常情况下的数据安全，比如转账一方扣钱，但是另一方并没有加钱，使用事务管理就会保证转账这个操作要么成功，两边账户金额都变化；要么失败，两边账户金额都不变化
3、事务特性（4种）：
	- 原子性：强调事务的不可分割
    - 一致性：事务执行的前后数据的完整性保持一致
    - 隔离性：一个事务执行的过程中，不应受到其他事务的干扰
    - 持久性：事务一旦结束，数据就持久到数据库
4、事务运行模式（3种）：
	-自动提交事务：默认事务管理模式，如果一个语句成功的完成，则提交该语句；如果遇到错误，则回滚该语句
    -显式事务：以begin transaction显式开始，以commit或者rollback显式结束
    -隐性事务：当连接此模式进行操作时，SQL将在提交或者回滚当前事务后自动启动新事务。
    		无需描述事务的开始，只需提交或者回滚每个事务。它生成连续的食物链

具体实现示例：

# 创建表
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
# 插入记录
insert into user(name,balance)
values
('wuli',1000),
('cc',1000),
('coco',1000);
# 原子操作
start transaction;
update user set balance=800 where name='wuli'; # 模拟买家支付200元
update user set balance=1100 where name='cc'; # 模拟中介抽取100元
update user set balance=1100 where name='coco'; # 模拟卖家得到100元
commit;

# 如果出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wuli'; #买支付200元
update user set balance=1100 where name='cc'; #中介拿走100元
ubdate user set balance=1100 where name='coco'; #卖家得100元,出现异常没有拿到
rollback;  # 回滚
commit;  # 数据真正提交到数据库
mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | wuli |    1000 |
|  2 | cc   |    1000 |
|  3 | coco |    1000 |
+----+------+---------+
3 rows in set (0.00 sec)

pymysql操作：

try:
    cursor.execute(sql_1)
    cursor.execute(sql_2)
    cursor.execute(sql_3)
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败',e)
else:
    connect.commit()  # 事务提交
    print("事务处理成功",cursor.rowcount) # 关闭连接
cursor.close()
connect.close()

总结：

事务用于将某些操作的多个SQL作为原子性操作，一旦其中有一个出现异常，即可回滚到原来的状态，
从而保证数据库的数据完整性。

四、存储过程

1、介绍

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

• 使用存储过程的优点：

1、用于替代程序写的SQL语句，实现程序与sql解耦合
2、基于网络传输，传输的数据量小，而直接传SQL数据量大

• 使用存储过程的缺点：

程序员拓展功能不方便

• 程序与数据库结合使用的三种方式

方式一：
	MySQL：存储过程
    程序：调用存储过程
    
方式二：
	MySQL：
    程序：纯SQL语句
    
方式三：
	MySQL：
    程序：类和对象，即ORM（本质还是SQL语句）

2、创建简单的无参存储过程：

delimiter //
create procedure p1()
begin
	select * from emp;
end //
delimiter ;

# 在MySQL中调用
call p1();

# 在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())

3、创建有参存储过程

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

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

示例：

delimiter $$
create procedure p3(
	in n int,
    out res int
)
begin
	select * from emp where id > n;
    set res=1;
end $$
delimiter ;

# mysql中调用：
set @res = 0;  # 定义变量res要加@符号，【假设0代表假（执行失败）,1代表真（执行成功）】
call p3(3,@res);
select @res;  # res的值被存储过程改成了1

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

4、执行存储过程

- 无参数
call proc_name()

- 有参数
call proc_name(1,2)

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

在python中基于pymysql执行存储过程

import pymysql

conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='111',db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1',args=(1,2,3,4))
# 获取执行完存储的参数
cursor.execute('select @_p1_0,@_p1_1,@_p1_2.@_p1_3')
print(cursor.fetchall())

conn.commit()
cursor.close()
conn.close()

5、删除存储过程

drop procedure proc_name;

五、删除存储过程

MySQL中提供了许多内置函数，例如：数学函数、聚合函数、字符串函数、日期和时间函数、加密函数、控制流函数

1、基本使用

select date_format('2020-01-01 11:11:11','%Y-%m-%d');  # 2020-01-01
select date_format('2020-01-01 11:11:11','%W %M %Y');  # Wednesday January 2020
select date_format('2020-01-01 11:11:11','%H:%i:%s');  # 11:11:11

2、将表中的记录的时间（月份）提取出来

create table blog(id int primary key auto_increment,
                  name varchar(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');

# 提取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)

3、自定义函数

# 注意：
- 函数会中不要写SQL语句（否则会报错），函数仅仅只是一个功能，是一个在SQL中被应用的功能
- 想在begin...end...中写SQL，要用存储过程

• 示例：

# 例一
delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;

# 例二：
delimiter //
create function f5(
    i int
)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;

• 删除函数：

drop function 函数名;

• 执行函数

select f1(11,nid),name from tb2;

六、流程控制（了解即可）

1、if 条件语句

2、循环语句

- while
- repeat
- loop