美文网首页
MySQL 中视图及其与表的关系

MySQL 中视图及其与表的关系

作者: djz0306 | 来源:发表于2019-07-11 21:17 被阅读0次

在前面的查询中经常把查询结果当成临时表来看,视图(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;

结果如下:

原表数据.jpg

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

更新前视图中的数据.jpg

更新表中数据:

update goods set shop_price = 65 where goods_id = 28;

更新后为:

更新后视图中的数据.jpg

此时查询视图

更新前视图中的数据.jpg

可以发现,表的数据变化会影响到视图中数据的变化。视图中的数据通常是不可以修改的,例如上表中的的平均价格就不能修改,因为修改后,就不知道在原来的表中商品的数据怎么变了。但是,在视图中的数据和表中的数据一一对应,即表能推出视图中具体对应的数据,视图也能推出表中具体对应的数据,类似于数学中函数与反函数,此情况下是可以修改视图的,同时也会影响到原来表中的数据。

视图的作用

  1. 可以简化查询,例如复杂的统计时,可以先用视图生成一个中间结果,再查询视图
  2. 更加精细的权限控制,例如只开发用户名字段而不开放密码字段
  3. 数据多时,分表时可以用到。例如数据多,同一类型数据分成了多张表,查询时可以将多张表用 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,由系统做决定

相关文章

  • MySQL 中视图及其与表的关系

    在前面的查询中经常把查询结果当成临时表来看,视图(view)则可以当成一张临时表 例如查询每个栏目下商品的平均价格...

  • MySQL之视图VIEW

    本文主要介绍MySQL中的视图, 在什么情况下使用视图。 I、视图介绍 1.1 视图的初步理解 视图是虚拟的表,与...

  • 数据库

    一、MySQL中视图与表的区别 1.1 MySQL中视图和表的区别以及联系 视图是已经编译好的SQL语句,是基于S...

  • MySQL视图,索引,数据导入与导出,备份和恢复

    视图 MySQL中的视图,是为了MySQL中的数据的展示的。是从一个表或者若干个表中,抽离出我们关心的数据,然后集...

  • 14 MySQL 视图

    MySQL 视图 [toc] 视图概述 视图介绍 什么是视图 虚拟表 内容与真实的表相似,有字段有记录 视图并不在...

  • mysql必知必会(22):视图

    一、视图 版本要求:MySQL5+ 视图是虚拟的表,视图本身不包含数据。 与包含数据的表不一样,视图只包含使用时动...

  • 数据库学习笔记——18使用视图

    1 视图 MySQL 5添加了对视图的支持。 视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的...

  • 9_mysql视图

    ① 视图(view)是从一个或多个表中(或视图)导出的表。② 视图与表(有时为与视图区别,也称表为基本表)不同,视...

  • mysql view(视图)

    mysql 视图是通过sql, 提前从一张表或几张表中查询出来的虚拟表, 视图不会存储数据, 不会对查询的速度产生...

  • MySQL的视图

    MySQL的视图 创建基础表 视图的测试 视图的分类 单源表视图:视图的数据可以只取自一个基本表的部分行、列,这样...

网友评论

      本文标题:MySQL 中视图及其与表的关系

      本文链接:https://www.haomeiwen.com/subject/znyjkctx.html