视图

视图的概念在之前已经接触过,其实就是查询中所使用的临时表,如果一个临时表经常被使用,那可以使用生成这个临时表的语句来定义一个视图,以后只需要将这个视图当做一个表来用就可以了.

视图实际上就是一个SQL查询,其数据基于原始表格,如果原始表格的数据有所变动,查询结果也用到变动后数据的话,则视图的数据也会变动,但要知道,视图并不存储任何数据,最好也不要向视图内增加删除数据.视图还一个作用就是保护原始数据,比如可以只给用户视图的查询权限,而不是原始数据的权限,这样既让用户可以有效的查询数据,又保护了原始数据的安全.

视图可以重复使用SQL语句,视图还有几个特点:名字不能与其他表和视图重复,视图可以嵌套,即里边的SELECT可以来自其他视图,视图的ORDER BY会被其中SELECT语句的ORDER BY覆盖,视图不能进行索引,也没有触发器和默认值.一般视图仅用来查询,而不去做查询之外的事情.除此之外,可以将视图当成普通的表使用

触发器

在对某个表进行增删改的时候,在这个动作之前或者之后,可以设置触发器,让触发器执行指定的动作.触发器响应的语句只有INSERT UPDATE DELETE三种,外加在之前还是之后,所以一共有6种触发器语句,查询语句没有触发器.

触发器的基本语法是

delimiter //
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
    ...
END//
delimiter ;

触发器如果针对INSERT和UPDATE语句,在BEGIN和END之间可以引用new,是表示插入或者修改的内容,在DELETE语句中可以引用old,表示是删除的数据.这样可以实现在数据获取之间就修改里边的内容,控制插入数据库的内容.删除的时候也能够获得数据,进行操作

函数

之前已经接触了内置函数,用来对列数据进行处理,这次主要讲自定义函数

# 创建自定义函数
delimiter \\
CREATE FUNCTION f1(
    i1 INT,
    i2 INT)
RETURNS INT
BEGIN
    DECLARE num INT;
    SET num = i1 + i2;
    RETURN(num);
END \\
delimiter ;

可见MySQL里的函数类似强类型语言的定义,需要定义函数名,传输参数的数据类型,和返回的数据类型,在函数体内,使用变量需要先声明,然后用SET 进行赋值.

函数体内不能够写SQL语句,只能够写逻辑语句.函数的删除用DROP FUNCTION语句,函数调用则和内置函数一样,需要传入对应类型的列参数

在MySQL 中,定义个变量,一般使用

存储过程

存储过程,就是一系列SQL语句的集合,分为带参数和不带参数

先来看一个简单的存储过程:

delimiter //
CREATE PROCEDURE p1()
BEGIN
    select * from user;
END//
delimiter ;

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    database='homework',
    charset='utf8')
cursor = conn.cursor()
cursor.callproc('p1')
conn.commit() 
res = cursor.fetchall()
print(res)
cursor.close()
conn.close()

存储过程还可以带参数,参数有三种类型,in,out和inout,其中in表示传入,这个值即使内部修改了,也不影响外部,out表示这个变量只能返回值,即使传入了,内部也拿不到这个值,但是执行之后能拿到修改后的值,inout表示既可以传入又可以当成返回值.这个值有可能在调用存储过程之后改变.

带参数的存储过程

只带in的参数比较简单,可以传入常量,对于out和inout参数,必须要传入一个MySQL变量(用 set @name 定义变量),其实就是通过存储过程对变量进行了操作:

delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    set temp1 = 1;
    set i3 = i3 + 100;
    set r1 = i1 + i2 + temp1 + temp2;
    select * from user;
end\\
delimiter ;

set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    database='homework',
    charset='utf8')
a = 3
b = 9
cursor = conn.cursor()
cursor.callproc('p1', (1, 2, a, b))
conn.commit()
res = cursor.fetchall()
print(res,a,b)
cursor.close()
conn.close()

