Delete from sql: DELETE | SQL | SQL-tutorial.ru

Синтаксис для использования команды удаления SQL

• Таблица, из которой мы хотим удалить строки, указывается в параметре table_name оператора DELETE FROM.
• Существует необязательное предложение WHERE, в котором мы можем указать условие, согласно которому строки должны удаляться.

ПРИМЕЧАНИЕ. Следует проявлять особую осторожность с предложением WHERE, поскольку без него все строки из таблицы будут удалены.

В предложении WHERE можно указать несколько условий с помощью операторов AND и OR.

Давайте применим концепции, изложенные выше, к строкам таблицы.

Удаление определенных строк на основе одного условия

Возьмем образец таблицы «Employee_details»;

• Из приведенной выше таблицы, чтобы удалить запись сотрудника с «EmployeeID», равным единице, мы воспользуемся следующим запросом:

• Чтобы проверить результат запроса в таблице выше, мы воспользуемся командой «SELECT *»:

Как мы видим, одна запись была удалена на основании условия, указанного в предложении WHERE.

• Из приведенной выше таблицы нам нужно удалить запись сотрудника с именем «Арун»:

Как мы видим, «Имя» имеет тип данных символьной строки, поэтому указанное значение должно быть заключено в одиночные кавычки, без которых система вернет синтаксическую ошибку.

Результатом вышеуказанного запроса будет следующее:

• Мы также можем удалить несколько строк с помощью одного запроса.

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

Это приведет к следующему:

Как видим, удалены четыре строки.

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

Удаление определенных строк на основе нескольких условий

Мы можем использовать операторы AND и OR для объединения нескольких условий в предложении WHERE команды DELETE.

• При использовании оператора OR все строки, удовлетворяющие одному из указанных условий, удаляются.

Из нашего образца таблицы «Employee_details», чтобы удалить все записи сотрудников, в которых имя сотрудника — «Аджай» или город — «Ченнаи»:

Это приведет к следующему:

Как мы видим, обе строки, удовлетворяющие любому из условий, были удалены.

• Когда мы используем оператор AND, удаляются только строки, удовлетворяющие обоим указанным условиям.

Из приведенной выше таблицы нам нужно удалить все записи для сотрудников, которые принадлежат «Бангалору» и имеют зарплату менее 50000:

Это приведет к следующему:

Как мы видим, была удалена только одна запись, удовлетворяющая обоим условиям.

Давайте посмотрим, что произойдет, если мы не будем использовать предложение WHERE.

Магистерская программа для бизнес-аналитиков

Удаление всех записей из таблицы

Крайне важно быть предельно осторожным при использовании команды DELETE для таблицы, поскольку данные удаляются безвозвратно.

• Без предложения WHERE удаляются все записи таблицы. Если не сделать это намеренно, это может вызвать множество проблем.

Чтобы удалить все записи из нашей таблицы «Employee_details», воспользуемся следующим запросом:

Чтобы проверить, все ли записи удалены, воспользуемся командой SELECT:

Как мы видим, команда SELECT возвращает пустой набор, поскольку все записи из таблицы «Employee_details» были удалены.

• Мы также можем использовать команду TRUNCATE для удаления всех записей из таблицы.

Это часть языка определения данных, подъязыка SQL, который позволяет создавать и изменять объекты базы данных. Синтаксис этой команды:

Как мы видим, здесь нет предложения WHERE, поэтому эта команда используется только тогда, когда нам нужно очистить содержимое таблицы.

Чтобы удалить все записи из таблицы «Employee_details»:

Чтобы проверить, все ли записи удалены:

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

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы Business Analyst. Запишитесь сейчас!

Следующие шаги

Удаление записей из таблиц и баз данных — огромная часть управления данными. Иногда мы вставляем неправильные данные или данные в таблице устаревают и требуют модификации, все это требует помощи Delete в SQL.

Ознакомьтесь с нашими руководствами, чтобы узнать, как удалить повторяющиеся строки в таблицах и многих операторах SQL.

Теперь, когда вы знаете, как удалять существующие записи, пора вам начать изучать другие команды SQL, чтобы вы могли начать манипулировать и запрашивать данные и продвигаться вперед в своем пути, чтобы стать экспертом в SQL. Если вам понравилась эта статья, вы должны ознакомиться с нашей магистерской программой для бизнес-аналитиков, поскольку она также охватывает А-Я SQL.

У вас есть к нам вопросы? Упомяните их в разделе комментариев нашей статьи «Удаление в SQL», и наши специалисты ответят на них за вас.

Оператор SQL DELETE — с примерами

Как удалить записи в SQL?

DELETE безвозвратно удаляет записи из таблицы.
DELETE может удалить одну или несколько записей в таблице.
Используйте предложение WHERE, чтобы УДАЛИТЬ только определенные записи.

Синтаксис SQL DELETE

Общий синтаксис:

УДАЛИТЬ имя-таблицы
 

Чтобы удалить определенные записи, добавьте предложение WHERE:

УДАЛИТЬ имя-таблицы
 ГДЕ условие
 

Примеры SQL DELETE

Проблема : Удалить все продукты.

УДАЛИТЬ продукт
 

Результат: 77 записей удалено.

Проблема : Удалить продукты стоимостью более 50 долларов.

УДАЛИТЬ продукт
 ГДЕ UnitPrice> 50
 

Результат: 7 записей удалены.

Проблема : Удалить клиента с Id = 21.

УДАЛИТЬ клиента
 ГДЕ Id = 21
 

Результат: 1 запись удалена.

SQL: лучшие способы его использования — Управление базами данных — Блоги

