[MySQL] 视图的说明和用法

数据库视图的简介:

数据库视图是虚拟表或者逻辑表,它被定义为具有 连接(JOIN) 属性的SQL SELECT语句.视图和数据库类似,由行和列组成,可以对数据库进行操作,所以也可以把视图看作经过封装的SQL语句.大多数数据库操作系统(MySQL等)都允许你通过一些先决条件(像赋予权限等)对数据库进行操作.

视图操作也是直接对数据库进行的,因此数据发生变化时视图也会进行变化.

数据库视图的优缺点:

优点:

• 视图可以让复杂查询简单化(进过封装).通过将多个表相关联的SQL语句设置为一个视图来隐藏上级用户和复杂的基础表.通过视图你可以使用简单的SQL语句去执行原本需要很复杂的SQL语句(当然简便的同时也会有部分限制,防止数据无故丢失).

• 视图可以限制特定用户的数据访问(对数据进行隐藏).如果你不希望将自己敏感数据给所有用户查看,那么你可以通过视图将非敏感数据(自己所希望给该用户的数据)给特定用户查看.

• 视图可以提供额外的安全层(对视图进行权限限制).视图允许你创建只读视图,你可以将视图的数据公开给特定用户,而不需要担心用户对数据进行修改的威胁.

• 视图可以添加计算列(对数据进行额外计算).规范的数据表中不应该有计算列(指多列数据进行相加,相乘等操作),但是视图中可以有.假设在数据表中有 产品价格 和 产品数量 这两列,但是表中并没有对 产品总额 的列,视图在创建的时候可以添加该列,最终 产品总额 列会在查询的时候自动计算出来.

• 视图实现向后兼容(可替代数据表).如果有一个数据库,有很多应用程序正在使用它.有一次你决定对数据库进行重新设计,将里面的一些数据表删除和修改,但是不希望对那么多应用程序进行修改.在这种情况下你可以将数据表格式替换为视图(当然,视图的列内容你需要重新从新设计的表中获取).

缺点:

• 性能 : 从视图中查询数据可能会很慢(隐藏SQL语句过于复杂,视图构建不合理,数据量过大等),特别是该视图是基于其他视图创建的情况(相当于特别复杂的SQL语句中又添加同样复杂的子查询).
• 表依赖关系 : 每当更改和视图有关的数据表的结构时,视图都需要重新进行构建.

数据库视图的实现:

(仅MySQL)

• 第一种方式,MySQL会根据视图定义语句创建一个临时表,并在此临时表上传入查询数据.
• 第二种方式,MySQL将传入查询和查询定义为一个查询,然后将这俩种查询组合起来.
  
  而文档中说每次 视图更改和替换 时,视图的副本将会在特定的数据库文件夹 arc(archive) 中备份(没找到该文件夹,不知道是不是真的).

数据库视图的限制:

• 不能在视图上创建索引.当视图使用查询时,MySQL会自动调用创建视图的表进行索引操作.
• 在MySQL中如果删除或重命名创建视图的基础表,MySQL不会进行报错,只会使视图操作无效.使用 CHECK TABLE 检查视图是否有效.
• 一个简单的视图可以更新表中数据,而具有 链表 , 子查询 等操作的视图比较复杂,MySQL无法区分,所以不能进行修改.

数据库视图的语句:

MySQL创建视图的语法:

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS
[SELECT  statement]

//由于手贱Ctrl + z进行了撤回,导致心态爆炸,以下为复制.

算法:

算法属性允许您控制MySQL在创建视图时使用的机制，MySQL提供了三种算法：MERGE，TEMPLATE 和 UNDEFINED。

• 使用MERGE算法，MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。 然后MySQL执行组合查询返回结果集。 如果SELECT语句包含集合函数(如MIN，MAX，SUM，COUNT，AVG等)或DISTINCT，GROUP BY，HAVING，LIMIT，UNION，UNION ALL，子查询，则不允许使用MERGE算法。 如果SELECT语句无引用表，则也不允许使用MERGE算法。 如果不允许MERGE算法，MySQL将算法更改为UNDEFINED。请注意，将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。

