Урок Виды связи таблиц, команды SQL для связи таблиц

[Dima972511]

Здравствуйте уважаемые пользователи форума

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

Виды связи таблиц:

• Один к одному
• Один ко многим
• Многие ко многим

Связь один к одному на примере​

Таблицы:

1. Accounts
   • ID (Тип: INT, INDEX: PRIMARY, UNIQUE)
   • Name (Тип: VARCHAR, Длина: 24)
   • Password (Тип: VARCHAR, Длина: 24)
2. Money
   • ID (Тип: INT, INDEX: PRIMARY, UNIQUE)
   • OwnerID (Тип: INT, INDEX: UNIQUE)
   • Bank (Тип: INT)

Задача:

Имеются 2 таблицы: Accounts где хранятся аккаунта, и таблицы Money где мы храним счета наших игроков.
Каждому игроку соответствует только 1 счет, и каждому счету соответствует только 1 игрок.

Решение:

В данном случае владельца мы определяем по полю OwnerID - где храним поле ID таблицы Accounts нужного нам игрока.
При этом поле ID таблицы accounts имеет уникальный индекс и не допускает повторений как и поле OwnerID таблицы Money.
Соотвественно каждый игрок может иметь 1 счет, и каждый счет может иметь 1 игрока. Такая связь называется связью один к одному.

Практика:

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

• Имя владельца банковского счета
• Доступный баланс

Выполним в пункте SQL следующий запрос:

SELECT ac.Name as 'Имя владельца', b.Bank as 'Доступный баланс' FROM accounts ac INNER JOIN money b ON ac.id = b.OwnerID

Конструкции

accounts ac

money b

Позволяют обращаться к таблице accounts в запросе с помощью простого наименования ac (вы можете как угодно представить её в запросе),
и к таблице money с помощью b соотвественно.

ac.Name as 'Имя владельца'

b.Bank as 'Доступный баланс'

С помощью конструкции as в данном фрагменте мы указываем как будет называться наше поле при выполнение запроса.
Поле accounts.Name будет выводится в поле Имя владельца, а поле Money.Bank будет в поле Доступный баланс

INNER JOIN money b ON ac.id = b.OwnerID

Данная конструкция служит для связи таблиц, после выражения INNER JOIN следует имя подключаемой таблицы, а после выражения ON
следует указание по каким полям мы объединяем таблицы. В данном случае мы объединяем таблицы по полям accounts.ID = money.OwnerID

Связь один ко многим на примере​

Таблицы:

1. Accounts
   • ID (Тип: INT, INDEX: PRIMARY, UNIQUE)
   • Name (Тип: VARCHAR, Длина: 24)
   • Password (Тип: VARCHAR, Длина: 24)
2. Cars
   • ID (Тип: INT, INDEX: PRIMARY, UNIQUE)
   • OwnerID (Тип: INT)
   • Model (Тип: INT)
   • Nomer (Тип: VARCHAR, INDEX: UNIQUE)

Задача:

Имеются 2 таблицы. В таблице Accounts мы храним аккаунты наших игроков, в таблице Cars храним машины наших игроков.
Каждый игрок может иметь несколько машин.Каждая машина имеет уникальный номерной знак.

Решение:

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

Практика:

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

• Имя владельца автомобиля
• Модель автомобиля
• Номер автомобиля

Выполним следующий запрос:

SELECT ac.Name as 'Имя владельца', c.Model as 'Модель транспортного средства', c.Nomer as 'Номер транспортного средства' FROM accounts ac INNER JOIN cars c ON  ac.ID = c.OwnerID ORDER BY ac.ID

Конструкция

ORDER BY ac.ID

Позволяет нам отсортировать результат запроса по полю ID таблицы Accounts. Что позволить удобно просматривать машины игроков по порядку следования игроков в таблице.

Так-же представим что нам необходимо достать автомобили одного из игроков, а не всех подряд. Допустим вытащим автомобили игрока который имеет поле ID = 1:

