部分コード（部分インデックス）
インデックスを作成するときに、インデックスに含まれる行の条件を指定できます。たとえば、一方の列が空ではなく、もう一方の列が指定された値と等しい場合などです。create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index式のインデックス（式のインデックス）
テーブルに対するクエリで式が頻繁に使用される場合は、その式にインデックスを作成できます。ただし、オプティマイザはあまり柔軟ではありませんが、式の列を並べ替えると、インデックスの使用が拒否されることに注意してください。create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table計算された列（生成された列）
列データが他の列の式を評価した結果である場合は、仮想列を作成できます。VIRTUAL（テーブルが読み取られてスペースを占有しないたびに計算される）とSTORED（テーブルにデータを書き込んでスペースを占有するときに計算される）の2つのタイプがあります。もちろん、そのような列に直接データを書き込むことはできません。create table tab1 (
a integer primary key,
b int,
c text,
d int generated always as (a * abs(b)) virtual,
e text generated always as (substr(c, b, b + 1)) stored
);Rツリーインデックス
インデックスは、オブジェクトの値/ネストの範囲での高速検索を目的としています。長方形のオブジェクトがその位置とサイズで指定され、現在のオブジェクトと交差するすべてのオブジェクトを見つける必要がある場合の、ジオシステムで一般的なタスク。このインデックスは仮想テーブルとして実装され（以下を参照）、これは本質的にのみインデックスです。R-Treeインデックスのサポートには、フラグを使用してSQLiteを構築する必要がSQLITE_ENABLE_RTREEあります（デフォルトではチェックされていません）。
create virtual table idx_rtree using rtree (
id, --
minx, maxx, -- c x
miny, maxy, -- c y
data --
);
insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778);
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);
select id from idx_rtree
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00 and maxy <= 35.44;
列の名前を変更する
SQLiteはテーブルの構造の変更を十分にサポートしていないため、テーブルの作成後に制約を変更したり、列を削除したりすることはできません。バージョン3.25.0以降、列の名前を変更することはできますが、タイプを変更することはできません。alter table tbl1 rename column a to b;他の操作については、必要な構造のテーブルを作成し、そこにデータを転送し、古いテーブルを削除して、新しいテーブルの名前を変更するためのすべてが提案されます。
行を追加します。それ以外の場合は更新します（アップサート）
on conflict演算子クラスを使用してinsert、新しい行を追加できます。キーごとに同じ値の行が既にある場合は、それを更新します。
create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial')
on conflict (word) do update set count = count + 1;ステートメントからの更新
別のテーブルのデータに基づいて行を更新する必要がある場合は、以前は各列またはにサブクエリを使用する必要がありましたwith。バージョン3.33.0以降、演算子はupdateキーワードで拡張され、fromこれを実行できるようになりました
update inventory
set quantity = quantity - daily.amt
from (select sum(quantity) as amt, itemid from sales group by 2) as daily
where inventory.itemid = daily.itemid;CTEクエリ、（共通テーブル式）を持つクラス
このクラスwithは、リクエストの一時的な表現として使用できます。バージョン3.34.0では、withinsideの使用の可能性が宣言されましたwith。
with tab2 as (select * from tab1 where a > 10),
tab3 as (select * from tab2 inner join ...)
select * from tab3;
キーワードの追加により
recursive、withそれはあなたが、関連するデータを操作したいクエリに使用することができます。
--
with recursive cnt(x) as (
values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;
--
create table tab1 (id, parent_id);
insert into tab1 values
(1, null), (10, 1), (11, 1), (12, 10), (13, 10),
(2, null), (20, 2), (21, 2), (22, 20), (23, 21);
--
with recursive tc (id) as (
select id from tab1 where id = 10
union
select tab1.id from tab1, tc where tab1.parent_id = tc.id
)
--
with recursive tc (id, parent_id) as (
select id, parent_id from tab1 where id in (12, 21)
union
select tc.parent_id, tab1.parent_id
from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;
-- , .
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null),
('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'),
('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');
with recursive
under_alice (name, level) as (
values('Alice', 0)
union all
select org.name, under_alice.level + 1
from org join under_alice on org.boss = under_alice.name
order by 2
)
select substr('..........', 1, level * 3) || name from under_alice;ウィンドウ機能（ウィンドウ機能）
バージョン3.25.0以降、ウィンドウ関数（分析関数とも呼ばれる）がSQLiteで使用可能になり、データ（ウィンドウ）に対して計算を実行できるようになりました。--
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;
--
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
(2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'),
(5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');
--
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;
-- (, c)
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;
--
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;
SQLiteユーティリティ
sqlite3 CLIに加えて、さらに2つのユーティリティを使用できます。最初の--sqldiffを使用すると、データベース（または別のテーブル）を構造だけでなくデータごとに比較できます。2番目のsqlite3_analizerは、データベースファイル内のテーブルとインデックスによってスペースがどのように効果的に使用されているかに関する情報を表示するために使用されます。同様の情報をdbstat仮想テーブルから取得できます（SQLITE_ENABLE_DBSTAT_VTABSQLiteをコンパイルするときにフラグが必要です）。
バージョン3.22.0以降、CLI sqlite3には、入力されているクエリに追加するインデックスを指定できる（実験的な）.expertコマンドが含まれています。
VacuumIntoバックアップを作成する
バージョン3.27.0以降、コマンドはvacuumキーワードintoで拡張され、SQLから直接停止することなくデータベースのコピーを作成できるようになりました。それはへの簡単な代替案であるバックアップAPI。
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';Printf関数
この関数はC関数に類似しています。この場合、NULL-値は、空の文字列として解釈さ%sと0数のプレースホルダ。
select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0
日時
SQLite DateおよびTime。これらのタイプの列でテーブルを作成することは可能ですが、タイプを指定せずに列を作成するのと同じであるため、そのような列のデータはテキストとして保存されます。これはデータを表示するときに便利ですが、検索が効果的でない、インデックスがない場合、データが多くのスペースを占有する、タイムゾーンがないなど、いくつかの欠点があります。これを回避するために、データをunix時間として保存できます。1970年1月1日午前0時からの秒数。
select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); -->
select strftime('%s', 'now'); -- Unix-
select strftime('%s', 'now', '+2 day'); --> unix-
-- unix- - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')ジェイソン
バージョン3.9.0以降、SQLiteでjsonを操作できます（SQLITE_ENABLE_JSON1コンパイル時フラグまたはロードされた拡張機能のいずれかが必要です）。Jsonデータはテキストとして保存されます。関数の結果もテキストです。
select json_array(1, 2, 3); --> [1,2,3] ()
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} ()
select json_extract('{"a":[2,5],"b":10}', '$.a[0]'); --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} ()
select value from json_each(json_array(2, 5)); --> 2 2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] ()全文検索
jsonと同様に、フルテキスト検索ではSQLITE_ENABLE_FTS5、拡張機能をコンパイルまたはロードするときにフラグを設定する必要があります。検索を操作するには、最初にインデックス付きフィールドを持つ仮想テーブルを作成し、次に通常のテーブルを使用してデータをそこにロードしinsertます。拡張機能はその作業のために追加のテーブルを作成し、作成された仮想テーブルはそれらのデータを使用することに留意する必要があります。
create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender body fts5
拡張機能
SQLite機能は、ロード可能なモジュールを介して追加できます。それらのいくつかはすでに上で言及されています-json1とfts。拡張機能は、ユーザー定義関数（たとえば
crc32、スカラー関数だけでなく、集約関数やウィンドウ関数も）と仮想テーブルの両方を追加するために使用できます。仮想テーブルはデータベースに存在するテーブルですが、それらのデータは拡張機能によって処理されますが、実装によっては、作成が必要なものもあります。
create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;その他、いわゆるテーブル値は、すぐに使用できます。
select value from generate_series(5, 100, 5);仮想テーブルの一部をここに示します。
1つの拡張機能で、関数と仮想テーブルの両方を実装できます。たとえば、json1には、13個のスカラー関数と2個の集計関数、および2個の仮想テーブル
json_eachとが含まれていjson_treeます。独自の関数を作成するには、Cの基本的な知識があり、SQLiteリポジトリから拡張コードを解析する必要があります。独自の仮想テーブルの実装はもう少し複雑です（明らかに、仮想テーブルがほとんどないのはそのためです）ここでは、Jay A. Kreibichによる少し時代遅れの本UsingSQLite、Michael Owensによる記事、リポジトリからのテンプレート、およびテーブル値関数としてのgenerate_seriesコードをお勧めします。
さらに、拡張機能は、ファイルシステムなど、オペレーティングシステム固有のものを実装して、移植性を提供できます。詳細はこちらをご覧ください。
その他
- 使用
'文字列定数のために（単一引用符）と"、列名とテーブル名のために（二重引用符）。 - テーブルtab1に関する情報を取得するには、次を使用できます。
-- main select * from pragma_table_info('tab1'); -- temp (attach) select * from pragma_table_info('tab1') where schema = 'temp' - SQLiteには独自の公式フォーラムがあり、SQLiteの作成者であるRichard Hippが参加し、バグレポートを投稿できます。
- SQLiteエディター：SQLite Studio、SQLite用のDBブラウザー、および（広告！）Sqlite-gui（Windowsのみ）。