mysql视图

2019-10-15

定义视图的语句，在每次查询视图时，都会重新执行一遍这个语句来生成最新的数据
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。不能进行修改、删除和创建
MySQL支持视图，包括可更新的视图。视图是存储的查询，这些查询在调用时会产生结果集。视图充当虚拟表。
查询时直接将视图当做一个数据表去查询即可

优点

安全性，保密数据
提高查询效率（当sql查询复杂时，可以提前创建一个视图）
定制化数据，将多张表中经常展示的数据放到视图中，方便查询

算法

CREATE VIEW或ALTER VIEW的可选算法子句是对标准SQL的MySQL扩展。它影响MySQL如何处理视图。算法有三个值:MERGE、TEMPTABLE或UNDEFINED。

  • MERGE：将引用视图的语句文本和视图定义合并，以使视图定义的各个部分替换该语句的相应部分。可以在创建临时表之后以及在完成对语句的使用之前在基础表上释放锁。这可能会导致比MERGE算法更快的锁定释放，因此不会长时间阻塞使用该视图的其他客户端
  • TEMPTABLE：视图的结果被检索到临时表中，然后该表用于执行该语句。
  • UNDEFINED：MySQL选择要使用的算法。它更倾向于MERGE，而不是TEMPTABLE，因为MERGE通常更有效，而且如果使用临时表，视图无法更新。

5.7.6之前的默认算法为UNDEFINED。从5.7.6开始，默认的算法是由optimizer_switch系统变量的derived_merge标志的值决定的

MERGE算法示例

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100;

SELECT * FROM v_merge WHERE vc1 < 100;

-- 最终的查询sql
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

视图是否可更新

一些视图是可更新的，对其的引用可用于指定要在数据更改语句中更新的表。也就是说，您可以在诸如UPDATE，DELETE或INSERT语句中使用它们更新基础表的内容。派生表也可以在多表UPDATE和DELETE语句中指定，但只能用于读取数据以指定要更新或删除的行。通常，视图引用必须是可更新的，这意味着它们可能会合并而不实现。复合视图具有更复杂的规则。
为了使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。
视图中生成的列被认为是可更新的，因为可以对其进行分配。但是，如果显式更新了此列，则唯一允许的值为DEFAULT。
使用MERGE算法处理多表视图，有时可能是可更新的 。为此，视图必须使用内部联接；视图定义中的单个表只能更新，因此SET子句必须仅命名视图表中的一列。UNION ALL尽管从理论上讲它们是可以更新的，但不允许使用这些视图 。
MySQL在创建视图时设置了一个称为视图可更新性标志的标志。如果UPDATE和DELETE(以及类似的操作)对视图是合法的，则该标志设置为YES (true)。否则，该标志被设置为NO (false)。INFORMATION_SCHEMA.VIEWS中的IS_UPDATABLE列显示该标志的状态。
如果视图是不可更新的，UPDATE， DELETE以及INSERT语句是非法的，将被拒绝
如果某个视图依赖于一个或多个其他视图，并且这些基础视图之一已更新，则IS_UPDATABLE标志可能不可靠。无论 IS_UPDATABLE值是多少，服务器都会跟踪视图的可更新性，并正确拒绝对不可更新的视图进行数据更改操作。如果由于基础视图的更改而导致视图的IS_UPDATABLE值变得不准确，则可以通过删除并重新创建视图来更新该值。

如果可更新视图还满足视图列的以下其他要求，则可插入该视图

  • 视图列名称不得重复
  • 该视图必须包含基本表中所有没有默认值的列
  • 视图列必须是简单的列引用。它们不能是如下表达式 
    3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

视图包含以下任何内容，则该视图不可更新：

  • 聚合函数（SUM()， MIN()， MAX()， COUNT()，等等）
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION 要么 UNION ALL
  • select列表中的子查询
  • MySQL的5.7.11之前，select列表中的子查询INSERT失败，但是UPDATE、DELETE没问题。从MySQL 5.7.11开始，这对于不相关的子查询仍然是正确的。对于选择列表中的从属子查询，不允许使用数据更改语句。
  • 某些联接
  • 在FROM子句中引用不可更新的视图
  • WHERE子句中的子查询，引用FROM子句中的表
  • 仅引用文字值（在这种情况下，没有要更新的基础表）
  • ALGORITHM = TEMPTABLE （使用临时表总是使视图不可更新）
  • 对基表任意列的多个引用(INSERT失败，UPDATE成功，DELETE失败)
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;

INSERT

INSERT语句的INSERT表可以是合并的视图引用。如果视图是一个join视图，那么视图的所有组件都必须是可更新的(不是物化的)。对于可更新的多表视图，如果INSERT插入到单个表中，那么它就可以工作。

-- 语句无效，因为联接视图的一个组件不可更新：
INSERT INTO vjoin (c) VALUES (1);

-- 语句有效；该视图不包含具体化的组件：
INSERT INTO vup (c) VALUES (1);

UPDATE

UPDATE语句中要更新的一个或多个表可以是合并的视图引用。如果视图是联接视图，则该视图的至少一个组件必须是可更新的
在多表UPDATE语句中，该语句更新的表引用必须是基本表或可更新的视图引用。未更新的表引用可以是物化视图或派生表。

