pet テーブルはペットの記録を保持します。 獣医の診察や出産といったペットの生涯におけるイベントなど、ペットに関するほかの情報を記録するには、別のテーブルが必要です。 このテーブルはどのようなものにしたらよいでしょうか。 次の情報を含める必要があります。
各イベントがどのペットに関するものかを示すためのペット名。
イベントがいつ発生したかを示す日付。
イベントを説明するフィールド。
イベントを分類できるようにする場合は、イベントタイプのフィールド。
これらを考慮すると、event テーブルの 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 テーブルだけでは質問に回答できない場合はどのようなときでしょうか。
各ペットの出産時の年齢を調べるとします。 前に、2 つの日付から年齢を計算する方法を学びました。 ペットの出産日は 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句で 2 つのテーブルを結合しています。-
複数のテーブルの情報を組み合わせる (結合する) 場合、1 つのテーブルのレコードとほかのテーブルのレコードがどのように対応するかを指定する必要があります。 両方のテーブルに
nameカラムがあるため、これは簡単です。 このクエリーは、ON句を使用して、2 つのテーブルのレコードをname値に基づいて対応させています。このクエリーは
INNER JOINを使用してテーブルを結合しています。INNER JOINでは、ON句で指定された条件を両方のテーブルが満たす場合にかぎって、結果にテーブルの行が許可されます。 この例では、petテーブルのnameカラムとeventテーブルのnameカラムが一致する必要があるとON句で指定しています。 一方のテーブルに名前が表示され、他方のテーブルには表示されない場合、ON句の条件が失敗するため、結果に行は表示されません。 nameカラムは両方のテーブルにあるため、このカラムを参照するときはどちらのテーブルのものかを明確に示す必要があります。 そのためには、カラム名の前にテーブル名を付加します。
2 つの異なるテーブルでなくても結合は実行できます。 テーブル内のレコードをその同じテーブル内のほかのレコードと比較する場合に、テーブルをそれ自体に結合すると役立つことがあります。 たとえば、ペットどうしの飼育ペアを検索するには、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 |
+--------+------+-------+------+---------+このクエリーでは、テーブル名のエイリアスを指定してカラムを参照し、各カラムがテーブルのどちらのインスタンスに関連するかを必ず明確にしています。