热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

快速入门:使用ecshop的goods表,查询平均价格前三高的栏目
快速入门:使用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;


推荐阅读
author-avatar
傻a2602909381
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有