サンプルデータその3，
みなさんはKEN_
KEN_
図1 ダウンロードページ
$ 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 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十条西（１～４丁目） | ｷﾀ10ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ) | | 0010011 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十一条西（１～４丁目） | ｷﾀ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 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十条西（１～４丁目） | ｷﾀ10ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ) | | 0010011 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十一条西（１～４丁目） | ｷﾀ11ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ) | +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
たとえばこの通り，prefectureカラムとprefecture_カラムは1対1で対応していますので，JOINで結合することで同じ結果に戻すことができるので，JOINの練習ができます。
ちなみに，prefecture_テーブルを切り出したのと同じように読み仮名だけを切り出していくと，town_テーブルを作ろうとしたところでcityとcity_はたまたま衝突するものがなかっただけでプライマリーキーを変更しておくべきか……など，
更に郵便番号を1番多く持っている都道府県はどこか，JOINとGROUP BYやサブクエリーを駆使した問い合わせ，
更なるサンプルデータを求めて
3つのサンプルデータを紹介してきましたが，
MySQLの勉強のためのサンプルデータと考えた場合，JOIN，GROUP BYを使って縦横無尽にデータを抽出することですので，
この3つを満たすというとやはりリソース情報になるのかなと思っていますが，
