このレッスンでは、SELECT 文で特に重要な機能について確認をしていきます。
これまでのレッスンで既に解説した内容も多いですが、復習を兼ねて、しっかりと自分のものとしていただければと思います。

テーブルの準備

本レッスンで使用するテーブルを準備します。
今回は、テーブルを 2 つ作成します。練習も兼ねて、手順どおりに作成を進めてください。
もし、失敗した場合でも、テーブルを削除して再作成することができますので、あせらず作業を進めていきましょう（テーブルを削除する方法）。

データベースの選択

まず、以下のように test データベースを選択します。

PowerShell

mysql> use test;

prefectures テーブルの作成

次に、prefecturesテーブル（都道府県テーブル）を作成します。
id（ID）と prefecture（都道府県名）だけの単純なテーブルです。
CREATE TABLE 文、INSERT 文のコマンドを実行して、テーブルを用意してください。

PowerShell

mysql> CREATE TABLE prefectures (
  id int primary key,
  prefecture varchar(20)
);

mysql> INSERT INTO prefectures VALUES
  (1, 'tokyo'),
  (2, 'osaka'),
  (3, 'nagoya'),
  (4, 'fukuoka');

mysql> SELECT * FROM prefectures;
+----+------------+
| id | prefecture |
+----+------------+
|  1 | tokyo      |
|  2 | osaka      |
|  3 | nagoya     |
|  4 | fukuoka    |
+----+------------+
4 rows in set (0.00 sec)

employees テーブルの作成

続いて、employeesテーブル（社員テーブル）を作成します。
ここでは、prefecture_id（都道府県 ID）というカラムを作成して、先ほど作成した prefectures テーブルに紐づけを行います。
この prefecture_id カラムには「外部キー制約（※）」を設定します。

FOREIGN KEY [インデックス名] ([外部キー設定カラム名]) REFERENCES [参照テーブル名]([参照テーブルのカラム名])

それでは、employeesテーブル（社員テーブル）を作成します。
次のように、CREATE TABLE 文、INSERT 文のコマンドを実行してください。

PowerShell

mysql> CREATE TABLE employees (
  id int primary key,
  last_name varchar(20),
  first_name varchar(20),
  age tinyint,
  gender varchar(8),
  prefecture_id int,
  foreign key fk_prefecture (prefecture_id) references prefectures(id)
);

mysql> INSERT INTO employees VALUES
  (1, 'tanaka', 'ichiro', 28, 'male', 1),
  (2, 'yamada', 'hanako', 19, 'female', 2),
  (3, 'suzuki', 'jiro', 33, 'male', 1),
  (4, 'saito', 'goro', 48, 'male', 3),
  (5, 'honda', 'umeko', 31, 'female', 2),
  (6, 'nakamura', 'yoshitaro', 23, 'male', 1),
  (7, 'yamada', 'taro', 55, 'male', 4),
  (8, 'watanabe', 'saburo', 25, 'male', 1),
  (9, 'yamamoto', 'yoshiko', 41, 'female', 1),
  (10, 'kato', null, 68, 'female', 4);

mysql> SELECT * FROM employees;
+----+-----------+------------+------+--------+---------------+
| id | last_name | first_name | age  | gender | prefecture_id |
+----+-----------+------------+------+--------+---------------+
|  1 | tanaka    | ichiro     |   28 | male   |             1 |
|  2 | yamada    | hanako     |   19 | female |             2 |
|  3 | suzuki    | jiro       |   33 | male   |             1 |
|  4 | saito     | goro       |   48 | male   |             3 |
|  5 | honda     | umeko      |   31 | female |             2 |
|  6 | nakamura  | yoshitaro  |   23 | male   |             1 |
|  7 | yamada    | taro       |   55 | male   |             4 |
|  8 | watanabe  | saburo     |   25 | male   |             1 |
|  9 | yamamoto  | yoshiko    |   41 | female |             1 |
| 10 | kato      | NULL       |   68 | female |             4 |
+----+-----------+------------+------+--------+---------------+
10 rows in set (0.00 sec)

