魔術師見習いのノート

プロフィール

魔術師見習い
Author魔術師見習い-_-.
Twitter魔術師見習い

コンピュータ関係のメモを主に書きます.

MENU

MySQLの基礎

投稿日:
修正日:
タグ:

MySQLの使い方に関するメモ。試した環境はDebianやWindowsである。

SQLの構成

SQL(Structured Query Language)とはRDB(Relational Data Base)において,データの操作や定義,制御を行うためのデータベース言語である。RDBは日本語で関係データベースと呼ばれるもので,1つ以上の表から構成される。表の行はデータの基本単位であり,レコードとも呼ばれる。同じように表の列は属性とも呼ばれる。SQLの利用方法は次の3つがある。

  • 対話的に処理。
  • プログラムに埋め込む。
  • 自動生成ツールで使用。

本稿では「対話的に処理」する場合の方法を説明する。

また,SQLの文は次の3つに分類できる。

データ定義文(DDL: Data Definition Language)
データベースやテーブルの作成や削除など。
データ操作文(DML: Data Manupulation language)
テーブルの検索や変更など。
データ制御文(DCL: Data Control Language)
トランザクション関連の処理(コミットやロールバック)。

インストール

Debian7.10 amd64では,

user% sudo aptitude install mysql-server
または
user% sudo apt-get install mysql-server
で,WindowsではGUIのインタフェースでインストールする。また両者ともMySQLのためのルートパスワードの設定が求められる。

Windowsのコマンドプロンプトで実行する場合,更に環境変数Pathにmysql.exeがあるディレクトリの場所を追加しなければならない。環境変数の設定は,次の手順で行う。

  1. エクスプローラで[コンピュータ]を開く。
  2. [システム・プロパティ]を選択。
  3. [システムの詳細設定]を選択。
  4. [詳細]タブを選択。
  5. [環境変数]を選択。
  6. [編集]でPathを選択。
  7. ;で区切って追加するパス(mysql.exeが格納されたディレクトリの位置)を追加する。

インストールや初期の設定が終了後,

user% mysql -u root -p
とコマンドを打ち込み,パスワードが求められてそれを入れてMySQLのプロンプトが出ればインストール成功である。

日本語

文字列に日本語を使うには設定が必要である。私の環境では(Debian7.10 amd64),/etc/mysql/my.cnfの[〜]の各セクションに,それぞれ次の行を追加した。

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

その後mysqlを再起動する。

