MySQL第八章：视图

创建视图

就是创建虚拟表，自动化一些重复性的查询模块，简化各种复杂操作（包括复杂的子查询和连接等），可以把部分查询或子查询存储在视图里，后期可以始终这些视图，就不再需要再写一次查询了。

注意:

视图虽然可以像一张表一样进行各种操作，但并没有真正储存数据，数据仍然储存在原始表中，视图只是储存起来的模块化的查询结果（类似于变量，给变量赋值为表格中的数据，同时变量指向了表格中的数据），是为了方便和简化后续进一步操作而储存起来的虚拟表。

案例

题目：

创建 sales_by_client 视图，包括client_id、name和每人的发票总额。

USE sql_invoicing;

CREATE VIEW sales_by_client AS
    SELECT 
        client_id,
        name,
        SUM(invoice_total) AS total_sales
    FROM clients c
    JOIN invoices i USING(client_id)
    GROUP BY client_id, name;
    -- 虽然实际上这里加不加上name都一样

注意：

• 运行上述代码后，在左边的SCHEMAS（模式）栏里点击刷新，此时sql_invoicing数据库的Views下拉菜单里就会出现sales_by_client视图，我们同样可以对其进行查看数据（点击右边的闪电按钮），把它当作表格来使用；

• 若要删掉该视图用 DROP VIEW sales_by_client 或通过右键菜单；

• 后期如果需要筛选出最佳客户（发票总额最高）的名单，可以直接from这个视图上进行操作。

创建视图后可就当作 sql_invoicing 数据库下一张表一样进行各种操作

操作要求：

从sales_by_client 视图中筛选出发票总额大于500的客户名字、发票总额和手机号码。

USE sql_invoicing;

SELECT 
    s.name,
    s.total_sales,
    phone
FROM sales_by_client s
JOIN clients c USING(client_id)
WHERE s.total_sales > 500

练习

题目：

创建一个客户差额表视图，可以看到客户的id，名字以及差额（发票总额-支付总额）

USE sql_invoicing;

CREATE VIEW clients_balance AS
    SELECT 
        client_id,
        c.name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices USING(client_id)
    GROUP BY client_id

更新或删除视图

修改视图可以先DROP在CREATE（也可以用CREATE OR REPLACE）

注意：

视图的查询语句可以在编辑模式下查看和修改，但最好是保存为sql文件并放在源码控制妥善管理

案例

题目：

在上一节的顾客差额视图的查询语句最后加上按差额降序排列，并删除视图再重建。（两种方法来实现）

-- 法1. 先删除再重建

USE sql_invoicing;

DROP VIEW IF EXISTS clients_balance;
-- 若不存在这个视图，直接 DROP 会报错，所以要加上 IF EXISTS 先检测有没有这个视图

CREATE VIEW clients_balance AS 
    SELECT 
        client_id,
        c.name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices USING(client_id)
    GROUP BY client_id
    ORDER BY balance DESC

-- 法2. 用REPLACE关键字
USE sql_invoicing;

CREATE OR REPLACE VIEW clients_balance AS
    SELECT 
        client_id,
        c.name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices USING(client_id)
    GROUP BY client_id
    ORDER BY balance DESC

注意：

• 法一：先删除再重建 用的多一点；
• 法二不需要先删除视图。

如何保存视图的原始查询语句？

• 法一（推荐方法）

  将原始查询语句保存为 views 文件夹下的和与视图同名的 clients_balance.sql 文件，然后将这个文件夹放在源码控制下, 通常放在 git repository（仓库）里与其它人共享，团队其他人因此能在自己的电脑上重建这个数据库。

• 法二

  若丢失了原始查询语句，要修改的话可点击视图的扳手按钮（三个图案中的第一个图案）打开编辑模式，可看到如下被MySQL处理了的查询语句

  

  MySQL在前面加了些莫名其妙的东西（line2-line4），这些是MySQL自己自动加上去的。并且在所有库名表名字段名外套上反引号（` `），防止名称冲突（如果当对象名和MySQL里的关键字相同时确保被当作对象名而不是关键字，会有反引号（` `）来防止名称冲突），但这都不影响。如果想要在编辑模式的窗口上进行修改代码，比如order by total_sales desc,可以点击右下角的apply，可以发现apply后自己增加的代码格式会和上面代码格式一样，变为 order by `total_sales` desc。

可更新视图

视图作为虚拟表/衍生表，除了可用在查询语句SELECT中，也可以用在增删改（INSERT DELETE UPDATE）语句中，但后者有一定的前提条件。

如果一个视图的原始查询语句中没有如下元素：

1. DISTINCT 去重
2. GROUP BY/HAVING/聚合函数 (后两个通常是伴随着 GROUP BY 分组出现的)
3. UNION 纵向连接

