第 3 週 · 單元 1-3

資料庫入門：打開吃貨評分站的資料庫，用 SQL 算出排行榜

網站的資料不放在程式裡，而是放在資料庫。今天親手查詢、新增、統計，下週 AI 寫的 Flask 程式，你才看得懂它在對資料庫做什麼。

W3SQLSQLite

本週結束時你會

用 SELECT、WHERE、ORDER BY 查出指定的資料
用 COUNT、AVG、GROUP BY 做統計
用 JOIN 把店家和評論接起來，算出排行榜
知道資料庫怎麼擋下錯誤資料（CHECK、外鍵）

今天的時間安排

0:00–0:15	安裝 DB Browser for SQLite、打開 foodie.db
0:15–0:30	觀念：資料表、欄位、主鍵、外鍵
0:30–1:05	實作 1【跟著做】查詢：SELECT、WHERE、ORDER BY、COUNT
1:05–1:15	休息
1:15–1:50	實作 2【跟著做】JOIN 與 GROUP BY：算出排行榜
1:50–2:15	實作 3【自己做】新增資料、讓資料庫擋下錯誤；請 AI 寫 SQL 並對答案
2:15–2:30	把自己的 PROJECT.md 資料表寫成建表 SQL，本週檢核

準備：打開資料庫

到 sqlitebrowser.org 下載 DB Browser for SQLite（Windows 選 Standard installer，Mac 選 Intel 或 Apple Silicon 版）。
從課程資源下載 foodie.db，放進 程式設計三/w03/。
DB Browser →「開啟資料庫」→ 選 foodie.db。上方分頁：「資料庫結構」看有哪些表，「瀏覽資料」看內容，「執行 SQL」寫查詢。

foodie.db
├─ shops     店家 12 間   id · name · category · address · created_at
└─ reviews   評論 49 則   id · shop_id → shops.id · nickname · rating · comment · created_at

主鍵（id）是每一筆資料的身分證號碼，不會重複。外鍵（shop_id）記錄「這則評論屬於哪一間店」，指向 shops 表的 id。一間店可以有很多則評論，這叫做一對多。

不能安裝軟體的備案

在終端機輸入 python -m sqlite3 foodie.db（Python 3.12 以上內建），就能直接輸入 SQL。結果會以 Python 格式顯示，例如 ('縮排鍋燒意麵',)。

實作 1：查詢【跟著做】

在「執行 SQL」分頁輸入，按 Ctrl+Enter 執行。每一題都先猜結果，再執行。

-- 1. 看所有店家
SELECT * FROM shops;

-- 2. 只看麵類，只要店名
SELECT name FROM shops WHERE category = '麵';

-- 3. 派森牛肉湯（id = 1）最新的 2 則評論
SELECT nickname, rating, comment, created_at
FROM reviews
WHERE shop_id = 1
ORDER BY created_at DESC
LIMIT 2;

-- 4. 五顆星的評論有幾則
SELECT COUNT(*) FROM reviews WHERE rating = 5;

對答案

題	正確結果
1	12 間店
2	縮排鍋燒意麵、變數擔仔麵、函式炒鱔魚
3	小林 4 分「價格合理」、豆豆 5 分「排隊也值得」
4	18

第 3 題常見錯誤是忘了 DESC，結果拿到最舊的兩則。讓學生自己發現「最新」要由新排到舊。

實作 2：JOIN 與 GROUP BY，算出排行榜【跟著做】

平均分數在 reviews 表，店名在 shops 表。要把兩張表接起來：

-- 5. 各類別有幾間店
SELECT category, COUNT(*) AS 店家數
FROM shops
GROUP BY category
ORDER BY 店家數 DESC;

-- 6. 排行榜：每間店的平均分數與評論數
SELECT s.name, s.category,
       ROUND(AVG(r.rating), 1) AS 平均分數,
       COUNT(r.id)             AS 評論數
FROM shops s
LEFT JOIN reviews r ON r.shop_id = s.id
GROUP BY s.id
ORDER BY AVG(r.rating) IS NULL, AVG(r.rating) DESC, 評論數 DESC;

