第101話 経営情報システム⑦ SQLは結構楽しかった
少しだけDBの続きです。今回はDB設計の考え方について始めます。
DBを設計する場合の重要な考え方を、3層スキーマという。
●3層スキーマ → 外部・概念・内部
DBを設計する際、外部スキーマ、内部スキーマ、概念スキーマという三つの階層に分けて設計を行うと、各スキーマで表現されるデータの独立性が維持できるので、帳票のスタイルに変更があった場合でも、それぞれの変化が他の階層に設計に影響を与えないという効果がある。
・外部スキーマとは、特定の利用者やアプリケーションソフトウエアで利用する観点から表現されるデータ構造。具体的にはデータの入力画面や印刷された帳票などで表現される。つまり、アウトプット側の発想だ。
・概念スキーマとは、DB化したいデータを、DBMSのデータモデルに従って記述したもの。データを正規化した表の集まり。
・内部スキーマとは、データの物理的な格納方法を定義したもの。ファイル名や格納位置、領域サイズなどを指定する。インプット側。
●外部スキーマ → OUTPUT
●概念スキーマ → 正規化された表の集まり
●内部スキーマ → INPUT
このようにDBを構築するにあたり、3層スキーマと呼ばれる仕組みがある。このようにしておけばDBからデータを取り出す際、ルール化された指図方法をすることでアウトプットも正確になるし、データの修正が頻発しようともDBとして利用できるというわけだ。
次の話題。次はSQL。
SQLとは、Structured Query Language の略で、RDB(リレーショナルデータベース)の作成や操作のための標準的な言語である。もともとはアメリカIBM社が開発したもので、国際的にはISO、日本ではJISが制定している。
つまりは、DBへ指図するときの専用言語、みたいなイメージだろうか。
1) データベース言語
DBやテーブルなどの作成、削除、データの検索や更新などを行う言語。この代表格がSQLである。
①データ定義言語(DDL: Date Definition Language )
DBの構造や容量、整合性、管理方法などの定義を行う。RDBでは、DBやテーブルの編成、変更、削除、権原の設定を行う。
②データ操作言語(DML: Date Mainpulation Language )
DBを操作するための言語。DBへのデータの登録、更新、削除、検索などの操作を行う。
●SQLのDDL → CRAETE(生成)、ALTER(変更)、DROP(消去)
●SQLのDML → SELECT(参照)、UPDATE(更新)、INSERT(挿入)、DELETE(削除)
③SELECTの基本書式
●基本書式 → SELECT<列名> FROM<表名> WHERE<条件式>
ちなみに、【SELECT】は射影で、どの列を指定するのかを示し、【WHERE】は選択で、どの行を指定知るのかを示す。
たとえば、「社員表」という表に社員コードと所属コードと年齢が、「部署表」という表に所属コードと所属名が書かれた表があるとする。
1)社員表から社員名を抽出する場合のSELECT文は
SELECT 社員名 FROM 社員表
2)社員表から社員名と所属コードを抽出するSELECT文は
SELECT 社員名,所属コード FROM 社員表
※複数の列がある場合には「,」で区切る
3)社員表から全ての行、列を抽出する場合
SELECT * FROM 社員表
4)社員表から、所属コードが14のデータを抽出する場合
SELECT * FROM 社員表 WHERE 所属コード=14
5)社員表から、年齢が30以上の社員名と年齢を抽出する場合
SELECT 社員名,年齢 FROM 社員表 WHERE 年齢>=30
6)社員表から、年齢が30以上でかつ所属コードが14であるデータを抽出する場合
SELECT * FROM 社員表 WHERE 年齢>=30 AND 所属コード=14
7) 社員表から年齢が20以上30以下のデータを抽出する場合
SELECT * FROM 社員表 WHERE 年齢 BETWEEN 20 AND 30
※「BETWEEN」は、~以上~以下の意
8)社員表から社員名の1文字目が“田”で始まるデータを抽出する場合
SELECT * FROM 社員表 WHERE 社員名 LIKE’田%’
9)社員表と部署表から、所属コードが12の社員名と所属名を抽出する場合
SELECT 社員表.社員名,部署表.所属名 FROM 社員表,部署表
WHERE 社員表.所属コード=部署表.所属コード
AND 社員表.所属コード=12
具体的な社員表と部署表とがあればもっと分かりやすかったかもね。
ちなみに、WHERE句で指定できる抽出条件はいろいろありまして、
「=」であれば、等しいデータだし、「条件1 AND 条件2」なら条件1と条件2をともに満たすデータだし。文章に表現しにくいからすべてを載せることはしないけれど、なかなかパズルみたいで楽しかった。
なお、「BETWEEN 値 AND 値」は、例えば、「BETWEEN 20 AND 30」なら「20以上30以下」を表すが、これは、「WHERE A>=20 AND A<=30」と書いても同じ列を抽出することになる。
④グループ化
グループ化したい場合には「GROUP BY」句を指定する。たとえば、先ほどの社員表と部署表を用いるなら、例えば、所属コードでグループ化しつつ、グループ化した中で平均年齢を抽出することも可能だ。この場合は集合関数と呼ばれるものをSELECTにぶっこんであげるとよい。
10)社員表から所属コードでグループ化し、グループ内の平均年齢を抽出する場合
SELECT 所属コード,AVG(年齢) FROM 社員表 GROUP BY 所属コード
※このときの「AVG」が集合関数で平均値を求めるもの。(年齢)は何の平均を求めるのかを明示する
なお、グループ化を行う場合には、SELECTに指定する列名は必ず「グループ化されている列名」でなければならない。
つまり、GROUP BY句で指定した列名をSELECTでも指定してあげることが必要だということだ。
●GROUP BY句 → SELECTで指定した列名とGROP BYで指定する列名は同じ
⑤HAVING
GROUP BY句はグループ化を、HAVING句はグループ化したものに対する選択を意味する。だからHAVING句は単独で使われることがなく、当然に、GROUP BY句とセットで使われることになる。
11)社員表から所属コードでグループ化して件数が1件を超える所属コードおよび平均年齢を抽出・算出する場合
SELECT 所属コード,AVG(年齢) FROM 社員表 GROUP BY 所属コード HAVING COUNT(*)>1
※「COUNT」も集合関数であり、行数を数えろという意味
⑥ORDER BY
RDBにおける表は「行」の集合体であり、各行がどのような順序で抽出されているかは全く保証されていない。ってか、そもそもそんな概念がない。
「行」はヨコであり、行の集合によって「列(タテ)」が出来るのだが、この「ORDER BY」句を使用することで、昇順・降順に並び替えることが出来る。
●ORDER BY句の基本構文 → ORDER BY<列名> [ASCまたはDESC]
※ASCは昇順、DESCは降順を表し、省略されている倍にはASCが指定されたとみなされる
12)社員表の年齢を降順で並び替え、社員表から社員名と年齢を抽出する場合
SELECT 社員名,年齢 FROM 社員表 ORDER BY 年齢 DESC
実際の本試験ではつぎのような問題が出題されたことがある。
H23改題
下表(省略)は、2011年1月30日から同年2月20日までの販売履歴表である。この表に対して次のSQL文を実行した場合、どのような結果を得るか。
SELECT 担当者コード,製品名,SUM(個数)
FROM 販売履歴表
GROUP BY 担当者コード,製品名
HAVING SUM(個数)>=3
実際の問題は、抽出された正しい表を選択させる問題だったと思う。
先の本試験でも出題されたがちゃんと得点できた。
続く。