SQLite 資料庫應用實戰：沙盒路徑、查詢語法、子查詢與多表連接

SQLite 資料庫應用實戰：沙盒路徑、查詢語法、子查詢與多表連接

在 iOS/macOS 開發中，SQLite 常作為本地輕量級資料庫使用。本文整理沙盒路徑獲取、常用查詢語法、子查詢模式、多表連接與分組統計實戰技巧。

核心基礎知識

沙盒路徑獲取 (Objective-C)

// 正確寫法：使用 firstObject 避免越界
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"myDatabase.sqlite"];

排序關鍵字

• ASC / asc：升序 (預設)
• DESC / desc：降序

實戰查詢範例 (假設表：t_student, t_class)

1. 基礎條件查詢與排序

-- 查詢年齡 > 25 的學生姓名、班級編號，按學號降序
SELECT s.stu_id, s.stu_age, s.stu_name, s.class_id
FROM t_student s
WHERE s.stu_age > 25
ORDER BY s.stu_id DESC;

-- 查詢年齡 > 25 且班級為 1 或 2 的學生，按學號升序
SELECT s.stu_name, s.stu_id, s.stu_age, s.class_id
FROM t_student s
WHERE s.stu_age > 25 AND s.class_id IN (1, 2)
ORDER BY s.stu_id;

2. 子查詢應用 (重點)

-- 3. 查詢年齡大於平均年齡的學員
SELECT * FROM t_student
WHERE stu_age > (SELECT AVG(stu_age) FROM t_student);

-- 4. 查詢班級名稱為「班級1」的所有學員 (班級編號未知)
SELECT * FROM t_student
WHERE class_id = (SELECT class_id FROM t_class WHERE class_name = '班級1');

-- 5. 查詢除「班級1」外的所有學員
SELECT * FROM t_student
WHERE class_id != (SELECT class_id FROM t_class WHERE class_name = '班級1');

3. 多表連接 (JOIN) - 核心技能

-- 隱式內連接 (舊語法，不推薦)
SELECT stu.stu_name, stu.stu_age, cla.class_name
FROM t_student stu, t_class cla
WHERE stu.class_id = cla.class_id;

-- 顯式內連接 (ANSI 標準，推薦)
SELECT s.stu_id, s.stu_name, c.class_id, c.class_name
FROM t_student s
INNER JOIN t_class c ON s.class_id = c.class_id
WHERE s.stu_age > 25;

-- 查詢「班級1」和「班級2」的學生與班級資訊
SELECT s.stu_id, s.stu_name, c.class_id, c.class_name
FROM t_student s
INNER JOIN t_class c ON s.class_id = c.class_id
WHERE c.class_name IN ('班級1', '班級2');

4. 分組統計 (GROUP BY) 與 HAVING

-- 統計每個班級人數
SELECT class_id, COUNT(*) AS student_count
FROM t_student
GROUP BY class_id;

-- 統計每班最大/最小/平均/總和年齡
SELECT class_id, 
       MAX(stu_age) AS max_age,
       MIN(stu_age) AS min_age,
       AVG(stu_age) AS avg_age,
       SUM(stu_age) AS sum_age
FROM t_student
GROUP BY class_id;

-- 查詢平均年齡 > 25 的班級資訊 (HAVING 過濾分組後結果)
SELECT class_id, AVG(stu_age) AS avg_age
FROM t_student
GROUP BY class_id
HAVING AVG(stu_age) > 25;

-- 複雜：查詢平均年齡 > 25 的班級及其學生詳細資訊 (子查詢 + 連接)
SELECT s.stu_name, s.stu_age, c.class_name
FROM t_student s
INNER JOIN t_class c ON s.class_id = c.class_id
WHERE c.class_id IN (
    SELECT class_id FROM t_student
    GROUP BY class_id
    HAVING AVG(stu_age) > 25
);

效能與寫法建議

• 優先使用顯式 JOIN：語義清晰，易於維護，優化器處理更好。
• 子查詢 vs JOIN：現代優化器常能自動轉換，但 EXISTS 通常優於 IN 子查詢。
• 索引策略：外鍵欄位 (class_id)、WHERE 條件欄位 (stu_age)、JOIN 條件欄位必建索引。
• 避免 SELECT *：明確列出需要欄位，減少 I/O 並利用覆蓋索引。
• 參數化查詢：代碼中務必使用 sqlite3_bind_* 防止注入並利用查詢計畫快取。