PostgreSQL 视图练习

练习来源于《SQL基础教程（第2版）》

5.1 创建初满足下述三个条件的视图（视图名称为ViewPractice5_1)

shop=# create view ViewPractice5_1
shop-# as
shop-# select product_type,sale_price,regist_date
shop-# from Product
shop-# where (sale_price >= 1000) and (regist_date = '2009-09-20');

5.2 练习答案

shop=# insert into ViewPractice5_1 values ('刀子',300,'2009-11-02');
错误:  在字段 "product_id" 中空值违反了非空约束
描述:  失败, 行包含(null, null, 刀子, 300, null, 2009-11-02).

5.3 练习答案

shop=# select product_id,product_name,product_type,sale_price,
shop-# (select avg(sale_price) as sale_price_all from Product)
shop-# from Product;
 product_id | product_name | product_type | sale_price |    sale_price_all
------------+--------------+--------------+------------+-----------------------
 0001       | T恤衫        | 衣服         |       1000 | 1541.6666666666666667
 0002       | 打孔器       | 办公用品     |        500 | 1541.6666666666666667
 0003       | 运动T恤      | 衣服         |       1000 | 1541.6666666666666667
 0004       | 菜刀         | 厨房用具     |       3000 | 1541.6666666666666667
 0005       | 高压锅       | 厨房用具     |       6800 | 1541.6666666666666667
 0006       | 叉子         | 厨房用具     |        500 | 1541.6666666666666667
 0007       | 擦菜板       | 厨房用具     |        880 | 1541.6666666666666667
 0008       | 圆珠笔       | 办公用具     |        100 | 1541.6666666666666667
 0009       | 印章         | 办公用品     |         95 | 1541.6666666666666667
(9 行记录)

5.4 练习答案

shop=# create view AvgPriceByType as
shop-# select product_id,product_name,product_type,sale_price,
shop-# (select avg(sale_price) as avg_sale_price from Product)
shop-# from Product;
CREATE VIEW
shop=# select * from AvgPriceByType ;
 product_id | product_name | product_type | sale_price |    avg_sale_price
------------+--------------+--------------+------------+-----------------------
 0001       | T恤衫        | 衣服         |       1000 | 1541.6666666666666667
 0002       | 打孔器       | 办公用品     |        500 | 1541.6666666666666667
 0003       | 运动T恤      | 衣服         |       1000 | 1541.6666666666666667
 0004       | 菜刀         | 厨房用具     |       3000 | 1541.6666666666666667
 0005       | 高压锅       | 厨房用具     |       6800 | 1541.6666666666666667
 0006       | 叉子         | 厨房用具     |        500 | 1541.6666666666666667
 0007       | 擦菜板       | 厨房用具     |        880 | 1541.6666666666666667
 0008       | 圆珠笔       | 办公用具     |        100 | 1541.6666666666666667
 0009       | 印章         | 办公用品     |         95 | 1541.6666666666666667
(9 行记录)

ps:这里我用的是标量子查询，其实还可以用关联子查询

shop=# create view AvgPriceByType1 as
shop-# select product_id,
shop-# product_name,
shop-# product_type,
shop-# sale_price,
shop-# (select avg(sale_price)
shop(#  from Product P2
shop(# where P1.product_type = P2.product_type
shop(# group by P2.product_type) as avg_sale_price
shop-# from Product P1;
CREATE VIEW
shop=# select * from AvgPriceByType1;
 product_id | product_name | product_type | sale_price |    avg_sale_price
------------+--------------+--------------+------------+-----------------------
 0001       | T恤衫        | 衣服         |       1000 | 1000.0000000000000000
 0002       | 打孔器       | 办公用品     |        500 |  297.5000000000000000
 0003       | 运动T恤      | 衣服         |       1000 | 1000.0000000000000000
 0004       | 菜刀         | 厨房用具     |       3000 | 2795.0000000000000000
 0005       | 高压锅       | 厨房用具     |       6800 | 2795.0000000000000000
 0006       | 叉子         | 厨房用具     |        500 | 2795.0000000000000000
 0007       | 擦菜板       | 厨房用具     |        880 | 2795.0000000000000000
 0008       | 圆珠笔       | 办公用具     |        100 |  100.0000000000000000
 0009       | 印章         | 办公用品     |         95 |  297.5000000000000000
(9 行记录)

小结

视图就是在已有的表上再进行一次排列筛选出符合条件的表，标量查询一定要在select关键词后使用，如果使用的是多行或者直接在外层查询里放聚合函数就会报错；