Модификация данных в базах данных SQL Server

СУБД позволяет модифицировать хранящиеся в системе данные. SQL Server поддерживает несколько методов добавления новых строк в таблицы баз данных, изменения данных в существующих строках и удаления строк.

Добавление данных в БД SQL Server

SQL Server поддерживает несколько методов добавления информации в базу данных:

• посредством оператора INSERT;

• с помощью оператора SELECT... INTO;

• средствами оператора WRITETEXT и некоторых функций интерфейса прикладного программирования баз данных (API БД), позволяющих добавить в строку данные типа ntext, text или image;

• компонент массового копирования, используемый для добавления большого числа строк.

Оператор INSERT добавляет в таблицу одну или несколько строк. В простейшем случае оператор INSERT имеет следующий вид:

INSERT [INTO] таблица_или_представление [(список_столбцов)] значения

Этот оператор помещает в заданную таблицу или представление данные (значения) в виде одной или нескольких строк. Список имен столбцов (список_столбцов }, разделенных запятыми, задает столбцы для размещения данных. Если столбцы не заданы, данные получат все столбцы таблицы или представления. Если задана лишь часть списка, то во все столбцы, не названные в списке, будет вставлено пустое значение или значение по умолчанию (если существует определение DEFAULT). Все не названные столбцы должны допускать пустые значения или у них должно быть определено значение по умолчанию.

Кроме того, оператор INSERT не позволяет задать значения для столбцов следующих типов, поскольку SQL Server генерирует эти значения автоматически:

• столбцы со свойством IDENTITY;

• столбцы с определением DEFAULT, в котором использована функция NEWID();

• вычисляемые столбцы.

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

Определяя оператор INSERT, можно задать значения с помощью конструкции VALUES (для одной строки) или подзапроса SELECT (для одной или нескольких строк).

Конструкция VALUES позволяет задавать значения в одной строке таблицы. Значения указывают в виде списка скалярных выражений, разделенных запятой. Тип данных, точность целой и дробной части этих выражений должны совпадать с аналогичными параметрами соответствующего столбца из списка столбцов или допускать неявное преобразование. Если список столбцов не задан, значения должны быть указаны в той же последовательности, что и столбцы таблицы или представления.

Например, в базе данных Persons создается следующая таблица:

USE Master

CREATE TABLE NewPersons

(

PersonID INT IDENTITY(1,1) NOT NULL,

PersonTitle VARCHAR(50) NOT NULL,

PersonType CHAR(12) NOT NULL

CONSTRAINT [persontype_df] DEFAULT ('Undecided'),

BirthPlace VARCHAR(50) NULL

)

GO

В созданную таблицу решено добавить строку с данными. В следующем операторе INSERT для добавления новой строки в таблицу NewPersons используется конструкция VALUES:

USE Master

INSERT INTO NewPersons (PersonTitle, PubCity)

VALUES ('Miss', 'Novosibirsk')

GO

В этом операторе определяются значения столбцов PersonTitle и BirthPlace. Включать в оператор INSERT значение столбца PersonId не обязательно, поскольку этот столбец определен со свойством IDENTITY и значения этого столбца генерируются автоматически. Кроме того, так как значение столбца PersonType не определено, при исполнении оператора INSERT SQL Server автоматически добавляет в него значение по умолчанию (Undecided).

Подзапрос SELECT в операторе INSERT позволяет добавить к таблице данные из одной или нескольких других таблиц или представлений, причем несколько строк одновременно. Подзапрос SELECT в операторе INSERT применяется для добавления к таблице существующих данных, тогда как конструкция VALUES в операторе INSERT используется для добавления к таблице новых данных.

В следующем сценарии INSERT для добавления строк в таблицу NewPersons используется подзапрос SELECT:

USE Master

INSERT INTO NewPersons (PersonTitle. PersonType)

SELECT Title, Type

FROM Titles

WHERE Type = 'kuku'

GO

Оператор INSERT помещает информацию, которую возвращает подзапрос SELECT, в таблицу NewPersons.

Оператор SELECT INTO позволяет создать новую таблицу и заполнить ее результатами оператора SELECT. Конструкция INTO оператора SELECT более подробно обсуждались выше.

В SQL Server предусмотрено несколько методов добавления в строку значений типа ntext, text или image:

• относительно небольшие по объему данные можно задавать в операторе INSERT так же, как данные типа char, nchar или binary;

• оператор WRITETEXT позволяет выполнить непротоколируемое интерактивное обновление поля типа text, ntext или image. Этот оператор полностью перезаписывает любые данные, которые указаны в обрабатываемом им столбце. Оператор WRITETEXT нельзя использовать для обновления столбцов типа text, ntext и image в представлениях;

• приложения OLE DB способны записывать новые значения типа text, ntext и image посредством интерфейса ISequentialStream;

• приложения ODBC используют для записи новых значений типа text, ntext и image функцию SQL PutData.

Компоненты SQL Server для массового копирования позволяют вставлять в таблицу или представление, а также извлекать из таблицы, представления или запроса большое число строк. Массовое копирование — самый быстрый способ добавления большого числа строк к SQL Server.

Модификация данных в БД SQL Server

После создания таблицы и ее заполнения данные можно изменять или обновлять. SQL Server предоставляет несколько методов изменения данных в существующей таблице:

• оператор UPDATE;

• API базы данных и курсоры;

• оператор UPDATETEXT.

Обновление работает как с таблицами, так и с представлениями, но с некоторыми ограничениями.

Оператор UPDATE способен изменять данные в одной строке, группе строк или во всех строках таблицы или представления. Его также применяют для обновления строк на удаленном сервере с помощью имени связанного сервера или функции OPENROWSET, OPENDATASOURCE или OPENQUERY (если компонент доступа OLE DB, используемый для доступа к удаленному серверу, поддерживает обновление). Оператор UPDATE, который ссылается на таблицу или представление, может изменять данные только в одной таблице в каждый отдельный момент.

Обновление пройдет успешно только в том случае, если новое значение совместимо с типом данных целевого столбца и соответствует всем ограничениям, налагаемым на столбец.

Оператор UPDATE состоит из следующих основных конструкций:

• SET;

• WHERE;

• FROM.

Конструкция SET задает столбцы, которые следует изменить, и их новые значения. Во всех строках, которые соответствуют условию поиска, заданному конструкцией WHERE, значения заданных полей обновляются значениями, заданными в конструкции SET. Если конструкция WHERE не задана, обновляются все строки.

Например, показанный далее оператор UPDATE включает конструкцию SET, которая увеличивает возраст людей в таблице NewPersons на единицу:

USE Master

UPDATE NewPersons

SET Age = Age + 1

GO

В этом операторе не используется конструкция WHERE, поэтому обновляются все строки таблицы (если в поле Age нет пустого значения, но это выглядело бы по меньшей мере странно).

Конструкция WHERE выполняет две функции:

• задает подлежащие обновлению строки;

• указывает строки исходной таблицы, из которых извлекаются данные для обновления, если также задана конструкция FROM.

Если конструкция WHERE не задана, обновляются все строки таблицы.

В следующем сценарии в операторе UPDATE использована конструкция WHERE, которая обновляет только строки, соответствующие определенному в конструкции условию:

USE Master

UPDATE NewPersons

SET PersonType = 'Missis'

WHERE PersonType = 'Miss'

GO

Этот оператор изменяет Miss на Missis. Если конструкция WHERE не задана, то все значения в столбце PersonType были бы заменены значением Missis.

С помощью конструкции FROM удается заменить данные в обновляемой таблице данными из одной или нескольких таблиц или представлений. Например, в следующем сценарии в операторе UPDATE в конструкцию FROM входит внутреннее соединение, которое соединяет названия в таблицах NewPersons и Persons:

USE Master

UPDATE NewPersons

SET Age = Persons.Age

FROM NewPersons JOIN Persons

ON NewPersons.Title = Persons.Title