/etc/init.d/mysql restart
SQLを使う際の注意点
  • 命令や名前の大文字小文字は区別しない(本稿では可読性のためSQLのキーワードは大文字で記述する)。
  • 文字列はシングルクォーテーション(')で囲う。
  • いくつかの変更するための処理(述語)は,変更処理だけ行い変更後の表を画面に表示しない。もしユーザが変更結果を知りたい場合,参照するための処理を指定する必要がある。
サンプルRDBの作成

本稿の説明では主に次の表を使って説明する。

社員表(sampledb.employee)
社員番号(id)名前(name)年齢(age)性別(sex)血液型(blood)
1001山田太郎40B
1002佐藤一郎29A
1003鈴木花子35AB
2001伊藤月子30A
2002齋藤次郎36O
2003高橋雪34A
3001後藤三郎19O

前述の表を生成する場合,以下のコードをコピーアンドペーストすれば良い。

CREATE DATABASE sampledb;
CREATE TABLE sampledb.employee
(id 	INTEGER	        PRIMARY KEY NOT NULL,
 name 	CHAR(30)        NOT NULL,
 age 	INTEGER	        ,
 sex 	CHAR(3)	        ,
 blood 	CHAR(2)	        );
INSERT INTO sampledb.employee VALUES (1001, '山田太郎', 40, '男', 'B');
INSERT INTO sampledb.employee VALUES (1002, '佐藤一郎', 29, '男', 'A');
INSERT INTO sampledb.employee VALUES (1003, '鈴木花子', 35, '女', 'AB');
INSERT INTO sampledb.employee VALUES (2001, '伊藤月子', 30, '女', 'A');
INSERT INTO sampledb.employee VALUES (2002, '齋藤次郎', 36, '男', 'O');
INSERT INTO sampledb.employee VALUES (2003, '高橋雪', 34, '女', 'A');
INSERT INTO sampledb.employee VALUES (3001, '後藤三郎', 19, '男', 'O');

ここでこれらの文の見方については言及しない。


データ操作文(DML: Data Manupulation language)

参照と変更

DMLは表に対する処理のことであり,主に参照系と変更系の2種類に分類できる。

参照系(select)
社員表から社員番号と名前,性別の列を表示。
SELECT id,name,sex FROM sampledb.employee;
この述語は次のように構成される。
SELECT 列 FROM 表
表はいずれかのデータベースに属し,"データベース.表"のように指定するか,USEコマンドで現在使用するデータベースを変更することで,表だけ記述できる。
USE sampledb;
SELECT id,name,sex FROM employee;
社員表の一覧(全ての列)を表示。
SELECT * FROM sampledb.employee;
社員表から年齢(age)が30歳以上のヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE age >= 30;
社員表から男性でかつ年齢(age)が30歳以上のヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE sex='男' && age >= 30;
社員表から女性または年齢(age)が30歳以上のヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE sex='女' || age >= 30;
社員表から男性で年齢(age)が20歳から30歳までのヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE age BETWEEN 20 AND 30;
または
SELECT * FROM sampledb.employee WHERE 20 <= age && age <= 30;
社員表から〜子で終わる名前のヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE name LIKE '%子';
または正規表現を使って
SELECT * FROM sampledb.employee WHERE name REGEXP '.*子';
社員表の一覧を年齢の昇順で表示
SELECT * FROM sampledb.employee ORDER BY age;
社員表の一覧を年齢の降順で表示
SELECT * FROM sampledb.employee ORDER BY age DESC;
社員表の一覧から血液型ごとに平均年齢を表示。
SELECT blood,AVG(age) FROM sampledb.employee GROUP BY blood;
社員表の一覧から血液型ごとに平均年齢(30歳以上)を表示。
SELECT blood,AVG(age) FROM sampledb.employee GROUP BY blood HAVING blood > 30;
変更系(update,insert,delete)
社員表に社員番号1004番の田中三郎,年齢18歳,血液型O型を追加。
INSERT INTO sampledb.employee VALUES (1004, '田中三郎', 18, 'O');
列を指定。
INSERT INTO sampledb.employee(id, name, age) VALUES (1005, '衛宮四郎', 18);
INSERTは
INSERT INTO 表[(列, ...)] VALUES (値, ...);
社員表の社員番号1001番の年齢を30歳に変更。
UPDATE sampledb.employee SET age=30 WHERE id=1001;
UPDATE 表 SET 列=値[,列=値];
ただしUPDATEやDELETEはWHERE句を指定しなければ全てのレコードに対して処理を行うので,必ずWHERE句を付けると考えた方が無難である。
社員表の社員番号1001番の年齢を30歳に,血液型をB型に変更。
UPDATE sampledb.employee SET age=30, blood='B' WHERE id=1001;
社員表から社員番号1002のヒトの項目を削除。
DELETE FROM sampledb.employee WHERE id=1002;
DELETEは表だけ指定すれば最低限成り立つが,前述の通りそれでは全てのレコードを対象にしてしまう。
DELETE FROM 表;
集合関数

SQLではSUM(総和)やMAX(最大値),MIN(最小値),AVG(平均),COUNT(総数)を求めるような関数が用意されている。以下にそれらの関数を使用した例を示す。

社員表から社員の平均年齢を検索
select AVG(age) FROM sampledb.employee;
副問い合わせ

SQLでは()を使用することで,ある検索結果で得た情報を別の検索のために使用することができる。これを副問い合わせという。以下に例を示す。

社員表から平均年齢よりも高い社員を検索
SELECT * FROM sampledb.employee WHERE age > ( SELECT AVG(age) FROM sampledb.employee );
結合

SQLではいくつかの表を組み合わせて検索する場合があり,そのための仕組みとして結合という方法が用意されている。結合にはいくつか種類があるが,ここでは以下の3つの結合を紹介する。

内部結合(INNER JOIN)
2つの表で一致する部分を抽出。
外部結合(OUTER JOIN)
片方の表で一致する部分を抽出。
交差結合(CROSS JOIN)
2つの表の組み合わせを生成。

以下でMySQLでのそれぞれの結合の方法を紹介する。

内部結合を説明するために新たに次の表を追加する。

顧客表(sampledb.customer)
顧客番号(cid)名前(name)担当社員番号(eid)
109001本田五郎1001
109002高倉六郎1002
109003豊田松代1001
109004松田竹子1003
CREATE TABLE sampledb.customer
(cid 	INTEGER	        PRIMARY KEY NOT NULL,
 name 	CHAR(30)        NOT NULL,
 eid    INTEGER         NOT NULL);
INSERT INTO sampledb.customer VALUES (109001, '本田五郎', 1001);
INSERT INTO sampledb.customer VALUES (109002, '高倉六郎', 1002);
INSERT INTO sampledb.customer VALUES (109003, '豊田松代', 1001);
INSERT INTO sampledb.customer VALUES (109004, '松田竹子', 1003);
内部結合

複数の表に分けて管理する場合,それらのデータが冗長にならないように設計した方がデータベースのサイズは小さい。例えば,前述のデータベースでは,顧客表は担当者の番号だけを記録し,担当者の名前は社員表を見れば良い。しかし顧客名と担当者名を対応させたい時,それでは不便である。そのような時に内部結合を使用する。

顧客名と担当する社員の社員番号,担当する社員の名前を表示。
SELECT customer.name,employee.id,employee.name FROM sampledb.employee INNER JOIN sampledb.customer on employee.id=customer.eid;

関連する2つの表がある時,それらは1側と多側に分けることができる。例えば前述の社員表と顧客表においては,社員表の社員番号と顧客表の担当社員番号で関連付けることができ,社員表の社員番号が各レコードで一意に定まるの対し,顧客表は同じ内容を指しているが一意に定まることはない。

SELECT 列 FROM 1側の表 INNER JOIN 多側の表 on 関連付ける列;
関連付ける列は"表1.列=表2.列"のように指定する。 なお表示する列を指定する場合は,1側(employee.id)と多側の列(customer.eid)を指定しても同じような結果となる。
  • SELECT customer.name,employee.id,employee.name FROM sampledb.employee INNER JOIN sampledb.customer on employee.id=customer.eid;
  • SELECT customer.name,customer.eid,employee.name FROM sampledb.employee INNER JOIN sampledb.customer on employee.id=customer.eid;
しかし,1側を指定した方が検索にかかる時間は短い。

また,表の名前はASを使うことで別名を付けて簡略化することができる。

SELECT c.name,e.id,e.name FROM sampledb.employee AS e INNER JOIN sampledb.customer AS c on e.id=c.eid;

ASは表の名前だけでなく列名などにも使用可能である。例えば前述の例では顧客名も担当者名もnameで分かりづらい。そういった場合,それぞれ別名を付けて表示することができる。

SELECT c.name AS 顧客名,e.id AS ID,e.name AS 担当者名 FROM sampledb.employee AS e INNER JOIN sampledb.customer AS c on e.id=c.eid;

外部結合
前述と同じような内容を知りたい時でも,「社員がそれぞれどの顧客を担当しているか」を知りたい場合がある。当然ながら社員とはいえ,さまざまな仕事内容があり,中には顧客と直接対応しない者もいる。そのような情報が欲しい場合,外部結合を使用して片方の表に乗っている場合はレコードを表示する。
SELECT c.name,e.id,e.name FROM sampledb.employee AS e LEFT OUTER JOIN sampledb.customer AS c on e.id=c.eid;
外部結合の場合,LEFTやRIGHTといったキーワードで,ベースとする表を指定する。関連付けた列でベースとしている方にレコードが存在する場合,そのレコードを生成する。今回の例では,社員をベースに考えているため,社員表をベースとしている。なお左表と右表というのは"左表 {LEFT|RIGHT} OUTER JOIN 右表"というように記述する。今回の例でいえば,"employee LEFT OUTER JOIN customer"と"customer RIGHT OUTER JOIN employee"は同じ結果となる。
交差結合

データ定義文(DDL: Data Definition Language)

SQLサーバは複数のデータベースを,データベースは複数の表を,表は複数のレコードを持つ。DMLがレコードの参照・変更・追加・削除などであったが,DDLはデータベースや表に対してそのような処理を行う。

MySQLで表を生成するまでの流れは次の通りである。

  1. データベースの作成。
    CREATE DATABASE sampledb
  2. テーブルの作成。
    ID id: 整数名前 name: char(20) 年齢 age: 整数血液型 blood: char(2)
    CREATE TABLE テーブル名 
    (属性名	データ型	値の制約,
     属性名	データ型	値の制約
     ...);
    

    テーブル名は現在使用しているデータベースのものであれば,直接テーブル名を指定できるが,それ以外の場合は「データベース名.テーブル名」とする。現在使用中のテーブルはUSE文で変更できる。

    USE データベース名;

    以下にテーブルの生成例を示す。

    CREATE TABLE sampledb.employee
    (id	INTEGER		PRIMARY KEY NOT NULL,
     name	CHAR(20)	NOT NULL,
     age	INTEGER		,
     blood	CHAR(2)		);
    

    CHARで指定する数字は,文字数ではなくバイト数である。英字であればその2つは同じ意味を持つが,その他の言語を利用する場合それらは文字コードによって使用するバイト数が異なる。

    生成したテーブルの列情報はDESCで確認できる。

    DESC テーブル名;

作成したデータベースやテーブルの一覧はSHOWで確認できる。

データベース
SHOW databases;
SHOW tables;
前述のテーブルの一覧表示は,現在設定されているデータベースのものが表示される。
制約

各レコードのデータ型を設定する場合,整数や文字列といったコンセプトとは別に,格納する際の条件(制約)が設定できる。制約には,ブランク(NULL)を禁止するものや,同じ列の他のレコードと値が同じになってはいけないものなどさまざまある。RDBを生成する場合,必ず1レコードだけを抽出するための列を用意するべきである。このような列を主キーという。主キーの制約には"PRIMARY KEY NOT NULL"の2つが必要である。

データベースや表の削除
  • データベースの削除。
    DROP DATABASE sampledb;
  • 表の削除。
    DROP TABLE sampledb.employee;


データ制御文(DCL: Data Control Language)

データベースには,複数のユーザが同時に処理を行うことで,データの整合性がとれない状況を防ぐ仕組みがある。代表的なものには,以下の2つがある。

排他的制御
あるユーザが処理を行う際に他のユーザが処理できないようにロックする方法。
トランザクション処理
いくつかのレコード変更処理をトランザクションという単位でまとめて扱い,最終的にそのデータを反映させる場合はコミット,取り消す場合はロールバックを行うことでデータの整合性を守る。なおMySQLではデフォルトでは自動コミットとなっており,変更系の文を記述するたびにデータの変更が行われる。

DCLはこのような制御を行う。

トランザクション処理

実際にデータベースを扱う場合,一部のデータだけ変更できれば良いわけではない。例えば購入処理では,購入者の所有数を増やすと同時に,在庫表のものを減らさなければならない。このような処理はいずれかだけできれば良い訳ではなく,必ず全て必要である。SQLでは,このような処理をトランザクションという単位でまとめて処理を行う。トランザクションの主な処理は次のような手順である。

  1. トランザクションの開始。
    START TRANSACTION;
  2. 変更処理をいろいろ(参照処理も可能だが参照処理は逐次行われる)。
  3. コミット。データを正式に反映する。
    COMMIT;
    ロールバック。トランザクション開始時からの変更処理を取り消す。
    ROLLBACK;

本稿を順番に読んだ場合,今まで見たレコードの変更系処理は,処理を記述する度にコミットされてきた。それゆえコミットのイメージは分かるものとし,ここではロールバックの例を示す。

  1. トランザクションの開始。
    START TRANSACTION;
  2. 参照。初期状態を確認(参照系)。
    SELECT * FROM sampledb.employee;
  3. 更新(変更系)。
    UPDATE sampledb.employee SET age=41 WHERE id=1001;
  4. 参照(参照系)。
    SELECT * FROM sampledb.employee;
  5. 新しいレコードを追加(変更系)。
    INSERT INTO sampledb.employee VALUES (1005, '幸田七郎', 25, '男', 'B');
  6. 参照(参照系)。
    SELECT * FROM sampledb.employee;
  7. ロールバック。前述の2つの変更系処理を取り消す。
    ROLLBACK;
  8. 参照。初期状態と同じ状態になっている。
    SELECT * FROM sampledb.employee;
自動コミットモード

RDBとSQL 応用

投稿日:
タグ:

DBやSQL関連の中級以上向け技術のメモ。

三層スキーマ

データベースの構造・スキーマを3階層に分けて、定義する方式がある。

三層スキーマ
外部スキーマ
概念スキーマ
内部スキーマ
内部スキーマ
DBの実態。データを効率的に管理するための物理設計。DBもデータである以上、ファイルとして管理される。DB開発者が考えるスキーマ。
概念スキーマ
テーブル。データを一意に効率的に管理するための設計。DB設計者が考えるスキーマ。
キーワード:正規化, ER図
外部スキーマ
テーブルやビュー、SELECTの結果。ユーザ視点のもので、実際にデータを使用する際に考えるスキーマ。

縦持ちと横持ち

生徒のテストの点数を管理する場合、エクセルのような表計算ソフトを使用するヒトは、以下のような表を作成するだろう。

名前
佐藤太郎5060567060
鈴木二郎6055705570
田中三郎4070806080
横持ち

このような表を横持ちテーブルという。1つのレコードに対して各フィールドに必ず1つの値が入る場合、有効な設計である。しかしRDBでは横持ちテーブルは、列間の独立性が高く、常に良いとは限らない。横持ちテーブルに対し、以下のようなテーブルを縦持ちテーブルという。

名前科目点数
佐藤太郎50
佐藤太郎60
佐藤太郎56
佐藤太郎70
佐藤太郎60
鈴木二郎60
鈴木二郎55
鈴木二郎70
鈴木二郎55
鈴木二郎70
田中三郎40
田中三郎70
田中三郎80
田中三郎60
田中三郎80
縦持ち

一見すると、縦持ちテーブルは横持ちテーブルに比べて、可読性が低く、かつデータが重複しているため、効率が悪いように見えるかもしれない。しかし、RDBでは縦持ちテーブルの方が効率が良い場合がある。

横持ちテーブルで科目数を増減したい場合、列を追加する必要がある。RDBでは列の追加は、ALTER文を使用してテーブル設計を変更する必要がある。これに対し、縦持ちテーブルはINSERT文だけで追加可能である。すなわち、テーブルを再設計するコストがない。

横持ちテーブル
ALTER TABLE テーブル ADD 美 int;
ALTER TABLE テーブル DROP 美;
縦持ちテーブル
INSERT INTO テーブル VALUES ('衛宮四郎', '美', 50);
DELETE FROM テーブル WHERE 科目='美';

更に、横持ちテーブルでこのような変更を行った場合、各フィールドにデータが入らないケースが発生する(すなわちnull)。そうなると、列ごとの何らかの処理を行う場合、nullを除外するような処理を記述しなければならなくなる(例:COALESCEを使う)。

また、データ処理においても、縦持ちテーブルは優れた性質を持つ。生徒ごとの合計点を計算する場合、縦持ちテーブルと横持ちテーブルではそれぞれ次のように記述する。

横持ちテーブル
SELECT 名前,国+数+社+理+英 FROM テーブル GROUP BY 名前;
縦持ちテーブル
SELECT SUM(点数) FROM テーブル WHERE 名前="佐藤太郎"

このように、縦持ちテーブルは、ある関連するデータの値が0個〜複数個ある場合に、横持ちテーブルよりも拡張性に優れている。

しかしその反面、縦持ちテーブルは横持ちテーブルに比べて、レコード数が増加しやすい。RDBにおいて、レコード数が多いと、検索や処理速度の低下を招くため、前述のようなケースでも必ず縦持ちテーブルが良いとは限らない。例えば前述の横持ちテーブルは、1つのUPDATE文やINSERT文で5教科の更新や追加をできるのに対し、縦持ちテーブルではそれぞれ5回発行しなければならない。

横持ち→縦持ち

以下のようなテーブルがあったとする。

名前ニックネーム1ニックネーム2ニックネーム3
佐藤太郎タローtaro3たろっち
鈴木二郎ジロー2ronull
このテーブルは1つのINSERT文やUPDATE文で1レコードを更新できるが、前述した通り何らかの処理をする場合は縦持ちの方が都合が良いことがある。このような場合、UNIONを使うと横持ちテーブルを縦持ちに変換できる。
SELECT ニックネーム1 AS ニックネーム FROM テーブル WHERE 名前 ='佐藤太郎' UNION
SELECT ニックネーム2 AS ニックネーム FROM テーブル WHERE 名前 ='佐藤太郎' UNION
SELECT ニックネーム3 AS ニックネーム FROM テーブル WHERE 名前 ='佐藤太郎';
ニックネーム
タロー
taro3
たろっち
※別にWHEREは結合後につけても結果自体は変わらないが、性能が同じとは限らない。

前述の三層スキーマでも述べたように、SQLでは実態のテーブル(概念スキーマ)と実際に扱うデータ構造(外部スキーマ)を分けることができる。

縦持ちから横持ちテーブルへの変換については後述する。


ビュー

頻繁に使用するSELECT文は、ビューを作成することで、擬似的なテーブルとして利用できる。 例えば前述のテーブルに以下のようなSQL文を発行する。
CREATE VIEW ニックネームテーブル (名前,ニックネーム) AS
SELECT 名前,ニックネーム1 FROM テーブル UNION
SELECT 名前,ニックネーム2 FROM テーブル UNION
SELECT 名前,ニックネーム3 FROM テーブル;
そうすると次のようなビューが作成される。
名前ニックネーム
佐藤太郎タロー
佐藤太郎taro3
佐藤太郎たろっち
鈴木二郎ジロー
鈴木二郎2ro
鈴木二郎null

CASE式

プログラミング経験者のSQL初心者は、はじめてSQLを使用するとSQLはただデータを格納するためだけに使い、データの整理はプログラミングでどうにかしようとしたくなる(少なくとも私はそうだった)。

例えば以下のような学校があったとする(Appendixに初期化用のSQL文を記載)。

クラス
1-A1512
1-B1410
2-A1120
2-B2110
3-A1516
3-B1415
この時、運動会で全校を対象に同じ組同士でチームを組む場合、各色のチーム人数を調べる場合、例えばRubyでは次のように記載できる。
db = func()
# func()は「select * from sample_table」の結果を受け取る。
# [
# {"クラス"=> "1-A", "男"=>15, "女"=>12}, 
# {"クラス"=> "1-B", "男"=>14, "女"=>10}, 
# {"クラス"=> "2-A", "男"=>11, "女"=>20}, 
# {"クラス"=> "2-B", "男"=>21, "女"=>10}, 
# {"クラス"=> "2-A", "男"=>15, "女"=>16}, 
# {"クラス"=> "3-B", "男"=>14, "女"=>15}
# ]

red = 0
white = 0
db.each do |x|
  case x["クラス"]
  when "1-A", "2-A", "3-A"
    red += x["男"] + x["女"]
  when "1-B", "2-B", "3-B"
    white += x["男"] + x["女"]
  end
end
puts "赤:#{red} ホワイト:#{white}"
この方法では、DBのデータを全て取得した後にアプリがもう一度全てのデータにアクセスしている。しかし、この程度の処理であれば、DBがデータにアクセスしながら値を整理し、アプリはその結果を出力するだけで良い。
SELECT
  CASE class WHEN '1-A' THEN '赤'
             WHEN '2-A' THEN '赤'
             WHEN '3-A' THEN '赤'
             WHEN '1-B' THEN '白'
             WHEN '2-B' THEN '白'
             WHEN '3-B' THEN '白'
             END,
  SUM(population)
from sample_table
  GROUP BY CASE class WHEN '1-A' THEN '赤'
             WHEN '2-A' THEN '赤'
             WHEN '3-A' THEN '赤'
             WHEN '1-B' THEN '白'
             WHEN '2-B' THEN '白'
             WHEN '3-B' THEN '白'
             END;
実行結果
168
178

CASE式を覚えることで、性能改善や利便性の向上を図ることができる。ただし、CASE式や結合処理はDBの負荷を上げるため、アプリ側で対処するケースもあるため、そこは状況に応じた使い分けが必要である。


縦持ち→横持ち
CASE式を使用すると、縦持ちテーブルを横持ちテーブルに変換できる。例えば以下のようなテーブルがあったとする。
クラス性別人数
1-A15
1-A12
1-B14
1-B10
2-A11
2-A20
2-B21
2-B10
3-A15
3-A16
3-B14
3-B15
縦持ち
この時、次のようなSQL文を実行することで横持ちテーブルに変換できる。
SELECT class,
       sum(CASE WHEN sex='男' THEN population ELSE 0 END) as '男',
       sum(CASE WHEN sex='女' THEN population ELSE 0 END) as '女'
FROM sample_table GROUP BY class;

制約

-編集中-
PRIMARY KEY制約
UNIQUE制約
NOT NULL制約
CHECK制約

正規化

データの重複をなくし、整合的にデータを取り扱えるデータベースを設計することを正規化という。 正規化は段階に合わせて6つに分類される。

正規化(データの整合性)とパフォーマンスは基本的にトレードオフの関係にある。そのため、必ずしも段階が高ければ良いとはいう訳ではなく、状況に応じて使い分けるのが望ましい。

なお、一般的には第3正規化まで行っている設計が多い。

第1正規化
以下の条件を満たしたテーブルにすること。
  • 1つのセルに1つのデータのみ。
  • レコードを一意に特定するキー(主キー:PK)がある。
非正規形のままではRDBで扱うことができない。 言ってみれば第1正規化とはRDBに格納可能な形にすることである。
1つのセルに1つのデータのみ。
名前子供
田中一郎太郎
花子
伊藤五郎null
名前子供
田中一郎太郎
田中一郎花子
伊藤五郎null
レコードを一意に特定するキー(主キー:PK)がある
例1)主キーが名前の場合
名前
佐藤太郎5060567060
鈴木二郎6055705570
田中三郎4070806080
横持ち
例2)主キーが名前と科目(複数)の場合
名前科目点数
佐藤太郎50
佐藤太郎60
佐藤太郎56
佐藤太郎70
佐藤太郎60
鈴木二郎60
鈴木二郎55
鈴木二郎70
鈴木二郎55
鈴木二郎70
田中三郎40
田中三郎70
田中三郎80
田中三郎60
田中三郎80
縦持ち
キー
キーには、ナチュラルキーサロゲートキーの2種類がある。前述したようなあるデータモデルから一意に特定できる要素を見出して、それをキーとしたものを、ナチュラルキー、データモデルにはないが、一意に特定するために新しく追加したキーをサロゲートキーという。例えば前述の縦持ちテーブルのPKをサロゲートキーにすると、次のようになる。
ID名前科目点数
01佐藤太郎50
02佐藤太郎60
03佐藤太郎56
04佐藤太郎70
05佐藤太郎60
06鈴木二郎60
07鈴木二郎55
08鈴木二郎70
09鈴木二郎55
10鈴木二郎70
11田中三郎40
12田中三郎70
13田中三郎80
14田中三郎60
15田中三郎80
サロゲートキーを使用するメリットには、次のようなものがある(ミックさん談)。
  1. そもそも主キーにできる項目がない場合(重複してる)
  2. 主キーの値が使いまわされる場合
  3. 主キーの体系が変化する場合
