Запрос на удаление записей sql. Удаление записей из базы данных SQL

DELETE - это DM-операция удаления записей из таблицы. Критерий отбора записей для удаления определяется выражением WHERE . В случае, если критерий отбора не определён, выполняется удаление всех записей. Синтаксис:

DELETE FROM ;

Более быстро операцию удаление всех строк из таблицы можно в Transact-SQL также выполнить с помощью команды:

TRUNCATE TABLE

Transact-SQL (T-SQL) - процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE.

Примеры работы оператора

Пример 1. Требуется удалить из таблицы Laptop все портативные компьютеры с размером экрана менее 15 дюймов.

DELETE FROM Laptop WHERE screen

Все блокноты можно удалить с помощью оператора:

DELETE FROM Laptop;

Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM:

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

Пример 2. Пусть требуется удалить те модели ноутбуков из таблицы Product, для которых нет соответствующих строк в таблице Notebook. Используя стандартный синтаксис, задача решается так:

DELETE FROM Notebook WHERE type = "nb" AND model NOT IN (SELECT model FROM NB);

Предикат type = "Nb" необходим здесь, чтобы не были удалены также модели принтеров, сканеров и персональных компьютеров.

Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом:

DELETE FROM Notebook FROM Product pr LEFT JOIN NB ON pr.model = NB.model WHERE type = "nb" AND NB.model IS NULL;

Здесь применяется внешнее соединение, в результате чего столбец NB.model для моделей ноутбуков, отсутствующих в таблице NB, будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.

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

Для начала SQL-запрос на добавление новой записи в таблицу :

INSERT INTO users (login, pass) values("TestUser", "123456")

При добавлении записи вначале идёт команда "INSERT INTO ", затем название таблицы, в которую мы вставляем запись. Далее идёт в круглых скобках названия полей, которые мы хотим заполнить. А затем в круглых скобках после слова "values " начинаем перечислять значения тех полей, которые мы выбрали. После выполнения этого запроса в нашей таблице появится новая запись.

Иногда требуется обновить запись в таблице , для этого существует следующий SQL-запрос :

UPDATE users SET login = "TestUser2", pass="1234560" WHERE login="TestUser"

Данный запрос является более сложным, так как он имеет конструкцию "WHERE ", но о ней чуть ниже. Вначале идёт команда "UPDATE ", затем имя таблицы, а после "SET " мы описываем значения всех полей, которые мы хотим изменить. Было бы всё просто, но встаёт вопрос: "А какую именно запись следует обновлять? ". Для этого существует "WHERE ". В данном случае мы обновляем запись, поле "login " у которой имеет значение "TestUser ". Обратите внимание, что если таких записей будет несколько, то обновятся абсолютно все ! Это очень важно понимать, иначе Вы рискуете потерять свою таблицу.

Давайте немного ещё поговорим о "WHERE ". Помимо простых проверок на равенство существуют так же и неравенства, а также логические операции: AND и OR .

UPDATE users SET login = "TestUser2", pass="1234560" WHERE id < 15 AND login="TestUser"

Данный SQL-запрос обновит те записи, id которых меньше 15 И поле "login " имеет значение "TestUser ". Надеюсь, Вы разобрались с конструкцией "WHERE ", потому что это очень важно. Именно "WHERE " используется при выборке записей из таблиц , а это самая частоиспользуемая задача при работе с базами данных.

И, напоследок, простой SQL-запрос на удаление записей из таблицы :

DELETE FROM users WHERE login="TestUser2"

После команды "DELETE FROM " идёт имя таблицы, в которой требуется удалить записи. Дальше описываем конструкцию "WHERE". Если запись будет соответствовать описанным условиям, то она будет удалена. Опять же обратите внимание, в зависимости от количества записей, удовлетворяющих условию после "WHERE ", может удалиться любое их количество.

Команда TRUNCATE имеет следующий синтаксис:

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

Пример 1

Если нужно полностью очистить таблицу tovar, то следует выполнить команду:

TRUNCATE TABLE tovar;

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

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

  • Удаление из одной таблицы;
  • Каскадное удаление из нескольких таблиц.

Удаление из одной таблицы

DELETE FROM имя_таблицы

;

  • Если указана инструкция LOW_PRIORITY, то удаление не произойдет, пока все пользователи не завершат чтение таблицы.
  • Инструкция QUICK запрещает объединять индексы при выполнении удаления. Это несколько ускоряет процесс удаления.
  • Инструкция ORDER BY позволяет упорядочить записи перед удалением. Сама по себе эта опция не имеет смысла. Ее полезно использовать в сочетании с инструкцией LIMIT.
  • Инструкция LIMIT задает количество удаляемых записей.

Каскадное удаление

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

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

