sql初尝试（2）

USE shop;
CREATE VIEW ViewPratice5_1(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT product_name,sale_price,regist_date
FROM product
WHERE sale_price>=1000
AND regist_date='2009-09-20';

这样系统会报错，create与select的个数不对应

USE shop;
CREATE VIEW ViewPratice5_1(product_name,sale_price,regist_date)
AS
SELECT product_name,sale_price,regist_date
FROM product
WHERE sale_price>=1000
AND regist_date='2009-09-20';
这样就创建好视图，再可

 

USE shop;
SELECT * FROM ViewPratice5_1;

USE shop;
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
SELECT * FROM ViewPratice5_1;

并不会插入数据，而会报错。因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底
层基本表的定义才能成功修改。

注意AVG（平均值）用法效果是一个数而非一列

USE shop;
ALTER TABLE product ADD COLUMN sale_price_all VARCHAR(12)

USE shop;
UPDATE product
SET sale_price_all=AVG(sale_price)

update为聚合函数，聚合函数不允许出现嵌套

SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product
ORDER BY product_id;

 

如果是分组求平均值后插入数据则需要用关联子查询

USE shop;
SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type=p2.product_type) AS avg_price
FROM product AS p1
ORDER BY product_id;