navicat for mysql触发器_mysql管理工具navicatformysql视图、触发器、函数、存储过程、游标、动态执行SQL（防SQL注入）...

Day61

回顾：

SQL语句：

数据行：

临时表：(select * from tb where id>10)

指定映射：select id,name,1,sum(x)/count()

条件：case when id>8 then xx else xx end三元运算：if(isnull(xx),0,1)

补充：

左右连表：join上下连表：union# 自动去重select id,name fromtb1union

select num,sname fromtb2

# 不去重select sid,sname fromstudentUNION ALL

select sid,sname fromstudent

上节练习：

基于用户权限管理

参考表结构：

用户信息

id username pwd1 alex 123123权限1订单管理2用户劵3Bug管理

....

用户类型&权限1 1

1 2

2 1

3 1程序：

用户登录

基于角色的权限管理

用户信息

id username pwd role_id1 alex 123123 1

2 eric 123123 1权限1订单管理2用户劵3Bug管理

....

角色表：1IT部门员工2咨询员工3IT主管

角色权限管理1 1

1 2

3 1

3 2

3 3

===>

1. 基于角色的权限管理2. 需求分析

今日内容：1. 视图

100个SQL：88: v1select .. fromv1select asd fromv1

某个查询语句设置别名，日后方便使用-创建create view 视图名称 asSQL

PS: 虚拟-修改alter view 视图名称 asSQL-删除drop view视图名称;2. 触发器

当对某张表做：增删改操作时，可以使用触发器自定义关联行为insert intotb (....)--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,代指老数据

3. 函数

def f1(a1,a2):return a1 +a2

f1()

bin()

内置函数：

执行函数selectCURDATE();

blog

id title ctime1 asdf 2019-11

2 asdf 2019-11

3 asdf 2019-10

4 asdf 2019-10

select ctime,count(1) from blog groupctimeselect DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")2019-11 2

2019-10 2自定义函数(有返回值)：

delimiter \\create functionf1(

i1int,

i2int)returns int

BEGIN

declare num int default 0;set num = i1 +i2;return(num);END\\

delimiter ;SELECT f1(1,100);4. 存储过程

保存在MySQL上的一个别名=>一坨SQL语句

别名()

用于替代程序员写SQL语句

方式一：

MySQL: 存储过程

程序：调用存储过程

方式二：

MySQL：。。

程序：SQL语句

方式三：

MySQL：。。

程序：类和对象(SQL语句)1. 简单create procedurep1()BEGIN

select * fromstudent;INSERT into teacher(tname) values("ct");ENDcall p1()cursor.callproc('p1')2. 传参数(in,out,inout)

delimiter//

create procedurep2(in n1 int,in n2 int)BEGIN

select * from student where sid >n1;END //delimiter ;

call p2(12,2)cursor.callproc('p2',(12,2))3. 参数 out

delimiter//

create procedurep3(in n1 int,

inout n2int)BEGIN

set n2 = 123123;select * from student where sid >n1;END //delimiter ;set @v1 = 10;

call p2(12,@v1)select @v1;set @_p3_0 = 12ser@_p3_1 = 2call 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. 可传参：inout inout

b. pymysqlcursor.callproc('p3',(12,2))

r1= cursor.fetchall()print(r1)cursor.execute('select @_p3_0,@_p3_1')

r2= cursor.fetchall()print(r2)

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

delimiter//

create procedurep3(in n1 int,

out n2int 用于标识存储过程的执行结果 1,2)BEGIN

insert intovv(..)insert intovv(..)insert intovv(..)insert intovv(..)insert intovv(..)insert intovv(..)END //delimiter ;4. 事务

delimiter//

create procedurep4(

out statusint)BEGIN

1. 声明如果出现异常则执行{set status = 1;rollback;

}

开始事务--由秦兵账户减去100

--方少伟账户加90

--张根账户加10

commit;

结束set status = 2;END //delimiter ;===============================delimiter \\create PROCEDUREp5(

OUT p_return_codetinyint)BEGIN

DECLARE exit handler forsqlexceptionBEGIN

--ERROR

set p_return_code = 1;rollback;END;

STARTTRANSACTION;DELETE fromtb1;insert into tb2(name)values('seven');COMMIT;--SUCCESS

set p_return_code = 2;END\\

delimiter ;5. 游标

delimiter//

create procedurep6()begin

declare row_id int; --自定义变量1

declare row_num int; --自定义变量2

declare done INT DEFAULTFALSE;declare temp int;declare my_cursor CURSOR FOR select id,num fromA;declare CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;openmy_cursor;

xxoo: LOOPfetch my_cursor intorow_id,row_num;if done thenleave xxoo;END IF;set temp = row_id +row_num;insert into B(number) values(temp);endloop xxoo;closemy_cursor;end //delimter ;6. 动态执行SQL(防SQL注入)

delimiter//

create procedurep7(in tpl varchar(255),in arg int)begin

1. 预检测某个东西 SQL语句合法性2. SQL =格式化 tpl +arg3. 执行SQL语句set @xo =arg;PREPARE xxx FROM 'select * from student where sid > ?';EXECUTE xxx USING @xo;DEALLOCATE prepareprod;end //delimter ;

call p7("select * from tb where id > ?",9)===>delimiter \\CREATE PROCEDUREp8 (in nid int)BEGIN

set @nid =nid;PREPARE prod FROM 'select * from student where sid > ?';EXECUTE prod USING @nid;DEALLOCATE prepareprod;END\\

delimiter ;

数据库相关操作：1. SQL语句 *****

- select xx() fromxx ;2. 利用MySQL内部提供的功能

作业：

一、SQL题*****二、数据库

表： id name email gender

alex1

三百万行

预习：5. 索引6. ORM操作 SQLAlchmy- 类对象对数据进行操作