Предложение WHERE указывает процессору базы данных на необходимость ограничения количества отбираемых записей согласно одному или нескольким заданным критериям. Критерий — это логическое выражение, результатом оценки которого является либо "истина" (true), либо "ложь" (false). В языке SQL существует много аналогичных выражений эквивалентности, знакомых пользователям Visual Basic (например: >0 и =' Smith ').

Предположим, вам нужно отобрать только тех заказчиков, которые проживают в Калифорнии (СА). Для этого можно использовать приведенный ниже запрос SQL.

SELECT FirstName, LastName, State FROM tblCustomer

WHERE State = 'CA'

В результате выполнения этого запроса будет извлечена запись с данными о клиенте с именем Daisy Klein.

Обратите внимание также на то, что для обозначения текстовой строки в предложении WHERE используется одиночная кавычка. Подобное обозначение удобно тем, что отличается от обозначения текстовой строки в Visual Basic двойной кавычкой, а инструкции SQL иногда встраиваются в код Visual Basic.

Можно создавать более сложные предложения WHERE, связывая два и более критерия с помощью логических операторов AND или OR. Например, необходимо отобрать всех клиентов, проживающих в городе Денвер (Denver) штата Колорадо (СО), т.е. вас не интересуют те клиенты, которые проживают в других городах этого штата. Для этого нужно задать два критерия и связать их оператором AND, как показано в приведенном ниже примере.

SELECT FirstName, LastName, City, State

FROM tblCustomer

WHERE (State = 'CO') AND (City = 'Denver')

В результате выполнения этого запроса будет извлечена запись с данными о клиенте с именем ThurstonRyan, который живет в городе Денвер, штат Колорадо. Если в этом городе живут другие клиенты, то в результате выполнения данного запроса будут извлечены записи с информацией о них. Однако при этом не будут извлекаться сведения о клиентах, которые проживают в городах с тем же названием, но в других штатах.

Если вас интересует информация о клиентах в штатах Вашингтон (WA) и Калифорния (СА), воспользуйтесь оператором OR, чтобы связать два критерия, как показано ниже.

SELECT FirstName, LastName, City, State

FROM tblCustomer

WHERE State = 'CO' OR State = 'CA'

В результате выполнения этого запроса будут извлечены три записи с данными о клиентах в штатах Вашингтон и Калифорния. Из сказанного выше следует, что для отбора данных из таблицы с помощью условий AND и OR можно составить практически любую комбинацию критериев в предложении WHERE.

НА ЗАМЕТКУ

Ключ к успешной разработке приложений клиент/сервер – выработка такой тактики, согласно которой приложение-клиент не заказывало бы слишком много записей в одной выборке. Это гарантирует, что приложения будут выполняться быстро и компьютеры ваших пользователей не зависнут. Залог успеха вашей работы — рациональное использование предложения WHERE.

 

Операторы, используемые в предложении WHERE

 

При построении предложения WHERE можно использовать операторы, перечисленные в табл. 2.1.

Таблица 2.1. операторы, используемые в предложении WHERE 

Оператор	Функция
<	Меньше
<=	Меньше или равно
>	Больше
>=	Больше или равно
=	Равно
<>	Не равно
BETWEEN	Внутри диапазона значений
LIKE	Соответствует образцу
IN	Входит в список значений

 

Оператор BETWEEN

Этот оператор возвращает все записи, значения которых лежат внутри определенных вами границ. Например, для того чтобы отобрать все заказы, оформленные за период с 4 января по 5 июня 2001 года, необходимо написать приведенную ниже инструкцию SQL.

SELECT

FROM tblOrder

WHERE OrderDate BETWEEN '1/4/2001' AND '6/5/2001'

В результате выполнения этого запроса будут извлечены записи, показанные на рис. 2.4.

Обратите внимание, что значения даты в SQL Server обозначаются символом одиночной кавычки. Разработчикам, которые имеют опыт работы с датами в Microsoft Access и привыкли использовать для этого символ #, придется учесть это новшество и заменить символы # одиночными кавычками.

РИС. 2.4. Результаты выполнения запроса для таблицы tblOrder с использованиям предложения SELECT и оператора BETWEEN

Границы оператора BETWEEN являются включающими; это значит, что если вы запрашиваете информацию обо всех заказах, оформленных за период с 4 января по 5 июня 2001 года, то в результирующий набор включаются заказы, которые были оформлены как 4 января, так и 5 июня.

 

