SQLに必要な知識をまとめてみた: テーブル作成, 主キー, 正規化等

概要

SQLで自分なりに勉強した内容を可能な限りアウトプットしてまとめていきたいと思っている。

この記事はざっくりとSQLについて必要な知識を網羅していく形になります。
それぞれの項目はざっくりとした説明になると思うので内容について細かく調べたくなった時は別のサイトや別の記事を見た方が参考になるかもしれないです。

自分の学んだ知識のアウトプットと読んだ人がSQLを学ぶ上で何を学ぶべきかを把握して頂くのが主な目的なので間違えてる部分ももしかしたらあるかもしれませんが（可能な限り間違えない努力はしましたが自分の理解力不足で間違えた解釈をしてしまっている部分がある可能性はあります。）間違えてる部分がもしありましたらコメントで指摘して頂けると嬉しいです。

また、網羅するとは言いましたが自分が学んだ範囲の内容を網羅するという意味なので実際はカバーできていない内容も多いかもですがご了承ください。

環境はOracleSQLDeveloperを用いているので一部MySQLとは異なる部分があるかもしれません。

また、便宜上テーブル名や変数名等を日本語で記述していますが環境や設定によってはテーブル名や変数名を英語にしないとエラーを吐くかもしれません。（自分の環境では英語じゃない場合エラーを吐きましたが日本語の方がわかりやすいという理由でこの記事ではテーブルや変数名等は日本語で定義しています。）

テーブルの作成について

create table テーブル名 (
    属性 定義域,
    ...
    属性 定義域
);

で構成される。アルファベットの大文字と小文字は気にしなくていいのでCREATE TABLEと書いても問題ない。（同様に改行位置も気にしなくてよい。）
属性と定義域という用語についてだが

というテーブルがあるとすると顧客ID、姓、名が属性に当たる。

定義域（ドメイン）は他のプログラミング言語で言う型と似たような物である。
例えば　「姓　varchar(5)」 と定義すると最大五文字まで格納できる姓という属性を定義する。

なお文字列を表す型にはchar型とvarchar型があるがvarchar型は可変長文字列でchar型は固定長文字列である。可変ではあるが()の中で最大文字数を指定する。

charを使う場合も()の中で文字数を指定するが指定した文字数未満のデータがテーブルに入る場合足りない分の文字は指定された長さになるように右側が空白で埋まる。

他にもnumber型等のデータ型があるが説明はここでは割愛する。
（下記の引用元に他のデータ型についても細かく解説がのっている。）
(引用元：Oracle Database SQLリファレンス)
作ったテーブルを削除する場合は

drop table テーブル名;

で指定したテーブルを削除することができる。

主キー

テーブルの定義において主キーというのを定義する必要があるので主キーについてここで説明する。
ざっくりとした説明になるがテーブルの中で一行を特定することが可能な属性の最小の集合のことを主キーと言う。

上記のテーブルの例だと姓と名は主キーになりえないが（同姓の顧客データが他に入ってる可能性があるので佐藤だけわかってもそれだけでどの行か特定できない。名も同様。）顧客IDは客別にそれぞれ別々のIDが与えられているはずなのでこれがわかるだけでどの顧客か（テーブルのどの列か）特定可能である。

つまり主キーはテーブルの中で絶対に重複しえない属性を選択する必要がある。
（また、主キーにはNULLが入ることも許されない。）
先ほど最小の集合と言ったが主キーは二つ以上の属性の組み合わせの場合もある。

例えば上記のテーブルだと顧客IDのみで特定できるのは顧客の姓名の列だけで何を注文したかまで特定できない。この場合主キーは{顧客ID,商品ID}の組み合わせになる。

主キーの設定はCreate tableの定義の中に

primary key (顧客ID, 商品ID)

を変数定義後に書く。

テーブルの作成

テーブルの定義を終えたら実際にデータを定義したテーブルに入れる作業が必要になる。
その場合

insert into テーブル名 values ( 値１, 値２, 値３, ... );

をテーブルの定義後に使う。
例えば
テーブルを

create table 顧客データ (
    顧客ID number(30),
    商品ID number(30),
    姓 varchar(30),
    名 varchar(30),
    注文品 varchar(30),
    primary key (顧客ID, 商品ID)
);

と定義した後に

