Chapter13 関数とサブクエリ

概要と目標 欲しいレコードを、
抽出できるようになる。

レコードを抽出するSELECT文の使い方を学習し、
欲しいレコードが抽出できるようになりましょう。

今回のゴール

コマンドプロンプト

mysql> select count(*) from users;
+-------+
| total |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

mysql>
mysql> select name, password, char_length(password) as length
    ->   from users
    ->   having length <= 8;
+--------+----------+--------+
| name   | password | length |
+--------+----------+--------+
| 前田   | maemae   |      6 |
| 大島   | oshioshi |      8 |
| 高橋   | takataka |      8 |
| 山本   | yamayama |      8 |
+--------+----------+--------+
4 rows in set (0.00 sec)

mysql> select avg(char_length(email)) as email_length from users;
+--------------+
| email_length |
+--------------+
|      17.2000 |
+--------------+
1 row in set (0.00 sec)

mysql> select name, email, char_length(email) as length
    ->   from users
    ->   having length >= (select avg(char_length(email)) from users);
+--------+---------------------+--------+
| name   | email               | length |
+--------+---------------------+--------+
| 高橋   | takahashi@dummy.com |     19 |
| 指原   | sashihara@dummy.jp  |     18 |
| 山本   | yamamoto@dummy.net  |     18 |
+--------+---------------------+--------+
3 rows in set (0.00 sec)

mysql>

集約関数 レコード数や合計値、
最大値や、最小値を取得。

MySQLには、様々な関数が用意されている。
中でもフィールドの合計値や平均値、レコード数を取得できる集約関数はよく使う。

主な集約関数
関数 説明
COUNT(*) レコード数を取得する
SUM(フィールド名) 指定したフィールドの合計値を取得する
AVG(フィールド名) 指定したフィールドの平均値を取得する
MAX(フィールド名) 指定したフィールドの最大値を取得する
MIN(フィールド名) 指定したフィールドの最小値を取得する

詳細は12.19.1 GROUP BY (集約) 関数を参照

テーブル内のレコード数を抽出してみよう。

  1. MySQLモニタを起動
  2. 前回のレッスンで使用したデータベースがない場合は、
    「chapter13」 › 「settings」 › 「該当文字コード」フォルダ内の「setting.sql」を
    sourceコマンド、またはファイルの内容をコピー&ペーストし、
    今回のレッスンに必要なデータベースとテーブル、レコードを作成する
source /php-lessons/chapter13/settings/Shift-JIS/settings.sql;

ファイルパスは変更（コマンドラインツールにドラッグ&ドロップ）して下さい

実行例

コマンドプロンプト

mysql> source /php-lessons/chapter13/settings/Shift-JIS/settings.sql
Query OK, 2 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+----+---------+-----------------------------------+-----------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| id | user_id | title                             | content                                        | created             | modified            |
+----+---------+-----------------------------------+-----------------------------------------------------------------------------------------------------------+---------------------+---------------------+
|  1 |       2 | ブログ始めました                  | ついに、ブログを始めました。毎日更新するので楽しみにしてくださいね。                                      | 2017-04-07 10:00:00 | 2017-04-07 10:00:00 |
|  2 |       1 | 2回目の投稿！                     | こんにちは、2回目のブログですね。ちゃんと続いてますよ。                                        | 2017-04-08 10:00:00 | 2017-04-08 10:00:00 |
|  3 |       3 | そろそろ・・・                    | こんにちは、そろそろネタが尽きてきました。                                        | 2017-04-09 10:00:00 | 2017-04-09 10:00:00 |
|  4 |       3 | まだやってます                    | 1度、このブログをやめようと思いまいしたが、まだやることにしました。                                       | 2017-04-11 10:00:00 | 2017-04-11 10:00:00 |
|  5 |       3 | 久しぶりの投稿                    | お久しぶりです。ちょっと日が空いてしまいましたね。                                        | 2017-05-01 10:00:00 | 2017-05-01 10:00:00 |
|  6 |       3 | また、空いちゃいました            | お久しぶりです。またまた日が空いてしまいましたね。                                        | 2017-05-31 10:00:00 | 2017-05-31 10:00:00 |
|  7 |       4 | おいしいお店発見！                | こんにちは、おいしいパスタのお店を発見しました。                                        | 2017-07-15 10:00:00 | 2017-07-15 10:00:00 |
|  8 |       5 | 一人旅してます                    | こんにちは。今、北海道にいてます。一人旅してます。                                        | 2017-07-15 10:00:00 | 2017-07-15 10:00:00 |
|  9 |       4 | また、行っちゃいました            | こんにちは、先日見つけた美味しいパスタのお店に、また行っちゃいました。                                    | 2017-07-17 10:00:00 | 2017-07-17 10:00:00 |
| 10 |       3 | 限界です                          | こんにちは、もうブログを続けるのは限界です。                                        | 2017-07-18 10:00:00 | 2017-07-18 10:00:00 |
+----+---------+-----------------------------------+-----------------------------------------------------------------------------------------------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

