Лекция № 5

Тема: "Отбор данных из нескольких таблиц"

План

1. Отбор данных из двух таблиц

2. Выбор данных из нескольких таблиц

3. Способы связывания таблиц

4. Объединение запросов UNION

1. Отбор данных из двух таблиц

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

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

имя_таблицы.имя_поля;

- в разделе From таблицы записываются так:

таблица1 inner join таблица2

- вместо раздела Where указывают раздел On, в котором обязательно указывают условие связывания таблиц:

талица1.поле=таблица2.поле

Далее в разделе Where указываются другие условия.

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

Пример. Вывести ФИО студентов и их оценки. Данные отсортировать по фамилии. Из схемы БД видим, что ФИО студентов находится в таблице students, а оценка - в таблице ocenki. При этом обе таблицы связаны по полю id_stud.

Select fam,

       imya,

       otch,

       ocenka

From students inner join ocenki

On students.id_stud=ocenki.id_stud

Пример. Изменим предыдущий запрос: выведем только оценки по "Математике", при условии, что "Математика" обозначена id_predmet=1

Select fam,

       imya,

       otch,

       ocenka

From students inner join ocenki

On students.id_stud=ocenki.id_stud

Where id_predmet=1

Замечание. Обратите внимание, что предмет мы задали не по названию "математика", а по его номеру. Это связано с тем, что в нашем запросе участвуют две таблицы students и ocenki. Если посмотреть на схему БД, то название предмета храниться в отдельной таблице predmety, которая в запросе не участвует. Если вы хотите отбирать данные по названию предмета, то в запрос нужно будет подклоючить третью таблицу predmety. Как это сделать, будет показано ниже.

Пример. Вывести код студента, фамилию, имя, оценку по физике для всех студентов ("Физика" имеет id_predmet=3). ПО схеме БД видим, что поле id_stud имеется в обеих таблицах, участвующих в запросе (students и ocenki), поэтому для отбора этого поля нужно ОБЯЗАТЕЛЬНО указать, из какой именно таблицы отбирается поле (в нашем примере из таблицы students).

Select students.id_stud,

       fam,

       imya,

       ocenka

From students inner join ocenki

On students.id_stud=ocenki.id_stud

Where id_predmet=3 --Физика

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

Пример. Запишем предыдущий запрос через псевдоним

Select a.id_stud,

       fam,

       imya,

       ocenka

From students as a inner join ocenki as b

On a.id_stud=b.id_stud

Where predmet='Физика'

Пример. Для каждого студента рассчитать его средний балл.

Select fam,

       imya,

       avg(ocenka) as srbal

From students inner join ocenki

On students.id_stud=ocenki.id_stud

Group by fam, imya

Пример. Вывести список студентов, у которых средний балл 4 и выше.

Select fam,

       imya,

       avg(ocenka) as srbal

From students inner join ocenki

On students.id_stud=ocenki.id_stud

Group by fam, imya

Having avg(ocenka)>=4

2. Выбор данных из нескольких таблиц

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

При построении запроса по более чем двум таблицам раздел from записывается так:

From табл1 inner join Табл2 on табл1.поле=табл2.поле

           inner join Табл3 on табл2.поле=табл3.поле

           inner join Табл4 on табл3.поле=табл4.поле и т.д.

Пример. Для каждого студента выведем ФИО, специальность и название группы. ФИО находится в таблице students, специальность - в таблице spec, название группы - в таблице grupy. Таблицы связаны как показано на схеме БД по полям id_spec и id_grup.

Select fam,

       imya,

       otch,

       nazv_spec,

       nazv_grup

From spec inner join grupy on spec.id_spec = grupy.id_spec

          inner join students on grupy.id_grup = students.id_grup

Пример. Определить количество студентов на каждом отделении. Название отделения находится в таблице otdelen, студенты находятся в таблице students. Для того, чтобы таблица otdelen "смогла" обратится к таблице students, необходимо указать полную цепочку связей между этими таблицами. По схеме БД видно, что otdelen связана с spec (по полю id_otd), spec связана с grupy (по полю id_spec), grupy связана с students (по полю id_grup). Запрос с группировкой.

Select nazv_otd,

       count(students.id_stud) as kol_stud

From otdelen inner join spec on otdelen.id_otd=spec.id_otd

             inner join grupy on spec.id_spec=grupy.id_spec

             inner join students on grupy.id_grup=students.id_grup

Group by nazv_otd

Обратите внимание: если запрос строится по нескольким таблицам, то для подсчета количества записей в таблице нельзя использовать функцию

count(*), так как не понятно, по какой именно таблице выполняется расчет. В этом случае используют запись count(таблица.ключевое_поле).

Пример. Для каждой специальности подсчитать количество студентов, у которых по физике оценка не менее 4. Нам нужны данные из таблиц spec (специальность), ocenki (оценка) и predmety (Физика). По схеме БД видно, что для связывания таблицы spec с таблицей ocenki, нам также также понадобятся таблицы grupy и students.

Select nazv_spec,

       count(students.id_stud) as kol_stud

From spec inner join grupy on spec.id_spec=grupy.id_spec

          inner join students on grupy.id_grup=students.id_grup

          inner join ocenki on students.id_stud=ocenki.id_stud

          inner join predmety on ocenki.id_predmet=predmety.id_predmet