insert into 顧客データ values ( 123456, 111111, '佐藤','一郎','鉛筆');

とやることでテーブルへのデータの挿入が完了する。
（文字列型はシングルクォーテーションで囲む)

外部キー

外部キーとは二つのテーブルで関連する列を指定することである
例えば

のテーブル以外に

のようなテーブルがある場合
片方のテーブル顧客IDはもう片方のテーブルの顧客IDを参照している。
参照される側が親テーブル、参照する側が子テーブルという。
下側のテーブルを

create table 顧客情報 (
    顧客ID number(30),
    性別 varchar(10),
    primary key (顧客ID),
    foreign key (顧客ID) references　顧客データ(顧客ID)
);

とした場合顧客情報が子テーブルで顧客データが親テーブルになる。
参照先は親テーブルの主キーである必要があることに注意（子テーブルの主キーである必要はない）。
このように関連づけることで

insert into 顧客情報 values ( 121212, '男');

と子テーブルに参照先の親テーブルのカラムにない値が含まれた行を追加しようとするとエラーを吐くように制約をつけることができる。

また親テーブル側のデータに対して削除をしたり更新したりしようとしたとき、対象となる値がすでに子テーブル側で使用されている場合には、エラーとなったり同時に削除や更新を行ったりという選択を行える。
(引用元：DB Online 「FOREIGN KEY制約(外部キー制約を設定する)」)

select文

作ったテーブルを実際に画面に表示するのにはselect文を使う必要がある。
select文の基本形は

select 属性名 from テーブル名;

となっている。
改行などは任意の場所でしても問題ない。
先ほどのテーブルの例に戻るが

と行を先ほどより増やしたテーブルを考える。
これで例えば

select 顧客ID, 姓, 名 from 顧客データ;

と記述すると

と指定した属性のデータのみをテーブルから取り出すことができる。
また

select 顧客ID, 姓, 名 from 顧客データ where 注文品 = 'ゲーム機';

とすることにより

このように注文品がゲーム機の顧客の指定した属性のデータを取り出して表示できる。
全てのデータを表示したい時はテーブルの全ての属性を一つずつselectの後に書くこともできるが

select * from 顧客データ where 注文品 = 'ゲーム機';

とアスタリスクを使うことで

このようにwhere句で指定した条件を満たす行の全ての属性を取り出せる。
他にもwhere句の条件式には">"、"<"、">="、"<="、"!="等様々な演算子が使える。SQL文では条件式でイコールを表す時は"=="ではなく"="であるので注意が必要かもしれない。
また、条件式では

select * from 顧客データ where 名前 like '_郎';

と曖昧検索することもできる。この場合だと'_'は任意の一文字という意味であるから

と名前の二文字目が「郎」で終わるデータが入ってる行が全て取り出される。

他にも%を使うことで任意の複数文字（名前 like '%郎'とすると郎で終わる名前の人の行が全て取り出される。_郎では例えばテーブルの「名」属性に十一郎を含む行があるとしたらその行は取り出せないが%郎だと取り出せる。)の曖昧検索ができるので覚えておくと便利かもしれない。

他にもselectの後の属性名指定のところにavg(平均を取りたい属性名),max(最大値を取りたい属性名),min(最小値を取りたい属性名),count等があるがここらへんは自分が理解しきれていない部分もあるのでここでの説明は割愛する。(興味がある人はgroup by句やhaving句等と並行して調べてみるといいかもしれない。)

JOIN、UNION、EXCEPT(MINUS)について

複数のテーブルを作成した後にそれらのテーブルを結合させることができる
こうすることにより別々のテーブルのデータの検索を一つのテーブルにまとめてからすることができる。
結合の種類はいくつかあるが

クロス結合
内部結合
外部結合

等があるが
内部結合と外部結合が特に使用頻度が高いのでこの二つの用語の意味をざっくり解説していこうと思う。

内部結合
それぞれのテーブルの指定したカラムが一致したものだけを取り出す。

外部結合
それぞれのテーブルの指定したカラムが一致したものだけを取り出すだけではなくどちらかのテーブルにしか存在しない値も取り出す。（どちらのテーブルから取り出すかはLEFT OUTER JOINかRIGHT OUTER JOINで変わる。)
(引用元：SQL素人でも分かるテーブル結合(inner joinとouter join))
外部結合と内部結合の具体例については上記の引用元の記事がとてもわかりやすかったので気になった人はこちらを読むことをオススメしたい。

