SELECT col1, col2 FROM table;
SELECT name, email FROM users;
ℹ Use * to select all columns (avoid in production)
SELECT col AS alias FROM table;
SELECT first_name AS name, created_at AS joined FROM users;
SELECT DISTINCT col FROM table;
SELECT DISTINCT country FROM customers;
SELECT ... FROM table WHERE condition;
SELECT * FROM orders WHERE status = 'pending' AND total > 100;
SELECT ... ORDER BY col ASC|DESC;
SELECT name, price FROM products ORDER BY price DESC;
SELECT ... LIMIT n OFFSET m;
SELECT * FROM posts LIMIT 20 OFFSET 40;
ℹ SQL Server: use TOP n or FETCH NEXT
WHERE col LIKE 'pattern'
SELECT * FROM users WHERE email LIKE '%@gmail.com';
WHERE col IN (v1, v2, ...)
SELECT * FROM orders WHERE status IN ('pending', 'processing');WHERE col BETWEEN v1 AND v2
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
WHERE col IS NULL | IS NOT NULL
SELECT * FROM users WHERE deleted_at IS NULL;
CASE WHEN cond THEN val ... ELSE val END
SELECT name, CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS type FROM users;
SELECT COUNT(*) | COUNT(col) FROM table;
SELECT COUNT(*) AS total, COUNT(email) AS with_email FROM users;
SELECT SUM(col), AVG(col), MIN(col), MAX(col) FROM table;
SELECT SUM(total) AS revenue, AVG(total) AS avg_order FROM orders;
SELECT col, AGG(x) FROM table GROUP BY col;
SELECT country, COUNT(*) AS users FROM users GROUP BY country;
... GROUP BY col HAVING AGG(x) > n
SELECT country, COUNT(*) as cnt FROM users GROUP BY country HAVING COUNT(*) > 100;
... GROUP BY col ORDER BY AGG(x) DESC
SELECT status, COUNT(*) cnt FROM orders GROUP BY status ORDER BY cnt DESC;
SELECT ... FROM a INNER JOIN b ON a.id = b.a_id;
SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;
SELECT ... FROM a LEFT JOIN b ON a.id = b.a_id;
SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;
SELECT ... FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT o.id, u.name FROM orders o RIGHT JOIN users u ON o.user_id = u.id;
SELECT ... FROM a FULL OUTER JOIN b ON condition;
SELECT a.id, b.id FROM table_a a FULL OUTER JOIN table_b b ON a.key = b.key;
ℹ Not supported in MySQL 8 — use UNION of LEFT + RIGHT JOIN
SELECT ... FROM t AS a JOIN t AS b ON a.col = b.col;
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
SELECT ... FROM a CROSS JOIN b;
SELECT colors.name, sizes.label FROM colors CROSS JOIN sizes;
ℹ Returns Cartesian product — rows(a) × rows(b)
WHERE id IN (SELECT id FROM ...)
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);
SELECT (SELECT COUNT(*) FROM ...) AS n
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;
WHERE EXISTS (SELECT 1 FROM ...)
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
WITH cte AS (SELECT ...) SELECT * FROM cte;
WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days') SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;
WITH RECURSIVE cte AS (base UNION ALL recursive) ...
WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 10) SELECT n FROM nums;
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
SELECT name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank FROM employees;
RANK() OVER (ORDER BY col DESC)
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS pay_rank FROM employees;
ℹ RANK skips numbers on ties; DENSE_RANK does not
LAG(col, n) OVER (ORDER BY ...)
SELECT date, revenue, LAG(revenue,1) OVER (ORDER BY date) AS prev_revenue FROM daily_sales;
SUM(col) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING)
SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions;
NTILE(n) OVER (ORDER BY col)
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;
INSERT INTO table (col1, col2) VALUES (v1, v2);
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');INSERT INTO table (cols) VALUES (r1), (r2), ...;
INSERT INTO tags (name) VALUES ('sql'), ('database'), ('tutorial');INSERT INTO t1 (cols) SELECT cols FROM t2;
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2023-01-01';
UPDATE table SET col = val WHERE condition;
UPDATE users SET status = 'inactive', updated_at = NOW() WHERE last_login < NOW() - INTERVAL '90 days';
DELETE FROM table WHERE condition;
DELETE FROM sessions WHERE expires_at < NOW();
ℹ Always include WHERE — omitting it deletes all rows
INSERT … ON CONFLICT (col) DO UPDATE SET …
INSERT INTO settings (key, value) VALUES ('theme', 'dark') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;ℹ PostgreSQL syntax. MySQL uses ON DUPLICATE KEY UPDATE.
TRUNCATE TABLE table;
TRUNCATE TABLE temp_imports;
ℹ Faster than DELETE; resets auto-increment; cannot be rolled back in some DBs
CREATE TABLE name (col type constraints, ...);
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());
ALTER TABLE t ADD COLUMN col type;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE users RENAME COLUMN username TO display_name;
CREATE INDEX name ON table (col);
CREATE INDEX idx_users_email ON users (email); CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
DROP TABLE [IF EXISTS] table;
DROP TABLE IF EXISTS temp_staging;
REFERENCES other_table(col) ON DELETE action
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE);
Это логический порядок обработки. WHERE выполняется до SELECT, поэтому нельзя ссылаться на псевдоним SELECT в Клаузула WHERE — используйте вместо неё CTE или подзапрос. HAVING может фильтровать агрегатах GROUP BY.
| Выражение | Результат | Примечания |
|---|---|---|
| NULL = NULL | FALSE / UNKNOWN | Use IS NULL instead |
| NULL != NULL | UNKNOWN | Any comparison with NULL is UNKNOWN |
| NULL + 5 | NULL | Arithmetic on NULL propagates NULL |
| COALESCE(NULL, 'x') | 'x' | Returns first non-NULL value |
| NULLIF(a, a) | NULL | Returns NULL if both args are equal |
| COUNT(*) | Counts all rows | COUNT(col) skips NULLs |
SQL-клаузулы выполняются в следующем логическом порядке: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. Это важно, потому что WHERE не может ссылаться на псевдонимы SELECT, но HAVING может ссылаться на столбцы GROUP BY.
INNER JOIN возвращает только строки, в которых условие соединения выполняется в обеих таблицах. LEFT JOIN (LEFT OUTER JOIN) возвращает все строки из левой таблицы с NULL для не совпадающих столбцов правой таблицы. Используйте LEFT JOIN, когда вам нужны все записи из левой таблицы независимо от наличия совпадений справа.
Оконные функции вычисляют значения для набора строк, связанных с текущей строкой, без свёртывания их в одну строку (в отличие от агрегатов GROUP BY). Распространённые оконные функции: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(). Они используют клаузулу OVER() для определения окна.
CTE — это именованный временный набор результатов, определённый с ключевым словом WITH перед SELECT, INSERT, UPDATE или DELETE. CTE улучшают читаемость по сравнению с вложенными подзапросами и могут быть рекурсивными (WITH RECURSIVE) для иерархических данных, таких как организационные диаграммы или деревья категорий.