關聯式資料庫與 SQL

怎麼問資料庫問題

上一課把選課紀錄拆成了學生、課程、選課三張表。教務處每天要回答很多問題： 「資管系有哪些學生？」「CS101 誰的分數最高？」「每個系各有幾個人？」「王小明修了哪些課？」

這些問題都用 SQL（Structured Query Language，結構化查詢語言）來問。它起源於 1974 年 IBM 的錢伯林（Donald Chamberlin） 與博伊斯（Raymond Boyce）設計的 SEQUEL，後來改名為 SQL；1986 年成為美國國家標準（ANSI），1987 年成為國際標準（ISO）。 MySQL、PostgreSQL、SQLite、Oracle、SQL Server 都用 SQL，各家有少數自己的寫法，但基本的指令相同。這一課的練習以 MySQL 8.0 為準。

SQL 和前幾課的程式語言有一個很大的不同：它是宣告式（declarative）的，你只描述「要什麼資料」， 不寫「怎麼一筆一筆去找」；怎麼找最快，由資料庫自己決定。

SQL 的指令大致分成幾類：

這一課的練習資料庫 school 有三張表，欄位名稱用英文（實務上的慣例）：

SELECT：查詢、篩選與排序

最基本的查詢長這樣：

SELECT 欄位1, 欄位2      -- 要哪些欄位；* 代表全部欄位
FROM 資料表               -- 從哪一張表
WHERE 條件                -- 只要符合條件的列（可省略）
ORDER BY 欄位 DESC        -- 依哪個欄位排序：ASC 由小到大（預設），DESC 由大到小（可省略）
LIMIT 3;                  -- 只要前幾列（可省略）

SELECT name, year FROM students WHERE dept = '資管系' ORDER BY year DESC;

WHERE 的條件可以這樣寫：

SQL 的關鍵字不分大小寫，習慣上寫成大寫，比較容易和欄位名稱區分；每個敘述用分號 ; 結束。

統計：聚合函數與 GROUP BY

聚合函數（aggregate function）把很多列算成一個值：

SELECT COUNT(*) FROM students;              -- 全校有幾位學生：一個數字
SELECT AVG(score) FROM enrollments;         -- 所有分數的平均

想知道「每個系有幾位學生」，就要先分組再統計，用 GROUP BY：資料庫把 dept 相同的列放進同一組，每一組算出一列結果。

SELECT dept, COUNT(*) FROM students GROUP BY dept;

students 依 dept 分組                         結果
資管系：S001、S004              ──COUNT──▶   資管系  2
中文系：S002、S006              ──COUNT──▶   中文系  2
物理系：S003、S008              ──COUNT──▶   物理系  2
資工系：S005、S007              ──COUNT──▶   資工系  2

對分組後的結果再篩選，要用 HAVING，不是 WHERE：WHERE 在分組之前篩掉個別的列，HAVING 在分組之後篩掉整組。

SELECT course_id, AVG(score) FROM enrollments
GROUP BY course_id
HAVING AVG(score) >= 75;                    -- 只留平均 75 分以上的課

一個查詢裡各子句的執行順序和書寫順序不同，理解這個順序就知道為什麼 WHERE 不能用聚合函數：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

JOIN：把資料表合併起來

選課表只記學號與課號，要看到姓名和課名，就要把資料表依外鍵合併（JOIN）：

SELECT s.name, c.title, e.score
FROM enrollments e
JOIN students s ON e.student_id = s.student_id     -- 學號相同的接在一起
JOIN courses  c ON e.course_id  = c.course_id      -- 課號相同的接在一起
WHERE s.name = '王小明';

• enrollments e 的 e 是別名，之後可以用 e.score 代替 enrollments.score；兩張表有同名的欄位（例如 student_id）時一定要寫清楚是哪一張的。
• ON 後面寫配對的條件，通常是「外鍵 = 對方的主鍵」。
• 只寫 JOIN（也就是 INNER JOIN）時，只留下兩邊都配得到的列。像臺灣史 HI110 沒有人選，就不會出現在結果裡； 想保留沒配到的列，要用 LEFT JOIN，沒配到的欄位會是 NULL。

資料庫怎麼找出「學號相同」的列？最直接的方法是巢狀迴圈：拿每一位學生，和每一筆選課紀錄比一次。下面用 Python 把這個過程走一遍。

資料多的時候：索引

巢狀迴圈要比 學生數 × 選課數 次：1 萬位學生、10 萬筆選課，就是 10 億次比較。 真正的資料庫會在主鍵、外鍵這類常用來查詢的欄位上建立索引（index），就像書後面的索引頁，可以直接跳到學號相同的那幾列， 不必每一對都比。主鍵一定有索引；其他欄位可以用 CREATE INDEX 另外建立。

這正是 SQL 宣告式的好處：同一個 JOIN 查詢，資料庫會依資料量與有沒有索引，自己選擇巢狀迴圈、索引查找或雜湊等方法，你寫的 SQL 不用改。

新增、修改、刪除資料

-- 新增一列：值的順序要和欄位順序相同；也可以先列出欄位名稱
INSERT INTO students (student_id, name, dept, year) VALUES ('S009', '蔡宜蓁', '資工系', 1);

-- 修改：SET 新的值，WHERE 指定要改哪幾列
UPDATE students SET dept = '資工系' WHERE student_id = 'S003';

-- 刪除：WHERE 指定要刪哪幾列
DELETE FROM enrollments WHERE student_id = 'S002' AND course_id = 'EN201';

兩件一定要記住的事：

1. UPDATE 與 DELETE 忘了寫 WHERE，會作用在整張表的每一列。 很多資料庫事故就是這樣發生的。 修改前可以先用同樣的 WHERE 寫一個 SELECT，確認選到的列對了再改；重要的修改放在交易（START TRANSACTION … COMMIT）裡，出錯時可以 ROLLBACK 回復。
2. 外鍵會擋下破壞連結的修改。 王小明還有選課紀錄時，直接刪除學生表裡的他，選課表就會指向一位不存在的學生， 所以資料庫會拒絕，回報錯誤代碼 1451。要先刪掉（或改掉）參照他的選課紀錄，才能刪除學生； 建立外鍵時也可以設定 ON DELETE CASCADE，讓刪除學生時一併刪除他的選課紀錄。

重點整理

• SQL 是關聯式資料庫共通的語言（起源於 1974 年 IBM 的 SEQUEL，1986 年 ANSI、1987 年 ISO 標準），是宣告式的：描述要什麼，不描述怎麼找
• SELECT 欄位 FROM 表 WHERE 條件 ORDER BY 欄位 DESC LIMIT n：選欄位、篩選列、排序、取前幾列
• 聚合函數（COUNT、SUM、AVG、MAX、MIN）把很多列算成一個值；GROUP BY 先分組再統計，分組後的篩選用 HAVING
• 子句的執行順序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• JOIN … ON 外鍵 = 主鍵 合併資料表；最直接的做法是巢狀迴圈，資料庫會用索引讓它變快
• INSERT 新增、UPDATE … SET … WHERE 修改、DELETE FROM … WHERE 刪除；忘了 WHERE 會作用在整張表
• 外鍵會擋下讓資料指向不存在對象的新增與刪除

下一課：資料庫通常放在伺服器上，你的電腦要透過網路才能連到它。接下來進入網路：區域網路、廣域網路、路由器與交換器。 想實際寫更多 SQL（子查詢、交易、檢視表、權限），可以接著上 SQL 軌。