SQLite 常用开发命令

常用开发命令概述

SQLite 提供了丰富的命令行工具和 SQL 命令，用于数据库开发和管理。本文档详细介绍了 SQLite 常用的开发命令，包括数据库操作、表操作、数据操作、事务管理等，帮助开发人员和运维人员高效地使用 SQLite 数据库。

1. 数据库操作命令

1.1 连接数据库

命令： sqlite3 [database_file]

功能： 连接到指定的 SQLite 数据库文件，如果文件不存在则创建一个新的数据库文件。

示例：

bash

# 连接到已存在的数据库
sqlite3 database.db

# 创建并连接到新数据库
sqlite3 new_database.db

# 使用内存数据库
sqlite3 :memory:

1.2 查看数据库信息

命令： .databases

功能： 显示当前连接的数据库信息，包括数据库名称和文件路径。

示例：

bash

sqlite> .databases
seq  name             file                                                       
---  ---------------  ----------------------------------------------------------
0    main             /path/to/database.db

1.3 备份数据库

命令： .backup [backup_file]

功能： 备份当前数据库到指定的文件。

示例：

bash

sqlite> .backup backup.db

1.4 恢复数据库

命令： .restore [database_name] [backup_file]

功能： 从备份文件恢复数据库。

示例：

bash

sqlite> .restore main backup.db

1.5 退出 SQLite 命令行

命令： .exit 或 .quit

功能： 退出 SQLite 命令行工具。

示例：

bash

sqlite> .exit

2. 表操作命令

2.1 查看所有表

命令： .tables 或 .tables [pattern]

功能： 显示当前数据库中的所有表。

示例：

bash

sqlite> .tables
users  orders  products

2.2 查看表结构

命令： .schema [table_name] 或 PRAGMA table_info([table_name]);

功能： 显示表的创建语句或表结构信息。

示例：

bash

# 使用 .schema 命令
sqlite> .schema users
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    status TEXT DEFAULT 'inactive',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

# 使用 PRAGMA 命令
sqlite> PRAGMA table_info(users);
0|user_id|INTEGER|1||1
1|username|TEXT|1||0
2|email|TEXT|1||0
3|password_hash|TEXT|1||0
4|status|TEXT|0|'inactive'|0
5|created_at|DATETIME|0|CURRENT_TIMESTAMP|0
6|updated_at|DATETIME|0|CURRENT_TIMESTAMP|0

2.3 创建表

命令： CREATE TABLE [IF NOT EXISTS] table_name (column_definitions);

功能： 创建新表。

示例：

sql

CREATE TABLE IF NOT EXISTS products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.4 修改表

命令： ALTER TABLE table_name ADD COLUMN column_definition;

功能： 向表中添加新列。

示例：

sql

ALTER TABLE users ADD COLUMN last_login DATETIME;

2.5 删除表

命令： DROP TABLE [IF EXISTS] table_name;

功能： 删除指定的表。

示例：

sql

DROP TABLE IF EXISTS old_table;

3. 数据操作命令

3.1 插入数据

命令： INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

功能： 向表中插入新数据。

示例：

sql

-- 插入单条数据
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');

-- 插入多条数据
INSERT INTO users (username, email, password_hash) VALUES 
('user2', 'user2@example.com', 'hash2'),
('user3', 'user3@example.com', 'hash3');

3.2 查询数据

命令： SELECT columns FROM table_name [WHERE condition] [ORDER BY columns] [LIMIT limit];

功能： 从表中查询数据。

示例：

sql

-- 查询所有数据
SELECT * FROM users;

-- 查询指定列
SELECT username, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE status = 'active';

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 限制查询结果
SELECT * FROM users LIMIT 10;

-- 带条件、排序和限制的查询
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;

3.3 更新数据

命令： UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition];

功能： 更新表中的数据。

示例：

sql

-- 更新单条数据
UPDATE users SET status = 'active' WHERE user_id = 1;

-- 更新多条数据
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

3.4 删除数据

命令： DELETE FROM table_name [WHERE condition];

功能： 删除表中的数据。

示例：

sql

-- 删除单条数据
DELETE FROM users WHERE user_id = 1;

-- 删除多条数据
DELETE FROM orders WHERE order_date < '2022-01-01';

-- 删除表中所有数据
DELETE FROM temp_table;

4. 索引操作命令

4.1 创建索引

命令： CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name ON table_name (column1, column2, ...);

功能： 为表创建索引。

示例：

sql

-- 创建普通索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_orders_user_id_created_at ON orders(user_id, created_at);

4.2 查看索引

