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

#SQLの基本のキ

+ SELECT 項目名1,項目名2,… FROM テーブル名 WHERE 条件;
+ INSERT INTO テーブル名 VALUES(値1,値2…);
+ UPDATE テーブル名 SET 項目名 = 値 WHERE 条件;
+ DELETE FROM テーブル名 WHERE 条件;

**※文の終わりに「;」を付けるのをお忘れなく！**

----

環境
　Windows 10
　MySQL : version(5.7.28)
使用アプリ
　コマンドプロンプト(Windowsマーク押して「cmd」って打ったら出てくるやつ)

----

仮想の全国共通テスト結果をまとめたデータを作成したので、それを元にSQLを実行していきたいと思います。

    mysql> SELECT * FROM test_master;
    +----+-----------+-------+-----------+
    | id | name      | score | area      |
    +----+-----------+-------+-----------+
    |  1 | Sato      |    90 | Tokyo     |
    |  2 | Suzuki    |    65 | Fukuoka   |
    |  3 | Takahashi |  NULL | Kanagawa  |
    |  4 | Tanaka    |    50 | Osaka     |
    |  5 | Ito       |    88 | Aomori    |
    |  6 | Watanabe  |    79 | Okinawa   |
    |  7 | Yamamoto  |    44 | Iwate     |
    |  8 | Nakamura  |  NULL | Chiba     |
    |  9 | Kobayahsi |    31 | Tokyo     |
    | 10 | Kato      |    92 | Aichi     |
    | 11 | Yoshida   |    67 | Tokyo     |
    | 12 | Yamada    |    77 | Kumamoto  |
    | 13 | Sasaki    |    29 | Hiroshima |
    | 14 | Yamagushi |    15 | Osaka     |
    | 15 | Inoue     |    70 | Kanagawa  |
    | 16 | Kimura    |    55 | Kanagawa  |
    | 17 | Hayashi   |    34 | Saitama   |
    | 18 | Saito     |    71 | Tokyo     |
    +----+-----------+-------+-----------+
    18 rows in set (0.01 sec)

※集計関数で必要になるのでここではNULLを入れていますが、#02以降はNULLがない状態でSQLを実行します


#01 集計関数
  -- データの個数を調べたいとき

    mysql> SELECT COUNT(score) FROM test_master;
    +--------------+
    | COUNT(score) |
    +--------------+
    |           16 |
    +--------------+
    1 row in set (0.00 sec)

  -- 主キーで数えると全体の行数を数えられる

    mysql> SELECT COUNT(id) FROM test_master;
    +-----------+
    | COUNT(id) |
    +-----------+
    |        18 |
    +-----------+
    1 row in set (0.00 sec)

  -- 全体の行数を数えたいとき

    mysql> SELECT COUNT(*) FROM test_master;
    +----------+
    | COUNT(*) |
    +----------+
    |       18 |
    +----------+
    1 row in set (0.00 sec)
  
  -- 合計(scoreを全部足した値)

    mysql> SELECT SUM(score)FROM test_master;
    +------------+
    | SUM(score) |
    +------------+
    |        957 |
    +------------+
    1 row in set (0.01 sec)

  -- 平均(scoreの平均値)

    mysql> SELECT AVG(score)FROM test_master;
    +------------+
    | AVG(score) |
    +------------+
    |    59.8125 |
    +------------+
    1 row in set (0.01 sec)

  -- 最大(scoreの中で一番高い値)

    mysql> SELECT MAX(score)FROM test_master;
    +------------+
    | MAX(score) |
    +------------+
    |         92 |
    +------------+
 
 -- 最小(scoreの中で一番低い値)

    mysql> SELECT MIN(score)FROM test_master;
    +------------+
    | MIN(score) |
    +------------+
    |         15 |
    +------------+
    1 row in set (0.00 sec)

