MySQL视图，函数，触发器，存储过程

1. 视图　　

视图是一个虚拟表，它的本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，
但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，
因此不推荐使用. 而且工作中一般不方便，因为是虚拟表不方便共用，如果需要修改，可能设计到与DBA的沟通，很麻烦

1 1  -- 使用视图
2 2 select .. from v1
3 3 select asd from v1
4 4 -- 某个查询语句设置别名，日后方便使用
5 5 
6 6 - 创建
7 7 create view 视图名称 as SQL
8 8 
9 9 PS: 虚拟的，临时表 无法插入操作 
1010 
1111 - 修改
1212 alter view 视图名称 as SQL
1313 
1414 - 删除
1515 drop view 视图名称;

2. 触发器

定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
工作中一般也很少用到，因为自己在代码中就能设计操作前后的行为

1insert into tb (....)
2
3delimiter //   -- 修改结束标记
4create trigger t1 BEFORE INSERT on student for EACH ROW   -- 创建insert操作前的触发器
5BEGIN   -- 触发器具体内容
6     INSERT into teacher(tname) values(NEW.sname);
7     INSERT into teacher(tname) values(NEW.sname);
8     INSERT into teacher(tname) values(NEW.sname);
9     INSERT into teacher(tname) values(NEW.sname);
10 END //  
11delimiter ;  -- 恢复默认的语句结束标记
12
13------------------------------------------------
14
15insert into student(gender,class_id,sname) values('女',1,'涛'),('女',1,'根');
16
17-- NEW,代指新数据 可以在触发器中点语法使用
18-- OLD,代指老数据  删除的那一行记录被OLD引用

3.函数

因为在sql语句执行中调用函数会比较耗时，而且对索引的那一列使用了函数，则无法命中索引了。

所以工作中对响应速度要求高，一般不会不使用函数处理结果集。而是在架构级别或者程序级别处理结果集。

内置的函数很多，详情参看官方文档

1-- 内置函数：
2            执行函数 select CURDATE();
3            
4            blog
5            id       title            ctime
6             1        asdf        2019-11
7             2        asdf        2019-11
8             3        asdf        2019-10
9             4        asdf        2019-10
10             
11             
12            select ctime,count(1) from blog group ctime
13            
14            select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
15            2019-11   2
16            2019-10   2
17            
18        DATE_FORMAT  时间格式化函数，较常用
19        
20        
21--        自定义函数(必须有返回值)：
22            
23            delimiter \\
24                create function f1(
25                    i1 int,
26                    i2 int)
27                returns int
28                BEGIN
29                    declare num int default 0;
30                    set num = i1 + i2;
31                    return(num);
32                END \\
33            delimiter ;
34            
35            SELECT f1(1,100);

4. 存储过程

包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，可以让程序与sql解耦合，且执行通过一个名字减少数据传输。mysql 5.5版本以后才有的功能
开发岗位一般也比较少使用。主要会是DBA使用。

用MySQL的三种方式：

方式一：
　　MySQL: 存储过程
　　程序：调用存储过程
方式二：
　　MySQL：。。
　　程序：SQL语句
方式三：
　　MySQL：。。
　　程序：类和对象（SQL语句）

MySQL中代码属于强类型语言，变量需要先声明变量名和变量类型.

1.简单示例

1delimiter //
2create procedure p2(
3    in n1 int,
4    in n2 int
5)
6BEGIN
7    ----- 获取大于传参数字的id行
8    select * from student where sid > n1;
9END //
10delimiter ;
11
12---------------- 命令行
13call p2(12,2)
14
15--------------- pymsql
16cursor.callproc('p2',(12,2))

2.传参数（in）

1delimiter //
2create procedure p3(
3    in n1 int,
4    inout n2 int
5)
6BEGIN
7    set n2 = 123123;
8    select * from student where sid > n1;
9END //
10delimiter ;
11
12------------------- 
13set @v1 = 10;
14call p2(12,@v1)
15select @v1;
16
17set @_p3_0 = 12
18ser @_p3_1 = 2
19call p3(@_p3_0,@_p3_1)
20select @_p3_0,@_p3_1
21
22
23
24------------------------ pymysql
25
26cursor.callproc('p3',(12,2))
27r1 = cursor.fetchall()
28print(r1)
29
30
31cursor.execute('select @_p3_0,@_p3_1')  #  @_p3_0 是底层创建好的名字
32r2 = cursor.fetchall() # 去除out值
33print(r2)

3.参数 out

为什么有结果集又有out伪造的返回值？

1delimiter //
2create procedure p3(
3    in n1 int,
4    out n2 int  -- 用于标识存储过程的执行结果  一般用 tinyint 1,2等来表示相应的执行结果，方便程序获取后知道执行结果
5)
6BEGIN
7    insert into vv(..)
8    insert into vv(..)
9    insert into vv(..)
10    insert into vv(..)
11    insert into vv(..)
12    insert into vv(..)
13END //
14delimiter ;

View Code

1delimiter //
2create procedure p4(
3    out status int
4)
5BEGIN
6    -- 伪代码描述
7    1. 声明如果出现异常则执行{
8        set status = 1;
9        rollback;
10    }
11       
12    开始事务
13        -- 由秦兵账户减去100
14        -- 方少伟账户加90
15        -- 张根账户加10
16        commit;
17    结束
18    
19    set status = 2;
20    
21    
22END //
23delimiter ;
24
25===============================
26delimiter \\
27create PROCEDURE p5(
28    OUT p_return_code tinyint
29)
30BEGIN 
31  DECLARE exit handler for sqlexception 
32  BEGIN 
33    -- ERROR 
34    set p_return_code = 1; 
35    rollback; 
36  END; 
37 
38  START TRANSACTION; 
39    DELETE from tb1;
40    insert into tb2(name)values('seven');
41  COMMIT; 
42 
43  -- SUCCESS 
44  set p_return_code = 2; 
45 
46  END\\
47delimiter ;

4.事务

1delimiter //
2create procedure p6()
3begin 
4    declare row_id int; -- 自定义变量1  
5    declare row_num int; -- 自定义变量2 
6    declare done INT DEFAULT FALSE;  -- 默认为false 表述循环未执行完
7    declare temp int;
8    
9        -- 声明游标
10    declare my_cursor CURSOR FOR select id,num from A;
11    declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
12    
13    -- 开始循环
14    open my_cursor;
15        xxoo: LOOP
16            fetch my_cursor into row_id,row_num;
17            if done then   --  需要自己判断是否循环结束
18                leave xxoo; -- 结束循环
19            END IF;
20            set temp = row_id + row_num;
21            insert into B(number) values(temp);
22        end loop xxoo;
23    close my_cursor;
24    
25end  //
26delimter ;

5.游标-实现循环语句

游标性能比较差，一般很少用，使用场景是：针对每一行都需要专门的处理计算的时候可能会用到，但是一般update+ 循环也能解决如：UPDATE B set num=id+num;

1delimiter //
2create procedure p7(
3    in tpl varchar(255),
4    in arg int
5)
6begin 
7    set @xo = arg;
8    PREPARE prod FROM 'select * from student where sid > ?'; -- 1. 预检测某个东西 SQL语句合法性
9    EXECUTE prod USING @xo; -- 2. SQL =格式化 tpl + arg 
10    DEALLOCATE prepare prod; -- 3. 执行SQL语句
11end  //
12delimter ;
13
14---------------------------
15call p7("select * from tb where id > ?",9)

6. 动态执行SQL（防SQL注入）