Оператор LIKE и символы шаблона

С помощью оператора LIKE отбираются записи, соответствующие заданному шаблону. Этот шаблон обычно состоит из так называемых подстановочных символов (wildcard characters) * или с которыми вы, возможно, уже знакомы по работе с файловыми системами MS DOS или Windows.

Символ процента (%) означает частичное соответствие. Например, чтобы отобрать в таблице tblCustomer все записи, в которых фамилия начинается с буквы J, можно воспользоваться приведенным ниже запросом.

SELECT ID, FirstName, LastName, Address, City, State

FROM tblCustomer

WHERE [LastName] LIKE 'J%'

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

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

SELECT ID, FirstName, LastName, Address, PostalCode

FROM tblCustomer

WHERE PostalCode LIKE '80___'

В результате выполнения этого запроса будут извлечены записи для тех клиентов, почтовый индекс которых начинается с числа 80.

Оператор LIKE можно использовать также для отбора записей на основе вхождения в диапазон определенных алфавитных или числовых значений. Например, чтобы возвратить список клиентов, фамилии которых начинаются с букв в диапазоне от А до М, используйте приведенную ниже команду SQL.

SELECT ID, FirstName, LastName

FROM tblCustomer

WHERE LastName LIKE '[A-M]%'

В результате выполнения этого запроса будут извлечены пять записей для тех клиентов, фамилии которых начинаются с букв в диапазоне от А до М.

НА ЗАМЕТКУ

Действие символов подстановки в Microsoft Access отличается от действия этих же символов в стандартном SQL. В Microsoft Access символ звездочки используется вместо символа процента для подбора любого числа произвольных символов, а символ знака вопроса – вместо символа подчеркивания для подбора любого одиночного символа. В стандартном SQL символ подчеркивания используется для подбора i любого числа произвольных символов, а символ процента – для подбора любого одиночного символа.

 

Оператор IN

Этот оператор используется для отбора записей, которые соответствуют элементам из заданного списка значений. Например, чтобы отобрать всех клиентов в штатах Колорадо (СО) или Висконсин (WI), воспользуйтесь приведенной ниже инструкцией SQL.

SELECT FirstName, LastName, State

FROM tblCustomer

WHERE State IN ('CO', 'WI')

В результате выполнения этого запроса будут извлечены три записи для тех клиентов, которые живут в штатах Колорадо или Висконсин. Как видите, с помощью оператора IN можно получить те же результаты, что и с помощью оператора OR. Некоторые разработчики предпочитают применять оператор IN при использовании нескольких критериев, поскольку в таком случае команда SQL выглядит более аккуратно.

 

Предложение ORDER BY формирует для процессора баз данных команду на сортировку отобранных записей. Можно сортировать по любому полю или нескольким полям, причем как в возрастающей, так и в убывающей последовательности. Для чтобы задать порядок сортировки, добавьте в конец обычного запроса SELECT предложение ORDER BY, а за ним укажите поле или поля, по которым нужно выполнить сортировку. Например, чтобы отобрать список клиентов, отсортированный по фамилии, воспользуйтесь приведенной ниже инструкцией SQL.

SELECT ID, FirstName, LastName

FROM tblCustomer

ORDER BY LastName

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

 

Сортировка в убывающей последовательности

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

SELECT *

FROM tblOrder

order BY OrderDate desc

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

 

Сортировка по нескольким полям

Для того чтобы отсортировать записи по нескольким полям, после предложения ORDER BY перечислите поля друг за другом через запятую. Например, чтобы отсортировать записи в таблице tblCustomer по фамилии, а затем по имени, воспользуйтесь приведенной ниже командой SQL.

SELECT FirstName, LastName, City, State

FROM tblCustomer

ORDER BY LastName, FirstName

В результате выполнения этого запроса из таблицы tblCustomer будут извлечены все записи, отсортированные по фамилиям, а затем по именам (например, за клиентом Betty Klein будет располагаться клиент Daisy Klein).

 

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

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

SELECT ID, OrderDate, CustomerID

FROM tblOrder

ORDER BY OrderDate DESC

