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

В таблице pet хранятся только основные данные о животных. Если же нужно держать в базе какую-либо дополнительную информацию о них (скажем, записи о событиях наподобие посещения ветеринара или рождения потомства), понадобится еще одна таблица. Давайте определимся с ее структурой. Эта таблица должна содержать:

• имена животных, чтобы не путаться с тем, к какому животному относится какое событие

• дата события

• поле для описания события

• поле, отражающее тип события, для того, чтобы можно было распределить их по категориям

С учетом всех приведенных выше требований можно составить примерно такую команду CREATE TABLE:

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 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

Загрузите записи с помощью следующей команды:

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

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

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

mysql> SELECT pet.name,

-> (TO_DAYS(date) - TO_DAYS(birth))/365 AS age,

-> remark

-> FROM pet, event

-> WHERE pet.name = event.name AND type = "litter";

+--------+------+-----------------------------+

| name | age | remark |

+--------+------+-----------------------------+

| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |

| Buffy | 4.12 | 5 puppies, 2 female, 3 male |

| Buffy | 5.10 | 3 puppies, 3 female |

+--------+------+-----------------------------+

На некоторые аспекты этого запроса следует обратить особое внимание:

• В выражении FROM указаны две таблицы, так как запрос будет получать информацию из обеих.

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

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

Для объединения не обязательно иметь две отдельные таблицы; иногда можно объединить таблицу с самой собой - если нужно сравнить одни записи таблицы с другими записями той же таблицы. Например, для того, чтобы обнаружить среди животных ``семейные пары'', можно объединить таблицу pet с ней самой, составив пары животных разного пола, но одного вида:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

-> FROM pet AS p1, pet AS p2

-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

+--------+------+--------+------+---------+

| name | sex | name | sex | species |

+--------+------+--------+------+---------+

| Fluffy | f | Claws | m | cat |

| Buffy | f | Fang | m | dog |

| Buffy | f | Bowser | m | dog |

+--------+------+--------+------+---------+

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

В MySQL имеется несколько функций, реализующих арифметические операции над датами. Эти функции позволяют, например, вычислять возраст или получать части даты.

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

mysql> SELECT name, birth, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(birth))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))

-> AS age

-> FROM pet;

+----------+------------+--------------+------+

| name | birth | CURRENT_DATE | age |

+----------+------------+--------------+------+

| Fluffy | 1993-02-04 | 2001-08-29 | 8 |

| Claws | 1994-03-17 | 2001-08-29 | 7 |

| Buffy | 1989-05-13 | 2001-08-29 | 12 |

| Fang | 1990-08-27 | 2001-08-29 | 11 |

| Bowser | 1989-08-31 | 2001-08-29 | 11 |

| Chirpy | 1998-09-11 | 2001-08-29 | 2 |

| Whistler | 1997-12-09 | 2001-08-29 | 3 |

| Slim | 1996-04-29 | 2001-08-29 | 5 |

| Puffball | 1999-03-30 | 2001-08-29 | 2 |

+----------+------------+--------------+------+

В этом примере функция YEAR() выделяет из даты год, а RIGHT() - пять крайних справа символов, представляющих календарный день (MM-DD). Часть выражения, сравнивающая даты, выдает 1 или 0, что позволяет уменьшить результат на единицу, если текущий день (CURRENT_DATE) находится к началу календаря ближе, нежели день рождения животного. Все выражение смотрится несколько неуклюже, поэтому вместо него в заголовке соответствующего столбца результатов выводится псевдоним (age - "возраст").

Запрос неплохо работает, но разобраться в результатах было бы проще, если бы строки располагались в определенном порядке. Этого можно достичь, добавив в запрос выражение ORDER BY name и отсортировав таким образом результаты по имени:

mysql> SELECT name, birth, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(birth))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))

-> AS age

-> FROM pet ORDER BY name;

+----------+------------+--------------+------+

| name | birth | CURRENT_DATE | age |

+----------+------------+--------------+------+

| Bowser | 1989-08-31 | 2001-08-29 | 11 |

| Buffy | 1989-05-13 | 2001-08-29 | 12 |

| Chirpy | 1998-09-11 | 2001-08-29 | 2 |

| Claws | 1994-03-17 | 2001-08-29 | 7 |

| Fang | 1990-08-27 | 2001-08-29 | 11 |

