第 4 章：基础操作
第 4 章：基础操作
掌握 rqlite 的表管理、CRUD 操作和基本查询语法。
4.1 操作前的准备
rqlite 所有数据库操作都通过 HTTP API 进行。本章示例使用 curl 发送请求，假设 rqlite 单节点运行在 localhost:4001。
为简化后续示例，先定义一些别名：
# 查询（SELECT）
alias rq='curl -s -G localhost:4001/db/query --data-urlencode'
# 执行（INSERT/UPDATE/DELETE/DDL）
alias rxe='curl -s -XPOST localhost:4001/db/execute -H "Content-Type: application/json" -d'
4.2 创建表
4.2.1 基本建表
# 创建用户表
rxe '[["CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
age INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)"]]'
响应：
{
"results": [{"time": 0.000345}]
}
4.2.2 常用字段类型
| SQLite 类型 | 说明 | 示例 |
|---|---|---|
INTEGER | 整数 | id INTEGER PRIMARY KEY |
TEXT | 文本 | name TEXT NOT NULL |
REAL | 浮点数 | price REAL DEFAULT 0.0 |
BLOB | 二进制数据 | avatar BLOB |
DATETIME | 日期时间（存储为 TEXT/REAL/INTEGER） | created_at DATETIME |
BOOLEAN | 布尔值（0 或 1） | active BOOLEAN DEFAULT 1 |
JSON | JSON 文本（SQLite 3.38+） | metadata JSON |
4.2.3 创建索引
# 单列索引
rxe '[["CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"]]'
# 复合索引
rxe '[["CREATE INDEX IF NOT EXISTS idx_users_age_name ON users(age, username)"]]'
# 唯一索引
rxe '[["CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users(username)"]]'
4.2.4 创建带约束的表
rxe '[["CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
product TEXT NOT NULL,
quantity INTEGER CHECK(quantity > 0),
price REAL CHECK(price >= 0),
status TEXT CHECK(status IN (\"pending\", \"paid\", \"shipped\", \"completed\", \"cancelled\")) DEFAULT \"pending\",
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)"]]'
注意: 默认情况下 rqlite 不强制外键约束。如需启用，启动时添加
-fk参数。
4.3 插入数据（INSERT）
4.3.1 单条插入
# 使用参数绑定（推荐，防止 SQL 注入）
rxe '[["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "zhangsan", "[email protected]", 28]]'
响应：
{
"results": [
{
"last_insert_id": 1,
"rows_affected": 1,
"time": 0.000456
}
]
}
4.3.2 批量插入
rqlite 支持在一个请求中发送多条 SQL，它们会在同一个事务中执行：
rxe '[
["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "lisi", "[email protected]", 32],
["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "wangwu", "[email protected]", 25],
["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "zhaoliu", "[email protected]", 30],
["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "sunqi", "[email protected]", 22]
]'
响应：
{
"results": [
{"last_insert_id": 2, "rows_affected": 1, "time": 0.000123},
{"last_insert_id": 3, "rows_affected": 1, "time": 0.000112},
{"last_insert_id": 4, "rows_affected": 1, "time": 0.000109},
{"last_insert_id": 5, "rows_affected": 1, "time": 0.000115}
],
"time": 0.000500
}
重要: 单个请求中的多条 SQL 在同一事务中执行。如果任何一条失败，全部回滚。
4.3.3 使用 INSERT OR REPLACE
# 如果记录已存在（主键或唯一约束冲突），则替换
rxe '[["INSERT OR REPLACE INTO users (id, username, email, age) VALUES (?, ?, ?, ?)", 1, "zhangsan", "[email protected]", 29]]'
4.3.4 使用 INSERT OR IGNORE
# 如果记录已存在，则忽略
rxe '[["INSERT OR IGNORE INTO users (username, email, age) VALUES (?, ?, ?)", "zhangsan", "[email protected]", 30]]'
4.4 查询数据（SELECT）
4.4.1 基本查询
# 查询所有用户
rq 'q=SELECT * FROM users' | python3 -m json.tool
响应：
{
"results": [
{
"columns": ["id", "username", "email", "age", "created_at"],
"types": ["integer", "text", "text", "integer", "datetime"],
"values": [
[1, "zhangsan", "[email protected]", 29, "2026-05-10 10:00:00"],
[2, "lisi", "[email protected]", 32, "2026-05-10 10:00:01"],
[3, "wangwu", "[email protected]", 25, "2026-05-10 10:00:01"],
[4, "zhaoliu", "[email protected]", 30, "2026-05-10 10:00:01"],
[5, "sunqi", "[email protected]", 22, "2026-05-10 10:00:01"]
]
}
],
"time": 0.000234
}
4.4.2 条件查询
# WHERE 条件
rq 'q=SELECT * FROM users WHERE age > 25'
# LIKE 模糊匹配
rq 'q=SELECT * FROM users WHERE username LIKE "zhang%"'
# IN 操作符
rq 'q=SELECT * FROM users WHERE id IN (1, 3, 5)'
4.4.3 排序和分页
# 排序
rq 'q=SELECT * FROM users ORDER BY age DESC'
# 分页（LIMIT + OFFSET）
rq 'q=SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 0'
rq 'q=SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 2'
4.4.4 聚合查询
# 计数
rq 'q=SELECT COUNT(*) as total FROM users'
# 按条件分组
rq 'q=SELECT age, COUNT(*) as count FROM users GROUP BY age ORDER BY count DESC'
# 平均值
rq 'q=SELECT AVG(age) as avg_age FROM users'
# 最大值和最小值
rq 'q=SELECT MAX(age) as max_age, MIN(age) as min_age FROM users'
4.4.5 多表联查
# 先插入一些订单数据
rxe '[
["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 1, "笔记本", 2, 5999.0, "completed"],
["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 1, "鼠标", 5, 99.0, "shipped"],
["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 2, "键盘", 1, 399.0, "pending"],
["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 3, "显示器", 1, 2499.0, "paid"]
]'
# JOIN 查询：用户及其订单
rq 'q=SELECT u.username, o.product, o.quantity, o.price, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY u.username'
4.4.6 子查询
# 查找有订单的用户
rq 'q=SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders)'
# 查找消费总额最高的用户
rq 'q=SELECT u.username, SUM(o.quantity * o.price) as total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id
ORDER BY total_spent DESC
LIMIT 1'
4.5 更新数据（UPDATE）
4.5.1 基本更新
# 更新单条记录
rxe '[["UPDATE users SET email = ? WHERE id = ?", "[email protected]", 1]]'
4.5.2 批量更新
# 同一事务中更新多条
rxe '[
["UPDATE users SET age = age + 1 WHERE age < 30"],
["UPDATE orders SET status = \"completed\" WHERE status = \"shipped\""]
]'
4.5.3 条件更新
# 使用 CASE 表达式
rxe '[["UPDATE orders SET price = CASE
WHEN quantity > 3 THEN price * 0.8
WHEN quantity > 1 THEN price * 0.9
ELSE price
END WHERE status != \"completed\""]]'
4.6 删除数据（DELETE）
4.6.1 基本删除
# 删除特定记录
rxe '[["DELETE FROM orders WHERE status = \"cancelled\""]]'
# 删除所有记录（保留表结构）
rxe '[["DELETE FROM orders"]]'
4.6.2 级联删除
由于外键约束（需启动时启用 -fk），删除用户时关联的订单也会被删除：
# 删除用户及其关联订单（需外键约束）
rxe '[["DELETE FROM users WHERE id = 5"]]'
4.7 表管理操作
4.7.1 查看表结构
# 查看所有表
rq 'q=SELECT name FROM sqlite_master WHERE type=\"table\" AND name NOT LIKE \"sqlite_%\"'
# 查看特定表的 DDL
rq 'q=SELECT sql FROM sqlite_master WHERE name=\"users\"'
# PRAGMA 方式查看表信息
rq 'q=PRAGMA table_info(users)'
4.7.2 修改表结构
SQLite 的 ALTER TABLE 支持有限，rqlite 同样继承这些限制：
# 重命名表
rxe '[["ALTER TABLE orders RENAME TO customer_orders"]]'
# 添加列
rxe '[["ALTER TABLE users ADD COLUMN phone TEXT"]]'
# 注意：SQLite 不支持 DROP COLUMN（3.35.0 前）
# 如需删除列或修改列类型，需要重建表
4.7.3 重建表的步骤
rxe '[
["CREATE TABLE users_new (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL, age INTEGER DEFAULT 0, phone TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP)"],
["INSERT INTO users_new SELECT id, username, email, age, phone, created_at FROM users"],
["DROP TABLE users"],
["ALTER TABLE users_new RENAME TO users"]
]'
4.7.4 删除表
rxe '[["DROP TABLE IF EXISTS customer_orders"]]'
# 删除所有表
rq 'q=SELECT name FROM sqlite_master WHERE type="table"' | \
python3 -c "
import json, sys
data = json.load(sys.stdin)
tables = data['results'][0]['values']
stmts = [[f'DROP TABLE IF EXISTS {t[0]}'] for t in tables]
print(json.dumps(stmts))
" | curl -s -XPOST localhost:4001/db/execute \
-H 'Content-Type: application/json' -d @-
4.8 PRAGMA 配置
通过 PRAGMA 可以查看和调整 SQLite 的运行参数：
# 查看当前 journal 模式
rq 'q=PRAGMA journal_mode'
# 查看页面大小
rq 'q=PRAGMA page_size'
# 查看缓存大小
rq 'q=PRAGMA cache_size'
# 查看所有 PRAGMA 设置
rq 'q=PRAGMA compile_options'
| PRAGMA | 说明 | 常用值 |
|---|---|---|
journal_mode | 日志模式 | WAL（rqlite 默认） |
page_size | 页面大小 | 4096 |
cache_size | 缓存页面数 | -2000（约 2MB） |
foreign_keys | 外键约束 | ON/OFF |
busy_timeout | 忙等待超时 | 5000ms |
synchronous | 同步级别 | NORMAL |
注意: 大多数 PRAGMA 在 rqlite 中读取有效，但写入 PRAGMA 不保证在所有节点上生效。
4.9 常见操作速查表
| 操作 | SQL 示例 |
|---|---|
| 建表 | CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT) |
| 建索引 | CREATE INDEX idx ON t(name) |
| 插入 | INSERT INTO t (name) VALUES (?) |
| 查询 | SELECT * FROM t WHERE id = ? |
| 更新 | UPDATE t SET name = ? WHERE id = ? |
| 删除 | DELETE FROM t WHERE id = ? |
| 聚合 | SELECT COUNT(*), AVG(col) FROM t GROUP BY col2 |
| 联表 | SELECT * FROM a JOIN b ON a.id = b.aid |
| 分页 | SELECT * FROM t LIMIT ? OFFSET ? |
| 模糊 | SELECT * FROM t WHERE name LIKE ? |
4.10 业务场景：博客系统数据模型
一个简单的博客系统数据模型示例：
# 1. 创建表
rxe '[
["CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, slug TEXT NOT NULL UNIQUE)"],
["CREATE TABLE IF NOT EXISTS articles (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, slug TEXT NOT NULL UNIQUE, content TEXT, category_id INTEGER, status TEXT CHECK(status IN (\"draft\", \"published\", \"archived\")) DEFAULT \"draft\", view_count INTEGER DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id))"],
["CREATE TABLE IF NOT EXISTS comments (id INTEGER PRIMARY KEY AUTOINCREMENT, article_id INTEGER NOT NULL, author TEXT NOT NULL, content TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE)"],
["CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status)",
"CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category_id)",
"CREATE INDEX IF NOT EXISTS idx_comments_article ON comments(article_id)"]
]'
# 2. 插入分类
rxe '[
["INSERT INTO categories (name, slug) VALUES (?, ?)", "技术", "tech"],
["INSERT INTO categories (name, slug) VALUES (?, ?)", "随笔", "essay"],
["INSERT INTO categories (name, slug) VALUES (?, ?)", "教程", "tutorial"]
]'
# 3. 插入文章
rxe '[["INSERT INTO articles (title, slug, content, category_id, status) VALUES (?, ?, ?, ?, ?)", "rqlite 入门", "rqlite-intro", "本文介绍 rqlite 的基本概念...", 1, "published"]]'
# 4. 查询已发布文章列表
rq 'q=SELECT a.title, a.view_count, c.name as category FROM articles a LEFT JOIN categories c ON a.category_id = c.id WHERE a.status = \"published\" ORDER BY a.created_at DESC'
4.11 本章小结
| 要点 | 内容 |
|---|---|
| API 风格 | 查询用 GET /db/query，写入用 POST /db/execute |
| 参数绑定 | 使用 ? 占位符防止 SQL 注入 |
| 批量操作 | 单请求多条 SQL，在同一事务中执行 |
| SQLite 限制 | ALTER TABLE 功能有限，不支持 ATTACH |
| 推荐实践 | 始终使用参数绑定，批量操作利用事务 |
上一章：第 3 章：架构深度解析 下一章：第 5 章：HTTP API 详解