SQL基础教程笔记：第五章 复杂查询

目录

视图

学习重点

  • 从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。
  • 使用视图，可以轻松完成跨多表查询数据等复杂操作。
  • 可以将常用的SELECT语句做成视图来使用。
  • 创建视图需要使用CREATE VIEW语句。
  • 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。
  • 删除视图需要使用DROP VIEW语句。

视图和表

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

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

  • 通常，我们在创建表时，会通过 INSERT 语句将数据保存到数据库之中，而数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中。因此，我们通过 SELECT 语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。
  • 但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是SELECT语句（图 5-1）。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。

image
视图的优点有以下几点。

  • 第一点是由于视图无需保存数据，因此可以节省存储设备的容量。在实际的业务中数据量往往非常大，这时使用视图所节省的容量就会非常可观了.
-- 通过视图等SELECT语句保存数据
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type;
  • 第二个优点就是可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。创建好视图之后，只需在 SELECT 语句中进行调用，就可以方便地得到想要的结果了。特别是在进行汇总以及复杂的查询条件导致 SELECT 语句非常庞大时，使用视图可以大大提高效率。
  • 视图中的数据会随着原表的变化自动更新。视图归根到底就是SELECT 语句，所谓“参照视图”也就是“执行 SELECT 语句”的意思，因此可以保证数据的最新状态。这也是将数据保存在表中所不具备的优势(数据保存在表中时，必须要显式地执行SQL更新语句才能对数据进行更新)。

总结：

  • 表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。
  • 应该将经常使用的SELECT语句做成视图。

创建视图的方法

image
SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。视图的列名在视图名称之后的列表中定义。
image
这样我们就在数据库中创建出了一幅名为ProductSum（商品合计）的视图。请大家一定不要省略第2行的关键字AS。这里的AS与定义别名时使用的AS并不相同，如果省略就会发生错误。虽然很容易混淆，但是语法就是这么规定的，所以还是请大家牢记。
image
定义视图时可以使用任何SELECT语句，既可以使用WHERE、GROUPBY、HAVING，也可以通过 SELECT * 来指定全部列。
image
也就是说，使用视图的查询通常需要执行2条以上的SELECT语句。这里没有使用“2 条”而使用了“2 条以上”，是因为还可能出现以视图为基础创建视图的多重视图（图 5-2）。例如，我们可以像代码清单 5-4 那样以 ProductSum 为基础创建出视图 ProductSumJim。
image

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

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

虽然之前我们说过在定义视图时可以使用任何 SELECT 语句，但其实有一种情况例外，那就是不能使用 ORDER BY 子句，因此下述视图定义语句是错误的。
image
为什么不能使用ORDER BY子句呢？这是因为视图和表一样， 数据行都是没有顺序的。实际上，有些DBMS在定义视图的语句中是可以使用ORDER BY子句的(例如，在PostgreSQL中上述SQL
语句就没有问题，可以执行)，但是这并不是通用的语法。因此，在定义视图时请不要使用 ORDER BY 子句。

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

之前我们说过，在SELECT语句中视图可以和表一样使用。那么，对于 INSERT、 DELETE、 UPDATE 这类更新语句（更新数据的 SQL）来说，会怎么样呢？实际上，虽然这其中有很严格的限制，但是某些时候也可以对视图进行更新。标准SQL中有这样的规定：如果定义视图的SELECT语句能够满足某些条件，那么这个视图就可以被更新。下面就给大家列举一些比较具有代表性的条件。
image
除了第二条外，其他的条件大多数都与聚合有关。简单来说，像这次的例子中使用ProductSum 那样，使用视图来保存原表的汇总结果时，是无法判断如何将视图的更改反映到原表中的。
image
但是，上述INSERT语句会发生错误。这是因为视图ProductSum是通过GROUP BY子句对原表进行汇总而得到的。为什么通过汇总得到的视图不能进行更新呢？视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。使用前述INSERT语句，向视图 ProductSum 中添加数据 (' 电器制品 ', 5) 时，原表Product应该如何更新才好呢？按理说应该向表中添加商品种类为“电器制品”的 5 行数据，但是这些商品对应的商品名称和销售单价等我们都不清楚（图5-3）。数据库在这里就遇到了麻烦。
视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。
image
image
image
image
image
下面让我们使用 SELECT 语句来确认数据行是否添加成功吧。
image
UPDATE语句和DELETE语句当然也可以像操作表时那样正常执行，但是对于原表来说却需要设置各种各样的约束（主键和 NOT NULL 等），需要特别注意。