以上のように、ID（id）、氏（last_name）、名（first_name）、年齢（age）、性別（gender）、都道府県ID（prefecture_id）をカラムに持った表ができていれば OK です。

DROP TABLE [テーブル名];

DROP TABLE employees;

AS は「テーブル名やカラム名に別名を指定する」ときに使用するものです。
これまでのレッスンでも使用してきましたので、おおよその使い方は分かっているものと思います。
ここでは、もう少し踏み込んで見ていきましょう。

基本構文

AS 句の基本構文は次のとおりです。AS の後に別名を付けるだけの単純なものです。

[カラム名 | テーブル名] AS [別名]

カラム名に別名を指定する

実際に AS 句を使用して、SQL 文を実行してみましょう。
カラム名 last_name の別名として、日本語の 氏 を指定してみます。

PowerShell

mysql> SELECT last_name AS 氏 FROM employees;
+----------+
| 氏       |
+----------+
| tanaka   |
| yamada   |
| suzuki   |
| saito    |
| honda    |
| nakamura |
| yamada   |
| watanabe |
| yamamoto |
| kato     |
+----------+
10 rows in set (0.00 sec)

無事に別名 氏 で取得できました。

AS を省略する

なお、AS は省略することができます。
今度は次のように SQL を実行してみましょう。
カラム名 last_name の別名に family_name と指定して取得してみます。

PowerShell

mysql> SELECT last_name family_name FROM employees;
+-------------+
| family_name |
+-------------+
| tanaka      |
| yamada      |
| suzuki      |
| saito       |
| honda       |
| nakamura    |
| yamada      |
| watanabe    |
| yamamoto    |
| kato        |
+-------------+
10 rows in set (0.00 sec)

問題なく取得できました。
SQL 文を読むときに詰まってしまわないように、省略できることを把握しておきましょう。

テーブル名に別名を指定する

AS 句は、テーブル名に別名を付けることもできます。
見慣れない書き方が出てきますが、次のような SQL 文を書くことができます。

PowerShell

SELECT e.id, e.last_name, p.prefecture
  FROM employees AS e
  INNER JOIN prefectures AS p
  ON e.prefecture_id = p.id;

テーブル名を読み替えているのは、次の２箇所です。

• FROM 句の後の employees AS e ： employees テーブルの別名を e と指定
• INNER JOIN 句の後の prefectures AS p ： prefectures テーブルの別名を p と指定

複数テーブルがある場合のカラム名の指定

なお、取得カラム名の指定を見ると e.id, e.last_name, p.prefecture となっています。
これは、一つの SELECT 文で 2 つ以上のテーブルを使用する場合は、次のように、テーブル名とカラム名を . で繋げて指定する必要があるためです（場合により省略可能ですが、ここでは触れません）。

[テーブル名].[カラム名]

別名を指定しなければ、employees.id, employees.last_name, prefectures.prefecture というように冗長な記載になってしまいます。

２つのテーブルを結合する

ここでは、INNER JOIN 句というものを使用して、employees テーブルと prefectures テーブルを結合しています。
INNER JOIN については、Lesson 11 で学習しますが、ここでも簡単に見ておきましょう。

INNER JOIN 句の基本構文は次のようになります。

SELECT [カラム名] FROM [テーブル名1] INNER JOIN [テーブル名2] ON [結合条件]

一方、今回の SQL では次のような指定になっています。

... FROM employees AS e INNER JOIN prefectures AS p ON e.prefecture_id = p.id

基本構文にあてはめると、[テーブル名1] に employees、[テーブル名2] に prefectures、[結合条件] に e.prefecture_id = p.id を指定していることになります。

つまり、社員テーブルの prefecture_id と都道府県テーブルの id が一致することを条件に 2 つのテーブルを結合しているということになります。

SQL を実行する

最後に、確認のため SQL を実行してみましょう。
なお、以下の例では、先に記載した SQL 文の AS 句を省略しています。

PowerShell

mysql> SELECT e.id, e.last_name, p.prefecture
  FROM employees e
  INNER JOIN prefectures p
  ON e.prefecture_id = p.id;
