本篇文章作為第二週的最後篇，我們將介紹一些 SQL 的進階用法。

--------系列簡介--------

網站系統可說是現在最多學子與新人想要入門的一個領域，
這個原本屬於新興的領域，這幾年來也累積許多年的知識與 pattern ，
在有限的環境（stateless）與有限的伺服器端、瀏覽器資源下，
成為許多人進入程式的一塊入門鐵板(?)。

這個系列希望能夠就網站系統設計幾個門檻著手，
這是設定給初心者作為學習，給同好們作為回顧，

重新認識有關網站系統的每個環節。
最近這幾天都在寫 DB 的文章，重新 review 很多細節，
跟第一週一樣，還是有很多細節很難一一提到，

不過這也是連續三十天寫文章的好處之一，
我們可以有機會一直對照，跟對照之前沒有講完的地方。

今天我們要聊入門最後的幾個重點，Join 以及 Key、Index。

＠ 多資料表的查詢

對資料庫的操作，最多的地方還是在於資料的讀取這塊，
像是我們之前提到的留言板例子。

我們有一個叫做 MessageBoard 的資料表，其中有 userId ，
對應到 User 資料表的 UserId 欄位。

架設我們想撈出所有使用者的留言，並加上使用者的 email ，

單靠我們目前所學會的單一資料表查詢是不夠的，
所以接下來我們要教對初學者而言，最常見的跨資料表查詢 (Join)。

我們最常見的查詢是左查詢（Left Join），

這指的是我們以 Messageboard 資料表為主體(Left)，
希望透過這個資料表查詢與其相關的 User 資料表（Right）上的資料。

多資料表的查詢比較複雜一點，
所以我們先列出簡單的表達式，不過還是以實際例子為主作為說明。

select <資料表>.<欄位> , <資料表>.<欄位> ,<資料表>.<欄位>
from <資料表> left join <資料表> on <資料表關聯條件>

以剛剛 messageboard 額外知道使用者 email 的範例是

select MessageBoard.*,User.email
from Messageboard left join User
on Messageboard.userid = user.userid ;

以上說得事情是以 Messageboard 的資料去查詢 User 的資料表，
如果這一筆 Messageboard 的資料在 User 資料表有出現，
那就把 User 資料表的 email 也一併顯示。

線上測試範例 http://sqlfiddle.com/#!2/93871/27/0

＠ 如果 UserID 真的沒有資料呢？

像這個例子，給定 User Id 是 8 ，但是User Table 只有 1,2,3 ，
然後用剛剛的查詢語法，我們來查詢看看會是什麼結果。

insert into messageboard(messageID,MessageCategory,
Author,UserId ,Content,postDate )
values (null,3,'路人',8,'天線寶寶說再見～','2012/10/7 17:05:00');

你會發現，這一筆查詢到的 email 是 null，
如果給定的 join 條件沒有資料就會是這個結果，這就是所謂的左查詢。

＠ 多個資料表以上的查詢？假設我不只希望讀取 email ，還希望顯示類別？

select MessageBoard.*,User.email,messagecategory.name
from Messageboard
left join User
on Messageboard.userid = user.userid
left join messagecategory
on Messageboard.MessageCategory =
messagecategory.messageCategoryID;

線上測試範例：http://sqlfiddle.com/#!2/93871/31/0

只要依序加上新的查詢條件就可以了。

＠ 選取欄位跟設定條件時都要打資料表全名，又長有難記，有沒有別的方法？

我們可以取資料料表別名（alias）來幫助操作，
取別名的方式是在資料表名稱後面空一格，輸入你希望的別名。

如以上的程式碼可以簡化成

select msg.*,u.email,mg.name
from Messageboard msg
left join User u
on msg.userid = u.userid
left join messagecategory mg
on msg.MessageCategory =
mg.messageCategoryID;

線上測試案例：http://sqlfiddle.com/#!2/93871/32/0

其中 msg , u , mg 是三個不同資料表的別名。

＠ 不同資料表的欄位名稱重複？

舉個例子，假設我們今天希望使用 user 資料表的 Name 取代 author 欄位，
但是我們上面的查詢已經有一個 name 欄位，這時候會怎麼樣？

如以下例子

select msg.*,u.email,u.name,mg.name
from Messageboard msg
left join User u
on msg.userid = u.userid
left join messagecategory mg
on msg.MessageCategory =
mg.messageCategoryID;

線上測試範例：http://sqlfiddle.com/#!2/93871/33/0

你會少了一個 Name 欄位，因為重複的資料被濾掉了。
這時候我們就需要為這個欄位取個別名，避免重複。

在欄位名稱後加上 as <修改後別名> ，就可以了。如以下範例

select msg.*,u.email,u.name,mg.name as categoryName
from Messageboard msg
left join User u
on msg.userid = u.userid
left join messagecategory mg
on msg.MessageCategory =
mg.messageCategoryID;

線上測試範例：http://sqlfiddle.com/#!2/93871/34/0

＠ 有什麼我應該知道的？

多層資料表查詢因為需要參考許多表格，所以常常有機會進行資料表掃描(table scan)，
所以查詢時應該盡量避免多層 join （ex.4-5層以上的 join）或是條件太複雜的 join。

不然在處理大量資料時（幾十萬、百萬、千萬甚至億等級數量的資料），會有效率問題。

＠ 我們前兩篇文章一直提到主鍵，什麼是主鍵？

我們在聊資料的時候提過，資料需要一個識別資料的方式，
在資料庫裡面有一個很容易去要求大家要遵守這個遊戲規則的方法，

就是為這個資料表設定一個絕對不會重複資料的限制，這就是主鍵。

像是如果是紀錄台灣社會的人事資料庫，人事資料就可以以身份證字號當作主鍵，
因為台灣的社會裡面身份證字號是不會重複的。

如果插入或修改資料的結果，會導致這個主鍵重複，
資料庫可以直接認定這個操作是錯誤的並且告知操作者，這就是主鍵。

另外主鍵通常也會扮演索引的角色，
所以編輯或刪除透過主鍵來進行，通常效率都會比較好。

＠ 什麼是索引？

索引指的事情是只用一兩個資料欄位來區隔整個資料表，以此來加速查詢。

像是我們閱讀一般的書籍，我們可能會用章節的標題，
來進行快速的跳閱，索引也是一樣。

假設我們今天有生日欄位並設為索引，

資料庫建立索引時可能會先另外將這份資料在資料庫中循序的建立一份索引檔，
當我們下 where 條件查詢時，他可以從年開始搜尋起，再搜尋月，再搜尋日。

這樣可以避開一些重複或者資料不必要的查找，
其中又有細分為 CLUSERTERED 跟 NONCLUSTERED 兩種索引。

不過這些細節太深了，我們等之後有機會碰到再談。

其中最糟的狀況就是全資料表查詢（table scan）。

對一般的使用者入門而言，如果有大量資料時常見的查詢效率低落的現象，
可以幫常用或者緩慢的查詢進行索引。

更重要的還是每個資料表都應該要建立主鍵。

＠ 還有什麼我們沒有提到的？

View 、 Store Procedure 、 trigger ，

View 是用來將一些我們常用的查詢預先定義起來的作法。
Store Procedure 是進行定義一些複雜邏輯操作 (if-else ..etc)用的。

trigger 可以幫助我們在資料庫進行資料異動（增刪）時自動進行一些我們設定的行為。

另外我們在這幾篇裡面，礙於時間並沒有提到常見的函式方法，
像是 count , sum , group by 等 SQL 語法用途，

這些等我們第三週之後一定會用到，到時會在進行詳述。

那麼，這週雖然講得很淺，希望對大家還是多少有些幫助。:)

第三週開始我們就要進入真正的網站開發了。