LaoHandDeveloper Cheatsheet

SQLite Cheatsheet - SQLite Database Command Reference

The choice among embedded databases is narrow: when you want zero operations, a single file you can copy, and backups that amount to copying that file, SQLite is almost always right, at the cost of allowing only one writer at a time. This table groups everything from connecting and basics through table operations, data manipulation, query techniques, indexes and constraints, transactions and locking, import and export with tools, and utility functions and PRAGMA, so the path runs from creating a schema all the way to WAL mode and CSV round-trips. The deciding factor is the concurrency model. If writes come from multiple processes or threads, PRAGMA journal_mode=WAL plus a sensible busy_timeout is mandatory; for a single-process script the better investment is defining types and constraints in the schema so bad data fails at insert time. Reach for it when adding local persistence to a CLI, running a migration, doing one-off analysis, or loading a CSV for querying. The boundary against Postgres or MySQL is plain: with no server process there is no network layer, and the bottleneck is usually writes rather than queries.

Databases·52 commands·Last updated 2026-07-21
sqlitedatabasesqlEmbedded Databases

Typical Use Case

For developers who need lightweight local storage: embedded data in desktop and mobile apps, scripts and automation jobs, test fixtures, browser-side data, or small read-heavy server configuration. SQLite has no server process and no setup — one file is the database, and copying it is the migration. Three layers are covered: DML and query technique including upsert, GROUP BY with HAVING, joins and aggregates; indexes and constraints, meaning unique indexes, foreign keys and CHECK, which are the only real lever on how the database behaves once the data grows; and operational work, covering WAL journal mode, dumping a backup, vacuuming space and integrity checking.

Database Connection & Basics 6

sqlite3 file.db
Open or create a SQLite database file
.open file.db
Open a database file from within the sqlite3 CLI
.databases
List all open database connections
.tables
List all tables in the current database
.quit
Exit the sqlite3 CLI
.help
Show help for all available dot commands

Table Operations 6

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
Create table with columns and constraints
DROP TABLE IF EXISTS users;
Drop table if it exists
ALTER TABLE users ADD COLUMN email TEXT;
Add a new column
CREATE TABLE backup AS SELECT * FROM users;
Create a new table from query results
CREATE TEMP TABLE temp_data (id INT);
Create a temporary table, auto-deleted after session
.schema users
View the CREATE TABLE statement for a table

Data CRUD 7

INSERT INTO users (name, age) VALUES ('Tom', 25);
Insert a single record
INSERT INTO users (name, age) VALUES ('Tom', 25), ('Jerry', 30);
Batch insert multiple records
SELECT * FROM users;
Query all records
UPDATE users SET age = 26 WHERE name = 'Tom';
Update records matching condition
DELETE FROM users WHERE name = 'Tom';
Delete records matching condition
REPLACE INTO users (id, name, age) VALUES (1, 'Tom', 26);
Insert or replace based on primary key or unique constraint
INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name;
UPSERT: update on conflict, insert otherwise

Advanced Queries 8

SELECT * FROM users WHERE age > 18;
WHERE condition filtering
SELECT * FROM users ORDER BY age DESC, name ASC;
Multi-field sorting
SELECT status, COUNT(*) FROM users GROUP BY status;
GROUP BY aggregation
SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;
HAVING filter on grouped results
SELECT * FROM users LIMIT 10 OFFSET 20;
Pagination: limit results and skip first 20
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
INNER JOIN between tables
SELECT DISTINCT city FROM users;
Deduplicate query results
SELECT COUNT(*), AVG(age), MAX(age), MIN(age) FROM users;
Aggregate functions: count, avg, max, min

Indexes & Constraints 6

CREATE INDEX idx_users_name ON users(name);
Create index to speed up queries
CREATE UNIQUE INDEX idx_users_email ON users(email);
Create unique index
DROP INDEX IF EXISTS idx_users_name;
Drop an index
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, age INT DEFAULT 0);
Column-level constraints: PK, UNIQUE, NOT NULL, DEFAULT
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INT REFERENCES users(id));
Define foreign key constraint
CREATE TABLE products (id INT, price REAL CHECK(price > 0));
CHECK constraint to restrict value range

