SQL基本文法

この講座では、SQLの基本文法について学びます。

• SQLによるデータの作成、抽出、更新、削除（CRUD操作）
• WHERE条件、集計関数、並び替え、グループ化
• 条件分岐（CASE WHEN）
• テーブルの結合（JOIN）

SQLのCRUD操作とテーブルの関係を以下の図に示します。

graph LR
    subgraph CRUD操作
        A["INSERT<br>データの挿入"]
        B["SELECT<br>データの抽出"]
        C["UPDATE<br>データの更新"]
        D["DELETE<br>データの削除"]
    end
    subgraph データベース
        T["テーブル<br>（行と列の集合）"]
    end
    A -- "行を追加" --> T
    T -- "行を取得" --> B
    C -- "行を変更" --> T
    D -- "行を削除" --> T

1. 事前準備

この講座のハンズオンでは、以下のツールが必要です。まだ準備できていない場合は、リンク先の手順に沿って準備をお願いします。

• MySQL

2. MySQLに接続する

ターミナル（Windowsの場合はコマンドプロンプトまたはPowerShell）を開き、以下のコマンドでMySQLに接続します。

mysql -u root -p

パスワードの入力を求められるので、MySQLのインストール時に設定したrootパスワードを入力します。

Enter password:

接続に成功すると、以下のようなメッセージが表示されます。

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.xx MySQL Community Server - GPL

mysql>

mysql> の部分に、SQLのコマンドを入力し実行していきます。

3. データベースの作成

データベースを作成するためには、CREATE DATABASEという構文を使用します。testdbというデータベースを作成する場合、以下のSQLを実行します。

CREATE DATABASE testdb;

正常に実行されると、以下のメッセージが表示されます。

Query OK, 1 row affected (0.01 sec)

Query OKはSQLの実行が正常終了したことを表します。1 row affectedは「1行のデータが影響を受けた」という意味で、今回は「一つのデータベースが作成された」ことを示します。

3.1 データベースの一覧を確認する

データベースが作成されたかを確認するために、SHOW DATABASESのコマンドを実行します。

SHOW DATABASES;

以下のように、先ほど作成したtestdbが一覧に含まれていれば成功です。

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

先ほど作成したtestdbを含む5つのデータベースが一覧表示されています。

4. データベースの指定

テーブルを作成する前に、「どのデータベースにテーブルを作成するか」を指定する必要があります。USEコマンドを使用して操作対象のデータベースを切り替えます。

USE testdb;

以下のメッセージが表示されれば、データベースの指定が完了です。

Database changed

これ以降のSQL操作は、testdbデータベースに対して行われます。

5. テーブルの作成

テーブルを作成するには、CREATE TABLEの構文を使用します。id、name、age、area、created_at、updated_atというカラムを持つpersonテーブルを作成する場合、以下のSQLを実行します。

