Создание баз данных

Если таблицы  соединяются по равенству значений пары столбцов (группы столбцов) из различных  таблиц, такая операция называется соединением таблиц по равенству. Соединение по равенству позволяет соединить  только те пары строк, которые действительно  взаимосвязаны друг с другом. Так, например, мы можем соединить таблицы  городов и клиентов по условию  City.IdCity = Customer.IdCity. В таком варианте мы соединяем таблицы осмысленно, так как каждая строка таблицы Customer соединяется только с одной строкой соответствующего города. На базе таблиц City и Customer мы получаем таблицу со столбцами из обеих таблиц, имеющую строки с понятным смыслом. Можно также сказать, что в таблицу Customer вместо столбца IdCity мы вставляем все характеристики (столбцы) соответствующего города из таблицы City. Соединение таблиц используется, когда необходимо вывести значения столбцов:

• разных таблиц;
• одной таблицы, но отвечающих условию, заданному на другой таблице.

Эти два варианта, а также их комбинация, характерны для любого вида соединения, а не только по равенству. Рассмотрим следующие примеры.

SELECT FName, LName, CityName

FROM Customer, City

WHERE Customer.IdCity = City.IdCity

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

До тех  пор, пока запрос относится к одной  таблице, обращение к столбцам по их именам не вызывает проблем - в таблице  все имена столбцов должны быть неповторяющимися. Однако как только запрос соединяет  несколько таблиц, может возникнуть неоднозначность при ссылках  на столбцы с одинаковыми именами  из разных таблиц. Для разрешения этой неоднозначности во фразах SELECT и WHERE (как и в некоторых других фразах) имена столбцов необходимо уточнять именами таблиц. Запишем предыдущий запрос с полным уточнением имен.

SELECT Customer.FName, Customer.LName, City.CityName

FROM Customer, City

WHERE Customer.IdCity = City.IdCity

В этом запросе  мы уточнили имена столбцов во фразах SELECT и WHERE, хотя в предложение SELECT это было не обязательно, так как используются неповторяющиеся имена. Тем не менее, рекомендуется при соединении таблиц для наглядности уточнять имена столбцов. Однако на практике для задания более лаконичных имен часто используют короткие синонимы таблиц, по которым можно сослаться на них в любых других местах запроса. Синоним указывается сразу после имени таблицы в предложении FROM. В частности предыдущий запрос с использование синонимов для таблиц можно записать более компактным образом.

SELECT k.FName, k.LName, c.CityName

FROM Customer k, City c

WHERE k.IdCity = c.IdCity

Следующий запрос отбирает всех клиентов из Казани с фамилией Иванов

SELECT K.IdCust, k.FName

FROM Customer k, City c

WHERE k.IdCity = c.IdCity AND k.LName = 'Иванов' AND c.CityName = 'Казань'

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

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

Запрос: Список всех клиентов, которые когда-либо заказывали товар с кодом 1.

SELECT DISTINCT c.IdCust, c.FName, c.LName

FROM Customer c, [Order] o, OrdItem oi

WHERE c.IdCust = o.IdCust AND o.IdOrd = oi.IdOrd AND oi.IdProd = 1

Сформулируем  общую процедуру составления  многотабличного запроса.

1. Определить множество таблиц, необходимых для ответа на запрос. В это множество должны входить таблицы, на столбцах которых сформулированы условия, а также те, столбцы которых необходимо вывести. Это так называемые базовые таблицы запроса.
2. В структуре взаимосвязанных таблиц найти путь, соединяющий базовые таблицы. Это так называемый путь вычисления запроса. В результате вы получите перечень таблиц, необходимых для формулировки запроса. Это так называемые таблицы запроса.
3. Во фразе FROM перечислить необходимые таблицы.
4. Во фразе WHERE соединить таблицы запроса и при необходимости задать условия отбора строк в базовых таблицах запроса.
5. Во фразе SELECT перечислить выводимые столбцы.

Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список товаров в заданном заказе (по заданному IdOrd). Результат должен включать следующие поля: название товара, цена, количество, стоимость.

Соединения с использованием фразы FROM

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

таблица [INNER | {FULL | LEFT | RIGHT} [OUTER]] JOIN таблица {ON условие}

Внутреннее соединение

В операторе JOIN внутреннее соединение указывается  ключевым словом INNER (впрочем, его можно  опустить, так как соединение двух таблиц является внутренним по умолчанию). Условие соединения указывается после ключевого слова ON. В этом случае внутреннее соединение с помощью фразы FROM JOIN очень похоже на соединение с использованием фразы WHERE. Запишем первый пример с предыдущего раздела с использование оператора JOIN.

Запрос: Список всех клиентов с указанием названий городов, в которых они проживают

SELECT FName, LName, CityName

FROM Customer k JOIN

       City c ON k.IdCity = c.IdCity

При соединении с использованием фразы FROM дополнительное условие можно для увеличения наглядности запроса помещать во фразу WHERE. В этом случае второй пример с предыдущего раздела примет такой вид.

Запрос: Список всех клиентов из Казани с фамилией Иванов

SELECT K.IdCust, k.FName

FROM Customer k INNER JOIN

       City c ON k.IdCity = c.IdCity

WHERE k.LName = 'Иванов' AND c.CityName = 'Казань'

Внешнее соединение

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

С помощью  специальных ключевых слов LEFT OUTER, RIGHT OUTER и FULL OUTER, написанных перед JOIN, можно выполнить соответственно левое, правое и полное соединение. В SQL-выражении запроса таблица, указанная слева от оператора JOIN, называется левой, а указанная справа от него — правой.