-- 语句有效；列c来自联接视图的可更新部分：
UPDATE vjoin SET c=c+1;

-- 语句无效；列x来自不可更新部分
UPDATE vjoin SET x=x+1;

-- 语句有效；多表UPDATE的更新表引用（vup）是可更新的视图：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;

-- 语句无效；它尝试更新实例化派生表：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;

DELETE

DELETE语句中要删除的一个或多个表必须是合并视图。不允许连接视图

-- 语句无效，因为该视图是联接视图：
DELETE vjoin WHERE ...;

-- 语句有效，因为该视图是合并（可更新）视图：
DELETE vup WHERE ...;

-- 语句有效，因为它从合并（可更新）视图中删除：
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;

如果不是所有的列都是简单的列引用（例如，如果它包含表达式或复合表达式的列），则视图不可插入。尽管此类视图不可插入，但如果仅更新非表达式的列，则可以更新该视图。

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
-- 这个视图不能插入，因为col2是一个表达式。
-- 但是，如果更新没有尝试更新col2，则它是可更新的。此更新是允许的:
UPDATE v SET col1 = 0;
-- 不允许进行此更新，因为它尝试更新表达式列：
UPDATE v SET col2 = 0;

限制

该SELECT语句不能引用系统变量或用户定义的变量。
该SELECT语句不能引用准备好的语句参数。 
定义中引用的任何表或视图都必须存在。如果在创建视图后删除了定义所引用的表或视图，则使用该视图会导致错误。
定义不能引用TEMPORARY 表，也不能创建TEMPORARY 视图。
您不能将触发器与视图关联。
SELECT语句中 列名的别名 将对照最大列长度64个字符（而不是最大别名长度256个字符）进行检查。

创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
CREATE VIEW v1 AS SELET nid,`name` 
FROM
	A 
WHERE
	nid > 4

参数：
1、OR REPLACE子句，替换现有视图。如果该视图不存在， CREATE OR REPLACE VIEW则与CREATE VIEW相同
2、DEFINER存在该子句，则所需的特权取决于该user值，省略则表示DEFINER=CURRENT_USER
3、SQL SECURITY：比DEFINER优先级更高
DEFINER：视图以DEFINER帐户的特权运行。（默认值）
INVOKER：以是否有视图特权和视图中的表的权限决定是否可以运行。
4、WITH CHECK OPTION：为可更新视图提供可以提供该子句以限制对视图引用的表中行的插入或更新。
当根据另一个视图定义该视图时，LOCAL和CASCADED关键字确定检查测试的范围。
LOCAL：仅将关键字限制为已定义的视图。
CASCADED：使对基础视图的检查也被评估。如果未指定任何关键字，则默认为CASCADED。
5、基表和视图共享相同的名称空间，因此基表和视图不能具有相同的名称

WITH CHECK子句

可以为可更新视图提供WITH CHECK OPTION子句，以防止对select_statement中的WHERE子句不为真的行进行插入。它还阻止对WHERE子句为真但更新会导致它不为真的行进行更新(换句话说，它阻止可见行被更新为不可见行)。
在可更新视图的WITH CHECK OPTION子句中，当视图根据另一个视图定义时，LOCAL和CASCADED关键字确定检查测试的范围。当没有给出关键字时，默认值是CASCADED
在MySQL 5.7.6之前，WITH CHECK OPTION测试的工作方式如下：

  • 使用LOCAL，检查view WHERE子句，但不检查基础视图。
  • 使用CASCADED，检查view WHERE子句，然后对基础视图递归检查，将WITH CASCADED CHECK OPTION其添加到视图中（出于检查目的；其定义保持不变），并应用相同的规则。
  • 如果没有检查选项，则不检查view WHERE子句，也不检查基础视图。

从MySQL 5.7.6开始，WITH CHECK OPTION测试是符合标准的

  • 使用LOCAL，检查view WHERE子句，然后对基础视图递归检查并应用相同的规则。
  • 使用CASCADED，检查view WHERE子句，然后对基础视图递归检查，将WITH CASCADED CHECK OPTION其添加到视图中（出于检查目的；其定义保持不变），并应用相同的规则。
  • 如果没有检查选项，则不检查view WHERE子句，然后递归检查到基础视图，并应用相同的规则。

删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ... 
DROP VIEW v1

修改视图

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
ALTER VIEW v1 AS SELET A.nid,B.`name` 
FROM
	A
	LEFT JOIN B ON A.id = B.nid
	LEFT JOIN C ON A.id = C.nid 
WHERE
	A.id > 2 
	AND C.nid < 5

查看元数据

SELECT * from INFORMATION_SCHEMA.VIEWS
SHOW CREATE VIEW

视图可以join链接多个表生成一个视图，也可以union多个表生成视图（需要两个表查询的字段相同）

CREATE VIEW v1 AS SELECT
id,`name` 
FROM
	tb1 
WHERE
	id > 1 UNION
SELECT
	id,`name` 
FROM
	tb2 
WHERE
	id > 1;

 

CATALOG
标签
mysql redis sqlserver
{/if}