Добавлен: 25.10.2018
Просмотров: 859
Скачиваний: 7
6
update s
set Counts=Counts*case when Counts>20
then 3
else 2
end
from Sale s
join Client c on
c.ClientNo=s.ClientNo
and c.ClientName='
ОАО Лотос+'
Рис. 6. – Сложный запрос обновления заказов, сделанных фирмой ОАО Лотос+
Сравнивая рис. 6 и рис. 4, можно сделать вывод, что заказ SaleNo = 12 увеличился с 30
единиц до 90, т.е. в 3 раза. Другой заказ (SaleNo=13) был увеличен в 2 раза, т.к. первичный размер
заказа меньше 20.
Рассмотрим написанный запрос более подробно. После ключевого update указан псевдоним
s,
присвоенный таблице Sale в операторе from. Псевдонимы используют либо для более краткой
формы записи названия таблицы (при многократном обращении к одной и той же таблицы), либо
при соединении нескольких таблиц. В операторе set указано то, что требуется обновить поле Counts
таблицы Sale, т.к. именно на неё ссылается псевдоним s. Расчёт нового значения выполняется на
основе условного оператора case, который в отличие от реализации соответствующего оператора в
высокоуровневых языках, в Transact-SQL возвращает значение. Синтаксис этого оператора имеет
вид [1]:
--
Синтаксис простого выражения case
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE elseresultexpression ] END
--
Синтаксис поискового выражения case, позволяющего конструировать сложные
выражения CASE
WHEN Booleanexpression THEN resultexpression [ ...n ]
[ ELSE elseresultexpression ]
END
Оператор case анализирует список условий и возвращает один из нескольких результатов.
Выражение case имеет два формата:
•
простое выражение case, используемое для определения результата сравнения выражения с
набором простых выражений;
•
поисковое выражение case, используемое для определения результата
вычисления набора логических выражений.
Оба формата поддерживают дополнительный аргумент else. Выражение case может
присутствовать в любой инструкции или предложении, которые разрешают использование
допустимых выражений. Например, выражение case можно использовать в таких инструкциях, как
select, update, delete
и set, а также в таких предложениях, как selectlist, in, where, order by и having.
Рассмотрим синтаксические элементы более подробно:
inputexpression
Выражение, полученное при использовании простого формата выражения case. Аргумент
input_expression
представляет собой любое допустимое выражение.
WHEN whenexpression
Простое выражение, с которым сравнивается аргумент inputexpression при использовании простого
формата функции case. Аргумент when_expression представляет собой любое допустимое
выражение. Типы данных аргумента inputexpression и каждого из выражений whenexpression
7
должны быть одинаковыми или неявно приводимыми друг к другу.
THEN resultexpression
Выражение, возвращаемое, если сравнение выражений inputexpression и whenexpression
дает в результате true или выражение booleanexpression вычисляется в true. Аргумент result
expression
представляет собой любое допустимое выражение.
ELSE elseresultexpression
Это выражение, возвращаемое, если ни одна из операций сравнения не дает в результате
true.
Если этот аргумент опущен и ни одна из операций сравнения не дает в результате true, функция
case
возвращает null. Аргумент else_result_expression представляет собой любое допустимое
выражение. Типы данных аргумента else_result_expression и любого из аргументов result_expression
должны быть одинаковыми или неявно приводимыми друг к другу.
WHEN Booleanexpression
Это логическое выражение, полученное при использовании поискового формата
функции case. Аргумент boolean_expression представляет собой любое допустимое логическое
выражение.
В рассматриваемом примере (рис. 6) необходимо было обновить поле из таблицы Sale для
клиента ОАО Лотос+. Отметим, что в таблице Sale отсутствуют названия организаций (рис. 1) а
имеются только её уникальные идентификаторы. Поэтому нам необходимо найти идентификатор
клиента ОАО Лотос+ в таблице Client, определить её идентификатор, а затем найти все заказы в
таблице Sale. В языке SQL это выполняется с помощью естественных внутренних соединений
(оператор join). Т.е. строки, сохранённые в таблице Sale соединяются со строками, хранящимися в
таблице Client по равенству значений полей ClientNo. При этом обновляться будут только строки,
соответствующие клиенту ОАО Лотос+.
Синтаксис оператора update, выполняющего обновления значений строк имеет вид [1]:
[ WITH
<common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ <object> | rowset functionlimited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
SET
{ column_name = { expression | DEFAULT |
NULL } | { udt_column_name.{ {
property_name = expression | field_name =
expression } | method_name ( argument [ ,...n ] )
} }
| column_name { .WRITE ( expression , @Offset ,
@Length ) } | @variable = expression | @variable =
column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression |
@variable { += | -= | *= | /= | %= | &= | ^= | |= } expression |
@variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
[ <OUTPUT Clause> ] [
FROM{ <table_source> } [
,...n ] ] [ WHERE {
<search_condition> | { [
CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name } ]
} } ]
[ OPTION ( <query_hint> [
,...n ] ) ] [ ; ]
<object>
8
::= {
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name}
Рассмотреть синтаксические элементы более подробно можно в любом учебнике.
Для того, чтобы удалить клиента ОАО Лотос+ из БД необходимо выполнить запрос,
представленный на рис. 7.
delete Client
where ClientName='
ОАО Лотос+'
Рис. 7. – Удаление информации о фирме ОАО Лотос+ из БД
Для удаления значения из таблицы использована команда delete, вслед за которой указано
название таблицы. В операторе where указано условие, которым удовлетворяет удаляемые строки.
Как следует из рис. 7 вместе с клиентом были удалены все сделанные им заказы (см. рис. 6.). Это
сделано потому, что при объявлении таблицы Sale указан внешний ключ ClientNo с опцией on delete
cascade
(Листинг 1).
Перейдём к рассмотрению синтаксических конструкций языка Transact-SQL, позволяющих
извлекать информацию из БД, а именно, инструкцию select. Для выборки всех клиентов,
сохранённых в БД, необходимо написать запрос, представленный на рис. 8.
select *
from Client
Рис. 8. – Выборка информации о всех клиентах
9
После ключевого слова select указывается список полей. В нашем случае указан символ
звёздочки (*), означающий выбрать все поля. После слова from указываются таблицы, из которых
необходимо извлечь информацию.
Предыдущий запрос извлекал все строки из таблицы. Так как в любой таблице может
содержаться огромное количество, строк, то чаще всего на результат выборки накладывают фильтр
с помощью директивы where (рис. 9).
select *
from Client
where ClientNo=1
Рис. 9. – Получение информации о клиенте, у которого в поле ClientNo указано
значение 1
В операторе where указывается условие фильтрации, которое может состоять из одного (рис.
9) или нескольких критериев (рис. 10). На рис. 10 представлены два примера написания запроса,
извлекающего информацию о тех клиентах, идентификаторы которых равны 1,2 или 5.
select *
select *
from Client from Client
where ClientNo in (1,2,5)
where ClientNo=1 or ClientNo=2
or ClientNo=5
Рис. 10. – Извлечение информации о нескольких клиентах
Первая версия запроса (рис. 10, слева) использует оператор in, позволяющий проверить поле
ClientNo
на равенство хотя бы одному из значений, указанных в скобках. Вторая версия использует
булевую бинарную операцию or (логическое «или»), позволяющую соединить несколько критериев
в одно условие. На рис. 10 логическая операция применена для соединения нескольких критериев
для одного поля, а на рис. 11 представлен запрос, в котором сформирован фильтр по двум полям.
select *
from Client where (ClientNo=2) or
(ClientName like '
ИП%')
Рис. 11. – Соединение критериев по нескольким полям с помощью операции or
С целью повышения наглядности, соединяемые критерии размещены в скобках, хотя в
данном случае в (Transact-SQL) это необязательно. В запросе просматривается каждая строка
таблицы Client и выводится в том случае, если либо идентификатор клиента равен 2, ли если
значение поля ClientName начинается со строки «ИП», за которой следует неограниченное
количество символов (данная маска задана с помощью символа %). Для проверки строкового поля
на неточное соответствие значению используется ключевое слово like (с помощью которого
описывается предикат), которое определяет совпадает ли указанная символьная строка с заданным
шаблоном [1]. Шаблон может включать обычные символы и символы-шаблоны. Во время
сравнения с шаблоном необходимо, чтобы его обычные символы в точности совпадали с
символами, указанными в строке. Символы-шаблоны могут совпадать с произвольными
элементами символьной строки. Использование символов-шаблонов с оператором like
предоставляет больше возможностей, чем использование операторов сравнения строк = и <>.
10
Табл. 1. – Типы шаблонов Microsoft SQL Server 2008
В предыдущих запросах на выборку данных извлекались все поля таблицы и порядок следования
строк был не определён. В следующем запросе (рис. 12) извлекаются названия всех организаций и
результат сортируется в порядке возрастания.
select ClientName
from Client
order by ClientName
Рис. 12. – Сортировка организаций по названию
Для сортировки результирующего набора используется фраза order by, в которой указано
поле сортировки. По умолчанию сортировка выполняется в порядке возрастания. Если требуется в
порядке убывания, то необходимо после имени поля указать слово desc.
До текущего момента все запросы извлекали данные из одной таблицы, что осуществлялось
указанием имени единственной таблицы в директиве from. На рис. 13 представлены две формы
запросов, извлекающих информацию из всех трёх таблиц, изображённых на рис. 1. В запросе
извлекается информация о продажах с указанием названия покупателя и продукта. Первая форма
запроса (рис. 13) использует директиву where для соединения трёх таблиц (Client, Sale, Product),
указанных после слова from, по полям первичных/внешних ключей. Т.к. в полях внешних ключей
могут сохраняться значения, равные значениям первичных ключей других таблиц, то для
организации соединений используется операция равенства (=), по обе стороны которой указаны
именя соединяемых полей. Результирующий набор состоит из названия организации
(c.ClientName),
названия продукта (p.ProductName), все поля из таблицы Sale (s.*), а также
вычисляемое поле, рассчитывающие сумму заказа (цена*количество) которое появляется в
результирующем наборе под псевдонимом Summa (слово as является необязательным). Сортировка
выполняется по клиенту, продукту и в порядке убывания (desc) даты продажи.
Вторая форма запроса (рис. 13) организует соединение с помощью директивы join. По
умолчанию предполагается выполнение внутреннего соединения, поэтому фраза inner не
обязательна. После слова on указываются поля (первичных/внешних ключей), по которым
выполняется соединение. Использование фразы join вместо where более естественно описывает
процедуру соединения таблиц.
Символ-шаблон ! Описание ! Пример использования
%
Любая строка длиной от нуля
и более символов
Оператор
where
Название like '%компьютер%' выполняет поиск
и выдает все названия, содержащие слово «компьютер»
_ (
подчеркивание)
Любой одиночный символ
Оператор
where
фамилия_автора LIKE '_етров' выполняет
поиск и выдает все имена, состоящие из шести букв и
заканчивающиеся сочетанием «етров» (Петров Ветров и
[ ]
Любой одиночный
символ, содержащийся в
диапазоне ([a-f]), т.е. в наборе
([abcdef])
Оператор
where
Фамилия_автора LIKE '[Л-С]омов' выполняет
поиск и выдает все фамилии авторов, заканчивающиеся
на «омов» и начинающиеся на любую букву в
промежутке от «Л» до «С», например Ломов, Ромов,
Сомов и т.п. При выполнении операции поиска в
диапазоне символы, включенные в диапазон,
могут изменяться в зависимости от правил сортировки
[^]
Любой символ,
отсутствующий в
диапазоне ([^a-f]) или
в наборе ([^abcdef]).
Оператор
where
Фамилия_автора LIKE 'ив[^а]%' выполняет
поиск и выдает все фамилии,
начинающиеся на «ив», в которых третья буква
отличается от «а»