走向面試之經典的資料庫基礎：二、SQL進階之case、子查詢、分頁、join與視圖

標籤：style   blog   http   color   os   使用   io   strong   for   

一、CASE的兩種用法1.1 等值判斷->相當於switch case

　　（1）具體用法模板：

　　　　CASE expression

 　　　　　　WHEN value1 THEN returnvalue1

 　　　　　　WHEN value2 THEN returnvalue2

 　　　　　　WHEN value3 THEN returnvalue3

　　　　　　 ELSE defaultreturnvalue

　　　　END 

　　（2）具體使用樣本：

　　假設我們有一個論壇網站，其中有一張User表{ UId,Name,Level }，Level是一個int類型，代表了使用者等級類型，例如：1代表骨灰，2代表大蝦等；我們就可以用CASE來對其進行等值判斷了：

select Name,Rank=(    case Level        when 1 then ‘骨灰‘        when 2 then ‘大蝦‘        when 3 then ‘菜鳥‘    end)    from User

1.2 條件判斷->相當於if else if else

　　（1）具體用法模板：

　　　　CASE

　　　　　　WHEN condition1 THEN returnvalue1

　　　　　　WHEN condition2 THEN returnvalue2

　　　　　　WHEN condition3 THEN returnvalue3

　　　　　　ELSE defaultreturnvalue

　　　　END

注意：then後面返回的資料類型要一致， returnvalue1、 returnvalue2、 returnvalue3的資料類型必須一致。

　　（2）具體使用樣本：

　　假設我們有一張Score成績表，裡面記錄有所有同學的成績，此時我們想要對所有成績進行一個評級，比如成績如果>=90那麼則評為A級，>=80且<90則評為B級，這裡我們怎來寫呢？

select studentId,rank=(        case        when english between 90 and 100 then ‘A‘        when english between 80 and 89 then ‘B‘        when english between 70 and 79 then ‘C‘        when english between 60 and 69 then ‘D‘        when english < 60 then ‘E‘        else ‘缺考‘    end)from Score

二、子查詢的用法2.1 子查詢初步

　　就像使用普通的表一樣，被當作結果集的查詢語句被稱為子查詢。所有可以使用表的地方几乎都可以使用子查詢來代替。例如：我們如果要找到所有計科一班的同學資訊，可以首先通過T_Class表找到計科一班的Id，然後再在T_Student表中找到所有ClassId為計科一班Id的行即可。

select * from T_Student where ClassId=(    select Id from T_Class where Name=‘計科一班‘)

2.2 單值子查詢

　　只有返回且僅返回一行、一列資料的子查詢才能當成單值子查詢。例如我們上面提到的例子，子查詢中只返回了一個ClassId，這就是單值子查詢。當子查詢跟隨在=、!=、<、<=、>、>=，<> 之後，或子查詢用作運算式，只能使用單值子查詢。

2.3 多值子查詢

　　如果子查詢是多行單列的子查詢，這樣的子查詢的結果集其實是一個集合，那麼可以使用in關鍵字代替=號。例如：我們如果想快速地在T_Student表中刪除計科一班和計科二班的所有學生記錄，我們可以使用in關鍵字：

delete from T_Student where ClassId in(    select Id from T_Class where Name=‘計科一班‘ or Name=‘計科二班‘)

2.4 Exists—你存在我深深的腦海裡

　　exists是用來判斷是否存在的，當exists查詢中的查詢存在結果時則返回真，否則返回假。not exists則相反。

　　exists做為where 條件時，是先對where 前的主查詢詢進行查詢，然後用主查詢的結果一個一個的代入exists的查詢進行判斷，如果為真則輸出當前這一條主查詢的結果，否則不輸出。

　　exists後面的查詢稱為相互關聯的子查詢，即子查詢的查詢條件依賴於外層父查詢中的某個屬性值，其處理過程一般為：先取外層查詢中的第一個元組，根據它與內層查詢中的相關屬性值處理內層查詢，若where子句返回true，則將此元組放入結果表中，然後取外層查詢中的下一個元組，重複這個過程直到全部檢查完畢為止。

　　例如：我們有一張人員資訊表，裡邊有一個人員類型Id欄位（pTypeId），它是一個外鍵，對應著人員類型表的主鍵ptId。如果我們有以下的SQL語句，使用Exists關鍵字則可以有如下的理解：

select * from Employee e where exists (select * from EmployeeType et where e.pTypeId=et.ptId)

　　那麼，在這句SQL的執行過程中，我們可以將其理解為一個雙重的for迴圈，外邊是主表的迴圈遍曆，然後將其放到一個temp變數中，再進入從表的for迴圈，並與從表的項進行一個一個的按照匹配規則（這裡是e.pTypeId=et.ptId）進行匹配，如果有匹配成功則返回true，否則返回false。

三、手寫分頁SQL代碼

　　這裡假設每頁的頁大小為10條記錄

3.1 利用Top N進行簡單分頁

　　（1）如果我們要擷取第一頁的資料，也就是前10個：

select top 10 * from Account

　　（2）現在我們要擷取第一頁之後的資料，也就是第20個~最後一個：

