Lesson 8
関数
Lesson 8
Chapter 1
数値関数、文字列関数、日付関数、集合関数、変換関数
本レッスンでは、SQL で使用する関数について学んでいきます。
SQL もプログラミング言語であるため、数多くの関数が用意されています。
また、各 DBMS(Oracle、PostgreSQL、SQLServer など)ごとに、独自の関数があります。
ここでは各 DBMS で共通にサポートされている関数を中心に、よく使用される関数を紹介していきます。 全ての関数を覚える必要はなく、実務においては、必要な時に調べて適切に使いこなすことができれば問題ないでしょう(公式リファレンス参照)。
それでは、学習をはじめていきましょう。
数値関数
最初に取り上げる「数値関数」は、指数関数、三角関数などのいわゆる「数学的な計算」を行う関数となります。
基本的にはコンピュータが計算してくれますので、使い方さえ把握できれば大丈夫です。
主な数値関数一覧
主に以下のような数値関数があります(詳しくは 公式リファレンス 参照)。
No | 名前 | 一般性 | 説明 |
---|---|---|---|
1 | ABS | ◎ | 絶対値を返す |
2 | MOD(%) | ◎ | 割り算(除算)の余りを返す |
3 | POWER(POW) | ◎ | べき乗を計算して返す |
4 | EXP | ◎ | 指数関数を計算して返す |
5 | LN(LOG),LOG10,LOG2 | ◎ |
自然対数、常用対数、二進対数を返す |
6 | SQRT | ◎ | 引数の平方根を返す |
7 | ROUND,CEILING(CEIL), FLOOR(≒ TRUNCATE) |
◎ | 数値を丸めて返す(四捨五入、切上げ、切捨て) |
8 | SIGN | ○ | 引数の符号を返す |
9 | RAND | △ | ランダム値(浮動小数点値)を返す |
10 | CONV | △ | 基数変換を行う(M 進数を N 進数に変換) |
11 | SIN,COS,TAN,COT | ○ | 三角関数(サイン、コサイン、タンジェント、コタンジェント)を返す |
12 | ASIN,ACOS,ATAN,ATAN2 | ○ | 逆三角関数(アークサイン、アークコサイン、アークタンジェント)を返す |
13 | PI | ○ | 円周率(π)を返す |
※ 項目「名前」に括弧書で記載されているのは関数の別名(同機能)です。
別名があるのは「標準 SQL 規格」や「他の DBMS」との整合性・互換性を保つためなどの理由によります。
※ 項目「一般性」は、① 標準 SQL 規格にあるか、② 各 DBMS で共通に用意されているかなどを基準に、独自の判断をしたものです。
よく使用される数値関数の実行
以下、よく使用する関数について、簡単に解説をしていきます。
特にテーブルは使用しないので、PowerShell でコマンドを打って確認していきましょう。
① ABS 関数(絶対値を取得)
ABS 関数は絶対値を返します。
次のようにコマンドを打って、@val
変数に、テスト値 ABS(-28)
を格納します。
PowerShell
mysql> SET @val = ABS(-28);
@val
の内容を確認するには、SELECT 句を使用します。
PowerShell
mysql> SELECT @val;
+------+
| @val |
+------+
| 28 |
+------+
1 row in set (0.00 sec)
正しく、絶対値が取得できていることが確認できました。
② POWER 関数(べき乗を取得)
POWER 関数はべき乗を計算します。POWER(X, Y)
と書くことで、X の Y 乗を返します。
次のようにコマンドを打つと、2 の 10 乗の計算結果として 1024 が取得できます。
PowerShell
mysql> SELECT @val;mysql> SELECT POWER(2, 10);
+--------------+
| POWER(2, 10) |
+--------------+
| 1024 |
+--------------+
1 row in set (0.00 sec)
③ SQRT 関数(平方根を取得)
SQRT 関数は、平方根(ルート)を返します。
次のようにコマンドを打つと、2 の 平方根として 1.41421356... が取得できます。
PowerShell
mysql> SELECT SQRT(2);
+--------------------+
| SQRT(2) |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set (0.00 sec)
④ ROUND,CEILING,FLOOR 関数(数値を丸める)
各関数の指定方法は、次のとおりです。
ROUND(X, D)
:X を小数点 D 桁で四捨五入します。D は省略可能でデフォルトは 0 です。CEILING(X)
:X の小数点を切り上げした整数を返します。FLOOR(X)
:X の小数点を切り下げした整数を返します。
PowerShell
mysql> SELECT ROUND(9.876, 2), ROUND(9.876), CEILING(9.876), FLOOR(9.876);
+-----------------+--------------+----------------+--------------+
| ROUND(9.876, 2) | ROUND(9.876) | CEILING(9.876) | FLOOR(9.876) |
+-----------------+--------------+----------------+--------------+
| 9.88 | 10 | 10 | 9 |
+-----------------+--------------+----------------+--------------+
1 row in set (0.00 sec)
⑤ RAND 関数(ランダム値を取得)
RAND() 関数を使用すると、0 以上 1 未満のランダムな浮動小数点数が取得できます。
PowerShell
mysql> SELECT RAND();
+---------------------+
| RAND() |
+---------------------+
| 0.28151403114340645 |
+---------------------+
1 row in set (0.00 sec)
⑥ CONV 関数(進数変換)
CONV() 関数を使用すると、例えば、16 進数を 10 進数に変換したりすることができます。
基本構文は次のとおりです。
CONV(N, from_base, to_base)
N
に変換したい数値を文字列で指定し、from_base
に変換前の基数 to_base
に変換後の基数を指定します。
基数の指定は 2 ~ 36 までの間で任意に指定できます。
基数とは
基数とは進数表記をする際の「基礎となる数」のことをいいます。
そのままですが、2 進数の基数は 2、10 進数の基数は 10、16 進数の基数は 16 となります。
ここでは、10 進数で「255」である数値を、16 進数に変換してみましょう。
PowerShell
mysql> SELECT CONV('255', 10, 16);
+---------------------+
| CONV('255', 10, 16) |
+---------------------+
| FF |
+---------------------+
1 row in set (0.00 sec)
以上のとおり、10 進数の「255」は、16 進数で「FF」となりますので、正しく取得できたことになります。
以上、よく使用する数値関数について確認を行いました。
そのほかの数値関数については、公式リファレンス に使用方法が書いてありますので、必要に応じて参照してみてください。
文字列関数
文字列関数は、文字列から特定のデータを取得したり、加工したりする場合に使用します。
主な数値関数一覧
主に以下のような文字列関数があります(詳しくは 公式リファレンス 参照)。
No | 名前 | 一般性 | 説明 |
---|---|---|---|
1 | LOWER,UPPER | ◎ | 文字列を小文字または大文字に変換する |
2 | CONCAT | ◎ |
文字列を連結する (DBMS により関数名は区々のため注意) |
3 | TRIM | ◎ | 文字列の先頭と末尾にある空白(または指定文字)を削除する |
4 | LTRIM,RTRIM | ◎ | 文字列の先頭または末尾にある空白を削除する |
5 | CHAR_LENGTH,LENGTH | ◎ | 文字列の文字数を返す、文字列のバイト数を返す |
6 | SUBSTRING(SUBSTR,MID) | ◎ |
指定位置から始まる部分文字列を返す (SUBSTR、MID は別名) |
7 | LEFT,RIGHT | ○ | 左端または右端から指定数分の文字を返す |
8 | REPLACE | ○ | 文字列を置換する |
9 | STRCMP | ○ | 2 つの文字列を比較 |
10 | ASCII,ORD | ○ | ASCII文字の文字コードを返す、マルチバイト文字の文字コードを返す |
11 | CHAR | ○ | 文字コードを文字に変換する |
12 | FORMAT | ○ | 指定された小数点以下桁数に書式設定された数値を返します |
※ 項目「名前」に括弧書で記載されているのは関数の別名(同機能)です。
※ 項目の「一般性」は、① 標準 SQL 規格にあるか、② 各 DBMS で共通に用意されているかなどを基準に、独自の判断をしたものです。
よく使用される文字列関数の実行
以下、よく使用する文字列関数について、見ていきます。
① LOWER,UPPER 関数(小文字・大文字への変換)
LOWER 関数は文字列を小文字に変換し、UPPER 関数は文字列を大文字に変換します。
次のようにコマンドを打って確認してみましょう。
PowerShell
mysql> SELECT LOWER('PowerShell'), UPPER('PowerShell');
+---------------------+---------------------+
| LOWER('PowerShell') | UPPER('PowerShell') |
+---------------------+---------------------+
| powershell | POWERSHELL |
+---------------------+---------------------+
1 row in set (0.00 sec)
② CONCAT 関数(文字列の連結)
CONCAT 関数は複数の文字列を結合する関数です。
次のようにコマンドを打って確認してみましょう。
PowerShell
mysql> SELECT CONCAT('Power', ' ', 'Shell');
+-------------------------------+
| CONCAT('Power', ' ', 'Shell') |
+-------------------------------+
| Power Shell |
+-------------------------------+
1 row in set (0.00 sec)
3 つの文字列が結合できたことが確認できました。
③ TRIM,LTRIM,RTRIM 関数(先頭・末尾の空白削除)
各関数の指定方法は、次のとおりです。
TRIM(str)
で文字列の先頭および末尾の空白を削除します。LTRIM(str)
で文字列の先頭の空白を削除します。RTRIM(str)
で文字列の末尾の空白を削除します。
単に実行するだけであれば、次の SQL で十分ですが、このままでは右端の空白が削除できたかを画面から確認することができません。
PowerShell
mysql> SELECT TRIM(' both '), LTRIM(' left '), RTRIM(' right ');
ここでは、以下のように CONCAT 関数を使って左右に "
を連結した上で、確認をしてみましょう。
PowerShell
mysql> SELECT
CONCAT('"', TRIM(' both '), '"') AS TRIM,
CONCAT('"', LTRIM(' left '), '"') AS LTRIM,
CONCAT('"', RTRIM(' right '), '"') AS RTRIM;
+--------+----------+-----------+
| TRIM | LTRIM | RTRIM |
+--------+----------+-----------+
| "both" | "left " | " right" |
+--------+----------+-----------+
1 row in set (0.00 sec)
それぞれ、先頭または末尾の空白が、指定通りに削除できたことが確認できました。
④ TRIM 関数で空白以外の文字を削除する
TRIM 関数は、空白以外の任意の文字を削除することができます。
構文は次の形式となります。
TRIM({ BOTH | LEADING | TRAILING } [削除する文字] FROM [文字列])
次のように、BOTH、LEADING、TRAILING の指定で削除対象部分を指定することができます(デフォルトは BOTH です)。
TRIM(BOTH 'x' FROM 'xxx文字列xxx')
:先頭および末尾の指定文字を削除TRIM(LEADING 'x' FROM 'xxx文字列xxx')
:先頭の指定文字を削除TRIM(TRAILING 'x' FROM 'xxx文字列xxx')
:末尾の指定文字を削除
PowerShell
mysql> SELECT
TRIM(BOTH 'x' FROM 'xxx文字列xxx') AS BOTH_TRIM,
TRIM(LEADING 'x' FROM 'xxx文字列xxx') AS LEADING_TRIM,
TRIM(TRAILING 'x' FROM 'xxx文字列xxx') AS TRAILING_TRIM;
+-----------+--------------+---------------+
| BOTH_TRIM | LEADING_TRIM | TRAILING_TRIM |
+-----------+--------------+---------------+
| 文字列 | 文字列xxx | xxx文字列 |
+-----------+--------------+---------------+
1 row in set (0.00 sec)
正しく文字を削除できたことが確認できました。
⑤ CHAR_LENGTH,LENGTH 関数(文字列の長さを取得)
CHAR_LENGTH
関数は「文字列の文字数」を取得します。LENGTH
関数は「文字列のバイト数」を取得します。
PowerShell
mysql> SELECT CHAR_LENGTH('SQL関数'), LENGTH('SQL関数');
+------------------------+-------------------+
| CHAR_LENGTH('SQL関数') | LENGTH('SQL関数') |
+------------------------+-------------------+
| 5 | 7 |
+------------------------+-------------------+
1 row in set (0.02 sec)
CHAR_LENGTH 関数では文字数 5 を取得できています。
LENGTH 関数ではバイト数として 7 を取得しています(下記参照)。
文字列のバイト数
環境によって異なりますが、Windows 環境では、デフォルトの日本語文字セットとして SHIFT_JIS が適用されます。
そのため、アルファベットは 1 バイト、日本語は 2 バイト(半角カナは 1 バイト)でカウントされます(つまり、'SQL' は 3 バイト、'関数' は 4 バイトで計算されます)。
⑥ SUBSTRING 関数(文字列の部分取得)
SUBSTRING 関数の指定方法は、次のとおりです。
SUBSTRING(str, pos)
:str につき位置 pos から末尾までの文字列を取得する。SUBSTRING(str, pos, len)
:位置 pos から len の長さの文字列を取得する。
PowerShell
mysql> SELECT SUBSTRING('PowerShell', 6), SUBSTRING('PowerShell', 2, 4);
+----------------------------+-------------------------------+
| SUBSTRING('PowerShell', 6) | SUBSTRING('PowerShell', 2, 4) |
+----------------------------+-------------------------------+
| Shell | ower |
+----------------------------+-------------------------------+
1 row in set (0.00 sec)
⑦ LEFT,RIGHT 関数(文字列の部分取得)
LEFT 関数,RIGHT 関数の指定方法は、次のとおりです。
LEFT(str, len)
:str につき左端から len の長さの文字列を取得する。RIGHT(str, len)
:str につき右端から len の長さの文字列を取得する。
PowerShell
mysql> SELECT LEFT('PowerShell', 3), RIGHT('PowerShell', 6);
+-----------------------+------------------------+
| LEFT('PowerShell', 3) | RIGHT('PowerShell', 6) |
+-----------------------+------------------------+
| Pow | rShell |
+-----------------------+------------------------+
1 row in set (0.00 sec)
⑧ REPLACE 関数(文字列の置換)
REPLACE 関数は文字列を置換する関数です。
構文は REPLACE(str, from_str, to_str)
で、str に対象文字列、from_str に置換する文字列、to_str に置換後の文字列を指定します。
次のように実行してみましょう。
PowerShell
mysql> SELECT REPLACE('データベースの学習をする', '学習', '環境構築');
+---------------------------------------------------------+
| REPLACE('データベースの学習をする', '学習', '環境構築') |
+---------------------------------------------------------+
| データベースの環境構築をする |
+---------------------------------------------------------+
1 row in set (0.00 sec)
'学習' が '環境構築' に変換されたことが確認できました。
⑨ STRCMP 関数(文字列の比較)
STRCMP 関数は文字列を比較する関数です。
構文は STRCMP(expr1, expr2)
で、expr1 と expr2 に比較する文字を指定します。
戻り値は、① 文字列が一致するかどうか、および、② 2 つの文字列のソート順により決まります。
戻り値 | 比較結果 | 説明 |
---|---|---|
0 | 文字列一致 | |
-1 | 文字列不一致 | ソート順につき expr1 の方が expr2 より小さい |
1 | 文字列不一致 | ソート順につき expr1 の方が expr2 より大きい |
文字列が一致するかどうかを確認する場合は「戻り値が 0 か否か」で判断すれば足ります。
次のようにコマンドを打って、確認をしてみましょう。
PowerShell
mysql> SELECT
STRCMP('あいう', 'あいう'),
STRCMP('かきく', 'カキク'),
STRCMP('abc', 'ABC');
+----------------------------+----------------------------+----------------------+
| STRCMP('あいう', 'あいう') | STRCMP('かきく', 'カキク') | STRCMP('abc', 'ABC') |
+----------------------------+----------------------------+----------------------+
| 0 | -1 | 0 |
+----------------------------+----------------------------+----------------------+
1 row in set (0.00 sec)
文字列が一致する場合に 0 が返却されることを確認できました。
なお、最後の 'abc'
と 'ABC'
は同一の文字列として認識されています。
つまり、STRCMP 関数では、アルファベットの大文字・小文字などは区別されないということです。
以上、よく使用する文字列関数について確認を行いました。
そのほかの文字列関数については、公式リファレンス に使用方法が書いてありますので、必要に応じて参照してみてください。
日付関数
日付関数は、日付や時刻の取得・操作を行うものです。
標準 SQL で定められている日付関数は少ないですが、DBMS ごとに多種多様な関数が用意されています。
ここでは、よく使用される一般的な関数を中心に見ていきましょう。
主な数値関数一覧
主に以下のような日付関数があります(詳しくは 公式リファレンス 参照)。
No | 名前 | 一般性 | 説明 |
---|---|---|---|
1 | CURDATE(CURRENT_DATE) | ◎ | 現在の日付を返す |
2 | CURTIME(CURRENT_TIME) | ◎ | 現在の時刻を返す |
3 | NOW(CURRENT_TIMESTAMP) | ◎ | 現在の日付時刻を返す |
4 | EXTRACT | ◎ | 日付の一部を抽出する |
5 | YEAR,MONTH, DAY(DAYOFMONTH) |
○ | 日付から、それぞれ年、月、日を返す |
6 | DATE | ○ | 日付時刻から日付部分を抽出する |
7 | ADDDATE(DATE_ADD), SUBDATE(DATE_SUB) |
○ | 日付に指定値を加算する、日付から指定値を減算する |
8 | ADDTIME | ○ | 日付または時刻に指定時間を加算する |
9 | DATEDIFF | ○ | 2 つの日付の差を返す |
10 | WEEKDAY | ○ |
曜日インデックスを返す (0 ~ 6 : 月 ~ 日) |
11 | DAYOFWEEK | ○ |
曜日インデックスを返す(ODBC 標準) (1 ~ 7 : 日 ~ 土) |
12 | DAYOFYEAR | △ |
1 から 366 の範囲で通年で何日目かを返す (366 は うるう年で使用) |
13 | WEEKOFYEAR | △ | 1 から 53 の範囲で通年で何週目かを返す |
14 | UNIX_TIMESTAMP | ○ | UNIX タイムスタンプを返す |
15 | UTC_DATE,UTC_TIME,UTC_TIMESTAMP | ○ | 現在の UTC 日付、現在の UTC 時刻、現在の UTC 日付時刻を返す |
16 | DATE_FORMAT | ○ | 日付を指定した書式に変換する |
17 | STR_TO_DATE | ○ | 文字列を日付に変換する |
※ 項目「名前」に括弧書で記載されているのは関数の別名(同機能)です。
※ 項目の「一般性」は、各 DBMS で共通に用意されているかなどを基準に、独自の判断をしたものです。
よく使用される日付関数の実行
以下、よく使用する日付関数について、見ていきます。
① CURDATE(CURRENT_DATE) 関数(現在日付の取得)
CURDATE() 関数は現在の日付を取得します。
MySQL では CURDATE()
が正式で CURRENT_DATE
は別名ですが、標準 SQL に準拠するのは CURRENT_DATE の方です。
次のように SQL で確認してみましょう。
PowerShell
mysql> SELECT CURDATE(), CURRENT_DATE;
+------------+--------------+
| CURDATE() | CURRENT_DATE |
+------------+--------------+
| 2022-11-30 | 2022-11-30 |
+------------+--------------+
1 row in set (0.00 sec)
CURRENT_DATE は括弧なしで実行できます(標準 SQL では括弧を付けないことが条件となっているためです)。
② CURTIME(CURRENT_TIME) 関数(現在時刻の取得)
CURTIME() 関数は現在の時刻を取得します。
MySQL では CURTIME()
が正式で CURRENT_TIME
は別名ですが、こちらも、標準 SQL に準拠するのは CURRENT_TIME の方です。
次のように SQL で確認してみましょう(CURRENT_TIME には括弧は不要です)。
PowerShell
mysql> SELECT CURTIME(), CURRENT_TIME;
+-----------+--------------+
| CURTIME() | CURRENT_TIME |
+-----------+--------------+
| 11:33:05 | 11:33:05 |
+-----------+--------------+
1 row in set (0.00 sec)
③ NOW(CURRENT_TIMESTAMP) 関数(現在日時の取得)
NOW() 関数は現在の日付を取得します。
上記の 2 つと同様に、MySQL では NOW()
が正式で CURRENT_TIMESTAMP
は別名ですが、標準 SQL に準拠するのは CURRENT_TIMESTAMP の方です。
次のように SQL で確認してみましょう(CURRENT_TIMESTAMP には括弧は不要です)。
PowerShell
mysql> SELECT NOW(), CURRENT_TIMESTAMP;
+---------------------+---------------------+
| NOW() | CURRENT_TIMESTAMP |
+---------------------+---------------------+
| 2022-11-30 11:35:10 | 2022-11-30 11:35:10 |
+---------------------+---------------------+
1 row in set (0.00 sec)
④ EXTRACT 関数(日付の一部抽出)
EXTRACT 関数は日付から指定した日時要素を取得します。これは、標準 SQL で指定された関数となります。
構文は次のとおりです。
EXTRACT(unit FROM date)
unit
には日時要素を、date
には日付を指定します。
例えば「年」を取得する場合は次のように指定します(日時要素は「YEAR」と指定します)。
EXTRACT(YEAR FROM '2022-11-30 11:35:10')
具体例を見た方が早いので、早速、次の SQL を実行してみましょう。
PowerShell
mysql> SELECT
EXTRACT(YEAR FROM '2022-11-30 11:35:10') AS YEAR,
EXTRACT(MONTH FROM '2022-11-30 11:35:10') AS MONTH,
EXTRACT(DAY FROM '2022-11-30 11:35:10') AS DAY,
EXTRACT(HOUR FROM '2022-11-30 11:35:10') AS HOUR,
EXTRACT(MINUTE FROM '2022-11-30 11:35:10') AS MINUTE,
EXTRACT(SECOND FROM '2022-11-30 11:35:10') AS SECOND;
+------+-------+------+------+--------+--------+
| YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
+------+-------+------+------+--------+--------+
| 2022 | 11 | 30 | 11 | 35 | 10 |
+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)
以上のように日付データから、YEAR(年)、MONTH(月)、DAY(日)、HOUR(時)、MINUTE(分)、SECOND(秒)を取得することができました。
そのほか日時要素として指定できるのは 公式リファレンス:時間間隔 で定義されたものになりますので、必要に応じて参照してみてください。
なお、MySQL の公式では日時要素のことを「時間間隔」と記載していますので、以降の解説では「時間間隔」と呼ぶようにします。
⑤ YEAR,MONTH,DAY 関数(日付の一部抽出)
見てのとおりですが、YEAR 関数は日付から「年」を、MONTH 関数は日付から「月」を、DAY 関数(別名:DAYOFMONTH 関数)は日付から「日」を取得します。
次の SQL を実行して、確認してみましょう。
PowerShell
mysql> SELECT YEAR('2022-11-30 11:35:10'), MONTH('2022-11-30 11:35:10'), DAY('2022-11-30 11:35:10');
+-----------------------------+------------------------------+----------------------------+
| YEAR('2022-11-30 11:35:10') | MONTH('2022-11-30 11:35:10') | DAY('2022-11-30 11:35:10') |
+-----------------------------+------------------------------+----------------------------+
| 2022 | 11 | 30 |
+-----------------------------+------------------------------+----------------------------+
1 row in set (0.00 sec)
「年」「月」「日」につき、EXTRACT 関数と同様の結果が取得できました。
⑥ ADDDATE,SUBDATE 関数(日付の加算・減算)
次は、日付を加算する ADDDATE 関数(DATE_ADD)、減算する SUBDATE 関数(別名:DATE_SUB)について見ていきます。
(構文1)単純な構文で日にちの加算・減算を行う
単純な構文は次のとおりです。
ADDDATE(date, days)
:date
に日付、days
に加算する日数を指定する。SUBDATE(date, days)
:date
に日付、days
に減算する日数を指定する。
PowerShell
mysql> SELECT ADDDATE('2022-11-30', 8), SUBDATE('2022-11-30 11:35:10', 5);
+--------------------------+-----------------------------------+
| ADDDATE('2022-11-30', 8) | SUBDATE('2022-11-30 11:35:10', 5) |
+--------------------------+-----------------------------------+
| 2022-12-08 | 2022-11-25 11:35:10 |
+--------------------------+-----------------------------------+
1 row in set (0.00 sec)
以上のように、 時刻部分('11:35:10'
)の有無に関わらず、日付のみが変更されることが確認できました。
(構文2)日にち以外の時間間隔で加算・減算を行う
ADDDATE 関数、SUBDATE 関数では、日にち以外の加算減算もできます。
構文は次のとおりです。
ADDDATE(date, INTERVAL expr unit)
:date
に日付、expr
に加算する値、unit
に時間間隔を指定する。SUBDATE(date, INTERVAL expr unit)
:date
に日付、expr
に減算する値、unit
に時間間隔を指定する。
まずは、ADDDATE 関数について、いくつかの時間間隔を指定した SQL を実行してみます。
加算結果が分かりやすいように、基準日は '2023-01-01 00:00:00'
としています。
PowerShell
mysql> SELECT
ADDDATE('2023-01-01 00:00:00', INTERVAL 2 YEAR) AS ADD_2_YEAR,
ADDDATE('2023-01-01 00:00:00', INTERVAL 15 MONTH) AS ADD_15_MONTH,
ADDDATE('2023-01-01 00:00:00', INTERVAL 30 HOUR) AS ADD_30_HOUR,
ADDDATE('2023-01-01 00:00:00', INTERVAL 200 SECOND) AS ADD_200_SECOND;
+---------------------+---------------------+---------------------+---------------------+
| ADD_2_YEAR | ADD_15_MONTH | ADD_30_HOUR | ADD_200_SECOND |
+---------------------+---------------------+---------------------+---------------------+
| 2025-01-01 00:00:00 | 2024-04-01 00:00:00 | 2023-01-02 06:00:00 | 2023-01-01 00:03:20 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
年や日をまたぐ場合でも、正しく日時が加算されることが確認できました。
次に、SUBDATE 関数についても SQL を実行してみましょう。
PowerShell
mysql> SELECT
SUBDATE('2023-01-01 00:00:00', INTERVAL 3 YEAR) AS SUB_3_YEAR,
SUBDATE('2023-01-01 00:00:00', INTERVAL 100 DAY) AS SUB_100_DAY,
SUBDATE('2023-01-01 00:00:00', INTERVAL 600 MINUTE) AS SUB_600_MINUTE,
SUBDATE('2023-01-01 00:00:00', INTERVAL 1 SECOND) AS SUB_1_SECOND;
+---------------------+---------------------+---------------------+---------------------+
| SUB_3_YEAR | SUB_100_DAY | SUB_600_MINUTE | SUB_1_SECOND |
+---------------------+---------------------+---------------------+---------------------+
| 2020-01-01 00:00:00 | 2022-09-23 00:00:00 | 2022-12-31 14:00:00 | 2022-12-31 23:59:59 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
以上、正しく減算できることが確認できました。
⑦ WEEKDAY,DAYOFWEEK 関数(曜日インデックスの取得)
WEEKDAY 関数も、DAYOFWEEK 関数も曜日を取得するための関数です。
どちらも曜日に該当する数値を返しますが、その戻り値の定義が次のように異なります。
No | 曜日取得関数 | 戻り値の定義 |
---|---|---|
1 | WEEKDAY | 0=月,1=火,2=水,3=木,4=金,5=土,6=日 |
2 | DAYOFWEEK | 1=日,2=月,3=火,4=水,5=木,6=金,7=土 |
プログラミングにおけるインデックスは、0
から始めるのが一般的なので、WEEKDAY の方が使いやすいかもしれません。
一方、DAYOFWEEK の方は ODBC 標準(※)に合わせて定義されているため汎用性が高いといえます。
ODBC とは
ODBC とはアプリケーションからデータベースに接続する方式についての標準を定めたものです。
Microsoft が制定したもので、DBMS による違いを吸収してアクセスの統一化をすることを目的としています。
それでは、実際に SQL の実行をして確認をしてみましょう。
なお、ここで指定する '2023-01-01'
は日曜日です。
PowerShell
mysql> SELECT WEEKDAY('2023-01-01'), DAYOFWEEK('2023-01-01');
+-----------------------+-------------------------+
| WEEKDAY('2023-01-01') | DAYOFWEEK('2023-01-01') |
+-----------------------+-------------------------+
| 6 | 1 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)
WEEKDAY の戻り値は 6(日曜日)、DAYOFWEEK の戻り値は 1(日曜日)と、それぞれ正しい値が取得できました。
⑧ UNIX_TIMESTAMP 関数(UNIX タイムスタンプを返す)
UNIX_TIMESTAMP 関数は、UNIX タイムスタンプ(※)を返します。
UNIX タイムスタンプとは
UNIX タイムスタンプとは、1970年1月1日午前0時0分0秒
からの経過秒数を数値で表したものです。
次のように SQL で確認してみましょう。
PowerShell
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1669784075 |
+------------------+
1 row in set (0.00 sec)
⑨ UTC_DATE,UTC_TIME,UTC_TIMESTAMP 関数(UTC 日付・時刻を返す)
UTC_DATE 関数は UTC 日付を、UTC_TIME 関数は UTC 時刻を、UTC_TIMESTAMP 関数は UTC 日付・時刻を返します。
UTC 時刻とは
UTC 時刻とは「協定世界時」の略称であり、世界の標準時刻として用いられています。
日本時間からちょうど 9 時間引いた時間になります。
SQL を実行して確認してみましょう。
PowerShell
mysql> SELECT UTC_DATE, UTC_TIME, UTC_TIMESTAMP, NOW();
+------------+----------+---------------------+---------------------+
| UTC_DATE | UTC_TIME | UTC_TIMESTAMP | NOW() |
+------------+----------+---------------------+---------------------+
| 2022-11-30 | 05:08:15 | 2022-11-30 05:08:15 | 2022-11-30 14:08:15 |
+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)
最後の NOW()
はローカル時間(ここでは日本時刻)です。
9 時間の時差があることが確認できます。
⑩ DATE_FORMAT,STR_TO_DATE 関数(日付と文字列の変換)
DATE_FORMAT 関数は日付を文字列に変換し、STR_TO_DATE 関数は文字列を日付に変換します。
DATE_FORMAT 関数
DATE_FORMAT 関数の構文は次のとおりです。
DATE_FORMAT(date, format)
:date
に日付をformat
に書式を指定します。
書式は、'%Y年%m月%d日 %H時%i分%s秒'
のように指定します。
まず、一度 SQL を実行してみましょう。
PowerShell
mysql> SELECT DATE_FORMAT('2022-11-30 14:23:51', '%Y年%m月%d日 %H時%i分%s秒');
+-----------------------------------------------------------------+
| DATE_FORMAT('2022-11-30 14:23:51', '%Y年%m月%d日 %H時%i分%s秒') |
+-----------------------------------------------------------------+
| 2022年11月30日 14時23分51秒 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
書式に使用する %Y
などの語句は「書式指定子」と言われ、主に次のようなものがあります。
No | 指定子 | 説明 |
---|---|---|
1 | %Y | 年を 4 桁で表示する(%y は 2 桁) |
2 | %m | 月を 2 桁で表示する |
3 | %d | 日を 2 桁で表示する |
4 | %H | 時間を 00 ~ 23 の範囲で表示する(%h は 00 ~ 12 の範囲) |
5 | %i | 分を 2 桁で表示する |
6 | %s | 秒を表示する |
7 | %f | マイクロ秒を 6 桁で表示する |
その他にも様々な書式指定子があります。詳しくは 公式リファレンス を参照してください。
STR_TO_DATE 関数
STR_TO_DATE 関数は、先ほどの DATE_FORMAT 関数とは反対に、文字列を日付に変換します。
構文は次のとおりです。
STR_TO_DATE(str, format)
:str
に文字列をformat
に文字列の書式を指定します。
書式は、'%Y年%m月%d日 %H時%i分%s秒'
のように指定します。使用する指定子は先ほどの DATE_FORMAT 関数と同じです。
次のような SQL を実行して、文字列から日付に変換してみましょう。
PowerShell
mysql> SELECT STR_TO_DATE('2022年11月30日 14時23分51秒', '%Y年%m月%d日 %H時%i分%s秒');
+-------------------------------------------------------------------------+
| STR_TO_DATE('2022年11月30日 14時23分51秒', '%Y年%m月%d日 %H時%i分%s秒') |
+-------------------------------------------------------------------------+
| 2022-11-30 14:23:51 |
+-------------------------------------------------------------------------+
1 row in set (0.02 sec)
文字列に合わせて書式を設定することで、様々な形式の文字列を日付に変換することができます。
集合関数
集合関数(集計関数)を使用することで、指定したカラムに対して「合計値」や「平均値」を求めるなど様々な集計を実行することができます。
主な集合関数一覧
MySQL には、数多くの集合関数が用意されていますが、ここでは標準 SQL に定められているものについて一覧としました。
その他の集合関数については 公式リファレンス:集計関数 を参照してください。
No | 名前 | 一般性 | 説明 |
---|---|---|---|
1 | COUNT | ◎ | 引数に指定したカラムの値が存在するレコード数を取得する |
2 | MAX | ◎ | 引数に指定したカラムの最大値を取得 |
3 | MIN | ◎ | 引数に指定したカラムの最小値を取得 |
4 | SUM | ◎ | 引数に指定したカラムの合計値を取得 |
5 | AVG | ◎ | 引数に指定したカラムの平均値を取得 |
※ 項目の「一般性」は、標準 SQL 規格にあることを基準に、独自の判断をしたものです。
この集合関数の使い方については、次の Lesson 9 で改めて説明しますので、ここでは割愛します。
変換関数
変換関数を使用することで、変数などのデータ型を変換(キャスト)したり、文字コードの変換などをすることができます。
変換関数の一覧
MySQL には、次のような変換関数が用意されています(公式リファレンス 参照)。
No | 名前 | 一般性 | 説明 |
---|---|---|---|
1 | CAST | ◎ | 値を特定の型としてキャスト(型変換)する |
2 | CONVERT | ◎ | 値を特定の型としてキャスト(型変換)する |
3 | BINARY | ○ | 式をバイナリ文字列にキャスト(型変換)する |
※ 項目の「一般性」は、各 DBMS で共通に用意されているかなどを基準に、独自の判断をしたものです。
変換関数の実行
以下、変換関数について、見ていきます。
① CAST 関数(値をキャストする)
CAST 関数は、値を指定した型にキャストします。
構文は次のとおりです。
CAST(expr AS type)
:expr
に変換する値を、type
に変換するデータ型を指定します。
type
に指定できるデータ型は、次のとおりです(詳細は 公式リファレンス 参照)。
使えるものを羅列しただけですので、全てを理解したり覚えたりする必要はありません。
No | データ型 | 説明 |
---|---|---|
1 | SIGNED | 符号付き整数値を生成。 |
2 | UNSIGNED | 符号なし整数値を生成。 |
3 | DECIMAL[(M[,D])] | DECIMAL 値を生成。M に桁数、D に小数点桁数を指定(オプション)。 |
4 | DOUBLE | DOUBLE 型の数値を生成。 |
5 | FLOAT[(p)] | FLOAT 型の数値を生成。p に精度(ビット数)を指定(オプション)。 |
6 | REAL | 実数を生成(FLOAT または DOUBLE) |
7 | CHAR[(N)] | CHAR 型の文字列を生成。引数 N で文字数を指定(オプション) |
8 | DATE | DATE 値を生成。 |
9 | DATETIME | DATETIME 値を生成。 |
10 | TIME | TIME 値を生成。 |
11 | YEAR | YEAR 値を生成。 |
12 | NCHAR[(N)] | 各国語文字セットの文字列を生成。 |
13 | BINARY[(N)] | BINARY 型の文字列を生成。引数 N でバイト数を指定(オプション)。 |
まず、数値型のキャストについて、次のように SQL で確認してみましょう。
PowerShell
mysql> SELECT CAST('12345678' AS SIGNED), CAST(123.45678 AS DECIMAL(5, 2));
+----------------------------+----------------------------------+
| CAST('12345678' AS SIGNED) | CAST(123.45678 AS DECIMAL(5, 2)) |
+----------------------------+----------------------------------+
| 12345678 | 123.46 |
+----------------------------+----------------------------------+
1 row in set (0.00 sec)
DECIMAL はオプションを使用して、全体で 5 桁、小数点部分を 2 桁で指定しています。
次に、日付型のキャストについても SQL で確認してみましょう。
PowerShell
mysql> SELECT CAST('2022-11-30 14:23:51' AS DATE), CAST('2022-11-30 14:23:51' AS TIME);
+-------------------------------------+-------------------------------------+
| CAST('2022-11-30 14:23:51' AS DATE) | CAST('2022-11-30 14:23:51' AS TIME) |
+-------------------------------------+-------------------------------------+
| 2022-11-30 | 14:23:51 |
+-------------------------------------+-------------------------------------+
1 row in set (0.00 sec)
それぞれ、日付部分(DATE)、時刻部分(TIME)が取得できました。
② CONVERT 関数(値をキャストする)
データ型の変換を行う
CONVERT 関数は、CAST 関数と同じようにデータ型の変換をすることができます。
構文は次のとおりです。
-
CONVERT(expr, type)
expr
に変換する値を、type
に変換するデータ型を指定します。
type
に指定できるデータ型は、CAST 関数と同じです。
数値型のキャストについて、次のように SQL で確認してみましょう。
PowerShell
mysql> SELECT CONVERT('12345678', SIGNED), CONVERT(123.45678, DECIMAL(5, 2));
+-----------------------------+-----------------------------------+
| CONVERT('12345678', SIGNED) | CONVERT(123.45678, DECIMAL(5, 2)) |
+-----------------------------+-----------------------------------+
| 12345678 | 123.46 |
+-----------------------------+-----------------------------------+
1 row in set (0.00 sec)
比較として、CAST 関数と同じ内容を実行しました。結果も同じことが確認できます。
文字コードの変換を行う
CONVERT 関数は文字コードの変換を行うこともできます。
構文は次のとおりです。
-
CONVERT(expr USING transcoding_name)
expr
に文字列を、transcoding_name
に変換する文字コードを指定します。
挙動を確認するため、次のように SQL を実行してみてください。
まず、SET 関数を使用して @str
という変数に、文字列 'あいう'
を代入します。
次に、CHARSET 関数を使用して、文字列の文字コードを確認します。
PowerShell
mysql> SET @str = 'あいう';
mysql> SELECT CHARSET(@str);
+---------------+
| CHARSET(@str) |
+---------------+
| cp932 |
+---------------+
文字コードとして cp932
という値が表示されています。
これは、SHIFT_JIS
という文字コードに該当します。
では、文字コードを変換してみます。
変換する文字コードは、MySQL でよく使用される utf8mb4
とします。
次のように、CONVERT 関数を使用して変換してみましょう。
PowerShell
mysql> SET @str = CONVERT(@str USING utf8mb4);
mysql> SELECT CHARSET(@str);
+---------------+
| CHARSET(@str) |
+---------------+
| utf8mb4 |
+---------------+
文字コードが変換されていることが確認できました。
③ BINARY 関数(式をバイナリ文字列にキャスト)
最後に BINARY 関数についてです。
バイナリとは
バイナリとは、0
と 1
で表現されるデータ形式のことです。
例えば、'Z'
という文字は、コンピュータ内では 1011010
という 2 進数で格納されます。
この 1011010
という形式のデータを「バイナリ」と言います。
なお、2 進数のままでは扱いづらいため 16 進数で表記することが通例です('Z'
を 16 進数に置き換えると 5A
となります)。
BINARY 関数の構文は BINARY expr
であり、expr
のところに「式」が入ります。
想像がつきにくいところかもしれませんが、例えば、文字列をバイナリ値で比較する場合などに活用することができます。
次のとおりに SQL を実行してみてください。
この SQL は、'a' = 'A'
という文字列比較と BINARY 'a' = 'A'
という文字列比較を行うものです。
PowerShell
mysql> SELECT 'a' = 'A', BINARY 'a' = 'A';
+-----------+------------------+
| 'a' = 'A' | BINARY 'a' = 'A' |
+-----------+------------------+
| 1 | 0 |
+-----------+------------------+
1 row in set (0.00 sec)
前者の 'a' = 'A'
はテキスト比較をしており結果は 1
(つまり true)となっています。
一方、後者の BINARY 'a' = 'A'
はバイナリ値比較をしており結果は 0
(つまり false)となっています。
具体的には、'a' のバイナリ値 '1100001'
と 'A' のバイナリ値 '1000001'
を比較して、値が異なることから false と判定しています。
このように、BINARY 関数を使用すると、文字列の厳密な比較を行う場合などに活用することができます。
MySQL でバイナリ値を表示する
MySQL でバイナリ値を表示するには、HEX 関数を使用します。
HEX 関数は引数に指定した文字列を、16 進数の文字コードで表示します。
PowerShell
mysql> SELECT HEX('A');
+----------+
| HEX('A') |
+----------+
| 41 |
+----------+
1 row in set (0.00 sec)
2 進数のバイナリ値で表示するには、数値関数で紹介した CONV 関数を使用します。
PowerShell
mysql> SELECT CONV(HEX('A'), 16, 2);
+-----------------------+
| CONV(HEX('A'), 16, 2) |
+-----------------------+
| 1000001 |
+-----------------------+
1 row in set (0.00 sec)
以上、MySQL の関数について、主要なものを一通り見てきました。
すぐに全てを使いこなすことは難しいと思いますが、必要に応じて調べたり復習したりして、少しずつ自分のものとしていただければと思います。
