eラーニング資料 e ラーニングの制作目標 データベース編 41 ページデータベースの基本となる概要を以下に示す この内容のコースで eラーニングコンテンツを作成予定 データベース管理 コンピュータで行われる基本的なデータに対する処理は 次の 4 種類です 新しいデータを追加する 既存のデータを探索

eラーニング資料 e ラーニングの制作目標 データベース編 41 ページデータベースの基本となる概要を以下に示す この内容のコースで eラーニングコンテンツを作成予定 データベース管理 コンピュータで行われる基本的なデータに対する処理は 次の 4 種類です 新しいデータを追加する 既存のデータを探索する 違うデータに変更する 要らなくなったデータを削除する 各システムごとに障害対策も含めて 正確にこのようなデータ処理のプログラムを作ることは大変なことです そこでこのデータ処理のプログラムを毎回作らなくても 呼び出して各処理を実行することが可能なシステムが作られました それがデータベース管理システムへと発展してきました データベースモデル 追加 変更 探索 削除などの汎用的な命令が使えて 障害対策も含む機能であるデータベース管理システムは 階層モデル ネットワークモデル 関係 ( リレーショナル ) モデルに大別できます そして現在最も数多く利用されているのが関係 ( リレーショナル ) モデルです このモデルのデータベース管理システムとしては oracle, sql server, db2, postgresql, mysql などが有名です そしてこれに使われる操作用の言語が SQL です リレーショナルデータベースのイメージ リレーショナルデータベースで使う 2 次元の表形式は 例えば次のようなイメージです 破線の部分が行で 表は行の集合で構成されます そして 1 行の情報は データの特徴を表現する各データ型の列の集合で成り立ちます なおリレーショナル用語では列の各情報を 属性 行を タプル と呼びます 一般に1 行が現実世界における一つのまとまったデータになるように表を作り 複数の表の関連を定義して管理します 具体的には表の名前とそれを構成する列の名前で SQL によって操作します なお 行をレコードと呼ぶことがあります データベース操作の SELECT で選択した列と行を得る 次の表が membertbl の名前で存在する場合 memberid lastname firstname が列の名前です この表から memberid が s102035 である行の lastname と firstname を取得する SQL は次の表現です SELECT firstname, lastname FROM membertbl WHRER memberid = s102035 この操作の実行で得られるのは次の情報です ここで memberid ように 表の中から ある 1 行 を特定することができる列を主キーと呼びます また 次郎 や 山田 のように 行と列が交差した値が格納される個々の部分をフィールドと呼びます なお上記表のイメージだけでは判断できませんが 列の中で ほかの表との関連付けのために使用される列は外部キーと呼びます

eラーニング資料 e ラーニングの制作目標 データベース編 42 ページ SQL とは 計算表はシートで全体が見える状態で扱いますが データベースは扱うデータ量が膨大で全体が見えない状態でも操作できるようになっています RDBMS(Relational Database Management System: リレーショナルデータベース管理システム ) は一般的に複数のデータベースを管理できます そして1つのデータベースの中に複数の表があって その表が関連し合って動作します 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: 構造化問 い合わせ言語 ) の操作は次のように 3 つに分類されます 機能分類操作対応する命令 DDL:Data Definition Language DML:Data Manipulation Language DCL:Data Control Language 表や視点を定義して作成 表や視点を削除 列名変更 さまざまな変更 照会, 問い合わせ 挿入 変更 更新 削除 権限を与える CREATE TABLE CREATE VIEW DROP TABLE DROP VIEW ALTER TABLE ~ RENME ~TO~ ALTER TABLE ~ SELECT ~ FROM INSERT INTO ~ WHERE ~ UPDATE ~ SET ~=~ WHERE ~ DELETE FROM ~ WHERE ~ 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

e ラーニング資料 e ラーニングの制作目標 データベース編 43 ページ 表を作ってレコードを追加する 一般にコンソール用のアクセスツールで 表の作成などが可能です 上記のような表を作る場合 まず 次のような 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'); 表にレコードを追加する またレコードの削除紹介 SQL のコマンドは セミコロン ; で完結します 上記のような membertbl の名前の表に行を追加して次の表のようにしたい この追加で使う DML の SQL 表現は次のようになります 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 の行を削除する DML の SQL 表現は次のようになります DELETE FROM membertbl WHERE memberid = s102031 ; 表の一部を変更する 列の追加や削除 上記のような membertbl の名前の表に passwd の列を追加して次の表のようにしたい この列の追加で使う DDL の SQL 表現は次のようになります ( 属性は 255 文字までの可変長 ) ALTER TABLE membertbl ADD (passwd VARCHAR(255)); 上記の s102031 の passwd を abc123 に変更する DML の SQL 表現は次のようになります UPDATE membertbl SET passwd='abc123' WHERE memberid = 's102031'; なお この追加した列を削除する DDL の SQL 表現は次のようになります ALTER TABLE membertbl DROP passwd;

