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

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