DBについての簡単な説明

ここでは、MySQLを用いて簡単にトランザクション処理やjoinの考え方について説明していきたいと思います。

# トランザクション処理とは？

簡単にいうとRPGでいうところの__「セーブポイント」__のようなものです。
トランザクションをONにしておけば__COMMIT__という処理を行わない限り、値が更新されないという素敵機能です。
もし間違った処理を行ってしまった場合、COMMITの前に__ROLLBACK__という処理を行えば値は元通りになります。

では実際に順を追ってトランザクションを体感してみましょう。

```sql:簡単にDBとTableを作りましょう
CREATE DATABASE demo;

use demo;

CREATE TABLE test (id int(11) PRIMARY KEY AUTO_INCREMENT,name VARCHER(255) NOT NULL);
```

demoDBとtestTableを作成し、値をインサートしましょう。

```sql:値をインサートしましょう
INSERT INTO test (name) VALUES ("a"),("b"),("c"),("d"),("e");
```

ここでいったん値を確認してみましょう。

```sql:値を確認しましょう
SELECT * FROM test;
```
この文を実行すると

| id | name |
|:-:|:-:|
|  1 | a  |
| 2  | b  |
|  3 |  c |
| 4  | d  |
| 5  |  e |

testTableにはこのようにインサートされているはずです。
続いてトランザクション処理に移りたいと思います。

```sql:トランザクションをスタートさせましょう
START TRANSACTION;
```

この機能を使うことでトランザクションが有効になります。
トランザクションをスタートした時点で、__オートコミット機能__がOFFになります。

__※オートコミット機能とは__
INSERT文やUPDATE文等を実行した際、自動的にCOMMITをして、変更を永続的なものにする機能のことです。

トランザクションをスタートしたことによって、自動で変更が永続的になるということがなくなったため、値がセーブポイントにセーブされたような状態になります。

それでは実際に値を変更して見ましょう。

```sql:値を変更しましょう
UPDATE test SET name="c" WHERE id=2;
```

そして、変更した値をSELECT文を用いて確認してみましょう。

| id | name |
|:-:|:-:|
|  1 | a  |
| 2  | __c__  |
|  3 |  c |
| 4  | d  |
| 5  |  e |

__id=2のnameがc__に置き換わっています。
この変更をROLLBACKを用いて打ち消してみましょう。

```sql:ロールバックしましょう
ROLLBACK;
```

ROLLBACKした後、値を確認するとトランザクションをスタートした時の状態に戻っているはずです。
セーブポイントに戻ってきたような感じですね。

続いてCOMMITをして見ましょう。

トランザクションをスタートし、先ほど行ったUPDATE文をもう一度実行してみましょう。

```sql:値を変更しましょう
START TRANSACTION;

UPDATE test SET name="c" WHERE id=2;
```

値が変更されていたか確認し、次はCOMMITを実行してみましょう。

```sql:コミットしましょう
COMMIT;
```

これで変更は永続的なものになりました。
COMMIT後にROLLBACKを行っても値は元に戻らないはずです。確認してみましょう。

これでトランザクションについて説明は終わりです。
本当にRPGでいうところの__「セーブポイント」__のようなものなので難しく考えず、どんどん活用していってください。

# JOINについて

続いて、__JOIN(内部結合、外部結合)__について説明したいと思います。
まずは、__内部結合、外部結合はどのようなものなの？__ということを説明したいと思います。

いきなりですが、
__1つのプロジェクトで1つの大きなテーブルを扱うということはほとんどありません。__
1つの大きなテーブルを使うということは、膨大な量のカラム達や膨大な量のレコード達を管理しなければならず、とても非効率的で、カラムの指定の間違いや、セキュリティ的にも問題が発生してしまいそうです。

なので、__テーブルは複数個に分かれて存在することが普通__です。

複数のテーブルの関係性を__リレーション__と呼びます。
リレーションに基づき情報を結合して管理したり、フロント側に受け渡したりすることで、効率的かつ安全にデータを扱っています。
MySQLやPostgreSQL等は__RDBMS(relational database management system)__というシステムで、__JOIN(内部結合、外部結合)__は__RDBMSらしい機能__なのです。

# 実際に使ってみる

実際に使ってみましょう。
まずはテーブルの作成を行います。

