🦑

SQLite3のためのSQLリファレンス

2022/08/24に公開

はじめに

  • この記事では、公式文書「SQL As Understood By SQLite」を元に、リファレンス目的でSQLite3のSQLを整理します。
    • 自分の理解しやすい概念や呼び名に置き換えています。
      • SQLの学習には適しません。初学者に不正確な概念を植え付けるかも知れません。
    • 「SQLiteの理解するSQL」を網羅したものではありません。
      • 自分が使わなさそうな、一部の機能や書式は網羅していません。
      • 他のシステムとの互換性のための構文は多くを省略しています。
    • 仕様書である元ドキュメントとは別物ですのでご留意願います。
    • 用語には定義へのリンクが張られ、記事の末尾には索引を設けてあります。
  • テーブル、レコード、カラム、リレーションなどといったリレーショナルデータベースシステムの概念については扱いません。
  • SQLiteの導入だとか、使い方(SQLiteコマンドを含む)だとかは扱わず、専らSQL言語についてのみ言及します。
  • SQLには詳しくないので、間違いや改善など、ご指摘・ご提案いただけると助かります。
SQLite
用語と書式表現
  • 以下のような用語を使います。
    • 「テーブル」は「行列」と表記することがあります。
    • 「レコード」は「行」と表記することがあります。
    • 「カラム」は「列」と表記することがあります。
    • 「ピアグループ」を「同輩グループ」と表記することがあります。
    • 「演算子」、「式」、「句」などの区分は不正確です。
    • 「文」の部品と「句」の区分は曖昧です。
  • 以下のような構文書式を使います。
    • 「《~》」は、リンク先で別途定義されている要素です。
    • 「〔~〕」は、省略可能な要素です。
    • 「…」は、その前に書かれた部分が反復可能であることを示しますが、繰り返される範囲や回数は曖昧です。
      • 例えば、「《~》~《~》…」と書かれている場合に、直前の要素だけが繰り返されるとは限りません。
    • 見た目の余白をもって、空白文字が省略されている場合があります。
      • 例えば、「《~》~《~》」とある時、「《」の前や「》」の後に空白が必要な場合があります。
    • 項目定義内に複数の書式がある場合は、いずれか一つが選択可能なことを表します。
    • 書式か地の文かは曖昧です。
      • 単独で書式が書かれる場合は、太字で前後に改行があります。
        • しかし、横幅制限で行が折り返したのか、改行があるのかは曖昧です。
      • 地の文の中で書式が書かれる場合は、コード表現が使われます。
    • 一部の書式の表現で、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文字('')で表現します。
    • シングルクォートの代わりにダブルクォート(")も使えます。
  • C言語風のエスケープはありません。
  • エンコードは、UTF-8、UTF-16BE、UTF-16LEのいずれかです。

BLOB

  • 16進数を含む文字列リテラルの前にxまたはXが付きます。
    • 例:X'53514C697465'

NULL

  • NULLは、データがないことを表します。

日時

  • CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPは、UTCで、現在日、現在時、現在日時を表すリテラルです。
  • 《GENERATED ALWAYS AS制約》(いわゆる計算列)では使用できません。

真偽値

真偽値 表記例
TRUE11.00.1-0.1'1english'
FALSENULL0.00'english''0'

スカラ、リスト、行列

スカラ

構造を持たない単一の値です。

《リテラル》
《列》
《スカラ式》

スカラ式

《式》

リスト

(《スカラ》, …)
《リスト式》

リスト式

《式》

行列

《VALUES句》
《テーブル》
《行列式》

行列式

《式》

《リテラル》
《単項演算子》《式》
《式》《二項演算子》《式》
《列》
《関数》
  (《式》)
《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

  • XYともにNULLの場合は真と評価されます。
  • 一方がNULLで他方が非NULLの場合は偽と評価されます。
  • 双方ともNULLでなければ==と等価です。

X IS NOT Y、X IS DISTINCT FROM Y

  • XYともにNULLの場合は偽と評価されます。
  • 一方がNULLで、他方が非NULLの場合は真と評価されます。
  • 双方ともNULLでなければ!=と等価です。

X IN Y、X NOT IN Y

  • Xが、Yに列挙された値の中に存在すれば真と評価されます。
    • XYも、式やサブクエリが書けます。
  • X《スカラ》ならY《リスト》でなければなりません。
  • X《リスト》ならY《行列》でなければなりません。
    • このXは列並びと等価、Yは行並びと等価です。
    • Yの列数はXの要素数と同じでなければなりません。
    • Xには《行列》も書けますが、最初の《リスト》(1行目の列)だけが使われます。
  • 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] abcのいずれかに一致
