SQLite 常用命令及技巧

SQLite 简介

SQLite 是一个轻量级的嵌入式关系数据库，具有以下特点：

  • 无需服务器，零配置
  • 单个文件存储整个数据库
  • 跨平台支持
  • 支持大部分 SQL92 标准
  • 适合小型应用、移动应用、嵌入式设备

连接与基本操作

安装 SQLite

1
2
3
4
5
6
7
8
# macOS
brew install sqlite

# Ubuntu/Debian
sudo apt-get install sqlite3

# Windows
# 下载预编译二进制文件：https://www.sqlite.org/download.html

连接数据库

1
2
3
4
5
6
7
8
9
10
11
# 创建或连接数据库
sqlite3 database.db

# 连接内存数据库
sqlite3 :memory:

# 连接指定数据库并执行SQL
sqlite3 database.db "SELECT * FROM table_name;"

# 以只读模式打开
sqlite3 file:database.db?mode=ro

SQLite 命令行工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 退出SQLite
.exit
.quit

-- 帮助
.help

-- 显示所有数据库
.databases

-- 显示所有表
.tables

-- 显示表结构
.schema table_name

-- 显示完整表结构（包含索引）
.schema

-- 显示索引
.indexes
.indexes table_name

-- 设置输出格式
.mode csv      -- CSV格式
.mode column   -- 列对齐
.mode html     -- HTML表格
.mode insert   -- INSERT语句
.mode line     -- 每行一个字段
.mode list     -- 列表（默认）
.mode tabs     -- 制表符分隔

-- 设置列标题显示
.headers on
.headers off

-- 设置输出宽度
.width 10 20 30

-- 输出到文件
.output filename.txt

-- 恢复标准输出
.output stdout

-- 执行SQL文件
.read filename.sql

-- 显示当前设置
.show

-- 设置NULL值显示
.nullvalue NULL

-- 设置主键提示
.prompt 'sqlite> '

-- 导出数据库为SQL
.dump
.dump table_name

-- 导入数据
.import filename.csv table_name

数据库操作

创建数据库

1
2
# SQLite在连接时自动创建数据库文件
sqlite3 new_database.db

附加数据库

1
2
3
4
5
6
7
8
-- 附加其他数据库
ATTACH DATABASE 'another.db' AS alias_name;

-- 使用附加的数据库
SELECT * FROM alias_name.table_name;

-- 分离数据库
DETACH DATABASE alias_name;

查看数据库信息

1
2
3
4
5
-- 查看所有数据库
.databases

-- 查看数据库文件位置
SELECT file FROM pragma_database_list;