```sql:テーブル作成
CREATE TABLE employees(id int(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL);
CREATE TABLE products(id int(11) PRIMARY KEY AUTO_INCREMENT, price int(30) NOT NULL, name VARCHAR(255) NOT NULL, manager_id int(11) NOT NULL);
CREATE TABLE purchase_historys(product_id int(11) NOT NULL, customer_id int(11) NOT NULL);
CREATE TABLE customers(id int(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL);
```

上から
・従業員テーブル
・商品テーブル
・購入履歴テーブル
・顧客テーブル
です。

これらのテーブルをこれからシチュエーションに分けて結合してみたいと思います。

値をインサートしていきます。

```sql:値のインサート
INSERT INTO employees (name) VALUES ("田中"),("後藤"),("松本"),("橘"),("伊藤");
INSERT INTO products (price, name, manager_id) VALUES (300, "はさみ", 1),(600, "爪きり", 2),(400, "クッション", 3),(1500, "包丁", 4),(5900, "財布", 3),(82000, "スマホ", 2),(300, "大根", 4),(500000, "車", 5),(12000000, "土地", 5),(122000000, "絵画",6);
INSERT INTO purchase_historys (product_id, customer_id) VALUES (1, 5),(2, 3),(3, 1),(4, 4),(5, 3),(4, 1),(3, 2),(5, 1),(4, 2);
INSERT INTO customers (name) VALUES ("佐藤"),("宮本"),("手塚"),("桜井"),("富永");

```


__従業員テーブル__

| id | name |
|:-:|:-:|
| 1  | 田中  |
| 2  | 後藤  |
| 3  | 松本  |
| 4  | 橘  |
| 5  | 伊藤  |

__商品テーブル__

| id | price | name | manager_id |
|:-:|:-:|:-:|:-:|
| 1  | 300  | はさみ  | 1  |
| 2  | 600  | 爪きり  | 2  |
| 3  | 400  | クッション  | 3  |
| 4  | 1500  | 包丁  | 4  |
| 5  | 1200  | CD  | 5  |
| 6  | 5900  | 財布  | 3  |
| 7  | 82000  | スマホ  | 2  |
| 8  | 300  | 大根  | 4  |
| 9  | 500000  | 車  | 5  |
| 10  | 12000000  | 土地  | 5  |
| 11  | 122000000  | 絵画  | 6  |

__購入履歴テーブル__

| product_id | customer_id |
|:-:|:-:|
| 1  | 5  |
| 2  | 3  |
| 3  | 1  |
| 4  | 4  |
| 5  | 3  |
| 4  | 1  |
| 3  | 2  |
| 5  | 1  |
| 4  | 2  |

__顧客テーブル__

| id | name |
|:-:|:-:|
| 1  | 佐藤  |
| 2  | 宮本  |
| 3  | 手塚  |
| 4  | 桜井  |
| 5  | 富永  |


こんな感じに中身に値をインサートしました。
実際に値を結合してみましょう！

### CASE1:従業員が管理している製品の確認

従業員とその従業員が管理している商品の情報を知りたいときは
従業員テーブルと商品テーブルを結合しましょう。
商品テーブルにあるmanager_idは管理している従業員のidなので、__「従業員のidとmanager_idが一致したらレコードを結合する。」__というSQL文を書きましょう。

今回のケースでは、__INNER JOIN(内部結合)__を行います。
INNER JOINを使ったSELECT文の書き方はこうです。

```sql:内部結合
SELECT * FROM テーブル1 INNER JOIN テーブル2 ON テーブル1.カラム名=テーブル2.カラム名;
```

テーブル1にテーブル2を結合する際、テーブル1の特定のカラムにある値とテーブル2の特定のカラムにある値が一致するときに結合する。という書き方になります。

今回のケースに当てはめると

```sql:従業員テーブルと商品テーブルを内部結合
SELECT * FROM employees INNER JOIN products ON employees.id=products.manager_id;
```

このような書き方になります。
__employees.id__と__products.manager_id__は、__emoloyeesテーブルの中にあるid__というカラムと、__productsテーブルにあるmanager_id__というカラムを指します。

この文を実行すると

| id | name | id | price | name | manager_id |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | 田中 | 1 | 300 | はさみ | 1 |
| 2 | 後藤 | 2 | 600 | 爪きり | 2 |
| 2 | 後藤 | 7 | 82000 | スマホ | 2 |
| 3 | 松本 | 3 | 400 | クッション | 3 |
| 3 | 松本 | 6 | 5900 | 財布 | 3 |
| 4 | 橘 | 4 | 1500 | 包丁 | 4 |
| 4 | 橘 | 8 | 300 | 大根 | 4 |
| 5 | 伊藤 | 5 | 1200 | CD | 5 |
| 5 | 伊藤 | 9 | 500000 | 車 | 5 |
| 5 | 伊藤 | 10 | 12000000 | 土地 | 5 |

