【数据库】复杂查询：视图，子查询，关联子查询

5.1视图
视图：1张临时的表
优点：1.无需保存数据，节省容量；2.频繁使用的SELECT语句可以保存为视图，提高效率。
5.2创建视图

postgres=# CREATE VIEW ProductSum(product_type,cnt_product)
postgres-# AS
postgres-# SELECT product_type,COUNT(*)
postgres-# FROM Product
postgres-# GROUP BY product_type;
CREATE VIEW

注：不要忘记AS
5.4以视图创建视图

postgres=# CREATE VIEW ProductSumJim(product_type,cnt_product)
postgres-# AS
postgres-# SELECT product_type,cnt_product
postgres-# FROM ProductSum
postgres-# WHERE product_type='办公用品';
CREATE VIEW
postgres=# SELECT * FROM ProductSumJim;
 product_type | cnt_product
--------------+-------------
 办公用品     |           2
(1 行记录)

注：1.定义视图时不要使用ORDER BY语句；2.视图可以更新的条件，视图和原表要保证同步，例如汇总后得到的视图不可以更新
5.5 可以向视图添加数据的情况

postgres=# CREATE VIEW ProductJim(product_id,product_name,product_type,sale_price,purchase_price,regist_date)
postgres-# AS
postgres-# SELECT * FROM Product
postgres-# WHERE product_type='办公用品';
CREATE VIEW
postgres=# INSERT INTO ProductJim VALUES('0009','印章','办公用品',95,10,'2009-11-30');
INSERT 0 1

5.7 删除视图

postgres=# DROP VIEW ProductSum;
错误:  无法删除 视图 productsum 因为有其它对象倚赖它
描述:  视图 productsumjim 倚赖于 视图 productsum
提示:  使用 DROP .. CASCADE 把倚赖对象一并删除.
postgres=# DROP VIEW ProductSum CASCADE;
注意:  递归删除 视图 productsumjim
DROP VIEW

5.2 子查询
子查询：一次性视图
5.9 子查询

postgres=# SELECT product_type,cnt_product
postgres-# FROM(SELECT product_type,COUNT(*) AS cnt_product
postgres(# FROM Product
postgres(# GROUP BY product_type) AS ProductSum;
 product_type | cnt_product
--------------+-------------
 衣服         |           2
 办公用品     |           2
 厨房用具     |           4
(3 行记录)

5.10 子循环可嵌套

postgres=# SELECT product_type,cnt_product
postgres-# FROM (SELECT *
postgres(# FROM (SELECT product_type,COUNT(*) AS cnt_product
postgres(# FROM Product
postgres(# GROUP BY product_type) AS ProductSum
postgres(# WHERE cnt_product=4) AS ProductSum2;
 product_type | cnt_product
--------------+-------------
 厨房用具     |           4
(1 行记录)

注：第三行括号前的空格

标量子查询：返回一行一列的数据
5.12 选出销售单价高于平均单价的情况

postgres=# SELECT product_name,sale_price
postgres-# FROM Product
postgres-# WHERE sale_price>(SELECT AVG(sale_price) FROM Product);
 product_name | sale_price
--------------+------------
 运动T恤      |       4000
 菜刀         |       3000
 高压锅       |       6800
(3 行记录)

注：WHERE不能使用聚合函数
5.13 在SELECT子句中使用标量子查询

postgres=# SELECT product_id,
postgres-# product_name,sale_price,(SELECT AVG(sale_price) FROM Product) AS avg_price
postgres-# FROM Product;
 product_id | product_name | sale_price |       avg_price     
------------+--------------+------------+-----------------------
 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
(8 行记录)

5.3 关联子查询
？5.16 通过关联子查询寻找每个种类中大于本类平均单价的项目

postgres=# SELECT product_type,product_name,sale_price
postgres-# FROM Product AS P1
postgres-# WHERE sale_price>(SELECT AVG(sale_price)
postgres(# FROM Product AS P2
postgres(# WHERE P1.product_type=P2.product_type
postgres(# GROUP BY product_type);
 product_type | product_name | sale_price
--------------+--------------+------------
 办公用品     | 打孔器       |        500
 衣服         | 运动T恤      |       4000
 厨房用具     | 菜刀         |       3000
 厨房用具     | 高压锅       |       6800
(4 行记录)