表操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 基本创建
CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 如果不存在则创建
CREATE TABLE IF NOT EXISTS table_name (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 创建临时表
CREATE TEMP TABLE temp_table (
    id INTEGER,
    name TEXT
);

-- 从查询结果创建表
CREATE TABLE new_table AS SELECT * FROM old_table;

SQLite 数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- SQLite使用动态类型，有5种存储类：
-- NULL: 空值
-- INTEGER: 整数（1, 2, 3, 4, 6, 8字节）
-- REAL: 浮点数（8字节）
-- TEXT: 文本字符串（UTF-8, UTF-16BE, UTF-16LE）
-- BLOB: 二进制数据

-- 类型亲和性示例
CREATE TABLE types_demo (
    col1 INT,           -- INTEGER亲和性
    col2 VARCHAR(255),  -- TEXT亲和性
    col3 CHAR(10),      -- TEXT亲和性
    col4 FLOAT,         -- REAL亲和性
    col5 BLOB,          -- BLOB亲和性
    col6 NUMERIC        -- NUMERIC亲和性
);

查看表结构

1
2
3
4
5
6
7
8
9
10
11
-- 查看表结构
.schema table_name

-- 使用PRAGMA查看表信息
PRAGMA table_info(table_name);

-- 查看所有表
SELECT name FROM sqlite_master WHERE type='table';

-- 查看表的SQL创建语句
SELECT sql FROM sqlite_master WHERE name='table_name';

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- SQLite的ALTER TABLE功能有限，支持以下操作：

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;

-- 添加列
ALTER TABLE table_name ADD COLUMN column_name TEXT;

-- 重命名列（SQLite 3.25.0+）
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- 删除列（SQLite 3.35.0+）
ALTER TABLE table_name DROP COLUMN column_name;

-- 注意：SQLite不支持直接修改列类型、删除列（旧版本）
-- 解决方案：重建表
BEGIN TRANSACTION;

CREATE TABLE new_table (
    id INTEGER PRIMARY KEY,
    name TEXT,
    new_column TEXT
);

INSERT INTO new_table SELECT id, name, NULL FROM old_table;

DROP TABLE old_table;

ALTER TABLE new_table RENAME TO old_table;

COMMIT;

删除表

1
2
3
4
5
-- 删除表
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

数据操作（CRUD）

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 插入单条数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- 插入多条数据
INSERT INTO table_name (column1, column2) VALUES 
    (value1, value2),
    (value3, value4),
    (value5, value6);

-- 从查询结果插入
INSERT INTO table1 (column1, column2) 
SELECT column1, column2 FROM table2;

-- 插入或替换（如果存在则替换）
INSERT OR REPLACE INTO table_name (id, name) VALUES (1, 'New Name');

-- 插入或忽略（如果存在则忽略）
INSERT OR IGNORE INTO table_name (id, name) VALUES (1, 'Name');

-- 使用UPSERT（SQLite 3.24.0+）
INSERT INTO table_name (id, name) VALUES (1, 'Name')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 查询所有数据
SELECT * FROM table_name;

-- 查询指定列
SELECT column1, column2 FROM table_name;

-- 条件查询
SELECT * FROM table_name WHERE condition;

-- 排序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

-- 限制结果数量
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 20;  -- 跳过20条，取10条

-- 分组查询
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- 分组过滤
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

-- 去重
SELECT DISTINCT column1 FROM table_name;

-- 别名
SELECT column1 AS alias_name FROM table_name;

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
-- 更新数据
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

-- 更新多个值
UPDATE table_name SET column1 = value1 WHERE id IN (1, 2, 3);

-- 使用CASE更新
UPDATE table_name 
SET status = CASE 
    WHEN age < 18 THEN 'minor'
    WHEN age >= 18 THEN 'adult'
END;

删除数据

1
2
3
4
5
6
7
8
9
-- 删除数据
DELETE FROM table_name WHERE condition;

-- 删除所有数据
DELETE FROM table_name;

-- 清空表（重置自增ID）
DELETE FROM sqlite_sequence WHERE name='table_name';
DELETE FROM table_name;

高级查询

连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 内连接
SELECT * FROM table1 
INNER JOIN table2 ON table1.id = table2.table1_id;

-- 左连接
SELECT * FROM table1 
LEFT JOIN table2 ON table1.id = table2.table1_id;

-- 自然连接（自动匹配同名列）
SELECT * FROM table1 NATURAL JOIN table2;

-- 自连接
SELECT a.*, b.* FROM table1 a, table1 b WHERE a.parent_id = b.id;

子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- WHERE子句中的子查询
SELECT * FROM table1 WHERE id IN (SELECT table1_id FROM table2);

-- FROM子句中的子查询
SELECT * FROM (SELECT * FROM table1 WHERE condition) AS temp;

-- EXISTS子查询
SELECT * FROM table1 WHERE EXISTS (
    SELECT 1 FROM table2 WHERE table2.table1_id = table1.id
);

-- 标量子查询
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) 
FROM customers;

联合查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- UNION（去重）
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- UNION ALL（不去重）
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

-- INTERSECT（交集）
SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2;

-- EXCEPT（差集）
SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

CTE（公用表表达式）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 简单CTE
WITH cte_name AS (
    SELECT * FROM table_name WHERE condition
)
SELECT * FROM cte_name;

-- 多个CTE
WITH 
    cte1 AS (SELECT * FROM table1),
    cte2 AS (SELECT * FROM table2)
SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

-- 递归CTE
WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT id, parent_id, name FROM tree WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT t.id, t.parent_id, t.name 
    FROM tree t
    JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

索引操作

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX unique_index_name ON table_name (column_name);

-- 创建复合索引
CREATE INDEX index_name ON table_name (column1, column2);

-- 创建部分索引（带WHERE条件）
CREATE INDEX index_name ON table_name (column_name) WHERE condition;

-- 创建表达式索引
CREATE INDEX index_name ON table_name (UPPER(column_name));

