データベースの基本となる概要を以下に示す。この内容のコースで、ｅラーニングコンテンツを作成予定 【データベース管理】 コンピュータで行われる基本的なデータに対する処理は、次の 4 種類です。新しいデータを追加する。 既存のデータを探索する。違うデータに変更する。要らなくなったデータを削除する。 各システムごとに障害対策も含めて、正確にこのようなデータ処理のプログラムを作ることは大変なこと です。そこでこのデータ処理のプログラムを毎回作らなくても、呼び出して各処理を実行することが可能 なシステムが作られました。それがデータベース管理システムへと発展してきました。 【データベースモデル】 追加、変更、探索、削除などの汎用的な命令が使えて、障害対策も含む機能であるデータベース管理 システムは、階層モデル、ネットワークモデル、関係（リレーショナル）モデルに大別できます。 そして現在最も数多く利用されているのが関係（リレーショナル）モデルです。
このモデルのデータベース管理システムとしては、oracle, sql server, db2, postgresql, mysql などが有 名です。そしてこれに使われる操作用の言語が SQL です。 【リレーショナルデータベースのイメージ】 リレーショナルデータベースで使う 2 次元の表形式は、例えば次のようなイメージです。 破線の部分が行で、表は行の集合で構成されます。そして 1 行の情報は、データの特徴を表現する各 データ型の列の集合で成り立ちます。なおリレーショナル用語では列の各情報を「属性」、行を「タプル」 と呼びます。一般に１行が現実世界における一つのまとまったデータになるように表を作り、複数の表の 関連を定義して管理します。具体的には表の名前とそれを構成する列の名前で、SQL によって 操作します。なお、行をレコードと呼ぶことがあります。 【データベース操作 の SELECT で 選択した列と行を得る】 次の表が「memberTbl」の名前で存在する場合、「memberID」、「lastName」、「firstName」・・が列の名 前です。
この表から、memberID が s102035 である行の lastName と firstName を取得する SQL は次の表現です。 SELECT firstName , lastName FROM memberTbl WHRER memberID = ’s102035’ この操作の実行で得られるのは次の情報です。
ここで memberID ように、表の中から『ある 1 行』を特定することができる列を主キーと呼びます。 また、「次郎」や「山田」のように、行と列が交差した値が格納される個々の部分をフィールドと呼びます。 なお上記表のイメージだけでは判断できませんが、列の中で、ほかの表との関連付けのために使用さ れる列は外部キーと呼びます。
【SQL とは】
計算表はシートで全体が見える状態で扱いますが、データベースは扱うデータ量が膨大で 全体が見えない状態でも操作できるようになっています。
RDBMS（Relational Database Management System：リレーショナルデータベース管理システム）は一般 的に複数のデータベースを管理できます。そして１つのデータベースの中に複数の表があ って、その表が関連し合って動作します。
RDBMS を経由してデータベースの操作を行うための言語が SQL（Structured Query Language：構造化 問い合わせ言語）です。SQL の命令は、データ定義機能（DDL：Data Definition Language）、データ操 作機能（DML：Data Manipulation Language）、データ制御機能（DCL：Data Control Language）の三つ の機能に分類されます。
【操作の分類】
RDBMS（Relational Database Management System）で使う SQL（Structured Query Language：構造化問 い合わせ言語）の操作は次のように３つに分類されます。
機能分類 操作 対応する命令
DDL：Data Definition Language
表や視点を定義し
て作成 CREATE TABLE、CREATE VIEW 表や視点を削除 DROP TABLE、DROP VIEW 列名変更 ALTER TABLE ～ RENME ～TO～ さまざまな変更 ALTER TABLE ～
DML：Data Manipulation Language
照会,問い合わせ SELECT ～ FROM
挿入 INSERT INTO ～ WHERE ～ 変更、更新 UPDATE ～ SET ～=～ WHERE ～ 削除 DELETE FROM ～ WHERE ～ DCL：Data Control Language 権限を与える GRANT PRIVILEGES ON～ FROM
権限を取り上げる REVOKE PRIVILEGES ON～ FROM
【データベースを作って操作するユーザを指定し、操作の接続をする】
RDBMS（Relational Database Management System：リレーショナルデータベース管理システム）では、一 つのデータベースで、複数の表を関連付けて作成します。一つの RDBMS で複数のデータベース名を 管理できます。まずデータベースに名前を付けて作成し、それを扱えるユーザを作ります。
システムにより操作方法に違いがありますが、例えば elearnの名前のデータベース作成する場合 であれば、次のようなコマンド操作です。
CREATE DATABASE elearn;
そして次にユーザを作成して、このデータベースをアクセスできる権限を与えます。
例えば、elearn のデータベースに、192.168.～の IP アドレスのホストから接続して、無制限に 操作可能な権限を'e_admin'のユーザに与える場合の SQL の DCL は次のような表現です。 GRANT ALL PRIVILEGES ON elearn.* TO 'e_admin'@'192.168.%.%';
一般にデータベースシステムはコンソール用のアクセスツールが用意されています。
oracle であれば sqlplus コマンドです。Mysql であれば『92.168.0.77』のサーバにある『elearn』のデータ ベースへ『e_admin』のユーザが接続し SQL で操作するためのコマンドは次のように実行します。 mysql --user=e_admin --host=ホスト IP アドレスなど --password=○○○ elearn
【表を作ってレコードを追加する】
一般にコンソール用のアクセスツールで、表の作成などが可能です。
上記のような表を作る場合、まず、次のような SQL の DDL で表の構造を作ります。 CREATE TABLE memberTbl(
memberID CHAR(8) NOT NULL, -- （8 文字型） lastName CHAR(10), -- （10 文字型） firstName CHAR(10), city CHAR(10), address CHAR(20), tel CHAR(15), mail CHAR(20),
PRIMARY KEY (memberID) );
ここで、CAHR は文字のデータ型を意味してカッコの中が文字数です。
また、NOT NULL の制約で、データをセットしなければ使えない指定をしています。 なお、PRIMARY KEY (memberID)により主キーを memberID に設定しています。 そして次のような SQL の DML でデータを挿入します。
INSERT INTO memberTbl (memberID,lastName,firstName,city,address,tel,mail,passwd)
VALUES ('s102031','山田','太郎','tokyo','新宿区西新宿 2-8-1','03-5321-1111','tarou@mail2.com');
【表にレコードを追加する。またレコードの削除紹介】
上記のような「memberTbl」の名前の表に行を追加して次の表のようにしたい。
この追加で使うＤＭＬのＳＱＬ表現は次のようになります。 INSERT INTO memberTbl VALUES
('s102933','斉藤','太郎','名古県','佐賀市城内 1 丁目 1-59','0952-24-2111','tarou2@mail.com'), ('s102934','高橋','花子','岩手県','盛岡市内丸 10 番 1 号','019-651-3111','hanako@mail2.com'); なお、memberID が’s102031’の行を削除するＤＭＬのＳＱＬ表現は次のようになります。
DELETE FROM memberTbl WHERE memberID = ’s102031’; 【表の一部を変更する。 列の追加や削除】
上記のような「memberTbl」の名前の表に、passwd の列を追加して次の表のようにしたい。
この列の追加で使う DDL のＳＱＬ表現は次のようになります。（属性は 255 文字までの可変長） ALTER TABLE memberTbl ADD (passwd VARCHAR(255));
上記の s102031 の passwd を abc123 に変更する DML のＳＱＬ表現は次のようになります。 UPDATE memberTbl SET passwd='abc123' WHERE memberID = 's102031'; なお、この追加した列を削除する DDL のＳＱＬ表現は次のようになります。
ALTER TABLE memberTbl DROP passwd;
ＳＱＬのコマンドは セミコロン『；』で完結します。
【表の操作例】 memberTbl の名前の表が次のようにあります。
① 重複する行を除いて lastName を表示する SQL は次のようなります。→ SELECT DISTINCT lastName FROM memberTbl;
② lastName が'山田'で、city が'tokyo'の memberID を得る SQL は 次の 2 通りできます。→
SELECT memberID FROM memberTbl WHERE lastName = '山田' AND city = 'tokyo'; SELECT memberID FROM memberTbl WHERE
NOT(lastName <> '山田' OR city <> 'tokyo'); ③ firstName のデータの最後が'子'で終わる名前の memberID と
firstName を得る。 →
SELECT memberID, firstName FROM memberTbl WHERE firstName LIKE '%子'; SELECT memberID, firstName FROM memberTbl WHERE firstName LIKE '__子'; ④ 's102031'から's102035'の範囲内において、
memberID, lastName , firstName を得る。→ SELECT memberID, lastName , firstName FROM memberTbl
WHERE memberID BETWEEN 's102031' AND 's102035'; ⑤ カッコ()内に列挙したデータに一致するもの得るデータを得る。 → SELECT memberID, lastName , firstName FROM memberTbl
WHERE memberID IN ('s102031','s102934','s102035'); ⑥ 上記の表示で、memberID のデータをキーにして昇順で表示する → SELECT memberID, lastName , firstName FROM memberTbl
WHERE memberID IN ('s102031','s102934','s102035') ORDER BY memberID ASC; ⑦ 上記の表示で、memberID のデータをキーにして降順で表示する →
SELECT memberID, lastName , firstName FROM memberTbl
WHERE memberID IN ('s102031','s102934','s102035') ORDER BY memberID DESC; ⑧ レコードの列を数える。なお、実行結果の列名を別名で表示する。 →
SELECT count(*) as 'レコード件数' FROM memberTbl;
⑨ グループごとのカウント （lastName で集計） → SELECT lastName ,count(lastName) as '人数' FROM memberTbl
GROUP BY lastName;
⑩ グループごとの処理結果に対しする条件抽出 （lastName 集計が 2 以上）→ SELECT lastName ,count(lastName) as '人数' FROM memberTbl
【表を作ってレコードを追加する。 列のデータ型紹介】 一般にコンソール用のアクセスツールで、表の作成などが可能です。
上記のような表を作る場合、まず、次のような SQL の DDL で表の構造を作ります。 CREATE TABLE planTbl (
planID CHAR(5) NOT NULL, -- ID URI CHAR(15), -- URI
pageOrder REAL DEFAULT 1, -- この数が小さい順に利用 prevID CHAR(5), -- 前にクリアすべき planID level INTEGER DEFAULT 0, -- 難易度
title VARCHAR(128), -- タイトル PRIMARY KEY (planID)
);
ここで、PRIMARY KEY (planID)により主キーを planID に設定しています。 DEFAULT の制約で、データセットしない場合の初期値を設定しています。
CHAR、REAL、INTEGER、VARCHAR のデータ型はそれぞれ、文字、小数点可能な数値、整数、 可変長文字列です。そして次のような SQL の DML でデータを挿入します。
INSERT INTO planTbl (planID ,URI ,title)
VALUES ('m1011','m101.html','データベース管理');
【表にレコードを追加して、表示操作】
上記のような「planTbl」の名前の表に、次の行を追加する SQL を実行する。 INSERT INTO planTbl (planID ,URI ,pageOrder, prevID, title) VALUES
('m1012','m101.html',2,'m1011','データベース管理'), ('m1021','m102.html',3,'m1012','データベースモデル'),
('m1031','m103.html',4,'m1021','リレーショナルデータベースのイメージ'); その後で、全てを列、行を表示するＤＭＬのＳＱＬ表現は次のようになります。
SELECT * FROM planTbl; これで得られる表示は次のようになります。
上記のような planTbl の名前の表がある場合で、集合関数を使った例を示します。 ① 最大値を得る max 関数を使って pageOrder が最も大きい値を求めます。
SELECT max(pageOrder) FROM planTbl; →
② pageOrder が最も大きい値の行を表示させる。（サブクエリーと呼ばれる手法）
SELECT * FROM planTbl WHERE pageOrder IN (SELECT max(pageOrder) FROM planTbl);
③ PageOrder の平均を求めます。 →
SELECT AVG(pageOrder) FROM planTbl;
④ 上記クエリーをサブクエリーに使って、平均を超える列を求める。
【既存表を参照する表を作って、レコードを追加する】
上記のような memberTbl と planTbl の表を使って次の logTbl の名前の表を作る例です。 CREATE TABLE logTbl (
memberID CHAR(8) NOT NULL,-- memberID の ID time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, planID CHAR(5) NOT NULL, -- planTbl の ID clerFlag INTEGER,-- 1:クリア, 0 が未クリア
FOREIGN KEY (memberID) REFERENCES memberTbl(memberID), FOREIGN KEY (planID) REFERENCES planTbl(planID),
PRIMARY KEY (memberID, time)
); -- FOREIGN の指定で、既存の表の列を参照させています。 INSERT INTO logTbl (memberID,time,planID,clerFlag) VALUES ('s102031','2013-10-21 10:10:00','m1011',1),
('s102035','2013-10-21 10:30:00','m1011',1), ('s102099','2013-10-23 11:45:00','m1012',1), ('s102031','2013-10-23 11:50:00','m1021',1); 【上記３つの表(memberTbl と planTbl と ogTbl)で、操作する例】 ① logTbl の表と、memberTbl の表を、
logTbl.memberID = memberTbl.memberID の 条件で「内部結合」して表示すると右の表が得られます。
内部結合では表の他方で存在しない列がある行を出しません。このＳＱＬを３通り以下に示す。 SELECT planID, time,logTbl.memberID, firstName FROM
logTbl,memberTbl WHERE logTbl.memberID = memberTbl.memberID; SELECT planID, time,logTbl.memberID, firstName FROM
logTbl INNER JOIN memberTbl ON logTbl.memberID = memberTbl.memberID; SELECT planID, time,logTbl.memberID, firstName FROM
memberTbl INNER JOIN logTbl ON logTbl.memberID = memberTbl.memberID; なお、INNER は省略可能です。
② planTbl の表と、logTbl の表と、 memberTbl の表をの「内部結合」例です
logTbl.memberID = memberTbl.memberID と planTbl.planID = logTbl.planID の結合条件にした例で、上記と下記アンダーラインが同じで、カッコで 上記表を得て、それに planTbl の title を結合するような記述です。下に示すようにカッコを使います。
SELECT planTbl.planID, title, time,logTbl.memberID, firstName FROM planTbl
INNER JOIN( logTbl INNER JOIN memberTbl ON logTbl.memberID = memberTbl.memberID) ON planTbl.planID = logTbl.planID;
③ logTbl の表と、memberTbl の表を、左に外部結合する例です。（OUTER の記述は省略できます） SELECT planID, time,logTbl.memberID, firstName
FROM logTbl LEFT OUTER JOIN memberTbl ON logTbl.memberID = memberTbl.memberID;
