视图，触发器和存储过程

视图

视图是一个虚拟表

创建视图

CREATE VIEW 视图名称 [列名,....] AS 子查询

create view emp2dep as select empl.*,depa.name as dep_name from empl inner JOIN depa on empl.dep_id=depa.id;

如果省略视图的列名，则该视图的列由子查询中的列组成

触发器

对表的增删改行为

创建触发器

create trigger 触发器名 before|after 触发事件INSERT/update/delete on 表名 FOR EACH ROW

CREATE TABLE cmd (

id INT PRIMARY KEY auto_increment,

USER CHAR (32),

priv CHAR (10),

cmd CHAR (64),

sub_time datetime, #提交时间

success enum ('yes', 'no') #0代表执行失败

);

CREATE TABLE errlog (

id INT PRIMARY KEY auto_increment,

err_id int

);

创建触发器

delimiter // #将sql语句的结束符号改为：//

CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW

BEGIN

IF NEW.success = 'no' THEN #等值判断只有一个等号

        INSERT INTO errlog(err_id) VALUES(NEW.id) ; #必须加分号

  END IF ; #必须加分号

END //

delimiter ; #将sql语句的结束符号改回来分号

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

DATE_FORMAT函数

CREATE TABLE blog (

id INT PRIMARY KEY auto_increment,

NAME CHAR (32),

sub_time datetime

);

INSERT INTO blog (NAME, sub_time)

VALUES

('第1篇','2015-03-01 11:31:21'),

('第2篇','2015-03-11 16:31:21'),

('第3篇','2016-07-01 10:21:31'),

('第4篇','2016-07-22 09:23:21'),

('第5篇','2016-07-23 10:11:11'),

('第6篇','2016-07-25 11:21:31'),

('第7篇','2017-03-01 15:33:21'),

('第8篇','2017-03-01 17:32:21'),

('第9篇','2017-03-01 18:31:21');

SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(id) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m'); #提取时间的年和月份

存储过程

创建存储过程

delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

在mysql中调用

call p1()

在python中基于pymysql调用

cursor.callproc('p1')

print(cursor.fetchall())

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
delimiter //
create procedure p2(
in n1 int,
in n2 int #n2没起作用
)
BEGIN

select * from blog where id > n1;

END //
delimiter ;

在mysql中调用
call p2(3,2);

在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

delimiter //
create procedure p3(
in n1 int,
out res int
)
BEGIN
select * from blog where id > n1;
set res = 1;
END //
delimiter ;

在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p3(3,@res);
select @res;

在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())