+----+--------+---------------------+------------+---------------------+---------------------+
| id | name   | email               | password   | created             | modified            |
+----+--------+---------------------+------------+---------------------+---------------------+
|  1 | 前田   | maeda@dummy.com     | maemae     | 2017-04-03 10:00:00 | 2017-07-15 10:00:00 |
|  2 | 大島   | oshima@dummy.com    | oshioshi   | 2017-04-05 10:00:00 | 2017-04-05 10:00:00 |
|  3 | 高橋   | takahashi@dummy.com | takataka   | 2017-04-07 10:00:00 | 2017-05-10 10:00:00 |
|  4 | 指原   | sashihara@dummy.jp  | sashisashi | 2017-07-14 10:00:00 | 2017-07-20 10:00:00 |
|  5 | 山本   | yamamoto@dummy.net  | yamayama   | 2017-07-14 10:00:00 | 2017-08-10 10:00:00 |
+----+--------+---------------------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql>
  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 「posts」テーブルのレコード数を抽出するコマンドを記述し、
    コマンドラインツールにコピー&ペースト
chapter13/select.sql
-- 集約関数
-- 「posts」テーブルの全レコード数を抽出
select count(*) from posts;
実行例

コマンドプロンプト

mysql> select count(*) from posts;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.01 sec)

mysql>

演算結果のグループ化 集約関数を使った結果は、
グループ化できる。

SELECT構文にGROUP BY句を用いれば、抽出結果をグループ化できる。

GROUP BYを用いたSELECT構文
SELECT 表示するフィールド名 FROM テーブル名 [GROUP BY フィールド名];

[〜]： 省略可能
表示するフィールド名： 「,（半角カンマ）」区切りで複数指定できる。
                    「*（アスタリスク）」 を指定すると全てフィールドが表示される。

詳細は13.2.9 SELECT 構文を参照

レコードの抽出結果をグループ化しよう。

  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 「user_id」ごとのレコード数を抽出するコマンドを記述し、
    コマンドラインツールにコピー&ペースト
chapter13/select.sql
-- 演算結果のグループ化
-- 「posts」テーブルのレコード数を「user_id」ごとに抽出
select user_id, count(*) from posts group by user_id;
実行例

コマンドプロンプト

mysql> select user_id, count(*) from posts group by user_id;
+---------+----------+
| user_id | count(*) |
+---------+----------+
|       1 |        1 |
|       2 |        1 |
|       3 |        5 |
|       4 |        2 |
|       5 |        1 |
+---------+----------+
5 rows in set (0.00 sec)

mysql>

フィールドに別名を付ける フィールド名がわかりにくい時は、
別名を付ければいい。

フィールド名の後ろにASキーワードを使えば、フィールドに別名を付けれる。

フィールドに別名を用いたSELECT構文
SELECT 表示するフィールド名 AS 別名 FROM テーブル名 [GROUP BY フィールド名];

