ここでは、MySQLを用いて簡単にトランザクション処理やjoinの考え方について説明していきたいと思います。
トランザクション処理とは？
簡単にいうとRPGでいうところの「セーブポイント」のようなものです。
トランザクションをONにしておけばCOMMITという処理を行わない限り、値が更新されないという素敵機能です。
もし間違った処理を行ってしまった場合、COMMITの前にROLLBACKという処理を行えば値は元通りになります。
では実際に順を追ってトランザクションを体感してみましょう。
CREATE DATABASE demo;
use demo;
CREATE TABLE test (id int(11) PRIMARY KEY AUTO_INCREMENT,name VARCHER(255) NOT NULL);
demoDBとtestTableを作成し、値をインサートしましょう。
INSERT INTO test (name) VALUES ("a"),("b"),("c"),("d"),("e");
ここでいったん値を確認してみましょう。
SELECT * FROM test;
この文を実行すると
| id | name |
|---|---|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
testTableにはこのようにインサートされているはずです。
続いてトランザクション処理に移りたいと思います。
START TRANSACTION;
この機能を使うことでトランザクションが有効になります。
トランザクションをスタートした時点で、オートコミット機能がOFFになります。
※オートコミット機能とは
INSERT文やUPDATE文等を実行した際、自動的にCOMMITをして、変更を永続的なものにする機能のことです。
トランザクションをスタートしたことによって、自動で変更が永続的になるということがなくなったため、値がセーブポイントにセーブされたような状態になります。
それでは実際に値を変更して見ましょう。
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を用いて打ち消してみましょう。
ROLLBACK;
ROLLBACKした後、値を確認するとトランザクションをスタートした時の状態に戻っているはずです。
セーブポイントに戻ってきたような感じですね。
続いてCOMMITをして見ましょう。
トランザクションをスタートし、先ほど行ったUPDATE文をもう一度実行してみましょう。
START TRANSACTION;
UPDATE test SET name="c" WHERE id=2;
値が変更されていたか確認し、次はCOMMITを実行してみましょう。
COMMIT;
これで変更は永続的なものになりました。
COMMIT後にROLLBACKを行っても値は元に戻らないはずです。確認してみましょう。
これでトランザクションについて説明は終わりです。
本当にRPGでいうところの「セーブポイント」のようなものなので難しく考えず、どんどん活用していってください。
JOINについて
続いて、JOIN(内部結合、外部結合)について説明したいと思います。
まずは、内部結合、外部結合はどのようなものなの？ということを説明したいと思います。
いきなりですが、
1つのプロジェクトで1つの大きなテーブルを扱うということはほとんどありません。
1つの大きなテーブルを使うということは、膨大な量のカラム達や膨大な量のレコード達を管理しなければならず、とても非効率的で、カラムの指定の間違いや、セキュリティ的にも問題が発生してしまいそうです。
なので、テーブルは複数個に分かれて存在することが普通です。
複数のテーブルの関係性をリレーションと呼びます。
リレーションに基づき情報を結合して管理したり、フロント側に受け渡したりすることで、効率的かつ安全にデータを扱っています。
MySQLやPostgreSQL等はRDBMS(relational database management system)というシステムで、JOIN(内部結合、外部結合)はRDBMSらしい機能なのです。
実際に使ってみる
実際に使ってみましょう。
まずはテーブルの作成を行います。
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);
上から
・従業員テーブル
・商品テーブル
・購入履歴テーブル
・顧客テーブル
です。
これらのテーブルをこれからシチュエーションに分けて結合してみたいと思います。
値をインサートしていきます。
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文の書き方はこうです。
SELECT * FROM テーブル1 INNER JOIN テーブル2 ON テーブル1.カラム名=テーブル2.カラム名;
テーブル1にテーブル2を結合する際、テーブル1の特定のカラムにある値とテーブル2の特定のカラムにある値が一致するときに結合する。という書き方になります。
今回のケースに当てはめると
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文には好きなカラムを指定することができる機能と、カラムの名前に別名をつけてあげることができる機能があります。
SELECT テーブル名.カラム名 FROM テーブル名;
こうすることで、好きなカラムを表示することができます。
試しにemployeesテーブルで試してみましょう。
SELECT employees.id FROM employees;
このSQL文を実行してみましょう。
するとidというカラムだけ持ってくることができたと思います。
次に、好きな名前をつける方法を説明したいと思います。
SELECT テーブル名.カラム名 AS 好きな名前 FROM テーブル名
先ほどのSQL文に「AS 好きな名前」をつけるだけです。
SELECT employees.id AS ids, employees.name AS names FROM employees;
このSQL文を実行してみましょう。
するとidsというカラムとnamesというカラム名をつけて持ってくることができたと思います。
つけた名前をエイリアスというので覚えておきましょう。
さて、ここでCASE1のINNER JOINを見てみましょう。
SELECT * FROM employees INNER JOIN products ON employees.id=products.manager_id;
このSQL文を先ほどの文に直してみましょう。
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文の書き方はこうです。
SELECT * FROM テーブル1 LEFT OUTER JOIN テーブル2 ON テーブル1.カラム名=テーブル2.カラム名;
SELECT * FROM テーブル1 RIGHT OUTER JOIN テーブル2 ON テーブル1.カラム名=テーブル2.カラム名;
LEFT JOINはテーブル1にテーブル2を結合する際、テーブル1の特定のカラムにある値とテーブル2の特定のカラムにある値が一致するときに一致したレコードと結合する、ただし、左側の値は全件取得し、右側に結合されるレコードに値が存在していない場合は、NULLになる。というものです。
RIGHT JOINはテーブル1にテーブル2を結合する際、テーブル1の特定のカラムにある値とテーブル2の特定のカラムにある値が一致するときに一致したレコードと結合する、ただし、右側の値は全件取得し左側に結合されるレコードに値が存在していない場合は、NULLになる。というものです。
今回のケースは担当者がいない商品を知りたいので、商品テーブルを全件取得したいです。
今回のケースにあてはめると
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は複数結合することができ、従業員テーブルと商品テーブルと購入履歴テーブルと顧客テーブルをうまく結合すると従業員の売り上げおよび客層を知ることができます。
まずは、コードを見て見ましょう。
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はテーブル同士の共通点で繋げていくもの。
です。
両方とも絶対に使う知識なので、いろいろ調べて理解を深めてください。