+----+-----------+------------+
| id | last_name | prefecture |
+----+-----------+------------+
|  1 | tanaka    | tokyo      |
|  3 | suzuki    | tokyo      |
|  6 | nakamura  | tokyo      |
|  8 | watanabe  | tokyo      |
|  9 | yamamoto  | tokyo      |
|  2 | yamada    | osaka      |
|  5 | honda     | osaka      |
|  4 | saito     | nagoya     |
|  7 | yamada    | fukuoka    |
| 10 | kato      | fukuoka    |
+----+-----------+------------+
10 rows in set (0.00 sec)

以上のように、テーブルが取得できれば OK です。

集合関数については、Lesson 8 などでも簡単に触れました。
ここでは主要な 5 つの集合関数について詳しく見ていきましょう。

主要な集合関数

標準 SQL に定められている集合関数は以下の表のとおりです。
これらの関数は、基本的に、どの DBMS でも使用できます。

なお、MySQL では他にも様々な関数が使用できますので、詳細については、公式リファレンス：集計関数 を参照してみてください。

それでは、１つずつ、順に見ていきましょう。

COUNT 関数

COUNT 関数を使用することで、レコード数を取得することができます。
構文は、COUNT(カラム名) というように引数にカウントしたいカラム名を指定します。

単純にレコード数をカウントする

カラム名に関係なく、該当するレコード数を取得する場合は、COUNT(*) と指定します。
以下の SQL で、employees テーブルのレコード数を取得してみましょう。

PowerShell

mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)

問題なくレコード数を取得することができました。

カラム名を指定してレコード数をカウントする

次に、カラム名を指定してレコード数を取得してみます。
ここでは、employees テーブルの first_name カラムを指定してみます。
なお、id = 10 のレコードの first_name カラムは NULL となっています。

+----+-----------+------------+------+--------+---------------+
| id | last_name | first_name | age  | gender | prefecture_id |
+----+-----------+------------+------+--------+---------------+
|  1 | tanaka    | ichiro     |   28 | male   |             1 |
|  2 | yamada    | hanako     |   19 | female |             2 |
|  3 | suzuki    | jiro       |   33 | male   |             1 |
|  4 | saito     | goro       |   48 | male   |             3 |
|  5 | honda     | umeko      |   31 | female |             2 |
|  6 | nakamura  | yoshitaro  |   23 | male   |             1 |
|  7 | yamada    | taro       |   55 | male   |             4 |
|  8 | watanabe  | saburo     |   25 | male   |             1 |
|  9 | yamamoto  | yoshiko    |   41 | female |             1 |
| 10 | kato      | NULL       |   68 | female |             4 |
+----+-----------+------------+------+--------+---------------+

COUNT 関数は、指定したカラムのうち NULL のレコードについてはカウントしません。
次のように、SQL を実行して確認してみましょう。

PowerShell

mysql> SELECT COUNT(first_name) FROM employees;
+-------------------+
| COUNT(first_name) |
+-------------------+
|                 9 |
+-------------------+
1 row in set (0.00 sec)

NULL のカラムがカウントされず、結果が 9 となることが確認できました。

条件を指定してレコード数をカウントする

実際のシステム開発においては、WHERE 句で絞り込みをしたり GROUP BY 句でグループ化した上で、COUNT 関数を使用することも多いです。
それぞれの実行方法について、確認しておきましょう。

① WHERE 句で絞込みをしてカウントする

それでは、WHERE 句で絞込みをして COUNT 関数を使ってみましょう。
ここでは、employees テーブルのうち、年齢（age）が30歳以上のレコード数を取得してみます。
SQL 文は次のように指定します。

PowerShell

mysql> SELECT COUNT(*) FROM employees WHERE age >= 30;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.02 sec)

以上のように 6 というレコード数を取得することができました。

② GROUP BY 句でグループ化してカウントする

次に、GROUP BY 句でグループ化して COUNT 関数を使ってみましょう。
まず、employees テーブルのうち、性別（gender）でグループ化した上でレコード数を取得してみます。
次のように SQL を実行することで、性別ごとのレコード数が取得できます。

PowerShell