| Fluffy | 1993-02-04 | 2001-08-29 | 8 |

| Puffball | 1999-03-30 | 2001-08-29 | 2 |

| Slim | 1996-04-29 | 2001-08-29 | 5 |

| Whistler | 1997-12-09 | 2001-08-29 | 3 |

+----------+------------+--------------+------+

Отсортировать результаты по возрасту также можно при помощи выражения ORDER BY:

mysql> SELECT name, birth, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(birth))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))

-> AS age

-> FROM pet ORDER BY age;

+----------+------------+--------------+------+

| name | birth | CURRENT_DATE | age |

+----------+------------+--------------+------+

| Chirpy | 1998-09-11 | 2001-08-29 | 2 |

| Puffball | 1999-03-30 | 2001-08-29 | 2 |

| Whistler | 1997-12-09 | 2001-08-29 | 3 |

| Slim | 1996-04-29 | 2001-08-29 | 5 |

| Claws | 1994-03-17 | 2001-08-29 | 7 |

| Fluffy | 1993-02-04 | 2001-08-29 | 8 |

| Fang | 1990-08-27 | 2001-08-29 | 11 |

| Bowser | 1989-08-31 | 2001-08-29 | 11 |

| Buffy | 1989-05-13 | 2001-08-29 | 12 |

+----------+------------+--------------+------+

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

mysql> SELECT name, birth, death,

-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))

-> AS age

-> FROM pet WHERE death IS NOT NULL ORDER BY age;

+--------+------------+------------+------+

| name | birth | death | age |

+--------+------------+------------+------+

| Bowser | 1989-08-31 | 1995-07-29 | 5 |

+--------+------------+------------+------+

В этом запросе используется выражение death IS NOT NULL, а не death <> NULL, так как NULL - особое значение (более подробные пояснения приведены в разделе see section 3.3.4.6 Работа с значениями NULL).

А как поступать, если потребуется определить, дни рождения каких животных наступят в следующем месяце? Для таких расчетов день и год значения не имеют; из столбца, содержащего дату рождения, нас интересует только месяц. В MySQL предусмотрено несколько функций для получения частей дат - YEAR(), MONTH(), и DAYOFMONTH(). В данном случае нам подойдет функция MONTH(). Увидеть работу этой функции можно с помощью простого запроса, выводящего дату рождения birth и MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;

+----------+------------+--------------+

| name | birth | MONTH(birth) |

+----------+------------+--------------+

| Fluffy | 1993-02-04 | 2 |

| Claws | 1994-03-17 | 3 |

| Buffy | 1989-05-13 | 5 |

| Fang | 1990-08-27 | 8 |

| Bowser | 1989-08-31 | 8 |

| Chirpy | 1998-09-11 | 9 |

| Whistler | 1997-12-09 | 12 |

| Slim | 1996-04-29 | 4 |

| Puffball | 1999-03-30 | 3 |

+----------+------------+--------------+

Найти животных, дни рождения которых наступят в следующем месяце, тоже несложно. Предположим, что сейчас на дворе апрель. Тогда номер текущего месяца - 4, а искать надо животных, родившихся в мае (5-м месяце), таким образом:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

+-------+------------+

| name | birth |

+-------+------------+

| Buffy | 1989-05-13 |

+-------+------------+

Конечно, в декабре возникают некоторые осложнения. Если просто добавить единицу к номеру месяца (12) и поискать животных, родившихся в тринадцатом месяце, найти что-нибудь вряд ли удастся. Вместо этого нужно искать животных, родившихся в январе (1-м месяце).

Можно даже написать небольшой запрос, который будет работать вне зависимости от того, какой нынче месяц. Функция DATE_ADD() позволяет прибавить к дате некоторый интервал времени. Если добавить к значению, возвращаемому функцией NOW(), месяц, а затем извлечь из получившейся даты номер месяца при помощи функции MONTH(), мы получим именно тот месяц, который нам нужен:

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Ту же задачу можно решить и другим методом - для этого нужно прибавить единицу к номеру месяца, следующего за текущим (воспользовавшись функцией расчета по модулю (MOD) для перехода к 0, если номер текущего месяца равен 12):

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

Функция MONTH возвращает число от 1 до 12, а выражение MOD(число,12) - число от 0 до 11. Поэтому операцию сложения нужно проводить после MOD(), иначе результат перепрыгнет с ноября (11) сразу на январь (1).