----
これ以降はこちらのテーブルを使います

    +----+-----------+-------+-----------+
    | id | name      | score | area      |
    +----+-----------+-------+-----------+
    |  1 | Sato      |    90 | Tokyo     |
    |  2 | Suzuki    |    65 | Fukuoka   |
    |  3 | Takahashi |    45 | Kanagawa  |
    |  4 | Tanaka    |    50 | Osaka     |
    |  5 | Ito       |    88 | Aomori    |
    |  6 | Watanabe  |    79 | Okinawa   |
    |  7 | Yamamoto  |    44 | Iwate     |
    |  8 | Nakamura  |    64 | Chiba     |
    |  9 | Kobayahsi |    31 | Tokyo     |
    | 10 | Kato      |    92 | Aichi     |
    | 11 | Yoshida   |    67 | Tokyo     |
    | 12 | Yamada    |    77 | Kumamoto  |
    | 13 | Sasaki    |    29 | Hiroshima |
    | 14 | Yamagushi |    15 | Osaka     |
    | 15 | Inoue     |    70 | Kanagawa  |
    | 16 | Kimura    |    55 | Kanagawa  |
    | 17 | Hayashi   |    34 | Saitama   |
    | 18 | Saito     |    71 | Tokyo     |
    +----+-----------+-------+-----------+
    18 rows in set (0.00 sec)

----

#02 GROUP BYでグループ化
  -- 重複をしたareaを省いたデータ抽出

    mysql> SELECT DISTINCT area FROM test_master;
    +-----------+
    | area      |
    +-----------+
    | Tokyo     |
    | Fukuoka   |
    | Kanagawa  |
    | Osaka     |
    | Aomori    |
    | Okinawa   |
    | Iwate     |
    | Chiba     |
    | Aichi     |
    | Kumamoto  |
    | Hiroshima |
    | Saitama   |
    +-----------+
12 rows in set (0.01 sec)

  -- エリアごとのscoreの合計

    mysql> SELECT area, SUM(score) FROM test_master GROUP BY area;
    +-----------+------------+
    | area      | SUM(score) |
    +-----------+------------+
    | Aichi     |         92 |
    | Aomori    |         88 |
    | Chiba     |         64 |
    | Fukuoka   |         65 |
    | Hiroshima |         29 |
    | Iwate     |         44 |
    | Kanagawa  |        170 |
    | Kumamoto  |         77 |
    | Okinawa   |         79 |
    | Osaka     |         65 |
    | Saitama   |         34 |
    | Tokyo     |        259 |
    +-----------+------------+
    12 rows in set (0.00 sec)

#03 HAVINGで抽出条件を指定
  -- HAVINGを使った場合、areaごとのscoreの合計が100以上であるものが抽出される（GROUP BY句との位置に気をつける）

    mysql> SELECT area, SUM(score) FROM test_master GROUP BY area HAVING SUM(score) > 100;
    +----------+------------+
    | area     | SUM(score) |
    +----------+------------+
    | Kanagawa |        170 |
    | Tokyo    |        259 |
    +----------+------------+
    2 rows in set (0.01 sec)

-- WHERE句を使った場合、scoreが70より大きいものがグループ化されて集計されるという形になる（GROUP BY句との位置に気をつける）

    mysql> SELECT area, SUM(score) FROM test_master WHERE score > 70 GROUP BY area;
    +----------+------------+
    | area     | SUM(score) |
    +----------+------------+
    | Aichi    |         92 |
    | Aomori   |         88 |
    | Kumamoto |         77 |
    | Okinawa  |         79 |
    | Tokyo    |        161 |
    +----------+------------+
    5 rows in set (0.00 sec)