查看索引

1
2
3
4
5
6
7
8
9
10
11
-- 查看所有索引
.indexes

-- 查看表的索引
PRAGMA index_list(table_name);

-- 查看索引详情
PRAGMA index_info(index_name);

-- 查看索引SQL
SELECT sql FROM sqlite_master WHERE type='index' AND name='index_name';

删除索引

1
2
3
4
5
-- 删除索引
DROP INDEX index_name;

-- 如果存在则删除
DROP INDEX IF EXISTS index_name;

重建索引

1
2
3
4
-- 重建索引
REINDEX;
REINDEX index_name;
REINDEX table_name;

触发器

创建触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- INSERT触发器
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
BEGIN
    INSERT INTO log_table (action, record_id) VALUES ('INSERT', NEW.id);
END;

-- UPDATE触发器
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
WHEN OLD.status != NEW.status
BEGIN
    INSERT INTO log_table (action, old_value, new_value) 
    VALUES ('UPDATE', OLD.status, NEW.status);
END;

-- DELETE触发器
CREATE TRIGGER trigger_name
AFTER DELETE ON table_name
BEGIN
    INSERT INTO log_table (action, record_id) VALUES ('DELETE', OLD.id);
END;

-- INSTEAD OF触发器（用于视图）
CREATE TRIGGER trigger_name
INSTEAD OF INSERT ON view_name
BEGIN
    INSERT INTO table_name (column1, column2) VALUES (NEW.column1, NEW.column2);
END;

查看触发器

1
2
3
4
5
-- 查看所有触发器
SELECT name FROM sqlite_master WHERE type='trigger';

-- 查看表的触发器
SELECT name, sql FROM sqlite_master WHERE type='trigger' AND tbl_name='table_name';

删除触发器

1
2
3
4
5
-- 删除触发器
DROP TRIGGER trigger_name;

-- 如果存在则删除
DROP TRIGGER IF EXISTS trigger_name;

视图

创建视图

1
2
3
4
5
6
7
8
9
10
11
-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

-- 如果不存在则创建
CREATE VIEW IF NOT EXISTS view_name AS
SELECT * FROM table_name;

-- 创建临时视图
CREATE TEMP VIEW temp_view AS
SELECT * FROM table_name;

查看视图

1
2
3
4
5
-- 查看所有视图
SELECT name FROM sqlite_master WHERE type='view';

-- 查看视图定义
SELECT sql FROM sqlite_master WHERE type='view' AND name='view_name';

删除视图

1
2
3
4
5
-- 删除视图
DROP VIEW view_name;

-- 如果存在则删除
DROP VIEW IF EXISTS view_name;

事务

事务操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 开始事务
BEGIN TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;
-- 或
END TRANSACTION;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;

事务隔离

1
2
3
4
5
6
7
8
-- SQLite默认使用SERIALIZABLE隔离级别
-- 可以设置不同的隔离模式：

-- 未提交读（实际上SQLite不支持脏读）
PRAGMA read_uncommitted = true;

-- 查看设置
PRAGMA read_uncommitted;

锁定模式

1
2
3
4
5
6
-- 设置锁定模式
PRAGMA locking_mode = EXCLUSIVE;  -- 独占模式
PRAGMA locking_mode = NORMAL;     -- 正常模式

-- 查看锁定模式
PRAGMA locking_mode;

PRAGMA 命令

数据库配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 查看数据库版本
SELECT sqlite_version();

-- 设置外键约束
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys = OFF;
PRAGMA foreign_keys;  -- 查看状态

-- 设置自动VACUUM
PRAGMA auto_vacuum = FULL;
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA auto_vacuum = NONE;

-- 设置缓存大小（页数，负数表示KB）
PRAGMA cache_size = 10000;

-- 设置页面大小（创建数据库前设置）
PRAGMA page_size = 4096;

-- 设置同步模式
PRAGMA synchronous = FULL;    -- 最安全，最慢
PRAGMA synchronous = NORMAL;  -- 平衡
PRAGMA synchronous = OFF;     -- 最快，不安全

-- 设置日志模式
PRAGMA journal_mode = DELETE;   -- 默认
PRAGMA journal_mode = WAL;      -- Write-Ahead Logging（推荐）
PRAGMA journal_mode = MEMORY;   -- 内存日志
PRAGMA journal_mode = OFF;      -- 无日志

