Запросы MySQL: SELECT, INSERT, UPDATE, DELETE, JOIN с примерами

Запросы MySQL помогают выбирать, связывать и менять строки в таблицах. Создайте учебную базу из двух таблиц и повторите примеры по порядку. Вы научитесь фильтровать данные, считать итоги, соединять таблицы, менять строки через транзакцию, читать план EXPLAIN и выполнять параметризованный запрос из PHP.

Как устроен запрос в MySQL и где его выполнять

MySQL хранит таблицы на сервере и принимает SQL-команды от клиента.

Для упражнений используйте отдельную базу. Так тестовые INSERT, UPDATE и DELETE не затронут рабочие данные.

Команды можно вводить в mysql-клиенте, MySQL Workbench или phpMyAdmin. Сервер скачайте со страницы MySQL Community Server. После подключения завершайте каждую команду точкой с запятой.

mysql -h 127.0.0.1 -u your_user -p

Учебная база из покупателей и заказов

Таблица customers хранит покупателей. Таблица orders связывает заказ с покупателем через customer_id.

CREATE DATABASE sql_practice CHARACTER SET utf8mb4;
USE sql_practice;

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    city VARCHAR(100)
) ENGINE = InnoDB;

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    ordered_at DATETIME NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE = InnoDB;

INSERT INTO customers (id, name, email, city) VALUES
    (1, 'Anna', 'anna@example.test', 'Moscow'),
    (2, 'Boris', 'boris@example.test', 'Moscow'),
    (3, 'Vera', 'vera@example.test', 'Kazan'),
    (4, 'Gleb', NULL, NULL);

INSERT INTO orders (id, customer_id, amount, status, ordered_at) VALUES
    (101, 1, 1200.00, 'paid', CURRENT_DATE - INTERVAL 3 DAY),
    (102, 1, 800.00, 'new', CURRENT_DATE - INTERVAL 2 DAY),
    (103, 2, 2000.00, 'paid', CURRENT_DATE - INTERVAL 1 DAY),
    (104, 3, 500.00, 'cancelled', CURRENT_DATE),
    (105, 3, 1500.00, 'paid', CURRENT_DATE);

SELECT COUNT(*) AS customers_count FROM customers;
SELECT COUNT(*) AS orders_count FROM orders;

customers_count
4

orders_count
5

Anna и Vera получили по 2 заказа, Boris – один. У Gleb заказов нет. Эта строка пригодится при сравнении INNER JOIN и LEFT JOIN. О создании баз и таблиц читайте в материале о создании баз данных и таблиц SQL.

SELECT в MySQL выбирает и сортирует строки

Команда SELECT возвращает указанные столбцы из подходящих строк. WHERE отбирает строки, ORDER BY задает порядок, а LIMIT ограничивает страницу результата.

Столбцы, WHERE и операторы сравнения

После SELECT укажите столбцы, после FROM – таблицу, после WHERE – условие.

Строковый литерал 'Anna' заключают в одинарные кавычки. Числовые идентификаторы пишите без кавычек.

SELECT id, name, city
FROM customers
WHERE name = 'Anna';

id | name | city
1  | Anna | Moscow

Операторы сравнения работают с числами, датами и строками. Скобки помогают явно задать порядок AND и OR.

SELECT id, amount, status
FROM orders
WHERE amount >= 1000 AND status <> 'cancelled'
ORDER BY id;

id  | amount  | status
101 | 1200.00 | paid
103 | 2000.00 | paid
105 | 1500.00 | paid

LIKE, IN, BETWEEN и IS NULL

В LIKE символ % заменяет любое число символов. Символ _ заменяет ровно один символ. IN сравнивает значение со списком, а BETWEEN включает обе границы.

SELECT id, name FROM customers WHERE name LIKE 'An%';
SELECT id, name FROM customers WHERE name LIKE '_era';

SELECT id, name
FROM customers
WHERE id IN (1, 3)
ORDER BY id;

SELECT id, amount
FROM orders
WHERE amount BETWEEN 800 AND 1500
ORDER BY id;

SELECT id, name
FROM customers
WHERE email IS NULL;

LIKE 'An%'       → Anna
LIKE '_era'      → Vera
IN (1, 3)        → Anna, Vera
BETWEEN 800–1500 → 101, 102, 105
IS NULL          → Gleb

NULL означает отсутствие значения. Условие email = NULL не найдет пустые значения. Используйте IS NULL или IS NOT NULL.

SELECT id, amount
FROM orders
WHERE ordered_at >= CURRENT_DATE
  AND ordered_at < CURRENT_DATE + INTERVAL 1 DAY
ORDER BY id;

Запрос возвращает заказы 104 и 105 за текущий день заполнения базы. Другие способы фильтрации дат описаны в статье о работе с датой и временем в MySQL.

DISTINCT, ORDER BY и страницы

DISTINCT оставляет уникальные сочетания выбранных столбцов. ASC сортирует по возрастанию, DESC – по убыванию. Без ORDER BY сервер не обещает постоянный порядок строк.

SELECT DISTINCT city
FROM customers
WHERE city IS NOT NULL
ORDER BY city ASC;

city
Kazan
Moscow

Добавьте второй ключ сортировки, когда значения могут совпадать. Уникальный идентификатор делает страницы стабильнее.

SELECT id, amount
FROM orders
ORDER BY amount DESC, id ASC
LIMIT 3 OFFSET 0;

Запись LIMIT 2, 3 сначала пропускает 2 строки, затем возвращает 3 строки – с третьей по пятую.

SELECT id, amount
FROM orders
ORDER BY id
LIMIT 2, 3;

id  | amount
103 | 2000.00
104 | 500.00
105 | 1500.00

Для страницы из 3 строк используйте смещение (номер страницы - 1) * 3. DISTINCT подробно разобран в статье о выборке уникальных значений.

Агрегаты и группировка строк

Агрегатная функция превращает несколько строк в одно значение. GROUP BY делит строки на группы, а HAVING фильтрует уже сформированные группы.

COUNT, SUM, AVG, MIN и MAX

COUNT(*) считает строки. COUNT(email) пропускает NULL. SUM, AVG, MIN и MAX работают со значениями столбца и также пропускают NULL.

SELECT COUNT(*) AS customers_count,
       COUNT(email) AS emails_count
FROM customers;

SELECT COUNT(*) AS orders_count,
       SUM(amount) AS total,
       ROUND(AVG(amount), 2) AS average_amount,
       MIN(amount) AS min_amount,
       MAX(amount) AS max_amount
FROM orders;

customers_count | emails_count
4               | 3

orders_count | total   | average_amount | min_amount | max_amount
5            | 6000.00 | 1200.00        | 500.00     | 2000.00

GROUP BY, WHERE и HAVING

WHERE отбрасывает исходные строки до группировки.

HAVING проверяет готовые группы, поэтому условие может содержать COUNT или SUM.

SELECT customer_id,
       COUNT(*) AS orders_count,
       SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

customer_id | orders_count | total
1           | 2            | 2000.00
3           | 2            | 2000.00

Сначала отфильтруйте оплаченные заказы через WHERE. Затем оставьте группы с нужной суммой через HAVING.

SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 1500
ORDER BY customer_id;

customer_id | paid_total
2           | 2000.00
3           | 1500.00

ONLY_FULL_GROUP_BY и ошибка группировки

В SELECT каждый столбец при группировке должен входить в GROUP BY, участвовать в агрегате или однозначно зависеть от ключа группы.

Сначала посмотрите список SQL-режимов текущего соединения.

SELECT @@SESSION.sql_mode;

Найдите ONLY_FULL_GROUP_BY среди названий, разделенных запятыми. Если название присутствует, режим включен для текущего соединения. Сам SELECT только читает настройку и не меняет ее.

Следующий запрос намеренно нарушает правило группировки.

SELECT customer_id, status, SUM(amount)
FROM orders
GROUP BY customer_id;

