5.3.4.9 Using More Than one Table — MySQL 9.2

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

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

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

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

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

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

Имя	Дата	Тип	Примечание
Пушок	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     |
+--------+------+-------+------+---------+

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