LoginSignup
2
1

More than 3 years have passed since last update.

@h0iga3shi13(Higashi Hiroyuki)

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

Last updated at Posted 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)

おわりに

2
1
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
2
1