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, значение счетчика сбрасывается и если после этого добавить строку, то значение автоматически увеличиваемого поля начнет свой отсчет с единицы. Учитывайте этот эффект при использовании определенного оператора удаления данных.