SQLの演算子と関数

SQLには様々な演算子と関数が用意されている。
ここでは、それらについていくつか残しておく。


# 基本的な演算子

代表的な演算子や使い方をを下記の表にまとめる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/1a91f36e-5e06-e68f-a6a0-81134a45635b.png)




# CASE演算子

CASE演算子は列の値や条件式を評価して、値を変換することができる。

使い方は主に2通りある。

### 使い方その1

1つ目はある列や式が特定の値の時に値を変換する使い方である。
構文は以下のとおり
```
CASE 評価する列や式 WHEN 値1 THEN 値1の時に返す値
WHEN 値2 THEN 値2のときに返す値
ELSE デフォルト値
END
```

CASEの次に　評価する列や式を記述し、WHENの次に条件となる値を記述する。
WHENの次に記述した値だった場合、THEN の次に記述した値に変換される。

どのケースにも当てはまらない場合はデフォルト値となる。

具体例は以下のとおり

家計簿テーブル

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/f3aebe9e-3bc9-362a-acde-466bb5938c85.png)


``` SQL
SELECT
    expense_item
    , withdrawal_amount
    , CASE expense_item 
        WHEN '娯楽費' THEN '変動費' 
        WHEN '食費' THEN '変動費' 
        WHEN '水道光熱費' THEN '固定費' 
        END AS 出費分類 
FROM
    household_account_book;

```


出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/3d41283e-b39c-b1a7-acac-616ac9f56136.png)




### 使い方その2

次はWHENの部分に条件式を記述する使い方である。

構文は以下のとおり
```
CASE 評価する列や式 WHEN 値1 THEN 値1の時に返す値
WHEN 値2 THEN 値2のときに返す値
ELSE デフォルト値
END
```
書き方は使い方その1と変わらない

具体例は以下のとおり
``` SQL
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;
```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/ecc8f45b-ae61-b6f2-ccc2-ce4267c32432.png)



出費額の金額に応じて出力する文字列が変わっている。

式の結果に応じて複数のパターンを得たい場合CASE演算子は有効である。


# 関数まとめ

## LENGTH

引数に入れた列や式の文字数をを戻り値としてして返却する。

``` SQL
SELECT
    memo
    , LENGTH(memo) 
FROM
    household_account_book;

```
出力結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/e85d7e3b-849f-8792-8aa7-9baa0644d10d.png)



## TRIM,LTRIM,RTRIM

いずれも文字列に含まれる空白を取り除く時に使用できる。

各関数の違いは以下のとおり

| 関数名 | 取得文字列 |
|:-:|:-:|
|  TRIM(文字列を表す列) | 左右の空白を取り除いた文字列を取得する  |
| LTRIM (文字列を表す列)  |  左側の空白を取り除いた文字列を取得する  |
| RTRIM (文字列を表す列)  | 右側の空白を取り除いた文字列を取得する  |

 

・TRIMの例
``` SQL
SELECT
    memo
    , TRIM(memo) AS trimMemo 
FROM
    household_account_book 
WHERE
    memo = '   電車代   ';
```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/3b5160b1-b69e-965b-ad23-ffab830151e6.png)

電車代の左右の空白が取り除かれている。


・LTRIM

``` SQL
SELECT
    memo
    , LTRIM(memo) AS trimMemo 
FROM
    household_account_book 
WHERE
    memo = '   電車代   ';

```
出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/2cc19fe2-9a6a-5f0e-887a-7e53e7f73bda.png)

左側の空白だけ取り除かれている。


・RTRIM

``` SQL
SELECT
    memo
    , RTRIM(memo) AS trimMemo 
FROM
    household_account_book 
WHERE
    memo = '   電車代   ';

```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/a8353acf-be29-d7f9-663e-0fd67512a0ec.png)

右側の空白だけ取り除かれている。


## REPLACE
文字列の一部をを別の文字列に置換することができる。


書き方は以下のとおり
```
REPLACE(置換したい文字列,置換前の部分文字列, 置換後の部分文字列) → 置換処理された後の文字列
```
例↓
``` SQL
SELECT
    memo
    , REPLACE (memo, '電車', 'タクシー') AS replaceMemo 
FROM
    household_account_book 
WHERE
    memo = '電車代';
```

出力結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/1b69a0c1-1939-ee1d-1a59-976c2cb2dfa6.png)


## SUBSTRING,SUBSTR
文字列の一部だけ取り出したいよーって場合に使用する。

SUBSTRINGとSUBSTRどちらが使用できるかはDBMSによって異なる。

書き方は以下のとおり

