SELECT * FROM table | Select all columns |
SELECT col1, col2 FROM table | Select specific columns |
SELECT DISTINCT col FROM table | Select unique values |
SELECT col AS alias FROM table | Column alias |
SELECT * FROM table LIMIT 10 | Limit results |
SELECT * FROM table OFFSET 5 | Skip rows |
SELECT TOP 10 * FROM table | Top N rows (SQL Server) |
WHERE col = value | Equal to |
WHERE col <> value | Not equal to |
WHERE col > value | Greater than |
WHERE col >= value | Greater than or equal |
WHERE col < value | Less than |
WHERE col <= value | Less than or equal |
WHERE col BETWEEN a AND b | Between range |
WHERE col IN (a, b, c) | In list of values |
WHERE col NOT IN (a, b, c) | Not in list |
WHERE col IS NULL | Is null |
WHERE col IS NOT NULL | Is not null |
WHERE cond1 AND cond2 | Both conditions true |
WHERE cond1 OR cond2 | Either condition true |
WHERE NOT condition | Negate condition |
WHERE col LIKE 'a%' | Starts with a |
WHERE col LIKE '%a' | Ends with a |
WHERE col LIKE '%word%' | Contains word |
WHERE col LIKE '_a%' | Second char is a |
WHERE col LIKE '[abc]%' | Starts with a, b, or c |
WHERE col LIKE '[^abc]%' | Not starting with a, b, c |
WHERE col REGEXP 'pattern' | Regular expression (MySQL) |
ORDER BY col | Sort ascending (default) |
ORDER BY col ASC | Sort ascending |
ORDER BY col DESC | Sort descending |
ORDER BY col1, col2 | Sort by multiple columns |
ORDER BY col1 DESC, col2 ASC | Mixed sort order |
ORDER BY FIELD(col, a, b, c) | Custom sort order (MySQL) |
GROUP BY col | Group by column |
GROUP BY col1, col2 | Group by multiple columns |
HAVING COUNT(*) > 5 | Filter groups by count |
HAVING SUM(col) > 100 | Filter groups by sum |
GROUP BY col WITH ROLLUP | Include subtotals |
COUNT(*) | Count all rows |
COUNT(col) | Count non-null values |
COUNT(DISTINCT col) | Count unique values |
SUM(col) | Sum of values |
AVG(col) | Average of values |
MIN(col) | Minimum value |
MAX(col) | Maximum value |
GROUP_CONCAT(col) | Concatenate values (MySQL) |
STRING_AGG(col, ",") | Aggregate strings (PostgreSQL) |
ROW_NUMBER() OVER (ORDER BY col) | Row number |
RANK() OVER (ORDER BY col) | Rank with gaps |
DENSE_RANK() OVER (ORDER BY col) | Rank without gaps |
NTILE(4) OVER (ORDER BY col) | Divide into quartiles |
LAG(col, 1) OVER (ORDER BY date) | Previous row value |
LEAD(col, 1) OVER (ORDER BY date) | Next row value |
FIRST_VALUE(col) OVER (PARTITION BY grp) | First value in partition |
LAST_VALUE(col) OVER (PARTITION BY grp) | Last value in partition |
SUM(col) OVER (PARTITION BY grp) | Sum within partition |
SUM(col) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) | Running total |
SELECT * FROM a INNER JOIN b ON a.id = b.a_id | Inner join (matching rows) |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id | Left join (all from left) |
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id | Right join (all from right) |
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id | Full outer join (all from both) |
SELECT * FROM a CROSS JOIN b | Cross join (cartesian product) |
SELECT * FROM a, b WHERE a.id = b.a_id | Implicit join (old syntax) |
SELECT * FROM emp e1 JOIN emp e2 ON e1.mgr_id = e2.id | Self join |
SELECT * FROM a WHERE id IN (SELECT a_id FROM b) | Subquery in WHERE |
SELECT *, (SELECT COUNT(*) FROM b) AS cnt FROM a | Scalar subquery |
SELECT * FROM (SELECT * FROM a WHERE x > 5) AS sub | Derived table |
WITH cte AS (SELECT * FROM a) SELECT * FROM cte | Common Table Expression (CTE) |
WITH RECURSIVE cte AS (...) SELECT * FROM cte | Recursive CTE |
SELECT * FROM a UNION SELECT * FROM b | Combine unique rows |
SELECT * FROM a UNION ALL SELECT * FROM b | Combine all rows |
SELECT * FROM a INTERSECT SELECT * FROM b | Common rows only |
SELECT * FROM a EXCEPT SELECT * FROM b | Rows in a not in b |
INSERT INTO table (col1, col2) VALUES (v1, v2) | Insert single row |
INSERT INTO table VALUES (v1, v2, v3) | Insert all columns |
INSERT INTO table (cols) VALUES (v1), (v2), (v3) | Insert multiple rows |
INSERT INTO table (cols) SELECT cols FROM other | Insert from select |
INSERT IGNORE INTO table (cols) VALUES (vals) | Ignore duplicates (MySQL) |
INSERT INTO table (cols) VALUES (vals) ON DUPLICATE KEY UPDATE col = val | Upsert (MySQL) |
INSERT INTO table (cols) VALUES (vals) ON CONFLICT (col) DO UPDATE SET col = val | Upsert (PostgreSQL) |
UPDATE table SET col = value | Update all rows |
UPDATE table SET col = value WHERE condition | Update specific rows |
UPDATE table SET col1 = v1, col2 = v2 WHERE cond | Update multiple columns |
UPDATE table SET col = col + 1 WHERE cond | Increment value |
UPDATE a SET a.col = b.val FROM a JOIN b ON a.id = b.a_id | Update with join |
UPDATE table SET col = CASE WHEN cond THEN v1 ELSE v2 END | Conditional update |
DELETE FROM table | Delete all rows |
DELETE FROM table WHERE condition | Delete specific rows |
DELETE FROM a USING a JOIN b ON a.id = b.a_id | Delete with join |
TRUNCATE TABLE table | Delete all (faster, no log) |
DELETE TOP (100) FROM table | Delete limited rows (SQL Server) |
CREATE TABLE name (col1 TYPE, col2 TYPE) | Create table |
CREATE TABLE name (id INT PRIMARY KEY) | With primary key |
CREATE TABLE name (id INT AUTO_INCREMENT) | Auto increment (MySQL) |
CREATE TABLE name (id SERIAL) | Auto increment (PostgreSQL) |
CREATE TABLE name (col TYPE NOT NULL) | Not null constraint |
CREATE TABLE name (col TYPE DEFAULT value) | Default value |
CREATE TABLE name (col TYPE UNIQUE) | Unique constraint |
CREATE TABLE name (FOREIGN KEY (col) REFERENCES other(id)) | Foreign key |
CREATE TABLE name (CHECK (col > 0)) | Check constraint |
CREATE TABLE IF NOT EXISTS name (...) | Create if not exists |
CREATE TABLE new AS SELECT * FROM old | Create from select |
ALTER TABLE t ADD col TYPE | Add column |
ALTER TABLE t DROP COLUMN col | Remove column |
ALTER TABLE t RENAME COLUMN old TO new | Rename column |
ALTER TABLE t MODIFY col NEW_TYPE | Change column type (MySQL) |
ALTER TABLE t ALTER COLUMN col TYPE NEW_TYPE | Change column type (PostgreSQL) |
ALTER TABLE t ADD PRIMARY KEY (col) | Add primary key |
ALTER TABLE t ADD CONSTRAINT name UNIQUE (col) | Add unique constraint |
ALTER TABLE t DROP CONSTRAINT name | Drop constraint |
ALTER TABLE old RENAME TO new | Rename table |
DROP TABLE table | Drop table |
DROP TABLE IF EXISTS table | Drop if exists |
DROP DATABASE db | Drop database |
CREATE DATABASE db | Create database |
CREATE SCHEMA schema | Create schema |
CREATE INDEX idx ON table (col) | Create index |
CREATE UNIQUE INDEX idx ON table (col) | Create unique index |
DROP INDEX idx | Drop index |
INT / INTEGER | Integer (-2B to 2B) |
BIGINT | Large integer |
SMALLINT | Small integer (-32K to 32K) |
TINYINT | Tiny integer (0 to 255) |
DECIMAL(p, s) / NUMERIC(p, s) | Exact decimal (precision, scale) |
FLOAT / REAL | Floating point |
DOUBLE | Double precision |
BOOLEAN / BOOL | True/False |
CHAR(n) | Fixed-length string |
VARCHAR(n) | Variable-length string |
TEXT | Long text |
DATE | Date (YYYY-MM-DD) |
TIME | Time (HH:MM:SS) |
DATETIME / TIMESTAMP | Date and time |
BLOB / BYTEA | Binary data |
JSON / JSONB | JSON data |
UUID | Universally unique identifier |
CONCAT(s1, s2) | Concatenate strings |
UPPER(str) / LOWER(str) | Change case |
LENGTH(str) / LEN(str) | String length |
TRIM(str) | Remove whitespace |
LTRIM(str) / RTRIM(str) | Trim left/right |
SUBSTRING(str, start, len) | Extract substring |
REPLACE(str, old, new) | Replace substring |
LEFT(str, n) / RIGHT(str, n) | Left/right characters |
CHARINDEX(sub, str) | Find position (SQL Server) |
POSITION(sub IN str) | Find position (PostgreSQL) |
COALESCE(a, b, c) | First non-null value |
NULLIF(a, b) | Return NULL if a = b |
NOW() / CURRENT_TIMESTAMP | Current date and time |
CURRENT_DATE / CURDATE() | Current date |
CURRENT_TIME / CURTIME() | Current time |
DATE_ADD(date, INTERVAL 1 DAY) | Add to date (MySQL) |
date + INTERVAL '1 day' | Add to date (PostgreSQL) |
DATEDIFF(d1, d2) | Difference in days |
EXTRACT(YEAR FROM date) | Extract date part |
YEAR(date) / MONTH(date) / DAY(date) | Extract parts (MySQL) |
DATE_FORMAT(date, '%Y-%m-%d') | Format date (MySQL) |
TO_CHAR(date, 'YYYY-MM-DD') | Format date (PostgreSQL) |
CASE WHEN cond THEN val ELSE val END | Case expression |
IF(cond, true_val, false_val) | If function (MySQL) |
IIF(cond, true_val, false_val) | If function (SQL Server) |
IFNULL(val, default) | Default if null (MySQL) |
ISNULL(val, default) | Default if null (SQL Server) |
NVL(val, default) | Default if null (Oracle) |
CAST(val AS type) | Type conversion |
CONVERT(type, val) | Type conversion (SQL Server) |
BEGIN / START TRANSACTION | Start transaction |
COMMIT | Commit changes |
ROLLBACK | Rollback changes |
SAVEPOINT name | Create savepoint |
ROLLBACK TO name | Rollback to savepoint |
SET TRANSACTION ISOLATION LEVEL ... | Set isolation level |
CREATE VIEW name AS SELECT ... | Create view |
CREATE OR REPLACE VIEW name AS ... | Create or replace view |
DROP VIEW name | Drop view |
CREATE PROCEDURE name() BEGIN ... END | Create procedure (MySQL) |
CREATE FUNCTION name() RETURNS type AS ... | Create function |
CALL procedure_name() | Call procedure |
EXEC procedure_name | Execute procedure (SQL Server) |
GRANT SELECT ON table TO user | Grant permission |
GRANT ALL ON database.* TO user | Grant all permissions |
REVOKE SELECT ON table FROM user | Revoke permission |
CREATE USER 'user'@'host' IDENTIFIED BY 'pass' | Create user (MySQL) |
CREATE USER user WITH PASSWORD pass | Create user (PostgreSQL) |
DROP USER user | Drop user |
-- Find duplicates
SELECT col, COUNT(*) as cnt
FROM table GROUP BY col HAVING COUNT(*) > 1;
-- Running total with window function
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;
-- Pivot table (MySQL)
SELECT
category,
SUM(CASE WHEN year = 2023 THEN sales END) as '2023',
SUM(CASE WHEN year = 2024 THEN sales END) as '2024'
FROM data GROUP BY category;
-- Recursive CTE for hierarchy
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 as level
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.level + 1
FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;