《深入淺出 SQL》筆記

一、SELECT 進階

1.SQL 基本語法：

自行參閱：SQL語法教學


2.CASE 關鍵字：

可檢查現有資料欄位的值與條件，若現有資料欄位的值符合條件，才會在新資料欄中填入指定的值。

另外也可用來告訴RDBMS，如果沒有記錄符合條件時的處理方式。

自行參閱：SQL CASE


3.ORDER BY 關鍵字：

可根據條件自動將資料欄排列。

自行參閱：SQL ORDER BY


4.GROUP BY 關鍵字：

可根據資料欄位中的值分成群組。

自行參閱：SQL GROUP BY


5.MAX、MIN 函式：

尋找資料欄位中的最大/最小值。

自行參閱：SQL 最大值、SQL 最小值


6.COUNT 函式：

可回傳指定資料欄位的列數。

自行參閱：SQL COUNT


7.DISTINCT 關鍵字：

顯示不重複的欄位值。

自行參閱：SQL DISTINCT


8.LIMIT 關鍵字：

限制顯示的查詢結果數量。

自行參閱：SQL limit




二、複數資料表的資料庫設計

1.資料表應是為了節省精力而設計。別為了克服設計不良的資料表，而執意改善查詢。


2.對資料表內的資料描述（資料欄與資料表），以及任何相關物件和各種連結方式的描述，就稱為綱要（SCHEMA）。


3.主鍵（Primary Key）：

各儲存格資料具備唯一不重複特性的欄位。

自行參閱：SQL 主鍵


4.外來鍵（Foreign Key）：

儲存格資料可重複且與相關聯資料表的主鍵資料對應。

＊外來鍵為 NULL 的作用，表示父資料表裡沒有相符的主鍵。

自行參閱：SQL 外來鍵


5.接合資料表 （Junction Table）：

若不管在哪個資料表裡添加外來鍵，都會造成資料表裡出現重複資料，則需要一個中介橋梁，簡化多對多的關聯性，此 junction table 為用來儲存兩個相關資料表的主鍵。


6.功能相依性（Functional Dependency）：

（1）完全功能相依（Full Functional Dependency）：若主鍵是由多個屬性組合而成，且某非鍵值的資料欄位依賴主鍵之全部時。

（2）部分功能相依（Partial Functional Dependency）：若主鍵是由多個屬性組合而成，且某非鍵值的資料欄位依賴主鍵之部分時。

（3）遞移功能相依（Transitive Dependency）：任何非鍵值的資料欄位，與另一個非鍵值的資料欄位有相關性。


7.正規化（Normalization）：

（1）第一正規化（1NF）：

• 規則一：資料欄只包含具單元性的值。
• 規則二：沒有重複的資料群組。

步驟：

1. 刪除各個資料表中的重複群組。
2. 為每一組關聯的資料建立不同的資料表。
3. 使用主索引鍵識別每一組關聯的資料。

（2）第二正規化（2NF）；

• 規則一：先符合 1NF。
• 規則二：沒有部份功能相依。

步驟：

1. 為可套用於多筆記錄的多組值建立不同的資料表。
2. 使用外來鍵，讓這些資料表產生關聯。

（3）第三正規化（3NF）：

• 規則一：符合 2NF。
• 規則二：沒有遞移功能相依。

步驟：

1. 刪除不依賴索引鍵的欄位。

＊通常只針對變更頻繁的資料運用第三正規形式。如果保留某些相依的欄位，請將應用程式設計為要求使用者在欄位變更時，驗證所有相關聯的欄位。

自行參閱1：說明資料庫正規化基本概念

自行參閱2：資料庫正規化和設計技巧




三、聯結與複數資料表的操作

1.AS 關鍵字：

用於暫時對資料表與資料欄位賦予新名稱，以避免使用者把各種名稱搞混。

自行參閱：SQL AS


2.聯結（JOIN）關鍵字：

內部聯結（INNER JOIN）：利用條件式裡的比較運算子結合兩份資料表。

• EQUIJOIN( = )：檢查相等性。
• NON-EQUIJOIN( <> )：檢查非相等性。
• NATURAL JOIN：自動匹配兩個表之間相同的欄位。

外部聯結（OUTTER JOIN）：列出一個表格中每一筆的資料， 無論它的值在另一個表格中有沒有出現。

• LEFT OUTER JOIN：接受左資料表的所有記錄，並拿這些記錄與右資料表比較。常用於左資料表與右資料表具一對多關聯性時。
• RIGHT OUTER JOIN：接受右資料表的所有記錄，並拿這些記錄與左資料表比較。

交叉聯結（CROSS JOIN）：顯示兩個資料表的每一列相乘的結果。

自行參閱1：JOIN (INNER, LEFT, RIGHT, FULL and CROSS)

自行參閱2：[MySQL]left, right, inner, outer join 使用方法

自行參閱3：SQL联合语句的视觉解释


3.聯集（UNION）關鍵字：

用於取得複數資料表內容。

自行參閱1：SQL UNION
自行參閱2：SQL UNION ALL


4.聯集的規則：

• 只接受一個 ORDER BY，且須位於敘述末端。
• 每個 SELECT 敘述裡的資料欄位數量必須一致。
• 每個 SELECT 敘述須包含相同運算式與統計函式。
• SELECT 敘述的順序不重要，並不會改變結果。
• SQL 預設為清除聯集結果裡的重複列。
• 資料欄位的型別必須相同，或者要能互相轉換。

四、子查詢

1.子查詢（Subquery）：

用於單一查詢不夠用時。

自行參閱：SQL 子查詢


2.子查詢的規則：

• 子查詢都是一個 SELECT 敘述。
• 子查詢總是位於括號裡。
• 子查詢沒有屬於自己的分號。
• 子查詢能與 INSERT、DELETE、UPDATE、SELECT 一起使用。

3.子查詢可能出現在查詢中的四個地方：

（1）SELECT 子句。
（2）COLUMN LIST 做為其中一欄。
（3）FROM 子句。
（4）HAVING 子句。




五、限制條件、視觀表、交易

1.CHECK 函式：

檢查限制允許插入某個資料欄位的值。

自行參閱：SQL Constraint


2.視觀表（View）：

為建立在表格之上的一個架構，它本身並不實際儲存資料。

自行參閱：View檢視表之相關心得


3.判斷 SQL 步驟是否為「一次交易」的四個原則：

（1）單元性：交易裡的每一個步驟都必須完成，否則只能都不完成。
（2）一致性：交易執行完畢後，應維持資料庫的一致性。
（3）獨立性：每次交易都會看到具有一致性的資料庫狀況，無論其他交易有什麼行動。
（4）耐久性：交易完成後，資料庫需要正確地儲存資料，並保護資料受到威脅。


4.SQL 的三種安全交易工具：

（1）START TRANSACTION：持續追蹤後續所有 SQL 敘述，直到輸入 COMMIT 或 ROLLBACK 為止。
（2）COMMIT：確認所有敘述。
（3）ROLLBACK：復原到交易開始前的狀態。




六、安全性

1.MySQL 權限設定：

自行參閱：MySQL 存取權限系統




延伸閱讀：《新觀念 資料庫系統理論與設計實務》筆記