视图 - 《MySQL》 - 极客文档

视图概述
创建视图
删除视图

《深入浅出MySQL》 https://dev.mysql.com/doc/refman/5.6/en/create-view.html

视图概述

视图特点

简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上：
- 使用权限可被限制在基表的行的子集上。
- 使用权限可被限制在基表的列的子集上。
- 使用权限可被限制在基表的行和列的子集上。
- 使用权限可被限制在多个基表的连接所限定的行上。
- 使用权限可被限制在基表中的数据的统计汇总上。
- 使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。
逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。

视图的优点
视图能简化用户的操作
视图机制可以使用户以不同的方式查询同一数据
视图对数据库重构提供了一定程度的逻辑独立性
视图可以对机密的数据提供安全保护

视图的安全性
视图的安全性可以防止未授权用户查看特定的行或列，使用户只能看到表中特定行的方法如下：
在表中增加一个标志用户名的列；
建立视图，是用户只能看到标有自己用户名的行；
把视图授权给其他用户。
逻辑数据独立性
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
创建视图
```
CREATE
 [OR REPLACE]
 [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
 [DEFINER = user]
 [SQL SECURITY { DEFINER | INVOKER }]
 VIEW view_name [(column_list)]
 AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]
```

CREATE VIEW语句创建一个新视图，或者如果给定了OR REPLACE子句，则替换现有的视图。如果视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同，如果视图确实存在，则CREATE OR REPLACE VIEW来替换它。
select_statement是一个提供视图定义的SELECT语句。(从视图中select实际上是使用SELECT语句选择。)select_statement可以从base tables或其他view中进行选择。
视图定义在创建时是“冻结的”，不受后续对底层表定义的更改的影响。例如，如果一个视图被定义为表上的SELECT *，那么以后添加到表中的新列将不会成为视图的一部分，而从表中删除的列将在从视图中选择时导致错误。
ALGORITHM子句影响MySQL处理视图的方式。DEFINER和SQL SECURITY子句指定在视图调用时检查访问权限时使用的安全上下文。可以使用WITH CHECK OPTION子句约束视图引用的表中的插入或更新。本节后面将描述这些子句。
CREATE VIEW语句需要视图的CREATE VIEW权限，以及SELECT语句选择的每个列的一些权限。对于SELECT语句中其他地方使用的列，您必须具有SELECT权限。如果存在OR REPLACE子句，您还必须拥有视图的DROP权限。如果存在DEFINER子句，则所需的特权取决于用户值，参见： “Stored Object Access Control”.

视图属于数据库。默认情况下，在默认数据库中创建一个新视图。要在给定数据库中显式创建视图，请使用db_name.view_name语法，用数据库名限定视图名:

CREATE VIEW test.v AS SELECT * FROM t;

SELECT 语句中的非限定表名或视图名也根据默认数据库进行解释。视图可以引用其他数据库中的表或视图，方法是用适当的数据库名称限定表或视图的名称。
在数据库中，base tables和view共享相同的名称空间，因此base tables和view不能具有相同的名称。比如：

mysql> create view testdb.test7 as select * from test7;
ERROR 1050 (42S01): Table 'test7' already exists

mysql> create view testdb.test7_view as select * from test7;
Query OK, 0 rows affected (0.02 sec)

SELECT语句检索的列可以是表列的简单引用，也可以是使用函数、常量值、操作符等的表达式。
视图必须具有惟一的列名。默认情况下，SELECT语句中取出的列名称用于视图列名。要为视图列定义显式名称，请将可选的column_list子句指定为逗号分隔的标识符列表。column_list中的名称数目必须与SELECT语句取到的列数目相同。
视图可以由多种SELECT语句创建。它可以引用base tables或其他view。它可以使用join、union和子查询。SELECT甚至不需要引用任何表:

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

下面的例子定义了一个从另一个表中选择两列的视图，以及从这些列中计算出的表达式:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

视图定义受以下限制:

SELECT语句不能在FROM子句中包含子查询。
SELECT语句不能引用系统变量或用户定义的变量。
在stored program（procedure, event, trigger等）中，SELECT语句不能引用程序参数或局部变量。
SELECT语句不能引用准备好的语句参数。
定义中引用的任何表或视图都必须存在。如果在创建视图之后，删除了定义所引用的表或视图，则使用视图将导致错误。要检查此类问题的视图定义，请使用check TABLE语句。
定义不能引用临时表，也不能创建临时视图。
不能将触发器与视图关联。
SELECT语句中的列名别名根据64个字符的最大列长度(而不是256个字符的最大别名长度)进行检查。

ORDER BY可以用在视图定义中，但是如果访问视图的SELECT中使用的order by，则视图定义中的ORDER BY被忽略。当ORDER BY与视图定义中的LIMIT或OFFSET组合在一起时，在外部查询使用查询结果之前，总是强制执行排序，但不能保证在最终结果中使用相同的排序。作为一种解决方案，将ORDER BY子句添加到外部查询。

对于定义中的其他选项或子句，它们被添加到引用视图的语句的选项或子句中，但效果是未定义的。例如，如果一个视图定义包含一个LIMIT子句，并且您使用一个具有自己的LIMIT子句的语句从视图中进行选择，则未定义应用哪个LIMIT。同样的原则也适用于SELECT关键字后面的ALL、DISTINCT或SQL_SMALL_RESULT等选项，以及INTO、FOR UPDATE、LOCK IN SHARE MODE和PROCEDURE等子句。

如果通过改变系统变量来改变查询处理环境，则从视图中获得的结果可能会受到影响:

mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   |
+-------+
1 row in set (0.00 sec)

ALGORITHM子句是标准SQL的MySQL扩展。它影响MySQL处理视图的方式。算法接受三个值:MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，则默认算法是未定义的。

对于TEMPTABLE方式，会将视图的结果放置到临时表中，然后使用临时表执行sql，这样的好处是在临时表建完之后，就会释放在原表上面的锁，这样比MERGE方式更快的释放在访问的表上的锁。
对于UNDEFINED方式，是指有系统自己决定使用TEMPTABLE方式还是MERGE方式，MERGER方式更高效，且TEMPTABLE方式不能更新视图的数据。
对于MERGE方式，实际上是把访问视图的SQL拼接到视图本身的sql上面。要求视图的行和表的行之间是一一对应的，如果不存在这样的一一对应的关系，则会切换到临时表算法。包含以下关键字的sql，不能使用merge方式：聚合函数（sum，min，max，count等等），distinct，group by，having，union或者union all，常量视图。

另外，这些视图的纪录也是不能更新和删除的，不能更新和删除纪录的视图除了以上那些情况外，还包括：

select中包含子查询
join
from一个不能更新的视图
from一个表的子查询
算法是临时表的视图

下面这些视图是不能更新的:

------包含聚合函数
mysql> create or replace view payment_sum as
    -> select staff_id, sum(amount) from payment group by staff_id;

------常量视图
mysql> create or replace view pi as select 3.1415926 as pi;

------select包含子查询
mysql> create view city_view as 
    -> select (select city from city where city_id = 1);

WITH [CASCADED | LOCAL] CHECK OPTION决定了是否允许更新数据使纪录不再满足视图的条件。

local是只要满足本视图的条件就ok，
cascade则是必须满足所有针对该表的所有视图的条件才ok。

如果没有明确是local还是cascade，则默认是cascade。

删除视图

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

DROP VIEW删除一个或多个视图。您必须拥有每个视图的DROP权限。
如果参数列表中指定的任何视图不存在，该语句将返回一个错误，该错误通过名称指示它无法删除哪些不存在的视图，但也将删除列表中确实存在的所有视图。
IF EXISTS子句可以防止不存在的视图发生错误。当给出这个子句时，将为每个不存在的视图生成一个NOTE。

视图

视图概述

视图特点

视图的优点

视图的安全性

逻辑数据独立性

创建视图

删除视图