MySQL / 12. Использование нескольких таблиц

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

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

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

Обсуждение
Использованные в предыдущих разделах таблицы artist и painting имеют связь «один-ко-многим»: определенный художник мог написать много картин, но каждая картина создана ровно одним автором. Связь «один-ко-многим» достаточно проста, и две таблицы могут быть связаны при помощи общего ключа (common key).

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

mysql> SELECT name, abbrev FROM states;

+----------+---------+
| name      | abbrev |
+----------+---------+
| Alabama | AL        |
| Alaska     | AK       |
| Arizona   | AZ       |
| Arkansas | AR      |
...

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

mysql> SELECT title, state FROM painting ORDER BY state;

+---------------------+-------+
| title                       | state |
+---------------------+-------+
| The Rocks             | IA     |
| The Last Supper    | IN    |
| Starry Night           | KY    |
| The Potato Eaters | KY    |
| The Mona Lisa       | MI     |
| Les Deux Soeurs    | NE    |
+----------------------+-------+

Если вы хотите видеть полные названия штатов, а не их аббревиатуры, то можете использовать связь «один-к-одному», существующую между столбцами таблицы states. Объедините эту таблицу с painting, используя общие для двух таблиц значения аббревиатур:

mysql> SELECT painting.title, states.name AS state
-> FROM painting, states
-> WHERE painting.state = states.abbrev
-> ORDER BY state;

+---------------------+-----------+
| title                       | state       |
+---------------------+-----------+
| The Last Supper   | Indiana    |
| The Rocks             | Iowa        |
| Starry Night          | Kentucky  |
| The Potato Eaters | Kentucky |
| The Mona Lisa       | Michigan   |
| Les Deux Soeurs   | Nebraska |
+---------------------+------------+

Более сложной связью между таблицами является связь «многие-ко-многим», которая имеет место, если записи одной таблицы может соответствовать несколько записей второй, и наоборот. Обычно в книгах по базам данных такая связь иллюстрируется задачей «о деталях и поставщиках» («parts and suppliers»). (Указанную деталь можно получить от разных поставщиков.

Как тогда составить список, показывающий, какие детали приходят от каких поставщиков?) Но я уже столько раз видел этот пример, что хочу привести другой. И хотя общая идея остается той же, давайте все же рассмотрим мой пример. Вы с приятелями – страстные поклонники юкера (euchre – карточная игра для четверых, в которую играют парами). Каждый год вы собираетесь вместе, делитесь на пары и организуете товарищеский турнир. Естественно, чтобы избежать споров об итогах каждого матча, вы записываете состав пар и результаты в базу данных. Можно было бы хранить результаты в таблице, куда каждый год вы записываете названия команд-участниковтурнира, количества побед и поражений, имена игроков и место их проживания:

mysql> SELECT * FROM euchre ORDER BY year, wins DESC, player;

+----------+------+------+-------+--------+--------------+
| team      | year | wins | losses | player | player_city |
+----------+------+------+-------+--------+--------------+
| Kings      | 2001 | 10   | 2        | Ben      | Cork           |
| Kings      | 2001 | 10   | 2        | Billy      | York            |
| Crowns   | 2001 | 7    | 5        | Melvin  | Dublin         |
| Crowns   | 2001 | 7    | 5        | Tony     | Derry          |
| Stars       | 2001 | 4    | 8       | Franklin | Bath           |
| Stars       | 2001 | 4    | 8       | Wallace | Cardiff        |
| Sceptres | 2001 | 3    | 9       | Maurice | Leeds          |
| Sceptres | 2001 | 3    | 9       | Nigel     | London        |
| Crowns   | 2002 | 9    | 3       | Ben       | Cork            |
| Crowns   | 2002 | 9    | 3       | Tony     | Derry           |
| Kings       | 2002 | 8    | 4      | Franklin | Bath            |
| Kings       | 2002 | 8    | 4      | Nigel      | London        |
| Stars       | 2002 | 5    | 7      | Maurice | Leeds           |
| Stars       | 2002 | 5    | 7      | Melvin   | Dublin           |
| Sceptres | 2002 | 2    | 10    | Billy       | York              |
| Sceptres | 2002 | 2    | 10    | Wallace | Cardiff          |
+----------+------+-----+------+----------+---------------+

