サンプルデータその3, KEN_ ALL. CSV
みなさんはKEN_
KEN_
$ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip $ unzip ken_all.zip $ iconv -f Shift_JIS -t UTF-8 KEN_ALL.CSV > /tmp/KEN_ALL_UTF8.CSV
このファイルは非常に残念ながら文字コードがShift_iconv
コマンドを使用して文字コードをUTF-8に変更したものを作っておきます。半角カナの有無に関わらず,
mysql> CREATE DATABASE zipcode CHARACTER SET utf8mb4; mysql> CREATE TABLE zipcode.zipcode( -> code varchar(12) NOT NULL, -> old_zipcode varchar(5) NOT NULL, -> zip_code varchar(7) NOT NULL, -> prefecture_kana varchar(255) NOT NULL, -> city_kana varchar(255) NOT NULL, -> town_kana varchar(255) NOT NULL, -> prefecture varchar(128) NOT NULL, -> city varchar(128) NOT NULL, -> town varchar(128) NOT NULL -> ) DEFAULT CHARACTER SET= utf8mb4;
データの意味は郵便番号データの説明 - 日本郵便に記載されています。とりあえず,
mysql> SET character_set_server= utf8mb4; mysql> SET NAMES utf8mb4; mysql> use zipcode mysql> LOAD DATA INFILE '/tmp/KEN_ALL_UTF8.CSV' INTO TABLE zipcode.zipcode FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy); Query OK, 123823 rows affected (1.54 sec) Records: 123823 Deleted: 0 Skipped: 0 Warnings: 0
2つのSET
ステートメントは,yum
コマンドでインストールしたまま何の編集もしていないため,
テーブルのカラム数とCSVファイルのカラム数が合わなくなったため,LOAD DATA INFILE
ステートメントの中でカラム数の差異を吸収しています@dummy
というユーザー変数に格納され,
このCSVファイルに触ったことのある方はご存知かと思いますが,
mysql> SELECT zip_code, prefecture, prefecture_kana, city, city_kana, town, town_kana FROM zipcode.zipcode ORDER BY zip_code LIMIT 3; +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+ | zip_code | prefecture | prefecture_kana | city | city_kana | town | town_kana | +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+ | 0010000 | 北海道 | ホッカイドウ | 札幌市北区 | サッポロシキタク | 以下に掲載がない場合 | イカニケイサイガナイバアイ | | 0010010 | 北海道 | ホッカイドウ | 札幌市北区 | サッポロシキタク | 北十条西(1~4丁目) | キタ10ジョウニシ(1-4チョウメ) | | 0010011 | 北海道 | ホッカイドウ | 札幌市北区 | サッポロシキタク | 北十一条西(1~4丁目) | キタ11ジョウニシ(1-4チョウメ) | +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+ mysql> CREATE TABLE zipcode.prefecture_kana( -> prefecture varchar(128) NOT NULL, -> prefecture_kana varchar(255) NOT NULL, -> PRIMARY KEY(prefecture) -> ) DEFAULT CHARACTER SET= utf8mb4; mysql> INSERT INTO zipcode.prefecture_kana SELECT DISTINCT prefecture, prefecture_kana FROM zipcode; mysql> ALTER TABLE zipcode DROP COLUMN prefecture_kana, ADD CONSTRAINT FOREIGN KEY fkey_prefecture (prefecture) REFERENCES prefecture_kana(prefecture); mysql> SELECT zip_code, prefecture, prefecture_kana, city, city_kana, town, town_kana FROM zipcode.zipcode NATURAL JOIN zipcode.prefecture_kana ORDER BY zip_code LIMIT 3; +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+ | zip_code | prefecture | prefecture_kana | city | city_kana | town | town_kana | +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+ | 0010000 | 北海道 | ホッカイドウ | 札幌市北区 | サッポロシキタク | 以下に掲載がない場合 | イカニケイサイガナイバアイ | | 0010010 | 北海道 | ホッカイドウ | 札幌市北区 | サッポロシキタク | 北十条西(1~4丁目) | キタ10ジョウニシ(1-4チョウメ) | | 0010011 | 北海道 | ホッカイドウ | 札幌市北区 | サッポロシキタク | 北十一条西(1~4丁目) | キタ11ジョウニシ(1-4チョウメ) | +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
たとえばこの通り,prefecture
カラムとprefecture_
カラムは1対1で対応していますので,JOIN
で結合することで同じ結果に戻すことができるので,JOIN
の練習ができます。
ちなみに,prefecture_
テーブルを切り出したのと同じように読み仮名だけを切り出していくと,town_
テーブルを作ろうとしたところでcity
とcity_
はたまたま衝突するものがなかっただけでプライマリーキーを変更しておくべきか……など,
更に郵便番号を1番多く持っている都道府県はどこか,JOIN
とGROUP BY
やサブクエリーを駆使した問い合わせ,
更なるサンプルデータを求めて
3つのサンプルデータを紹介してきましたが,
MySQLの勉強のためのサンプルデータと考えた場合,JOIN
,GROUP BY
を使って縦横無尽にデータを抽出することですので,
この3つを満たすというとやはりリソース情報になるのかなと思っていますが,