SQLite3 命令大全：從入門到精通的完整參考手冊

SQLite3 命令行工具完整操作指南

基礎操作

# 查看版本
sqlite3 --version

# 創建/打開數據庫（文件不存在則自動創建）
sqlite3 test.db

# 退出命令行
.quit 或 .exit

顯示格式配置

# 查看當前設置
.show

# 切換顯示模式
.mode column    # 列對齊模式（推薦）
.mode list      # 默認列表模式
.mode csv       # CSV格式
.mode html      # HTML表格
.mode line      # 每行一字段

# 設置標題顯示
.header on / off

# 設置NULL值顯示
.nullvalue 'NULL'

# 自定義分隔符
.separator ','

數據庫與表操作

# 建表
CREATE TABLE students(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER,
  score REAL CHECK(score >= 0)
);

# 插入數據
INSERT INTO students(name, age, score) VALUES('張三', 20, 95.5);

# 查詢數據
SELECT * FROM students;
SELECT name, score FROM students WHERE age > 18 ORDER BY score DESC LIMIT 5;

# 更新數據
UPDATE students SET score = 98 WHERE name = '張三';

# 刪除數據
DELETE FROM students WHERE id = 1;

進階查詢技巧

# 條件查詢
SELECT * FROM students WHERE name LIKE '張%';     # 模糊匹配
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
SELECT * FROM students WHERE name IN ('張三','李四');

# 聚合統計
SELECT COUNT(*) FROM students;
SELECT AVG(score) FROM students GROUP BY age;
SELECT name, SUM(score) FROM students GROUP BY name HAVING SUM(score) > 200;

# 去重與別名
SELECT DISTINCT age FROM students;
SELECT name AS 姓名, score AS 分數 FROM students;

數據導入導出

# 導出SQL腳本
.output backup.sql
.dump
.output stdout

# 導入SQL腳本
.read backup.sql

# 導出CSV
.mode csv
.header on
.output data.csv
SELECT * FROM students;
.output stdout

索引與性能

# 創建索引
CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age_score ON students(age, score);

# 查看索引
.indexes students

# 刪除索引/表
DROP INDEX idx_students_name;
DROP TABLE students;

配置持久化

將常用設置寫入~/.sqliterc（Linux/Mac）或設置環境變量SQLITE_INIT指向配置文件（Windows），實現啟動自動應用：

# .sqliterc 示例
.mode column
.header on
.nullvalue NULL
.separator |

數據類型與約束速查

ALTER TABLE 限制與變通

SQLite僅支持ADD COLUMN、RENAME TABLE，修改列類型/刪除列需：

BEGIN TRANSACTION;
CREATE TABLE new_table AS SELECT ... FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;
COMMIT;