mysql 触发器动态sql_mysql 视图_触发器_函数_存储过程_事务_动态防SQL注入

1、视图

创建：

create view 视图名称 as  SQL语句 (如 select id,name from t1 where id > 10)；

修改：

alter view 视图名称 as SQL语句；

删除：

drop view 视图名称；

2、触发器：

当对某张表格做了感知的操纵(增删改查)时，触发定制的行为

demiliter //　　　　　　　　　　　　　　　　　　　　　　# 更改终结符为  //

create tigger t1 before insert on student for each row 　　　# 把触发器命名 t1 对 student 表做 insert 动作时触发

begin

insert  into  loggin(lname) values(new.sname)

insert  into  loggin(lname) values(new.sname)

end

NEW，是新数据，一般是insert update操作时才会产生

OLD，是旧数据，一般是delete 操作时产生

3、函数

python

def f1(a1,a2):

return a1 + a2

f1()

内置函数

执行函数  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 log group by ctime;

select DATE_FORMAT(ctime,"%Y-%m") from blog group by DATE_FORMAT(ctime,"%Y-%M")；

2019-11　　2

2019-10　　2

自定义函数(有返回值)

delimiter //

create function(

i1 int,

i2 int

)

begin

delcare num int default 0;

set num  = i1 + i2;

return(num);

end

delimiter ;

select f1(1,100);

4、存储过程

保存在MySQL上的一个别名 ==》 一些已经写好的sql语句

调用： 别名();

用于代替程序员写sql语句

方法一：

MySQL：存储过程

程序：调用存储过程

1、简单

delimiter //

create procedure p1()

begin

select * from teacher;

inser into student(sname) valuer('ct');

end //

delimiter ;

call p1();   # MySQL中调用

cursor.callproc('p1')　　　# python 调用

2、传参数(in，out，inout)

delimiter //

create procedure p2(

in n1 int,

in n2 int

)

begin

select * from teacher where tid > n1;

end //

delimiter ;

call p1(12，2);   # MySQL中调用

cursor.callproc('p2',(12,2))  # Python pymysql 中调用

3、参数 out

delimiter //

create procedure p3(

in n1 int;

out n2 int

)

begin

set n2 = 123123;

select * from student where sid > n2;

end //

delimiter ;

# mysql调用  通过@v1 变量来间接实现return 的效果

set @v1 = 12;

call p3(12,@v1)

select @v1;  # 经过call调用p3 把@V变量放进去，然后n2 其实就是@v ， set 把@v 从12 重新赋值为123123。

# pymysql 调用存储过程的时候，默认在mysql执行了下面的语句。

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.fetchal()

print(r1)

cursor.execute('select @_p3_0,@_p3_1')

r2 = cursor.fatchall()

print(r2)

有结果集为啥需要out伪造的返回值

delimiter //

create procedure p3(

从 in n1 int,

out n2 int  用于标识存储过程的执行结果 1，2 标识 1 是正常  2 是 错误 error

)

begin

insert into vv(..)

insert into vv(..)

insert into vv(..)

end //

delimiter ;

4、事务

delimiter //

create procedure p4(

out sataus int

)

begin

1、声明如果出现异常则执行{

set status = 1;

rollback;

}

开始事务

--- A账户减去100元

--- B账户增加80元

--- C账户增加20元

--- commit；

结束

setstatus = 2；

end //

delimiter ；

----------------------------------------------

#  事务示例

demiliter //

create PROCEDURE P5(

OUT p_return_code tinyint

)

befin

declare exit hadler for sqlexecption

begin

--- ERROR

set p_return_code = 1;

rollback;

end;

start transaction;

delete from tb1;

inser into tb2(name) values('veven');

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 continuehandler for not found set done = TRUE;

open my_cursor:

bbj:LOOP

fetch my_cursor inti row_id,row_num;

if done the

leave bbj;

end if

set temp = row_id + row_num

insert into B(number) values(temp)

end loop bbj;

close my_cursor;

end //

delimiter ;

6、动态执行SQL(防SQL注入)

delimiter //

create procedure p7(

in tpl varchar(255)

in arg int

)

begin

1、预检车某个东西 SQL语句合法性

2、SQL = 格式化 tp1

3、执行sql语句

set @xo = arg;

PREPARE XXX FROM 'select * from student where sid > ?';

execute XXX using @xo; # 只能用@的变量

DEALLOCATE prepare prod;

end //

delimiter ;

=====>

delimiter //

create prcodure 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 ;