当前位置:Gxlcms > mysql > 教程-视图的概念和基本用法_MySQL

教程-视图的概念和基本用法_MySQL

时间:2021-07-01 10:21:17 帮助过:39人阅读

bitsCN.com

  快速入门:使用ecshop 的goods表,查询平均价格前三高的栏目。

  传统方式:

  select cat_id,avg(shop_price) as aprice from goods

  group by cat_id

  order by aprice desc limit 3;

  新的方式:利用创建视图

  #创建视图:

  create view v1 as

  select cat_id,avg(shop_price) as aprice from goods;

  #查看视图:

  Show tables;

  #查看视图列:

  Desc 视图名

  #查看视图创建:

  Show create view 视图名

  #删除视图:

  Drop view 视图名

  视图的好处:

  1. 简化查询。

  2. 权限控制。

  3. 分表查询.

  4. 可维护性好。、

  A .简化查询,上面的例子用视图实现的代码:

  创建视图表

  Create view v1 as

  Select gooods_id,cat_id,goods_name,avg(shop_price) as aprice from esc_goods group by cat_id;

  #查询视图表

  Select *from v1 order by aprice limit 3;

  B 权限控制,假设有如下表,由小刘来维护,需要他进行开发和管理销售软件,但又不能让其看到成本价,该怎么做?

  编号产品名称市场价成本价单位品牌

  1黄金446200元/克周大福

  2铂金375.94192元/克周大福

  3金条456.87185.35元/克周大生

  4饰品461203.58元/克周大生

  #演示如何通过视图来控制权限

  #创建珠宝表

  create table jewelry(

  id int unsigned not null auto_increment primary key,

  name varchar(20) not null,

  price decimal(10,2) not null,

  cost decimal(10,2) not null,

  unit varchar(20) not null,

  brand varchar(30) not null

  );

  #插入几条记录

  insert into jewelry(name,price,cost,unit,brand) values('黄金',446,200,'元/克','周大福');

  insert into jewelry(name,price,cost,unit,brand) values('铂金',375.94,192,'元/克','周大福');

  insert into jewelry(name,price,cost,unit,brand) values('金条',456.87,185.35,'元/克','周大生');

  insert into jewelry(name,price,cost,unit,brand) values('饰品',461,203.58,'元/克','周大生');

  #创建一个视图

  #新建用户,登录MySQL

  mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values('localhost'

  test',password('123'),'','','');

  #刷新系统权限表

  mysql> flush privileges;

  #为用户授权,要使用root账户,针对某个视图创建权限(数据库名.视图名)

  grant select,insert,update,delete on wcp.vj to test@localhost;

  #删除用户

  C 分表查询,加快查询速度。假设现在有一个海量数据表,比如QQ账户表,用户反映登录时间太长,有没有办法将其优化一下?

  分表:

  可以按qq号码取余的方式将qq用户表分成10张表,

  Qq1,qq2,qq3,qq4,qq5,

  如何查询?

  10003 %10 = 3

  Create view vq as

  Slect * from qq1 union select * from qq2

  D 维护性好,小明在公司做网站开发和维护工作,因为业务的变化,数据库中有两张表a和b,现在需要组合成c表,你能帮他出出主意吗?

  A表

  B表

  C表

  可以利用视图,先把表A和表B联合成一张表C,然后从c表分别创建原来 表A的视图和表B的视图 ,这样程序只需要稍微改下,原来的查询等语句照样使用。

  深入理解视图-视图的算法及其与表的关系

  1. 视图和表是什么关系?

  视图是一张虚拟的表,并不是物理上存在的,只不过我们可以将其作为表来使用。

  使用视图可以节省空间。

  2. 视图是如何工作的?

  在使用视图的时候,引用对应的表,查询得到这个结果。

  3. 视图和表之间的操作会影响对方吗?

  编号名称价格操作系统品牌

  1iphone55999iosApple

  2iphone4s4430iosApple

  3htc 328w2930androidHTC

  4htc G212250androidHTC

  5htc one s2500androidHTC

  6lumia 9204590wp8Nokia

  7lumia 8002900wp7Nokia

  8nokia N9 2300MeeGoNokia

  9Galaxy 33480androidsamsung

  #创建phone表

  create table phone(

  id int unsigned not null auto_increment primary key,

  name varchar(30) not null,

  price decimal(7,2) not null,

  os varchar(30) not null,

  brand varchar(30) not null

  );

  #插入八条记录

  insert into phone(name,price,os,brand) values('iphone5',5999,'ios','Apple');

  insert into phone(name,price,os,brand) values('iphone4s',4430,'ios','Apple');

  insert into phone(name,price,os,brand) values('htc328w',2930,'android','HTC');

  insert into phone(name,price,os,brand) values('htc G21',2250,'android','HTC');

  insert into phone(name,price,os,brand) values('htc one s',2500,'android','HTC');

  insert into phone(name,price,os,brand) values('lumia920',4590,'wp8','Nokia');

  insert into phone(name,price,os,brand) values('lumia800',2900,'wp7','Nokia');

  insert into phone(name,price,os,brand) values('nokia n9',2300,'meego','Nokia');

  #创建一个视图vphone1

  mysql> create view vp1 as select name,price,brand from phone;

  #对表更新,看其对视图的影响

  结论:表的更新会直接影响到视图。

  #查看视图

  #对视图更新,看其对表的影响

  update vp1 set price = price - 500;

  结论:改变视图是可以改变表的。但是不是总是能改变,只有在视图和原表一一对应的情况下才能通过视图改变表。

  #再创建一个视图,vphone2

  mysql> create view vp2 as

  -> select brand,avg(price) as aprice from phone

  -> group by brand;

  #查看视图

  #更新视图vphone2

  mysql> update vp2 set aprice = aprice + 500;

  ERROR 1288 (HY000): The target table vp2 of the UPDATE is not updatable

  结论:但是不是总是能改变,只有在视图和原表一一对应的情况下才能通过视图改变表。

  #向视图vphone1中插入记录,

  mysql> insert into vp1 values('galaxy '3470','samsung','android');

  ERROR 1423 (HY000): Field of view 'wcp.vp1' underlying table doesn't have a default value

  对于在视图中没有出现的列,而在表中有没有默认值,则此时插入会失败。

  #更改表结构

  mysql> alter table phone modify os varchar(30) not null default '';

  #再向视图vphone1中插入记录

  mysql> insert into vp1 values('galaxy '3470','samsung','android');

  4. 视图使用注意事项

  a. 视图也是一种表,是虚拟表,或者说表和视图共享数据库中相同的名称空间,不能与已有的表(视图)出现重名。

  b. 视图属于数据库。在默认情况下,将在当前数据库创建新视图。

  视图的算法

  1. 使用视图来实现查询每个栏目下最贵的商品,该怎么做?

  原先做法:

  mysql> select goods_id,goods_name,cat_id,shop_price from (

  -> select goods_id,goods_name,cat_id,shop_price from goods

  -> order by cat_id,shop_price desc) as temp

  -> group by cat_id;

  使用视图来实现:

  mysql> create view vec as

  -> select goods_id,goods_name,cat_id,shop_price from goods

  -> order by cat_id,shop_price desc;

  mysql> select * from vec group by cat_id;

  结果不对,why?

  把创建视图和查询视图的语句合并到一起。

  select * from select goods_id,goods_name,cat_id,shop_price from goods group by cat_id order by cat_id,shop_price desc;

  这样又回到我们错误的老路上。

  原因,就是因为使用的是merge算法,

  视图的三种算法(algorithm)

  模式说明备注

  merge合并,在执行视图的时候,将查询视图语句和创建视图语句合并到一起,然后执行。默认

  temptable临时表,在执行查询视图语句时,首先执行创建视图语句,并将其作为一个临时表,接着让查询视图的语句查询。

  undefined未定义,自动选择,

  改变视图的算法:

  Create algorithm = temptable view 视图名 as

  mysql> create algorithm=temptable view vec as

  -> select goods_id,goods_name,cat_id,shop_price from goods

  -> order by cat_id,shop_price desc;

bitsCN.com

人气教程排行