В реляционных базах данных мы создаем таблицы для хранения данных в различных форматах. SQL Server хранит данные в формате строки и столбца, который содержит значение, связанное с каждым типом данных.При разработке таблиц SQL мы определяем такие типы данных, как целое число, число с плавающей запятой, десятичное число, varchar и бит. Например, таблица, в которой хранятся данные о клиентах, может содержать такие поля, как имя клиента, адрес электронной почты, адрес, штат, страна и т. Д. Различные команды SQL выполняются в таблице SQL и могут быть разделены на следующие категории:

• Язык определения данных (DDL): Эти команды используются для создания и изменения объектов базы данных в базе данных.
  • Создать: Создает объекты
  • Изменить: Изменяет объекты
  • Сброс: Удаляет объекты
  • Усечение: Удаляет все данные из таблицы
• Язык манипулирования данными (DML): Эти команды вставляют, извлекают, изменяют, удаляют и обновляют данные в базе данных.
  • Выберите: Извлекает данные из одной или нескольких таблиц
  • Insert: Добавляет новые данные в таблицу
  • Обновление: Изменяет существующие данные
  • Удалить: Удаляет существующие записи в таблице

• Язык управления данными (DCL): Эти команды связаны с правами или средствами управления разрешениями в базе данных.
  • Грант: Назначает разрешения пользователю
  • Отозвать: Отменяет разрешения у пользователя
• Transaction Control Language (TCL): Эти команды управляют транзакциями в базе данных.
  • Фиксация: Сохраняет изменения, внесенные запросом
  • Откат: Откат явной или неявной транзакции до начала транзакции или до точки сохранения внутри транзакции
  • Сохранить транзакции: Устанавливает точку сохранения или маркер внутри транзакции

Предположим, у вас есть данные о заказах клиентов, хранящиеся в таблице SQL. Если вы продолжаете вставлять данные в эту таблицу непрерывно, таблица может содержать миллионы записей, что вызовет проблемы с производительностью в ваших приложениях.Обслуживание вашего индекса также может занять очень много времени. Часто нет необходимости хранить заказы старше трех лет. В этих случаях вы можете удалить эти записи из таблицы. Это сэкономит место для хранения, а также сократит ваши усилия по обслуживанию.

Удалить данные из таблицы SQL можно двумя способами:

• Использование оператора удаления SQL
• Использование усечения

Позже мы рассмотрим разницу между этими командами SQL.Давайте сначала рассмотрим оператор удаления SQL.

Оператор удаления SQL без каких-либо условий

В операторах языка обработки данных (DML) оператор удаления SQL удаляет строки из таблицы. Вы можете удалить определенную строку или все строки. Базовая инструкция удаления не требует аргументов.

Давайте создадим SQL-таблицу заказов, используя приведенный ниже скрипт. В этой таблице есть три столбца [OrderID], [ProductName] и [ProductQuantity].

  Create Table Orders   (OrderID int,   ProductName varchar (50),   ProductQuantity int  )  

Вставьте несколько записей в эту таблицу.

  Вставить в значения заказов (1, 'ABC books', 10),   (2, 'XYZ', 100),   (3, 'SQL book', 50)  

Теперь предположим, что мы хотим удалить данные таблицы. Вы можете указать имя таблицы для удаления данных с помощью оператора удаления. Оба оператора SQL одинаковы. Мы можем указать имя таблицы из ключевого слова (необязательно) или указать имя таблицы сразу после удаления.

  Удалить заказы   Go   Удалить из заказов   GO  

Оператор удаления SQL с отфильтрованными данными

Эти операторы удаления SQL удаляют все данные таблицы.Обычно мы не удаляем все строки из таблицы SQL. Чтобы удалить определенную строку, мы можем добавить предложение where с оператором delete. Предложение where содержит критерии фильтрации и в конечном итоге определяет, какие строки следует удалить.

Например, предположим, что мы хотим удалить идентификатор заказа 1. После добавления предложения where SQL Server сначала проверяет соответствующие строки и удаляет эти конкретные строки.

  Удалить заказы, где порядковый номер = 1 
 
 
  

Если условие предложения where ложно, оно не удаляет никакие строки.Например, мы удалили заказанный 1 из таблицы заказов. Если мы выполним оператор снова, он не найдет ни одной строки, удовлетворяющей условию предложения where. В этом случае он возвращает 0 затронутых строк.

Оператор удаления SQL и предложение TOP

Вы также можете использовать оператор TOP для удаления строк. Например, приведенный ниже запрос удаляет первые 100 строк из таблицы «Заказы».

  Удалить верхнюю часть (100) [OrderID]   из заказов  

Поскольку мы не указали «ORDER BY», он выбирает случайные строки и удаляет их.Мы можем использовать предложение Order by для сортировки данных и удаления верхних строк. В приведенном ниже запросе он сортирует [OrderID] в порядке убывания, а затем удаляет его из таблицы [Orders].

  Удалить из заказов, где [OrderID] в   (  Выбрать первые 100 [OrderID] FROM Orders   заказать по [OrderID] Desc  )  

Удаление строк на основе другой таблицы

Иногда нам нужно удалить строки на основе другой таблицы. Эта таблица может существовать в той же базе данных или нет.

Мы можем использовать метод поиска в таблице или соединение SQL для удаления этих строк. Например, мы хотим удалить строки из таблицы [Заказы], которые удовлетворяют следующему условию:

У него должны быть соответствующие строки в таблице [dbo]. [Customer].

Посмотрите на запрос ниже, здесь у нас есть оператор select в предложении where оператора delete. SQL Server сначала получает строки, удовлетворяющие оператору select, а затем удаляет эти строки из таблицы [Заказы] с помощью оператора удаления SQL.

  Удалить заказы, в которых указан порядок   (выберите код заказа   от клиента)  

