sql
SELECT * FROM users;Notes
Convenient interactively, but explicit column lists are often better for apps and stable contracts.
SQLite CRUD and SELECT Cheatsheet
High-value SELECT, INSERT, UPDATE, DELETE, UPSERT, ORDER BY, LIMIT, and filtering patterns for day-to-day SQLite development.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Read and filter rows
Select, sort, paginate, and project rows efficiently.
sql
SELECT id, email, created_at FROM users;Notes
Fetching only the columns you need reduces transfer and simplifies downstream code.
sql
SELECT id, title FROM posts WHERE title LIKE '%sqlite%';Notes
LIKE is convenient for simple matching; full-text search is better for larger text search needs.
sql
SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 20;Notes
A core pattern for dashboards, logs, and feed-style pages.
sql
SELECT id, email FROM users ORDER BY id LIMIT 20 OFFSET 40;Notes
Works well for smaller datasets, though keyset pagination scales better for very large tables.
Insert, update, delete, and upsert
Core write patterns including conflict handling.
sql
INSERT INTO users (email, display_name) VALUES ('ava@example.com', 'Ava');Notes
Explicit column lists make inserts safer as schemas evolve.
sql
INSERT INTO tags (name) VALUES ('sql'), ('sqlite'), ('database');Notes
Batch inserts are concise and usually faster than one statement per row.
sql
UPDATE users SET timezone = 'America/Los_Angeles' WHERE id = 1;Notes
Always verify the WHERE clause when running updates interactively.
sql
DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;Notes
A common cleanup pattern for caches, sessions, and temporary records.
sql
INSERT INTO users (email, display_name)
VALUES ('ava@example.com', 'Ava Martin')
ON CONFLICT(email) DO UPDATE SET display_name = excluded.display_name;Notes
A high-value pattern for sync jobs, imports, and idempotent writes.
Ignore duplicates on conflict
Keep the existing row when a unique key collides.
sqlANYsqlitedo nothingconflict
sql
INSERT INTO tags (name) VALUES ('sqlite') ON CONFLICT(name) DO NOTHING;Notes
Useful for de-duplicated lookup tables and seeding scripts.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.