テーブルについて
単一のテーブルからデータを取り出すのは簡単です。
しかし、複数のテーブルから目的のデータを取り出すには、テーブル同士の関連など、ある程度の前提知識がないと難しいものです。
SELECT文を使って目的のデータを取得するためには、少しテーブルの作りについて知識を深めておく必要があります。
特に、テーブルのデータの持ち方や、主キーと外部キーは、SQLを扱うにあたって重要な項目ですのでここでざっと確認しておきましょう。
目次
1. データの持ち方（1セル単一データ）
データを効率よく管理するために、テーブルに保存するデータは、「1セル単一データ」という基本があります。
（簡単のために「セル（桝）」という言葉を使います。）
例えば、趣味を保存するテーブルを見てみましょう。
下は1セル複数データの例です。
| name | hobby |
|---|---|
| 鈴木 | テニス,写真,キャンプ |
| 佐藤 | 野球,ゲーム,写真 |
hobby列には、カンマ区切りで複数のデータが保存されています。
このような形は、効率よくデータが管理できないので悪いとされています。
下は上のテーブルを1セル単一データに直した例です。
| name | hobby |
|---|---|
| 鈴木 | テニス |
| 鈴木 | 写真 |
| 鈴木 | キャンプ |
| 佐藤 | 野球 |
| 佐藤 | ゲーム |
| 佐藤 | 写真 |
このように、ほとんどのテーブルは、「1セル単一データ」のみが保存されていると思ってください。
（さまざまな事情で、1セル複数データのテーブルも存在しています。）
詳しくは、データベースの設計の話になってしまいますので、割愛します。
テーブルの行と列
ここでもう一度、テーブルの行と列について確認しておきましょう。
行は横方向を指します。
列は縦方向を指します。
1-1. 列
列は、カラムなどとも呼ばれます。
列には列名があります。
SQLで列を指定する際には、この列名を指定します。
列名を指定することで、必要な列のデータのみ取得することができます。
1-2. 列の型
テーブルの列には型があります。
この型によって列に保存できる値が変わってきます。
列の型にはさまざまなものがありますが、ここでは砂場SQLの練習用テーブルに設定されてる3つの型に簡単に触れておきます。
練習用テーブルには以下の3つの型を使用しています。
| 型 | 値 |
|---|---|
| INT | 整数 |
| VARCHAR | 文字列 |
| DATE | 年月日 |
整数を取り扱う列はにはINT型が設定されています。
INT型が設定されている列は、-2,147,483,648 ～ 2,147,483,647までの数値を保存できます。
文字列を取り扱う列にはVARCHAR型が設定されています。
255文字までの文字列を保存できるようにVARCHAR型を設定しています。
年月日を取り扱う列にはDATE型が設定されています。
DATE型の列は、「2022-03-22」のような年月日形式のデータを保存できます。
SQLでは、文字列やDATE型の値を指定する場合は、その対象をシングルクオート（'）で囲ってください。
数値の場合はシングルクオート（'）は必要ありません。
数値
文字列
年月日
1-3. 行
行には、列の列名のような名前はありません。
行を特定するには、名前の代わりにテーブルに保存されている値を指定します。
名簿テーブルがあるとします。
| name | kana | ken |
|---|---|---|
| 高橋 | たかはし | 東京 |
| 佐藤 | さとう | 埼玉 |
| 鈴木 | すずき | 北海道 |
このテーブルから最近名簿に登録された「あの佐藤さん」のデータを取得したいとします。
この場合は、nameに「佐藤」を指定すれば、「あの佐藤」さんの行が取り出せます。
| name | kana | ken |
|---|---|---|
| 佐藤 | さとう | 埼玉 |
しかし、名簿テーブルに同姓同名で県も同じ人がいたらどうなるでしょう。
| name | kana | ken |
|---|---|---|
| 高橋 | たかはし | 東京 |
| 佐藤 | さとう | 埼玉 |
| 佐藤 | さとう | 埼玉 |
| 鈴木 | すずき | 北海道 |
nameに佐藤を指定すると、2名の佐藤さんが取り出せてしまいます。
| name | kana | ken |
|---|---|---|
| 佐藤 | さとう | 埼玉 |
| 佐藤 | さとう | 埼玉 |
困ったことに、「あの佐藤」さんの区別がつきません。
このテーブルには「主キー」がないために重複したデータができてしまいます。
2. 主キー
主キーとは、行を一意に識別するためのキーのことです。
一意とは、重複がないということです。
例えば、学生を特定するための「学籍番号」や、ユーザを特定するための「ユーザID」などが主キーに選ばれることが多いです。
主キーは列に対して指定します。
主キーに選ばれた列の値は、重複のないことが保証されます。
上の困ったテーブルに「user_id」という列を追加してこの列を主キーとしました。
user_id列には、主キー制約を付けてあります。
| user_id | name | kana | ken |
|---|---|---|---|
| 1 | 高橋 | たかはし | 東京 |
| 2 | 佐藤 | さとう | 埼玉 |
| 3 | 佐藤 | さとう | 埼玉 |
| 4 | 鈴木 | すずき | 北海道 |
これで、user_idが予めわかっていれば、user_id=3を指定して、「あの佐藤」さんを取り出せば良いですし、name=佐藤で取り出したとしても、user_idの違いから「あの佐藤」さんを区別することができます。
| user_id | name | kana | ken |
|---|---|---|---|
| 2 | 佐藤 | さとう | 埼玉 |
| 3 | 佐藤 | さとう | 埼玉 |
また、主キーは複数の列に対して指定することもできます。
主キーとなった複数の列の値の組み合わせは一意に保証されます。
例えば下のテーブルのnameとkenに主キーが設定されていれば、
| name | kana | ken |
|---|---|---|
| 高橋 | たかはし | 東京 |
| 佐藤 | さとう | 埼玉 |
| 鈴木 | すずき | 北海道 |
新たに「佐藤 さとう 埼玉」というデータを挿入することができません。
主キーに設定されているnameとkenの組み合わせた値と重複（佐藤と埼玉）するからです。
こちらの「佐藤 さとう 茨城」というデータは挿入可能です。
佐藤と茨城という組み合わせの値が無いからです。
テーブルには行を特定するための主キーがあると思ってください。
3. 外部キー
外部キーとは、別のテーブルのあるキー（ほとんどは主キー）を参照するキーのことです。
外部キーとして指定された列の値は、その参照先のテーブルの列の値であることが保証されます。
ある人の趣味を管理するテーブルを見てみましょう。
一人の人には複数の趣味があります。
話を簡単にするためにかなり簡略化したテーブルを用意します。
ユーザ
| user_id | name | kana | ken |
|---|---|---|---|
| 1 | 高橋 | たかはし | 東京 |
| 2 | 佐藤 | さとう | 埼玉 |
| 3 | 鈴木 | すずき | 北海道 |
趣味
| hobby_id | name |
|---|---|
| 1 | キャンプ |
| 2 | テニス |
| 3 | 読書 |
ユーザ趣味
| hobby_id | name |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 3 | 3 |
ユーザテーブルと、趣味テーブルはそれぞれ独立していますが、ユーザ趣味テーブルはユーザテーブルと趣味テーブルに関連したデータを保持しています。
ユーザ趣味テーブルのuser_idは、外部キーとしてユーザテーブルのuser_idを参照しています。
これでユーザ趣味テーブルのuser_idの値は、ユーザテーブルのuser_idの値であることが保証されます。
ユーザ趣味テーブルのhobby_idは、外部キーとして趣味テーブルのhobby_idを参照しています。
これでユーザ趣味テーブルのhobby_idの値は、趣味テーブルのhobby_idの値であることが保証されます。
ユーザ趣味の値が保証されているといことは、ユーザ趣味テーブルには、ユーザテーブルのuser_idの値と趣味テーブルのhobby_idの値しか存在しないということです。
ユーザ趣味に対して、「100 200」などというどのテーブルにも存在しないデータは挿入できません。
これらのテーブルをSQLでうまくつなぎ合わせることで、下のようなデータを取り出すことができます。
| user_id | name | hobby |
|---|---|---|
| 1 | 高橋 | キャンプ |
| 2 | 佐藤 | キャンプ |
| 3 | 鈴木 | テニス |
| 3 | 鈴木 | 読書 |
関連のあるテーブル同士は外部キーでつながっていると思ってください。
（さまざまな事情で、外部キーのないテーブルも存在しています。）
テーブル同士の関連を文章で読むのは大変です。
そんなときによく使われるのがER図と呼ばれる図です。
4. ER図
ER図は、テーブル同士の関連を四角と線で表現した図です。
ER図の書き方は、複数ありますが、ここでは、一般的なIE表記を使います。
例で使用するER図は、ERマスターというツールで作成したました。図に少し癖がありますが、大事なところは伝わると思いますのでこのままERマスターの図で進めます。
四角で囲われたものがテーブルです
。
四角の上部に表示されているのがテーブル名です。
テーブル名の下には、テーブルが持つ列名があります。
列名横のカッコ内の文字（PK,FK,PFK）は次のことを表しています。
PKは主キー（primary key）です。
FKは外部キー（foreign key）です。
PFKは主キーかつ外部キーということです。
テーブル間の線は、テーブル同士の関連を表しています。
線は、「１対１」「１対多」「多対多」などのテーブル間の関連によって使い分けます。
「多対多」の関連はここでは割愛します。
4-1. １対１
１対１の関連は、「自分の１つのデータには、相手方に１つのデータが存在する」といった関係です。
テーブルが線で繋がっています。線の縦棒は１つということを意味しています。
この例では、1人のユーザには必ず１つメールアドレスが存在するという意味です。
まさに、データが１対１の関係というわけです。
具体的なデータは下のようになります。
User
| user_id | name |
|---|---|
| 1 | 佐藤 |
| 2 | 高橋 |
| 3 | 田中 |
Mail_address
| user_id | mail_address |
|---|---|
| 1 | sato@sym.mobi |
| 2 | takahashi@sym.mobi |
| 3 | tanaka@sym.mobi |
3人ともメールアドレスを1つ持っています
。このER図からは
、ユーザは必ず１つのメールアドレスを持っていると読み取れます。
線の片側に●が付いている場合は、上で言う「必ず」というのが消えます。
（この●は、多重度とかカーディナリティなどと呼ばれるものです。一般的には白丸「〇」で書きますが、黒丸はERマスターの癖の一つと思ってください。）
この例では、1人のユーザは、メールアドレスを持っていないか１つ持っているという意味になります。
要するに、●が付いた側は、「０または１」という見方になります。
１対０または１ということです。
具体的なデータは下のようになります。
| user_id | name |
|---|---|
| 1 | 佐藤 |
| 2 | 高橋 |
| 3 | 田中 |
Mail_address
| user_id | mail_address |
|---|---|
| 1 | sato@sym.mobi |
| 3 | tanaka@sym.mobi |
佐藤さんと田中さんはメールアドレスを１つ持っていますが、高橋さんはメールアドレスを持っていません。
このER図からは、ユーザはメールアドレスを持っていないか1つ持っていると読み取れます。
4-2. １対多
１対多の関連は、「自分の１つのデータには、相手方に複数のデータが存在する」といった関係です。
IE表記では、１対多の「多」の方が、3本線（通称鳥の足）になっているのが特徴です。
この例では、1人のユーザには必ず1つ以上のメールアドレスが存在するという意味です。
データが１対多（1以上）の関係というわけです。
具体的なデータは下のようになります。
| user_id | name |
|---|---|
| 1 | 佐藤 |
| 2 | 高橋 |
| 3 | 田中 |
Mail_address
| mail_id | mail_address | user_id |
|---|---|---|
| 1 | sato@sym.mobi | 1 |
| 2 | sato2@sym.mobi | 1 |
| 3 | takahashi@sym.mobi | 2 |
| 4 | takahashi@sym.mobi | 2 |
| 5 | tanaka@sym.mobi | 3 |
佐藤さんと高橋さんは、メールアドレスを2個持ち、田中さんは、メールアドレスが1つです。
このER図からは、ユーザは必ず１つ以上のメールアドレスを持っていると読み取れます。
線の片側に●が付いている場合は、上で言う「必ず」というのが消えます。
この例では、1人のユーザは、メールアドレスを持っていないか1つ以上持っているという意味になります。
要するに、●が付いた側は、「０以上」という見方になります。
具体的なデータは下のようになります。
| user_id | name |
|---|---|
| 1 | 佐藤 |
| 2 | 高橋 |
| 3 | 田中 |
Mail_address
| mail_id | mail_address | user_id |
|---|---|---|
| 1 | sato@sym.mobi | 1 |
| 2 | sato2@sym.mobi | 1 |
| 3 | takahashi@sym.mobi | 2 |
佐藤さんは2つ、高橋さんは1つメールアドレスを持っていますが、田中さんはメールアドレスを持っていません。
このER図からは、ユーザは0個以上のメールアドレスを持っていると読み取れます。
ほとんどの場合、テーブルとテーブルの関連は「1対多」となっています。
ER図を読むコツとしては、「1対多」の「1側」のテーブルの主キー（PK）を確認します。
多くの場合、その主キー（PK）と、相手方のテーブルのどこかの列が外部キー（FKまたはPFK）として繋がっています。
上の方で例にでてきたユーザの趣味を管理するテーブルをER図にすると下のようになります。
このER図は、「1対多」の線が2本あります。
「1側」のテーブルUserの主キー（PK）を確認するとuser_idです。
線と繋がっている先の「多側」のテーブルUser_hobbyにはUserの主キーと繋がる外部キー（FKまたはPFK）があるはずです。
「1側」のテーブルHobbyの主キー（PK）を確認するとhobby_idです。
線と繋がっている先の「多側」のテーブルUser_hobbyにはHobbyの主キーと繋がる外部キー（FKまたはPFK）があるはずです。
といった感じで、読んでいくといいかもしれません。
このER図からは、
ユーザには必ず1つ以上の趣味がある。
趣味がないユーザはいない。
などと読み取れます。
5. まとめ
テーブルについて以下のことを確認しました。
テーブルに保存されているデータは1セル単一データが基本です。
列には列名があります。
列を指定するには列名を使います。
行を指定するには保存されている値を使います。
テーブルには主キーが設定されています。
主キーとなった列の値は重複しないことが保証されます。
他のテーブルを参照するテーブルには外部キーが設定されています。
外部キーとなった列の値は、参照先テーブルに存在する値ということが保証されます。
外部キーの設置によってテーブルとテーブルの関連（リレーションシップ）が明確になります。
テーブルとテーブルの関連（リレーションシップ）を図にしたものがER図です。
リレーションシップには「１対１」「１対多」「多対多」などがあります。