Обратите внимание, что ключевое слово DESC сортирует результирующий набор по убыванию. В результате выполнения этого запроса из таблицы tblOrder будут извлечены сведения о заказах каждого клиента, причем сначала будут располагаться самые последние заказы. Все просто прекрасно, за исключением того, что в базе данных, которая хранит информацию обо всех когда-либо выполненных заказах, придется просмотреть тысячи записей, в то время как вас интересуют только три наиболее крупных заказа. Поэтому вместо предыдущей команды попробуйте выполнить приведенную ниже инструкцию SQL.

SELECT TOP 3 *

FROM tblOrder

ORDER BY OrderAmount DESC

В результате выполнения этого запроса из таблицы tblOrder будут извлечены три записи для наиболее крупных заказов.

Почему же запрос вернул четыре записи вместо запрошенных трех? Такой запрос (ТОР 3) вовсе не гарантирует, что будут возвращены только три записи. Возможно, что результирующий набор будет содержать одну или две записи (или даже ни одной), если в таблице содержится только такое количество записей. А если на последнее место в результирующем наборе претендуют две и более записи, то вполне возможно, что будут возвращены четыре или даже большее количество записей.

В синтаксисе SQL нет понятия BOTTOM N, но зато есть возможность возвратить заданное количество последних записей в таблице. Для того чтобы создать такой запрос, достаточно отсортировать записи в возрастающей последовательности (т.е. от самого малого значения к самому большому), как показано ниже.

SELECT TOP 3 *

FROM tblOrder

ORDER BY OrderDate

Этот запрос показывает три самых "древних" заказа в базе данных.

Сортировка данных в порядке возрастания в SQL принимается по умолчанию. Поэтому нет необходимости использовать ключевое слово ASC для задания порядка сортировки по возрастанию.

 

Создание запросов TOP PERCENT

Можно писать запросы, возвращающие записи, количество которых определяется заданным процентом от общего количества записей в таблице. Например, если у вас есть таблица с 1000 записей и необходимо возвратить один процент первых записей, то, как правило, будет отображено 10 записей. (Возможно, будет отображено более 10 записей, если несколько записей имеют одинаковое значение. Такой же случай рассматривался и для запроса ТОР N.)

Для возврата первых записей в результирующий набор, количество которых задано процентным отношением к общему количеству записей в таблице, используется предложение TOP N PERCENT. Например, чтобы отобрать первые 20% от неоплаченных заказов в таблице tblOrder, воспользуйтесь приведенной ниже командой SQL.

SELECT TOP 20 PERCENT *

FROM tblOrder

ORDER BY OrderDate DESC

В результате выполнения этого запроса из таблицы tblOrder будут извлечены две записи для самых последних заказов, которые составляют 20% от 10 строк таблицы tblOrder.

 

Для выборки связанной информации из нескольких таблиц используется объединение (join). Чтобы создать объединение в запросе, необходимо определить первичные (primary) и внешние (foreign) ключи в таблицах, участвующих в объединении (эти понятия обсуждаются в главе 1, "Основы построения баз данных"). Например, рассмотрим две связанные таблицы с показанными ниже структурами.

Хотя в таблице tblOrder хранится информация о заказах, а в таблице tblCustomer — информация о клиентах, вполне вероятно, что вам потребуется, например, отобрать такую информацию о заказах клиентов, как показано ниже.

Такой результирующий набор нетрудно получить, используя объединение, несмотря на то что необходимые данные хранятся в разных таблицах. Можно надеяться на получение нужных данных, если сообщить процессору баз данных о том, что первичный ключ таблицы tblCustomer(ID) связан с внешним ключом ((CustomerID)) таблицы tblOrder.

НА ЗАМЕТКУ

Обратите внимание, что в этом результате объединения один и тот же клиент отображен дважды, хотя его имя было введено в базу данных один раз. Это значит, что клиент имеет несколько заказов. Иными словами, при нескольких заказах одного и того же клиента вам не пришлось вводить в базу данных информацию об этом клиенте несколько раз. Вместе с тем в результате выполнения запроса иногда можно получить избыточную информацию. Для исправления такой ситуации можно применять несколько разных методов.

 

Выражение объединения в SQL

В SQL Server объединение можно установить с помощью выражения эквивалентности между двумя полями, например:

SELECT FirstName, LastName, OrderDate

FROM tblOrder INNER JOIN tblCustomer

ON tblOrder.CustomerID = tblCustomer.ID

Этот запрос SQL возвращает информацию обо всех клиентах, которые имеют заказы в таблице tblOrder. В результате выполнения запроса возвращаются три столбца данных: поля FirstName и LastName из таблицы tblCustomer, а также поле OrderDate из таблицы tblOrder.