e ラーニング資料 e ラーニングの制作目標 データベース編 44 ページ 表の操作例 membertbl の名前の表が次のようにあります 1 重複する行を除いて lastname を表示する SQL は次のようなります SELECT DISTINCT lastname FROM membertbl; 2 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'); 3 firstname のデータの最後が ' 子 ' で終わる名前の memberid と firstname を得る SELECT memberid, firstname FROM membertbl WHERE firstname LIKE '% 子 '; SELECT memberid, firstname FROM membertbl WHERE firstname LIKE ' 子 '; 4 's102031' から 's102035' の範囲内において memberid, lastname, firstname を得る WHERE memberid BETWEEN 's102031' AND 's102035'; 5 カッコ () 内に列挙したデータに一致するもの得るデータを得る WHERE memberid IN ('s102031','s102934','s102035'); 6 上記の表示で memberid のデータをキーにして昇順で表示する WHERE memberid IN ('s102031','s102934','s102035') ORDER BY memberid ASC; 7 上記の表示で memberid のデータをキーにして降順で表示する WHERE memberid IN ('s102031','s102934','s102035') ORDER BY memberid DESC; 8 レコードの列を数える なお 実行結果の列名を別名で表示する SELECT count(*) as ' レコード件数 ' FROM membertbl; 9 グループごとのカウント (lastname で集計 ) SELECT lastname,count(lastname) as ' 人数 ' FROM membertbl GROUP BY lastname; 10 グループごとの処理結果に対しする条件抽出 (lastname 集計が 2 以上 ) SELECT lastname,count(lastname) as ' 人数 ' FROM membertbl GROUP BY lastname HAVING count(lastname) >= 2;

e ラーニング資料 e ラーニングの制作目標 データベース編 45 ページ 表を作ってレコードを追加する 列のデータ型紹介 一般にコンソール用のアクセスツールで 表の作成などが可能です 上記のような表を作る場合 まず 次のような SQL の DDL で表の構造を作ります CREATE TABLE plantbl ( planid CHAR(5) NOT NULL, URI CHAR(15), pageorder REAL DEFAULT 1, previd CHAR(5), level INTEGER DEFAULT 0, title VARCHAR(128), PRIMARY KEY (planid) ); -- ID -- URI -- この数が小さい順に利用 -- 前にクリアすべき 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',' リレーショナルデータベースのイメージ '); その後で 全てを列 行を表示する DML の SQL 表現は次のようになります SELECT * FROM plantbl; これで得られる表示は次のようになります 上記のような plantbl の名前の表がある場合で 集合関数を使った例を示します 1 最大値を得る max 関数を使って pageorder が最も大きい値を求めます SELECT max(pageorder) FROM plantbl; 2 pageorder が最も大きい値の行を表示させる ( サブクエリーと呼ばれる手法 ) SELECT * FROM plantbl WHERE pageorder IN (SELECT max(pageorder) FROM plantbl); 3 PageOrder の平均を求めます SELECT AVG(pageOrder) FROM plantbl; 4 上記クエリーをサブクエリーに使って 平均を超える列を求める SELECT * FROM plantbl WHERE pageorder > (SELECT AVG(pageOrder) FROM plantbl);

e ラーニング資料 e ラーニングの制作目標 データベース編 46 ページ 既存表を参照する表を作って レコードを追加する 上記のような 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); 上記 3 つの表 (membertbl と plantbl と ogtbl) で 操作する例 1 logtbl の表と membertbl の表を logtbl.memberid = membertbl.memberid の 条件で 内部結合 して表示すると右の表が得られます 内部結合では表の他方で存在しない列がある行を出しません この SQL を 3 通り以下に示す 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 は省略可能です 2 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; 3 logtbl の表と membertbl の表を 左に外部結合する例です (OUTER の記述は省略できます ) SELECT planid, time,logtbl.memberid, firstname FROM logtbl LEFT OUTER JOIN membertbl ON logtbl.memberid = membertbl.memberid; 左の logtbl に在る s102099 は membertbl に存在しませんが 左結合なので表示しています