データベース の履歴(No.1)

• 履歴一覧
• ソース を表示
• データベース は削除されています。
  • 1 (2005-09-09 (金) 08:13:41)

---

第9回 データベース（MySQL）†

この内容、あるサイトの解説記事からパクってます。順次更新しないと…

目次†

MySQLのインストール†

データベースの準備†

データベースの設計†

郵便番号データベース†

「旧番号, 新番号, 都道府県名, 市区郡町村名, 町村字名」という レコードを持つ郵便番号データベースを作成してみましょう。

1. まず、日本全国の郵便番号データをダウンロードします。
   • http://vilab.org/yubin_euc.csv
2. 「head yubin_euc.csv」や「tail yubin_euc.csv」というコマンドで内容を確認してみましょう。

   "907-14","907-1432","沖縄県","八重山郡竹富町","古見"
   "907-15","907-1543","沖縄県","八重山郡竹富町","崎山"
   "907-14","907-1431","沖縄県","八重山郡竹富町","高那"
   "907-11","907-1101","沖縄県","八重山郡竹富町","竹富"
   "907-14","907-1434","沖縄県","八重山郡竹富町","南風見"
   "907-14","907-1433","沖縄県","八重山郡竹富町","南風見仲"
   "907-17","907-1751","沖縄県","八重山郡竹富町","波照間"
   "907-15","907-1544","沖縄県","八重山郡竹富町","鳩間"
   "907-18","907-1800","沖縄県","八重山郡与那国町","以下に掲載がない場合"
   "907-18","907-1801","沖縄県","八重山郡与那国町","与那国"

テーブルの設計†

関係データベースは、「テーブル」（表）から構成されています。テーブルには「カラム」または「フィールド」と呼ばれる縦の欄（項目）があり、ひとつひとつのデータは横一列の「レコード」（行）として並びます。

今回は以下のようなテーブルを作ります。「旧番号」「新番号」「都道府県名」などの欄がカラムであり、「060, 060-0041, 北海道, 札幌市中央区, 大通東」のようなひとまとまりのデータがレコードです。

テーブルの設計ではカラム（各欄）の型を定義します。「型」というのは、そのカラムに入るデータの形式のことで必ず定義します。今回は以下のようになります。テーブルの設計はデータベース構築の肝です。興味があれば、「正規化」と呼ばれるデータベースの論理設計手法を勉強してみてください。

SQLによるテーブル操作†

SQLは標準のデータベース言語であり、 MySQLをはじめ AccessからOracleまで、ほとんどのデータベースは SQLによる命令文で動きます。

テーブルを作成する†

【基本構文】 create table テーブル名 (カラム名1 カラム型1, カラム名2 カラム型2, ...);

1. データベースにテーブルを作ります。上記の構成でテーブルを作成するには、以下のSQLコマンドをタイプします。SQL文は、セミコロン（;）を打つまでが１行と認識されます。 例のように、長い命令を途中で改行して入力することが可能です。

   mysql> use user_db;
   
   mysql> create table zipcode(
       ->   oldzip text,
       ->   newzip text not null,
       ->   pref text,
       ->   city text,
       ->   addr text not null
       -> );
   Query OK, 0 rows affected (0.00 sec)

2. SQLでコマンドを打つことを、「SQL文を発行する」といいます。正しくテーブルが作られたか、確認してみましょう。

   mysql> show fields from zipcode;
   +---------+------+------+-----+---------+-------+
   | Field   | Type | Null | Key | Default | Extra |
   +---------+------+------+-----+---------+-------+
   | oldzip  | text | YES  |     | NULL    |       |
   | newzip  | text |      |     |         |       |
   | pref    | text | YES  |     | NULL    |       |
   | city    | text | YES  |     | NULL    |       |
   | addr    | text |      |     |         |       |
   +---------+------+------+-----+---------+-------+
   5 rows in set (0.00 sec)

• データベースの操作の基本は、MySQLにログイン → データベース選択 → テーブル名を指定して作業　という流れとなります。

（テーブルの削除のしかた）†

【基本構文】 drop table テーブル名;

データを作成する - INSERT文†

【基本構文】 insert into テーブル名 (カラム名1, カラム名2, ...) values (カラム値1, カラム値2, ...);

1. テーブルにデータ（レコード）を作成するには 「インサート（INSERT）文」を使います。

   mysql> insert into zipcode
       ->   (oldzip,newzip,pref,city,addr)
       -> values('154','154-0002','東京都','世田谷区','下馬');
   
   Query OK, 1 rows affected (0.03 sec)

2. 同様にして、以下のデータを登録してください。

   旧番号	新番号	都道府県名	市区郡町村名	町村字名
   060	060-0041	北海道	札幌市中央区	大通東
   154	154-0002	東京都	世田谷区	下馬
   468	468-0039	愛知県	名古屋市天白区	西入町
   907-18	907-1801	沖縄県	八重山郡与那国町	与那国