[〜]： 省略可能
表示するフィールド名： 「,（半角カンマ）」区切りで複数指定できる。
                    「*（アスタリスク）」 を指定すると全てフィールドが表示される。

詳細は13.2.9 SELECT 構文を参照

集約関数のフィールドに別名を付けてみよう。

  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 「user_id」ごとのレコード数を表示するフィールドの名前を「total」にするコマンドを記述し、
    コマンドラインツールにコピー&ペースト
chapter13/select.sql
-- フィールドに別名を付ける
-- 「posts」テーブルのレコード数を「user_id」ごとに「total」というフィールド名で抽出
select user_id, count(*) as total from posts group by user_id;
実行例

コマンドプロンプト

mysql> select user_id, count(*) as total from posts group by user_id;
+---------+-------+
| user_id | total |
+---------+-------+
|       1 |     1 |
|       2 |     1 |
|       3 |     5 |
|       4 |     2 |
|       5 |     1 |
+---------+-------+
5 rows in set (0.00 sec)

mysql>

演算結果に条件を付ける 演算結果に条件を付けるには、
WHEREじゃなく、HAVING。

演算結果に条件を指定する場合はHAVING句を使う。

演算結果に条件を付けるSELECT構文
SELECT 表示するフィールド名 AS 別名 FROM テーブル名
  [GROUP BY フィールド名]
  [HAVING 条件式];

[〜]： 省略可能
表示するフィールド名： 「,（半角カンマ）」区切りで複数指定できる。
                    「*（アスタリスク）」 を指定すると全てフィールドが表示される。

詳細は13.2.9 SELECT 構文を参照

集約関数の結果に条件を付けてみよう。

  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 「user_id」ごとのレコード数が「2」以上のレコードを抽出するコマンドを記述し、
    コマンドラインツールにコピー&ペースト
chapter13/select.sql
-- 集約関数の結果に条件を付ける
-- 「posts」テーブルの「user_id」ごとのレコード数が「2」以上のレコードを抽出
select user_id, count(*) as total
  from posts
  group by user_id
  having total >= 2;
実行例

コマンドプロンプト

mysql> select user_id, count(*) as total
    ->   from posts
    ->   group by user_id
    ->   having total >= 2;
+---------+-------+
| user_id | total |
+---------+-------+
|       3 |     5 |
|       4 |     2 |
+---------+-------+
2 rows in set (0.00 sec)

mysql>

文字列の関数 文字列に使える関数もある。

SQLにも様々な文字列に関する関数がある。

主な文字列の関数
関数 説明
LENGTH(フィールド名) 指定したフィールドの長さ（バイト数）を取得
CHAR_LENGTH(フィールド名) 指定したフィールドの文字数を取得
SUBSTRING(フィールド名, 開始位置, 終了位置) 指定したフィールドの開始位置から
終了位置までの文字列を取得する

詳細は12.5 文字列関数を参照

「CHAR_LENGTH」で、
文字数を抽出してみよう。

  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 「content」フィールドの文字数を抽出するコマンドを記述し、
    コマンドラインツールにコピー&ペースト
chapter13/select.sql
-- 文字列の関数
-- 「posts」テーブルの「content」の文字数を抽出
select content, char_length(content) as len from posts;
実行例

コマンドプロンプト

mysql> select content, char_length(content) as len from posts;
+-----------------------------------------------------------------------------------------------------------+-----+
| content                                                                                                   | len |
+-----------------------------------------------------------------------------------------------------------+-----+
| ついに、ブログを始めました。毎日更新するので楽しみにしてくださいね。                                      |  34 |
| こんにちは、2回目のブログですね。ちゃんと続いてますよ。                                                   |  28 |
| こんにちは、そろそろネタが尽きてきました。                                                                |  21 |
| 1度、このブログをやめようと思いまいしたが、まだやることにしました。                                       |  34 |
| お久しぶりです。ちょっと日が空いてしまいましたね。                                                        |  25 |
| お久しぶりです。またまた日が空いてしまいましたね。                                                        |  25 |
| こんにちは、おいしいパスタのお店を発見しました。                                                          |  24 |
| こんにちは。今、北海道にいてます。一人旅してます。                                                        |  25 |
| こんにちは、先日見つけた美味しいパスタのお店に、また行っちゃいました。                                    |  35 |
| こんにちは、もうブログを続けるのは限界です。                                                              |  22 |
+-----------------------------------------------------------------------------------------------------------+-----+
10 rows in set (0.00 sec)

