5 SQL 复杂查询

 

5 复杂查询

5-1 视图

究竟视图是什么呢？如果用一句话概述的话，就是“从SQL的角度来看视图就是一张表”。实际上，在SQL语句中并不需要区分哪些是表，哪些是视图。

 

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

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

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

 

视图的优点大体有两点：第一点是由于视图无需保存数据，因此可以节省存储设备的容量。第二个优点就是可以将频繁使用的select语句保存成视图，这样就不用每次都重新书写了。而且还可以大大提高效率。而且视图中的数据会随着原表的变化自动更新。

法则5-1

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

 

法则5-2

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

 

创建视图的方法

/*

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

*/

 

DELETE FROM shohin;

 

SELECT * FROM shohin;

 

INSERT INTO shohin VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');

INSERT INTO shohin VALUES ('0002', '打孔器', '办公用品', 500, 300, '2009-09-11');

INSERT INTO shohin VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);

INSERT INTO shohin VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT INTO shohin VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

INSERT INTO shohin VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');

INSERT INTO shohin VALUES ('0007', '擦菜饭', '厨房用具', 880, 790, '2008-04-28');

INSERT INTO shohin VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

 

CREATE VIEW shohinSum (shohin_bunrui, cnt_shohin) AS

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;

 

SELECT * FROM shohinSum;

 

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

首先执行定义视图的SELECT语句，根据得到的结果，再执行在FROM子句中使用视图的SELECT语句。

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

 

多重视图。

使用视图的查询

CREATE VIEW shohinSumJim (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, cnt_shohin

FROM shohinSum WHERE shohin_bunrui = '办公用品';

 

虽然语法上没有错误，但是我们还是应该尽量避免在视图的基础上创建视图。这是因为对于多数DBMS来说，多重视图会降低SQL的性能。

法则5-3

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

 

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

 

法则5-4

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

 

法则5-5

视图和表需要同时进行更新，因此通过聚合得到的视图无法进行更新。

 

删除视图

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

 

DROP VIEW shohinSum;

 

 

5-2 子查询

我们先来复习一下视图的概念，视图并不是用来保存数据的，而是通过保存读取数据的SELECT语句的方法来为用户提供便利的工具。反之，子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中。

子查询和视图

CREATE VIEW shohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;

 

-- 下面代码也能实现此功能  这里的内层 SELECT子句为 FROM子句中的select子句

SELECT shohin_bunrui, cnt_shohin FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM shohin GROUP BY shohin_bunrui) AS shohinSum;

 

/*

首先执行from子句中的select语句（子查询）

SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM shohin GROUP BY shohin_bunrui;

根据上面的结果执行外层的select语句

SELECT shohin_bunrui, cnt_shohin FROM shohinSum;

*/

 

SELECT shohin_bunrui, cnt_shohin FROM shohinSum;

 

SELECT shohin_bunrui, cnt_shohin FROM (SELECT * FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM shohin GROUP BY shohin_bunrui) AS shohinSum WHERE cnt_shohin = 4) AS shohinSum2;

 

法则5-6

子查询作为内层查询会首先执行。

 

子查询的名称：原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。为子查询设定名称时需要使用AS关键字，该关键字有时也可以省略。

 

标量就是单一的意思，在数据库之外的领域也经常使用。

 

标量子查询则有一个特殊的限制，那就是必须而且只能返回1行1列的结果。

标量子查询（scalar subquery）

-- 在where子句中使用标量子查询

 

-- 在where子句中不能使用聚合函数

-- 虽然这样的select语句看上去能够满足我们的要求，但是由于在where子句中不能使用聚合函数，因此这样的select语句是错误的。

SELECT shohin_id, shohin_mei, hanbai_tanka FROM shohin WHERE hanbai_tanka > AVG(hanbai_tanka);

 

-- 可以使用下面的代码实现

-- 计算平均销售单价的标量子查询

SELECT AVG(hanbai_tanka) FROM shohin;

SELECT shohin_id, shohin_mei, hanbai_tanka FROM shohin WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM shohin);

 

 

-- 在SELECT子句中使用标量子查询

SELECT shohin_id, shohin_mei, hanbai_tanka, (SELECT AVG(hanbai_tanka) FROM shohin) AS avg_tanka FROM shohin;

 

SELECT shohin_bunrui, AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui;

 

-- 在HAVING子句中使用标量子查询

SELECT shohin_bunrui, AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) > (SELECT AVG(hanbai_tanka) FROM shohin);

 

法则5-7

标量子查询就是返回单一值的子查询

 

标量子查询的书写位置并不仅仅局限于WHERE子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是SELECT子句，GROUP BY子句，HAVING子句，还是ORDER BY子句，几乎所有的地方都可以使用。

 

特别需要注意的是该子查询绝对不能返回多行结果。也就是说如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了。

5-3 关联子查询

在WHERE子句中使用子查询时，该子查询的结果必须是单一的。

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

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

SELECT AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui;

 

-- 发生错误的子查询

SELECT shohin_id, shohin_mei, hanbai_tanka FROM shohin WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)

FROM shohin GROUP BY shohin_bunrui);

 

-- 正确的关联子查询书写方法

SELECT shohin_id, shohin_mei, hanbai_tanka FROM shohin AS S1 WHERE hanbai_tanka >

(SELECT AVG(hanbai_tanka) FROM shohin AS S2 WHERE S1.shohin_bunrui = S2.shohin_bunrui GROUP BY shohin_bunrui);

 

-- 错误的关联子查询书写方法：将关联条件移到子查询之外 该书写方法究竟违法了什么规则呢？那就是关联名称的作用域。也就是说关联名称存在一个有效作用域的限制。

SELECT shohin_bunrui, shohin_mei, hanbai_tanka FROM shohin AS S1 WHERE S1.shohin_bunrui = S2.shohin_bunrui AND hanbai_tanka >

(SELECT AVG(hanbai_tanka) FROM shohin AS S2 GROUP BY shohin_bunrui);

-- 正确的关联子查询书写方法

SELECT shohin_bunrui, shohin_mei, hanbai_tanka FROM shohin AS S1 WHERE hanbai_tanka >

(SELECT AVG(hanbai_tanka) FROM shohin AS S2 WHERE S1.shohin_bunrui = S2.shohin_bunrui GROUP BY shohin_bunrui);

 

这里起到关键作用的就是在子查询中添加的WHERE子句的条件。

 

请大家一定不要忘记关联名称具有一定的有效作用域。如前所述，SQL是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时会留下执行结果，作为抽出源的S2表其实已经不存在了。因此，在执行外层查询时，由于S2表已经不存在了，就会返回“不存在使用该名称的表”这样的错误。

法则5-8

在细分的组内进行比较时，需要使用关联子查询。