Создание связи между двумя таблицами в Excel
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
- Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
- Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
- Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор >Имя таблицы и введите имя.
- Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения. Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
- Щелкните Данные>Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
- В окне Управление связями нажмите кнопку Создать.
- В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
- Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
- В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
- В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
- Нажмите кнопку ОК.
Дополнительные сведения о связях между таблицами в Excel
- Примечания о связях
- Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
- «Могут потребоваться связи между таблицами»
- Шаг 1. Определите, какие таблицы указать в связи
- Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблицы к другой
Примечания о связях
- Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
- Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
- В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
- Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
- Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
- Запустите надстройку Power Pivot в Microsoft Excel и откройте окно Power Pivot.
- Нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
- В разделе Price (Цена) нажмите Free (Бесплатно).
- В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
- Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
- Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
- Прокрутите вниз и нажмите Select Query (Запрос на выборку).
- Нажмите кнопку Далее.
- Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
- Чтобы импортировать второй набор данных, нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace.
- В разделе Type (Тип) нажмите Data Данные).
- В разделе Price (Цена) нажмите Free (Бесплатно).
- Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
- Прокрутите вниз и нажмите Select Query (Запрос на выборку).
- Нажмите кнопку Далее.
- Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
- Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
- В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
- В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
- Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
- Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
- В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
- В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
- В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
- Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
- В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
- Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
- В главной таблице нажмите Сортировка по столбцу.
- В поле «Сортировать» выберите MonthInCalendar.
- В поле «По» выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
«Могут потребоваться связи между таблицами»
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
- Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
- Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
4. Связанные таблицы
Базу данных, содержащую большое количество полей и записей, чаще всего представляют в виде нескольких таблиц, которые называют связанными таблицами, а саму базу данных при этом называют реляционной базой данных.Название «реляционная» подчеркивает наличие определенных отношений между полями связанных таблиц. Связанные таблицы состоят, как правило, из одной базовой и нескольких подчиненных таблиц или нескольких таблиц, между которыми обозначены связи. Базовая таблица и подчиненные таблицы связаны потому, что они хранятся в одной базе данных. Графическая связь между таблицами отражается с помощью схемы данных. Пример схемы данных представлен в табл. 13.2. Программная связь между таблицами осуществляется с помощь полей с одинаковыми типами данных. Каждая из таблиц – базовая и подчиненная – имеют в своем составе уникальное поле или поле, значения в котором не могут повторяться. Уникальное поле базовой таблицы называется ключевым полем или первичным ключом. В ключевом поле записи не могут повторяться. Любому значению первичного ключа соответствует единственная запись в таблице. Таблица 13.2 Схема данных в связанных таблицах Ключевое поле в подчиненной таблице называется внешним ключом. Внешний ключ однозначно связан с первичным ключом, поскольку это столбец таблицы, значения которого полностью соответствуют значениям первичного ключа другой таблицы. Наличие ключевых полей позволяет осуществлять корректный переход от одной таблицы к другой. Из приведенных определений связанных таблиц и ключей следует несколько правил работы с реляционной базой данных.
- В подчиненную таблицу нельзя добавить запись с несуществующим в базовой таблице ключом.
- В базовой таблице нельзя удалить запись, если не удалены соответствующие записи в подчиненной таблице.
- Изменения ключа базовой таблицы должны сопровождаться изменениями соответствующих записей подчиненной таблицы.
5. Системы управления базами данных, программа Access
- Таблицы– это основные объекты любой базы данных. Таблица хранит структуру базы и все ее данные. Таблицы создаются пользователями для хранения данных.
- Запросы– объекты для извлечения данных и представления их в удобном виде. С помощью запросов пользователь может делать отбор, сортировку и фильтрацию данных. При запросах извлекаются данные из базовой таблицы и создаются новые результирующие или подчиненные таблицы. Пример извлечения данных представлен в табл.13.3
- Формы– это табличные средства, упрощающие процесс ввода данных в базу. Формы облегчают работу с базой данных и позволяют частично автоматизировать процесс ввода.
- Отчеты – это средства вывода данных на печать. Данные выводятся в форме таблицы и могут иметь некоторые оформительские элементы, например, колонтитулы.
- Страницыдоступа данных предназначены для управления доступа к данным, расположенным в базе. Страницы определяют тип данных, которые предоставляются конкретному пользователю.
23.08.2019 28.52 Кб 22 инфа по викторине.docx
25.11.2019 39.31 Кб 28 инфинитив.docx
16.03.2016 587.3 Кб 81 Информатика (курс для зочников).pdf
15.11.2019 136.7 Кб 36 Информатика ДЗ-1 Информация.doc
18.11.2018 173.66 Кб 51 информатика(лекции).docx
16.03.2016 1.01 Mб 995 Информатика. Лекции.doc
16.03.2016 131.07 Кб 128 Информатика.doc
08.05.2015 1.2 Mб 99 ИнформатикаМетодическиеУказания.pdf
16.03.2016 1.14 Mб 62 ИнформатикаМетодическиеУказания.pdf
17.09.2019 4.67 Mб 46 Информационные сети и телекоммуникации.doc
25.09.2019 2.35 Mб 31 Информационные технологии 3 вариант (1).docx
Ограничение
Для продолжения скачивания необходимо пройти капчу:
Управление связанными таблицами
Связь с внешними источниками данных и создание решений на основе различных наборов данных — Office Access. Диспетчер связанных таблиц — это централизованное расположение для просмотра и управления всеми источниками данных и связанными таблицами в базе данных Access. Из-за изменения расположения источника данных, имени таблицы или схемы может потребоваться время от времени обновлять, связывать, находить, изменять или удалять связанные таблицы. Например, вы переходите с тестовой среды на среду, поэтому вам нужно изменить расположение источника данных. Если требования к решению изменились, вы также можете добавить или удалить связанные таблицы.
Заметка Диалоговое окно Диспетчер связанных таблицзависит от версии Access. В Microsoft 365, вы можете использовать это диалоговое окно для обновления, повторной связи, добавления, изменения, поиска и удаления связанных таблиц, но экспортировать данные в Excel. В Access 2016 и более поздних Access 2016 диалоговое окно гораздо проще, но вы можете экспортировать данные в Excel.
Обслуживание каждого источника данных и связанных таблиц с помощью диспетчера связанных таблиц
Подготовка
Диспетчер связанных таблиц можно использовать несколькими подходами, но рекомендуется использовать следующую последовательность действий:
- Обновите источник данных, чтобы обеспечить успешные связи и выявить проблемы.
- Если возникла проблема с источником данных, при запросе или редактировании источника данных введите правильное расположение, чтобы устранить неполадки.
- Повторное связь отдельных таблиц с состоянием «Сбой». Access автоматически обновляет состояние, чтобы указать успех или неудачу.
- Повторяйте действия 1–3, пока не будут устранены все проблемы.
Обновление источника данных и связанных с них таблиц
Обновите источник данных и связанные с них таблицы, чтобы обеспечить его доступность и надежную работу связанных таблиц.
- Выберите Внешние данные > диспетчер связанных таблиц.Совет Чтобы обновить связанную таблицу из области навигации, щелкните ее правой кнопкой мыши и выберите обновить ссылку. Вы также можете наведите курсор на имя связанной таблицы, чтобы увидеть строку подключения и другие сведения.
- В диалоговом окне Диспетчер связанных таблиц выберите источник данных или отдельные связанные таблицы. При выборе источника данных выбираются все связанные таблицы. Раз развернуть (+) элементы в источнике данных, чтобы выбрать отдельные связанные таблицы.
- Выберите Обновить.
- Если возникла проблема с расположением источника данных, введите правильное расположение при запросе или Изменить источник данных.
- Убедитесь, что отображается столбец Состояние (может потребоваться прокрутить страницу вбок), а затем проверьте его, чтобы увидеть результаты:
- Успех Связанные таблицы успешно обновлены.
- Сбой Проблема возникает в одной или нескольких связанных таблицах. Чаще всего причиной сбойного состояния являются новые учетные данные или изменение имени таблицы. Чтобы устранить эту проблему, перенаправьте ссылку на источник данных или связанную таблицу.
- Выберите Обновить еще раз, пока не исправите каждую неудаженную связанную таблицу, а в столбце Состояние будет отображаться«Успешно».
Повторное связывать источник данных или связанную таблицу
Чтобы изменить расположение источника данных и связанную таблицу, измените имя источника данных.
- Выберите Внешние данные > диспетчер связанных таблиц.Совет На панели навигации можно наведите курсор на имя связанной таблицы, чтобы увидеть строку подключения и другие сведения.
- В диалоговом окне Диспетчер связанных таблиц выберите источник данных или отдельные связанные таблицы. Возможно, потребуется развернуть (+) элементы в столбце Источник данных.
- Выберите Повторное связи.
- Независимо от того, находится ли источник данных в Access, Access запросит его новое расположение. После ввода правильного расположения вы можете оставить существующие имена связанных таблиц без изменений или выбрать новые.
- Убедитесь, что отображается столбец Состояние (может потребоваться прокрутить страницу вбок), а затем проверьте его, чтобы увидеть результаты:
- Успех Связанные таблицы успешно перессылаются.
- Сбой Проблема возникает в одной или нескольких связанных таблицах.
- Вам будет предложено ввести имя новой таблицы. В некоторых случаях может потребоваться выбрать новую таблицу из списка таблиц. Если вам будет предложено ввести строку подключения, введите ее в поле Строка подключения. Access автоматически обновляет состояние, чтобы указать успех или неудачу.
- Выберите Повторное связываение, пока не будет исправлена каждая из сбойных связанных таблиц, а в столбце Состояние будет отображаться«Успешно».
Поиск связанной таблицы
Если у вас много связанных таблиц, найдите нужный результат в поле Поиск в верхней части диалогового окна Диспетчер связанных таблиц.
- Введите текста в поле Поиск. Access выполняет поиск совпадений в столбцах Имя источника данных и Сведения об источнике данных. Поиск не имеет чувствительность к делу и использует заранее печатный шрифт для динамического списка совпадений имен ссылок таблицы.
- Чтобы сбросить отображение, очистку окна Поиск.
Изменение источника данных
В зависимости от источника данных можно изменить отображаемую и путь к источнику данных, имя файла, пароль или строку подключения.
- Выберите Внешние данные > диспетчер связанных таблиц.Совет На панели навигации можно наведите курсор на имя связанной таблицы, чтобы увидеть строку подключения и другие сведения.
- В диалоговом окне Диспетчер связанных таблиц выберите источник данных, наведите курсор на источник данных и выберите изменить.
- Измените данные в диалоговом окне Изменение ссылки. Изменение сведений об источнике Excel данных
- Нажмите кнопку Готово.
Добавление источника данных и связанных таблиц
Вы можете добавить следующие типы источников данных: Access, SQL (Сервер и Azure), Excel или пользовательский (текст, Microsoft Dynamics, список ShaePoint, ODBC).
Совет Разработчики Access, вводя строки подключения в кодЕ VBA, могут добавлять и редактировать строки подключения в диалоговом окне Диспетчер связанных таблиц, вместо того чтобы изменять код напрямую или писать сложный код, который автоматически изменяет строки подключения.
- Выберите Внешние данные > диспетчер связанных таблиц.
- В диалоговом окне Диспетчер связанных таблиц выберите добавить.
- Чтобы сделать его более обнаруживаемой и осмысленным, введите имя в поле Отображаемая имя. Отображаемого имени по умолчанию является тип источника данных. Это имя отображается в столбце Источник данных, вы можете отсортировать этот столбец и использовать его для классификации источников данных, что особенно полезно при использовании множества источников данных.
- Выполните одно из следующих действий:
- Выберите SQL (Server/Azure),выберите Далее, а затем введите данные SQL Server входа и DSN. Дополнительные сведения см. в SQL Server или связывать данные в базе данных Azure SQL Server.
- Выберите Access, выберите Далее, а затем в диалоговом окне Добавить ссылку введите имя файла и пароль. Дополнительные сведения см. в том, как импортировать данные из другойбазы данных Access или связать их с данными.
- Выберите Excel, а затем введите имя файла в диалоговом окне Добавить ссылку. Дополнительные сведения см. в этойExcel.
- Выберите custom (Text, Dynamics, SharePoint List, ODBC),а затем в диалоговом окне Добавление новой связи введите путь к источнику данных и строку подключения. Дополнительные сведения см. в ссылках Синтаксис строки подключенияи Ссылка на строки подключения.
- Выберите Закрыть.
Удаление источника данных или связанной таблицы
Вам может потребоваться удалить источник данных или связанную таблицу, так как они больше не нужны, а также чтобы убрать диалоговое окно Диспетчер таблиц.
- Выберите Внешние данные > диспетчер связанных таблиц.
- В диалоговом окне Диспетчер связанных таблиц выберите один или несколько источников данных или связанных таблиц. Возможно, потребуется развернуть (+) элементы в столбце Источник данных.
- Выберите Удалить.
- Когда вам будет предложено подтвердить, выберите Да. При удалении связанной таблицы удаляются только сведения, используемые для открытия таблицы в источнике данных, а не сама таблица.
2.8. Связанные таблицы
На данный момент мы писали достаточно простые запросы, потому что использовали только одну таблицу. Но мы же создали в прошлой главе 4-е таблицы, и хотелось бы научиться связывать их в одно целое. Я бы не стал усложнять базу данных, создавая справочники, если бы связь не была возможной.
Давайте попробуем связать две таблицы на примере должностей. В таблице Peoples у нас есть поле «idPosition». В этом поле содержится идентификатор (первичный ключ) строки, с которой связана запись со строкой из таблицы «tbPosition». Следующий пример показывает, как можно связать эти таблицы:
SELECT * FROM tbPeoples, tbPosition WHERE tbPeoples.idPosition=tbPosition.idPosition
Первая строка, как всегда говорит, что надо вывести все поля (SELECT *). Вторая строка говорит, из каких таблиц надо получать данные (FROM tbPeoples, tbPosition). На этот раз у нас здесь указано сразу две таблицы – работники и должности. Третья строка показывает связь:
tbPeoples.idPosition=tbPosition.idPosition
Для того, чтобы указать к какой таблице относиться поле «idPosition» (поле с таким именем есть в обеих таблицах, которые мы используем) мы записываем полное имя поля как ИмяБазы.ИмяПоля. Если имя поля уникально для обеих таблиц (как «vcFamil», которое есть только в таблице tbPeolpes), то можно имя таблицы опускать. Именно поэтому мы раньше опускали имя таблицы, когда использовали поля в секции SELECT и WHERE, ведь мы работали только с одной таблицей, и никаких конфликтов не могло быть. Как только мы указали две таблицы в секции FROM, сразу возникает вероятность встретиться с конфликтами имен полей.
Итак, в секции WHERE мы указываем, что поле «idPosition» из таблицы tbPeoples равно полю «idPosition» из таблицы tbPosition.
Связь необходима. Если ее не указать, то результат будет совершенно другим. Посмотрим, что произойдет, если не указывать связь, а просто выбрать данные из двух таблиц:
SELECT * FROM tbPeoples, tbPosition
Результат выполнения этого запроса показан на рисунке 2.4. На рисунке я немного изменил результат, чтобы поле «idPosition» из таблицы tbPeoples находилось рядом с одноименным полем (с которым происходит связь) из таблицы tbPosition. Выделенный фрагмент содержит поля, которые принадлежат таблице должностей.
Теперь посмотрим на строку Иванова. Обратите внимание, что их много. Иванов связался со всеми существующими должностями. Потом идут строки Петрова, который так же связался со всеми возможными должностями. Таким образом, количество строк в результате равно количеству строк из первой таблицы умноженное на количество строк из второй таблицы. Такое объединение называется ортогональным или декартовым.
Теперь посмотрим, что означает связь:
tbPeoples.idPosition=tbPosition.idPosition
Будем смотреть на эту команду не как на связь, а на как простое ограничение WHERE, которое говорит, что в результате поле «idPosition» в обоих, таблицах должны быть равны. Посмотрите на результат работы запроса без связи. Где значения этих полей равны? Для Иванова это та строка, где связь произошла с должностью генерального директора. Для Петрова это связь с коммерческим директором и т.д. Таким образом, все лишние записи отбрасываются, и мы получаем в результате только те строки, которые связаны по правильному ключу.
Почему база данных сама не указывает связь? Просто среди таблиц может быть несколько ключей, в том числе, две таблицы могут связываться разными способами и сервер просто не может знать, какая именно связь нам нужна в данный момент. Например, исходя из практики, фамилия, имя, отчество и дата рождения образуют уникальное сочетание. Вы можете попытаться связать две таблицы по этим данным, не обращая внимания на внешние ключи, чтобы определить для себя какие-то дополнительные сведения.
При написании связи нам пришлось писать полные имена таблиц. Чтобы этого не приходилось делать, в запросе можно создавать псевдонимы. Псевдонимы создаются в секции FROM, ставятся через пробел после имени таблицы, и действуют только внутри этого запроса:
SELECT * FROM tbPeoples pl, tbPosition ps WHERE pl.idPosition=ps.idPosition
В данном запросе у нас используется две таблицы и для каждой из них указывается псевдоним. Для таблицы tbPeoples это псевдоним pl, а для таблицы tbPosition это ps. В качестве псевдонима может выступать любое имя из любого количества букв. Я чаще всего использую первую букву, если она не будет конфликтовать с другими именами. В данном случае имена двух таблиц начинается с буквы p, вот и приходиться использовать две буквы.
В секции WHERE теперь не надо писать полное имя таблицы. Достаточно только указывать псевдоним:
pl.idPosition=ps.idPosition
Удобство от использования псевдонимов очень хорошо заметно, когда вы будете связывать несколько таблиц. Давайте посмотрим, как связывать три таблицы:
SELECT * FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples=pn.idPeoples
В секции FROM перечислены уже три таблицы, а в секции WHERE наведены две связи. Таблица tbPeoples связана с таблицей должностей, а вторая связь связывает таблицу tbPeoples c таблицей телефонов.
У нас в таблице работников 19 записей, а в таблице телефонов 18 строк. Проанализируйте результат и вы увидите, что некоторые работники имеют по несколько номеров телефонов. Например, строка Иванова встречается 3 раза, но с разными номерами. Те работники, которые не имеют телефонов, в результат не попали. Почему? Просто нет связи, а значит условие pl.idPeoples=pn.idPeoples не срабатывает.
Использование жесткого объединение с помощью знака равенства называют внутренним объединением. Чтобы увидеть записи, которые не связаны, нужно использовать внешнее объединение, которое бывает левым или правым.
Как же тогда увидеть всех работников, и при этом наладить связь? Для этого используются левые объединения:
SELECT * FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples*=pn.idPeoples
Самое интересное кроется как раз в последнем условии:
pl.idPeoples*=pn.idPeoples
Обратите внимание, что слева от знака равно стоит знак умножения или проще – звездочка. Это значит, что из таблицы работников (tbPeoples, которая находиться со стороны звездочки) нужно взять все строки, а если есть связь с таблицей, указанной справа, то отобразить ее.
Выполните этот запрос, и вы увидите всех работников. У тех, у кого нет номера телефона, поля из таблицы tbPhoneNumbers будут содержать нулевые значения NULL.
Использование знака * для не жесткого объединения описано в стандарте SQL, но я говорил, что не все базы данных поддерживают этот стандарт полностью. Например, MS Access позволяет создавать левые объединения, но здесь это делается совершенно по-другому. Мы рассмотрим этот метод в главе 2.8.
Когда мы связываем таблицы жестко с помощью знака равно, такое объединение называют внутренним. Когда мы используем не жесткое объединение со звездочкой, то оно бывает правым или левым. Чтобы вам проще было понять, где какое направление – посмотрите на звездочку. Мы ее поставили слева, значит, объединение было левым. Если бы звездочка была справа от знака равенства, то объединение стало бы правым.
Чтобы получить правое объединение, достаточно поменять поля местами:
SELECT * FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pn.idPeoples=*pl.idPeoples
Вот теперь знак звездочки находиться справа. Как видите, разница в них небольшая, но она значительна при использовании объединения таблиц по методу MS.
Псевдонимы можно использовать в любой секции, даже в секции SELECT:
SELECT pl.vcFamil, pl.vcName, pl.vcSurname, ps.vcPositionName, pn.vcPhoneNumber FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples*=pn.idPeoples
Если быть более точным, то бывают случаи, когда использовать псевдонимы необходимо. Если есть имя поля, которое присутствует одновременно в обеих таблицах, то для его объявления в секции SELECT необходимо явно указать таблицу. Например, попробуйте добавить в список SELECT поле «idPeoples», без указания имени таблицы или псевдонима. В ответ на этот запрос, сервер выдаст ошибку: Ambiguous column name ‘idPeoples’ (двусмысленное имя колонки «idPeoples»). Сервер не знает, значение колонки «idPeoples», из какой таблицы нужно вернуть пользователю. Это вы знаете, что благодаря сравнению pl.idPeoples*=pn.idPeoples в результате все равно обе колонки будут содержать одно и то же значение, но сервер на это не надеется и даже не пытается понять, а просто выдает ошибку о двусмысленности.
При использовании связанных таблиц очень часто бывает необходимость выбрать одну таблицу полностью, а остальные могут выбираться частично. Например, давайте выберем из таблицы «tbPeoples» только ФИО, а из таблиц должностей и телефонов все поля:
SELECT vcFamil, vcName, vcSurname, ps.*, pn.* FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples*=pn.idPeoples
Обратите внимание, что поля, которые нам нужны из таблицы tbPeoples — перечисляются, а чтобы не перечислять все поля остальных таблиц, мы просто пишем ps.* или pn.*. То есть знак звездочки, означающий вывод всех полей относится не ко всем таблицам, а только к перечисленным.
В главе 1.2.6 мы рассматривали пример создания таблицы, в которой внешний ключ был связан с первичным ключом той же самой таблицы. В нашей тестовой базе данных такой таблицей является tbPosition, где хранятся должности работников. В этой таблице поле «idParentPosition» связано с первичным ключом «idPosition» этой же таблицы и предназначено для указания названия должности, которая является главной. Таким образом, можно построить дерево главный-подчиненный.
Давайте попробуем вывести табличку из двух полей, где первое поле будет отображать название должности, а второе – главную должность. Вот как это будет выглядеть в виде SQL запроса:
SELECT p1.vcPositionName AS 'Должность', p2.vcPositionName AS 'Главная должность' FROM tbPosition p1, tbPosition p2 WHERE p1.idParentPosition*=p2.idPosition
Прежде чем мы разберем этот запрос, давайте посмотрим на результат его работы:
ДОЛЖНОСТЬ ГЛАВНАЯ ДОЛЖНОСТЬ Генеральный директор NULL Коммерческий директор Генеральный директор Директор по общим вопросам Генеральный директор Начальник отдела снабжения Коммерческий директор Начальник отдела сбыта Коммерческий директор Начальник отдела кадров Директор по общим вопросам ОТиЗ Директор по общим вопросам Бухгалтерия Коммерческий директор Менеджер по снабжению Начальник отдела снабжения Менеджер по продажам Начальник отдела сбыта
Первая строка соответствует должности генерального директора. Это самый главный человек в компании, поэтому для нее во второй колонке указан NULL, т.е. главной должности нет.
Следующая строка в первой колонке содержит должность коммерческого директора. Вполне логично, что она подчиняется генеральному директору, что и отображено во второй колонке результата.
Теперь посмотрим на SQL запрос, с помощью которого мы получили эти данные. Для начала посмотрим на секцию FROM, где дважды указана одна и та же таблица «tbPosition», но с разными псевдонимами p1 и p2. В секции WHERE мы наводим связь между псевдонимами одной и той же таблицы:
p1.idParentPosition*=p2.idPosition
Таким образом, через псевдонимы мы указали связь внутри одной и той же таблицы. В секции SELECT мы отображаем имя должности из первого псевдонима и имя из второго.
Теперь посмотрите на следующий запрос:
SELECT p1.vcPositionName AS 'Должность', p2.vcPositionName AS 'Главная должность', p3.vcPositionName AS 'Главная для главной' FROM tbPosition p1, tbPosition p2, tbPosition p3 WHERE p1.idParentPosition=p2.idPosition AND p2.idParentPosition*=p3.idPosition
Здесь мы дважды использовали связь таблицы саму на себя. Результат работы этого запроса:
Должность Главная должность Главная для главной Коммерческий директор Генеральный директор NULL Директор по общим вопросам Генеральный директор NULL Начальник отдела снабжения Коммерческий директор ГенеральныйДиректор Начальник отдела сбыта Коммерческий директор ГенеральныйДиректор
Давайте теперь напишем запрос, который отобразит все записи из всех связанных таблиц нашей тестовой базы данных. А таблиц у нас всего 4, но в нашей секции FROM будет пять таблиц, потому что дважды будет ссылка на таблицу должностей, чтобы отобразить должность текущего работника и должность начальника:
SELECT pl.vcFamil, pl.vcName, pl.vcSurname, dDateBirthDay, p1.vcPositionName AS 'Должность', p2.vcPositionName AS 'Начальник', pn.vcPhoneNumber, pt.vcTypeName FROM tbPeoples pl, tbPosition p1, tbPosition p2, tbPhoneNumbers pn, tbPhoneType pt WHERE pl.idPosition=p1.idPosition AND p1.idParentPosition*=p2.idPosition AND pn.idPeoples=pl.idPeoples AND pt.idPhoneType=pn.idPhoneType
Попробуйте разобраться в этом запросе. Если вы поймете его, то можно считать, что тема связанных таблиц усвоена удачно.
Саязанные таблицы в стиле MS
Объединение по стандарту SQL, который мы рассматривали в главе 2.7, описывает условие связи в секции WHERE. В MS зачем-то связи перенесли в секцию FROM. На мой взгляд, это как минимум не удобно для создания и для чтения связей. Стандартный вариант намного проще и удобнее. И все же, метод MS мы рассмотрим, ведь только с его помощью в MS Access можно создать левое или правое объединение, и этот же метод поддерживается в MS SQL Server.
Ортогональное объединение по методу MS, т.е. без указания связи:
SELECT * FROM tbPeoples CROSS JOIN tbPosition
Внутреннее объединение (эквивалентно знаку равенства) по методу MS описывается следующим образом:
SELECT * FROM tbPeoples pl INNER JOIN tbPosition ps ON pl.idPosition=ps.idPosition
Как видите, для этого метода не нужна секция WHERE, но зато намного больше всего нужно писать. Вначале мы описываем, что нам нужно внутреннее объединение (INNER JOIN). Слева и справа от этого оператора указываются таблицы, которые нужно связать. После этого ставиться ключевое слово ON, и только теперь наводим связь между полями связанных таблиц. Таким образом, этот запрос эквивалентен следующему:
SELECT * FROM tbPeoples pl, tbPosition ps WHERE pl.idPosition=ps.idPosition
Самая большая путаница начинается, когда нужно объединить три таблицы в одно целое. Посмотрите на следующий запрос:
SELECT * FROM tbPeoples pl LEFT OUTER JOIN tbPhoneNumbers pn ON pl.idPeoples=pn.idPeoples INNER JOIN tbPosition ps ON pl.idPosition=ps.idPosition
Сначала объединяются таблицы tbPeoples и tbPhoneNumbers через внешнее левое объединение (LEFT OUTER JOIN). Затем указывается связь между этими таблицами. А вот теперь результат объединение, связываем внутренним объединением (INNER JOIN) с таблицей tbPosition. Внимательно осмотрите запрос, чтобы понять его формат, и что в нем происходит.
Чтобы получить правое объединение, необходимо просто поменять перечисление таблиц местами:
SELECT * FROM tbPhoneNumbers pn RIGHT OUTER JOIN tbPeoples pl ON pl.idPeoples=pn.idPeoples INNER JOIN tbPosition ps ON pl.idPosition=ps.idPosition
Меняется местами перечисление таблиц, а вот порядок указания связанных полей не имеет особого значения и здесь ничего не меняется.
Если честно, то мне не очень нравиться объединение по методу Microsoft. Какое-то оно неудобное и громоздкое. Даже не знаю, зачем его придумали, когда в стандарте есть все то же самое, только намного проще и нагляднее.