mysql>

「SUBSTRING」で、
文字列を取得してみよう。

  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 「content」フィールドの1文字目から10文字を抽出するコマンドを記述し、
    コマンドラインツールにコピー&ペースト
chapter10/select.sql
-- 「posts」テーブルの「content」の「1」文字目から「10」文字抽出
  select title, substring(content, 1, 10) as content from posts;
実行例

コマンドプロンプト

mysql> select title, substring(content, 1, 10) as content from posts;
+-----------------------------------+--------------------------------+
| title                             | content                        |
+-----------------------------------+--------------------------------+
| ブログ始めました                  | ついに、ブログを始め           |
| 2回目の投稿！                     | こんにちは、2回目の            |
| そろそろ・・・                    | こんにちは、そろそろ           |
| まだやってます                    | 1度、このブログをや            |
| 久しぶりの投稿                    | お久しぶりです。ちょ           |
| また、空いちゃいました            | お久しぶりです。また           |
| おいしいお店発見！                | こんにちは、おいしい           |
| 一人旅してます                    | こんにちは。今、北海           |
| また、行っちゃいました            | こんにちは、先日見つ           |
| 限界です                          | こんにちは、もうブロ           |
+-----------------------------------+--------------------------------+
10 rows in set (0.00 sec)

mysql>

サブクエリ クエリを実行した結果を使って、
さらに別のクエリを発行する。

サブクエリは「副問い合わせ」といい、クエリを実行して抽出された結果を使って、
さらにクエリを発行するこができる。
サブクエリはSELECT句や、FROM句、HAVING句など、様々な箇所で利用できる。
（今回は、HAVING句での利用方法のみ学習）

HAVING句にサブクエリを付けるSELECT構文
SELECT 表示するフィールド名 AS 別名 FROM テーブル名
  [GROUP BY フィールド名]
  [HAVING フィールド名 演算子 (サブクエリ)];

[〜]： 省略可能
表示するフィールド名： 「,（半角カンマ）」区切りで複数指定できる。
                    「*（アスタリスク）」 を指定すると全てフィールドが表示される。

詳細は13.2.9 SELECT 構文を参照

記事の平均文字数を割り出し、
その平均文字数以下の記事を抽出してみよう。

サブクエリを使わずに、「content」フィールドの平均文字数を割り出し、
その平均文字数以下の記事を抽出したい場合は、 まずAVG()関数 と CHAR_LENGTH()関数 を使って
「content」フィールドの平均文字数を割り出す必要がある。

「content」フィールドの平均文字数を取得するSQL文
select avg(char_length(content)) from posts;

この結果、平均文字数は「27.3」文字であることがわかる。
そして、HAVING句 を活用して、「content」フィールドの文字数が「27.3」以下のレコードを抽出する。

「content」フィールドの文字数が「27.3」以下のレコードを抽出するSQL文
select content, char_length(content) as len from posts having len <= 27.3;

サブクエリを利用しない場合は、このようにクエリを2度に分けて発行する必要がある。
しかしサブクエリを使えば、このようなクエリを1度に発行することができる。

  1. 「chapter13」フォルダ内の「select.sql」をテキストエディタで開く
  2. 下記を参考に「サブクエリ」を利用して、「content」の文字数が平均文字数以下レコードを
    抽出するコマンドを記述し、コマンドラインツールにコピー&ペースト
chapter13/select.sql
-- サブクエリ（副問い合わせ）
-- 「posts」テーブルの「content」の文字数が、「content」の平均文字数以下のものを抽出
select content, char_length(content) as len
  from posts
  having len <= (select avg(char_length(content)) from posts);
実行例

コマンドプロンプト

mysql> -- 「posts」テーブルの「content」の文字数が、「content」の平均文字数以下のものを抽出
mysql> select content, char_length(content) as len
    ->   from posts
    ->   having len <= (select avg(char_length(content)) from posts);
+-----------------------------------------------------------------------------+-----+
| content                                                                     | len |
+-----------------------------------------------------------------------------+-----+
| こんにちは、そろそろネタが尽きてきました。                                  |  21 |
| お久しぶりです。ちょっと日が空いてしまいましたね。                          |  25 |
| お久しぶりです。またまた日が空いてしまいましたね。                          |  25 |
| こんにちは、おいしいパスタのお店を発見しました。                            |  24 |
| こんにちは。今、北海道にいてます。一人旅してます。                          |  25 |
| こんにちは、もうブログを続けるのは限界です。                                |  22 |
+-----------------------------------------------------------------------------+-----+
6 rows in set (0.00 sec)

mysql>

練習問題 今回の理解度をチェック。

「chapter13」フォルダ内の「training.sql」を利用し、以下の問題を解いて下さい。

実行例 （コマンド部分は省略）

コマンドプロンプト

mysql> [「users」テーブルの全レコード数を「total」というフィールド名で抽出]
+-------+
| total |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

mysql>
mysql> [「users」テーブルの「name」、「password」と「password」の文字数を
        「length」というフィールド名としたフィールドを、
        「password」が、「8」文字以内のレコードのみ抽出]
+--------+----------+--------+
| name   | password | length |
+--------+----------+--------+
| 前田   | maemae   |      6 |
| 大島   | oshioshi |      8 |
| 高橋   | takataka |      8 |
| 山本   | yamayama |      8 |
+--------+----------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> [「users」テーブルの「email」フィールドの平均文字数を、
        「email_length」というフィールド名で抽出]
+--------------+
| email_length |
+--------------+
|      17.2000 |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> [「users」テーブルの「name」、「email」と「email」の文字数を
        「length」というフィールド名としたフィールドを、
        「email」フィールドが「email」フィールドの平均文字数以上のレコードのみ抽出]
+--------+---------------------+--------+
| name   | email               | length |
+--------+---------------------+--------+
| 高橋   | takahashi@dummy.com |     19 |
| 指原   | sashihara@dummy.jp  |     18 |
| 山本   | yamamoto@dummy.net  |     18 |
+--------+---------------------+--------+
3 rows in set (0.01 sec)

mysql>
解答例
chapter13/training.sql
-- 「users」テーブルの全レコード数を「total」というフィールド名で抽出
select count(*) as total from users;

-- 「users」テーブルの「name」、「password」と「password」の文字数を「length」というフィールド名としたフィールドを、
-- 「password」が、「8」文字以内のレコードのみ抽出して下さい。
select name, password, char_length(password) as length
  from users
  having length <= 8;

-- 「users」テーブルの「email」フィールドの平均文字数を、
-- 「email_length」というフィールド名で抽出して下さい。
select avg(char_length(email)) as email_length from users;

-- 「users」テーブルの「name」、「email」と「email」の文字数を「length」というフィールド名としたフィールドを、
-- 「email」フィールドが「email」フィールドの平均文字数以上のレコードのみ抽出して下さい。
select name, email, char_length(email) as length
  from users
  having length >= (select avg(char_length(email)) from users);

解答例は全問題のチェックボックスが on になるとご覧いただけます。

まとめ SELECT構文は奥が深い。

SELECT構文は、非常複雑な抽出もできる構文。

  • 関数を使えば様々な計算結果を取得できる
  • 計算結果をグループごとや条件を付けて表示できる
  • サブクエリ使えば、複数のクエリを一度に発行できる