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

一：使用视图的原因：

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

2：查询性能提高。

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

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

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

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

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

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

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

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

3：本次没有选择，然后点击执行即生成视图，如下：

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