sqlANYsqliteselect
sql
SELECT * FROM users;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.
sqlANYsqliteprojectioncolumns
sql
SELECT id, email, created_at FROM users;Fetching only the columns you need reduces transfer and simplifies downstream code.
sqlANYsqlitewherelike
sql
SELECT id, title FROM posts WHERE title LIKE '%sqlite%';LIKE is convenient for simple matching; full-text search is better for larger text search needs.
sqlANYsqliteorder bylimit
sql
SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 20;A core pattern for dashboards, logs, and feed-style pages.
sqlANYsqlitepaginationoffset
sql
SELECT id, email FROM users ORDER BY id LIMIT 20 OFFSET 40;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.
sqlANYsqliteinsert
sql
INSERT INTO users (email, display_name) VALUES ('ava@example.com', 'Ava');Explicit column lists make inserts safer as schemas evolve.
sqlANYsqlitebulk insert
sql
INSERT INTO tags (name) VALUES ('sql'), ('sqlite'), ('database');Batch inserts are concise and usually faster than one statement per row.
sqlANYsqliteupdate
sql
UPDATE users SET timezone = 'America/Los_Angeles' WHERE id = 1;Always verify the WHERE clause when running updates interactively.
sqlANYsqlitedelete
sql
DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;A common cleanup pattern for caches, sessions, and temporary records.
sqlANYsqliteupserton conflict
sql
INSERT INTO users (email, display_name)
VALUES ('ava@example.com', 'Ava Martin')
ON CONFLICT(email) DO UPDATE SET display_name = excluded.display_name;A high-value pattern for sync jobs, imports, and idempotent writes.
sqlANYsqlitedo nothingconflict
sql
INSERT INTO tags (name) VALUES ('sqlite') ON CONFLICT(name) DO NOTHING;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.