データを表示する - SELECT文†

【基本構文】 select フィールド名 from テーブル名 ;

1. 前項で登録したデータを表示してみます。テーブルからレコードを取り出すには、「セレクト（SELECT）文」を使います。
2. 郵便番号と都道府県のみ選択して表示します。

   mysql> select newzip,pref from zipcode;
   +----------+--------+
   | newzip   | pref   |
   +----------+--------+
   | 060-0041 | 北海道 |
   | 154-0002 | 東京都 |
   | 468-0039 | 愛知県 |
   | 907-1801 | 沖縄県 |
   +----------+--------+
   4 rows in set (0.00 sec)

3. すべてのフィールドを表示する場合は、*記号で代用できます。

   mysql> select * from zipcode;

データの一括投入†

1. 郵便番号データは、各県数千件のデータになるので、INSERT文での手作業での登録は現実的ではありません。
2. そこで一気に流し込みを行ないます。ファイルは、適当な場所に yubin_euc.csv として置いておきます。

   mysql> load data infile '/置いてある場所/yubin_euc.csv'
       -> into table zipcode fields terminated by ',';
   Query OK, 121622 rows affected (0.78 sec)
   Records: 121622 Deleted: 0 Skipped: 0 Warnings: 0

   • エラーが出る場合は、yubin_euc.csv が読めるようになっているか、さらにはそれが置いてあるディレクトリが読めるようになっているか、チェックしてください。 なっていない場合には、chmod でパーミッションを与えてください。
3. レコードの登録件数は、以下のSQLコマンドで確認できます。

   mysql> select count(*) from zipcode;
   +----------+
   | count(*) |
   +----------+
   | 121622   |
   +----------+
   1 row in set (0.00 sec)

データの検索†

テーブル操作のSQL文を使ってみます。データベースの肝の部分です。 あらかじめMySQLにログインしてからデータベースを選択しておいてください。

データを検索する - WHERE句†

テーブルからデータ（レコード）を検索するには 「セレクト（SELECT）文」を使います。WHERE句で条件文をしているすることにより、条件にマッチするデータのみを表示できます。

【基本構文】 select フィールド名 from テーブル名 where 条件式 ;

複数の行に分けて書くこともできます。

select フィールド名
from テーブル名
where 条件式 ; 

1. 古い郵便番号が「468」の、すべてのフィールドを表示する。

   select * from zipcode where oldzip='468' ;

2. 市区名が「世田谷区」の、新番号と町村名を表示する。

   select newzip,addr
   from zipcode
   where city='世田谷区' ;

3. 以下のようにすると、条件にマッチするレコードの件数が分かります。

   select count(*)
   from zipcode
   where pref='鳥取県' ;

4. 以下のように「リミット（LIMIT）句」を使うと、表示する件数を限定できます。 県名が「鳥取県」の、すべてのフィールドを、50件表示する。

   select *
   from zipcode
   where pref='鳥取県'
   limit 50;

ちょっと高度な検索†

以下のSQLを理解し、結果を記録してください。

1. 都道府県名が「滋賀県」 かつ 市区郡町村名が「草津市」

   select *
   from zipcode
   where pref='滋賀県' && city='草津市' ;

2. 市区郡町村名が「渋谷区」 または 「新宿区」

   select *
   from zipcode
   where city='渋谷区' || city='新宿区' ;

3. 市区郡町村名に「日高」が含まれる（likeは文字列比較、%はワイルドカード）

   select *
   from zipcode
   where city like '%日高%';

課題†

【問1】 上記の「ちょっと高度な検索」の実行結果をまとめてください。検索結果が大量の場合には、ちゃんとできたことがわかる程度に適当な量を抜粋してください（「中略」とか「途中省略」とか）。

【問2】 下記問題を解いて、解答のSQL文と実行結果をまとめてください。検索結果が大量の場合には、ちゃんとできたことがわかる程度に適当な量を抜粋してください（「中略」とか「途中省略」とか）。

1. 「東京都町田市」の郵便番号の一覧を検索しなさい。
2. 「東京都町田市玉川学園」の郵便番号を検索しなさい。
3. 旧郵便番号が「223」だった地区の住所を検索しなさい。
4. 町字名（addr）が、「霞ヶ関」（または、自分の好きな地名何でもいい。結果が出てくるようにしてください）である地区の一覧を検索しなさい。
5. 住所のどこかに「中田」の入っている地名の一覧を検索しなさい。

• 時間のあまった人は、自分の住所等を題材にして適当な検索の例題を考えてみてください。
• それでも時間の余った人は、前回の続きをがんばってみてください。

【提出】

• 問題を解いて結果を確認したら、解答のSQL文と実行結果をメールで送ってください。
  • report＠fig.ele.eng.tamagawa.ac.jp

---

おしまい。
今日はレポートはありません。