SQLite

This SQLite cheatsheet is a concise guide

1. What is SQLite?

2. Data Types

SQLite is dynamically typed

TypeDescription
NULLNo value
INTEGERWhole numbers
REALFloating numbers
TEXTStrings
BLOBBinary data

3. Create Database

sqlite3 mydb.db

4. Create Table

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

5. Insert Data

INSERT INTO users (name, age, email) VALUES ('Uday', 22, 'uday@gmail.com');

Insert multiple:

INSERT INTO users (name, age) VALUES ('A', 20), ('B', 25);

6. Select Data

SELECT * FROM users; SELECT name, age FROM users;

With condition:

SELECT * FROM users WHERE age > 20;

7. Operators

=, !=, >, <, >=, <= AND, OR, NOT LIKE, IN, BETWEEN

Example:

SELECT * FROM users WHERE name LIKE 'U%';

8. Update Data

UPDATE users SET age = 23 WHERE id = 1;

9. Delete Data

DELETE FROM users WHERE id = 1;

Delete all:

DELETE FROM users;

10. Constraints

NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT

Example:

age INTEGER CHECK(age >= 18)

11. Primary Key

id INTEGER PRIMARY KEY AUTOINCREMENT

12. Foreign Key

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

Enable:

PRAGMA foreign_keys = ON;

13. Joins

INNER JOIN

SELECT users.name, orders.id FROM users INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN

SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;

14. Aggregate Functions

COUNT(), SUM(), AVG(), MIN(), MAX()

Example:

SELECT COUNT(*) FROM users;

15. GROUP BY

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

16. HAVING

SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

17. ORDER BY

SELECT * FROM users ORDER BY age ASC; SELECT * FROM users ORDER BY age DESC;

18. LIMIT

SELECT * FROM users LIMIT 5;

19. DISTINCT

SELECT DISTINCT age FROM users;

20. Index

CREATE INDEX idx_name ON users(name);

Delete index:

DROP INDEX idx_name;

21. Views

CREATE VIEW user_view AS SELECT name, age FROM users;

22. Transactions

BEGIN; INSERT INTO users VALUES (...); COMMIT;

Rollback:

ROLLBACK;

23. ALTER TABLE

ALTER TABLE users ADD COLUMN phone TEXT;

Rename:

ALTER TABLE users RENAME TO new_users;

24. Drop Table

DROP TABLE users;

25. Subqueries

SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

26. CASE Statement

SELECT name, CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END FROM users;

27. LIKE & Wildcards

% → any characters _ → single character

Example:

SELECT * FROM users WHERE name LIKE 'A%';

28. IN Operator

SELECT * FROM users WHERE age IN (20, 25);

29. BETWEEN

SELECT * FROM users WHERE age BETWEEN 18 AND 30;

30. NULL Handling

IS NULL IS NOT NULL

31. String Functions

LENGTH(name) UPPER(name) LOWER(name) SUBSTR(name, 1, 3)

32. Date & Time

DATE('now') TIME('now') DATETIME('now')

33. PRAGMA (SQLite Settings)

PRAGMA table_info(users); PRAGMA database_list;

34. Backup DB

.backup backup.db

35. Import/Export

.mode csv .import file.csv users

Export:

.headers on .mode csv .output file.csv SELECT * FROM users;

36. Triggers

CREATE TRIGGER log_insert AFTER INSERT ON users BEGIN INSERT INTO logs VALUES ('User added'); END;

37. Full Text Search (FTS)

CREATE VIRTUAL TABLE docs USING fts5(content);

38. JSON Support

SELECT json_extract('{"name":"Uday"}', '$.name');

39. Attach Database

ATTACH DATABASE 'test.db' AS test;

40. Detach

DETACH DATABASE test;

⚡ Pro Tips (Important for Exams & Projects)

PRAGMA foreign_keys = ON;