このようなテーブルが表示されます。
従業員テーブルに商品テーブルを結合する際、従業員テーブルの__id__と商品テーブルの__manager_id__が一致するときに結合する。という書き方になっているので、__manager_idが6__の絵画は表示されていません。
このように対象の値が一致したもののみ結合するのが__INNER JOIN(内部結合)__です。

しかしこのままでは
__・カラム名がかぶっていてどのテーブルの値かがわからない__
__・リレーションとして扱うカラムも表示されてしまう__

といった問題が発生してしまいます。
__なので、カラム名に好きな名前をつけてあげましょう。__
__そして、ほしいカラムだけ持ってきてしまいましょう。__

#### SELECT文のカスタマイズ

SELECT文には__好きなカラムを指定することができる機能__と、__カラムの名前に別名をつけてあげることができる機能__があります。

```sql:SELECT文のカスタマイズ
SELECT テーブル名.カラム名 FROM テーブル名;
```

こうすることで、好きなカラムを表示することができます。
試しにemployeesテーブルで試してみましょう。

```sql:SELECT文のカスタマイズ
SELECT employees.id FROM employees;
```

このSQL文を実行してみましょう。
するとidというカラムだけ持ってくることができたと思います。

次に、好きな名前をつける方法を説明したいと思います。

```sql:SELECT文のカスタマイズ
SELECT テーブル名.カラム名 AS 好きな名前 FROM テーブル名
```

先ほどのSQL文に__「AS 好きな名前」__をつけるだけです。

```sql:SELECT文のカスタマイズ
SELECT employees.id AS ids, employees.name AS names FROM employees; 
```

このSQL文を実行してみましょう。
するとidsというカラムとnamesというカラム名をつけて持ってくることができたと思います。

つけた名前を__エイリアスというので覚えておきましょう。__

さて、ここでCASE1のINNER JOINを見てみましょう。

```sql:CASE1の内部結合
SELECT * FROM employees INNER JOIN products ON employees.id=products.manager_id;
```

このSQL文を先ほどの文に直してみましょう。

```sql:修正後のCASE1の内部結合
SELECT employees.id AS employee_id,
employees.name AS employee_name,
products.price AS priduct_price,
products.name AS priduct_name
FROM employees INNER JOIN products ON employees.id=products.manager_id;
```

この文を実行すると

| employee_id | employee_name | priduct_price | priduct_name |
|:-:|:-:|:-:|:-:|
| 1 | 田中 | 300 | はさみ |
| 2 | 後藤 | 600 | 爪きり |
| 2 | 後藤 | 82000 | スマホ |
| 3 | 松本 | 400 | クッション |
| 3 | 松本 | 5900 | 財布 |
| 4 | 橘 | 1500 | 包丁 |
| 4 | 橘 | 300 | 大根 |
| 5 | 伊藤 | 1200 | CD |
| 5 | 伊藤 | 500000 | 車 |
| 5 | 伊藤 | 12000000 | 土地 |

こんな感じでかぶりのないほしい情報だけ持つ1つのテーブルを表示させることができます。
この後も活用していくので、覚えてみましょう。

### CASE2:現在担当者が存在していない製品の確認

次に、現在担当者が存在していない商品に担当者をつけたいので、担当者が存在していない商品を知りたい場合というケースです。
このケースでは、もう一度従業員テーブルと商品テーブルを結合しましょう。
__「従業員のidとmanager_idが一致したらレコードを結合する。しかし外部結合で。」__というSQL文を書きましょう。

今回のケースでは、__外部結合__を行います。
外部結合には4種類あります。
__LEFT JOIN　RIGHT JOIN　自己結合　完全外部結合__の4種類です。
4種類の外部結合の中でも特に扱う__LEFT JOIN　RIGHT JOIN__について解説したいと思います。
外部結合を使ったSELECT文の書き方はこうです。

```sql:外部結合(左側)
SELECT * FROM テーブル1 LEFT OUTER JOIN テーブル2 ON テーブル1.カラム名=テーブル2.カラム名;
```

```sql:外部結合(右側)
SELECT * FROM テーブル1 RIGHT OUTER JOIN テーブル2 ON テーブル1.カラム名=テーブル2.カラム名;
```