В качестве альтернативы мы можем использовать SQL-соединения между этими таблицами и удалить строки. В приведенном ниже запросе мы соединяем таблицы [Заказы]] с таблицей [Клиент]. Соединение SQL всегда работает с общим столбцом между таблицами. У нас есть столбец [OrderID], который объединяет обе таблицы вместе.

  УДАЛИТЬ заказы   ИЗ заказов o   ВНУТРЕННЕЕ СОЕДИНЕНИЕ клиента c ON o.orderid = c.orderid  

Чтобы понять приведенный выше оператор удаления, давайте рассмотрим фактический план выполнения.

Согласно плану выполнения, он выполняет сканирование обеих таблиц, получает совпадающие данные и удаляет их из таблицы «Заказы».

• Общее табличное выражение (CTE)

Мы можем использовать обычное табличное выражение (CTE) для удаления строк из таблицы SQL. Сначала мы определяем CTE, чтобы найти строку, которую мы хотим удалить.

Затем мы соединяем CTE с таблицей SQL Orders и удаляем строки.

  С cteOrders AS   (ВЫБЕРИТЕ OrderID   ОТ клиента   ГДЕ CustomerID = 1)   УДАЛИТЬ заказы   FROM cteOrders sp   INNER JOIN dbo.Orders o ON o.orderid =  

Влияние на диапазон идентичности

Столбцы идентификаторов в SQL Server генерируют уникальные последовательные значения для вашего столбца. Они используются в первую очередь для однозначной идентификации строки в таблице SQL.Столбец первичного ключа также является хорошим выбором для кластеризованного индекса в SQL Server.

В приведенном ниже сценарии у нас есть таблица [Сотрудник]. У этой таблицы есть идентификатор столбца идентификатора.

  Create Table Employee   (  id int identity (1,1),   [Name] varchar (50)  )  

Мы вставили 50 записей в эту таблицу, которые сгенерировали значения идентификаторов для столбца id.

  Объявить @id int = 1   While (@id <= 50)   BEGIN   Вставить в значения Employee ([Name]) ('Test' + CONVERT (VARCHAR, @ ID))   Установить @id = @ id + 1   КОНЕЦ  

Если мы удалим несколько строк из таблицы SQL, это не сбрасывает значения идентичности для последующих значений.Например, давайте удалим несколько строк со значениями идентификаторов от 20 до 25.

  Удалить из сотрудника  , где id от 20 до 25  

Теперь просмотрите записи таблицы.

  Выберите * от сотрудника, у которого id> 15  

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

Оператор удаления SQL и журнал транзакций

SQL delete регистрирует удаление каждой строки в журнале транзакций. Предположим, вам нужно удалить миллионы записей из таблицы SQL.Вы не хотите удалять большое количество записей за одну транзакцию, потому что это может привести к экспоненциальному росту файла журнала и недоступности вашей базы данных. Если вы отмените транзакцию в середине, откат оператора удаления может занять несколько часов.

В этом случае вы всегда должны удалять строки небольшими порциями и регулярно фиксировать эти порции. Например, вы можете удалить пакет из 10 000 строк за раз, зафиксировать его и перейти к следующему пакету. Когда SQL Server фиксирует фрагмент, можно контролировать рост журнала транзакций.

Лучшие практики

• Перед удалением данных всегда следует выполнять резервное копирование.
• По умолчанию SQL Server использует неявные транзакции и фиксирует записи, не спрашивая пользователя. Рекомендуется запускать явную транзакцию с помощью команды «Начать транзакцию». Он дает вам возможность зафиксировать или откатить транзакцию. Вам также следует часто выполнять резервное копирование журнала транзакций, если ваша база данных находится в режиме полного восстановления.
• Вы хотите удалять данные небольшими порциями, чтобы избежать чрезмерного использования журнала транзакций.Это также позволяет избежать блокировки для других транзакций SQL.
• Вы должны ограничить разрешения, чтобы пользователи не могли удалять данные. Только авторизованные пользователи должны иметь доступ для удаления данных из таблицы SQL.
• Вы хотите запустить оператор удаления с предложением where. Он удаляет отфильтрованные данные из таблицы SQL. Если ваше приложение требует частого удаления данных, рекомендуется периодически сбрасывать значения идентификаторов. В противном случае вы можете столкнуться с проблемами исчерпания идентификационных данных.
• Если вы хотите очистить таблицу, рекомендуется использовать оператор усечения. Оператор truncate удаляет все данные из таблицы, использует минимальное ведение журнала транзакций, сбрасывает диапазон значений идентификаторов и работает быстрее, чем оператор удаления SQL, поскольку он немедленно освобождает все страницы для таблицы.
• Если вы используете ограничения внешнего ключа (отношения родитель-потомок) для своих таблиц, вы должны удалить строку из дочерней строки, а затем из родительской таблицы.Если вы удаляете строку из родительской строки, вы также можете использовать опцию каскадного удаления для автоматического удаления строки из дочерней таблицы. Вы можете обратиться к статье: Удалить каскад и обновить каскад во внешнем ключе SQL Server для получения дополнительной информации.
• Если вы используете оператор top для удаления строк, SQL Server удаляет строки случайным образом. Вы всегда должны использовать верхнее предложение с соответствующим предложением Order by и Group by.
• Оператор удаления устанавливает блокировку исключительного намерения для справочной таблицы; следовательно, в течение этого времени никакие другие транзакции не могут изменять данные.Вы можете использовать подсказку NOLOCK для чтения данных.
• Следует избегать использования подсказки таблицы для переопределения поведения блокировки по умолчанию для оператора удаления SQL; его должны использовать только опытные администраторы баз данных и разработчики.

Важные соображения

Использование операторов удаления SQL для удаления данных из таблицы SQL дает много преимуществ, но, как видите, это требует методического подхода. Важно всегда удалять данные небольшими партиями и соблюдать осторожность при удалении данных из производственного экземпляра.Наличие стратегии резервного копирования для восстановления данных за минимальное время является обязательным условием, чтобы избежать простоев или снижения производительности в будущем.