[a-d] adのいずれかに一致
その他 その文字そのもの
  • 左辺の文字列が右辺のパターンに一致すれば真になります。
  • パターンの特殊文字をエスケープしたい場合は、[~]で囲みます。
    • [~]は「~のいずれか」という意味ですが、1文字だけであれば、その文字そのものであることを表します。
    • 例: 'りんご100%' GLOB '*100[%]'は真です。
  • 大文字と小文字は区別されます。
  • 《glob関数》でも同等のことができます。

BETWEEN式

x BETWEEN y AND z

  • xyz《式》です。
  • 左辺の値が中辺と右辺の範囲内にあれば真になります。
  • x BETWEEN y AND zは、xが一度しか評価されないことを除きx >= y AND x <= zと等価です。

CASE式

  • 条件によって選択された値になります。
  • 左から順に評価され、最初に条件が成立した時点で、以降は評価されません。
  • 以下の例のように、二種類の書式があります。
以下は("x"の評価回数以外は)等価
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"式と"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式

CAST(《式》AS《名前》)

  • 式の値を指定の型に変換します。

関数

《一般関数》
《集計関数》
《分析関数》

一般関数

《名前》(《式》, …)

算術関数

max(X, Y, …)、min(X, Y, …)

  • 複数の引数の中から最大の値あるいは最小の値を返します。
  • 引数にNULLが含まれるとNULLを返します。

round(X)、round(X, Y)

  • Xの小数部をY桁に丸めた浮動小数点数を返します。
  • Yを省略すると0が使われます。

abs(X)

  • 引数Xの絶対値を返します。
  • XNULLならNULLを、数値にできない場合は0.0を返します。
  • Xが整数-9223372036854775808の場合は、該当の正の64ビット2の補数値がないため、例外「整数オーバーフロー」を投じます。

random()

  • 整数-9223372036854775808+9223372036854775807の範囲で疑似乱数を返します。

randomblob(N)

  • 疑似乱数で埋めたNバイトのBLOBを返します。
  • N1未満なら、1バイトを返します。
無作為識別子を生成する例
SELECT hex(randomblob(16)); --> '79CF5C2FC33D2A96CEF644484A42745D' (乱数なので毎回変わります)
SELECT lower(hex(randomblob(16))); --> '79cf5c2fc33d2a96cef644484a42745d' (乱数なので毎回変わります)

文字列、BLOB関数

hex(X)

  • 引数をBLOBとして解釈し、大文字で16進ダンプした文字列を返します。

length(X)

  • XTEXTなら、最初のNUL文字までの文字数(バイト数ではない)を返します。
    • SQLiteの文字列には、通常NUL文字は含まれません。
  • XBLOB値なら、バイト数を返します。
  • XNULLなら、NULLを返します。
  • Xが数値なら、数値を文字列で表現した際の長さを返します。

lower(X)、upper(X)

  • すべてのASCII文字が小文字あるいは大文字に変換されたXのコピーを返します。

replace(X, Y, Z)

  • 文字列X中の全てのYZに置き換えた文字列を返します。
  • Yが空の文字列なら、元のままのXを返します。
  • 文字列でないZは、あらかじめUTF-8文字列に変換されます。

substr(X, Y, Z)、substr(X, Y)

  • 左端を1番目として、XY番目からZ文字の部分文字列を返します。
  • Zを省略すると、Yから文字列の最後までを返します。
  • Yが負なら、abs(Y)は右端を1番目とする位置を示します。
  • Zが負なら、Y番目の文字の前にあるabs(Z)文字が返されます。
  • XBLOBの場合は、インデックスはバイトを参照します。
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 XY LIKE X ESCAPE Zと同等です。
  • 《LIKE演算子》とは、XYが逆になっていることに注意してください。
  • Xがパターンで、Yがそのパターンと照合する文字列です。
