SQLite3のためのSQLリファレンス
はじめに
- この記事では、公式文書「SQL As Understood By SQLite」を元に、リファレンス目的でSQLite3のSQLを整理します。
- 自分の理解しやすい概念や呼び名に置き換えています。
- SQLの学習には適しません。初学者に不正確な概念を植え付けるかも知れません。
- 「SQLiteの理解するSQL」を網羅したものではありません。
- 自分が使わなさそうな、一部の機能や書式は網羅していません。
- 他のシステムとの互換性のための構文は多くを省略しています。
- 仕様書である元ドキュメントとは別物ですのでご留意願います。
- 用語には定義へのリンクが張られ、記事の末尾には索引を設けてあります。
- 自分の理解しやすい概念や呼び名に置き換えています。
- テーブル、レコード、カラム、リレーションなどといったリレーショナルデータベースシステムの概念については扱いません。
- SQLiteの導入だとか、使い方(SQLiteコマンドを含む)だとかは扱わず、専らSQL言語についてのみ言及します。
- SQLには詳しくないので、間違いや改善など、ご指摘・ご提案いただけると助かります。
SQLite
- SQLiteは、SQLのサブセットが使えるスタンドアローンなデータベース管理システムです。
- Windows、MacOS、Android、iOSなどに対応しています。
- 公式サイト
- 参考: Railroad Diagrams (GitHub)
- 参考: EBNF (GitHub)
- 参考: UnityでSQLiteを使う ~ SQLiteUnityKit拡張ライブラリ (Qiita)
用語と書式表現
- 以下のような用語を使います。
- 「テーブル」は「行列」と表記することがあります。
- 「レコード」は「行」と表記することがあります。
- 「カラム」は「列」と表記することがあります。
- 「ピアグループ」を「同輩グループ」と表記することがあります。
- 「演算子」、「式」、「句」などの区分は不正確です。
- 「文」の部品と「句」の区分は曖昧です。
- 以下のような構文書式を使います。
- 「《~》」は、リンク先で別途定義されている要素です。
- 「〔~〕」は、省略可能な要素です。
- 「…」は、その前に書かれた部分が反復可能であることを示しますが、繰り返される範囲や回数は曖昧です。
- 例えば、「《~》~《~》…」と書かれている場合に、直前の要素だけが繰り返されるとは限りません。
- 見た目の余白をもって、空白文字が省略されている場合があります。
- 例えば、「《~》~《~》」とある時、「《」の前や「》」の後に空白が必要な場合があります。
- 項目定義内に複数の書式がある場合は、いずれか一つが選択可能なことを表します。
- 書式か地の文かは曖昧です。
- 単独で書式が書かれる場合は、太字で前後に改行があります。
- しかし、横幅制限で行が折り返したのか、改行があるのかは曖昧です。
- 地の文の中で書式が書かれる場合は、
コード表現
が使われます。
- 単独で書式が書かれる場合は、太字で前後に改行があります。
- 一部の書式の表現で、
RegExp/
~**/
**の形式で正規表現式が使用されます。
検証環境
この記事のコードは、以下の環境で検証いたしました。
- Windows 10
-
DB Browser for SQLite
- バージョン 3.12.2
- x86_64-little_endian-llp64 向けビルド, x86_64 で動作中
- Qt バージョン 5.12.8
- SQLite バージョン 3.35.5.
基本構造
- SQLは、「《文》;」が繰り返されたものです。
コメント
- 「
--
以降行末まで」および「/*
と*/
で囲まれた部分」がコメントになります。
リテラル
INTEGER、REAL
- 小数点(
.
)があるか、指数部(e
またはE
)があるか、-9223372036854775808
より小さいか、9223372036854775807
より大きい場合はREAL
で、それ以外はINTEGER
です。 -
INTEGER
は、1, 2, 3, 4, 6, 8バイトの整数です。 -
REAL
は、8バイトのIEEE浮動小数点数です。
16進表記
-
0x
または0X
の後に16進表現(最長8文字)が続きます。 - SQLの構文中でのみ、16進数(64ビットの2の補数
INTEGER
)として解釈されます。
TEXT
- 文字列をシングルクォート(
'
)で挟みます。- 文字列中のシングルクォートは、連続するシングルクオート2文字(
''
)で表現します。 - シングルクォートの代わりにダブルクォート(
"
)も使えます。
- 文字列中のシングルクォートは、連続するシングルクオート2文字(
- C言語風のエスケープはありません。
- エンコードは、UTF-8、UTF-16BE、UTF-16LEのいずれかです。
BLOB
- 16進数を含む文字列リテラルの前に
x
またはX
が付きます。- 例:
X'53514C697465'
- 例:
NULL
-
NULL
は、データがないことを表します。
日時
-
CURRENT_DATE
、CURRENT_TIME
、CURRENT_TIMESTAMP
は、UTCで、現在日、現在時、現在日時を表すリテラルです。 - 《GENERATED ALWAYS AS制約》(いわゆる計算列)では使用できません。
真偽値
真偽値 | 表記例 |
---|---|
真 |
TRUE 、1 、1.0 、0.1 、-0.1 、'1english'
|
偽 |
FALSE 、NULL 、0.0 、0 、'english' 、'0'
|
スカラ、リスト、行列
スカラ
構造を持たない単一の値です。
スカラ式
- 結果が《スカラ》になる式です。
リスト
リスト式
- 結果が《リスト》になる式です。
行列
行列式
- 結果が《行列》になる式です。
式
《リテラル》
《単項演算子》《式》
《式》《二項演算子》《式》
《列》
《関数》
(《式》)
《BETWEEN式》
《CASE式》
《EXISTS句》
《CAST式》
《NULL式》
演算子
以降の説明では、一部に演算子以外の句や式などを含みます。
二項演算子
優先順位 | 演算子 | 分類 |
---|---|---|
高 | || | 文字列演算 |
↑ | * / % | 四則演算 |
↑ | + - | 四則演算 |
↑ | << >> & | | ビット演算 |
↓ | < <= > >= | 比較演算 |
↓ | = == != <> IS IS NOT IN LIKE GLOB MATCH
|
比較演算 |
↓ | AND |
論理演算 |
低 | OR |
論理演算 |
単項演算子
演算子 | 書式 | 分類 | 意味 |
---|---|---|---|
- | -《式》 | 符号 | 負 |
+ | +《式》 | 符号 | 正 (無作用) |
四則演算
演算子 | 書式 | 意味 |
---|---|---|
+ | 《式》 + 《式》 | 加算 |
- | 《式》 - 《式》 | 減算 |
* | 《式》 * 《式》 | 乗算 |
/ | 《式》 / 《式》 | 除算 |
% | 《式》 % 《式》 | 剰余 |
ビット演算
ビットシフト
演算子 | 書式 | 意味 |
---|---|---|
<< | 《式》 << 《式》 | 左シフト |
>> | 《式》 >> 《式》 | 右シフト |
ビット論理演算
演算子 | 書式 | 意味 |
---|---|---|
& | 《式》 & 《式》 | 論理積 |
| | 《式》 | 《式》 | 論理和 |
~ | ~《式》 | 1の補数 |
比較演算
演算子 | 書式 | 意味 |
---|---|---|
= == IS IS NOT DISTINCT FROM |
《式》 = 《式》 《式》 == 《式》 《式》 IS 《式》 《式》 IS NOT DISTINCT FROM 《式》 |
等しい |
!= <> IS NOT IS DISTINCT FROM |
《式》 != 《式》 《式》 <> 《式》 《式》 IS NOT 《式》 《式》 IS DISTINCT FROM 《式》 |
等しくない |
< | 《式》 < 《式》 | より小さい |
<= | 《式》 <= 《式》 | より小さいか等しい |
> | 《式》 > 《式》 | より大きい |
>= | 《式》 >= 《式》 | より大きいか等しい |
IN | 《式》 IN 《式》 | 含む |
NOT IN | 《式》 NOT IN 《式》 | 含まない |
LIKE | 《式》LIKE《式》〔ESCAPE《TEXT》〕 | パターンに一致する |
GLOB | 《式》GLOB《式》 | パターンに一致する |
- 一部の例外を除き、オペランドが
NULL
の場合にはNULL
と評価されます。
X AND Y
- 一方が
NULL
で他方が偽の場合は偽(0
)と評価されます。
X OR Y
- 一方が
NULL
で他方が真の場合は真(1
)と評価されます。
X IS Y、X IS NOT DISTINCT FROM Y
-
X
、Y
ともにNULL
の場合は真と評価されます。 - 一方が
NULL
で他方が非NULL
の場合は偽と評価されます。 - 双方とも
NULL
でなければ==
と等価です。
X IS NOT Y、X IS DISTINCT FROM Y
-
X
、Y
ともにNULL
の場合は偽と評価されます。 - 一方が
NULL
で、他方が非NULL
の場合は真と評価されます。 - 双方とも
NULL
でなければ!=
と等価です。
X IN Y、X NOT IN Y
-
X
が、Y
に列挙された値の中に存在すれば真と評価されます。-
X
もY
も、式やサブクエリが書けます。
-
-
X
が《スカラ》ならY
は《リスト》でなければなりません。 -
X
が《リスト》ならY
は《行列》でなければなりません。 -
Y
が《テーブル名》なら、サブクエリ(SELECT * FROM《テーブル名》)
の省略形と解釈されます。 -
Y
が空の《リスト》ならX
がなんであれ(含NULL
)、IN
は偽であり、NOT IN
は真です。
LIKE
パターン | 意味 |
---|---|
% | 任意の0文字以上の文字列 |
_ | 任意の1文字 |
その他 | その文字そのもの |
- 左辺の文字列が右辺のパターンに一致すれば真になります。
- 指定された文字でパターンの特殊文字をエスケープできます。
-
ESCAPE
の《TEXT》は1文字でなければなりません。
-
- ASCII範囲の大文字と小文字は区別されません。
- 《like関数》でも同等のことができます。
'りんご100#' LIKE '%100%' -- は真です。
'りんご100%' LIKE '%100$%' ESCAPE '$' -- は真です。
'りんご100#' LIKE '%100$%' ESCAPE '$' -- は偽です。
'りんご$100' LIKE '%$$100' ESCAPE '$' -- は真です。
GLOB
パターン | 意味 |
---|---|
* | 任意の0文字以上の文字列 |
? | 任意の1文字 |
[abc] |
a 、b 、c のいずれかに一致 |
[a-d] |
a ~d のいずれかに一致 |
その他 | その文字そのもの |
- 左辺の文字列が右辺のパターンに一致すれば真になります。
- パターンの特殊文字をエスケープしたい場合は、
[~]
で囲みます。-
[~]
は「~のいずれか」という意味ですが、1文字だけであれば、その文字そのものであることを表します。 - 例:
'りんご100%' GLOB '*100[%]'
は真です。
-
- 大文字と小文字は区別されます。
- 《glob関数》でも同等のことができます。
BETWEEN式
x BETWEEN y AND z
-
x
、y
、z
は《式》です。 - 左辺の値が中辺と右辺の範囲内にあれば真になります。
-
x BETWEEN y AND z
は、x
が一度しか評価されないことを除きx >= y AND x <= z
と等価です。
CASE式
- 条件によって選択された値になります。
- 左から順に評価され、最初に条件が成立した時点で、以降は評価されません。
- 以下の例のように、二種類の書式があります。
CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END -- `x`は一度しか評価されません。
CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END -- `x`は複数回評価される可能性があります。
CASE《式》WHEN《式》THEN《式》〔WHEN《式》THEN《式》…〕〔ELSE《式》〕END
- C言語の
switch ~ case ~ default ~
に近いです。
CASE WHEN《式》THEN《式》〔WHEN《式》THEN《式》…〕〔ELSE《式》〕END
- C言語の三項演算子(
~ ? ~ : ~
)に近いです。 - 以下の例のように、《iif関数》でも部分的に同等のことができます。
(CASE WHEN x THEN y ELSE z END) == iif(x, y, z)
NULL式
《式》ISNULL
《式》NOTNULL
《式》NOT NULL
- 式が
NULL
値かどうかを判定し、1
または0
を返します。
論理演算
演算子 | 書式 | 意味 |
---|---|---|
AND | 《式》AND《式》 | かつ |
OR | 《式》OR《式》 | または |
NOT | NOT《式》 | でない |
EXISTS句
EXISTS (《クエリ文》)
- 引数に行があれば
1
なければ0
になります。- 例えば、
EXISTS (VALUES (NULL))
は1
です。
- 例えば、
文字列演算
演算子 | 書式 | 意味 |
---|---|---|
|| | 《式》||《式》 | 連結 |
型変換
CAST式
- 式の値を指定の型に変換します。
関数
一般関数
算術関数
max(X, Y, …)、min(X, Y, …)
- 複数の引数の中から最大の値あるいは最小の値を返します。
- 引数に
NULL
が含まれるとNULL
を返します。
round(X)、round(X, Y)
-
X
の小数部をY
桁に丸めた浮動小数点数を返します。 -
Y
を省略すると0
が使われます。
abs(X)
- 引数
X
の絶対値を返します。 -
X
がNULL
ならNULL
を、数値にできない場合は0.0
を返します。 -
X
が整数-9223372036854775808
の場合は、該当の正の64ビット2の補数値がないため、例外「整数オーバーフロー」を投じます。
random()
- 整数
-9223372036854775808
~+9223372036854775807
の範囲で疑似乱数を返します。
randomblob(N)
- 疑似乱数で埋めた
N
バイトのBLOB
を返します。 -
N
が1
未満なら、1
バイトを返します。
SELECT hex(randomblob(16)); --> '79CF5C2FC33D2A96CEF644484A42745D' (乱数なので毎回変わります)
SELECT lower(hex(randomblob(16))); --> '79cf5c2fc33d2a96cef644484a42745d' (乱数なので毎回変わります)
文字列、BLOB関数
hex(X)
- 引数を
BLOB
として解釈し、大文字で16進ダンプした文字列を返します。
length(X)
-
X
がTEXT
なら、最初のNUL
文字までの文字数(バイト数ではない)を返します。- SQLiteの文字列には、通常
NUL
文字は含まれません。
- SQLiteの文字列には、通常
-
X
がBLOB
値なら、バイト数を返します。 -
X
がNULL
なら、NULL
を返します。 -
X
が数値なら、数値を文字列で表現した際の長さを返します。
lower(X)、upper(X)
- すべてのASCII文字が小文字あるいは大文字に変換された
X
のコピーを返します。
replace(X, Y, Z)
- 文字列
X
中の全てのY
をZ
に置き換えた文字列を返します。 -
Y
が空の文字列なら、元のままのX
を返します。 - 文字列でない
Z
は、あらかじめUTF-8文字列に変換されます。
substr(X, Y, Z)、substr(X, Y)
- 左端を1番目として、
X
のY
番目からZ
文字の部分文字列を返します。 -
Z
を省略すると、Y
から文字列の最後までを返します。 -
Y
が負なら、abs(Y)
は右端を1番目とする位置を示します。 -
Z
が負なら、Y
番目の文字の前にあるabs(Z)
文字が返されます。 -
X
がBLOB
の場合は、インデックスはバイトを参照します。
SELECT substr('あいうえおかきくけこ',-2,-2); --> 'きく'
SELECT substr(X'E38182E38184E38186E38188E3818AE3818BE3818DE3818FE38191E38193',-6,-6); --> 'きく'
trim(X)、trim(X, Y)
-
X
の両端からY
に含まれる文字を全て削除した文字列を返します。 -
Y
を省略すると、スペースを削除します。
rtrim(X)、rtrim(X, Y)
-
X
の右端からY
に含まれる文字を全て削除した文字列を返します。 -
Y
を省略すると、スペースを削除します。
ltrim(X)、ltrim(X, Y)
-
X
の左端からY
に含まれる文字を全て削除した文字列を返します。 -
Y
を省略すると、スペースを削除します。
quote(X)
-
X
をSQLリテラルのテキストにして返します。 - 文字列は一重引用符で囲まれ、必要に応じて内部引用符はエスケープされます。
-
BLOB
は、16進リテラルとしてエンコードされます。 -
NUL
文字を含む文字列は、最初のNUL
文字以降が切り捨てられます。
char(X1, X2, …, XN)
- 各引数を文字コードとする文字列を返します。
-
NUL
文字を含めることもできます。
SELECT hex(char(64,65,66,0,67,68,69)); --> '40414200434445'
unicode(X)
- 最初の文字のコードを返します。
- 引数が文字列でない場合の結果は定義されていません。
like(X, Y)、like(X, Y, Z)
-
Y LIKE X
、Y LIKE X ESCAPE Z
と同等です。 -
《LIKE演算子》とは、
X
とY
が逆になっていることに注意してください。 -
X
がパターンで、Y
がそのパターンと照合する文字列です。
like('%neon%',name) == (name LIKE '%neon%')
glob(X, Y)
-
Y GLOB X
と同等です。 -
《GLOB演算子》とは、
X
とY
が逆になっていることに注意してください。 -
X
がパターンで、Y
がそのパターンと照合する文字列です。
glob('*helium*',name) == (name GLOB '*helium*')
instr(X, Y)
- 文字列
X
内で最初に見つかった文字列Y
の位置を、左端を1
とする番号で返します。見つからない場合は0
を返します。 -
X
とY
が両方ともBLOB
なら、文字ではなくバイトとして扱います。 - 引数がどちらも
NULL
でもBLOB
でもなければ、双方を文字列として解釈します。 - 引数のいずれかが
NULL
なら、結果はNULL
になります。
printf(FORMAT, …)
- 標準Cライブラリの
printf()
関数のように機能します。 - 書式文字列が欠落しているか
NULL
の場合、結果はNULL
になります。%n
は無視されて、引数を消費しません。%p
は%X
のエイリアスです。%z
は%s
と交換可能です。 - 引数リストに引数が少なすぎる場合、欠落している引数は
NULL
値であると見なされ、数値形式の場合は0
または0.0
に、%s
の場合は空の文字列に変換されます。
書式制御文字列
FORMAT詳細
RegExp/%($flags)?($width)?(\.$precision)?($length)?type/
type | 意味 |
---|---|
%% | % |
d i |
符号付き10進整数 |
u | 符号なし10進整数 |
f | 10進固定小数点数 |
e E |
10進浮動小数点数 |
g G |
コンパクトな10進数値 |
x X |
16進整数 |
o | 8進整数 |
s z |
NUL 文字で終端する文字列、引数がNULL なら無表示 |
c | 最初の1文字 |
p |
%x と同じ |
n | 無表示で引数を消費しない |
q Q |
SQL文字列リテラル、q は外側引用符なし、Q は外側引用符あり引数が NULL だと、q は(NULL) 、Q はNULL
|
w | 引数がNULL の時の挙動は%q と同じ、それ以外では%s と同じ |
length | 意味 (整数に対してのみ有効) |
---|---|
<n> | 32bit |
<n>l | 32bit |
<n>ll | 64bit |
flag | 意味 |
---|---|
- | パディングなしの左寄せ、デフォルトは右寄せ |
+ | 符号付きの正数に+ 記号を前置 |
space | 符号付きの正数に1つのスペースをを前置 |
0 | パディング文字に0 に使用 |
, | 3桁区切りのカンマ |
# |
%g 、%G の末尾のゼロを削除、浮動小数点数の小数点を表示%o 、%x 、%X の値の前に0 、0x 、0X を付加(alternate-form-1) |
! | 文字列の幅と精度をバイトではなく文字で理解 浮動小数点数の有効桁数を16から26に増加、小数点以下最少1桁 (alternate-form-2) |
zeroblob(N)
-
0x00
×N
バイトのBLOB
を返します。
日時関数
《GENERATED ALWAYS AS制約》(いわゆる計算列)での使用には制限があります。
《日時文字列》,《日時修飾子》, …〕)
date(〔- 日付を生成します。
- 引数がないとUTCで現在の日付を返します。
《日時文字列》,《日時修飾子》, …〕)
time(〔- 時刻を生成します。
- 引数がないとUTCで現在の日付を返します。
《日時文字列》,《日時修飾子》, …〕)
datetime(〔- 日時を生成します。
- 引数がないとUTCで現在の日付を返します。
《日時文字列》,《日時修飾子》, …〕)
julianday(〔- ユリウス通日を生成します。
- 引数がないとUTCで現在の日付を返します。
《日時書式》,《日時文字列》,《日時修飾子》, …〕)
strftime(〔- 書式による文字列生成
- 引数がないと
NULL
になります。
関数 |
strftime() による同等の表現 |
---|---|
date(…) |
strftime('%Y-%m-%d', …) |
time(…) |
strftime('%H:%M:%S', …) |
datetime(…) |
strftime('%Y-%m-%d %H:%M:%S', …) |
julianday(…) |
strftime('%J', …) |
日時書式
書式 | 意味 | 範囲 |
---|---|---|
%Y | 年 | 0000~9999 |
%m | 月 | 01~12 |
%d | 日 | 00 |
%w | 曜 | 0~6 (日曜==0) |
%H | 時 | 00~24 |
%M | 分 | 00~59 |
%S | 秒 | 00~59 |
%f | 少数秒 | SS.SSS |
%j | 通日 | 001-366 |
%W | 週番号 | 00-53 |
%J | ユリウス通日 | 0.0~ |
%s | UNIX時間 (1970-01-01からの秒数) |
0.0~ |
%% | % |
日時文字列
書式 | 備考 |
---|---|
YYYY-MM-DD | |
YYYY-MM-DD HH:MM | |
YYYY-MM-DD HH:MM:SS | |
YYYY-MM-DD HH:MM:SS.SSS | |
HH:MM | |
HH:MM:SS | |
HH:MM:SS.SSS | |
now | |
DDDDDDDDDD | UNIX時間 |
日時修飾子
書式 | 意味 |
---|---|
NNN days | 日を加算 |
NNN hours | 時間を加算 |
NNN minutes | 分を加算 |
NNN.NNNN seconds | 秒を加算 |
NNN months | 月を加算 |
NNN years | 年を加算 |
start of month | 月の始め |
start of year | 年の初め |
start of day | 日の始め |
weekday N | 本日以後のN 曜日 (0~6; 日曜==0) |
unixepoch | UNIX時間 |
localtime | 標準時 (地方時) |
utc | 協定世界時 |
日時関数の使用例
- 現在の日付
SELECT date('now');
- 今月の最終日
SELECT date('now','start of month','+1 month','-1 day');
- UNIX時間
1092941466
の日時SELECT datetime(1092941466, 'unixepoch');
- UNIX時間
1092941466
のローカルタイムゾーン日時SELECT datetime(1092941466, 'unixepoch', 'localtime');
- 現時刻のUNIX時間
SELECT strftime('%s','now');
- 米国独立宣言に署名してからの日数
SELECT julianday('now') - julianday('1776-07-04');
- 2004年の特定の瞬間からの秒数
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
- 今年の10月の最初の火曜日の日付
SELECT date('now','start of year','+9 months','weekday 2');
- 現時刻のユリウス通日からの経過秒数
SELECT (julianday('now') - 2440587.5)*86400.0;
- 註:
CAST((julianday('now') - 2440587.5)*86400.0 AS INTEGER)
==strftime('%s', 'now')
論理関数
iif(X, Y, Z)
-
X
が真ならY
を、あらざればZ
を返します。 -
《CASE式》
CASE WHEN X THEN Y ELSE Z END
と同等です。
nullif(X, Y)
- 二つの引数が同じなら
NULL
を、異なればX
を返します。
ifnull(X, Y)
-
X
がNULL
ならY
を、あらざればX
を返します。 - 必ず2つの引数が必要です。
coalesce(X, Y, …)
-
NULL
でない最初の引数、または最後の引数を返します。 - 少なくとも2つの引数が必要です。
-
coalesce(X, Y)
は、ifnull(X, Y)
と同等です。
データベース関数
changes()
- 最後に完了した
INSERT
/DELETE
/UPDATE
文によって追加/削除/更新された行数を返します。
total_changes()
- 現在のデータベース接続が開かれてから、
INSERT
/UPDATE
/DELETE
文によって変更された行数を返します。
last_insert_rowid()
- 最後に追加された行の
ROWID
を返します。
typeof(X)
- テーブルに格納されている値のデータ型を取得(関数)
- 式
X
のデータ型を示す次のいずれかの文字列を返します。 "null", "integer", "real", "text", "blob"
sqlite_version()
- SQLiteのバージョン情報を返します。
JSON関数 (演算子)
json(X)
- json値
X
から最適化されたjsonを生成します。 - ラベルの重複に対する挙動は定義されていません。
- 正しいjson表現でない場合はエラーします。
json値
- json表記に従う
TEXT
、あるいは、《JSON関数》の返す値です。
json_array(…)
- 引数を要素とするjson配列を生成します。
json_array_length(X)、json_array_length(X, Y)
- json値
X
、または、X
のパスY
に対して、json配列とみなして要素数を返します。 - 対象の要素が配列でない場合は
0
を返します。 - パスに該当する要素が
X
に存在しない場合はNULL
を返します。 - 正しいjson表現でない場合はエラーします。
パス
- jsonの全体、あるいは、一部を示すjs文字列です。
- 全体を
"$"
で表記します。 - 配列の要素数(次の添え字)を
"#"
で表記します。 - 正しいjs表現でない場合はエラーします。
- 例
- パス
$
は、json全体を示します。 - json
'[1,2,3]'
に対して、パス'$[1]'
は2
を示します。 - json
'[1,2,3]'
に対して、パス'$[#]'
は'$[3]'
と同義でNULL
を示します。 - json
'{"a":[1,2,3]}'
に対して、パス'$.a'
は[1,2,3]
を、パス'$.a[1]'
は2
を示します。
- パス
- 例
json_extract(X, …)
- json値
X
に対して、以降の引数をパスとして示される要素を返します。 - パスが指定されない場合は
NULL
を返し、複数の場合は要素を配列にして返します。 - パスに該当する要素が
X
に存在しない場合は、要素はNULL
になります。 - 正しいjson表現でない場合はエラーします。
X -> Y
- 概ね、
json_quote(json_extract(X, Y))
に相当する演算子です。 - 正しいjson表現でない場合はエラーします。
X ->> Y
-
json_extract(X, Y)
に相当する演算子です。 - 正しいjson表現でない場合はエラーします。
json_insert(X, Y1, Z1, …)、json_replace(X, Y1, Z1, …)、json_set(X, Y1, Z1, …)
- json値
X
に対して、パスY
の値をZ
とします。-
json_insert
は、新規要素を追加できますが、既存要素を置換できません。 -
json_replace
は、新規要素を追加できず、既存要素の置換のみ可能です。 -
json_set
は、新規要素の追加、および、既存要素の置換が可能です。
-
- 引数
Y
,Z
のペアが0以上の任意数指定可能です。- 引数
Y
,Z
が指定されない場合は、X
をそのまま返します。
- 引数
- 正しいjson表現でない場合はエラーします。
json_object(X1, Y1, …)
- ラベル
X
の値をY
とするjson値({"X":"Y"}
)を返します。 - 引数
X
,Y
のペアが0以上の任意数指定可能です。
json_patch(X, Y)
- json値
X
にjson値Y
をマージします。 - 各json値がjsonオブジェクトであることが想定されています。
- json値が配列の場合はマージでなく置き換えが生じます。
- 正しいjson表現でない場合はエラーします。
json_remove(X1, Y1, …)
- json値
X
のパスY
の値を除去して返します。 - 引数
X
,Y
のペアが0以上の任意数指定可能です。 - 正しいjson表現でない場合はエラーします。
json_type(X)、json_type(X, Y)
- json値
X
、または、X
のパスY
に対して、その型を表す文字列を返します。 -
X
がNULL
であればNULL
を返します。 - 正しいjson表現でない場合はエラーします。
json_valid(X)
-
X
が有効なjson値であれば真を返します。
json_quote(X)
-
X
をjson表現上の文字列に変換し、二重引用符で囲われた文字列を返します。
json_group_array(X)
- 複数行の値
X
からjson配列を生成して返します。 - 集計関数のように働きます。
json_group_object(X, Y)
- 複数行のキー
X
と値Y
からjsonオブジェクトを生成して返します。 - 集計関数のように働きます。
json_each(X)、json_each(X, Y)
- json配列値
X
、または、X
のパスY
に対して、1列(value)のテーブルを生成します。
json_tree(X)、json_tree(X, Y)
- jsonオブジェクト値
X
、または、X
のパスY
に対して、8列(key, value, type, atom, id, parent, fullkey, path)のテーブルを生成します。 - 最初の行の
key
はNULL
、value
は元のオブジェクト
になり、以降に階層構造が展開されます。
集計、分析関数
関数名 | 一般関数 simple |
集計関数 aggregate |
分析関数 window |
説明 |
---|---|---|---|---|
avg | ✔ | ✔ | 平均値 | |
count | ✔ | ✔ | 行カウント | |
first_value last_value nth_value |
✔ | 最初 最後 <N>番目の行 |
||
lag lead |
✔ | 前の行 後ろの行 |
||
max min |
✔ | ✔ | ✔ | 最大値 最小値 |
median | 中間値 | |||
ntile | ✔ | <N>分割内番号 | ||
rank | ✔ | 断続ORDER 順位 |
||
dense_rank | ✔ | 連続ORDER 順位 |
||
row_number | ✔ | 行番号 | ||
sum total |
✔ | ✔ | 合計 | |
group_concat | ✔ | ✔ | 値の連結リスト |
集計関数
《名前》(〔DISTINCT〕《式》, …)〔《FILTER句》〕
avg(X)
- グループ内の
NULL
でないX
の平均値を浮動小数点数で返します。 - 数値に変換できない値や
BLOB
は0
として解釈されます。 - 全ての値が
NULL
であれば、結果はNULL
になります。
count(X)、count(*)
-
count(X)
は、グループ内のNULL
でないX
の数を返します。- 全ての値が
NULL
であれば、結果は0
になります。
- 全ての値が
-
count(*)
は、グループ内の行数を返します。列の値には影響を受けません。
group_concat(X)、group_concat(X, Y)
- グループ内の
NULL
でないX
を、Y
またはカンマ,
で区切って連結した文字列を返します。 - 連結された要素の順序は定義されません。
- 全ての値が
NULL
であれば、結果はNULL
になります。
max(X)
- グループ内の
X
の最大値を返します。 - 最大値とは《ORDER BY句》で並び替えられる最後の値です。
- 全ての値が
NULL
であれば、結果はNULL
になります。
min(X)
- グループ内の
NULL
を除くX
の最小値を返します。 - 最小値とは《ORDER BY句》で並び替えられる最初の
NULL
以外の値です。 - 全ての値が
NULL
であれば、結果はNULL
になります。
sum(X)、total(X)
- グループ内の
NULL
以外のX
の合計を返します。 - 全ての値が
NULL
の場合、sum(X)
はNULL
を返しますが、total(X)
は0.0
を返します。 -
total(X)
は、常に浮動小数点数を返します。 -
sum(X)
は、整数、浮動小数点数、NULL
のいずれかを返します。 -
sum(X)
は、X
が整数とNULL
のみからなる場合に、例外「整数オーバーフロー」を生じる可能性があります。
json_group_array(X)
- グループ内の
X
を要素として生成したjson配列を返します。
json_group_object(X, Y)
- グループ内の
X
とY
をラベルと値として生成したjsonオブジェクトを返します。
分析関数
《名前》(《式》, …)〔《FILTER句》〕《OVER句》
row_number()
- パーティション内の行の番号を返します。
-
《OVER句》の《ORDER BY句》で定義された順序で、またはそれ以外の場合は未定義の順序で、
1
から始まる番号が付けられます。
rank()
- パーティション内の同輩グループの最初の行の
row_number()
(欠番のある断続順位)です。 -
《ORDER BY句》がない場合、すべての行が同順位と見なされ、常に
1
を返します。
dense_rank()
- パーティション内の同輩グループの番号(欠番のない連続順位)です。
- グループには、《ORDER BY句》で定義された順序で、
1
から始まる連続番号が付けられます。 -
《ORDER BY句》がない場合、すべての行が同順位と見なされ、常に
1
を返します。
percent_rank()
-
rank()
を0.0 ~ 1.0
にマッピングしたもので、(rank() - 1) / (partition_rows - 1)
で算出される正規化された順位を返します。-
partition_rows
はパーティション内の行数です。 -
partition_rows
が1
なら0.0
を返します。
-
cume_dist()
-
row_number / partition_rows
で算出される累積分布を返します。 -
row_number
は、同輩グループの最後の行のrow_number()
で、partition_rows
はパーティション内の行数です。-
partition_rows
が0
ならNULL
を返します。
-
ntile(N)
- パーティションを
N
個に分割したグループに付けた1
から始まる番号を返します。 - 分割がやむを得ず不均等になる場合は、最初に大きなグループを配します。
- 《ORDER BY句》がない場合、グループの順序は定義されません。
lag(expr)、lag(expr, offset)、lag(expr, offset, default)
- パーティション内の現在の行から
offset
または1
遡る行に対して、expr
を評価した結果を返します。 - 対象の行が存在しなければ、
default
またはNULL
を返します。 -
offset
は現在の行を0
として、負でない整数でなければなりません。
lead(expr)、lead(expr, offset)、lead(expr, offset, default)
- パーティション内の現在の行から
offset
または1
進む行に対して、expr
を評価した結果を返します。 - 対象の行が存在しなければ、
default
またはNULL
を返します。 -
offset
は現在の行を0
として、負でない整数でなければなりません。
first_value(expr)
- パーティション内の最初の行に対して、
expr
を評価した結果を返します。
last_value(expr)
- パーティション内の最後の行に対して、
expr
を評価した結果を返します。
nth_value(expr, N)
- パーティション内の最初の行を
1
として、行N
に対してexpr
を評価した結果を返します。 - 該当する行がなければ
NULL
を返します。
句 (clause)
名前
RegExp/[A-Za-z_][A-Za-z_0-9]*/
- この書式では、予約語は使用できません。
- この書式では、予約語を含む自由度の高い名付けが可能です。
RegExp/\[[^\]]+\]/]
- この書式では、予約語を含む自由度の高い名付けが可能です。
スキーマ名
- データベースの名前です。
テーブル
テーブル名
テーブル句
《テーブル》〔AS《名前》〕〔[《INDEXED BY句》](#indexed by句)〕
INDEXED BY句
INDEXED BY《インデックス名》
- 指定された名前付きインデックスを使用するように強制します。
NOT INDEXED
- インデックスを使用しないように強制します。
列
列名
列定義
- 型名を表す《名前》には、名前であること以外に制約はありません。
- どのような型名の列であっても、全ての型の値を受け入れます。
- ただし、列の型名は、入出力時に生じる既定の型への変換のヒントに使用されます。
列制約
DEFAULT制約
- 挿入時に値が与えられなかった場合に使われる値を規定します。
- デフォルトは
NULL
です。
COLLATE制約
COLLATE《照合関数》
- 文字列を比較する際に使用する関数を規定します。
照合関数
-
BINARY
- デフォルトの単純なバイト比較です。
-
NOCASE
- ASCIIの26の大文字は、比較前に小文字に折りたたまれます。
-
NUL
文字は終端と見なされます。
-
RTRIM
- 末尾のスペース文字が無視されることを除いて
BINARY
と同じです。
- 末尾のスペース文字が無視されることを除いて
GENERATED ALWAYS AS制約
〔GENERATED ALWAYS〕AS (《式》)〔《計算方式》〕
- いわゆる「計算」列を規定します。
- 計算列は読み出し専用です。
- キーワード
GENERATED ALWAYS
と、計算方式VIRTUAL
は省略可能です。
- 式中では、以下の制限があります。
- 日時系リテラルは使用できません。
- 日時系関数の引数として、
'now'
、'localtime'
、'utc'
は使用できません。- 列生成は可能ですが、行挿入時にエラーします。
-
now
の代替としての「引数なし」(date()
、time()
、datetime()
)は使用可能です。
-
《INSERT文》で《挿入行列》に
DEFAULT VALUES
が使用できません。
計算方式
-
VIRTUAL
- 値を格納せず、参照の度に再計算します。
- デフォルトの方式です。
-
STORED
- 書き込み時に一度だけ計算して、以後は格納されている値を返します。
PRIMARY KEY列制約
PRIMARY KEY《コンフリクト句》〔AUTOINCREMENT〕
- テーブル制約《PRIMARY KEY》と同じです。
UNIQUE列制約
UNIQUE《コンフリクト句》
- テーブル制約《UNIQUE》と同じです。
CHECK列制約
CHECK (《式》)
- テーブル制約《CHECK》と同じです。
NOT NULL
NOT NULL《コンフリクト句》
- 列が
NULL
値を含まないように制限します。
テーブル制約
PRIMARY KEY
PRIMARY KEY (《列名》, …)《コンフリクト句》
- テーブルで唯一の主キーとなる列を設定します。
- 列または列の組み合わせは
UNIQUE
制約を受けます。 -
PRIMARY KEY
がINTEGER
型の単独列だと…-
ROWID
列が置き換えられます。 -
AUTOINCREMENT
の指定がなくても自動的に増加します。 -
AUTOINCREMENT
を指定すると欠番の再利用を行わなくなります
-
- 列または列の組み合わせは
UNIQUE
- 列または列の組み合わせの値の重複を制限します。
CHECK
CHECK (《式》, …)
- 式で列または列の組み合わせの値を制限します。
- 式の評価値が'0'だと制約に違反しているものと見なされます。
-
NULL
は違反になりません。
-
- 式の評価値が'0'だと制約に違反しているものと見なされます。
- 式にサブクエリを使うことはできません。
外部キー制約
- 別テーブルに存在する値のみに制限します。
- 外部キー制約を使用するためには、《プラグマ》
PRAGMA foreign_keys = ON;
が必要です。
⇒ SQLite Foreign Key Support (公式ドキュメント)
VALUES句
VALUES (《スカラ》, …), …
- 《行列》を表す即値です。
- サブクエリで置き換え可能です。例えば、以下の各ペアは同等です。
- 例1
SELECT * FROM (VALUES ("a", 0), ("b", 1), ("c", 2))
SELECT * FROM (SELECT "a" AS a, 0 AS column2 UNION ALL SELECT "b", 1 UNION ALL SELECT "c", 2)
- 例2
SELECT * FROM (VALUES (123, 0), ("b", 1), ("c", 2))
SELECT * FROM (SELECT 123 AS column1, 0 AS column2 UNION ALL SELECT "b", 1 UNION ALL SELECT "c", 2)
- 例1
- 初行の値の型が《TEXT》だとそのまま列名になり、それ以外の列名は
column《列番号》
になるようです。
コンフリクト句
〔ON CONFLICT《競合解決方式》〕
- 制約違反が発生した場合の処理を指定します。
- 指定が無いか、指定の方法が不可能な場合は、
ABORT
で処理されます。
競合解決方式
ROLLBACK
- 現在のトランザクションをロールバックします。
ABORT
- エラーを投げて、現在の文の実行前に戻しますが、それまでの文の結果は残ります。
- ひとつの文で複数行を変更している場合、それまでに違反なく変更された行も元に戻ります。
- デフォルトの処理です。
FAIL
- エラーを投げて、発生時の状態を維持して中断します。
- ひとつの文で複数行を変更している場合、それまでに違反なく変更された行は残りますが、以降の行は処理されません。
-
UNIQUE
、PRIMARY KEY
の一意性、NOT NULL
、CHECK
に対する制約違反にのみ有効で、他はABORT
で処理されます。
IGNORE
- 制約違反の生じた行をスキップして、後続行の処理を続行します。
- ひとつの文で複数行を変更している場合、違反のあった行だけが無視され、前後の有効な変更は残ります。
-
UNIQUE
、PRIMARY KEY
の一意性、NOT NULL
に対する違反は無視されます。- ただし、外部キー制約違反に対しては
ABORT
で処理されます。
- ただし、外部キー制約違反に対しては
REPLACE
-
UNIQUE
またはPRIMARY KEY
制約違反が発生した場合は、原因となった既存の行を削除して、処理を続行します。 - その際に削除トリガが発火するためには、《プラグマ》
PRAGMA recursive_triggers = ON;
が必要です。 -
NOT NULL
制約違反が発生した場合は、既定値に置き換えます。既定値がなければABORT
で処理されます。 -
CHECK
制約または外部キー制約違反が発生した場合は、ABORT
で処理されます。
FILTER句
FILTER (WHERE《式》)
ORDER BY句
ORDER BY《ORDER項目》, …
- 並べ替えを行います。
ORDER項目
《式》〔《COLLATE制約》〕〔《順序》〕〔《NULL順序》〕
NULL順序
NULLS FIRST
NULLS LAST
-
NULL
の位置(先頭/末尾)を選択びます。先頭がデフォルトです。
順序
ASC
DESC
- 昇順/降順を選択します。昇順がデフォルトです。
LIMIT句
LIMIT 《式》
- 出力する行数を式の値までに制限します。
- 負値だと無制限です。
- 第2式の行数をスキップした上で、最大第1式の行数を出力します。
- 第1式の行数をスキップした上で、最大第2式の行数を出力します。
OVER句
指定のウインドウ越しに式を評価します。
OVER《WINDOW名》
- 《WINDOW句》で定義されたウインドウを使用します。
OVER《WINDOW定義》
- 即値で定義したウインドウを使用します。
- 《WINDOW句》で定義されたウインドウを元にすることもできます。
WINDOW名
WINDOW定義
(〔《WINDOW名》〕〔《PARTITION BY句》〕〔《ORDER BY句》〕〔《フレーム》〕)
- 《WINDOW名》は、元にするウインドウです。
PARTITION BY句
PARTITION BY《式》, …
フレーム
- 参照の対象を指定します。デフォルトは
ROWS CURRENT ROW
(現在の行)です。
フレーム型
RANGE
- 《ORDER BY句》のソートキーの値が、現在の行を基準にして、範囲内にある行を対象に含めます。
ROWS
- 現在の行を基準にして行を数え、範囲内の行を対象に含めます。
GROUPS
- [《ORDER BY句》](#order by句)の同輩グループについて、現在のグループを基準にしてグループを数え、範囲内のグループに属する行を全て対象に含めます。
フレーム境界
BETWEEN《開始フレーム境界》AND《終了フレーム境界》
- 開始と終了を指定して範囲内を対象に含めます。
UNBOUNDED PRECEDING
- 最初の1行だけを対象にします。
《式》PRECEDING
- 現在行以前の1行だけを対象にします。
CURRENT ROW
- 現在の行だけを対象にします。
フレーム境界 | 意味 |
---|---|
UNBOUNDED PRECEDING | 最初の行 |
<n> PRECEDING | 現在より<n>行前 |
CURRENT ROW | 現在の行 |
<n> FOLLOWING | 現在より<n>行後 |
UNBOUNDED FOLLOWING | 最後の行 |
開始フレーム境界
UNBOUNDED PRECEDING
《式》PRECEDING
CURRENT ROW
《式》FOLLOWING
終了フレーム境界
《式》PRECEDING
CURRENT ROW
《式》FOLLOWING
UNBOUNDED FOLLOWING
EXCLUDE句
EXCLUDE CURRENT ROW
- 現在の行を除外します。
EXCLUDE GROUP
- 現在の行を含め、同輩グループの全ての行が除外されます。
EXCLUDE TIES
- 現在の行を除き、同輩グループの全ての行が除外されます。
WITH句
単純WITH句
WITH《共通テーブル式》, …
- サブクエリを順に処理して複数の共通テーブルを生成します。
WITH tmp(col1) AS (VALUES ('Hello world!'))
SELECT * FROM tmp;
共通テーブル式
《共通テーブル構造》AS (《クエリ文》)
- 《クエリ文》で共通テーブルを生成します。
共通テーブル構造
- 共通テーブルの列構造を定義します。
再帰WITH句
WITH〔RECURSIVE〕《共通テーブル構造》AS (《初期クエリ》UNION〔ALL〕《再帰クエリ》)
- サブクエリを再帰的に処理して単一の共通テーブルを生成します。
-
UNION
とUNION ALL
の違いは、《複合クエリ演算子》の場合と同様です。
WITH tmp(num) as (SELECT 1 UNION ALL SELECT num + 1 FROM tmp WHERE num < 10)
SELECT * FROM tmp;
初期クエリ
- 《共通テーブル構造》は参照できません。
- [《ORDER BY句》](#order by句)や《LIMIT句》は使用できません。
再帰クエリ
- 《共通テーブル構造》は《FROM句》で一度だけ参照しなければならず、かつ、それ以外では参照できません。
- 《集計関数》や《分析関数》は使用できません。
再帰WITH句のアルゴリズム
-
とします。i=0 - 初期クエリを実行し、結果を
とします。T_i - 直前のクエリの結果が
NULL
でない間、以下を繰り返します。-
とします。i=i+1 - 共通テーブルの内容を直前のクエリの結果で置き換えます。
- 再帰クエリを実行し、結果を
とします。T_i
-
- 共通テーブルの内容を、
~T_0 を演算子T_i UNION〔ALL〕
で結合したもので置き換えます。
WITH RECURSIVE tmp(num, last, fib) AS (
SELECT 0, 0, 0
UNION ALL
SELECT num + 1, fib, max(fib, 1) + last FROM tmp WHERE num < 20 -- max(fib, 1) は iif(num, fib, 1) でも可
)
SELECT fib FROM tmp;
クエリコア
- 即値から生成された《行列》です。
SELECT〔DISTINCT〕《結果列》, …〔《FROM句》〕〔WHERE《式》〕〔《GROUP BY句》〕〔《WINDOW句》〕
- テーブルから生成された《行列》です。
-
DISTINCT
が指定されると、重複した行が集約されます。- 指定が無ければ、重複を含めた全ての行(
ALL
)になります。- 上記書式では省略していますが、
ALL
を明記することもできます。
- 上記書式では省略していますが、
- 指定が無ければ、重複を含めた全ての行(
- 非集計クエリでは、
- 《結果列》は、WHERE句をパスした各行に対して評価されます。
- 集計クエリでは、
- WHERE句をパスした行がない場合は、全ての列が
NULL
と想定されます。 - 《集計関数》を含まない《結果列》では、いずれか1行の値が使われます。
-
《GROUP BY句》を伴わないと、
- 《結果列》は、全ての行を通して一度だけ評価され、出力は1行に集約されます。
- 《GROUP BY句》を伴うと、
- WHERE句をパスした行がない場合は、全ての列が
結果列
- 式を結果とし、名前を与えます。
〔《名前》.〕*
- 名前に属する全ての列を結果とします。名前は、テーブルかテーブルのエイリアスです。
GROUP BY句
- これは、《クエリコア》の一部です。
WINDOW句
WINDOW《WINDOW名》AS《WINDOW定義》, …
FROM句
FROM《テーブルかサブクエリ》〔《JOIN句》…〕
- 単一のテーブル、または、結合したテーブルを参照します。
テーブルかサブクエリ
《テーブル句》
(《テーブルかサブクエリ》〔《JOIN句》…〕)
(《クエリ文》)〔〔AS〕《名前》〕
JOIN句
- 句に前置したテーブル(左辺)に対して、句内のテーブル(右辺)がデカルト結合されます。
- デフォルトの結合演算子は
INNER JOIN
です。
結合演算子
INNER JOIN
- 《結合制約》に適合しない行が除外されます。
- 制約がなければ、単なるデカルト結合です。
JOIN
-
INNER JOIN
と同じです。
-
INNER JOIN
と同じです。
CROSS JOIN
-
INNER JOIN
と結果的に同じですが、SQLite固有の制約があり、意識して使用する場合を除いて使用を避けることが推奨されています。 - CROSS JOIN を使用したクエリ プランの手動制御 (公式ドキュメント)
LEFT OUTER JOIN
- 左辺の全ての行に対して、《結合制約》に適合した行が追加されます。
- 適合しなくても左辺の行は除外されません。
- 除外されなかった不適合の行では、右辺由来の列は
NULL
になります。
- 制約がなければ、単なるデカルト結合です。
RIGHT OUTER JOIN
- 右辺の全ての行に対して、《結合制約》に適合した行が追加されます。
- 適合しなくても右辺の行は除外されません。
- 除外されなかった不適合の行では、左辺由来の列は
NULL
になります。
- 制約がなければ、単なるデカルト結合です。
FULL OUTER JOIN
- 左右辺の全ての行に対して、《結合制約》に適合した行が追加されます。
- 適合しなくても行は除外されません。
- 除外されなかった不適合の行では、他辺由来の列は
NULL
になります。
- 制約がなければ、単なるデカルト結合です。
NATURAL INNER JOIN
- 暗黙に、テーブル間で同名の列を
USING
に指定した、INNER JOIN
です。 - 共通の列名がなければ、単なるデカルト結合です。
- 《結合制約》は指定できません。
NATURAL JOIN
-
NATURAL INNER JOIN
と同じです。
NATURAL LEFT OUTER JOIN
- 暗黙に、テーブル間で同名の列を
USING
に指定した、LEFT OUTER JOIN
です。 - 共通の列名がなければ、単なるデカルト結合です。
- 《結合制約》は指定できません。
NATURAL RIGHT OUTER JOIN
- 暗黙に、テーブル間で同名の列を
USING
に指定した、RIGHT OUTER JOIN
です。 - 共通の列名がなければ、単なるデカルト結合です。
- 《結合制約》は指定できません。
NATURAL FULL OUTER JOIN
- 暗黙に、テーブル間で同名の列を
USING
に指定した、FULL OUTER JOIN
です。 - 共通の列名がなければ、単なるデカルト結合です。
- 《結合制約》は指定できません。
結合制約
ON《式》
- 式が真になる行のみに制約します。
USING (《列名》, …)
- テーブル間で共通の列名を指定することで、指定した列の値が一致する行のみに制約します。
-
SELECT * FROM X JOIN Y USING (A)
は、SELECT * FROM X JOIN Y ON X.A=Y.A
とほぼ同じですが、ON
の場合はX.A
とY.A
双方が*
に含まれるのに対して、USING
ではX.A
のみが含まれてY.A
は含まれません。-
SELECT *,Y.A FROM X JOIN Y USING (A)
などと、明示的にY.A
を使うことは可能です。
-
文
ATTACH、DETACH文
- データベースを追加接続します。
DETACH〔DATABASE〕《スキーマ名》
- データベースを切断します。
CREATE、DROP文
TABLE
CREATE〔TEMPORARY〕TABLE〔IF NOT EXISTS〕《テーブル》(《列定義》, …〔《テーブル制約》, …〕)〔《テーブルオプション》〕
- テーブルを生成します。
-
TEMPORARY
を指定すると、切断時に保存されない一時テーブルが作られます。 -
IF NOT EXISTS
を指定すると、テーブルが既存の場合に文が無視されます。
テーブルオプション
〔《テーブルオプション》 , 〕WITHOUT ROWID
- 指定すると
ROWID
列が作られません。- 代わりに
PRIMARY KEY
の指定が必須になります。
- 代わりに
〔《テーブルオプション》 , 〕STRICT
- 指定すると列定義にデータ型を省略できません。
- 許容される型は、次のいずれかに限られます。
-
INT
、INTEGER
、REAL
、TEXT
、BLOB
、ANY
-
CREATE〔TEMPORARY〕TABLE〔IF NOT EXISTS〕《テーブル》AS《クエリ文》
- テーブルを生成します。
-
TEMPORARY
を指定すると、切断時に保存されない一時テーブルが作られます。 - クエリ文の出力する《行列》からテーブルが作られます。
- 元の列の値を変えずに使用する場合は、元の列の型に準拠した型が与えられます。
-
TEXT
とREAL
はそのまま適用されます。 -
INTEGER
はINT
に、NUMERIC
はNUM
になります。 -
BLOB
型は「無指定」になります。
-
- 元の列の値を変更した列では、列の型指定は失われ、「無指定」になります。
- 元の列の制約、デフォルト値、照合関数などは全て失われます。
- サブクエリの列定義で
AS
を使って列名を変更することは、副作用なしに可能です。
- 元の列の値を変えずに使用する場合は、元の列の型に準拠した型が与えられます。
DROP TABLE《テーブル》〔IF EXISTS〕
- テーブルを削除します。
-
IF EXISTS
を指定すると、テーブルがない場合に文が無視されます。
INDEX
CREATE〔UNIQUE〕INDEX〔IF NOT EXISTS〕《インデックス》ON《テーブル名》(《索引付き列》, …)〔WHERE《式》〕
- インデックスを生成します。
- WHERE句があると部分インデックスが作られます。
-
UNIQUE
が指定されると、重複が生じようとした際にエラーになります。-
NULL
値は個別の値と見なされるため、エラーになりません。
-
DROP INDEX〔IF EXISTS〕《インデックス》
- インデックスを削除します。
索引付き列
《列名》〔《COLLATE制約》〕〔《順序》〕
《式》〔《COLLATE制約》〕〔《順序》〕
インデックス
インデックス名
TRIGGER
CREATE〔TEMPORARY〕TRIGGER〔IF NOT EXISTS〕〔《スキーマ名》.〕《トリガー名》〔《トリガータイミング》〕《トリガーアクション》ON《テーブル名》〔FOR EACH ROW〕〔WHERE《式》〕BEGIN《トリガー文》; … END
- テーブルに対してトリガーを生成します。
-
TEMPORARY
を指定すると、切断時に保存されない一時トリガーが作られます。 -
FOR EACH ROW
はデフォルトの処理なので省略可能です。-
FOR EACH STATEMENT
はサポートされておらず、他のオプションはありません。
-
CREATE〔TEMPORARY〕TRIGGER〔IF NOT EXISTS〕〔《スキーマ名》.〕《トリガー名》INSTEAD OF《トリガーアクション》ON《ビュー名》〔FOR EACH ROW〕〔WHERE《式》〕BEGIN《トリガー文》; … END
- ビューに対して書き込み時に作用するトリガーを生成します。
- ビューへの書き込み時に生じるエラーが抑制されます。
- 実際に書き込めるようになるわけではなく、《トリガー文》によって元テーブルへの書き込みを代替する形になります。
-
TEMPORARY
を指定すると、切断時に保存されない一時トリガーが作られます。 -
FOR EACH ROW
はデフォルトの処理なので省略可能です。-
FOR EACH STATEMENT
はサポートされておらず、他のオプションはありません。
-
DROP TRIGGER〔IF EXISTS〕〔《スキーマ名》.〕《トリガー名》
- トリガーを削除します。
トリガー名
トリガータイミング
BEFORE
AFTER
INSTEAD OF
トリガーアクション
DELETE
INSERT
UPDATE〔OF《列名》, …〕
トリガー文
トリガー文での制限
- 変更を加える《テーブル》は、《テーブル名》でなければなりません。
- 非一時トリガーでは、同じデータベース内のテーブルだけが使えます。
- 一時トリガーでは、アタッチされている任意のデータベースのテーブルを対象にできます。
-
INSERT INTO table DEFAULT VALUES
書式は使えません。 -
《UPDATE》と《DELETE》では、[《INDEXED BY句》](#indexed by句)(
NOT INDEXED
を含む)は使えません。 - 《WITH句》は使えません。
- 《RAISE関数》を使用することで、トリガー文の実行を中断できます。
RAISE関数
RAISE( IGNORE )
RAISE( ROLLBACK《式》)
RAISE( ABORT《式》)
RAISE( FAIL《式》)
- 《式》は、エラーメッセージになります。
VIEW
CREATE〔TEMPORARY〕VIEW〔IF NOT EXISTS〕〔《スキーマ名》.〕《ビュー名》〔(《列名》, …)〕AS《クエリ文》
- ビューを生成します。
DROP VIEW〔IF EXISTS〕〔《スキーマ名》.〕《ビュー名》
- ビューを削除します。
ビュー名
INSERT文
〔《WITH句》〕《挿入方式》INTO 《テーブル》〔AS《名前》〕〔(《列名》, …)〕《挿入行列》〔《UPSERT句》〕
挿入方式
INSERT
- 新しい行を挿入します。
INSERT OR《競合解決方式》
- 行が既存であった場合に、《競合解決方式》に従います。
- UNIQUE/PRIMARY KEY制約の列がない場合は、重複判定は行われず、常に挿入されます。
REPLACE
-
INSERT OR REPLACE
と同じです。
挿入行列
- 即値を挿入します。
- クエリの結果を挿入します。
DEFAULT VALUES
- デフォルト値を挿入します。
- デフォルト値が未定義の列は
NULL
が使われます。
- デフォルト値が未定義の列は
-
INSERT INTO table DEFAULT VALUES
書式でのみ使用できます。(列指定不可) - 《UPSERT句》は使えません。
- 《GENERATED ALWAYS AS制約》があると使えません。
- トップレベルの《INSERT文》でのみ使用できます。
UPSERT句
ON CONFLICT〔(《索引付き列》, …)〔WHERE《式》〕〕DO《UPSERT処理》
UPSERT処理
NOTHING
- 何も処理しません。
- 制約違反の生じた行を更新します。
- WHERE句は、制約違反の生じた行での更新の可否を制御するもので、対象を絞り込むものではありません。
- WHERE句がなくても、処理対象は、制約違反の生じた1行に限定されています。
- 列名が参照するのは既存行の値です。
- 挿入するはずだった値を参照するには、**
exclude.《列名》
**を使用します。
- 挿入するはずだった値を参照するには、**
UPDATE文
《WITH句》UPDATE〔《競合解決方式》〕《テーブル句》SET《代入式》, …〔《FROM句》〕〔WHERE《式》〕
- 行の特定列を更新します。
- WHERE句が無ければ、全ての行が更新の対象になります。
代入式
DELETE文
《WITH句》DELETE FROM《テーブル句》〔WHERE《式》〕
- 行を削除します。
- WHERE句が無ければ、テーブルの全ての行が削除されます。
クエリ文
単純クエリ
〔《WITH句》〕《クエリコア》〔[《ORDER BY句》](#order by句)〕〔《LIMIT句》〕
複合クエリ
〔《WITH句》〕《クエリコア》《複合クエリ演算子》《クエリコア》…〔《ORDER BY句》〕〔《LIMIT句》〕
- 《複合クエリ演算子》に伴われる《クエリコア》を複数連ねることができます。
- 全ての《クエリコア》の結果は同じ列数でなければなりません。
- 最後の《クエリコア》が《VALUES句》の場合、《ORDER BY句》や《LIMIT句》は使えません。
複合クエリ演算子
全ての演算子は左から結合します。
UNION
- 重複する行を除いて、左辺に右辺を連結して返します。(和集合)
- 必要に応じて行の並べ替えが生じます。
UNION ALL
- 左辺の下に右辺を単純に連結して返します。(和集合)
INTERSECT
- 左辺にも右辺にも存在する行を返します。(積集合)
EXCEPT
- 左辺にあって右辺にない行を返します。(差集合)
ALTER文
テーブル操作
RENAME TO 《名前》
- 《テーブル名》を変更します。
- 《列名》を変更します。
ADD〔COLUMN〕《列定義》
- 列を追加します。
DROP〔COLUMN〕《名前》
- 列を削除します。
TRANSACTION文
BEGIN〔《排他制御》〕TRANSACTION
- 名前のないトランザクションを開始します。
- ネストできません。トランザクション中に開始しようとするとエラーします。
COMMIT TRANSACTION
- 全てのトランザクションを終了し変更を確定します。
END TRANSACTION
-
COMMIT
と同じです。
ROLLBACK〔TRANSACTION〕
- 最初のトランザクション開始時点まで巻き戻します。
SAVEPOINT《セーブポイント名》
- 名前付きのトランザクションを開始します。
- ネストできます。
RELEASE〔SAVEPOINT〕《セーブポイント名》
- セーブポイントを破棄します。
ROLLBACK TO〔SAVEPOINT〕《セーブポイント名》
- セーブポイント開始時点まで巻き戻します。
セーブポイント名
排他制御
DEFERRED
- デフォルトの処理です。
- トランザクションは実際の読み書きまで開始されず、その間は排他制御が行われません。
IMMEDIATE
- 他からの書き込みを排除します。
- 他からの読み出しは排除されません。
EXCLUSIVE
- 他からの書き込みと読み出しを排除します。
REINDEX文
REINDEX〔《照合関数》〕
- データベース全体のインデックスを再構築します。
REINDEX《テーブル》
- テーブルのインデックスを再構築します。
REINDEX《インデックス》
- インデックスを再構築します。
VACUUM文
- データベースを最適化します。
-
INTO
でファイル名を指定すると、元のデータベースには変更を加えずに、別名で保存します。
プラグマ
⇒ PRAGMA Statements (公式ドキュメント)
索引
英数字
数字
A
ABORT
abs(X)
ALTER文
ATTACH、DETACH文
avg(X)
B
C
CASE式
CAST式
changes()
char(X1, X2, …, XN)
CHECK
CHECK列制約
coalesce(X, Y, …)
COLLATE制約
count(X)、count(*)
CREATE、DROP文
cume_dist()
D
date(〔《日時文字列》,《日時修飾子》, …〕)
datetime(〔《日時文字列》,《日時修飾子》, …〕)
DEFAULT制約
DELETE文
dense_rank()
DISINCT (クエリコア)
DISINCT (集計関数)
E
F
FAIL
FILTER句
first_value(expr)
FROM句
G
GENERATED ALWAYS AS制約
GLOB
glob(X, Y)
GROUP BY句
group_concat(X)、group_concat(X, Y)
H
I
ifnull(X, Y)
IGNORE
iif(X, Y, Z)
INDEX (CREATE, DROP文)
INDEXED BY句
INSERT文
instr(X, Y)
INTEGER、REAL
J
JOIN句
JSON関数
julianday(〔《日時文字列》,《日時修飾子》, …〕)
L
lag(expr)、lag(expr, offset)、lag(expr, offset, default)
last_insert_rowid()
last_value(expr)
lead(expr)、lead(expr, offset)、lead(expr, offset, default)
length(X)
LIKE
like(X, Y)、like(X, Y, Z)
LIMIT句
lower(X)、upper(X)
ltrim(X)、ltrim(X, Y)
M
max(X)
max(X, Y, …)、min(X, Y, …)
min(X)
N
NOT NULL
nth_value(expr, N)
ntile(N)
NULL
nullif(X, Y)
NULL式
NULL順序
O
P
PARTITION BY句
percent_rank()
PRIMARY KEY
PRIMARY KEY列制約
printf(FORMAT, …)
Q
R
RAISE関数
random()
randomblob(N)
rank()
REINDEX文
REPLACE
replace(X, Y, Z)
ROLLBACK
round(X)、round(X, Y)
row_number()
rtrim(X)、rtrim(X, Y)
S
SELECTコア
SELECT文
sqlite_version()
strftime(〔《日時書式》,《日時文字列》,《日時修飾子》, …〕)
substr(X, Y, Z)、substr(X, Y)
sum(X)、total(X)
T
TABLE (CREATE, DROP文)
TEXT
time(〔《日時文字列》,《日時修飾子》, …〕)
total_changes()
TRANSACTION文
TRIGGER (CREATE, DROP文)
trim(X)、trim(X, Y)
typeof(X)
U
unicode(X)
UNIQUE
UNIQUE列制約
UPDATE文
UPSERT句
UPSERT処理
V
VACUUM文
VALUES句
VIEW (CREATE, DROP文)
W
WINDOW句
WINDOW定義
WINDOW名
WITH句
X
Z
仮名
あ
か
さ
シフト (ビット)
スカラ
スカラ、リスト、行列
スカラ式
スキーマ名
セーブポイント名
た
データベース関数
テーブル
テーブルオプション
テーブルかサブクエリ
テーブル句
テーブル制約
テーブル操作
テーブル名
トリガーアクション
トリガータイミング
トリガー文
トリガー文での制限
トリガー名
は
はじめに
ビット演算
ビュー名
プラグマ
フレーム
フレーム境界
フレーム型
ら
漢字
あ
か
開始フレーム境界
外部キー制約
型変換
関数
基本構造
競合解決方式
共通テーブル式
共通テーブル構造
行列
行列式
句 (clause)
計算方式
結果列
結合演算子
結合制約
さ
再帰WITH句
再帰WITH句のアルゴリズム
再帰クエリ
索引付き列
算術関数
式
四則演算
集計、分析関数
集計関数
終了フレーム境界
順序
照合関数
初期クエリ
書式制御文字列
真偽値
挿入行列
挿入方式
た
な
名前
二項演算子
日時
日時関数
日時関数の使用例
日時修飾子
日時書式
日時文字列
は
排他制御
比較演算
複合クエリ
複合クエリ演算子
文
分析関数
Discussion