Обратите внимание, что в запросе с объединением таблиц при использовании полей, имеющих одинаковые имена, но принадлежащих разным таблицам, необходимо перед именем поля вставлять ссылку на соответствующую таблицу (например, tblOrder.ID вместо ID). В большинстве случаев при использовании конструктора представлений в среде Visual Studio .NET для создания запроса интегрированная среда разработки способна определить выполняемые действия и дополнить выражение недостающими частями. Как уже сообщалось ранее, в данной книге примеры инструкций SQL приводятся в наиболее кратком виде, а необязательные части применяются только в случае необходимости.

 

Использование конструктора представлений для создания объединений

Поскольку создание объединений может составлять самую сложную часть запросов, особенно когда задействовано более двух таблиц, неплохо было бы при создании таких запросов иметь некоторое подспорье. К счастью, в Visual Basic предусмотрен конструктор представлений (View Designer), благодаря которому создание запроса с объединением нескольких таблиц значительно упрощается. При использовании конструктора представлений нет необходимости запоминать сложный синтаксис объединения в SQL. Вместо этого можно создать объединение графическим путем, выполнив приведенные ниже действия.

1. В окне Server Explorer создайте новое представление для базы данных Novelty.

2. После этого появится диалоговое окно Add Table (Создать таблицу), в котором следует указать таблицы tblCustomer и tblOrder, а затем щелкнуть на кнопке Close. Схема представления в окне конструктора представлений показана на рис. 2.5.

РИС. 2.5. Создание объединения двух таблиц в окне конструктора представлений

Обратите внимание на то, что конструктор представлений автоматически создает объединение между двумя таблицами на основе известного ключевого поля ID в таблице tblCustomer и явно заданного ранее отношения с полем CustomerID в таблице tblOrder.

После выполнения запроса на основе объединения двух таблиц в окне конструктора представлений будут отображены извлеченные данные, как показано на рис. 2.6.

 

Использование внешних объединений

Обычное (внутреннее) объединение (inner join) возвращает записи из двух таблиц, если значение первичного ключа первой таблицы соответствует значению внешнего ключа второй таблицы, связанной с первой. Предположим, необходимо получить все записи из одной таблицы, участвующей в объединении, вне зависимости от того, существуют ли связанные записи в другой таблице. В этом случае необходимо использовать внешнее объединение (outer join).

Например, для извлечения списка клиентов и заказов, в который включены также клиенты, не имеющие неоплаченных заказов, можно использовать приведенный ниже запрос.

SELECT FirstName, LastName, OrderDate

FROM tblCustomer LEFT OUTER JOIN

tblOrder ON tblCustomer.ID = tblOrder.CustomerID

Обратите внимание, что в предложении LEFT JOIN используется синтаксис имя_таблицы.имя_поля. Более длинное имя позволяет избежать неоднозначности при использовании полей с одинаковыми именами, поскольку поле ID существует как в так и в tblOrder. Фактически предложение LEFT OUTER JOIN означает, что будут отображены все данные таблицы tblCustomer, которая находится в левой стороне выражения tblCustomer.ID = tblOrder.CustomerID.

РИС. 2.6. Выполнение запроса на основе объединения двух таблиц в окне конструктора представлений

Этот запрос возвращает приведенный ниже набор записей.

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

Правое внешнее объединение (right join) аналогично левому внешнему объединению (left join), за исключением того, что оно возвращает все записи из второй таблицы, участвующей в объединении (имеется в виду таблица с правой стороны), независимо от того, есть ли соответствующие им записи в первой таблице (расположенной с левой стороны). (Левое и правое объединения являются разновидностями внешнего объединения и в определенных обстоятельствах могут возвращать идентичные результаты.)

 

Часто требуется создавать запросы, подобные следующему: "Сколько заказов поступило вчера?" При этом вас не интересует, кто оформил заказ, вы только хотите знать количество вчерашних заказов. Это можно сделать, используя запросы, группирующие итоговые функции.

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

SELECT CustomerID, COUNT(CustomerID) AS TotalOrders

FROM tblOrder

GROUP BY CustomerID

Результат выполнения такого запроса приведен ниже.

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

Для отображения имен клиентов вместо их идентификаторов нужно просто объединить с результатами запроса данные из таблицы tblCustomer.

