Delete sql синтаксис. Удаление данных в SQL. Команда update для многих столбцов
В этой статье мы разберём, пожалуй, одни из самых важных 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 ", может удалиться любое их количество.
ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse
Удаляет одну или несколько строк из таблицы или представления в SQL Server.
Syntax for SQL Server and Azure SQL Database
[ WITH
WITH <обобщенноетабличное
выражение>
Задает временный именованный результирующий набор, также называемый обобщенным табличным выражением, который определяется в области действия инструкции DELETE. Результирующий набор получается из инструкции SELECT.
Обобщенные табличные выражения также можно использовать в инструкциях SELECT, INSERT, UPDATE и CREATE VIEW. Дополнительные сведения см. в разделе .
TOP (expression
)
[ PERCENT ]
Задает количество или процент удаляемых случайных строк. expression
может быть либо числом, либо процентом от числа строк. Строки, на которые ссылается выражение TOP, используемое с инструкциями INSERT, UPDATE и DELETE, не упорядочиваются. Дополнительные сведения см. в разделе
.
FROM
Необязательное ключевое слово, которое можно использовать между ключевым словом DELETE и целевым table_or_view_name
, или rowset_function_limited
.
table_alias
Псевдоним, заданный в предложении table_source
предложение, представляющем таблицу или представление, из которой должны быть удалены строки.
имя_сервера
Имя сервера (с использованием имени связанного сервера или функционировать в качестве имени сервера) на котором расположена таблица или представление. Если имя_сервера указан, имябазы данных и schema_name являются обязательными.
имябазы
данных
Имя базы данных.
schema_name
Имя схемы, которой принадлежит таблица или представление.
table_or view_name
Имя таблицы или представления, откуда удаляются строки.
Табличную переменную в пределах ее области действия также можно использовать в качестве источника таблицы в инструкции DELETE.
Представление ссылается table_or_view_name должно быть обновляемым и ссылаться ровно одну базовую таблицу в предложении FROM определения представления. Дополнительные сведения об обновляемых представлениях см. в разделе .
rowset_function_limited
Возвращает удаленные строки или выражения, основанные на них, как часть операции DELETE. Предложение OUTPUT не поддерживается ни в каких инструкциях DML, направленных на представления и удаленные таблицы. Дополнительные сведения см. в разделе
.
ИЗ table_source
Задает дополнительное предложение FROM. Это Transact-SQL расширение DELETE позволяет задавать данные из
Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк.
Дополнительные сведения см. в разделе .
WHERE
Указывает условия, используемые для ограничения числа удаляемых строк. Если предложение WHERE не указывается, инструкция DELETE удаляет все строки из таблицы.
Предусмотрено два вида операций удаления в соответствии с тем, что указывается в предложении WHERE.
Операции удаления с поиском указывают условие поиска для уточнения строк, которые будут удалены. Например, ГДЕ column_name = значение .
Операции удаления по позиции используют предложение CURRENT OF для указания курсора. Удаление осуществляется в текущей позиции курсора. Это может быть более точной, чем инструкция DELETE по найденному, использующая предложение search_condition предложение для указания удаляемых строк. Инструкция DELETE по найденному удаляет несколько строк, если условие поиска не определяет уникально одну строку.
Указывает ограничивающие условия для удаляемых строк. Количество предикатов, которое может содержать условие поиска, не ограничено. Дополнительные сведения см. в разделе
.
CURRENT OF
Указывает выполнение инструкции DELETE в текущей позиции указанного курсора.
GLOBAL
Указывает, что cursor_name
ссылается на глобальный курсор.
cursor_name
Имя открытого курсора, из которого производится выборка. Если как глобальный, так и локальный курсор с именем cursor_name
существует, этот аргумент ссылается на глобальный курсор, если GLOBAL указанного; в противном случае, он ссылается на локальный курсор. Курсор должен позволять производить обновления.
cursor_variable_name
Имя переменной курсора. Переменная курсора должна содержать ссылку на курсор, обновления которого разрешены.
OPTION (
Ключевые слова, которые указывают, что подсказки оптимизатора применяются при настройке способа Компонент Database Engine обрабатывает инструкцию. Дополнительные сведения см. в разделе
.
Для удаления всех строк в таблице воспользуйтесь инструкцией TRUNCATE TABLE. Инструкция TRUNCATE TABLE выполняется быстрее, чем инструкция DELETE, и использует меньше системных ресурсов и ресурсов журнала транзакций. Инструкция TRUNCATE TABLE имеет ограничения, например, таблица не может участвовать в репликации. Дополнительные сведения см. в разделе
Используйте @@ROWCOUNT функцию для возврата количества удаленных строк клиентскому приложению. Дополнительные сведения см. в разделе .
Для инструкции DELETE можно реализовать обработку ошибок, заключив ее в конструкцию TRY…CATCH.
При выполнении инструкции DELETE может произойти ошибка, если она нарушает триггер или пытается удалить строку, на которую ссылаются данные в другой таблице с помощью ограничения FOREIGN KEY. Если инструкция DELETE удаляет несколько строк и одна из удаленных строк нарушает триггер или ограничение, то эта инструкция отменяется, т.е. возвращается ошибка и строки не удаляются.
В случае арифметической ошибки (переполнение, деление на ноль или выход за пределы допустимых значений), возникающей в ходе вычисления выражения при выполнении инструкции DELETE, компонент Компонент Database Engine будет обрабатывать эти ошибки, как если бы параметр SET ARITHABORT имел значение ON. Оставшаяся часть пакетной операции отменяется и возвращается сообщение об ошибке.
Инструкцию DELETE можно использовать в тексте определяемой пользователем функции, если изменяемым объектом является табличная переменная.
При удалении строки, содержащей столбец FILESTREAM, также удаляются и связанные с ней файлы файловой системы. Базовые файлы удаляются сборщиком мусора FILESTREAM. Дополнительные сведения см. в разделе .
Предложение FROM нельзя указывать в инструкции DELETE, ссылающейся (прямо или косвенно) на представление, в котором указан триггер INSTEAD OF. Дополнительные сведения о триггерах INSTEAD разделе .
При использовании выражения TOP в инструкции DELETE строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этой инструкции. Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции вложенного запроса выборки следует использовать ORDER BY. См. подраздел «Примеры» далее в этом разделе.
Предложение TOP не может быть использовано вместе с инструкцией DELETE для секционированных представлений.
По умолчанию инструкция DELETE всегда получает монопольную блокировку (X) на таблицу, которую она изменяет, и держит блокировку до тех пор, пока транзакция не завершится. Если ресурс удерживается монопольной (X) блокировкой, то другие транзакции не могут изменять данные. Операции считывания будут допускаться только при наличии подсказки NOLOCK или уровня изоляции незафиксированной операции чтения. Можно переопределить поведение оптимизатора запросов по умолчанию с помощью табличных подсказок на время выполнения инструкции DELETE указанием другого способа блокировки, но использовать подсказки рекомендуется только опытным разработчикам и администраторам баз данных и только при крайней необходимости. Дополнительные сведения см. в разделе .
Когда строки удаляются из кучи, компонент Компонент Database Engine может использовать строку или страницу блокировки для операции. В результате пустые страницы, в которых выполняются операции удаления, остаются размещенными для кучи. Если их не освободить, занимаемое ими место не может быть использовано под другие объекты базы данных.
Чтобы удалить из кучи строки и освободить страницы, воспользуйтесь одним из следующих методов.
Задайте указания TABLOCK в инструкции DELETE. Использование TABLOCK приведет к тому, что при выполнении операции удаления будет установлена монопольная блокировка таблицы, а не блокировка строки или страницы, что позволит освободить страницы. Дополнительные сведения о подсказке TABLOCK см. в разделе .
Если из таблицы удаляются все строки, пользуйтесь инструкцией TRUNCATE TABLE.
Перед удалением строк создайте в куче кластеризованный индекс. Потом его можно будет удалить. Этот метод потребует больше времени и потребляет больше временных ресурсов.
Инструкция DELETE всегда полностью регистрируется в журнале.
Permissions
Разрешения DELETE необходимы для целевой таблицы. Разрешения SELECT также необходимы, если инструкция содержит предложение WHERE.
Удаление разрешения по умолчанию для членов sysadmin фиксированной серверной роли db_owner и db_datawriter фиксированных ролей базы данных и владельца таблицы. Члены sysadmin , db_owner и db_securityadmin ролей, а также владелец таблицы могут передавать разрешения другим пользователям.
Базовый синтаксис
В примерах в этом разделе описывается базовая функциональность инструкции DELETE с помощью минимального необходимого синтаксиса.
А. Использование инструкции DELETE без предложения WHERE
Следующий пример удаляет все строки из таблицы SalesPersonQuotaHistory в базе данных AdventureWorks2012 , поскольку не указано предложение WHERE, ограничивающее количество удаляемых строк.
DELETE FROM Sales.SalesPersonQuotaHistory; GO
Ограничение удаляемых строк
В примерах в этом разделе описываются способы ограничения количества удаляемых строк.
A. Использование предложения WHERE для удаления набора строк
Следующий пример удаляет все строки таблицы ProductCostHistory в базе данных AdventureWorks2012 , у которых значение в столбце StandardCost больше 1000.00 .
В следующем примере показано использование более сложного предложения WHERE. Предложение WHERE определяет два условия, которые должны быть выполнены для определения удаляемых строк. Значение в столбце StandardCost должно быть в диапазоне от 12.00 до 14.00 , а значение в столбце SellEndDate должно быть равно NULL. В этом примере также выводится значение из **@@ROWCOUNT ** функцию для возврата количества удаленных строк.
Б. Использование курсора для определения удаляемой строки
Следующий пример удаляет одну строку из EmployeePayHistory в таблице AdventureWorks2012 базы данных с помощью курсора my _ cursor . Операция удаления затрагивает только одну строку, выбранную в данный момент курсором.
DECLARE complex_cursor CURSOR FOR SELECT a.BusinessEntityID FROM HumanResources.EmployeePayHistory AS a WHERE RateChangeDate <> (SELECT MAX (RateChangeDate) FROM HumanResources.EmployeePayHistory AS b WHERE a.BusinessEntityID = b.BusinessEntityID) ; OPEN complex_cursor; FETCH FROM complex_cursor; DELETE FROM HumanResources.EmployeePayHistory WHERE CURRENT OF complex_cursor; CLOSE complex_cursor; DEALLOCATE complex_cursor; GO
В. Использование операторов объединения и вложенных запросов к данным в одной таблице для удаления строк в другой таблице
В следующих примерах показано два способа удаления строк в одной таблице на основании данных в другой таблице. В обоих примерах строки из SalesPersonQuotaHistory в таблице AdventureWorks2012 удалить базу данных на основе продаж с начала года, хранящиеся в SalesPerson таблицы. Первый DELETE инструкции показано решение совместимый ISO вложенный запрос, а второй - DELETE инструкции Transact-SQL из расширения для соединения двух таблиц.
A. Ограничение числа удаляемых строк с помощью ключевого слова TOP
Когда TOP (n ) с помощью удаления используется предложение, операция удаления производится над случайно выбранных n строк. Следующий пример удаляет 20 случайных строк из таблицы PurchaseOrderDetail в базе данных AdventureWorks2012 , имеющих дату ранее 1 июля 2006 г.:
Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции вложенного запроса выборки следует использовать ORDER BY. Следующий запрос удаляет из таблицы PurchaseOrderDetail 10 строк, имеющих самую раннюю дату. Чтобы гарантировать удаление только 10 строк, столбец, указанный в инструкции подзапроса выборки (PurchaseOrderID) должен являться первичным ключом таблицы. Использование неключевого столбца в инструкции подзапроса выборки может привести к удалению более чем 10 строк, если указанный столбец содержит повторяющиеся значения.
Удаление строк из удаленной таблицы
В примерах в этом разделе описаны способы удаления строк из удаленной таблицы с использованием в качестве ссылки на удаленную таблицу или . Удаленная таблица существует на другом сервере или экземпляре SQL Server.
Применимо к : от SQL Server 2008 до SQL Server 2016. |
A. Удаление данных из удаленной таблицы с помощью связанного сервера
В следующем примере будет удалена строка из удаленной таблицы. Пример начинается с создания ссылки на удаленный источник данных с помощью . Имя связанного сервера, MyLinkServer , затем определяется как часть четырехчастного имени объекта в виде server.catalog.schema.object .
USE master; GO -- Create a link to the remote data source. -- Specify a valid server name for @datasrc as "server_name" or "server_name\instance_name" . EXEC sp_addlinkedserver @server = N"MyLinkServer" , @srvproduct = N" " , @provider = N"SQLNCLI" , @datasrc = N"server_name" , @catalog = N"AdventureWorks2012" ; GO
Б. Удаление данных из удаленной таблицы с помощью функции OPENQUERY
В следующем примере выполняется удаление строк из удаленной таблицы с помощью вызова функции , возвращающей набор строк. В этом примере используется имя связанного сервера, созданного в предыдущем примере.
В. Удаление данных из удаленной таблицы с помощью функции OPENDATASOURCE
В следующем примере выполняется удаление строк из удаленной таблицы с помощью вызова функции , возвращающей набор строк. Укажите допустимое имя сервера для источника данных с использованием формата имя_сервера или server_name\instance_name .
Удаление записей
Для удаления записей из таблицы применяется оператор 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 запрос на удаление всех заказов, не имеющих в составе ни одного товара (т. е. все пустые заказы).
Эта глава представляет команды которые управляют значениями представляемыми в таблице. Когда вы закончите эту главу, вы будете способны помещать строки в таблицу, удалять их, и изменять индивидуальные значения представленные в каждой строке. Будет показано использование запросов в формировании полной группы строк для вставки, а также, как может использоваться предикат для управления изменения значений и удаления строк. Материал в этой главе составляет полный объем знаний показывающий, как создавать и управлять информацией в базе данных. Более мощные способы проектировани предикатов будут обсуждены в следующей главе .