如果不仅需要拿结果集,还需要拿到SQL中的返回值,比如在上边的例子里我们指定了a=3和b=9,已经知道p1过程除了返回查询结果之外,还会把第三个位置参数自增100,第四个位置参数等于前两个位置参数的和加1,和传入多少没有关系,在执行之后,其实a和b的值应该被修改成103和4.要如何获得103和4,需要修改python的代码,用一段固定写法获得执行过程后的位置变量,再赋给a和b:

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    database='homework',
    charset='utf8')
a = 3
b = 9
cursor = conn.cursor()
cursor.callproc('p1', (1, 2, a, b))
res = cursor.fetchall()
cursor.execute('SELECT @_p1_2,@_p1_3')  # 固定写法,获得调用过的p1过程的返回值
res2 = cursor.fetchall()
a, b = res2[0]
conn.commit()
print('res is ', res)
print('a is {}, b is {} now'.format(a, b))

cursor.close()
conn.close()

存储过程由于执行的是SQL语句,而且可以伪造返回值,在类似python等程序来操作数据库的时候,通过返回值,可以进行很多逻辑操作,非常实用,等于将数据库的操作和外部程序操作有效结合在了一起.但是用外部程序结合存储过程的缺点是耦合程度太高,很难移植.看几个存储过程的例子:

delimiter \\
create PROCEDURE p1(OUT p_return_code tinyint)
  BEGIN 
    DECLARE exit handler for sqlexception 
      BEGIN 
        -- ERROR 
        set p_return_code = 1; 
        rollback; 
      END; 

    DECLARE exit handler for sqlwarning 
      BEGIN 
        -- WARNING 
        set p_return_code = 2; 
        rollback; 
      END; 
                         
    START TRANSACTION; 
      alter table tb1 drop column test3;
      insert into tb2(name)values('seven');
      COMMIT; 
      -- SUCCESS 
      set p_return_code = 0; 
END\\
delimiter ;

这里故意删除tb1表里一个不存在的字段test3,在mysql里设置变量并且传入,执行如下:

set @vi = 0;
call p1(@vi);
select @vi;

针对一个查询结果集,可以声明一个游标应对这个查询结果集,声明之后,就可以通过移动游标来从结果集中取得数据,游标的性能不高,但是经常可以用来实现一些循环操作.游标只能存在于存储过程或者函数中,离开存储过程或者函数,就失效了.

比如将A表有自增id和number列,要将其全部复制到B表,而且B表的number要等于自增id加上原来的number.可以通过连表然后新增来做,但也可以通过一个存储过程用游标来生成

delimiter //
CREATE PROCEDURE p3()
BEGIN 
  DECLARE a_id INT; -- 自定义变量1用来接收每次的自增id  
  DECLARE a_number INT; -- 自定义变量2用来接收每次的number
  DECLARE temp INT default 0; -- 声明用来求和的临时变量 
  DECLARE done INT DEFAULT FALSE; -- 声明一个标志,标志是否已经取到结尾
  DECLARE my_cursor CURSOR FOR select aid,anumber FROM A;  -- 这是声明游标的语句,游标指向FOR后边的查询结果
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 这是固定格式,当出现NOT FOUND 错误的时候,将done设置为TRUE
                        
  OPEN my_cursor;  -- 需要先打开游标
    loop_name: LOOP  -- loop_name可以自定义,后边LOOP关键字表示循环
      FETCH my_cursor INTO a_id,a_number;  -- FETCH用来从游标取一行结果,每取一次,游标下移一行,取到的行数据需要拆解给同样数量的变量.
      IF done THEN 
        LEAVE loop_name;
      END IF;
      SET temp = a_id + a_number;
      INSERT INTO B(bnumber) values(temp);
    END LOOP loop_name;
  CLOSE my_cursor;  -- 运行之后关闭游标,如果多次操作,可以反复打开和关闭,每次关闭之后再打开,游标恢复初始位置.
END  //
delimiter ;

这是防止SQL注入的方法,PyMySQL里边采取避免自己格式化,传参数给execute方法来避免注入.如果不在程序内使用,采用存储过程也可以动态执行,原理和PyMySQL一样,先格式化标准语句,再将占位符传进去,不能自己去直接写.

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