第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;
    • 「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