Web教材一覧データベース

RDBの基本操作とSQL

このシリーズの目次「データベース」で示した,このシリーズで用いるデータをExcelファイルにした「教材データ一覧」を参照してください。

学習のポイント

データベースの管理をするソフトウェアのことをDBMS(Database Management System)といいます。現在最も普及しているDBMSはRDB(リレーショナル・データベース)です。先にデータの正規化を学習しましたが,正規化したファイル群をRDBのテーブル(表)形式でコンピュータに実装します。その実装方法は省略します。
 ここでは,必要とする情報が必要とする項目を持つテーブルを作り出すには,どのテーブルをどのように組み合わせればよいかについて考えます。それを通して,RDBの基本操作(射影,選択,結合)とSQLのSELECT文の概要を理解します。

キーワード

DBMS,RDB,射影,選択,結合,SQL

過去問題:「SQL」( sql.html


RDBの基本操作

RDBは,データを2次元のテーブルとして取り扱います。その基本操作には,射影,選択,結合の3つがあります。

具体的なテーブルで例示すると次のようになります。

射影操作
射影とは,あるテーブルから指定した(フィールド,項目)を取り出して,新しいテーブルを作成する操作です。例えば,売上表から「得意先コード」と「数量」の列を取り出すような操作です。
選択操作
選択とは,与えた条件に合致した(レコード)だけを取り出して,新しいテーブルを作成する操作です。上図では,条件「得意先コード=500」で選択した操作です。
結合操作
結合とは,2つのテーブルを共通するキーにより関係づけて,新しい1つのテーブルにする操作です。下図は,得意先コードを共通のキーとして結合したものです。

基本操作の組合せ

射影,選択,結合の基本操作を組み合わせることにより,複雑な処理をすることができます。下図では,
  選択:売上表から月日が1004から1008までのレコードを選択して,
  結合:得意先コードで得意先表と結合することにより得意先名を得て,
     商品コードで商品表とと結合することにより商品名と単価を得て,
  射影:月日,得意先名,商品名,数量,単価の項目を取り出して,
新しい表を作成しています。

練習問題

売上表から月日が1004から1008までに商品区分がパソコンであるレコードを選択して,月日,得意先名,商品名,数量を項目とするテーブルを作成するのに,どのような操作が必要になるかを示して,結果のテーブルを示しなさい

SQLの概要

SQLは,RDBを作成したり操作する言語です。国際規格になっており,ほとんどのRDBのDBMS(データベース管理システム)はSQLに準拠しています。
 SQLの機能は広いのですが,ここでは上記の基本操作に関係するSELECT文に限定し,しかも,その基本的な部分だけを学習します。なお,SELECT文に関する演習問題は,パソコンのデータベースシステムであるAccessを用いて,「SELECT文の演習」で行います。

基本形式
     SELECT 射影するフィールド名         何を
      FROM  使用するテーブル名          どこから
      WHERE 結合条件 AND 選択条件;     どのように

射影の例
     SELECT 得意先コード,数量
      FROM  売上表;
選択の例
     SELECT *   (*は「すべてのフィールド」の意味)
      FROM  売上表
      WHERE 得意先コード=500;
結合の例      SELECT 月日,売上表.得意先コード,商品コード,数量,得意先名
      FROM  売上表,得意先表
      WHERE 売上表.得意先コード=得意先表.得意先コード;

組合せの例
     SELECT 月日,得意先名,商品名,単価,数量
      FROM  売上表,得意先表,商品表
      WHERE 売上表.得意先コード=得意先表.得意先コード
       AND  売上表.商品コード=商品表.商品コード
       AND  月日 BETWEEN 1004 AND 1008;

先の「練習問題」をSQLで記述しなさい(解答)

直積(X)

SELECT * FROM 表1,表2
二つの表のすべての組合せになります。
  表1          表2       出力
   品名   メーカー   色 サイズ    品名   メーカー  色 サイズ
   ──── ────   ─ ───    ──── ────  ─ ───
   パソコン レノボ    白  大     パソコン レノボ   白  大
   プリンタ エプソン   黒  中     パソコン レノボ   黒  中
               赤  小     パソコン レノボ   赤  小
                        プリンタ エプソン  白  大
                        プリンタ エプソン  白  大
                        プリンタ エプソン  白  大

副照会(副問合せ)

SELECTを入れ子にすることができます。
  SELECT 列, 列, …
    FROM 表, 表, …
    WHERE 列 = (SELECT ・・・)
とすると、( )の中で得られた結果を用いた操作になります。

副照会により、2つの表をまとめることができます。次のオプション(キーワード、赤字の部分)があります。

UNION(和集合、∪)
ある表の照会結果と,別の表の照会結果を合わせて一つの表にする操作です。
   SELECT 列, 列, … FROM 表1  ・・・結果1
   UNION
   SELECT 列, 列, … FROM 表2  ・・・結果2
(列, 列, … は、個数と順序が同じで、それぞれの列のデータ型が同じか変換可能でなければなりません。)
により、結果1と結果2を合わせ、重複を削除した表になります。
(重複を削除したくないときは、UNION ALL とします。
EXCEPT(差集合、-)
表1にあり、表2にないものを返します。
INTERSECT(共通集合、∩)
表1と表2の両方にあるものについて、重複を削除して(表1の行だけを)返します。

上の3つを表にしました。
  表1 表2  和集合 差集合 共通集合
   A  A   A   B   A
   B  D   B   C
   C      C
          D

CASE式

SQLのCASE式とは、SELECT句やUPDATE句などのSQL文中で「条件分岐処理」を行うのに用いられる命令文です。CASE式を使うことで柔軟性が高まり、簡単に条件分岐ができます。
 WHERE句やHAVING句でも条件を指定してデータの絞り込みを行えますが、出力結果に対して処理を加えるといったことはできません。その点、CASE式では指定した条件を元に何かしらの処理を加えられます。

基本構文

  SELECT 射影カラム
   CASE カラム名        // 以下条件に用いるカラム
    WHEN 条件A THEN 処理A //条件Aを満たす場合に処理Aを行う
    WHEN 条件B THEN 処理B
    ELSE 処理C
   END AS 結果カラム名    // 処理結果に新しいカラム名を設定する
   FROM テーブル名;

  SELECT 氏名, 性別, 年齢
   CASE 年齢     WHEN 年齢 >= 18 THEN "成人" ELSE "未成年"    END AS 年層    FROM 会員表;

集計表の作成

売上明細表を商品別、得意先別の売上集計表を出力する。
           得意先
    商品 A売上個数 B売上個数
     X   100    200
     Y   300    400
     Z   500    600
  SELECT
   商品,
   SUM(CASE 得意先 WHEN 'A' THEN 売上個数 ELSE 0 END) AS A売上個数,
    // Aなら売上個数を集計、A以外は0を集計 集計結果を新項目 "A売上個数" にする
   SUM(CASE 得意先 WHEN 'B' THEN 売上個数 ELSE 0 END) AS B売上個数
  FROM
   売上明細表
  GROUP BY 商品
  ORDER BY 商品;


理解度チェック

第1問

  1. 一つのテーブルから,指定したフィールドだけを取り出して,新しいテーブルを作る操作を「射影」という。
    ○ フィールド=項目=列
  2. 一つのテーブルから,与えた条件に合致するレコードを取り出して,新しいテーブルを作る操作を「選択」という。
    ○ レコード=データ=行
  3. 二つのテーブルを,対応するフィールドにより結びつけて,一つのテーブルにする操作を「結合」という。
    ○ 結合をジョインともいう
  4. SQLは,リレーショナル・データベースをアクセスする言語であり,国際規格になっている。
  5. SQLでは,「射影」「選択」「結合」の操作を記述できるが,それぞれ個別に記述するのであり,これらの機能を組み合わせて,一つの文にすることはできない。
    × SELECT文を見よ
  6. 二つのテーブルを結合するとき,少なくとも一方のテーブルの結合フィールドは,ユニークキーになっているのが通常である。
    ○ ユニークキー=それが決まれば行が決まる=マスタのテーブル
  7. 二つのテーブルに同一名のフィールドがあるとき,どちらのテーブルのフィールドであるかを明示するために「フィールド名.テーブル名」の形式で記述する。
    × 「テーブル名.フィールド名」

第2問

教材データ一覧」の「授業(第3正規形)」を参考にして,次の設問に対する表を作りなさい。

  1. 吉田先生が担当している選択科目について,科目番号,科目名,単位を表にしなさい。
  2. 現在履修中(成績が0)である学生名と科目名を表にしなさい。
  3. 井上一郎が単位を取得した科目の科目番号,科目名,単位,成績を表にしなさい。
  4. 現在履修中の科目を担当している教員の教員名,担当科目名,学生名の表を作りなさい。
  5. 取得した(成績が3以上)の単位の合計が高い順に,学生名,単位合計の表を作りなさい。

本シリーズの目次へ