Иначительная часть работы многих администраторов баз данных и администраторов SQL Server состоит в обеспечении текущей работы базы данных. Сюда относится добавление, изменение и удаление данных из таблиц. Запросы на T-SQL позволяют легко выполнять эти задания. Нужно только использовать предложения INSERT, UPDATE и DELETE. Перед тем как я покажу, как применять эти предложения, вспомним несколько вещей, которые нужно сделать до начала работы.
Предварительные условия
Примеры предложений INSERT, UPDATE и DELETE, которые я использую в этой статье, запускаются на простой базе данных MyDB, содержащей несколько временных таблиц и таблицу MyTable. Таблица MyTable используется для хранения названий разных временных таблиц. Единственная цель временных таблиц — заполнить пространство. Просто присутствуя, они обеспечивают наличие данных в таблице sysobjects, которая используется в качестве источника данных для вставки данных в MyTable.
Предполагая наличие полномочий, необходимых для создания баз данных и таблиц, для запуска предложений, модифицирующих данные, можно создать MyDB, MyTable и временные таблицы при помощи файлов MyDB.sql и MyTable.sql. Эти запросы можно увидеть в листингах 1 и 2.
Для запуска запроса откройте окно запроса либо в SQL Server 2005 SQL Server Management Studio (далее SSMS), либо в SQL Server 2000 Query Analyzer и скопируйте код MyDB.sql в окно. В двух записях FILENAME замените C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMyDB.mdf на путь на имеющемся сервере. Выполните запрос для создания MyDB. Для разработки MyTable и временных таблиц скопируйте код в MyTable.sql в окно запроса и выполните его.
Перед тем как приступать к работе с данными любой таблицы, нужно ознакомиться с ее структурой. Для этого открываем таблицу MyTable и выполняем следующие действия.
Добавление одной записи
Стандартное предложение INSERT, которое добавляет одиночную запись (или строку) в таблицу, состоит из трех частей и имеет вид
INSERT INTO Part1 (Part2) VALUES (Part3)
В части 1 определена целевая таблица, которая будет содержать новую запись. В части 2 перечисляются названия полей в целевой таблице, для которой имеются данные. Нужно заключить перечень в круглые скобки и использовать запятые для отделения названий полей. В части 3 обеспечивается добавление данных. Когда надо будет установить фактические значения данных, используется параметр VALUES и следующие за ним значения. Требуется заключить перечень значений данных в круглые скобки и отделять значения запятыми. Если это значение символьное, вроде строки или даты, его также нужно заключить в одинарные кавычки (‘‘). Например, выполнение следующего предложения вставляет значения данных TestTable и 2007–09–22 в поля ObjectName и Creation-Date таблицы MyTable, соответственно:
INSERT INTO MyTable (ObjectName, CreationDate) VALUES (‘TestTable’, ‘007–09–22’)
Те, кто уже знает мои приемы работы с таблицей MyTable, возможно, заметили, что я просто установил два или три поля в предложение INSERT. Первое поле — это поле идентификатора, которое имеет специальное назначение. Данные этого поля добавляются автоматически, и его значения увеличиваются с каждой новой вставленной записью. Это важное свойство SQL Server, о котором нужно знать. Чтобы узнать больше о типах идентификационных данных, загляните в SQL Server Books Online (BOL) в раздел IDENTITY (Свойство) на msdn2.microsoft.com/en-us/library/ms186775.aspx.
Чтобы просмотреть вновь вставленную запись, выполните следующее предложение:
SELECT * FROM MyTable
На рисунке 1 показано, как выглядит результат.
Добавление результатов предложением SELECT
Я уже показывал, как получить данные из базы данных, используя предложение SELECT. Можно задействовать оператор INSERT для сохранения данных, извлеченных предложением SELECT, в таблицу. Как и оператор INSERT для отдельной записи, оператор INSERT, который сохраняет результаты предложения SELECT, имеет три части: заданная база данных (часть 1), названия полей (часть 2) и данные (часть 3). Тем не менее вместо параметра VALUES для определения актуальных значений данных в части 3 используется предложение SELECT, которое извлекает данные из другого источника. Например, когда выполняется к базе данных MyDB, запрос
INSERT INTO MyTable (ObjectName, CreationDate) SELECT name, crdate FROM sysobjects WHERE type = ‘U’ ORDER BY name
вставляет в новую таблицу MyTable записи, содержащие название и дату разработки всех пользовательских таблиц в MyDB, упорядоченных в алфавитном порядке в соответствии с названиями. Таким образом, предполагая, что выполнено предложение INSERT с одиночной записью, описанное ранее, результаты этого запроса должны выглядеть так же, как и результаты, показанные на рисунке 2, с двумя исключениями. Значения CreationDate для MyTable и временных таблиц будут содержать дату и время запуска MyTable.sql. Второе поле ObjectName будет шире. Я сократил его из-за недостатка места.
На рисунке 2 TestTable — только первая запись. Имя условия ORDER BY только обращается к новым записям, которые вставляет в данный момент оператор SELECT. Таблица TestTable была предварительно дополнена при помощи оператора, состоящего из единичной записи оператора INSERT.
Можно использовать любой оператор SELECT, до тех пор пока типы данных в полях, перечисленных в части 2 оператора INSERT, соответствуют полям, определенным в предложении SELECT. Это дает возможность собрать все типы данных. Тем не менее используйте новое знание с осторожностью, чтобы не вставить миллионы записей на занятый сервер или на сервер с ограниченным дисковым пространством.
Модернизация данных
Теперь, когда мы умеем добавлять данные, давайте посмотрим, как работает оператор UPDATE. Простой оператор UPDATE обычно состоит из трех частей:
UPDATE Part1 SET Part2 WHERE Part3
В части 1 определена целевая таблица. В части 2 определены поля, которые должны быть изменены, вместе с новыми данными для каждого поля. Часть 3 не является обязательной, но в большинстве случаев она очень важна. Здесь устанавливается фильтр, использующий оператор WHERE. Если оператор WHERE не установлен, будет изменена каждая единичная запись в таблице. Например, запрос
UPDATE MyTable SET CreationDate = ‘2007–09–23’
изменяет значение CreationDate для каждой записи в таблице MyTable, как показано на рисунке 3. На рисунке видно, что значение CreationDate — это 2007–09–23 00:00:00.000, а не 2007–09–23, как указано в запросе. Так как тип данных поля CreationDate определен как дата и запрос не указывает временную часть, SQL Server допускает, что имеется в виду полночь, и добавляет 00:00:00.000.
Теперь используем оператор WHERE для изменения полей ObjectName и CreationDate выбранной записи:
UPDATE MyTable SET ObjectName = ‘PartyTime’, CreationDate = ‘1999–12–31 23:00:00’ WHERE TableID = 1
Как показано на рисунке 4, только первая запись (установленная посредством WHERE TableID = 1) изменена с новым названием таблицы PartyTime и новой датой разработки 1999–12–31 23:00:00.
Удаление данных
Каждый администратор баз данных опасается, что предложение DELETE попадет не в те руки. Хотя оно может быть использовано неправильно, оно играет важную роль, когда запросы применяются для изменения данных в таблицах. Типичное предложение DELETE обычно состоит из двух частей:
DELETE Part1
WHERE Part2
В части 1 определяется целевая таблица. В части 2 определяется фильтр, использующий оператор WHERE. Как и оператор WHERE в предложении UPDATE, условие WHERE в выражении DELETE необязательно, но в большинстве случаев необходимо. Если не будет включен оператор WHERE и фильтр, будут удалены все записи в заданной таблице.
Например, нужно удалить из таблицы MyTables все записи с датой разработки ранее 22 сентября 2007 года.
Запрос
DELETE MyTable WHERE CreationDate > ‘2007–09–22’
Приведет к результату, показанному на рисунке 5. Нужно быть внимательным во время изменения данных при помощи предложений DELETE или UPDATE. Оператор WHERE используется всегда, кроме случая, когда надо удалить из таблицы все данные.
Если неизвестно, сколько записей нужно удалить, выполняется предложение SELECT с тем же оператором WHERE. Вместо определения полей в предложении SELECT задается оператор COUNT (*), возвращающий количество строк, которые должны быть удалены. Например, чтобы проверить результат последнего выражения с предложением DELETE, можно выполнить:
SELECT COUNT (*) FROM MyTable WHERE CreationDate > ‘2007–09–22’
Если результирующий ряд исчисляется в миллионах, подумайте, надо ли детализировать оператор WHERE. Я расскажу об использовании функции COUNT в следующей статье, поэтому не стоит беспокоиться, если не получится применить это в работе.
Изучите свои данные
Если вы уверены в том, что теперь сможете изменять данные в MyTable, попробуйте выполнить операторы INSERT, UPDATE и DELETE в существующей базе данных на тестовом сервере. Для этого скопируйте код из запроса ExistingDatabaseQuery.sql (см. листинг 3) в окно запроса, поместите MyDB в первой строке к названию существующей базы данных и выполните запрос.
Затем можно переработать примеры из предложений INSERT, UPDATE и DELETE к имеющейся конфигурации и выполнить их. Таким образом вы узнаете, как лучше использовать эти предложения. Возможно, обнаружатся данные, о существовании которых ранее не было известно.
Билл Макэвой (bill@cookingwithsql.com) — руководитель и администратор базы данных сайта Cooking with SQL Web