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