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

1. 视图

创建视图的目的是对于重复使用的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 视图名称;

2. 触发器

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

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

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

3. 函数

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);

4. 存储过程

视图创建别名，只能查询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 ;