5 复杂查询

法则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;

使用视图的查询

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

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

法则5-3

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

法则5-4

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

法则5-5

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

删除视图

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

DROP VIEW shohinSum;

子查询和视图

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

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

标量子查询（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

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

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

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

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);

法则5-8

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