则该视图是可更新视图（Updatable Views），可以增删改，否则只能查。

另外，增（INSERT）还要满足附加条件：视图必须包含底层原表的所有必须字段

注意：

对视图做的变化会映射到原表上（也会对原表进行相应的变化）

案例

题目：

创建一个视图（新虚拟表）invoices_with_balance，可以看到invoice的所有字段以及差额（发票总额-支付总额），并筛选出差额>0的包含以上字段的记录。

USE sql_invoicing;

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT 
    /* 这里有个小技巧，要插入表中的多列列名时，
    可从左侧栏中连选并拖入相关列 */
    invoice_id, 
    number, 
    client_id, 
    invoice_total, 
    payment_total, 
    invoice_date,
    invoice_total - payment_total AS balance,  -- 新增列
    due_date, 
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

该视图满足条件，是可更新视图，故可以增删改（修改视图的数据也会更新原表）

• 删：删掉id为1的发票记录

DELETE FROM invoices_with_balance
WHERE invoice_id = 1

• 改：将2号发票记录的期限延后两天

UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2

• 增

在视图中用INSERT新增记录的话还有另一个前提，即视图必须包含其底层所有原始表的所有必须字段。

例如，若这个 invoices_with_balance 视图里没有 invoice_date 字段（invoices 中的必须字段,，也就是NN字段，字段值不能为空），那就无法通过该视图向 invoices 表新增记录，因为 invoices 表不会接受 invoice_date 字段为空的记录

WITH CHECK OPTION 子句

在视图的原始查询语句最后加上 WITH CHECK OPTION 可以防止执行那些会让视图中某些行（记录）消失的修改语句。

案例

题目新invoices_with_balance 视图与原始的 orders 表相比增加了balance(invoice_total - payment_total) 列，现要更新视窗，让invoice_id为2 的payment_total = invoice_total.

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

注意：

• 执行完上述语句后，需要点击左边SCHEMAS模式的更新按钮，更新后会发现invoices_with_balance视图里2号订单消失；
• 在视图原始查询语句（非关键字两边会有``)的最后加入 WITH CHECK OPTION 后，对3号订单执行类似上面的语句后会报错：Error Code: 1369. CHECK OPTION failed ‘sql_invoicing.invoices_with_balance’`;
• 若执行不了，点击偏好设定–SQL editor–将最下面的safe updates打开。

视图的其他优点

三大优点：

简化查询、增加抽象层和减少变化的影响、数据安全性

具体来讲：

1. （首要优点）简化查询 ；

2. 增加抽象层，减少变化的影响 ：视图给表增加了一个抽象层（模块化），这样如果数据库设计改变了（如一个字段需要重新命名），只需修改视图的查询语句使该字段重新命名为新的别名，不需要在多个sql文件中修改使用涉及到原表（from和join）的那几十个查询。相反，如果没有视图这一层的话，所有查询将直接使用指向原表的原始查询语句，这样一旦更改原表设计，就要相应地更改所有的这些查询。

   详细解释：

   原表结构变动的挑战

   在没有使用视图的情况下，如果原表的结构发生变化，如更改列名、数据类型，或者重新组织数据，所有直接依赖于这个表的SQL查询都需要相应地进行修改。这是因为这些查询直接引用了原表中的具体列名和结构。

   视图带来的独立性和维护简化

   当使用视图时，这些查询不再直接依赖于原表的具体结构，而是依赖于视图。视图充当了原表和查询之间的中间层，它向查询提供了一致的接口，即使底层的原表结构发生变化。

   例子

   假设有一个原表 Employee，它有一个列 Name。许多查询都是基于这个列进行的。现在，由于某种原因，需要将这个列更名为 EmployeeName。如果直接在原表上进行这个更改，所有引用了 Name 列的查询都需要修改。

   但是，如果有一个视图 View_Employee，它定义如下：

   CREATE VIEW View_Employee AS
   SELECT Name, Department
   FROM Employee;
   

   所有查询都是基于这个视图进行的，而不是直接基于 Employee 表。当 Employee 表中的 Name 列更名为 EmployeeName 时，你只需要更新这个视图的定义：

   CREATE OR REPLACE VIEW View_Employee AS
   SELECT EmployeeName AS Name, Department
   FROM Employee;
   

   这个新定义中，EmployeeName 列被重新命名为 Name，这样所有基于 View_Employee 视图的查询都不需要做任何修改。它们仍然可以按照之前的方式运行，因为视图对它们隐藏了底层原表结构的变化。

3. 限制对原数据的访问权限 ：在视图中可以对原表的行和列进行筛选，这样如果你禁止了对原始表的访问权限，用户只能通过视图来修改数据，他们就无法修改视图中未返回的那些字段和记录。但注意这通常并不简单，需要良好的规划，否则最后可能搞得一团乱，