⚠ Created by ChatGPT 🤖

Here’s a handy SQLite3 cheatsheet covering the most commonly used commands and features.

🗄️ Database Basics

-- Open or create a database (CLI)
sqlite3 mydatabase.db

-- Show tables
.tables

-- Show schema of a table
.schema tablename

-- Exit CLI
.quit

📁 Creating Tables

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

✏️ Insert Data

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

🔍 Query Data

-- Basic SELECT
SELECT * FROM users;

-- WHERE clause
SELECT * FROM users WHERE name = 'Alice';

-- LIKE and wildcards
SELECT * FROM users WHERE name LIKE 'A%';

-- ORDER BY
SELECT * FROM users ORDER BY created_at DESC;

-- LIMIT
SELECT * FROM users LIMIT 10;

🛠️ Update and Delete

-- UPDATE
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;

🔄 Joins

-- INNER JOIN
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

⚙️ Indexes

CREATE INDEX idx_users_email ON users(email);

⚡ Transactions

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- or ROLLBACK;

🧰 Useful Commands (SQLite CLI)

.headers ON
.mode column
.timer ON

External Resources