5.3.4.9 Using More Than one Table — MySQL 8.4

5.3.4.9 Использование нескольких таблиц

Таблица pet отслеживает, какие у вас есть домашние животные. Если вы хотите записать другую информацию о них, например, события из их жизни, такие как посещение ветеринара или рождение приплода, вам нужна еще одна таблица. Как должна выглядеть эта таблица? Она должна содержать следующую информацию:

Имя животного, чтобы вы знали, к какому животному относится каждое событие.
Дата, чтобы вы знали, когда произошло событие.
Поле для описания события.
Поле типа события, если вы хотите иметь возможность классифицировать события.

Учитывая эти соображения, оператор CREATE TABLE для таблицы event может выглядеть так:

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));

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

name	date	type	remark
Пушистик	1995-05-15	приплод	4 котёнка, 3 девочки, 1 мальчик
Буффа	1993-06-23	приплод	5 щенков, 2 девочки, 3 мальчика
Буффа	1994-06-19	приплод	3 щенка, 3 девочки
Чирик	1999-03-21	ветеринар	нужно выровнять клюв
Худой	1997-08-03	ветеринар	сломанный ребро
Боузер	1991-10-12	приют
Клык	1991-10-12	приют
Клык	1998-08-28	день рождения	Подарили ему новую жевательную игрушку
Когти	1998-03-17	день рождения	Подарили ему новый ошейник от блох
Свистун	1998-12-09	день рождения	Первый день рождения

Загрузите записи так:

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

Исходя из того, что вы узнали из запросов, которые вы выполняли в таблице pet, вы сможете выполнять извлечения записей в таблице event; принципы те же. Но когда таблица event сама по себе недостаточно отвечает на вопросы, которые вы можете задать?

Предположим, что вы хотите узнать возраст, в котором каждое животное имело приплод. Ранее мы видели, как вычислять возраст по двум датам. Дата рождения приплода матери находится в таблице event, но для расчета ее возраста в эту дату вам нужна дата ее рождения, которая хранится в таблице pet. Это означает, что для запроса необходимы обе таблицы:

mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

Несколько замечаний по этому запросу:

Оператор FROM объединяет две таблицы, потому что для выполнения запроса необходима информация из обеих.
При объединении (соединении) информации из нескольких таблиц необходимо указать, как записи в одной таблице могут быть сопоставлены с записями в другой. Это легко, потому что у них есть общий столбец name. Запрос использует оператор ON для сопоставления записей в двух таблицах на основе значений name.

Запрос использует оператор INNER JOIN для объединения таблиц. INNER JOIN позволяет отображаться строкам из любой таблицы в результате, только если обе таблицы соответствуют условиям, указанным в операторе ON. В этом примере оператор ON указывает, что столбец name в таблице pet должен совпадать со столбцом name в таблице event. Если имя встречается в одной таблице, но не в другой, строка не отображается в результате, потому что условие в операторе ON не выполняется.
Поскольку столбец name встречается в обеих таблицах, необходимо быть точным, к какой таблице вы имеете в виду, ссылаясь на столбец. Это делается путем добавления имени таблицы перед именем столбца.

Не обязательно иметь две разные таблицы для выполнения объединения. Иногда полезно объединять таблицу с самой собой, если вы хотите сравнить записи в таблице с другими записями в той же таблице. Например, чтобы найти пары размножения среди ваших животных, вы можете объединить таблицу pet с самой собой для получения кандидатов в пары из живых самцов и самок одного вида:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

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