【逆引き】よく使うSQLまとめ【MySQL】

公開:2020/10/02
更新:2020/10/02
10 min読了の目安(約9700字TECH技術記事

よく使うMySQLのSQL構文を、逆引きできるよう日本語でまとめました!
このSQLが何をしているものなのか、わかりやすいように日本語で書いてあります。やりたいことをCtrl(command) + Fキーで検索できるよう、それっぽい語句を含ませてます。

ORマッパーを使っていると、生のSQLを書く機会はあまり多くないと思いますが、不具合調査したりちょっとデータを修正するくらいで、あればやっぱりSQLを書けた方が便利ですよね。

記事のフォーマット

  • (日本語で)何をしているSQLか
  • SQL
  • 結果

を1セットでまとめています。

(前提条件)テーブル定義とデータ・MySQLのバージョン

この記事で使うテーブル定義とデータはこのようになっています。
ユーザーテーブルと記事テーブルで、ユーザーは記事を複数持ちます(1対多)

users

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| email      | varchar(255) | NO   |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
| updated_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  1 | ユーザ1       | user1@example.com | 2020-08-01 00:00:00 | NULL       |
|  2 | ユーザ2       | user2@example.com | 2020-09-01 00:00:00 | NULL       |
|  3 | ユーザ3       | user3@example.com | 2020-10-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

articles

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | int unsigned | NO   |     | NULL    |                |
| title      | varchar(255) | NO   |     | NULL    |                |
| content    | text         | YES  |     | NULL    |                |
| image      | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
| updated_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

+----+---------+-------------+------------+-------------+---------------------+------------+
| id | user_id | title       | content    | image       | created_at          | updated_at |
+----+---------+--------------+------------+------------+---------------------+------------+
|  1 |       1 | タイトル1     | 本文1       | NULL        | 2020-08-01 00:00:00 | NULL       |
|  2 |       2 | タイトル2−1   | NULL       | picture.jpg | 2020-09-01 00:00:00 | NULL       |
|  3 |       2 | タイトル2−2   | 本文2−2     | NULL        | 2020-10-01 00:00:00 | NULL       |
+----+---------+-------------+------------+-------------+---------------------+------------+

MySQLのバージョン

バージョンは「8.0.20」を使った結果になります。

mysql> select version();

+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+

逆引きSQL

●データ操作言語 (DML) WHERE句

idが「1」のユーザを抽出【 = 】

対象列がINTなどの数値型の場合

SELECT * FROM users WHERE id = 1;

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  1 | ユーザ1       | user1@example.com | 2020-08-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

名前が「ユーザ2」のユーザを抽出【 = ''】

対象列がVARCHARなどの文字型の場合はシングルコーテーションで囲います。
ちなみに = でNULLのデータは抽出されません。NULLを取得したい場合は後述のIS NULLを参照

SELECT * FROM users WHERE name = 'ユーザ2';

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  2 | ユーザ2       | user2@example.com | 2020-09-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

idが1以外のユーザを抽出【 != 】

否定は != でも <> でもかけますが、個人的には != 推し
例は挙げてませんが、否定も同じく文字型の場合はシングルコーテーションが必要です。

SELECT * FROM users WHERE id != 1;

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  2 | ユーザ2       | user2@example.com | 2020-09-01 00:00:00 | NULL       |
|  3 | ユーザ3       | user3@example.com | 2020-10-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

イメージが登録されていない記事を抽出【IS NULL】

NULLは = や != では比較できません。NULLはNULLです。

SELECT * FROM articles WHERE image IS NULL;

+----+---------+-------------+------------+-------------+---------------------+------------+
| id | user_id | title       | content    | image       | created_at          | updated_at |
+----+---------+--------------+------------+------------+---------------------+------------+
|  1 |       1 | タイトル1     | 本文1       | NULL        | 2020-08-01 00:00:00 | NULL       |
|  3 |       2 | タイトル2−2   | 本文2−2     | NULL        | 2020-10-01 00:00:00 | NULL       |
+----+---------+-------------+------------+-------------+---------------------+------------+

イメージが登録されている記事を抽出【IS NOT NULL】

SELECT * FROM articles WHERE image IS NOT NULL;

+----+---------+-------------+------------+-------------+---------------------+------------+
| id | user_id | title       | content    | image       | created_at          | updated_at |
+----+---------+--------------+------------+------------+---------------------+------------+
|  2 |       2 | タイトル2−1   | NULL       | picture.jpg | 2020-09-01 00:00:00 | NULL       |
+----+---------+-------------+------------+-------------+---------------------+------------+

記事があるユーザを抽出【EXISTS】

苦手な人が多いEXISTS。
EXISTS内のサブクエリの結果が真になる(1行でも記事が存在する)ユーザが抽出されます。サブクエリのWHEREがキモです。
ちなみにサブクエリ内のアスタリスクに意味はありません。「1」でも「'zenn'」でも結果は変わりません。(EXISTS または NOT EXISTS を使用したサブクエリー)

SELECT * FROM users WHERE EXISTS(
        SELECT * FROM articles WHERE users.id = articles.user_id
    );

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  1 | ユーザ1       | user1@example.com | 2020-08-01 00:00:00 | NULL       |
|  2 | ユーザ2       | user2@example.com | 2020-09-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

記事がないユーザを抽出【NOT EXISTS】

サブクエリ内のデータが偽になる(1行も記事が存在しない)ユーザが抽出されます。

SELECT * FROM users WHERE NOT EXISTS(
        SELECT * FROM articles WHERE users.id = articles.user_id
    );

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  3 | ユーザ3       | user3@example.com | 2020-10-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

●データ操作言語 (DML) SELECT句

記事数の集計【COUNT】

ただ件数を数えたいだけならGROUP BYは不要。

SELECT COUNT(*) FROM articles;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

ユーザ毎の合計記事数【COUNT 〜 GROUP BY】

列の値ごと(集計キーごと)に集計したい場合はGROUP BYが必要。

SELECT user_id, COUNT(*) FROM articles GROUP BY user_id;

+---------+----------+
| user_id | COUNT(*) |
+---------+----------+
|       1 |        1 |
|       2 |        2 |
+---------+----------+

作成日の順位をつける【ROW_NUMBER】

順位をつけてくれる便利なウィンドウ関数です。このSQLだけだとありがたみを感じないかもしれません・・。でもこれを覚えるとどんなデータでも取得できる気がしてきます。
RANK()との違いは、同順だった場合に順位が一意になるかならないかで、ROW_NUMBERの場合は必ず一意になり、RANKの場合はなりません(例えば1、1、3、4、5とかになる)

SELECT id, title, created_at, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM articles;

+----+-----------------------+---------------------+---------+
| id | title                 | created_at          | row_num |
+----+-----------------------+---------------------+---------+
|  1 | タイトル1                | 2020-08-01 00:00:00 |       1 |
|  2 | タイトル2−1              | 2020-09-01 00:00:00 |       2 |
|  3 | タイトル2−2              | 2020-10-01 00:00:00 |       3 |
+----+-----------------------+---------------------+---------+

作成日の順位をつけて、一番古い記事を取得【ROW_NUMBER】

先程のSQLをサブクエリにしただけですが、1つの前の応用です。順位をつけることができたなら、それを抽出条件にすることもできるということです。ベスト3とかワースト3とか、並べ替えた内の数件だけ取得したいといった場面で活躍します。

SELECT a.id, a.title, a.created_at, a.row_num FROM (
    SELECT articles.* 
    , ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
    FROM articles
) a
WHERE a.row_num = 1;

+----+-----------------------+---------------------+---------+
| id | title                 | created_at          | row_num |
+----+-----------------------+---------------------+---------+
|  1 | タイトル1                | 2020-08-01 00:00:00 |       1 |
+----+-----------------------+---------------------+---------+

●データ操作言語 (DML) 文字列編集

結合、何文字目から何文字目、文字列⇒日付、日付⇒文字列、

●データ操作言語 (DML) JOIN句

●データ操作言語 (DML) ORDER BY句

まとめ

以上、MySQLの逆引きSQLでした!思い出したら追加していきます。
不明なところや、この場合はどうしたらえぇねんとかあれば、コメントかTwitterへ(@paya02_ictdev)

DBの用意にはこちらの記事を参考にさせていただきました!ありがとうございます。

docker-compose でMySQL環境簡単構築
https://qiita.com/A-Kira/items/f401aea261693c395966