MySQL で「なんちゃって銀行」チュートリアル

新人に向けて, ゼロから SQL をレクチャーした際に話した内容を整理した。


## 前提

以下のいずれかの手順で, MySQL, もしくは, MariaDB がインストール済みであることとする。

- [Windows 版 MySQL インストール手順](https://qiita.com/ryo-sato/items/bd026f5e627a746f1734)
- [CentOS 7: MariaDB インストール手順](https://qiita.com/ryo-sato/items/a137b3d2ed84a7831b83)

## 想像してみよう!

銀行の ATM に立った際, 色んなボタンが表示される。
お金を引き出したい場合は, 以下の手順になると思う。

1. ATM の画面が表示される。
   - **引き出し** ボタンを押下する。
1. 「キャッシュカードを入れてください。」と表示される。
   - キャッシュカードを入れる。
1. 「暗証番号を入力してください。」と表示される。
   - 暗証番号を入力する。
1. 「通帳があるなら入れてください。無ければそのまま引き出したい金額を入力してください。」と表示される。
   - そのまま金額を入力する。
1. 引き出す金額が表示される。
   - **確認** ボタンを押下する。
1. 出金処理が開始され, (入れていた場合は通帳と) キャッシュカードが出てくる。
   - キャッシュカードを受け取る。
1. 受け取り口が開き, お金が出てくる。
   - お金を受け取る。
1. 受け取り口が閉じ, 最初の画面に戻る。

で, 多分 SQL が実行されるタイミングは出金処理のタイミング。
きっと SELECT や UPDATE が走りそう。
もしかしたら暗証画面を間違えた時点で弾く銀行もあるかも。

とりあえず今回は,

1. 銀行を作る: **CREATE DATABASE**
1. 銀行の頭取を作る: **CREATE USER, GRANT**
1. 口座一覧を作る: **CREATE TABLE**
1. 口座を作る: **INSERT INTO**
1. 残高照会する: **SELECT**
1. 入金, 出金する: **UPDATE**
1. もうちょい賢く入出金する: **START TRANSACTION, SELECT, UPDATE, COMMIT**
1. 口座を解約する: **DELETE**

というところまでやってみる。

*そんな設計ある訳ねーだろ? 認証が無い? 今日は良いんだ, そんなことは!*

## やってみよう!

### 銀行を作る: **CREATE DATABASE**

とりあえず今回は, サトー銀行: sato_bank という名前にする。
MySQL に管理者としてログインし, `CREATE DATABASE sato_bank;` と入力する。

```
Query OK, 1 row affected (0.01 sec)
```

うむ。出来たっぽい? `SHOW DATABASES;` で確認しよう。

```
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sato_bank          |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)
```

おぉ, 出来てる! (拍手!)
というわけで, 銀行の作成は OK だ。


### 銀行の頭取を作る: **CREATE USER, GRANT**

実は MySQL の管理者: root という神は, データベースを作り放題壊し放題出来てしまうので,
今回の銀行のみ管理できるユーザーを作成する。例えるなら銀行の頭取に相当するかもしれない。

頭取を作成するには, CREATE USER でユーザーを作成し, GRANT で銀行を操る権限を付与する流れになる。
1 つずつ見ていこう。

まず, `CREATE USER 'ryo'@'localhost' IDENTIFIED BY 'qd8CH6fesiPVd56enawmxvD0e6ITyT0z';` でユーザーを作成する。
頭取さんの名前は ryo さん, パスワードは管理ツールで自動生成したものにしたけど, ここは各自で変更すること!

```
Query OK, 0 rows affected (0.01 sec)
```

うむ。良さそう。実行したら `SELECT host,user FROM mysql.user WHERE user = 'ryo';` で確認しよう。

```
+-----------+------+
| host      | user |
+-----------+------+
| localhost | ryo  |
+-----------+------+
1 row in set (0.00 sec)
```

おぉ? 良いんじゃね?
そしたら次は, `GRANT ALL ON sato_bank.* TO 'ryo'@'localhost';` でデータベースを操る権限を付与しよう。

```
Query OK, 0 rows affected (0.01 sec)
```

そしたら `SHOW GRANTS FOR 'ryo'@'localhost';` で権限を確認しよう。

```
+------------------------------------------------------------+
| Grants for ryo@localhost                                   |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `ryo`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `sato_bank`.* TO `ryo`@`localhost` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
```

うむ。大丈夫だね。
そこで一旦, `quit` でログアウトしてから, `mysql -u ryo -p -D sato_bank` で接続しなおそう。
ログインできたら `SHOW DATABASES;` を実行してみる。

```
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sato_bank          |
+--------------------+
2 rows in set (0.00 sec)
```

管理者で確認したときと比べて, 表示されるデータベース数が少なくなっていると思う。
これで, 他のデータベースを迂闊に変更して壊してしまう心配は無くなったわけだ。

ちなみに昔の MySQL では CREATE USER しなくても GRANT で自動的にユーザーを作ってくれたけど,
今は文法エラーで弾かれるようになってしまった。。。


### 口座一覧を作る: **CREATE TABLE**

というわけで, 早速口座一覧を作ろう。
凄く簡単に, 口座番号: id と残高: balance だけ作成する。口座開設時に名前や住所, 電話番号なんて不要だぜ!
`CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL);` で作れる。シーケンス? 今は良いんだ!

```
Query OK, 0 rows affected (0.06 sec)
```

うむ。良さそうだから `SHOW TABLES;` で確認しようか。

```
+---------------------+
| Tables_in_sato_bank |
+---------------------+
| accounts            |
+---------------------+
1 row in set (0.01 sec)
```

ついでに, `DESC accounts;` でも確認しておこう。

```
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id      | int(11) | NO   | PRI | NULL    |       |
| balance | int(11) | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```

大丈夫そうだね。これで口座開設も出来るようになった。


### 口座を作る: **INSERT INTO**

開設するに当たっては, 最初は 1000 円入れることが多いかも。
というわけで, id は 1 番にするとして, お金は 1000 円預ける形で口座を作ってみよう。
`INSERT INTO accounts VALUES (1, 1000);` を実行だ。

```
Query OK, 1 row affected (0.01 sec)
```

うむ。では `SELECT * FROM accounts;` で確認してみよう。

```
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
+----+---------+
1 row in set (0.00 sec)
```

おぉ, 良いんじゃね? ついでにもう一人, id は 2 で 5000 円預金させてみよう。
`INSERT INTO accounts VALUES (2, 5000);` を実行し, `SELECT * FROM accounts;` で確認だ。

```
Query OK, 1 row affected (0.01 sec)
```

```
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |    5000 |
+----+---------+
2 rows in set (0.00 sec)
```

いーんじゃないでしょうか!


### 残高照会する: **SELECT**

残高照会する場合, 普通は自分の口座しか見えないハズ。
なので id が 1 番の場合は, `SELECT * FROM accounts WHERE id = 1;` で絞れる。

```
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
+----+---------+
1 row in set (0.00 sec)
```

ただ, 実際の ATM で残高照会した場合, 自分の口座番号は表示されないと思う。
むしろ, 残高だけ表示されるんじゃ無いかな? というわけで, 残高だけ表示させてみよう。
アスタリスクでは無くて, `SELECT balance FROM accounts WHERE id = 1;` と指定してあげる。

```
+---------+
| balance |
+---------+
|    1000 |
+---------+
1 row in set (0.00 sec)
```

良さそうだね!


### 入出金する: **UPDATE**

お金を入れたら残高が変わる。そりゃそーなのだが, 今回は単純に残高を変えてみよう。
例えば, id が 1 番の人の残高を 100,000 円にしてみる。
`UPDATE accounts SET balance = 100000 WHERE id = 1;` で変更できる。

```
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

そしたら `SELECT balance FROM accounts WHERE id = 1;` で残高照会しよう。

```
+---------+
| balance |
+---------+
|  100000 |
+---------+
1 row in set (0.00 sec)
```

これで脳内大金持ち間違いなし!


### もうちょい賢く入出金する: **START TRANSACTION, SELECT, UPDATE, COMMIT**

いやちょっと待ってくれ。そんなこと出来たら ATM で改竄し放題じゃないか!
というわけで, 例えば 10000 円入れたら残高が 110000 円になることを考える。

これがちょっと一手間要る。
以下の 4 行なのだが, 2 行目で引っ張ってきた残高を A という変数に突っ込んで,
3 行目で加算していることがお分かりだろうか。

```
START TRANSACTION;
SELECT @A:=balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = @A + 10000 WHERE id = 1;
COMMIT;
```

モノは試しでやってみよう。

```
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @A:=balance |
+-------------+
|      100000 |
+-------------+
1 row in set, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
```

おぉ? ちょっと表示が見づらいけど, とりあえず `SELECT balance FROM accounts WHERE id = 1;` で残高照会しよう。

```
+---------+
| balance |
+---------+
|  110000 |
+---------+
1 row in set (0.00 sec)
```

おぉ, 入金した分だけ増えてますね! これで好きなように数字をいじられなくて済むわけだ。


### 口座を解約する: **DELETE**

と, 一通り遊んだら, 現実には滅多に無いけど解約しましょうか。
今ある口座は `SELECT * FROM accounts;` で確認できる。

```
+----+---------+
| id | balance |
+----+---------+
|  1 |  110000 |
|  2 |    5000 |
+----+---------+
2 rows in set (0.00 sec)
```

うん。2 番の口座を没収しましょう。
`DELETE FROM accounts WHERE id = 2;` で解約してみる。

```
Query OK, 1 row affected (0.01 sec)
```

ほぅ。とりあえず `SELECT * FROM accounts;` で確認しよう。

```
+----+---------+
| id | balance |
+----+---------+
|  1 |  110000 |
+----+---------+
1 row in set (0.00 sec)
```

消えてますね! さようなら 2 番!


## おわりに

こんないい加減なシステムは実際の銀行にあるわけがありません。
よい子はしっかり設計, 実装しましょう。

ただ, 今回のチュートリアルでは, 実際の使われ方を想像しながらテーブルの列を決めたり,
データを入出力していくんだ, ということが分かってもらえれば OK です。

あとは, 本を読むなり他のサイトを参考にして勉強してください!

どっとはらい。