SQLには様々な演算子と関数が用意されている。
ここでは、それらについていくつか残しておく。
基本的な演算子
代表的な演算子や使い方をを下記の表にまとめる。
CASE演算子
CASE演算子は列の値や条件式を評価して、値を変換することができる。
使い方は主に2通りある。
使い方その1
1つ目はある列や式が特定の値の時に値を変換する使い方である。
構文は以下のとおり
CASE 評価する列や式 WHEN 値1 THEN 値1の時に返す値
WHEN 値2 THEN 値2のときに返す値
ELSE デフォルト値
END
CASEの次に 評価する列や式を記述し、WHENの次に条件となる値を記述する。
WHENの次に記述した値だった場合、THEN の次に記述した値に変換される。
どのケースにも当てはまらない場合はデフォルト値となる。
具体例は以下のとおり
家計簿テーブル
SELECT
expense_item
, withdrawal_amount
, CASE expense_item
WHEN '娯楽費' THEN '変動費'
WHEN '食費' THEN '変動費'
WHEN '水道光熱費' THEN '固定費'
END AS 出費分類
FROM
household_account_book;
出力結果
使い方その2
次はWHENの部分に条件式を記述する使い方である。
構文は以下のとおり
CASE 評価する列や式 WHEN 値1 THEN 値1の時に返す値
WHEN 値2 THEN 値2のときに返す値
ELSE デフォルト値
END
書き方は使い方その1と変わらない
具体例は以下のとおり
SELECT
expense_item
, withdrawal_amount
, CASE
WHEN withdrawal_amount < 10000
THEN '適切な出費'
WHEN withdrawal_amount > 10000
THEN '気を付けよう'
WHEN withdrawal_amount > 100000
THEN '使いすぎ！！'
END AS 出費量
FROM
household_account_book;
出力結果
出費額の金額に応じて出力する文字列が変わっている。
式の結果に応じて複数のパターンを得たい場合CASE演算子は有効である。
関数まとめ
LENGTH
引数に入れた列や式の文字数をを戻り値としてして返却する。
SELECT
memo
, LENGTH(memo)
FROM
household_account_book;
出力結果
TRIM,LTRIM,RTRIM
いずれも文字列に含まれる空白を取り除く時に使用できる。
各関数の違いは以下のとおり
| 関数名 | 取得文字列 |
|---|---|
| TRIM(文字列を表す列) | 左右の空白を取り除いた文字列を取得する |
| LTRIM (文字列を表す列) | 左側の空白を取り除いた文字列を取得する |
| RTRIM (文字列を表す列) | 右側の空白を取り除いた文字列を取得する |
・TRIMの例
SELECT
memo
, TRIM(memo) AS trimMemo
FROM
household_account_book
WHERE
memo = ' 電車代 ';
出力結果
電車代の左右の空白が取り除かれている。
・LTRIM
SELECT
memo
, LTRIM(memo) AS trimMemo
FROM
household_account_book
WHERE
memo = ' 電車代 ';
出力結果
左側の空白だけ取り除かれている。
・RTRIM
SELECT
memo
, RTRIM(memo) AS trimMemo
FROM
household_account_book
WHERE
memo = ' 電車代 ';
出力結果
右側の空白だけ取り除かれている。
REPLACE
文字列の一部をを別の文字列に置換することができる。
書き方は以下のとおり
REPLACE(置換したい文字列,置換前の部分文字列, 置換後の部分文字列) → 置換処理された後の文字列
例↓
SELECT
memo
, REPLACE (memo, '電車', 'タクシー') AS replaceMemo
FROM
household_account_book
WHERE
memo = '電車代';
出力結果
SUBSTRING,SUBSTR
文字列の一部だけ取り出したいよーって場合に使用する。
SUBSTRINGとSUBSTRどちらが使用できるかはDBMSによって異なる。
書き方は以下のとおり
SUBSTRING(文字列を表す列, 抽出を開始する位置, 抽出する文字の数)
例↓
SELECT
memo
, SUBSTRING(memo, 1, 2) AS substringMemo
FROM
household_account_book
WHERE
memo = '2月の水道光熱費';
出力結果
CONCAT
文字列を連結したい時に使用する。
演算子で連結することもできるが、こちらで行うこともできる。
連結できる文字列の数やNULLの扱いがDBMSによって異なるみたい。
書き方は以下のとおり
CONCAT(文字列, 文字列[, 文字列])
例↓
SELECT
memo
, CONCAT(expense_item, ':', memo) AS concatMemo
FROM
household_account_book
WHERE
expense_item = '食費';
出力結果
ROUND
指定した位置で四捨五入することができる関数。
書き方は以下のとおり
ROUND(数値を表す列, 有効とする桁数)
例↓
SELECT
ROUND(1710, - 2) AS round1
, ROUND(2260, - 2) AS round2
, ROUND(1189, - 2) AS round3
FROM
household_account_book
LIMIT
1;
出力結果
TRUNC
指定した桁数から切り捨てたい場合に使用する関数
TRUNC(数値を表す列, 有効とする桁数)
例↓
SELECT
TRUNC(17112345, - 4) AS trunc1
, TRUNC(29634444, - 4) AS trunc2
, TRUNC(11891111, - 4) AS trunc3
FROM
household_account_book
LIMIT
1;
出力結果
POWER
べき乗を計算した結果を取得するときに使用する。
書き方は以下のとおり
POWER(数値を表す列, 何乗するかを指定する数字) 指定した回数乗じた結果
例↓
SELECT
POWER(2, 4) AS power1
, POWER(3, 3) AS power2
, POWER(4, 2) AS power3
FROM
household_account_book
LIMIT
1;
出力結果
CURREUT_DATE, CURRENT_TIME
現在の時刻を取得するために使用する。
取得する形式は以下のとおり
CURRENT_DATE→YYYY-MM-DD
CURRENT_TIME→HH:MM:SS
SELECT
CURRENT_DATE
, CURRENT_TIME
FROM
household_account_book
LIMIT
1;
CAST
あるデータ型を別のデータ型に変換するときに使用する。
書き方は以下のとおり
CAST(変換する値 AS 変換する型)
例として、家計簿テーブルの出金額のデータ型を変換して値に「円」という文字列を付け足した文字列を出力するとする。
SELECT
expense_item
, withdrawal_amount
, CAST(withdrawal_amount AS character (20)) || '円' AS 変換後の値
FROM
household_account_book;
出力結果
COALESCE
複数の引数を受け取り、受け取った引数を左から順番にチェックし、最初に見つかったNULLでない値を返す関数。
構文は以下のとおり
COALESCE(列や値, 列や値, 列や値…)
例↓
SELECT
COALESCE('A', 'B', 'C')
, COALESCE(null, 'B', 'C')
, COALESCE(null, null, 'C')
, COALESCE(null, 'B', null)
FROM
household_account_book
LIMIT
1;
出力結果
いまいち使い所がわかりにくいが、例えば、NULLが入る可能性がある列を取得する時にNULLの値の代わりをセットして結果を表示なんてことができる。
例として、家計簿テーブルのデータが以下の通りだったとして、メモがnullになっているレコードには代わりの値を表示させたいとする。
下記SQLを実行する
SELECT
day
, expense_item
, COALESCE(memo, 'メモがありません') AS memo
, deposit_amount
, withdrawal_amount
FROM
household_account_book;
出力結果
nullだった部分が「メモがありません」という文字列になっているの分かる。