3.3.4.9 Using More Than one Table — MySQL 5.7

3.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
Fluffy	1995-05-15	litter	4 котят, 3 самки, 1 самец
Buffy	1993-06-23	litter	5 щенков, 2 самки, 3 самца
Buffy	1994-06-19	litter	3 щенка, 3 самки
Chirpy	1999-03-21	vet	нужно было выпрямить клюв
Slim	1997-08-03	vet	сломанный ребро
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Подарили ему новую игрушку для жевания
Claws	1998-03-17	birthday	Подарили ему новый ошейник от блох
Whistler	1998-12-09	birthday	Первый день рождения

Загрузите записи следующим образом:

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     |
+--------+------+-------+------+---------+

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