#04 IF()
  -- teamカラムを追加して、scoreが80以上であればAというチームに、それ以外はBというチームに振り分ける

    mysql> SELECT *, IF(score > 80, 'A', 'B') AS team FROM test_master;
    +----+-----------+-------+-----------+------+
    | id | name      | score | area      | team |
    +----+-----------+-------+-----------+------+
    |  1 | Sato      |    90 | Tokyo     | A    |
    |  2 | Suzuki    |    65 | Fukuoka   | B    |
    |  3 | Takahashi |    45 | Kanagawa  | B    |
    |  4 | Tanaka    |    50 | Osaka     | B    |
    |  5 | Ito       |    88 | Aomori    | A    |
    |  6 | Watanabe  |    79 | Okinawa   | B    |
    |  7 | Yamamoto  |    44 | Iwate     | B    |
    |  8 | Nakamura  |    64 | Chiba     | B    |
    |  9 | Kobayahsi |    31 | Tokyo     | B    |
    | 10 | Kato      |    92 | Aichi     | A    |
    | 11 | Yoshida   |    67 | Tokyo     | B    |
    | 12 | Yamada    |    77 | Kumamoto  | B    |
    | 13 | Sasaki    |    29 | Hiroshima | B    |
    | 14 | Yamagushi |    15 | Osaka     | B    |
    | 15 | Inoue     |    70 | Kanagawa  | B    |
    | 16 | Kimura    |    55 | Kanagawa  | B    |
    | 17 | Hayashi   |    34 | Saitama   | B    |
    | 18 | Saito     |    71 | Tokyo     | B    |
    +----+-----------+-------+-----------+------+
    18 rows in set (0.00 sec)

#05  CASE

  -- いいねが80以上であればAチーム、60以上であればBチーム、それ以外はCチームに振り分ける

    mysql> SELECT *, CASE WHEN score > 80 THEN 'A' WHEN score > 60 THEN 'B' ELSE 'C' END AS team FROM test_master;
    +----+-----------+-------+-----------+------+
    | id | name      | score | area      | team |
    +----+-----------+-------+-----------+------+
    |  1 | Sato      |    90 | Tokyo     | A    |
    |  2 | Suzuki    |    65 | Fukuoka   | B    |
    |  3 | Takahashi |    45 | Kanagawa  | C    |
    |  4 | Tanaka    |    50 | Osaka     | C    |
    |  5 | Ito       |    88 | Aomori    | A    |
    |  6 | Watanabe  |    79 | Okinawa   | B    |
    |  7 | Yamamoto  |    44 | Iwate     | C    |
    |  8 | Nakamura  |    64 | Chiba     | B    |
    |  9 | Kobayahsi |    31 | Tokyo     | C    |
    | 10 | Kato      |    92 | Aichi     | A    |
    | 11 | Yoshida   |    67 | Tokyo     | B    |
    | 12 | Yamada    |    77 | Kumamoto  | B    |
    | 13 | Sasaki    |    29 | Hiroshima | C    |
    | 14 | Yamagushi |    15 | Osaka     | C    |
    | 15 | Inoue     |    70 | Kanagawa  | B    |
    | 16 | Kimura    |    55 | Kanagawa  | C    |
    | 17 | Hayashi   |    34 | Saitama   | C    |
    | 18 | Saito     |    71 | Tokyo     | B    |
    +----+-----------+-------+-----------+------+
    18 rows in set (0.01 sec)

#06 抽出結果を別のテーブルとして作成する

  -- Tokyoエリアの結果だけをまとめたtest_tokyoテーブルを作成する

`CREATE TABLE test_tokyo AS SELECT * FROM test_master WHERE area = 'Tokyo';`

    mysql> SELECT * FROM test_tokyo;
    +----+-----------+-------+-------+
    | id | name      | score | area  |
    +----+-----------+-------+-------+
    |  1 | Sato      |    90 | Tokyo |
    |  9 | Kobayahsi |    31 | Tokyo |
    | 11 | Yoshida   |    67 | Tokyo |
    | 18 | Saito     |    71 | Tokyo |
    +----+-----------+-------+-------+
    4 rows in set (0.00 sec)

#07 VIEW

