/*
下面是问题中的SELECT语句
*/
-- 确认视图内容
SELECT * FROM ViewPractice5_1;
/*
下面是解答示例
*/
-- 创建视图的语句
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM Product
WHERE sale_price >= 1000
AND regist_date = ‘2009-09-20‘;
View Code
/*
下面是问题中的SELECT语句
*/
-- 向视图中添加1行记录
INSERT INTO ViewPractice5_1 VALUES (‘刀‘, 300, ‘2009-11-02‘);
-- 实际上和下面的INSERT语句相同
INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES (NULL, ‘刀‘, NULL, 300, NULL, ‘2009-11-02‘);
/*
使用PostgreSQL时,需要在INSERT之前
执行如下语句将视图设定为可以更新
*/
CREATE OR REPLACE RULE insert_rule5_1
AS ON INSERT
TO ViewPractice5_1 DO INSTEAD
INSERT INTO Product (product_name, sale_price, regist_date)
VALUES (new.product_name, new.sale_price, new.regist_date);
/*
进行上述设定之后再次执行INSERT时会像下面这样由于NOT NULL约束而发生错误
postgres=# INSERT INTO ViewPractice5_1 VALUES (‘刀‘, 300, ‘2009-11-02‘);
ERROR: null value in column “product_id" violates not-null constraint
*/
View Code
SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price) FROM Product) AS sale_price_all
FROM Product;
-- 创建视图的语句
CREATE VIEW AvgPriceByType AS
SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price)
FROM Product P2
WHERE P1.product_type = P2.product_type
GROUP BY P1.product_type) AS avg_sale_price
FROM Product P1;
-- 确认视图内容
SELECT * FROM AvgPriceByType;
View Code