前回の記事で投稿した いろんなSQL Vol.1 のつづきの記事となります。
環境
Windows 10
MySQL : version(5.7.28)
使用アプリ
コマンドプロンプト(Windowsマーク押して「cmd」って打ったら出てくるやつ)
現在のテーブルを表示(相関サブクエリ用にareaカラムのデータを多少変更しています)
mysql> SELECT *, (SELECT AVG(score) FROM test_master) AS avg FROM test_master;
+----+-----------+-------+-----------+---------+
| id | name | score | area | avg |
+----+-----------+-------+-----------+---------+
| 1 | Sato | 40 | Tokyo | 56.4444 |
| 2 | Suzuki | 65 | Fukuoka | 56.4444 |
| 3 | Takahashi | 45 | Kanagawa | 56.4444 |
| 4 | Tanaka | 50 | Osaka | 56.4444 |
| 5 | Ito | 88 | Tokyo | 56.4444 |
| 6 | Watanabe | 79 | Kanagawa | 56.4444 |
| 7 | Yamamoto | 44 | Osaka | 56.4444 |
| 8 | Nakamura | 64 | Fukuoka | 56.4444 |
| 9 | Kobayahsi | 31 | Tokyo | 56.4444 |
| 10 | Kato | 92 | Aichi | 56.4444 |
| 11 | Yoshida | 67 | Tokyo | 56.4444 |
| 12 | Yamada | 77 | Kumamoto | 56.4444 |
| 13 | Sasaki | 29 | Hiroshima | 56.4444 |
| 14 | Yamagushi | 15 | Osaka | 56.4444 |
| 15 | Inoue | 70 | Kanagawa | 56.4444 |
| 16 | Kimura | 55 | Kanagawa | 56.4444 |
| 17 | Hayashi | 34 | Saitama | 56.4444 |
| 18 | Saito | 71 | Tokyo | 56.4444 |
+----+-----------+-------+-----------+---------+
10 相関サブクエリ
area_avgカラムを作り、各areaごとの平均点を出す
mysql> SELECT
-> *,
-> (SELECT AVG(score) FROM test_master) AS avg,
-> (SELECT AVG(score) FROM test_master AS t2 WHERE t1.area = t2.area) AS
area_avg
-> FROM
-> test_master AS t1;
+----+-----------+-------+-----------+---------+----------+
| id | name | score | area | avg | area_avg |
+----+-----------+-------+-----------+---------+----------+
| 1 | Sato | 40 | Tokyo | 56.4444 | 59.4000 |
| 2 | Suzuki | 65 | Fukuoka | 56.4444 | 64.5000 |
| 3 | Takahashi | 45 | Kanagawa | 56.4444 | 62.2500 |
| 4 | Tanaka | 50 | Osaka | 56.4444 | 36.3333 |
| 5 | Ito | 88 | Tokyo | 56.4444 | 59.4000 |
| 6 | Watanabe | 79 | Kanagawa | 56.4444 | 62.2500 |
| 7 | Yamamoto | 44 | Osaka | 56.4444 | 36.3333 |
| 8 | Nakamura | 64 | Fukuoka | 56.4444 | 64.5000 |
| 9 | Kobayahsi | 31 | Tokyo | 56.4444 | 59.4000 |
| 10 | Kato | 92 | Aichi | 56.4444 | 92.0000 |
| 11 | Yoshida | 67 | Tokyo | 56.4444 | 59.4000 |
| 12 | Yamada | 77 | Kumamoto | 56.4444 | 77.0000 |
| 13 | Sasaki | 29 | Hiroshima | 56.4444 | 29.0000 |
| 14 | Yamagushi | 15 | Osaka | 56.4444 | 36.3333 |
| 15 | Inoue | 70 | Kanagawa | 56.4444 | 62.2500 |
| 16 | Kimura | 55 | Kanagawa | 56.4444 | 62.2500 |
| 17 | Hayashi | 34 | Saitama | 56.4444 | 34.0000 |
| 18 | Saito | 71 | Tokyo | 56.4444 | 59.4000 |
+----+-----------+-------+-----------+---------+----------+
11 抽出条件にサブクエリを使う
scoreが一番高い人を抽出
mysql> SELECT * FROM test_master WHERE score = (SELECT MAX(score) FROM test_master);
+----+------+-------+-------+
| id | name | score | area |
+----+------+-------+-------+
| 10 | Kato | 92 | Aichi |
+----+------+-------+-------+
12 抽出元にサブクエリを使う
area別に人数を分けて、
mysql> SELECT area, COUNT(*) AS byArea FROM test_master GROUP BY area;
+-----------+--------+
| area | byArea |
+-----------+--------+
| Aichi | 1 |
| Fukuoka | 2 |
| Hiroshima | 1 |
| Kanagawa | 4 |
| Kumamoto | 1 |
| Osaka | 3 |
| Saitama | 1 |
| Tokyo | 5 |
+-----------+--------+
エリア別にわけた人数の平均を求めるとき
mysql> SELECT AVG(byArea) FROM (SELECT area, COUNT(*) AS byArea FROM test_master GROUP BY area) AS mean;
+-------------+
| AVG(byArea) |
+-------------+
| 2.2500 |
+-------------+
13 TRANSACTION (COMMIT)
START TRANSACTION ～ COMMITで囲まれている間の処理が終わるまでは、これ以外の処理や操作を受け付けない
mysql> START TRANSACTION;
mysql> UPDATE test_master SET score = score - 2 WHERE id = 1;
mysql> UPDATE test_master SET score = score + 5 WHERE id = 2;
mysql> COMMIT;
14 TRANSACTION (ROLLBACK)
プログラムの処理中になにか障害が起こったときは、START TRANSACTION ～ ROLLBACKで囲んだ処理をなかったにできる（更新する前のデータに戻すことができる）
mysql> START TRANSACTION;
mysql> UPDATE test_master SET score = score - 2 WHERE id = 1;
mysql> ROLLBACK;
新しいテーブルを2つ用意しました
mysql> SELECT * FROM カテゴリー;
+----+--------------------+
| id | カテゴリー名 |
+----+--------------------+
| 1 | 和食 |
| 2 | 洋食 |
| 3 | 中華 |
+----+--------------------+
mysql> SELECT * FROM 商品;
+----+-----------------+-----------------------+--------+
| id | カテゴリー | 商品名 | 価格 |
+----+-----------------+-----------------------+--------+
| 1 | 1 | 肉じゃが定食 | 900 |
| 2 | 1 | すきやき | 2000 |
| 3 | 1 | 焼き魚定食 | 1200 |
| 4 | 1 | なすのおひたし | 600 |
| 5 | 2 | オムライス | 1200 |
| 6 | 2 | ビーフシチュー | 1800 |
| 7 | 3 | 酢豚定食 | 1200 |
| 8 | 3 | チャーハン | 500 |
| 9 | 3 | おつまみメンマ | 250 |
| 10 | 3 | ザーサイ | 120 |
+----+-----------------+-----------------------+--------+
14 内部結合
カテゴリーテーブルのカテゴリーidを商品テーブルのカテゴリーと紐付ける
mysql> SELECT
-> *
-> FROM
-> 商品 INNER JOIN カテゴリー ON 商品.カテゴリー = カテゴリー.id;
+----+-----------------+-----------------------+--------+----+--------------------+
| id | カテゴリー | 商品名 | 価格 | id | カテゴリー名 |
+----+-----------------+-----------------------+--------+----+--------------------+
| 1 | 1 | 肉じゃが定食 | 900 | 1 | 和食 |
| 2 | 1 | すきやき | 2000 | 1 | 和食 |
| 3 | 1 | 焼き魚定食 | 1200 | 1 | 和食 |
| 4 | 1 | なすのおひたし | 600 | 1 | 和食 |
| 5 | 2 | オムライス | 1200 | 2 | 洋食 |
| 6 | 2 | ビーフシチュー | 1800 | 2 | 洋食 |
| 7 | 3 | 酢豚定食 | 1200 | 3 | 中華 |
| 8 | 3 | チャーハン | 500 | 3 | 中華 |
| 9 | 3 | おつまみメンマ | 250 | 3 | 中華 |
| 10 | 3 | ザーサイ | 120 | 3 | 中華 |
+----+-----------------+-----------------------+--------+----+--------------------+
15 外部結合
左外部結合
カテゴリーテーブルに入っているデータをすべて抽出したあとに、商品テーブルと結合
mysql> SELECT
-> *
-> FROM
-> カテゴリー LEFT OUTER JOIN 商品 ON カテゴリー.id = 商品.id;
+----+--------------------+------+-----------------+--------------------+--------+
| id | カテゴリー名 | id | カテゴリー | 商品名 | 価格 |
+----+--------------------+------+-----------------+--------------------+--------+
| 1 | 和食 | 1 | 1 | 肉じゃが定食 | 900 |
| 2 | 洋食 | 2 | 1 | すきやき | 2000 |
| 3 | 中華 | 3 | 1 | 焼き魚定食 | 1200 |
+----+--------------------+------+-----------------+--------------------+--------+
右外部結合
商品テーブルに入っているデータをすべて抽出したあとに、カテゴリーテーブルと結合
mysql> SELECT
-> *
-> FROM
-> カテゴリー right OUTER JOIN 商品 ON カテゴリー.id = 商品.id;
+------+--------------------+----+-----------------+-----------------------+--------+
| id | カテゴリー名 | id | カテゴリー | 商品名 | 価格 |
+------+--------------------+----+-----------------+-----------------------+--------+
| 1 | 和食 | 1 | 1 | 肉じゃが定食 | 900 |
| 2 | 洋食 | 2 | 1 | すきやき | 2000 |
| 3 | 中華 | 3 | 1 | 焼き魚定食 | 1200 |
| NULL | NULL | 4 | 1 | なすのおひたし | 600 |
| NULL | NULL | 5 | 2 | オムライス | 1200 |
| NULL | NULL | 6 | 2 | ビーフシチュー | 1800 |
| NULL | NULL | 7 | 3 | 酢豚定食 | 1200 |
| NULL | NULL | 8 | 3 | チャーハン | 500 |
| NULL | NULL | 9 | 3 | おつまみメンマ | 250 |
| NULL | NULL | 10 | 3 | ザーサイ | 120 |
+------+--------------------+----+-----------------+-----------------------+--------+