Меню

Связь между таблицами субд устанавливает отношения между

Связь между таблицами субд устанавливает отношения между

Схема данных является графическим образом БД. Она используется различными объектами Access для определения связей между несколькими таблицами. Например, при создании формы, содержащей данные из нескольких взаимосвязанных таблиц, схема данных обеспечивает автоматический согласованный доступ к полям этих таблиц. Она же обеспечивает целостность взаимосвязанных данных при корректировке таблиц.

После того, как созданы таблицы БД, можно установить связи между ними, обратившись к схеме данных. Запуск схемы данных выполняется через значок «Схема данных» на панели инструментов Access.

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

Обязательным условием при установлении связи является совпадение связываемых полей по типу и формату.

Типы связей

Тип отношения в создаваемой Microsoft Access связи зависит от способа определения связываемых полей.

Отношение «один-ко-многим» является наиболее часто используемым типом связи между таблицами. В отношении «один-ко-многим» каждой записи в таблице A могут соответствовать несколько записей в таблице B, но запись в таблице B не может иметь более одной соответствующей ей записи в таблице A.

При отношении «один-к-одному» запись в таблице A может иметь не более одной связанной записи в таблице B и наоборот. Отношения этого типа используются не очень часто, поскольку большая часть сведений, связанных таким образом, может быть помещена в одну таблицу. Отношение «один-к-одному» может использоваться для разделения очень широких таблиц, для отделения части таблицы по соображениям защиты, а также для сохранения сведений, относящихся к подмножеству записей в главной таблице.

При отношении «многие-ко-многим» одной записи в таблице A могут соответствовать несколько записей в таблице B, а одной записи в таблице B несколько записей в таблице A. Этот тип связи возможен только с помощью третьей (связующей) таблицы, первичный ключ которой состоит из двух полей, которые являются внешними ключами таблиц A и B. Отношение «многие-ко-многим» по сути дела представляет собой два отношения «один-ко-многим» с третьей таблицей.

Определение связей между таблицами

Связь между таблицами определяется путем добавления связываемых таблиц в окно «Схема данных» с последующим перетаскиванием ключевого поля из одной таблицы в другую. Рассмотрим пример связывания таблиц.

Предположим, что требуется установить связь между таблицами «Кафедра» и «Преподаватель» через поле ККАФ (код кафедры). В таблице «Кафедра» это поле является уникальным ключом, а в таблице «Преподаватель» — внешним ключом. Если схема данных создается заново, то при нажатии на кнопку «Схема данных» поверх окна схемы данных появится окно «Добавление таблицы» . В этом окне следует выделить требуемые таблицы и нажать «Добавить» .

В результате в окно схемы данных будут добавлены графические образы двух таблиц:

Необходимо перетащить мышью поле ККАФ таблица «Кафедра» на поле ККАФ таблицы «Преподаватель» . В открывшемся окне «Изменение связей» следует установить флажок «Обеспечение целостности данных» . В этом случае Access будет выдавать предупреждающие сообщения о неправильном вводе данных, если, например, в поле ККАФ подчиненной таблицы «Преподаватель» будет введено значение, отсутствующее в поле ККАФ базовой таблицы «Кафедра» .

Обратите внимание, что Access автоматически определил тип связи как «один-ко-многим».

Можно также установить флажки «каскадное обновление связей» и «каскадное удаление связей» . В этом случае Access автоматически скорректирует (удалит) записи в подчиненных таблицах, если будут изменены записи в базовой таблице.

После нажатия на кнопку » Создать «, образы таблиц будут соединены связями как показано на рисунке. Ключевые в базовых таблицах выделяются жирным шрифтом.

Для установления связей по составному ключу необходимо в окне «Изменение связей» в полях «Таблица/Запрос» и «Связанная таблица/запрос» вручную выбрать из списков пары связываемых полей. На рисунке показан пример связи по составному ключу.

Если перетащить поле, не являющееся ключевым и не имеющее уникального индекса, на другое поле, которое также не является ключевым и не имеет уникального индекса, создается неопределенное отношение. В запросах, содержащих таблицы с неопределенным отношением, Microsoft Access по умолчанию отображает линию объединения между таблицами, но условия целостности данных при этом не накладываются и нет гарантии уникальности записей в любой из таблиц.

