データベース

データベースと SQL

ここでいうデータベースは関係データベース(relational database,RDB)と呼ばれるものです。関係データベースを操作するには SQL という言語を使います。SQL はもともと Structured Query Language(構造化問合せ言語)の略でしたが,今では単に SQL(エスキューエル)が正式名になっています。

ちなみにこの query(問合せ)という語は,ソフトによっては「クエリー」のように日本式発音でカナ書きしているものもあります。

関係データベースを管理するソフト(RDBMS)にはいろいろなものがありますが,ここでは SQLite というオープンソース(パブリックドメイン)のデータベースソフトを使ってみましょう。これは PHP 5 にも組み込まれています。

SQLiteがインストールされたマシンなら sqlite3 と打ち込めば使えます。次のページからも SQLite が使えるかもしれません(学内のみ)。

sqlite3 コマンドで表示を MySQL の mysql コマンドや PostgreSQL の psql コマンドとできるだけ近づけるには,次のように設定するといいでしょう。

.headers on
.mode column

これで列名が付き,列幅がすべて10になります。

.explain on にすると1列目だけ幅が4になります。

.width 6 12 12 などとして幅を指定することもできます。

.help で説明が出ます。.exit または .quit で終了します。

テーブルを作ろう

たとえば hoge という名前のテーブル(表)を作ってみましょう。

create table hoge(hinmei text, nedan int);

text は文字列,int は整数(integer)の意味です。他の SQL プログラムでは,16バイトまでの文字列の意味で char(16) あるいは varchar(16) といった書き方をしなければならないかもしれません。

実は SQLite はほとんど typeless(型なし)で,int と宣言しても文字列でも小数でも入れることができます。ただし,並べ替えのときには text,char,varchar とそれ以外とで違いがあります。

この表に値(values)を挿入(insert)してみましょう。

insert into hoge values('りんご', 500);
insert into hoge values('みかん', 300);

挿入した列をすべて表示してみましょう。「すべての列」は米印 * で表します。

select * from hoge;

すると次のような表が表示されるはずです。

hinmeinedan
りんご500
みかん300

hinmei の列だけ表示するには次のようにします。

select hinmei from hoge;

今度は値段が 400 円以上の品名だけ表示してみましょう。

select hinmei from hoge where nedan >= 400;

合計(sum)を求めてみましょう。

select sum(nedan) from hoge;

値段の順に並べてみましょう。

select * from hoge order by nedan;

無指定では値段の小さい順(昇順,ascending order)に並びます。大きい順(降順,descending order)に並べるには desc を付けます。

select * from hoge order by nedan desc;

データの更新には update を使います。

update hoge set nedan=200 where hinmei='みかん';

行の削除は delete です。

delete from hoge where hinmei='みかん';

表を削除するには drop table 表の名前; とします。

データベース設計

主キーを定めよう

上の例で使った商品データベースでは,りんごがいろいろあったら品名だけで行が決まりません。名前だけで行が決まるような詳しい名前を付けるか,あるいは商品コードを付けましょう。

学生名簿でも,同じ名前の学生が二人いたら,名前だけで行が決まりません。学籍番号を付けましょう。

商品コードや学籍番号のように,これが決まれば行が特定できるものを,主キーといいます。

第1正規化

学生名簿に「所属クラブ」という列があると,複数のクラブに所属している学生はどう扱えばいいでしょうか。

学籍番号氏名所属クラブ
1788三重太郎野球部
1789山田花子茶道部漫研

こんな芸当はできません。^^;

学籍番号氏名所属クラブ1所属クラブ2
1788三重太郎野球部-
1789山田花子茶道部漫研

これだと三つのクラブに所属する人が現れたら困ります。それに,このようにしてしまうと,あとの処理が難しくなります。

そのようなときは,複数の行に分けましょう。

学籍番号氏名所属クラブ
1788三重太郎野球部
1789山田花子茶道部
1789山田花子漫研

これを第1正規化といいます。

第2正規化

上の表では,主キーは「学籍番号」と「所属クラブ」の二つになります。二つの主キーの値を指定すれば行が定まるので,これはまったく問題ありません。問題は,氏名が「学籍番号」だけで決まってしまい,もう一つの主キー「所属クラブ」によらないということです。このように,ある列の値が主キーの一部だけによって決まる場合は,表を次のように二つに分けましょう。

学生一覧表(主キーは学籍番号)

学籍番号氏名
1788三重太郎
1789山田花子

クラブ所属表(主キーは学籍番号と所属クラブ)

学籍番号所属クラブ
1788野球部
1789茶道部
1789漫研

実際にはクラブに属していない学生も多いので,クラブ所属表は学生一覧表ほど大きくなりません。

このように,主キーの一部だけに依存するような項目をなくすことを,第2正規化といいます。

第3正規化

学生一覧表は実際にはもっといろいろな項目を含みます。たとえばコースについての情報が次のように入っているとしましょう。

学籍番号氏名コース名コース主任
1788三重太郎社会情報○○教授
1789山田花子情報処理□□教授

「コース名」が決まれば「コース主任」は決まってしまいますね。そこでまた表を分割しましょう。

学生一覧表(主キーは学籍番号)

学籍番号氏名コース名
1788三重太郎社会情報
1789山田花子情報処理

コース一覧表(主キーはコース名)

コース名コース主任
社会情報○○教授
情報処理□□教授

コース一覧表は,コースの数だけでいいので,たいへん小さくなります。

このように,別の項目に依存するような項目をなくすことを,第3正規化といいます。

表はできるだけ正規化するのが正しいデータベース設計です。

結合(join)

上のように正規化した場合,いくつかの小さな表ができます。これらを結合して,正規化する前の大きな表に戻す方法が必要になります。

学生一覧表とクラブ所属表から,両方のクラブに所属する学生について,氏名なども含めた大きい表を作るには,次のようにします。

select * from 学生一覧表 natural join クラブ所属表;

また,学生一覧表とクラブ所属表から,学生一覧表に含まれるすべての学生について,氏名なども含めた大きい表を作るには,次のようにします。

select * from 学生一覧表 natural left join クラブ所属表;

ほかにもいろいろな結合が考えられますが,これだけ知っていれば大丈夫でしょう。

参考書・参考サイト

参考書

参考サイト


Last modified: