Главная » Microsoft SQL Server, Базы данных » Обновление данных

0

Скажу без преувеличения, что инструкция UPDATE является исключительно мощным инструментом. То, что раньше занимало десятки программных строк и несколько вложенных циклов, теперь можно реализовать с помощью всего одной инструкции. SQL нельзя назвать в полной мере настоящим командным языком — он является декларативным. Код SQL всего лишь ставит задачу оптимизатору запросов. После этого оптимизатор разрабатывает наилучший план получения ответа на заданный вопрос и выполняет сформированную задачу. При этом он оценивает, сколько использовать таблиц и в каком порядке, как их объединять и какие индексы использовать. Он учитывает статистику заполнения таблиц и их размеры; производительность процессора и дисковой системы, емкость памяти. Только на основе всей этой информации он способен сформировать план выполнения запроса. Написание программ, обновляющих последовательно строки, никогда не позволило’бы добиться такого уровня оптимизации.

Обновление одной таблицы

Инструкция UPDATE языка SQL предельно проста. Она способна изменить значение всего одной ячейки, а также всех столбцов всех строк таблицы. Однако предложение FROM позволяет таблице стать частью составного источника данных и использовать всю силу инструкции SELECT.

Вот как работает инструкция UPDATE:

UPDATE dbo. таблица

SET столбец = значение,/выражение/столбец, столбец = значение…

[FROM источники_данных]

[WHERE условия]

Инструкция UPDATE может изменить значения множества строк, но только одной таблицы. Ключевое слово SET используется для преобразования данных в новые значения. Эти новые значения могут быть константами, переменными, выражениями и даже столбцами другого источника данных, имеющегося в предложении FROM инструкции UPDATE.

Дополнительная Полный список возможностей выражений см. в главе 8.

информация

Предложение WHERE жизненно важно для любой инструкции UPDATE, поскольку в его отсутствие обновляется вся таблица. Если предложение WHERE присутствует, то обновляются только те строки, которые им не отфильтрованы. Проверяйте и еще раз проверяйте предложение WHERE. Не считайте меня занудным, но я еще раз повторю: “Семь раз отмерь, один отрежь”.

В следующем примере инструкция UPDATE выполняет типичную операцию из реальной жизни — она изменяет значение одного столбца одной строки. Лучше всего отфильтровать одну строку с помощью первичного ключа:

USE СНА2

UPDATE dbo.Guide

SET Qualifications = ‘Spelunking, Cave Diving,

First Aid, Navigation’

Where GuidelD = 6

Проверим результаты обновления:

SELECT GuidelD, LastName, Qualifications FROM dbo.Guide WHERE GuidelD = 6

Будет получен следующий результат:

GuidelD         LastName Qualifications

6          Bistier Spelunking, Cave Diving,

First Aid, Navigation

Выполнение глобального поиска и замены

Очистка базы данных от “мусора” представляет собой типичную задачу ее администратора. К счастью, SQL содержит функцию replace (), которая в комбинации с инструкцией UPDATE может помочь в глобальном поиске и замене.

В следующем примере, который ссылается на учебную базу данных Family, мы будем искать вхождения двойной буквы 1 (т.е. “И”) в фамилии и заменять их на “qua”:

Use Family Update Person

Set LastName = Replace(Lastname, ’11’, ‘qua’)

Теперь проверим результат выполнения инструкции, извлекая фамилии (результирующий список сокращен):

Select lastname from Person

lastname

Haquaoway

Haquaoway

Miquaer

Miquaer

Haquaoway

Ссылка на множество таблиц при обновлении

данных

Более мощной функцией инструкции UPDATE является присвоение столбцу результата выражения, которое ссылается на тот же или другие столбцы или даже на другие таблицы.

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

Одним из способов визуализации объединений, создаваемых в предложении FROM, является слияние всех таблиц в новый единый сверхширокий результирующий набор данных. После этого оставшаяся часть инструкции SQL работает уже с этим новым множеством. Несмотря на то что именно так и работает предложение FROM, реальная инструкция UPDATE работает не с этим множеством, а только с таблицей, указанной сразу после ключевого слова UPDATE.

Синтаксис инструкции update from входит в расширения Т-SQL и не содержится в стандарте ANSI SQL-92. Если в будущем планируется перенос базы данных на другую платформу, то для обновления корректных строк используйте подзапросы.

Рассмотрим пример из реальной жизни. Предположим, что все сотрудники вскоре должны получить повышение зарплаты, размер которой зависит от подразделения, времени нахождения в текущей должности, производственных результатов и срока работы в компании (согласен, в реальной жизни такое вряд ли возможно). Если процент для каждого отдела хранится в таблице Department, то всего одна инструкция UPDATE позволяет объединить ее с таблицей Employee и учесть коэффициент размера премии. Предположим, что формула начислений следующая:

2+(((стаж_в_компании*0,1)+(месяцев_на_должности*0, 02)

+ ((рейтинг_производительности*0, 5) если больше 2))

*          коэффициент_отдела)

В предлагаемом ниже сценарии создается несколько таблиц, которые заполняются данными, после этого тестируется формула и в результате выполняется обновление.

USE Tempdb

CREATE TABLE dbo.Dept (

DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptName VARCHAR(50) NOT NULL,

RaiseFactor NUMERIC(4,2)

)

ON [Primary]

go

Create TABLE dbo.Employee (

EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptID INT FOREIGN KEY REFERENCES Dept,

LastName VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

Salary INT,

PerformanceRating NUMERIC(4,2),

DateHire DATETIME,

DatePosition DATETIME )

ON [Primary]

go

– создание данных примера

INSERT dbo.Dept VALUES (‘Engineering’ , 1.2)

INSERT dbo.Dept VALUES (‘Sales’,.8)

