如何使用 SQL 视图和子查询进行复杂查询

本文内容

前几篇我们一起学习了 SQL 如何对表进行创建、更新和删除操作、SQL SELECT WHERE 语句如何指定一个或多个查询条件 和 SQL 如何插入、删除和更新数据 等数据库的基本操作方法。

从本文开始，我们将会在这些基本方法的基础上，学习一些实际应用中的方法。

本文将以此前学过的 SELECT 语句，以及嵌套在 SELECT 语句中的视图和子查询等技术为中心进行学习。由于视图和子查询可以像表一样进行使用，因此如果能恰当地使用这些技术，就可以写出更加灵活的 SQL 了。

一、视图

本节重点

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

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

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

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

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

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

1.1 视图和表

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

视图究竟是什么呢？如果用一句话概述的话，就是“从 SQL 的角度来看视图就是一张表”。

实际上，在 SQL 语句中并不需要区分哪些是表，哪些是视图，只需要知道在更新时它们之间存在一些不同就可以了，这一点之后会为大家进行介绍。

至少在编写 SELECT 语句时并不需要特别在意表和视图有什么不同。

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

通常，我们在创建表时，会通过 INSERT 语句将数据保存到数据库之中，而数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中。

因此，我们通过 SELECT 语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。

但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。

实际上视图保存的是 SELECT 语句（图 1）。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。

视图和表

图 1　视图和表

  • 视图的优点

    视图的优点大体有两点。

    第一点是由于视图无需保存数据，因此可以节省存储设备的容量。

    例如，我们在 SQL 如何插入、删除和更新数据 中创建了用来汇总商品种类（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 语句做成视图。

1.2 创建视图的方法

创建视图需要使用 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（向 Product 表中插入数据）中的 SQL 语句，将数据插入到空表 Product 中

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

代码清单 2 ProductSum 视图

代码清单 2 ProductSum 视图

这样我们就在数据库中创建出了一幅名为 ProductSum（商品合计）的视图。

请大家一定不要省略第 2 行的关键字 AS。这里的 AS 与定义别名时使用的 AS 并不相同，如果省略就会发生错误。虽然很容易混淆，但是语法就是这么规定的，所以还是请大家牢记。

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

代码清单 3 使用视图

代码清单 3 使用视图

执行结果：

1
2
3
4
5

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

通过上述视图 ProductSum 定义的主体（SELECT 语句）我们可以看出，该视图将根据商品种类（product_type）汇总的商品数量（cnt_product）作为结果保存了起来。

这样如果大家在工作中需要频繁进行汇总时，就不用每次都使用 GROUP BY 和 COUNT 函数写 SELECT 语句来从 Product 表中取得数据了。

创建出视图之后，就可以通过非常简单的 SELECT 语句，随时得到想要的汇总结果。并且如前所述，Product 表中的数据更新之后，视图也会自动更新，非常灵活方便。

之所以能够实现上述功能，是因为视图就是保存好的 SELECT 语句。

定义视图时可以使用任何 SELECT 语句，既可以使用 WHERE、GROUP BY、HAVING，也可以通过 SELECT * 来指定全部列。

  • 使用视图的查询

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

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

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

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

    这里没有使用“2 条”而使用了“2 条以上”，是因为还可能出现以视图为基础创建视图的多重视图（图 2）。

    例如，我们可以像代码清单 4 那样以 ProductSum 为基础创建出视图 ProductSumJim。

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

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

    代码清单 4 视图 ProductSumJim

    代码清单 4 视图 ProductSumJim

    1
2
3

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

    执行结果：

    1
2
3

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

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

    法则 3

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