Урок доступен в видеоформате

Удаление данных, оператор DELETE

Когда данные становятся не нужны, их удаляют: товар убирают из справочника, отменённое бронирование стирают. Строки удаляет оператор DELETE, а очистить таблицу целиком можно ещё и оператором TRUNCATE. Правда, отменить такую очистку можно не всегда.

Общая структура запроса с оператором DELETE

MySQL 8.4

DELETE FROM имя_таблицы
[WHERE условие_выборки];

[ ]необязательная часть

имя_таблицы: Таблица, из которой удаляются записи.
условие_выборки: Какие строки удалить. Без WHERE удалятся все строки таблицы.
Необязательный

Например, за пылесос в таблице Payments нет ни одного платежа, так что его можно спокойно убрать из справочника товаров Goods:

MySQL 8.4
DELETE FROM Goods
WHERE good_name = 'vacuum cleaner';

good_id	good_name	type
10 Ещё 10 строк	pineapples	3
11	television	8
12	~~vacuum cleaner~~	8
13	jacket	7
14 Ещё 5 строк	fur coat	7

Условие в WHERE записывается так же, как в запросе SELECT: можно сравнивать значения, объединять условия через AND и OR, использовать IN, LIKE и подзапросы.

Попробуйте сами: выберите условие и посмотрите, какие строки попадут под удаление, а затем выполните запрос.

WHERE

MySQL 8.4

DELETE FROM Goods
WHERE type = 2;

Строк под условием3

good_id	good_name	type
3	bread	2
4	milk	2
5	red caviar	3
6	cinema	4
9	potato	2
10	pineapples	3
13	jacket	7
14	fur coat	7

Запрос без условия очищает таблицу целиком, сама таблица при этом остаётся:

MySQL 8.4
DELETE FROM Payments;

Удалить все записи таблицы можно также с помощью оператора TRUNCATE. Он не перебирает строки по одной, как DELETE, а сразу очищает всю таблицу, поэтому работает гораздо быстрее, особенно на больших таблицах.

Общая структура запроса с оператором TRUNCATE

MySQL 8.4

TRUNCATE TABLE имя_таблицы;

имя_таблицы: Таблица, которую нужно полностью очистить.

При этом TRUNCATE ведёт себя не так, как DELETE:

Нельзя указать условие WHERE — очищается вся таблица
Не срабатывают триггеры удаления
Действие нельзя отменить: TRUNCATE сразу фиксирует текущую транзакцию, и ROLLBACK не вернёт строки
Сбрасывает счётчик AUTO_INCREMENT до начального значения
Не работает, если на таблицу ссылаются внешние ключи других таблиц
Чтобы использовать, необходимо право DROP на таблицу

Нельзя указать условие WHERE — очищается вся таблица
Не срабатывают триггеры на удаление строк — срабатывают только триггеры на TRUNCATE, если они заданы
Действие можно отменить: внутри транзакции ROLLBACK вернёт удалённые строки
Может сбрасывать счётчик идентификаторов при использовании опции RESTART IDENTITY (по умолчанию используется CONTINUE IDENTITY, и счётчик не сбрасывается)
Если на таблицу ссылаются внешние ключи других таблиц, нужно добавить опцию CASCADE — тогда будут очищены и эти таблицы
Чтобы использовать, необходимо право TRUNCATE на таблицу

Подробнее о том, чем DELETE отличается от TRUNCATE и DROP, читайте в статье TRUNCATE, DELETE и DROP в SQL: в чём разница и что выбрать.

Удаление по данным другой таблицы

Иногда строки для удаления нужно выбрать по данным из другой таблицы. Допустим, сервис больше не работает с жильём без кухни, и бронирования такого жилья нужно удалить. Сведения о кухне хранятся в таблице Rooms, а бронирования — в таблице Reservations.

Самый простой способ — подзапрос в WHERE, который вернёт идентификаторы подходящего жилья:

