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

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

前提

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

• Windows 版 MySQL インストール手順
• CentOS 7: MariaDB インストール手順

想像してみよう!

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

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

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

とりあえず今回は,

1. 銀行を作る: CREATE DATABASE
2. 銀行の頭取を作る: CREATE USER, GRANT
3. 口座一覧を作る: CREATE TABLE
4. 口座を作る: INSERT INTO
5. 残高照会する: SELECT
6. 入金, 出金する: UPDATE
7. もうちょい賢く入出金する: START TRANSACTION, SELECT, UPDATE, COMMIT
8. 口座を解約する: 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 です。

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

どっとはらい。