Summer/2006-02 の編集

* 第2日午後　データベースとSQL実習 [#gaef5153]

- 来年はjoinのある課題にしないと。

** 目次 [#r061f41b]

#contents

** データベースの準備 [#pb8615f7]

*** 「SQLの窓」のインストール [#l0356e16]
+ 「SQLの窓」をダウンロードする。
-- http://vilab.org/summer/winofsql15.lzh
+ ホームディレクトリ（マイ ドキュメント）の中に展開する。
-- フォルダ winofsql15 を D:\ から マイドキュメントにコピーする。
+ 東京の郵便番号データをダウンロードし、Excelで内容を確認する。
-- http://vilab.org/summer/ziptokyo.xls 
+ 「SQLの窓」展開したフォルダの中の db.html をダブルクリックして起動する。
-- winofsql15 の下の db.html
+ 「参照...」ボタンを押して、データベースとして ziptokyo.xls を読み込む。
+ 「select *」ボタンを押して、データベースの内容を確認する。

*** Accessでの方法 [#xe2a72b7]
- AccessでSQLを使う方法
-- http://www.pursue.ne.jp/jouhousyo/SQLDoc/try.htm
-- Excelのファイルを読み込むには、「インポート」する。

*** テーブルの設計 [#wc11d619]

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

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

|旧番号|新番号 |都道府県名|市区郡町村名    |町村字名|h
|060   |0600041|北海道    |札幌市中央区    |大通東  |
|154   |1540002|東京都    |世田谷区        |下馬    |
|468   |4680039|愛知県    |名古屋市天白区  |西入町  |
|90718 |9071801|沖縄県    |八重山郡与那国町|与那国  |

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

|zipcode|>|>||h
|名称  |内容        |型  |属性|h
|oldzip|旧番号      |text||
|newzip|新番号      |text|空欄不可|
|pref  |都道府県名  |text||
|city  |市区郡町村名|text||
|addr  |町字名      |text|空欄不可|

** SQLによるテーブル操作 [#p8459703]

「SQLの窓」では、Excelのワークシート名をworksheetとすると、テーブル名は ''[worksheet$]'' で参照する。この郵便番号データベースの例だと、''zipcodeのかわりに [zipcode$]''と書く必要がある。

*** テーブルの作成（省略） [#t366f18b]

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

*** テーブルの削除（省略） [#m338309f]

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

*** データを表示する - SELECT文 [#i5975842]

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

+ 前項で登録したデータを表示してみる。テーブルからレコードを取り出すには、「セレクト（SELECT）文」を使う。
+ 郵便番号と都道府県のみ選択して表示する。
 select newzip,pref from zipcode;
 +---------+--------+
 | newzip  | pref   |
 +---------+--------+
 | 0600041 | 北海道 |
 | 1540002 | 東京都 |
 | 4680039 | 愛知県 |
 | 9071801 | 沖縄県 |
 +---------+--------+
+ すべてのフィールドを表示する場合は、*記号で代用できる。
 select * from zipcode;

** データの検索 [#x127fe34]

テーブル操作のSQL文を使ってみる。データベースの肝の部分。

*** データを検索する - WHERE句 [#s0272778]

テーブルからデータ（レコード）を検索するには 「セレクト（SELECT）文」を使う。WHERE句で条件文をしているすることにより、条件にマッチするデータのみを表示できる。
>''【基本構文】 select フィールド名 from テーブル名 where 条件式 ; ''
<
複数の行に分けて書くこともできる。
 select フィールド名
 from テーブル名
 where 条件式 ;

+ 古い郵便番号が「113」の、すべてのフィールドを表示する。（「SQLの窓」の場合、数字の '' はつけないほうがいいかも）
 select * from zipcode where oldzip='113' ;
+ 市区名が「世田谷区」の、新番号と町村名を表示する。
 select newzip,addr
 from zipcode
 where city='世田谷区' ;
+ 以下のようにすると、条件にマッチするレコードの件数が分かる。
 select count(*)
 from zipcode
 where city='国分寺市' ;
+ 以下のように「リミット（LIMIT）句」を使うと、表示する件数を限定できる。 県名が「新宿区」の、すべてのフィールドを、50件表示する。（「SQLの窓」の場合、limitは使えないかも）
 select *
 from zipcode
 where city='新宿区'
 limit 50;

*** ちょっと高度な検索 [#fbffe792]

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

+ 市区郡町村名が「渋谷区」 または 「文京区」
 select *
 from zipcode
 where city='渋谷区' or city='新宿区' ;
+ 市区郡町村名に「島」が含まれる（likeは文字列比較、%はワイルドカード）
 select *
 from zipcode
 where city like '%島%';
+ 市区郡町村名が「中央区」 かつ 市区郡町村名が「日本橋」で始まる。（「SQLの窓」の場合、'%日本橋%'でないと結果が出ないかも）
 select *
 from zipcode
 where city='中央区' and addr like '日本橋%' ;

*** データの挿入 - INSERT文 [#le52b3c1]

-「SQLの窓」ではINSERT文は下記のように使えないかもしれません。

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

+ テーブルにデータ（レコード）を作成するには 「インサート（INSERT）文」を使う。
 insert into zipcode
   (oldzip,newzip,pref,city,addr)
  values('060','0600041','北海道','札幌市中央区','大通東');
+ 同様にして、以下のデータを登録してみなさい。
|旧番号|新番号 |都道府県名|市区郡町村名    |町村字名|h
|060   |0600041|北海道    |札幌市中央区    |大通東  |
|468   |4680039|愛知県    |名古屋市天白区  |西入町  |
|90718 |9071801|沖縄県    |八重山郡与那国町|与那国  |

** 課題 [#wb4812fd]

+ 「東京都町田市」の郵便番号の一覧を検索しなさい。
+ 「東京都町田市玉川学園」の郵便番号を検索しなさい。
+ 旧郵便番号が「132」だった地区の住所を検索しなさい。
+ 町字名（addr）が、「霞が関」（または、自分の好きな地名なら何でもいい）を含む地区の一覧を検索しなさい。
+ 住所のどこかに「神田」の入っている地名の一覧を検索しなさい。

- 問題を解いたら、解答のSQLと出力結果を、私にメールしてください。
- 長い出力結果は、最初の1行、最後の1行、行数でいいです。
 hi-shiozawa＠engs.tamagawa.ac.jp
-- ↑ ＠マークを半角文字（@）にしないと送れません。

タイムスタンプを変更しない