Как связать таблицы в sql c

Соединение таблиц

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

USE productsdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL ); CREATE TABLE Customers ( Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL ); CREATE TABLE Orders ( Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL );

Здесь таблицы Products и Customers связаны с таблицей Orders связью один ко многим. Таблица Orders в виде внешних ключей ProductId и CustomerId содержит ссылки на столбцы Id из соответственно таблиц Products и Customers. Также она хранит количество купленного товара (ProductCount) и и по какой цене он был куплен (Price). И кроме того, таблица также хранит в виде столбца CreatedAt дату покупки.

Пусть эти таблицы будут содержать следующие данные:

INSERT INTO Products VALUES ('iPhone 6', 'Apple', 2, 36000), ('iPhone 6S', 'Apple', 2, 41000), ('iPhone 7', 'Apple', 5, 52000), ('Galaxy S8', 'Samsung', 2, 46000), ('Galaxy S8 Plus', 'Samsung', 1, 56000), ('Mi 5X', 'Xiaomi', 2, 26000), ('OnePlus 5', 'OnePlus', 6, 38000) INSERT INTO Customers VALUES ('Tom'), ('Bob'),('Sam') INSERT INTO Orders VALUES ( (SELECT Id FROM Products WHERE ProductName='Galaxy S8'), (SELECT Id FROM Customers WHERE FirstName='Tom'), '2017-07-11', 2, (SELECT Price FROM Products WHERE ProductName='Galaxy S8') ), ( (SELECT Id FROM Products WHERE ProductName='iPhone 6S'), (SELECT Id FROM Customers WHERE FirstName='Tom'), '2017-07-13', 1, (SELECT Price FROM Products WHERE ProductName='iPhone 6S') ), ( (SELECT Id FROM Products WHERE ProductName='iPhone 6S'), (SELECT Id FROM Customers WHERE FirstName='Bob'), '2017-07-11', 1, (SELECT Price FROM Products WHERE ProductName='iPhone 6S') )

Теперь соединим две таблицы Orders и Customers:

SELECT * FROM Orders, Customers

При такой выборке для каждой строки из таблицы Orders будет совмещаться с каждой строкой из таблицы Customers. То есть, получится перекрестное соединение. Например, в Orders три строки, а в Customers то же три строки, значит мы получим 3 * 3 = 9 строк:

То есть в данном случае мы получаем прямое (декартово) произведение двух групп. Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из Orders связан с конкретным покупателем из Customers, а не со всеми возможными покупателями.

Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле CustomerId из Orders соответствует полю Id из Customers:

SELECT * FROM Orders, Customers WHERE Orders.CustomerId = Customers.Id

Теперь объединим данные по трем таблицам Orders, Customers и Products. То есть получим все заказы и добавим информацию по клиенту и связанному товару:

SELECT Customers.FirstName, Products.ProductName, Orders.CreatedAt FROM Orders, Customers, Products WHERE Orders.CustomerId = Customers.Id AND Orders.ProductId=Products.Id

Поскольку надо соединить три таблицы, то применяются как минимум два условия. Ключевой таблицей остается Orders, из которой извлекаются все заказы, а затем к ней подсоединяется данные по клиенту по условию Orders.CustomerId = Customers.Id и данные по товару по условию Orders.ProductId=Products.Id

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

SELECT C.FirstName, P.ProductName, O.CreatedAt FROM Orders AS O, Customers AS C, Products AS P WHERE O.CustomerId = C.Id AND O.ProductId=P.Id

Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:

SELECT C.FirstName, P.ProductName, O.* FROM Orders AS O, Customers AS C, Products AS P WHERE O.CustomerId = C.Id AND O.ProductId=P.Id

Объединение таблиц с помощью операторов Join и Keep

Объединение — операция объединения двух таблиц в одну. Записи результирующей таблицы представляют собой комбинации записей в исходных таблицах. При этом две такие записи, составляющие одну комбинацию в результирующей таблице, как правило, имеют общее значение одного или нескольких общих полей. Такое объединение называется естественным. В программе Qlik Sense объединение может выполняться в скрипте, создавая логическую таблицу.

