MySQL：VIEW视图

概述

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且是在使用视图时动态生成的。

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。

使用视图查询数据时，数据库会从真实表中取出对应的数据。视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

MySQL 的视图不支持输入参数的功能，因此交互性上还有欠缺。但对于变化不是很大的操作，使用视图可以很大程度上简化用户的操作。

如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据

视图与数据表的区别

视图不同于数据表

• 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。

• 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。

• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。

• 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。

• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。

• 视图的建立和删除只影响视图本身，不影响对应的基本表。

视图的优点

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

1. 定制用户数据：

   聚焦特定的数据在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

   例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

2. 简化数据操作：

   在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3. 提高数据的安全性：

   视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4. 共享所需数据：

   通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5. 更改数据格式：

   使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6. 重用 SQL 语句:

   视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

在 MySQL 中操作视图

CREATE VIEW 创建视图

通过 CREATE VIEW 语句在数据库中创建视图：

CREATE [OR REPLACE] VIEW [db_name.] view_name [column_list]
AS 
  SELECT column_lists
  FROM table_name
  [WHERE condition];

• view_name 在数据库中必须是唯一的，不能与其他表或视图同名。
• 使用 OR REPLACE 选项替换已存在的视图
• [column_list] 也可以显式指定视图的列列表
• 如果从视图中选择具有自己的 ORDER BY 子句的查询，则忽略它。

基于另一个视图创建视图

视图可以基于现有的视图创建，这样可以进一步抽象查询逻辑或添加额外的过滤条件。

"""也可以基于另一个视图创建视图"""
CREATE VIEW view_name [column_list]
AS
  SELECT column_list
  FROM another_view
  [...];

假设已经有了一个视图 view_sales_summary，它显示了销售数据的汇总信息，现在想创建一个新的视图 view_top_customers，该视图基于 view_sales_summary 并且只显示销售额最高的前10位客户。

CREATE VIEW view_top_customers
 AS
  SELECT *
  FROM view_sales_summary
  ORDER BY total_sales DESC
  LIMIT 10;

创建基于多个表的视图

可以通过 INNER JOIN、LEFT JOIN、RIGHT JOIN 等方式基于多个表创建视图。

CREATE VIEW view_name [column_list]
AS 
  SELECT column_list
  FROM table1 t1
  INNER JOIN table2 t2 ON t1.column1 = t2.column1
  INNER JOIN table3 t3 USING (column2)
  ...;

假设有两个表 customers 和 orders，并且还有一个表 order_items，你想要创建一个视图 view_customer_orders，该视图显示每个客户的订单详情。

CREATE VIEW view_customer_orders 
AS
  SELECT c.customer_id, c.name, o.order_id, oi.item_name, oi.quantity, oi.price
  FROM customers c
  INNER JOIN orders o ON c.customer_id = o.customer_id
  INNER JOIN order_items oi ON o.order_id = oi.order_id;

使用子查询创建视图

有时候需要在视图定义中使用子查询来进一步处理数据或进行复杂的逻辑运算。

CREATE VIEW view_name [column_list]
AS 
  SELECT column_list
  FROM table_name [| subqueries]
  WHERE subqueries;

假设想创建一个视图 view_customer_totals，该视图显示每个客户的总销售额。

CREATE VIEW view_customer_totals AS
  SELECT customer_id, name, (
    SELECT SUM(price * quantity)
    FROM order_items
    WHERE order_id IN (
      SELECT order_id
      FROM orders
      WHERE customer_id = c.customer_id
    )
  ) AS total_sales
  FROM customers c;

视图处理算法

MySQL 视图处理算法（View Processing Algorithms），包括 MERGE、TEMPTABLE 、 UNDEFINED 三种模式

在创建和修改视图的时候，语句有一个可选项 ALGORITHM = 来指定视图处理将会使用的算法

