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:聯合的意思，即把兩次或多次查詢結果合并起來。

 

要求：兩次查詢的列數必須一致，這也是上訴做視圖的原因所在，而且字串排序也必須一樣，不然會提示聯合錯誤，

 

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