资源下载 - 从此开始!

下载我_精品源码软件教程资源网

您的位置:首页 > 建站教程 > 数据库教程 > MYSQL教程 > 正文

mysql中视图和union联合查询的使用

2017-10-02 19:11 来源:未知 编辑:admin【纠错

MySQL联合查询效率较高,我们常用的mysql联合查询(内联、左联、右联、全联)的了同时mysql视图也是我们常见的,下面我们一起来看看视图和union联合查询的使用吧。
一:使用视图的原因:

 

1:安全性。一般是这样做的:创建一个视图,定义好该视图所操作的数据,之后将用户权限与视图绑定。

 

2:查询性能提高。

 

3:有灵活性的功能需求后,需要改动表的结构而导致工作量比较大。那么可以使用虚拟表的形式达到少修改的效果,在实际开发中比较有用。

 

4:复杂的查询需求或排序可以进行问题分解,创建多个视图获取数据。将视图联合起来得到需要的结果。

 

二:本次使用视图是因为项目中查询两个表数据并排序分页,但是两个表的数据字段不一,如果改动会导致其他地方的sql需要修改,所以建了视图来解决这个问题:

 

1:建立视图: 直接使用phpMyAdmin,当然也可以使用sql语句: 如下:进入数据库里的某一表,会看到新建视图这个功能:{OX6BSE~]9K(QO)_6S4KP]82:写入sql,获取相应的字段并命名,建立合适的视图:7`(]J@)}DUPYO1`KO7Z}1FO可选的ALGORITHM子句是对标准SQL的MySQL扩展。ALGORITHM可取三个值:MERGE、TEMPTABLE或UNDEFINED,如果没有ALGORITHM子句,默认算法是UNDEFINED(未定义的),算法会影响MySQL处理视图的方式。具体使用哪儿种方式,大家问问度娘。

 

用户指授权某一用户使用,这里没有选择,下面也没有选择,直接填写视图的名称,字段名为空,AS 里填写上你自己写好的sql语句。

 

WITH CHECK OPTION表示更新视图时要保证在该试图的权限范围之内(可选参数)

 

CASCADED:更新视图时要满足所有相关视图和表的条件

 

LOCAL:更新视图时,要满足该视图本身定义的条件即可

 