Для таблиц InnoDB при построении внешнего ключа всегда описывается реакция СУБД на удаление связанных записей. Реакция может быть трех видов:

  • RESTRICT – ограничение;
  • CASCADE – каскадное удаление;
  • SET NULL – установить значение NULL вместо значений удаленного внешнего ключа.

Пример 4

Пусть имеется таблица tovar, содержащая внешний ключ - categ со ссылкой на таблицу category.

Таким образом, каждый товар относится к определенной категории:

Необходимо удалить категорию «Лакокрасочные» из таблицы category и каскадно удалить все связанные с ней записи из таблицы tovar. Если во время создания внешнего ключа categ была указана реакция CASCADE при удалении, то для решения задачи достаточно выполнить команду:

DELETE FROM category WHERE category.categ_name=”лакокрасочные”;

Все товары, относящиеся к категории «лакокрасочные» автоматически удалятся из связанной таблицы tovar.

Однако, если пользователь работает с таблицами MyIsam, то СУБД не знает ни о каких внешних ключах и каскадного удаления не произойдет. В этом случае пригодятся специальные формы команды DELETE, предназначенные для каскадного удаления:

DELETE имя_таблицы [ ,имя_таблицы...]

DELETE

FROM имя_таблицы, [имя_таблицы...] USING таблица_ссылка

Пример 5

Задачу из примера 4 можно решить двумя эквивалентными командами:

DELETE tovar, category FROM tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;

DELETE from tovar, category using tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;

Замечание 1

На первый взгляд кажется, что команды

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

DELETE FROM имя_таблицы;

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

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

Эта глава представляет команды которые управляют значениями представляемыми в таблице. Когда вы закончите эту главу, вы будете способны помещать строки в таблицу, удалять их, и изменять индивидуальные значения представленные в каждой строке. Будет показано использование запросов в формировании полной группы строк для вставки, а также, как может использоваться предикат для управления изменения значений и удаления строк. Материал в этой главе составляет полный объем знаний показывающий, как создавать и управлять информацией в базе данных. Более мощные способы проектировани предикатов будут обсуждены в следующей главе .

КОМАНДЫ МОДИФИКАЦИИ ЯЗЫКА DML

Значения могут быть помещены и удалены из полей, трем командами языка DML (Язык Манипулирования Данными): INSERT (ВСТАВИТЬ), UPDATE (МОДИФИЦИРОВАТЬ), DELETE (УДАЛИТЬ). Не смущайтесь, все они упоминались ранее в SQL, как команды модификации.

ВВОД ЗНАЧЕНИЙ

Все строки в SQL вводятся с использованием команды модификации INSERT. В самой простой форме, INSERT использует следующий синтаксис: INSERT INTO VALUES (, . . .); Так, например, чтобы ввести строку в таблицу Продавцов, вы можете использовать следующее условие: INSERT INTO Salespeople VALUES (1001, "Peel", "London", .12); Команды DML не производят никакого вывода, но ваша программа должна дать вам некоторое подтверждение того что данные были использованы.

Им таблицы (в нашем случае - Salespeople (Продавцы)), должно быть предварительно определено, в команде CREATE TABLE (см. Главу 17), а каждое значение пронумерованное в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется. В ANSI, эти значения не могут составлять выражений, что означает что 3 - это доступно, а выражение 2 + 1 - нет. Значения, конечно же, вводятся в таблицу в поименном порядке, поэтому первое значение с именем, автоматически попадает в столбец 1, второе в столбец 2, на так далее.

ВСТАВКА ПУСТЫХ УКАЗАТЕЛЕЙ (NULL)

Если вам нужно ввести пустое значение(NULL), вы вводите его точно так- же как и обычное значение. Предположим, что еще не имелось пол city для мистера Peel. Вы можете вставить его строку со значением=NULL в это поле, следующим образом: INSERT INTO Salespeople VALUES (1001, "Peel", NULL, .12); Так как значение NULL - это специальный маркер, а не просто символьное значение, он не включается в одиночные кавычки.

ИМЕНОВАНИЕ СТОЛБЦА ДЛЯ ВСТАВКИ (INSERT)

Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке. Предположим что вы берете значения для таблицы Заказчиков из отчета выводимого на принтер, который помещает их в таком порядке: city, cname, и cnum, и для упрощения, вы хотите ввести значения в том же порядке: INSERT INTO Customers (city, cnamе, cnum) VALUES ("London", "Honman", 2001); Обратите внимание что столбцы rating и snum - отсутствуют. Это значит, что эти строки автоматически установлены в значение - по умолчанию. По умолчанию может быть введено или значение NULL или другое значе- ние определяемое как - по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце, и этот столбец не установлен как по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT которая относится к таблице(смотри Главу 18 для информации об ограничениях на NULL и на "по умолчанию").

ВСТАВКА РЕЗУЛЬТАТОВ ЗАПРОСА

