Back SQLite3 Expert Cheat Sheet Programming
Download Open
Show description 2,401 chars · Programming

SQLite3 Expert Cheat Sheet

SQLite3 Expert Cheat Sheet





SQLite3 • Pro Cheat Sheet

SQLite3: Commands, Patterns, and Interactive Builder





Navigate

Basics
Inspect DB
Querying
Import / Export
Schema & Migrations
Indexes & Perf
FTS5 & JSON1
Transactions
Explain / Analyze
Backups & Safety
One‑Liners
Interactive Builder




Basics

CLI.metaSQL

Copy$ sqlite3 my.db
sqlite> .help -- show meta-commands
sqlite> .quit


Common meta‑commands (.tables, .schema, headers, modes)
Copysqlite> .tables -- list tables
sqlite> .schema open_mics -- show CREATE TABLE
sqlite> .headers on -- include column names
sqlite> .mode column -- pretty tabular
sqlite> .mode csv -- csv output
sqlite> .separator \t -- switch delimiter
sqlite> .output result.csv -- send output to file
sqlite> .output stdout -- back to screen







Inspect the Database

Copysqlite> PRAGMA database_list; -- attached dbs & paths
sqlite> .tables -- tables & views
sqlite> PRAGMA table_info(open_mics); -- columns
sqlite> PRAGMA foreign_key_list(t); -- fkeys on table t



Tip: If a table seems “missing,” verify you’re opening the correct file using PRAGMA database_list; and check the path.






Querying Essentials

CopySELECT state, city, venue, address, day, time, recurring, description
FROM open_mics;


Filters, Sorting, Limits
CopySELECT *
FROM open_mics
WHERE state='CA' AND day IN ('sunday','monday')
ORDER BY city, time
LIMIT 50;



Computed Columns & CASE
CopySELECT venue,
CASE WHEN recurring IN (1,'1','true','TRUE') THEN 'Yes' ELSE 'No' END AS recurring_text
FROM open_mics;



Joins & Aggregates
SELECT state, COUNT(*) AS shows
FROM open_mics
GROUP BY state
ORDER BY shows DESC;



Window Functions (SQLite ≥ 3.25)
SELECT state, city, venue,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY city) rn
FROM open_mics;






Import / Export


Copy$ sqlite3 -header -csv open_mics.db \
"SELECT state, city, venue, address, day, time, recurring, description FROM open_mics;"
> openmics.csv

Copy$ sqlite3 my.db
sqlite> .mode csv
sqlite> .import data.csv open_mics




TSV & Pretty Table
Copy$ sqlite3 -header -separator $'\t' open_mics.db \
"SELECT * FROM open_mics LIMIT 20;" > sample.tsv






Schema & Migrations

CREATE TABLE open_mics (
id INTEGER PRIMARY KEY,
source TEXT NOT NULL,
state TEXT, city TEXT, venue TEXT,
address TEXT,…