MySQL :: Справочное руководство по MySQL 8.0 :: 13.2.2 Оператор DELETE

DELETE - это оператор DML, удаляет строки из таблицы.

Оператор DELETE может начинаться с С предложением для определения общей таблицы выражения, доступные в УДАЛИТЬ .См. Раздел 13.2.15, «WITH (общие табличные выражения)».

Однотабличный синтаксис

  УДАЛИТЬ [LOW_PRIORITY] [QUICK] [IGNORE] FROM  имя_таблицы  [[AS]  псевдоним_таблицы ]
    [РАЗДЕЛ ( имя_раздела  [,  имя_раздела ] ...)]
    [WHERE  where_condition ]
    [СОРТИРОВАТЬ ПО ...]
    [LIMIT  row_count ]  

Оператор DELETE удаляет строки из tbl_name и возвращает количество удаленные строки.Чтобы проверить количество удаленных строк, вызовите ROW_COUNT () функция, описанная в Раздел 12. 16, «Информационные функции».

Основные статьи

Условия в необязательном предложении WHERE определить, какие строки нужно удалить. Без ГДЕ предложение, все строки удаляются.

где_условие - выражение, которое оценивается как истина для каждой удаляемой строки. Он указан как описано в разделе 13.2.10, «Оператор SELECT».

Если указано предложение ORDER BY , строки удаляются в указанном порядке. В LIMIT Предложение устанавливает ограничение на количество строки, которые можно удалить. Эти пункты применяются к одиночным таблицам удаляет, но не удаляет несколько таблиц.

Синтаксис для нескольких таблиц

  УДАЛИТЬ [LOW_PRIORITY] [QUICK] [IGNORE]
      имя_таблицы  [. *] [,  имя_таблицы  [. *]]...
    ИЗ  table_references 
    [WHERE  where_condition ]

