データベース(MySQL)

データベースとSQL

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

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

関係データベースを管理するソフト(RDBMS)にはいろいろなものがありますが,ここでは MySQL というオープンソースのデータベースソフトを使ってみましょう。MySQL は PHP から簡単に使うことができます。

次のページから MySQL が使えます。

テーブルを作ろう

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

create table hoge(hinmei varchar(255), nedan int);

画面上では次のように見えます。

mysql> create table hoge(hinmei varchar(255), nedan int);
Query OK, 0 rows affected (0.02 sec)

varchar(n) は n 文字までの文字列です(n ≦ 255)。int は4バイトの整数(integer)の意味です。varchar(n) の代わりに text という型もよく使われます。text は65535バイトまで自由に伸びます。

うまく表ができたかどうか調べてみましょう。

mysql> describe hoge;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| hinmei | varchar(255) | YES  |     | NULL    |       |
| nedan  | int(11)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

int は32ビットですので-2147483648〜2147483647の範囲で,自動的に表示幅が11桁に設定されます。

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

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

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

select * from hoge;

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

mysql> select * from hoge;
+--------+-------+
| hinmei | nedan |
+--------+-------+
| りんご |   500 |
| みかん |   300 |
+--------+-------+
2 rows in set (0.00 sec)

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 表の名前; とします。

データベース設計

主キーを定めよう

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

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

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

さきほどの商品テーブルに主キーとして商品コードを付けるなら,例えば次のようにします。

create table hoge(id int not null auto_increment,
                  hinmei varchar(255),
                  nedan int,
                  primary key(id));

これは,idという欄は整数(int)で,空ではなく(not null),もし空の値(null)を代入しようとたら自動で1,2,3,……という数を代入することを表します。このようなauto_incrementな欄は主キー(primary key)と定義しなければなりません。

第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: 2012-04-19 21:00:31