GO

Этот оператор обновляет значения столбца Age в таблице NewPersons значениями одноименного столбца из таблицы Persons.

ADO, OLE DB и API ODBC поддерживают обновление текущей строки в результирующем наборе, полученным приложением. Кроме того, при использовании серверного курсора Transact-SQL можно обновлять текущую строку с помощью оператора UPDATE, в который входит конструкция WHERE CURRENT OF. Изменения касаются только той строки, на которой установлен курсор.

SQL Server предоставляет несколько методов для обновления значений типа ntext, text и image:

• относительно небольшие данные задаются в операторе UPDATE так же, как данные типа char, nchar или binary;

• операторы Transact-SQL WRITETEXT и UPDATETEXT позволяют обновить значение типа text, ntext или image;

• приложения OLE DB способны записывать новые значения типа text, ntext и image с помощью интерфейса ISequentialStream;

• приложения ODBC используют для записи новых значений типа text, ntext и image функцию SQLPutData.

SQL Server также поддерживает обновление значений типа ntext, text или image по частям. Все остальные приложения и сценарии на языке Transact-SQL, пакеты, хранимые процедуры и триггеры разрешается использовать для обновления лишь части поля типа ntext, text или image оператором UPDATETEXT.

Удаление данных из БД SQL Server

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

• оператор DELETE;

• API и курсоры;

• оператор TRUNCATE TABLE.

Операторы, удаляющие данные, работают как с представлениями, так и с таблицами (с некоторыми ограничениями).

Оператор DELETE удаляет из таблицы или представления одну или несколько строк. В упрощенном виде синтаксис оператора DELETE выглядит так:

DELETE таблица_или_представление FROM исходная_таблица WHERE условие_поиска

Таблица_или_представление определяет имя таблицы или представления, откуда нужно удалить строки. Удаляются все строки таблицы или представления, которые соответствуют условию поиска, заданному конструкцией WHERE. Если конструкция WHERE не задана, то удаляются все строки таблицы или представления. Конструкция FROM задает дополнительные таблицы, представления или условия соединения, которые можно использовать в предикатах условия поиска конструкции WHERE, определяющей строки, подлежащие удалению из таблицы или представления. Строки не удаляются из названной в конструкции FROM таблицы, а только из таблицы, названной в конструкции DELETE.

Любая таблица, из которой удалены все строки, остается в базе данных. Действие оператора DELETE распространяется только на строки таблицы. Сами таблицы удаляются из базы данных оператором DROP TABLE.

Рассмотрим следующий сценарий с оператором DELETE:

USE Master

DELETE NewPersons

FROM Persons

WHERE NewPerson.Title = Persons.Title

AND Titles.Age = 100

GO

Здесь удаляются строки из таблицы NewPersons, если возраст составляет 100 лет.

ADO, OLE DB и API ODBC поддерживают удаление текущей строки в результирующем наборе, полученным приложением. Кроме того, сценарии Transact-SQL, хранимые процедуры и триггеры могут использовать для удаления строки, на которой в настоящее время установлен курсор, конструкцию WHERE CURRENT OF в операторе DELETE.

Оператор TRUNCATE TABLE — быстрый непротоколируемый метод удаления из таблицы всех строк. Этот метод практически всегда работает быстрее оператора DELETE, у которого не заданы условия, поскольку DELETE протоколирует удаление каждой строки, a TRUNCATE TABLE — лишь освобождение целых страниц данных. Оператор TRUNCATE TABLE немедленно освобождает все место, занятое данными и индексами таблицы.

Следующий оператор TRUNCATE TABLE удаляет все строки из таблицы NewPersons в базе данных Master:

USE Master

TRUNCATE TABLE NewPersons

GO

Как и в случае оператора DELETE, после применения оператора TRUNCATE TABLE определение таблицы (с ее индексами и ассоциированными объектами) остается в базе данных. Для удаления определения таблицы следует использовать уже ам знакомый оператор DROP TABLE.

Дата добавления: 2014-01-11