SQL の中で日本語を使う.
郵便番号データベースは zips, kens, shichosons の 3 つのテーブルから構成される.
郵便番号データベースの作成手順については別の Web ページで説明している.
すでに作成済みのデータベースを,下記の手順で開くことができる.
以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)
■ Ubuntu での実行例(「SQLite/mydb」を開く場合)
データベースファイル SQLite/mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "SQLite/mydb")
「Tables」を展開すると,テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')
zips, kens, shichosons テーブルの中身を表示してみる. zips, kens, shichosons テーブルの表示ができないというときは, 授業の場で個別対応します
テーブル zips を使用.住所(jusho)の取得などを行う.
SELECT * FROM zips WHERE zipcode=8120053;
SELECT * FROM zips WHERE zipcode>10000 and zipcode<20000;
テーブル kens, shichosons を使用.都道府県名(ken_kanji)による条件検索を行う.
SELECT ken_kanji FROM kens;
SELECT ken_kanji, ken_kana FROM kens WHERE ken_kanji='福岡県';
SELECT ken_kanji, ken_kana FROM kens WHERE ken_kanji='京都府';
SELECT ken_kanji, shichoson_kanji FROM shichosons WHERE ken_kanji='福岡県';
テーブル shichosons を使用.市町村名(shichoson_kanji)による条件検索を行う.
SELECT shichoson_kanji, shichoson_kana FROM shichosons WHERE shichoson_kanji='福岡市西区';
テーブル zips の属性 choiki_kanji (これは文字列属性)に関する条件検索を行う. 文字列属性に対して「=」を使う.
SELECT * FROM zips WHERE choiki_kanji='箱崎';
SELECT * FROM zips WHERE choiki_kanji='箱';
テーブル zips の属性 ken_kanji, shichoson_kanji, choiki_kanji (これは文字列属性)に関する条件検索を行う. 文字列属性に対して「LIKE」を使う.
SELECT * FROM zips WHERE choiki_kanji = '泉原町';
SELECT * FROM zips WHERE choiki_kanji LIKE '%空港%';
SELECT * FROM zips WHERE choiki_kanji LIKE '%箱崎%';
SELECT * FROM shichosons WHERE shichoson_kanji LIKE '%福岡市西区%';
テーブル zips と テーブル shichosons を,jiscode属性によって結合(join)し, 条件で絞り込む
SELECT * FROM zips, shichosons WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji = '福岡県';
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 の結合.
SELECT * FROM zips, shichosons, kens WHERE zips.jiscode = shichosons.jiscode AND shichosons.ken_kanji=kens.ken_kanji AND zipcode=8120053;
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(*) でタップル数を数える。
select count(*) FROM zips;
select count(*) FROM zips WHERE choiki_kanji != '';
select count(distinct zipcode) FROM zips WHERE zipcode>=10000 and zipcode<=20000;
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 を使って、タップル数を数える。
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 '%箱崎%'
SELECT ken_kanji, COUNT(*) FROM shichosons group by 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;
COUNT で数えたタップル数に関して条件を指定し,タップルを絞り込む.
SELECT ken_kanji, COUNT(*) FROM shichosons group by ken_kanji HAVING COUNT(*) <= 20;
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;