以下の"like"関数と"LIKE"式は等価
like('%neon%',name) == (name LIKE '%neon%')

glob(X, Y)

  • Y GLOB Xと同等です。
  • 《GLOB演算子》とは、XYが逆になっていることに注意してください。
  • Xがパターンで、Yがそのパターンと照合する文字列です。
以下の"glob"関数と"GLOB"式は等価
glob('*helium*',name) == (name GLOB '*helium*')

instr(X, Y)

  • 文字列X内で最初に見つかった文字列Yの位置を、左端を1とする番号で返します。見つからない場合は0を返します。
  • XYが両方とも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)QNULL
w 引数がNULLの時の挙動は%qと同じ、それ以外では%sと同じ
length 意味 (整数に対してのみ有効)
<n> 32bit
<n>l 32bit
<n>ll 64bit
flag 意味
- パディングなしの左寄せ、デフォルトは右寄せ
+ 符号付きの正数に+記号を前置
space 符号付きの正数に1つのスペースをを前置
0 パディング文字に0に使用
, 3桁区切りのカンマ
# %g%Gの末尾のゼロを削除、浮動小数点数の小数点を表示
%o%x%Xの値の前に00x0Xを付加
(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)

  • XNULLなら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に対して、その型を表す文字列を返します。
  • XNULLであれば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)のテーブルを生成します。
  • 最初の行のkeyNULLvalue元のオブジェクトになり、以降に階層構造が展開されます。

集計、分析関数

関数名 一般関数
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の平均値を浮動小数点数で返します。
  • 数値に変換できない値やBLOB0として解釈されます。
  • 全ての値が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)

  • グループ内のXYをラベルと値として生成した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_rows1なら0.0を返します。

cume_dist()

  • row_number / partition_rowsで算出される累積分布を返します。
  • row_numberは、同輩グループの最後の行のrow_number()で、partition_rowsはパーティション内の行数です。
    • partition_rows0なら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]*/ \small{(英字か下線で始まり、英数字か下線が続く文字列)}

  • この書式では、予約語は使用できません。

《TEXT》

  • この書式では、予約語を含む自由度の高い名付けが可能です。

RegExp/\[[^\]]+\]/] \small{(角括弧で挟まれた文字列)}

  • この書式では、予約語を含む自由度の高い名付けが可能です。

スキーマ名

《名前》

  • データベースの名前です。

テーブル

《スキーマ名》.〕《テーブル名》

テーブル名

《名前》

テーブル句

《テーブル》〔AS《名前》〕〔[《INDEXED BY句》](#indexed by句)〕

INDEXED BY句

INDEXED BY《インデックス名》

  • 指定された名前付きインデックスを使用するように強制します。

NOT INDEXED

  • インデックスを使用しないように強制します。

《テーブル》.〕《列名》

列名

《名前》

列定義

《列名》《名前》〕〔《列制約》…〕

  • 型名を表す《名前》には、名前であること以外に制約はありません。
  • どのような型名の列であっても、全ての型の値を受け入れます。
    • ただし、列の型名は、入出力時に生じる既定の型への変換のヒントに使用されます。

列制約

DEFAULT制約

DEFAULT《リテラル》
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〕

UNIQUE列制約

UNIQUE《コンフリクト句》

CHECK列制約

CHECK (《式》)

NOT NULL

NOT NULL《コンフリクト句》

  • 列がNULL値を含まないように制限します。

テーブル制約

PRIMARY KEY

PRIMARY KEY (《列名》, …)《コンフリクト句》

  • テーブルで唯一の主キーとなる列を設定します。
    • 列または列の組み合わせはUNIQUE制約を受けます。
    • PRIMARY KEYINTEGER型の単独列だと…
      • ROWID列が置き換えられます。
      • AUTOINCREMENTの指定がなくても自動的に増加します。
      • AUTOINCREMENTを指定すると欠番の再利用を行わなくなります

UNIQUE

UNIQUE (《列名》, …)《コンフリクト句》

  • 列または列の組み合わせの値の重複を制限します。

CHECK

CHECK (《式》, …)

  • 式で列または列の組み合わせの値を制限します。
    • 式の評価値が'0'だと制約に違反しているものと見なされます。
      • NULLは違反になりません。
  • 式にサブクエリを使うことはできません。

外部キー制約

FOREIGN KEY (《列名》, …)《外部キー句》

  • 別テーブルに存在する値のみに制限します。
  • 外部キー制約を使用するためには、《プラグマ》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)
  • 初行の値の型が《TEXT》だとそのまま列名になり、それ以外の列名はcolumn《列番号》になるようです。