При включенном ONLY_FULL_GROUP_BY сервер отклонит запрос с ошибкой 1055. Пример сообщения об ошибке для этой учебной схемы выглядит так:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_practice.orders.status'; this is incompatible with sql_mode=only_full_group_by

Если режим выключен, запрос выполнится, но выбор status внутри группы будет неопределенным. У customer_id = 1 есть статусы paid и new. Добавьте status в GROUP BY, если отчет должен разделять статусы, или уберите status из SELECT.

JOIN в MySQL связывает таблицы

JOIN связывает строки по условию ON. INNER JOIN оставляет совпадения, LEFT JOIN сохраняет все строки слева, а RIGHT JOIN – все строки справа.

INNER JOIN возвращает совпадения

SELECT c.id AS customer_id, c.name, o.id AS order_id
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

customer_id | name  | order_id
1           | Anna  | 101
1           | Anna  | 102
2           | Boris | 103
3           | Vera  | 104
3           | Vera  | 105

Gleb исчез из результата, потому что в orders нет заказа с customer_id = 4.

LEFT JOIN сохраняет строки слева

SELECT c.id AS customer_id, c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

customer_id | name  | order_id
1           | Anna  | 101
1           | Anna  | 102
2           | Boris | 103
3           | Vera  | 104
3           | Vera  | 105
4           | Gleb  | NULL

Условие правой таблицы ставьте в ON, если хотите сохранить покупателей без подходящих заказов.

SELECT c.id, c.name, o.id AS paid_order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;

Условие o.status = 'paid' в WHERE уберет строки без оплаченного заказа. Примеры поиска таких строк есть в статье про оператор MySQL LEFT JOIN.

RIGHT JOIN и соединение через запятую

RIGHT JOIN сохраняет строки правой таблицы. В следующем запросе справа стоят customers, поэтому результат совпадает с LEFT JOIN.

SELECT c.id AS customer_id, c.name, o.id AS order_id
FROM orders AS o
RIGHT JOIN customers AS c ON o.customer_id = c.id
ORDER BY c.id, o.id;

Запись через запятую с условием WHERE выражает внутреннее соединение.

Явный JOIN отделяет связь таблиц от фильтра.

SELECT c.name, o.id AS order_id
FROM customers AS c, orders AS o
WHERE c.id = o.customer_id
ORDER BY c.id, o.id;

Без условия связи 4 покупателя и 5 заказов дадут 20 сочетаний. Такой результат называют декартовым произведением.

Подзапросы решают вложенные задачи

Подзапрос – это SELECT внутри другого запроса. Он может вернуть одно значение, список, набор строк или производную таблицу.

IN и EXISTS в WHERE

IN проверяет вхождение значения в набор из подзапроса. EXISTS проверяет факт существования строки и не размножает покупателей.

SELECT id, name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE amount >= 1500
)
ORDER BY id;

id | name
2  | Boris
3  | Vera

SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id AND o.amount >= 1500
)
ORDER BY c.id;

Оба запроса возвращают Boris и Vera. Число 1 в EXISTS не попадает в результат.

Коррелированный подзапрос в SELECT

Коррелированный подзапрос обращается к столбцу текущей строки внешнего запроса.

Логически он получает значение для каждого покупателя, хотя оптимизатор может изменить физический план.

SELECT c.id, c.name,
       (
           SELECT COUNT(*)
           FROM orders AS o
           WHERE o.customer_id = c.id
       ) AS orders_count
FROM customers AS c
ORDER BY c.id;

id | name  | orders_count
1  | Anna  | 2
2  | Boris | 1
3  | Vera  | 2
4  | Gleb  | 0

Скалярный подзапрос должен вернуть максимум одну строку. COUNT без GROUP BY возвращает одну строку даже при отсутствии заказов.

Производная таблица в FROM

Подзапрос в FROM создает набор строк для внешнего запроса. Алиас totals дает доступ к этому набору.

SELECT c.name, totals.total
FROM customers AS c
INNER JOIN (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) AS totals ON totals.customer_id = c.id
ORDER BY c.id;

