在前面的查询中经常把查询结果当成临时表来看,视图(view)则可以当成一张临时表
例如查询每个栏目下商品的平均价格,并取出平均价格前三低的栏目:
select cat_id,avg(shop_price) from goods group by cat_id order by avg(shop_price) limit 3;
查询每个栏目下商品的平均价格,并取出平均价格 4-9 的栏目:
select cat_id,avg(shop_price) from goods group by cat_id order by avg(shop_price) limit 3,5;
上面重复用到了select cat_id,avg(shop_price) from goods group by cat_id
的结果集 A ,那么可以将其看成一张临时表,这就引入了 view 的概念。视图 view 的简历不需要指定视图的列名与类型,他会继承原来的表。视图是表的某种查询的投影,所以主要步骤在于查询表上,查询的结果命名为视图就行了
创建视图
语法:select view 视图名 as select 语句
例如:
create view stats as select cat_id,avg(shop_price) from goods group by cat_id;
就会发现多了一个 stats 表
select * from stats;
和
select cat_id,avg(shop_price) from goods group by cat_id;
两句语句的查询结果一样
视图与表的关系
例如查询表:
select goods_id,cat_id,shop_price from goods where cat_id=15;
结果如下:

此时视图 stats 中 cat_id = 15 的平均价格为70:

更新表中数据:
update goods set shop_price = 65 where goods_id = 28;
更新后为:

此时查询视图

可以发现,表的数据变化会影响到视图中数据的变化。视图中的数据通常是不可以修改的,例如上表中的的平均价格就不能修改,因为修改后,就不知道在原来的表中商品的数据怎么变了。但是,在视图中的数据和表中的数据一一对应,即表能推出视图中具体对应的数据,视图也能推出表中具体对应的数据,类似于数学中函数与反函数,此情况下是可以修改视图的,同时也会影响到原来表中的数据。
视图的作用
- 可以简化查询,例如复杂的统计时,可以先用视图生成一个中间结果,再查询视图
- 更加精细的权限控制,例如只开发用户名字段而不开放密码字段
- 数据多时,分表时可以用到。例如数据多,同一类型数据分成了多张表,查询时可以将多张表用 union 连起来,然后用视图来查询
通常的表保存有三个文件,.frm 文件表示表结构,.MYD 文件表示表数据,.MYI 文件表示表索引,而视图则只保留 .frm 文件,即表结构,没有保存表数据与表索引。
algorithm
对于一些简单视图,他并没有建立临时表,只是在发挥作用的过程中把条件存起来,下次查询的时候把条件合并,直接查表。相比于建立临时表,叠加查询的速度更快。那么到底是建立临时表还是叠加语句,algorithm 就是做这个的。algorithm 可以做到 merge(合并语句), temptable(临时表), undefined(未定义,由系统决定)。例如:
create view v1 as select * from goods where shop_price > 300;
然后对 v1 进行查询:
select goods_id,goods_name,shop_price from v1 where shop_price < 500;
总的来说就是价格大于 300 但是小于 500 。这个简单的查询如果建立临时表,开销有点大,这时可以指定 algorithm 选项为 merge ,即合并语句,而不是建立临时表,例如
create algorithm=merge view v2 as select * from goods where shop_price > 300;
此时再对 v2 进行查询,
select goods_id,goods_name,shop_price from v2 where shop_price < 500;
虽然结果上没有差别,但是此时 v2 视图并没有建立临时表。
有的时候,查询相对复杂,就需要建立临时表:
create view v3 as select * from goods order by cat_id asc,shop_price desc;
如果还需要查询每个栏目下最高的商品价格,就无法直接在原来的语句上直接增加合并了。所以只能先建立临时表。此时可以:
create algorithm=temptable view v3 as select * from goods order by cat_id asc,shop_price desc;
如果不清楚是否使用临时表,可以使用 algorithm=undefined,由系统做决定
网友评论