コンフリクト句

〔ON CONFLICT《競合解決方式》

  • 制約違反が発生した場合の処理を指定します。
  • 指定が無いか、指定の方法が不可能な場合は、ABORTで処理されます。

競合解決方式

ROLLBACK

  • 現在のトランザクションをロールバックします。

ABORT

  • エラーを投げて、現在の文の実行前に戻しますが、それまでの文の結果は残ります。
    • ひとつの文で複数行を変更している場合、それまでに違反なく変更された行も元に戻ります。
  • デフォルトの処理です。

FAIL

  • エラーを投げて、発生時の状態を維持して中断します。
    • ひとつの文で複数行を変更している場合、それまでに違反なく変更された行は残りますが、以降の行は処理されません。
  • UNIQUEPRIMARY KEYの一意性、NOT NULLCHECKに対する制約違反にのみ有効で、他はABORTで処理されます。

IGNORE

  • 制約違反の生じた行をスキップして、後続行の処理を続行します。
    • ひとつの文で複数行を変更している場合、違反のあった行だけが無視され、前後の有効な変更は残ります。
  • UNIQUEPRIMARY 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 《式》

  • 出力する行数を式の値までに制限します。
  • 負値だと無制限です。

LIMIT 《式》OFFSET《式》

  • 第2式の行数をスキップした上で、最大第1式の行数を出力します。

LIMIT 《式》,《式》

  • 第1式の行数をスキップした上で、最大第2式の行数を出力します。

OVER句

指定のウインドウ越しに式を評価します。

OVER《WINDOW名》

OVER《WINDOW定義》

  • 即値で定義したウインドウを使用します。
  • 《WINDOW句》で定義されたウインドウを元にすることもできます。

WINDOW名

《名前》

WINDOW定義

(〔《WINDOW名》〕〔《PARTITION BY句》〕〔《ORDER BY句》〕〔《フレーム》〕)

PARTITION BY句

PARTITION BY《式》, …

フレーム

《フレーム型》《フレーム境界》《EXCLUDE句》

  • 参照の対象を指定します。デフォルトは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句の例 ("SELECT&nbsp;'Hello&nbsp;world!';"と同じ結果)
WITH tmp(col1) AS (VALUES ('Hello world!'))
SELECT * FROM tmp;

共通テーブル式

《共通テーブル構造》AS (《クエリ文》)

共通テーブル構造

《テーブル名》(《列名》, …)

  • 共通テーブルの列構造を定義します。

再帰WITH句

WITH〔RECURSIVE〕《共通テーブル構造》AS (《初期クエリ》UNION〔ALL〕《再帰クエリ》)

  • サブクエリを再帰的に処理して単一の共通テーブルを生成します。
  • UNIONUNION ALLの違いは、《複合クエリ演算子》の場合と同様です。
シンプルな再帰WITH句の例 (1~10の数列を生成)
WITH tmp(num) as (SELECT 1 UNION ALL SELECT num + 1 FROM tmp WHERE num < 10)
SELECT * FROM tmp;

初期クエリ

《クエリ文》

再帰クエリ

《単純クエリ》

再帰WITH句のアルゴリズム

  1. i=0とします。
  2. 初期クエリを実行し、結果をT_iとします。
  3. 直前のクエリの結果がNULLでない間、以下を繰り返します。
    1. i=i+1とします。
    2. 共通テーブルの内容を直前のクエリの結果で置き換えます。
    3. 再帰クエリを実行し、結果をT_iとします。
  4. 共通テーブルの内容を、T_0~T_iを演算子UNION〔ALL〕で結合したもので置き換えます。
再帰WITH句の例 (フィボナッチ数列の生成)
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;

クエリコア

《VALUES句》