3:本次没有选择,然后点击执行即生成视图,如下:[C)I`PUU6X]BP)}VDLSKP82

 

视图的创建语法:
create view 视图名 as select 语句;
4.使用视图有什么好处呢?
  ①简化查询语句
  比如:有一张商品表,我们经常要查每个栏目下商品的平均价格
  select cat_id,avg(shop_price) from goods gropy by cat_id;
  这时候我们就可以创建一张视图:
  create view avgPrice as select cat_id,avg(shop_price) from goods gropy by cat_id;
  创建完,以后我们要查每个栏目的平均价格时,只要这么写
  select * from avgPrice;就可以了。
  ②可以进行权限控制
  把表的权限封闭,但是开放相应的视图权限,视图里只开放部分数据列
  比如我们的goods商品表,我们不想让别人看到我们的销售价格,这时候我们就可以把查看商品表的权限封闭,创建一张视图
  create view showGoods as select goods_id,goods_name from goods;
  不出现销售价格列就可以了。
  ③大数据分表时可以用到
  比如表的行数据超过200万行时,速度就会变慢
  可以把一张表的数据拆成4张表来存放
  News表
  newsid  1,2,3,4...
  news1,news2,news3,news4表
  把一张表的数据分散到4张表里,分散的方法有很多,
  最常用的是id取模来计算 
  id%4+1=[1,2,3,4]
  ...
  还可以用视图,把四张表形成一张视图
  create view news as select * from news1 union select * from news2 union ...
 5.视图的修改
 alter view 视图名 as select 语句;
 6.视图与表的关系
 视图是表的查询结果,自然表的数据变了,会影响视图的结果
 7.那么视图改变了会影响到表吗?
  ①视图的增删改也会影响表;
  ②但视图并不总是能增删改的;
  视图的数据与表的数据一一对应时可以修改;
  对于视图的insert还应注意:视图必须包含表中没有默认值的列。
 8.视图的algorithm(运算规则)
 algorithm = merge/temptable/undefined
 merge:当引用视图时,引用视图的语句与定义视图的语句合并
 意味着视图只是一个规则,语句规则,当查询视图时,把查询视图的语句
 比如:where...那些与创建时的语句where子句等合并,分析,形成一条select语句。
 举个列子:
 我们先创建一张视图查询所有商品价格大于3000的商品
 create view g2 as select goods_id,goods_name,shop_price from goods where shop_price > 3000;
 然后我们再查询视图的时候,再加上一个where条件<5000
 select * from g2 where shop_price < 5000
 这时候它就会把两条语句合并分析最终形成这样一条select语句
 select goods_id,goods_name,shop_price from goods where shop_price > 3000 and shop_price < 5000;
 
temptable:是根据创建语句瞬间创建一张临时表,然后查询视图的语句从该临时表查数据
 
merge 和 temptalbe 有一个显著的区别:
merge最终去查的还是goods表,而temptable去查的是虚拟表。
举个例子:我们要得到每个栏目下最贵的商品
首先我们创建一张视图查出每个栏目的商品按价格降序排序
create view lmj as select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;
这时候我们在查询这张视图的时候再对cat_id进行分组是不是就能得到我们想要的结果呢?
select * from lmj group by cat_id;
答案是不能的,因为它把我们的创建视图的语句和查询视图的语句合并成
select cat_id,goods_id,goods_name,shop_price from goods group by cat_id order by cat_id,shop_price desc;
 
而如果我们在创建视图的时候指定了它的运算规则为:temptable
create algorithm=temptable view lmj as select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;
然后我们再查询视图:select * frm lmj group by cat_id;就能得到我们想要的结果了。
它会先把select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;这句sql语句取到的结果放到一张临时表,然后我们再从这张临时表查自然能得到我们想要的结果了,而不是合并了再去查。
undefined:未定义,自动,让系统帮你选。

这样视图建好了,大家可以对其进行操作了,个人理解视图类似于桥梁的作用,通过sql建立一张表,里面存了你需要查询的内容和信息,方便使用。

 

三:使用union联合查询

 

1:union:联合的意思,即把两次或多次查询结果合并起来。

 

要求:两次查询的列数必须一致,这也是上诉做视图的原因所在,而且字符串排序也必须一样,不然会提示联合错误,

 

在上诉写视图sql的时候,字段名要一一对应,并且顺序不能乱,因为union以第一个sql语句的列名为准,

 

如果不同的语句中取出的行,有完全相同(这里表示的是每个列的值都相同),那么union会将相同的行合并,最终只保留一行。也可以这样理解,union会去掉重复的行。

 

如果不想去掉重复的行,可以使用union all。

 


以下例子来说明联合查询(内联、左联、右联、全联)的好处:


T1表结构(用户名,密码)   
userid(int)   usernamevarchar(20)   passwordvarchar(20)   
1   jack  jackpwd   
2   owen  owenpwd  


T2表结构(用户名,密码)   
userid(int)   jifenvarchar(20)   dengjivarchar(20)   
    1   20   3   
    3   50   6  


第一:内联(inner join)
如果想把用户信息、积分、等级都列出来,那么一般会这样写:

select * from T1, T3 where T1.userid = T3.userid
(其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid )。

把两个表中都存在userid的行拼成一行(即内联),但后者的效率会比前者高很多,建议用后者(内联)的写法。

SQL语句:
select * from T1 inner join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3  

 

第二:左联(left outer join)
显示左表T1中的所有行,并把右表T2中符合条件加到左表T1中;
右表T2中不符合条件,就不用加入结果表中,并且NULL表示。

SQL语句:
select * from T1 left outer join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
2   owen   owenpwd   NULL   NULL   NULL  

 

第三:右联(right outer join)。
显示右表T2中的所有行,并把左表T1中符合条件加到右表T2中;
左表T1中不符合条件,就不用加入结果表中,并且NULL表示。

SQL语句:
select * from T1 right outer join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
NULL   NULL   NULL   3   50   6  

 

第四:全联(full outer join)
显示左表T1、右表T2两边中的所有行,即把左联结果表 + 右联结果表组合在一起,然后过滤掉重复的。

SQL语句:
select * from T1 full outer join T2 on T1.userid = T2.userid
 
运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
2   owen   owenpwd   NULL   NULL   NULL   
NULL   NULL   NULL   3   50   6  

总结,关于联合查询,效率的确比较高,4种联合方式如果可以灵活使用,基本上复杂的语句结构也会简单起来。


在存在 order by 和分页 limit 的情况下,需要用()将sql括起来使用。


如:


(SELECT * FROM view_price) UNION ALL (SELECT * FROM view_combo </span><span class="s2">) LIMIT 1, 10

计算总数:

SELECT COUNT(*) from ((SELECT * FROM view_price  ) UNION All (SELECT * FROM view_combo  )) as T

个人觉得这个方式还是很有用处的,在处理一些麻烦的数据的时候,虽然在后期维护的时候会每次都要去改动建立视图的sql语句,但当在使用的时候利还是大于弊的。对于以上的内容还有很多待补充的地方,欢迎大家批评指正!


打赏一下,我们会为大家提供更多优质资源!

猜你喜欢

看过本文的人还看过

关闭

微信扫一扫

站长微信账号