SQL関数と演算子

公開日：2019-12-01　最終更新日：2021-02-02

SQL関数と演算子

エクセルVBAでデータベースを扱うためのSQL入門です。
前2回でSELECTをやりましたが、その中で登場したCOUNTやSUMはSQL関数です。
今回は、SQL関数と演算子について、代表的なものについて使い方を解説します。

Excelのワークシートではワークシート関数、VBAにはVBA関数があるように、
SQLにはSQL関数が多数あります。
いずれの場合でも、関数を使わないとできることが限られてしまいます。
関数の理解度ができることの範囲を決めると言っても良いでしょう。

SQL関数と演算子は、DBによって違うものが多くあります。
特にSQL関数は、数も多くDBによる違いがとても多くなっていますので、
ここでは、使用頻度の高そうなものの中から、なるべく広く使えるものを中心に取り上げました。
それでも、他のDBでは使えないものも出てきますので、SQLite以外では使用するDBごとに確認してください。

集計/数値関数

関数名	構文	説明
COUNT	COUNT(式)	グループした時のグループ内の行数を返します。式に*（アスタリスク）を指定した場合はテーブルの行数を返します。式にカラム名を指定した場合は、NULL以外のセルの個数を返します。
SUM	SUM(数値式)	グループした時のグループ内の合計を返します。 NULLは無視されます。
AVG	AVG(数値式)	グループした時のグループ内の平均を返します。 NULLは無視されます。
MAX	MAX(数値式)	グループした時のグループ内の最大値を返します。 NULLは無視されます。
MIN	MIN(数値式)	グループした時のグループ内の最小値を返します。 NULLは無視されます。
ABS	ABS(数値式)	絶対値を返します。
ROUND	ROUND(数値式, 小数点桁数)	小数点の位置で四捨五入して返します。 0なら整数で、2なら小数単以下2桁で返します。 ※DBによっては四捨五入ではなく偶数丸め（銀行丸め）になります。

文字列関数

関数名	構文	説明
LENGTH	LENGTH(文字列式)	文字列の文字数を返します。バイト数ではなく、文字数です、漢字も1文字となります。 ※DBによってはバイト数を返します。
REPLACE	REPLACE(文字列式, 置換前, 置換後)	文字列の置換前を置換後に置き換えて返します。大文字・小文字は区別されます。
SUBSTR	SUBSTR(文字列式, 開始位置, 文字数)	文字列の開始位置から文字数分の文字列を返します。文字数を省略した場合は文字列の最後までが対象となります。 ※DBによっては関数名がSUBSTRINGになります。 ※DBによっては文字数ではなくバイト数指定になります。
TRIM	TRIM(文字列式, 削除文字)	文字列から削除文字を削除した文字列を返します。削除文字を省略した場合はスペース（半角全角）になります。
LTRIM	LTRIM(文字列式, 削除文字)	文字列の左から連続する削除文字を削除した文字列を返します。削除文字を省略した場合はスペース（半角全角）になります。
RTRIM	RTRIM(文字列式, 削除文字)	文字列の右から連続する削除文字を削除した文字列を返します。削除文字を省略した場合はスペース（半角全角）になります。

日付時刻関数

日付の扱いはDBごとに大きく違い、日付時刻のSQL関数も全くと言っていいほどそれぞれに違いがあります。
このSQL入門では、日付時刻に関しては、関数はなるべく使わずに文字列として扱う予定です。
以下は、SQLite独自の関数または挙動になります。

関数名

構文

説明

DATE

DATE(日付時刻)

日付時刻（文字列式）から'YYYY-MM-DD'で返します。

TIME

TIME(日付時刻)

日付時刻（文字列式）から'HH:MM:SS'で返します。

DATETIME

DATETIME(日付時刻)

日付時刻（文字列式）から'YYYY-MM-DD HH:MM:SS'で返します。

STRFTIME

STRFTIME(フォーマット,日付時刻)

日付時刻（文字列式）から指定フォーマットで返します。

