データベースの作り方

基本用語

レコード

意味を持たせる最小単位の値の集合。 電話機の電話帳機能で言えば1人分のデータ。

フィールド (カラム)

1レコードを構成する1つ1つの項目。電話帳機能で言えば「氏名」， 「カタカナ氏名」，「電話番号」などが該当する。

表 (テーブル)

同じフィールド構成のレコードの集合体。 データベースの基本セットとなる。1台の電話機の電話帳1セットが 表に当たる。テーブル。

現在主に利用されるデータベースはすべてが表形式で格納される。 計算機処理する用にデータを格納するなら，扱うデータをすべて適切な表形式に 格納するための構造設計をする必要がある。

検索しやすいものにする大原則

更新管理しやすいものにする大原則

リレーショナルデータベースへの適合作業

表の作成

一意性を確保し，冗長性をなくした1つ1つの表を table として設計する。 各フィールド(カラム)に名前をつけたものを table とする。 上記の3表をtable化したものを例示する。

表第1列第2列第3列
意味カラム名 意味カラム名意味カラム名
表1学籍番号sid 教員番号kid履修科目semi
表2学籍番号sid 氏名name
表3教員番号kid 氏名nameメイルアドレスemail

各々の表にも名前をつける。ここでは，以下のように命名する。

表1renraku
表2student
表3lecturer

以上の3つの表の構成をまとめると以下のようになる。

表1: renraku(sid, kid, semi)
表2: student(sid, name)
表3: lecturer(kid, name, email)

SQLite3を利用したデータベース作成

本講ではリレーショナルデータベース管理システムとして SQLite3を用いる。 SQLite3は `sqlite3' コマンドを起動することですぐにリレーショナルデータベース 操作が始められる。

他のデータベースは，DBサーバの導入作業が必要で， データはDBサーバ上に起動したDB管理ソフトが独自形式で保存する。

表の定義

SQLite3を利用すると，1つのデータベースファイルに複数の表を格納し， 管理することができる。

ここでは上記の3表を格納管理するデータベースファイルを gakuji.sq3 として，表を定義してみる。

sqlite3 gakuji.sq3
CREATE TABLE renraku(sid, kid, semi);
create table student(sid, name);
create table lecturer(kid, name, email);

現在操作しているデータベース内に作った表の確認は .schema コマンドで行なう。

.sch
CREATE TABLE lecturer(kid, name, email);
CREATE TABLE renraku(sid, kid, semi);
CREATE TABLE student(sid, name);

ピリオド(.)で始まるコマンドはSQLite3のDB操作コマンドで， 他と区別できる範囲で省略できる。ドットコマンド一覧は， ".help" で得られる。ドットコマンド以外のSQL文は 終端をセミコロンにする。SQL文では大文字小文字が区別されないが， ドットコマンドでは区別される。

テキストデータファイルのインポート

全レコードを記入しておいた テキストファイルからまとめて挿入する方法を示す。

テキストファイルは，1行1レコード，一定の区切り文字で作っておく。 CSVはそれに相応しい。以下の3つの表を用意する。ただし， SQLite3はUTF-8コードしかサポートしないので，各ファイルはUTF-8で作成し， 以下の作業は端末をUTF-8モードにして行なう。

kterm では Ctrl+マウス第2ボタン で utf8mode を選択する。

renraku-csv.utf8

C110123,F10001,専門演習1
C110134,F10001,専門演習2
C110138,F10014,専門演習2
C110140,F10014,専門演習2
C110144,F10002,専門演習2

student-csv.utf8

C110123,公益太郎
C110134,飯森花子
C110138,高見台一
C110140,緑智子
C110144,海原月山

lecturer-csv.utf8

F10001,酒田康一,koichi@example.jp
F10014,鳥海三郎,chokai@example.jp
F10002,酒田康一朗,ko16@example.jp

これらを SQLite3 内のtableに取り込むには .import コマンドを利用し，「.import ファイル Table」のように行なう。このとき，フィールド(カラム)の 区切り文字をあらかじめ指定しておく。これには .separator コマンドを利用する。

.sepa ,             # 区切りをカンマにする
.import renraku-csv.utf8 renraku
.import student-csv.utf8 student
.import lecturer-csv.utf8 lecturer

データ取り込みの確認は，SQL文で全レコードの選択を指定して行なう (セミコロンを忘れずに)。

select * from renraku;
select * from student;
select * from lecturer;

絞り込み選択

「select * from テーブル;」で全レコードが得られる。 あるカラムが特定の条件を満たすものだけ選択したければ where 句を用いる。

select * from テーブル WHERE カラムに対する条件等;

たとえば，学籍番号がC110144のもののみ選択したければ以下のようにする。

select * from student where sid=='C110144';
C110144,海原月山

`==' は数値や文字列が等しいときに当てはまる。文字列が部分的に マッチする場合は like と % を用いる。% が正規表現の .* に相当する。