Таблицы, которые находятся в скрипте, можно объединять. Логика Qlik Sense будет распознавать не отдельные таблицы, а результаты объединения, которые будут представлены в одной внутренней таблице. В некоторых случаях это требуется, однако существуют недостатки:

• Загруженные таблицы часто становятся больше, и программа Qlik Sense работает медленнее.
• Некоторая информация может быть потеряна: частота (количество записей) в исходной таблице может быть больше недоступна.

Функция Keep , которая позволяет уменьшить одну или обе таблицы до пересечения данных таблиц перед сохранением таблиц в программу Qlik Sense , предназначена для уменьшения количества случаев, когда необходимо использовать явные объединения.

Примечание к информации В данном руководстве термин «объединение» обычно используется для объединений, выполненных до создания внутренних таблиц. Однако ассоциация, выполненная после создания внутренних таблиц, по сути, также является объединением.

Объединения внутри оператора SQL SELECT

При использовании некоторых драйверов ODBC можно выполнять объединение внутри оператора SELECT . Это практически эквивалентно созданию объединения с помощью префикса Join .

Однако большинство драйверов ODBC не позволяют сделать полное внешнее объединение (двунаправленное). Они позволяют сделать только левостороннее или правостороннее внешнее объединение. Левостороннее (правостороннее) внешнее объединение включает только сочетания, в которых в левой (правой) таблице существует ключ объединения. Полное внешнее объединение включает все сочетания. Программа Qlik Sense автоматически создает полное внешнее объединение.

Более того, создание объединений в операторах SELECT значительно сложнее, чем создание объединений в программе Qlik Sense .

[Order Details].ProductID, [Order Details].

UnitPrice, Orders.OrderID, Orders.OrderDate, Orders.CustomerID

RIGHT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;

Этот оператор SELECT позволяет объединить таблицу, содержащую заказы несуществующей компании, и таблицу, содержащую сведения о заказах. Это правостороннее внешнее объединение, то есть будут включены все записи OrderDetails и записи со значением OrderID , которое отсутствует в таблице Orders . Однако заказы, содержащиеся в таблице Orders , но не содержащиеся в OrderDetails , не будут включены.

Join

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

LOAD a, b, c from table1.csv;

join LOAD a, d from table2.csv;

Результирующая внутренняя таблица имеет поля a , b , c и d . Количество записей различается в зависимости от значений полей этих двух таблиц.

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

Для получения дополнительной информации см. Join.

Keep

Явный префикс Join в скрипте загрузки данных выполняет полное объединение двух таблиц. В результате получается одна таблица. Во многих случаях такие объединения приводят к созданию очень больших таблиц. Одной из основных функций программы Qlik Sense является способность к связыванию таблиц вместо их объединения, что позволяет сократить использование памяти, повысить скорость обработки и гибкость. Функция keep предназначена для сокращения числа случаев необходимого использования явных объединений.

Префикс Keep между двумя операторами LOAD или SELECT приводит к уменьшению одной или обеих таблиц до пересечения их данных перед сохранением таблиц в программе Qlik Sense . Перед префиксом Keep следует задать одно из ключевых слов: Inner , Left или Right . Выборка записей из таблицы осуществляется так же, как и при соответствующем объединении. Однако две таблицы не объединяются и сохраняются в программе Qlik Sense в виде двух отдельных именованных таблиц.

Для получения дополнительной информации см. Keep.

Inner

Перед префиксами Join и Keep в скрипте загрузки данных можно использовать префикс Inner .

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

Если этот префикс используется перед Keep , он указывает, что две таблицы следует уменьшить до области взаимного пересечения, прежде чем они смогут быть сохранены в программе Qlik Sense .

В этих таблицах используются исходные таблицы Table1 и Table2 :

Table 1

A	B
1	aa
2	cc
3	ee

Table2

A	C
1	xx
4	yy

Inner Join

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

