SQL 视图简介

学习重点

  • 从 SQL 的角度来看,视图和表是相同的,两者的区别在于表中保存的是实际的数据,而视图中保存的是 SELECT 语句(视图本身并不存储数据)。

  • 使用视图,可以轻松完成跨多表查询数据等复杂操作。

  • 可以将常用的 SELECT 语句做成视图来使用。

  • 创建视图需要使用 CREATE VIEW 语句。

  • 视图包含“不能使用 ORDER BY ”和“可对其进行有限制的更新”两项限制。

  • 删除视图需要使用 DROP VIEW 语句。

一、视图和表#

我们首先要学习的是一个新的工具——视图

KEYWORD

  • 视图

视图究竟是什么呢?如果用一句话概述的话,就是“从 SQL 的角度来看视图就是一张表”。实际上,在 SQL 语句中并不需要区分哪些是表,哪些是视图,只需要知道在更新时它们之间存在一些不同就可以了,这一点之后会为大家进行介绍。至少在编写 SELECT 语句时并不需要特别在意表和视图有什么不同。

那么视图和表到底有什么不同呢?区别只有一个,那就是“是否保存了实际的数据”。

通常,我们在创建表时,会通过 INSERT 语句将数据保存到数据库之中,而数据库中的数据实际上会被保存到计算机的存储设备(通常是硬盘)中。因此,我们通过 SELECT 语句查询数据时,实际上就是从存储设备(硬盘)中读取数据,进行各种计算之后,再将结果返回给用户这样一个过程。

但是使用视图时并不会将数据保存到存储设备之中,而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句(图 1)。我们从视图中读取数据时,视图会在内部执行该 SELECT 语句并创建出一张临时表。

视图和表

图 1 视图和表

  • 视图的优点

    视图的优点大体有两点。第一点是由于视图无需保存数据,因此可以节省存储设备的容量。例如,我们在 数据的插入 中创建了用来汇总商品种类(product_type)的表。由于该表中的数据最终都会保存到存储设备之中,因此会占用存储设备的数据空间。但是,如果把同样的数据作为视图保存起来的话,就只需要代码清单 1 那样的 SELECT 语句就可以了,这样就节省了存储设备的数据空间。

    代码清单 1 通过视图等 SELECT 语句保存数据

    1
    2
    3
    
    SELECT product_type, SUM(sale_price), SUM(purchase_price)
    FROM Product
    GROUP BY product_type;
    

    由于本示例中表的数据量充其量只有几行,所以使用视图并不会大幅缩小数据的大小。但是在实际的业务中数据量往往非常大,这时使用视图所节省的容量就会非常可观了。

    法则 1

    表中存储的是实际数据,而视图中保存的是从表中取出数据所使用的 SELECT 语句。

    第二个优点就是可以将频繁使用的 SELECT 语句保存成视图,这样就不用每次都重新书写了。创建好视图之后,只需在 SELECT 语句中进行调用,就可以方便地得到想要的结果了。特别是在进行汇总以及复杂的查询条件导致 SELECT 语句非常庞大时,使用视图可以大大提高效率。

    而且,视图中的数据会随着原表的变化自动更新。视图归根到底就是 SELECT 语句,所谓“参照视图”也就是“执行 SELECT 语句”的意思,因此可以保证数据的最新状态。这也是将数据保存在表中所不具备的优势 1

    法则 2

    应该将经常使用的 SELECT 语句做成视图。

二、创建视图的方法#

创建视图需要使用 CREATE VIEW 语句,其语法如下所示。

KEYWORD

  • CREATE VIEW 语句

语法 1 创建视图的 CREATE VIEW 语句

1
2
3
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>

SELECT 语句需要书写在 AS 关键字之后。SELECT 语句中列的排列顺序和视图中列的排列顺序相同,SELECT 语句中的第 1 列就是视图中的第 1 列,SELECT 语句中的第 2 列就是视图中的第 2 列,以此类推。视图的列名在视图名称之后的列表中定义。

备忘

接下来,我们将会以此前使用的 Product(商品)表为基础来创建视图。如果大家已经根据之前章节的内容更新了 Product 表中的数据,请在创建视图之前将数据恢复到初始状态。操作步骤如下>所示。

① 删除 Product 表中的数据,将表清空

1
DELETE FROM Product;

② 执行代码清单 6(表的删除和更新)中的 SQL 语句,将数据插入到空表 Product

下面就让我们试着来创建视图吧。和此前一样,这次我们还是将 Product 表(代码清单 2)作为基本表。

代码清单 2 ProductSum 视图

