Команда 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, то все записи таблицы будут обновлены без исключений.
SQL UPDATE — обновление данных
Оператор SQL UPDATE предназначен для обновления (редактирования) данных в таблице. Он применяется, когда в той или иной строке таблицы уже записаны некоторые данные и нужно внести в них изменения. Оператор UPDATE имеет следующий синтаксис:
UPDATE ИМЯ_ТАБЛИЦЫ SET ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ, . ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ [ WHERE УСЛОВИЕ]
Квадратные скобки [], в которые заключена часть запроса WHERE УСЛОВИЕ, означает, что эта часть является необязательной.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
А скрипт для создания базы данных «Портал объявлений 1», её таблицы и заполения таблицы данных — в файле по этой ссылке .
Использование оператора SQL UPDATE вместе с секцией WHERE
Хотя часть запроса на обновление данных WHERE УСЛОВИЕ является необязательной, в большинстве случаев она применяется, так как обновить чаще требуется значения столбцов в определённых строках.
Пример 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 |
Требуется изменить значения столбцов Units и Money в строке с Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Units=148, Money=23680 WHERE align=»justify»>После выполнения этого запроса соответствующая строка будет содержать следующие данные:
| 4 | Транспорт | Мотоциклы | 148 | 23680 |
Запросом на обновление данных с использованием оператора SQL UPDATE и секции WHERE можно изменить значения столбцов и в нескольких строках, которые соответствуют условию, указанному в секции WHERE.
Пример 2. База данных и таблица — те же, что и в примере 1. Требуется поменять название категории «Недвижимость» на «Постройки». Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Category=’Постройки’ WHERE Category=’Недвижимость’
В результате действия этого запроса изменится значение столбца Category во второй, третьей и десятой строках таблицы.
Использование оператора SQL UPDATE и вычисляемые значения
В запросах на обновление данных с использованием оператора SQL UPDATE можно путём задания вычислений менять значения, имеющие числовой формат. Соответствующие запросы могут быть с или без секции WHERE.
Пример 3. База данных и таблица — те же, что и в предыдущих примерах.
Теперь предположим, что во время заполнения таблицы данными изменились расценки на объявления, публикуемые на портале. Требуется увеличить значения столбца Money в 2 раза во всех строках таблицы. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Money = Money*2
Использование оператора SQL UPDATE без секции WHERE
Пример 4. База данных и таблица — те же, что и в предыдущих примерах. Требуется сделать неопределёнными (NULL) значения столбцов Units и Money во всех строках таблицы. Запрос для такого обновления данных будет следующим (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Units= NULL , Money= NULL
В результате действия этого запроса столбцы Units и Money примут значение NULL во всех строках таблицы.
Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, DELETE, HAVING и UNION.
UPDATE данными из других таблиц
Иногда возникает необходимость глобально обновить столбец одной таблицы значениями из этой же таблицы или из другой таблицы. Обычно для таких целей используют запрос вида:
update TABLE1
set FIELD1 = (select PRIMARYKEY
from TABLE1 T1
where T1.FIELD1=FIELD2)
Это единственный способ обновить таблицу таким образом, поскольку синтаксис «update TABLE1, TABLE2. » в IB не поддерживается.
Вероятность, что вышеприведенный запрос будет работать медленно, весьма высока. Намного выше чем у запроса, обновляющего записи данными из другой таблицы. Посмотрите план такого запроса, и если увидите там два цикла с перебором записей NATURAL, то запрос надо менять. Ускорить его можно путем «переворачивания» update и select местами внутри хранимой процедуры.
.
for select T1.PK , T2.PK
from TABLE1 T1 , TABLE1 T2
where T1.FIELD1 = T2.FIELD1
into :TARGET , :SOURCE
do
begin
update TABLE1
set FIELD1 = :SOURCE
where PK = :TARGET ;
end
Такая конструкция осуществит обновление всего за один «проход» по записям TABLE1, но в любом случае стоит проверить план оператора select и план оператора update отдельно.
Есть еще один способ, который аналогичен приведенному for select, но использует номер записи IB – RDB$DB_KEY:
create procedure TESTUPD
as
declare variable db_key CHAR(8);
begin
for select RDB$DB_KEY , .
from TAB
into :db_key
do
update TAB
set .
where RDB$DB_KEY = :db_key ;
end
Причем по таблице TAB может не быть индекса совсем, но по скорости выполнения такая конструкция практически равна скорости с оптимизацией по индексам. Например, если таблица TAB не имеет ни одного индекса, то без rdb$db_key время обновления 3-х тысяч записей 1500 секунд, а с rdb$db_key – 10 секунд.
Замечание. Обратите внимание, что длина db_key равна 8 байт. Если таблица TAB на самом деле является view, состоящим из двух таблиц, то длина db_key должна быть 16 байт, и так далее.
Неожиданный способ на основе ключевых слов AS CURSOR и WHERE CURRENT OF:
declare variable counter integer;
declare variable x integer;
begin
counter = 1;
for select c1 from t2 into 😡
as cursor FOO
do
begin
update t2
set c2 = c1 / :counter, c1 = :counter
where current of foo;
counter = :counter + 1;
end
end
Здесь таблица T2 рассматривается как курсор FOO, что в принципе эквивалентно предыдущему примеру с RDB$DB_KEY. Переменные counter, x, c2 и c1 – просто пример применения.
Выгода этого способа перед предыдущим – отсутствие необходимости объявлять переменную для хранения RDB$DB_KEY. Как упоминалось выше, размер db_key для view зависит от количества таблиц, на котором это view построено. При использовании as cursor и current of не нужно задумываться, какой объект используется для сканирования и обновления – таблица или view.
Copyright iBase.ru © 2002-2023
Обновление базы данных SQL Server с помощью объекта SqlDataAdapter в Visual C++
В этой статье описывается, как использовать SqlDataAdapter объект для обновления базы данных SQL Server в Microsoft Visual C++.
Исходная версия продукта: Visual C++
Оригинальный номер базы знаний: 308510
Сводка
Объект SqlDataAdapter служит мостом между объектом ADO.NET DataSet и SQL Server базой данных. Это промежуточный объект, который можно использовать для выполнения следующих действий:
- Заполнение ADO.NET DataSet данными, полученными из базы данных SQL Server.
- Обновите базу данных, чтобы отразить изменения (вставки, обновления, удаления), внесенные в данные, с помощью DataSet . В этой статье приведены примеры кода .NET для Visual C++, демонстрирующие, как SqlDataAdapter можно использовать объект для обновления SQL Server базы данных с изменениями данных, выполненными в объекте DataSet , заполненном данными из таблицы в базе данных.
В этой статье описывается пространство System::Data::SqlClient имен библиотеки классов платформа .NET Framework .
Объект и свойства SqlDataAdapter
Свойства InsertCommand SqlDataAdapter , UpdateCommand и DeleteCommand объекта используются для обновления базы данных с изменениями данных, выполненными в объекте DataSet . Каждое из этих свойств является SqlCommand объектами, указывающими соответствующие INSERT команды , UPDATE и DELETE TSQL, используемые для публикации DataSet изменений в целевой базе данных. Объекты, назначенные SqlCommand этим свойствам, можно создать вручную в коде или автоматически создать с помощью SqlCommandBuilder объекта .
В первом примере кода в этой статье показано, как SqlCommandBuilder объект можно использовать для автоматического UpdateCommand SqlDataAdapter создания свойства объекта . Во втором примере используется сценарий, в котором невозможно использовать автоматическое создание команд, и поэтому демонстрируется процесс, с помощью которого можно вручную создать и использовать SqlCommand объект в UpdateCommand качестве свойства SqlDataAdapter объекта.
Создание примера таблицы SQL Server
Чтобы создать пример SQL Server таблицы для использования в примерах кода .NET для Visual C++, описанных в этой статье, выполните следующие действия.
- Откройте SQL Server анализатора запросов, а затем подключитесь к базе данных, в которой вы хотите создать пример таблицы. В примерах кода, приведенных в этой статье, используется база данных Northwind, которая поставляется с SQL Server.
- Выполните следующие инструкции T-SQL, чтобы создать пример таблицы CustTest, а затем вставить в нее запись.
Create Table CustTest ( CustID int primary key, CustName varchar(20) ) Insert into CustTest values(1,'John') Пример кода 1. Автоматически созданные команды
SELECT Если инструкция для получения данных, используемых для заполнения DataSet , основана на отдельной таблице базы данных, вы можете воспользоваться преимуществами объекта для автоматического DeleteCommand CommandBuilder создания свойств DataAdapter , InsertCommand и UpdateCommand объекта . Это упрощает и уменьшает код, необходимый для выполнения INSERT операций , UPDATE и DELETE .
В качестве минимального требования необходимо задать SelectCommand свойство для работы автоматического создания команд. Схема таблицы, полученная с SelectCommand помощью , определяет синтаксис автоматически созданных INSERT инструкций , UPDATE и DELETE .
Также SelectCommand должен возвращать по крайней мере один первичный ключ или уникальный столбец. Если нет, InvalidOperation создается исключение, а команды не создаются.
Чтобы создать пример консольного приложения .NET visual C++, демонстрирующего SqlCommandBuilder использование объекта для автоматического InsertCommand создания свойств объекта , DeleteCommand и UpdateCommand SqlCommand для SqlDataAdapter объекта, выполните следующие действия:
- Запустите Visual Studio .NET, а затем создайте новое управляемое приложение C++. Присвойтите ему имя updateSQL.
- Скопируйте и вставьте следующий код в updateSQL.cpp (заменив содержимое по умолчанию):
#include "stdafx.h" #using < mscorlib.dll>#using < System.dll>#using < System.Data.dll>#using < System.Xml.dll>using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; #ifdef _UNICODE int wmain(void) #else int main(void) #endif < SqlConnection *cn = new SqlConnection(); DataSet *CustomersDataSet = new DataSet(); SqlDataAdapter *da; SqlCommandBuilder *cmdBuilder; //Set the connection string of the SqlConnection object to connect //to the SQL Server database in which you created the sample //table in Section 1.0 cn->ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;"; cn->Open(); //Initialize the SqlDataAdapter object by specifying a Select command //that retrieves data from the sample table da = new SqlDataAdapter("select * from CustTest order by CustId", cn); //Initialize the SqlCommandBuilder object to automatically generate and initialize //the UpdateCommand, InsertCommand and DeleteCommand properties of the SqlDataAdapter cmdBuilder = new SqlCommandBuilder(da); //Populate the DataSet by executing the Fill method of the SqlDataAdapter da->Fill(CustomersDataSet, "Customers"); //Display the Update, Insert and Delete commands that were automatically generated //by the SqlCommandBuilder object Console::WriteLine("Update command Generated by the Command Builder : "); Console::WriteLine("=================================================="); Console::WriteLine(cmdBuilder->GetUpdateCommand()->CommandText); Console::WriteLine(" "); Console::WriteLine("Insert command Generated by the Command Builder : "); Console::WriteLine("=================================================="); Console::WriteLine(cmdBuilder->GetInsertCommand()->CommandText); Console::WriteLine(" "); Console::WriteLine("Delete command Generated by the Command Builder : "); Console::WriteLine("=================================================="); Console::WriteLine(cmdBuilder->GetDeleteCommand()->CommandText); Console::WriteLine(" "); //Write out the value in the CustName field before updating the data using the DataSet DataRow *rowCust = CustomersDataSet->Tables->Item["Customers"]->Rows->Item[0]; Console::WriteLine("Customer Name before Update : ", rowCust->Item["CustName"]); //Modify the value of the CustName field String *newStrVal = new String("Jack"); rowCust->set_Item("CustName", newStrVal); //Modify the value of the CustName field again String *newStrVal2 = new String("Jack2"); rowCust->set_Item("CustName", newStrVal2); //Post the data modification to the database da->Update(CustomersDataSet, "Customers"); Console::WriteLine("Customer Name after Update : ", rowCust->Item["CustName"]); //Close the database connection cn->Close(); //Pause Console::ReadLine(); return 0; > cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;"; Update command generated by the Command Builder: ================================================== UPDATE CustTest SET CustID = @p1 , CustName = @p2 WHERE ( (CustID = @p3) AND ((CustName IS NULL AND @p4 IS NULL) OR (CustName = @p5))) Insert command generated by the Command Builder : ================================================== INSERT INTO CustTest( CustID , CustName ) VALUES ( @p1 , @p2 ) Delete command generated by the Command Builder : ================================================== DELETE FROM CustTest WHERE ( (CustID = @p1) AND ((CustName IS NULL AND @p2 IS NULL) OR (CustName = @p3))) Customer Name before Update : John Customer Name after Update : Jack2 Пример кода 2. Создание и инициализация свойства UpdateCommand вручную
Выходные данные, созданные примером кода 1, указывают на то, что логика автоматического создания команд для UPDATE операторов основана на оптимистическом параллелизме. То есть записи не блокируются для редактирования и могут быть изменены другими пользователями или процессами в любое время. Так как запись могла быть изменена после возврата из SELECT инструкции, но до UPDATE выдачи инструкции, автоматически созданная UPDATE инструкция содержит WHERE предложение, чтобы строка обновлялась только в том случае, если она содержит все исходные значения и не была удалена. Это делается для того, чтобы новые данные не перезаписылись. В случаях, когда автоматически созданное обновление пытается обновить строку, которая была удалена или не содержит исходные значения, найденные в DataSet , команда не влияет ни на какие записи, и DBConcurrencyException создается исключение .
Если требуется UPDATE завершить независимо от исходных значений, необходимо явно задать UpdateCommand для DataAdapter , а не полагаться на автоматическое создание команд.
Чтобы вручную создать и инициализировать UpdateCommand свойство объекта, используемого SqlDataAdapter в примере кода 1, выполните следующие действия.
-
Скопируйте и вставьте следующий код (перезапись существующего кода) в функцию в Main() файле UpdateSQL.cpp в приложении C++, созданном в примере кода 1:
SqlConnection *cn = new SqlConnection(); DataSet *CustomersDataSet = new DataSet(); SqlDataAdapter *da; SqlCommand *DAUpdateCmd; cn->ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;"; cn->Open(); da = new SqlDataAdapter("select * from CustTest order by CustId", cn); //Initialize the SqlCommand object that will be used as the DataAdapter's UpdateCommand //Notice that the WHERE clause uses only the CustId field to locate the record to be updated DAUpdateCmd = new SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId" , da->SelectCommand->Connection); //Create and append the parameters for the Update command DAUpdateCmd->Parameters->Add(new SqlParameter("@pCustName", SqlDbType::VarChar)); DAUpdateCmd->Parameters->Item["@pCustName"]->SourceVersion = DataRowVersion::Current; DAUpdateCmd->Parameters->Item["@pCustName"]->SourceColumn = "CustName"; DAUpdateCmd->Parameters->Add(new SqlParameter("@pCustId", SqlDbType::Int)); DAUpdateCmd->Parameters->Item["@pCustId"]->SourceVersion = DataRowVersion::Original; DAUpdateCmd->Parameters->Item["@pCustId"]->SourceColumn = "CustId"; //Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter da->UpdateCommand = DAUpdateCmd; da->Fill(CustomersDataSet, "Customers"); DataRow *rowCust = CustomersDataSet->Tables->Item["Customers"]->Rows->Item[0]; Console::WriteLine("Customer Name before Update : ", rowCust->Item["CustName"]); //Modify the value of the CustName field String *newStrVal = new String("Jack"); rowCust->set_Item("CustName", newStrVal); //Modify the value of the CustName field again String *newStrVal2 = new String("Jack2"); rowCust->set_Item("CustName", newStrVal2); da->Update(CustomersDataSet, "Customers"); Console::WriteLine("Customer Name after Update : ", rowCust->Item["CustName"]); cn->Close(); Console::ReadLine(); return 0; cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;"; Customer Name before Update : John Customer Name after Update : Jack2