郵便番号テーブルを用いた SQL 演習
演習で行うこと
SQLデータベースにおける日本語の活用について学習する.
郵便番号データベース (Japanese ZIP code database)
郵便番号データベースは,zips,kens,shichosonsの3つのテーブルで構成されている.
郵便番号データベースの作成手順については別の Web ページで説明している.
Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)
すでに作成済みのデータベースを,以下の手順で開くことができる.
以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)
- Sqliteman を起動する
- 「File」→
「Open」
- データベースファイルを開く (Open Database File)
* Ubuntu での実行例(「SQLite/mydb」を開く場合)
データベースファイル SQLite/mydb を選択し, 「開く」をクリックする (Click '開く' after choosing the database file "SQLite/mydb")
- データベースの内容が表示されるので確認する (Database appears)
「Tables」を展開すると,テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')
zips,kens,shichosons テーブルの内容を表示して確認する. zips,kens,shichosons テーブルの表示ができない場合は, 授業内で個別に対応いたします
郵便番号に関する条件検索
テーブル zips を使用し,住所(jusho)の取得などを実行する.
- テーブル zips を使用し,郵便番号(zipcode)が「8120053」であるタップルを選択して,住所(jusho)を取得する.
SELECT * FROM zips WHERE zipcode=8120053;
- テーブル zips を使用し,郵便番号(zipcode)が「10000」より大きく,
かつ「20000」未満であるタップルを選択して,住所(ken_kanji, shichoson_kanji, choiki_kanji)を取得する.
SELECT * FROM zips WHERE zipcode>10000 and zipcode<20000;
都道府県名に関する条件検索
テーブル kens, shichosons を使用し,都道府県名(ken_kanji)による条件検索を実行する.
-
テーブル kens に登録されている都道府県名(ken_kanji)を全て表示する
SELECT ken_kanji FROM kens;
-
都道府県名(kenn_kanji)が「福岡県」であるタップルを テーブル kens から選択し,都道府県名(ken_kanji)とその読み仮名(ken_kana)を取得する.
SELECT ken_kanji, ken_kana FROM kens WHERE ken_kanji='福岡県';
-
都道府県名(kenn_kanji)が「京都府」であるタップルを テーブル kens から選択し,都道府県名(ken_kanji)とその読み仮名(ken_kana)を取得する.
SELECT ken_kanji, ken_kana FROM kens WHERE ken_kanji='京都府';
-
都道府県名が「福岡県」であるタップルを テーブル shichosons から選択し,都道府県名と市町村名を取得する.
SELECT ken_kanji, shichoson_kanji FROM shichosons WHERE ken_kanji='福岡県';
市町村名に関する条件検索
テーブル shichosons を使用し,市町村名(shichoson_kanji)による条件検索を実行する.
-
市町村名(shichoson_kanji)が「福岡市西区」であるタップルを テーブル shichosons から選択し,都道府県名(shichoson_kanji)とその読み仮名(shichoson_kana)を取得する.
SELECT shichoson_kanji, shichoson_kana FROM shichosons WHERE shichoson_kanji='福岡市西区';
町域名に関する条件検索
テーブル zips の属性 choiki_kanji (これは文字列属性)に関する条件検索を実行する. 文字列属性に対して「=」演算子を使用する.
- 町域名(choiki_kanji)が「箱崎」であるタップルを テーブル zips から選択し,
その郵便番号(zipcode)などを取得する.
SELECT * FROM zips WHERE choiki_kanji='箱崎';
-
町域名(choiki_kanji)が「箱」であるタップルを テーブル zips から選択し,
その郵便番号(zipcode)などを取得する.
SELECT * FROM zips WHERE choiki_kanji='箱';
文字列による郵便番号の検索(LIKE と % を用いた部分一致)
テーブル zips の属性 ken_kanji,shichoson_kanji,choiki_kanji (これは文字列属性)に関する条件検索を実行する. 文字列属性に対して「LIKE」演算子を使用する.
-
「泉原町」の郵便番号を取得する(市の名前が不明な場合)
SELECT * FROM zips WHERE choiki_kanji = '泉原町';
-
町域名 (choiki_kanji) に「空港」という文字列を含むタップルを選択し,郵便番号(zipcode) などを取得する.
SELECT * FROM zips WHERE choiki_kanji LIKE '%空港%';
-
町域名 (choiki_kanji) に「箱崎」という文字列を含むタプルを選択し,郵便番号(zipcode) などを取得する.
SELECT * FROM zips WHERE choiki_kanji LIKE '%箱崎%';
-
市町村名 (shichoson_kanji) に「福岡市西区」という文字列を含むタプルを選択し,JIS コード(jiscode) などを取得する.
SELECT * FROM shichosons WHERE shichoson_kanji LIKE '%福岡市西区%';
2つのテーブルの結合(Join;ジョイン)を行う例
テーブル zips と テーブル shichosons を,jiscode属性によって結合(join)し, 条件で絞り込む処理を実行する
-
都道府県名(ken_kanji)が「福岡県」であるタップルを選択し,住所(ken_kanji,shichoson_kanji,choiki_kanji)とその郵便番号(zipcode)を取得する.
SELECT * FROM zips, shichosons WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji = '福岡県';
-
市町村の読み(shichoson_kanji)が「太宰府市」であるタップルを選択し,住所(ken_kanji,shichoson_kanji,choiki_kanji),郵便番号(zipcode)などを取得する.
SELECT * FROM zips, shichosons WHERE zips.jiscode = shichosons.jiscode AND shichoson_kanji='太宰府市';
NATURAL JOIN を使用して同じ結果を得ることができる
SELECT * FROM zips NATURAL JOIN shichosons WHERE shichoson_kanji='太宰府市';
テーブル zips と テーブル shichosons と テーブル kens の結合処理を実行する.
- 郵便番号(zipcode)が 8120053 であるタップルを選択し,都道府県名
(ken_kanji)とそのよみ仮名(ken_kana)などを取得する.
SELECT * FROM zips, shichosons, kens WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji=kens.ken_kanji AND zipcode=8120053;
-
住所(ken_kanji,shichoson_kanji)が「福岡県,福岡市東区」であるタップルを,テーブル zips から選択し,
他のテーブルとの結合によって,都道府県と市町村と町域の読み仮名(ken_kana と shichoson_kana と choiki_kana )などを取得する.
SELECT * FROM zips, kens, shichosons WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji=kens.ken_kanji AND kens.ken_kanji = '福岡県' AND shichosons.shichoson_kanji = '福岡市東区';
タップルの総数 (COUNT)
COUNT(*) を使用してタップル数を計算する.
- テーブル zips のタップル数を取得する.
select count(*) FROM zips;
- テーブル zips において,choiki_kana が空文字でないものだけを計数する.
select count(*) FROM zips WHERE choiki_kanji != '';
-
テーブル zips について,郵便番号(zipcode)が10000以上
20000以下のタップルのタップル数を取得する.
select count(distinct zipcode) FROM zips WHERE zipcode>=10000 and zipcode<=20000;
ORDER BY の例
-
テーブル zips について,町域名が「田原」である住所(ken_kanji,shichoson_kanji,choiki_kanji)とその郵便番号(zipcode)などを,郵便番号順に表示する
SELECT * FROM zips WHERE choiki_kanji='田原' ORDER BY zipcode;
SELECT * FROM zips, kens, shichosons WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji=kens.ken_kanji AND choiki_kanji='田原' ORDER BY zipcode;
group by と COUNT の組み合わせ
group by による集約の後,COUNT を使用してタップル数を計算する.
- テーブル zips において,町域漢字(choiki_kanji)に「箱崎」という文字列を含むタップルを選択し,
それをテーブル shichosons と結合した後,都道府県名(ken_kanji)でグループ化する.
都道府県ごとにタップル数を算出する.
select count(*), * FROM zips, shichosons WHERE zips.jiscode = shichosons.jiscode AND zips.choiki_kanji LIKE '%箱崎%' group by shichosons.ken_kanji;
(参考) group by,COUNT を使用しない場合
SELECT * FROM zips, shichosons WHERE zips.jiscode = shichosons.jiscode AND zips.choiki_kanji LIKE '%箱崎%'
-
テーブル shichosons について,都道府県名(ken_kanji)でグループ化し,都道府県ごとにタップル数を算出する(都道府県ごとの市町村数が得られる).
SELECT ken_kanji, COUNT(*) FROM shichosons group by ken_kanji;
-
テーブル zips,shichosons,kens を使用する.結合の後,都道府県名(ken_kanji)でグループ化し,都道府県ごとのタップル数を取得する(都道府県ごとの郵便番号数が得られる).
select count(*), * FROM zips, shichosons, kens WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji = kens.ken_kanji group by kens.ken_kanji;
HAVING の例
COUNT で計算したタップル数に関して条件を指定し,タップルを絞り込む.
- テーブル shichosons について,都道府県名(ken_kanji)でグループ化し,都道府県ごとに市町村(shichoson)の数を算出して,20以下のものだけを選択する
(市町村数が20以下である都道府県を取得する).
SELECT ken_kanji, COUNT(*) FROM shichosons group by ken_kanji HAVING COUNT(*) <= 20;
-
テーブル zips,shichosons,kens を使用する.結合の後,都道府県名
(ken_kanji)でグループ化し,都道府県ごとのタップル数が3000以上のものを取得する(都道府県ごと
の郵便番号数が得られる).
SELECT kens.ken_kanji, COUNT(*) FROM zips, shichosons, kens WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji = kens.ken_kanji group by kens.ken_kanji HAVING COUNT(*)>3000;