對答案：排行榜前 6 名與最後一名

名次	店家	平均	評論數	說明
1	派森牛肉湯	4.7	6	兩間都是 4.67 分，評論數多的排前面
2	函式炒鱔魚	4.7	3	兩間都是 4.67 分，評論數多的排前面
3	布林豆花	4.6	5
4	字典虱目魚粥	4.5	6	同為 4.5 分
5	迴圈碗粿	4.5	4	同為 4.5 分
6	例外蝦捲	4.4	7	評論最多，但平均較低
12	模組肉燥飯	（空白）	0	沒有評論，排最後

如果用 JOIN（不加 LEFT）

沒有評論的「模組肉燥飯」會直接消失，排行榜只剩 11 間店。

LEFT JOIN

保留左邊（shops）的每一間店，即使右邊（reviews）沒有對應的資料。

請學生把 LEFT JOIN 改成 JOIN 執行一次，親眼看到少了一間店。這是下週排行榜頁面最容易出錯的地方。

實作 3：新增資料，讓資料庫擋下錯誤【自己做】

-- 先開啟外鍵檢查（SQLite 預設是關閉的！）
PRAGMA foreign_keys = ON;

-- 7. 替模組肉燥飯（id = 12）新增一則評論
INSERT INTO reviews (shop_id, nickname, rating, comment)
VALUES (12, '你的暱稱', 5, '肉燥很香');

-- 8. 試試看：評分 6 分
INSERT INTO reviews (shop_id, nickname, rating) VALUES (1, '測試', 6);

-- 9. 試試看：不存在的店家 id = 99
INSERT INTO reviews (shop_id, nickname, rating) VALUES (99, '測試', 5);

題	應該看到	是誰擋下的
7	成功；再執行一次排行榜，模組肉燥飯有分數了	—
8	`CHECK constraint failed`	建表時的 `CHECK (rating BETWEEN 1 AND 5)`
9	`FOREIGN KEY constraint failed`	外鍵 `REFERENCES shops(id)`

練習完記得復原：DB Browser 上方按「撤銷變更」，資料庫會回到原本的 49 則評論，否則下週對答案的數字會不一樣。

請 AI 寫 SQL，再對答案

提示詞 Antigravity

我有一個 SQLite 資料庫，結構如下：
shops(id, name, category, address, created_at)
reviews(id, shop_id → shops.id, nickname, rating 1–5, comment, created_at)

請各寫一段 SQL，並逐行解釋：
1. 評論數最多的前 3 間店（店名、評論數；同數量時依店名排序）
2. 找出完全沒有評論的店家
3. 「小吃」類所有評論的平均分數，四捨五入到小數 2 位

題	正確結果
1	例外蝦捲 7、字典虱目魚粥 6、派森牛肉湯 6
2	模組肉燥飯（若你在實作 3 新增了評論且沒有復原，結果會是空的）
3	4.53

AI 的答案和上表不同時，不要直接相信任何一方：把 AI 的 SQL 貼進 DB Browser 執行，找出差在哪裡。

延伸：把你的 PROJECT.md 變成建表 SQL

提示詞 Antigravity

請先讀 PROJECT.md 的「3. 資料表」。
請寫出 SQLite 的建表 SQL：標出主鍵、必填欄位（NOT NULL）、外鍵（REFERENCES），並用 CHECK 限制不合理的值。
再寫 3 筆 INSERT 範例資料，以及一段我可以用來檢查關聯是否正確的 SELECT ... JOIN。

在 DB Browser 建立新資料庫並執行。三筆資料都能 JOIN 出來，資料表設計就通過了。

本週檢核

第 1–6 題的結果與標準答案一致
能說出 JOIN 和 LEFT JOIN 的差別
看過 CHECK 與外鍵擋下錯誤資料，並已復原資料庫
自己 PROJECT.md 的資料表能成功建立並 JOIN

第 4 週預告：示範專案開工。今天的排行榜 SQL，下週會原封不動出現在 Flask 程式裡。