-- 查看数据库列表
PRAGMA database_list;

-- 查看数据库大小
PRAGMA page_count;
SELECT page_count * page_size / 1024.0 / 1024.0 AS size_mb FROM pragma_page_count;

表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看表信息
PRAGMA table_info(table_name);

-- 查看表统计信息
PRAGMA table_xinfo(table_name);

-- 查看索引列表
PRAGMA index_list(table_name);

-- 查看索引信息
PRAGMA index_info(index_name);

-- 查看外键
PRAGMA foreign_key_list(table_name);

性能优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 优化数据库
PRAGMA optimize;

-- 分析统计信息
ANALYZE;
ANALYZE table_name;

-- 整理数据库碎片
VACUUM;

-- 增量VACUUM
PRAGMA incremental_vacuum(100);

-- 检查数据库完整性
PRAGMA integrity_check;
PRAGMA integrity_check(table_name);

-- 快速检查
PRAGMA quick_check;

备份与恢复

数据库备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 方法1：直接复制数据库文件
cp database.db database_backup.db

# 方法2：使用.dump导出SQL
sqlite3 database.db .dump > backup.sql

# 方法3：导出特定表
sqlite3 database.db ".dump table_name" > table_backup.sql

# 方法4：使用.backup命令
sqlite3 database.db ".backup backup.db"

# 方法5：在线备份（在SQLite命令行中）
.backup main backup.db

数据库恢复

1
2
3
4
5
6
7
8
# 方法1：从SQL文件恢复
sqlite3 new_database.db < backup.sql

# 方法2：在SQLite命令行中恢复
.read backup.sql

# 方法3：复制备份文件
cp backup.db database.db

导出特定格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 导出CSV格式
sqlite3 database.db <<EOF
.headers on
.mode csv
.output data.csv
SELECT * FROM table_name;
EOF

# 导出JSON格式（需要SQLite 3.33.0+）
sqlite3 database.db <<EOF
.mode json
.output data.json
SELECT * FROM table_name;
EOF

# 导出HTML格式
sqlite3 database.db <<EOF
.mode html
.output data.html
SELECT * FROM table_name;
EOF

导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 导入CSV数据
sqlite3 database.db <<EOF
.mode csv
.import data.csv table_name
EOF

# 导入带标题的CSV
sqlite3 database.db <<EOF
.mode csv
.headers on
.import data.csv table_name
EOF

# 导入制表符分隔数据
sqlite3 database.db <<EOF
.mode tabs
.import data.txt table_name
EOF

常用函数

字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 字符串连接
SELECT 'Hello' || ' ' || 'World';

-- 字符串长度
SELECT LENGTH('Hello');

-- 转换大小写
SELECT UPPER('hello');
SELECT LOWER('HELLO');

-- 字符串截取
SELECT SUBSTR('Hello World', 1, 5);

-- 字符串替换
SELECT REPLACE('Hello World', 'World', 'SQLite');

-- 去除空格
SELECT TRIM('  Hello  ');
SELECT LTRIM('  Hello');
SELECT RTRIM('Hello  ');

-- 查找子串
SELECT INSTR('Hello World', 'World');

-- 格式化字符串
SELECT PRINTF('%s has %d apples', 'John', 5);

数值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 向上取整
SELECT CEIL(3.14);  -- 不支持，使用
SELECT CAST(3.14 AS INTEGER) + (3.14 > CAST(3.14 AS INTEGER));

-- 向下取整
SELECT FLOOR(3.14);  -- 不支持，使用
SELECT CAST(3.14 AS INTEGER);

-- 绝对值
SELECT ABS(-10);  -- 10

-- 取模
SELECT 10 % 3;  -- 1

-- 随机数
SELECT RANDOM();  -- 大整数
SELECT ABS(RANDOM() % 100);  -- 0-99

-- 幂运算
SELECT POWER(2, 3);  -- 8

-- 平方根
SELECT SQRT(16);  -- 4

日期时间函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 当前日期时间
SELECT DATE('now');        -- 日期
SELECT TIME('now');        -- 时间
SELECT DATETIME('now');    -- 日期时间
SELECT JULIANDAY('now');   -- 儒略日

-- 日期格式化
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');
SELECT STRFTIME('%Y年%m月%d日', 'now');

