• 风雨兼程,与党和人民同行 2019-05-23
  • 张鼎丞对人民军队建设作出的贡献 2019-05-23
  • 把党的领导贯穿军事科研工作各方面和全过程 2019-05-23
  • 在强国论坛和新浪微博上也就说了几句国产80后伟大的钢琴家,嚯!粉丝不干了,邮箱里塞满男性女性生殖器的骂声和愤慨声。其实,真没有诋毁的意思。比如,美国一家男性内衣 2019-05-22
  • 【理上网来·喜迎十九大】不忘初心、继续前进,勇于全面深化改革 2019-05-22
  • 这5件事预测你能否可以开心活过80岁 2019-05-22
  • 新疆14所高校大学生同台比赛 2019-05-21
  • 西南铁路端午假期发送400余万人次 两人不文明乘车或受失信惩戒 2019-05-21
  • 省委中心组成员到联系帮扶点开展专题调研 2019-05-21
  • 热刺中场埃里克森领衔 北欧童话能否续写逆袭新篇 2019-05-21
  • 在深入推动长江经济带发展座谈会上的讲话 2019-05-20
  • 北京副中心核心区建成世界超一流智能配电网 2019-05-20
  • 江苏:五峰山跨江线路升高改造工程启动(组图) 2019-05-20
  • 无视婚变传闻 王浩信晒与朋友新旧对比照秀友情 2019-05-19
  • 专访香港演员陈钰芸:女版李小龙 用武术演绎精彩人生 2019-05-19
  • Jquery中文网 码报 www.okkrb.tw
    Jquery中文网 >  数据库  >  mysql  >  正文 mysql中视图和union联合查询的使用

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

    发布时间:2017-12-13   编辑:码报 www.okkrb.tw
    jquery中文网为您提供mysql中视图和union联合查询的使用等资源,欢迎您收藏本站,我们将为您提供最新的mysql中视图和union联合查询的使用资源
    MySQL联合查询效率较高,我们常用的mysql联合查询(内联、左联、右联、全联)的了同时mysql视图也是我们常见的,下面我们一起来看看视图和union联合查询的使用吧。
    一:使用视图的原因:

    码报 www.okkrb.tw  

    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:联合的意思,即把两次或多次查询结果合并起来。

     

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

     

    在上诉写视图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语句,但当在使用的时候利还是大于弊的。对于以上的内容还有很多待补充的地方,欢迎大家批评指正!

    您可能感兴趣的文章:
    mysql中视图和union联合查询的使用
    Mysql 多表联合查询效率分析及优化
    mysql开启慢查询以检查查询慢的语句
    mysql中RAND()随机查询记录的效率问题和解决办法
    mysql常用sql语句大全
    学习开启mysql慢查询与分析查询语句
    MySQL-Proxy读写分离要注意的问题
    jsp通过union实现数据库多表分页查询的小例子
    phpcms教程之mysql配置优化
    ubuntu下mysql配置

    [关闭]
  • 风雨兼程,与党和人民同行 2019-05-23
  • 张鼎丞对人民军队建设作出的贡献 2019-05-23
  • 把党的领导贯穿军事科研工作各方面和全过程 2019-05-23
  • 在强国论坛和新浪微博上也就说了几句国产80后伟大的钢琴家,嚯!粉丝不干了,邮箱里塞满男性女性生殖器的骂声和愤慨声。其实,真没有诋毁的意思。比如,美国一家男性内衣 2019-05-22
  • 【理上网来·喜迎十九大】不忘初心、继续前进,勇于全面深化改革 2019-05-22
  • 这5件事预测你能否可以开心活过80岁 2019-05-22
  • 新疆14所高校大学生同台比赛 2019-05-21
  • 西南铁路端午假期发送400余万人次 两人不文明乘车或受失信惩戒 2019-05-21
  • 省委中心组成员到联系帮扶点开展专题调研 2019-05-21
  • 热刺中场埃里克森领衔 北欧童话能否续写逆袭新篇 2019-05-21
  • 在深入推动长江经济带发展座谈会上的讲话 2019-05-20
  • 北京副中心核心区建成世界超一流智能配电网 2019-05-20
  • 江苏:五峰山跨江线路升高改造工程启动(组图) 2019-05-20
  • 无视婚变传闻 王浩信晒与朋友新旧对比照秀友情 2019-05-19
  • 专访香港演员陈钰芸:女版李小龙 用武术演绎精彩人生 2019-05-19