命令： .indexes [table_name] 或 PRAGMA index_list([table_name]);

功能： 显示表的索引信息。

示例：

bash

# 使用 .indexes 命令
sqlite> .indexes users
idx_users_email
idx_users_username

# 使用 PRAGMA 命令
sqlite> PRAGMA index_list(users);
seq|name|unique|origin|partial
0|idx_users_email|0|c|0
1|idx_users_username|1|c|0

4.3 删除索引

命令： DROP INDEX [IF EXISTS] index_name;

功能： 删除指定的索引。

示例：

sql

DROP INDEX IF EXISTS idx_old_index;

5. 事务管理命令

5.1 开始事务

命令： BEGIN TRANSACTION; 或 BEGIN;

功能： 开始一个新的事务。

示例：

sql

BEGIN TRANSACTION;

5.2 提交事务

命令： COMMIT TRANSACTION; 或 COMMIT;

功能： 提交当前事务，将所有修改保存到数据库。

示例：

sql

COMMIT;

5.3 回滚事务

命令： ROLLBACK TRANSACTION; 或 ROLLBACK;

功能： 回滚当前事务，撤销所有未提交的修改。

示例：

sql

ROLLBACK;

5.4 创建保存点

命令： SAVEPOINT savepoint_name;

功能： 在当前事务中创建一个保存点。

示例：

sql

SAVEPOINT savepoint1;

5.5 回滚到保存点

命令： ROLLBACK TO savepoint_name;

功能： 回滚到指定的保存点，撤销保存点之后的所有修改。

示例：

sql

ROLLBACK TO savepoint1;

5.6 释放保存点

命令： RELEASE savepoint_name;

功能： 释放指定的保存点。

示例：

sql

RELEASE savepoint1;

6. 查询优化命令

6.1 分析查询执行计划

命令： EXPLAIN QUERY PLAN SELECT ...; 或 EXPLAIN SELECT ...; 或 EXPLAIN ANALYZE SELECT ...;

功能： 分析查询执行计划，帮助优化查询性能。

示例：

bash

