【DB】テーブル定義に困ったのでポイントを整理してみる

こんにちは。あがさんです！！

ここ最近は、DBとSQLをほぼゼロから始める異世界生活でした。
仕入れた知識が抜け落ちないように記事にしようと思います！

## そもそもテーブルとは
リレーショナルデータベースのデータを管理、保存するための入れ物。 
図書館で例えると本棚のポジションになります。

```データベース(図書館) > テーブル（本棚） > データ・レコード(本)```
みたいなイメージです。

|id|name|pass|
|:---|:---|:---|
|1|太郎|*********|
|2|次郎|********|

行ごとの情報（太郎、次郎）をレコード、
列ごとの項目（id、name、pass）をカラムと呼びます。


## どうやってテーブル定義する？
実際にSQL構文を書ければすぐできるのですが、頭の中で取り扱う情報ごとに要素の洗い出しが必要になります。場合によっては`テーブルを複数に分けた方がデータを扱いやすくなります。`テーブルを作成する前にカラムに対してどんな設定を施すか検討します。

今回はサンプルとして**G_gandum_db**を作成しました。

#### Fighters テーブル
| Field  | Type        | Null | Key | Default | Extra |
|:-------|:------------|:----:|:---:|:--------|:-----:|
| id     | int         | NO   | PRI | NULL    |   AI  |
| name   | varchar(50) | YES  |     | NULL    |       |
| nation | varchar(50) | YES  |     | NULL    |       |
| emblem | varchar(50) | YES  |     | NULL    |       |
| mf_id  | int         | YES  | MUL | NULL    |       |


`外部キー制約：　Fighters.mf_id -> MobileFighters.id`
<br>
※外部キーを設けることで、Fightersテーブルのmf_idはMobileFightersテーブルのidを参照して、MobileFightersテーブルの内容も一緒に抽出できるようになります。
<br>

#### MobileFighters テーブル
| Field  | Type        | Null | Key | Default | Extra |
|:-------|:------------|:----:|:---:|:--------|:-----:|
| id           | int         | NO   | PRI | NULL    |    AI  |
| name         | varchar(50) | YES  |     | NULL    |       |
| special_move | varchar(50) | YES  |     | NULL    |       |

```
ターミナルで表示する場合は
mysql> DESCRIBE Fighters;
mysql> DESCRIBE MobileFighters;
```

## テーブル作成

```SQL:テーブル作成 
-- MobileFightersテーブルの作成_AIを追記
CREATE TABLE MobileFighters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    special_move VARCHAR(50)
);

-- Fightersテーブルの作成_AIを追記
CREATE TABLE Fighters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    nation VARCHAR(50),
    emblem VARCHAR(50),
    mf_id INT,
    FOREIGN KEY (mf_id) REFERENCES MobileFighters(id)
);
```

```SQL:データを入力
-- MobileFightersテーブルにデータを挿入
INSERT INTO MobileFighters (id, name, special_move) VALUES
(1, 'ゴッドガンダム', '爆熱ゴッドフィンガー'),
(2, 'ガンダムマックスター', 'バーンガ・マックス'),
(3, 'ドラゴンガンダム', '真・流星胡蝶剣'),
(4, 'ガンダムローズ', 'ローズ・ビット'),
(5, 'ボルトガンダム', 'グラビトンハンマー');

-- Fightersテーブルにデータを挿入
INSERT INTO Fighters (name, nation, emblem, mf_id) VALUES
('ドモン・カッシュ', 'ネオジャパン', 'キング・オブ・ハート', 1),
('チボデー・クロケット', 'ネオアメリカ', 'クイーン・ザ・スペード', 2),
('サイ・サイシー', 'ネオチャイナ', 'クラブ・エース', 3),
('ジョルジュ・ド・サンド', 'ネオフランス', 'ジャック・イン・ダイヤ', 4),
('アルゴ・ガルスキー', 'ネオロシア', 'ブラック・ジョーカー', 5);

-- 訂正：主キーのidにAutoインクリメントを設定していれば、idの値の入力を省略可能
```

-----
##### データベースが出来たので、試しに何か抽出してみます!!
```SQL
-- "キング・オブ・ハート"の称号を持つガンダムファイターの出身国と搭乗機体をそれぞれ出力。
SELECT Fighters.name AS "ガンダムファイター", Fighters.nation AS "出身国"MobileFighters.name AS "登場機体"
  FROM Fighters
  JOIN MobileFighters
    ON Fighters.mf_id = MobileFighters.id
 WHERE emblem = 'キング・オブ・ハート';
 
```
**出力結果**
| ガンダムファイター  | 出身国       |搭乗機体       |
|:---------------|:-------------|:-------------|
| ドモン・カッシュ     | ネオジャパン    | ゴッドガンダム   |
-----
```SQL
-- "サイ・サイシー"の搭乗機体と必殺技の名前を出力。
SELECT MobileFighters.name AS "登場機体", MobileFighters.special_move AS "必殺技"
  FROM Fighters
  JOIN MobileFighters
    ON Fighters.mf_id = MobileFighters.id
 WHERE Fighters.name = 'サイ・サイシー';
```

**出力結果**

| 登場機体                 | 必殺技                |
|:---------------|:-------------|
| ドラゴンガンダム         | 真・流星胡蝶剣        |

簡単ではありますが、２つのテーブルを作成して連携したデータの抽出をすることが出来ました。



## 実際に困った点 (主にMySQLworkbenchで操作をしている時)
- CREATE構文を使って、BIGINTでデータ型を登録しようとするとMySQLworkbenchでは推奨されなくて警告を受けました。テーブルを作成することはできますが、シンプルにINTにして作成するとオールグリーン（問題なし）でした！

- Booleanをデータ型として登録したい場合はTINYINT(0)で設定することが慣例のようです。０,１でfalseとtrueを区別します。デフォルトを0(false)にすることが多いです

- 外部キーを含むテーブルを作成する場合は参照元のテーブルが先に作成されていないとエラーとなります。今回の事例だと、先にMobileFightersを作成した後にFightersを作るという順番になります。

- 上記のテーブル作成の順番と同じ考え方でデータのINSERTを行う際も、先にMobileFightersを作成して、その後にFightersを作ると良いでしょう。



## まとめ
いかがでしたでしょうか。
　今までSQL学習するときはデータベースが仕上がっていたのでSELECT構文を考える事がメインでした。
自分でデータベースから設計しようとすると、CREATEやINSERT構文を作る時に記法や注意点が分からず大変でした、、、^^;
でもだんだん慣れてくると、ER図を見てどんなサービスなのか推測できたりして楽しいですね〜! 苦手意識をなるべく減らすため、面白いデータベースを作っていきます！！
<br> ここまで読んでいただき、ありがとうございました。
また次回もよろしくお願いします！！

*~Qiitaの歴史がまた1ページ~*

**参考文献**
・『おうちで学べるデータベースのきほん』　（ミック、木村 明治　著）