VIEW ... 更新や変更があったテーブルのデータを取得することができ、その抽出条件だけを保持した仮想的なテーブルを作成することが可能。

  -- id 1のSatoさんのscoreに40点という変更があったので、更新するがtest_tokyoのテーブルにはその変更が反映されないことを確認する

    mysql> SELECT * FROM test_master;
    +----+-----------+-------+-----------+
    | id | name      | score | area      |
    +----+-----------+-------+-----------+
    |  1 | Sato      |    40 | Tokyo     |
    |  2 | Suzuki    |    65 | Fukuoka   |
    |  3 | Takahashi |    45 | Kanagawa  |
    |  4 | Tanaka    |    50 | Osaka     |
    |  5 | Ito       |    88 | Aomori    |
    |  6 | Watanabe  |    79 | Okinawa   |
    |  7 | Yamamoto  |    44 | Iwate     |
    |  8 | Nakamura  |    64 | Chiba     |
    |  9 | Kobayahsi |    31 | Tokyo     |
    | 10 | Kato      |    92 | Aichi     |
    | 11 | Yoshida   |    67 | Tokyo     |
    | 12 | Yamada    |    77 | Kumamoto  |
    | 13 | Sasaki    |    29 | Hiroshima |
    | 14 | Yamagushi |    15 | Osaka     |
    | 15 | Inoue     |    70 | Kanagawa  |
    | 16 | Kimura    |    55 | Kanagawa  |
    | 17 | Hayashi   |    34 | Saitama   |
    | 18 | Saito     |    71 | Tokyo     |
    +----+-----------+-------+-----------+
    18 rows in set (0.00 sec)

    mysql> SELECT * FROM test_tokyo;
    +----+-----------+-------+-------+
    | id | name      | score | area  |
    +----+-----------+-------+-------+
    |  1 | Sato      |    90 | Tokyo |
    |  9 | Kobayahsi |    31 | Tokyo |
    | 11 | Yoshida   |    67 | Tokyo |
    | 18 | Saito     |    71 | Tokyo |
    +----+-----------+-------+-------+
    4 rows in set (0.00 sec)

  -- VIEWを使って、更新されたテーブルのデータを取得し、その抽出条件だけを保持した仮想的なテーブルを作成して、変更後のTokyoエリアのscore合計を見てみる

    mysql> CREATE VIEW test_tokyo_view AS SELECT * FROM test_master WHERE area = 'tokyo';

    mysql> SELECT * FROM test_tokyo_view;
    +----+-----------+-------+-------+
    | id | name      | score | area  |
    +----+-----------+-------+-------+
    |  1 | Sato      |    40 | Tokyo |
    |  9 | Kobayahsi |    31 | Tokyo |
    | 11 | Yoshida   |    67 | Tokyo |
    | 18 | Saito     |    71 | Tokyo |
    +----+-----------+-------+-------+
    4 rows in set (0.01 sec)

#08 UNION

  -- scoreを高い順に並べて上位2名と下位1名のレコードをつなげて抽出する

    mysql> (SELECT * FROM test_master ORDER BY score DESC LIMIT 2)
    -> UNION ALL
    -> (SELECT * FROM test_master ORDER BY score LIMIT 1);
    +----+-----------+-------+--------+
    | id | name      | score | area   |
    +----+-----------+-------+--------+
    | 10 | Kato      |    92 | Aichi  |
    |  5 | Ito       |    88 | Aomori |
    | 14 | Yamagushi |    15 | Osaka  |
    +----+-----------+-------+--------+
    3 rows in set (0.02 sec)

**注意点**
UNIONを使うときは2つのクエリのカラム数とデータ型が一致していないとエラーになる

#09 サブクエリ
サブクエリ ... クエリの中でクエリを使うことをサブクエリと呼ぶ

  -- scoreの平均点を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 | Aomori    | 56.4444 |
    |  6 | Watanabe  |    79 | Okinawa   | 56.4444 |
    |  7 | Yamamoto  |    44 | Iwate     | 56.4444 |
    |  8 | Nakamura  |    64 | Chiba     | 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 |
    +----+-----------+-------+-----------+---------+
    18 rows in set (0.01 sec)

----

#おわりに

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