SELECT * from Table1;

inner join SELECT * from Table2;

VTable

A	B	C
1	aa	xx

Inner Keep

Если вместо этого выполняется Inner Keep , таблиц все равно будет две. Две таблицы связаны посредством общего поля A .

SELECT * from Table1;

inner keep SELECT * from Table2;

VTab1

A	B
1	aa

VTab2

A	C
1	xx

Для получения дополнительной информации см. Inner.

Left

Перед префиксами Join и Keep в скрипте загрузки данных можно использовать префикс left .

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

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

В этих таблицах используются исходные таблицы Table1 и Table2 :

Table1

A	B
1	aa
2	cc
3	ee

Table2

A	C
1	xx
4	yy

Сначала выполняется Left Join в отношении таблиц, в результате чего образуется таблица VTable , содержащая все строки из таблицы Table1 , совмещенные с полями из совпадающих строк в таблице Table2 .

SELECT * from Table1;

left join SELECT * from Table2;

VTable

A	B	C
1	aa	xx
2	cc	—
3	ee	—

Если вместо этого выполняется Left Keep , таблиц все равно будет две. Две таблицы связаны посредством общего поля A .

SELECT * from Table1;

left keep SELECT * from Table2;

VTab1

A	B
1	aa
2	cc
3	ee

VTab2

A	C
1	xx

Для получения дополнительной информации см. Left.

Right

Перед префиксами Join и Keep в скрипте загрузки данных можно использовать префикс right .

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

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

В этих таблицах используются исходные таблицы Table1 и Table2 :

Table1

A	B
1	aa
2	cc
3	ee

Table2

A	C
1	xx
4	yy

Сначала выполняется Right Join в отношении таблиц, в результате чего образуется таблица VTable , содержащая все строки из таблицы Table2 , совмещенные с полями из совпадающих строк в таблице Table1 .

SELECT * from Table1;

right join SELECT * from Table2;

VTable

A	B	C
1	aa	xx
4	—	yy

Если вместо этого выполняется Right Keep , таблиц все равно будет две. Две таблицы связаны посредством общего поля A .

SELECT * from Table1;

right keep SELECT * from Table2;

VTab1

A	B
1	aa

VTab2

A	C
1	xx
4	yy

Для получения дополнительной информации см. Right.

Виды связей в базах данных

MySQL — это реляционная база данных. Это означает, что данные в базе могут быть распределены в нескольких таблицах, и связаны друг с другом с помощью отношений (relation). Отсюда и название — реляционные.

Связи между таблицами происходят с помощью ключей. К примеру, в созданной нами ранее таблице пользователей есть первичный ключ — поле id. Если мы захотим сделать таблицу со статьями и хранить в ней авторов этих статей, то мы можем добавить новый столбец author_id и хранить в нём id пользователей из таблицы users.

Это был лишь один из примеров. Всего же типов подобных связей может быть 3:

• один-к-одному;
• один-ко-многим;
• многие-ко-многим.

Давайте же рассмотрим пример каждой из этих связей.

• Тест на знание основ HTML
• Тест на знание основ PHP
• Тест на знание ООП в PHP

Один-к-одному

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

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

