Справочник по MySQL : SQL : Удаление строк из таблицы (DELETE FROM)
Удаление записей осуществляется командой DELETE FROM.
Синтаксис оператора DELETE FROM
DELETE [LOW_PRIORITY | QUICK] FROM table_name [WHERE where_definition] [ORDER BY . ] [LIMIT rows]
DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] . ] FROM table-references [WHERE where_definition]
DELETE [LOW_PRIORITY | QUICK] FROM table_name[.*], [table_name[.*] . ] USING table-references [WHERE where_definition]
Команда DELETE удаляет из таблицы table_name все записи, удовлетворяющие условию where_definition. Если условие WHERE where_definition не задано, то из таблицы table_name удаляются все записи.
Команда DELETE возвращает количество удаленных записей.
Следует учитывать, что если в команде DELETE не задано условие WHERE where_definition, то команда возвратит 0, хотя записи были удалены.
Если все же необходимо удалить все записи и узнать их количество, то в условии where_definition нужно задать заведомо истинное значение:
DELETE FROM `users` WHERE 1=1
Но в этом случае выполнение команды происходит значительно медленнее.
LOW_PRIORITY — Если указано это ключевое слово, то удаление записей из таблицы table_name не будет произведено до тех пор, пока другие процессы не закончат чтение их этой таблицы. QUICK — Если указано это ключевое слово, то при удалении записей обработчик таблицы не будет объединять индексы, что может ускорить операцию удаления. table_name — Имя таблицы в базе данных, из которой будет происходить удаление строк. LIMIT — Задает максимальное количество строк, которые могут быть удалены за текущий запрос. Следующий запрос удалит из таблицы users только 5 строк:
DELETE FROM `users` LIMIT 5
ORDER BY — Задает имя поля, или имена полей через запятую, по которым происходит сортировка удаляемых записей. Эта возможность актуальна при необходимости удалить только определенное кол-во записей, отсортированных по какому-либо свойству. Следующий пример удалит из таблицы users запись самого молодого человека. Допустим таблица users имеет следующий вид:
CREATE TABLE `users`( `name` VARCHAR(255) NOT NULL, `age` SMALLINT(3) NOT NULL )
Запишем в нее несколько записей:
INSERT INTO `users` ( `name`, `age` ) VALUES ('Миша', 25), ('Ксюша', 15), ('Настя', 12), ('Саша', 26), ('Дима', 30)
Таблица users имеет значения:
SELECT * FROM `users`
+-------+-----+ | name | age | +-------+-----+ | Миша | 25 | | Ксюша | 15 | | Настя | 12 | | Саша | 26 | | Дима | 30 | +-------+-----+ 5 rows in set (0.00 sec)
Удаляем самого молодого:
DELETE FROM `users` ORDER BY `age` LIMIT 1
Теперь таблица имеет значения:
SELECT * FROM `users`
+-------+-----+ | name | age | +-------+-----+ | Миша | 25 | | Ксюша | 15 | | Саша | 26 | | Дима | 30 | +-------+-----+ 4 rows in set (0.00 sec)
Можем также удалить самого старого:
DELETE FROM `users` ORDER BY `age` DESC LIMIT 1
Теперь таблица имеет значения:
SELECT * FROM `users`
+-------+-----+ | name | age | +-------+-----+ | Миша | 25 | | Ксюша | 15 | | Саша | 26 | +-------+-----+ 3 rows in set (0.00 sec)
Сортировка удаляемых записей при помощи команды ORDER BY появилась в MySQL начиная с версии 4.0. FROM table-references и USING . — Позволяют удалять записи сразу из нескольких таблиц руководствуясь многотабличным условием WHERE where_definition. Следующие два примера равнозначны по производимым действиям и удаляют одинаковые записи в таблицах table_1 и table_2 и используя при поиске значений еще и таблицу table_3:
DELETE `table_1`, `table_2` FROM `table_1`, `table_2`, `table_3` WHERE `table_1`.`id`=`table_2`.`id` && `table_2`.`id`=`table_3`.`id`
Аналогичный запрос:
DELETE FROM `table_1`, `table_2` USING `table_1`, `table_2`, `table_3` WHERE `table_1`.`id`=`table_2`.`id` && `table_2`.`id`=`table_3`.`id`
Как удалить записи в двух таблицах по общему условию SQL Server
У меня есть 2 таблицы. Days и Parameters . В первой таблицe — id , days (int). Во второй таблице — id , param (int), daysId (int). daysId — ключ из первой таблицы. Необходимо удалить данные из первой таблицы, где days < 90, но и также необходимо удалить из второй таблицы параметры, которые привязаны к удаляемым данным по daysId . Как это можно связать 2 таблицы в запросе delete ? В моих таблицах больше столбцов, я привел минимальный пример.
Отслеживать
51.6k 200 200 золотых знаков 61 61 серебряный знак 242 242 бронзовых знака
задан 6 июл 2018 в 11:09
1,158 1 1 золотой знак 10 10 серебряных знаков 20 20 бронзовых знаков
А СУБД у вас какая?
6 июл 2018 в 11:14
Я не работал с СКЛ сервером. Мне кажется, можно настроить FK с удалением каскадом, и тогда удалять надо будет в принципе только из 1 таблицы
6 июл 2018 в 11:17
daysId — ключ из первой таблицы FK имеется? Создайте и установите ON DELETE CASCADE — оно само удалится.
6 июл 2018 в 11:17
@NickProskuryakov я сейчас по ошибке, удалил значения вместе больше 90, меньше 90, в итоге небольшая работа за неделю стерлась 😀
6 июл 2018 в 12:17
@NickProskuryakov на мой взгляд, такие вещи должны решаться архитектурой. Если данные имеют ценность без мастер таблицы, то запретить удаление каскадом. А если при удалении из основной таблицы, они должны удаляться так же, то каскадное удаление в самый раз. СУБД специально созданы, для того чтобы работать с данными. Почему бы не использовать их для этого? (это риторический вопрос). Ваш подход я понял, просто не согласен. Хорошего дня =)
2.18. Удаление данных
Теперь посмотрим, как можно удалять записи из таблицы. Для этого используется оператор DELETE. Общий вид оператора показан в листинге 2.7.
Листинг 2.7. Общий вид оператора удаления DELETE
DELETE [ FROM ] < table_name WITH ( < table_hint_limited >[ . n ] ) | view_name | rowset_function_limited > [ FROM < < table_source >> [ . n ] ] [ WHERE < < search_condition >| < [ CURRENT OF < < [ GLOBAL ] cursor_name >| cursor_variable_name > ] > > ]
Минимальная команда удаления выглядит следующим образом:
DELETE tbPeoples
Эта команда удаляет все строки из таблицы tbPeoples. Те же самые действия можно выполнить с помощью вызова команды:
DELETE FROM tbPeoples
Отличие этой команды в том, что мы добавили ключевое слово FROM, которое в данной команде может опускаться.
Пока не надо выполнять эти команды, потому что они очистят таблицу tbPeoples, а нам этого пока не нужно. Данные еще пригодятся для тестирования будущих запросов. Если хотите протестировать удаление, то создайте новую таблицу, заполните ее данными и очистите. Заодно вспомните и закрепите знания, которые мы рассматривали ранее. Можно создать новую таблицу с помощью команды SELECT INTO, это будет проще и быстрее.
Я предпочитаю использовать вторую команду, хотя она выполняет те же самые действия, а содержит на одно слово больше. Просто второй вариант более интеллектуально понятен. В первом случае команда читается как «удалить tbPeoples». Это не совсем корректно. Второй вариант команды звучит как «Удалить из tbPeoples». Это уже ближе отражает суть выполняемых действий.
Честно сказать, такая простота очистки всего содержимого таблицы, на мой взгляд, является недостатком. В моей практике уже не раз были случаи, когда удаление происходило случайно. Просто кто-то выделял в Query Analizyr не полную команду, и таблица очищалась сильнее, чем хотелось бы. Проблема кроется и в том, что в большинстве редакторов нет подтверждения при удаления. Я видел только одну программу, которая перед удалением отображала окно подтверждения и показывала, сколько строк будет удаляться – phpMySQLAdmin. Это программа для управления через WEB интерфейс базой данных MySQL.
Чтобы ограничить количество удаляемых строк, используется секция WHERE. Она используется точно так же, как и в запросах SELECT или UPDATE. Когда мы тестировали оператор INSERT, то вставили в таблицу достаточно много записей. Давайте подчистим их и попрактикуемся в использовании оператора DELETE.
Для начала удалим все строки, в которых поле «vcFamil» содержит нулевое значение. Это делается с помощью следующего запроса:
DELETE FROM tbPeoples WHERE vcFamil is NULL
С помощью секции WHERE мы требуем, чтобы сервер удалил только те записи, в которых поле «vcFamil» содержит нулевое значение.
Следующий запрос удаляет запись, в которой первичный ключ «idPeoples» содержит значение -22:
DELETE FROM tbPeoples WHERE idPeoples = -22
Предыдущие два запроса можно было бы выполнить одной командой:
DELETE FROM tbPeoples WHERE vcFamil is NULL OR idPeoples = -22
В данном случае удаляются записи, в которых или фамилия не заполнена, или ключевое поле равно -22.
Давайте теперь для закрепления материала создадим что-нибудь посложнее. Например, удалим все записи из таблицы tbPeoples, у которых нет записей в таблице телефонов tbPhoneNumbers. Для этого сначала пишем запрос SELECT, чтобы убедиться, что он выбирает необходимые данные и не ошибиться:
SELECT * FROM tbPeoples pl WHERE NOT EXISTS (SELECT idPeoples FROM tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples)
Этот запрос выбирает все строки, для которых не существует (NOT EXISTS) записи в таблице tbPhoneNumbers. Отличный пример, ведь подобные запросы приходиться писать достаточно часто.
Теперь модифицируем пример для того, чтобы он удалял выбранные данные. Для этого первую строку заменяем на DELETE:
DELETE FROM tbPeoples WHERE NOT EXISTS (SELECT idPeoples pl FROM tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples)
Но этого не достаточно, потому что при удалении, в секции FROM не должно быть псевдонимов. Это значит, что от них нужно отказаться и в подзапросе использовать вместо псевдонима полное имя таблицы из внешнего запроса:
DELETE FROM tbPeoples WHERE NOT EXISTS (SELECT idPeoples FROM tbPhoneNumbers pn WHERE tbPeoples.idPeoples=pn.idPeoples)
Недавно меня один очень опытный программист спросил: «А можно использовать оператор LIKE при удалении?». А почему бы и нет? Можно использовать любые операторы, которые мы изучали для секции WHERE, и LIKE тут не исключение. Следующий запрос удалит все записи, в которых фамилия начинается с буквы «С»:
DELETE FROM tbPeoples WHERE vcFamil LIKE 'С'
Теперь рассмотрим удаление из связанных таблиц. Допустим, что нам необходимо удалить номера телефонов определенного работника. Для этого выполняем следующий запрос:
DELETE pn FROM tbPhoneNumbers pn, inserted i WHERE pn.idPeoples=i.idPeoples AND vcFamil = Фамилия
Нельзя удалять сразу из двух таблиц, может быть только одна. Так как в этом примере связываются две таблицы, то после оператора DELETE необходимо явно указать, из какой из двух происходит удаление.
При удалении данных, для каждой удаляемой строки в журнале транзакций создается отдельная запись. О транзакциях мы подробно поговорим в разделе 2.19, а сейчас нам необходимо понимать, что это достаточно трудоемкий процесс. Это можно понять, даже не вникая в суть самих транзакций. Журнал транзакций – это файл и в него происходит запись для каждой удаляемой строки. Если в таблице 1 000 000 записей, то будет столько же обращений к жесткому диску, а если каждое обращение к диску отнимет хотя бы 10 байт данных, то мы потеряем на жестком диске 10 000 000 байт свободного пространства. Я думаю, что нас ожидают не очень радужные перспективы.
Но у MS SQL Server есть вариант решения этой проблемы — TRUNCATE TABLE. Ее можно использовать при удалении из таблицы всех записей, и при этом обращения к журналу будут сведены к минимуму. Для удаления всех записей из таблицы Товары нужно использовать следующий запрос:
TRUNCATE TABLE Имя_Таблицы
В результате этого из таблицы будут удалены все записи, а в журнал будет записано только информация о том, что все удалено.
С помощью оператора TRUNCATE TABLE можно удалять только все записи из таблицы, потому что нет секции WHERE. К тому же, нельзя удалять из таблицы, которая связана с помощью внешнего ключа.
Есть еще одно очень важное отличие между операторами DELETE и TRUNCATE TABLE. Если удалить все записи из таблицы с помощью DELETE, а затем добавить новую запись, то значение автоматически увеличиваемого поля будет увеличиваться дальше, т.е. счетчик не будет сброшен. А после удаления с помощью TRUNCATE TABLE, значение счетчика сбрасывается и если после этого добавить строку, то значение автоматически увеличиваемого поля начнет свой отсчет с единицы. Учитывайте этот эффект при использовании определенного оператора удаления данных.
как удалить записи из table
Вы либо должны добавить во второй таблице внешнему ключу свойство ON DELETE CASCADE , и тогда при удалении данных из первой таблицы будут автоматически удаляться из второй, либо писать запрос на удаление с сравнением полей.
Отслеживать
ответ дан 30 мар 2020 в 17:28
31 2 2 бронзовых знака
Для того, чтобы автоматизировать этот процесс стоит повесить триггер на процесс удаления первой таблицы.
CREATE TRIGGER TRG_DEL ON SimulatedResult.SimulatedResultGroup FOR DELETE AS delete from SimulatedResult.EventForSimulateGroup where SimulatedResultGroupId in (select id from DELETED ); Теперь при удалении записи из SimulatedResultGroup будут автоматически удаляться записи из EventForSimulateGroup