2と3の例には、商品コードと商品を扱う場合に、商品コードが再利用されるような場合がある。 商品コードをナチュラルキー(PK)とした場合、レコードを上書きするしかないが、別にキーを設定することで、新たに追加するだけで良い。
第2正規化

主キーの一部から非キーが一意に特定できる場合、テーブルが分けること。第2正規化とは、第1正規形から部分関数従属性を取り除くことである。

関数従属性
ある属性(列)の値が決まる時、別の属性が一意に決まること。
部分関数従属性
キーの一部に非キー属性が関数従属している状態のこと。

言ってみれば、第2正規化とは情報からエンティティ毎に整理することである。 大雑把に説明すると、例えばサッカーのグループリーグの情報を表すDBがあり、選手を起点に考える。

  • 選手の名前(キー)
  • 選手の身長
  • 選手の体重
  • 選手の所属チーム
  • 所属チームのホームグラウンド
  • 所属チームの勝利数
  • 所属チームの敗北数
第2正規形でない第1正規形は、選手と所属チームという2つのエンティティをひとまとめに管理しているが、これらを別のものとして分けるのが第2正規形である。前述の一覧でいえば、「〜の」でテーブルを分割ことである。

RDBではキーがなければレコードを作成できない。そのため、第2正規形でない第1正規形のように1つのテーブルにエンティティが混在する場合、全体の主キーが決まらなければレコードが作成できない。また、同一のエンティティを複数のレコードで管理するため、データの整合性を保持するのが難しい。これを解消したものが第2正規形である。

