SELECT в MySQL. Введение
Оператор SELECT в MySQL — ключевой инструмент для чтения данных. С его помощью можно фильтровать строки (WHERE), сортировать (ORDER BY), группировать (GROUP BY), агрегировать данные и строить отчёты. В этом материале — полный синтаксис и 100 практических примеров от базовых до продвинутых.
Синтаксис
SELECT [DISTINCT | ALL]
список_столбцов
FROM
таблица_или_выражение
[WHERE условие]
[GROUP BY столбцы]
[HAVING условие_для_групп]
[ORDER BY столбцы [ASC|DESC]]
[LIMIT число [OFFSET число]];
Типовая конструкция
SELECT id, name, email
FROM users;
100 примеров SELECT
1. Выбор всех столбцов
SELECT * FROM users;
2. Выбор конкретных столбцов
SELECT name, email FROM users;
3. Псевдонимы столбцов
SELECT name AS user_name, email AS user_email FROM users;
4. Условие равенства
SELECT * FROM users WHERE active = 1;
5. Сравнение и диапазон
SELECT * FROM orders WHERE total >= 100 AND total <= 500;
6. IN-список
SELECT * FROM users WHERE country IN ('DE','NL','FR');
7. Поиск NULL
SELECT * FROM users WHERE deleted_at IS NULL;
8. LIKE и подстроки
SELECT * FROM products WHERE name LIKE '%USB%';
9. Регистрозависимый поиск (BINARY)
SELECT * FROM users WHERE BINARY username = 'Admin';
10. LIMIT и OFFSET
SELECT id, name FROM users ORDER BY id LIMIT 10 OFFSET 20;
11. Сортировка по нескольким полям
SELECT * FROM users ORDER BY active DESC, created_at ASC;
12. Уникальные значения
SELECT DISTINCT country FROM users;
13. Конкатенация
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
14. Подсчёт строк
SELECT COUNT(*) AS cnt FROM users;
15. Минимум/максимум
SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;
16. Среднее и сумма
SELECT AVG(total) AS avg_total, SUM(total) AS sum_total FROM orders;
17. Округление
SELECT ROUND(AVG(rating), 2) AS avg_rating FROM reviews;
18. Дата/время (YEAR, MONTH)
SELECT YEAR(created_at) AS y, MONTH(created_at) AS m, COUNT(*) FROM users GROUP BY y, m;
19. CASE выражение
SELECT id, CASE WHEN total >= 100 THEN 'big' ELSE 'small' END AS bucket FROM orders;
20. IF выражение
SELECT id, IF(active=1,'yes','no') AS is_active FROM users;
21. INNER JOIN
SELECT u.name, o.id, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;
22. LEFT JOIN
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id;
23. RIGHT JOIN
SELECT o.id, u.name FROM orders o RIGHT JOIN users u ON o.user_id = u.id;
24. JOIN с несколькими таблицами
SELECT u.name, o.id, p.method FROM users u JOIN orders o ON o.user_id=u.id JOIN payments p ON p.order_id=o.id;
25. SELF JOIN
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
26. CROSS JOIN
SELECT a.locale, b.currency FROM locales a CROSS JOIN currencies b;
27. USING в JOIN
SELECT * FROM products p JOIN categories c USING(category_id);
28. Агрегация после JOIN
SELECT u.id, COUNT(o.id) AS orders FROM users u LEFT JOIN orders o ON o.user_id=u.id GROUP BY u.id;
29. HAVING после GROUP BY
SELECT user_id, SUM(total) s FROM orders GROUP BY user_id HAVING s > 1000;
30. JOIN и фильтр по связанной таблице
SELECT u.* FROM users u JOIN orders o ON o.user_id=u.id WHERE o.status='paid';
31. JOIN с подзапросом
SELECT u.* FROM users u JOIN (SELECT user_id, MAX(created_at) mx FROM orders GROUP BY user_id) t ON t.user_id=u.id;
32. ANTI-JOIN через NOT EXISTS
SELECT u.* FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id);
33. SEMI-JOIN через EXISTS
SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.total>=500);
34. RIGHT JOIN + агрегаты
SELECT c.name, COUNT(p.id) FROM categories c RIGHT JOIN products p ON p.category_id=c.id GROUP BY c.name;
35. FULL OUTER JOIN (эмуляция UNION)
SELECT * FROM a LEFT JOIN b ON a.id=b.id UNION SELECT * FROM a RIGHT JOIN b ON a.id=b.id;
36. Подзапрос в WHERE (IN)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active=1);
37. Подзапрос в FROM (derived table)
SELECT d.user_id, d.cnt FROM (SELECT user_id, COUNT(*) cnt FROM orders GROUP BY user_id) d;
38. Скалярный подзапрос
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id=u.id) AS orders_cnt FROM users u;
39. Коррелированный подзапрос
SELECT u.* FROM users u WHERE u.created_at = (SELECT MAX(created_at) FROM users u2 WHERE u2.country=u.country);
40. CTE: WITH для предочистки
WITH recent AS (SELECT * FROM orders WHERE created_at >= CURDATE() - INTERVAL 30 DAY) SELECT * FROM recent;
41. CTE + агрегация
WITH sales AS (SELECT user_id, SUM(total) s FROM orders GROUP BY user_id) SELECT * FROM sales WHERE s >= 1000;
42. Рекурсивный CTE (иерархия)
WITH RECURSIVE t AS (SELECT id, parent_id, name, 0 AS lvl FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, t.lvl+1 FROM categories c JOIN t ON c.parent_id=t.id) SELECT * FROM t;
43. Подзапрос в SELECT
SELECT id, (SELECT name FROM users WHERE id=o.user_id) AS user_name FROM orders o;
44. Подзапрос и EXISTS
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.total>100);
45. ANY/SOME сравнение
SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id=10);
46. ALL сравнение
SELECT * FROM products WHERE price < ALL (SELECT price FROM products WHERE category_id=10);
47. LIMIT в подзапросе
SELECT * FROM (SELECT * FROM orders ORDER BY created_at DESC LIMIT 100) t ORDER BY t.created_at;
48. EXCEPT эмуляция (NOT IN)
SELECT id FROM a WHERE id NOT IN (SELECT id FROM b);
49. INTERSECT эмуляция (INNER JOIN)
SELECT a.id FROM a JOIN b ON a.id=b.id;
50. Подзапрос + GROUP BY + HAVING
SELECT user_id FROM orders GROUP BY user_id HAVING SUM(total) > (SELECT AVG(total) FROM orders);
51. ROW_NUMBER()
SELECT id, ROW_NUMBER() OVER(ORDER BY created_at) AS rn FROM orders;
52. RANK() и DENSE_RANK()
SELECT user_id, total, RANK() OVER(ORDER BY total DESC) rnk, DENSE_RANK() OVER(ORDER BY total DESC) dr FROM orders;
53. LAG/LEAD
SELECT id, total, LAG(total) OVER(ORDER BY created_at) AS prev_total, LEAD(total) OVER(ORDER BY created_at) AS next_total FROM orders;
54. FIRST_VALUE/LAST_VALUE
SELECT id, FIRST_VALUE(total) OVER(ORDER BY created_at) AS first_t, LAST_VALUE(total) OVER(ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_t FROM orders;
55. Окно по PARTITION BY
SELECT user_id, COUNT(*) OVER(PARTITION BY user_id) AS cnt_by_user FROM orders;
56. Скользящая сумма
SELECT id, created_at, SUM(total) OVER(ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum FROM orders;
57. Процентили NTILE
SELECT id, NTILE(4) OVER(ORDER BY total DESC) AS quart FROM orders;
58. PERCENT_RANK и CUME_DIST
SELECT id, PERCENT_RANK() OVER(ORDER BY total) pr, CUME_DIST() OVER(ORDER BY total) cd FROM orders;
59. WINDOW с именем
SELECT id, SUM(total) OVER w AS s FROM orders WINDOW w AS (PARTITION BY user_id ORDER BY created_at);
60. Окно без ORDER BY
SELECT user_id, SUM(total) OVER(PARTITION BY user_id) AS s FROM orders;
61. Окно с RANGE
SELECT id, SUM(total) OVER(ORDER BY created_at RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) AS week_sum FROM orders;
62. Сравнение строки с медианой (персентиль)
SELECT * FROM (SELECT o.*, PERCENT_RANK() OVER(ORDER BY total) pr FROM orders o) t WHERE pr >= 0.5;
63. Rolling average
SELECT id, AVG(total) OVER(ORDER BY created_at ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS rolling_avg FROM orders;
64. Top-N per group
SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY total DESC) rn FROM orders o) t WHERE rn <= 3;
65. Running distinct count (эмуляция)
SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days FROM orders GROUP BY user_id;
66. Окна + фильтры (QUALIFY-эмуляция)
SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) rn FROM orders o) t WHERE rn=1;
67. Сравнение со средним по группе
SELECT o.*, AVG(total) OVER(PARTITION BY user_id) avg_by_user FROM orders o;
68. Ранжирование внутри категории
SELECT p.*, RANK() OVER(PARTITION BY category_id ORDER BY price DESC) rnk FROM products p;
69. Кумулятивная сумма по пользователю
SELECT user_id, created_at, SUM(total) OVER(PARTITION BY user_id ORDER BY created_at) AS cum_sum FROM orders;
70. Skewness через оконные функции (эмпирика)
SELECT AVG(total) - MEDIAN(total) FROM orders; -- при наличии пользовательской MEDIAN
71. JSON_EXTRACT
SELECT JSON_EXTRACT(metadata, '$.ip') AS ip FROM logins;
72. ->> (короткая форма)
SELECT metadata->>'$.ip' AS ip FROM logins;
73. JSON_TABLE
SELECT * FROM JSON_TABLE(order_json, '$.items[*]' COLUMNS(product_id INT PATH '$.id', qty INT PATH '$.qty')) AS jt;
74. Поиск в JSON
SELECT * FROM orders WHERE JSON_CONTAINS_PATH(order_json, 'one', '$.coupon');
75. Работа с датой (DATEDIFF)
SELECT id, DATEDIFF(NOW(), created_at) AS days_ago FROM users;
76. INTERVAL выражение
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) AS next_week FROM orders;
77. Строковые функции
SELECT TRIM(LOWER(email)) AS norm_email FROM users;
78. REGEXP
SELECT * FROM users WHERE email REGEXP '^[a-z0-9._%+-]+@example\\.com$';
79. FULLTEXT поиск
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('+mysql +select' IN BOOLEAN MODE);
80. Гео-расстояние (Haversine, упрощённо)
SELECT id, (6371*ACOS(COS(RADIANS(:lat))*COS(RADIANS(lat))*COS(RADIANS(lng)-RADIANS(:lng))+SIN(RADIANS(:lat))*SIN(RADIANS(lat)))) AS km FROM places ORDER BY km;
81. CONVERT / CAST
SELECT CAST(price AS DECIMAL(10,2)) AS p FROM products;
82. COLLATE
SELECT * FROM users ORDER BY name COLLATE utf8mb4_unicode_ci;
83. FORMAT чисел
SELECT FORMAT(SUM(total), 2, 'de_DE') FROM orders;
84. REPLACE/REGEXP_REPLACE
SELECT REGEXP_REPLACE(phone, '[^0-9]+', '') AS digits FROM customers;
85. UUID
SELECT UUID() AS id;
86. EXPLAIN плана запроса
EXPLAIN SELECT * FROM orders WHERE user_id=1;
87. EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=1;
88. STRAIGHT_JOIN подсказка
SELECT STRAIGHT_JOIN * FROM a JOIN b ON a.id=b.id;
89. FORCE INDEX
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id=1;
90. USE INDEX / IGNORE INDEX
SELECT * FROM orders USE INDEX (idx_status) WHERE status='paid';
91. HIGH_PRIORITY (MyISAM; с осторожностью)
SELECT HIGH_PRIORITY * FROM legacy_table;
92. SQL_CALC_FOUND_ROWS (устар.)
SELECT SQL_CALC_FOUND_ROWS * FROM big_table LIMIT 10;
93. LOCK IN SHARE MODE (устар. синтаксис)
SELECT * FROM orders WHERE id=1 LOCK IN SHARE MODE;
94. FOR UPDATE (в транзакции)
SELECT * FROM orders WHERE id=1 FOR UPDATE;
95. READ UNCOMMITTED (se session)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT COUNT(*) FROM orders;
96. READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT COUNT(*) FROM orders;
97. REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT COUNT(*) FROM orders;
98. SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT COUNT(*) FROM orders;
99. Временные таблицы в SELECT … INTO
CREATE TEMPORARY TABLE t AS SELECT * FROM orders WHERE created_at > NOW()-INTERVAL 1 DAY;
100. SELECT в VIEW
CREATE OR REPLACE VIEW recent_orders AS SELECT * FROM orders WHERE created_at > NOW()-INTERVAL 7 DAY;
Заключение
Теперь у вас есть быстрые шпаргалки по 100 частым случаям использования SELECT в MySQL 8.0. Сохраняйте и используйте в проектах. Для углубления читайте официальную документацию MySQL.
Следующая статья: