Sql delete from where: DELETE (Transact-SQL)

Содержание

Оператор SQL DELETE — Как безопасно удалить записи из базы данных

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

Выполнение инструкции SQL DELETE

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

В пост о INSERT под номером сотрудника 9-9-9-9-0-3 мы добавили информацию о Джонатане Крике. Выберем его запись в таблице «Сотрудники».

Отлично — мы можем видеть его «дату рождения», «пол» и «дату найма».

Теперь посмотрим, какая информация содержится об этом же сотруднике в таблице «Титулы».

Отлично! Его должность «старший инженер», и он начал работать на 1

ст от октября 1997 г. Отсутствие информации в графе to_date свидетельствует о том, что он все еще работает в компании. Хороший!

Как мы упоминали в начале руководства, синтаксис, который следует соблюдать при удалении информации, — это DELETE FROM, имя таблицы и WHERE с соответствующим условием.

В нашем случае код будет УДАЛИТЬ ИЗ «Сотрудников», ГДЕ «Номер сотрудника» — 9-9-9-9-0-3.

Что должно произойти, когда мы запустим этот запрос, так это то, что только строка с номером сотрудника 9-9-9-9-0-3 будут удалены.

Давайте посмотрим, так ли это, выполнив этот оператор DELETE, а затем выбрав запись из таблицы «Сотрудники», указав то же условие в предложении WHERE.

Итак… какой вывод появится?

Пустая запись. Это означает, что мы надлежащим образом удалили информацию о Джонатане Крике.

Потрясающий! Как вы думаете… можем ли мы все еще видеть, что он был старшим инженером, нанятым 19 октября?97? Придется проверить, что осталось в таблице «Заголовки».

Хм… тоже пусто. Почему? Разве мы не заказывали команду DELETE только для таблицы «Сотрудники»? Ответ заключается в связи между двумя таблицами.

Затем проверяем информацию DDL о таблице «Titles».

Мы видим в ограничении внешнего ключа, что у нас также есть предложение ON DELETE CASCADE.

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

Предположим, что мы удалили информацию о Джонатане по ошибке. Есть ли способ вернуться? Учитывая, что мы применили оператор COMMIT в начале поста, то да, есть. Мы должны иметь возможность запустить команду ROLLBACK.

Выполним… ок!

А теперь давайте проверим, что запись вернулась в таблицу. Это в таблице «Сотрудники»… ок…

И… он есть и у нас в «Титлах».

Итак, наша последняя операция COMMIT хорошо справилась с сохранением всего набора данных — начального большого набора данных вместе с тремя записями, которые мы добавили в разделе INSERT.

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

Небезопасная операция удаления

Если мы не установим условие в предложении WHERE оператора DELETE, мы сильно рискуем. Это потенциально может привести к удалению всех записей таблицы. Вот почему мы должны быть очень осторожны при использовании этого утверждения. Всегда!

Вспомним, что у нас есть в таблице «Дубликаты отделов».

Номера и названия девяти отделов компании.

Давайте выполним оператор DELETE без прикрепленного к нему предложения WHERE.

Видишь? Были затронуты девять рядов.

Теперь мы можем еще раз проверить таблицу.

Пустее и быть не может!

Чтобы отменить изменения, мы должны выполнить оператор ROLLBACK. Хорошо?

И… получилось? Нам нужно будет выбрать всю информацию из этой крошечной таблицы, чтобы проверить, есть ли у нас обратно наши данные.

Вот оно! Ожидание получения!

Поэтому в заключение можно сказать следующее: Будьте осторожны с оператором DELETE. Не забудьте добавить условие в предложение WHERE, если вы не хотите потерять всю свою информацию.

В следующем разделе мы сравним DROP, DELETE и TRUNCATE.

DROP vs TRUNCATE vs DELETE

В этом разделе мы кратко обсудим основное различие между тремя зарезервированными словами: DROP, TRUNCATE и DELETE.

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

КАПЛЯ

Посмотрите на эту гипотетическую таблицу с 10 записями.

