SQLite
This SQLite cheatsheet is a concise guide
1. What is SQLite?
- Lightweight, serverless database
- Stored in a single `.db` file
- Used in Android, browsers, apps
2. Data Types
SQLite is dynamically typed
| Type | Description |
|---|---|
| NULL | No value |
| INTEGER | Whole numbers |
| REAL | Floating numbers |
| TEXT | Strings |
| BLOB | Binary 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)
-
SQLite = no server
-
Best for:
- Android apps 📱
- Offline apps
-
Always use:
PRAGMA foreign_keys = ON;