sql-create-view

SQL - 现代视图的创建与使用

什么是 SQL 视图？

在 SQL 中，视图（View）是一个基于 SQL 语句结果集的虚拟表。它本质上是一个存储的查询，你可以像操作普通表一样与其交互。视图包含行和列，但它不实际存储数据本身。相反，它在每次被查询时，都会从底层基础表动态生成数据。

可以将视图视为一个安全且简化的数据库窗口。它是数据库管理员和开发人员的强大工具，用于实现以下目标：

简化复杂查询： 将复杂的联接（joins）、计算和过滤逻辑隐藏在一个简单的名称后面，使应用程序开发人员或数据分析师更容易查询数据。
增强安全性： 限制对特定行或列的访问。例如，你可以创建一个视图，显示员工姓名和部门，但隐藏敏感的薪资信息。
提供稳定的 API： 为应用程序创建一致的接口。如果你需要重构底层表结构，通常可以更新视图的逻辑以保持相同的输出，从而避免更改应用程序代码。

`CREATE VIEW` 语句

要创建视图，你需要使用 CREATE VIEW 语句。许多现代数据库系统还支持 CREATE OR REPLACE VIEW，这在开发过程中非常有用，因为它允许你修改视图的定义而无需先 DROP 再 CREATE 它。

语法

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];

示例：创建产品摘要视图

假设我们有一个结构复杂的 Products 表。我们希望为销售团队提供一个简化视图，该视图只显示活跃产品并计算包含税费的最终价格。

首先，这是我们的基础表：

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    SupplierID INT,
    BasePrice DECIMAL(10, 2) NOT NULL,
    TaxRate DECIMAL(4, 2) NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE
);

INSERT INTO Products (ProductID, ProductName, SupplierID, BasePrice, TaxRate, IsActive) VALUES
(1, 'Laptop Pro', 101, 1200.00, 0.08, TRUE),
(2, 'Wireless Mouse', 102, 25.00, 0.08, TRUE),
(3, 'Old Keyboard', 101, 15.00, 0.07, FALSE), -- 这是一个非活跃产品
(4, '4K Monitor', 103, 450.00, 0.09, TRUE);

现在，让我们创建一个视图来为销售团队简化此表：

CREATE OR REPLACE VIEW V_Active_Product_Pricing AS
SELECT
    ProductID,
    ProductName,
    BasePrice,
    (BasePrice * (1 + TaxRate)) AS FinalPrice
FROM
    Products
WHERE
    IsActive = TRUE;

查询视图

你现在可以像查询普通表一样查询这个视图：

SELECT * FROM V_Active_Product_Pricing WHERE FinalPrice > 100;

结果将只包含活跃产品和计算出的 FinalPrice：

产品ID	产品名称	基础价格	最终价格
1	Laptop Pro	1200.00	1296.00
4	4K Monitor	450.00	490.50

使用 `WITH CHECK OPTION` 强制数据完整性

WITH CHECK OPTION 子句是可更新视图的一个强大功能。可更新视图通常是一个简单视图，它直接映射到单个基础表中的列。此子句确保通过视图执行的任何 INSERT 或 UPDATE 操作都必须符合视图的 WHERE 子句条件。

换句话说，它防止用户以会使行从视图结果中消失的方式插入或更新行。

示例

让我们为特定部门“Sales”的员工创建一个视图，并确保通过此视图添加的任何新员工也都被分配到“Sales”部门。

-- 基础表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

-- 带有检查选项的视图
CREATE OR REPLACE VIEW V_Sales_Employees AS
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department = 'Sales'
WITH CHECK OPTION;

-- 这个 INSERT 将成功，因为它符合 WHERE 子句
INSERT INTO V_Sales_Employees (EmployeeID, Name, Department) VALUES (1, 'Alice', 'Sales'); -- 成功

-- 这个 INSERT 将失败，因为 'Engineering' 不符合 WHERE 子句
INSERT INTO V_Sales_Employees (EmployeeID, Name, Department) VALUES (2, 'Bob', 'Engineering'); -- 错误！

第二个 INSERT 语句将被数据库拒绝，从而保护视图的逻辑一致性。

视图与替代方案（CTE、物化视图）

视图不是构造和重用 SQL 的唯一方式。了解何时使用其他现代结构非常重要。

公用表表达式（CTEs）： 使用 CTE（通过 WITH 子句）创建临时的、命名的结果集，该结果集仅在单个查询的持续时间内存在。CTE 非常适合提高复杂、多步骤查询的可读性，而无需创建像视图这样的永久数据库对象。
物化视图（Materialized Views）： 与标准视图不同，物化视图将其结果集物理存储在磁盘上并定期刷新。当需要对不经常更改的庞大数据集进行性能关键型报告时，请使用物化视图。这是 PostgreSQL 和 Oracle 等关系型数据库管理系统（RDBMS）中的高级功能。

最佳实践和常见陷阱

性能： 视图只是一个存储的查询；它本身并不能提高性能。底层查询在每次访问视图时都会运行。确保视图中的查询通过在基础表上建立适当的索引而得到良好优化。
避免深层嵌套： 避免创建基于其他视图的视图，这种深层嵌套会使调试和性能调优变得极其困难。
命名约定： 采用清晰的命名约定来区分视图和表，例如在视图名称前加上 v_ 或 vw_（例如 v_User_Report）。
依赖性： 请注意，更改或删除基础表将破坏所有依赖于它的视图。现代数据库工具可以帮助你跟踪这些依赖关系。