【Mysql】Mysql视图、触发器、存储过程、游标

一、视图

定义

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。

基表：用来创建视图的表叫做基表；

通过视图，可以展现基表的部分数据；

视图数据来自定义视图的查询中使用的表，使用视图动态生成；

优点

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已
经是过滤好的复合条件的结果集。

安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某
个列，但是通过视图就可以简单的实现。

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影
响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

语法

CREATE VIEW <视图名> AS 
1

准备

CREATE TABLE `user` ( 
	`id` INT auto_increment COMMENT '编号', 
	`name` VARCHAR (32) COMMENT '学生姓名', 
	`sex` TINYINT DEFAULT 1 COMMENT '性别(1:男;0:女)', 
	`age` TINYINT DEFAULT 18 COMMENT '年龄', 
	PRIMARY KEY (`id`) 
) COMMENT = '学生表' ENGINE = INNODB; 

CREATE TABLE `goods` ( 
	`id` INT auto_increment COMMENT '编号', 
	`name` VARCHAR (32) COMMENT '商品名称', 
	`price` DECIMAL (10, 6) DEFAULT 0 COMMENT '价格', 
	PRIMARY KEY (`id`)
 ) COMMENT = '商品表' ENGINE = INNODB; 

CREATE TABLE `user_goods` ( 
	`id` INT auto_increment COMMENT '编号', 
	`user_id` INT COMMENT '用户ID', 
	`goods_id` INT COMMENT '商品ID', 
	PRIMARY KEY (`id`)
 ) COMMENT = '用户商品表' ENGINE = INNODB; 

-- 创建视图 
CREATE VIEW view_test1 AS SELECT
	 `user`.id AS user_id, 
	 `user`.`name` AS user_name,
	 `user`.`sex` AS user_sex,
	 `user`.`age` AS user_age, 
	 `goods`.id AS goods_id, 
	 `goods`.`name` AS goods_name 
 FROM
 	`user`
 JOIN `user_goods` ON `user`.id = `user_goods`.user_id 
 JOIN `goods` ON `goods`.id = `user_goods`.goods_id;
 
-- 调用 
SELECT * FROM view_test1; 
-- 删除视图 
DROP VIEW view_test1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

作用

• 可复用，减少重复语句书写；类似程序中函数的作用；
• 重构利器
  假如因为某种需求，需要将user拆房表usera和表userb；如果应用程序使用sql语句：
  select * from user 那就会提示该表不存在；若此时创建视图 create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name; ，则只需要更改数据库结构，而不需要更改应用程序；
  视图在oracle 物化视图 mysql select * from
• 逻辑更清晰，屏蔽查询细节，关注数据返回；
• 权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作；

二、触发器

触发器是否具备事务性？否。

定义

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表
事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比
如当对一个表进行DML操作（ insert ， delete ， update ）时就会激活它执行。

4要素

• 监视对象： table
• 监视事件： insert 、 update 、 delete
• 触发时间： before ， after
• 触发事件： insert 、 update 、 delete

语法

CREATE TRIGGER trigger_name 
trigger_time trigger_event 
ON tbl_name FOR EACH ROW 
	[trigger_order] 
trigger_body -- 此处写执行语句 

-- trigger_body: 可以一个语句，也可以是多个语句；多个语句写在 BEGIN ... END 间 
-- trigger_time: { BEFORE | AFTER } 
-- trigger_event: { INSERT | UPDATE | DELETE } 
-- trigger_order: { FOLLOWS | PRECEDES } other_trigger_name 12345678910
1
2
3
4
5
6
7
8
9
10

准备

CREATE TABLE `work` ( 
	`id` INT PRIMARY KEY auto_increment, 
	`address` VARCHAR (32)
 ) DEFAULT charset = utf8 ENGINE = INNODB; 

CREATE TABLE `time` ( 
	`id` INT PRIMARY KEY auto_increment, 
	`time` DATETIME 
) DEFAULT charset = utf8 ENGINE = INNODB; 

CREATE TRIGGER trig_test1 AFTER INSERT 
ON `work` FOR EACH ROW 
INSERT INTO `time` VALUES(NULL,NOW()); 123456789
1
2
3
4
5
6
7
8
9
10
11
12
13

NEW 和 OLD

在 INSERT 型触发器中， NEW 用来表示将要（ BEFORE ）或已经（ AFTER ）插入的新数据；

在 DELETE 型触发器中， OLD 用来表示将要或已经被删除的原数据；

在 UPDATE 型触发器中， OLD 用来表示将要或已经被修改的原数据， NEW 用来表示将要或已经修
改为的新数据；

NEW.columnName （columnName为相应数据表某一列名） 
OLD.columnName （columnName为相应数据表某一列名）
1
2

案例

在下订单的时候，对应的商品的库存量要相应的减少，即买几个商品就减少多少个库存量。

准备

CREATE TABLE `goods` ( 
	`id` INT PRIMARY KEY auto_increment, 
	`name` VARCHAR (32), 
	`num` SMALLINT DEFAULT 0 
);

CREATE TABLE `order` ( 
	`id` INT PRIMARY KEY auto_increment, 
	`goods_id` INT, 
	`quantity` SMALLINT COMMENT '下单数量' 
);