select * from Account where Id not in (select top 10 Id from Account) 

　　（3）現在我們對第20個~最後一個的資料集中取前10個也就成為第二頁的資料了：

select top 10 * from Account where Id not in (select top 10 Id from Account) 

　　（4）將上述代碼總結為分頁代碼，設頁大小為pageSize，請求頁號為pageIndex：

select top @pageSize * from Account where Id not in (select top ((@pageIndex-1)*@pageSize) Id from Account) 

PS：這種分頁方式的缺點是如果要取很多頁之後的資料，那麼就要取出前面很多頁的ID，查詢開銷較大，執行效率也就低下。

　　可以看出，在小資料量的對比下，Top N的查詢開銷較小。但是在大資料量的對比下，Row_Number的方式會取得更高的查詢效率以及較小的開銷。

3.2 利用Row_Number()進行高效分頁

　　（1）SQL Server 2005後增加了Row_Number函數，可以簡化分頁代碼的實現。首先，Row_Number()是一個排序函數，它可以產生一個有序的行號（如果單靠ID來排序，中間存在斷層，例如某一個ID行已經被刪除了）。根據MSDN的定義：返回結果集分區內行的序號，每個分區的第一行從 1 開始。而排序的標準是什麼呢？這個就要靠緊跟其後的OVER()語句來定義了。這裡我們可以通過一個樣本來看看，其產生的行號如何。

select *,ROW_NUMBER() OVER(order by Id) as RowNum from Account

　　（2）根據ROW_NUMBER()的使用，我們可以將其應用到分頁上，於是我們可以寫出以下的代碼來實現擷取第二頁的資料集：

select * from (    select *,ROW_NUMBER() OVER(Order by Id) as rownum from Account) as twhere t.rownum between 11 and 20order by t.Id asc

　　（3）將上述代碼總結為分頁代碼，設頁大小為pageSize，請求頁號為pageIndex：

select * from (    select *,ROW_NUMBER() OVER(Order by Id) as rownum from Account) as twhere t.rownum between (@pageIndex-1)*pageSize+1 and @pageSize*pageIndexorder by t.Id asc

四、各種串連—JOIN4.1 Join==Inner Join

　　預設情況下，使用Join則代表Inner Join內串連，表示兩個表根據某種等值規則進行串連。例如下面樣本：查詢所有學生的學號、姓名及所在班級

select p.Id,p.Name,c.Name from T_Person p join T_Class c on p.ClassId=c.Id

4.2 Left Join

　　例如：查詢所有學生（參加及未參加考試的都算）及成績，這裡涉及到學生表及成績表，題目要求參加及未參加考試的都要列出來，於是以學生表為基準，對成績表進行左串連：

select * from Student sleft join SC sc on s.S#=sc.S#

　　可以通過運行結果圖，看到王二小這個童鞋沒有參加考試，也就沒有成績。

4.3 Right Join

　　例如：要查詢出所有沒有參加考試（在成績表中不存在的學生）的學生的姓名。於是還是可以以學生表為基準，但是要對成績表進行右串連：

select * from Student sright join SC sc on s.S#=sc.S#

4.4 Cross Join

　　此種串連在實際應用中不算常見的，但卻是理論基礎，因為它代表了笛卡爾積。其實，所有串連方式都會先產生臨時笛卡爾積表，笛卡爾積是關係代數裡的一個概念，表示第一個表的行數乘以第二個表的行數等於笛卡爾積結果集的大小。

select * from Student scross join SC sc

　　這裡假如Student表中有10行記錄，SC表中有20行記錄，那麼兩表進行交叉串連笛卡爾積運算會得到10*20=200行記錄的結果集。

五、視圖5.1 三個角度看視圖

　　從使用者角度來看，一個視圖是從一個特定的角度來查看資料庫中的資料。

　　從資料庫系統內部來看，一個視圖是由SELECT語句組成的查詢定義的虛擬表。

　　從資料庫系統內部來看，視圖是由一張或多張表中的資料群組成的；從資料庫系統外部來看，視圖就如同一張表一樣，對錶能夠進行的一般操作都可以應用於視圖，例如查詢，插入，修改，刪除操作等。

5.2 建立視圖

　　例如，我們可以建立一個學產生績詳細資料檢視，對一個需要進行三表串連的查詢進行封裝：

create view vw_scasselect s.S#,s.Sname,c.Cname,sc.Score from Student sjoin SC sc on s.S#=sc.S#join Course c on sc.C#=c.C#

　　然後，我們對vw_sc進行select查詢：

5.3 視圖的注意事項

　　（1）視圖在操作上和資料表沒有什麼區別，但兩者的差異是其本質是不同：資料表是實際儲存記錄的地方，然而視圖並不儲存任何記錄。

　　（2）相同的資料表，根據不同使用者的不同需求，可以建立不同的視圖（不同的查詢語句）。

　　（3）視圖的目的是方便查詢，所以 一般情況下不對視圖進行增改，不能刪。

 

周旭龍

出處：http://edisonchou.cnblogs.com

本文著作權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連結。

走向面試之經典的資料庫基礎：二、SQL進階之case、子查詢、分頁、join與視圖