MySQL之视图和触发器

一、视图

1.1、定义

视图（ view ）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。
基表：用来创建视图的表叫做基表。
通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

视图主要用来做优化的。通常使用select来构建视图表，而很少使用update、delete、insert，因为限制特别多，容易出错。

一般，视图只做查询，不做更删改。

1.2、优点

1. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联关系和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

1.3、语法

CREATE VIEW <视图名> AS <SELECT语句>
1

示例：

create view vt1 as select A.student_id from
(
(select student_id,num from score where course_id=(
select `cid` from `course` where cname='linux c/c++')) as A
left join
(select student_id,num from score where course_id=(
select `cid` from `course` where cname='mysql')) as B

on A.student_id=B.student_id
) WHERE A.num>if(isnull(B.num),0,B.num);

select * from vt1;
1
2
3
4
5
6
7
8
9
10
11
12

1.3、作用

1. 权限管理。可以隐藏表结构；视图的表结构可以开放给用户，但不会开放基表的表结构。即某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作。
2. 可复用。减少重复语句书写；类似程序中函数的作用。
3. 节约资源。一些列表的关联查询构成的数据包比较大，而使用 select … from view构成的数据包就小多了。
4. 重构利器。假如因为某种需求，需要将 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; ，则只需要更改数据库结构，而不需要更改应用程序。
5. 逻辑更清晰，屏蔽查询细节，关注数据返回。

二、触发器

触发器是具备事务性的。

2.1、定义

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

2.2、四要素

（1）监视对象： table
（2）监视事件： insert 、update 、delete
（3）触发时间： before ， after
（4）触发事件： insert 、update 、delete

2.3、语法

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

trigger_body：可以一个语句，也可以是多个语句；多个语句写在 BEGIN … END 间。
trigger_time：{ BEFORE | AFTER }
trigger_event：{ INSERT | UPDATE | DELETE }
trigger_order：{ FOLLOWS | PRECEDES } other_trigger_name

示例：

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());
1
2
3
4
5
6
7
8
9
10
11
12

2.4、NEW 和 OLD

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

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

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

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

2.5、delimiter

delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号 ; 。如果一次输入的语句较多，并且语句中间有分号，这时需要重新指定一个特殊的分隔符。
通常指定 $$ 或 ||。

2.6、示例

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

准备表结构和表数据：

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 goods VALUES (NULL, 'linux', 90);
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）场景1：客户修改订单购买的数量，在原来购买数量的基础上减少2个。

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 INTO `order` (`goods_id`,`quantity`) VALUES(1,2);

select * from goods where goods_id=1;
select * from `order`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

（2）场景2：客户修改订单购买的数量，商品表的库存数量自动改变。

delimiter //
CREATE TRIGGER trig_order_3 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

三、权限管理

3.1、创建用户

语法：

CREATE USER username username@host IDENTIFIED BY password;
1

host 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost ，如果想让该用户可以从任意远程主机登陆，可以使用通配符 % 。

3.2、授权

语法：

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
1

privileges ：用户的操作权限，如SELECT ， INSERT ，UPDATE 等，如果要授予所的权限则使用ALL。

databasename.tablename 如果是 . 表示任意数据库以及任意表。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION 选项导致后来该用户不能使用 GRANT 命令创建用户或者给其它用户授权。
如果不想这个用户有这个 grant 的权限，则不要加该 WITH GRANT OPTION 选项。

3.3、对视图授权

GRANT select, SHOW VIEW ON `databasename`.`tablename` to 'username'@'host';
1

3.4、刷新权限

修改权限后需要刷新权限。

FLUSH PRIVILEGES;
1

四、远程连接

步骤：
（1）注释 mysqld.cnf 中 bind-address 。

-- mysqld.cnf
# vi /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address=127.0.0.1
1
2
3

（2）修改 mysql.user 表。

-- 修改user表
select `user`, `host` from `mysql`.`user`;
update user set host='%' where user='root';
1
2
3

（3）然后重启 mysql。

五、总结

1. 使用视图的优点：简单、安全、数据独立。
2. 视图的作用：可复用、权限管理、节约资源、可重构。
3. 触发器让操作的执行不是由程序调用，也不是手工启动，而是由事件来触发。