Transactions & Locking 7

BEGIN TRANSACTION;
Start a transaction
COMMIT;
Commit transaction, persist all changes
ROLLBACK;
Rollback transaction, undo all uncommitted changes
SAVEPOINT sp1;
Set a savepoint for partial rollback
ROLLBACK TO sp1;
Rollback to a specific savepoint
RELEASE SAVEPOINT sp1;
Release a savepoint
PRAGMA journal_mode=WAL;
Set WAL mode for better concurrent read/write performance

Import/Export & Tools 6

.import --csv data.csv my_table
Import CSV file into a table
.output dump.sql
Redirect output to a file
.dump
Export entire database as SQL text
.read dump.sql
Execute SQL file (restore backup)
VACUUM;
Reclaim free space, reduce database file size
ANALYZE;
Update statistics to help query optimizer

Utility Functions & PRAGMA 6

PRAGMA table_info(users);
View column information of a table
PRAGMA index_list(users);
View index list of a table
PRAGMA integrity_check;
Check database integrity
SELECT datetime('now');
Get current date and time
SELECT strftime('%Y-%m-%d', 'now');
Format date output
SELECT length('hello'), typeof(42), randomblob(16);
Utility functions: length, typeof, randomblob

Parameter matrix

ParameterEffectExample
.open <file>打开或创建数据库文件sqlite3 file.db
.tables / .databases列出当前库的表 / 已打开的连接.tables
.schema <table>查看建表语句.schema users
CREATE TABLE ... PRIMARY KEY定义主键与列级约束CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, age INT DEFAULT 0);
REFERENCES users(id)外键约束CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INT REFERENCES users(id));
CHECK(price > 0)限制列的取值范围CREATE TABLE products (id INT, price REAL CHECK(price > 0));
ON CONFLICT DO UPDATEUPSERT：冲突更新、不冲突插入INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name;
REPLACE INTO按主键或唯一约束插入或整行替换REPLACE INTO users (id, name, age) VALUES (1, 'Tom', 26);
GROUP BY + HAVING分组统计后再过滤分组SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;
LIMIT / OFFSET分页：限制条数并跳过前若干条SELECT * FROM users LIMIT 10 OFFSET 20;
JOIN ... ON关联查询（INNER JOIN）SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
CREATE [UNIQUE] INDEX建索引 / 唯一索引以加速与去重CREATE UNIQUE INDEX idx_users_email ON users(email);
PRAGMA journal_mode=WAL切 WAL 模式提升并发读写性能PRAGMA journal_mode=WAL;
BEGIN / COMMIT / ROLLBACK事务边界控制BEGIN TRANSACTION;
SAVEPOINT / ROLLBACK TO保存点与部分回滚ROLLBACK TO sp1;
.import --csv把 CSV 导入为表.import --csv data.csv my_table
.dump / .read导出整库为 SQL / 执行 SQL 文件恢复.dump
VACUUM / ANALYZE回收空闲空间 / 更新统计信息VACUUM;
PRAGMA integrity_check检查数据库文件完整性PRAGMA integrity_check;

Common pitfalls

Symptom数据库文件越用越大，删掉大量数据后体积纹丝不动

Cause删除只是把页标记为空闲，不会自动把文件缩回去。

Fix执行 `VACUUM;` 回收空闲空间；该操作会重写整库，注意预留磁盘余量。

Symptom查询在数据量上来后越来越慢，执行计划里没有用上索引

Cause统计信息陈旧，优化器按旧数据量做决策；或者查询条件与索引列不匹配。

Fix先跑 `ANALYZE;` 更新统计信息，再用 `PRAGMA index_list(users)` 确认目标列上确实有索引。

Symptom写入时报 database is locked，或并发读多时频繁超时

