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.

 


 

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

45319. Содержание, форма и основные свойства конституции 25.12 KB
  Правовой статус главы муниципального образования. Закона об общих принципах организации местного самоуправления уставом муниципального образования может быть предусмотрена должность главы муниципального образования – выборного должностного лица возглавляющего деятельность по осуществлению местного самоуправления на территории муниципального образования. Глава муниципального образования соединяет в своем правовом и фактическом статусе черты префекта департамента Франции и председателя исполкома местного Совета. Термин глава муниципального...
45320. Механизм защиты Конституции РФ 22.96 KB
  В конституционной практике Российской Федерации давно сложился ряд правовых форм способов защиты Конституции:1 процесс охраны Конституции Президентом Российской Федерации. Во-первых в качестве гаранта федеральной Конституции Президент Российской Федерации который наделяется прерогативами по защите конституционного строя прав и свобод человека и гражданина осуществлению мер по охране суверенитета РФ ее независимости и государственной целостности обеспечению согласованного функционирования органов государственной власти. Суд наделен...
45321. Конституционный строй 19.94 KB
  Одним из ключевых институтов гражданской службы определенных Федеральным законом О государственной гражданской службе Российской Федерации является институт должности гражданской службы. Прохождение гражданской службы возможно только на должности государственной службы. При освобождении от должности и не будучи назначенным на другую должность лицо увольняется с гражданской службы. Должности федеральной государственной гражданской службы учреждаются федеральным законом или указом Президента Российской Федерации должности...
45322. ИНСТИТУТ ОСНОВНЫХ ПРАВ И СВОБОД ЧЕЛОВЕКА И ГРАЖДАНИНА 27.9 KB
  Правовой статус депутата представительного органа мсу Закон об общих принципах организации местного самоуправления развивая нормы Конституции РФ весьма удачно решил проблему политикоправового статуса депутата других членов выборных органов. Представительный орган местного самоуправления состоит из депутатов избираемых на основе всеобщего равного и прямого избирательного права при тайном голосовании п. Известно что в мировой истории становления демократических начал императивный мандат сыграл решающую роль при формировании парламентского...
45323. Конституционно-правовой механизм обеспечения прав и свобод человека и гражданина 19.09 KB
  Основные принципы государственной службы в Российской Федерации. Вопрос о принципах государственной службы является важнейшим в структуре института государственной службы правовой системы не только Российской Федерации но и других стран мира. Термин принципы государственной службы указывает на основополагающие черты сущностные характеристики важнейшее содержание и значение самой государственной службы а также на главнейшие юридические положения в структуре одноименного правового института. Анализируя принципы государственной службы...
45324. Принципы российского федерализма 27.75 KB
  Российская Федерация состоит из республик краев областей городов федерального значения автономной области автономных округов равноправных субъектов Российской Федерации. В составе Российской Федерации в соответствии со ст. Государственная целостность Российской Федерации. Государственная целостность федеративного государства гарантируется тем что государственный суверенитет как верховенство и независимость государственной власти на всей своей территории и в международных отношениях принадлежит только Федерации но...
45325. Избирательный процесс в РФ: общая характеристика 25.17 KB
  Представительный орган муниципального образования Представительный орган муниципального образования может осуществлять свои полномочия в случае избрания не менее двух третей от установленной численности депутатов. Уставом муниципального образования определяется правомочность заседания представительного органа муниципального образования. Заседание представительного органа муниципального образования не может считаться правомочным если на нем присутствует менее 50 процентов от числа избранных депутатов.
45326. Право на объединение, многопартийность и правовой статус политических партий в РФ 25.91 KB
  Политическая партия определяется как общественное объединение созданное в целях участия граждан Российской Федерации в политической жизни общества посредством формирования и выражения их политической воли участия в общественных и политических акциях в выборах и референдумах а также в целях представления интересов граждан в органах государственной власти и органах местного самоуправления. Деятельность политических партий должна осуществляться на принципах добровольности равноправия самоуправления законности и гласности. Иначе...
45327. Гражданство РФ - принципы, основания его приобретения и прекращения 22.21 KB
  Основы конституционного строя детальная же регламентация этого конституционно-правового института закреплена в Федеральном законе О гражданстве Российской Федерации. Конституция РФ и Закон о гражданстве закрепляют следующие принципы гражданства Российской Федерации: Единство гражданства. В соответствии с этим принципом республики – субъекты РФ не могут устанавливать собственное гражданство предусматривающее изъятия из общего единого статуса гражданина Российской Федерации; гражданин республики одновременно является...