SQLite 指令實戰手冊：從建庫、建表、CRUD 到匯入匯出、備份還原完整流程

SQLite 是輕量級、無伺服器、零設定的關聯式資料庫，廣泛用於行動裝置、嵌入式系統與桌面應用。本文以指令列模式為主，完整示範資料庫全生命週期操作。

資料型別對照

亦可接受 datetime, varchar, decimal 等別名，內部會對應上述五大型別。

基本流程：建庫、進入、離開

$ sqlite3 bookstore.db          # 不存在則建立，存在則開啟
sqlite> .databases                # 列出掛載的資料庫
sqlite> .help                     # 顯示所有點命令
sqlite> .quit                     # 離開

建表、修改結構、刪表

-- 建表
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title NVARCHAR(50),
  pubdate DATETIME
);
CREATE TABLE publishers (id INTEGER PRIMARY KEY, name VARCHAR(20));

-- 檢視
.tables
.schema books

-- 加欄位
ALTER TABLE books ADD COLUMN publisher TEXT NOT NULL DEFAULT '' COLLATE NOCASE;

-- 改表名
ALTER TABLE publishers RENAME TO authors;

-- 改欄位名/型別（SQLite 不支援直接 ALTER COLUMN，採重建表法）
ALTER TABLE authors RENAME TO authorsOld;
CREATE TABLE authors (id INTEGER PRIMARY KEY, author NVARCHAR(20));
INSERT INTO authors SELECT id, name FROM authorsOld;
DROP TABLE authorsOld;

-- 刪表
DROP TABLE authors;

資料操作：增、查、改、刪

-- 單筆插入
INSERT INTO books (id, title, pubdate, publisher)
VALUES (1, '交換機.路由器.防火牆', '2007-08-28', '電子工業出版社');

-- 省略自增 id
INSERT INTO books (title, pubdate, publisher)
VALUES ('英語沙龍 實戰版', '2005-12-31', '世界知識出版社');

-- 多筆 UNION ALL 插入
INSERT INTO books (id, title, pubdate, publisher)
SELECT 100, '軟體專案管理', '2007-08-30', '中國電力出版社' UNION ALL
SELECT 105, '初級會計電算化', '2006-03-27', '中國財政經濟出版社';

-- 查詢分頁（LIMIT/OFFSET）
SELECT * FROM books LIMIT 5 OFFSET 0;   -- 第 1 頁
SELECT * FROM books LIMIT 5 OFFSET 5;   -- 第 2 頁

-- 推薦：以主鍵游標分頁（效能穩定）
SELECT * FROM books
WHERE id >= (SELECT id FROM books ORDER BY id LIMIT 1 OFFSET 0)
LIMIT 5;

輸出格式化與匯出

-- 直欄顯示
.headers on
.mode line
SELECT * FROM books;

-- 匯出 INSERT 腳本
.mode insert rebooks
SELECT * FROM books;   -- 輸出 INSERT INTO rebooks VALUES(...);

-- 匯出 CSV
.mode list
.separator ,
.output books.csv
SELECT * FROM books;
.output stdout

-- 完整資料庫備份為 SQL
sqlite3 bookstore.db .dump > backup.sql

-- 從 SQL 還原
sqlite3 new.db < backup.sql

-- 從 CSV 匯入
CREATE TABLE rebooks (id INTEGER PRIMARY KEY, title NVARCHAR(50), pubdate DATETIME, publisher TEXT);
.separator ,
.import books.csv rebooks

檢視、索引、系統資訊

-- 檢視
CREATE VIEW v_books AS SELECT * FROM books;

-- 索引
CREATE INDEX idx_books_publisher ON books(publisher);
.indices books

-- 列出所有表（含暫存表）
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name;

-- 內建函數速查
-- 數學: abs, max, min, random, round
-- 字串: length, lower, upper, substr, like, glob
-- 條件: coalesce, ifnull, nullif
-- 聚合: avg, count, max, min, sum
-- 其他: typeof, last_insert_rowid, sqlite_version, changes

備份與刪除資料庫檔案

# 熱備份（線上）
sqlite3 bookstore.db .dump > bookstore_$(date +%Y%m%d).sql

# 刪除檔案
rm -f bookstore.db

小結

掌握 .mode、.output、.separator 三大點命令組合，即可靈活處理匯入匯出、報表產生與資料遷移。SQLite 雖輕量，但功能完整，善用系統表（sqlite_master）與點命令，即能高效管理。