More than 3 years have passed since last update.

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

Last updated at 2020-10-10Posted at 2020-10-02

#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)

#おわりに

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up