Where nazv_predmet='Физика' and ocenka>=4

Group by nazv_spec

Пример. Для каждой группы вывести количество парней и девушек. В запросе участвуют две таблицы: grupy и students (связаны по полю id_grup). Запрос с группировкой.

Select nazv_grup, pol,

       count(students.id_stud) as kol_stud

From grupy inner join students on grupy.id_grup=students.id_grup

Group by nazv_grup, pol

Пример. Вывести список групп, в которых количество девушек больше 10. В запросе участвуют две таблицы: grupy и students (связаны по полю id_grup). Запрос с группировкой.

Select nazv_grup,

       count(students.id_stud)

From grupy inner join students on grupy.id_grup=students.id_grup

Where pol='ж'

Group by nazv_grup

Having count(students.id_stud)>10

3. Способы связывания таблиц

Наиболее часто при связывании таблиц используют операцию внутреннего связывания inner join.

Данная операция говорит, что выводится результат "пересечения" двух таблиц. То есть выводятся данные, которые есть во всех таблицах, участвующие в запросе.

Например, если отобрать оценки всех студентов с помощью запроса вида:

Select fam,

       imya,

       otch,

       nazv_predmet,

       ocenka

From students inner join ocenki on students.id_stud=ocenki.id_stud

              inner join predmety on ocenki.id_predmet = predmety.id_predmet

Order by fam

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

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

Запрос, в котором левая таблица должна отобразиться полностью, реализуется с помощью операции "левого связывания"  left outer join.

В нашем случае запрос имеет вид:

Select fam,

       imya,

       otch,

       nazv_predmet,

       ocenka

From students left outer join ocenki on students.id_stud=ocenki.id_stud

              left outer join predmety on ocenki.id_predmet = predmety.id_predmet

Order by fam

Реже встречается обратная операция, когда из подчиненной таблицы (правой) отбираются все записи, а из главной только связанные. Такое связывание реализуют с помощью операции  right outer join

Существует еще один тип связи, при котором из обеих таблиц отбираются все записи без учета наличия связанны записей как в левой, так и в правой таблицах. Такое связывание реализуют с помощью операции full outer join.

Пример. Рассмотренный выше запрос отображает всех студентов, даже если они не имеют оценок. Но, если мы добавим в таблицу predmety новый предмет и не выставим по нему ни одной оценки, то такой предмет в результате работы запроса не будет отображен, так как он не связан ни с одним студентом и ни с одной оценкой. Свяжем таблицу predmety через "полное" связывание.

Select fam,

       imya,

       otch,

       nazv_predmet,

       ocenka

From students left outer join ocenki on students.id_stud=ocenki.id_stud

              full outer join predmety on ocenki.id_predmet = predmety.id_predmet

Order by fam

4. Объединение запросов UNION

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

запрос1

union

запрос2

[order by поля]

Обратите внимание, что объединяемые запросы кроме отдельных условий отбора могут иметь общий раздел where после операции объединения. Более того, объединяемые запросы автоматически сортируются по всем полям и не должны иметь своих отдельных разделов сортировки. Если хотите изменить порядок сортировки, то используйте общий раздел order by.

Пример. Отобрать фамилию, имя, отчество студентов, а затем фамилию, имя, отчество студенток.

select fam, imya, otch from stud where pol='м'

union

select fam, imya, otch from stud where pol='ж'

Данный пример также может быть решен с помощью одного запроса с составным условием (where pol='м' or pol='ж').

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

Пример. Нужно отобрать список групп из таблицы grupy для заполнения выпадающего списка-справочника на форме. При этом полученный список должен вначале иметь пустое значение.

select null as nazv_grup from grupy

union

select nazv_grup from grupy

order by nazv_grup

Часто такая операция используется для получения соединения full outer join в тех СУБД, где такое соединение не поддерживается (Acces, MySQL, SQLite). Данное соединение можно получить, объединив запрос с left outer join и с right outer join.

Пример. Пусть имеется запрос с полным связыванием таблиц

Select fam,

       imya,

       otch,

       nazv_predmet,

       ocenka

From students full outer join ocenki on students.id_stud=ocenki.id_stud

              full outer join predmety on ocenki.id_predmet = predmety.id_predmet

Order by fam

Необходимо переписать его с помощью объединения запросов для СУБД, в которой полное связывание не поддерживается.

Select fam,

       imya,

       otch,

       nazv_predmet,

       ocenka

From students left outer join ocenki on students.id_stud=ocenki.id_stud

              left outer join predmety on ocenki.id_predmet = predmety.id_predmet

Order by fam

union

Select fam,

       imya,

       otch,

       nazv_predmet,

       ocenka

From students right outer join ocenki on students.id_stud=ocenki.id_stud

              right outer join predmety on ocenki.id_predmet = predmety.id_predmet

Order by fam

Вопросы для самоконтроля

1. Какие правила используют при создании запроса по нескольким таблицам? Для чего создают псевдонимы таблиц?

2. Какие способы связывания таблиц в запросах вы знаете?

3. Опишите принцип объединения нескольких запросов в одном наборе данных.

4. Как организовать полное связывание таблиц (FULL OUTER JOIN) в СУБД, которые такого параметра связывания не имеют?