SQLite Syntax Guide
1. CREATE TABLE
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype
);
Example:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);
2. INSERT DATA
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Example:
INSERT INTO users (name, age) VALUES ('Alice', 25);
3. SELECT DATA
SELECT column1, column2 FROM table_name;
Example:
SELECT name, age FROM users;
SELECT * FROM users; -- Select all columns
4. UPDATE DATA
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
Example:
UPDATE users SET age = 26 WHERE name = 'Alice';
5. DELETE DATA
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM users WHERE name = 'Alice';
6. WHERE Clause
SELECT * FROM table_name WHERE condition;
Example:
SELECT * FROM users WHERE age > 20;
7. ORDER BY
SELECT * FROM table_name ORDER BY column1 ASC|DESC;
Example:
SELECT * FROM users ORDER BY age DESC;
8. LIMIT
SELECT * FROM table_name LIMIT number;
Example:
SELECT * FROM users LIMIT 5;
9. LIKE & Wildcards
SELECT * FROM table_name WHERE column LIKE pattern;
Example:
SELECT * FROM users WHERE name LIKE 'A%';
-- % matches any number of characters
-- _ matches one character
10. JOINs
INNER JOIN:
SELECT * FROM table1
INNER JOIN table2
ON table1.column = table2.column;
LEFT JOIN:
SELECT * FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Example:
SELECT users.name, orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
11. ALTER TABLE
ADD a new column:
ALTER TABLE table_name ADD COLUMN column_name datatype;
RENAME a table:
ALTER TABLE old_name RENAME TO new_name;
12. DROP TABLE
DROP TABLE table_name;
Example:
DROP TABLE users;
13. CREATE INDEX
CREATE INDEX index_name ON table_name(column);
Example:
CREATE INDEX idx_name ON users(name);
14. Transactions
BEGIN TRANSACTION;
-- SQL statements
COMMIT;
ROLLBACK; -- Undo changes
