第9回　サンプルテーブルのレイアウトを把握しよう(1) - MySQL講座

はじめに

みなさんこんにちは。サカイです。前回のサンプルデータ登録は無事にできましたか？今回からの講座では登録してもらったデータに対して抽出操作や更新操作などを行っていきますので、まだの方はぜひ前回の講座を見てサンプルデータを MySQL 上に用意しておいてくださいね。

では始めましょう。

太郎君と先輩、登場

• 太郎君、おはよう！
• おはようございます。先輩。この前は参りましたよ。ちょっとした設定の違いだけで、2分で済むデータ登録に15分も待たされたんですからね。先輩も人が悪いですよ。15分もかからないことは、本当は知っていたんでしょう？
• わはは。印象に残っただろう？ 一見無駄なようだけど、実際に自分でやってみて15分待った経験というのは自分の中にじっくりと滲み入っているから、印象の残り具合が全然違うよね。「知識」と「経験」の差、と言ったところかな。のちのち違いがわかってくると思うぞ。
• う～ん、なんだかうまいこと言ってだまされたような気もするけど、えぇ、だまされたと思ってどこまでもついていきますよ！
• ということで、前回はデータ登録を終えたんだったっよね？
• はい。登録して、SHOW TABLE 文や SELECT文を使って内容を確認してみました。

テーブル構成を確認する

• じゃぁ、早速いろいろな検索をやってみようか。・・・と言いたいところだけど、その前にやっておかなきゃいけないことがあるんだ。
• なんですか？
• 箱(テーブル)の中にどんなものが入っているかを知らないと、検索もなにもできないよね。「テーブルレイアウトを確認する」っていうんだけど、どんなテーブルがあって、それぞれのテーブルにはどんなカラムがあるのか、それを知っておく必要があるんだ。
• なるほど、そういえばそうですね。
• どんなテーブルがあるか、は、太郎君はもう調べることができるよね。
• はい。 SHOW TABLES 文を使います。こうやって。

mysql> USE phppro2;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_phppro2 |
+-------------------+
| customers         |
| items             |
| kounyuu_h         |
| kounyuu_m         |
| prefecture        |
+-------------------+
5 rows in set (0.02 sec)

• 結構結構。本来は開発ドキュメントとしてテーブル定義書というのがあって、各テーブルの意味やテーブルの持つカラムの意味、値に特別な意味があるときなどはその説明などが記述されているはずなんだ。

でも、、、、この案件はそういう文書がちゃんとメンテできてなくて(*1)、口伝でカラムの意味などが共有されてきているんだ(*2)。

• ・・・はい。。。
• （こほん） えぇと、じゃぁ各テーブルの説明をするよ。これは販売管理システムのデータなんだ。各テーブル名からなんとなく推測できると思うけど、それぞれのテーブルはこんなデータを保持している。

 customers    顧客データ
 items        商品データ
 kounyuu_h    購入履歴(販売)データヘッダ
 kounyuu_m    購入履歴(販売)データ明細
 prefecture   都道府県情報

それぞれのテーブルのレイアウト（カラム一覧）を見るには、

DESC [テーブル名]; 

とするんだ。やってごらん。

• はい。 まず customers テーブルでやってみます。

mysql> DESC customers;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(64)  | YES  |     | NULL    |                |
| zip             | varchar(8)   | YES  |     | NULL    |                |
| prefecture_code | char(2)      | YES  |     | NULL    |                |
| address         | varchar(255) | YES  |     | NULL    |                |
| birthday        | date         | YES  |     | NULL    |                |
| regist_date     | datetime     | YES  |     | NULL    |                |
| unregist_date   | datetime     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)

• ほかのテーブルも全部見てみますね。

mysql> DESC items;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(64) | NO   | MUL | NULL    |                |
| base_price | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESC kounyuu_h;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| customer_id | int(11)  | YES  | MUL | NULL    |                |
| buydate     | datetime | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESC kounyuu_m;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| kounyuu_h_id | int(11) | NO   | MUL | NULL    |                |
| item_id      | int(11) | YES  | MUL | NULL    |                |
| amount       | int(11) | YES  |     | NULL    |                |
| price        | int(11) | YES  |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> DESC prefecture;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| code  | char(2)    | NO   | PRI | NULL    |       |
| name  | varchar(4) | NO   | MUL | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• うん。いいね。かいつまんで各テーブルとカラムの説明をしようか。

prefecture テーブルには、都道府県コードと名前の組み合わせが入っている。
customers テーブルは顧客の情報を登録しておくテーブルだ。

name(名前)、zip(郵便番号)、prefecture_code(都道府県コード)、address(住所)、birthday(誕生日)、regist_date(入会日時)、unregist_date(脱会日時)が登録されている。

prefecture_code には都道府県の「コード」だけが入っていて、実際の都道府県の「名前」は、prefecture テーブルにある。 都道府県コードは prefecture テーブル内で一意になっているから、customersテーブルの都道府県コードから実際の都道府県名称を一意に得ることができるんだ。

• 一意、というのは確実に一個しかない、ということですか？
• そのとおり。ほかに、ユニーク　という言い方もあるよ。どっちも同じ意味で使われる。

items はうちが扱っている商品データが格納されるテーブルだ。 idのほかは、name(商品名)とbase_price(基本価格)だけがあるね。商品情報ってもっといろんな情報を持つ事が多いけど、うちのこのテーブルレイアウトはとってもシンプルだね。

そして残る２つ、kounyuu_h と kounyuu_m が購入履歴のヘッダと明細テーブルだ。
ヘッダは1回の買い物につき1件登録され、明細はその買い物で購入した商品の種類の数だけ登録されることになる。

customer_id は顧客IDで、あらかじめ customer テーブルに登録してある顧客情報の ID が入る。buydate は購入日時だね。このテーブルによって、誰がいつ買い物したのかが分かるわけだ。明細のほうは、その購入ヘッダのID(kounyuu_h_id)に紐付いて、item_id(商品ID)、amount(個数)、price(販売価格)が登録されている。

• 正直なところ、なんかわかったようなわからないような感じです。。
• そうだろうね。テーブル間の関係の全体像を頭に思い浮かべておくと理解しやすいんだよ。今説明したテーブルの説明を図にするとこんな感じになるんだ。

※ クリックすると拡大表示されます。

• わぁ。customerテーブルがprefectureテーブルを必要としていて、っていうか参照していて、購入履歴ヘッダが顧客テーブルを、そして購入履歴明細が履歴ヘッダと items テーブルを参照しているのが一目瞭然ですね。
• この図は慣れるまでは常に手元に置いておくといいよ。僕だって少し複雑なクエリの時にはこんな図を描いてみて考えるからね。

----
(*1) 決して誉められた状態ではありません！ テーブル定義についてもきちんと文書を残す習慣をつけましょうね、先輩！（本文へ）
(*2) ・・・と言いつつ、実態として同じような環境の案件は少なくないでしょう。決して誉められた状態ではありません！ そういうときは太郎君、先輩から教えてもらったことを文書として記録していく作業を引き受けるといいですよ。（本文へ）