ざっくりSQL入門 #9：よく使う関数

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。

今回は、（個人的に）よく使う関数を記載しておきます。
ただし集計関数についてはこちら。

全般/型変換

【COALESCE】もしNULLなら別の値を使う

COALESCE(`age`, '不明') -- ageの値がNULLの場合に「不明」という文字列を返す

【CAST】型変換する

CAST('0500' AS SIGNED) -- 文字列の「0500」を数値の「500」に変換する
-- UNSIGNED, CHAR, DATE, TIME, DATETIME, DECIMAL, BINARY が指定可能

数値

【ROUND】指定値を指定桁で四捨五入する

ROUND(55.55,  1) -- 55.6
ROUND(55.55,  0) -- 56
ROUND(55.55, -1) -- 60

【TRUNCATE】指定値の指定桁以降を切り捨てる

TRUNCATE(555.555,  1) -- 555.5
TRUNCATE(555.555,  0) -- 555
TRUNCATE(555.555, -1) -- 550

【ABS】絶対値を求める

ABS(-5) -- 5

【MOD】除算の余りを求める

MOD(10, 4) -- 2（10÷4の余り）

【CEIL】指定値以上で最小の整数値（正の数なら切り上げ、負の数なら切り捨てになることに注意）

CEIL( 5.5) --  6
CEIL(-5.5) -- -5

【FLOOR】指定値以下で最大の整数値（正の数なら切り捨て、負の数なら切り上げになることに注意）

FLOOR( 5.5) --  5
FLOOR(-5.5) -- -6

文字列操作系

文字列をつなぐ（phpの「.」のような用途）

CONCAT('今は', 5 , '月です') -- 「今は5月です」

文字列を切り出す

SUBSTRING('query', 1, 3) -- 「que」

指定した文字で指定桁数まで埋める

LPAD(5, 2, '0') -- 5を2桁になるまで0で埋めるので「05」

日付・時刻系

現在日時

NOW()     -- 現在の日付と時刻(yyyy-mm-dd hh:mm:dd の形)
CURDATE() -- 現在の日付      (yyyy-mm-dd の形)

日時の整形など

YEAR(  '2018-01-02 03:04:05' ) -- 年（2018）
MONTH( '2018-01-02 03:04:05' ) -- 月（1）
DAY(   '2018-01-02 03:04:05' ) -- 日（2）
HOUR(  '2018-01-02 03:04:05' ) -- 時（3）
MINUTE('2018-01-02 03:04:05' ) -- 分（4）
SECOND('2018-01-02 03:04:05' ) -- 秒（5）
DATE_FORMAT('2018-01-02 03:04:05', '%Y%m%d') -- 「20180102」

その他、DATE_FORMATのフォーマット部分に指定できるものは 下記公式マニュアル参照のこと
（日時に加え、曜日や週番号なども取得可能）
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

日時の計算

ADDDATE('2018-05-10', INTERVAL +1 MONTH) -- 「2018-06-10」
ADDDATE('2018-05-10', INTERVAL -1 DAY)   -- 「2018-05-09」

DATEDIFF('2018-05-10', '2018-05-11') -- -1（5月10日から5月11日を引く）
DATEDIFF('2018-05-11', '2018-05-10') --  1（5月11日から5月10日を引く）

条件分岐・判定

IF(1 < 2, 'T', 'F') -- 「T」（条件がtrueであれば2つ目、falseであれば3つ目の引数を返す）

CASE
  WHEN 1 > 2 THEN 'value1'
  WHEN 3 < 4 THEN 'value2'
  ELSE            'value3'
END -- 「value2」（WHEN以下の条件に合えばTHEN以下を返す、ただしいずれも当てはまらなければELSE以下を返す）