视图
视图是虚拟的表,用户只有在使用视图时,才会根据sql语句去获取数据集。
视图可以直接当做数据表来使用,但不是真正意义的表。
视图只能在查询时用,无法对真实的表进行增删改操作。
1.创建视图
格式:
CREATE VIEW 视图名称 AS SQL语句
2.修改视图
格式:
ALTER VIEW 视图名称 AS SQL语句
3.删除视图
格式:
DROP VIEW 视图名称
4.使用视图
SELECT * FROM 视图名称
存储过程
存储过程相当于程序语言中的函数,可以传入参数,也可以有返回值。当用户主动调用存储过程时,存储过程中定义的sql语句将按照逻辑执行。
1.创建存储过程
存储过程可接收的参数有三类:
in:用于传入参数
out:用于返回值
inout:既可以传入参数又可以返回值
无参数存储过程:
1 -- 创建存储过程 2 3 delimiter // # delimiter用于修改SQL语句默认终止符 4 create procedure p1() 5 BEGIN 6 select * from t1; 7 END// 8 delimiter ; # 将SQL语句终止符改回默认 9 10 11 12 -- 执行存储过程 13 14 call p1() # 调用存储过程
带参数存储过程:
1 -- 创建存储过程 2 delimiter \ 3 create procedure p1( 4 in i1 int, # i1,i2用于接收传入的参数 5 in i2 int, 6 inout i3 int, 7 out r1 int # r1传入任何值,默认都为null 8 ) 9 BEGIN 10 DECLARE temp1 int; # declare声明变量类型 11 DECLARE temp2 int default 0; # 声明变量类型,并赋予默认值 12 13 set temp1 = 1; 14 15 set r1 = i1 + i2 + temp1 + temp2; 16 17 set i3 = i3 + 100; 18 19 end\ 20 delimiter ; 21 22 -- 执行存储过程 23 set @t1 =4; 24 set @t2 = 0; 25 CALL p1 (1, 2 ,@t1, @t2); 26 SELECT @t1,@t2;
2.删除存储过程
DROP PROCEDURE 存储过程名称
3.执行存储过程
1 -- 无参数 2 call proc_name() 3 4 -- 有参数,全in 5 call proc_name(1,2) 6 7 -- 有参数,有in,out,inout 8 set @t1=0; 9 set @t2=3; 10 call proc_name(1,2,@t1,@t2)
4.pymysql执行存储过程
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4)) ret=cursor.fetchall() # 获取数据集 print(ret) # 获取执行完存储的参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() # 获取执行完存储过程的参数的值 conn.commit() cursor.close() conn.close() print(result)
触发器
对某个表进行 增删改 操作前/操作后,如果希望出发其它行为,可以使用触发器来完成。
1.创建触发器
格式:
插入前触发与插入后触发:
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
删除和修改时触发同上,如:delete on.. , update on..
例如:
NEW表示即将插入的数据行,OLD表示即将删除的数据行。
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 ;
2.删除触发器
DROP 触发器名称;
3.使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。当删除整个表时,每删除一条数据,则触发一次。
insert into tb1(num) values(666)