- Многотабличные запросы SQL
- Введение
- Объединение таблиц в SQL
- Выборка данных из нескольких таблиц (JOIN)
- Синтаксис соединения по ANSI/ISO
- Естественные соединения (NATURAL JOIN)
- PL/SQL
- Создание запроса на основе нескольких таблиц
- Выберите нужное действие
- Уточнение данных в запросе с помощью данных из связанной таблицы
- Использование мастера запросов для построения запроса на основе главной и связанной таблицы
- Пример на основе базы данных «Борей»
- Построение запроса с помощью мастера запросов
- Объединение данных в двух таблицах с помощью их связей с третьей таблицей
- Создание запроса на выборку с использованием таблиц, связанных отношением «многие-ко-многим»
- Пример на основе базы данных «Борей»
- Открытие запроса в Конструкторе
- Просмотр всех записей из двух похожих таблиц
- Создание запроса на объединение двух таблиц
Многотабличные запросы SQL
Здравствуйте! До сих пор в нашем курсе мы разбирали упражнения, которые оперировали данными только из одной таблицы. Сегодня мы это исправим и научимся делать запросы сразу к нескольким таблицам в одной базе данных.
Введение
Итак, в прошлых статьях, например по оператору SELECT в SQL, мы прописывали что то похожее:
На самом деле изначально в языке SQL было предписано указывать поле, которое хотим выбрать в явном виде, а именно так:
Мы указываем через оператор доступа «точка» то поле, которое нужно. С течением времени, SQL стал гораздо умнее и понятнее, и на данный момент он сам понимает из какой таблице мы хотим выбрать поля, если речь идет о запросах с одной таблицей.
Если же обращаться к данным из нескольких таблиц, то хорошим тоном будет указывать явно поля, так как иногда в таблицах могут содержаться поля с одинаковыми названиями, а это потенциальная ошибка.
В SQL для многотабличных запросов продумали объединение таблиц несколькими способами, о них мы и поговорим подробнее.
Объединение таблиц в SQL
Начнем с самого простого способа, в котором будем явно указывать поля и условия, при котором их нужно выводить. Вот пример:
Вывести попарно продавцов и покупателей из одного города.
Поскольку, нужно вывести попарно то придется перебрать все комбинации — SQL сделает это:
sname | cname | city |
---|---|---|
Колованов | Деснов | Москва |
Плотников | Деснов | Москва |
Проворов | Деснов | Москва |
Колованов | Краснов | Москва |
Плотников | Краснов | Москва |
Проворов | Краснов | Москва |
Петров | Кириллов | Тверь |
Шипачев | Пушкин | Челябинск |
Мозякин | Лермонтов | Одинцово |
Колованов | Белый | Москва |
Плотников | Белый | Москва |
Проворов | Белый | Москва |
Колованов | Чудинов | Москва |
Плотников | Чудинов | Москва |
Проворов | Чудинов | Москва |
Мозякин | Лосев | Одинцово |
Это пример объединения таблиц с использованием явного определения полей. Такой запрос вполне понятен, но в будущем если будет возможность обойтись без префиксов, мы будем обходиться без них.
Источник
Выборка данных из нескольких таблиц (JOIN)
Довольно часто приходится выбирать данные из нескольких таблиц. Для показа в одном запросе данных из нескольких таблиц Oracle позволяет выполнять так называемые соединения таблиц. Имеется два относящихся к соединению таблиц правила, о которых следует постоянно помнить. Данные из двух (или нескольких) таблиц могут быть объединены в том случае, если у обеих таблиц имеется совпадающий столбец (с тем же самым или с другим именем) и этот столбец в одной из таблиц является первичным ключом (или частью этого ключа).
SELECT ename, deptno FROM emp ORDER BY deptno; | SELECT deptno, dname FROM dept ORDER BY deptno; | ||||||||||||||||||||||||||||||||||||||||
|
|
Давайте рассмотрим пример оператора соединения (join), использующего традиционный синтаксис Oracle, где мы объединяем вместе содержимое таблиц emp и dept для получения перечня всех сотрудников и названий отделов, где они работают:
ENAME | DEPTNO | DNAME |
---|---|---|
CLARK | 10 | ACCOUNTING |
KING | 10 | ACCOUNTING |
MILLER | 10 | ACCOUNTING |
JONES | 20 | RESEARCH |
FORD | 20 | RESEARCH |
ADAMS | 20 | RESEARCH |
SMITH | 20 | RESEARCH |
SCOTT | 20 | RESEARCH |
WARD | 30 | SALES |
TURNER | 30 | SALES |
ALLEN | 30 | SALES |
JAMES | 30 | SALES |
BLAKE | 30 | SALES |
MARTIN | 30 | SALES |
Обратите внимание на многие важные компоненты этого соединения таблиц. Использование во фразе FROM двух таблиц четко указывает на то, что имеет место соединиения таблиц. Обратите также внимание на то, что перед именем каждой таблицы присутствует буква: e для таблицы emp или d для таблицы dept. Это служит иллюстрацией интересной концепции – столбцы могут иметь псевдонимы точно так же, как их имеют таблицы. Псевдонимы служат важной цели – они не дают Oracle запутаться в том, какую таблицу использовать при выводе данных в столбец deptno. Вспомните, что в обеих таблицах (emp и dept) имеются столбцы с именем deptno.
Неоднозначности при соединении таблиц можно также избежать, если в качестве префикса перед именем столбца указать имена таблиц.
Заметьте, что в нашу фразу WHERE включено сравнение по полю deptno, соединяющему данные в emp с данными в dept. В случае отсутствия этой связи в выходные данные были вы включены все данные из emp и dept.
Синтаксис соединения по ANSI/ISO
В соответствии с синтаксисом ANSI/ISO, для того, чтобы соединить содержимое двух таблиц для получения единого результата, мы должны включить в SQL-оператор фразу JOIN имятаблицы ON условиесоединения. Если вы хотите в соответствии с этим синтаксисом выполнить то же соединение таблиц, которое мы делали раньше, наш оператор будет выглядеть следующим образом:
ENAME | DEPTNO | DNAME |
---|---|---|
CLARK | 10 | ACCOUNTING |
KING | 10 | ACCOUNTING |
MILLER | 10 | ACCOUNTING |
JONES | 20 | RESEARCH |
FORD | 20 | RESEARCH |
ADAMS | 20 | RESEARCH |
SMITH | 20 | RESEARCH |
SCOTT | 20 | RESEARCH |
WARD | 30 | SALES |
TURNER | 30 | SALES |
ALLEN | 30 | SALES |
JAMES | 30 | SALES |
BLAKE | 30 | SALES |
MARTIN | 30 | SALES |
Обратите внимание на различия между этим синтаксисом и синтаксисом Oracle. Во-первых, в синтаксисе ANSI/ISO сравнения, используемые для соединения, отделяются от всех остальных сравнений с помощью специального ключевого слова ON, указывающего на то, что именно это сравнение используется для соединения. Вы по-прежнему можете включать в соответствующие ANSI/ISO запросы на соединение фразу WHERE. Единственное отличие состоит в том, что фраза WHERE теперь будет содержать только дополнительные операторы сравнения, используемые дл дополнительной фильтрации данных. Кроме того, вы не должны теперь указывать во фразе FROM имена всех объединяемых таблиц. Вместо этого сразу же после фразы FROM вы должны использовать фразу JOIN, в которой и будут определены имена всех соединяемых таблиц.
Естественные соединения (NATURAL JOIN)
Естественным соединением называется соединение между двумя таблицами, в котором Oracle соединяет таблицы по одинаково называющемуся столбцу (столбцам) обеих таблиц (естественным образом!). Естественное соединение выполняется в том случае, если указано ключевое слово NATURAL.
Единственным совпадающим столбцом для таблиц emp и dept является столбец depnto,
ENAME | DEPTNO | DNAME |
---|---|---|
CLARK | 10 | ACCOUNTING |
KING | 10 | ACCOUNTING |
MILLER | 10 | ACCOUNTING |
JONES | 20 | RESEARCH |
FORD | 20 | RESEARCH |
ADAMS | 20 | RESEARCH |
SMITH | 20 | RESEARCH |
SCOTT | 20 | RESEARCH |
WARD | 30 | SALES |
TURNER | 30 | SALES |
ALLEN | 30 | SALES |
JAMES | 30 | SALES |
BLAKE | 30 | SALES |
MARTIN | 30 | SALES |
Нетрудно заменить, что естественные соединения позволяют в значительной степени упростить запросы с соединением за счет устранения псевдонимов таблиц и сравнений дл соединения.
Tags: Выборка данных из нескольких таблиц, JOIN
PL/SQL
Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.
Источник
Создание запроса на основе нескольких таблиц
В простейшем случае построение и использование запроса в Access заключается в выборе требуемых полей из таблицы, применении условий (если они нужны) и просмотре результатов запроса. Но чаще необходимо использовать данные, которые находятся в разных таблицах. К счастью, вы можете создавать запросы, объединяющие сведения из нескольких источников. В этой статье объясняется, когда следует получать данные сразу из нескольких таблиц и как это делать.
Выберите нужное действие
Уточнение данных в запросе с помощью данных из связанной таблицы
В некоторых случаях запрос, построенный на основе одной таблицы и предоставляющий необходимые сведения, может стать более информативным и полезным благодаря данным из другой таблицы. Например, предположим, что у вас есть список кодов сотрудников, которые выводятся в результатах запроса. Вы понимаете, что было бы более удобнее, если бы в этом списке были видны и имена сотрудников, но они находятся в другой таблице. Чтобы в результатах запроса отображались имена сотрудников, следует включить в него обе таблицы.
Использование мастера запросов для построения запроса на основе главной и связанной таблицы
Убедитесь, что для таблиц задано отношение в окно отношений.
На вкладке Работа с базами данных в группе Показать или скрыть выберите пункт Отношения.
На вкладке Конструктор в группе Связи нажмите кнопку Все связи.
Выберите таблицы, которые нужно связать.
Если таблицы отображаются в окне схемы данных, убедитесь, что отношение между ними уже установлено.
Отношение отображается в виде линии, соединяющей общие поля двух таблиц. Чтобы узнать, какие поля таблиц связаны отношением, дважды щелкните линию связи.
Если таблицы не отображаются в окне схемы данных, следует добавить их.
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Имена таблиц.
Дважды щелкните каждую из таблиц, которые вы хотите отобразить, а затем нажмите кнопку Закрыть.
Если между таблицами не установлено отношение, создайте его, перетащив поле из одной таблицы на поле другой. Поля, по которым создается отношение, должны иметь одинаковый тип данных.
Примечание: Создать отношение между полем с типом Тип данных «Счетчик» и полем, имеющим тип данных Числовой тип данных, можно в том случае, если это поле имеет размер «длинное целое». Это часто бывает так при создании отношение «один-ко-многим».
Откроется диалоговое окно Изменение связей.
Нажмите кнопку Создать для создания связи.
Дополнительные сведения о параметрах, используемых при создании отношения, см. в статье Создание, изменение и удаление отношения.
Закройте окно схемы данных.
На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.
В диалоговом окне Новый запрос выберите пункт Простой запрос и нажмите кнопку ОК.
В поле со списком Таблицы и запросы выберите таблицу, содержащую основные сведения, которые вы хотите включить в запрос.
В области Доступные поля щелкните первое поле, которое вы хотите включить в запрос, и нажмите кнопку с одинарной стрелкой вправо, чтобы переместить это поле в список Выбранные поля. Повторите то же самое для каждого поля этой таблицы, которое вы хотите включить в запрос. Это могут быть поля, данные из которых должны выводиться в результатах запроса, или поля, используемые для наложения ограничений на выводимые строки путем задания определенных условий.
В поле со списком Таблицы и запросы выберите таблицу, содержащую дополнительные сведения, с помощью которых вы хотите уточнить результаты запроса.
Добавьте поля, которые следует использовать для уточнения результатов запроса, в список Выбранные поля и нажмите кнопку Далее.
В группе Выберите подробный или итоговый отчет выберите вариант Подробный или Итоговый.
Если не требуется использовать в запросе какие-либо агрегатные функции ( Sum, Avg, Min, Max, Count, StDev или Var), выберите подробный запрос. В противном случае выберите вариант «Сводка». Выбрав параметры, нажмите кнопку Далее.
Нажмите кнопку Готово для просмотра результатов.
Пример на основе базы данных «Борей»
В приведенном ниже примере используется мастер запросов, с помощью которого строится запрос, отображающий список заказов, стоимость доставки каждого заказа и фамилию сотрудника, выполнившего заказ.
Примечание: Этот пример подразумевает изменение учебной базы данных «Борей». Рекомендуем сделать ее резервную копию и выполнять инструкции, используя резервную копию.
Построение запроса с помощью мастера запросов
Откройте учебную базу данных «Борей». Закройте форму входа.
На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.
В диалоговом окне Новый запрос выберите пункт Простой запрос и нажмите кнопку ОК.
В поле со списком Таблицы и запросы выберите пункт Таблица: Заказы.
В списке Доступные поля дважды щелкните пункт ИД_заказа, чтобы переместить это поле в список Выбранные поля. Дважды щелкните пункт Цена доставки, чтобы переместить это поле в список Выбранные поля.
В поле со списком Таблицы и запросы выберите пункт Таблица: Сотрудники.
В списке Доступные поля дважды щелкните пункт Имя, чтобы переместить это поле в список Выбранные поля. Дважды щелкните пункт Фамилия, чтобы переместить это поле в список Выбранные поля. Нажмите кнопку Далее.
Так как вы создаете список всех заказов, следует использовать подробный запрос. Если нужно суммировать стоимость доставки заказов, выполненных сотрудником, или применить другую агрегатную функцию, следует использовать итоговый запрос. Выберите вариант Подробный (вывод каждого поля каждой записи) и нажмите кнопку Далее.
Нажмите кнопку Готово для просмотра результатов.
Запрос вернет перечень заказов, для каждого из которых будет указана стоимость доставки, а также имя и фамилия сотрудника, выполнившего его.
Объединение данных в двух таблицах с помощью их связей с третьей таблицей
Часто данные в двух таблицах связаны друг с другом через третью таблицу. Это может быть в том случае, когда данные в первых двух таблицах связаны отношение «многие-ко-многим». Хорошим приемом при проектировании баз данных является разбиение одной связи с отношением «многие-ко-многим» между двумя таблицами на две связи с отношением «один-ко-многим», в которых участвуют три таблицы. Это делается путем создания третьей (связующей) таблицы, в которой есть первичный ключ и внешний ключ для каждой из таблиц. Затем создается связь «один-ко-многим» между каждым внешним ключом связующей таблицы и соответствующим первичным ключом связуемой таблицы. В таких случаях следует включать в запрос все три таблицы, даже если вы хотите получить данные только из двух.
Создание запроса на выборку с использованием таблиц, связанных отношением «многие-ко-многим»
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните две таблицы, содержащие данные, которые вы хотите включить в запрос, а также связуемую таблицу, а затем нажмите кнопку «Закрыть».
Все три таблицы появятся в рабочей области конструктора запросов, связанные по соответствующим полям.
Дважды щелкните поля, которые вы хотите использовать в запросе. Каждое поле появится в бланк запроса.
В бланке запроса укажите условия для полей в строке Условия отбора. Чтобы поле, по которому задаются условия, не отображалось в результатах запроса, снимите флажок в строке Показать для него.
Чтобы отсортировать результаты по значениям поля, в бланке запроса в строке Сортировка для него выберите значение По возрастанию или По убыванию (в зависимости от того, в каком направлении вы хотите выполнить сортировку записей).
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Access выведет результаты запроса в Режим таблицы.
Пример на основе базы данных «Борей»
Примечание: Этот пример подразумевает изменение учебной базы данных «Борей». Рекомендуем сделать ее резервную копию и выполнять инструкции, используя резервную копию.
Предположим, что у вас появилась новая возможность: поставщик из Рио-де-Жанейро нашел ваш веб-сайт и хочет с вами сотрудничать. Однако он работает только в Рио-де-Жанейро и Сан-Паулу. Компания поставляет все интересующие вас категории пищевых продуктов. Являясь довольно крупным предприятием, поставщик хочет, чтобы вы гарантировали достаточно большой рынок сбыта, который обеспечил бы ему годовые продажи объемом не менее 20 000 бразильских реалов (около 9 300 долларов США). Можете ли вы обеспечить требуемый рынок сбыта?
Данные, необходимые для ответа на этот вопрос, находятся в двух местах: в таблице «Клиенты» и в таблице «Сведения о заказе». Эти таблицы связаны друг с другом через таблицу «Заказы». Отношения между этими таблицами уже заданы. В таблице «Заказы» для каждого заказа может быть указан только один клиент, связанный с таблицей «Клиенты» по полю «ИДКлиента». Каждая запись в таблице «Сведения о заказе» связана только с одним заказом в таблице «Заказы» по полю «ИД_заказа». Таким образом, у каждого клиента может быть множество заказов, для каждого из которых есть несколько записей со сведениями.
В данном примере следует построить перекрестный запрос, в котором будут отображены годовые продажи в городах Рио-де-Жанейро и Сан-Паулу.
Открытие запроса в Конструкторе
Откройте базу данных «Борей». Закройте форму входа.
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните «Клиенты», «Заказы»и выберите «Сведения о заказе».
Все три таблицы появятся в рабочей области конструктора запросов.
В таблице «Клиенты» дважды щелкните поле «Город», чтобы добавить его в бланк запроса.
В бланке запроса в строке Условие отбора столбца Город введите In («Рио-де-Жанейро»,»Сан Паулу). Это позволяет включить в запрос только записи о заказах клиентов из этих городов.
В таблице «Сведения о заказе» дважды щелкните поля «ДатаИсполнения» и «Цена».
Поля добавляются в бланк запроса.
В столбце бланка запроса ДатаИсполнения выберите строку Поле. Замените [ДатаИсполнения] на Год: Format([ДатаИсполнения],»yyyy»). При этом будет создан псевдоним поля ( Год), позволяющий использовать только значение года из даты, указанной в поле «ДатаИсполнения».
В столбце бланка запроса Цена выберите строку Поле. Замените [Цена] на Продажи: [Сведения о заказе].[Цена]*[Количество]-[Сведения о заказе].[Цена]*[Количество]*[Скидка]. При этом будет создан псевдоним поля ( Продажи), вычисляющий сумму продаж для каждой записи.
На вкладке Конструктор в группе Тип запроса щелкните элемент Перекрестная таблица.
В бланке запроса появятся две новые строки: Итоги и Перекрестная таблица.
В столбце бланка запроса Город щелкните строку Перекрестная таблица, а затем щелкните Заголовки строк.
Названия городов будут использоваться в качестве заголовков строк (т. е. запрос будет возвращать одну строку для каждого города).
В столбце Год щелкните строку Перекрестная таблица, а затем щелкните Заголовки столбцов.
Значения годов будут использоваться в качестве заголовков столбцов (т. е. запрос будет возвращать один столбец для каждого года).
В столбце Продажи щелкните строку Перекрестная таблица, а затем щелкните элемент Значение.
Значения продаж будут отображаться на пересечениях строк и столбцов (т. е. запрос будет возвращать одно значение продаж для каждого сочетания города и года).
В столбце Продажи щелкните строку Итоги, а затем щелкните элемент Sum.
Запрос будет суммировать все значения столбца.
В строке Итоги для других двух столбцов можно оставить значение по умолчанию Группировка, так как в этих столбцах требуется отобразить отдельные значения, а не агрегированные показатели.
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Теперь у вас есть запрос, возвращающий общие годовые продажи по Рио-де-Жанейро и Сан-Паулу.
Просмотр всех записей из двух похожих таблиц
Иногда требуется объединить данные из двух таблиц, которые имеют одинаковую структуру, но расположены в разных базах данных. Рассмотрим следующий сценарий.
Предположим, вы являетесь аналитиком и занимаетесь обработкой сведений об учащихся. Вы начинаете работу над новым проектом совместной обработки данных по вашей и другой школе с целью улучшения их учебных планов. По некоторым из исследуемых вопросов удобнее просматривать записи по обеим школам вместе, словно бы они находились в одной таблице.
Вы можете импортировать данные другой школы в новые таблицы в своей базе данных, но в этом случае изменения, внесенные в базу данных другой школы, не будут отражаться в вашей базе данных. Лучшим решением было бы установить связь с таблицами другой школы, а затем создать запросы, объединяющие эти данные во время выполнения. При этом вы сможете анализировать данные в едином наборе вместо того, чтобы выполнять два отдельных анализа, а затем пытаться объединить их в один.
Чтобы просмотреть все записи из двух таблиц с одинаковой структурой, используйте запрос на объединение.
Запросы на объединение невозможно отобразить в Конструкторе. Они создаются с помощью команд SQL, которые нужно вводить на вкладке объекта в режим SQL.
Создание запроса на объединение двух таблиц
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
На вкладке Конструктор в группе Тип запроса нажмите кнопку Объединение.
Запрос переключится из Конструктора в режим SQL. На данном этапе вкладка объекта в режиме SQL будет пуста.
В режиме SQL введите SELECT и список полей первой таблицы, которые вы хотите включить в запрос. Имена полей должны быть заключены в квадратные скобки и разделены запятыми. Когда вы закончите вводить имена полей, нажмите клавишу ВВОД. Курсор переместится на одну строку вниз в окне режима SQL.
Введите FROM и имя первой таблицы, включаемой в запрос. Нажмите клавишу ВВОД.
Если вы хотите указать условие для поля первой таблицы, введите WHERE, имя поля, оператор сравнения (обычно знак равенства =) и условие. Можно добавлять дополнительные условия к концу предложения WHERE, используя ключевое слово AND и такой же синтаксис, как и для первого условия (например, WHERE [Уровень]=»100″ AND [Часов]>2). После завершения ввода условий нажмите клавишу ВВОД.
Введите слово UNION и нажмите клавишу ВВОД.
Введите SELECT и список полей второй таблицы, которые вы хотите включить в запрос. Следует указать те же поля, что для первой таблицы, и в том же порядке. Имена полей должны быть заключены в квадратные скобки и разделены запятыми. Когда вы закончите вводить имена полей, нажмите клавишу ВВОД.
Введите FROM и имя второй таблицы, включаемой в запрос. Нажмите клавишу ВВОД.
Если вы хотите, добавьте предложение WHERE, как описано в шаге 6.
Введите точку с запятой ( ;), чтобы обозначить конец запроса.
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Результаты будут отображены в режиме таблицы.
Источник