Источник



Как определить связи между таблицами в базе данных Access

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Оригинальный номер КБ: 304466

Внимание! Материал, изложенный в этой статье, требует знания пользовательского интерфейса на компьютерах с одним пользователем. Эта статья относится только к базе данных Microsoft Access (.mdb или .accdb).

Аннотация

В этой статье описывается, как определить отношения в базе данных Microsoft Access. Статья включает в себя следующие темы:

  • Что такое связи между таблицами?
  • Виды связей между таблицами
    • Связи «один ко многим»
    • Связи «многие ко многим»
    • Связи «один к одному»
  • Как определить связи между таблицами
    • Как определить связи «один ко многим» или «один к одному»
    • Как определить связь «многие ко многим»
  • Целостность данных
  • Каскадные обновления и удаления
  • Типы соединения

Что такое связи между таблицами?

В реляционной базе данных отношения позволяют предотвратить избыточные данные. Например, при разработке базы данных, которая будет отслеживать информацию о книгах, может быть таблица «Названия», в которой хранится информация о каждой книге, например название книги, дата публикации и издатель. Существует также информация, которую вы можете хранить об издателе, например, номер телефона издателя, адрес и почтовый индекс. Если вы храните всю эту информацию в таблице «Названия», номер телефона издателя будет дублироваться для каждого названия, которое печатает издатель.

Лучшим решением является хранение информации издателя только один раз, в отдельной таблице, которую мы будем называть «Издатели». Затем вы поместите указатель в таблице «Названия», которая ссылается на запись в таблице «Издатели».

Чтобы убедиться, что данные остаются синхронизированными, можно обеспечить целостность данных между таблицами. Отношения целостности данных помогают убедиться, что информация в одной таблице соответствует информации в другой. Например, каждое название в таблице «Названия» должно быть связано с конкретным издателем в таблице «Издатели». Название не может быть добавлено в базу данных для издателя, которого не существует в базе данных.

Логические отношения в базе данных позволяют эффективно запрашивать данные и создавать отчеты.

Виды связей между таблицами

Связь работает путем сопоставления данных в ключевых столбцах, обычно столбцах (или полях), которые имеют одно и то же имя в обеих таблицах. В большинстве случаев связь соединяет основной ключ или уникальный столбец идентификатора для каждой строки, от одной таблицы к полю в другой таблице. Колонка в другой таблице называется «внешний ключ». Например, если вы хотите отслеживать продажи каждого заголовка книги, вы создаете связь между основным ключевым столбцом (назовем его title_ID) в таблице «Названия» и столбцом в таблице «Продажи», который называется title_ID. Столбец title_ID в таблице «Продажи» является внешним ключом.

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

Связи «один ко многим»

Связь «один ко многим» являются наиболее распространенным типом связи. В такого рода связях строка в таблице А может иметь много строк в таблице B. Но строка в таблице B может иметь только одну строку в таблице А. Например, таблицы «Издатели» и «Названия» имеют связь «один ко многим». То есть, каждый издатель выпускает много названий. Но каждое название принадлежит только одному издателю.

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

В окне связей в Access, сторона первичного ключа связи «один ко многим» обозначается номером 1. Сторона внешнего ключа связи обозначается символом бесконечности.

Пример для связей

Связи «многие ко многим»

В связи «многие ко многим» строка в таблице А может иметь много совпадающих строк в таблице B, и наоборот. Вы создаете такую связь, определяя третью таблицу, которая называется промежуточной таблицей. Первичный ключ промежуточной таблицы состоит из внешних ключей как таблицы А, так и таблицы B. Например, таблица «Авторы» и таблица «Названия» имеют связь «многие ко многим», которая определяется связью «один ко многим» из каждой из этих таблиц к таблице «TitleAuthors». Первичным ключом таблицы «TitleAuthors» является комбинация столбца au_ID (первичный ключ таблицы «Авторы») и столбца title_ID (первичный ключ таблицы «Названия»).

Пример для связей

Связи «один к одному»

В связи «один к одному» строка в таблице А может иметь не более одной совпадающей строки в таблице B, и наоборот. Связь «один к одному» создается, если оба связанных столбца являются первичными ключами или имеют уникальные ограничения.

Этот тип отношений не распространен, потому что большая часть информации, которая связана таким образом, будет в одной таблице. Вы можете использовать связь «один к одному», чтобы предпринять следующие действия:

  • Разделите таблицу на множество столбцов.
  • Из соображений безопасности изолируйте часть таблицы.
  • Храните данные, которые недолговечны и могут быть легко удалены при удалении таблицы.
  • Храните информацию, которая относится только к подмножеству основной таблицы.

В Access сторона первичного ключа связи «один к одному» обозначается символом ключа. Сторона внешнего ключа также обозначается символом ключа.

Как определить связи между таблицами

При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем AutoNumber. Вы можете сопоставить поле AutoNumber с полем Number, только если свойство FieldSize обоих совпадающих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство theFieldSizeproperty обоих полей имеет значение Long Integer. Даже если оба совпадающих поля являются числовыми полями, они должны иметь параметр sameFieldSizeproperty.

Читайте также:  Запишите таблицу начертание шрифтов

Как определить связи «один ко многим» или «один к одному»

Чтобы создать связь «один ко многим» или «один к одному», выполните следующие действия.

Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.

В Access 2002 и Access 2003 выполните следующие действия.

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

В Access 2007, Access 2010 или Access 2013 нажмите Связи в группе Показать/Скрыть на вкладке Инструменты базы данных.

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

Дважды щелкните названия таблиц, которые вы хотите связать, а затем закройте диалоговое окно Показать таблицу. Чтобы создать связь между одной и той же таблицей, добавьте эту таблицу два раза.

Перетащите поле, которое вы хотите связать, из одной таблицы в связанное поле в другой таблице. Чтобы перетащить несколько полей, нажмите Ctrl, нажмите на каждое поле, а затем перетащите их.

В большинстве случаев вы перетаскиваете поле первичного ключа (это поле отображается жирным текстом) из одной таблицы в аналогичное поле (это поле часто имеет одно и то же имя), которое называется внешним ключом в другой таблице.

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

При необходимости установите параметры связей. Если у вас есть информация о конкретном элементе в диалоговом окне Изменение связей, нажмите кнопку со знаком вопроса, а затем нажмите на элемент. (Эти параметры будут подробно описаны ниже в этой статье.)

Нажмите кнопку Создать, чтобы создать связь.

Повторите шаги с 4 по 7 для каждой пары таблиц, которые вы хотите связать.

При закрытии диалогового окна Изменение связей Access спрашивает, хотите ли вы сохранить макет. Сохраняете ли вы макет или не сохраняете макет, созданные вами связи сохраняются в базе данных.

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

Как определить связь «многие ко многим»

Чтобы создать связь «многие ко многим», выполните следующие действия.

Создайте две таблицы, которые будут иметь связь «многие ко многим».

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

В связующей таблице установите первичный ключ, чтобы включить основные ключевые поля из двух других таблиц. Например, в связующей таблице «TitleAuthors» первичный ключ будет состоять из полей OrderID и ProductID.

Чтобы создать первичный ключ, выполните следующие действия:

Откройте таблицу в Конструкторе.

Выберите поле или поля, которые вы хотите определить в качестве первичного ключа. Чтобы выбрать одно поле, нажмите на селектор строки для нужного поля. Чтобы выбрать несколько полей, удерживайте клавишу Ctrl, а затем нажмите селектор строки для каждого поля.

В Access 2002 или в Access 2003 нажмите на Первичный ключ на панели инструментов.

В Access 2007 нажмите на Первичный ключ в группе Инструменты на вкладке Дизайн.

Если вы хотите, чтобы порядок полей в первичном ключе с несколькими полями отличался от порядка этих полей в таблице, нажмите Индексы на панели инструментов для отображения диалогового окна Indexes, а затем заново упорядочите имена полей для индекса с именем PrimaryKey.

Определите связь один-ко-многим между каждой основной и связующей таблицами.

Целостность данных

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

  • Совпадающие поля из основной таблицы являются первичным ключом или имеет уникальный индекс.
  • Связанные поля имеют один и тот же тип данных. Из этого правила есть два исключения: Поле AutoNumber может быть связано с полем Number, которое имеет FieldSize настройку свойства Long Integer, а поле AutoNumber, которое имеет FieldSize настройку свойства Replication ID, может быть связано с полем Number, которое имеет FieldSize настройку свойства Replication ID.
  • Обе таблицы относятся к одной и той же базе данных Access. Если таблицы являются связанными таблицами, они должны быть таблицами в формате Access, и необходимо открыть базу данных, в которой они хранятся, чтобы установить целостность данных. Референтная целостность не может быть применена для связанных таблиц из баз данных в других форматах.

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

  • Невозможно ввести значение во внешнем ключевом поле связанной таблицы, которое не существует в первичном ключе первичной таблицы. Тем не менее, можно ввести значение Null во внешнем ключе. Это указывает на то, что записи не связаны между собой. Например, невозможно иметь заказ, который назначается клиенту, который не существует. Тем не менее, можно иметь заказ, который не назначается никому, введя значение Null в поле CustomerID.
  • Вы не можете удалить запись из основной таблицы, если в соответствующей таблице существуют соответствующие записи. Например, вы не можете удалить запись сотрудника из таблицы «Сотрудники», если в таблице «Заказы» есть заказы, назначенные сотруднику.
  • Невозможно изменить основное ключевое значение в основной таблице, если эта запись имеет соответствующие записи. Например, вы не можете изменить идентификатор сотрудника в таблице «Сотрудники», если в таблице «Заказы» есть заказы, назначенные этому сотруднику.

Каскадные обновления и удаления

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

Если выбрать опцию Каскадное обновление связанных полей при определении отношения, то каждый раз при изменении первичного ключа записи в первичной таблице Microsoft Access автоматически обновляет первичный ключ к новому значению во всех связанных записях. Например, при изменении идентификатора клиента в таблице «Клиенты», поле CustomerID в таблице «Заказы» автоматически обновляется для каждого из заказов этого клиента, чтобы связи не были нарушены. Access каскадирует обновления без отображения каких-либо сообщений.

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

Если вы выберете Каскадное обновление связанных полей при определении связей, при удалении записей в первичной таблице Access автоматически удаляет связанные записи в соответствующей таблице. Например, при удалении записи клиента из таблицы «Клиенты», все заказы клиента автоматически удаляются из таблицы «Заказы». (Это включает записи в таблице «Детали заказа», которые связаны с записями «Заказы»). При удалении записей из формы или таблицы после установки флажка рядом с Каскадное удаление связанных записей, Access предупреждает вас, что связанные записи также могут быть удалены. Однако при удалении записей с помощью запроса удаления Access автоматически удаляет записи в соответствующих таблицах, не отображая предупреждение.

Типы соединения

Существует три основных типа соединения: Вы можете увидеть их на следующем снимке экрана:

Типы соединения.

Вариант 1 определяет внутреннее соединение. Внутреннее соединение — это соединение, в котором записи из двух таблиц объединяются в результатах запроса только в том случае, если значения в объединенных полях соответствуют определенному состоянию. В запросе соединение по умолчанию — это внутреннее соединение, которое выбирает записи только в том случае, если значения в объединенных полях совпадают.

Вариант 2 определяет левое внешнее соединение. Левое внешнее соединение — это соединение, в котором все записи с левой стороны операции LEFT JOIN в оператора запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на правой стороне.

Вариант 3 определяет правое внешнее соединение. Правое внешнее соединение — это соединение, в котором все записи с правой стороны операции RIGHT JOIN в операторе запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на левой стороне.

Источник

SQL для начинающих: часть 3 — связи базы данных

Russian (Pусский) translation by Yuri Yuriev (you can also view the original English article)

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

Читайте также:  Две разные таблицы подряд

Вы также можете увидеть базы данных SQL в действии, просмотрев SQL scripts, apps and add-ons на рынке Envato.

Напоминание

Введение

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

Существует несколько типов отношений базы данных. Сегодня мы рассмотрим следующее:

  • Отношения один к одному
  • Отношения «один ко многим» и «многие к одному»
  • «Многие ко многим» отношения
  • Самостоятельные ссылки

