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条数据
