SQLite 入門實戰（四）：CRUD、運算子、邏輯查詢、排序分組、日期時間、常用函數與備份

SQLite 核心 SQL 操作完整指南

承接前三篇安裝、點命令、建表，本文深入 CRUD 語法、比較/邏輯運算子、排序分組去重、日期時間處理、聚合函數、字串函數、索引視圖、備份還原，所有指令均在 sqlite3 CLI 實測可用。

一、儲存類別 (Storage Classes)

型別親和性：欄位宣告型別僅為建議，SQLite 允許任意類別寫入任意欄位 (除 INTEGER PRIMARY KEY 強制整數)。

二、建表與 CRUD

CREATE TABLE company (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    age INT NOT NULL,
    address CHAR(50),
    salary REAL
);

-- 插入 (指定欄位)
INSERT INTO company (id, name, age, address, salary) VALUES (1, 'Paul', 32, 'California', 20000);
-- 插入 (全欄位，id 自動遞增)
INSERT INTO company VALUES (7, 'James', 24, 'Houston', 10000);
-- 批次插入
INSERT INTO company VALUES (2,'Allen',25,'Texas',15000),(3,'Teddy',23,'Norway',20000);

-- 查詢
SELECT * FROM company;
SELECT * FROM company WHERE age < 30;

-- 更新
UPDATE company SET address = 'NanJing' WHERE name = 'wirelessqa';

-- 刪除
DELETE FROM company WHERE address = 'NanJing';

三、比較與邏輯運算子

四、排序、分組、去重、限制

-- 排序
SELECT * FROM company ORDER BY salary ASC;
SELECT * FROM company ORDER BY age, salary DESC;

-- 分組聚合
SELECT name, SUM(salary) FROM company GROUP BY name;
SELECT name, SUM(salary) FROM company GROUP BY name ORDER BY name DESC;

-- HAVING 分組過濾
SELECT name, COUNT(*) FROM company GROUP BY name HAVING COUNT(*) > 1;

-- 去重
SELECT DISTINCT age FROM company;

-- 限制筆數
SELECT * FROM company LIMIT 4;

五、日期時間函數

-- 當前日期/時間
SELECT date('now');           -- 2024-05-18
SELECT time('now');           -- 14:30:00
SELECT datetime('now');       -- 2024-05-18 14:30:00

-- Unix 時間戳轉換
SELECT datetime(1092941466, 'unixepoch');  -- 2004-08-19 18:51:06

-- 日期運算
SELECT date('now', '+7 days');
SELECT date('now', 'start of month');
SELECT date('now', '-1 month', '+3 days');

六、常用聚合與字串函數

七、索引、視圖、備份還原

-- 索引
CREATE INDEX idx_salary ON company(salary);
-- 視圖
CREATE VIEW high_salary AS SELECT * FROM company WHERE salary > 50000;
-- 備份 (SQL 文本)
sqlite3 company.db .dump > backup.sql
# 或二進位熱備份 (需 3.27+)
sqlite3 company.db ".backup backup.db"
-- 還原
sqlite3 company.db < backup.sql
# 或
sqlite3 company.db ".restore backup.db"
-- 刪除表
DROP TABLE company;
-- 刪除資料庫檔案
rm company.db

八、CSV 匯入匯出

-- 匯出 CSV
.mode csv
.headers on
.output data.csv
SELECT * FROM company;
.output stdout

-- 匯入 CSV
CREATE TABLE new_tbl (id INTEGER PRIMARY KEY, name TEXT);
.mode csv
.import data.csv new_tbl

總結

SQLite 雖輕量，但標準 SQL 功能極完整：型別親和性、日期時間運算、視窗函數 (3.25+)、JSON1 擴充、FTS5 全文搜尋、R-Tree 空間索引。掌握本文 CRUD、運算子、分組、日期、備份匯出全流程，即可在任何嵌入式/桌面/行動專案中自信驅動 SQLite。