name  | total
Anna  | 2000.00
Boris | 2000.00
Vera  | 2000.00

JOIN удобнее, когда нужно получить несколько полей или сравнить планы выполнения. Для списка всех покупателей используйте LEFT JOIN и COUNT(o.id).

SELECT c.id, c.name, COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

INSERT, UPDATE и DELETE меняют данные безопасно

INSERT добавляет строки, UPDATE меняет их, DELETE удаляет. Перед изменением выполните SELECT с тем же WHERE. Учебные действия проводите в транзакции InnoDB.

Одна и несколько строк через INSERT

START TRANSACTION;

INSERT INTO customers (id, name, email, city)
VALUES (5, 'Dina', 'dina@example.test', 'Perm');

INSERT INTO customers (id, name, email, city) VALUES
    (6, 'Egor', 'egor@example.test', 'Omsk'),
    (7, 'Irina', 'irina@example.test', 'Tula');

SELECT id, name
FROM customers
WHERE id IN (5, 6, 7)
ORDER BY id;

ROLLBACK;

id | name
5  | Dina
6  | Egor
7  | Irina

После ROLLBACK строки 5, 6 и 7 исчезнут. Для сохранения вставки замените ROLLBACK на COMMIT.

Обработка дубликата ключа

INSERT ... ON DUPLICATE KEY UPDATE обновляет строку при конфликте PRIMARY KEY или UNIQUE. Алиас incoming передает значения предлагаемой строки.

START TRANSACTION;

INSERT INTO customers (id, name, email, city)
VALUES (5, 'Dina', 'dina@example.test', 'Perm');

INSERT INTO customers (id, name, email, city)
VALUES (5, 'Dina Updated', 'dina@example.test', 'Tomsk') AS incoming
ON DUPLICATE KEY UPDATE
    name = incoming.name,
    city = incoming.city;

SELECT id, name, city FROM customers WHERE id = 5;
ROLLBACK;

id | name         | city
5  | Dina Updated | Tomsk

Синтаксис обработки дубликатов описан в документации MySQL.

UPDATE и DELETE с проверкой

Сначала посмотрите целевые строки. Затем выполните изменение и повторите SELECT внутри той же транзакции. Если результат не подходит, используйте ROLLBACK.

START TRANSACTION;

SELECT id, amount FROM orders WHERE id = 102;

UPDATE orders
SET amount = 900.00
WHERE id = 102;

SELECT id, amount FROM orders WHERE id = 102;
ROLLBACK;

SELECT id, amount FROM orders WHERE id = 102;

до UPDATE:     102 | 800.00
после UPDATE:  102 | 900.00
после ROLLBACK: 102 | 800.00

START TRANSACTION;

SELECT id, customer_id, amount FROM orders WHERE id = 105;
DELETE FROM orders WHERE id = 105;
SELECT COUNT(*) AS remaining FROM orders WHERE id = 105;

ROLLBACK;
SELECT COUNT(*) AS remaining FROM orders WHERE id = 105;

после DELETE: 0
после ROLLBACK: 1

WHERE должен ограничивать набор строк.

Параметр sql_safe_updates дополнительно защищает от UPDATE или DELETE без подходящего условия или LIMIT.

SELECT @@SESSION.sql_safe_updates;

Транзакция не отменяет изменения схемы. CREATE INDEX и DROP INDEX выполняйте отдельно.

EXPLAIN показывает план и помогает выбрать индекс

EXPLAIN показывает план выполнения. Индекс ускоряет поиск по подходящему столбцу, но занимает место и добавляет работу при изменении строк.

Поля type, key и rows

EXPLAIN FORMAT = TRADITIONAL
SELECT id, amount
FROM orders
WHERE amount >= 1500;

В учебной схеме у amount нет отдельного индекса, поэтому type может быть ALL. type = index означает полный проход по индексу.

Создание индекса и сравнение планов

CREATE INDEX idx_orders_amount ON orders (amount);

