Python MySQL（SQL语句的补充3）

1. 视图：
   1. 当重复使用到某个查询语句时，可以设置别名，方便日后使用，这样就叫做创建视图
   2. 创建：
      create view 视图名称 as 查询语句     #  这是虚拟的，原来的表改变这个视图也会改变
   3. 修改：
      alter view 视图名称 as 查询语句
   4. 删除：
      drop view 视图名称
2. 触发器：
   1. 在创建时，要先修改终止符，否则执行到;时就会终止，不会执行END，这样就创建不出触发器
      delimiter   //   #  这样可以把终止符改成//
   2. create trigger 名称 before/after insert on tb1 for each row begin  SQL语句;  END //
      #  在表执行XX命令前/后时，开始执行 另一条语句
   3. begin 的SQL语句可以使用NEW来取得插入的一行数据，也可以用NEW.XX来取得插入数据的某一列
      删除时可以用到OLD，更新时就有NEW和OLD
      比如  begin insert into t2(tname) values(NEW.sname)
      
   4. 创建完之后应该改回来  delimiter   ;
   5. 有插入删除更新  insert delete update
3. 函数：
   1. 内置函数
      1. 对时间进行修剪(假如time 为 2020-1-1)
         select date_format(time,"%Y-%m")  from t1 group date_format(time,"%Y-%m")  
         这样就可以通过年月对表信息进行分组
   2. 自定义函数
      1. delimiter \\
         create function f1(
         i1 int,i2 int)   returns int        #  限定参数和返回的值只能是int类型
         BEGIN
         declare num int;                   #  声明变量，还可以在声明变量时赋值  declare num int default 0 ; 
         set num = i1+i2;
         return(num)
         END \\
         delimiter;
         select f1(1,2)；  结果就是3
         不然在过程中执行select * from t1；之类的
      2. 如果出现1418错误可以使用以下方法：
         
         1. 在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
         2. MySQL启动时，加上--log-bin-trust-function-creators，参数设置为1
         3. 在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1
4. 存储过程*
   保存在MySQL上的别名  》》》》》一堆SQL语句，跟视图不一样，视图是表
   这样就不用写基础的SQL语句了  ，有人在数据库中写了就能直接使用
   
   1. 简单存储过程
      delimiter \\
      create procedure p1()
      BEGIN
      　　select * from t1;
      　　insert into t1(id) values(1)
      END \\
      delimiter ;
      call p1()
      Python中用cursor.callproc('p1')
   2. 传参数(in、out(跟return差不多)、inout)
      delimiter \\
      create procedure p1(
      in n1 int,
      out n2 int
      )
      BEGIN
      　　select * from t1 where id > n1;
      　　n2 = n1
      END \\
      delimiter ;
      set @v1 = 0
      call p1(2,@v1)       #  相当于把V1当成n2传进去
      select @V1;
      cursor.callproc('p1',(2,X))
      
   3. 特性： 
      1. 可传参： in out inout
      2. 虽然没有return 但是可以用out伪造一个return
      3. 为什么有结果集，又有out伪造的返回值？
         用于表示存储过程的执行结果（看下面的例子）
   4. 事务：
      1. delimiter \\
         create procedure p1(
         out n1 tinyint
         )
         BEGIN                                        #  声明如果出现异常所执行的语句
                DECLARE exit handler for sqlexception    
                BEGIN
                -- ERROR
                set n1 =1;                              #  如果执行失败 n1=1
                rollback;                                #  执行回滚
                END;
                START TRANSACTION;       #  开始事务
         　　    select * from t1 where id > n1;
                COMMIT;                               #  如果执行没有出错就提交
                 -- SUCCESS
                  set n1 = 2                             #  如果执行成功 n1=2
         END \\
         delimiter ;
         
   5. 游标：（可以在MySQL中简单实现一些循环）
      1. 使用思路
         1. 声明游标
         2. 
            
            获取A表数据中取数据
            my_cursor select aid,anum from A
         3. for row_id,row_num in my_cursor :   
            　　#  检测循环是否有数据，如果无数据就  break  （要自己检测）
            　　insert into B(bnum) values(row_id+row_num)
      2. 示例
         delimiter \\
         create procedure p1()
         BEGIN
         　　declare row_id int ;                                  #  自定义变量1
                declare row_num int ;                              #  自定义变量2
                declare done INT DEFAULT FALSE ;      #  初始为FALSE，判断循环是否终止
         
                declare my_cursor CURSOR FOR select aid,anum from A ;                       #  定义游标，从表A中获取数据
                declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;    #  判断，当没有数据时设done=TRUE
                open my_cursor;                                                     #  光标开始执行
                       xxoo:LOOP                                                      #  开始循环，循环名为xxoo
                              fetch my_cursor into row_id,row_num;     #  获取row_id和row_num
                              if done then                                               #  如果done为TRUE就离开循环
                                     leave xxoo;
                              END IF;
         
                              insert into B(num) values(row_id+row_num);
                       end loop xxoo;
                close my_cursor
         END  \\
         delimiter ;
         
         
   6. 动态执行SQL（防SQL注入）
      delimiter \\
      create procedure p1(
             in arg int
      )
      BEGIN
             1.预检测 SQL语句的合法性
             2.初始化SQL=tpl+arg
             3.执行SQL语句
             set @v1 = arg;
             PREPARE prod FROM  'select * from A where id > ?';
             EXECUTE prod USING @v1;         #  只接受会话级别的变量，把？替换成@v1
             DEALLOCATE prepare prod ;          #   执行SQL
      END \\
      delimiter ;
      
      call p1(6)