SQLite 完整語法指南：從命令列操作到 DDL/DML 核心語法

SQLite 完整語法指南：從命令列操作到 DDL/DML 核心語法

SQLite 作為零配置、無伺服器、單檔案的輕量級資料庫，廣泛應用於嵌入式裝置、行動應用、桌面軟體與測試環境。本文系統整理 sqlite3 命令列工具操作、資料型別、約束條件、DDL/DML 核心語法與索引管理，提供快速查閱手冊。

一、sqlite3 命令列工具（Meta-Commands）

以 . 開頭，不需分號結尾。

二、資料型別（儲存類別）

SQLite 採用動態型別系統，值而非欄位決定型別：

• NULL：空值
• INTEGER：帶號整數（1~8 位元組）
• REAL：IEEE 754 浮點數
• TEXT：UTF-8/16BE/16LE 字串
• BLOB：二進位資料

型別親和性：欄位宣告型別僅影響隱含轉換偏好（INTEGER/TEXT/REAL/NUMERIC/BLOB）。

三、約束條件

四、核心 DDL/DML 語法

所有 SQL 指令以 ; 結尾，-- 為註解。

1. 建表

CREATE TABLE [IF NOT EXISTS] table_name (
    col1 TYPE [CONSTRAINT],
    col2 TYPE [CONSTRAINT],
    ...
);

-- 範例：學生資訊表
CREATE TABLE student_info (
    stu_no INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    id INTEGER UNIQUE,
    age INTEGER CHECK(age > 6),
    school TEXT DEFAULT 'XX小学'
);

2. 新增資料

INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
-- 多筆
INSERT INTO student_info (stu_no, name) VALUES (1, 'Alex'), (2, 'Bob');

3. 修改資料

UPDATE table_name SET col1=val1, col2=val2 WHERE condition;
UPDATE student_info SET name='Hence' WHERE stu_no=1;

4. 刪除資料

DELETE FROM table_name [WHERE condition];
DELETE FROM student_info WHERE stu_no=1;
-- 不加 WHERE 清空全表

5. 查詢資料

SELECT [DISTINCT] col1, col2, ... FROM table_name [WHERE expr] [ORDER BY col ASC|DESC] [LIMIT n];

-- 範例
SELECT * FROM student_info;
SELECT * FROM student_info LIMIT 10;
SELECT * FROM student_info ORDER BY age DESC;
SELECT * FROM student_info WHERE age BETWEEN 10 AND 20;
SELECT * FROM student_info WHERE name IN ('Alex','Bob');
SELECT COUNT(*) FROM student_info;
SELECT DISTINCT school FROM student_info;

6. 索引管理

CREATE INDEX idx_name ON table_name (col);
CREATE INDEX idx_stu_no ON student_info (stu_no);
DROP INDEX idx_name;

7. 刪除資料表

DROP TABLE [IF EXISTS] table_name;

五、進階實用技巧

• 參數化查詢：SELECT * FROM t WHERE id = ? 防注入、提升計畫複用
• 事務控制：BEGIN; ... COMMIT; / ROLLBACK; 預設自動提交
• WAL 模式：PRAGMA journal_mode=WAL; 大幅提升並發讀寫效能
• 分析統計：ANALYZE; 更新查詢規劃器統計資訊
• 真空整理：VACUUM; 回收空間、重組檔案（需獨佔鎖）

六、嵌入式開發建議

1. 連線字串加入 ?mode=rwc（讀寫建立）或 ?mode=ro（唯讀）
2. 關閉同步 PRAGMA synchronous=OFF（極高效能、斷電風險）或 NORMAL（平衡）
3. 設定 PRAGMA cache_size=-32768（32MB 頁面快取）
4. 使用預編譯語句 sqlite3_prepare_v2 避免重複解析