Розбираємо бази даних та мову SQL. (Частина 5 - зв'язки та джоїни) - "Java-проект від А до Я"

Стаття із серії про створення Java-проекту (посилання на інші матеріали – наприкінці). Її мета — аналіз ключових технологій, результат — написання телеграм-бота.

• Типи зв'язків у БД
  • Один до багатьох (one-to-many)
  • Один до одного (one-to-one)
  • Багато хто до багатьох (many-to-many)
• З'єднання (Джоїни)
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • Закріплюємо Джоїни
• Домашнє завдання

Всім привіт, майбутні Сеньйори та Сеньйорити програмного забезпечення. Як я вже говорив у попередній частині ( перевірка домашнього завдання ), сьогодні буде новий матеріал. Для особливо спраглих я накопав цікаве домашнє завдання, щоб ті, хто вже все знає і ті, хто не знає, але хоче нагуглити, могли вправлятися та перевірити своє вміння. Сьогодні говоритимемо про типи зв'язків та джоїни.

Типи зв'язків у БД

Щоб зрозуміти, що таке зв'язки, треба згадати, що таке зовнішній ключ. Хто забув - велкам на початок серії .

Один до багатьох (one-to-many)

Згадаймо наш приклад із країнами та містами. Зрозуміло, що місто має мати країну. А як прив'язати країну до міста? Потрібно до кожного міста прикріпити унікальний ідентифікатор (ID) країни, до якої належить: ми вже це робабо. Це і називається одним із типів зв'язків — один до багатьох (ще добре б знати англійську версію —one-to-many). Перефразовуючи, можна сказати: до однієї країни може належати кілька міст. Так і слід запам'ятовувати це: зв'язок один до багатьох. Поки що зрозуміло, так? Якщо не дуже, то перша картинка з інтернетів: Тут показано, що є замовники та їх замовлення. Адже розумно, що в одного замовника може бути більше одного замовлення. У наявності one-to-many :) Або інший приклад:Є три таблиці: видавець, автор та книга. У кожного видавця, який не хоче збанкрутувати і прагне бути успішним, є більше одного автора, чи згодні? У свою чергу, кожен автор має більше однієї книги — тут теж сумнівів бути не може. А це означає, знову-таки, зв'язок один автор до багатьох книг, один видавець до багатьох авторів . Прикладів можна ще навести безліч. Складність у сприйнятті спочатку може полягати лише в тому, щоб навчитися абстрактно мислити: дивитися з боку на таблиці та їхню взаємодію.

Один до одного (one-to-one)

Це, можна сказати, окремий випадок зв'язку одним-багатьом. Ситуація, в якій один запис в одній таблиці пов'язаний лише з одним записом в іншій таблиці. Які можуть бути приклади із життя? Якщо виключити багатоженство, можна сказати, що є зв'язок друг до друга між чоловіком і дружиною. Хоча якщо навіть сказати, що багатоженство дозволено, то все одно кожна дружина може мати лише одного чоловіка. Так само можна сказати про батьків. У кожної людини може бути лише один біологічний батько та лише одна біологічна мати. Явний зв'язок один до одного. Поки писав це, спала на думку думка: а навіщо тоді розділяти зв'язок один на одного на два записи в різних таблицях, якщо у них і так зв'язок однозначний? Сама і відповідь вигадала. Ці записи можуть бути пов'язані з іншими записами в інших зв'язках. Про що я? Ще один приклад із зв'язків один одному — це країна і президент. Чи можна ж записати в таблиці “країна” всі дані про президента? Так можна SQL і слова не скаже. Ось тільки якщо подумати, що президент до того ж ще й людина... І ще в нього може бути дружина (ще один зв'язок один до одного) і діти (ще один зв'язок один-до-багатьом) і тоді виходить, що це вже потрібно буде країну пов'язувати із дружиною та дітьми президента…. Звучить марно, так? :D Прикладів інших може бути безліч і для цього. Причому в такій ситуації можна додавати зовнішній ключ в обидві таблиці, на відміну від one-to-many. І ще в нього може бути дружина (ще один зв'язок один до одного) і діти (ще один зв'язок одним-багатьом) і тоді виходить, що це вже потрібно буде країну пов'язувати з дружиною і дітьми президента. Звучить марно, так? :D Прикладів інших може бути безліч і для цього. Причому в такій ситуації можна додавати зовнішній ключ в обидві таблиці, на відміну від one-to-many. І ще в нього може бути дружина (ще один зв'язок один до одного) і діти (ще один зв'язок одним-багатьом) і тоді виходить, що це вже потрібно буде країну пов'язувати з дружиною і дітьми президента. Звучить марно, так? :D Прикладів інших може бути безліч і для цього. Причому в такій ситуації можна додавати зовнішній ключ в обидві таблиці, на відміну від one-to-many.

Багато хто до багатьох (many-to-many)

Вже з назви можна здогадатися, про що йтиметься. Найчастіше в житті, а ми програмуємо наше життя, бувають ситуації, коли не вистачає перелічених вище типів зв'язків для опису потрібних нам речей. Ми вже говорабо про видавців, книги та авторів. Тут просто так і прі зв'язками… У кожного видання може бути кілька авторів — зв'язок один до багатьох. Водночас у кожного автора може бути кілька видавців (чому ні, видавався письменник в одному місці, посварився через гроші, пішов до іншого видавництва, наприклад). І це знову зв'язок один до багатьох. Або так: у кожного автора може бути кілька книг, але й у кожної книги може бути кілька авторів. Знову зв'язок один до багатьох між автором та книгою, книгою та автором. З цього прикладу можна зробити формалізованіший висновок: Як задавати в SQL попередніх типах зв'язку було зрозуміло: просто передаємо ID-шник того, що один у ті записи, яких багато, так? Одна країна дає свій ID-шник як зовнішній ключ до багатьох міст. А що робити зі зв'язком багато хто до багатьох ? Такий спосіб не підходить. Потрібно додати ще одну таблицю, яка б пов'язувала дві таблиці. Наприклад, заходимо в MySQL, створюємо нову БД manytomany, створюємо дві таблиці, author і book в яких будуть тільки імена та їх ID-шники: CREATE DATABASE manytomany; USE багатотомани; CREATE TABLE author( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) );Тепер створимо третю таблицю, яка матиме два зовнішні ключі з наших таблиць author і book, і ця зв'язка буде унікальною. Тобто, не можна буде додати запис з одними й тими ж ключами два рази: CREATE TABLE ), UNIQUE (book_id, author_id)); Тут ми використали кілька нових фішок, які потрібно прокоментувати окремо.

• NOT NULL означає, що поле завжди має бути заповнене, і якщо ми цього не зробимо, SQL скаже нам про це;
• UNIQUE говорить про те, що поле або зв'язування полів повинні бути унікальними в таблиці. Часто буває так, що, крім унікального ідентифікатора, унікальним для кожного запису має бути ще одне поле. І UNIQUE відповідає саме за цю справу.

З моєї практики: при переході зі старої системи на нову ми як розробники повинні зберігати ID-шники старої системи для роботи з нею і створити свої власні. Чому свої створити, а не використати старі? Вони можуть бути недостатньо унікальні, або такий підхід у створенні ID-шників вже не є актуальним і обмеженим. Для цього ми зробабо і старий ID-шник теж унікальним у таблиці. Щоб це перевірити, потрібно додати дані. Додамо книгу та автора: NSERT INTO book (name) VALUES ("book1"); INSERT INTO author (name) VALUES ("author1"); Ми вже знаємо з попередніх статей, що у них будуть ID-шники 1 та 1. Тому можемо одразу додати запис до третьої таблиці: INSERT INTO authors_x_books VALUES (1,1); І все буде добре до моменту, поки ми не захочемо повторити ще раз останню команду: тобто записати ще раз одні й ті самі айдішники: « Результат будемо закономірний — помилка». Буде дублікат. Запис не буде записано. Ось так буде створено багато хто до багатьох зв'язків… Все це дуже круто та цікаво, але напрошується закономірне питання: а як цю інформацію отримати? Як поєднати дані з різних таблиць воєдино та отримати одну відповідь? Ось про це ми і поговоримо у наступній частині))

З'єднання (Джоїни)

У попередній частині я готував вас до того, щоб одразу було зрозуміло, що таке джоїни і де їх використовувати. Тому що я глибоко переконаний, що як тільки прийде розуміння, відразу стане все дуже просто, і всі статті про джоїни будуть ясними, як очі немовляти :D Грубо і загалом, джоїни - це отримання результату з кількох таблиць шляхом поєднання (джоїну з англійської) join). І все…) А щоб з'єднати, потрібно вказати поле, яким будуть з'єднуватися таблиці. Не так страшний чорт, як його малюють, так?) Далі просто поговоримо про те, які джоїни бувають і як їх використовувати. Типів джоїнів багато, і всі ми не розглядатимемо. Лише ті, які нам реально потрібні. Тому такі екзотичні джоїни як Cross та Natural нам не цікаві. Зовсім забув, нам слід запам'ятати ще один нюанс: у таблиць та полів можуть бути аліаси- псевдоніми. Вони зручно використовуються для джоїнів. Наприклад, можна зробити так: SELECT * FROM table1; якщо у запиті часто використовуватиметься table1, можна йому дати псевдонім: SELECT* FROM table1 as t1; або ще простіше написати: SELECT * FROM table1 t1; і тоді далі у запиті можна буде використовувати t1 як псевдонім цієї таблиці.

INNER JOIN

Найпоширеніший і найпростіший джоїн. Він говорить про те, що коли ми маємо дві таблиці та поле, за яким його можна з'єднати, будуть обрані всі записи, зв'язки яких існують у двох таблицях. Складно сказав якось. Подивимося на прикладі: Додамо до нашої БД cities по одному запису. Один запис до міст і один — до країн: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); і $ INSERT INTO city (name, population) VALUES ("Tbilisi", 1171100); Ми додали країну, яка не має міста в нашій таблиці, і місто, яке не прив'язане до країни в нашій таблиці. Так ось INNER JOIN займається тим, що видає всі записи на ті з'єднання, які є в двох таблицях. Ось як виглядає загальний синтаксис, коли ми хочемо поєднати дві таблиці table1 і table2:INNER JOIN table2 ON t1.id = t2.t1_id; і тоді буде видано всі записи, які мають зв'язок у двох таблицях. Для нашого випадку, коли ми хочемо отримати разом з містами ще й інформацію для країн, вийде так: $ SELECT * FROM city ci Тут хоч імена і збігаються, але можна чітко побачити, що йдуть спочатку поля міст, потім поля країн. А тих двох записів, які ми додали вище, там нема. Бо INNER JOIN саме так і працює.

LEFT JOIN

Бувають випадки, і досить часто, коли нас не влаштовує втрата полів головної таблиці через те, що до неї немає запису в суміжній таблиці. Для цієї справи потрібний LEFT JOIN. Якщо ми в нашому попередньому запиті вкажемо замість INNER - LEFT, у нас у відповіді додасться ще одне місто - Tbilisi: $ SELECT * Новий запис про Тбілісі є і все, що стосується країни, там стоїть у null . Найчастіше це так і використовується.

RIGHT JOIN

Тут буде відмінність від LEFT JOIN у тому, що вибиратися всі поля будуть не ліворуч, а праворуч у з'єднанні. Тобто, будуть взяті не міста, а всі країни: $ SELECT * FROM city ci Тепер видно, що в цьому випадку Тбілісі не буде, натомість у нас буде Узбекистан. От якось так…))

Закріплюємо Джоїни

Тепер я хочу показати вам типову картинку, яку зубрять джуни перед співбесідою, щоб переконати, що вони розуміють суть джоїнів: Тут все показано у вигляді множин, кожне коло – це таблиця. А ті місця, де зафарбовано – це ті частини, які будуть показані у SELECT. Дивимося:

• INNER JOIN - це лише перетин множин, тобто ті записи, які мають зв'язки на дві таблиці - А і В;
• LEFT JOIN - це всі записи з таблиці A, включаючи всі записи з таблиці, які мають перетин (зв'язок) з А;
• RIGHT JOIN - це з точністю до навпаки до LEFT JOIN - всі записи в таблиці і записи з А, які мають зв'язок.

Після всього цього ця картинка має бути зрозумілою))

Домашнє завдання

Цього разу завдання будуть дуже цікаві і всі ті, хто успішно їх вирішить, може не сумніватися, що готовий до початку роботи з боку SQL! Завдання не розжовані і написані були для мідлів, так що легко і нудно не буде вам :) Я дам вам тиждень на те, щоб зробити завдання самому, і потім випущу окрему статтю з детальним розбором вирішення тих завдань, які я вам дав.

Власне завдання:

1. Написати SQL script створення таблиці 'Student' з полями: id (primary key), name, last_name, e_mail (unique).
2. Написати SQL script створення таблиці 'Book' із полями: id, title (id + title = primary key). Зв'язати 'Student' та 'Book' зв'язком 'Student' one-to-many 'Book'.
3. Написати SQL script створення таблиці 'Teacher' із полями: id (primary key), name, last_name, e_mail (unique), subject.
4. Зв'язати 'Student' і 'Teacher' зв'язком 'Student' many-to-many Teacher'.
5. Вибрати 'Student' у яких у прізвищі є 'oro', наприклад 'Sid oro v', 'V oro novsky'.
6. Вибрати з таблиці 'Student' всі прізвища ('last_name') та кількість їх повторень. Вважати, що у базі є однофамільці. Відсортувати за кількістю у порядку зменшення. Виглядати має так:

   last_name	quantity
   Петроов	15
   Ivanov	12
   Sidorov	3

7. Вибрати з 'Student' топ 3 імен 'name', що найбільш повторюються. Відсортувати за кількістю у порядку зменшення. Виглядати має так:

   name	quantity
   Олександр	27
   Sergey	10
   Peter	7

8. Вибрати 'Student', у яких найбільша кількість 'Book' і пов'язаних з ним 'Teacher'. Відсортувати за кількістю в порядку зменшення. Виглядати має так:

   Teacher's last_name	Student's last_name	Book's quantity
   Петроов	Sidorov	7
   Ivanov	Smith	5
   Петроов	Kankava	2>

9. Вибрати 'Teacher', у яких найбільша кількість 'Book' у всіх його 'Student'. Відсортувати за кількістю у порядку зменшення. Виглядати має так:

   Teacher's last_name	Book's quantity
   Петроов	9
   Ivanov	5

10. Вибрати 'Teacher' у яких кількість 'Book' у всіх його 'Student' знаходиться між 7-ю та 11-ю. Відсортувати за кількістю у порядку зменшення. Виглядати має так:

    Teacher's last_name	Book's quantity
    Петроов	11
    Sidorov	9
    Ivanov	7

11. Вивести всіх 'last_name' та 'name' всіх 'Teacher' та 'Student' з полем 'type' (student або teacher). Сортувати за алфавітом за 'last_name'. Виглядати має так:

    last_name	type
    Ivanov	student
    Kankava	teacher
    Smith	student
    Sidorov	teacher
    Петроов	teacher

12. Додати до існуючої таблиці 'Student' колонку 'rate', в якій зберігатиметься курс, на якому студент зараз знаходиться (числове значення від 1 до 6).
13. Цей пункт не є обов'язковим до виконання, але буде плюсом. Написати функцію, яка пройдеться по всіх 'Book', і виведе через кому все 'title'.

Висновок

Дещо затягнулася серія про БД. Згоден. Тим не менш, ми пройшли великий шлях і в результаті виходимо зі знанням справи! Всім дякую за прочитання, нагадую, що всі хто хоче йти далі і стежити за проектом, потрібно створити обліковий запис на GitHub і підписатися на мій обліковий запис :) Далі більше — поговоримо про мавен і докер. Дякую всім за прочитання. Повторю ще раз: дорогу здолає той, хто йде ;)

Список всіх матеріалів серії на початку цієї статті.