При выборе данных из нескольких таблиц с отношениями мы будем использовать запрос JOIN. Существует несколько типов JOIN, и мы собираемся узнать следующее:

  • Перекрестные соединения
  • Обычные соединения
  • Внутренние соединения
  • Левые (внешние) соединения
  • Правые (внешние) соединения

Мы также узнаем об оговорках ON и USING.

Отношения один к одному

Предположим, у вас есть таблица для клиентов:

Мы можем поместить информацию об адресе клиента в отдельную таблицу:

Теперь мы имеем отношение между таблицей Customers и таблицей Addresses. Если каждый адрес может принадлежать только одному клиенту, это отношение «Один к одному». Имейте в виду, что такого рода отношения не очень распространены. Наша начальная таблица, которая включала адрес вместе с клиентом, в большинстве случаев могла работать нормально.

Обратите внимание: теперь в таблице Customers есть поле с именем «address_id», которое ссылается на запись соответствия в таблице Address. Это называется «Foreign Key» и используется для всех видов отношений баз данных. Мы рассмотрим этот вопрос позже.

Мы можем показать отношения между клиентскими и адресными записями следующим образом:

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

Отношения «один ко многим» и «многие к одному»

Это наиболее часто используемый тип отношений. Рассмотрим веб-сайт e-commerce со следующим:

  • Клиенты могут делать много заказов.
  • Заказы могут содержать много позиций.
  • Позиции могут иметь описания на многих языках.

В этих случаях нам необходимо создать отношения «один ко многим». Вот пример:

У каждого клиента может быть ноль, один или несколько заказов. Но заказ может принадлежать только одному клиенту.

Отношения «многие ко многим»

В некоторых случаях вам может потребоваться несколько экземпляров с обеих сторон. Например, каждый заказ может содержать несколько элементов. И каждый элемент также может быть в нескольких заказах.

Для этих отношений нам нужно создать дополнительную таблицу:

Таблица Items_Orders имеет только одну цель, а именно, чтобы создать отношение «многие ко многим» между элементами и заказами.

Вот картинка таких отношений:

Если вы хотите включить записи items_orders в график, это может выглядеть так:

Самостоятельные ссылки

Это используется, когда таблица должна иметь отношения с самой собой. Например, у вас есть реферальная программа. Клиенты могут направлять других клиентов на ваш веб-сайт. Таблица может выглядеть так:

Клиенты 102 и 103 были переданы клиентом 101.

На самом деле это может быть похоже на отношение «один ко многим», поскольку один клиент может ссылаться на нескольких клиентов. Также он может выглядеть, как древовидная структура:

Один клиент может ссылаться на ноль, одного или несколько клиентов. К каждому клиенту может обращаться только один клиент, или вообще никто.

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

Foreign Keys

До сих пор мы узнали только о некоторых концепциях. Теперь пришло время воплотить их в жизнь с помощью SQL. Для этой части нам нужно понять, что такое Foreign Keys.

В приведённых выше примерах отношений мы всегда имели эти поля «**** _ id», которые ссылались на столбец в другой таблице. В этом примере столбец customer_id в таблице Orders является столбцом Foreign Key:

В базе данных типа MySQL есть два способа создания столбцов внешних ключей:

Чёткое определение Foreign Key

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

Теперь таблицу заказов, в которой будет Foreign Key:

Оба столбца (customers.customer_id и orders.customer_id) должны иметь одинаковую структуру данных. Если один является INT, другой не должен быть BIGINT, например.

Обратите внимание, что в MySQL только механизм InnoDB имеет полную поддержку Foreign Keys. Но другие механизмы хранения данных по-прежнему позволят вам указывать их без каких-либо ошибок. Кроме того, столбец Foreign Key индексируется автоматически, если не указать для него другой индекс.

Без явной декларации

Та же таблица заказов может быть создана без явного объявления столбца customer_id как Foreign Key:

При получении данных с помощью запроса JOIN вы всё равно можете рассматривать этот столбец как Foreign Key , хотя механизм базы данных не знает об этом отношении.

Далее мы собираемся узнать о JOIN-запросах.

Визуализация отношений

Моим любимым программным обеспечением для проектирования баз данных и визуализации отношений Foreign Key является MySQL Workbench.

После разработки базы данных вы можете экспортировать SQL и запустить его на своем сервере. Это очень удобно для больших и сложных баз данных.

