MySQL応用編！いろんなSQL Vol.2

前回の記事で投稿した [いろんなSQL Vol.1](https://qiita.com/h0iga3shi13/items/5bf241532d12bd5b2fff) のつづきの記事となります。

----

環境
　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 |
    +------+--------------------+----+-----------------+-----------------------+--------+

#おわりに

+ [いろんなSQL応用編 Vol.1](https://qiita.com/h0iga3shi13/items/5bf241532d12bd5b2fff)
+ [いろんなSQL応用編 Vol.3](https://qiita.com/h0iga3shi13/items/b68901b443f489679f2d)
+ [いろんなSQL応用編 Vol.4](#)