mysql> SELECT gender, COUNT(*) FROM employees GROUP BY gender;
+--------+----------+
| gender | COUNT(*) |
+--------+----------+
| female |        4 |
| male   |        6 |
+--------+----------+
2 rows in set (0.03 sec)

続いて、応用的な SQL 文を作成してみます。
ここでは、employees テーブルと prefectures テーブルを結合して、prefecture（都道府県）でグループ化した上でレコード数を取得するようにしてみます。
SQL 文は次のように指定します。

PowerShell

mysql> SELECT p.prefecture, COUNT(*)
  FROM employees e
  INNER JOIN prefectures p
  ON e.prefecture_id = p.id
  GROUP BY p.prefecture;
+------------+----------+
| prefecture | COUNT(*) |
+------------+----------+
| fukuoka    |        2 |
| nagoya     |        1 |
| osaka      |        2 |
| tokyo      |        5 |
+------------+----------+
4 rows in set (0.00 sec)

無事に、都道府県ごとのレコード数を取得することができました。
少し複雑に見えますが、これまで学習してきた知識で理解できる内容となっていますので、ご自身で入力をしながら、それぞれの句の指定内容を理解するようにしてみてください。

MAX 関数

MAX 関数は、指定したカラムの最大値を返す関数です。
構文は、MAX(カラム名) というように引数にカラム名を指定します。
次のように SQL を指定することで、employees テーブルのうち age カラムの最大値を取得することができます。

PowerShell

mysql> SELECT MAX(age) FROM employees;
+----------+
| MAX(age) |
+----------+
|       68 |
+----------+
1 row in set (0.02 sec)

性別ごとの最大値を取得するには、GROUP BY 句を使って、次のように SQL を指定します。

PowerShell

mysql> SELECT gender, MAX(age) FROM employees GROUP BY gender;
+--------+----------+
| gender | MAX(age) |
+--------+----------+
| female |       68 |
| male   |       55 |
+--------+----------+
2 rows in set (0.00 sec)

MIN 関数

MIN 関数は、指定したカラムの最小値を返す関数です。つまり、MAX 関数の反対となります。
構文は、MIN(カラム名) というように引数にカラム名を指定します。
次の SQL で、employees テーブルのうち age カラムの最小値を取得してみましょう。

PowerShell

mysql> SELECT MIN(age) FROM employees;
+----------+
| MIN(age) |
+----------+
|       19 |
+----------+
1 row in set (0.00 sec)

次の SQL で性別ごとの最小値も確認してみましょう。

PowerShell

mysql> SELECT gender, MIN(age) FROM employees GROUP BY gender;
+--------+----------+
| gender | MIN(age) |
+--------+----------+
| female |       19 |
| male   |       23 |
+--------+----------+
2 rows in set (0.00 sec)

SUM 関数

SUM 関数は、指定したカラムの合計値を返す関数です。
他の関数と同様に、SUM(カラム名) というように引数にカラム名を指定します。
次の SQL で、employees テーブルの age カラムの合計値が取得できます。

PowerShell

mysql> SELECT SUM(age) FROM employees;
+----------+
| SUM(age) |
+----------+
|      371 |
+----------+
1 row in set (0.00 sec)

次の SQL で性別ごとの合計値も取得してみましょう。

PowerShell

mysql> SELECT gender, SUM(age) FROM employees GROUP BY gender;
+--------+----------+
| gender | SUM(age) |
+--------+----------+
| female |      159 |
| male   |      212 |
+--------+----------+
2 rows in set (0.00 sec)

AVG 関数

AVG 関数は、指定したカラムの平均値を返す関数です。
こちらも、AVG(カラム名) というように引数にカラム名を指定します。
同じような SQL ばかりで飽きるかもしれませんが、次のように指定して employees テーブルの age カラムの平均値を求めてみましょう。

PowerShell

mysql> SELECT AVG(age) FROM employees;
+----------+
| AVG(age) |
+----------+
|  37.1000 |
+----------+
1 row in set (0.00 sec)

性別ごとの age の平均値も取得してみましょう。

PowerShell

