MySQL学习笔记四：视图的创建、更新与子查询

视图的创建

表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句，可以看成是一种临时表格，具有占用内存少、视图中的数据随着原表内容的更新而更新的特点。创建视图的格式为create view A as select…from B：

create view productsum (product_type,cnt_product) as select 
product_type,count(*) from product group by product_type;
SELECT * FROM shop.productsum;

注意应该避免在视图的基础上创建视图，多重视图会降低SQL性能：

create view p (product_type,cnt_product) as select 
product_type,cnt_product from productsum where product_type='办公用品';

定义视图时最好不要使用order by语句，虽然mysql可以执行：

create view p (product_type,cnt_product) as select 
product_type,cnt_product from productsum order by cnt_product desc;

视图的更新

满足以下条件时视图可以更新，且对应的表也会更新：

①SELECT子句中未使用DISTINCT；
②FROM子句中只有一张表；
③未使用GROUP BY子句；
④未使用HAVING子句；
⑤表格中除插入的值以外的值没有not null约束等。

比如上一节的productsum视图是根据group by语句创建出来的，因此我们插入值时会出现错误，因为不知道这5条电器商品的具体信息，无法在product表中更新：

insert into productsum values ('电器',5);-- 报错

在select语句中既没有聚合又没有分组语句时就可以更新视图。重新创建一个关于所有衣服信息的视图再插入数据：

create view productjim as select * from productins where product_type='衣服';
insert into productjim values('0004','内裤','衣服','950','1000',null);
-- 可以看到productjim视图（下图1）和productins表格（下图2）都增加了“内裤”记录

当然delete语句和update语句也可以用在productjim上，但是update的时候要注意原表中的约束。比如删除“内裤”数据，更改id为0003的商品注册日期：

delete from productjim where product_name='内裤';
-- 可以看到productjim视图和productins表格的“内裤”记录被删除了
update productjim set regist_date='2010-09-09' where product_id='0003';
-- 可以看到productjim视图和productins表格的“运动T恤”记录注册日期被改了
drop view productjim;-- 删除视图：

子查询

子查询也就是有多个select语句，内层select跟在外层from子句后面，末尾用as关键字来定义子查询名称。同时该名称是一次性的，不会像视图那样保存在硬盘之中，而是在select语句执行之后就消失了。下面用子查询的方法来创建第一节中的productsum视图：

select product_type,cnt_product from (select product_type,
count(*) as cnt_product from product group by product_type) as productsum;

子查询作为内层会先执行，但是注意要尽量避免使用多层嵌套的子查询。

标量子查询

标量子查询只能返回一行一列结果，也就是一个常数，因此几乎所有地方都能用。下面选取出销售单价高于所有商品平均价的商品（平均值为2097.5）：

select product_id,product_name,sale_price from product
 where sale_price>=(select avg(sale_price) from product);

找出销售单价高于所有商品平均价的商品类别：

select product_type,avg(sale_price) from product group by product_type
 having avg(sale_price)>=(select avg(sale_price) from product);

加入商品均价列：

select product_id,product_name,sale_price,(select avg(sale_price)
 from product) as avgprice from product;

难点：关联子查询

在细分的组内进行比较时可以使用，比如找出各种商品种类中高于该类平均售价的商品，如有三类商品就要返回3个平均值，因此使用上一节的标量子查询就不行了。

select product_type,product_name,sale_price from product as p1
where sale_price>(select avg(sale_price) from product as p2
 where p1.product_type=p2.product_type);

该条语句中，先执行内层select语句，p2视图呈现每一组类别的平均售价，一次返回一条（类）结果，然后外层select语句在该类的商品中逐个比较并呈现，如此往复。

同理我们可以在每条商品数据后面加上该类产品的平均售价：

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 salepriceall from product as p1;