删除视图

image

-- 删除视图
DROP VIEW ProductSum;

image

子查询

学习重点

  • 一言以蔽之，子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
  • 由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
  • 标量子查询就是只能返回一行一列的子查询。

子查询和视图

视图并不是用来保存数据的，而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利。反之，以视图为基础的子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中。子查询的特点概括起来就是一张一次性视图。
image
能够实现同样功能的子查询如代码清单 5-9 所示。
image
如上所示,子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中.虽然“AS ProductSum”就是子查询的名称，但由于该名称是一次性的，因此不会像视图那样保存在存储介质（硬盘）之中，而是在SELECT语句执行之后就消失了。

实际上，该SELECT语句包含嵌套的结构，首先会执行FROM子句中的SELECT语句，然后才会执行外层的SELECT语句（图 5-4）。
image
image
子查询作为内层查询会首先执行。
由于子查询的层数原则上没有限制，因此可以像“子查询的FROM子句中还可以继续使用子查询，该子查询的 FROM 子句中还可以再使用子查询……”这样无限嵌套下去（代码清单 5-10）。
image
但是，随着子查询嵌套层数的增加， SQL 语句会变得越来越难读懂，性能也会越来越差。因此，请大家尽量避免使用多层嵌套的子查询。

子查询的名称

之前的例子中我们给子查询设定了 ProductSum 等名称。原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。在上述例子中，子查询用来对 Product 表的数据进行汇总，因此我们使用了后缀 Sum 作为其名称。为子查询设定名称时需要使用 AS 关键字，该关键字有时也可以省略(其中也有像 Oracle 这样，在名称之前使用AS 关键字就会发生错误的数据库，大家可以将其视为例外的情况) 。

标量子查询

接下来我们学习子查询中的标量子查询（scalar subquery）。标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“东京都”这样的值。标量子查询就是返回单一值的子查询。
在WHERE子句中使用标量子查询：
image
image

标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。
image
从上述结果可以看出，在商品一览表中加入了全部商品的平均单价。有时我们会需要这样的单据。
image

使用标量子查询时的注意事项

最后我们来介绍一下使用标量子查询时的注意事项，那就是该子查询绝对不能返回多行结果。也就是说，如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在=或者<>等需要单一输入值的运算符当中，也不能用在SELECT等子句当中。例如，如下的 SELECT 子查询会发生错误。
image
上述SELECT语句会返回“因为子查询返回了多行数据所以不能执行”这样的错误信息。

关联子查询

学习重点

  • 关联子查询会在细分的组内进行比较时使用。
  • 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
  • 关联子查询的结合条件如果未出现在子查询之中就会发生错误。

普通的子查询和关联子查询的区别

选取出各商品种类中高于该商品种类的平均销售单价的商品，
image
事实上，对于代码清单 5-16 中的SELECT语句，即使在子查询中不使用GROUP BY子句，也能得到正确的结果。这是因为在WHERE子句中追加了“P1.product_type=P2.product_type”这个条件，使得AVG 函数按照商品种类进行了平均值计算。但是为了跟前面出错的查询进行对比，这里还是加上了GROUP BY子句。
image
这次由于作为比较对象的都是同一张 Product 表，因此为了进行区别，分别使用了 P1 和 P2 两个别名。在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 >”的形式记述。
在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。因此，使用关联子查询时，通常会使用“限定（绑定）”或者“限制”这样的语言，例如本次示例就是限定“商品种类”对平均单价进行比较。
在细分的组内进行比较时，需要使用关联子查询。

关联子查询也是用来对集合进行切分的

换个角度来看，其实关联子查询也和 GROUP BY 子句一样，可以对集合进行切分。
image
image
我们首先需要计算各个商品种类中商品的平均销售单价，由于该单价会用来和商品表中的各条记录进行比较，因此关联子查询实际只能返回1行结果。这也是关联子查询不出错的关键。

