Лабораторная работа № 15. Многотабличные запросы

Цель работы: освоить методы выборки из базы данных информации, размещённой в нескольких соединяемых таблицах

Для дальнейших упражнений понадобятся следующие таблицы из базы данных «Борей»: «Заказы», «Заказано», «Сотрудники», «Клиенты» и «Товары». Импортируйте в Вашу базу данных недостающие таблицы.

Создайте с помощью оператора CREATE TABLE таблицы «писатель» и книга».и заполните их так, как показано на рис 10. В колонке «Автор» таблицы «книга» указаны коды писателей из таблицы «писатель». Несколько клеток в последних строках обеих таблиц специально оставлены пустыми. В строке 7 таблицы «книги» указан код 12, отсутствующий в таблице «писатель».

Рис. 10. Заполнение таблиц книги и писатели

Таблицы, состоящие из двух столбцов, в одном из которых хранится наименование объекта, а в другом – номер или код, называется справочником. Код из справочника используется в других таблицах вместо имени объекта. Замена наименования кодом уменьшает вероятность ошибки при вводе данных и позволяет при изменении наименования, например фамилии, внести изменение только в одно поле справочника.

Многотабличный запрос с описанием связей между таблицами в предложении WHERE. Запрос на выборку наименований книг и их авторов выглядит так:

SELECT a.Наим,b.ФИО
FROM книга a,писатель b
WHERE a.Автор=b.КодП

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

В многотабличном запросе можно использовать любые условия для отбора данных. Если не использовать никаких условий, то будет выведено декартово произведение из всех строк, таблиц, используемых в запросе, то есть 4*9=36 строк. Удалите из последнего запроса предложение WHERE и выполните получившийся запрос.

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

SELECT a.Наим,b.ФИО
FROM книга a INNER JOIN писатель b ON a.Автор=b.КодП

В результате данного запроса не выбраны названия книг и авторы из записей, не удовлетворяющих условию:

книгa.Автор=писатель.КодП.

Левые и правые внешние соединения. Чтобы кроме данных из записей, удовлетворяющих условию запроса, включить в выборку названия книг из записей, не удовлетворяющих условию запроса, применяется «левое внешнее соединение» (LEFT JOIN):

SELECT a.Наим,b.ФИО
FROM книга a LEFT JOIN писатель b ON a.Автор=b.КодП.

Для книг «Обломов», «Пётр I» и «На дне» поле ФИО в результатах этого запроса останется пустым

Чтобы кроме данных из записей, удовлетворяющих условию запроса, включить в выборку ФИО писателей из записей, не удовлетворяющих условию запроса, применяется «правое внешнее соединение» (RIGHT JOIN):

SELECT a.Наим,b.ФИО
FROM книга a RIGHT JOIN писатель b ON a.Автор=b.КодП

Введите и выполните последние 3 запрос. Сравните результаты выборок между собой и с первым запросом данной лаб. работы.

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

- выбрать все имеющиеся в базе названия произведений Пушкина и Толстого Л.Н. и вместе с ФИО авторов;

- используя таблицы «заказы» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и даты размещения их заказов при условии, что клиенты из Лондона;

- используя таблицы «заказы» «заказано», «товары» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и коды и марки выбранных ими товаров при условии, что названия клиентов начинаются на “F” и заказы оформлял сотрудник Кротов.

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

Пример. Допустим, в таблице «Сотрудники» имеются поля «ФИО», «должность» и «отдел». Требуется найти всех программистов и их начальников. Известно, что программисты работают почти во всех отделах. Запрос выглядит так:

SELECT а.ФИО AS программист,b.ФИО AS начальник_отдела
FROM Сотрудники а,Сотрудники b
WHERE a.должность=’программист’ AND b.должность=’нач_отдела’
AND a.отдел=b.отдел

Результаты будут выглядеть примерно так:

Самостоятельно составьте и выполните следующий запрос: из таблицы «Клиенты» выберите всех продавцов и представителей из тех городов, в которых есть и продавцы и представители.