结构化查询语言(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
作为连接条件,但它们不在视图的投影中,因此无法通过视图查询这些列。
更多提示
在 CREATE VIEW
中可以使用许多 SELECT
的扩展功能,例如:
- 集合操作 (
UNION
,INTERSECT
等) - 递归定义
CASE
表达式ORDER BY
子句(但如果SELECT
查询中包含ORDER BY
,会覆盖视图定义中的排序)
通过视图进行写操作
在某些情况下,可以通过视图对底层表进行写操作 (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
(删除数据)混淆。