SQLite3 Commands Cheat Sheet – Quick Reference Guide (2025)

15 Apr, 2025

SQLite3 is a lightweight, serverless, and self-contained SQL database engine widely used in applications, embedded systems, and development environments. Below is a handy cheat sheet for common SQLite3 commands and SQL queries.

SQLite3 Cheat Sheet for Quick Reference

Need a quick SQLite3 reference? This cheat sheet covers essential SQLite3 commands for database management, queries, imports/exports, and more. Perfect for developers!

1. Starting SQLite3

Command	Description
`sqlite3`	Open SQLite3 in interactive mode
`sqlite3 database.db`	Open (or create) a database file
`.exit` or `.quit`	Exit SQLite3
`.help`	List all available SQLite3 dot commands

2. Database Operations

Command	Description
`.open database.db`	Open (or create) a database file
`.databases`	List attached databases
`.backup ?DB? FILE`	Backup database to a file
`.restore ?DB? FILE`	Restore database from a file
`.dump ?TABLE?`	Export database (or table) as SQL script

"Need to recover a corrupted database? Try SQLite Recovery Tool."

3. Table Operations

Create a Table

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
);

List Tables

Command	Description
`.tables`	List all tables
`.schema ?TABLE?`	Show table structure (SQL schema)

Modify Tables

-- Add a column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename a table
ALTER TABLE users RENAME TO customers;

-- Drop a table
DROP TABLE users;

4. Querying Data

Basic Queries

-- Select all rows
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Filter rows with WHERE
SELECT * FROM users WHERE age > 18;

-- Sort results
SELECT * FROM users ORDER BY name ASC;

-- Limit results
SELECT * FROM users LIMIT 10;

Aggregate Functions

-- Count rows
SELECT COUNT(*) FROM users;

-- Group data
SELECT age, COUNT(*) FROM users GROUP BY age;

-- Get max/min/avg
SELECT MAX(age), MIN(age), AVG(age) FROM users;

Joins

-- Inner Join
SELECT users.name, orders.amount 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;

-- Left Join
SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

5. Modifying Data

Command	Description
`INSERT INTO users (name, email) VALUES ('John', 'john@example.com');`	Add a new row
`UPDATE users SET email = 'new@example.com' WHERE id = 1;`	Update a row
`DELETE FROM users WHERE id = 1;`	Delete a row

6. Indexes & Constraints

-- Create an index
CREATE INDEX idx_name ON users(name);

-- Add a UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);

-- Remove an index
DROP INDEX idx_name;

7. Transactions

-- Begin a transaction
BEGIN TRANSACTION;

-- Commit changes
COMMIT;

-- Rollback changes
ROLLBACK;

8. Exporting & Importing Data

Command	Description
`.mode csv`	Set output mode to CSV
`.output data.csv`	Export query results to a file
`.import data.csv users`	Import CSV into a table
`.read script.sql`	Execute SQL from a file

9. Miscellaneous Commands

Command	Description
`.headers on/off`	Toggle column headers
`.mode column`	Display output in columns
`.show`	Display current settings
`.timer on/off`	Measure query execution time

Conclusion

This cheat sheet covers the most essential SQLite3 commands for database management, querying, and administration. Keep it handy for quick reference while working with SQLite databases!

For more details, refer to the official SQLite documentation.

Hope this helps you!

#sqlite