• 使用TEMPTABLE算法，MySQL首先根据定义视图的SELECT语句创建一个临时表，然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE算法的效率比MERGE算法效率低。 另外，使用TEMPTABLE算法的视图是 不可更新 的。

• 当您创建视图而不指定显式算法时，UNDEFINED是默认算法。 UNDEFINED算法使MySQL可以选择使用MERGE或TEMPTABLE算法。MySQL优先使用MERGE算法进行TEMPTABLE算法，因为MERGE算法效率更高。

名称:

在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。 另外，视图的名称必须遵循表的命名规则。

SELECT语句

在SELECT语句中，可以从数据库中存在的任何表或视图查询数据。SELECT语句必须遵循以下几个规则：

• SELECT语句可以在WHERE子句中包含子查询，但FROM子句中的不能包含子查询。
• SELECT语句不能引用任何变量，包括局部变量，用户变量和会话变量。
• SELECT语句不能引用准备语句的参数。

请注意，SELECT语句不需要引用任何表。

更新:

在MySQL中，视图不仅是可查询的，而且是可更新的。这意味着您可以使用INSERT或UPDATE语句通过可更新视图插入或更新基表的行。 另外，您可以使用DELETE语句通过视图删除底层表的行。

但是，要创建可更新视图，定义视图的SELECT语句不能包含以下任何元素：

• 聚合函数，如：MIN，MAX，SUM，AVG，COUNT等。
• DISTINCT子句
• GROUP BY子句
• HAVING子句
• UNION或UNION ALL子句
• 左连接或外连接。
• SELECT子句中的子查询或引用该表的WHERE子句中的子查询出现在FROM子句中。
• 引用FROM子句中的不可更新视图
• 仅引用文字值
• 对基表的任何列的多次引用

WITH CHECK OPTION确保视图的一致性:

有时候，创建一个视图来显示表的部分数据。然而，简单视图是可更新的，因此可以更新通过视图不可见的数据。此更新使视图不一致。为了确保视图的一致性，在创建或修改视图时使用WITH CHECK OPTION子句。

下面说明了WITH CHECK OPTION子句的语法

CREATE OR REPLACE VIEW view_name 
AS
  select_statement
  WITH CHECK OPTION;

LOCAL＆CASCADED检查范围:

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。因为MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。

为了确定检查的范围，MySQL提供了两个选项：LOCAL和CASCADED。如果您没有在WITH CHECK OPTION子句中显式指定关键字，则MySQL默认使用CASCADED。

• 当视图使用WITH CASCADED CHECK OPTION时，MySQL会循环检查视图的规则以及底层视图的规则。
• 当视图使用WITH LOCAL CHECK OPTION时，MySQL会检查WITH LOCAL CHECK OPTION和WITH CASCADED CHECK OPTION选项的视图规则。而只有WITH LOCAL CHECK OPTION时MySQL只会检查当前视图的规则，并且不会检查底层视图的规则。两种规则都存在时MySQL检查所有依赖视图的规则

查看视图定义:

MySQL提供了用于显示视图定义的SHOW CREATE VIEW语句。

以下是SHOW CREATE VIEW语句的语法：

SHOW CREATE VIEW [database_name].[view_ name];

修改视图:

MySQL提供两个语句，允许您修改现有视图：ALTER VIEW和CREATE OR REPLACE VIEW 。

ALTER VIEW:

ALTER VIEW语句的语法类似于CREATE VIEW语句，除了CREATE关键字被ALTER关键字替换外，其它都一样。

ALTER
 [ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
  VIEW [database_name].  [view_name]
   AS 
 [SELECT  statement]

CREATE OR REPLACE VIEW:

除ALTER VIEW语句外，还可以使用CREATE OR REPLACE VIEW语句来创建或替换现有视图。如果一个视图已经存在，MySQL只会修改视图。如果视图不存在，MySQL将创建一个新的视图。

以下语句使用CREATE OR REPLACE VIEW语法根据employees表创建一个名称为v_contacts的视图：

CREATE OR REPLACE VIEW v_contacts AS
    SELECT 
        firstName, lastName, extension, email
    FROM
        employees;
-- 查询视图数据
SELECT * FROM v_contacts;