视图

2018-09-26 本文已影响6人 1833322237

1. 什么是视图

1.虚拟存在的表
2.对使用视图的用户来说透明的，带来的好处简单、安全、数据独立

2. 视图的操作

2.1 创建修改视图

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `c` from `t1` 
where (`t1`.`c` < 4) WITH CASCADED CHECK OPTION

select_statement 提供视图定义的select语句，可以从其他基表和视图查询
视图在创建时就已经定义好，不会受视图依赖的表影响。依赖表添加的列，视图查询结果不会出现新的列，删除了列，会导致视图中的对应列找不到，报错。

2.1.1.ALGORITHM

ALGORITHM 视图处理算法，视图本身的属性。
ALGORITHM三个值： MERGE，TEMPTABLE或 UNDEFINED
MERGE：服务器将视图SQL和查询SQL进行合并，然后基于底层表查询，返回结果给客户端。
TEMPTABLE：视图的结果检索到临时表（虚拟的），然后在执行查询SQL。
UNDEFINED：视图尽可能的选择MERGE算法而不是TEMPTABLE算法。应为MERGE算法更高效，而且临时表被使用时视图不能够被更新。

显示指定TEMPTABLE算法的一个原因是在创建临时表之后以及在用于完成处理语句之前，可以在基础表上释放锁。这会导致比MERGE算法更快的锁释放，因此使用该视图的其他客户端不会被锁定。
算法被认为是UNDEFINED有3中情况，1是没有指定ALGORITHM ， 2是显示指定ALGORITHM =UNDEFINED ，3是ALGORITHM = MERGE 但是视图只能用TEMPTABLE算法处理，此时MYSQL会生成警告并把算法设置为UNDEFINED

如果视图包含GROUP BY 、DISTANCT、任何聚合函数、UNION、子查询等，只要无法再原表和视图记录中建立意义映射的场景，MYSQL都使用临时表算法来实现视图。
如果想确定MYSQL到底使用哪种算法，可以EXPLAIN 查询语句。临时表算法查询结果select_type出现DERIVED

2.1.2. 可更新和可插入的视图

包含以下选项视图不可跟新：

包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION、UNION ALL
select_statement 包含子查询 (select (select ....))
包含常量列的视图(只局限于视图中的非常量列进行update)
ALGORITHM = TEMPTABLE 视图
FROM一个不能更新的视图
没有更新的基表
对基表的列多次引用（可以update、delete, 不能insert）
join 外连接不可更新，内连接视连接的表（视图）情况而定

包含下列选项才是可插入视图：

可更新
对基表没有进行列的多次引用
视图必须包含基表中没有默认值的所有列
视图列必须是简单的列引用。它们不能是表达式

MYSQL不支持视图上建任何触发器

2.1.3. WITH CHECK OPTION Clause

MYSQL 5.7.6之前

With LOCAL 要检查视图where子句不检查依赖的视图
With CASCADED 要检查视图where子句还要检查依赖视图
With no check option 不检查视图where子句也不检查依赖视图
之后
With LOCAL 要检查视图where子句还要检查依赖的视图
With CASCADED 要检查视图where子句还要检查依赖视图
With no check option 不检查视图where子句但要检查依赖视图

查看MYSQL版本
select version() from dual;

2.3. 删除修改视图

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

包含 IF EXISTS 不报错误， 不包含如果视图不存在 报错
RESTRICT | CASCADE 解析并被忽略

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

视图