mysql> SELECT gender, AVG(age) FROM employees GROUP BY gender;
+--------+----------+
| gender | AVG(age) |
+--------+----------+
| female |  39.7500 |
| male   |  35.3333 |
+--------+----------+
2 rows in set (0.00 sec)

集合関数については、以上のとおりです。
集合関数は、GROUP BY 句と一緒に使用する場合も多いですので、そのあたりの書き方にも慣れておきたいところです。

SELECT INTO 構文を使用することで、データベースから取得した値を、変数などに格納することができます。
変数に格納した値は、プログラム内の次の処理に使用したりすることができます。

構文

基本的な構文を書くと次のようになります。

SELECT [カラム名] INTO [@変数名] FROM [テーブル名];

なお、変数に複数行のデータを入れることはできないため、取得レコードは 1 件のみとする必要があります。

SELECT INTO 文の実行

それでは、実際に SQL で実行してみましょう。
employees テーブルの id = 5 のレコードから prefecture_id（都道府県ID）を取得して、変数 @prefecture_id に格納してみます。

PowerShell

mysql> SELECT prefecture_id INTO @prefecture_id FROM employees WHERE id = 5;

続いて、変数 @prefecture_id に値が格納されているかを確認してみます。いつものように SELECT 文で取得しましょう。

PowerShell

mysql> SELECT @prefecture_id;
+----------------+
| @prefecture_id |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

employees テーブルの id = 5 のレコードの prefecture_id である 2 が変数に格納されていることが確認できました。
この変数は、そのまま次の操作に使用できます。
試しに prefectures テーブルから prefecture_id = 2 に対応する都道府県名（prefecture）を取得してみましょう。

PowerShell

mysql> SELECT prefecture FROM prefectures WHERE id = @prefecture_id;
+------------+
| prefecture |
+------------+
| osaka      |
+------------+
1 row in set (0.00 sec)

以上のとおり、変数 @prefecture_id を、他の SQL 文で使用できることが確認できました。

複数のカラムの値を取得する

SELECT INTO 文では、１つのレコードからしか値を取得できませんが、カラムは複数指定することができます。
次のように SQL を指定して、employees から、id，last_name，first_name を取得してみましょう。

PowerShell

mysql> SELECT id, last_name, first_name INTO @id, @last_name, @first_name
  FROM employees WHERE id = 1;

続いて、変数に値が格納されているかを確認してみます。

PowerShell

mysql> SELECT @id, @last_name, @first_name;
+------+------------+-------------+
| @id  | @last_name | @first_name |
+------+------------+-------------+
|    1 | tanaka     | ichiro      |
+------+------------+-------------+
1 row in set (0.00 sec)

以上のとおり、複数のカラムの値も、変数に格納することができました。

SELECT [カラム名] INTO OUTFILE [ディレクトリ/ファイル名] FROM [テーブル名];

mysql> SELECT @@secure_file_priv;
+------------------------------------------------+
| @@secure_file_priv                             |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.03 sec)

mysql> SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/result.txt' FROM employees;
Query OK, 10 rows affected (0.01 sec)

Lesson 8 でも解説しましたが、CONCAT 関数は、複数の文字列を連結する際に使用します。
ここでは、employees テーブルの last_name と first_name を結合してみましょう。
last_name と first_name の間には _（アンダースコア）を入れるようにしてみます。

次のように SQL を実行してみてください。

PowerShell

mysql> SELECT CONCAT(last_name, '_', first_name) FROM employees;
+------------------------------------+
| CONCAT(last_name, '_', first_name) |
+------------------------------------+
| tanaka_ichiro                      |
| yamada_hanako                      |
| suzuki_jiro                        |
| saito_goro                         |
| honda_umeko                        |
| nakamura_yoshitaro                 |
| yamada_taro                        |
| watanabe_saburo                    |
| yamamoto_yoshiko                   |
| NULL                               |
+------------------------------------+
10 rows in set (0.00 sec)

文字列が結合できたことが確認できました。
なお、最後の id = 10 のレコードは、first_name が NULL であるため、CONCAT 関数が実行できず NULL 値が返却されています。

以上 SELECT 文の機能のうち、重要と思われるものを中心に紹介しました。