CREATE TABLE person (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    area VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLEの後にテーブル名を記載し、カッコの中にカラム名とデータ型を記載します。

主なデータ型は以下のとおりです。

• INT：整数型（例：1, 100, -50）
• VARCHAR(n)：可変長文字列型。最大n文字まで格納できる（例：'taro', 'hello'）
• TEXT：長い文字列型。長文テキストの格納に使用する
• DOUBLE：小数型（例：3.14, 0.5）
• DATE：日付型（例：'2024-01-01'）
• DATETIME：日時型（例：'2024-01-01 10:30:00'）
• BOOLEAN：真偽値型（例：TRUE, FALSE）

今回のCREATE TABLE文では、データ型の他にいくつかのオプションを使用しています。

• AUTO_INCREMENT：レコードを挿入するたびに自動的に連番が割り振られる。手動で値を指定する必要がない
• PRIMARY KEY：主キー制約。テーブル内で値が重複しないことを保証し、各レコードを一意に識別する
• DEFAULT CURRENT_TIMESTAMP：レコード挿入時に、その時点の日時が自動的に設定される
• ON UPDATE CURRENT_TIMESTAMP：レコード更新時に、その時点の日時が自動的に設定される

正しく実行されると、以下のメッセージが表示されます。

Query OK, 0 rows affected (0.03 sec)

テーブルの作成はデータの変更を伴わないため、0 rows affectedと表示されます。

この時点では、テーブルの構造（カラム）だけが作成され、データはまだ入っていない空の状態です。

5.1 テーブルの一覧を確認する

テーブルの一覧を確認するために、SHOW TABLESコマンドを実行します。

SHOW TABLES;

personテーブルが表示されていれば成功です。

+------------------+
| Tables_in_testdb |
+------------------+
| person           |
+------------------+
1 row in set (0.00 sec)

testdbデータベース内にpersonテーブルが1つ存在していることが確認できます。

5.2 テーブルの構成を確認する

テーブルの構成が想定通りに作成されているかを確認するには、DESCRIBEコマンドを使用します。

DESCRIBE person;

以下のようにテーブル構成が表示されます。

+------------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field      | Type        | Null | Key | Default           | Extra                                         |
+------------+-------------+------+-----+-------------------+-----------------------------------------------+
| id         | int         | NO   | PRI | NULL              | auto_increment                                |
| name       | varchar(50) | YES  |     | NULL              |                                               |
| age        | int         | YES  |     | NULL              |                                               |
| area       | varchar(20) | YES  |     | NULL              |                                               |
| created_at | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+-------------+------+-----+-------------------+-----------------------------------------------+
6 rows in set (0.00 sec)

idカラムはint型でPRI（PRIMARY KEY）が設定されており、Extraにauto_incrementと表示されています。created_atとupdated_atはdatetime型で、デフォルト値にCURRENT_TIMESTAMPが設定されていることが確認できます。NullがYESのカラムは、値を指定しない場合にNULLが格納されます。

6. レコードの挿入（INSERT）

テーブルにレコードを挿入するには、INSERT INTOの構文を使用します。現在、personテーブルにはデータが入っていない空の状態です。

nameにtaro、ageに35、areaにtokyoを指定したレコードを挿入する場合、以下のSQLを実行します。

INSERT INTO
    person (name, age, area)
VALUES
    ('taro', 35, 'tokyo');

INSERT INTOの後にテーブル名を記載し、カッコの中に挿入するカラム名を指定します。VALUESの後のカッコに、挿入する値をカラムの順番に合わせて記載します。

idはAUTO_INCREMENTのため自動的に連番が割り振られ、created_atとupdated_atはDEFAULT CURRENT_TIMESTAMPのため挿入時の日時が自動的に設定されます。これらのカラムはINSERT文で指定する必要がありません。

正常に実行されると、以下のメッセージが表示されます。

Query OK, 1 row affected (0.01 sec)

1 row affectedは、1件のレコードが挿入されたことを示しています。実行後、テーブルは以下の状態になります。

もう1件、レコードを追加してみましょう。

INSERT INTO
    person (name, age, area)
VALUES
    ('jiro', 25, 'osaka');

実行後、テーブルは以下の状態になります。idは自動的に2が割り振られます。

7. レコードの抽出（SELECT）

レコードを抽出するには、SELECTの構文を使用します。現在のpersonテーブルの状態は以下のとおりです。

7.1 レコードの取得

全項目取得

SELECTの後に*を指定すると、テーブルの全カラムを対象にレコードを抽出できます。

SELECT * FROM person;

以下のように、全カラム・全レコードが表示されます。

+----+------+------+-------+---------------------+---------------------+
| id | name | age  | area  | created_at          | updated_at          |
+----+------+------+-------+---------------------+---------------------+
|  1 | taro |   35 | tokyo | 2025-01-01 10:00:00 | 2025-01-01 10:00:00 |
|  2 | jiro |   25 | osaka | 2025-01-01 10:05:00 | 2025-01-01 10:05:00 |
+----+------+------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

先ほど挿入した2件のレコードが、すべてのカラムで表示されています。2 rows in setは、2件のレコードが抽出されたことを示します。

項目を絞って取得

SELECTの後にカラム名をカンマ区切りで指定すると、必要なカラムだけを抽出できます。id、name、ageだけを取得する場合、以下のSQLを実行します。

SELECT
    id, name, age
FROM
    person;

以下のように、指定した3つのカラムだけが表示されます。

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | taro |   35 |
|  2 | jiro |   25 |
+----+------+------+
2 rows in set (0.00 sec)

SELECT *のときは6カラムすべてが表示されていましたが、id、name、ageを指定したことで、この3カラムのみが抽出されています。

7.2 条件を指定して抽出する（WHERE）

WHERE句を使用すると、条件を指定してレコードを抽出できます。現在のテーブルには以下の2件のデータがあります。

ここから、nameがtaroのレコードのみを抽出するSQLです。

SELECT
    *
FROM
    person
WHERE
    name = 'taro';

結果、taroのレコードのみが抽出されます。

+----+------+------+-------+---------------------+---------------------+
| id | name | age  | area  | created_at          | updated_at          |
+----+------+------+-------+---------------------+---------------------+
|  1 | taro |   35 | tokyo | 2025-01-01 10:00:00 | 2025-01-01 10:00:00 |
+----+------+------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

2件のデータのうち、WHERE name = 'taro'の条件に一致するtaroの1件だけが抽出されます。

WHEREを省略すると全件取得となります。件数が多いテーブルでは処理時間が膨大になるため、注意が必要です。

8. レコードの更新（UPDATE）

レコードを更新するには、UPDATEの構文を使用します。現在のテーブルの状態は以下のとおりです。

taroのageを35から40に変更するSQLです。

UPDATE
    person
SET
    age = 40
WHERE
    name = 'taro';

UPDATEの後に更新対象のテーブル名を記載し、SETの後に更新内容を記載します。WHEREで更新対象の条件を指定します。

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Rows matched: 1は条件に一致したレコードが1件、Changed: 1は実際に値が変更されたレコードが1件であることを示しています。

更新結果をSELECTで確認してみましょう。

SELECT * FROM person;

taroのageが40に変更されていれば成功です。

+----+------+------+-------+---------------------+---------------------+
| id | name | age  | area  | created_at          | updated_at          |
+----+------+------+-------+---------------------+---------------------+
|  1 | taro |   40 | tokyo | 2025-01-01 10:00:00 | 2025-01-01 11:00:00 |
|  2 | jiro |   25 | osaka | 2025-01-01 10:05:00 | 2025-01-01 10:05:00 |
+----+------+------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

taroのageが元の35から40に更新されていることが確認できます。また、updated_atがUPDATE実行時の日時に自動更新されている点にも注目してください。created_atは作成時の日時のまま変わりません。

9. レコードの削除（DELETE）

レコードを削除するには、DELETEの構文を使用します。現在のテーブルの状態は以下のとおりです。

nameがtaroであるレコードを削除するSQLです。

DELETE FROM
    person
WHERE
    name = 'taro';

Query OK, 1 row affected (0.00 sec)

1 row affectedにより、1件のレコードが削除されたことがわかります。

削除結果をSELECTで確認します。

SELECT * FROM person;

taroのレコードが表示されなければ成功です。

+----+------+------+-------+---------------------+---------------------+
| id | name | age  | area  | created_at          | updated_at          |
+----+------+------+-------+---------------------+---------------------+
|  2 | jiro |   25 | osaka | 2025-01-01 10:05:00 | 2025-01-01 10:05:00 |
+----+------+------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

taroのレコードが削除され、テーブルはjiroの1件のみになっています。

10. テーブルとデータベースの削除

10.1 テーブルの削除（DROP TABLE）

テーブル自体を削除したい場合は、DROP TABLE構文を使用します。

DROP TABLE person;

削除後にSHOW TABLESを実行すると、テーブルが存在しないことを確認できます。

SHOW TABLES;

Empty set (0.00 sec)

personテーブルが削除されたため、テーブルが1つも存在しないことを示すEmpty setが返されています。

10.2 データベースの削除（DROP DATABASE）

データベース自体を削除したい場合は、DROP DATABASE構文を使用します。

DROP DATABASE testdb;

削除後にSHOW DATABASESを実行すると、testdbが削除されていることを確認できます。

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

testdbが一覧から消え、システムデータベースの4件のみが表示されています。データベースが正しく削除されたことが確認できます。

11. WHERE条件 : LIKE（部分一致）

WHERE条件にLIKEを指定することで、文字列が部分一致するレコードを抽出できます。

LIKEでは%（ワイルドカード）を使用して一致条件を指定します。%は「任意の0文字以上の文字列」を意味し、%の位置によって一致条件が変わります。

LIKE 'sato%'のように検索語の後ろに%を置くと前方一致となり、satoで始まるレコードが対象になります。LIKE '%taro'のように前に%を置くと後方一致となり、taroで終わるレコードが対象になります。LIKE '%sato%'のように前後に%を置くと部分一致となり、satoを含むすべてのレコードが対象になります。

11.1 テストデータの準備

部分一致の動作を確認するために、テストデータを準備します。まずはデータベースとテーブルを再作成します。

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE person (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    area VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

3件のレコードを追加します。

INSERT INTO person (name, age, area) VALUES ('sato taro', 35, 'tokyo');
INSERT INTO person (name, age, area) VALUES ('sato jiro', 25, 'tokyo');
INSERT INTO person (name, age, area) VALUES ('suzuki taro', 20, 'nagoya');

実行後、テーブルは以下の状態になります。

データが正しく登録されているか確認します。

SELECT * FROM person;

+----+-------------+------+--------+---------------------+---------------------+
| id | name        | age  | area   | created_at          | updated_at          |
+----+-------------+------+--------+---------------------+---------------------+
|  1 | sato taro   |   35 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  2 | sato jiro   |   25 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  3 | suzuki taro |   20 | nagoya | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-------------+------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

11.2 前方一致で抽出する

nameの先頭にsatoがつくレコードを抽出します。

SELECT * FROM person
WHERE name LIKE 'sato%';

+----+-----------+------+-------+---------------------+---------------------+
| id | name      | age  | area  | created_at          | updated_at          |
+----+-----------+------+-------+---------------------+---------------------+
|  1 | sato taro |   35 | tokyo | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  2 | sato jiro |   25 | tokyo | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-----------+------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

3件のデータのうち、nameがsatoで始まるsato taroとsato jiroの2件が抽出されます。suzuki taroはsatoで始まらないため対象外です。

11.3 後方一致で抽出する

nameの末尾にtaroがつくレコードを抽出します。

SELECT * FROM person
WHERE name LIKE '%taro';

+----+-------------+------+--------+---------------------+---------------------+
| id | name        | age  | area   | created_at          | updated_at          |
+----+-------------+------+--------+---------------------+---------------------+
|  1 | sato taro   |   35 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  3 | suzuki taro |   20 | nagoya | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-------------+------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

3件のデータのうち、nameがtaroで終わるsato taroとsuzuki taroの2件が抽出されます。sato jiroはtaroで終わらないため対象外です。

11.4 部分一致で抽出する

nameにzuが含まれるレコードを抽出します。

SELECT * FROM person
WHERE name LIKE '%zu%';

+----+-------------+------+--------+---------------------+---------------------+
| id | name        | age  | area   | created_at          | updated_at          |
+----+-------------+------+--------+---------------------+---------------------+
|  3 | suzuki taro |   20 | nagoya | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-------------+------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

3件のデータのうち、nameにzuを含むのはsuzuki taroのみであるため、1件だけが抽出されます。

12. WHERE条件 : 比較演算子

WHERE条件では、LIKE以外にも様々な比較演算子を使用できます。

• =：等しい（例：age = 25）
• !=または<>：等しくない（例：age != 25）
• >：より大きい（例：age > 25）
• <：より小さい（例：age < 25）
• >=：以上（例：age >= 25）
• <=：以下（例：age <= 25）

現在のテーブルの状態は以下のとおりです。

例えば、ageが25より大きいレコードを抽出する場合、以下のSQLを実行します。

SELECT * FROM person
WHERE age > 25;

+----+-----------+------+-------+---------------------+---------------------+
| id | name      | age  | area  | created_at          | updated_at          |
+----+-----------+------+-------+---------------------+---------------------+
|  1 | sato taro |   35 | tokyo | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-----------+------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

ageが25より大きい条件に一致するのはsato taro（35歳）のみであるため、1件だけが抽出されます。

13. 集計関数

引き続き、以下のpersonテーブルを使用します。

13.1 COUNT（件数の取得）

COUNT関数を使用すると、抽出されたレコードの件数を取得できます。

personテーブルの全件数を取得するSQLです。

SELECT COUNT(*) FROM person;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

personテーブルに3件のレコードが存在するため、COUNT(*)の結果は3になっています。

WHEREと組み合わせて、条件に合う件数を取得することもできます。

SELECT COUNT(*) FROM person WHERE name LIKE '%sato%';

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

nameにsatoを含むレコードはsato taroとsato jiroの2件であるため、COUNT(*)の結果は2になっています。

13.2 SUM（合計値）

合計値を算出するには、SUM関数を使用します。

SELECT SUM(age) FROM person;

+----------+
| SUM(age) |
+----------+
|       80 |
+----------+
1 row in set (0.00 sec)

35 + 25 + 20 = 80 となり、正しく合計値が取得されています。

13.3 AVG（平均値）

平均値を算出するには、AVG関数を使用します。

SELECT AVG(age) FROM person;

+----------+
| AVG(age) |
+----------+
|  26.6667 |
+----------+
1 row in set (0.00 sec)

（35 + 25 + 20） / 3 = 26.6667 となり、正しく平均値が取得されています。

13.4 MAX（最大値）

最大値を算出するには、MAX関数を使用します。

SELECT MAX(age) FROM person;

+----------+
| MAX(age) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

3件のレコードのage（35, 25, 20）のうち、最大値である35が取得されています。

13.5 MIN（最小値）

最小値を算出するには、MIN関数を使用します。

SELECT MIN(age) FROM person;

+----------+
| MIN(age) |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

3件のレコードのage（35, 25, 20）のうち、最小値である20が取得されています。

14. 並び替え（ORDER BY）

指定したカラムでレコードの並び替えを行いたい場合、ORDER BYを使用します。

14.1 昇順で並び替える（ASC）

ageの昇順で並び替えるSQLです。昇順はデフォルトの動作なので、ASCは省略可能です。

SELECT * FROM person
ORDER BY age;

+----+-------------+------+--------+---------------------+---------------------+
| id | name        | age  | area   | created_at          | updated_at          |
+----+-------------+------+--------+---------------------+---------------------+
|  3 | suzuki taro |   20 | nagoya | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  2 | sato jiro   |   25 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  1 | sato taro   |   35 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-------------+------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

ageの値が小さい順（20 → 25 → 35）にレコードが並び替えられています。

14.2 降順で並び替える（DESC）

ageの降順で並び替える場合は、カラム名の後にDESCを記載します。

SELECT * FROM person
ORDER BY age DESC;

+----+-------------+------+--------+---------------------+---------------------+
| id | name        | age  | area   | created_at          | updated_at          |
+----+-------------+------+--------+---------------------+---------------------+
|  1 | sato taro   |   35 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  2 | sato jiro   |   25 | tokyo  | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
|  3 | suzuki taro |   20 | nagoya | 2025-01-01 12:00:00 | 2025-01-01 12:00:00 |
+----+-------------+------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

ageの値が大きい順（35 → 25 → 20）にレコードが並び替えられています。

15. グループ化（GROUP BY）

テーブルのレコードを特定のカラムでグループ化したい場合、GROUP BY構文を使用します。引き続き、以下のpersonテーブルを使用します。

15.1 グループ化してみる

areaごとの件数を出力します。

SELECT area, COUNT(*)
FROM person
GROUP BY area;

+--------+----------+
| area   | COUNT(*) |
+--------+----------+
| nagoya |        1 |
| tokyo  |        2 |
+--------+----------+
2 rows in set (0.00 sec)

areaごとにグループ化され、nagoyaは1件、tokyoは2件であることが表示されています。

15.2 グループ化と集計関数

COUNTの部分を他の集計関数に置き換えることで、グループ化したカラムに対する合計や平均値なども求められます。areaごとの平均年齢を求めてみます。

SELECT area, AVG(age)
FROM person
GROUP BY area;

+--------+----------+
| area   | AVG(age) |
+--------+----------+
| nagoya |  20.0000 |
| tokyo  |  30.0000 |
+--------+----------+
2 rows in set (0.00 sec)

areaごとの平均年齢が表示されています。nagoyaはsuzuki taro（20歳）のみなので20.0000、tokyoはsato taro（35歳）とsato jiro（25歳）の平均で30.0000になっています。

15.3 HAVING（グループ化した結果への条件指定）

グループ化した結果に対して条件を指定したい場合、HAVING句を使用します。WHEREはグループ化前のレコードに対する条件指定ですが、HAVINGはグループ化後の結果に対する条件指定です。

例えば、平均年齢が25より大きいエリアのみを抽出する場合、以下のSQLを実行します。

SELECT area, AVG(age)
FROM person
GROUP BY area
HAVING AVG(age) > 25;

+-------+----------+
| area  | AVG(age) |
+-------+----------+
| tokyo |  30.0000 |
+-------+----------+
1 row in set (0.00 sec)

平均年齢が25より大きいのはtokyo（30.0000）のみであるため、tokyoのレコードのみが抽出されます。

16. 条件分岐（CASE WHEN）

ここまで学んだ WHERE はレコードを「抽出する条件」でしたが、CASE WHENを使うと、条件に応じて「出力する値を切り替える」ことができます。プログラミングの if-else と同じような役割を持ち、実務でもよく使われる構文です。

16.1 基本構文

CASE WHENの基本構文は以下のとおりです。

SELECT
    カラム名,
    CASE
        WHEN 条件式1 THEN 値1
        WHEN 条件式2 THEN 値2
        ELSE デフォルト値
    END AS 別名
FROM テーブル名;

WHEN で条件を指定し、条件に合致した場合に THEN の値が出力されます。どの条件にも合致しなかった場合は ELSE の値が出力されます。END でCASE式の終わりを示し、AS で出力カラムに名前を付けます。

16.2 値を切り替えて出力する

実際に試してみましょう。person テーブルの age に応じて年齢区分を表示します。

SELECT
    name,
    age,
    CASE
        WHEN age >= 30 THEN 'senior'
        WHEN age >= 25 THEN 'mid'
        ELSE 'young'
    END AS age_group
FROM person;

以下のような実行結果が表示されます。

+-------------+------+-----------+
| name        | age  | age_group |
+-------------+------+-----------+
| sato taro   |   35 | senior    |
| sato jiro   |   25 | mid       |
| suzuki taro |   20 | young     |
+-------------+------+-----------+
3 rows in set (0.00 sec)

age が30以上の「sato taro」には senior、25以上の「sato jiro」には mid、それ以外の「suzuki taro」には young と表示されています。条件は上から順に評価され、最初に合致した THEN の値が採用されます。

16.3 集計関数と組み合わせる

CASE WHENは集計関数と組み合わせることで、条件ごとの集計を行うこともできます。たとえば、地域ごとの人数をCASE WHENで集計してみましょう。

SELECT
    COUNT(CASE WHEN area = 'tokyo' THEN 1 END) AS tokyo_count,
    COUNT(CASE WHEN area = 'nagoya' THEN 1 END) AS nagoya_count
FROM person;

以下のような実行結果が表示されます。

+-------------+--------------+
| tokyo_count | nagoya_count |
+-------------+--------------+
|           2 |            1 |
+-------------+--------------+
1 row in set (0.00 sec)

COUNT の中にCASE WHENを入れることで、条件に合致するレコードだけをカウントしています。GROUP BY を使わずに、1行で複数の条件ごとの集計結果を横並びに出力できるのが特徴です。

17. テーブルの結合（JOIN）

今までは一つのテーブルに対しての操作を扱ってきましたが、JOINを使用することで複数のテーブルを結合して出力できます。

17.1 テストデータの準備

結合を試すために、employee（社員）とdepartment（部署）の2つのテーブルを作成します。

CREATE TABLE employee (
    id INT,
    name VARCHAR(10),
    department_id INT
);

CREATE TABLE department (
    id INT,
    name VARCHAR(10)
);

employeeテーブルに3件のレコードを追加します。

INSERT INTO employee (id, name, department_id) VALUES (1, 'satou', 10);
INSERT INTO employee (id, name, department_id) VALUES (2, 'suzuki', 20);
INSERT INTO employee (id, name, department_id) VALUES (3, 'tanaka', 40);

departmentテーブルに3件のレコードを追加します。

INSERT INTO department (id, name) VALUES (10, 'system');
INSERT INTO department (id, name) VALUES (20, 'finance');
INSERT INTO department (id, name) VALUES (30, 'general');

実行後、2つのテーブルは以下の状態になります。

employeeテーブル

departmentテーブル

ここで注目すべき点は、employeeのtanaka（department_idが40）に対応する部署がdepartmentテーブルに存在しないことと、departmentのgeneral（idが30）に所属する社員がemployeeテーブルに存在しないことです。この不一致が、結合の種類によって結果にどう影響するかを見ていきます。

データが正しく登録されているか確認します。

SELECT * FROM employee;

+------+--------+---------------+
| id   | name   | department_id |
+------+--------+---------------+
|    1 | satou  |            10 |
|    2 | suzuki |            20 |
|    3 | tanaka |            40 |
+------+--------+---------------+
3 rows in set (0.00 sec)

SELECT * FROM department;

+------+---------+
| id   | name    |
+------+---------+
|   10 | system  |
|   20 | finance |
|   30 | general |
+------+---------+
3 rows in set (0.00 sec)

17.2 INNER JOIN（内部結合）

INNER JOINは、結合条件に対して両方のテーブルに存在するレコードのみを抽出します。

SELECT * FROM employee
INNER JOIN department
ON employee.department_id = department.id;

ONの後に結合条件を記載します。この場合は、employeeのdepartment_idとdepartmentのidが一致するレコードを結合しています。

+------+--------+---------------+------+---------+
| id   | name   | department_id | id   | name    |
+------+--------+---------------+------+---------+
|    1 | satou  |            10 |   10 | system  |
|    2 | suzuki |            20 |   20 | finance |
+------+--------+---------------+------+---------+
2 rows in set (0.00 sec)

employeeのdepartment_idが40のレコード（tanaka）は、departmentにidが40のレコードが存在しないため、抽出対象外となります。同様に、departmentのidが30のレコード（general）も抽出されません。両方のテーブルで一致するレコードのみが結果に含まれます。

17.3 LEFT JOIN（左外部結合）

LEFT JOINは、左側（結合元）テーブルの全レコードを出力し、右側（結合先）テーブルのレコードが存在する場合のみ結合します。存在しない場合はNULLになります。

SELECT * FROM employee
LEFT JOIN department
ON employee.department_id = department.id;

+------+--------+---------------+------+---------+
| id   | name   | department_id | id   | name    |
+------+--------+---------------+------+---------+
|    1 | satou  |            10 |   10 | system  |
|    2 | suzuki |            20 |   20 | finance |
|    3 | tanaka |            40 | NULL | NULL    |
+------+--------+---------------+------+---------+
3 rows in set (0.00 sec)

左側のemployeeテーブルの全レコードが表示されます。tanaka（department_idが40）は、該当する部署が存在しないため、department側のカラムがNULLになっています。

17.4 RIGHT JOIN（右外部結合）

RIGHT JOINは、LEFT JOINとは逆で、右側（結合先）テーブルの全レコードをベースに、左側（結合元）テーブルの存在するもののみを表示します。

SELECT * FROM employee
RIGHT JOIN department
ON employee.department_id = department.id;

+------+--------+---------------+------+---------+
| id   | name   | department_id | id   | name    |
+------+--------+---------------+------+---------+
|    1 | satou  |            10 |   10 | system  |
|    2 | suzuki |            20 |   20 | finance |
| NULL | NULL   |          NULL |   30 | general |
+------+--------+---------------+------+---------+
3 rows in set (0.00 sec)

右側のdepartmentテーブルの全件が表示され、employeeに該当レコードがないgeneralは、employee側のカラムがNULLになっています。

18. エイリアス（AS）

18.1 カラムに名前をつける

結合したテーブルでは、同じ名前のカラムが存在することがあり、どちらのテーブルのカラムなのかわかりにくくなります。ASを使用してカラムに別名をつけることで、結果を読みやすくできます。

SELECT
    employee.id AS employee_id,
    employee.name AS employee_name,
    department.id AS department_id,
    department.name AS department_name
FROM employee
INNER JOIN department
ON employee.department_id = department.id;

+-------------+---------------+---------------+-----------------+
| employee_id | employee_name | department_id | department_name |
+-------------+---------------+---------------+-----------------+
|           1 | satou         |            10 | system          |
|           2 | suzuki        |            20 | finance         |
+-------------+---------------+---------------+-----------------+
2 rows in set (0.00 sec)

ASで指定した別名（employee_id、employee_name、department_id、department_name）がカラム名として表示されています。同じ名前のidやnameカラムが区別しやすくなっています。

18.2 テーブルに別名をつける

テーブル名が長い場合、SQL内で短い別名を指定できます。employee eのように記載することで、SQL内でeと書くだけでemployeeを参照できます。

SELECT
    e.id AS employee_id,
    e.name AS employee_name,
    d.id AS department_id,
    d.name AS department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.id;

+-------------+---------------+---------------+-----------------+
| employee_id | employee_name | department_id | department_name |
+-------------+---------------+---------------+-----------------+
|           1 | satou         |            10 | system          |
|           2 | suzuki        |            20 | finance         |
+-------------+---------------+---------------+-----------------+
2 rows in set (0.00 sec)

テーブルに別名（e、d）を付けた場合でも、先ほどと同じ結果が得られます。SQL文内でemployee.nameの代わりにe.nameと短く記述できるため、可読性が向上しています。

19. クリーンアップ

ハンズオンが完了したら、この講座で作成したテーブルとデータベースを削除します。

DROP TABLE person;

DROP TABLE employee;

DROP TABLE department;

DROP DATABASE testdb;

削除が完了したら、MySQLからexitコマンドで抜けます。

exit

20. まとめ

この講座では、SQLの基本文法について学びました。

• SQLはリレーショナルデータベースに対してデータ操作を行うための言語である
• CREATE DATABASE / CREATE TABLEでデータベースやテーブルを作成し、DROPで削除する
• INSERT INTOでレコードを挿入し、SELECTで抽出する。WHERE句で条件を指定できる
• UPDATEでレコードを更新し、DELETEで削除する。WHEREを省略すると全件が対象となるため注意が必要
• LIKEを使用すると文字列の部分一致検索が可能。%の位置で前方一致、後方一致、部分一致を切り替える
• 集計関数（COUNT、SUM、AVG、MAX、MIN）でデータの集計が可能
• ORDER BYでレコードの並び替え、GROUP BYでグループ化、HAVINGでグループ化後の条件指定が可能
• CASE WHENで条件に応じて出力する値を切り替えられる。集計関数と組み合わせることで条件ごとの集計も可能
• INNER JOIN、LEFT JOIN、RIGHT JOINで複数のテーブルを結合できる
• ASを使用してカラムやテーブルに別名をつけることで、SQLの可読性を向上できる