SELECT ac.Name as 'Имя владельца', c.Model as 'Модель транспортного средства', c.Nomer as 'Номер транспортного средства' FROM accounts ac INNER JOIN cars c ON  ac.ID = c.OwnerID WHERE ac.ID = 1

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

SELECT ac.Name as 'Имя владельца', c.Model as 'Модель транспортного средства', c.Nomer as 'Номер транспортного средства' FROM accounts ac INNER JOIN cars c ON  ac.ID = c.OwnerID GROUP BY ac.Name

Связь многие ко многим на примере​

Таблицы:

1. Supplier (Поставщик)
   • ID (ТИП: INT, INDEX: PRIMARY, UNIQUE)
   • Name (ТИП: VARCHAR)
2. Trucker (Дальнобойщик)
   • ID (ТИП: INT, INDEX: PRIMARY, UNIQUE)
   • Name (ТИП: VARCHAR)

Задача:

Мы разрабатываем сервер посвященный дальнобойщикам. Наша задача узнать сколько дальнобойщиков работает с поставщиком, а так-же сколько поставщиков работает с дальнобойщиком. Один поставщик может иметь множество дальнобойщиков, так-же один дальнобойщик может работать с множеством поставщиков. Такая связь называется многие ко многим. Реляционная модель базы данных не позволяет напрямую реализовать связь многие ко многие. Поэтому мы создадим еще 2 таблицы которые будут содержать поставленные грузы и купленные грузы:

• SellCargo
  • ID (ТИП: INT, INDEX: PRIMARY, UNIQUE)
  • IDSupplier (ТИП: INT)
  • Name (ТИП: VARCHAR)
• BuyCargo
  • ID (ТИП: INT, INDEX: PRIMARY, UNIQUE)
  • IDTrucker (ТИП: INT)
  • IDCargo (ТИП: INT)
  • Count (ТИП: INT)
  • Destination (ТИП: VARCHAR)

Теперь в поле IDSupplier таблицы SellCargo мы будем содержать поле ID таблицы Supplier нужного нам поставщика, а в поле IDTrucker таблицы BuyCargo мы будем содержать поле ID таблицы Trucker нужного нам дальнобойщика. Так-же в поле IDCargo таблицы BuyCargo мы будем содержать поле ID таблицы SellCargo. Таким образом создав промежуточные таблицы содержащие товары мы создали связь многие ко многие между таблицами Suplier и Trucker. В поле Count мы будем содержать количество груза, а в поле Destination место назначения груза.

Практика:

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

• Поставщик
• ФИО дальнобойщика

Выполним следующий запрос:

SELECT s.Name as 'Поставщик', t.FIO as 'ФИО дальнобойщика' FROM Suplier s INNER JOIN SellCargo sc ON sc.IDSupplier = s.ID INNER JOIN BuyCargo bc ON bc.IDCargo = sc.ID INNER JOIN Trucker t ON t.ID = bc.IDTrucker ORDER by s.Name

А теперь допустим нам необходимо найти с какими поставщиками работает определенный дальнобойщик. Допустим это будет дальнобойщик с полем ID = 1:

SELECT s.Name as 'Поставщик', t.FIO as 'ФИО дальнобойщика' FROM Suplier s INNER JOIN SellCargo sc ON sc.IDSupplier = s.ID INNER JOIN BuyCargo bc ON bc.IDCargo = sc.ID INNER JOIN Trucker t ON t.ID = bc.IDTrucker WHERE t.ID = 1

Или же узнаем с какими дальнобойщиками работает поставщик. Допустим это будет поставщик с полем ID = 3:

SELECT s.Name as 'Поставщик', t.FIO as 'ФИО дальнобойщика' FROM Suplier s INNER JOIN SellCargo sc ON sc.IDSupplier = s.ID INNER JOIN BuyCargo bc ON bc.IDCargo = sc.ID INNER JOIN Trucker t ON t.ID = bc.IDTrucker WHERE s.ID = 3

На этом все! Надеюсь вам был полезен мой урок, ведь правильное проектирование базы данных сэкономит вам кучу времени и строчек кода. ​