CREATE TABLE `profiles` ( `id` INT NOT NULL , `about` TEXT NULL , `vk_link` VARCHAR(255) NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Добавим для каждого пользователя профиль:

• Курс HTML для начинающих
• Курс PHP для начинающих
• Курс MySQL для начинающих
• Курс ООП в PHP

INSERT INTO profiles (id, about, vk_link) SELECT id, "Стрессоустойчивость, коммуникабельность", CONCAT("https://vk.com/id", id) FROM users;

Посмотрим на получившиеся профили:

SELECT * FROM profiles;

Теперь каждой записи из таблицы users соответствует только одна запись из таблицы users_profiles и наоборот.

INNER JOIN

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

SELECT столбцы FROM таблица1 INNER JOIN таблица2 ON условие_для_связи

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

SELECT * FROM users INNER JOIN profiles ON users.id = profiles.id;

Каждая строка из левой таблицы, сопоставляется с каждой строкой из правой таблицы, после этого проверяется условие.

Если мы хотим выбрать только некоторые столбцы, то после оператора SELECT нужно перед именем поля явно указать название таблицы, из которой оно берется:

SELECT users.id, users.name, profiles.vk_link FROM users INNER JOIN profiles ON users.id = profiles.id;

Алиасы

Согласитесь, в прошлом примере пришлось довольно много букв написать. Чтобы этого избежать, в запросах можно использовать алиасы для имён таблиц. Для этого после имени таблицы можно написать AS alias. Давайте для таблицы users зададим алиас — u, а для таблицы profiles — p. Эти алиасы теперь можно использовать в любой части запроса:

SELECT u.id, u.name, p.vk_link FROM users AS u INNER JOIN profiles as p ON u.id = p.id;

Заметьте, запрос сократился. Писать запрос с использованием алиаса быстрее.

Как уже говорилось выше, алиас можно использовать в любой части запроса, в том числе и в условии WHERE:

SELECT u.id, u.name, p.vk_link FROM users AS u INNER JOIN profiles as p ON u.id = p.id WHERE u.id=2;

Один-ко-многим

При такой связи одной записи в одной таблице соответствует несколько записей в другой. В начале этого урока мы рассмотрели как раз такой пример, когда говорили о добавлении в таблицу с новостями поля author_id. Таким образом, у каждой статьи есть один автор. В то же время у одного автора может быть несколько статей.
Давайте создадим таблицу для статей. Пусть в ней будет идентификатор статьи, её название, текст, и идентификатор автора.

CREATE TABLE `my_db`.`articles` ( `id` INT NOT NULL AUTO_INCREMENT , `author_id` INT NOT NULL , `name` TEXT NOT NULL , `text` TEXT NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Добавим несколько статей:

INSERT INTO `articles`(`author_id`, `name`, `text`) VALUES (1, "Пингвины научились летать", "Шокирующая новость поразила общественность!"); INSERT INTO `articles`(`author_id`, `name`, `text`) VALUES (1, "В городе N обнаружен зомби-вирус", "Шокирующая новость поразила общественность!"); INSERT INTO `articles`(`author_id`, `name`, `text`) VALUES (2, "Котики снижают уровень стресса", "Успокаивающая новость расслабила общественность");

Запросим теперь эти записи, чтобы убедиться, что всё ок

SELECT * FROM articles;

Давайте теперь выведем имена статей вместе с авторами. Для этого снова воспользуемся оператором INNER JOIN.

SELECT a.name, u.name FROM articles AS a INNER JOIN users AS u ON a.author_id=u.id;

Как видим, у Ивана две статьи, и ещё одна у Ольги.

Если бы мы захотели на странице со статьей выводить рядом с автором краткую информацию о нем, нам нужно было бы сделать ещё один JOIN на табличку profiles.

SELECT a.name, u.name, p.about FROM articles AS a INNER JOIN users AS u ON a.author_id=u.id INNER JOIN profiles AS p ON u.id=p.id;

LEFT JOIN

Помимо INNER JOIN, есть ещё несколько операторов класса JOIN. Один из самых частоиспользуемых — LEFT JOIN. Он позволяет сделать запрос к двум таблицам, между которыми есть связь, и при этом для одной из таблиц вернуть записи, даже если они не соответствуют записям в другой таблице.
Как например, если бы мы хотели вывести не только пользователей, у которых есть статьи, но и тех, кто «халтурит» 🙂

Давайте для начала сделаем запрос с использованием INNER JOIN, который выведет пользователей и написанные ими статьи:

SELECT u.id, u.name, a.name FROM users AS u INNER JOIN articles AS a ON u.id=a.author_id;

Теперь заменим INNER JOIN на LEFT JOIN:

SELECT u.id, u.name, a.name FROM users AS u LEFT JOIN articles AS a ON u.id=a.author_id;

Видите, вывелись записи из левой таблицы (users), которым не соответствует при этом ни одна запись из правой таблицы (articles).

Многие-ко-многим

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

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

CREATE TABLE `categories` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(255) NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

И сразу добавим в неё несколько рубрик.

INSERT INTO `categories`(`name`) VALUES ("Хорошие новости"); INSERT INTO `categories`(`name`) VALUES ("Плохие новости"); INSERT INTO `categories`(`name`) VALUES ("Новости о животных");

Проверим, что они добавились.

SELECT * FROM categories;

Теперь нам нужно добавить ещё одну таблицу, в которой будут храниться связи между article.id и category.id. Создаём:

CREATE TABLE `articles_categories` ( `article_id` INT NOT NULL , `category_id` INT NOT NULL , PRIMARY KEY (`article_id`, `category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Обратите внимание на составной первичный ключ. Здесь нам требуется, чтобы именно пара (id_статьи — id_рубрики) была уникальной. А сами по себе значения в отдельных колонок могут повторяться.

И давайте добавим нашу новость о котиках в категории:

• Новости о животных
• Хорошие новости

INSERT INTO `articles_categories`(`article_id`, `category_id`) VALUES (3, 1); INSERT INTO `articles_categories`(`article_id`, `category_id`) VALUES (3, 3);

Добавим также новость о вирусе в «Плохие новости».

INSERT INTO `articles_categories`(`article_id`, `category_id`) VALUES (2, 2);

а новость про пингвинах в «Новости о животных».

INSERT INTO `articles_categories`(`article_id`, `category_id`) VALUES (1, 3);

Посмотрим что у нас получилось:

SELECT * FROM articles_categories;

Теперь давайте выведем рубрики новости о котиках:

SELECT c.name FROM categories AS c INNER JOIN articles_categories AS ac ON ac.category_id=c.id INNER JOIN articles AS a ON a.id=ac.article_id WHERE a.name="Котики снижают уровень стресса";

Таким образом реализуется связь многие-ко-многим.

Разбираем базы данных и язык 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 и слова не скажет. Вот только если подумать, что президент к тому же еще и человек. И еще у него может быть жена (еще одна связь один-к-одному) и дети (еще одна связь один-ко-многим) и тогда получается, что это уже нужно будет страну связывать с женой и детьми президента…. Звучит бредово, да? 😀 Примеров других может быть множество и для этой связи. Причем в такой ситуации можно добавлять внешний ключ в обе таблицы, в отличие от связи 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); И все будет хорошо до момента, пока мы не захотим повторить еще раз последнюю команду: то есть, записать еще раз одни и те же айдишники:Результат будем закономерный — ошибка. Будет дубликат. Запись не будет записана. Вот так будет создана многие ко многим связь… Все это очень круто и интересно, но напрашивается закономерный вопрос: а как эту информацию получить? Как соединить данные из разных таблиц воедино и получить один ответ? Вот об этом мы и поговорим в следующей части))

Соединения (Джоины)

В предыдущей части я готовил вас к тому, чтобы сразу было понятно, что такое джоины и где их использовать. Потому что я глубоко убежден, что как только придет понимание, сразу станет все очень просто, и все статьи о джоинах будут ясными, как очи младенца 😀 Грубо и в общем, джоины — это получение результата из нескольких таблиц путем СОЕДИНЕНИЯ (джоина из английского 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’, например ‘Sid oro v’, ‘V oro novsky’.
6. Выбрать из таблицы ‘Student’ все фамилии (‘last_name’) и количество их повторений. Считать, что в базе есть однофамильцы. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

last_name	quantity
Petrov	15
Ivanov	12
Sidorov	3

name	quantity
Alexander	27
Sergey	10
Peter	7

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

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

Teacher’s last_name	Book’s quantity
Petrov	11
Sidorov	9
Ivanov	7

last_name	type
Ivanov	student
Kankava	teacher
Smith	student
Sidorov	teacher
Petrov	teacher

Вывод

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