萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql中視圖和union聯合查詢的使用

mysql中視圖和union聯合查詢的使用

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語句,但當在使用的時候利還是大於弊的。對於以上的內容還有很多待補充的地方,歡迎大家批評指正!

copyright © 萬盛學電腦網 all rights reserved