mysql学习第二季-526互联

回顾：SQL语句：数据行：临时表 (select * from tb where id>0）指定映射：条件 case when id >8 then ** else ** end 三元运算：if（isnull(**),0,1）左右连表 join 上下连表 union （自动去重） union all (不去重)

用户权限设计方案

　　基于用户的权限管理

　　　　　　用户信息
　　　　　　ID 　　uesrname　　pwd
　　　　　　1 　　　　alxe　　　123
　　　　　　权限
　　　　　　1 订单管理
　　　　　　2用户劵
　　　　　　3BUG管理
　　　　　　用户与权限
　　　　　　1　　1
　　　　　　1　　2

　　基于角色的权限管理　　　　　

　　　　　　用户信息
　　　　　　ID 　　uesrname　　pwd role_id(角色)
　　1 　　　　alxe　　　123　　 1
　　　　　　权限
　　　　　　1 订单管理
　　　　　　2用户劵
　　　　　　3BUG管理
　　　　　　角色表
　　　　　　1 　　IT部门
　　　　　　2　　咨询员工
　　　　　　3　　IT主管
　　　　　　角色权限管理
　　　　　　1　　　　1
　　　　　　1　　　　2
　　　　　　2 1

基于角色权限系统扩展性更好

需求分析，架构梳理

今日内容：

视图（虚拟表，根据原表内容变化）不能对视图中内容进行插入及修改

创建 create view v1(视图名) as SQL语句
修改 alter view v1(视图名) as SQL语句
删除 drop view v1(视图名称)

触发器（对一个表操作，自动对另一个表也操作）数据库级别操作，数据库中自动执行，不常用

　插入前 creater TRIGGER 触发器名称 before insert on tb1(表名) for EACH ROW(每一条) BEGIN 触发器的内容(SQL语句) end

delimiter //   更改SQL执行结束标志为//
CREATE TRIGGER tt1 BEFORE INSERT on student for EACH row 
BEGIN
INSERT into teacher(tname) VALUES(NEW.sname);   
END //
delimiter ;  更改SQL执行结束标志为;
NEW,指新数据
OLD，指老数据

查看源码

插入后 creater TRIGGER 触发器名称 after insert on tb1(表名) for EACH ROW BEGIN 触发器的内容(SQL语句) end
删除前　 creater TRIGGER 触发器名称 before delete on tb1(表名) for EACH ROW BEGIN 触发器的内容(SQL语句) end
删除后 creater TRIGGER 触发器名称 after delete on tb1(表名) for EACH ROW BEGIN 触发器的内容(SQL语句) end
更新前 creater TRIGGER 触发器名称 before update on tb1(表名) for EACH ROW BEGIN 触发器的内容(SQL语句) end
更新后 creater TRIGGER 触发器名称 after update on tb1(表名) for EACH ROW BEGIN 触发器的内容(SQL语句) end

函数

　内置函数 select 函数名时间格式化函数 date_format()

　自定义函数函数体中不能有SQL语句 creat function 函数名（i1 int ,i2 int 参数类型） returns int begin declare num int default 0; set num=i1+i2 return(num); end

存储过程保存在MYSQL上的一个别名 =>SQL语句

　　简单

　　create PROCEDURE p1() 存储名字 BEGIN SQL语句（可以是查询、修改、插入删除） END

　　call p1() 存储过程中的SQL语句全部执行

　　方式1：MySQL：存储过程程序员调用存储过程方式二程序员写SQL语句方式三程序员直接写类和对象（SQL语句）

　　传参数（int，out,inout）

　　create PROCEDURE p2( in n1 int,in n2 int ) 存储名字 BEGIN SQL语句（可以是查询、修改、插入删除） END

　　call p2(3) 存储过程中的SQL语句全部执行

　　pycharm中调用 cursor.callproc（'p2',(3)）

　　传参数（in,out）

　　create PROCEDURE p3(in n1 int,out n2 int)存储名字 BEGIN SQL语句（可以是查询、修改、插入删除） END

　　call p3(3,变量) 存储过程中的SQL语句全部执行，执行完成后变量值会被存储过程中的函数修改并输出，外部可以获取到这个变量的改变

　　pycharm中调用 cursor.callproc（'p2',(3)）拿到结果集 cursor.execute('select @_p3_0,@_p3_1')　　拿到参数的返回值　　

特性： 1、可传参： in out inout 2、pymysql操作存储过程可以拿到结果集也可以拿到返回值（out伪造的值）返回值用于标识存储过程的执行结果

游标

声明游标
获取A表中的数据 my_cursor select id num from A

for row_id,row_num in my_cursor: 检测循环是否还有数据，如果无数据 break; insert into B(num) values(roe_id+roe_num)

CREATE PROCEDURE p6()     -----创建存储过程

DECLARE ssid INT;
declare ssnum VARCHAR(50);
declare temp int;

DECLARE done int DEFAULT FALSE;   -----声明循环标志位
DECLARE my_cursor CURSOR for SELECT sid,num from score;    ----声明游标
DECLARE CONTINUE HANDLER for not FOUND set done=TRUE;    ----数据为空，置位循环结束标志位

OPEN my_cursor;
xx:LOOP               -----循环
    FETCH my_cursor into ssid,ssnum;    ---获取游标中的一行值
    if done THEN
        leave xx;                    -----离开循环
    end if;
    set temp=ssid+ssnum
    INSERT into class(cid,caption) VALUES(ssid,temp);     ----插入新表中
end loop xx;                    ------结束循环
end

查看源码

动态执行SQL（防SQL注入）

create procedure p4( in tp1 varchar(50),in arg int ) begin 1、预检测某个东西 SQL语法合法性 2、格式化 tp1+arg 3、执行SQL语句 (

set @arg=arg; prepare xxx from ;execute xxx using @arg; deallocate prepare prod; ) end