新人に向けて, ゼロから SQL をレクチャーした際に話した内容を整理した。
前提
以下のいずれかの手順で, MySQL, もしくは, MariaDB がインストール済みであることとする。
想像してみよう!
銀行の ATM に立った際, 色んなボタンが表示される。
お金を引き出したい場合は, 以下の手順になると思う。
- ATM の画面が表示される。
- 引き出し ボタンを押下する。
- 「キャッシュカードを入れてください。」と表示される。
- キャッシュカードを入れる。
- 「暗証番号を入力してください。」と表示される。
- 暗証番号を入力する。
- 「通帳があるなら入れてください。無ければそのまま引き出したい金額を入力してください。」と表示される。
- そのまま金額を入力する。
- 引き出す金額が表示される。
- 確認 ボタンを押下する。
- 出金処理が開始され, (入れていた場合は通帳と) キャッシュカードが出てくる。
- キャッシュカードを受け取る。
- 受け取り口が開き, お金が出てくる。
- お金を受け取る。
- 受け取り口が閉じ, 最初の画面に戻る。
で, 多分 SQL が実行されるタイミングは出金処理のタイミング。
きっと SELECT や UPDATE が走りそう。
もしかしたら暗証画面を間違えた時点で弾く銀行もあるかも。
とりあえず今回は,
- 銀行を作る: CREATE DATABASE
- 銀行の頭取を作る: CREATE USER, GRANT
- 口座一覧を作る: CREATE TABLE
- 口座を作る: INSERT INTO
- 残高照会する: SELECT
- 入金, 出金する: UPDATE
- もうちょい賢く入出金する: START TRANSACTION, SELECT, UPDATE, COMMIT
- 口座を解約する: 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 です。
あとは, 本を読むなり他のサイトを参考にして勉強してください!
どっとはらい。