select * from lecturer where kid LIKE 'F1001%';
F10014,鳥海三郎,chokai@example.jp

特定カラムのみの選択

selectの後ろに得たいカラム名のみカンマ区切りで記述する。

select kid,email from lecturer where kid like 'F1000%';
F10001,koichi@example.jp
F10002,ko16@example.jp

表の合成

「学生の所属を見たい」ということなら表1のrenrakuテーブルに求める ものがある。しかし，

C110123,F10001,専門演習1

では誰か一瞥で分からない。学籍番号vs.氏名の表であるstudentを 合成することで氏名も分かる。

renrakuテーブル
sidkidsemi
C110123F10001専門演習1
C110134F10001専門演習2
C110138F10014専門演習2
studentテーブル
sidname
C110123公益太郎
C110134飯森花子
C110138高見台一

これには，

renrakuのsidとstudentのsidが同じであるレコードを合成

する操作(JOIN操作)を行なう。

select * from renraku JOIN student ON renraku.sid==student.sid;
C110123,F10001,専門演習1,C110123,公益太郎
C110134,F10001,専門演習2,C110134,飯森花子
C110138,F10014,専門演習2,C110138,高見台一
C110140,F10014,専門演習2,C110140,緑智子
C110144,F10002,専門演習2,C110144,海原月山

「select *」すると単純に合成した表が出るので冗長である。

select renraku.sid,student.name,kid,semi from 
renraku JOIN student ON renraku.sid==student.sid;
C110123,公益太郎,F10001,専門演習1
C110134,飯森花子,F10001,専門演習2
C110138,高見台一,F10014,専門演習2
C110140,緑智子,F10014,専門演習2
C110144,海原月山,F10002,専門演習2

ビュー

分割した基本的な表にしておくのは効率的だし，JOINで 見やすい表が得られることも分かったが，表を索くときに毎回JOINをするのは 煩雑である。特定の select 操作で得られる表を，架空の表として定義できる。 これがビューである。今回の

select renraku.sid,student.name,kid,semi from 
renraku JOIN student ON renraku.sid==student.sid;

をそのままビューにする。ビューもテーブルと同様に扱えるので テーブル同様名前をつける。


CREATE VIEW v_renraku AS
select renraku.sid,student.name,kid,semi from
renraku JOIN student ON renraku.sid==student.sid;

v_renrakuからselectするとあたかも普通の表のように結果が得られる。

select * from v_renraku LIMIT 3;
C110123,公益太郎,F10001,専門演習1
C110134,飯森花子,F10001,専門演習2
C110138,高見台一,F10014,専門演習2

(ここで用いたlimit句は出力件数を制限できる)

select sid,name,semi from v_renraku where sid > 'C110135';
C110138,高見台一,専門演習2
C110140,緑智子,専門演習2
C110144,海原月山,専門演習2

yuuji@e.koeki-u.ac.jp