Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@h0iga3shi13(Higashi Hiroyuki)

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

Last updated at Posted at 2020-10-09

前回の記事で投稿した いろんな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 |
+------+--------------------+----+-----------------+-----------------------+--------+

#おわりに

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?