Из таблицы видно, что в каждой команде несколько участников и каждый игрок выступал в нескольких командах. В таблице зафиксирована связь «многие-ко-многим», но она не приведена к нормальной форме. Каждая строка хранит избыточную информацию. (Данные о каждой команде записаны несколько раз, как и сведения о каждом игроке.) Лучше представить эту связь «многие-ко-многим» так:

• Хранить название каждой команды, год и победы-поражения единожды в таблице euchre_team.

• Хранить имя каждого игрока и город его проживания единожды в таблице euchre_player.

• Создать третью таблицу, euchre_link, в которой будут храниться соответствия игроков и команд и которая будет служить связкой (link) или мостом (bridge) между двумя исходными таблицами. Чтобы минимизировать информацию, хранящуюся в этой таблице, присвоим уникальный идентификатор каждой команде и каждому игроку в соответствующих им таблицах, и будем хранить в таблице euchre_link только такие идентификаторы.

Таблицы игроков и команд будут выглядеть так:

mysql> SELECT * FROM euchre_team;

+--+-----------+------+------+--------+
| id | name      | year  | wins | losses |
+--+-----------+------+------+--------+
| 1  | Kings       | 2001 | 10   | 2         |
| 2  | Crowns   | 2001 | 7     | 5         |
| 3  | Stars       | 2001 | 4     | 8         |
| 4  | Sceptres | 2001 | 3     | 9         |
| 5  | Kings       | 2002 | 8     | 4         |
| 6  | Crowns   | 2002 | 9     | 3         |
| 7  | Stars       | 2002 | 5     | 7         |
| 8  | Sceptres | 2002 | 2     | 10       |
+--+-----------+------+------+--------+

mysql> SELECT * FROM euchre_player;

+--+----------+---------+
| id | name     | city       |
+--+----------+---------+
| 1  | Ben       | Cork      |
| 2  | Billy       | York      |
| 3  | Tony      | Derry    |
| 4  | Melvin   | Dublin    |
| 5  | Franklin | Bath      |
| 6  | Wallace | Cardiff  |
| 7  | Nigel      | London |
| 8  | Maurice | Leeds    |
+--+----------+---------+

Таблица euchre_link сопоставляет игроков и команды:

mysql> SELECT * FROM euchre_link;

+----------+-----------+
| team_id | player_id |
+----------+-----------+
| 1            | 1             |
| 1            | 2             |
| 2            | 3             |
| 2            | 4             |
| 3            | 5             |
| 3            | 6             |
| 4            | 7             |
| 4            | 8             |
| 5            | 5             |
| 5            | 7             |
| 6            | 1             |
| 6            | 3             |
| 7            | 4             |
| 7            | 8             |
| 8            | 2             |
| 8            | 6             |
+----------+-----------+

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

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

mysql> SELECT t.name, t.year, t.wins, t.losses, p.name, p.city
-> FROM euchre_team AS t, euchre_link AS l, euchre_player AS p
-> WHERE t.id = l.team_id AND p.id = l.player_id
-> ORDER BY t.year, t.wins DESC, p.name;