SELECT〔DISTINCT〕《結果列》, …〔《FROM句》〕〔WHERE《式》〕〔《GROUP BY句》〕〔《WINDOW句》

  • テーブルから生成された《行列》です。
  • DISTINCTが指定されると、重複した行が集約されます。
    • 指定が無ければ、重複を含めた全ての行(ALL)になります。
      • 上記書式では省略していますが、ALLを明記することもできます。
  • 非集計クエリでは、
    • 《結果列》は、WHERE句をパスした各行に対して評価されます。
  • 集計クエリでは、
    • WHERE句をパスした行がない場合は、全ての列がNULLと想定されます。
    • 《集計関数》を含まない《結果列》では、いずれか1行の値が使われます。
    • 《GROUP BY句》を伴わないと、
      • 《結果列》は、全ての行を通して一度だけ評価され、出力は1行に集約されます。
    • 《GROUP BY句》を伴うと、
      • WHERE句をパスした行はキー列の値によってグループに分けられ、《結果列》はグループ毎に評価されます。
      • HAVING句《式》の値が偽であるグループは破棄されます。
        • 《式》には集計関数も使用できます。
        • 全てのグループが破棄されると、出力はゼロ行になります。

結果列

《式》〔〔AS〕《名前》

  • 式を結果とし、名前を与えます。

《名前》.〕*   \small{(アスタリスク1文字)}

  • 名前に属する全ての列を結果とします。名前は、テーブルかテーブルのエイリアスです。

GROUP BY句

GROUP BY《式》, …〔HAVING《式》

WINDOW句

WINDOW《WINDOW名》AS《WINDOW定義》, …

FROM句

FROM《テーブルかサブクエリ》《JOIN句》…〕

  • 単一のテーブル、または、結合したテーブルを参照します。

テーブルかサブクエリ

《テーブル句》
  (《テーブルかサブクエリ》《JOIN句》…〕)
  (《クエリ文》)〔〔AS〕《名前》

JOIN句

《結合演算子》《テーブルかサブクエリ》《結合制約》

  • 句に前置したテーブル(左辺)に対して、句内のテーブル(右辺)がデカルト結合されます。
  • デフォルトの結合演算子はINNER JOINです。

結合演算子

INNER JOIN

  • 《結合制約》に適合しない行が除外されます。
  • 制約がなければ、単なるデカルト結合です。

JOIN

  • INNER JOINと同じです。

  \large{,}   \small{(カンマ)}

  • INNER JOINと同じです。

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.AY.A双方が*に含まれるのに対して、USINGではX.Aのみが含まれてY.Aは含まれません。
    • SELECT *,Y.A FROM X JOIN Y USING (A)などと、明示的にY.Aを使うことは可能です。

ATTACH、DETACH文

ATTACH〔DATABASE〕《式》AS《スキーマ名》

  • データベースを追加接続します。

DETACH〔DATABASE〕《スキーマ名》

  • データベースを切断します。

CREATE、DROP文

TABLE

CREATE〔TEMPORARY〕TABLE〔IF NOT EXISTS〕《テーブル》(《列定義》, …〔《テーブル制約》, …〕)〔《テーブルオプション》

  • テーブルを生成します。
  • TEMPORARYを指定すると、切断時に保存されない一時テーブルが作られます。
  • IF NOT EXISTSを指定すると、テーブルが既存の場合に文が無視されます。

テーブルオプション

〔《テーブルオプション》 , 〕WITHOUT ROWID

  • 指定するとROWID列が作られません。
    • 代わりにPRIMARY KEYの指定が必須になります。

〔《テーブルオプション》 , 〕STRICT

  • 指定すると列定義にデータ型を省略できません。
  • 許容される型は、次のいずれかに限られます。
    • INTINTEGERREALTEXTBLOBANY

CREATE〔TEMPORARY〕TABLE〔IF NOT EXISTS〕《テーブル》AS《クエリ文》

  • テーブルを生成します。
  • TEMPORARYを指定すると、切断時に保存されない一時テーブルが作られます。
  • クエリ文の出力する《行列》からテーブルが作られます。
    • 元の列の値を変えずに使用する場合は、元の列の型に準拠した型が与えられます。
      • TEXTREALはそのまま適用されます。
      • INTEGERINTに、NUMERICNUMになります。
      • 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《列名》, …〕

トリガー文

《UPDATE文》
《INSERT文》
《DELETE文》