JOIN Queries

Для извлечения данных из базы, имеющей отношения, нам часто приходится использовать JOIN queries.

Прежде чем начать, давайте создадим таблицы и некоторые образцы данных для работы.

У нас 4 клиента. У одного клиента два заказа, у двух клиентов по одному заказу, а у одного клиента нет заказа. Теперь давайте посмотрим различные виды JOIN queries, которые мы можем запустить в этих таблицах.

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

Это тип JOIN query по умолчанию, если условие не указано.

Результатом является так называемый «Cartesian product» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Так как каждая таблица имела 4 строки, мы получили результат из 16 строк.

Ключевое слово JOIN может быть опционально заменено запятой.

Конечно, такой результат не очень полезен. Давайте посмотрим на другие типы соединений.

Обычное соединение

При таком типе JOIN query таблицы должны иметь имя соответствующего столбца. В нашем случае обе таблицы имеют столбец customer_id. Таким образом, MySQL будет присоединяться к записям только тогда, когда значение этого столбца соответствует двум записям.

Как вы можете видеть, столбец customer_id отображается только один раз, потому что ядро базы данных рассматривает это как общий столбец. Мы видим два заказа Адама, а два других — Джо и Сэнди. Наконец, мы получаем некоторую полезную информацию.

Внутреннее соединение

Когда указано условие соединения, выполняется Inner Join. В этом случае было бы неплохо иметь поле customer_id в обеих таблицах. Результаты должны быть похожими на Natural Join.

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

Давайте добавим еще несколько условий в запрос.

На этот раз мы получили заказы на сумму более $15.

ON Clause

Прежде чем перейти к другим типам соединений, нам нужно посмотреть ON clause. Это полезно для помещения условий JOIN в отдельное предложение.

Теперь мы можем отличить условие JOIN от условий WHERE. Но есть и небольшая разница в функциональности. Мы увидим это в примерах LEFT JOIN.

USING Clause

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

На самом деле это похоже на NATURAL JOIN, поэтому столбец join (customer_id) не повторяется дважды в результатах.

Левое (внешнее) соединение

LEFT JOIN — это тип внешнего соединения. В этих запросах, если во второй таблице не найдено совпадений, запись из первой таблицы по-прежнему отображается.

Хотя у Энди нет заказов, его запись все ещё отображается. Значения под столбцами второй таблицы имеют значение NULL.

Это полезно для поиска записей, которые не имеют отношений. Например, мы можем искать клиентов, которые не разместили какие-либо заказы.

Всё, что мы сделали, это нашли NULL для order_id.

Также обратите внимание, что ключевое слово OUTER является необязательным. Вы можете просто использовать LEFT JOIN вместо LEFT OUTER JOIN.

Условия

Теперь давайте рассмотрим запрос с условием.

Так что случилось с Энди и Сэнди? LEFT JOIN должен был вернуть клиентов без соответствующих заказов. Проблема в том, что предложение WHERE блокирует эти результаты. Чтобы их получить, мы можем попытаться включить условие NULL.

У нас Энди, но нет Сэнди. Тем не менее это выглядит не так. Чтобы получить то, что мы хотим, нам нужно использовать ON clause.

Теперь у нас есть все, и все заказы выше $ 15. Как я уже говорил, ON clause иногда имеет несколько иную функциональность, чем WHERE clause. В условии Outer Join , таком как этот, строки включаются, даже если они не соответствуют условиям ON clause.

Правое (внешнее) соединение

RIGHT OUTER JOIN работает точно так же, но порядок таблиц обратный.

На этот раз у нас нет результатов NULL, потому что каждый заказ имеет соответствующую запись клиента. Мы можем изменить порядок таблиц и получить те же результаты, что и в LEFT OUTER JOIN.

Читайте также:  Используя текст заполните таблицу время

Теперь у нас есть эти значения NULL, потому что таблица Customers находится на правой стороне соединения.

Заключение

Спасибо, что прочитали статью. Надеюсь, вам понравилось! Пожалуйста, оставляйте свои комментарии и вопросы, и хорошего дня!

Не забудьте проверить SQL scripts, apps and add-ons на рынке Envato. Вы получите представление о возможностях баз данных SQL, и сможете найти идеальное решение, которое поможет вам в текущем проекте разработки.

Следуйте за нами на Twitter или подпишитесь на Nettuts + RSS Feed для получения лучших обучающих материалов по веб-разработке в Интернете.

Источник

Связи между таблицами базы данных

1. Введение

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

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

2. Благодарности

Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!

3.1. Как организовываются связи?

Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

3.2. Виды связей

Связи делятся на:

  1. Многие ко многим.
  2. Один ко многим.
    • с обязательной связью;
    • с необязательной связью;
  3. Один к одному.
    • с обязательной связью;
    • с необязательной связью;

Рассмотрим подробно каждый из них.

4. Многие ко многим

Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:

  • Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
  • Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.

Работников представляет таблица «Employee» (id, имя, возраст), должности представляет таблица «Position» (id и название должности). Как видно, обе эти таблицы связаны между собой по правилу многие ко многим: каждому работнику соответствует одна и больше должностей (многие должности), каждой должности соответствует один и больше работников (многие работники).

4.1. Как построить такие таблицы?

EmployeeId PositionId
1 1
1 2
2 3
3 3

Слева указаны работники (их id), справа — должности (их id). Работники и должности на этой таблице указываются с помощью id’шников.

На эту таблицу можно посмотреть с двух сторон:

  1. Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
  2. Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.

4.2. Реализация

С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы

  • ссылаемся атрибутом PositionId таблицы EmployeesPositions на атрибут PositionId таблицы Position;
  • атрибутом EmployeeId таблицы EmployeesPositions — на атрибут EmployeeId таблицы Employee;

4.3. Вывод

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

5. Один ко многим

Эта самая распространенная связь между базами данных. Мы рассматриваем ее после связи многие ко многим для сравнения.

Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).

В этом случае мы наблюдаем следующее: пользователь может иметь многие номера телефонов, но нельзя сказать, что номеру телефона принадлежит определенный пользователь.

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

5.1. Как построить такие таблицы?

PhoneId PersonId PhoneNumber
1 5 11 091-10
2 5 19 124-66
3 17 21 972-02

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

5.2. Почему мы не делаем тут таблицу-посредника?

Таблица-посредник нужна только в том случае, если мы имеем связь многие-ко-многим. По той простой причине, что мы можем рассматривать ее с двух сторон. Как, например, таблицу EmployeesPositions ранее:

  1. Каждому работнику принадлежат несколько должностей (многие).
  2. Каждой должности принадлежит несколько работников (многие).

Но в нашем случае мы не можем сказать, что каждому телефону принадлежат несколько пользователей — номеру телефона может принадлежать только один пользователь.
Теперь прочтите еще раз заметку в конце пункта 5.1. — она станет для вас более понятной.

5.3. Реализация

6. Один к одному

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:

DisabledPersonId EmployeeId
1 159
2 722
3 937

Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.

Выполнив это мы получили связь один к одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

6.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

6.2. Реализация

7. Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

7.1. Один ко многим

А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.

7.2. Один к одному

А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.

7.3. Многие ко многим

А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.

8. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

  1. Возле таблицы Person находится золотой ключик. Он обозначает слово «один».
  2. Возле таблицы Phone находится знак бесконечности. Он обозначает слово «многие».

9. Итоги

10. Задачи

Для лучшего усвоения материала предлагаю вам решить следующие задачи:

  1. Описать таблицу фильм: id, название, длительность, режиссер, жанр фильма. Обратите внимание на то, что у фильма может быть более одного жанра, а к одному жанру может относится более, чем один фильм.
  2. Описать таблицу песня: id, название, длительность, певец. При этом у песни может быть более одного певца, а певец мог записать более одной песни.
  3. Реализовать таблицу машина: модель, производитель, цвет, цена
    • Описать отдельную таблицу производитель: id, название, рейтинг.
    • Описать отдельную таблицу цвета: id, название.

    У одной машины может быть только один производитель, а у производителя — много машин. У одной машины может быть много цветов, а у одного цвета может быть много машин.

  4. Добавить в БД из пункта 6.2. таблицу военно-обязанных по типу того, как мы описали отдельную таблицу DisabledEmployee.

Источник

Adblock
detector