SELECT Basics
- SELECT *All columns
- SELECT col1, col2Specific columns
- DISTINCTUnique values
- AS aliasColumn alias
- LIMIT nLimit rows
- OFFSET nSkip rows
WHERE Conditions
- = != < > <= >=Comparison
- AND OR NOTLogical
- BETWEEN a AND bRange
- IN (a, b, c)In list
- LIKE '%pattern%'Pattern match
- IS NULLNull check
Data Modification
- INSERT INTOAdd rows
- UPDATE ... SETModify rows
- DELETE FROMRemove rows
- TRUNCATE TABLERemove all rows
Aggregate Functions
- COUNT(*)Count rows
- SUM(col)Sum values
- AVG(col)Average
- MIN(col)Minimum
- MAX(col)Maximum
- GROUP BYGroup rows
- HAVINGFilter groups
JOIN Types
-- INNER JOIN (matching rows only)
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN (all from left, matching from right)
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- RIGHT JOIN (all from right, matching from left)
SELECT * FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- FULL OUTER JOIN (all from both)
SELECT * FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
Table Operations (DDL)
-- Create table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Alter table
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
-- Drop table
DROP TABLE users;
DROP TABLE IF EXISTS users;
String Functions
- UPPER(s)Uppercase
- LOWER(s)Lowercase
- LENGTH(s)String length
- TRIM(s)Remove whitespace
- CONCAT(a, b)Concatenate
- SUBSTRING(s, start, len)Extract
ORDER BY
- ORDER BY colSort ascending
- ORDER BY col DESCSort descending
- ORDER BY col1, col2Multiple columns
- NULLS FIRST/LASTNull ordering
Common Queries
-- Count by category
SELECT category, COUNT(*) FROM products GROUP BY category;
-- Filter with HAVING
SELECT category, AVG(price) FROM products
GROUP BY category HAVING AVG(price) > 50;
-- Subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
-- CASE expression
SELECT name, CASE WHEN price > 100 THEN 'expensive' ELSE 'cheap' END AS tier
FROM products;
Indexes & Constraints
- PRIMARY KEYUnique identifier
- FOREIGN KEYReference constraint
- UNIQUEUnique values
- NOT NULLRequired field
- CHECKValue constraint
- CREATE INDEXAdd index
LIKE Patterns
- %Any characters
- _Single character
- '%abc'Ends with abc
- 'abc%'Starts with abc
- '%abc%'Contains abc
- 'a_c'a?c pattern