トリガー文での制限
  • 変更を加える《テーブル》は、《テーブル名》でなければなりません。
  • 非一時トリガーでは、同じデータベース内のテーブルだけが使えます。
  • 一時トリガーでは、アタッチされている任意のデータベースのテーブルを対象にできます。
  • 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と同じです。

挿入行列

《VALUES句》

  • 即値を挿入します。

《クエリ文》

  • クエリの結果を挿入します。

DEFAULT VALUES

  • デフォルト値を挿入します。
    • デフォルト値が未定義の列はNULLが使われます。
  • INSERT INTO table DEFAULT VALUES書式でのみ使用できます。(列指定不可)
  • 《UPSERT句》は使えません。
  • 《GENERATED ALWAYS AS制約》があると使えません。
  • トップレベルの《INSERT文》でのみ使用できます。

UPSERT句

ON CONFLICT〔(《索引付き列》, …)〔WHERE《式》〕〕DO《UPSERT処理》

  • 制約違反で挿入に失敗した場合の処理を規定します。
  • 前の《クエリ文》《結合制約》と区別できなくて構文エラーになる場合は、直前にWHERE TRUEを置きます。

UPSERT処理

NOTHING

  • 何も処理しません。

UPDATE SET《代入式》, …〔WHERE《式》

  • 制約違反の生じた行を更新します。
    • 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句》

複合クエリ演算子

全ての演算子は左から結合します。

UNION

  • 重複する行を除いて、左辺に右辺を連結して返します。(和集合)
  • 必要に応じて行の並べ替えが生じます。

UNION ALL

  • 左辺の下に右辺を単純に連結して返します。(和集合)

INTERSECT

  • 左辺にも右辺にも存在する行を返します。(積集合)

EXCEPT

  • 左辺にあって右辺にない行を返します。(差集合)

ALTER文

ALTER TABLE《テーブル》《テーブル操作》

テーブル操作

RENAME TO 《名前》

RENAME〔COLUMN〕《名前》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文

VACUUM〔《スキーマ名》〕〔INTO《式》

  • データベースを最適化します。
  • INTOでファイル名を指定すると、元のデータベースには変更を加えずに、別名で保存します。

プラグマ

PRAGMA Statements (公式ドキュメント)

索引

英数字
数字

16進表記

A

ABORT
abs(X)
ALTER文
ATTACH、DETACH文
avg(X)

B

BETWEEN式
BLOB

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

EXCLUDE句
EXISTS句

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

hex(X)

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

ORDER BY句
ORDER項目
OVER句

P

PARTITION BY句
percent_rank()
PRIMARY KEY
PRIMARY KEY列制約
printf(FORMAT, …)

Q

quote(X)

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

X IN Y、X NOT IN Y

Z

zeroblob(N)

仮名

インデックス
インデックス名

クエリコア
クエリ文
コメント
コンフリクト句

シフト (ビット)
スカラ
スカラ、リスト、行列
スカラ式
スキーマ名
セーブポイント名

データベース関数
テーブル
テーブルオプション
テーブルかサブクエリ
テーブル句
テーブル制約
テーブル操作
テーブル名
トリガーアクション
トリガータイミング
トリガー文
トリガー文での制限
トリガー名

はじめに
ビット演算
ビュー名
プラグマ
フレーム
フレーム境界
フレーム型

リスト
リスト式
リテラル

漢字

一般関数
演算子

開始フレーム境界
外部キー制約
型変換
関数
基本構造
競合解決方式
共通テーブル式
共通テーブル構造
行列
行列式
句 (clause)
計算方式
結果列
結合演算子
結合制約

再帰WITH句
再帰WITH句のアルゴリズム
再帰クエリ
索引付き列
算術関数

四則演算
集計、分析関数
集計関数
終了フレーム境界
順序
照合関数
初期クエリ
書式制御文字列
真偽値
挿入行列
挿入方式

代入式
単項演算子
単純WITH句
単純クエリ

名前
二項演算子
日時
日時関数
日時関数の使用例
日時修飾子
日時書式
日時文字列

排他制御
比較演算
複合クエリ
複合クエリ演算子

分析関数

文字列、BLOB関数
文字列演算


列制約
列定義
列名
論理演算
論理演算 (ビット)
論理関数

Discussion