【MySQL高级】视图与触发器

陶然同学

发布于 2023-02-24 13:44:09

5090

发布于 2023-02-24 13:44:09

文章被收录于专栏：陶然同学博客

3. 视图

3.1 视图概述

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

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

3.2 创建或者修改视图

创建视图的语法为：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

MERGE 引用视图和视图定义的语句的文本被合并，使视图定义的部分取代语句的相应部分。意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句比如：where…那些与创建时的语句where子句等合并，分析，形成一条select语句。

我们先创建一张视图查询所有商品价格大于3000的商品

 create view g2 as select goods_id,goods_name,shop_price from goods where shop_price > 3000;

然后我们再查询视图的时候，再加上一个where条件<5000

select * from g2 where shop_price < 5000;

这时候它就会把两条语句合并分析最终形成这样一条select语句

select goods_id,goods_name,shop_price from goods where shop_price > 3000 and shop_price < 5000;

TEMPTABLE 视图中的结果被检索到一个临时表中，然后用来执行语句。

UNDEFINED MySQL选择使用哪种算法。如果可能的话，它更倾向于MERGE而不是TEMPTABLE，因为MERGE通常更有效率，而且如果使用临时表，视图无法更新。

官方给出的说法：MERGE通常更有效率

merge 和 temptalbe 有一个显著的区别，

merge最终去查的还是原表，而temptable去查的是虚拟表。

修改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

选项 : 
    WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
    
    LOCAL ： 只要满足本视图的条件就可以更新。
    CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

示例 , 创建city_country_view视图 , 执行如下SQL :

create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查询视图 :

3.3 查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看：

3.4 删除视图

语法 :

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

示例 , 删除视图city_country_view :

DROP VIEW city_country_view ;

5. 触发器

5.1 介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

5.2 创建触发器

语法结构 :

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器

begin

    trigger_stmt ;

end;

示例

需求

通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

首先创建一张日志表 :

create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

创建 insert 型触发器，完成插入数据时的日志记录 :

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));    
end $

DELIMITER ;

创建 update 型触发器，完成更新数据时的日志记录 :

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

创建delete 行的触发器 , 完成删除数据时的日志记录 :

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);

update emp set age = 39 where id = 3;

delete from emp where id = 5;

5.3 删除触发器

语法结构 :

drop trigger [schema_name.]trigger_name

如果没有指定 schema_name，默认为当前数据库。

5.4 查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

语法结构：

show triggers ；

6.课后练习

建表语句

create table dept1(
  id int unsigned primary key auto_increment,
  deptno mediumint unsigned not null default 0,
  dname varchar(20) not null default "",
  loc varchar(13) not null default ""
)engine=innodb default charset=gbk;
create table emp1(
  id int unsigned primary key auto_increment,
  empno mediumint unsigned not null default 0,/*编号*/
  ename varchar(20) not null default "",/*姓名*/
  job varchar(9) not null default "",/*工作*/
  mgr mediumint unsigned not null default 0,/*上级编号*/
  hiredate date not null,/*入职时间*/
  sal decimal(7,2) not null, /*薪水*/
  comm decimal(7,2) not null,/*红利*/
  deptno mediumint unsigned not null default 0/*部门编号*/
)engine=innodb default charset=gbk;

任务要求1：写一个随机生成100-109的函数（PS：FLOOR可以取整示例：FLOOR(3.5) = 3）

任务要求2：写一个随机生成长度为N的字符串的函数（n为入参，字符串中字符为大小写的英文）

任务要求3：写一个存储过程，向两张表emp1与dept2中分别插入1W条数据

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-11-04，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库