SELECT tblOrder.CustomerID, FirstName, LastName

COUNT(dbo.tblOrder.CustomerID) AS TotalOrders 

FROM tblOrder INNER JOIN tblCustomer

ON tblOrder.CustomerID = tblCustomer.ID

GROUP BY FirstName, LastName, CustomerID

После выполнения такого запроса будет получен приведенный ниже результат.

В этом случае предложение GROUP BY содержит поле CustomerID вместе с объединенными полями FirstName и LastName из таблицы tblCustomer. При использовании предложения GROUP BY в него необходимо включить все поля, по которым группируются извлекаемые записи. В данном случае идентификатор клиента и его имя участвуют в группировании данных и потому присутствуют в предложении GROUP BY. (К счастью, если вы забудете выполнить эту операцию, среда Visual Studio .NET автоматически предложит вам сделать это.)

 

Применение предложения HAVING для группирования данных в запросах

Как уже отмечалось выше, критерий запроса служит для ограничения количества извлекаемых записей. В обычных запросах для включения критериев используется предложение WHERE, в запросах с группированием — предложение HAVING. Эти предложения применяются совершенно одинаково, за исключением того, что HAVING относится к агрегированным строкам (т.е. к результату группирования), a WHERE – к отдельным строкам. Это довольно незначительное отличие, потому что в 9 случаях из 10 они дают совершенно одинаковый результат. Например для создания отчета о продажах клиента Jane с группированием данных можно использовать приведенный ниже запрос.

SELECT tblOrder.CustomerID, FirstName, LastName,

COUNT(dbo.tblOrder.CustomerID) AS TotalOrders

FROM tblOrder INNER JOIN tblCustomer

ON tblOrder.CustomerID = tblCustomer.ID

GROUP BY FirstName, LastName, CustomerID

HAVING FirstName = 'Jane'

Этот запрос возвращает одну запись для клиента Jane Winters с указанием двух сделанных ею заказов. Допустим, теперь нужно получить список активных покупателей, т.е. клиентов, сделавших более одного заказа. Поскольку агрегированное количество заказов хранится в вычисленном поле TotalOrders, можно предположить, что для определения таких клиентов допустимо использовать выражение HAVING TotalOrders > 1. К сожалению, это выражение некорректно, так как TotalOrders – это не поле базы данных, а вычисленное поле. Вместо этого следует включить данное вычисление в предложение HAVING показанного ниже запроса.

SELECT tblOrder.CustomerID, FirstName, LastName,

COUNT(dbo.tblOrder.CustomerID) AS TotalOrders

FROM tblOrder INNER JOIN tblCustomer

ON tblOrder.CustomerID = tblCustomer.ID

GROUP BY FirstName, LastName, CustomerID

HAVING (COUNT(tblOrder.CustomerID) > 1)

После выполнения этого запроса будут получены три строки, каждая из которых содержит номер, имя, фамилию и количество заказов для каждого клиента, который сделал более одного заказа.

 

Функция SUM

Ваши возможности в подведении итогов не ограничены простым подсчетом записей. Используя функцию SUM, можно генерировать итоговые результаты для всех возвращаемых записей по любым числовым полям. Например, для создания запроса, который генерирует итоги по количеству заказанных товаров каждым клиентом, необходимо написать следующую команду SQL:

SELECT OrderID, SUM(Quantity) AS TotalItems

FROM tblOrderItem

GROUP BY OrderID

Этот запрос возвращает приведенный ниже результирующий набор.

Как и в предыдущих примерах группирования, если вы захотите извлечь дополнительную связанную информацию (например, имя и фамилию клиента), следует использовать объединение с другой таблицей. Помните, что для агрегирования данных потребуется сгруппировать данные по крайней мере по одному полю.

 

Перечень итоговых функций

В табл. 2.2 перечислены все итоговые функции, доступные в SQL.

Таблица 2.2. Итоговые функции SQL

Синтаксис этих функций, по сути, соответствует синтаксису функции COUNT, которая рассматривалась в предыдущем разделе. Например, для ежедневного вычисления среднего количества товаров в каждом заказе воспользуйтесь приведенным ниже запросом SQL.

SELECT AVG(tblOrderItem.Quantity) AS AverageLineItemQuantity

FROM tblOrder INNER JOIN

tblOrderItem ON tblOrder.ID = tblOrderItem.OrderID

Этот запрос возвращает значение 2, т.е. количество товаров в заказах всех клиентов.

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

SELECT tblOrderItem.OrderID, SUM(Quantity * Price)

AS OrderTotal

FROM tblInventory INNER JOIN

tblOrderItem ON tblItem.ID = tblOrderItem.OrderID GROUP BY OrderID

Этот запрос возвращает приведенный ниже результирующий набор.

 

Команда манипулирования данными (data manipulation command) — это команда SQL, которая изменяет записи. Такие команды создаются на языке манипулирования данными DML, который является подмножеством языка SQL. Эти команды не возвращают записи, а только изменяют их в базе данных.

DML-команды SQL обычно применяются для изменения большого объема данных на основе заданного критерия. Например, для повышения на 10% цены всех товаров следует использовать запрос на обновление, который автоматически выполнит такие изменения для всех товаров.

В среде Visual Studio .NET предусмотрен очень мощный интерфейс для выполнения DML-команд. Действительно, инструменты среды Visual Studio .NET могут пре

доставить полезную информацию (например, правильную строку подключения для соединения с базой данных) или генерировать в окне конструктора основные DML-команды при извлечении данных из таблицы или изменении типа запроса.

НА ЗАМЕТКУ

Примеры в этом разделе демонстрируют способы изменения данных в базе данных Novelty. Если после многочисленных попыток изменить данные вы хотите вернуть базу данных Noveltу в ее прежнее состояние, то ее можно переустановить, запуская описанный во введении к этой книге сценарий.

На низком уровне (т.е. не на уровне графического интерфейса пользователя) DML-команды SQL можно использовать с помощью следующих двух инструментов:

• Microsoft SQL Server Query Analyzer (или просто Query Analyzer) — инструмент с графическим интерфейсом пользователя для создания запросов и команд для SQL Server;

• osql — используемый в режиме командной строки процессор запросов.

Вы можете использовать любой из этих инструментов, а в данной главе применяется Query Analyzer, который обладает более широкими возможностями и более удобен в употреблении, чем процессор запросов osql. В настоящей главе основное внимание сосредоточено на фактически выполняемых командах, а не на методах использования графического интерфейса Query Analyzer. Инструмент Query Analyzer находится в группе программ Microsoft SQL Server. (В главе 7, "ADO.NET: дополнительные компоненты", более подробно рассматриваются способы применения DML-команд в среде Visual Studio.NET.)

 

Запросы на обновление

Запрос на обновление может изменить сразу целую группу записей. Этот запрос состоит из трех частей:

• предложение UPDATE, которое указывает на обновляемую таблицу;

• предложение SET, задающее данные для обновления;

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

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

UPDATE tblItem

SET Price = Price * 1.1

SELECT * FROM tblItem

Команда SELECT, которая располагается вслед за предложением UPDATE, не обязательна и предназначена для просмотра результатов обновления.

Ниже приведены значения полей после выполнения данного запроса на обновление.

Для ограничения числа записей, подвергаемых воздействию запроса на обновление, достаточно добавить в запрос SQL предложение WHERE. Например, чтобы применить повышение цен только к дорогим товарам, стоимость которых больше $100, откорректируйте запрос так, как показано ниже.

UPDATE

SET Price = Price * 1.1

WHERE Price > 100

Эта команда увеличивает на 10% цену на товары, текущая цена которых больше $100.

 

Запросы на удаление

С помощью запроса на удаление (delete query) можно одним махом удалить одну или несколько записей. Например, чтобы удалить все заказы, которые были оформлены до (но не во время) последнего празднования Дня всех святых, воспользуйтесь запросом SQL, код которого приведен ниже.

DELETE *

FROM tblOrder

WHERE OrderDate < '10/31/2002'

 

Запрос на добавление записей

Запрос на добавление (append query) используется в двух случаях:

• при добавлении одиночной записи в таблицу;

• при копировании одной или нескольких записей из одной таблицы в другую.

Для создания запроса на добавление используйте предложение SQL INSERT. Точный синтаксис запроса зависит от того, добавляете ли вы одну запись или копируете несколько. Например, для добавления одной новой записи в tblOrder можно использовать приведенный ниже запрос.

INSERT INTO tblOrder(CustomerID, OrderDate)

VALUES (119, '6/16/2001')

При выполнении этого запроса в таблице tblOrder создается новый заказ для клиента с идентификационным номером 119 и датой 16 июня 2001 года.

НА ЗАМЕТКУ

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

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

CREATE TABLE tblOrderArchive (

 ID [int] NOT NULL,

 CustomerID [int] NULL,

 OrderDate [datetime] NULL)

НА ЗАМЕТКУ

Как уже сообщалось выше, команды SQL для создания и управления структурой базы данных называются командами манипулирования данными или DML-командами. Более подробно они рассматриваются далее в главе.

Ниже приведена инструкция SQL для копирования старых записей из tblOrder в tblOrderArchive.

INSERT INTO tblOrderArchive

SELECT * FROM tblOrder

WHERE OrderDate < '6/1/2001'

При выполнении этой инструкции SQL в таблицу tblOrderArchive копируются все записи, содержащие заказы, которые были оформлены до 1 июня 2001 года.

 

Запросы на основе команды SELECT INTO

Запрос на основе команды SELECT INTO аналогичен запросу на добавление, за исключением того, что он создает новую таблицу и сразу же копирует в нее записи. В Microsoft Access он называется запросом на создание таблиц (make-table query). Так, в предыдущем примере все записи из таблицы tblOrder копировались в таблицу tblOrderArchive, исходя из предположения, что таблица tblOrderArchive уже существует. Вместо этого запроса для копирования тех же записей в новую таблицу с такой же структурой, как и у оригинала, воспользуйтесь приведенным ниже запросом SQL.

SELECT * INTO tblOrderArchive

FROM tblOrder

НА ЗАМЕТКУ

Этот запрос копирует все записи из tblOrder в новую таблицу с именем tblOrderArchive. Однако если такая таблица уже существует, эта команда не будет выполнена. Это отличается от результата выполнения данного запроса в Microsoft Access.

Если его выполнить в окне конструктора запросов программы Access при условии, что таблица tblOrderArchive уже существует, то процессор баз данных удалит исходную таблицу и заменит ее вновь созданной, которая будет заполнена содержимым скопированных записей. В SQL Server для удаления таблицы нужно использовать DDL-команду DROP TABLE.

В запросе на основе команды SELECT INTO можно применить критерий отбора (с помощью предложения WHERE) точно так же, как это делалось в запросе на добавление (см. предыдущий раздел). Это дает возможность копировать подмножество записей из исходной таблицы в новую, которая формируется запросом на создание таблицы.

 

Команды языка определения данных (Data Definition Language — DDL) представляют собой инструкции SQL, которые позволяют создавать элементы структуры базы данных, манипулировать ими и удалять. Используя DDL, можно создавать и удалять таблицы, а также изменять структуру этих таблиц.

Команды DDL относятся к наиболее редко используемым инструкциям в SQL в основном потому, что существует множество прекрасных инструментов, которые позволяют легко справиться с задачами создания таблиц, полей и индексов. В среде Visual Studio.NET DDL-команды SQL используются незаметно для разработчика при создании схемы базы данных в окне Server Explorer, но в ней не предусмотрены инструменты для непосредственного выполнения команд SQL по отношению к базе данных. Для этого следует применять инструменты Query Analyzer и osql либо использовать DDL-команды непосредственно в коде.

Но если вы работаете в среде клиент/сервер, то для создания структуры базы данных удобнее использовать DDL-команды. Подобно командам манипулирования данными, DDL-команды не возвращают результирующих наборов (поэтому их и называют не запросами, а командами).

 

Создание элементов базы данных с помощью предложения CREATE

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

CREATE TABLE tblRegion (

 State char (2),

 Region varchar (50)

)

Тип данных char(2), означает, что процессор баз данных должен создать текстовое поле фиксированной длины для хранения максимум двух символов, а выражение varchar (50) указывает на создание поля с переменной длиной до 50 символов.

При выполнении этого запроса будет создана таблица со следующей структурой:

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

 

Добавление ограничений в таблицу

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

Ограничение создается с помощью предложения SQL CONSTRAINT, которое принимает два параметра: имя индекса и имя поля или полей, в индексации которых вы заинтересованы. Можно объявить индекс с помощью ключевого слова UNIQUE или PRIMARY, и тогда этот индекс будет означать, что поле может принимать только уникальные значения или что поле (поля) служит первичным ключом таблицы.

НА ЗАМЕТКУ