+----------+------+------+--------+----------+---------+
| name      | year  | wins | losses | name     | city      |
+----------+------+------+--------+----------+---------+
| Kings       | 2001 | 10    | 2        | Ben        | Cork     |
| Kings       | 2001 | 10    | 2        | Billy        | York     |
| Crowns   | 2001 | 7      | 5         | Melvin   | Dublin   |
| Crowns   | 2001 | 7      | 5         | Tony      | Derry    |
| Stars       | 2001 | 4      | 8         | Franklin | Bath     |
| Stars       | 2001 | 4      | 8         | Wallace | Cardiff |
| Sceptres | 2001 | 3      | 9         | Maurice | Leeds    |
| Sceptres | 2001 | 3      | 9         | Nigel      | London |
| Crowns   | 2002 | 9      | 3         | Ben        | Cork     |
| Crowns   | 2002 | 9      | 3         | Tony      | Derry    |
| Kings       | 2002 | 8      | 4         | Franklin | Bath     |
| Kings       | 2002 | 8      | 4         | Nigel      | London |
| Stars       | 2002 | 5      | 7         | Maurice | Leeds    |
| Stars       | 2002 | 5      | 7         | Melvin    | Dublin   |
| Sceptres | 2002 | 2      | 10        | Billy       | York      |
| Sceptres | 2002 | 2      | 10        | Wallace | Cardiff |
+----------+------+-------+--------+----------+---------+

• Выведем членов определенной команды (2001 год, Crowns):

mysql> SELECT p.name, p.city
-> FROM euchre_team AS t, euchre_link AS l, euchre_player AS p
-> WHERE t.id = l.team_id AND p.id = l.player_id
-> AND t.name = 'Crowns' AND t.year = 2001;

+-------+--------+
| name  | city     |
+-------+--------+
| Tony   | Derry  |
| Melvin | Dublin |
+-------+--------+

• Выведем команды, за которые выступал определенный игрок (Billy):

mysql> SELECT t.name, t.year, t.wins, t.losses
-> FROM euchre_team AS t, euchre_link AS l, euchre_player AS p
-> WHERE t.id = l.team_id AND p.id = l.player_id
-> AND p.name = 'Billy';

+----------+-------+------+--------+
| name      | year  | wins | losses |
+----------+-------+------+--------+
| Kings       | 2001 | 10    | 2         |
| Sceptres | 2002  | 2     | 10       |
+----------+-------+------+--------+

Обратите внимание на то, что для ответа на вопросы о связях «многие-комногим» необходимо использовать трехстороннее соединение, но это не означает, что трехстороннее соединение само по себе подразумевает связь «многие-ко-многим». Ранее в разделе мы объединяли таблицу states с таблицей painting для сопоставления аббревиатурам названий штатов их полных названий:

mysql> SELECT painting.title, states.name AS state
-> FROM painting, states
-> WHERE painting.state = states.abbrev
-> ORDER BY state;

+---------------------+-----------+
| title                       | state       |
+---------------------+-----------+
| The Last Supper   | Indiana    |
| The Rocks             | Iowa        |
| Starry Night          | Kentucky |
| The Potato Eaters | Kentucky |
| The Mona Lisa       | Michigan  |
| Les Deux Soeurs   | Nebraska |
+----------------------+-----------+

Чтобы вывести фамилию художника, написавшего каждую из картин, слегка изменим запрос, объединив результаты с таблицей artist:

mysql> SELECT artist.name, painting.title, states.name AS state
-> FROM artist, painting, states
-> WHERE artist.a_id = painting.a_id AND painting.state = states.abbrev;

+-----------+----------------------+------------+
| name       | title                        | state        |
+-----------+----------------------+------------+
| Da Vinci    | The Last Supper    | Indiana    |
| Da Vinci    | The Mona Lisa       | Michigan   |
| Van Gogh | Starry Night           | Kentucky |
| Van Gogh | The Potato Eaters | Kentucky  |
| Van Gogh | The Rocks              | Iowa        |
| Renoir      | Les Deux Soeurs    | Nebraska |
+-----------+-----------------------+------------+

Теперь запрос включает трехстороннее соединение, хотя природа связи между авторами и картинами не изменилась: это связь «один-ко-многим», а не «многие-ко-многим».

Статьи по MySQL на эту тему:

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