УДАЛИТЬ [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM  имя_таблицы  [.  *] [,  имя_таблицы  [. *]] ...
    ИСПОЛЬЗОВАНИЕ  table_references 
    [WHERE  where_condition ]  

Привилегии

Вам нужна привилегия DELETE на table, чтобы удалить из него строки. Вам нужно только SELECT привилегия для любых столбцов которые только читаются, например, те, которые указаны в WHERE clause.

Производительность

Когда вам не нужно знать количество удаленных строк, TRUNCATE TABLE оператор - это более быстрый способ очистить стол, чем УДАЛИТЬ заявление без WHERE clause. в отличие УДАЛИТЬ , TRUNCATE TABLE не может быть использован внутри транзакции или если у вас есть блокировка таблицы. Видеть Раздел 13.1.37, «Оператор TRUNCATE TABLE» и Раздел 13.3.6, «Операторы LOCK TABLES и UNLOCK TABLES».

На скорость операций удаления также могут влиять факторы. обсуждается в Раздел 8.2.5.3, «Оптимизация операторов DELETE».

Чтобы гарантировать, что данный УДАЛИТЬ не занимает много времени, специфичный для MySQL LIMIT row_count пункт для DELETE определяет максимальное количество удаляемых строк. Если количество строк до удаление превышает лимит, повторите УДАЛИТЬ заявление до количества затронутых строк меньше значения LIMIT .

Подзапросы

Вы не можете удалить из таблицы и выбрать из той же таблицы в подзапрос.

Опора для разделенных таблиц

DELETE поддерживает явный выбор раздела используя предложение PARTITION , которое принимает список разделенных запятыми имен одного или нескольких разделов или подразделы (или оба), из которых можно выбрать строки для удаления. Разделы, не включенные в список, игнорируются.Учитывая многораздельная таблица t с разделом с именем p0 , выполнение инструкции DELETE FROM t PARTITION (p0) оказывает такое же влияние на таблицу как выполнение ALTER ТАБЛИЦА t ОБРЕЗАТЬ РАЗДЕЛ (p0) ; в обоих случаях все строки в разделе сбрасываются p0 .

РАЗДЕЛ может использоваться вместе с ГДЕ условие, в этом случае условие проверено только на строках в перечисленных разделах.Например, УДАЛИТЬ ИЗ РАЗДЕЛА t (p0), ГДЕ c <5 удаляет строки только из раздела p0 , для которого выполняется условие c <5 ; строк в любых другие разделы не проверяются и, следовательно, не затрагиваются УДАЛИТЬ .

Предложение PARTITION также может использоваться в несколько таблиц DELETE операторов. Ты можешь использовать до одной такой опции для каждой таблицы, названной в ИЗ вариант.

Для получения дополнительной информации и примеров см. Раздел 24.5, «Выбор раздела».

Столбцы с автоматическим приращением

Если вы удалите строку, содержащую максимальное значение для AUTO_INCREMENT столбец, значение не используется повторно для MyISAM или InnoDB Таблица. Если вы удалите все строки в таблице с помощью DELETE ИЗ имя_таблицы (без WHERE clause) в autocommit mode, последовательность запускается заново для всех механизмов хранения, кроме InnoDB и MyISAM .Есть некоторые исключения из этого поведения для InnoDB таблицы, как описано в Раздел 15.6.1.6, «Обработка AUTO_INCREMENT в InnoDB».

Для таблиц MyISAM можно указать AUTO_INCREMENT вторичный столбец в ключ из нескольких столбцов. В этом случае повторное использование значений, удаленных из начало последовательности встречается даже для MyISAM таблицы. См. Раздел 3.6.9, «Использование AUTO_INCREMENT».

Модификаторы

Оператор DELETE поддерживает следующие модификаторы:

• Если вы укажете модификатор LOW_PRIORITY , сервер задерживает выполнение УДАЛИТЬ , пока другие клиенты не исчезнут читаем из таблицы.Это влияет только на механизмы хранения. которые используют только блокировку на уровне таблицы (например, MyISAM , ПАМЯТЬ и MERGE ).

• Для таблиц MyISAM , если вы используете QUICK модификатор, механизм хранения не индекс слияния уходит во время удаления, что может ускорить некоторые виды операций удаления.

• Модификатор IGNORE заставляет MySQL игнорировать игнорируемые ошибки в процессе удаления строк.(Ошибки обнаруженные на этапе синтаксического анализа, обрабатываются в обычным способом.) Ошибки, которые игнорируются из-за использования IGNORE возвращаются как предупреждения. Для большего Для получения дополнительной информации см. «Влияние IGNORE на выполнение оператора».

Порядок удаления

Если инструкция DELETE включает ORDER BY , строки удаляются в порядке указанные в пункте. Это полезно в первую очередь в сочетании с LIMIT .Например, следующие оператор находит строки, соответствующие предложению WHERE , сортирует их по timestamp_column и удаляет первый (самый старый):

  УДАЛИТЬ ИЗ somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;  

ORDER BY также помогает удалять строки в порядке требуется, чтобы избежать нарушений ссылочной целостности.

Таблицы InnoDB

Если вы удаляете много строк из большой таблицы, вы можете превысить размер таблицы блокировок для таблицы InnoDB .К избежать этой проблемы или просто свести к минимуму время, в течение которого стол остается заблокированным, следующая стратегия (которая не использует УДАЛИТЬ вообще) может быть полезно:

1. Выберите строки , а не , которые нужно удалить в пустая таблица, имеющая ту же структуру, что и исходная таблица:

     INSERT INTO t_copy SELECT * FROM t WHERE ...;  

2. Используйте RENAME TABLE , чтобы атомарно переместите исходную таблицу в сторону и переименуйте копию в исходное название:

     ПЕРЕИМЕНОВАТЬ ТАБЛИЦУ t TO t_old, t_copy TO t;  

3. Отбросьте исходную таблицу:

     ТАБЛИЦА ПАДЕНИЯ t_old;  

Никакие другие сеансы не могут получить доступ к задействованным таблицам, пока RENAME TABLE выполняется, поэтому Операция переименования не вызывает проблем с параллелизмом. Видеть Раздел 13.1.36, «Оператор RENAME TABLE».

Таблицы MyISAM

В таблицах MyISAM удаленные строки сохраняются. в связанном списке и последующих INSERT операций повторно используют старую строку позиции. Чтобы освободить неиспользуемое пространство и уменьшить размер файлов, используйте OPTIMIZE TABLE заявление или myisamchk Утилита для реорганизации таблиц. ТАБЛИЦА ОПТИМИЗАЦИИ проще в использовании, но myisamchk шустрее.Видеть Раздел 13.7.3.4, «Оператор OPTIMIZE TABLE» и Раздел 4.6.4, «myisamchk - Утилита обслуживания таблиц MyISAM».

Модификатор QUICK влияет на то, покидает ли индекс объединяются для операций удаления. УДАЛИТЬ БЫСТРО наиболее полезен для приложений, в которых значения индекса для удаленных строки заменяются аналогичными значениями индекса из вставленных строк позже. В этом случае повторно используются дыры, оставленные удаленными значениями.

БЫСТРОЕ УДАЛЕНИЕ не используется при удалении значений приводят к неполному заполнению индексных блоков, охватывающих диапазон значений индекса для которых снова появляются новые вставки.В этом случае использование QUICK может привести к потере места в индексе что остается невостребованным. Вот пример такого сценария:

1. Создайте таблицу, содержащую индексированный AUTO_INCREMENT столбец.

2. Вставьте много строк в таблицу. Каждая вставка приводит к значение индекса, которое добавляется к верхнему пределу индекса.

3. Удалить блок строк в нижнем конце диапазона столбцов с помощью УДАЛИТЬ БЫСТРО .

В этом сценарии индексные блоки, связанные с удаленным значения индекса не заполняются, но не объединяются с другими index блоков за счет использования QUICK . Они остаются не заполненными при новых вставках, потому что новые строки не имеют значения индекса в удаленном диапазоне. Кроме того, они остаются недостаточно заполнен, даже если вы позже воспользуетесь УДАЛИТЬ без QUICK , если только некоторые из удаленных значений индекса оказываются в индексных блоках внутри или рядом с недозаливные блоки.Чтобы освободить неиспользуемое индексное пространство под этими обстоятельства, используйте OPTIMIZE TABLE .

Если вы собираетесь удалить много строк из таблицы, это может быть быстрее использовать УДАЛИТЬ БЫСТРО с последующим ТАБЛИЦА ОПТИМИЗАЦИИ . Это восстанавливает index вместо выполнения многих операций слияния блоков индекса.

Удаление нескольких таблиц

Вы можете указать несколько таблиц в DELETE инструкция для удаления строк из одной или нескольких таблиц в зависимости от условия в WHERE clause. Вы не можете использовать ЗАКАЗАТЬ BY или LIMIT в многостоловой УДАЛИТЬ . В table_references пункт перечисляет таблицы, участвующие в объединении, как описано в Раздел 13.2.10.2, «Пункт JOIN».

Для первого синтаксиса с несколькими таблицами только соответствующие строки из таблицы, перечисленные перед предложением FROM , являются удалено. Для второго синтаксиса с несколькими таблицами только совпадающие строки из таблиц, перечисленных в пункте ИЗ (перед предложением USING ) удаляются.В эффект заключается в том, что вы можете удалять строки из многих таблиц одновременно time и есть дополнительные таблицы, которые используются только для поиска:

  УДАЛИТЬ t1, t2 ИЗ t1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t2 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t3
ГДЕ t1.id = t2.id И t2.id = t3.id;  

Или же:

  УДАЛИТЬ ИЗ t1, t2 ИСПОЛЬЗУЯ t1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t2 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t3
ГДЕ t1. id = t2.id И t2.id = t3.id;  

Эти операторы используют все три таблицы при поиске строк для удалить, но удалить совпадающие строки только из таблиц t1 и t2 .

В предыдущих примерах используется INNER JOIN , но несколько таблиц DELETE инструкции может использовать другие типы соединений, разрешенные в SELECT операторов, например ЛЕВОЕ СОЕДИНЕНИЕ . Например, чтобы удалить строки, существуют в t1 , которые не совпадают в t2 , используйте LEFT JOIN :

  УДАЛИТЬ t1 ИЗ t1 ЛЕВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2.id ГДЕ t2.id ЕСТЬ NULL;  

Синтаксис разрешает .* после каждого tbl_name для совместимости с Доступ .

Если вы используете многотабличный DELETE заявление с участием таблиц InnoDB , для которых есть ограничения внешнего ключа, оптимизатор MySQL может обрабатывают таблицы в порядке, отличном от их родительско-дочерние отношения. В этом случае утверждение не выполняется и откатывается. Вместо этого вы должны удалить из одной таблицы и полагаться на возможности ON DELETE , которые InnoDB обеспечивает, чтобы другие таблицы были изменен соответствующим образом.

Примечание

Если вы объявляете псевдоним для таблицы, вы должны использовать псевдоним, когда ссылаясь на таблицу:

  УДАЛИТЬ t1 ИЗ теста AS t1, test2 ГДЕ ...  

Псевдонимы таблиц в нескольких таблицах DELETE следует декларировать только в table_references часть утверждение. В других местах ссылки на псевдонимы разрешены, но не на псевдонимы. декларации.

Верный:

  УДАЛИТЬ a1, a2 ИЗ t1 КАК a1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t2 КАК a2
ГДЕ a1.id = a2.id;

УДАЛИТЬ ИЗ a1, a2 ИСПОЛЬЗУЯ t1 КАК a1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t2 КАК a2
ГДЕ a1. id = a2.id;  

Неправильно:

  УДАЛИТЬ t1 КАК a1, t2 КАК a2 ИЗ t1 ВНУТРЕННЕГО СОЕДИНЕНИЯ t2
ГДЕ a1.id = a2.id;

УДАЛИТЬ ИЗ t1 КАК a1, t2 КАК a2 ИСПОЛЬЗУЯ t1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ t2
ГДЕ a1.id = a2.id;  

Псевдонимы таблиц также поддерживаются для одиночных таблиц. DELETE операторов, начиная с MySQL 8.0.16. (Ошибка № 89410, Ошибка № 27455809)

Введение в оператор Delete

Оператор DELETE используется для удаления строк из таблицы данных SQL Server.В этой статье мы узнаем, как использовать оператор DELETE. Мы обсуждаем некоторые передовые практики, ограничения и подводим итоги на нескольких примерах.

Это четвертая статья в серии статей. Вы можете начать с самого начала, прочитав Введение в операторы изменения данных SQL Server.

Все примеры для этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks2012. Вы можете начать использовать эти бесплатные инструменты, используя мое Руководство Начало работы с SQL Server

Прежде чем мы начнем

Хотя в этой статье в качестве примеров используется база данных AdventureWorks, я решил создать примерную таблицу для использования в базе данных, чтобы лучше проиллюстрировать примеры. Здесь вы можете найти скрипт, который вам понадобится для запуска.

Кроме того, давайте сначала заполним таблицу некоторыми данными, используя следующий оператор INSERT:

 С topSalesPerson (FullName, SalesLastYear, City, rowguid)
В ВИДЕ (
ВЫБЕРИТЕ S.FirstName + '' + S.LastName, S.SalesLastYear, S.City, NEWID ()
ОТ Sales.vSalesPerson S
ГДЕ S.SalesLastYear> 1000000
)
ВСТАВИТЬ В esqlSalesPerson (FullName, SalesLastYear, City, rowguid)
       ВЫБЕРИТЕ FullName, SalesLastYear, City, rowguid
       ОТ topSalesPerson 

Вы можете узнать больше об операторе INSERT, прочитав нашу статью Введение в оператор INSERT.

Базовая структура инструкции DELETE

Оператор DELETE используется для изменения значений столбца.

Оператор DELETE состоит из трех компонентов:

1. Таблица, из которой нужно удалить строки.
2. Критерий, используемый для выбора строк для удаления.

Общий формат оператора DELECT:

 УДАЛИТЬ
ОТ tableName
ГДЕ searchCondition
… 

Теперь мы собираемся выполнить несколько примеров DELETE, поэтому, если вы еще этого не сделали, запустите сценарий, чтобы создать таблицу esqlSalesPerson.

Простой пример - удаление каждой строки

Когда вы используете DELETE без предложения WHERE, он удаляет каждую строку из таблицы. Если мы хотим удалить все из esqlSalesPerson, мы могли бы запустить:

 УДАЛИТЬ
ОТ esqlSalesPerson 

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

 НАЧАТЬ СДЕЛКУ
ВЫБРАТЬ СЧЕТЧИК (1) ОТ esqlSalesPerson

УДАЛИТЬ
ОТ esqlSalesPerson

ВЫБРАТЬ СЧЕТЧИК (1) ОТ esqlSalesPerson
ОТКАТ 

Первый оператор счета возвращает 13 строк; тогда как второй возвращает 0.Поскольку я ОТКАТИВАЮ транзакцию, операция удаления не является постоянной.

Простой пример - удаление одной строки

Более реалистичный вариант использования - удаление одной строки. Во многих приложениях это достигается путем фильтрации по одной строке.

Если вы знаете первичный ключ, вы золотой, поскольку первичный ключ уникален и предназначен для точной идентификации каждой строки.

Предположим, мы хотим удалить строку Джиллиан Карсон. Для этого мы выдадим следующее:

 УДАЛИТЬ
ОТ esqlSalesPerson
ГДЕ SalesPersonID = 10095 

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

Вы можете представить, что если бы у вас было веб-приложение, в котором перечислены все продавцы, сетка может содержать полное имя продавца и прошлогодние продажи, но в таблице также будет скрыт их SalesPersonID.

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

Простой пример - удаление нескольких строк

Предположим, мы хотим показать только высокоэффективных продавцов в таблице esqlSalesPerson.Мы только хотим, чтобы продавцы с прошлогодними продажами превышали или равнялись 2000000 долларов.

Поскольку наша таблица содержит те, у которых количество меньше этого, нам нужно удалить их, выполнив следующее:

 УДАЛИТЬ
ОТ esqlSalesPerson
ГДЕ Распродажа в прошлом году> 2000000. 00 

Вы можете запустить следующую команду, чтобы попробовать сценарий в транзакции:

 НАЧАТЬ СДЕЛКУ
ВЫБРАТЬ СЧЕТЧИК (1) ОТ esqlSalesPerson

УДАЛИТЬ
ОТ esqlSalesPerson
ГДЕ SalesLastYear <2000000.00

ВЫБЕРИТЕ FullName, SalesLastYear
ОТ esqlSalesPerson
ОТКАТ 

Вот результат запуска скрипта:

Синяя стрелка показывает, что изначально было 13 записей, а красная - оставшиеся, продажи которых превышают или равны двум миллионам долларов.

Вы также можете создавать более сложные условия фильтрации. Позже в статье мы покажем, как использовать подзапрос. Вы также можете использовать логические условия в предложении WHERE так же, как и в операторе SELECT.

Рекомендации по использованию оператора DELETE

Чтобы удалить все строки в таблице, используйте TRUNCATE TABLE. Это намного быстрее, чем DELETE, поскольку регистрирует изменения. Но есть ключевые отличия (см. Ниже DELETE - это не TRUNCATE!)

Также не забывайте, что вы можете использовать функцию @@ ROWCOUNT, чтобы узнать, сколько строк было удалено.

Обработка ошибок

Если оператор DELETE вызывает ошибку, все строки восстанавливаются до своего состояния до выполнения оператора. Если возникает ошибка, никакие строки не удаляются.

Есть много причин, по которым оператор удаления может не работать. Вот некоторые из наиболее типичных:

• Сиротские строки - если ограничение FOREIGN KEY определено между двумя таблицами, такими как parentTable и childTable, то DELETE строка parentTable вызовет ошибку, если существуют строки childTable, связанные с родительским. Чтобы решить эту проблему, сначала нужно удалить соответствующие строки childTable, а затем строку parentTable.
• Арифметические ошибки - Если оценка выражения приводит к арифметической ошибке, такой как деление на ноль, DELETE отменяется, и строки не удаляются.

Поведение при блокировке

Оператор удаления устанавливает исключительную (X) блокировку на таблицу. Это означает, что никакой другой запрос не может изменять данные таблицы до завершения транзакции DELETE.

Вы все еще можете читать данные, но вам нужно использовать подсказку NOLOCK или прочитать незафиксированный уровень изоляции.

DELETE не является TRUNCATE!

Удалить не TRUNCATE! Используйте DELETE, чтобы удалить одну или несколько строк из таблицы. TRUNCATE следует рассматривать только в особых случаях, например, когда вам нужно вернуть таблицу в исходное состояние.

Многие люди путают DELETE и TRUNCATE. ЕСЛИ вы не уверены, я бы порекомендовал прочитать мой пост на тему «В чем разница между усечением и удалением в SQL Server?»

Сложный пример - УДАЛЕНИЕ с использованием подзапроса

Вы также можете создать более сложный оператор удаления. Практически любое предложение, которое вы можете записать в предложение WHERE оператора SELECT, можно записать в оператор DELETE, включая подзапросы в предложении WHERE.

Приведем пример.

Предположим, вам нужно удалить всех продавцов, проживающих в США, из таблицы esqlSalesPerson. Хотя в нашей таблице есть город, в нем нет страны. Мы можем обойти это с помощью подзапроса.

 НАЧАТЬ СДЕЛКУ
ВЫБРАТЬ СЧЕТЧИК (1) ОТ esqlSalesPerson

УДАЛИТЬ
ОТ esqlSalesPerson
ГДЕ esqlSalesPerson.City В
       (ВЫБЕРИТЕ ОТЛИЧНЫЙ город
        ОТ Person.Address A
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.StateProvince S
              НА A.StateProvinceID = S.StateProvinceID
                 И С.CountryRegionCode = 'США')

ВЫБЕРИТЕ FullName, SalesLastYear
ОТ esqlSalesPerson
ОТКАТ 

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

Подзапрос окрашен в синий цвет. Обратите внимание, что он ничем не отличается от любого другого коррелированного подзапроса, который вы, возможно, использовали в прошлом.

Механизм этого оператора DELETE:

1. Найдите отдельные города для всех людей, живущих в США.
2. УДАЛИТЬ продавцов, город которых находится в этом списке.

Первый шаг является результатом подзапроса. На втором этапе удаляются строки согласно предложению WHERE.

Оператор SQL DELETE

Оператор SQL DELETE удаляет одну или несколько существующих записей из таблицы или представления. Синтаксис оператора удаления SQL Server:

 - SQL Server DELETE Syntax
УДАЛИТЬ ИЗ [Имя_таблицы]
  ГДЕ Условие 

Условие: Предоставьте фильтры или условия. Если условие истинно, то только оператор SQL Server DELETE удалит записи.

Таблица SQL Server для выполнения различных типов операций удаления:

SQL Delete Single record

Мы собираемся удалить одну запись из таблицы [SQL Delete]. Для этого мы используем предложение Where, чтобы ограничить оператор Delete удалением одной записи.

 ИСПОЛЬЗОВАНИЕ [Учебное пособие по SQL]
ИДТИ
УДАЛИТЬ ИЗ [Удалить SQL]
  WHERE [EmpID] = 2 

Он удалил запись сотрудника, значение ID которой = 103

SQL Удалить несколько записей

В этом примере удаления нескольких записей мы используем оператор AND в предложении Where для проверки нескольких условий, прежде чем он начнет удаление записывать.

 УДАЛИТЬ ИЗ [Удалить SQL]
  ГДЕ [Образование] = "Неполное среднее образование" И
        [YearlyIncome] = 45000 

Удалены все записи с неполным средним образованием и годовым доходом 45000

Верхнее предложение удаления SQL

В этом примере оператора удаления сервера Sql мы удаляем первую запись из таблицы, чей годовой доход меньше или равен 5000. Для этого примера сервера Sql мы используем предложение TOP.

 ИСПОЛЬЗОВАНИЕ [Учебное пособие по SQL]
ИДТИ
УДАЛИТЬ ВЕРХ (1)
FROM [Удаление SQL]
  WHERE [YearlyIncome] <= 50000 

Он проверит записи, годовой доход которых меньше или равен 5000, а затем TOP Clause выберет первую запись.

Удалить Присоединиться

Позвольте мне удалить записи из таблицы [SQL Delete] на основе названия отдела в другой таблице. Для этого мы используем внутреннее объединение вместе с оператором удаления SQL для объединения двух таблиц. Данные внутри таблицы отдела.

Удалить выписку КОД

 DELETE del
FROM [SQL Delete] AS del
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
      Отдел AS dept ON
  del.  [DeptID] = dept.id
ГДЕ dept.DepartmentName = 'Sr. Разработчик программного обеспечения '

Были удалены все записи, название отдела которых Sr.Разработчик программного обеспечения. Помните, что название отдела взято из таблицы отделов.

Удалить подзапрос

В этом операторе удаления SQL с примером подзапроса давайте удалим записи из таблицы [SQL Delete] на основе имени отдела в другой таблице. Для этого мы используем подзапрос.

 УДАЛИТЬ ИЗ [Удалить SQL]
ГДЕ [DeptID] IN
 (
   ВЫБРАТЬ идентификатор ИЗ отдела
     ГДЕ DepartmentName = 'Руководитель модуля'
 ) 

Удалены все записи из таблицы [SQL Delete], название отдела которой - Ведущий модуль.

SQL Удалить все столбцы

В этом примере оператора удаления сервера Sql мы собираемся удалить все столбцы

 USE [Учебное пособие по SQL]
ИДТИ
УДАЛИТЬ ИЗ [Удаление SQL] 

ПРИМЕЧАНИЕ. Если вы случайно забыли предложение Where, вы удалите все записи из источника.

Оператор удаления с помощью Management Studio

Если у вас есть доступ к Management Studio в SQL Server, используйте Intellisense для написания этого оператора удаления. Для этого щелкните правой кнопкой мыши таблицу -> Выбрать таблицу сценариев как -> Удалить в -> Новое окно редактора запросов

SQL сгенерирует оператор удаления для выбранной таблицы с кодом

 УДАЛИТЬ ИЗ [dbo].[Удалить SQL]
      ГДЕ <Условия поиска ,,>
GO 

Давайте добавим условие поиска

 DELETE FROM [dbo]. [SQL Delete]
      WHERE [EmpID] = 1 

SQL Server Удалить хранимую процедуру

Давайте посмотрим, как использовать инструкцию SQL Server Delete внутри хранимой процедуры. Здесь мы удаляем записи, у которых род занятий равен канцелярскому или их годовой доход меньше или равен 60000

 - SQL Server Удалить хранимую процедуру
ЕСЛИ OBJECT_ID ('sp_DeleteEmpRecords', 'P') НЕ ПУСТО
    ПРОЦЕДУРА УДАЛЕНИЯ sp_DeleteEmpRecords;
ИДТИ

СОЗДАТЬ ПРОЦЕДУРУ sp_DeleteEmpRecords
В ВИДЕ
НАЧИНАТЬ
УСТАНОВИТЬ NOCOUNT ON;

УДАЛИТЬ ИЗ [Удалить SQL]
ГДЕ Occupation = 'Clerical' ИЛИ
Годовой доход <= 60000
КОНЕЦ
GO 

  Сообщения
-------
Команды успешно выполнены.  

Позвольте мне использовать команду EXEC для выполнения хранимой процедуры удаления

 EXEC dbo.sp_DeleteEmpRecords
GO 

  Сообщения
-------
Команды успешно выполнены.  

Теперь давайте посмотрим, удаляло ли выполнение хранимой процедуры записи в нашей таблице или нет.

 - Выбрать все записи из таблицы после выполнения SP
ВЫБЕРИТЕ [EmpID], [Имя], [Фамилия], [Образование], [Профессия],
   [Годовой доход], [Продажи], [Дата найма], [DeptID]
FROM [SQL Delete] 

Delete Between example

В этом примере инструкции удаления сервера Sql используется оператор BETWEEN для удаления записей между двумя датами.

 УДАЛИТЬ
FROM [Удаление SQL]
ГДЕ [HireDate] МЕЖДУ '2016-01-27- И' 2007-01-28 '

  Сообщений
-------
(Затронуто 3 строки)  

Приведенный выше запрос удалит все записи, дата найма которых находится между 27 января 2006 г. и 28 января 2007 г.

Пример удаления SQL, если существует

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