これ以外にもUnionやExceptというのもある。

Unionについてだが上記の外部結合や内部結合と違いつなげる二つのテーブルのカラム数が一致してないといけないという条件があるが複数のselect文の結果を縦に結合することができる。（内部結合と外部結合は横に結合するのでここも異なる点である。）
例えば

というテーブルAと

というテーブルBがあったとすると

select * from テーブルA
union
select * from テーブルB;

とすることで上記と同じテーブルが得られる。　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
Except(もしくはminus)についてだが

というテーブルAと

というテーブルBがあるとすると

select * from テーブルA
except
select * from テーブルB;

とすることで

を得ることができる。
テーブルAの行の中でテーブルBで返されない行だけを抽出する。
引き算のイメージをするとわかりやすいかもしれないがそのイメージであると

と

でminusをとった場合は

を返すので引き算でイメージするよりは上記のテーブルA minus テーブルBはテーブルAの行の中でテーブルBで返されない行だけ抽出するで認識した方がよいかもしれない。

候補キーとスーパーキー

候補キーとは名前の通り主キーの候補になるキーのことである。
（主キーと同様にテーブルの全ての属性を一意に識別できる最小の集合である。）

つまり候補キーが一つのテーブルに一つしか存在しない場合は候補キー=主キーとなる。候補キーも主キーと同様に重複する値は認められないが主キーと違ってNULLは認めるのも候補キーと呼ばれるが実際に候補キーにNULLを認められるかどうかは諸説のようである。
(引用元：wikipedia)

主キーはテーブルのうちでただ一つだけだが（候補キーの中から一つ選ぶ）テーブルを一意に識別できる最小の集合となる属性が他にある場合それらはすべて候補キーとなる。

一方スーパーキーというのは簡単に言うと候補キーに余分な要素を加えたものである。つまり候補キーの全ての属性を一意に識別できる最小の集合から「最小」を抜いたものである。

だと{顧客ID,商品ID}は主キーであり候補キーでありスーパーキーでもある。
スーパーキーはこれに加え{顧客ID,商品ID,姓},{顧客ID,商品ID,名},{顧客ID,商品ID,注文品},{顧客ID,商品ID,姓,注文品},{顧客ID,商品ID,姓,名},{顧客ID,商品ID,名,注文品},{顧客ID,商品ID,姓,名,注文品}はすべてスーパーキーである。
つまり一意にテーブル全体を識別できる全ての組み合わせをまとめてスーパーキーと呼ぶ。

正規化

正規化というのはデータの整合性を高めるためにテーブルを整理してデータの冗長性を減らすプロセスのことである。
正規化にはいくつかプロセスがあるが順をおって説明する。
なおこの項目に関してはテーブルの具体的な例が浮かばなかったのと実際に具体的なテーブルの例が浮かんだ後にそれをちゃんと正規化できる自信があまりないので具体的なテーブルではなく{属性1,属性2,属性3,属性4}のような属性の集合(これらの属性が表のそれぞれのカラムを表してると仮定する)で説明する。
具体的なテーブルの正規化は以下の記事を参考にするといいかもしれない。

正規化の要点を理解する

第一正規化
第一正規化は一つのカラムに1つの値のみ設定されている状態にすることである。
つまり基本的にSQL文でテーブルを定義し作成した場合すでに第一正規系になっている。

第二正規化
第二正規化は第一正規化されたテーブルから候補キーの部分関数従属性を取り除いたものである。具体的には
{属性1,属性2,属性3,属性4,属性5}というテーブルがあるとする。
また、属性1 → 属性4, {属性1, 属性3} → 属性2, 属性2 → 属性5
という関係性がこのテーブルにあるとする。

この場合候補キーは{属性1, 属性3}である。
({属性1, 属性3}は属性2を一意に決定し、属性2は属性5を一意に決定するので{属性1, 属性3} → 属性5であると言えるため)

この場合候補キーである{属性1, 属性3}の部分集合である属性1が属性4を一意に決定してしまっているため部分関数従属性が存在する。
つまりこのテーブルを
{属性1,属性4}, {属性1,属性3,属性2,属性5}

