周四,晴,记录生活分享点滴
参考博客:https://www.cnblogs.com/wupeiqi/articles/5713323.html
视图
视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】获取结果集,并可以将其当作表来使用。
临时表搜索
select * from ( select nid, name from tb1 where nid > 2 ) as a where a. name > 'alex';
/* 格式:create view 视图名称 as sql语句 */ create view v1 as selet nid, name from a where nid > 4
/* 格式:drop view 视图名称 */ drop view v1
/* 格式:alter view 视图名称 as sql语句 */ alter view v1 as selet a.nid, b. name from a left join b on a.id = b.nid left join c on a.id = c.nid where a.id > 2 and c.nid < 5
/* 使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。 */ select * from v1
小结:
- 视图是临时表
- 只能查看,不可其他操作
- 执行时才能获取数据
存储过程
存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。
创建存储过程
/* 创建存储过程 */ delimiter $$ create procedure p1() BEGIN select * from class; /* begin...end 中间填写需要执行的语句 */ END $$ /* $$...$$ 代替 ; 作为执行的符号 */ delimiter ;
mysql执行存储过程
call p1()
pymysql执行存储过程
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.callproc('p1') # 相当于pymysql正常执行select语句:cursor.execute('select...') result = cursor.fetchall() cursor.close() conn.close()
对于存储过程,可以接收参数,其参数有三类:
-
in 仅用于传入参数用
-
out 仅用于返回值用
-
inout 既可以传入又可以当作返回值
/* ---------------------- 创建存储过程 ---------------------- */ delimiter \ create procedure p1( in i1 int, /* 让内部使用 */ in i2 int, inout i3 int, out r1 int ) BEGIN /* DECLARE声明变量:在存储过程内部声明变量时,必须使用 */ DECLARE temp1 int; /* 创建数字类型的变量temp1 */ DECLARE temp2 int default 0; /* 相当于temp2 = 0 */ /* 变量赋值 */ set temp1 = 1; /*相当于python中temp1 = 1 */ set r1 = i1 + i2 + temp1 + temp2; set i3 = i3 + 100; end\ delimiter ; /* ---------------------- 执行存储过程 ---------------------- */ set @t1 =4; /* @t1表示引用 */ set @t2 = 0; CALL p1 (1, 2, @t1, @t2); SELECT @t1,@t2; /* 相当于print */ /* -------- 执行存储过程可以获取两类数据:普通值、结果集 -------- */
1.结果集
delimiter // create procedure p1() begin select * from v1; end // delimiter ;
2.结果集+out值
delimiter // create procedure p2( in n1 int, inout n3 int, out n2 int, ) begin declare temp1 int ; declare temp2 int default 0; select * from v1; set n2 = n1 + 100; set n3 = n3 + n1 + 100; end // delimiter ;
3.事务(与python异常处理相似)
delimiter \ create PROCEDURE p1( OUT p_return_code tinyint ) /* 如果存储过程出现异常,执行代码块一 */ BEGIN DECLARE exit handler for sqlexception /* 声明一个特殊的变量sqlexceptiono,表示执行了sql的异常处理 */ BEGIN -- ERROR set p_return_code = 1; rollback; /* 回滚,set p_return_code = 0;不再执行 */ END; /* 如果存储过程出现警告,执行代码块二 */ DECLARE exit handler for sqlwarning /* 警告处理 */ BEGIN -- WARNING set p_return_code = 2; rollback; END; /* 如果执行成功,上面的两个代码块不用执行 */ START TRANSACTION; /* 开始一个事务 */ DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; /* START到COMMIT过程中的操作为一个事务操作,如果中间有一个出错,就回滚到原来的状态 */ -- SUCCESS set p_return_code = 0; END\ delimiter ;
4.游标
delimiter // create procedure p3() begin declare ssid int; -- 自定义变量1 declare ssname varchar(50); -- 自定义变量2 DECLARE done INT DEFAULT FALSE; DECLARE my_cursor CURSOR FOR select sid,sname from student; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor; xxoo: LOOP fetch my_cursor into ssid,ssname; if done then leave xxoo; END IF; insert into teacher(tname) values(ssname); end loop xxoo; close my_cursor; end // delimter ;
5.动态执行SQL
delimiter CREATE PROCEDURE p4 ( in nid int ) BEGIN PREPARE prod FROM 'select * from student where sid > ?'; EXECUTE prod USING @nid; DEALLOCATE prepare prod; END\ delimiter ;
drop procedure proc_name;
执行存储过程
/* 无参数 */ call proc_name() /* 有参数,全in */ call proc_name(1,2) /* 有参数,有in,out,inout */ set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2)
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='sqlexample', charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程,获取存储过程的结果集,将返回值设置给了 @_存储过程名_序号 = r1 = cursor.callproc('p1', args=(1, 22, 3, 4)) # 如果是 in、inout传进去,out忽略 # 打印此条结果为 (1,22,3,4) # set @_p1_0 = 1 # set @_p1_1 = 22 # set @_p1_2 = 3 # set @_p1_3 = 4 # call p1(1,22,3,4) print(r1) result1 = cursor.fetchall() print(result1) # 获取执行完存储的参数 r2 = cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") # @_p1_0固定用法:p1指p1,0指args的1(第一个数) # 打印此条结果为 1 ,没有拿到上面的返回值,需要fetchall print(r2) result2 = cursor.fetchall() print(result2) conn.commit() cursor.close() conn.close()
小结
- 可写复杂逻辑
- 参数:in、out、inout
- 结果集:select...
- 视图和存储过程都是在mysql服务端写的
其他
条件语句
delimiter \ CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN /* 加if...else语句时,if后必须加then,相当于python中的冒号: 开头是if,结尾时end if */ SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END\ delimiter ;
循环语句
delimiter \ CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO /* DO相当于冒号 */ SELECT num ; SET num = num + 1 ; /* 赋值 */ END WHILE ; END\ delimiter ;
repeat循环
delimiter \ CREATE PROCEDURE proc_repeat () BEGIN DECLARE i INT ; SET i = 0 ; repeat /* 重复 */ select i; set i = i + 1; until i >= 5 end repeat; END\ delimiter ;
loop
delimiter \ CREATE PROCEDURE proc_loop () BEGIN declare i int default 0; loop_label: loop select i; set i=i+1; if i>=5 then leave loop_label; end if; end loop; END\ delimiter;
delimiter \ DROP PROCEDURE IF EXISTS proc_sql \ CREATE PROCEDURE proc_sql () BEGIN declare p1 int; set p1 = 11; set @p1 = p1; /* EXECUTE规定的@p1 */ PREPARE prod FROM 'select * from tb2 where nid > ?'; /* ? 为占位符,同% 、 prod代指字符串'select * from tb2 where nid > ?' */ EXECUTE prod USING @p1; /* 字符串格式化 */ DEALLOCATE prepare prod; END\ delimiter ; /* 防止SQL注入 第一种 cursor.callproc('p12','select * from tb where nid>?', 13) 第二种 cursor.excute('select * from tb where nid>%s', 13) */
小结
- sql是字符串
- 借助特殊语法并可以进行字符串格式化,用?作为占位符,execute proc using @p1
触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。
创建基本语法
/* 插入前 */ CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END /* 插入后 */ CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END /* 删除前 */ CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END /* 删除后 */ CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END /* 更新前 */ CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END /* 更新后 */ CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
插入前触发器
delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. NAME == 'alex' THEN /* NEW封装了用户传递过来的数据 */ INSERT INTO tb2 (NAME) VALUES ('aa') END IF; END// delimiter ;
插入后触发器
delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. num = 666 THEN INSERT INTO tb2 (NAME) VALUES ('666'), ('666') ; ELSEIF NEW. num = 555 THEN INSERT INTO tb2 (NAME) VALUES ('555'), ('555') ; END IF; END// delimiter ;
要点:
-
NEW表示即将插入的数据行
-
OLD表示即将删除的数据行
删除触发器
DROP TRIGGER tri_after_insert_tb1;
使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。
insert into tb1(num) values(666)
小结:
- 为某一个表 inser前后;update前后;delete前后
- OLD、NEW
- 删除多行,插入,更新 for 多行:OLD,NEW(删除之前将删除的值添加到另外一张表,如果是一个表,将表内的每一行数据全部执行一遍)