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,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) 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 p2.sex = 'm';
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
このクエリーでは、テーブル名のエイリアスを指定してカラムを参照し、各カラムがテーブルのどちらのインスタンスに関連するかを必ず明確にしています。