```
SUBSTRING(文字列を表す列, 抽出を開始する位置, 抽出する文字の数)
```
例↓
``` SQL
SELECT
    memo
    , SUBSTRING(memo, 1, 2) AS substringMemo 
FROM
    household_account_book 
WHERE
    memo = '2月の水道光熱費';
```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/9fe35296-7e53-f982-3fde-462026f8a447.png)



## CONCAT

文字列を連結したい時に使用する。

演算子で連結することもできるが、こちらで行うこともできる。
連結できる文字列の数やNULLの扱いがDBMSによって異なるみたい。

書き方は以下のとおり
```
CONCAT(文字列, 文字列[, 文字列])
```
例↓
``` SQL
SELECT
    memo
    , CONCAT(expense_item, ':', memo) AS concatMemo 
FROM
    household_account_book 
WHERE
    expense_item = '食費';
```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/c6552f21-9439-6248-10c2-f9bfca729fc5.png)


## ROUND

指定した位置で四捨五入することができる関数。

書き方は以下のとおり

```
ROUND(数値を表す列, 有効とする桁数)
```
例↓

``` SQL
SELECT
    ROUND(1710, - 2) AS round1
    , ROUND(2260, - 2) AS round2
    , ROUND(1189, - 2) AS round3 
FROM
    household_account_book 
LIMIT
    1;
```
出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/8a5249f5-5447-765e-def6-2ab5eb69f45a.png)



## TRUNC

指定した桁数から切り捨てたい場合に使用する関数

```
TRUNC(数値を表す列, 有効とする桁数)
```
例↓
``` SQL
SELECT
    TRUNC(17112345, - 4) AS trunc1
    , TRUNC(29634444, - 4) AS trunc2
    , TRUNC(11891111, - 4) AS trunc3 
FROM
    household_account_book 
LIMIT
    1;

```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/bc3d310a-0780-8cad-9825-c7762b4ef859.png)




## POWER
べき乗を計算した結果を取得するときに使用する。

書き方は以下のとおり
```
POWER(数値を表す列, 何乗するかを指定する数字) 指定した回数乗じた結果
```
例↓
``` SQL
SELECT
    POWER(2, 4) AS power1
    , POWER(3, 3) AS power2
    , POWER(4, 2) AS power3 
FROM
    household_account_book 
LIMIT
    1;
```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/97fcfe98-1d50-1387-17e5-c272abe3af8f.png)


## CURREUT_DATE, CURRENT_TIME
現在の時刻を取得するために使用する。

取得する形式は以下のとおり
```
CURRENT_DATE→YYYY-MM-DD
CURRENT_TIME→HH:MM:SS
```
``` SQL
SELECT
    CURRENT_DATE
    , CURRENT_TIME 
FROM
    household_account_book 
LIMIT
    1;
```


## CAST
あるデータ型を別のデータ型に変換するときに使用する。

書き方は以下のとおり
```
CAST(変換する値 AS 変換する型)
```

例として、家計簿テーブルの出金額のデータ型を変換して値に「円」という文字列を付け足した文字列を出力するとする。

``` SQL
SELECT
    expense_item
    , withdrawal_amount
    , CAST(withdrawal_amount AS character (20)) || '円' AS 変換後の値 
FROM
    household_account_book;

```


出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/0ff5bb28-65e3-9c0a-f6de-460a405565f0.png)




## COALESCE

複数の引数を受け取り、受け取った引数を左から順番にチェックし、最初に見つかったNULLでない値を返す関数。

構文は以下のとおり
```
COALESCE(列や値, 列や値, 列や値…)
```
例↓

``` SQL
SELECT
    COALESCE('A', 'B', 'C')
    , COALESCE(null, 'B', 'C')
    , COALESCE(null, null, 'C')
    , COALESCE(null, 'B', null) 
FROM
    household_account_book 
LIMIT
    1;
```

出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/87ca1e6b-0bbc-3daf-eef9-ae20fb242211.png)


いまいち使い所がわかりにくいが、例えば、NULLが入る可能性がある列を取得する時にNULLの値の代わりをセットして結果を表示なんてことができる。



例として、家計簿テーブルのデータが以下の通りだったとして、メモがnullになっているレコードには代わりの値を表示させたいとする。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/d3e8deee-c665-1813-cc61-375f557ad9f5.png)


下記SQLを実行する

``` SQL
SELECT
    day
    , expense_item
    , COALESCE(memo, 'メモがありません') AS memo
    , deposit_amount
    , withdrawal_amount 
FROM
    household_account_book;
```



出力結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935552/4ecd3774-9bb2-d590-7205-202a214f042c.png)


nullだった部分が「メモがありません」という文字列になっているの分かる。