はじめに
CLI の基本
単一のファイルへデータベース全体を保存するのが、SQLite です。必要に応じて .sqlite/.db のファイルを開くか作るのが、sqlite3 の CLI です。MySQL/PostgreSQL と違い、サーバーのプロセスがなく — アプリケーションが、SQLite のライブラリへ直接リンクします。バックアップや移行のために、テキストの SQL としてデータベース全体をエクスポートするのが、.dump です。
# open/create a database file
sqlite3 mydb.sqlite
# at the sqlite> prompt
.help # show help
.tables # list tables
.schema users # show CREATE statement for users
.databases # list attached databases
.dump # dump entire DB as SQL text
.quit # exit
# execute SQL from a file
sqlite3 mydb.sqlite < script.sql
# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"ドットコマンド
sqlite3 の CLI が解釈するものであり、SQL エンジンがではないのが、ドットコマンドです。セミコロンを必要とせず、プログラミング API 経由では利用できません。出力の整形を制御するのが、.mode です(読みやすい対話使用のためには、.header on 付きの「column」を試しましょう)。CREATE 文を示すのが、.schema で、インデックス、ビュー、トリガーを加えるのが、.fullschema です。発見には、.help を。
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help -- list all dot commands
.tables -- list all tables
.schema -- schema for all tables
.schema users -- schema for the users table
.fullschema -- schema incl. indexes, triggers, views
.indices users -- list indexes on users
.header on -- show column headers
.mode column -- column-aligned output
.mode list -- default, pipe-separated
.mode csv -- CSV output
.mode json -- JSON output
.show -- show current settings
.width 10 20 30 -- set column widths
.timer on -- show query execution timeデータベースファイルの管理
1 つの接続が、スキーマとして(main、temp、加えて attached)、最大 10 のデータベースファイルへ同時にクエリできるのが、ATTACH DATABASE です。閉じると消える、純粋なインメモリのデータベースを作るのが、:memory: の URI です — テストや一時的な計算に理想的です。読み取り専用、共有キャッシュ、その他のモードを可能にするのが、(file:... の)URI のファイル名です。独立したファイルが、ディスク上の各 attached された DB です。
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;
-- list attached databases (main + attached)
.databases
-- the in-memory database (never written to disk)
sqlite3 :memory:
-- temporary database (deleted when connection closes)
sqlite3 "" -- empty arg => temp DB
-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly
-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;ヘッダーと出力の整形
CLI だけに影響し、決してデータ自体には影響しないのが、出力の整形です。人間にとって最も読みやすいのが、.headers on 付きの「column」モードです。シェルのパイプラインに最良なのが、「list」(デフォルト)です。手軽な変換器にするのが、「json」と「csv」です。結果をファイルへリダイレクトするのが、.output で、.output stdout で戻すのを覚えましょう。ASCII の罫線を描くのが、.mode box/table です。
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;
-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;
-- boxed table output (sqlite 3.36+)
.mode box
.mode table
-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;
-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdoutスクリプトと SQL ファイ ルの実行
開いた CLI セッションの内側で SQL のファイルを実行するのが、.read で、< によるリダイレクトが、対話プロンプトの前に実行させます。最初のエラーでバッチを止めるのが、--bail です(さもなければ、SQLite が続け、失敗が覆い隠され得ます)。文字列の補間を避け、シェルから名前付きパラメータを bind するのが、.parameter(3.44+)です。開発では、.echo on 付きでスクリプトを必ず整合性確認しましょう。
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql
-- inside the CLI
.read setup.sql
-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql
-- echo commands as they run
.echo on
.read migrations.sql
-- show EXPLAIN before each statement (debug)
.explain on
-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
-cmd ".parameter set @min 18" \
"SELECT name FROM users WHERE age >= @min"テーブルとデータ型
テーブルの作成
rowid の別名であるのが、INTEGER PRIMARY KEY です。自動増分し、可能な限り最速のキーです。削除された ID が決して再利用されないよう、アルゴリズムを変えるのが、AUTOINCREMENT です(わずかに遅く、通常不要です)。datetime('now') のような任意の式を可能にするのが、DEFAULT (expr) です。クエリの結果を事前ロードしたテーブルを作るのが、CREATE TABLE AS SELECT ですが、制約やインデックスをコピーしません。
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 0),
role TEXT NOT NULL DEFAULT 'user',
created TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
total REAL NOT NULL DEFAULT 0,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- quick table from a query
CREATE TABLE archive AS
SELECT * FROM orders WHERE created < '2024-01-01';ストレージクラスと型の親和性
動的型付けを使うのが、SQLite です。どの列も、5 つのストレージクラスのいずれかを保持できます。可能なとき値を変換しようとするのが(INTEGER 列の「42」が、整数 42 になるなど)、「type affinity」ですが、不一致で決してエラーを出しません。列ごとの従来の厳密な型チェックを復元するのが、STRICT のテーブル(3.37+)です。実際に保存されたクラスを報告するのが、typeof() です。強力だが規律を要求するのが、この柔軟性です。データの整合性が効く新しいスキーマには、STRICT を。
-- SQLite has 5 storage classes (not strict types):
-- NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo... -- not supported
-- columns have 'type affinity', not enforced types
CREATE TABLE t (
a INTEGER, -- affinity INTEGER
b TEXT, -- affinity TEXT
c REAL, -- affinity REAL
d BLOB, -- affinity BLOB (no conversion)
e -- no affinity (accepts anything)
);
-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t; -- 'text'
-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1); -- errorよくあるデータ型
ネイティブの DATE/TIME や BOOLEAN の型が SQLite になく — ソート可能性のため、タイムスタンプは ISO-8601 の TEXT(「YYYY-MM-DD HH:MM:SS」)として保存し、操作するには日付/時刻の関数を使いましょう。整数の 0/1 であるのが、真偽値です。無視されるのが(互換性のためだけです)、VARCHAR(n) の長さの上限です。最初に INTEGER を試み、その後 REAL で、可能なとき正確な小数を保つのが、NUMERIC の親和性です。生のバイトをそのまま保存するのが、BLOB です。
-- integers
CREATE TABLE numerics (
small INTEGER, -- 1/2/4/8 bytes depending on value
big INTEGER, -- up to 64-bit
-- no separate BIGINT/SMALLINT, all map to INTEGER affinity
flag BOOLEAN -- stored as 0 (false) or 1 (true)
);
-- text & blobs
CREATE TABLE blobs (
name TEXT, -- variable-length UTF-8, no length limit
data BLOB, -- raw bytes, no conversion
note VARCHAR(255) -- length ignored, affinity TEXT
);
-- real numbers
CREATE TABLE floats (
price REAL, -- 8-byte IEEE float
precise NUMERIC(10,2) -- affinity NUMERIC, decimal preserved if exact
);
-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
ts TEXT -- 'YYYY-MM-DD HH:MM:SS' recommended
);テーブルの変更
サーバーレスのエンジンのために意図的に限定されているのが、SQLite の ALTER TABLE です。O(1) であるのが(テーブルの書き換えなし)、ADD COLUMN です。モダンな便利機能が、RENAME COLUMN(3.25+)と、DROP COLUMN(3.35+)です。型の変更や制約の追加のためには、12 ステップのテーブル再構築のパターンを使いましょう(作成、コピー、削除、改名、インデックスの再構築)。再構築の間、外部キーを一時的に無効化しなければならず — PRAGMA legacy_alter_table を参照。
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;
-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;
-- rename a table
ALTER TABLE users RENAME TO accounts;
-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;
-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers制約
エンジンレベルでデータの整合性を強制するのが、制約です。NOT NULL と UNIQUE を含意するのが、PRIMARY KEY です。行の中の任意の列を参照できるのが、CHECK です。ステートメントごとの競合解決を制御するのが、(OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK の)ON CONFLICT 句です。競合行を削除した後挿入するのが、rowid をリセットする、OR REPLACE です。UPSERT のセマンティクスには、ON CONFLICT ... DO UPDATE を推奨しましょう(CRUD のセクションを参照)。
CREATE TABLE products (
id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price REAL NOT NULL CHECK (price >= 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
category TEXT CHECK (category IN ('a','b','c')),
-- composite unique constraint
UNIQUE (name, category),
-- table-level check
CHECK (price * stock < 1000000)
);
-- name a constraint for clearer error messages
CREATE TABLE t (
x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);
-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;CRUD 操作
INSERT
ループ内の行ごとの挿入より劇的に速いのが、マルチ行の INSERT です。バッチ化しましょう。テーブル間でデータをコピーするのが、INSERT ... SELECT です。現在の接続の(トランザクションでない)、最も最近の INSERT の rowid を返すのが、last_insert_rowid() です。SQL インジェクションと引用の頭痛を避けるため、アプリには(Integration のセクションを参照)、パラメータ化された挿入を推奨しましょう。列のデフォルト値を明示的に取るのが、DEFAULT です。
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);
-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;
-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);
-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid(); -- returns the new idUPSERT(ON CONFLICT)
「存在すれば挿入か更新」のための SQLite の慣用句が、ON CONFLICT(UPSERT)です。既存の行を削除する(DELETE トリガーを発火し rowid をリセットする)INSERT OR REPLACE より、はるかに安全です。挿入が提案された行を参照するのが、「excluded」です。静かにスキップするのが、DO NOTHING です。任意の UNIQUE か PRIMARY KEY の制約が、競合のターゲットにでき、そののがこれです。カウンタ、last-seen の更新、冪等なインポートのための正しいパターンです。
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
email = excluded.email,
username = excluded.username;
-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;
-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
username = excluded.username;UPDATE
本当にすべての行を更新する意図でない限り、WHERE を必ず含めましょう — ドライランモードが SQLite にありません。SQLite を、PostgreSQL のエルゴノミクスに近づけるのが、UPDATE ... FROM(3.33+)と、UPDATE ... RETURNING(3.35+)です。別の SELECT なしに、更新後の状態が必要なアプリにとって、RETURNING が非常に有用です。行ごとのロジック付きの、単一ステートメントでの一括更新を可能にするのが、CASE で、これは行ごとの更新よりはるかに速いです。
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;
-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';
-- conditional update with CASE
UPDATE products
SET price = CASE
WHEN category = 'a' THEN price * 0.9
WHEN category = 'b' THEN price * 0.95
ELSE price
END;
-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
FROM items WHERE order_id = orders.id);
-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;DELETE
TRUNCATE が SQLite になく — すべての行を取り除くのが、DELETE FROM table ですが、行レベルのトリガーを発火し、(sqlite_sequence に保存された)AUTOINCREMENT のカウンタをリセットしません。取り除かれたものをログするのが、DELETE ... RETURNING(3.35+)です。監査データには、(deleted_at の列の)ソフト削除をハード削除より推奨しましょう。削除から解放されたスペースが、将来の挿入で再利用され、OS へ取り戻すのが、VACUUM です。
-- basic delete
DELETE FROM users WHERE id = 1;
-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);
-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;
-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;
-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';
-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;SELECT の基本
主力が、SELECT です。ページネーションを実装するのが、LIMIT count OFFSET skip です(大きなオフセットには、キーセットのページネーションがより速い — Querying のセクションを参照)。読みやすさを改善し、ビューでの計算された列に必要なのが、別名(省略可能な AS)です。同一の行の折りたたみが、DISTINCT で、より細かい制御には GROUP BY を考えましょう。結果セットへ if/else のロジックを加え、任意の句で使えるのが、CASE です。
-- basic select
SELECT id, username, email FROM users;
-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10; -- offset, count
-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;
-- expressions
SELECT
username,
age,
age * 365 AS days_alive,
UPPER(username) AS upper_name
FROM users;
-- distinct rows
SELECT DISTINCT category FROM products;
-- conditional output
SELECT name,
CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;クエリとフィルタリング
WHERE と演算子
SQL で特殊であるのが、NULL です。「未知」であり、値ではありません。常に NULL を返す(false として扱われる)のが、= NULL であるため、IS NULL / IS NOT NULL を使いましょう。三値論理に従うのが、AND/OR と組み合わされた NULL です。含むのが、BETWEEN です。OR 等価の省略形が、IN で、奇妙に振る舞うため、IN (NULL, ...) は避けましょう。複合条件を曖昧さなくするため、気前よく括弧を使いましょう。
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);
-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';
SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';
-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
OR (status = 'pending' AND total > 500);ORDER BY と LIMIT
一意のタイブレーカーのない ORDER BY が、実行をまたいで非決定的な順序を与えるため — 最終のソートキーとして、(id のような)一意の列を必ず加えましょう。スキャンし破棄するため、O(n) であるのが、OFFSET のページネーションです。O(limit) であり、大きなテーブルのための正しい選択が、(WHERE id > last_seen_id の)キーセットのページネーションです。フルのソートを必要とするのが、RANDOM() であるため、小さい結果セットかサンプルの部分集合にだけ使いましょう。
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;
-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;
-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;
-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;パターンマッチング
ASCII のためにデフォルトで大文字小文字を区別しないのが(驚くべき落とし穴 — 大文字小文字の敏感さのためには、COLLATE BINARY を)、LIKE です。大文字小文字を区別し、[A-Z] のような文字クラスをサポートするのが、GLOB です。組み込みでないのが、REGEXP で、関数を登録するか拡張をロードしなければなりません。規模の大きい全文検索のためには、(Performance のセクションを参照)、インデックスを使えない LIKE '%term%' の代わりに、FTS5 を。
-- LIKE: case-insensitive by default for ASCII,
-- wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';
-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;
-- GLOB: Unix shell-style, case-SENSITIVE
-- wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';
-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';
-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;CASE と条件ロジック
SQL の if/else であり、SELECT、WHERE、ORDER BY、GROUP BY、か HAVING で機能するのが、CASE です。値に一致するのが、「simple」の形式で、順に真偽条件を評価するのが、「searched」の形式です。クラシックなピボット/条件付きカウントの慣用句が、SUM(CASE WHEN ... THEN 1 ELSE 0 END) です。最初に一致する WHEN で短絡するため、順序が効くのが、CASE です。省略された場合のデフォルトが、NULL であるのが、ELSE です。
-- simple CASE (value match)
SELECT name,
CASE role
WHEN 'admin' THEN 'Administrator'
WHEN 'editor' THEN 'Editor'
ELSE 'Regular User'
END AS role_label
FROM users;
-- searched CASE (conditions)
SELECT name,
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END AS age_group
FROM users;
-- CASE in aggregate (conditional count)
SELECT
COUNT(*) AS total,
SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;
-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
WHEN 'featured' THEN 0
WHEN 'new' THEN 1
ELSE 2
END;DISTINCT と集合の操作
重複除去のソートを UNION が行うため、UNION ALL より遅いのが、UNION です。重複が不可能か許容できるときは、UNION ALL を推奨しましょう。互換的な列の数と型を要求するのが、すべての集合の操作です。UNION/UNION ALL より高い優先順位で INTERSECT/EXCEPT を適用するのが、SQLite です。順序の制御には、括弧を使いましょう。最後の SELECT でなく、複合クエリでの ORDER BY が、結果セット全体に適用されます。
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;
-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;
-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;
-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;
-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;JOIN
INNER JOIN
両方のテーブルに一致する行だけを返すのが、INNER JOIN です。どちらの側の一致しない行も落とされます。両方のテーブルが同名の列を持つときの省略形であり、単一のマージされた列を作るのが、USING (col) です。暗黙に、すべての同名の列を使うのが、NATURAL JOIN です。便利ですが脆い(スキーマ変更が、静かに結合の挙動を変え得ます)。明快さのためには、明示的な ON を。
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;
-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;
-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);
-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;LEFT JOIN
左テーブルからすべての行を保ち、欠けている右側の列が NULL になるのが、LEFT JOIN です。一致のない行を見つけるのが(「WHERE right.id IS NULL」のパターンである、アンチ結合)、NULL が絡むときには特に、NOT IN とサブクエリよりしばしばより明快で速いです。LEFT JOIN をまたいだ集約では、一致した行だけを数えるのが、(COUNT(*) でなく)COUNT(right.id) で、注文のないユーザーが、1 でなく 0 を出します。
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;
-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
-- multiple left joins
SELECT u.username,
COUNT(o.id) AS order_count,
SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;CROSS JOIN と RIGHT/FULL
デカルト積を作るのが、CROSS JOIN です。組み合わせ(サイズ × 色)の生成に有用ですが、大きなテーブルでは危険です(n×m の行です)。RIGHT JOIN を SQLite がサポートします(3.39+)が、めったに使われません。明快さのため、LEFT JOIN として書き直しましょう。ネイティブの FULL OUTER JOIN はなく、LEFT JOIN ... UNION ... RIGHT JOIN で、重なる一致行の重複を除去しつつ、エミュレートしましょう。
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;
-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;
-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;Self Join
別名を使い、各コピーにロールを与え、同じテーブルへ 2 回クエリするのが、self join です(employee 対 manager、user_a 対 user_b)。行が自分自身に一致し、鏡像の重複ペアが作られるのを避けるトリックが、「a.id < b.id」です。深い階層(任意の深さ)には、正しい道具が、再帰 CTE です(CTE のセクションを参照)— 固定の深さしか扱えないのが、self join です。
-- employees and their managers (same table)
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
manager_id INTEGER REFERENCES employees(id)
);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;
-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;マルチテーブルの結合
左から右へチェーンされるのが、結合です。累積の結果を見るのが、各 JOIN です。LEFT と INNER の結合を混ぜるとき、LEFT JOIN の後の INNER JOIN が、NULL を再フィルタし LEFT の目的を無効にし得るため、順序は慎重に。1 対多の結合をまたぐ二重カウントを避けるのが、COUNT(DISTINCT col) です。結合の多重度に注意しましょう:2 つの 1 対多テーブルの結合が、行の爆発を作り得ます(親ごとに m × n 行)。
-- chain joins across 3+ tables
SELECT u.username, o.id AS order_id, i.name AS item, i.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
WHERE u.active = 1
ORDER BY o.id, i.name;
-- mixing JOIN types
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id;
-- join with aggregation
SELECT u.username,
COUNT(DISTINCT o.id) AS orders,
SUM(i.qty * i.price) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.username;集約と GROUP BY
GROUP BY の基本
グループ化された列を共有する行を、グループごとに 1 行へ折りたたむのが、GROUP BY です。SELECT の集約されていないすべての列が、GROUP BY に現れなければなりません(SQLite が寛容で任意の値を選びますが、頼ってはいけません)。時間バケットのレポートに素晴らしいのが、(日付上の strftime のような)式によるグループ化です。GROUP BY の後に適用されるのが、ORDER BY です — グループをソートするのに使いましょう。
-- count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;
-- multiple aggregates per group
SELECT role,
COUNT(*) AS user_count,
AVG(age) AS avg_age,
MIN(age) AS min_age,
MAX(age) AS max_age,
SUM(age) AS total_age
FROM users
GROUP BY role;
-- group by multiple columns
SELECT role, active, COUNT(*) AS cnt
FROM users
GROUP BY role, active
ORDER BY role, active;
-- group by expression
SELECT strftime('%Y-%m', created) AS month,
COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;集約関数
行を数えるのが、COUNT(*) で、非 NULL の値を数えるのが、COUNT(col)、一意の非 NULL を数えるのが、COUNT(DISTINCT col) です。NULL を無視するのが、AVG/SUM です。文字列へ値を結合するのが(デフォルトのカンマの区切り)、GROUP_CONCAT です。常に REAL を返すのが(空の入力には 0.0)、TOTAL() であり、空の入力に対し NULL を返すのが、SUM() です。条件付きの集約には、(CASE よりきれいでより速くなり得る、3.30+ の)FILTER 句を。
SELECT
COUNT(*) AS row_count,
COUNT(email) AS emails_non_null,
COUNT(DISTINCT role) AS distinct_roles,
AVG(age) AS avg_age,
SUM(total) AS revenue,
MIN(created) AS first_signup,
MAX(created) AS last_signup,
GROUP_CONCAT(name) AS all_names,
GROUP_CONCAT(name, ' | ') AS names_piped,
TOTAL(price) AS total_real -- always returns REAL, 0.0 if empty
FROM orders;
-- aggregate with FILTER (SQLite 3.30+)
SELECT
COUNT(*) AS all_orders,
COUNT(*) FILTER (WHERE status='paid') AS paid,
SUM(total) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;HAVING(グループのフィルタ)
グループ化の前に入力行をフィルタするのが、WHERE で、集約後にグループをフィルタするのが、HAVING です。この順序が効きます。集約上の WHERE が違法です。読みやすさのために、(か GROUP BY での)HAVING での別名の参照を SQLite が許しますが、非標準です。クラシックな重複発見のクエリが、「group by X having count(*) > 1」のパターンです。
-- HAVING filters groups (after GROUP BY);
-- WHERE filters rows (before GROUP BY)
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
WHERE status = 'paid' -- filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3 AND SUM(total) > 100 -- filter groups
ORDER BY spent DESC;
-- HAVING with aliases (SQLite allows this)
SELECT role, COUNT(*) AS n
FROM users
GROUP BY role
HAVING n > 1;
-- find duplicate usernames
SELECT username, COUNT(*) AS dupes
FROM users
GROUP BY username
HAVING COUNT(*) > 1;GROUP_CONCAT
SQLite の文字列集約の関数であるのが、GROUP_CONCAT です(他では STRING_AGG か LISTAGG と呼ばれます)。デフォルトの区切りが、カンマで、2 番目の引数として、カスタムのものを指定しましょう。連結の順序を制御するのが、集約内の ORDER BY(3.44+)です。結合前の重複除去が、DISTINCT です。1GB の上限が、結果にあり、巨大なグループのためには、行を取得しアプリで集約しましょう。
-- concatenate values within a group
SELECT role, GROUP_CONCAT(username) AS members
FROM users
GROUP BY role;
-- members: 'alice,bob,carol'
-- custom separator
SELECT role, GROUP_CONCAT(username, ' | ') AS members
FROM users
GROUP BY role;
-- ordered concatenation (SQLite 3.44+)
SELECT role,
GROUP_CONCAT(username ORDER BY username) AS sorted_members
FROM users
GROUP BY role;
-- distinct values only
SELECT role,
GROUP_CONCAT(DISTINCT city) AS cities
FROM users
GROUP BY role;データのピボット
ネイティブの PIVOT が SQLite になく、SUM(CASE WHEN ...) か、よりきれいな FILTER 句でエミュレートしましょう。グループ化属性の 1 つの値を、各ピボットされた列がテストします。このパターンが、長い(正規化された)データを、幅の(クロス集計の)レポートへ変えます。完全に動的なピボット(未知のカテゴリ)のためには、アプリコードで SQL 文字列を組み立てなければなりません — 実行時に、SQL が列を生成できません。
-- manual pivot with SUM(CASE ...)
SELECT user_id,
SUM(CASE WHEN strftime('%w', created) = '0' THEN 1 ELSE 0 END) AS sun,
SUM(CASE WHEN strftime('%w', created) = '1' THEN 1 ELSE 0 END) AS mon,
SUM(CASE WHEN strftime('%w', created) = '2' THEN 1 ELSE 0 END) AS tue,
SUM(CASE WHEN strftime('%w', created) = '3' THEN 1 ELSE 0 END) AS wed,
SUM(CASE WHEN strftime('%w', created) = '4' THEN 1 ELSE 0 END) AS thu,
SUM(CASE WHEN strftime('%w', created) = '5' THEN 1 ELSE 0 END) AS fri,
SUM(CASE WHEN strftime('%w', created) = '6' THEN 1 ELSE 0 END) AS sat
FROM orders
GROUP BY user_id;
-- pivot with FILTER (cleaner)
SELECT user_id,
COUNT(*) FILTER (WHERE status='paid') AS paid,
COUNT(*) FILTER (WHERE status='pending') AS pending,
COUNT(*) FILTER (WHERE status='refunded') AS refunded
FROM orders
GROUP BY user_id;サブクエリ
スカラーのサブクエリ
正確に 1 行と 1 列を返し、値の式が有効などこでも現れ得るのが、スカラーのサブクエリです。外側のクエリを参照し行ごとに再実行されるのが、相関サブクエリです。強力だが、大きなテーブルでは遅くなり得ます(代わりに、JOIN か窓関数を考えましょう)。単純な相関サブクエリを結合へ最適化するのが SQLite は得意ですが、ホットな経路のためには、必ず EXPLAIN QUERY PLAN を確認しましょう。
-- a subquery returning a single value
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- in SELECT list
SELECT username,
age,
age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;
-- in HAVING
SELECT role, AVG(age) AS avg_age
FROM users
GROUP BY role
HAVING AVG(age) > (SELECT AVG(age) FROM users);
-- correlated scalar subquery (re-evaluated per row)
SELECT username,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;IN と NOT IN のサブクエリ
直感的なのが、IN のサブクエリですが、NULL に注意しましょう:サブクエリの結果が NULL を含めば、(false として扱われる)NULL に評価されるのが、すべての行に対する NOT IN であり、空の結果を返します。nullable な列でのアンチ結合には、NOT EXISTS を推奨しましょう — NULL セーフであり、しばしばより速いです。複合キーの一致には簡潔なのが、マルチ列の IN です。
-- users who have placed an order
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders);
-- users who have NOT placed an order
SELECT username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- DANGER: NOT IN with NULLs returns no rows!
-- if the subquery returns any NULL, NOT IN matches nothing.
-- Safe version using NOT EXISTS:
SELECT username FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- multi-column IN
SELECT * FROM products
WHERE (category, price) IN (
SELECT category, MIN(price) FROM products GROUP BY category
);EXISTS と NOT EXISTS
データを取得せず、行の存在をテストするのが、EXISTS です。最初の一致で止まるため、効率的です。非一致の行を見つける NULL セーフな方法が(NOT IN と違い)、NOT EXISTS です。最良のパフォーマンスのためには、相関サブクエリが結合する列に(例の orders.user_id)、インデックスが存在することを確保しましょう。慣例なのが、SELECT 1 で、EXISTS にとって、列のリストは無関係です。
-- EXISTS: true if subquery returns any row
SELECT username FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.total > 100
);
-- NOT EXISTS: classic anti-join (NULL-safe)
SELECT username FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- correlated EXISTS — usually efficient with an index on
-- the subquery's join column (orders.user_id here)
SELECT p.name FROM products p
WHERE EXISTS (
SELECT 1 FROM order_items i
WHERE i.product_id = p.id AND i.qty > 10
);派生テーブル(FROM 内のサブクエリ)
中間結果をステージングし、事前集約するか、結合前にフィルタする強力な方法であるのが、(FROM 内の)サブクエリである、派生テーブルです。別名が必要なのが、派生テーブルです。ほとんどの場合、サブクエリをマテリアライズする(一時テーブルへ書く)のが、SQLite です。繰り返しの使用のためには、より明快で複数回参照できるのが、(WITH 句の)CTE です。FROM のサブクエリへの、相関の参照を SQLite が許しません。
-- treat a query result as a table
SELECT t.role, t.cnt
FROM (
SELECT role, COUNT(*) AS cnt
FROM users
GROUP BY role
) t
WHERE t.cnt > 1;
-- join a derived table
SELECT u.username, t.total_spent
FROM users u
JOIN (
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
) t ON t.user_id = u.id;
-- derived tables must be aliased
SELECT * FROM (SELECT 1 AS x) AS sub;相関対非相関
1 回実行されキャッシュできるのが、非相関のサブクエリで、外側の行ごとに再実行されるのが(速く保つにはインデックスを)、相関サブクエリです。LATERAL/APPLY が SQLite になく、行ごとの集約を「結合」するには、派生テーブルで事前集約し LEFT JOIN しましょう。相関サブクエリが遅いとき、通常の修正が、GROUP BY 付きの JOIN か窓関数としての書き直しです。
-- UNCORRELATED: runs once, result cached
SELECT username FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- CORRELATED: references outer row, re-runs per outer row
SELECT u.username,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u;
-- lateral-style correlation in a JOIN subquery is NOT
-- supported directly; use a scalar subquery in SELECT or
-- rewrite as a join with GROUP BY:
SELECT u.username, COALESCE(t.n, 0) AS orders
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id
) t ON t.user_id = u.id;インデックス
インデックスの作成
読み取りを速くするが書き込みを遅くするのが(すべてのインデックスが、各 INSERT/UPDATE/DELETE で更新されます)、インデックスです。効くのが、列の順序です。(a, b) 上の複合インデックスが、WHERE a=? と WHERE a=? AND b=? を助けますが、WHERE b=? 単独では助けません(最も左の接頭辞のルールです)。制約を強制するのが、一意のインデックスです。B-tree としてインデックスを保存するのが、SQLite です。未使用のインデックスを落としましょう — スペースと書き込み性能を浪費します。
-- single-column index
CREATE INDEX idx_users_email ON users(email);
-- unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- IF NOT EXISTS for idempotent scripts
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
-- drop an index
DROP INDEX IF EXISTS idx_users_email;
-- list indexes
.indices users
-- or
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';部分インデックス
一致する行だけをインデックスするため、より小さく保守が速いのが、部分インデックスです。クエリが、常に安定した述語でフィルタするとき(「active = 1」、「deleted_at IS NULL」)に理想的です。オプティマイザーが使うためには、クエリの WHERE が、インデックスの WHERE に(かより厳しく)一致しなければなりません。部分集合上でのみ一意性を強制するのが、一意の部分インデックスで — 「親ごとに 1 つのアクティブ行」の制約に完璧です。
-- index only active users (smaller, faster)
CREATE INDEX idx_active_users ON users(username)
WHERE active = 1;
-- index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(user_id)
WHERE status = 'unpaid';
-- unique partial index: one active session per user
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id) WHERE active = 1;
-- query must match the WHERE for the index to be used
SELECT * FROM users WHERE active = 1 AND username = 'alice';式インデックス
関数か式の結果をインデックスし、変換された値での速いルックアップを可能にするのが、式インデックスです。しかし、クエリが正確に同じ式を使わなければなりません。よくある使い方:大文字小文字を区別しない検索(LOWER)、日付のバケット化(strftime)、JSON のフィールドの抽出(json_extract)です。決定的な関数だけが(RANDOM() か NOW() ではありません)。強力だが、スキーマ変更が厄介になるのが、式インデックスです。
-- index a function result (must match the query expression exactly)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- query that can use it (must use the same expression)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- index a date extraction for fast monthly queries
CREATE INDEX idx_orders_month ON orders(strftime('%Y-%m', created));
SELECT * FROM orders WHERE strftime('%Y-%m', created) = '2024-06';
-- index a JSON path
CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';
-- indexed computed value (collation)
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);カバリングと WITHOUT ROWID
クエリが読むすべての列を含み、インデックスだけで(テーブルのルックアップなし)SQLite がクエリを満たせるのが、カバリングインデックスです。PRIMARY KEY をクラスタ化されたインデックスにするのが、WITHOUT ROWID のテーブルです。キーでソートされて保存されるのが、行であり、キー/バリューのワークロードと、PK 上のレンジスキャンに理想的です。トレードオフ:AUTOINCREMENT なし、rowid の別名なし、わずかに複雑な更新。広く採用する前に、ベンチマークしましょう。
-- covering index: includes all columns the query needs,
-- so SQLite never reads the table
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- ^ fully served by the index
-- WITHOUT ROWID tables: store rows IN the index (clustered)
CREATE TABLE kv (
key TEXT PRIMARY KEY,
value TEXT
) WITHOUT ROWID;
-- the PK becomes the table itself; lookups by key are fast
SELECT value FROM kv WHERE key = 'config:theme';
-- good for key/value lookups and read-heavy tables
-- trade-off: no rowid, some features unsupportedインデックスの検査
最重要のツールが、EXPLAIN QUERY PLAN です。「USING INDEX」 = 良い。「SCAN」(フルテーブルスキャン)= 大きいテーブルでは通常悪いです。結合のためには、結合列がインデックスされていること、プランナーがより大きいテーブルをスキャンしていないことを確認しましょう。VDBE のバイトコードを示すのが(QUERY PLAN なしの)EXPLAIN で、めったに不要ですが、深いデバッグに有用です。プランナーがインデックスを選ぶのに使う、カーディナリティの統計を保持するの が、(ANALYZE により満たされた)sqlite_stat1 です。テーブルを再構築せず、肥大化したか壊れたインデックスを再構築するのが、REINDEX です。プランナーが良い計画を選ぶための、新鮮な統計を持つよう、大きなデータのロード後に ANALYZE を実行しましょう。
-- see if an index is used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';
-- look for 'SEARCH users USING INDEX idx_users_email'
-- list all indexes on a table with their definitions
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';
-- index stats (how often each index is used)
SELECT name, idx_scan, idx_read
FROM sqlite_stat1, sqlite_master
WHERE sqlite_stat1.tbl = 'users';
-- rebuild an index (after corruption or bloat)
REINDEX idx_users_email;
REINDEX; -- rebuild all indexes
-- analyze tables to update query planner stats
ANALYZE;ビュー
ビューの作成
保存されたクエリにより定義された仮想テーブルであるのが、ビューです。データを保存せず(temp ビューを除き)、基礎の SELECT を毎回再実行します。複雑なクエリの単純化、一貫したアクセスパターンの強制、アプリからのスキーマ変更の抽象化に使いましょう。デフォルトで読み取り専用であるのが(更新可能なビューには制約付き)、SQLite でのビューです。基礎のテーブルに影響するのは決してないのが、DROP VIEW です。
-- a view is a saved SELECT, queried like a table
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE active = 1;
SELECT * FROM active_users WHERE age > 18;
-- view with calculated columns
CREATE VIEW user_summary AS
SELECT u.id, u.username,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;
-- IF NOT EXISTS for idempotent scripts
CREATE VIEW IF NOT EXISTS active_users AS
SELECT id, username FROM users WHERE active = 1;
-- drop a view
DROP VIEW IF EXISTS active_users;更新可能なビュー
単純なビュー(単一の基底テーブル、JOIN/集約/DISTINCT/GROUP BY なし)上の INSERT/UPDATE/DELETE を SQLite が許します。基礎のテーブルへ転送されるのが、操作です。WITH CHECK OPTION を SQLite はサポートし「ない」ため、ビューを通して挿入された行が、ビューの WHERE を満たさず、その後ビュー越しに見えないかもしれません。書き込みを必要とする複雑なビューには、INSTEAD OF トリガーを使いましょう。
-- simple views (one base table, no aggregates/distinct/etc.)
-- are auto-updatable in SQLite
CREATE VIEW active_users AS
SELECT id, username, email, age FROM users WHERE active = 1;
-- INSERT through the view (active defaults/applies)
INSERT INTO active_users (id, username, email) VALUES (1, 'x', '[email protected]');
-- UPDATE through the view
UPDATE active_users SET age = 30 WHERE id = 1;
-- DELETE through the view
DELETE FROM active_users WHERE id = 1;
-- the WITH CHECK OPTION is NOT supported in SQLite, so
-- inserts/updates that don't match the view's WHERE still
-- land in the base table but vanish from the view.ビュー上の INSTEAD OF トリガー
ビューへの書き込みを横取りし、カスタムロジックを実行し、任意のビューを更新可能にするのが、INSTEAD OF トリガーです。複雑な(マルチテーブル、集約された)ビューを書き込み可能にする標準的な方法が、これです。入ってくる行と既存の行を参照するのが、NEW と OLD です。ビュー上でのみ発火し、基底テーブル上では発火しないのが、INSTEAD OF トリガーです。カプセル化されたビジネスロジックの実装や、非正規化されたビューの背後に正規化されたスキーマを隠すのに、使いましょう。
-- a view joining multiple tables is not auto-updatable,
-- but INSTEAD OF triggers let you define the write behavior
CREATE VIEW user_orders AS
SELECT u.id AS user_id, u.username, o.id AS order_id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;
-- route INSERTs on the view to the orders table
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
INSERT INTO orders (id, user_id, total)
VALUES (NEW.order_id, NEW.user_id, NEW.total);
END;
-- now this works:
INSERT INTO user_orders (user_id, order_id, total)
VALUES (1, 100, 49.99);一時ビュー
現在のデータベース接続のためだけに存在し、切断時に自動的に落とされるのが、一時ビューです。共有されたスキーマを散らかさずに、複雑なアドホッククエリをステージングするのに完璧です。別々の temp スキーマに保存されるため、(同じファイルへの接続さえ)他の接続に見えません。TEMP のテーブルを参照できるのが、TEMP のビューであり、セッションスコープの ETL パイプラインに有用です。
-- TEMP view exists only for the current connection
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders WHERE created > datetime('now', '-7 days');
-- the view vanishes when the connection closes
-- (no other connection can see it)
-- also valid: CREATE TEMPORARY VIEW
CREATE TEMPORARY VIEW my_scratch AS
SELECT id, username FROM users WHERE role = 'admin';
-- use case: ad-hoc analysis without polluting the schema
SELECT * FROM recent_orders WHERE total > 100;
-- list views
SELECT name, sql FROM sqlite_master
WHERE type = 'view';
SELECT name, sql FROM temp.sqlite_master
WHERE type = 'view'; -- temp viewsマテリアライズドビュー(エミュレート)
ネイティブのマテリアライズドビューが SQLite になく、スケジュールで更新される通常のテーブルが、同じことを達成します。アトミックな更新のためには、トランザクションで新しいテーブルを構築し、改名しましょう。コミットまで古いバージョンを、その後新しいバージョンを、読み手が見ます。再構築の後に、インデックスを加えましょう。読み取り速度のための書き込み/ストレージのコストのトレードオフが、マテリアライズドビューであり — 頻繁にクエリされる高コストな集約に理想的です。
-- SQLite has no native materialized views; emulate with a
-- real table that you refresh periodically
-- 1) create the 'materialized' table
CREATE TABLE mv_user_summary AS
SELECT u.id, u.username,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;
-- 2) add an index for fast lookups
CREATE INDEX idx_mv_user_summary_id ON mv_user_summary(id);
-- 3) refresh (full rebuild)
DELETE FROM mv_user_summary;
INSERT INTO mv_user_summary
SELECT u.id, u.username, COUNT(o.id), COALESCE(SUM(o.total), 0)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;
-- 4) refresh atomically with a name swap
BEGIN;
CREATE TABLE mv_user_summary_new AS SELECT ...;
DROP TABLE mv_user_summary;
ALTER TABLE mv_user_summary_new RENAME TO mv_user_summary;
COMMIT;トリガー
BEFORE / AFTER トリガー
行が書かれる前に動くのが、BEFORE トリガーです。検証(RAISE ABORT)や NEW の値の変換に使いましょう。行がコミットされた後に動くのが、AFTER トリガーです。監査、カスケード効果、非正規化に使いましょう。唯一のオプションであるのが(ステートメントレベルのトリガーなし)、FOR EACH ROW です。入ってくる行を保持するのが(INSERT/UPDATE)、NEW で、前の行を保持するのが(UPDATE/DELETE)、OLD です。条件付きでトリガーを発火させるのが、WHEN 句です。
-- AFTER INSERT: audit log
CREATE TRIGGER trg_users_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO users_audit (user_id, action, at)
VALUES (NEW.id, 'insert', datetime('now'));
END;
-- BEFORE INSERT: validate / normalize data
CREATE TRIGGER trg_users_lower_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'email cannot be empty')
WHERE NEW.email = '';
END;
-- AFTER UPDATE: log changes
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
INSERT INTO email_changes (user_id, old_email, new_email, at)
VALUES (NEW.id, OLD.email, NEW.email, datetime('now'));
END;カスケード効果のためのトリガー
非正規化されたカウンタ、サマリーテーブル、updated_at のタイムスタンプを、トリガーが自動的に維持できます。注意:可能であるのが(自身のテーブルを更新するトリガーの)、再帰トリガーのチェーンです。これを制御するのが(後方互換性のためのデフォルト OFF)、PRAGMA recursive_triggers です。同じイベント上の複数のトリガーの順序が、作成順に従い、同じタイミングのトリガーをまたぐ、BEFORE/AFTER の優先度はありません。
-- maintain a denormalized counter
CREATE TRIGGER trg_orders_count_inc
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE users
SET order_count = order_count + 1
WHERE id = NEW.user_id;
END;
CREATE TRIGGER trg_orders_count_dec
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
UPDATE users
SET order_count = order_count - 1
WHERE id = OLD.user_id;
END;
-- keep an 'updated_at' column fresh
CREATE TRIGGER trg_users_touch
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;INSTEAD OF トリガー(ビュー)
ビュー上の元の INSERT/UPDATE/DELETE の代わりに動き、1 つ以上の基底テーブルへの書き込みをルートできるのが、INSTEAD OF トリガーです。複雑なビューを書き込み可能にする正統な方法が、これです。ビューの行ごとに発火します。ビュー上に定義できるのが、INSTEAD OF トリガーだけです。BEFORE/AFTER トリガーには、基底テーブルが要ります。正規化されたスキーマの上の、API スタイルの抽象化レイヤーに有用です。
-- make a multi-table view writable
CREATE VIEW order_detail AS
SELECT o.id AS order_id, o.total, u.username, i.product_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id;
CREATE TRIGGER trg_order_detail_update
INSTEAD OF UPDATE ON order_detail
FOR EACH ROW
BEGIN
UPDATE orders SET total = NEW.total WHERE id = NEW.order_id;
UPDATE order_items SET product_name = NEW.product_name
WHERE order_id = NEW.order_id;
END;
-- now UPDATE on the view routes to both tables
UPDATE order_detail SET total = 99.99, product_name = 'Widget'
WHERE order_id = 5;RAISE とエラー処理
トリガーの失敗の伝播を制御するのが、RAISE です。ステートメントをキャンセルし変更をロールバックするのが、ABORT(最もよくある)で、トランザクション全体をロールバックするのが、ROLLBACK、ステートメントを中止するがトランザクション内の以前の成功したステートメントを保つのが、FAIL、犯人の行だけをスキップするのが、IGNORE です。一括ロードの間、無効な行を静かに落とすきれいな方法が、RAISE(IGNORE) です。
-- RAISE aborts the statement with a custom error
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
SELECT RAISE(ABORT, 'age must be non-negative');
END;
-- ROLLBACK: undo the whole transaction, statement fails
CREATE TRIGGER trg_rollback_example
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
SELECT RAISE(ROLLBACK, 'balance cannot go negative');
END;
-- FAIL: abort the current statement only (like ABORT)
-- IGNORE: skip this row, continue with the rest
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON logs
FOR EACH ROW
WHEN NEW.level NOT IN ('info','warn','error')
BEGIN
SELECT RAISE(IGNORE);
END;トリガーの管理
sqlite_master に保存されるのが(TEMP トリガーには temp.sqlite_master)、トリガーです。取り除くのが、DROP TRIGGER で、ALTER TRIGGER はなく — 落として再作成しましょう。接続ローカルであり、切断時に消えるのが、TEMP のトリガーで、デバッグに有用です。ドロップすることを除き、トリガーを一時的に無効化する組み込みの方法がなく、条件付きの実行のためには、WHEN 句を使いましょう。タイミング内で、作成順に発火するのが、同じイベント上の複数のトリガーです。
-- list all triggers
SELECT name, tbl_name, sql FROM sqlite_master
WHERE type = 'trigger';
-- list triggers on a specific table
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';
-- drop a trigger
DROP TRIGGER IF EXISTS trg_users_audit;
-- triggers in a TEMP schema (connection-local)
CREATE TEMP TRIGGER trg_temp_log
AFTER INSERT ON users
FOR EACH ROW
BEGIN
SELECT 'inserted ' || NEW.id;
END;
-- trigger timing order: BEFORE triggers fire in
-- creation order, then AFTER triggers in creation order.PRAGMA の設定
PRAGMA の基本
SQLite 固有の設定インターフェースであるのが、PRAGMA です。ジャーナリング、キャッシング、外部キー、整合性、クエリプランナーを設定が制御します。ドキュメントが別のことを言わない限り、セッションスコープであるのが(接続ごと)、設定です。トランザクション内での設定が無視され得ます。トランザクションの外か、テーブル作成の前でのみ効果を取るのが、一部の PRAGMA(journal_mode、page_size)です。ビルドが何をサポートするか見るには、PRAGMA compile_options を。
-- PRAGMA is SQLite's configuration knob (CLI and APIs)
-- read a setting
PRAGMA journal_mode;
PRAGMA cache_size;
PRAGMA foreign_keys;
-- set a setting (session scope unless noted)
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000; -- negative = KB, positive = pages
PRAGMA temp_store = MEMORY;
-- many PRAGMAs are no-ops below a certain SQLite version
-- check the compile-time options
PRAGMA compile_options;
-- e.g. ENABLE_FTS5, ENABLE_JSON1, MAX_ATTACHED, etc.
-- PRAGMAs are NOT part of SQL standard and are SQLite-only.外部キー
デフォルトで OFF であるのが、SQLite での外部キーの強制です。他のデータベースから来たユーザーにとって悪名高い落とし穴です。PRAGMA foreign_keys = ON により、すべての接続で有効化しなければならず、トランザクション内では設定できません。この設定が存在するのは、(FK 対応以前の)早いスキーマがさもなければ壊れるためです。本番では必ず有効化しましょう。依存関係に違反する順序でのデータのインポートを助けるのが、defer_foreign_keys です。
-- foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON; -- enable per connection
-- check current state
PRAGMA foreign_keys;
-- inspect FK definitions on a table
PRAGMA foreign_key_list(orders);
-- returns: id, seq, table, from, to, on_update, on_delete, match
-- enable FKs in your app's connection setup, every time
-- (this is a frequent source of bugs when moving from MySQL/PG)
-- deferred FK checking (checked at COMMIT, not per statement)
PRAGMA defer_foreign_keys = ON;
-- or declare DEFERRABLE INITIALLY DEFERRED on the FKジャーナルモードと WAL
ほとんどのアプリのための推奨ジャーナルモードが、WAL(Write-Ahead Logging)です。読み手と書き手が互いをブロックせず、耐久性が良いです。-wal と -shm のサイドカーファイルを作るのが、メインの DB の隣にです。データベースファイルごとに永続的であるのが(1 回設定しましょう)、WAL です。WAL の内容をメインファイルへ折り返しスペースを 取り戻すのが、wal_checkpoint(TRUNCATE) です。ネットワークのファイルシステムでの WAL は避けましょう — 共有メモリが要ります。
-- WAL: write-ahead logging (best for most apps)
PRAGMA journal_mode = WAL;
-- benefits: readers never block writers, writers never block
-- readers, crash recovery is fast
-- trade-off: creates -wal and -shm sidecar files
-- other modes: DELETE (default), TRUNCATE, PERSIST, MEMORY, OFF
PRAGMA journal_mode = DELETE;
-- WAL checkpoint: merge -wal back into the main file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE); -- also shrinks the -wal file
-- auto-checkpoint threshold (pages of WAL before auto-checkpoint)
PRAGMA wal_autocheckpoint = 1000;
-- WAL is persistent: setting it once keeps it for that DB file.同期と耐久性
SQLite がどれほど積極的に fsync を呼ぶかを制御するのが、synchronous です。最も安全なのが(すべてのコミットが fsync する)、FULL で、WAL を使うときの推奨値が、NORMAL です(チェックポイントで fsync し、コミットごとではなく、実際には無視できる破損のリスクです)。停電時のデータベースの破損のリスクがあるのが、OFF であり — 本当に使い捨てのデータか再構築可能なキャッシュのためだけに。スキーマ作成の前に page_size を設定すべきで、後の変更には VACUUM が要ります。
-- how thoroughly SQLite flushes to disk on commit
PRAGMA synchronous; -- read current value
PRAGMA synchronous = FULL; -- safest, slowest (default for rollback journal)
PRAGMA synchronous = NORMAL; -- safe with WAL, fast (recommended for WAL)
PRAGMA synchronous = OFF; -- fastest; corruption risk on power loss
PRAGMA synchronous = EXTRA; -- even more careful than FULL
-- trade-off: durability vs speed
-- FULL: fsync at every commit (survives OS crash)
-- NORMAL (WAL): fsync at checkpoint, not every commit
-- OFF: no fsync — fast but may corrupt on power loss
-- page size (set BEFORE creating tables, ideally)
PRAGMA page_size = 4096;
PRAGMA page_size;キャッシュ、メモリ、temp ストア
メモリ内のページキャッシュであるのが、cache_size です。読み取りが重いワークロードには、一般に大きい方が良いです。KB であるのが、負の値で、ページが、正の値です。中間の結果(ソート、一時テーブル)を RAM へ動かし、大きな集約を速くするのが、temp_store = MEMORY です。読み取りのためのメモリマップド I/O を有効化するのが、mmap_size で、大きな読み取り専用データベースを速くし得ます。アプリのメモリ予算とアクセスパターンに基づき、これらを調整しましょう。
-- page cache (negative = KB, positive = pages)
PRAGMA cache_size = -65536; -- 64 MB cache
PRAGMA cache_size = 20000; -- 20000 pages
-- where temp tables and intermediate results live
PRAGMA temp_store = DEFAULT; -- compile-time default
PRAGMA temp_store = FILE; -- temp files
PRAGMA temp_store = MEMORY; -- all temp data in RAM
-- mmap memory-mapped I/O for reads
PRAGMA mmap_size = 268435456; -- 256 MB
-- soft heap limit for the SQLite library
PRAGMA soft_heap_limit = 100000000; -- 100 MB
-- store prepared statements in memory between calls
PRAGMA cache_spill;整合性とスキーマの情報
データベース構造とコンテンツ全体を検証するのが(遅いが徹底的)、integrity_check で、コンテンツの検証をスキップし、はるかに速いのが、quick_check です。バックアップの間や、クラッシュの後に、integrity_check を実行しましょう。列のメタデータを報告するのが(FTS5 の rowid のような隠れた列のためには、table_xinfo)、table_info です。どのインデックスが存在し、その列が何かを明らかにするのが、index_list と index_info です。attached されたデータベースを示すのが、database_list です。
-- full integrity check (slow on large DBs)
PRAGMA integrity_check;
-- returns 'ok' or a list of problems
-- quick check (faster, less thorough)
PRAGMA quick_check;
-- list tables / schema
SELECT name, sql FROM sqlite_master WHERE type = 'table';
-- table info: columns, types, notnull, default, pk
PRAGMA table_info(users);
-- returns: cid, name, type, notnull, dflt_value, pk
-- extended table info (includes hidden columns from FTS/etc.)
PRAGMA table_xinfo(users);
-- index list with origin and unique flag
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);
-- database file header info
PRAGMA database_list;日付と時刻の関数
date / time / datetime
ネイティブの DATE 型が SQLite になく、ISO-8601(「YYYY-MM-DD HH:MM:SS」)の TEXT であるのが、日付です。多くの入力形式を受け付けるのが(ISO、スラッシュ、月名、「unixepoch」修飾子経由の Unix タイムスタンプ)、日付/時刻の関数です。ステートメントごとに 1 回評価され、行ごとではないのが、「now」です。マルチ行の UPDATE のすべての行が、同じタイムスタンプを得ます。これは通常、望むものです。UTC で保存し、表示用に localtime で整形しましょう。
-- current date/time (UTC by default)
SELECT date('now'); -- '2024-06-15'
SELECT time('now'); -- '14:30:00'
SELECT datetime('now'); -- '2024-06-15 14:30:00'
SELECT julianday('now'); -- 2460476.1042 (days since 4713 BC)
-- parse various formats
SELECT date('2024-06-15');
SELECT date('2024/06/15');
SELECT date('June 15, 2024');
SELECT datetime('2024-06-15 14:30:00.123');
SELECT date('2024-06-15','unixepoch'); -- from Unix timestamp (seconds)
-- Unix timestamp to datetime
SELECT datetime(1718450400, 'unixepoch');
SELECT datetime(1718450400, 'unixepoch', 'localtime');strftime の整形
普遍的な日付の整形器であるのが、strftime です。C の strftime を映し返すのが、形式コードです。グループ化のための(年、月、曜日の)日付のパートの抽出や、カスタムの表示形式の描画に、これを使いましょう。月ごとのグループ化のためには、「YYYY-MM」を出すのが、strftime('%Y-%m', created) で、これは自然にソート・グループ化されます。DAYNAME()/MONTHNAME() が SQLite になく、曜日名が必要なら、CASE と strftime('%w', ...) を組み合わせましょう。
-- strftime is the most flexible formatter
-- strftime(format, timestring, modifier, modifier, ...)
SELECT strftime('%Y-%m-%d', 'now'); -- '2024-06-15'
SELECT strftime('%Y-%m-%d %H:%M', 'now'); -- '2024-06-15 14:30'
SELECT strftime('%H:%M:%S', 'now'); -- '14:30:00'
-- common format codes
-- %Y year (4-digit) %m month (01-12) %d day (01-31)
-- %H hour (00-23) %M minute (00-59) %S second (00-59)
-- %j day of year %w day of week (0=Sun)
-- %W week of year %p AM/PM %I hour (01-12)
-- get the day name
SELECT strftime('%w', '2024-06-15'); -- '6' (Saturday)
SELECT strftime('%Y-%m', 'now'); -- month bucket for grouping修飾子(算術)
日付を動かしたり整列させたりするため、左から右へチェーンされるのが、修飾子です。「+N units」 / 「-N units」であるのが、間隔です(単位:days、hours、minutes、seconds、months、years)。期間の境界へスナップするのが、「start of month/year/day」で — 月次レポートに完璧です。次の weekday N の日付を返すのが(今日が一致すれば、今日)、weekday N です。現在月の最終日を出すのが、「start of month」、「+1 month」、「-1 day」の組み合わせで — クラシックな慣用句です。
-- shift a date by an interval
SELECT date('now', '+1 day'); -- tomorrow
SELECT date('now', '-1 month'); -- one month ago
SELECT date('now', '+1 year', '+1 day');-- next year + 1 day
SELECT datetime('now', '+2 hours', '+30 minutes');
-- start of period
SELECT date('now', 'start of month'); -- first day of month
SELECT date('now', 'start of year'); -- first day of year
SELECT date('now', 'start of day'); -- midnight today
-- weekday: next occurrence of a given weekday (0=Sun, 1=Mon, ... 6=Sat)
SELECT date('now', 'weekday 1'); -- next Monday
SELECT date('now', 'weekday 0', '+7 days'); -- Sunday after this one
-- combine modifiers
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of monthUnix 時刻とユリウス日
エポックからの整数/秒であるのが、Unix のタイムスタンプで、直接整数を返すのが、unixepoch()(3.38+)です。連続した分数の日のカウントであるのが、ユリウス日です。2 つの julianday 値の引き算が、日数で経過時間を与えます(秒には 86400 を掛けましょう)。年齢計算のためには、365.25 による割り算が、閏年をおおよそ考慮します。法的な正確さのためには、year/month/day のコンポーネントを明示的に比較しましょう。
-- Unix timestamp (seconds since 1970-01-01 UTC)
SELECT strftime('%s', 'now'); -- current Unix time (text)
SELECT unixepoch('now'); -- integer Unix time (SQLite 3.38+)
SELECT datetime(1718450400, 'unixepoch'); -- back to datetime
-- Julian day (fractional days since 4713-01-01 BC)
SELECT julianday('now'); -- 2460476.6042
SELECT julianday('2024-06-15') - julianday('2024-06-10'); -- 5.0 (days between)
-- compute elapsed time in seconds
SELECT (julianday('now') - julianday('2024-01-01')) * 86400.0 AS seconds_elapsed;
-- human-readable age from a birthdate
SELECT CAST((julianday('now') - julianday('1990-05-20')) / 365.25 AS INT) AS age;タイムゾーンと localtime
タイムゾーンのデータベースが SQLite になく、UTC であるのが、「now」で、ホスト OS のタイムゾーン設定に頼るのが、「localtime」/「utc」の修飾子です。推奨パターン:タイムスタンプは常に UTC で保存し(UTC であるのが、datetime('now'))、表示時に「localtime」の修飾子でローカル時刻へ変換しましょう。これが、異なるゾーンのサーバーをまたぐ曖昧さや、サマータイムの遷移を避けます。ローカル時刻を決して保存しないこと。
-- 'now' is always UTC; convert to local time for display
SELECT datetime('now', 'localtime'); -- local wall-clock time
SELECT datetime('now'); -- UTC
-- convert a stored UTC timestamp to local time
SELECT datetime(created, 'localtime') FROM users;
-- convert local time back to UTC for storage
SELECT datetime('2024-06-15 14:30:00', 'utc');
-- best practice: store UTC, format for display
CREATE TABLE events (ts TEXT DEFAULT (datetime('now'))); -- UTC
-- display:
SELECT datetime(ts, 'localtime') AS local_ts FROM events;
-- timezone is determined by the OS; SQLite has no TZ database.文字列の関数
長さと部分文字列
TEXT のための文字数と、BLOB のためのバイト数を数えるのが、length() です。UTF-8 のバイト長のためには、先に BLOB へ cast しましょう。1 起点なのが、substr() で、末尾から数えるのが、負の開始です。ネイティブの LPAD/RPAD が SQLite になく、文字列の連結と substr でエミュレートしましょう。マルチバイトのテキストを切り詰めるときのバイト対文字の混乱に注意しましょう:文字上で動くのが substr なので安全ですが、バイトレベルのスライシングはそうではありません。
SELECT length('hello'); -- 5
SELECT length('日本語'); -- 3 (characters, not bytes)
SELECT length(x'00ff'); -- 2 (blob length in bytes)
SELECT substr('hello world', 7); -- 'world' (1-indexed)
SELECT substr('hello world', 1, 5); -- 'hello' (length 5)
SELECT substr('hello', -3); -- 'llo' (negative = from end)
SELECT substr('hello', -3, 2); -- 'll'
-- byte length (vs character length)
SELECT length('日本語'); -- 3 (characters)
SELECT length(CAST('日本語' AS BLOB)); -- 9 (UTF-8 bytes)
-- left/right pad (workaround; no native LPAD/RPAD)
SELECT substr('0000' || '42', -4, 4); -- '0042'大文字小文字とトリミング
デフォルトで ASCII の文字だけに影響するのが、upper()/lower() です。ICU がロードされない限り、非 ASCII の文字が変わらずに残ります。デフォルトで前後の空白を取り除くのが、trim() で、2 番目の引数として与えられた特定の文字もです。リテラルの部分文字列の置換を行うのが(正規表現やパターンなし)、replace() です。文字セットの置換のためのネイティブの TRANSLATE() がなく、replace() の呼び出しをチェーンするか、カスタム関数を使いましょう。
SELECT upper('hello'); -- 'HELLO'
SELECT lower('HELLO'); -- 'hello'
SELECT upper('日本語'); -- '日本語' (no case to change)
SELECT trim(' hi '); -- 'hi' (both sides)
SELECT ltrim(' hi '); -- 'hi ' (left only)
SELECT rtrim(' hi '); -- ' hi' (right only)
-- trim specific characters
SELECT trim('xxhelloxx', 'x'); -- 'hello'
SELECT ltrim('aaabbb', 'a'); -- 'bbb'
-- replace characters
SELECT replace('a-b-c', '-', '/'); -- 'a/b/c'
SELECT replace('Hello World', 'o', '0'); -- 'Hell0 W0rld'連結と printf
標準の SQL の連結子であるのが、|| の演算子ですが、NULL を伝播します(NULL || x = NULL)ので、NULL が可能なときは COALESCE で包みましょう。C スタイルの整形をもたらし、パディング、数値の整形、カンマの区切り付きの文字列を構築する最もきれいな方法が、printf()/format() です。SQL への埋め込みのため、安全に引用符で囲まれエスケープされた文字列を返すのが、quote() です。動的な SQL を構築するときに有用です。
-- || operator concatenates
SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'
SELECT username || ' <' || email || '>' FROM users;
-- NULL propagates through || (NULL || 'x' IS NULL)
SELECT 'a' || NULL || 'b'; -- NULL
-- use COALESCE to handle NULLs:
SELECT COALESCE(username, '') || ' <' || COALESCE(email, '') || '>';
-- printf-style formatting (also called format())
SELECT printf('%s has %d orders', 'alice', 5); -- 'alice has 5 orders'
SELECT format('%,d', 1234567); -- '1,234,567' (3.38+)
SELECT printf('%5.2f', 3.14159); -- ' 3.14'
SELECT printf('%04d', 42); -- '0042'
-- quote() escapes a string for SQL
SELECT quote("It's alive"); -- '''It''s alive'''分割と検索
部分文字列の最初の出現を見つけるのが(1 起点、見つからなければ 0)、instr() です。ネイティブの SPLIT_PART が SQLite になく、substr + instr か再帰 CTE でエミュレートしましょう。「出現のカウント」の慣用句が、区切りの除去の前後の長さの引き算です。符号点から文字列を構築するのが、char() で、最初の文字の符号点を返すのが、unicode() です。フルのテキストの分割のためには、json_each 経由で、JSON の配列をロードしましょう。
-- find position of substring (1-indexed; 0 if not found)
SELECT instr('hello world', 'world'); -- 7
SELECT instr('hello', 'xyz'); -- 0
-- emulate split by getting the Nth field
-- (no native SPLIT_PART; use a CTE or substring + instr)
SELECT
substr('a,b,c,d', 1, instr('a,b,c,d', ',') - 1) AS first; -- 'a'
-- count occurrences of a substring
SELECT (length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))) / length(',') AS commas;
-- 3
-- reverse a string
SELECT reverse('hello'); -- 'olleh'
-- character from integer code
SELECT char(65, 66, 67); -- 'ABC'
SELECT unicode('A'); -- 65LIKE / GLOB / 照合順序
ASCII のためにデフォルトで大文字小文字を区別しないのが(頻出の驚き)、LIKE です。大文字小文字の敏感さのためには、COLLATE BINARY を。常に大文字小文字を区別し、シェルスタイルの文字クラスをサポートするのが、GLOB です。大文字小文字を区別しない比較のための組み込みの照合順序が、NOCASE で、大文字小文字を区別しないクエリがインデックスを使うよう、列のインデックスへ適用しましょう。非 ASCII の大文字小文字の折り畳みのためには、ICU の拡張をロードするか、インデックス付きの小文字化されたコピーの列を保存しましょう。