Разбираем базы данных и язык 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.

Многие ко многим (many-to-many)

Уже исходя из названия можно догадаться, о чем пойдет речь. Зачастую в жизни, а мы программируем нашу жизнь, бывают ситуации, когда не хватает вышеперечисленных типов связей для описания нужных нам вещей. Мы уже говорили об издателях, книгах и авторах. Здесь просто так и прёт связями… У каждого издания может быть несколько авторов — связь один ко многим. В тоже время у каждого автора может быть несколько издателей (почему нет, издавался писатель в одной месте, поругался из-за денег, ушел в другое издательство, например). И это опять связь один ко многим. Или так: у каждого автора может быть несколько книг, но и у каждой книги может быть несколько авторов. Опять связь один ко многим между автором и книгой, книгой и автором. Из этого примера можно сделать более формализованный вывод:Как задавать в SQL предыдущих типах связи было понятно: просто передаем ID-шник того, что один в те записи, которых много, да? Одна страна дает свой ID-шник как внешний ключ ко многим городам. А что делать со связью многие ко многим? Такой способ не подходит. Нужно добавить еще одну таблицу, которая связывала бы две таблицы. Например, заходим в MySQL, создаем новую БД manytomany, создаем две таблицы, author и book в которых будут только имена и их ID-шники: CREATE DATABASE manytomany; USE manytomany; 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 authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author(id), 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: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; и тогда будут выданы все записи, которые имеют связь в двух таблицах. Для нашего случая, когда мы хотим получить вместе с городами еще и информацию для стран, получится так: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id;Здесь хоть имена и совпадают, но можно отчетливо увидеть, что идут вначале поля городов, потом поля стран. А тех двух записей, которые мы добавили выше, там нет. Потому что INNER JOIN именно так и работает.

LEFT JOIN

Бывают случаи, и довольно-таки часто, когда нас не устраивает потеря полей главной таблицы из-за того, что к ней нет записи в смежной таблице. Для этого дела и нужен LEFT JOIN. Если мы в нашем предыдущем запросе укажем вместо INNER — LEFT, у нас в ответе добавится еще один город — Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id;Новая запись про Тбилиси есть и все, что относится к стране, там стоит в null. Зачастую это так и используется.

RIGHT JOIN

Здесь будет отличие от LEFT JOIN в том, что выбираться все поля будут не слева, а справа в соединении. То есть, будут взяты не города, а все страны: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id;Теперь видно, что в этом случае Тбилиси не будет, зато будет у нас Узбекистан. Вот как-то так…))

Закрепляем Джоины

Теперь я хочу показать вам типичную картинку, которую зубрят джуны перед собеседованием, чтобы убедить, что они понимают суть джоинов:Здесь все показано в виде множеств, каждый круг — это таблица. А те места, где закрашено — это те части, которые будут показаны в 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’, например ‘Sidorov’, ‘Voronovsky’.
6. Выбрать из таблицы ‘Student’ все фамилии (‘last_name’) и количество их повторений. Считать, что в базе есть однофамильцы. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

   last_name	quantity
   Petrov	15
   Ivanov	12
   Sidorov	3

7. Выбрать из ‘Student’ топ 3 самых повторяющихся имен ‘name’. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

   name	quantity
   Alexander	27
   Sergey	10
   Peter	7

8. Выбрать ‘Student’, у которых самое большое количество ‘Book’ и связанных с ним ‘Teacher’.Отсортировать по количеству в порядке убывания. Выглядеть должно так:

   Teacher’s last_name	Student’s last_name	Book’s quantity
   Petrov	Sidorov	7
   Ivanov	Smith	5
   Petrov	Kankava	2>

9. Выбрать ‘Teacher’, у которых самое большое количество ‘Book’ у всех его ‘Student’. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

   Teacher’s last_name	Book’s quantity
   Petrov	9
   Ivanov	5

10. Выбрать ‘Teacher’ у которых количество ‘Book’ у всех его ‘Student’ находится между 7-ю и 11-и. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

    Teacher’s last_name	Book’s quantity
    Petrov	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
    Petrov	teacher

12. Добавить к существующей таблице ‘Student’ колонку ‘rate’, в которой будет храниться курс, на котором студент сейчас находится (числовое значение от 1 до 6).
13. Этот пункт не обязателен к выполнению, но будет плюсом. Написать функцию, которая пройдется по всем ‘Book’, и выведет через запятую все ‘title’.

Вывод

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

Список всех материалов серии в начале этой статьи.