运行环境: postgreSQL。

第五章:复杂查询

视图

从 SQL 的角度视图就是一张表,使用表保存数据时,数据实际上会被保存到计算机的存储设备,而视图不会将数据保存到其它任何地方,实际上保存的是 SELECT 语句,从视图读取数据时,视图会在内部执行该 SELECT 语句并创建一张临时表。

视图的优点:

  • 视图无需保存数据,节省存储设备的容量。
  • 可以将频繁使用的 SELECT 保存成视图,方便重复调用。

创建视图的方法

创建视图使用 CREATE VIEW 语句:

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

SELECT 语句中列的排列顺序和视图中的排列顺序相同,示例:

1
2
3
4
5
CREATE VIEW ProductSum (product_type, cnt_product) AS 
	SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
	
--使用视图,视图和表一样,可以书写在 SELECT 的 FROM 子句中
SELECT product_type, cnt_product FROM ProductSum;

使用视图的查询

  1. 首先执行定义的 SELECT 语句;
  2. 根据得到的结果,再执行 FROM 子句中使用视图的 SELECT 语句;

由于可能出现以视图为基础创建视图的多重视图,因此使用视图查询通常需要执行2条以上的 SELECT 语句,以下为通过 ProductSum 为基础创建的视图 ProductSumJim:

1
2
3
4
5
6
CREATE VIEW ProductSumJim (product_type, cnt_product) AS
	SELECT product_type, cnt_product FROM ProductSum
	WHERE product_type = '办公用品';
	
--确认创建的视图
SELECT * FROM ProductSumJim;

对多数 DBMS 来说,由于多重视图会降低 SQL 的性能,因此应该尽量避免创建多重视图。

视图的限制

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

视图和表一样,数据行都是没有顺序的,因此即使类似 PostgreSQL 中是允许这种语法的,但不应该使用。

对视图进行更新

标准 SQL 中规定,如果定义视图的 SELECT 语句能够满足某些条件,视图可以更新数据,如:

  • SELECT 子句未使用 DISTINCT
  • FROM 子句中只有一张表
  • 未使用 GROUP BY 子句
  • 未使用 HAVING 子句

这是由于当使用了某些条件的视图进行数据更新时,会产生歧义,数据库不清楚如何更新原表中的数据,如下通过 ProductSum 插入数据:

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

当将视图映射到表时,数据库不清楚诸如商品编号、商品名称等列的数据,因此发生错误:

image-20191125133408466

下面为能够更新表数据的视图:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date) AS 
-- 既没有聚合又没有结合的 SELECT 语句
	SELECT * FROM Product WHERE product_type = '办公用品';
	
-- 插入数据
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

-- 当使用 PostgreSQL 时,其视图设定为只读,因此需要以下语句来允许更新操作
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);                                                                           

删除视图

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

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

示例,删除视图 ProductSum:

1
2
3
4
DROP VIEW ProductSum;

--使用 CASCADE 删除关联视图
DROP VIEW ProductSum CASCADE;

子查询

子查询和视图

子查询就是一次性视图,是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

以下是视图 ProductSum 和视图所对应的 SELECT 语句:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE VIEW ProductSum(product_type, cnt_product) AS 
 SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
 
-- 确认创建好的视图
SELECT product_type, cnt_product FROM ProductSum;

-- 子查询
SELECT product_type, cnt_product
	FROM (SELECT product_type, COUNT(*) AS cnt_product 
        	FROM Product
       GROUP BY product_type) AS ProductSum;
-- 注意:Oracle 中 FROM 不能使用 AS,因此需要将") AS ProductSum" 改为 ") ProductSum";

实际上,该 SELECT 语句首先执行 FROM 子句中的 SELECT 语句,再执行外层的 SELECT 语句。

增加子查询的层数

子查询在原则上没有限制:

1
2
3
4
5
6
SELECT product_type, cnt_product
	FROM (SELECT * 
        FROM (SELECT product_type, COUNT(*) AS cnt_product 
              FROM Product 								
              GROUP BY product_type) AS ProductSum
				WHERE cnt_product = 4) AS ProductSum2;

嵌套子查询增加了 SQL 语句的可读性和性能,因此应尽量避免多层嵌套子查询。

子查询的名称

原则上必须使用 AS 关键字给子查询设定名称。

标量子查询

标量即单一的意思,标量子查询必须而且只能返回 1 行 1 列的结果,也就是返回表中某一行的某一列的值,因此,标量子查询的返回值可以用在 = 或者 <> 这种需要单一值比较运算符之中。

在 WHERE 子句中使用标量子查询

在 WHERE 子句中不能使用聚合函数,因此当需要在 WHERE 子句中使用聚合函数查询时,就可以使用标量子查询,如下是求出销售单价高于销售单价的商品:

1
2
3
4
5
-- 计算平均销售单价的标量子查询
SELECT AVG(sale_price) FROM Product;

SELECT product_id, product_name, sale_price FROM Product
	WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

标量子查询的书写位置

标量子查询可以书写在任何可以使用单一值的位置,即能够使用常数或者列名的地方,无论是 SELECT 子句、GROUP BY 子句、HAVING 子句或者 ORDER BY 子句,几乎都可以使用。如下:

1
2
3
4
5
6
7
8
9
-- 在 SELECT 子句使用标量子查询
SELECT product_id, product_name, sale_price, 
	(SELECT AVG(sale_price) FROM Product) AS avg_price 
	FROM Product;

-- 在 HAVING 子句使用标量子查询,选取出按照商品种类计算出的销售单价高于全部商品的平均销售单价的商品种类
SELECT product_type, AVG(sale_price) 
	FROM Product GROUP BY product_type 
	HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);

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

标量子查询绝对不能返回多行结果,当子查询返回了多行结果,其就是一个普通的子查询了,诸如 = 或者 <> 等一系列需要单一输入值的运算符无法与其进行运算。

关联子查询

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

关联子查询会在细分的组内进行比较时使用,举个例子,使用子查询选取出各商品种类中高于该商品种类的平均销售单价的商品,分为以下几个步骤:

  1. 按照商品种类计算平均价格

    1
    
    SELECT AVG(sale_price) FROM Product GROUP BY product_type;
  2. 使用关联子查询按照商品种类与平均销售单价进行比较

    1
    2
    3
    4
    5
    6
    
    SELECT product_type, product_name, sale_price
    	FROM Product AS P1	WHERE sale_price > (SELECT AVG(sale_price)
                                         		FROM Product AS P2
                                  WHERE P1.product_type = P2.product_type
                                         		GROUP BY product_type);
    -- 使用 "P1.product_type = P2.product_type"这个条件,使得 AVG 函数按照商品种类进行平均计算,因此可以省略 GROUP BY 子句

在子查询中添加的 WHERE 子句条件起到关键作用。由于比较的是同一张 Product 表,因此使用了别名 P1 和 P2。

当使用关联子查询时,都要以”<表明>.<列名>“形式指定对应的数据。

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

关联子查询在某个角度看和 GROUP BY 子句一样可以对集合进行切分。如上述的例子,关联子查询实际只能返回1行结果,因此可以用来进行比较,当商品种类发生变化后,用来进行比较的平均单价也会发生变化。

image-20191126230518809

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

以下的 SQL 语句存在明显错误,关联名称是存在作用域的(scope)

1
2
3
4
5
6
-- 错误的关联子查询书写方法 
SELECT product_type, product_name, sale_price
	-- 在该作用域不存在 P2
	FROM Product AS P1 WHERE P1.product_type = P2.product_type 
		AND sale_price > 
		(SELECT AVG(sale_price) FROM Product AS P2 GROUP BY product_type);

P1, P2 作用域如下所示:

image-20191126231008295