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

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

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

記事のフォーマット

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

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

(前提条件)テーブル定義とデータ・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 | ユーザ１       | [e-mail] | 2020-08-01 00:00:00 | NULL       |
|  2 | ユーザ２       | [e-mail] | 2020-09-01 00:00:00 | NULL       |
|  3 | ユーザ３       | [e-mail] | 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 | タイトル１     | 本文１       | NULL        | 2020-08-01 00:00:00 | NULL       |
|  2 |       2 | タイトル２−１   | NULL       | picture.jpg | 2020-09-01 00:00:00 | NULL       |
|  3 |       2 | タイトル２−２   | 本文２−２     | NULL        | 2020-10-01 00:00:00 | NULL       |
+----+---------+-------------+------------+-------------+---------------------+------------+

MySQLのバージョン

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

mysql> select version();

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

逆引きSQL

●SQL ステートメントの構文

データベースの一覧を表示

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_database      |
+--------------------+

テーブルの一覧を表示

show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| articles                |
| users                   |
+-------------------------+

テーブルの一覧と、テーブルの種類を表示

テーブルの場合は BASE TABLE、ビューの場合は VIEW が表示されます。

show full tables;
+-------------------------+------------+
| Tables_in_test_database | Table_type |
+-------------------------+------------+
| articles                | BASE TABLE |
| users                   | BASE TABLE |
+-------------------------+------------+

テーブルレイアウトを確認

↑で説明用テーブルの紹介で使ったテーブル定義を確認するコマンドです。フレームワークからテーブルを作成したときはこの辺り自動で設定されますが、実際のテーブルがどうなっているのか確認したいときに使うと思います。

DESC 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    |                |
+------------+--------------+------+-----+---------+----------------+

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

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

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

SELECT * FROM users WHERE id = 1;

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  1 | ユーザ１       | [e-mail] | 2020-08-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

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

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

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

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  2 | ユーザ２       | [e-mail] | 2020-09-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

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

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

SELECT * FROM users WHERE id != 1;

+----+--------------+-------------------+---------------------+------------+
| id | name         | email             | created_at          | updated_at |
+----+--------------+-------------------+---------------------+------------+
|  2 | ユーザ２       | [e-mail] | 2020-09-01 00:00:00 | NULL       |
|  3 | ユーザ３       | [e-mail] | 2020-10-01 00:00:00 | NULL       |
+----+--------------+-------------------+---------------------+------------+

idが1,2のもの(値のリストに含まれるもの)【IN】

値のリストを持つデータを取得することができます。a OR b で置き換えることもできます。

SELECT * FROM articles WHERE id IN (1, 2);

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

idが1,2以外のもの(値のリストに含まれないもの)【NOT IN】

値のリストを持たないデータを取得することができます。NOT(a AND b)で置き換えることができます。NULLは対象外です。

SELECT * FROM articles WHERE id NOT IN (1, 2);

+----+---------+--------------------+-----------------+-------+---------------------+------------+
| id | user_id | title              | content         | image | created_at          | updated_at |
+----+---------+--------------------+-----------------+-------+---------------------+------------+
|  3 |       2 | タイトル２−２          | 本文２−２          | NULL  | 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 | タイトル１     | 本文１       | NULL        | 2020-08-01 00:00:00 | NULL       |
|  3 |       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 | タイトル２−１   | 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 | ユーザ１       | [e-mail] | 2020-08-01 00:00:00 | NULL       |
|  2 | ユーザ２       | [e-mail] | 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 | ユーザ３       | [e-mail] | 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 | タイトル１                | 2020-08-01 00:00:00 |       1 |
|  2 | タイトル２−１              | 2020-09-01 00:00:00 |       2 |
|  3 | タイトル２−２              | 2020-10-01 00:00:00 |       3 |
+----+-----------------------+---------------------+---------+

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

先程のSQLをサブクエリにしただけですが、１つの前の応用です。順位をつけることができたなら、それを抽出条件にすることもできるということです。ベスト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 | タイトル１                | 2020-08-01 00:00:00 |       1 |
+----+-----------------------+---------------------+---------+

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

文字列(または列)の結合【CONCAT】

任意の文字列の場合はシングルコーテーションで囲みます。

SELECT CONCAT('タイトルは「', title, '」です') AS title FROM articles;

+-----------------------+
| title                 |
+-----------------------+
| タイトルは「タイトル１」です    |
| タイトルは「タイトル２−１」です  |
| タイトルは「タイトル２−２」です  |
+-----------------------+

文字を左から切り取り【LEFT】

指定した文字数分を左から切り取ります(バイト数ではないので注意)。

SELECT title, LEFT(title, 2) AS title_mdf FROM articles;

+-----------------------+-----------+
| title                 | title_mdf |
+-----------------------+-----------+
| タイトル１                | タイ      |
| タイトル２−１              | タイ      |
| タイトル２−２              | タイ      |
+-----------------------+-----------+

文字を右から切り取り【RIGHT】

指定した文字数分を右から切り取ります(バイト数ではないので注意)。

SELECT title, RIGHT(title, 2) AS title_mdf FROM articles;

+-----------------------+-----------+
| title                 | title_mdf |
+-----------------------+-----------+
| タイトル１                | ル１       |
| タイトル２−１              | −１       |
| タイトル２−２              | −２       |
+-----------------------+-----------+

指定位置から指定文字数を切り取り【SUBSTR】

SUBSTRは、SUBSTRINGのシノニムです(別名のこと。機能は同じ)。位置は何文字目かを指定します。

• 指定位置から最後まで

SELECT title, SUBSTR(title, 2) AS title_mdf FROM articles;

+-----------------------+--------------------+
| title                 | title_mdf          |
+-----------------------+--------------------+
| タイトル１               | イトル１              |
| タイトル２−１             | イトル２−１            |
| タイトル２−２             | イトル２−２            |
+-----------------------+--------------------+

• 指定位置から指定文字数まで：SUBSTR(title, 2, 4)
  位置にマイナスの数値を指定すると、「末尾から何文字目か」になります

SELECT title, SUBSTR(title, 2, 4) AS title_mdf FROM articles;

+-----------------------+--------------+
| title                 | title_mdf    |
+-----------------------+--------------+
| タイトル１               | イトル１        |
| タイトル２−１             | イトル２        |
| タイトル２−２             | イトル２        |
+-----------------------+--------------+

文字列⇒日付、日付⇒文字列、置き換え

未

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

未

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

未

まとめ

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

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

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