При левом  внешнем соединении несоответствующие  записи, имеющиеся в левой таблице, сохраняются в результатной таблице, а имеющиеся в правой — удаляются. Значения столбцов из правой таблицы во всех строках, не имеющих соответствия с левой таблицей, принимают значение NULL.

При правом внешнем соединении несоответствующие  записи, имеющиеся в правой таблице, сохраняются в результатной таблице, а имеющиеся в левой — удаляются. Значения столбцов из левой таблицы во всех строках, не имеющих соответствия с правой таблицей, принимают значение NULL.

Соответственно  левое и правое внешние соединения различаются только порядком следования таблиц.

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

В следующем  примере возвращается полный список городов с указанием количества клиентов из каждого из них

SELECT c.CityName, a.CountCity

FROM City c LEFT OUTER JOIN

 (SELECT IdCity, COUNT(*) AS CountCity

  FROM Customer

  GROUP BY IdCity) a ON c.IdCity = a.IdCity

ORDER BY c.CityName

Если в  данном запросе заменить левое внешнее  соединение на внутреннее, то из результата будут потеряны города, из которых нет ни одного клиента (проверьте это заменив LEFT OUTER JOIN на INNER JOIN и объясните причину разницы). Обратите внимание, что таблица City соединяется не с таблицей, а с подзапросом, которому задан псевдоним a.

Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных (с использование оператора JOIN для соединения таблиц):

• список всех товаров, которые когда-либо заказывал заданный клиент;
• список всех клиентов, не имеющих ни одного заказа.

Множественные операции

Множественные операции выполняются над наборами записей (таблицами), полученными в  результате запросов, и, в свою очередь, возвращают таблицу.

В стандарте SQL множественные операции имеют  следующий синтаксис:

запрос {UNION | INTERSECT | EXCEPT} [DISTINCT | ALL] запрос

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

• UNION — все строки таблиц, возвращенных обоими запросами;
• INTERSECT — только те строки, которые имеются в таблицах обоих запросов;
• EXCEPT — только те строки таблицы первого запроса, которых нет среди строк таблицы второго запроса.

Запросы, содержащие множественные операторы, называются составными.

Стандарт SQL определяет следующие правила относительно повторяющихся строк в таблицах.

• базовые таблицы не могут содержать повторяющихся строк (это принципиальное требование реляционной модели данных);
• результирующие таблицы запросов могут содержать повторяющиеся строки, если это не запрещено ключевым словом DISTINCT во фразе SELECT;
• результирующие таблицы множественных операций не могут содержать повторяющихся строк, если это не разрешено ключевым словом ALL.

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

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

• обе таблицы должны иметь одинаковое количество столбцов;
• соответствующие пары столбцов должны быть одинаковых или совместимых типов.

Объединение наборов записей

Нередко требуется  объединить записи двух или более  таблиц с похожими структурами в  одну таблицу. Иначе говоря, к набору записей, возвращаемому одним запросом, требуется добавить записи, возвращаемые другим запросом. Для этого служит оператор UNION (объединение):

3anpoc1 UNION Запрос2;

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

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

Запрос: Объединение с исключением дублирующих строк

SELECT 1, 'Один'

UNION

SELECT 1, 'Один'

UNION

SELECT 2, 'Два'

Запрос: Объединение с сохранением дубликатов

SELECT 1, 'Один'

UNION ALL

SELECT 1, 'Один'

UNION ALL

SELECT 2, 'Два'

Пересечение наборов записей

Пересечение двух наборов записей осуществляется с помощью оператора INTERSECT (пересечение), возвращающего таблицу, записи в которой содержатся одновременно в двух наборах:

Запрос1 INTERSECT Запрос2;

(SELECT 1, 'Один'

 UNION

 SELECT 2, 'Два'

 UNION

 SELECT 3, 'Три')

 

INTERSECT

 

(SELECT 1, 'Один'

 UNION

 SELECT 2, 'Два'

 UNION

 SELECT 4, 'Четыре')

Разность наборов записей

Для получения  записей, содержащихся в одном наборе и отсутствующих в другом, служит оператор EXCEPT(за исключением):

Запрос1 ЕХCЕРТ Запрос2;

(SELECT 1, 'Один'

 UNION

 SELECT 2, 'Два'

 UNION

 SELECT 3, 'Три')

 

EXCEPT

 

(SELECT 1, 'Один'

 UNION

 SELECT 2, 'Два'

 UNION

 SELECT 4, 'Четыре')

 

Лабораторная  работа №5: Основы Transact SQL: Добавление, изменение и удаление данных в таблицах

Запросы, рассмотренные  ранее, были направлены на то, чтобы получить данные, содержащиеся в существующих таблицах базы данных. Главным ключевым словом таких запросов на выборку данных является SELECT. Запросы на выборку данных всегда возвращают виртуальную таблицу, которая отсутствует в базе данных и создается временно лишь для того, чтобы представить выбранные данные пользователю. При создании и дальнейшем сопровождении базы данных обычно возникает задача добавления новых и удаления ненужных записей, а также изменения содержимого ячеек таблицы. В SQL для этого предусмотрены операторы INSERT (вставить), DELETE (удалить) и UPDATE (изменить). Запросы, начинающиеся с этих ключевых слов, не возвращают данные в виде виртуальной таблицы, а изменяют содержимое уже существующих таблиц базы данных. Запросы на модификацию (добавление, удаление и изменение) данных могут содержать вложенные запросы на выборку данных из той же самой таблицы или из других таблиц, однако сами не могут быть вложены в другие запросы. Таким образом, операторы INSERT, DELETE и UPDATE в SQL-выражении могут находиться только в самом начале.