-- 日期计算
SELECT DATE('now', '+7 days');
SELECT DATE('now', '-1 month');
SELECT DATE('now', '+1 year', '+3 months', '-1 day');

-- 日期差值
SELECT JULIANDAY('2024-12-31') - JULIANDAY('2024-01-01');

-- 提取日期部分
SELECT STRFTIME('%Y', 'now');   -- 年
SELECT STRFTIME('%m', 'now');   -- 月
SELECT STRFTIME('%d', 'now');   -- 日
SELECT STRFTIME('%H', 'now');   -- 时
SELECT STRFTIME('%M', 'now');   -- 分
SELECT STRFTIME('%S', 'now');   -- 秒
SELECT STRFTIME('%w', 'now');   -- 星期几（0-6，0为周日）
SELECT STRFTIME('%j', 'now');   -- 一年中的第几天
SELECT STRFTIME('%W', 'now');   -- 一年中的第几周

-- 日期转换
SELECT DATE('2024-01-01 12:30:45');  -- 提取日期
SELECT TIME('2024-01-01 12:30:45');  -- 提取时间

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 计数
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name;  -- 不包含NULL

-- 求和
SELECT SUM(column_name) FROM table_name;

-- 平均值
SELECT AVG(column_name) FROM table_name;

-- 最大值
SELECT MAX(column_name) FROM table_name;

-- 最小值
SELECT MIN(column_name) FROM table_name;

-- 分组拼接
SELECT GROUP_CONCAT(column_name) FROM table_name;
SELECT GROUP_CONCAT(column_name, ', ') FROM table_name;

-- 统计唯一值
SELECT COUNT(DISTINCT column_name) FROM table_name;

条件函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- CASE WHEN
SELECT 
    name,
    CASE 
        WHEN age < 18 THEN 'minor'
        WHEN age >= 18 AND age < 65 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users;

-- IIF（SQLite 3.32.0+）
SELECT IIF(age >= 18, 'adult', 'minor') FROM users;

-- COALESCE（返回第一个非NULL值）
SELECT COALESCE(NULL, NULL, 'default');

-- NULLIF（如果相等返回NULL）
SELECT NULLIF(value, 0);  -- 如果value=0则返回NULL

类型转换

1
2
3
4
5
6
7
8
9
10
11
-- CAST转换
SELECT CAST('123' AS INTEGER);
SELECT CAST(123.45 AS TEXT);
SELECT CAST(123 AS REAL);

-- TYPEOF查看类型
SELECT TYPEOF(123);        -- integer
SELECT TYPEOF(123.45);     -- real
SELECT TYPEOF('hello');    -- text
SELECT TYPEOF(X'0102');    -- blob
SELECT TYPEOF(NULL);       -- null

JSON 支持（SQLite 3.38.0+）

JSON 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 解析JSON
SELECT JSON('{"name": "John", "age": 30}');

-- 提取值
SELECT JSON_EXTRACT('{"name": "John"}', '$.name');
SELECT JSON_EXTRACT('{"user": {"name": "John"}}', '$.user.name');

-- 简化语法
SELECT '{"name": "John"}' -> '$.name';
SELECT '{"user": {"name": "John"}}' ->> '$.user.name';

-- 创建JSON对象
SELECT JSON_OBJECT('name', 'John', 'age', 30);

-- 创建JSON数组
SELECT JSON_ARRAY(1, 2, 3, 'four');

-- JSON类型检查
SELECT JSON_TYPE('{"name": "John"}');  -- object
SELECT JSON_TYPE('[1, 2, 3]');         -- array

-- JSON数组长度
SELECT JSON_ARRAY_LENGTH('[1, 2, 3, 4]');

-- JSON插入
SELECT JSON_INSERT('{"name": "John"}', '$.age', 30);

-- JSON替换
SELECT JSON_REPLACE('{"name": "John", "age": 25}', '$.age', 30);

-- JSON删除
SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age');

-- JSON验证
SELECT JSON_VALID('{"name": "John"}');  -- 1
SELECT JSON_VALID('invalid json');      -- 0

全文搜索（FTS）

创建FTS表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- FTS3/FTS4
CREATE VIRTUAL TABLE posts_fts USING fts4(title, content);