Cause默认回滚日志模式下读写互斥，长事务还会一直占着锁。

Fix执行 `PRAGMA journal_mode=WAL;` 切换到 WAL；并把批量操作包进事务，用 `COMMIT` 尽早释放。

Symptom批量导入做了一半失败，数据处于半成品状态

Cause多条语句没有事务包裹，前半段已提交无法回退。

Fix用 `BEGIN TRANSACTION;` 开头，出错走 `ROLLBACK;`，成功走 `COMMIT;`；需要更细的粒度时用 `SAVEPOINT sp1;` 和 `ROLLBACK TO sp1;`。

Symptom按某个从别的 SQL 迁过来的语法写查询，直接报错

CauseSQLite 支持大部分 SQL-92 标准，但不支持 RIGHT JOIN 和 FULL JOIN。

Fix改写为 `LEFT JOIN`，或用子查询/`UNION` 的组合达到同样结果。

Symptom恢复备份时报语法错误，文件只恢复了一半

Cause`.read` 逐条执行导出文件，文件里含有不支持的语句或编码不一致。

Fix先 `PRAGMA integrity_check;` 确认库本身完好，再用 `sqlite3 new.db < dump.sql` 整文件重放，观察是否有报错行。

Symptom命令和 DDL 一起执行时看起来没生效

Cause`.tables`、`.schema`、`.import`、`.output` 这类是 sqlite3 命令行专属的点命令，不能放进 SQL 脚本或代码字符串里交给数据库引擎执行。

Fix在 sqlite3 交互 shell 中执行点命令，或在脚本文件里改用等价的 SQL 语句。

Troubleshooting

  1. 1确认文件完整性

    PRAGMA integrity_check;

    输出 ok 表示文件结构完好；出现错误行则应先用 .dump 抢救数据再修复。

  2. 2查看表结构与实际建表语句

    .schema users

    建表语句里能看到约束与索引定义是否如设计预期。

  3. 3查看列信息与索引清单

    PRAGMA table_info(users); PRAGMA index_list(users);

    前者确认列类型与是否可空，后者确认查询条件列上是否已有索引。

  4. 4统计分组结果定位异常数据

    SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;

    输出可直接看出哪些分组数据异常集中，HAVING 在此过滤的是分组而非行。

  5. 5把整库导出为 SQL 做备份

    .output dump.sql && .dump

    先 .output 重定向再 .dump，生成的 dump.sql 即为可重放的备份文本。

  6. 6切到 WAL 并确认生效

    PRAGMA journal_mode=WAL; PRAGMA integrity_check;

    第一条应返回 wal，随后跑一次完整性检查确认切换后库仍可正常读写。

Command Examples

Dump the database before touching the schema

.output dump.sql .dump .open file.db .read dump.sql

.output redirects everything that follows into a file, so an immediately following .dump writes the whole database to dump.sql. Do this before altering tables or adding indexes: SQLite has no system-level snapshot, so a dropped column can only be recovered from this file. Replaying it with .read into an empty database restores everything.

Output

dump.sql  // 数据库被导出为可重放的 SQL 文本

Wrap a bulk edit in a transaction and roll back on failure

BEGIN TRANSACTION; UPDATE users SET age = age + 1 WHERE age < 18; SELECT COUNT(*) FROM users WHERE age = 18; COMMIT;

An UPDATE or DELETE missing its WHERE clause rewrites the whole table. Inside a transaction, run the SELECT first and read the count before COMMIT — if it is not what you expected, ROLLBACK puts the database back exactly as it was.

Group, then filter out small groups with HAVING

SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 1;

WHERE filters rows before grouping; HAVING filters groups after. To find "statuses with more than one record" you must use HAVING — putting the same condition in WHERE only drops the offending rows, leaving COUNT at 1.

Output

status | COUNT(*)
active | 2

UPSERT keeps repeated script runs idempotent

INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name;