MySQL 8.4
DELETE FROM Reservations
WHERE room_id IN (
    SELECT id
    FROM Rooms
    WHERE has_kitchen = false
);

То же самое можно сделать с помощью JOIN. В этом случае после DELETE перечисляют таблицы, из которых удаляются записи.

MySQL 8.4

DELETE имя_таблицы_1 [, имя_таблицы_2] FROM
имя_таблицы_1 JOIN имя_таблицы_2
ON условие_соединения
[WHERE условие_выборки];

[ ]необязательная часть

имя_таблицы_1: Таблица, из которой удаляются записи. Её имя стоит и после DELETE, и в JOIN.
имя_таблицы_2: Присоединяемая таблица, по данным которой отбираются удаляемые записи. Если указать её и после DELETE, записи удалятся из обеих таблиц.
условие_соединения: Как связаны строки двух таблиц, например имя_таблицы_1.поле = имя_таблицы_2.поле.
условие_выборки: Какие записи удалить. В условии можно проверять столбцы обеих таблиц.
Необязательный

То же самое можно сделать с помощью USING. После него указываются дополнительные таблицы, по которым выбираются удаляемые записи.

MySQL 8.4

DELETE FROM имя_таблицы_1
USING имя_таблицы_2
WHERE условие_соединения
[AND условие_выборки];

[ ]необязательная часть

имя_таблицы_1: Таблица, из которой удаляются записи.
имя_таблицы_2: Дополнительная таблица, по данным которой отбираются удаляемые записи. Из неё самой ничего не удаляется.
условие_соединения: Как связаны строки двух таблиц, например имя_таблицы_1.поле = имя_таблицы_2.поле.
условие_выборки: Дополнительное условие: какие из связанных записей удалить. В нём можно проверять столбцы обеих таблиц.
Необязательный

Для нашей задачи запрос будет таким:

MySQL 8.4
DELETE Reservations FROM
Reservations JOIN Rooms ON
Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;

MySQL 8.4
DELETE FROM Reservations
USING Rooms
WHERE Reservations.room_id = Rooms.id
AND Rooms.has_kitchen = false;

Теперь удалим вместе с бронированиями и само жильё.

MySQL 8.4
DELETE Reservations, Rooms FROM
Reservations JOIN Rooms ON
Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;

Такой запрос удалит только жильё, у которого были бронирования: строки Rooms без пары в Reservations в соединение не попадут.

Оператор DELETE удаляет строки только из одной таблицы, поэтому удалим их по очереди: сначала бронирования, затем само жильё. Чтобы они выполнились вместе или не выполнились вовсе, объедините их в транзакцию:

MySQL 8.4
BEGIN;
DELETE FROM Reservations
USING Rooms
WHERE Reservations.room_id = Rooms.id
AND Rooms.has_kitchen = false;

DELETE FROM Rooms
WHERE Rooms.has_kitchen = false;
COMMIT;

Второй запрос удалит всё жильё без кухни, в том числе то, которое ни разу не бронировали.

В подзапросе нельзя читать ту же таблицу, из которой удаляет DELETE. Например, удалить бронирования дешевле средней цены так не получится:

MySQL 8.4
DELETE FROM Reservations
WHERE price < (SELECT AVG(price) FROM Reservations);

Запрос завершится ошибкой:

MySQL 8.4
ERROR 1093 (HY000): You can't specify target table 'Reservations' for update in FROM clause

Обойти ограничение можно, обернув подзапрос в ещё один SELECT. Тогда средняя цена сначала вычисляется и сохраняется во временной таблице, и удаление читает уже её:

MySQL 8.4
DELETE FROM Reservations
WHERE price < (
    SELECT avg_price
    FROM (SELECT AVG(price) AS avg_price FROM Reservations) AS stats
);

Проверьте себя

Нужно удалить из таблицы Reservations только бронирования дешевле 100. Какой запрос с этим справится?