'''CREATE'''
CREATE [OR REPLACE] [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view_name [column_list]
AS 
  SELECT-statement;

'''ALTER'''
ALTER [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] 
VIEW view_name
AS
  SELECT-statement;

MERGE

合并（MERGE）算法：首先将输入查询与视图定义中的 SELECT 语句合并为单个查询。然后执行组合查询以返回结果集。

将输入查询和视图定义的 SELECT 语句组合到单个查询中称为视图解析

这种算法适用于简单的视图定义和基于单个表的查询。

示例：

CREATE ALGORITHM=MERGE VIEW contactPersons(
    customerName, 
    firstName, 
    lastName, 
    phone
) AS
SELECT 
    customerName, 
    contactFirstName, 
    contactLastName, 
    phone
FROM customers;

执行如下语句：

SELECT * FROM contactPersons
WHERE customerName LIKE '%Co%';

MySQL执行以下步骤：

• 将视图名称contactPersons转换为表名称customers。
• 将 askterisk （*） 转换为列表列名一一对应于 SELECT column_list
• 添加 WHERE 子句。

TEMPTABLE

临时表（TEMPTABLE）算法：当使用临时表算法时，MySQL会在内存或磁盘上创建一个临时表来存储视图查询的结果集，并使用该临时表执行查询操作。

TEMPTABLE视图不能进行更新（updatable）

由于MySQL必须创建临时表来存储结果集，并将数据从基表移动到临时表，因此该算法的效率低于 MERGE 算法

这种算法适用于复杂的视图定义、多表关联和其他不适合合并算法的情况。

UNDEFINED

UNDEFINED 算法是创建视图时默认使用的算法。

用 ALGORITHM = MERGE 创建一个视图，而MySQL只能用临时表处理这个视图时，MySQL会自动将算法设置为 UNDEFINED 并产生一个警告。

UNDEFINED 允许MySQL选择 MERGE 或 TEMPTABLE，通常会选择 MERGE ，因为其效率更高

创建可更新视图

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

通过视图更新时转到基本表上进行更新，如果对视图增加或删除记录，实际上是对基本表增加或删除记录。

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

• 聚合函数：如 MIN(), MAX(), SUM(), AVG(), 和 COUNT()

• DISTINCT 子句

• GROUP BY 子句.

• HAVING 子句.

• UNION 或 UNION ALL 子句.

• LEFT JOIN 子句.

• SELECT 子句或 WHERE 子句中引用 FROM 子句中出现的表的子查询。
  ​

• 在 FROM 子句中引用不可更新视图。

• 只引用文字值.

• 对基表的任意列的多个引用.

使用 TEMPTABLE 算法的视图无法被更新

查看可更新视图的信息

可以通过查询 information_schema 数据库中的 views 表中的is_updatable 列来检查数据库中的视图是否处于可更新状态。

下面的查询从classicmodels数据库中获取所有视图，并显示哪些视图是可更新的:

SELECT 
    table_name, 
    is_updatable
FROM
    information_schema.views
WHERE
    table_schema = 'classicmodels';

WITH CHECK OPTION

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

WITH CHECK 选项是 CREATE VIEW 语句的可选子句。WITH CHECK 选项可以防止视图更新或插入不可见的行。换句话说，无论何时通过视图更新或插入基表的一行，MySQL都确保插入或更新操作符合视图的定义

CREATE [OR REPLACE VIEW] view_name 
AS
  SELECT column_list FROM table_name 
  WHERE condition
WITH [CASCADED | LOCAL] CHECK OPTION;

若 column 在视图不可见，在 WITH CHECK OPTION 下插入或更新不可见的数据，MySQL 会报错

对于 WITH CHECK OPTION ，MySQL提供了两个选项:LOCAL和CASCADED
默认情况下，使用 CASCADE 模式

WITH CASCADED CHECK OPTION

当使用 WITH CHECK OPTION CASCADED 模式时，更新视图时会检查所有与视图相关的表，确保更新的行满足视图定义的过滤条件和限制。

如果更新导致视图中的记录不再满足视图定义的条件，则更新将被拒绝

该模式下视图及其相关的表需在同一个数据库中。否则检查可能无法正常进行。

WITH LOCAL CHECK VIEW

当使用 WITH CHECK OPTION LOCAL 模式时，仅检查更新操作中涉及的行是否满足视图定义的过滤条件和限制。其他与视图相关的行不会受到检查。

这种模式下，只需要确保更新的行符合视图定义，而不需要考虑整个视图中的数据一致性。

查看视图

DESCRIBE

查看视图的字段信息与查看数据表的字段信息一样，都是使用 DESCRIBE 关键字来查看的：

DESCRIBE | DESC view_name;

查看所有视图

MySQL将视图视为’VIEW’类型的表。因此，要显示当前数据库中的所有视图，可以使用如下的 SHOW FULL TABLES 语句:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_types = 'VIEW'
LIKE pattern；

• FROM | IN 子句 可以查看其他数据库中的视图

• SHOW FULL TABLES 语句同时返回表和视图，需要添加WHERE子句来只获取视图

• 使用 LIKE 关键字 查看特定视图

查看视图详细信息

SHOW CREATE TABLE

通过 SHOW CREATE VIEW 的语句，还可以查看创建视图的语句。创建视图的语句可以作为修改或者重新创建视图的参考，方便用户操作。

SHOW CREATE VIEW view_name;

视图元数据

所有视图的定义都是存储在 information_schema 数据库下的 views 表中

INFORMATION_SCHEMA 数据库提供对MySQL数据库元数据(如数据库、表、列的数据类型或特权)的访问。;

SELECT *
FROM information_schema.views;

SELECT *
FROM information_schema.tables

• table_schema列 存储视图(或表)的模式或数据库。

• table_name列 存储视图(或表)的名称。

• table_type列 存储表的类型：

  • 表为BASE TABLE，视图为VIEW
  • 表为INFORMATION_SCHEMA SYSTEM，视图为 VIEW。

RENAME VIEW 重命名视图

在MySQL中，视图和表共享相同的命名空间。因此，可以使用 RENAME TABLE 语句重命名视图。

RENAME TABLE original_view_name
TO new_view_name;

通过查看视图语句来确定视图是否更名成功

不能使用 RENAME TABLE 语句将视图从一个数据库移动到另一个数据库。如果这样做，MySQL将发出一个错误。但通过间接方式可以这么做

间接重命名方式

重命名视图的另一种间接方法是使用 DROP VIEW 和 CREATE VIEW 语句的序列:

1. 使用 SHOW CREATE VIEW 语句获取CREATE VIEW语句。

2. 复制 CREATE VIEW 语句并将其保存到一个文件中。

3. 使用 DROP VIEW 语句删除视图。

4. 在 CREATE VIEW 语句中更改视图的名称。

5. 执行 CREATE VIEW 语句以创建具有新名称的视图。

通过使用一系列DROP VIEW和CREATE VIEW语句，还可以将视图从一个数据库移动到另一个数据库。

ALTER VIEW 修改视图

使用 ALTER VIEW 语句来修改已存在的视图定义（查询逻辑、列的选择和条件等内容）:

ALTER [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] 
VIEW [dbname.name] | view_name
AS 
  SELECT-statement;

ALTER VIEW 仅修改视图的定义，不会修改视图查询的结果集。因此，如果修改了视图的定义，需要重新执行对该视图的查询，以反映修改后的结果。

对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

修改视图的定义，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

DROP VIEW 删除视图

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

-- 删除单个视图
DROP VIEW [IF EXISTS] view_name;

-- 删除多个视图
DROP VIEW [IF EXISTS] view_name1, view_name2, ...;

如果列表包含一个不存在的视图，DROP VIEW 语句将失败，并且不会删除任何视图。

如果使用 IF EXISTS 选项，DROP VIEW 语句将为每个不存在的视图生成一个注释，而已存在的视图不会被删除。

在MySQL 5.7或更早的版本中，如果存在任何不存在的视图，DROP VIEW将返回一个错误。但它会删除已经存在的视图。