【MySQL】视图，触发器，函数，存储过程，事务，动态执行SQL定义及使用

视图

创建视图的目的是对于重复使用的SQL语句，设置别名，方面以后使用

（1）创建方法

create view 视图名称 as SQL语句
create view 视图名称 as select id from v1；
#对于select id from v1 查询语句，从v1表中查询id列，创建视图

--使用视图
select name from 视图名字

（2）修改视图

alter view 视图名称 as SQL语句；

（3）删除视图

drop view 视图名称;

触发器

触发器的功能是，当对某张表做增删改操作时，可以使用触发器自定义关联行为，比如定义一个触发器，在向某张表插入数据之前，向另一张表中也插入数据

delimiter //
create trigger t1 BEFORE INSERT on student for EACH ROW
BEGIN
INSERT into teacher(tname) values(NEW.sname); --NEW.sname 表示即将插入student表的数据
INSERT into teacher(tname) values(NEW.sname);
INSERT into teacher(tname) values(NEW.sname);
INSERT into teacher(tname) values(NEW.sname);
END //
delimiter ;

-- NEW,代指新数据 获得即将插入的数据
-- OLD, 代指老数据 获得即将删除数据表的数据

其中​​delimiter​​​的作用是修改Mysql中的分隔符，在Mysql中分隔符默认是​​;​​​ 我们将它修改成​​//​​​ 目的是定义好一个完整的触发器再执行sql语句，不然遇到​​;​​​就自动执行了，这样无法创建一个完整的触发器，创建完毕了再利用​​delimiter ;​​​将分隔符改回​​;​​

create trigger t1 AFTER INSERT on student for EACH ROW

也可以在插入之后，使用触发器

函数

Mysql提供了很多内置函数，也可以自定义函数

blog
id title ctime
1 asdf 2019-11-10
2 asdf 2019-11-09
3 asdf 2019-10-20
4 asdf 2019-10-23执行函数的方法
select CURDATE();
select ctime,count(1) from blog group by ctime;
select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group by DATE_FORMAT(ctime, "%Y-%m");

自定义函数(有返回值)

--自定义函数的方法
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int default 0;
set num = i1 + i2;
return(num);
END \\
delimiter ;

SELECT f1(1,100);

存储过程

视图创建别名，只能查询SQL语句，而存储过程可以写插入，修改，查询SQL语句。目的是代替程序员写SQL语句

目前有三种数据库与程序交互的方式

方式1：
MySQL : 存储过程
程序 ： 调用存储过程

方式2 :
MySQL : 无需其他操作
程序 : 写SQL语句

方式3：
MySQL ：无需其他操作
程序 : 类和对象（转换为SQL语句）

（1）创建无参数的存储过程

create procedure p1()
BEGIN
select * from student;
INSERT into teacher(tname) values("ct");
END

call p1()
cursor.callproc('p1')

（2）创建有参数（in，out，inout）的存储过程

in 类型 输入数据
out 类型 执行完存储过程可以拿返回值，伪造函数的返回值，用于标识存储过程的执行结果
inout 类型 兼顾两者特点

in 类型

delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN

select * from student where sid > n1;
END //
delimiter ;

call p2(12,2) -- 数据库终端调用
cursor.callproc('p2',(12,2)) --程序端调用

out类型

delimiter //
create procedure p3(
in n1 int,
out n2 int --out类型参数
)
BEGIN
set n2 = 123123;
select * from student where sid > n1;
END //
delimiter ;

set @_p3_0 = 12 --数据库端调用
set @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1

cursor.callproc('p3',(12,2)) --程序调用存储过程的方法
r1 = cursor.fetchall()
print(r1)

cursor.execute('select @_p3_0,@_p3_1') -- 程序查询存储过程参数的方法
r2 = cursor.fetchall()
print(r2)

（3）程序调用存储过程代码

import pymysql

conn = pymysql.connect(host="localhost",user='root',password='',database="db666",charset='utf8')
cursor = conn.cursor()
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)

cursor.execute('select @_p3_0') #查询第一个参数的值
r2 = cursor.fetchall()
print(r2)
cursor.close()
conn.close()

"""
set @_p3_0 = 12
ser @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1
"""

（4）事务
事务也是存储过程的一种，目的是能做异常处理

delimiter \\
create PROCEDURE p5(
out p_return_code tinyint
)

BEGIN

DECLARE exit handler for sqlexception --先声明异常处理
BEGIN --出现异常执行此部分sql语句
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION; --正常情况，执行下面的sql语句
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT; --执行完需要commit
-- SUCCESS
set p_return_code = 2;

END\\
delimiter ;

（5）游标
对于查询的每一行数据，需要单独操作时才使用游标

示例：有两个数据表A表与B表

A表
id num
1 9
2 …

B表
id num
1 9+1
2 8+2

要实现上面的操作需要使用游标，实现步骤如下：
a. 声明游标
b. 获取A表中数据
my_cursor select id, num from A
c. for row_id,row_num in my_cursor:
#检测循环是否还有数据，如果无数据，break
insert into B(num) values(row_id + row_num)

实现游标的代码

delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE; --声明int类型变量，默认为false，用于检测游标还有数据
declare temp int;

declare my_cursor CURSOR FOR select id,num from A; --声明游标cursor类型
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 游标内部没有值，将变量done设为true

open my_cursor; --打开游标
xxoo: LOOP --进行循环
fetch my_cursor into row_id,row_num; --获得数据
if done then --如果done为true
leave xxoo; -- 退出循环，相当于break
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo; --终止循环
close my_cursor;

（5）动态执行SQL（防止SQL注入，在数据库级别，在程序端也可以防止sql注入，文章​​Pymysql连接mysql​​）

delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
-- 1. 预检测某个东西 SQL语句合法性
-- 2. SQL =格式化 tpl + arg
-- 3. 执行SQL语句

set @xo = arg;
PREPARE xxx FROM 'select * from tb where id > ?';
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimter;

call p7("select * from tb where id > ?",9) --数据库终端调用方式

再举一个动态执行SQL例子

delimiter \\
CREATE PROCEDURE p8 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;