8.2数据库(4)
2018-8-2 17:09:08
又继续看mysql 下面有好多内容都是熟悉的!知道有这个事情就好啦!
上天要眷顾我,我会努力的!
pymysql内部编写了代码
存储过程
对于每一行数据分门别类计算的时候再用游标,
游标 其实就循环
回顾： 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 from tb1 union select num,sname from tb2 # 不去重 select sid,sname from student UNION ALL select sid,sname from student 上节练习： 基于用户权限管理 参考表结构： 用户信息 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. 需求分析 # 最重要的还是sql语句,下面这些东西用到的时候可以翻博客 # 主要是下面东西还都不带常用,了解就行!!熟悉就好! 今日内容： 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 ; 数据库相关操作： 1. SQL语句 ***** - select xx() from xx ; 2. 利用MySQL内部提供的功能 作业： 一、SQL题 ***** 二、数据库 表： id name email gender alex1 三百万行 预习： 5. 索引 6. ORM操作 SQLAlchmy - 类对象对数据进行操作