と分割する。分解するときは元の依存関係が維持されてるかのチェックをしながら正しい分解を考えていく。
例えば左のテーブルは属性1 → 属性4、右のテーブルは{属性1, 属性3} → 属性2と属性2→属性5の関係を示しているのでこれらの和集合はちゃんと元のテーブルの関係性と一致する。
また、情報無損失分解にちゃんとなってないかも確認する必要がある。
情報無損失分解とは簡単に言えば
分解した二つのテーブルの共通部分が分解した二つのテーブルのどちらか片方（両方でもいい）のデータを一意に定めることが条件である。

今回の例だと先ほどの二つの分解したテーブル{属性1,属性4}, {属性1,属性3,属性2,属性5}の共通部分は属性1であり属性1は左側のテーブルの全てを一意に定めるのでこれは情報無損失分解である。

第三正規化
第三正規化は
第三正規系の説明に入る前に非キー属性という用語について簡単に説明する。
非キー属性とは名前の通り候補キーに含まれない属性のことである。
候補キーの部分集合は非キー属性ではないので注意が必要である。
第三正規化についてだが
第二正規化されたテーブルから非キー属性の推移関数従属性を消す作業である。

推移関数従属性とは簡単に言うなら例えば属性1 → 属性2で属性2 → 属性3であるとき属性1 → 属性3とも言える。これが推移関数従属性である。
{属性1,属性4}, {属性1,属性3,属性2,属性5}
このテーブルは右側が{属性1, 属性3} → 属性2, 属性2 → 属性5となっているので非キー属性である属性2が非キー属性である属性5を定めてしまっているので第三正規系の条件を満たしていない。
よって
{属性1,属性4}, {属性1,属性3,属性2,属性5}を第三正規化してみる。
右側の{属性1,属性3,属性2,属性5}を{属性1,属性3,属性2},{属性2,属性5}と分解する。
分解するので情報無損失分解かもチェックする。
共通部分は属性２であり属性２は右側のテーブルを一意に定めるので情報無損失分解である。
また、元の依存関係に関しても先ほどと同様のやり方で満たしていることがわかる。よって第三正規系は{属性1,属性4}, {属性1,属性3,属性2},{属性2,属性5}となる。

注意点としては第三正規化は第二正規化されたテーブルであるという前提があることである。
例えばあるテーブルを見た時に推移性が見られなくてこれは第三正規化されたテーブルだ！と決めつけてしまうのは早くてその前にそのテーブルが第一、第二正規系を満たしているかもチェックしないといけない。

ボイスコッド正規形
ある関係上に存在する自明でない全ての関数従属性の決定項が候補キーであるときそれをボイスコッド正規系と言う。
(引用元：wikipedia)
つまり簡単に言えば第三正規化されたテーブルのそれぞれのテーブルの決定項（属性1→属性2の場合決定項は属性1）が全て候補キーである必要がある。
今回のテーブルだと
{属性1,属性4}, {属性1,属性3,属性2},{属性2,属性5}
であり左のテーブルは属性1が候補キーで真ん中は{属性1,属性3}が候補キーで一番右は属性2が候補キーであるのでこれはすでにボイスコッド正規系である。（非キーが決定項である関連性がどのテーブルにも存在しない)
ボイスコッド正規系も第三正規系と同様にボイスコッド正規系の条件だけでなく第一、第二、第三正規系の条件も満たしている前提があることに注意が必要。

まとめ

自分自身知識が整理しきれてないのもあり(後半部分は特に顕著だが)やや雑な説明になってしまったかもしれないがざっくりとSQLを学ぶために必要な知識が何か等の全体像がこの記事で伝われたら個人的に喜ばしいことだと思っている。
正規化する理由やサブクエリの解説なども入れたかったが僕自身の力不足もあり断念した。
もし興味があるのなら調べてみるといいかもしれない。
SQLが想像以上に難しくて奥深い言語であることが学んだ時も実感したが実際にこうして記事で整理しようとすると余計実感した。
記事を書く前に書きたいなと思ったことが実際に説明しようとするとうまく説明できずに断念したりいざ書こうとして自分がその項目をあまり理解してないと気づいたり等様々な発見が記事を書いてみてあった。
これからもSQL含め様々なことを積極的に学んで奥深さや難しさを実感していきたいと思う。