MySQL燕十八老师课程笔记：第十七课：视图

# 查询每个栏目下商品的平均价格，并取平均价前3高的栏目
select cat_id,avg(shop_price) as pj 
from goods 
group by cat_id 
order by pj desc limit 3;

# 取平均价格前3低的：
select cat_id,avg(shop_price) as pj 
from goods 
group by cat_id 
order by pj asc limit 3;

# 平均价由高到低，第3到第5名的栏目
select cat_id,avg(shop_price) as pj 
from goods 
group by cat_id 
order by pj desc limit (3,2);

在这个例子中，由于 group by cat_id， 查询出每个栏目下的平均价，频繁用到，设为结果集A，无论是想查前3高，还是前3低，都要用到结果集A。因此，可以把结果保存一张表，下次来查这张表但是，如果goods表又添加了商品，A结果集就与当时保存的临时表不一样了，这时，我们可以用视图来解决。

view：可以看作是一张虚拟表，是表通过某种运算得到的一个投影。

如何创建视图？建视图的时候，我们要指定视图的列名和列类型吗？
答：不需要，它只是个影子，继承了上面的字段，只是一种关系而已。

既然视图只是表的某种查询的投影，所以主要步骤在于查询表上，查询的结果命名为视图就可以了。

创建视图的语法：
create view 视图名 as
selelct 语句

create view stats
as 
select cat)id,avg(shop_price) as pd
from goods
group by cat_id;

然后就会发现数据库中多了一张stats 表，视图中放置的就是上句中的查询结果。
视图一旦创建完毕，就可以当做表来查询。

视图的用处：

1. 可以简化我们的查询：比如，复杂的统计时，先用视图生成一个中间结果，再查询视图。
   2.更精细的权限控制：比如某张表，用户表为例；
   两个网站搞合作，可以查询对方网站的用户，需要向对方开放用户表的权限，但是又不想开放用户表中的密码字段：

create view vuser
as
select user_id,username,email from user;
# 以此开放这个视图的权限给对方

3.数据多，分表时可以用到：比如，小说站，article表，1000多万篇，分成article，article2，article3，，，，article5 这5张表，每张表放200万条。调查小说时，不知在哪张表。

create view article as
select title from article1 union select title from article2....
 union select title from article5;

视图是表的一个影子，表与视图之间的关系：表的数据变化，会影响到视图的变化。
问：视图如果变了，表如何变？

答：由最上方的例子而言：平均价来自多行的shop_price的计算结果，如果 pj’ 列的值的变了，映射过去，到底修改哪几行shop_price？
在某种情况下，视图也是可以修改的。
要求：视图的数据和表的数据1 1对应，就像函数的映射。还需满足一些其他条件才行。

视图的定义是一直存在的，视图并不会占用空间。

对于表：一般由三个文件：.frm ——代表表的结构；.MYD——代表表的数据；.MYI——代表表的索引
对于视图：只拥有.frm文件，只拥有结构。

删除视图：drop

algorithm=merge 合并 / temptable 临时表 / undefined 未定义

merge ： 当引用视图时，引用试图的语句与定义试图的语句合并；
temptable：当引用视图时，根据视图的创建语句建立一个临时表；
undefine：未定义，自动，让系统帮你选。

merge：意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句（比如where那些）与where子句等合并，分析形成一条select语句。
例：创建视图的语句

create algorithm= merge view g2 as 
select goods_id,cat_id,goods_name,shop_price 
from goods where shop_price>2000;

查询视图的语句：

select * from g2 where shop_price<3000;

最终执行的语句：

select goods_id,cat_id,goods_name,shop_price 
from goods where shop_price>2000 and shop_price<3000;

虽然从结果上看不出区别，但是g2视图，并没有建立临时表。

而temptable 是根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表查数据。

create algorithm=temptable view g2 as 
select goods_id,cat_id,goods_name,shop_price 
from goods where shop_price>2000;

查询视图的语句：

select * from g2 where shop_price<3000;

综上：比较简单的查询语句，用merge就行；比较麻烦的查询就用temptable；