SQL笔记#复杂查询

一、视图

1、视图和表

使用试图时会执行SELECT语句并创建一张临时表。视图中保存的是SELECT语句；表中保存的是实际数据。

2、创建视图的方法

CREATE VIEW 视图名称(<视图列名1>，<视图列名2>，……)

AS

<SELECT语句>

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)FROM ProductGROUP BY product_type;

SELECT product_type, cnt_productFROM ProductSum;

 product_type | cnt_product
--------------+-------------厨房用具     |           4衣服         |           2办公用品     |           2

还可以以视图为基础创建视图，但多重视图会降低SQL的性能。

-- 在视图的基础上创建视图
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_productFROM ProductSumWHERE product_type = '办公用品';-- 确认创建好的视图
SELECT product_type, cnt_productFROM ProductSumJim;

 product_type | cnt_product
--------------+-------------办公用品     |           2

3、视图的限制1(定义视图时不能使用ORDER BY子句)

4、视图的限制2(对视图进行更新）

$\blacksquare$ 通过汇总得到的表无法更新

$\blacksquare$ 能够更新的情况(既没有聚合有没有结合)

CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS 
SELECT *FROM ProductWHERE product_type = '办公用品';

-- 向视图中添加数据行
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');--  确认数据是否已经添加到视图中
SELECT * FROM ProductJim;-- 确认数据是否已经添加到原表中
SELECT * FROM Product;

postgres=# SELECT * FROM ProductJim;product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-110008       | 圆珠笔       | 办公用品     |        100 |                | 2009-11-110009       | 印章         | 办公用品     |         95 |             10 | 2009-11-30
(3 行记录)postgres=# SELECT * FROM Product;product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-200002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-110003       | 运动T恤      | 衣服         |       4000 |           2800 |0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-200005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-150006       | 叉子         | 厨房用具     |        500 |                | 2009-09-200007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-280008       | 圆珠笔       | 办公用品     |        100 |                | 2009-11-110009       | 印章         | 办公用品     |         95 |             10 | 2009-11-30
(9 行记录)

5、删除视图

-- 删除视图
DROP VIEW ProductSum;-- 由于存在关联，所以可能会报错,可以采用下列方式/* 
DROP VIEW ProductSum CASCADE;
*/

-- 删除商品为0009(印章)的数据
DELETE FROM Product WHERE product_id = '0009';

二、子查询

1、子查询和视图

视图的方式：

-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)FROM ProductGROUP BY product_type;-- 确认创建好的视图
SELECT product_type, cnt_productFROM ProductSum;

子查询的方式：1、内层的查询，FROM子句中的SELECT子句；2、外层的查询，SELECT子句。

SELECT product_type, cnt_productFROM (SELECT product_type, COUNT(*) AS cnt_productFROM ProductGROUP BY product_type) AS ProductSum;

$\blacksquare$ 增加子查询的层数

-- 1、统计不同商品种类的数量
-- 2、选取商品数量为4的数据
-- 3、将选取的相关数据进行汇总--SQL Server, DB2, PostgreSQL, MySQL
SELECT product_type, cnt_productFROM (SELECT *FROM (SELECT product_type, COUNT(*) AS cnt_productFROM ProductGROUP BY product_type) AS ProductSumWHERE cnt_product = 4) AS ProductSum2;

 product_type | cnt_product
--------------+-------------厨房用具     |           4

2、子查询的名称

之前的例子给子查询设定了ProductSum等名称，原则上要给子查询设定名称。

3、标量子查询

$\blacksquare$ 什么是标量

标量就是单一的意思，必须且只能返回1行1列的结果。

$\blacksquare$ 在WHERE子句中使用标量子查询

-- 在WHERE子句中不能使用聚合函数,比如下面的代码存在错误
SELECT product_id,product_name,sale_priceFROM ProductWHERE sale_price>AVG(sale_price);

postgres=# SELECT AVG(sale_price)
postgres-#      FROM Product;avg
-----------------------2097.5000000000000000

1、内层的子查询：得到括号内的内容为2097.5；

2、外层的查询：选取售价大于该值的数据信息。

-- 其中对于条件的选取采用标量子查询
SELECT product_id, product_name, sale_priceFROM ProductWHERE sale_price > (SELECT AVG(sale_price)FROM Product);

 product_id | product_name | sale_price
------------+--------------+------------0003       | 运动T恤      |       40000004       | 菜刀         |       30000005       | 高压锅       |       6800

4、标量子查询的书写位置

无论是SELECT子句、GROUP BY子句、HAVING子句，还是ORDER BY子句，几乎所有的地方都可以使用。

-- 在SELECT子句中使用标量子查询
SELECT product_id, product_name, sale_price,(SELECT AVG(sale_price)FROM Product) AS avg_priceFROM Product;

 product_id | product_name | sale_price |       avg_price
------------+--------------+------------+-----------------------0001       | T恤          |       1000 | 2097.50000000000000000002       | 打孔器       |        500 | 2097.50000000000000000003       | 运动T恤      |       4000 | 2097.50000000000000000004       | 菜刀         |       3000 | 2097.50000000000000000005       | 高压锅       |       6800 | 2097.50000000000000000006       | 叉子         |        500 | 2097.50000000000000000007       | 擦菜板       |        880 | 2097.50000000000000000008       | 圆珠笔       |        100 | 2097.5000000000000000

-- 在HAVING子句中使用标量子查询
SELECT product_type, AVG(sale_price)FROM ProductGROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)FROM Product);

 product_type |          avg
--------------+-----------------------厨房用具     | 2795.0000000000000000衣服         | 2500.0000000000000000

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

在使用子查询时，要注意改子查询不能返回多行结果；例如下述的SELECT子查询会发生错误。

SELECT product_id, product_name, sale_price,(SELECT AVG(sale_price)FROM ProductGROUP BY product_type) AS avg_priceFROM Product;

三、关联子查询

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

$\blacksquare$ 按照商品种类与平均销售单价进行比较

-- 按照商品种类计算平均价格
SELECT AVG(sale_price)FROM ProductGROUP BY product_type;-- 发生错误的子查询，因为返回的值不止一个
SELECT product_id, product_name, sale_priceFROM ProductWHERE sale_price > (SELECT AVG(sale_price)FROM ProductGROUP BY product_type);

$\blacksquare$ 使用关联子查询的解决方案

SELECT product_type, product_name, sale_priceFROM Product AS P1WHERE sale_price > (SELECT AVG(sale_price)FROM Product AS P2WHERE P1.product_type = P2.product_typeGROUP BY product_type);

 product_type | product_name | sale_price
--------------+--------------+------------办公用品     | 打孔器       |        500衣服         | 运动T恤      |       4000厨房用具     | 菜刀         |       3000厨房用具     | 高压锅       |       6800

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

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

关联条件移到子查询之外是错误的。内部可以看到外部，外部看不到内部。注意作用域！！！

-- 错误的关联子查询书写方式
SELECT product_type,product_name,sale_priceFROM Product AS P1WHERE P1.product_type=P2.product_typeAND sale_price>(SELECT AVG(sale_price)FROM Product AS P2GROUP BY product_type);