第2日後半 データベースとSQL実習

目次

データベースの準備

「SQLの窓」のインストール

  1. 「SQLの窓」をダウンロードする。
  2. 東京の郵便番号データをダウンロードし、Excelで内容を確認する。
  3. 「SQLの窓」展開したフォルダの中の db.html をダブルクリックして起動する。
    • winofsql15 の下の db.html
  4. 「参照...」ボタンを押して、データベースとして ziptokyo.xls を読み込む。
  5. 「select *」ボタンを押して、データベースの内容を確認する。

Accessでの方法

テーブルの設計

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

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

旧番号新番号都道府県名市区郡町村名町村字名
0600600041北海道札幌市中央区大通東
1541540002東京都世田谷区下馬
4684680039愛知県名古屋市天白区西入町
907189071801沖縄県八重山郡与那国町与那国

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

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

SQLによるテーブル操作

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

テーブルの作成(省略)

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

テーブルの削除(省略)

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

データを表示する - SELECT文

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

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

データの検索

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

データを検索する - WHERE句

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

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

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

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

ちょっと高度な検索

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

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

データの挿入 - INSERT文

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

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

課題

  1. 「東京都町田市」の郵便番号の一覧を検索しなさい。
  2. 「東京都町田市玉川学園」の郵便番号を検索しなさい。
  3. 旧郵便番号が「132」だった地区の住所を検索しなさい。
  4. 町字名(addr)が、「上野」(または、自分の好きな地名なら何でもいい)を含む地区の一覧を検索しなさい。
  5. 住所のどこかに「日本橋」の入っている地名の一覧を検索しなさい。

トップ   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS