相同的二十行报表查询已复制到五个仪表板中，每个副本都有一点偏差。一个会计算已取消的订单，而另一个则不会。 SQL 视图 通过在数据库中以明确的名称定义一次逻辑来解决此问题。

我们在 SQLite 3.50 中运行了下面的每个示例，显示的结果来自该运行。视图是标准 SQL，但通过视图更新、物化视图和依赖关系处理等细节因数据库而异，我们会指出这些。

什么是视图？

视图是一个以名称保存在数据库中的 SELECT 语句。当您查询视图时，数据库会运行已保存的查询。它的作用就像一个用于读取的表，但普通视图不保存自己的数据副本。

想想电子表格中保存的过滤器。过滤器不是工作表的第二个副本。这是查看相同数据的一种方式，这些数据始终反映最新值。

样本表

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

创建视图

该视图汇总了每个客户的订单。它将 join、分组和 CASE 表达式 隐藏在一个简单的名称后面：

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

现在任何人都可以使用它，而无需知道它是如何工作的：

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben 显示为零，因为视图使用 LEFT JOIN 和 COALESCE。像这样的决策，只要在视图中做出一次，就可以保持报告的一致性。要刷新您对 NULL 处理的理解，请参阅 SQL NULL 值。

视图始终是最新的

由于普通视图每次都会运行查询，因此新数据会立即出现。我们为Ben添加了一个付费订单，并再次查询视图：

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

没有刷新步骤，也没有要保持同步的副本。这是与将查询结果保存到单独的表中的主要区别。

隐藏列

视图通常用于仅公开个人或应用程序需要的列。此处省略了电子邮件地址和信用额度：

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

在具有权限的数据库中，例如 PostgreSQL、SQL Server 和 MySQL，您可以授予报告用户对视图的访问权限，但不能授予基础表的访问权限。 SQLite 没有用户帐户，因此我们只能显示列限制本身。将视图视为一层保护。敏感数据仍然需要适当的访问控制，并且某些数据库提供行级安全性以按用户过滤行。

性能：浏览量并不能提高速度

一个常见的误解是视图使查询更快。当您查询普通视图时，它会被其定义所取代，因此数据库的工作与您自己编写完整查询相同。我们的执行计划显示视图的连接和分组作为查询的一部分运行：

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

所以慢查询的视图仍然是慢查询。速度来自索引和良好的查询设计，如 SQL 查询优化 和 SQL 索引解释 中所述。还要注意建立在其他视图之上的视图。多层嵌套使得在不经意间多次连接同一个大表变得很容易。

物化视图

物化视图确实存储其结果，就像缓存表一样。读取速度很快，但数据仅与上次刷新一样新鲜。

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

支持情况各不相同。 PostgreSQL 和 Oracle 具有物化视图，而 SQL Server 则提供具有自己规则的索引视图。 SQLite 和 MySQL 缺乏它们，因此人们使用按计划刷新的定期汇总表。我们没有在本文中运行 PostgreSQL 示例。

您可以更新视图吗？

这取决于数据库和视图。单个表的简单视图（没有分组或 DISTINCT）通常可以在 PostgreSQL、MySQL 和 SQL Server 中更新，并且更改会传递到表。具有联接或聚合的视图通常不能，因为数据库无法判断要更改哪一行。

SQLite 根本不允许直接写入视图。我们的尝试失败了：

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

一些数据库支持 INSTEAD OF 触发器来使视图可写，并且许多数据库提供 WITH CHECK OPTION，它会停止通过视图不可见的写入。在实践中，大多数团队将视图保持为只读并写入表。

依赖性和破坏

视图取决于它所读取的表。我们在表上创建了一个视图，然后删除了该表：

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite 允许删除，但仅在稍后使用视图时失败。其他数据库（例如 PostgreSQL）拒绝删除视图所依赖的表，除非您添加 CASCADE，这也会删除视图。无论哪种方式，重命名或删除列都可能会破坏视图，因此在架构更改后，请在回归测试中检查它们。

视图中的 SELECT * 还存在一个微妙的陷阱。我们创建了 CREATE VIEW star AS SELECT * FROM customers，然后向表中添加了一列。在 SQLite 中，视图选取了新列。其他数据库（包括 PostgreSQL）会在创建视图时展开列列表，因此不会出现新列。因此明确列出列。它记录了视图，并且在任何地方的行为都是相同的。

管理视图

删除视图只会删除已保存的查询。在我们的测试中，删除 public_customers 后，customers 表仍然包含所有三行。将视图定义与迁移一起保留在版本控制中，以便像任何其他代码一样审查更改。

视图、CTE 与表格

常见错误

• 期望速度提升。普通视图每次都会运行其查询。
• 在视图中使用SELECT *。 当表更改时，数据库之间的行为有所不同。
• 深度嵌套。 基于视图构建的视图隐藏了昂贵的连接。
• 在视图内使用 ORDER BY。 查询时不能保证顺序，因此请在外部查询中排序。
• 不明确的名称。 前缀（例如 v_）或明确的名词（例如 customer_sales）会有所帮助。
• 迁移期间忘记视图。在每次架构更改后测试它们。

最后的话

视图是一种将重要逻辑放在数据库中的简单方法。使用它们来保持报告的一致性并隐藏不需要的列，而不是作为性能技巧。首先，找到出现在代码中多个位置的查询，将其转换为具有显式列的视图，并将每个调用者指向它。

常见问题

继续学习