ProductSum 视图

这样我们就在数据库中创建出了一幅名为 ProductSum(商品合计)的视图。请大家一定不要省略第 2 行的关键字 AS。这里的 AS 与定义别名时使用的 AS 并不相同,如果省略就会发生错误。虽然很容易混淆,但是语法就是这么规定的,所以还是请大家牢记。

接下来,我们来学习视图的使用方法。视图和表一样,可以书写在 SELECT 语句的 FROM 子句之中(代码清单 3)。

代码清单 3 使用视图

使用视图

执行结果

 product_type | cnt_product
--------------+------------
 衣服         |           2
 办公用品     |           2
 厨房用具     |           4

通过上述视图 ProductSum 定义的主体(SELECT 语句)我们可以看出,该视图将根据商品种类(product_type)汇总的商品数量(cnt_product)作为结果保存了起来。这样如果大家在工作中需要频繁进行汇总时,就不用每次都使用 GROUP BYCOUNT 函数写 SELECT 语句来从 Product 表中取得数据了。创建出视图之后,就可以通过非常简单的 SELECT 语句,随时得到想要的汇总结果。并且如前所述,Product 表中的数据更新之后,视图也会自动更新,非常灵活方便。

之所以能够实现上述功能,是因为视图就是保存好的 SELECT 语句。定义视图时可以使用任何 SELECT 语句,既可以使用 WHEREGROUP BYHAVING,也可以通过 SELECT * 来指定全部列。

  • 使用视图的查询

    FROM 子句中使用视图的查询,通常有如下两个步骤:

    ① 首先执行定义视图的 SELECT 语句

    ② 根据得到的结果,再执行在 FROM 子句中使用视图的 SELECT 语句

    也就是说,使用视图的查询通常需要执行 2 条以上的 SELECT 语句 2

    这里没有使用“2 条”而使用了“2 条以上”,是因为还可能出现以视图为基础创建视图的多重视图(图 2)。例如,我们可以像代码清单 4 那样以 ProductSum 为基础创建出视图 ProductSumJim

    KEYWORD

    • 多重视图
    可以在视图的基础上创建视图

    图 2 可以在视图的基础上创建视图

    代码清单 4 视图 ProductSumJim

    视图 ProductSumJim

    1
    2
    3
    
    -- 确认创建好的视图
    SELECT product_type, cnt_product
    FROM ProductSumJim;
    

    执行结果

    product_type | cnt_product
    --------------+------------
    办公用品     |           2
    

    虽然语法上没有错误,但是我们还是应该尽量避免在视图的基础上创建视图。这是因为对多数 DBMS 来说,多重视图会降低 SQL 的性能。因此,希望大家(特别是刚刚接触视图的读者)能够使用单一视图。

    法则 3

    应该避免在视图的基础上创建视图。

    除此之外,在使用时还要注意视图有两个限制,接下来会给大家详细介绍。

三、视图的限制 ①——定义视图时不能使用 ORDER BY 子句#

虽然之前我们说过在定义视图时可以使用任何 SELECT 语句,但其实有一种情况例外,那就是不能使用 ORDER BY 子句,因此下述视图定义语句是错误的。

定义视图时不能使用 ORDER BY 子句

为什么不能使用 ORDER BY 子句呢?这是因为视图和表一样,数据行都是没有顺序的。实际上,有些 DBMS 在定义视图的语句中是可以使用 ORDER BY 子句的 3,但是这并不是通用的语法。因此,在定义视图时请不要使用 ORDER BY 子句。

法则 4

定义视图时不要使用 ORDER BY 子句。

四、视图的限制 ② ——对视图进行更新#

之前我们说过,在 SELECT 语句中视图可以和表一样使用。那么,对于 INSERTDELETEUPDATE 这类更新语句(更新数据的 SQL)来说,会怎么样呢?

实际上,虽然这其中有很严格的限制,但是某些时候也可以对视图进行更新。标准 SQL 中有这样的规定:如果定义视图的 SELECT 语句能够满足某些条件,那么这个视图就可以被更新。下面就给大家列举一些比较具有代表性的条件。

SELECT 子句中未使用 DISTINCT

FROM 子句中只有一张表

③ 未使用 GROUP BY 子句

④ 未使用 HAVING 子句

在前几章的例子中,FROM 子句里通常只有一张表。因此,大家可能会觉得 ② 中的条件有些奇怪,但其实 FROM 子句中也可以并列使用多张表。

其他的条件大多数都与聚合有关。简单来说,像这次的例子中使用的 ProductSum 那样,使用视图来保存原表的汇总结果时,是无法判断如何将视图的更改反映到原表中的。

例如,对 ProductSum 视图执行如下 INSERT 语句。

1
INSERT INTO ProductSum VALUES ('电器制品', 5);

但是,上述 INSERT 语句会发生错误。这是因为视图 ProductSum 是通过 GROUP BY 子句对原表进行汇总而得到的。为什么通过汇总得到的视图不能进行更新呢?

视图归根结底还是从表派生出来的,因此,如果原表可以更新,那么视图中的数据也可以更新。反之亦然,如果视图发生了改变,而原表没有进行相应更新的话,就无法保证数据的一致性了。

使用前述 INSERT 语句,向视图 ProductSum 中添加数据 (' 电器制品 ',5) 时,原表 Product 应该如何更新才好呢?按理说应该向表中添加商品种类为“电器制品”的 5 行数据,但是这些商品对应的商品编号、商品名称和销售单价等我们都不清楚(图 3)。数据库在这里就遇到了麻烦。

通过汇总得到的视图无法更新

图 3 通过汇总得到的视图无法更新

法则 5

视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。

  • 能够更新视图的情况

    像代码清单 5 这样,不是通过汇总得到的视图就可以进行更新。

    代码清单 5 可以更新的视图

    可以更新的视图

    对于上述只包含办公用品类商品的视图 ProductJim 来说,就可以执行类似代码清单 6 这样的 INSERT 语句。

    代码清单 6 向视图中添加数据行

    向视图中添加数据行

注意事项

由于 PostgreSQL 中的视图会被初始设定为只读,所以执行代码清单 6 中的 INSERT 语句时,会发生下面这样的错误。

执行结果(使用 PostgreSQL)

ERROR:  不能向视图中插入数据
HINT:  需要一个无条件的ON INSERT DO INSTEAD规则

这种情况下,在 INSERT 语句执行之前,需要使用代码清单 A 中的指令来允许更新操作。在 DB2 和 MySQL 等其他 DBMS 中,并不需要执行这样的指令。

代码清单 A 允许 PostgreSQL 对视图进行更新

PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO  ProductJim DO INSTEAD
INSERT INTO Product VALUES (
           new.product_id,
           new.product_name,
           new.product_type,
           new.sale_price,
           new.purchase_price,
           new.regist_date);

下面让我们使用 SELECT 语句来确认数据行是否添加成功吧。

  • 视图

    1
    2
    
    -- 确认数据是否已经添加到视图中
    SELECT * FROM ProductJim;
    

    执行结果

    执行结果

  • 原表

    1
    2
    
    -- 确认数据是否已经添加到原表中
    SELECT * FROM Product;
    

    执行结果

    执行结果

    UPDATE 语句和 DELETE 语句当然也可以像操作表时那样正常执行,但是对于原表来说却需要设置各种各样的约束(主键和 NOT NULL 等),需要特别注意。

五、删除视图#

删除视图需要使用 DROP VIEW 语句,其语法如下所示。

KEYWORD

  • DROP VIEW 语句

语法 2 删除视图的 DROP VIEW 语句

1
DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

例如,想要删除视图 ProductSum 时,就可以使用代码清单 7 中的 SQL 语句。

代码清单 7 删除视图

1
DROP VIEW ProductSum;

特定的 SQL

在 PostgreSQL 中,如果删除以视图为基础创建出来的多重视图,由于存在关联的视图,因此会发生如下错误。

执行结果(使用 PostgreSQL)

ERROR:   由于存在关联视图,因此无法删除视图productsum
DETAIL:  视图productsumjim与视图productsum相关联
HINT:    删除关联对象请使用DROP…CASCADE

这时可以像下面这样,使用 CASCADE 选项来删除关联视图。

PostgreSQL

1
DROP VIEW ProductSum CASCADE;

备忘

下面我们再次将 Product 表恢复到初始状态(8 行)。请执行如下 DELETE 语句,删除之前添加的 1 行数据。

代码清单 B

1
2
-- 删除商品编号为0009(印章)的数据
DELETE FROM Product WHERE product_id = '0009';

请参阅#

(完)


  1. 数据保存在表中时,必须要显式地执行 SQL 更新语句才能对数据进行更新。 ↩︎

  2. 但是根据实现方式的不同,也存在内部使用视图的 SELECT 语句本身进行重组的 DBMS。 ↩︎

  3. 例如,在 PostgreSQL 中上述 SQL 语句就没有问题,可以执行。 ↩︎

comments powered by Disqus