Вы можете также использовать команду INSERT чтобы получать или вы- бирать значения из одной таблицы и помещать их в другую, чтобы использовать их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос: INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = "London"; Здесь выбираются все значения произведенные запросом - то есть все строки из таблицы Продавцов со значениями city = "London" - и помещаются в таблицу называемую Londonstaff. Чтобы это работало, таблица Londonstaff должна отвечать следующим условиям:

* Она должна уже быть создана командой CREATE TABLE.

* Она должна иметь четыре столбца которые совпадают с таблицей

Продавцов в терминах типа данных; то есть первый, второй, и так далее, столбцы каждой таблицы, должны иметь одинаковый тип данных (причем они не должны иметь одинаковых имен).

Общее правило то, что вставляемые столбцы таблицы, должны совпадать со столбцами выводимыми подзапросом, в данном случае, для всей таб- лицы Продавцов. Londonstaff - это теперь независима таблица которая получила некоторые значения из таблицы Продавцов(Salespeople). Если значения в таблице Продавцов будут вдруг изменены, это никак не отразится на таблице Londonstaff (хотя вы могли бы создать такой эффект, с помощью Представ- лени(VIEW), описанного в Главе 20). Так как или запрос или команда INSERT могут указывать столбцы по имени, вы можете, если захотите, переместить только выбранные столбцы а так- же переупорядочить только те столбцы которые вы выбрали.

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

Понимая что таблица Порядков охватывает последний финансовый год, а не только несколько дней, как в нашем примере, вы можете видеть преимущество использования следующего условия INSERT в подсчете и вводе значений INSERT INTO Daytotals (date, total) SELECT odate, SUM (amt) FROM Orders GROUP BY odate; Обратите внимание что, как предложено ранее, имена столбцов таблицы Порядков и таблицы Daytotals - не должны быть одинаковыми. Кроме того, если дата приобретения и общее количество - это единственные столбцы в таблице, и они находятся в данном порядке, их имена могут быть исключены из вывода из-за их очевидной простоты.

УДАЛЕНИЕ СТРОК ИЗ ТАБЛИЦ

Вы можете удалять строки из таблицы командой модификации - DELETE. Она может удалять только введенные строки, а не индивидуальные значе- ни полей, так что параметр пол является необязательным или недоступным. Чтобы удалить все содержание таблицы Продавцов, вы можете ввести следующее условие: DELETE FROM Salespeople; Теперь когда таблица пуста ее можно окончательно удалить командой DROP TABLE (это объясняется в Главе 17). Обычно, вам нужно удалить только некоторые определенные строки из таб- лицы. Чтобы определить какие строки будут удалены, вы используете предикат, так же как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести DELETE FROM Salespeople WHERE snum = 1003; Мы использовали поле snum вместо пол sname потому, что это лучшая тактика при использовании первичных ключей когда вы хотите чтобы действию подвергалась одна и только одна строка. Для вас - это аналогично действию первичного ключ. Конечно, вы можете также использовать DELETE с предикатом который бы выбирал группу строк, как показано в этом примере: DELETE FROM Salespeople WHERE city = "London";

ИЗМЕНЕНИЕ ЗНАЧЕНИЙ ПОЛЯ

Теперь, когда вы уже можете вводить и удалять строки таблицы, вы должны узнать как изменять некоторые или все значения в существующей строке. Это выполняется командой UPDATE. Эта команда содержит предложение UPDATE в которой указано им используемой таблицы и предложение SET которое указывает на изменение которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, вы можете ввести UPDATE Customers SET rating = 200;

МОДИФИЦИРОВАНИЕ ТОЛЬКО ОПРЕДЕЛЕННЫХ СТРОК

Конечно, вы не всегда захотите указывать все строки таблицы для изменения единственного значения, так что UPDATE, наподобие DELETE, может брать предикаты. Вот как например можно выполнить изменение одинаковое для всех заказчиков продавца Peel (имеющего snum=1001): UPDATE Customers SET rating = 200 WHERE snum = 1001;

КОМАНДА UPDATE ДЛЯ МНОГИХ СТОЛБЦОВ

Однако, вы не должны, ограничивать себя модифицированием единственного столбца с помощью команды UPDATE. Предложение SET может назначать любое число столбцов, отделяемых запятыми. Все указанные назначения могут быть сделаны для любой табличной строки, но только для одной в каждый момент времени. Предположим, что продавец Motika ушел на пенсию, и мы хотим переназначить его номер новому продавцу: UPDATE Salespeople SET sname = "Gibson",city = "Boston",comm = .10 WHERE snum = 1004; Эта команда передаст новому продавцу Gibson, всех текущих заказчиков быв- шего продавца Motika и порядки, в том виде в котором они были скомпонованы для Motika с помощью пол snum. Вы не можете, однако, модифицировать сразу много таблиц в одной команде, частично потому, что вы не можете использовать префиксы таблицы со столбцами измененными предложением SET. Другими словами, вы не можете сказать - "SET Salespeople.sname = Gibson" в команде UPDATE, вы можете сказать только так - "SET sname = Gibson".

ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ ДЛЯ МОДИФИКАЦИИ