-- FTS5（推荐，SQLite 3.9.0+）
CREATE VIRTUAL TABLE posts_fts USING fts5(title, content);

-- 插入数据
INSERT INTO posts_fts (title, content) VALUES ('Title', 'Content text');

-- 全文搜索
SELECT * FROM posts_fts WHERE posts_fts MATCH 'search term';

-- 搜索特定列
SELECT * FROM posts_fts WHERE posts_fts MATCH 'title:search';

-- 前缀搜索
SELECT * FROM posts_fts WHERE posts_fts MATCH 'search*';

-- 布尔搜索
SELECT * FROM posts_fts WHERE posts_fts MATCH 'term1 AND term2';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'term1 OR term2';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'term1 NOT term2';

-- 排序（按相关性）
SELECT * FROM posts_fts WHERE posts_fts MATCH 'search' ORDER BY rank;

性能优化

查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 使用EXPLAIN QUERY PLAN查看执行计划
EXPLAIN QUERY PLAN SELECT * FROM table_name WHERE condition;

-- 使用索引覆盖
CREATE INDEX idx_cover ON table_name(column1, column2);
SELECT column1, column2 FROM table_name WHERE column1 = 'value';

-- 避免SELECT *
SELECT column1, column2 FROM table_name;

-- 使用LIMIT
SELECT * FROM table_name LIMIT 100;

-- 批量插入优化
BEGIN TRANSACTION;
INSERT INTO table_name VALUES (...);
INSERT INTO table_name VALUES (...);
-- 更多插入...
COMMIT;

-- 使用预编译语句（在应用中）
PREPARE stmt FROM 'INSERT INTO table_name VALUES (?, ?)';
EXECUTE stmt USING value1, value2;

数据库优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 分析统计信息
ANALYZE;

-- 优化数据库
PRAGMA optimize;

-- 整理碎片
VACUUM;

-- 设置WAL模式（提高并发性能）
PRAGMA journal_mode = WAL;

-- 增加缓存大小
PRAGMA cache_size = -64000;  -- 64MB

-- 设置同步模式
PRAGMA synchronous = NORMAL;

-- 设置临时存储
PRAGMA temp_store = MEMORY;

索引优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看查询是否使用索引
EXPLAIN QUERY PLAN SELECT * FROM table_name WHERE column = 'value';

-- 创建合适的索引
CREATE INDEX idx_composite ON table_name(column1, column2);

-- 使用部分索引减少索引大小
CREATE INDEX idx_active ON table_name(column) WHERE active = 1;

-- 重建索引
REINDEX;

-- 分析索引使用情况
SELECT * FROM sqlite_stat1;

常见问题解决

数据库锁定

1
2
3
4
5
6
7
8
-- 查看当前进程
SELECT * FROM sqlite_status;

-- 设置超时时间
PRAGMA busy_timeout = 5000;  -- 5秒

-- 使用WAL模式减少锁定
PRAGMA journal_mode = WAL;

数据库损坏

1
2
3
4
5
6
7
8
9
10
11
-- 检查完整性
PRAGMA integrity_check;

-- 尝试恢复
-- 1. 导出数据
.dump > backup.sql

-- 2. 创建新数据库
sqlite3 new.db < backup.sql

-- 3. 替换原数据库

性能问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查看数据库信息
PRAGMA page_count;
PRAGMA page_size;
SELECT page_count * page_size / 1024.0 / 1024.0 AS size_mb FROM pragma_page_count;

-- 查看表大小
SELECT name, 
       (pgsize * pgcount) / 1024.0 / 1024.0 AS size_mb
FROM (
    SELECT name, 
           (SELECT pgsize FROM pragma_page_size) AS pgsize,
           (SELECT pgcount FROM pragma_page_count) AS pgcount
    FROM sqlite_master WHERE type='table'
);

-- 查看索引使用情况
SELECT * FROM sqlite_stat1;

自增ID重置

1
2
3
4
5
6
7
8
-- 查看当前自增值
SELECT * FROM sqlite_sequence;

-- 重置自增值
DELETE FROM sqlite_sequence WHERE name='table_name';

-- 设置特定值
INSERT INTO sqlite_sequence (name, seq) VALUES ('table_name', 100);

实用技巧

批量操作

1
2
3
4
5
6
7
8
9
10
11
12
-- 批量插入
INSERT INTO table_name SELECT * FROM source_table;