LEFT JOINはテーブル1にテーブル2を結合する際、テーブル1の特定のカラムにある値とテーブル2の特定のカラムにある値が一致するときに一致したレコードと結合する、ただし、左側の値は全件取得し、右側に結合されるレコードに値が存在していない場合は、NULLになる。というものです。

RIGHT JOINはテーブル1にテーブル2を結合する際、テーブル1の特定のカラムにある値とテーブル2の特定のカラムにある値が一致するときに一致したレコードと結合する、ただし、右側の値は全件取得し左側に結合されるレコードに値が存在していない場合は、NULLになる。というものです。

今回のケースは担当者がいない商品を知りたいので、商品テーブルを全件取得したいです。

今回のケースにあてはめると

```sql:従業員テーブルと商品テーブルを外部結合
SELECT employees.id AS employee_id,
employees.name AS employee_name,
products.price AS priduct_price,
products.name AS priduct_name
FROM employees RIGHT OUTER JOIN products ON employees.id=products.manager_id;
```

このような書き方になります。
この文を実行すると

| employee_id | employee_name | priduct_price | priduct_name |
|:-:|:-:|:-:|:-:|
| 1 | 田中 | 300 | はさみ |
| 2 | 後藤 | 600 | 爪きり |
| 2 | 後藤 | 82000 | スマホ |
| 3 | 松本 | 400 | クッション |
| 3 | 松本 | 5900 | 財布 |
| 4 | 橘 | 1500 | 包丁 |
| 4 | 橘 | 300 | 大根 |
| 5 | 伊藤 | 1200 | CD |
| 5 | 伊藤 | 500000 | 車 |
| 5 | 伊藤 | 12000000 | 土地 |
| NULL | NULL | 122000000 | 絵画 |

このようなテーブルが表示されます。
このように、右側に結合されるproductsテーブルは全件取得され、employeesテーブルはidがmanager_idに存在するときに、一致した値のレコードと結合します。
manager_idが存在しない商品は左側がNULLになります。

### case3:従業員の売り上げおよび客層を知りたい

今回のケースはリレーションが深く絡んできます。
INNER JOINは複数結合することができ、従業員テーブルと商品テーブルと購入履歴テーブルと顧客テーブルをうまく結合すると従業員の売り上げおよび客層を知ることができます。
まずは、コードを見て見ましょう。

```sql:複数の内部結合
SELECT employees.id AS employee_id,
employees.name AS employee_name,
products.price AS product_price,
products.name AS product_name,
customers.id AS customer_id,
customers.name AS customer_name
FROM employees
INNER JOIN
	products
ON 
	employees.id=products.manager_id
INNER JOIN
	purchase_historys
ON 
	products.id=purchase_historys.product_id
INNER JOIN
	customers
ON 
	purchase_historys.customer_id=customers.id;
```

従業員のテーブルのidと商品テーブルの管理者idが一致したら結合→商品テーブルのidと購入履歴の商品idが一致したら結合→購入履歴の顧客idが顧客テーブルのidと一致したら結合

という書き方になっています。
この文を実行すると

| employee_id | employee_name | product_price | product_name | customer_id | customer_name |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | 田中 | 300 | はさみ | 5 | 富永 |
| 2 | 後藤 | 600 | 爪きり | 3 | 手塚 |
| 3 | 松本 | 400 | クッション | 1 | 佐藤 |
| 3 | 松本 | 400 | クッション | 2 | 宮本 |
| 4 | 橘 | 1500 | 包丁 | 4 | 桜井 |
| 4 | 橘 | 1500 | 包丁 | 1 | 佐藤 |
| 4 | 橘 | 1500 | 包丁 | 2 | 宮本 |
| 5 | 伊藤 | 1200 | CD | 3 | 手塚 |
| 5 | 伊藤 | 1200 | CD | 1 | 佐藤 |

このようなテーブルが表示されます。
商品の情報、管理者の情報、購入した顧客の情報をひとつにして持ってくることができました。
この情報を分析することによって、管理している従業員がどの程度の売り上げでどの客層に売れているのかを知ることができます。
実際の顧客テーブルには、生年月日や住所、職業や性別、その他情報が登録されているので、めちゃくちゃ便利です。

# まとめ

今回はSQLを用いてトランザクション処理やJOINの考え方を解説しました。
トランザクションは__セーブポイントのようなもの。__
JOINは__テーブル同士の共通点で繋げていくもの。__
です。
両方とも絶対に使う知識なので、いろいろ調べて理解を深めてください。