SQL Cheat Sheet
SQL cheat sheet: syntax and examples of common statements — SELECT, JOIN, GROUP BY, INSERT, UPDATE, DELETE, aggregates and constraints — for quick lookup while writing queries.
How It Works
SQL is the standard declarative language for relational databases: you state what you want and the optimizer decides how to fetch it. DML (SELECT/INSERT/UPDATE/DELETE) handles data, DDL (CREATE/ALTER/DROP) defines structure, DCL (GRANT) manages rights.
Data lives in tables (rows/columns) linked by foreign keys; JOIN assembles multiple tables into a result set. Indexes speed reads but slow writes; transactions guarantee concurrency safety via ACID (Atomic, Consistent, Isolated, Durable). Prefer parameterized queries to prevent SQL injection.
| Statement | Meaning | Example |
|---|---|---|
| SELECT | Select columns (* for all) | SELECT id, name FROM users |
| FROM | Specify source table | FROM orders |
| WHERE | Row filter condition | WHERE status = 'paid' |
| GROUP BY | Group rows for aggregation | GROUP BY category |
| HAVING | Filter grouped results | HAVING COUNT(*) > 1 |
| ORDER BY | Sort (ASC/DESC) | ORDER BY created_at DESC |
| LIMIT | Limit number of rows | LIMIT 10 |
| OFFSET | Skip first N rows (paging) | OFFSET 20 |
| DISTINCT | Return distinct values | SELECT DISTINCT city |
| JOIN | Inner join two tables | FROM a JOIN b ON a.id = b.a_id |
| LEFT JOIN | Left join, keep all left rows | FROM a LEFT JOIN b ON a.id = b.a_id |
| RIGHT JOIN | Right join, keep all right rows | FROM a RIGHT JOIN b ON a.id = b.a_id |
| INNER JOIN | Inner join (same as JOIN) | FROM a INNER JOIN b ON a.id = b.a_id |
| ON | Join condition | ON a.id = b.a_id |
| AS | Table or column alias | SELECT name AS 姓名 |
| INSERT INTO | Insert a row | INSERT INTO users (name) VALUES ('Tom') |
| UPDATE | Update rows | UPDATE users SET name = 'Tom' WHERE id = 1 |
| DELETE | Delete rows | DELETE FROM users WHERE id = 1 |
| CREATE TABLE | Create a table | CREATE TABLE t (id INT PRIMARY KEY) |
| ALTER TABLE | Alter table structure | ALTER TABLE t ADD COLUMN age INT |
| DROP TABLE | Drop a table | DROP TABLE t |
| PRIMARY KEY | Primary key constraint | id INT PRIMARY KEY |
| FOREIGN KEY | Foreign key constraint | FOREIGN KEY (a_id) REFERENCES a(id) |
| INDEX | Create index to speed up query | CREATE INDEX idx_name ON users(name) |
| UNION | Combine two result sets (distinct) | SELECT a FROM t1 UNION SELECT a FROM t2 |
| LIKE | Pattern match (% any, _ one) | WHERE name LIKE 'T%' |
| IN | Value in a set | WHERE status IN ('paid','done') |
| BETWEEN | Inclusive range | WHERE age BETWEEN 18 AND 30 |
| NULL / IS NULL | Null check (cannot use =) | WHERE deleted_at IS NULL |
| COUNT / SUM / AVG | Aggregate functions | SELECT COUNT(*) FROM users |
| CASE WHEN | Conditional expression | CASE WHEN age>18 THEN 'adult' ELSE 'minor' END |
| EXPLAIN | Show execution plan | EXPLAIN SELECT * FROM users |
Frequently Asked Questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows matching in both tables; LEFT JOIN returns all rows from the left table, filling right-side columns with NULL when unmatched. Use LEFT JOIN to keep every master record, INNER JOIN for the intersection.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can reference aggregates (e.g. COUNT(*)). You cannot use aggregate functions in WHERE.
What is the difference between % and _ in LIKE?
% matches any length (including zero) of characters; _ matches exactly one character. 'T%' matches any string starting with T; 'T_m' matches T, one char, then m (e.g. Tom).
How do I test for NULL?
NULL means unknown and cannot be tested with = NULL; use IS NULL / IS NOT NULL. In most databases comparing NULL to anything yields unknown (not true), which is a common trap.