-- 批量更新
UPDATE table_name SET column = value WHERE id IN (
    SELECT id FROM another_table WHERE condition
);

-- 批量删除
DELETE FROM table_name WHERE id IN (
    SELECT id FROM another_table WHERE condition
);

数据去重

1
2
3
4
5
6
7
8
9
10
-- 方法1：使用ROWID
DELETE FROM table_name 
WHERE rowid NOT IN (
    SELECT MAX(rowid) FROM table_name GROUP BY column1, column2
);

-- 方法2：创建临时表
CREATE TABLE temp AS SELECT DISTINCT * FROM table_name;
DROP TABLE table_name;
ALTER TABLE temp RENAME TO table_name;

分页查询

1
2
3
4
5
6
7
8
-- 基本分页
SELECT * FROM table_name LIMIT 10 OFFSET 20;

-- 优化分页（使用索引）
SELECT * FROM table_name 
WHERE id > last_id 
ORDER BY id 
LIMIT 10;

行号生成

1
2
3
4
5
6
7
8
9
10
-- 使用ROWID
SELECT ROWID, * FROM table_name;

-- 使用窗口函数（SQLite 3.25.0+）
SELECT ROW_NUMBER() OVER (ORDER BY column) AS row_num, *
FROM table_name;

-- 使用自增变量
SELECT (SELECT COUNT(*) FROM table_name t2 WHERE t2.id <= t1.id) AS row_num, *
FROM table_name t1;

生成测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 使用递归CTE生成序列
WITH RECURSIVE
    cnt(x) AS (
        SELECT 1
        UNION ALL
        SELECT x + 1 FROM cnt WHERE x < 100
    )
SELECT x FROM cnt;

-- 生成随机数据
WITH RECURSIVE
    cnt(x) AS (
        SELECT 1
        UNION ALL
        SELECT x + 1 FROM cnt WHERE x < 100
    )
SELECT 
    x AS id,
    'User' || x AS name,
    ABS(RANDOM() % 100) AS age
FROM cnt;

与其他数据库对比

SQLite vs MySQL

特性 SQLite MySQL
架构 嵌入式 客户端/服务器
配置 零配置 需要配置
并发 读并发，写串行 高并发
用户管理 无 完整权限系统
存储过程 不支持 支持
触发器 支持 支持
视图 支持（只读） 支持（可更新）
适用场景 小型应用、移动应用 大型应用、Web应用

SQLite vs PostgreSQL

特性 SQLite PostgreSQL
数据类型 动态类型 强类型
JSON支持 基础支持 强大支持
全文搜索 FTS扩展 内置
数组类型 不支持 支持
窗口函数 支持（3.25.0+） 完整支持
事务隔离 SERIALIZABLE 多级别
适用场景 嵌入式、测试 企业级应用

最佳实践

设计原则

  1. 合理使用索引：为常用查询条件创建索引
  2. 避免过度规范化：SQLite适合适度冗余的设计
  3. 使用事务：批量操作使用事务提高性能
  4. 选择合适的数据类型：利用SQLite的动态类型特性
  5. 定期维护：执行VACUUM和ANALYZE

性能建议

  1. 使用WAL模式：提高并发性能
  2. 批量操作：使用事务包裹多个写操作
  3. 预编译语句：重复执行的SQL使用预编译
  4. 合理设置缓存：根据内存情况调整cache_size
  5. 避免大事务：长时间运行的事务会影响并发

安全建议

  1. 参数化查询：防止SQL注入
  2. 输入验证：验证所有用户输入
  3. 文件权限：设置适当的数据库文件权限
  4. 加密：使用SQLCipher加密敏感数据
  5. 备份：定期备份数据库文件

总结

本文全面介绍了SQLite数据库的常用命令和技巧，包括：

  • 基本操作和命令行工具
  • 数据库和表的管理
  • 数据操作（CRUD）
  • 高级查询特性
  • 索引、触发器、视图
  • 事务和并发控制
  • 备份与恢复
  • 性能优化
  • JSON和全文搜索支持
  • 常见问题解决方案

SQLite作为轻量级数据库，适合小型应用、移动应用、嵌入式设备和测试环境。掌握这些命令和技巧，可以更高效地使用SQLite数据库。