INSERT INTO goods VALUES (NULL, 'C++', 40); 
INSERT INTO goods VALUES (NULL, 'C', 63); 
INSERT INTO goodS VALUES (NULL, 'mysql', 87); 
INSERT INTO `order` VALUES (NULL, 1, 3); 
INSERT INTO `order` VALUES (NULL, 2, 4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

需求1

客户修改订单购买的数量，在原来购买数量的基础上减少2个；

-- delimiter 
-- delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号 ;。如果一次输入的语句较多， 并且语句中间有分号，这时需要重新指定一个特殊的分隔符。通常指定 $$ 或 || 
delimiter // 
CREATE TRIGGER trig_order_1 AFTER INSERT 
ON `order` FOR EACH ROW 
BEGIN
	UPDATE goods SET num = num - 2 WHERE id = 1; 
END
// 
delimiter ; 
INSERT
1
2
3
4
5
6
7
8
9
10
11

需求2

客户修改订单购买的数量，商品表的库存数量自动改变；

delimiter // 
CREATE TRIGGER trig_order_2 BEFORE UPDATE 
ON `order` FOR EACH ROW 
BEGIN
	UPDATE goods SET num=num+old.quantity-new.quantity WHERE id = new.goods_id; 
END 
//
delimiter ; 

-- 测试 
UPDATE `order` SET quantity = quantity+2 WHERE id = 1;
1
2
3
4
5
6
7
8
9
10
11

三、存储过程

定义

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL
语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带
有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不
同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中
的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

特点

• 能完成较复杂的判断和运算 有限的编程
• 可编程行强，灵活
• SQL编程的代码可重复使用
• 执行的速度相对快一些
• 减少网络之间的数据传输，节省开销

语法

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数 据类型…]]) [特性 ...] 过程体
1

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。

IN：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设
置默认值

OUT：该值可在存储过程内部被改变，并可返回

INOUT：调用时指定，并且可被改变和返回过程体的开始与结束使用 BEGIN 与 END 进行标识。

案例

DELIMITER // 
	CREATE PROCEDURE proc_test1()
BEGIN 
	SELECT current_time(); 
	SELECT current_date(); 
END 
//
DELIMITER ; 
call proc_test1();
1
2
3
4
5
6
7
8
9

IN

DELIMITER // 
CREATE PROCEDURE proc_in_param (IN p_in INT) 
BEGIN
	SELECT
		p_in ; 
	SET 
		p_in = 2 ; 
	SELECT 
		p_in ; 
	END ;// 
DELIMITER ; 

-- 调用 
SET @p_in = 1; 

CALL proc_in_param (@p_in); 

-- p_in虽然在存储过程中被修改，但并不影响@p_id的值 
SELECT @p_in;=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

OUT

DELIMITER // 
	CREATE PROCEDURE proc_out_param(OUT p_out int) 
		BEGIN 
			SELECT p_out; 
			SET p_out=2; 
			SELECT p_out; 
		END; 
//
DELIMITER ; 

-- 调用 
SET @p_out=1; 
CALL proc_out_param(@p_out); 
SELECT @p_out; -- 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14

INOUT

DELIMITER // 
	CREATE PROCEDURE proc_inout_param(INOUT p_inout int) 
		BEGIN 
			SELECT p_inout; 
			SET p_inout=2; 
			SELECT p_inout; 
		END; 
//
DELIMITER ; 

#调用 
SET @p_inout=1; 
CALL proc_inout_param(@p_inout) ; 
SELECT @p_inout; -- 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14

四、游标

游标是针对行操作的，对从数据库中 select 查询得到的结果集的每一行可以进行分开的独立的相
同或者不相同的操作。

对于取出多行数据集，需要针对每行操作；可以使用游标；游标常用于存储过程、函数、触发器、
事件；

游标相当于迭代器

定义游标

DECLARE cursor_name CURSOR FOR select_statement;
1

打开游标

OPEN cursor_name;
1

取游标数据

FETCH cursor_name INTO var_name[,var_name,......]
1

关闭游标

CLOSE curso_name;
1

释放

DEALLOCATE cursor_name;
1

设置游标结束标志

DECLARE done INT DEFAULT 0; 
DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET done = 1; -- done 为标记为
1
2
3

案例

CREATE PROCEDURE proc_while ( 
	IN age_in INT, 
	OUT total_out INT 
)
BEGIN 
-- 创建 用于接收游标值的变量 
DECLARE p_id,p_age,p_total INT ; 
DECLARE p_sex TINYINT ; 
-- 注意:接收游标值为中文时,需要给变量 指定字符集utf8 
DECLARE p_name VARCHAR (32) CHARACTER SET utf8 ; -- 游标结束的标志 
DECLARE done INT DEFAULT 0 ; -- 声明游标 
DECLARE cur_teacher CURSOR FOR SELECT 
	teacher_id, 
	teacher_name, 
	teacher_sex, 
	teacher_age 
FROM
	teacher 
WHERE
	teacher_age > age_in ; -- 指定游标循环结束时的返回值 
DECLARE CONTINUE HANDLER FOR NOT found 
SET done = 1 ; 
-- 打开游标 
OPEN cur_teacher ; 
-- 初始化 变量 
SET p_total = 0 ; 
-- while 循环 
WHILE done != 1 DO 
	FETCH cur_teacher INTO p_id, 
	p_name, 
	p_sex, 
	p_age ; 
IF done != 1 THEN 
SET p_total = p_total + 1 ; 
END IF ; 
END WHILE ; 
-- 关闭游标 
CLOSE cur_teacher ; 
-- 将累计的结果复制给输出参数 
SET total_out = p_total ; 
END
// 
delimiter ; 

-- 调用 
SET @p_age =20; 
CALL proc_while(@p_age, @total); 
SELECT @total;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48