PostgreSQL

Appearance

视图 ​

什么是视图？ ​

视图是存储在数据库中的查询，它就像一个虚拟表，但不存储实际数据。视图基于 SQL 查询的结果集，每次访问视图时都会执行底层查询。

INFO

视图的本质视图本质上是一个命名查询，它允许我们像使用表一样使用复杂查询的结果。

为什么需要视图？ ​

视图是数据库设计中非常重要的一部分，尤其适合简化复杂查询和保护数据

视图的主要优势：

  • 简化复杂查询
  • 提供数据抽象层
  • 限制对基础表的直接访问
  • 提供一致的数据接口

视图的创建语法 ​

sql
CREATE VIEW 视图名称 AS
    SELECT 列1, 列2, ...
    FROM 表1, 表2, ...
    WHERE 条件;

实际示例 ​

问题场景 ​

假设我们有两个表：weather（存储天气记录）和cities（存储城市位置），我们经常需要查询这两个表的组合数据。

数据模型 ​

sql
CREATE TABLE weather (
    city        varchar(80),
    temp_lo     int,           -- 最低温度
    temp_hi     int,           -- 最高温度
    prcp        real,          -- 降水量
    date        date
);
sql
CREATE TABLE cities (
    name        varchar(80),
    location    point          -- 城市坐标位置
);

示例数据 ​

sql
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather VALUES ('Hayward', 37, 54, 0.0, '1994-11-29');
sql
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO cities VALUES ('Hayward', '(-122.0, 37.0)');

创建视图 ​

我们创建一个视图来组合这两个表的数据：

sql
CREATE VIEW city_weather AS
    SELECT name, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;

使用视图 ​

创建视图后，我们可以像查询普通表一样查询它：

sql
SELECT * FROM city_weather;

查询结果 ​

    name    | temp_lo | temp_hi | prcp |    date    |    location
------------+---------+---------+------+------------+----------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | (-194.0,53.0)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | (-194.0,53.0)
 Hayward       |      37 |      54 |    0 | 1994-11-29 | (-122.0,37.0)

视图的工作原理 ​

视图的高级用法 ​

视图还可以基于其他视图创建，形成多层视图结构

sql
-- 创建一个基于city_weather视图的新视图
CREATE VIEW sf_weather AS
    SELECT * FROM city_weather
    WHERE name = 'San Francisco';

持久化存储 ​

视图的 SQL 定义被完整保存在 pg_views 系统视图中，数据库重启后，视图定义仍然存在。

sql
-- 查看视图定义在系统表中的存储
SELECT 
    schemaname,
    viewname,
    viewowner,
    definition
FROM pg_views 
WHERE viewname = 'tech_employees';

视图的修改和版本管理 ​

视图的定义可以随时修改，修改后视图会自动更新。

sql
-- 修改视图定义
ALTER VIEW tech_employees AS
    SELECT * FROM employees
    WHERE department = 'Technology';

视图的最佳实践 ​

视图使用建议

  • 使用视图封装复杂查询逻辑
  • 使用视图提供数据抽象层
  • 通过视图控制用户访问权限
  • 使用视图简化应用程序的数据访问代码

注意事项

  • 视图会在每次访问时执行查询，可能影响性能
  • 复杂视图可能导致查询优化器难以优化
  • 考虑使用物化视图(Materialized Views)来存储查询结果

小结 ​

视图是 PostgreSQL 中非常实用的功能，它可以帮助开发者简化查询，提供数据抽象，并保持数据库接口的一致性。通过合理使用视图，我们可以构建出更加模块化、安全和易于维护的数据库应用。