提要:在本教程中,你将学习到什么是视图,以及如何在 PostgreSQL 里管理视图。

视图是一种存储查询的数据库对象。在 PostgreSQL 中,一个视图可以作为一个虚拟表来访问。换句话说,PostgreSQL 视图是一个逻辑表,它通过 SELECT 语句表示一个或多个基础表的数据。请注意,除物化视图外,视图不会以物理方式存储数据。

管理视图

在某些情况下,视图可能非常有用,例如:

  • 视图有助于简化查询的复杂性,因为您可以使用简单的 SELECT 语句来查询基于复杂查询的视图。
  • 和数据表一样,可以给视图进行授权。只有拥有权限的特定用户才能查看视图里的数据。
  • 视图提供了一致的 layer,即使是基础表更改的字段也是如此。

创建视图

在我们的示例数据库里,有以下4张表:

  1. customer:存储所有客户数据
  2. address:存储客户的地址
  3. city:存储城市数据
  4. country:存储国家和地区数据

管理视图

如果想得到一个完整的客户数据,通常会构造一个连接查询,如下所示:

SELECT cu.customer_id AS id,
    (((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'::text
            ELSE ''::text
        END AS notes,
    cu.store_id AS sid
   FROM (((customer cu
     JOIN address a ON ((cu.address_id = a.address_id)))
     JOIN city ON ((a.city_id = city.city_id)))
     JOIN country ON ((city.country_id = country.country_id)));

这个查询很复杂。可以通过下面的方法创建一个 customer_master 视图来代替:

CREATE VIEW customer_master AS
 SELECT cu.customer_id AS id,
    (((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'::text
            ELSE ''::text
        END AS notes,
    cu.store_id AS sid
   FROM (((customer cu
     JOIN address a ON ((cu.address_id = a.address_id)))
     JOIN city ON ((a.city_id = city.city_id)))
     JOIN country ON ((city.country_id = country.country_id)));

从现在开始,当需要获取完整的客户数据时,只需要简单的在视图上使用 SELECT 语句:

SELECT
 *
FROM
 customer_master;

修改视图

要修改定义在视图里的查询,可以在 CREATE VIEW 语句里加上 OR REPLACE

CREATE OR REPLACE view_name 
AS 
query

下面,给 customer_master 视图添加一个 email 字段:

CREATE OR REPLACE VIEW customer_master 
AS
SELECT cu.customer_id AS id,
    (((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'::text
            ELSE ''::text
        END AS notes,
    cu.store_id AS sid,
    cu.email
   FROM (((customer cu
     JOIN address a ON ((cu.address_id = a.address_id)))
     JOIN city ON ((a.city_id = city.city_id)))
     JOIN country ON ((city.country_id = country.country_id)));

要修改视图定义,使用 ALTER VIEW 语句。比如,可以使用下面的语句,将 customer_master 重命名为 customer_info

ALTER VIEW customer_master RENAME TO customer_info;

删除视图

使用下面的语法来删除一个已存在的视图:

DROP VIEW [ IF EXISTS ] view_name;

比如:

DROP VIEW IF EXISTS customer_info;
上一篇:视图
下一篇:可更新视图