Cheat sheet · Back end and data
SQL cheat sheet
Read, filter, group, join, change and design data with standard SQL.
- Sections
- 6
- Items
- 27
- Area
- Back end and data
- Download
- PNG, PDF
Cheat sheet
SQL
Read, filter, group, join, change and design data with standard SQL.
Read data
SELECT name, age FROM users;- Pick columns from a table
SELECT DISTINCT city FROM users;- Remove repeated values
WHERE age >= 18 AND city = 'Pune'- Filter rows
WHERE name LIKE 'A%'- Match a pattern (% means anything)
WHERE id IN (1, 2, 3)- One of several values
WHERE x IS NULL- No value (never use = NULL)
ORDER BY age DESC LIMIT 10- Sort, then keep the first 10
Summaries
COUNT(*) SUM(x) AVG(x) MIN(x) MAX(x)- Aggregate functions
GROUP BY city- One result row for each city
HAVING COUNT(*) > 5- Filter groups (WHERE filters rows)
Joins
INNER JOIN b ON a.id = b.a_id- Only rows that match in both tables
LEFT JOIN b ON a.id = b.a_id- All rows of a, with b when it matches
RIGHT JOIN- All rows of b, with a when it matches
FULL JOIN- All rows of both
Change data
INSERT INTO users (name, age) VALUES ('Asha', 30);- Add a row
UPDATE users SET age = 31 WHERE id = 1;- Change rows (always use WHERE)
DELETE FROM users WHERE id = 1;- Remove rows (always use WHERE)
Design tables
CREATE TABLE users (id INT PRIMARY KEY, name TEXT NOT NULL);- Make a table
FOREIGN KEY (a_id) REFERENCES a(id)- Link to a row in another table
UNIQUE (email)- No two rows may share this value
ALTER TABLE users ADD COLUMN city TEXT;- Add a column
CREATE INDEX idx_email ON users (email);- Make lookups on a column faster
DROP TABLE users;- Delete the table and its data
Bigger queries
WITH recent AS (SELECT ...) SELECT * FROM recent;- A named step (CTE) to keep a query readable
ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)- Number rows inside each group
BEGIN; ... COMMIT;- Make several changes succeed or fail together
ROLLBACK;- Cancel the changes in a transaction
Remember
- Order SQL runs in: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This explains most surprising errors.
- Run a SELECT with the same WHERE before you run an UPDATE or DELETE.
Learn it properly
A cheat sheet helps you remember. These lessons explain the ideas step by step, with examples.