mysql基础（八）视图和触发器

一、视图
视图是虚拟的表，是select查询返回的结果。两大特点：1.简便2.安全(可以决定显示哪些信息隐藏哪些信息)。
语句格式如下：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

其中OR REPLACE（必须拥有drop视图权限）用来替换视图，如果表不存在会新建。ALGORITHM定义算法，有三种属性：MERGE（混合）、TEMPTABLE（临时表）、UNDEFINED（未定义，会自动选择前两者之一做算法），在未指定algotithm情况下默认为undefined。WITH CHECK OPTION决定检查范围，两种属性：CASCADED、LOCAL，不指定情况下默认为cascaded。
示例：
先建两张表并插入数据

CREATE TABLE t1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT
);
INSERT INTO t1 VALUES(NULL,'张三',50),(NULL,'李四',32),(NULL,'王五',37);

CREATE TABLE t2(
id INT PRIMARY KEY AUTO_INCREMENT,
sex CHAR(5),
salary INT
);
INSERT INTO t2 VALUES(NULL,'男',5000),(NULL,'女',6200),(NULL,'男',86000);

两表联合查询，只显示两表id和t1的name列、t2的sex列。

SELECT t1.`id`,t1.name,t2.id,t2.sex FROM t1,t2 WHERE t1.id=t2.id

以此返回结果建立视图：

CREATE VIEW t1_t2 AS
SELECT t1.`id`,t1.name,t2.id,t2.sex FROM t1,t2 WHERE t1.id=t2.id;

报错：

报错原因存在相同列名，改下别名就行：

CREATE VIEW t1_t2 AS
SELECT t1.`id`,t1.name,t2.id AS 't2_id',t2.sex FROM t1,t2 WHERE t1.id=t2.id;

创建成功，查看视图：

SELECT * FROM t1_t2

格式和查询表相同，既然是虚拟表，当然也可以增删改。
插入数据至视图：

INSERT INTO t1_t2(NAME,sex) VALUES('赵六','男')

执行报错：

提示不能通过视图修改多个基表，对分属两张表的字段分别测试插入：

INSERT INTO t1_t2(NAME) VALUES('赵六');
INSERT INTO t1_t2(sex) VALUES('男');

插入成功，查看视图：

查看两张基表：


可见对视图添加数据也能影响到基表。
测试更新：

UPDATE t1_t2 SET NAME='张三丰' WHERE t2_id=1;
UPDATE t1_t2 SET sex='男' WHERE id=2;

同样对视图的基表产生作用。
测试删除：

因为此视图有两张基表，是不能做数据删除操作的（单表可以）。下面就更改视图，变为单张基表，再对视图数据进行删除。

ALTER VIEW t1_t2(编号,姓名) AS SELECT id,NAME FROM t1;
DELETE  FROM t1_t2 WHERE 编号=2;
DELETE  FROM t1_t2 WHERE 编号=4;

更改视图依然是使用alter，因为视图中改了列名，所以id=变成编号=。查看视图：

查看基表：

因为视图和表存放在一起，显示视图和显示表一样：

SHOW TABLES

删除视图也和删除表类似：

DROP VIEW t1_t2

删除视图不会对基表的数据产生影响。

二、触发器
触发器是与表有关的数据库对象，是在增删改动作前后触发并执行触发器中定义的语句集合（可以理解为对某些事件的监听并做出响应）。
CREATE TRIGGER trigger_name （触发器名）
trigger_time（触发时间，包括before、after）
trigger_event(触发事件，包括insert、update、delete)
ON tbl_name （监听的表名）
FOR EACH ROW
trigger_stmt（sql语句）
示例：
先建一张emp表：

CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
salary INT
);

再建一张日志表存放emp表的操作信息：

CREATE TABLE emp_log(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
op VARCHAR(20) NOT NULL COMMENT '操作类型，i/u/d',
op_time DATETIME NOT NULL COMMENT '操作时间',
op_id INT(11) NOT NULL COMMENT '操作ID',
op_params VARCHAR(500) COMMENT '操作参数'
)

创建一个针对emp表插入事件的触发器，触发时间在插入之后：

DELIMITER //
CREATE TRIGGER emp_insert
AFTER INSERT
ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_log VALUES(NULL,'insert',NOW(),new.`id`,CONCAT('插入后(id:',new.id,',name',new.name,',age',new.age,'salary',new.salary,')'));
END //
DELIMITER ;

注意new和old关键字

查看触发器：

SHOW TRIGGERS

查看日志表为空

现在对emp插入数据，激活触发器：

INSERT INTO emp VALUES(NULL,'aa',20,3500),(NULL,'bb',26,6000);

再查看日志表：

update、delete同理，不做演示。
删除触发器：

DROP TRIGGER emp_insert

如果把被监听的表删除，触发器也会自动删除。