Contrast with REPLACE INTO, which deletes then re-inserts — cascading deletes can wipe related rows. ON CONFLICT DO UPDATE updates in place and touches only the columns you name. For migration and import scripts, UPSERT is far safer than REPLACE.

Diagnosing a slow query: columns, indexes, query plan

PRAGMA table_info(users); PRAGMA index_list(users); ANALYZE;

table_info reveals column types and defaults — many mysterious "insert fails" cases are type or constraint problems. index_list confirms the index actually exists. ANALYZE refreshes the statistics the optimizer needs. After these three, a slow query is almost always down to a missing index or stale statistics.

Output

cid | name | type | notnull | dflt_value | pk
0 | id | INTEGER | 0 | NULL | 1
1 | name | TEXT | 1 | NULL | 0
2 | age | INT | 0 | 0 | 0

Savepoints: roll back one section of a long transaction

BEGIN TRANSACTION; SAVEPOINT sp1; DELETE FROM users WHERE name = 'Bob'; ROLLBACK TO sp1; RELEASE SAVEPOINT sp1; COMMIT;

SELECT COUNT(*) after the DELETE still returns 3: ROLLBACK TO sp1 undoes only what happened after the savepoint and leaves everything before it intact. That is the smallest rollback unit for "lose one batch, not the whole job" processing of large data sets.

Common Pitfalls

  • An UPDATE or DELETE missing its WHERE clause rewrites or drops the whole table. SQLite has no system snapshot, so recovery means replaying a .dump backup — run the matching SELECT first and check the count.
  • REPLACE INTO deletes before it inserts when the primary key conflicts, so cascading deletes can wipe rows in related tables with no way back. Prefer ON CONFLICT DO UPDATE.
  • SQLite does not enforce foreign keys by default (PRAGMA foreign_keys reports 0), so REFERENCES alone lets bad rows in. Enable it on the same connection inside migration scripts and re-verify.
  • Without WAL, concurrent writers block each other and you get "database is locked" — long transactions make it far worse. Use PRAGMA journal_mode=WAL in production, but remember it creates -wal/-shm sidecar files that a .db-only backup will miss.
  • Running .read dump.sql against a database that already has data silently appends and duplicates rows without erroring. Restore into an empty database.
  • .import --csv relies on the CSV column order matching the table; reorder a column and the data lands silently in the wrong fields. Spot-check with SELECT right after importing.
  • When an UPDATE assigns a value whose type disagrees with the column declaration, SQLite dynamic typing can store a number as text. Check PRAGMA table_info rather than eyeballing whether the value looks right.
  • VACUUM only runs without an active transaction and rewrites the entire file — on a large database it locks the DB for a long time and needs roughly twice the free disk space. Stop writers first.

Tips

  • SQLite is a zero-configuration database, no server process needed
  • Use .dump for backup, VACUUM to reclaim space
  • PRAGMA journal_mode=WAL significantly improves concurrent performance
  • SQLite supports most SQL-92 standards but not RIGHT/FULL JOIN
  • The .import command imports CSV files directly into tables

FAQ

Should I use REPLACE INTO or an UPSERT when a row already exists?

REPLACE INTO is a delete-then-insert: on a primary key or unique conflict it drops the old row and inserts a new one, firing delete and insert behaviour and any cascading foreign keys, so it is only safe when you truly mean to overwrite the whole row. An UPSERT is finer grained: `INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name` updates only the columns you list (excluded holds the incoming values) and otherwise leaves the existing row intact. If any existing field must survive, use the UPSERT.

How do I back up and restore a SQLite database from the command line?

For a backup, run `.output dump.sql` to redirect the next output to a file, then `.dump` to write the whole database as SQL text; restore it later with `.read dump.sql`. To bring in a CSV use `.import --csv data.csv my_table`, and the same `.output` trick exports a table to CSV. Once a file bloats, `VACUUM;` reclaims free pages and shrinks it, while `ANALYZE;` refreshes the statistics the optimizer uses to pick indexes. Run `PRAGMA integrity_check;` whenever you suspect corruption.