关联子查询执行时， DBMS 内部的执行情况如图 5-8 所示,
image
关联子查询的内部执行结果对于初学者来说是比较难以理解的，但是像上图这样将其内部执行情况可视化之后，理解起来就变得非常容易了吧。

结合条件一定要写在子查询中

下面给大家介绍一下 SQL 初学者在使用关联子查询时经常犯的一个错误，那就是将关联条件写在子查询之外的外层查询之中。请大家看一下下面这条 SELECT 语句。
image
该SELECT语句会发生错误，不能正确执行。
允许存在这样的书写方法可能并不奇怪，但是SQL的规则禁止这样的书写方法。该书写方法究竟违反了什么规则呢？那就是关联名称的作用域。关联名称就是像P1、P2这样作为表别名的名称，作用域（scope）就是生存范围（有效范围）。也就是说，关联名称存在一个有效范围的限制。具体来讲，子查询内部设定的关联名称，只能在该子查询内部使用（图5-9）。换句话说，就是“内部可以看到外部，而外部看不到内部”。请大家一定不要忘记关联名称具有一定的有效范围。如前所述，SQL是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时只会留下执行结果，作为抽出源的 P2 表其实已经不存在了。因此，在执行外层查询时，由于P2表已经不存在了，因此就会返回“不存在使用该名称的表”这样的错误。
image

练习题

image

-- 创建视图的语句
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
 FROM Product
 WHERE sale_price >= 1000
 AND regist_date = '2009-09-20';

image
会发生错误。
解答
对视图的更新归根结底是对视图所对应的表进行更新。因此，该INSERT语句实质上和下面的 INSERT 语句相同。

INSERT INTO Product (product_id, product_name, product_type, sale_price,
purchase_price, regist_date)
 VALUES (NULL, '刀子', NULL, 300, NULL, '2009-11-02');

product_id（商品编号）、product_name（商品名称）、product_type（商品种类）3 列在表定义时都被赋予了 NOT NULL 约束(其实product_id（商品编号）是被赋予了主键约束，但其中默认包含了NOT NULL约束)。因此，向 product_id（商品编号）以及 product_type（商品种类）中插入 NULL 的 INSERT 语句是无法执行的。并且，INSERT语句中只对product_name（商品名称）、sale_price（销售单价）、regist_date（登记日期）3 列进行了赋值，所以剩余的列都会被自动插入 NULL，于是就发生了错误。

image

SELECT product_id,
 product_name,
 product_type,
 sale_price,
 (SELECT AVG(sale_price) FROM Product) AS sale_price_all
 FROM Product;

但是有没有读者会想到如下 SELECT 语句呢？

SELECT product_id,
 product_name,
 product_type,
 sale_price,
 AVG(sale_price) AS sale_price_all
 FROM Product;

上述SELECT语句会发生错误(虽然在MySQL中该SELECT语句不会发生错误，但毕竟这只是基于MySQL特定需求的结果，无法在其他的 DBMS中使用，并且得到的结果也完全不同)。原因在于 AVG 是一个聚合函数。正如 3-2 节说明的那样，使用聚合函数时对书写在 SELECT 子句中的要素有很多限制。使用了这种错误方法的读者请重新阅读一下 3-2 节中“常见错误① —— 在SELECT子句中书写了多余的列”部分的内容。

image

-- 创建视图的语句
CREATE VIEW AvgPriceByType AS
SELECT product_id,
 product_name,
 product_type,
 sale_price,
 (SELECT AVG(sale_price)
 FROM Product P2
 WHERE P1.product_type = P2.product_type
 GROUP BY P2.product_type) AS avg_sale_price
 FROM Product P1;

-- 删除视图的语句
DROP VIEW AvgPriceByType;

解答
在视图中包含的列中，除了avg_sale_price之外的4列（product_id、product_name、product_type、sale_price） 在Product表中都存在，因此可以直接读取。但是，最后的 avg_sale_price（平均销售单价）则必须使用关联子查询进行结算。使用标量子查询和关联子查询也可以创建出上述视图。

posted on 2022-10-28 17:58  朴素贝叶斯  阅读(114)  评论(0)  编辑  收藏  举报

导航