Понятие именованных индексов может показаться несколько странным для тех, кто привык работать в Microsoft Access, поскольку Access скрывает имена индексов в своем пользовательском интерфейсе. Однако к имени индекса можно получить доступ программным путем.

Например, усовершенствовать таблицу tblRegion, созданную в предыдущем примере, можно добавлением уникального индекса к полю State, поскольку оно используется в объединении. Ниже представлена команда SQL, создающая эту таблицу с использованием предложения CONSTRAINT.

CREATE TABLE tblRegion (

 State char (2),

 Region varchar (50),

 CONSTRAINT StateIndex UNIQUE (State)

)

Этот запрос создает таблицу с уникальным индексом по полю State, причем этот индекс имеет имя StateIndex.

Несмотря на то что в приведенном выше примере индексируется поле State, больше смысла было бы в том, чтобы сделать поле State первичным ключом таблицы. В этом случае вы получили бы гарантию того, что в поле State не было не только повторяющихся значений, но и значений NULL. Ниже приводится команда SQL, создающая таблицу tblRegionNew, в которой первичным ключом является поле State.

CREATE TABLE tblRegionNew (

 State char Region varchar (50),

 CONSTRAINT StatePrimary PRIMARY KEY (State)

)

 

Назначение внешнего ключа

Для того чтобы назначить поле в качестве внешнего ключа, используйте ограничение FOREIGN KEY. Например, в структуре нашей базы данных существует отношение типа один-ко-многим между полем State таблицы tblRegion и соответствующим полем State таблицы tblCustomer. Команда SQL, используемая для создания таблицы tblCustomer, может выглядеть так, как показано ниже.

CREATE TABLE tblCustomer (

 ID int identity(1,1),

 FirstName varchar (20),

 LastName varchar (30),

 Address varchar (100),

 City varchar (75),

 State varchar (2),

 CONSTRAINT IDPrimary PRIMARY KEY (ID),

 CONSTRAINT StateForeign FOREIGN KEY (State)

 REFERENCES tblRegionNew (State)

)

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

 

Создание индексов с помощью команды CREATE INDEX

Помимо создания индексов в процессе формирования таблицы (с помощью предложения CONSTRAINT), можно также создавать индексы уже после того, как таблица сформирована (с помощью предложения CREATE INDEX). Это полезно в тех случаях, когда таблица уже существует (в то время как предложение CONSTRAINT применяется для формирования индексов только в момент создания таблицы).

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

CREATE INDEX StateIndex

ON tblCustomer (State)

Для того чтобы создать уникальный индекс, используйте ключевое слово UNIQUE, как показано ниже.

CREATE UNIQUE INDEX StateIndex

ON tblRegion (State)

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

CREATE UNIQUE NONCLUSTERED INDEX StateIndex ON tblRegion (

 State

) ON [PRIMARY]

 

Удаление таблиц и индексов с помощью предложения DROP

Удалять элементы базы данных можно с помощью предложения DROP. Например, чтобы удалить таблицу, используйте приведенную ниже команду SQL.

DROP TABLE tblRegion

С помощью предложения DROP можно также удалить индекс в таблице, как показано ниже.

DROP INDEX tblRegion.StateIndex

Обратите внимание, что для удаления первичного ключа нужно знать имя этого ключа.

У вас также есть возможность удалять отдельные поля таблиц. Для этого нужно использовать предложение DROP внутри предложения ALTER TABLE, как показано в следующем разделе. А для удаления базы данных применяется команда DROP DATABASE.

 

Модификация структуры таблицы с помощью предложения ALTER

С помощью предложения ALTER можно изменить определения полей в таблице. Например, чтобы добавить поле CustomerType в tblCustomer, используйте приведенную ниже команду SQL.

ALTER TABLE tblCustomer

ADD CustomerType int

Для того чтобы удалить поле из базы данных, используйте предложение DROP COLUMN вместе с предложением ALTER TABLE, как показано ниже.

ALTER TABLE tblCustomer

DROP COLUMN CustomerType

Кроме того, с помощью предложения ALTER TABLE можно добавить в таблицу ограничения. Например, для создания отношения между таблицами tblCustomer и tblOrder с помощью предложения ALTER TABLE используйте приведенную ниже команду SQL.

ALTER TABLE tblOrder

ADD CONSTRAINT OrderForeignKey

FOREIGN KEY (CustomerID)

REFERENCES tblCustomer (ID)

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