Omar Hosney

LinkedIn

SQLite Command Cheatsheet 📘

Database Operations 🗃️

sqlite3 [filename] - Open or create a database.
.open [filename] - Open an existing database.
.databases - List all open databases.
.save [filename] - Save the database to a file.
.backup [filename] - Backup the current database.

Table Operations 🛠️

CREATE TABLE - Create a new table.
DROP TABLE - Delete a table.
ALTER TABLE - Modify an existing table.
.schema [table] - Show the table schema.
PRAGMA table_info([table]) - Show table structure.

Data Manipulation ✍️

INSERT INTO - Add data to a table.
UPDATE - Modify existing data.
DELETE - Remove data.
SELECT - Query data from tables.
REPLACE INTO - Insert or replace data.

Queries & Filtering 🔍

SELECT * FROM [table] - Fetch all rows.
WHERE - Filter results by conditions.
ORDER BY - Sort results.
GROUP BY - Group rows by a column.
HAVING - Filter grouped results.

Indexes & Constraints 📊

CREATE INDEX - Create an index.
DROP INDEX - Delete an index.
UNIQUE - Ensure unique values.
CHECK - Validate data constraints.
FOREIGN KEY - Enforce relationships.

Advanced Commands 🚀

ATTACH [file] - Attach another database.
DETACH - Detach a database.
EXPLAIN QUERY PLAN - Analyze query performance.
PRAGMA - Query database settings.
.import/.export - Import/export CSV data.

Transaction Control ⚡

BEGIN - Start a transaction.
COMMIT - Save changes.
ROLLBACK - Undo changes.
END - Commit or rollback a transaction.
SAVEPOINT - Mark a transaction checkpoint.

Aggregates 📈

COUNT() - Count rows.
SUM() - Calculate totals.
AVG() - Calculate averages.
MAX() - Find maximum value.
MIN() - Find minimum value.

Utilities ⚙️

.tables - List all tables.
.exit - Quit SQLite shell.
.help - Show help menu.
.mode - Set output mode.
.headers - Toggle column headers.

Import Data from CSV 📥

Prepare Table - Ensure the table exists for importing data.
.mode csv - Set input mode to CSV.
.import [file.csv] [table] - Import records into the table.
Example:
.import data.csv users

Export Query to CSV 📤

.mode csv - Set output mode to CSV.
.headers on - Include column headers in the output.
.output [file.csv] - Redirect query results to a CSV file.
Example:
.mode csv .headers on .output result.csv SELECT * FROM users WHERE age > 25;

Append Data to Existing Table 🔄

Prepare Table - Ensure the table matches the CSV structure.
.mode csv - Set input mode to CSV.
.import --append [file.csv] [table] - Append data to the table.
Example:
.import --append new_data.csv users