Если вы УДАЛИТЕ его, вы потеряете записи, таблицу как структуру и все связанные объекты, такие как индексы и ограничения. Вы потеряете все! Кроме того, вы не сможете вернуться к исходному состоянию или к последнему оператору COMMIT. Как только вы уроните стол, он исчезнет. В такой ситуации поможет только дополнительное программное обеспечение для восстановления данных, но оно не считается стандартным SQL-инструментом.

Следовательно, используйте DROP TABLE только в том случае, если вы уверены, что больше не собираетесь использовать данную таблицу.

ОБРЕЗАТЬ

TRUNCATE — это оператор, который по существу удалит все записи из таблицы, как если бы вы использовали DELETE без предложения WHERE. Это означает, что TRUNCATE удалит все записи в вашей таблице, но ее структура останется неизменной.

Имейте в виду, что при усечении значения автоинкремента будут сброшены. Итак, если в вашей таблице 10 записей, а затем вы усекаете ее, когда вы начнете повторно заполнять этот объект данных информацией, следующие записи, которые будут вставлены, будут не 11 и 12. Первая запись будет номер 1, вторая запись будет номер 2 и так далее. Хороший!

УДАЛИТЬ

Наконец, DELETE удаляет записи построчно. Будут удалены только строки, соответствующие определенному условию или условиям, указанным в предложении WHERE.

Если блок WHERE опущен, вывод будет похож на результат, полученный с помощью TRUNCATE. Однако будет несколько существенных отличий.

Во-первых, оптимизатор SQL реализует различные программные подходы, когда мы используем TRUNCATE или DELETE. В результате TRUNCATE выдает результат намного быстрее, чем DELETE, потому что не нужно удалять информацию построчно.

Во-вторых, значения автоинкремента не сбрасываются с помощью DELETE. Итак, если вы УДАЛИТЕ все 10 записей в этой таблице, а затем начнете вставлять новые данные, первая новая запись будет иметь номер 11, а не 1; второй будет 12, а не 2 и так далее.

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

Чтобы узнать больше об операторах SQL, ознакомьтесь с нашими руководствами Как использовать оператор Limit в SQL и когда использовать оператор SQL CASE.

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

***

Хотите отточить свои навыки SQL? Пройдите наш курс SQL.

Готовы сделать первый шаг к карьере в науке о данных? Ознакомьтесь с полной программой Data Science сегодня. Мы также предлагаем бесплатную предварительную версию программы Data Science Program. Вы получите 12 часов от начального до продвинутого контента бесплатно. Это отличный способ проверить, подходит ли вам программа.

Next Tutorial: Работа с операторами в SQL

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

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

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

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

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

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

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

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

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

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

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

 Создать таблицу заказов  ( OrderID int,   ProductName varchar(50),   ProductQuantity int   )

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

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

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

 Удалить заказы  Перейти   Удалить из заказов   GO

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

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

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

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

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

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

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

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

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

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

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

Поиск в таблице

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

Должны иметь соответствующие строки в таблице [dbo].[Клиент].

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

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

SQL Join

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

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

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

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

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

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

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

 С cteOrders AS  (ВЫБЕРИТЕ OrderID   ОТ Заказчика  , ГДЕ CustomerID = 1)   УДАЛИТЬ Заказы   ИЗ cteOrders sp   ВНУТРЕННЕЕ СОЕДИНЕНИЕ d bo.Orders o ON o.orderid = sp.orderid;

Влияние на диапазон идентификаторов

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

В приведенном ниже сценарии у нас есть таблица [Employee]. Эта таблица имеет идентификатор столбца идентификаторов.

 Создать таблицу Сотрудник  (   id int identity(1,1),   [Имя] varchar(50)   )

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

 Объявить @id int=1  Пока(@id<=50)   НАЧАТЬ   Вставить в Employee([Name]) values('Test'+CONVERT(VARCHAR,@ID))   Set @id=@id+1   END

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

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

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

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

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

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

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

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

Важные замечания

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

Штирлиц — Сеть печатных салонов в Перми

Sql delete from where: DELETE (Transact-SQL) — SQL Server