设为首页收藏本站
查看: 75|回复: 0

[PHP] mysql教程-视图的概念和基本用法

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2013-9-12 18:11:00 | 显示全部楼层 |阅读模式
    快速入门:使用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;
      编号
      产品名称
      市场价
      成本价
      单位
      品牌
      1
      黄金
      446
      200
      元/克
      周大福
      2
      铂金
      375.94
      192
      元/克
      周大福
      3
      金条
      456.87
      185.35
      元/克
      周大生
      4
      饰品
      461
      203.58
      元/克
      周大生
    B 权限控制,假设有如下表,由小刘来维护,需要他进行开发和管理销售软件,但又不能让其看到成本价,该怎么做?   #演示如何通过视图来控制权限
    #创建珠宝表 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的视图 ,这样程序只需要稍微改下,原来的查询等语句照样使用。
      #p#深入理解视图#e#
      深入理解视图-视图的算法及其与表的关系
      1. 视图和表是什么关系?
      视图是一张虚拟的表,并不是物理上存在的,只不过我们可以将其作为表来使用。
      使用视图可以节省空间。
      2. 视图是如何工作的?
      在使用视图的时候,引用对应的表,查询得到这个结果。
      3. 视图和表之间的操作会影响对方吗?
      编号
      名称
      价格
      操作系统
      品牌
      1
      iphone5
      5999
      ios
      Apple
      2
      iphone4s
      4430
      ios
      Apple
      3
      htc 328w
      2930
      android
      HTC
      4
      htc G21
      2250
      android
      HTC
      5
      htc one s
      2500
      android
      HTC
      6
      lumia 920
      4590
      wp8
      Nokia
      7
      lumia 800
      2900
      wp7
      Nokia
      8
      nokia N9  
      2300
      MeeGo
      Nokia
      9
      Galaxy 3
      3480
      android
      samsung
      

      #创建phone表
      

    reate 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 goodsorder 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;
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表