# 使用 EXPLAIN QUERY PLAN
sqlite> EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'user@example.com';
QUERY PLAN
`--SEARCH users USING INDEX idx_users_email (email=?)

# 使用 EXPLAIN ANALYZE
sqlite> EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

6.2 分析表

命令： ANALYZE [table_name];

功能： 收集表的统计信息，帮助查询优化器生成更好的执行计划。

示例：

sql

-- 分析所有表
ANALYZE;

-- 分析指定表
ANALYZE users;

7. 实用命令

7.1 显示命令帮助

命令： .help [command]

功能： 显示 SQLite 命令行工具的帮助信息。

示例：

bash

# 显示所有命令帮助
sqlite> .help

# 显示特定命令帮助
sqlite> .help .tables

7.2 设置输出格式

命令： .mode [mode]

功能： 设置查询结果的输出格式。

常用模式：

column：列格式（默认）
line：行格式
list：列表格式
json：JSON 格式
csv：CSV 格式

示例：

bash

# 设置为行格式
sqlite> .mode line

# 设置为 JSON 格式
sqlite> .mode json

# 设置为 CSV 格式
sqlite> .mode csv

7.3 设置列宽

命令： .width [width1 width2 ...]

功能： 设置列格式下各列的宽度。

示例：

bash

# 设置列宽
sqlite> .width 10 20 30

7.4 输出到文件

命令： .output [file]

功能： 将查询结果输出到指定文件，而不是控制台。

示例：

bash

# 输出到文件
sqlite> .output query_result.txt

# 恢复输出到控制台
sqlite> .output stdout

7.5 执行 SQL 文件

命令： .read [file]

功能： 执行指定文件中的 SQL 语句。

示例：

bash

# 执行 SQL 文件
sqlite> .read create_tables.sql

8. PRAGMA 命令

8.1 启用外键约束

命令： PRAGMA foreign_keys = ON;

功能： 启用外键约束检查。

示例：

sql

PRAGMA foreign_keys = ON;

8.2 查看外键约束状态

命令： PRAGMA foreign_keys;

功能： 查看当前外键约束的状态。

示例：

sql

sqlite> PRAGMA foreign_keys;
1  -- 启用状态

8.3 设置事务日志模式

命令： PRAGMA journal_mode = [mode];

功能： 设置事务日志模式。

常用模式：

DELETE：默认模式
TRUNCATE
PERSIST
MEMORY
WAL：Write-Ahead Logging 模式

示例：

sql

PRAGMA journal_mode = WAL;

8.4 检查数据库完整性

命令： PRAGMA integrity_check; 或 PRAGMA quick_check;

功能： 检查数据库的完整性。

示例：

sql

sqlite> PRAGMA integrity_check;
ok  -- 数据库完整

8.5 设置缓存大小

命令： PRAGMA cache_size = [size];

功能： 设置数据库缓存的大小（以页为单位）。

示例：

sql

PRAGMA cache_size = 10000;  -- 设置为 10000 页（每页 4KB，共 40MB）

9. 常见 SQL 命令

9.1 创建视图

命令： CREATE [OR REPLACE] VIEW view_name AS SELECT ...;

功能： 创建视图。

示例：

sql

CREATE VIEW v_active_users AS
SELECT user_id, username, email FROM users WHERE status = 'active';

9.2 创建触发器

命令： CREATE [OR REPLACE] TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] ON table_name [FOR EACH ROW] BEGIN ... END;

功能： 创建触发器。

示例：

sql

CREATE TRIGGER tr_users_updated
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE user_id = NEW.user_id;
END;

9.3 创建自定义函数

命令： SQLite 命令行不直接支持创建自定义函数，需要通过编程语言 API 实现。

示例（Python）：

python

import sqlite3

# 自定义函数
import sqlite3

def uppercase(text):
    return text.upper()

# 连接数据库
conn = sqlite3.connect('database.db')

# 创建自定义函数
conn.create_function('uppercase', 1, uppercase)

# 使用自定义函数
cursor = conn.cursor()
cursor.execute("SELECT uppercase('hello world')")
result = cursor.fetchone()
print(result[0])  # 输出: HELLO WORLD

# 关闭连接
cursor.close()
conn.close()

10. 命令行选项

10.1 执行 SQL 语句

命令： sqlite3 database.db "SQL statement"

功能： 在命令行直接执行 SQL 语句。

示例：

bash

# 执行单个 SQL 语句
sqlite3 database.db "SELECT COUNT(*) FROM users;"

# 执行多个 SQL 语句
sqlite3 database.db "SELECT * FROM users WHERE status = 'active'; SELECT * FROM orders LIMIT 5;"

10.2 导出数据

命令： sqlite3 -csv database.db "SELECT * FROM users;" > users.csv

功能： 将查询结果导出到 CSV 文件。

示例：

bash

# 导出为 CSV 文件
sqlite3 -csv database.db "SELECT * FROM users;" > users.csv

# 导出为 JSON 文件
sqlite3 -json database.db "SELECT * FROM users;" > users.json

10.3 显示表头

命令： sqlite3 -header database.db "SELECT * FROM users;"

功能： 显示查询结果的表头。

示例：

bash

sqlite3 -header -csv database.db "SELECT * FROM users;" > users.csv

常见问题（FAQ）

Q: 如何在 SQLite 命令行中执行多行 SQL 语句？

A: 在 SQLite 命令行中，多行 SQL 语句会自动换行，直到遇到分号（;）才会执行。可以使用反斜杠（\）在一行的末尾表示语句继续到下一行。

示例：

bash

sqlite> SELECT user_id, username, email \n   ...> FROM users \n   ...> WHERE status = 'active' \n   ...> ORDER BY created_at DESC;\n```

### Q: 如何查看 SQLite 命令行历史？

A: SQLite 命令行工具默认没有历史记录功能，但可以通过以下方法启用：
- 在 Unix/Linux 系统上，可以使用 `rlwrap` 工具：`rlwrap sqlite3 database.db`
- 在 Windows 系统上，可以使用 SQLite GUI 工具，如 DB Browser for SQLite

### Q: 如何在 SQLite 命令行中使用变量？

A: SQLite 命令行工具不直接支持变量，但可以使用 shell 变量或通过脚本实现。

