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.

 


 

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

65058. Трмины из современного монгольского, бурятского, баргутского, дагурского и других монгольских языков и диалектов Внутренней Монголии 121 KB
  Среди них помимо старописьменного монгольского языка и староойратского языка на Тодо бичиг были современные монгольские языки халхамонгольский бурятский и калмыцкий. Наименее доступными для исследования были языки и диалекты монголов Внутренней Монголии: языки старых и новых баргутов дагуров и шэнэхэнских бурят...
65059. Заметки о знаках и тамгах Монголии 275 KB
  Тамги издавна широко применявшиеся в Монголии стране древнего скотоводства как знаки собственности известны и распространены там вплоть до настоящего времени. Вот почему кажется полезным анализ отдельных знаков регулярно встречающихся...
65060. ЧИНГИСХАНОВЫ НОЙОНЫ БАДАЙ И КИШЛЫК И НОГАЙСКИЕ ЭТНОНИМЫ БАДАЙ И КИШЛЫК 41.5 KB
  Бадай с Кишлыхом в роды родов их пользуются свободным дарханством повелевая своим подданным носить свой сайдак и провозглашать чару на пирах. Рашидаддин писал так: Бадай и Кишлык из этого племени. Эти Бадай и Кишлык были главами конюших Экэ Чэрэна который был старшим эмиром Онхана...
65061. Тюркский аспект «монгольского» завоевания Евразии 112.5 KB
  Как статистические данные численности населения Монголии так и расчеты по мобилизационным ресурсам государства Чингизхана взятым из различных литературных источников не единожды производившиеся в процессе дискуссий на интернет-форумах показывают что максимально возможное население...
65062. Ханы Мамаевой Орды 76.5 KB
  Можно думать что в сочиненном для остроты сюжета послании Олега ордынскому предводителю автор Сказания умышленно прибег к высокой титулатуре Мамая чтобы таким образом оттенить раболепие рязанского князя перед Ордой и его враждебное отношение к Москве.
65063. История Армении 152.5 KB
  Он сообщает достоверные сведения о положении Закавказья и Армении накануне монгольского нашествия о первых набегах монголов их налоговой политике взаимоотношениях с господствующей верхушкой завоеванных стран междоусобной борьбе в этих странах.
65064. Еще раз о численности монгольского войска в 1237 году 56.5 KB
  Вопрос о масштабах монгольского нашествия на Русь продолжает привлекать внимание исследователей. Отвергая как завышенные - 300 и более тысяч, так и заниженные - 30 тыс.- оценки, один из них приходит к выводу, что войска завоевателей насчитывали 55-65 тыс.
65065. Монгольские имена 86 KB
  Если рождались девочки а хотелось иметь мальчика очередной новорожденной давали мужское имя в надежде что таким образом в семье появиться следующий мальчик. С этой целью очередному новорожденному давали имя Отхон младший Адык последний Шавхар остаток Сюль...