第16回 テーブル定義の変更と応用練習 (その1)
はじめに
みなさん、こんにちは。サカイです。前回までで基本的なデータ操作のSQL構文である SELECT, INSERT, UPDATE, DELETE をすべて学びました。
今回はテーブル定義の変更である ALTER TABLE 文を話の中心に据えつつ、太郎君が以前よりやりたかった「都道府県ごとの人口比による顧客割合のランキング」を実現するためのエピソードを紹介します。
都道府県テーブル定義の変更(概要編)
- それじゃこの前約束したとおり今日は、都道府県人口のデータをテーブルに登録して会員数の人口比を集計するところまでやってみることにしようか。都道府県別の人口データは持ってるかな？
- はい。国立社会保障・人口問題研究所調べの 「XII. 都道府県別統計」 という資料がちょうどよいかなと思っています。
- なるほど。その中にある「表12－１ 都道府県，性別人口，人口性比，人口密度および人口増加率：2007年」という資料だね。いろいろなデータがあるけれどもこの中から都道府県名と人口の総数の列だけを見たらよさそうだね。
- はい。
- じゃぁ最初に、今日やることの概要をまとめておこうか。こんな順で作業をしていくから、この大きな流れを頭の中に置いておいてほしいな。
1. 人口データの確認 2. テーブル定義変更の検討 3. テーブル定義の変更 4. データの投入 5. やりたかった集計作業に挑戦
- はい、わかりました。
都道府県テーブル定義の変更(準備編)
- 「1.人口データの確認」は、さっき太郎君に見せてもらったデータで十分だ。一応「総数」は 1,000人単位であることを確認しておこうか。
- おっと。そうですね。北海道の人口 5,570 人じゃぁ少なすぎますもん。
- まずこの人口データをどのテーブルに持つかの検討なんだけど、今回は prefecture テーブルに持たせることにしよう。
理由は、都道府県と人口データが１対１に対応し、今のところ定期的なデータ更新の予定がないからだ。もし1対1でない場合、たとえば男女別のデータを持つとか毎年の履歴を持つとかを考えているのならば prefectureテーブルには持たずに人口データ専用のテーブルをつくることも検討したほうがいいね。
ともかく今回はその心配はないので、prefecture テーブルに新しいカラムを追加して人口データを持たせることにしよう。まず現在の prefecture テーブルがどういう定義になっているか見てくれるかな。
- はい。 SHOW CREATE TABLE 文ですね。
- 行末を、いつも ";" にしているけど今回は "¥G" にしてみてごらん。
- はい。
mysql> SHOW CREATE TABLE prefecture\G
*************************** 1. row ***************************
Table: prefecture
Create Table: CREATE TABLE `prefecture` (
`code` char(2) NOT NULL,
`name` varchar(4) NOT NULL,
PRIMARY KEY (`code`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932
1 row in set (0.01 sec)
- 先輩。この "¥G" ってなんですか？
- 結果を表形式ではなく縦並びにして表示することを指示するものなんだ。いつもやってるように 末尾を ";" にすると、こんなふうになっちゃってヘッダとかが邪魔だろう？ こういうときに "¥G" を使うと見やすく表示できるんだ。
mysql> SHOW CREATE TABLE prefecture;
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
|
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
| prefecture | CREATE TABLE `prefecture` (
`code` char(2) NOT NULL,
`name` varchar(4) NOT NULL,
PRIMARY KEY (`code`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 |
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)
- 確かに見え方が全然違いますね。 "¥G" のほうがスッキリします。
- この "¥G" は SHOW CREATE TABLE 以外の通常のSQLの時でも使えるんだよ。たとえばある結果セットが３件あるような SELECT 文の場合、普通はこんな表示になるよね。
mysql> SELECT * FROM items LIMIT 3;
+----+------------+------------+
| id | name | base_price |
+----+------------+------------+
| 1 | スーパー01 | 8520 |
| 2 | スーパー07 | 5950 |
| 3 | スーパー15 | 2210 |
+----+------------+------------+
3 rows in set (0.00 sec)
- これを "¥G" 指令をすると、こんなふうに縦にならんで表示されるんだ。カラム数が多すぎて ";" での表形式の表示ではわかりにくい場合で結果の件数がそれほど多くないときに使うと便利だね。
mysql> SELECT * FROM items LIMIT 3\G
*************************** 1. row ***************************
id: 1
name: スーパー01
base_price: 8520
*************************** 2. row ***************************
id: 2
name: スーパー07
base_price: 5950
*************************** 3. row ***************************
id: 3
name: スーパー15
base_price: 2210
3 rows in set (0.00 sec)
- それはそうと、さっきの CREATE テーブル文を見てみようか。ごちゃごちゃして定義を把握しにくければ、情報量は減るけれどもまず DESC 文で見てみる方法もあるね。
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.06 sec)
- こっちのほうがわかりやすいかな。ここに、人口情報を格納するカラムを追加すればいいわけだ。 名前は population にしておこうか。カラムの型は数字だから int 型で。
- すごい。さくさく方針が決まっていきますね！