EXPLAIN FORMAT = TRADITIONAL
SELECT id, amount
FROM orders
WHERE amount >= 1500;

Сравните type, key и rows до и после CREATE INDEX. На таблице из 5 строк сервер может оставить полный проход, потому что чтение маленькой таблицы дешевле.

EXPLAIN ANALYZE
SELECT id, amount
FROM orders
WHERE amount >= 1500;

DROP INDEX idx_orders_amount ON orders;

EXPLAIN ANALYZE выполняет запрос и показывает фактические показатели операторов.

Для запроса, который меняет данные, используйте EXPLAIN без запуска операции.

Условия, которые мешают индексу

Выбирайте нужные столбцы вместо SELECT *. Функция над индексируемым столбцом часто мешает обычному поиску по индексу.

SELECT id
FROM orders
WHERE DATE(ordered_at) = CURRENT_DATE;

SELECT id
FROM orders
WHERE ordered_at >= CURRENT_DATE
  AND ordered_at < CURRENT_DATE + INTERVAL 1 DAY;

Во втором варианте ordered_at остается без функции, поэтому условие подходит для диапазонного поиска. Шаблон с ведущим процентом не дает B-tree найти диапазон по префиксу.

SELECT id, name FROM customers WHERE name LIKE '%na';
SELECT id, name FROM customers WHERE name LIKE 'An%';

Кэш запросов удален в MySQL 8.0. Для ускорения используйте индексы и сравнивайте планы на данных приложения.

Запросы к MySQL из PHP через PDO

Перед запуском установите PHP с расширением PDO_MYSQL. Пользователь your_user должен иметь доступ к базе sql_practice. Для PDO подойдет документация PHP по PDO.

Подготовленный запрос и получение строк

Сохраните код в файле query.php в папке для упражнения. Запустите файл командой php query.php. Терминал покажет оплаченный заказ 101.

<?php
$dbUser = 'your_user';
$dbPassword = 'your_password';

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=sql_practice;charset=utf8mb4',
    $dbUser,
    $dbPassword,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

$customerId = 1;
$status = 'paid';

$stmt = $pdo->prepare(
    'SELECT id, amount
     FROM orders
     WHERE customer_id = :customer_id AND status = :status
     ORDER BY id'
);

$stmt->execute([
    'customer_id' => $customerId,
    'status' => $status,
]);

while ($row = $stmt->fetch()) {
    echo $row['id'] . ' | ' . $row['amount'] . PHP_EOL;
}

php query.php

101 | 1200.00

При успешном подключении fetch возвращает строку заказа 101. Функции mysql_* удалены в PHP 7.0, поэтому новый код использует PDO или mysqli.

Параметры против SQL-инъекции

prepare задает структуру запроса, а execute передает значения отдельно. Пользовательский текст остается значением и не меняет SQL-код.

$stmt = $pdo->prepare(
    'SELECT id, name FROM customers WHERE name = :name'
);
$stmt->execute(['name' => $userName]);

Конкатенация пользовательского ввода позволяет кавычке изменить смысл запроса.

Параметры защищают значения, но не имена таблиц и столбцов. Для сортировки сопоставьте выбор пользователя с заранее разрешенным списком.

Дальше можно изучить хранимые процедуры MySQL и PHP и MySQL на Python.

Ошибки в запросах MySQL

SQL-запрос может пройти синтаксическую проверку и вернуть неверные строки. Перед изменением данных сравните результат с ожидаемыми идентификаторами.

Как выбрать запрос под задачу

Сначала определите, какую строку должен представлять результат. Заказ требует обычной выборки, покупатель с итогами – группировки или подзапроса.

• Список заказов – SELECT с WHERE, ORDER BY и LIMIT.
• Итоги по покупателям – JOIN с GROUP BY.
• Проверка наличия заказа – EXISTS.
• Безопасное изменение – SELECT, транзакция и WHERE.
• Поиск причины медленной выборки – EXPLAIN до добавления индекса.

Совет от нашей редакции: перед сложным запросом запишите ожидаемые идентификаторы строк и сравните их с результатом.