第3正規化

第2正規形から推移関数従属性を取り除くこと。非キーから別の非キーを一意に特定できる場合、それをテーブルに分けること。

言ってみれば、第3正規化とはエンティティの情報を更に整理することである。第2正規化で全体の情報をエンティティに分けるが、このエンティティの持つ情報から更に一意に特定できる情報を独立化させたものが第3正規形である。

ボイス・コッド正規化

第3正規形のうち非キーから主キーが一意に定まらないDBのこと。ボイス・コッド正規形とは、非キーから主キーへの関数従属をなくした状態。

第4正規形

ボイス・コッド正規化までが情報の整理を行うのに対し、第4・5正規化はエンティティ間の関係を整理する。

エンティティはキーによって一意に特定できる。そのため、キーだけのテーブル(エンティティ)を作成することで、エンティティの関係を表すエンティティを作成できる。

例えば「A→→B|C」の関係のエンティティがある場合、「|Aのキー|Bのキー|」と「|Aのキー|Cのキー|」のようにして関係を表す。

第5正規形

1つの関係エンティティにつき1つの関係を表すDBが第5正規形である。

A→→B、A→→Cに加えてB→→Cの関係があるでは、3つのエンティティの関係を表すテーブルができる。

この時に以下のようにして分割することで、全てのエンティティの関係を表すことができる。

  • |A|B|
  • |A|C|
  • |B|C|

Appendix

my.cnf
[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8
初期化
START TRANSACTION;
CREATE DATABASE sampledb;
USE sampledb;
CREATE TABLE sample_table
(class  CHAR(3),
 sex CHAR(1),
 population  INTEGER);
INSERT INTO sample_table VALUES ('1-A', '男', 15);
INSERT INTO sample_table VALUES ('1-A', '女', 12);
INSERT INTO sample_table VALUES ('1-B', '男', 14);
INSERT INTO sample_table VALUES ('1-B', '女', 10);
INSERT INTO sample_table VALUES ('2-A', '男', 11);
INSERT INTO sample_table VALUES ('2-A', '女', 20);
INSERT INTO sample_table VALUES ('2-B', '男', 21);
INSERT INTO sample_table VALUES ('2-B', '女', 10);
INSERT INTO sample_table VALUES ('3-A', '男', 15);
INSERT INTO sample_table VALUES ('3-A', '女', 16);
INSERT INTO sample_table VALUES ('3-B', '男', 14);
INSERT INTO sample_table VALUES ('3-B', '女', 15);
COMMIT;

一覧