复杂查询:练习题

本文内容

5.1 创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 Product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。

条件1: 销售单价大于等于 1000 日元。

条件2: 登记日期是 2009 年 9 月 20 日。

条件3: 包含商品名称、销售单价和登记日期三列。

对该视图执行 SELECT 语句的结果如下所示。

1
SELECT * FROM ViewPractice5_1;

执行结果

 product_name | sale_price | regist_date
--------------+------------+-------------
 T恤衫        |       1000 |  2009-09-20
 菜刀         |       3000 |  2009-09-20

答:

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

5.2 向习题 5.1 中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?

1
INSERT INTO ViewPractice5_1 VALUES ('刀子', 300, '2009-11-02');

答:会发生错误。

» 解答

对视图的更新归根结底是对视图所对应的表进行更新。因此,该 INSERT 语句实质上和下面的 INSERT 语句相同。

1
2
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 约束 1。因此,向 product_id(商品编号)以及 product_type(商品种类)中插入 NULLINSERT 语句是无法执行的。

并且,INSERT 语句中只对 product_name(商品名称)、sale_price(销售单价)、regist_date(登记日期)3 列进行了赋值,所以剩余的列都会被自动插入 NULL,于是就发生了错误。

5.3 请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。

执行结果

 product_id | product_name | product_type | sale_price |   sale_price_all
------------+--------------+--------------+------------+----------------------
 0001       | T恤衫        | 衣服         |       1000 | 2097.5000000000000000
 0002       | 打孔器       | 办公用品     |        500 | 2097.5000000000000000
 0003       | 运动T恤      | 衣服         |       4000 | 2097.5000000000000000
 0004       | 菜刀         | 厨房用具     |       3000 | 2097.5000000000000000
 0005       | 高压锅       | 厨房用具     |       6800 | 2097.5000000000000000
 0006       | 叉子         | 厨房用具     |        500 | 2097.5000000000000000
 0007       | 擦菜板       | 厨房用具     |        880 | 2097.5000000000000000
 0008       | 圆珠笔       | 办公用品     |        100 | 2097.5000000000000000

答:

1
2
3
4
5
6
SELECT product_id,
       product_name,
       product_type,
       sale_price,
       (SELECT AVG(sale_price) FROM Product) AS sale_price_all
  FROM Product;

» 解答

使用标量子查询来计算销售单价的平均值。由于平均销售单价是 2097.5 这样一个单值,可以确定为标量值,因此可以书写在 SELECT 子句之中。

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

1
2
3
4
5
6
SELECT product_id,
       product_name,
       product_type,
       sale_price,
       AVG(sale_price) AS sale_price_all
  FROM Product;

上述 SELECT 语句会发生错误 2。原因在于 AVG 是一个聚合函数。正如 对表进行分组 中说明的那样,使用聚合函数时对书写在 SELECT 子句中的要素有很多限制。使用了这种错误方法的读者请重新阅读一下 对表进行分组 中“常见错误 ① —— 在 SELECT 子句中书写了多余的列”部分的内容。

5.4 请根据习题 5.1 中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为 AvgPriceByType)。

执行结果

 product_id | product_name | product_type | sale_price |     avg_sale_price
------------+--------------+--------------+------------+----------------------
 0001       | T恤衫        | 衣服         |       1000 | 2500.0000000000000000
 0002       | 打孔器       | 办公用品     |        500 |  300.0000000000000000
 0003       | 运动T恤      | 衣服         |       4000 | 2500.0000000000000000
 0004       | 菜刀         | 厨房用具     |       3000 | 2795.0000000000000000
 0005       | 高压锅       | 厨房用具     |       6800 | 2795.0000000000000000
 0006       | 叉子         | 厨房用具     |        500 | 2795.0000000000000000
 0007       | 擦菜板       | 厨房用具     |        880 | 2795.0000000000000000
 0008       | 圆珠笔       | 办公用品     |        100 |  300.0000000000000000

**提示:**其中的关键是 avg_sale_price 列。与习题 5.3 不同,这里需要计算出的是各商品种类的平均销售单价。这与 关联子查询 中使用关联子查询所得到的结果相同。也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

答:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 创建视图的语句
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;
1
2
-- 删除视图的语句
DROP VIEW AvgPriceByType;

» 解答

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

请参阅#

(完)


  1. 其实 product_id(商品编号)是被赋予了主键约束,但其中默认包含了 NOT NULL 约束。 ↩︎

  2. 虽然在 MySQL 中该 SELECT 语句不会发生错误,但毕竟这只是基于 MySQL 特定需求的结果,无法在其他的 DBMS 中使用,并且得到的结果也完全不同。 ↩︎

comments powered by Disqus

本文内容