Джеффри Мак-Манус - Обработка баз данных на Visual Basic®.NET
Хотя в таблице tblOrder хранится информация о заказах, а в таблице tblCustomer — информация о клиентах, вполне вероятно, что вам потребуется, например, отобрать такую информацию о заказах клиентов, как показано ниже.
FirstName LastName OrderDate Jane Winters 9/10/2001 Jane Winters 8/16/2001 Thurston Ryan 7/2/2001 Dave Martin 6/5/2001 Daisy Klein 4/4/2001 ...
Такой результирующий набор нетрудно получить, используя объединение, несмотря на то что необходимые данные хранятся в разных таблицах. Можно надеяться на получение нужных данных, если сообщить процессору баз данных о том, что первичный ключ таблицы 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. Выполнение запроса на основе объединения двух таблиц в окне конструктора представлений
Этот запрос возвращает приведенный ниже набор записей.
FirstName LastName OrderDate John Smith 1/4/2001 John Smith 1/9/2001 Jill Azalia 1/14/2001 Brad Jones <NULL> Daisy Klein 2/18/2001 Daisy Klein 3/21/2001 Daisy Klein 4/4/2001 Dave Martin 6/5/2001 Betty Klein <NULL> Thurston Ryan 7/2/2001 Jane Winters 8/16/2001 Jane Winters 9/10/2001В этот результирующий набор включены все клиенты базы данных, независимо от того, имеют ли они заказы или нет. Для клиентов, не имеющих заказов, в поле OrderDate появится <NULL>, что означает отсутствие данных.
Правое внешнее объединение (right join) аналогично левому внешнему объединению (left join), за исключением того, что оно возвращает все записи из второй таблицы, участвующей в объединении (имеется в виду таблица с правой стороны), независимо от того, есть ли соответствующие им записи в первой таблице (расположенной с левой стороны). (Левое и правое объединения являются разновидностями внешнего объединения и в определенных обстоятельствах могут возвращать идентичные результаты.)
Выполнение вычислений в запросах
В строках запроса допускается выполнение вычислений. Для этого нужно просто заменить имя поля в предложении SELECT именем арифметического выражения. Допустим, вам нужно создать запрос для вычисления налога с продаж для складских запасов (сведения о которых хранятся в таблице tblItem). В приведенном ниже запросе SQL вычисляется налог с продаж с учетной ставкой 7,5% для каждого товара.
SELECT ID, Item, Price, Price * 0.075 AS SalesTax
FROM tblItem
После выполнения этого запроса будут получен показанный ниже результат.
ID Name Price SalesTax 1 Rubber Chicken 5.99 0.44925 2 Hand Buzzer 1.39 0.10425 3 Stink Bomb 1.29 0.09675 4 Disappearing Penny Magic Trick 3.99 0.29925 5 Invisible Ink 2.29 0.17175 6 Loaded Dice 3.49 0.26175 7 Whoopee Cushion 5.99 0.44925Поскольку в этих вычислениях фигурируют деньги, конечный результат нужно округлить до двух десятичных знаков. К счастью, в SQL Server для этого предусмотрена специальная функция ROUND, которая позволяет очень легко выполнить это. Обычно ее используют с указанием двух параметров: собственно десятичного числа и точности, выраженной в виде количества знаков после запятой. Вот как выглядит запрос с функцией ROUND:
