70108

Многотабличные запросы

Лабораторная работа

Информатика, кибернетика и программирование

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

Русский

2014-10-15

127.5 KB

1 чел.

3. Многотабличные запросы

3.1. Выбор источников в секции FROM

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

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

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

Например, получим сведения о книгах и их авторах:

SELECT books.id, title, authors.id, last_name

FROM books, authors

WHERE books.author_id = authors.id;

При использовании ссылок на имена полей, относящихся к разным источникам, может возникнуть неоднозначность. Предположим, команда SELECT получает исходные данные из таблиц books и authors. В каждой из этих таблиц имеется поле с именем id. Без дополнительных уточнений невозможно определить, к какой таблице относится ссылка на поле id в следующей команде:

SELECT id

FROM books, authors;

Для предотвращения неоднозначности в "полные" имена столбцов включается имя таблицы. При этом используется специальный синтаксис, называемый точечной записью (название связано с тем, что имя таблицы отделяется от имени поля точкой). Например, books.id означает поле id таблицы books.

Точечная запись обязательна только при наличии неоднозначности между наборами данных. Ссылка может состоять только из имени поля – при условии, что это имя уникально во всех наборах данных, перечисленных в секции FROM. В приведенном выше примере поле title присутствует только в таблице books, а поле last_name входит только в таблицу authors, поэтому на их имена можно ссылаться без уточнения.

Источникам данных в секции FROM – таблицам, подзапросам и т.д. – можно назначать синонимы в секции AS (по аналогии с отдельными полями). Синоним часто используется для упрощения точечной записи. Наличие синонима для набора данных позволяет обращаться к нему при помощи точечной записи, что делает команды SQL более компактными и наглядными.

Например, получим сведения о книгах и их авторах с упрощением точечной записи при помощи секции AS:

SELECT b.id, title, a.id, last_name

FROM books AS b, authors AS a

WHERE b.author_id = a.id;

Ключевое слово AS не является обязательным при назначении синонима:

SELECT b.id, title, a.id, last_name

FROM books b, authors a

WHERE b.author_id = a.id;

3.2. Операции соединения

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

Довольно часто операции, основанные на операторе JOIN, называют объединением таблиц (наборов записей). Однако термин "объединение" лучше подходит для UNION – оператора теоретико-множественного объединения записей, при котором записи исходных наборов не комбинируются (не соединяются) друг с другом, а просто к одному набору записей добавляется другой набор. В случае оператора JOIN в результатную таблицу попадают записи, полученные из разных наборов путем присоединения одной из них к другой. Поэтому операции, основанные на операторе JOIN, будем называть операциями соединения таблиц (наборов записей).

3.2.1. Перекрестное соединение

Существуют несколько разновидностей соединения, которым соответствуют определенные ключевые слова, добавляемые к слову JOIN. Так, например, декартово произведение является операцией перекрестного соединения. В SQL-выражении для обозначения этой операции используется оператор CROSS JOIN. Результат перекрестного соединения принципиально не отличается от перечисления источников через запятую.

Пусть в базе данных имеются следующие две таблицы:

Сотрудники (Номер_сотрудника, Фамилия, Имя, Номер_отдела);

Отделы (Номер_отдела, Название).

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

SELECT *

FROM Сотрудники CROSS JOIN Отделы;

или

SELECT *

FROM Сотрудники, Отделы;

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

Сотрудники

Номер_сотрудника

Фамилия

Имя

Номер_отдела

1

Иванов

Иван

1

2

Петров

Петр

2

3

Сидоров

Сидор

1

Отделы

Номер_отдела

Название

1

Отдел автоматизации

2

Отдел кадров

Декартово произведение

Номер_
сотрудника

Фами
лия

Имя

Сотрудники. Номер_
отдела

Отделы. Номер_
отдела

Название

1

Иванов

Иван

1

1

Отдел автоматизации

1

Иванов

Иван

1

2

Отдел кадров

2

Петров

Петр

2

1

Отдел автоматизации

2

Петров

Петр

2

2

Отдел кадров

3

Сидоров

Сидор

1

1

Отдел автоматизации

3

Сидоров

Сидор

1

2

Отдел кадров

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

В основе любого соединения наборов записей лежит операция их декартового произведения.

3.2.2. Естественное соединение

В полученном декартовом произведении интерес представляют не все записи, а только те, в которых идентичные столбцы имеют одинаковые значения (Сотрудники.Номер_отдела = Отделы.Номер_отдела). Следовательно, в команде выборки с перекрестным соединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. Кроме того, в результатной таблице не нужны оба идентичных столбца, достаточно лишь одного из них. Такая таблица и будет естественным соединением таблиц Сотрудники и Отделы. Она получается с помощью следующего запроса:

SELECT Сотрудники.*, Отделы.Название

FROM Сотрудники, Отделы

WHERE Сотрудники.Номер_отдела = Отделы.Номер_отдела;

Данный запрос можно переписать, используя псевдонимы:

SELECT T1.*, T2.Название

FROM Сотрудники T1, Отделы T2

WHERE T1.Номер_отдела = T2.Номер_отдела;

Эквивалентный запрос с оператором NATURAL JOIN:

SELECT T1.*, T2.Название

FROM Сотрудники T1 NATURAL JOIN Отделы T2;

Естественное соединение

Номер_
сотрудника

Фамилия

Имя

Номер_отдела

Название

1

Иванов

Иван

1

Отдел автоматизации

2

Петров

Петр

2

Отдел кадров

3

Сидоров

Сидор

1

Отдел автоматизации

При естественном соединении, выполняемом с помощью оператора NATURAL JOIN, проверяется равенство всех одноименных столбцов соединяемых таблиц.

3.2.3. Соединение по именам столбцов

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

Допустим, имеются две таблицы с одинаковыми структурами:

Коробки (Размер, Количество, Цвет);

Крышки (Размер, Количество, Цвет).

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

SELECT *

FROM Коробки JOIN Крышки USING (Размер, Количество);

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

Приведенный выше запрос можно сформулировать иначе:

SELECT *

FROM Коробки, Крышки

WHERE (Коробки.Размер = Крышки.Размер)

 AND (Коробки.Количество = Крышки.Количество);

На следующих рисунках показаны таблицы Коробки и Крышки, а также результат рассмотренного запроса.

Коробки

Крышки

Размер

Количество

Цвет

Размер

Количество

Цвет

3020

13

Белый

3020

13

Белый

3030

7

Белый

3030

3

Белый

3015

20

Синий

3015

20

Синий

2020

26

Красный

2020

26

Желтый

2020

29

Белый

2020

28

Красный

Комплекты

Размер

Количество

Цвет

Размер

Количество

Цвет

3020

13

Белый

3020

13

Белый

3015

20

Синий

3015

20

Синий

2020

26

Красный

2020

26

Желтый

3.2.4. Условное соединение

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

Пусть в базе данных имеются следующие две таблицы:

Сотрудники (Номер, Фамилия, Имя, Номер_отдела);

Отделы (Номер, Название).

Тогда эти таблицы можно соединить:

SELECT *

FROM Сотрудники JOIN Клиенты

 ON (Номер_отдела = Отделы.Номер);

Допустимо также использовать для условного соединения инструкцию INNER JOIN ON.

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

Например:

SELECT *

FROM Сотрудники, Клиенты  

WHERE (Номер_отдела = Отделы.Номер)

 AND (Фамилия = 'Иванов');

и

SELECT *

FROM Сотрудники JOIN Клиенты

 ON (Номер_отдела = Отделы.Номер)

WHERE Фамилия = 'Иванов';

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

3.2.5. Сложные соединения

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

Пример соединения нескольких источников данных:

SELECT last_name, publisher, e.isbn, subject

FROM authors AS a

JOIN books AS b ON a.id = b.author_id

JOIN editions AS e ON b.id = e.book_id

JOIN publishers AS p ON e.publisher_id = p.id

JOIN subjects AS s ON b.subject_id = s.id;

Хотя таблица books участвует в соединении, ни одно из ее полей не входит в итоговый набор. Включение таблицы books в секции JOIN предоставляет критерии для соединения других таблиц. Все таблицы, поля которых возвращаются в результате запроса, связываются с другими таблицами через поле id таблицы books (кроме таблицы publishers, которая связывается с таблицей editions по полю publisher_id).


Лабораторная работа 3

Выполнить следующие запросы к базе данных booktown:

  1.  Код isbn, дата издания, цена продажи и количество экземпляров для всех изданий на складе.
  2.  Дата покупки, код покупателя, дата издания, тип обложки для всех покупок. Отсортировать по дате издания.
  3.  Дата покупки, фамилия и имя покупателя для всех покупок. Отсортировать по фамилии и имени покупателя.
  4.  Список авторов с названиями написанных ими книг, отсортированный по авторам.
  5.  Список названий книг и фамилий их авторов с указанием темы, отсортировать по названию книги.
  6.  Фамилия и имя покупателя, название купленной им книги для всех покупок. Отсортировать по покупателям.
  7.  Все пары: название издательства, фамилия автора (произведение которого было издано данным издательством). Упорядочить по названию издательства.
  8.  Фамилия и имя покупателя, название издательства купленной им книги для всех покупок. Упорядочить по покупателю.
  9.  Список номеров ISBN и тип обложки для книг в бумажном переплете с указанием издательств, выпустивших книгу; список отсортировать по издательствам.
  10.  Фамилия автора и название тем, которые он отразил в своих произведениях. Упорядочить по автору.
  11.  Код ISBN, название книги, фамилия автора, затраты магазина на закупку всех имеющихся в наличии экземпляров. Отсортировать так, чтобы в начале списка шли самые малозатратные издания.
  12.  Название и число экземпляров книг, количество которых на складе равно нулю. Упорядочить по названию.
  13.  Название и дата продажи книги, которая была куплена самой первой в магазине.
  14.  Фамилия и имя автора книги, купленной предпоследней. Указать также дату продажи.
  15.  Название книги, дата издания, первое или повторное издание для всех изданий (если издание 1, то «первое»; иначе – «повторное»). Отсортировать по названию книги.
  16.  Название книги, издательство и тип обложки для книг, изданных издательством Roc в твердом переплете. Упорядочить по названию книги.
  17.  Название и тема книг, относящихся к темам Science и Science Fiction. Отсортировать по названию книги.
  18.  Названия книг, которые приобрела Annie Jackson (с указанием покупателя).
  19.  Название и издатель книг, изданных не издательствами Ace Books, Roc, Penguin, Doubleday, Random House.
  20.  Название книги и адрес издательства для книг, изданных не в городе New York.

 


 

А также другие работы, которые могут Вас заинтересовать

16885. Эволюция надеется, что вы не знаете химию: проблема с хиральностью 63.5 KB
  Эволюция надеется что вы не знаете химию: проблема с хиральностью Чарлз Маккомб Когда в 1953 году появился газетный заголовок Жизнь в пробирке сообщество эволюционистов пришло в восторг. Работа Миллера рассматривалась ими как научное доказательство того что жизнь ...
16886. Эволюция – религия, а не наука 49 KB
  Эволюция – религия а не наука В двух своих предыдущих статьях [12] автор задокументировал с признаний эволюционистов тот факт что идея эволюции от молекулы к человеку не соответствует критериям научной теории. Не существует ни одного эволюционного перехода переходн
16887. Свидетельство химии о сотворении 53 KB
  Свидетельство химии о сотворении Эдвард А. Бордо Большинство доводов из области химии выдвигаемых против теории эволюции и в поддержку сотворения акцентируют внимание на биохимическом ракурсе проблемы происхождения жизни. Хотя никто и не говорит что эти биохимич...
16888. ОПТИМИЗАЦИЯ ФОРМЫ КОРПУСНЫХ ДЕТАЛЕЙ МЕТАЛЛОРЕЖУЩИХ СТАНКОВ С ПОМОЩЬЮ ПРОГРАММЫ ANSYS 127 KB
  ОПТИМИЗАЦИЯ ФОРМЫ КОРПУСНЫХ ДЕТАЛЕЙ МЕТАЛЛОРЕЖУЩИХ СТАНКОВ С ПОМОЩЬЮ ПРОГРАММЫ ANSYS Описана процедура оптимизации формы корпуса шпиндельной бабки токарного станка. Задача оптимизации заключалась в нахождении таких толщин стенок корпуса при которых он бы имел макси
16889. Расчет силовых смещений корпуса шпиндельной бабки токарного станка 119 KB
  Расчет силовых смещений корпуса шпиндельной бабки токарного станка 1.Описание конструкции шпиндельной бабки токарного станка 16К20 Шпиндельная бабка токарного станка 16К20 предназначена для базирования подшипников шпинделя а также для передачи вращения от шкива ремен
16890. Основные настройки текстового процессора Writer 144.5 KB
  Лабораторная работа №1 Основные настройки текстового процессора Writer Оборудование: ПК Программное обеспечение: Windows OpenOffice.org Writer. Цель работы: приобретение и закрепление практических навыков работы во Writer Теоретическая часть. Для корректной работы OpenOffice с русским я
16891. Основные операции по форматированию текстовых документов в OpenOfficeorg Writer 57 KB
  Лабораторная работа №2Основные операции по форматированию текстовых документов в OpenOffice.org Writer Оборудование: ПК Программное обеспечение: Windows OpenOffice.org Writer. Цель работы: приобретение и закрепление практических навыков работы в OpenOffice.org Writer Указания к выполнению работы:...
16892. Представление информации в табличной форме во Writer 112 KB
  Лабораторная работа №3Представление информации в табличной форме во Writer Оборудование: ПКПрограммное обеспечение: Windows OpenOffice.org WriterЦель работы: приобретение и закрепление практических навыков работы в текстовом процессоре OpenOffice.org Writer Теоретическая часть Таблицы Writ...