**示例：**
```bash
# 使用 shell 变量
USERNAME='user1'
sqlite3 database.db "SELECT * FROM users WHERE username = '$USERNAME';"

Q: 如何在 SQLite 命令行中格式化输出？

A: 可以使用 .mode 命令设置输出格式，使用 .width 命令设置列宽。

示例：

bash

sqlite> .mode column
sqlite> .width 10 20 30
sqlite> SELECT * FROM users;

Q: 如何在 SQLite 命令行中导入 CSV 文件？

A: 可以使用 .import 命令导入 CSV 文件。

示例：

bash

# 设置输入模式为 CSV
sqlite> .mode csv

# 导入 CSV 文件
sqlite> .import users.csv users

最佳实践总结

使用命令行工具：熟练掌握 SQLite 命令行工具的使用，提高开发和管理效率
使用事务：对多个相关操作使用事务，确保数据一致性
创建合适的索引：在频繁查询的字段上创建索引，提高查询性能
定期备份数据库：使用 .backup 命令定期备份数据库，防止数据丢失
定期检查数据库完整性：使用 PRAGMA integrity_check 命令定期检查数据库完整性
使用 WAL 模式：启用 WAL 模式，提高并发性能
分析查询执行计划：使用 EXPLAIN QUERY PLAN 命令分析查询执行计划，优化查询性能
设置合适的缓存大小：根据系统内存大小，设置合适的缓存大小
使用视图和触发器：合理使用视图和触发器，简化复杂查询和维护数据完整性
使用参数化查询：在应用程序中使用参数化查询，防止 SQL 注入

通过熟练掌握和使用这些常用开发命令，可以提高 SQLite 数据库的开发和管理效率，确保数据库系统的性能和可靠性。

SQLite 常用开发命令 ​

常用开发命令概述 ​

1. 数据库操作命令 ​

1.1 连接数据库 ​

1.2 查看数据库信息 ​

1.3 备份数据库 ​

1.4 恢复数据库 ​

1.5 退出 SQLite 命令行 ​

2. 表操作命令 ​

2.1 查看所有表 ​

2.2 查看表结构 ​

2.3 创建表 ​

2.4 修改表 ​

2.5 删除表 ​

3. 数据操作命令 ​

3.1 插入数据 ​

3.2 查询数据 ​

3.3 更新数据 ​

3.4 删除数据 ​

4. 索引操作命令 ​

4.1 创建索引 ​

4.2 查看索引 ​

4.3 删除索引 ​

5. 事务管理命令 ​

5.1 开始事务 ​

5.2 提交事务 ​

5.3 回滚事务 ​

5.4 创建保存点 ​

5.5 回滚到保存点 ​

5.6 释放保存点 ​

6. 查询优化命令 ​

6.1 分析查询执行计划 ​

6.2 分析表 ​

7. 实用命令 ​

7.1 显示命令帮助 ​

7.2 设置输出格式 ​

7.3 设置列宽 ​

7.4 输出到文件 ​

7.5 执行 SQL 文件 ​

8. PRAGMA 命令 ​

8.1 启用外键约束 ​

8.2 查看外键约束状态 ​

8.3 设置事务日志模式 ​

8.4 检查数据库完整性 ​

8.5 设置缓存大小 ​

9. 常见 SQL 命令 ​

9.1 创建视图 ​

9.2 创建触发器 ​

9.3 创建自定义函数 ​

10. 命令行选项 ​

10.1 执行 SQL 语句 ​

10.2 导出数据 ​

10.3 显示表头 ​

常见问题（FAQ） ​

Q: 如何在 SQLite 命令行中执行多行 SQL 语句？ ​

Q: 如何在 SQLite 命令行中格式化输出？ ​

Q: 如何在 SQLite 命令行中导入 CSV 文件？ ​

最佳实践总结 ​

SQLite 常用开发命令

常用开发命令概述

1. 数据库操作命令

1.1 连接数据库

1.2 查看数据库信息

1.3 备份数据库

1.4 恢复数据库

1.5 退出 SQLite 命令行

2. 表操作命令

2.1 查看所有表

2.2 查看表结构

2.3 创建表

2.4 修改表

2.5 删除表

3. 数据操作命令

3.1 插入数据

3.2 查询数据

3.3 更新数据

3.4 删除数据

4. 索引操作命令

4.1 创建索引

4.2 查看索引

4.3 删除索引

5. 事务管理命令

5.1 开始事务

5.2 提交事务

5.3 回滚事务

5.4 创建保存点

5.5 回滚到保存点

5.6 释放保存点

6. 查询优化命令

6.1 分析查询执行计划

6.2 分析表

7. 实用命令

7.1 显示命令帮助

7.2 设置输出格式

7.3 设置列宽

7.4 输出到文件

7.5 执行 SQL 文件

8. PRAGMA 命令

8.1 启用外键约束

8.2 查看外键约束状态

8.3 设置事务日志模式

8.4 检查数据库完整性

8.5 设置缓存大小

9. 常见 SQL 命令

9.1 创建视图

9.2 创建触发器

9.3 创建自定义函数

10. 命令行选项

10.1 执行 SQL 语句

10.2 导出数据

10.3 显示表头

常见问题（FAQ）

Q: 如何在 SQLite 命令行中执行多行 SQL 语句？

Q: 如何在 SQLite 命令行中格式化输出？

Q: 如何在 SQLite 命令行中导入 CSV 文件？

最佳实践总结