はじめに
経緯
- オープン系開発経験の乏しい新人さん(1〜3年生くらい)を対象に「データベースって何？どう使うの？」を教えることになったので。
- ここで言うデータベースとは狭義に「リレーショナルデータベース」(RDBMS)とします。
記事の目的
- 新人さんに教えるにあたり、自分の頭を整理するためにアウトプットしています。
- 方向性としては「どのように理解すべきか」を重視し、説明のために平素な表現をしている場合があります。
- 細かい話やリファレンス的なものはggrks精神(Google先生にお聞きくださいませ)で進めます。
- わかりづらい、認識に誤りがあるなど、突っ込みを頂けると喜びます。
リレーショナルデータベースって何？
語義
リレーショナル：関係のある、つながりのある。
リレーショナルデータベースとは、「関係、つながりのあるデータ」を管理するためのデータベースである。
関係、つながりとは
重要なキーワードは「正規化」と「結合」です。
正規化とは
一定のルールにしたがって、一連の情報を切り分けて別々に管理すること。
基本的には同じ情報をなんども登録しないようにする。
例：正規化されていない定食屋さんのメニュー
| メニュー | 値段 |
|---|---|
| コロッケとおしんこ定食 | 700円 |
| コロッケと野菜サラダ定食 | 800円 |
| コロッケととん汁定食 | 900円 |
| メンチカツとおしんこ定食 | 800円 |
| メンチカツと野菜サラダ定食 | 900円 |
| メンチカツととん汁定食 | 1000円 |
| ハンバーグとおしんこ定食 | 900円 |
| ハンバーグと野菜サラダ定食 | 1000円 |
| ハンバーグととん汁定食 | 1100円 |
| 刺し身とおしんこ定食 | 1000円 |
| 刺し身と野菜サラダ定食 | 1100円 |
| 刺し身ととん汁定食 | 1200円 |
メリット：ひと目ですべての情報が見える
デメリット：行数が増える、同じ情報がなんども出てきて気持ち悪い、メンテナンスが大変(米が値上がりしたら全部直すとか)
正規化された定食屋さんのメニュー
| メインおかず | 値段 |
|---|---|
| コロッケ | 300円 |
| メンチカツ | 400円 |
| ハンバーグ | 500円 |
| 刺し身 | 600円 |
| サブおかず | 値段 |
|---|---|
| おしんこ | 100円 |
| 野菜サラダ | 200円 |
| とん汁 | 300円 |
| セット | 値段 |
|---|---|
| 定食 | 300円 |
メリット：行数が少ない、メンテナンスし易い(米が値上がりしても「定食」だけ直せば良いとか)
デメリット：組み合わせて見ないと定食の全容がわからない
つまり正規化の目的とは、「データ容量低減」「メンテナンス性」などを目的としたものであって、
参照することだけを考えれば正規化しないほうが見やすい場合もあります。
詳しく知りたい場合は「正規化 目的 メリット」などをGoogle先生に聞いてください。
結合とは
正規化されたデータ同士をつなげてあげること。
上記例で言えば、「メインおかず」「サブおかず」「セット」を組み立てて、定食のメニューを作ること。
結合はしばしば「テーブル同士をつなげて情報を取得する」と表現されるが、
個人的には「分割されたテーブル同士を元の形に戻す」と認識したほうが良いと考えている。
通常、テーブルは結合されることを前提に正規化されている。
テーブル同士をどのように結合するかはテーブル定義された時から決まっている。
ミケランジェロが「掘るべき形は最初から石の中にある」とか言っていたのに近い気がする。
データベースってどうやって使うの？
「どうやって使うの？」は狭義にSQLの話にします。
SQL(Structured Query Language)とは
語義
Structured：構造化された
Query：問い合わせ
Language：言語
よくわからない
データベースに格納された情報を一定のルールで探して取り出すための言語。
SQLの分類
大きく分けて「データベースの定義を変更する」「データを参照・更新する」「その他」がある。
まずはこのうち「データを参照・更新する」を意識しておけば良い。
詳しく知りたい場合は「DDL DML DCL」などをGoogle先生に聞いてください。
SQL説明に登場する用語
- テーブル：上記した例では「メインおかずの表」「サブおかずの表」のような二次元表でデータを管理している塊。
- カラム：テーブルの列、「メインおかず」「値段」などのこと。
- レコード：テーブルデータの行、「コロッケ,300円」「メンチカツ,400円」などのこと。
データを参照・更新するSQLリファレンス
SQLにはRDBMSごとに方言があります。私はもともとOracle屋さんなので、他RDBMSでは細部が異なることがあります。(MySQLではDELETEにFROMが必須だったり。)
- SELECT：データを参照する
- 基本構文：
SELECT カラム FROM テーブル名; - INSERT：データを登録する
- 基本構文：
INSERT INTO テーブル名 VALUES ( 値 ); - UPDATE：データを更新する
- 基本構文：
UPDATE テーブル名 SET カラム=値; - DELETE：データを削除する
- 基本構文：
DELETE テーブル名;
以上４つに対して、
「絞込み(WHERE)」「並び替え(ORDER BY)」「結合(JOIN)」などを組み合わせて使う。
- WHERE：レコードを絞込む
- 値段が５００円以下のメインおかずを取得する場合
- 構文：
SELECT メインおかず, 値段 FROM メインおかず表 WHERE 値段<=500円; - ORDER BY：結果の並び順を指定する
- メインおかずを値段の昇順(ASC)に表示する場合
- 構文：
SELECT メインおかず, 値段 FROM メインおかず表 ORDER BY 値段 ASC; - JOIN：テーブルを結合する、いろいろ種類がある
- メインおかずとサブおかずの組み合わせと合計金額をすべて表示する
- 構文：
SELECT メインおかず, サブおかず, メインおかず.値段+サブおかず.値段 FROM メインおかず表 CROSS JOIN サブおかず;
ごめんなさい
JOINの例に出した「CROSS JOIN」は本来、あまり使わないです。
だいたいは「INNER JOIN」「OUTER JOIN」などを使いますが、前述した例では使えませんでした。
改めて「INNER JOIN」の使い方について、データの正規化から通して説明します。
改めて、よくある商品と売上情報の例
非正規化状態のデータ
| 売上日 | 商品名 | 数量 | 売上価格 |
|---|---|---|---|
| 2018/5/1 | コロッケ | 5個 | 1500円 |
| 2018/5/1 | メンチカツ | 2個 | 800円 |
| 2018/5/1 | ハンバーグ | 4個 | 2000円 |
| 2018/5/1 | 刺し身 | 6個 | 3600円 |
| 2018/5/2 | コロッケ | 4個 | 1200円 |
| 2018/5/2 | メンチカツ | 4個 | 1600円 |
| 2018/5/2 | ハンバーグ | 2個 | 1000円 |
| 2018/5/2 | 刺し身 | 1個 | 600円 |
正規化する
商品マスタ
| 商品コード | 商品名 | 価格 |
|---|---|---|
| M01 | コロッケ | 300円 |
| M02 | メンチカツ | 400円 |
| M03 | ハンバーグ | 500円 |
| M04 | 刺し身 | 600円 |
売上情報
| 売上日 | 商品コード | 数量 |
|---|---|---|
| 2018/5/1 | M01 | 5個 |
| 2018/5/1 | M02 | 2個 |
| 2018/5/1 | M03 | 4個 |
| 2018/5/1 | M04 | 6個 |
| 2018/5/2 | M01 | 4個 |
| 2018/5/2 | M02 | 4個 |
| 2018/5/2 | M03 | 2個 |
| 2018/5/2 | M04 | 1個 |
非正規化状態では同じ「商品名」が何度も登場していたため、これを別テーブルに切り分けた。
また商品が決まると単価が決まるため、「売上価格」カラムを無くして商品マスタに「単価」カラムを持たせた。
切り分けた両テーブルの商品をつなげるために「商品コード」カラムを作り、関係性を持たせた。
テーブルを結合してデータを取得する
SELECT T1.売上日,
T2.商品名,
T1.数量,
T1.数量*T2.価格 AS "売上価格"
FROM 売上情報 T1
INNER JOIN 商品マスタ T2
ON T1.商品コード=T2.商品コード
ORDER BY T1.売上日, T1.商品コード;
SQLの各部位の説明
SELECT T1.売上日,
T2.商品名,
T1.数量,
そのままの意味。
「売上日」、「商品名」、「数量」のカラムを取得している。「T1」「T2」については後述。
T1.数量*T2.価格 AS "売上価格"
SQLでは四則算を書くことが出来る。
数量と価格の積に「売上価格」の名前をつけている。
FROM 売上情報 T1
売上情報テーブルから情報を取得し、「T1」の別名をつけている。
カラムを指定する際に「どのテーブルのカラムか」を明示するために「T1.売上日」のように使う。
INNER JOIN 商品マスタ T2
ON T1.商品コード=T2.商品コード
今回のキモ。売上情報テーブルと商品マスタをつなげている箇所。
１行目の「INNER JOIN」は「内部結合をする」の意味、内部結合とは「条件に合致したレコードのみを採用する」こと。
２行目の「ON」で「レコード同士をつなげる条件」を指定している。
今回は「売上情報テーブルと商品マスタの商品コードが同じレコードをつなげる」とした。
ORDER BY T1.売上日, T1.商品コード;
情報の並び順の指定、カラム名の後に昇順か降順かを指定できる。
昇順の場合はASC、降順の場合はDESCをつける。省略した場合はASC(昇順)になる。
複数カラムを指定した場合、最初のカラムで並び替えた後、最初のカラムが同じ値の中で次のカラムで並び替えを行う。
今回は「売上日の昇順で並べ、同じ売上日の場合は商品コードの昇順で並べる」としている。
最初から石の中にあった
今回はテーブルの正規化から取得まで通したため、「元からどのようにつなげるデータだったのか」が分かりやすかったと思います。
同じように、これから業務で直面する「すでに正規化済みのテーブルたち」も「どのようにつなげるデータなのか」は決まっているのです。
これが「分割されたテーブル同士を元の形に戻す」という認識につながります。
その他のSQLの装飾について
膨大になるので今回はここまでとします。
「レコード数を数える(COUNT)」「最大値をもとめる(MAX)」「グループ化する(GROUP BY)」など、
いろいろなことが出来ます。
何が出来るか、どのように使うか、細かい話はGoogle先生に聞いてください。
おわりに
小さなデータベース、小さなテーブルを相手にする分にはこの辺りの基本を押さえればまずはOKです。
大きなデータベース、大きなテーブルを相手にするようになったら、基本にして奥義でもある「インデックス」や「実行計画」などを意識することになります。
もっと言えばI/Oとかネットワークとかに掛かる時間などに頭を悩ませるかもしれません。
皆さんが多くの業務に携わり、頭を悩ませられるようになる一助となれば幸いです。
次は
データベースに限るならキー、インデックス、トランザクション回りの話が妥当か。
業務で使うことを考えると静的プレースホルダや三層アーキテクチャの話もしたい。