%Y	年
%m	月
%d	日
%W	年初からの週数:
%j	年初からの経過日数:
%w	曜日
%H	時
%M	分
%S	秒
%f	秒＋ミリ秒
%s	1970/1/1からの経過秒数
%J	ユリウス日
%%	%文字そのもの

※大文字小文字に注意してください。

CURRENT_TIMESTAMP

現在日時をUTCで返します。

CURRENT_TIME

現在時刻をUTCで返します。

CURRENT_DATE

現在日付をUTCで返します。

SQLiteでは、日付時刻に'now'を定数指定することで現在日時を取得できます。

現在日時は全てUTCになります。
日本時間および現地時間にするには、以下のようにしてください。


  
    
      DATETIME(CURRENT_TIMESTAMP,'+9 hours')

      STRFTIME('%Y-%m-%d %H:%M','now','localtime')

DATE関数、TIME関数、DATETIME関数、STRFTIME関数には、
最後の引数の後ろに追加の引数としてModifiers（修飾子）が指定できます。

修飾子

N years	年月日時分秒を加減算します。 Nは±符号を付けた数値
N months
Ndays
N hours
N minutes
N seconds
start of year	日付の年の最初の日
start of month	日付の月の最初の日
start of day	日付の午前0時
weekday N	指定した日付の、Nで指定した曜日の最初の日 Nは、0日曜日～6土曜日
unixepoch	日付の指定を'DDDDDDDDDD'の形式で指定した時、UNIXタイムスタンプの値として処理
localtime	ローカルのタイムゾーンでの日付と時刻に変換
utc	日付のタイムゾーンをローカルタイムゾーンとして扱いUTCタイムゾーンに変換

localtime以外は使う事がないと思いますが、WEBに情報が少ないのであえて記載しておきました。
上記は、以下のページを参考にまとめました。
Modifiers(修飾子)の指定

その他の関数

関数名	構文	説明
IFNULL	IFNULL(式, 値)	式の値がNULLの場合は第2引数の値を返します。

※NULLについては、次回詳しく解説します。

演算子

比較演算子

=	等しい
>	大きい
<	小さい
>=	以上
<=	以下
<>	等しくない
!=	等しくない

論理演算子

AND

右辺と左辺がともに真の時に真

右辺と左辺のどちらかが真の時に真

BETWEEN

式 BETWEEN 値1 AND 値2
式の値が、値1以上値2以下の時に真

式 IN (式, ・・・)
INの前の式が、()内のいずれかと一致している場合に真

メタ文字によるパターンマッチングを行います

%	任意の文字列
_	アンダースコアです。任意の1文字

ビット演算子

&	論理積
\|	論理和

算術演算子

+	足し算
-	引き算
*	掛け算
/	割り算
%	剰余

文字列結合


  
    
      ||
      右辺と左辺の文字列式を結合した文字列を返します。

      文字列式1 || 文字列式2 → 文字列1文字列2

多くのDBにはCONCAT関数があります。
SQLiteにはCONAT関数が無いので、||で文字列結合してください。

IS NULL演算子

式がNULLかどうかを判定します。
否定の場合は、
IS NOT NULL
とします。

式 = NULL
これではNULL判定できません。

※NULLについては、次回詳しく解説します。

CASE演算子

CASE演算子は、以下の2通りの使い方があります。

CASE 式 WHEN 値 THEN 式 [ WHEN 値 THEN 式・・・] [ ELSE 式 ] END

式の値がWHENの値と一致する場合にその式を返します。
判定は左から順に行われ、最初に一致したWHENに対応するTHENの式が返されます。
一致するWHENが無いときはELSEの式が返され、ELSEが省略されている場合はNULLが返されます。


  
    
      CASE item_count WHEN 10 THEN 1 WHEN 11 THEN 2 ELSE 3 END

CASE WHEN 条件式 THEN 式 [ WHEN 条件式 THEN 式・・・] [ ELSE 式 ] END

条件式を左から順に判定し、最初に真になったTHENの式が返されます。
真になる条件式が無いときはELSEの式が返され、ELSEが省略されている場合はNULLが返されます。


  
    
      CASE WHEN item_count = 10 THEN 1 WHEN item_count = 11 THEN 2 ELSE 3 END