Вы можете использовать скалярные выражения в предложении SET команды UPDATE, однако, включив его в выражение пол которое будет изменено. В этом их отличие от предложения VALUES команды INSERT, в котором выражения не могут использоваться; это свойство скалярных выражений - весьма полезна особенность. Предположим, что вы решили удвоить комиссионные всем вашим продавцам. Вы можете использовать следующее выражение: UPDATE Salespeople SET comm = comm * 2; Всякий раз, когда вы ссылаетесь к указанному значению столбца в предложении SET, произведенное значение может получится из текущей строки, прежде в ней будут сделаны какие-то изменения с помощью команды UPDATE. Естественно, вы можете скомбинировать эти особенности, и сказать, - удвоить комиссию всем продавцам в Лондоне, таким предложением: UPDATE Salespeople SET comm = comm * 2 WHERE city = "London";

МОДИФИЦИРОВАНИЕ ПУСТЫХ(NULL) ЗНАЧЕНИЙ

Предложение SET - это не предикат. Он может вводить пустые NULL значения также как он вводил значения не используя какого-то специального синтаксиса (такого например как IS NULL). Так что, если вы хотите установить все оценки заказчиков в Лондоне в NULL, вы можете ввести следующее предложение: UPDATE customers SET rating = NULL WHERE city = "London"; что обнулит все оценки заказчиков в Лондоне.

РЕЗЮМЕ

Теперь вы овладели мастерством управления содержанием вашей базы данных с помощью трех простых команд: INSERT - используемой чтобы помещать строки в базу данных; DELETE - чтобы удалять их; REFERENCES - чтобы изменять значения в уже вставленных строках. Вы обучались использованию предиката с командами UPDATE и DELETE чтобы определить, на которую из строк будет воздействовать команда. Конечно, предикаты как таковые - не значимы для INSERT, потому что обсуждаемая строка не существует в таблице до окончания выполнения команды INSERT. Однако, вы можете использовать запросы с INSERT, чтобы сразу помещать все наборы строк в таблицу. Причем это, вы можете делать со столбцами в любом порядке. Вы узнали, что значения по умолчанию, могут помещаться в столбцы, если вы не устанавливаете это значение явно. Вы также видели использование стандартного значения по умолчанию, которым является NULL. Кроме того, вы поняли, что UPDATE может использовать выражение значения, тогда как INSERT не может. Следующая глава расширит ваше познания, показав вам, как использовать подзапросы с этими командами. Эти подзапросы напоминают те, с которыми вы уже знакомы, но имеются некоторые специальные выводы и ограничения, когда подзапросы используются в командах DML, что мы будем обсуждать в Главе 16 .

РАБОТА С SQL

1. Напишите команду которая бы поместила следующие значения, в их нижеуказанном порядке, в таблицу Продавцов: city - San Jose, name - Bianco, comm - NULL, cnum - 1100. 2. Напишите команду которая бы удалила все порядки заказчика Clemens из таблицы Порядков.

3. Напишите команду которая бы увеличила оценку всех заказчиков в Риме на 100.

4. Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

Удаление записей

Для удаления записей из таблицы применяется оператор DELETE:

DELETE FROM имяТаблицы WHERE условие;

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

Следующий запрос удаляет записи из таблицы Customer, в которой значение столбца LName равно "Иванов":

DELETE FROM Customer

WHERE LName = "Иванов"

Если таблица содержатся сведения о нескольких клиентах с фамилией Иванов, то все они будут удалены.

В операторе WHERE может находиться подзапрос на выборку данных (оператор SELECT). Подзапросы в операторе DELETE работают точно так же, как и в операторе SELECT. Следующий запрос удаляет всех клиентов из города Москва, при этом уникальный идентификатор города возвращается с помощью подзапроса.

DELETE FROM Customer

WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = "Москва" )

Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции DELETE еще одно предложение FROM. Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк. Оно позволяет задавать данные из второго FROM и удалять соответствующие строки из таблицы в первом предложении FROM. В частности предыдущий запрос может быть переписан следующим образом

DELETE FROM Customer

FROM Customer k INNER JOIN

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

SELECT *

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity AND c.CityName = "Москва"

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

DELETE FROM Product

Задание для самостоятельной работы: Сформулируйте на языке SQL запрос на удаление всех заказов, не имеющих в составе ни одного товара (т. е. все пустые заказы).

rustrackers.ru - Бесплатные программы для вашего ПК