Команда SQL для удаления и обновление данных в базе (DELETE, UPDATE)
В базу данных можно не только добавлять данные, но и удалять их оттуда. Ещё существует возможность обновить данные в базе. Рассмотрим оба случая.
Удаление данных из базы (DELETE)
Удаление из базы данных происходит с помощью команды «DELETE» (переводится с английского как «УДАЛИТЬ»). Функция удаляет не одну строку, а несколько, при этом выбирает для удаления строки по логике функции «SELECT». То есть чтобы удалить данные из базы, необходимо точно определить их. Приведём пример SQL команды для удаления одной строчки:
DELETE FROM `USERS` WHERE `ID` = 2 LIMIT 1;Благодаря этому запросу из таблицы «USERS» будет удалена одна запись, у которой в столбце «ID» стоит значение «2».
Обратите внимание, что в конце запроса стоит лимит на выборку «LIMIT 1;» размером в 1 строку. Его можно было не ставить, если поле «ID» является «PRIMARY KEY» (первичный ключ, то есть содержит только уникальные значения). Но всё-таки рекомендуем ставить ограничение «LIMIT 1;» в любом случае, если вы намерены удалить только одну строчку.
Теперь попробуем удалить сразу диапазон данных. Для этого достаточно составить запрос, в результате выполнения которого вернётся несколько строк. Все эти строки будут удалены:
DELETE FROM `USERS` WHERE `ID` >= 5;Этот запрос удалит все строки в таблицы, у которых в столбце «ID» стоит значение меньше 5. Если не поставить никакого условия «WHERE» и лимита «LIMIT», то будут удалены абсолютно все строки в таблице:
DELETE FROM `USERS`;На некоторых версиях MySQL способ удаления всех строк через «DELETE FROM _;» может работать медленнее, чем «TRUNCATE _;«; Поэтому для очистки всей таблицы лучше всё-таки использовать «TRUNCATE».
Обновление данных в базе (UPDATE)
Функция обновления «UPDATE» (переводится с английского как «ОБНОВИТЬ») довольно часто используется в проектах сайтов. Как и в случае с функцией «DELETE», фкнция обновления не успокоится до тех пор, пока не обновит все поля, которые подходят под условия, если нет лимита на выборку. Поэтому необходимо задавать однозначные условия, чтобы вместо одной строки нечаянно не обновить половину таблицы. Приведём пример использования команды «UPDATE»:
UPDATE `USERS` SET `NAME` = 'Мышь' WHERE `ID` = 3 LIMIT 1;В этом примере, в таблие «USERS» будет установлено значение «Мышь» в столбец «NAME» у строки, в столбце «ID» которой стоит значение «3». Можно обновить сразу несколько столбцов у одной записи, передав значения через запятую. Попробуем обновить не только значение с толбце «NAME», но и значение в столбце «FOOD» используя один запрос:
UPDATE `USERS` SET `NAME` = 'Мышь', `FOOD` = 'Сыр' WHERE `ID` = 3 LIMIT 1;Если не поставить никаких лимитов LIMIT и условий WHERE, то все записи таблицы будут обновлены без исключений.
Оператор DELETE стр. 1
Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис:
Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) можно в Transact-SQL (T-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE. Transact-SQL также выполнить с помощью команды
Однако есть ряд особенностей в реализации команды TRUNCATE TABLE , которые следует иметь в виду:
- не журнализируется удаление отдельных строк таблицы; в журнал записывается только освобождение страниц, которые были заняты данными таблицы;
- не отрабатывают триггеры, в частности, триггер на удаление;
- команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу, и даже если внешний ключ имеет опцию каскадного удаления.
- значение счетчика ( IDENTITY ) сбрасывается в начальное значение.
Требуется удалить из таблицы Laptop все портативные компьютеры с размером экрана менее 12 дюймов.
Все блокноты можно удалить с помощью оператора
Transact-SQL расширяет синтаксис оператора DELETE , вводя дополнительное предложение FROM :
При помощи источника табличного типа можно конкретизировать данные, удаляемые из таблицы в первом предложении FROM .
При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк. Поясним сказанное на примере.
Пусть требуется удалить те модели ПК из таблицы Product, для которых нет соответствующих строк в таблице PC.
Используя стандартный синтаксис, эту задачу можно решить следующим запросом:
Заметим, что предикат type = ‘pc’ необходим здесь, чтобы не были удалены также модели принтеров и портативных компьютеров.
Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом:
Здесь применяется внешнее соединение, в результате чего столбец PC.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL -значение, что и используется для идентификации подлежащих удалению строк.
| Страницы: | 1 | 2 | 3 |
Оператор SQL DELETE для удаления данных из таблицы
Оператор SQL DELETE и удаление данных с условием
Оператор SQL DELETE предназначен для удаления данных из таблицы. Он имеет следующий синтаксис:
DELETE FROM ИМЯ_ТАБЛИЦЫ WHERE УСЛОВИЕ
Если не указывать условие, из таблицы будут удалены все строки. Кроме того, следует помнить, что могут быть удалены лишь строки с первичными ключами, на которые не ссылаются внешние ключи в других таблицах (более подробно об ограничениях удаления — в уроке Реляционная модель данных).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
А скрипт для создания базы данных «Портал объявлений 1», её таблицы и заполения таблицы данных — в файле по этой ссылке .
Пример 1. Итак, есть база портала объявлений. В ней есть таблица Ads, содержащая данные о объявлениях, поданных за неделю (более подробно — в уроке об агрегатных функциях SQL, пример 7). Таблица выглядит так:
| Id | Category | Part | Units | Money |
| 1 | Транспорт | Автомашины | 110 | 17600 |
| 2 | Недвижимость | Квартиры | 89 | 18690 |
| 3 | Недвижимость | Дачи | 57 | 11970 |
| 4 | Транспорт | Мотоциклы | 131 | 20960 |
| 5 | Стройматериалы | Доски | 68 | 7140 |
| 6 | Электротехника | Телевизоры | 127 | 8255 |
| 7 | Электротехника | Холодильники | 137 | 8905 |
| 8 | Стройматериалы | Регипс | 112 | 11760 |
| 9 | Досуг | Книги | 96 | 6240 |
| 10 | Недвижимость | Дома | 47 | 9870 |
| 11 | Досуг | Музыка | 117 | 7605 |
| 12 | Досуг | Игры | 41 | 2665 |
Требуется удалить из таблицы строку, имеющую идентификатор 4. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
DELETE FROM ADS WHERE align=»justify»>Пример 2. Можно удалить и несколько строк, если в условии применить оператор сравнения «больше» или «меньше» (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
DELETE FROM ADS WHERE Id>4
В результате в таблице останутся лишь следующие строки:
| Id | Category | Part | Units | Money |
| 5 | Стройматериалы | Доски | 68 | 7140 |
| 6 | Электротехника | Телевизоры | 127 | 8255 |
| 7 | Электротехника | Холодильники | 137 | 8905 |
| 8 | Стройматериалы | Регипс | 112 | 11760 |
| 9 | Досуг | Книги | 96 | 6240 |
| 10 | Недвижимость | Дома | 47 | 9870 |
| 11 | Досуг | Музыка | 117 | 7605 |
| 12 | Досуг | Игры | 41 | 2665 |
Пример 3. Аналогично можно удалять строки с заданными значениями любого столбца. Удалим, например, строки об объявлениях, за которые выручено менее 10000 денежных единиц (запрос на MS SQL Server — с предваряющей конструкцией USE adportal1;):
DELETE FROM ADS WHERE Money DELETE FROM ИМЯ_ТАБЛИЦЫ
Пример 4. Чтобы удалить все данные из таблицы ADS, достаточно написать следующий запрос:
DELETE FROM ADS
Если после выполнения этого запроса обратиться к таблице ADS при помощи оператора SELECT, применяемого для получения выборки данных, то будет выведено сообщение о том, что эта таблица не содержит данных.
Оператору DELETE без условий и ограничений аналогичен оператор TRUNCATE TABLE. Он также удаляет из таблицы все строки, но выполняется намного быстрее.
Пример 5. Запрос на удаление всех данных из таблицы ADS при помощи оператора TRUNCATE TABLE будет следующим (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
TRUNCATE TABLE ADS
Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, UPDATE, HAVING и UNION.
Практическое руководство. Как удалить строки из базы данных
Вы можете удалить строки в базе данных, удалив соответствующие объекты LINQ to SQL из коллекции, связанной с таблицами. LINQ to SQL преобразует изменения в соответствующие команды SQL DELETE .
LINQ to SQL не поддерживает и не распознает операции каскадного удаления. Если требуется удалить строку в таблице, имеющей ограничения, необходимо выполнить любую из следующих задач.
- Установите правило ON DELETE CASCADE в ограничении внешнего ключа в базе данных.
- Используйте собственный код, чтобы сначала удалить дочерние объекты, не допускающие удаление родительского объекта.
В противном случае создается исключение. См. второй пример кода далее в этом разделе.
Вы можете переопределить LINQ to SQL методы по умолчанию для Insert операций с базой данных , Update и Delete . Дополнительные сведения см. в разделе Настройка операций вставки, обновления и удаления.
Разработчики, использующие Visual Studio, могут использовать реляционный конструктор объектов для разработки хранимых процедур с той же целью.
В следующих шагах предполагается, что подключение к базе данных Northwind выполняется с помощью допустимого объекта DataContext. Дополнительные сведения см. в разделе Практическое руководство. Подключение к базе данных.
Удаление строки в базе данных
- Отправьте в базу данных запрос на удаляемую строку.
- Вызовите метод DeleteOnSubmit.
- Отправьте изменение в базу данных.
Пример 1
В первом примере кода в базу данных отправляется запрос на сведения о заказе «Order #11000», помечаются сведения для удаления, а изменения отправляются в базу данных.
// Query the database for the rows to be deleted. var deleteOrderDetails = from details in db.OrderDetails where details.OrderID == 11000 select details; foreach (var detail in deleteOrderDetails) < db.OrderDetails.DeleteOnSubmit(detail); >try < db.SubmitChanges(); >catch (Exception e) < Console.WriteLine(e); // Provide for exceptions. >' Query the database for the rows to be deleted. Dim deleteOrderDetails = _ From details In db.OrderDetails() _ Where details.OrderID = 11000 _ Select details For Each detail As OrderDetail In deleteOrderDetails db.OrderDetails.DeleteOnSubmit(detail) Next Try db.SubmitChanges() Catch ex As Exception Console.WriteLine(ex) ' Provide for exceptions End Try Пример 2
Целью второго примера является удаление заказа (с номером 10250). Код сначала проверяет таблицу OrderDetails на наличие в ней дочерних элементов удаляемого заказа. Если в таблице есть дочерние элементы заказа, сначала они, а затем сам заказ помечаются для удаления. DataContext располагает фактические действия по удалению в надлежащей последовательности, чтобы команды удаления, отправляемые в базу данных, подчинялись ее ограничениям.
Northwnd db = new Northwnd(@"c:\northwnd.mdf"); db.Log = Console.Out; // Specify order to be removed from database int reqOrder = 10250; // Fetch OrderDetails for requested order. var ordDetailQuery = from odq in db.OrderDetails where odq.OrderID == reqOrder select odq; foreach (var selectedDetail in ordDetailQuery) < Console.WriteLine(selectedDetail.Product.ProductID); db.OrderDetails.DeleteOnSubmit(selectedDetail); >// Display progress. Console.WriteLine("detail section finished."); Console.ReadLine(); // Determine from Detail collection whether parent exists. if (ordDetailQuery.Any()) < Console.WriteLine("The parent is present in the Orders collection."); // Fetch Order. try < var ordFetch = (from ofetch in db.Orders where ofetch.OrderID == reqOrder select ofetch).First(); db.Orders.DeleteOnSubmit(ordFetch); Console.WriteLine("OrderID is marked for deletion.", ordFetch.OrderID); > catch (Exception e) < Console.WriteLine(e.Message); Console.ReadLine(); >> else < Console.WriteLine("There was no parent in the Orders collection."); >// Display progress. Console.WriteLine("Order section finished."); Console.ReadLine(); try < db.SubmitChanges(); >catch (Exception e) < Console.WriteLine(e.Message); Console.ReadLine(); >// Display progress. Console.WriteLine("Submit finished."); Console.ReadLine(); Dim db As New Northwnd("c:\northwnd.mdf") db.Log = Console.Out ' Specify order to be removed from database. Dim reqOrder As Integer = 10252 ' Fetch OrderDetails for requested order. Dim ordDetailQuery = _ From odq In db.OrderDetails _ Where odq.OrderID = reqOrder _ Select odq For Each selectedDetail As OrderDetail In ordDetailQuery Console.WriteLine(selectedDetail.Product.ProductID) db.OrderDetails.DeleteOnSubmit(selectedDetail) Next ' Display progress. Console.WriteLine("Detail section finished.") Console.ReadLine() ' Determine from Detail collection whether parent exists. If ordDetailQuery.Any Then Console.WriteLine("The parent is present in the Orders collection.") ' Fetch order. Try Dim ordFetch = _ (From ofetch In db.Orders _ Where ofetch.OrderID = reqOrder _ Select ofetch).First() db.Orders.DeleteOnSubmit(ordFetch) Console.WriteLine(" OrderID is marked for deletion.,", ordFetch.OrderID) Catch ex As Exception Console.WriteLine(ex.Message) Console.ReadLine() End Try Else Console.WriteLine("There was no parent in the Orders collection.") End If ' Display progress. Console.WriteLine("Order section finished.") Console.ReadLine() Try db.SubmitChanges() Catch ex As Exception Console.WriteLine(ex.Message) Console.ReadLine() End Try ' Display progress. Console.WriteLine("Submit finished.") Console.ReadLine() См. также раздел
- Практическое руководство. Как управлять конфликтами изменений
- Практическое руководство. Назначение хранимых процедур для выполнения обновления, вставки и удаления (реляционный конструктор объектов)
- Внесение и отправка изменений данных