INSERT dbo.Dept VALUES ( ‘IT’ ,2.5)

INSERT dbo.Dept VALUES (‘Manufacturing’,1.0) go

INSERT dbo.Employee

VALUES( 1,’Smith’, ‘Sam’,54000, 2.0, ‘1/1/97′, ‘1/4/2001′ )

INSERT dbo.Employee

VALUES( 1,’Nelson’,’Slim’,78000,1.5, ‘1/9/88′, ‘1/1/2000′ )

INSERT dbo.Employee

VALUES( 2,’Ball’, ‘Sally’,45000,3.5,’ ‘1/8/99′, ‘1/1/2001′ )

INSERT dbo.Employee

VALUES( 2, ‘Kelly’, ‘Jeff’,85000,2.4, ‘1/10/83′, ‘1/9/1998′ )

INSERT dbo.Employee

VALUES( 3, ‘Guelzow1, ‘Jo’,120000,4.0, ‘1/7/95′, ‘1/6/2001′ )

INSERT dbo.Employee

VALUES( 3, ‘Anderson’, ‘Missy’,95000,1.8, ‘1/2/99′, ‘1/9/97′ )

INSERT dbo.Employee

VALUES( 4,’Reagan’, ‘Frank’,75000,2.9, ‘1/4/00′, ‘1/4/2000′ )

INSERT dbo.Employee

VALUES( 4, ‘Adams’, ‘Hank’,34000,3.2, ‘1/9/98′, ‘1/9/1998′ )

Предполагая, что датой повышения зарплаты является 1/5/2002, следующий запрос протестирует данные примера:

SELECT LastName, Salary,

DateDiff(уу, DateHire, ‘1/5/2002′) as YearsCo,

DateDiff(mm, DatePosition, ‘1/5/2002′) as MonthPosition,

CASE

WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating ELSE 0 END as Performance,

Dept.RaiseFactor AS ‘Dept’

FROM dbo.Employее JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

Должен быть получен следующий результат:

LastName Salary YearsCo MonthPosition Performance Dept

Smith            54000      5                        13                   2.00   1.20

Nelson          78000      14                      28                    .00   1.20

Ball              45000      3                        16                  3.50   .80

Kelly             85000      19                      44                   2.40   .80

Guelzow                      120000 7             11                  4.00   2.50

Anderson       95000      3                        56                    .00   2.50

Reagan          75000      2                        25                   2.90   1.00

Adams           34000      4                        44                  3.20   1.00

На основании заполненных данных выполним запрос, вычисляющий коэффициент повышения зарплаты:

SELECT LastName,

(2 + (((DateDiff(уу, DateHire, ‘1/5/2002′) * .1)

+ (DateDiff(mm, DatePosition, ‘1/5/2002′) * .02)

+ (CASE

WHEN Employee.PerformanceRating >= 2

THEN Employee.PerformanceRating ELSE 0 END * .5 ))

*          Dept.RaiseFactor))/100 as EmpRaise FROM dbo.Employee

JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

Будет получен следующий результат:

LastName       EmpRaise

Smith    .041120000

Nelson  .043520000

Ball      .038960000

Kelly     .051840000

Guelzow         .093000000

Anderson       .055500000

Reagan  .041500000

Adams  .048800000

Итак, данные внесены, и формулы проверены. Теперь пришло время выполнить инструкцию UPDATE и скорректировать зарплату сотрудников:

UPDATE Employee SET Salary = Salary * (1 +

(2 + (((DateDiff(yy, DateHire, 11/5/2002′) * .1)

+ (DateDiff(mm, DatePosition, ‘1/5/2002′) * .02)

+ (CASE

WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating ELSE 0 END * .5 ))

*          Dept.RaiseFactor))/100 )

FROM dbo.Employee

JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

Следующая инструкция SELECT позволит нам увидеть плоды своего труда:

SELECT FirstName, LastName, Salary FROM Employee

А вот и сам результат:

FirstName LastName  Salary

Sam  Smith               56220

Slim Nelson              81394

Sally Ball                  46753

Jeff  Kelly                89406

Dave Guelzow           131160

Missy                       Anderson           100272

Frank                       Reagan   78112

Hank                        Adams    35659

В завершение нашего примера очистим созданные учебные таблицы:

DROP TABLE dbo.Employee DROP TABLE dbo.Dept

В приведенном выше примере были сведены воедино операции, описанные в нескольких предыдущих главах, в том числе создание и удаление таблиц, выражения CASE, объединения, скалярные функции работы с датами, не говоря уже об операциях вставки и обновления, описанных в настоящей главе. Пример был достаточно длинным, поскольку продемонстрировал

больше, нежели одну инструкцию UPDATE. В нем был показан типовой процесс проектирования сложных обновлений, который включает несколько этапов.

1.              Проверка доступных данных. Первая инструкция SELECT объединила таблицы Employee и Dept и вывела список всех столбцов, необходимых формуле.

2.              Тестирование формулы. Вторая инструкция SELECT базировалась на первой и применила формулу к нужным столбцам. На основании полученных данных можно вручную проверить результаты по избранным позициям и таким образом проверить формулу.

3.              Выполнение обновления. Как только формула создана и проверена, ее можно подставить в инструкцию UPDATE, которую, в свою очередь, следует выполнить.

Р1нструкцию UPDATE нельзя не назвать мощной. Я заменил исключительно сложные наборы данных и вложенные циклы, выполняющиеся медленно и к тому же подверженные ошибкам, всего одной инструкцией UPDATE с грамотно созданным объединением. В результате время выполнения сократилось с нескольких минут до считанных секунд. В то же время я нисколько не умаляю достоинства подхода к обновлению с помощью наборов, а не строк данных.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии