SQLite Database Cheatsheet

SQLite is a lightweight, self-contained, and serverless relational database engine. It’s widely used in various applications and environments due to its simplicity and ease of integration. This cheatsheet provides a quick reference guide for common SQLite commands and operations.

1. Getting Started

1.1 Creating a Database

sqlite3 mydatabase.db

1.2 Connecting to a Database

sqlite3 mydatabase.db

1.3 Exiting SQLite Shell

.exit

2. Basic SQL Operations

2.1 Creating a Table

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

2.2 Inserting Data

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

2.3 Querying Data

SELECT * FROM users;

2.4 Updating Data

UPDATE users SET email = '[email protected]' WHERE id = 1;

2.5 Deleting Data

DELETE FROM users WHERE id = 1;

3. Advanced Operations

3.1 Indexing

CREATE INDEX idx_username ON users (username);

3.2 Transactions

BEGIN TRANSACTION;
-- SQL statements
COMMIT;

3.3 Constraints

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

4. Backup and Restore

4.1 Backing Up a Database

sqlite3 mydatabase.db .dump > backup.sql

4.2 Restoring a Database

sqlite3 mynewdatabase.db < backup.sql

5. Common Queries

5.1 Aggregations

SELECT AVG(amount) FROM orders;

5.2 Joins

SELECT users.username, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

5.3 Filtering

SELECT * FROM users WHERE username LIKE 'a%';

6. Miscellaneous Tips

6.1 Checking SQLite Version

SELECT sqlite_version();

6.2 Displaying Table Schema

.schema users

6.3 Optimizing Database

ANALYZE;

This cheatsheet covers essential SQLite commands for creating databases, performing basic SQL operations, advanced features, backup and restore processes, common queries, and miscellaneous tips. Keep it handy for quick reference and efficient SQLite database management in your projects.

FAQ