第1日 データベースと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;
    • 「SQLの窓」では
      select newzip,pref from [zipcode$];
      とすること。
  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 '日本橋%' ;

課題1

以下の検索をするSQL文と、検索結果(長い場合は抜粋)を求めなさい。

  1. 「東京都町田市」の郵便番号の一覧を検索しなさい。
  2. 「東京都町田市玉川学園」の郵便番号を検索しなさい。
  3. 旧郵便番号が「132」だった地区の住所を検索しなさい。
  4. 町字名(addr)に、自分の好きな文字列が入っている地名の一覧を検索しなさい。
  5. 住所のどこかに「城」の入っている地名の一覧を検索しなさい。

データの挿入 - INSERT文

テーブルにデータ(レコード)を作成するには 「インサート(INSERT)文」を使う。

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

insert into zipcode
  (oldzip,newzip,pref,city,addr)
 values('060','0600041','北海道','札幌市中央区','大通東');

データの削除 - DELETE文

テーブルにデータ(レコード)を削除するには 「デリート(DELETE)文」を使う。

【基本構文】 delete from テーブル名 where 条件式;

データの更新 - UPDATE文

データ(レコード)を一括更新するには 「アップデート(UPDATE)文」を使う。

【基本構文】 update テーブル名 set フィールド名=値 where 条件式;

課題2

  1. INSERT文でデータを挿入して「商品」表を完成させる。下記は既に入っているシャンプーの例である。全部挿入したら select * でテーブル全体を表示させる。
    insert into [商品$]
    (商品コード,商品名,単価)
    values('A5023','シャンプー',500);
  2. INSERT文でデータを挿入して「販売」表を完成させる。全部挿入したら select * でテーブル全体を表示させる。

テーブルの結合(結合演算)

データベースを結合するJOIN演算は、where句で複数のテーブルの結合するフィールドを = で並べることでできる。

【基本構文】 select フィールド名 from テーブル名1,テーブル名2,... where テーブル名1.フィールド名=テーブル名2.フィールド名,... ;

select 商品名,販売数量 from [商品$],[販売$]
where [商品$].商品コード=[販売$].商品コード

selectのフィールド名の指定が、どのテーブルのものか紛らわしくなるときは(特に「SQLの窓」はバカなので)、「テーブル名.フィールド名」と指定しなければならない。

select [商品$].商品名コード,単価 from [商品$],[販売$]
where [商品$].商品コード=[販売$].商品コード

課題3

  1. 下記のSQL文を実行して、結合演算について理解しなさい。
    select [商品$].商品コード,商品名,販売数量 from [商品$],[販売$]
    where [商品$].商品コード=[販売$].商品コード
  2. 教科書問7のSQLを「SQLの窓」のために書き直すと以下のようになる。実行してなぜこのようになるのか動作を理解しなさい。
    select [商品$].商品コード,SUM(単価*販売数量)
    from [商品$],[販売$]
    where [商品$].商品コード=[販売$].商品コード
    group by [商品$].商品コード
    order by SUM(単価*販売数量)
  3. 上記を応用して、売上を得意先別にまとめるSQLを作成して検索しなさい。

課題の提出について


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