mysql视图参数_MySQL视图，触发器，函数，存储结构

基于用户权限管理

参考表结构：

用户信息

id username pwd

1 alex 123123

权限

1 订单管理

2 用户劵

3 Bug管理

....

用户类型&权限

1 1

1 2

2 1

3 1

程序：

用户登录

基于角色的权限管理

用户信息

id username pwd role_id

1 alex 123123 1

2 eric 123123 1

权限

1 订单管理

2 用户劵

3 Bug管理

....

角色表：

1 IT部门员工

2 咨询员工

3 IT主管

角色权限管理

1 1

1 2

3 1

3 2

3 3

===>

1. 基于角色的权限管理

2. 需求分析

今日内容：

1. 视图

100个SQL：

88: v1

select .. from v1

select asd from v1

某个查询语句设置别名，日后方便使用

- 创建

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,代指老数据

3. 函数

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

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 procedure p1()

BEGIN

select * from student;

INSERT into teacher(tname) values("ct");

END

call p1()

cursor.callproc('p1')

2. 传参数(in,out,inout)

delimiter //

create procedure p2(

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 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

)

BEGIN

insert into vv(..)

insert into vv(..)

insert into vv(..)

insert into vv(..)

insert into vv(..)

insert into vv(..)

END //

delimiter ;

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 ;

6. 动态执行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;

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 ;