Which columns deserve an index, and what does a bad index cost?

Index the columns you filter, sort and join on frequently: `CREATE INDEX idx_users_name ON users(name)`. When uniqueness is the real goal use `CREATE UNIQUE INDEX idx_users_email ON email`, which doubles as a data constraint. The cost is on writes — every INSERT and UPDATE has to maintain the index and it consumes disk, so do not index columns you never query. Use `PRAGMA index_list(users);` to audit what exists and `DROP INDEX IF EXISTS idx_users_name;` to clear the useless ones. Note that `CREATE TABLE backup AS SELECT * FROM users` copies data only — indexes are not inherited and must be recreated.

What is the difference between WHERE and HAVING, and how do they combine with GROUP BY?

WHERE filters raw rows before grouping and may only reference ordinary columns, never aggregates. HAVING filters after the grouping and aggregation step and requires GROUP BY. A canonical report is `SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5`, which keeps only the statuses with more than five members. When you need both filters, the efficient order is WHERE to shrink the data, then GROUP BY, then HAVING on the aggregated result. For multi-column sorting use `ORDER BY age DESC, name ASC`.

I keep hitting "database is locked" with concurrent writers — what should I change?

Switch the journal mode first: `PRAGMA journal_mode=WAL;` allows readers and a writer to proceed together and is the single biggest win for concurrency. Wrap write batches in a transaction — `BEGIN TRANSACTION;`, run several INSERTs, then `COMMIT;` — so you take the lock once instead of per statement. When only part of a long transaction is bad, mark a point with `SAVEPOINT sp1;`, recover with `ROLLBACK TO sp1;`, and finish the savepoint with `RELEASE SAVEPOINT sp1;`, or discard everything with `ROLLBACK;`. Scratch data belongs in `CREATE TEMP TABLE`, which is dropped when the session ends and stays out of the main lock.

Related Cheatsheets

6 related cheatsheets

MySQL Cheatsheet - Queries, Joins, Users & IndexesA MySQL command handbook for DBAs, backends, and data analysts, covering database & table management, CRUD, index optimization and slow-query localization with EXPLAIN, user privileges, logical backup & recovery, and replication — highlighting operations that can lock tables or stall replication.PostgreSQL Cheatsheet - PostgreSQL Command ReferenceA PostgreSQL command handbook for backend engineers, DBAs, and data analysts, covering connection & role management, database & table operations, EXPLAIN analysis, lock & transaction troubleshooting, pg_dump/pg_restore backup & recovery, and streaming replication, with paths for locating lock waits and table bloat.Redis Cheatsheet - Keys, Strings, Hash & Expire CommandsA Redis command handbook for the cache/backend and its operators, covering string and per-data-structure operations, key space and TTL management, RDB/AOF persistence trade-offs, memory and big-key inspection, slow query monitoring, cluster & replication, and practical debugging of hot keys, big keys, and cache puncturing/cascading.MongoDB Cheatsheet - MongoDB Command ReferenceA MongoDB reference for backend engineers, covering database & collection management, document CRUD, query operators, index strategies that keep queries off full collection scans, the aggregation pipeline, and replica-set vs sharded-cluster responsibilities.Neo4j Cypher Cheatsheet - Neo4j Graph Database Command ReferenceA Neo4j Cypher reference for working with graph data where relationships matter more than rows, covering node & relationship creation, pattern matching, updates & deletes, aggregation and ordering, constraints & indexes, and traversal-style graph queries.SQL JOIN Cheatsheet - SQL JOIN Query ReferenceA SQL JOIN reference for writing multi-table queries correctly, covering INNER, LEFT/RIGHT/FULL OUTER, CROSS, self-joins, USING/natural joins, and when a correlated subquery with EXISTS beats a JOIN — plus the row-duplication pitfall of joins on one-to-many tables.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Maintained by LaoHand

Publicly updated on Jul 21, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.

Contact Us