15. MySQL视图

• 1. 创建或者修改视图
  • 1.1 MySQL 创建视图的限制
  • 1.2 MySQL 更新视图的限制
• 2. 查看视图
• 3. 删除视图

视图并不在数据库中实际存在， 行和列数据来自定义视图的查询中使用的表， 并且是在使用视图时动态生成的。

优点：

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

1. 创建或者修改视图

创建视图需要有 CREATE VIEW 的权限，并且对于查询涉及的列有 SELECT 权限。如果使用 CREATE OR REPLACE 或者 ALTER 修改视图，那么还需要该视图的 DROP 权限。

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 OR REPLACE VIEW：

• 视图不存在：和 CREATE VIEW 等价
• 视图存在：和 ALTER VIEW 等价

1.1 MySQL 创建视图的限制

MySQL 定义视图的限制：

• SELECT 语句中的 FROM 语法中不能包含子查询
• SELECT 语句不能引用系统变量或用户定义的变量
• 在存储过程中，SELECT 不能引用程序参数或局部变量
• SELECT 语句不能引用准备好的声明参数（statement parameters）？？？
• 在定义中引用的表或视图必须存在。
• 不能引用临时表，所以不能创建临时视图
• 不能讲视图与触发器关联起来
• SELECT 子句中列的别名最大长度为64个字符
• 不能在视图中创建索引

对于第一条，如果视图是从其他数据库迁移过来的， 那么可能需要因此做一些改动， 可以将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。

1.2 MySQL 更新视图的限制

视图的可更新性（即通过视图进行插入等操作）和视图中查询的定义有关系，以下类型的视图是不可更新的：

• 包含以下关键字的 SQL 语句： 聚合函数 （SUM、 MIN、 MAX、 COUNT 等）、 DISTINCT、 GROUP BY、HAVING、UNION 或者 UNION ALL。

• 常量视图。

• SELECT 中包含子查询。
• JION。
• FROM 一个不能更新的视图。
• WHERE 子句的子查询引用了 FROM 子句中的表。

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

• CASCADED：必须满足针对该视图的所有条件才可以更新。
• LOCAL：只要满足本视图的条件就可以更新。
• 如果不指定此选项，与LOCAL规则相同，但不满足本视图条件时会显示成功且不会更新数据。

/* 基础视图 */
MariaDB [MYISAM_TEST]> CREATE OR REPLACE VIEW PARENT_VIEW (PID, PNAME) AS SELECT * FROM PARENT WHERE ID < 5;

/* 二层视图，CASCADED  */
MariaDB [MYISAM_TEST]> CREATE OR REPLACE VIEW PARENT_VIEW1(PID, PNAME) AS SELECT * FROM PARENT_VIEW WHERE PID > 1 WITH CASCADED CHECK OPTION;

/* 插入失败 */
MariaDB [MYISAM_TEST]> INSERT INTO PARENT_VIEW1 VALUES(11, 'DSAF');
ERROR 1369 (HY000): CHECK OPTION failed 'MYISAM_TEST.PARENT_VIEW1'

/* 二层视图，LOCAL */
MariaDB [MYISAM_TEST]> CREATE OR REPLACE VIEW PARENT_VIEW1(PID, PNAME) AS SELECT * FROM PARENT_VIEW WHERE PID > 1 WITH LOCA CHECK OPTION;

/* 插入成功 */
MariaDB [MYISAM_TEST]> INSERT INTO PARENT_VIEW2 VALUES(11, 'DSAF');
Query OK, 1 row affected (0.01 sec)

/* 二层视图，不指定检查选项 */
MariaDB [MYISAM_TEST]> CREATE OR REPLACE VIEW PARENT_VIEW3(PID, PNAME) AS SELECT * FROM PARENT_VIEW WHERE PID > 2;

/* 不会报错，也不会更新 */
MariaDB [MYISAM_TEST]> DELETE FROM PARENT_VIEW3 WHERE PID=1;
Query OK, 0 rows affected (0.00 sec)

2. 查看视图

SHOW TABLES;
SHOW TABLE STATUS;
SHOW CREATE VIEW;

3. 删除视图

一次可以删除多个视图

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