结构化查询语言(Structured Query Language): 视图

视图 (Views)

用户和应用程序通常需要以不同于现有表结构的形式获取信息。为了满足这些需求，SQL 提供了丰富的功能，比如投影、联接、GROUP BY 子句等。如果某些请求是固定的（尤其在应用场景中）或需要将表结构隐藏起来，可以定义视图。视图还能赋予不同于表的访问权限。

视图的特点

外观像表：视图有固定的数据列类型，可以像表一样进行查询。
无独立数据：视图本身没有数据，数据始终来源于表或其他视图。
预定义查询：视图本质上是一个预定义的 SELECT 命令。

创建视图

创建视图需要指定视图名称、（可选）列名以及基于的 SELECT 命令。SELECT 中可以使用所有标准功能。

CREATE VIEW <view_name> [(column_name, ...)] AS
  SELECT ...; -- 正常的 SELECT 语法

示例与解释

示例 1：隐藏列

创建一个视图 person_view_1，它包含 person 表的所有列，但隐藏了 id 和 ssn 列。用户只能访问视图的数据，而不能访问表的隐藏列。

CREATE VIEW person_view_1 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person;

-- 查询视图的语法与查询表相同
SELECT *
FROM   person_view_1
ORDER BY lastname;

注意：由于 id 列不在视图中，无法在视图的查询中使用：

-- 此查询会报错，因为视图中没有 'id' 列
SELECT *
FROM   person_view_1
WHERE  id = 5;

扩展查询视图：

视图支持所有常规 SQL 查询功能，例如聚合、分组、排序等：

-- 统计每个姓氏的数量
SELECT count(lastname), lastname
FROM   person_view_1
GROUP BY lastname
ORDER BY lastname;

示例 2：重命名列

可以在视图中重命名列。例如，将 lastname 列重命名为 familyname。

方法 1：在视图名称后指定列名

CREATE VIEW person_view_2a (firstname, familyname, date_of_birth, place_of_birth, weight) AS
  SELECT                    firstname, lastname,   date_of_birth, place_of_birth, weight
  FROM   person;

方法 2：在 `SELECT` 中使用别名

CREATE VIEW person_view_2b AS
  SELECT firstname, lastname AS familyname, date_of_birth, place_of_birth, weight
  FROM   person;

注意：如果两种方法同时使用，方法 1 中指定的列名会覆盖 方法 2 中的重命名。

-- 访问重命名后的列
SELECT firstname, familyname
FROM   person_view_2a;

-- 以下对象不存在
-- person.familyname
-- person_view_2a.lastname
-- person_view_2b.lastname

视图的常用场景

隐藏敏感数据：通过隐藏敏感列（如用户 ID、社会安全号码等），增强数据安全性。
简化复杂查询：将频繁使用的复杂查询封装成视图，方便后续使用。
数据转换：重命名列或根据需求格式化数据，以更友好的形式呈现给用户。
权限管理：赋予视图特定权限，而非直接暴露表。

视图为 SQL 查询提供了强大的抽象功能，既可以隐藏底层表结构，又能简化复杂操作，提升应用开发效率。

示例 3：应用 WHERE 条件

视图不仅可以隐藏列，还可以通过在定义中加入 WHERE 子句隐藏特定的行。

-- 限制访问部分行
CREATE VIEW person_view_3 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person
  WHERE  place_of_birth IN ('San Francisco', 'Richland');

-- 验证结果
SELECT *
FROM   person_view_3;

该视图仅包含出生地在 San Francisco 或 Richland 的人员信息，其他人员的记录被隐藏。

-- 查询结果为空
SELECT *
FROM   person_view_3
WHERE  place_of_birth = 'Dallas';

-- 查询原始表，结果不为空
SELECT *
FROM   person
WHERE  place_of_birth = 'Dallas';

示例 4：使用函数

以下示例展示了使用 sum() 聚合函数定义视图。

CREATE VIEW person_view_4 AS
  -- 注意：包含 GROUP BY 的 SELECT 语句中，只有分组列和聚合函数的结果可用
  SELECT lastname, count(lastname) AS count_of_members
  FROM   person
  GROUP BY lastname
  HAVING count(*) > 1;

-- 验证结果：视图中有两行
SELECT *
FROM   person_view_4;

-- 可以使用计算列作为 WHERE 条件
SELECT *
FROM   person_view_4
WHERE  count_of_members > 2;

此视图将 GROUP BY / HAVING 的复杂逻辑对用户和应用程序隐藏。

示例 5：使用 JOIN

视图可以通过 JOIN 包含多个表的列。以下示例展示了将人员与其联系信息组合的视图：

-- 人员与其联系方式
CREATE VIEW person_view_5 AS
  SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
  FROM   person p
  JOIN   contact c ON p.id = c.person_id;

-- 验证结果
SELECT *
FROM   person_view_5;

-- 查询特定姓氏的记录
SELECT *
FROM   person_view_5
WHERE  lastname = 'Goldstein';

在视图定义中使用了 person.id 和 contact.person_id 作为连接条件，但它们不在视图的投影中，因此无法通过视图查询这些列。

通过视图进行写操作

在某些情况下，可以通过视图对底层表进行写操作 (UPDATE, INSERT, DELETE)，但并非所有视图都支持写操作。

例如，以下视图不支持修改列 count_of_members，因为它是由聚合函数计算的：

CREATE VIEW person_view_4 AS
  SELECT lastname, count(lastname) AS count_of_members
  FROM   person
  GROUP BY lastname;

但类似以下简单视图是可写的：

CREATE VIEW person_0 AS
  SELECT * FROM person;

视图是否可写取决于实现，但通常满足以下条件时可能允许写操作：

基于单个表：视图只能基于一个表定义，且必须包含该表的主键。
无聚合函数：视图定义中不能包含任何聚合函数。
无复杂子句：视图定义中不能有 DISTINCT、GROUP BY 或 HAVING 子句。
无连接或子查询：视图定义中不能包含 JOIN、子查询、集合操作、EXISTS 或 NOT EXISTS 谓词。
只读标记：某些实现提供强制设置视图为只读的选项，即使视图本身技术上支持写操作。

删除视图

使用 DROP VIEW 删除视图定义，底层表中的数据不会受到影响。

-- 删除视图
DROP VIEW person_view_1;
DROP VIEW person_view_2a;
DROP VIEW person_view_2b;
DROP VIEW person_view_3;
DROP VIEW person_view_4;
DROP VIEW person_view_5;

注意：不要将 DROP（删除定义）与 DELETE（删除数据）混淆。

最后修改: 2025年01月28日星期二 13:15