mysql数据库(day5)-视图，触发器，存储过程
视图，触发器
1. 视图(以后开发中不常用，不推荐使用) 100个SQL： 88: v1 select .. from v1 select asd from v1 某个查询语句设置别名，日后方便使用 物理表数据变化视图跟着变，但是视图不支持insert - 创建 create view 视图名称 as SQL PS: 虚拟 - 修改 alter view 视图名称 as SQL - 删除 drop view 视图名称; 2. 触发器(不推荐使用)类似装饰器，在语句之前之后做点什么 当对某张表做：增删改操作时，可以使用触发器自定义关联行为 insert into tb (....) -- delimiter // -- create trigger t1 BEFORE INSERT on student for EACH ROW -- BEGIN -- INSERT into teacher(tname) values(NEW.sname); -- INSERT into teacher(tname) values(NEW.sname); -- INSERT into teacher(tname) values(NEW.sname); -- INSERT into teacher(tname) values(NEW.sname); -- END // -- delimiter ; -- -- insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根'); -- NEW,代指新数据 -- OLD,代指老数据
参考博客：
http://www.cnblogs.com/wupeiqi/articles/5713323.html
函数：
3. 函数 函数是保存在数据库中的，在python中是看不到 def f1(a1,a2): return a1 + a2 f1() bin() 内置函数： 执行函数 select CURDATE(); blog id title ctime 1 asdf 2019-11 2 asdf 2019-11 3 asdf 2019-10 4 asdf 2019-10 select ctime,count(1) from blog group ctime select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m") 2019-11 2 2019-10 2 自定义函数(有返回值)： delimiter \\ --更改终止符 create function f1( i1 int, --定义的是整型，传进去只能是整型 i2 int) returns int BEGIN --函数体，里面不能写select * from tb，直接就报错了，存储过程可以写 declare num int default 0; set num = i1 + i2; return(num); END \\ delimiter ; SELECT f1(1,100); 数据库函数的调用，函数是有返回值的
存储过程
概念是什么：保存在MySQL上的一个别名 => 一坨SQL语句
跟视图特别像，视图当作一张表来调用，存储过程里面直接写名字，视图不能进行插入进行更改，
视图的结果集可以是多张表，存储过程相当于好多的语句的合集，存储过程想干什么干什么，不受限制
好的作用：
1：用于替代程序员写SQL语句(有了存储过程，你就可以不用写了)
2：网络传输的时个用存储过程省流量
4. 存储过程(用的不多，但是你要知道从5.5才有存储过程) 概念是什么：保存在MySQL上的一个别名 => 一坨SQL语句 跟视图特别像，视图当作一张表来调用，存储过程里面直接写名字，视图不能进行插入进行更改， 视图的结果集可以是多张表，存储过程相当于好多的语句的合集，存储过程想干什么干什么，不受限制 好的作用： 1：用于替代程序员写SQL语句(有了存储过程，你就可以不用写了) 2：网络传输的时个用存储过程省流量 方式一： MySQL: 存储过程 程序：调用存储过程 方式二： MySQL：。。 程序：SQL语句 方式三： MySQL：。。 程序：类和对象（SQL语句） 1. 创建一个简单的存储过程 delimiter // create procedure p1() BEGIN select * from student; INSERT into teacher(tname) values("ct"); END delimiter ; call p1() 执行存储过程 cursor.callproc('p1') 调用存储过程 2. 传参数（in,out,inout）有三个关键字，in 只能往里面传不能返; out，从外面传不进去只能返，inout，能传能拿出来 delimiter // create procedure p2( in n1 int, #in 在存储过程里面用 in n2 int ) BEGIN select * from student where sid > n1; END // delimiter ; call p2(12,2) 你写了几个参数，调用的时候必须写参数，而且即使用不到你也要写上 cursor.callproc('p2',(12,2)) 3. 参数 out(存储过程没有return) delimiter // create procedure p3( in n1 int, inout n2 int # ) BEGIN set n2 = 123123; select * from student where sid > n1; END // delimiter ; set @v1 = 10; call p2(12,@v1) select @v1; set @_p3_0 = 12 ser @_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) =======> 特殊 a. 可传参： in out inout b. pymysql cursor.callproc('p3',(12,2)) r1 = cursor.fetchall() print(r1) cursor.execute('select @_p3_0,@_p3_1') ----想拿返回值必须再执行一下 r2 = cursor.fetchall() print(r2) 为什么有结果集又有out伪造的返回值？ delimiter // create procedure p3( in n1 int, out n2 int 用于标识存储过程的执行结果 比如1,2，代表不同的结果，如1是成功，2代表执行错了 ) BEGIN insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) END // delimiter ;
pymysql引用存储过程的代码
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 @_p5_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 p4( out status int ) BEGIN 1. 声明如果出现异常则执行{ set status = 1; rollback; } 开始事务 -- 由秦兵账户减去100 -- 方少伟账户加90 -- 张根账户加10 commit; 结束 set status = 2; END // delimiter ; =============================== delimiter \\ create PROCEDURE p5( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 2; END\\ delimiter ; 5. 游标(能不用游标就不用游标，性能不高) delimiter // create procedure p6() begin declare row_id int; -- 自定义变量1 declare row_num int; -- 自定义变量2 declare done INT DEFAULT FALSE; declare temp int; declare my_cursor CURSOR FOR select id,num from A; declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor; xxoo: LOOP fetch my_cursor into row_id,row_num; if done then leave xxoo; END IF; set temp = row_id + row_num; insert into B(number) values(temp); end loop xxoo; close my_cursor; end // delimter ;
动态sql执行(放sql注入在数据库级别)
目前有两种方法防止sql注入(pymsql,数据库)
动态执行SQL（防SQL注入） 伪代码 delimiter // create procedure p7( in tpl varchar(255), in arg int ) begin 1. 预检测某个东西 SQL语句合法性 2. SQL =格式化 tpl + arg 3. 执行SQL语句 set @xo = arg; 接下来执行的变量必须是session级别的变量，所以就有了这个设置 PREPARE xxx FROM 'select * from student where sid > ?'; 这个?是占位符 EXECUTE xxx USING @xo; DEALLOCATE prepare prod; end // delimter ; call p7("select * from tb where id > ?",9) ===> 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 ;
对性能要求不高可以用函数，索引能加速查找，但是有函数就索引无效了。
