アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < 転職した

ざっくり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以下を返す)