SQLiteの基本的な使い方がわかるチートシート

SQLiteは、サーバなしで使えるファイルベースのデータベースである。軽量でちゃちゃっと使えるくせに、SQL仕様をほぼ網羅している。お勉強に最適だ。

macOSではプリインストールされており、下準備なしで使える。もしインストールされていなければ、SQLiteの公式サイトからダウンロードできる。

データベースの作成

SQLiteプロジェクトでは、sqlite3というコマンドラインツールを提供している。起動には以下のコマンドを使う。

sqlite3 /path/to/データベース.db

データベースファイルへのパスを指定すると、ファイルが存在する場合はそのデータベースが開かれる。存在しない場合は、新たに作成される。SQLiteには、CEATE DATABASEのようなコマンドは存在せず、この方法でしかデータベースを作れない。その代わり、複数のデータベースに跨った操作を可能にする’アタッチ’という概念がある。

ちなみに、dbファイルを指定せずにsqlite3を起動すると、一時的なインメモリデータベースが自動で作成される。これはsqlite3が停止すると削除される。途中でデータベースを開きたいときは、.open /path/to/データベース.dbコマンドを使う。

ドットコマンド

sqlite3のコマンドは、ドットコマンドとPRAGMAコマンドに大別される。前者はコマンドラインツール自身に対する指示、後者はデータベースシステムに対する指示を行う。……らしいが、どこに境界があるのか正直よくわからない。

以下は基本的なドットコマンドの一例である。

バージョン情報の表示

sqliteのソースコード、圧縮ライブラリ、Cコンパイラのバージョンを表示。

.version

カラムのヘッダを表示

クエリ結果にカラムのヘッダー表示する。デフォルトはOFF。

.headers ON

データベースの一覧を表示

接続 or アタッチされているデータベースとパスの一覧を表示する。

.databases

テーブルの一覧を表示

データベース内のテーブル名の一覧を表示。

.tables

スキーマの表示

指定したテーブルのスキーマを表示。テーブル名を省略すると、データベース全体のスキーマが表示される。

.schema table_name

NULL値の表示を設定

NULLを表示する際の文字列を指定。

.nullvalue (NULL)

テーブルの出力形式を変更

テーブルの出力形式を変更する。選択肢はascii,box,csv,column,html,insert,json,line,list,markdown,quote,table,tabs,tcl。デフォルトはlist。

.mode table

出力先の変更

出力先をターミナル画面から任意のファイルに変更する。ターミナル画面に戻したい時はファイルパスを省略する。

.output path/to/output.txt

データベースの中身をダンプ

データベースの内容をSQL文として表示。バックアップに使える。デフォルトの出力先はターミナル画面。他のコマンドと同じように、.outputコマンドで任意のファイルに出力できる。

.dump

特定のテーブルをダンプ

テーブル名を渡すと、特定のテーブルのみをダンプできる。

.dump table_name

任意のファイルに出力したい場合は、例えば以下のようにする。

.output table_name_backup.sql
.dump table_name
.output

ダンプしたファイルの読み込み

任意のファイルを読み込み、SQLコマンドであれば実行する。そうでなければ、パースエラーを出力して終了する。

.read path/to/file

データベースのバックアップ

データベースのバックアップには、.backupコマンドを使う。dumpはSQL文が書かれたテキストファイルを出力するのに対し、こちらはデータベースのコピー（バイナリファイル）を出力する。

.backup dbname_backup.db

そのため.readコマンドでは読み込めず、sqlite3でデータベースとして開く。

プロンプトの終了

sqlite3を終了する。

.exit

ドットコマンドのヘルプを表示

ドットコマンドの一覧を概要付きで表示する。オプションなどの詳細は、公式ドキュメントのここに書かれている。

.help

PRAGMAコマンド

PRAGMAコマンドは、SQLiteライブラリの動作を変更したり、SQLiteライブラリに内部(テーブル以外の)データを問い合わせたりするために使用される。

らしいが、ざっとドキュメントを眺めても使うシチュエーションに馴染みがなく、よくわからなかった。経験を積みながら徐々にまとめていきたい。

一覧はここ。

以下は、PRAGMAコマンドの概要である。

• 最大1つの引数を受け取る。
• 引数は括弧で囲む（PRAGMA xx('yy')）。
• あるいは、等号でプラグマ名と区切る(PRAGMA xx = 'yy')。
• 1、yes、true、onは、大文字小文字問わず、いずれも真として解釈される。
• 0、no、false、offは、大文字小文字問わず、いずれも偽として解釈される。
• 文末に;をつける。

任意のテーブルの列の一覧を表示

列名だけの表示はできないため、テーブル情報を表示して確認する。

PRAGMA table_info(table_name);

以下のようにも書ける。

PRAGMA table_info = table_name;

外部キーが有効・無効を確認する

SQLiteでは、外部キーがデフォルトで無効化されている。はずだ。有効・無効の確認には、foreign_keysというコマンドを引数なしで使う。

PRAGMA foreign_keys;

外部キーの有効・無効を切り替える

外部キーを有効にする場合は真の値(1、yes、true、on)のいずれかを指定する。

PRAGMA foreign_keys = on;

無効にする場合は偽の値（0、no、false、off）のいずれかを指定する。

PRAGMA foreign_keys = off;

コメント

SQLiteのコメントは、以下のいずれかで記述する。

-- 1行

/*
  複数行コメント
*/

型アフィニティ

SQLiteは、その他のSQLデータベースエンジンと違い、カラム単位の厳密な静的型付けを使用していない。

ただ、完全にノールールではなく、型アフィニティという仕組みで緩めに動的な型付けを行う。’Affinity’という単語は、親和性や相性などの意味を持つ。一見ニュアンスが謎だ。物質同士の化学反応のしやすさを化学的親和性(Chemical affinity)と表現する。これと同じニュアンスである。多分。

例えば、あるカラムの型アフィニティにINTEGERが指定されている場合、’0’は数値の0として保存され、’零’はそのまま文字列として保存される。

指定できる型

型アフィニティには、どんな文字列でも指定できる（ドキュメントには、これに対する丁寧な説明も用意されている）。

そのためSQLiteでは、他のSQLデータベースエンジン用に書かれたコマンド柔軟に受け入れることができる

また、型に指定された文字列は、内部で以下の4つのいずれかとして解釈される。

これは、各データを挿入する際の変換規則にも通じる。例えばINTEGERやREALが指定されたカラムには文字列を入れられるが、TEXTが指定されたカラムに整数を入れようとすると、文字列に変換される。

以下は、それぞれのデータ型の変換ルールを確かめるためのサンプルコマンドである。

.header ON
.mode column
CREATE TABLE affinity_test(int_type INTEGER, real_type REAL, text_type TEXT, blob_type BLOB);
-- 整数
INSERT INTO affinity_test VALUES(1,1,1,1);
-- 整数に変換できる小数
INSERT INTO affinity_test VALUES(1.0,1.0,1.0,1.0);
-- 整数に変換できない小数
INSERT INTO affinity_test VALUES(1.1,1.1,1.1,1.1);
-- 整数にも小数にも変換できる文字列
INSERT INTO affinity_test VALUES('1.0','1.0','1.0','1.0');
-- 整数に変換できず、小数に変換できる文字列
INSERT INTO affinity_test VALUES('1.1','1.1','1.1','1.1');
-- 整数にも小数にも変換できない文字列
INSERT INTO affinity_test VALUES('壱','壱','壱','壱');
-- バイナリ
INSERT INTO affinity_test VALUES(x'01',x'01',x'01',x'01');

SELECT int_type, typeof(int_type), real_type, typeof(real_type), text_type, typeof(text_type), blob_type, typeof(blob_type) FROM affinity_test;
DROP TABLE affinity_test;

テーブルの作成

テーブルの作成には、CREATE TABLEコマンドを使う。

以下は、型アフィニティを指定せずにテーブルを作成するコマンドである。

CREATE TABLE table_name(column_name1, column_name2);

列に型アフィニティを指定

型アフィニティを指定する場合は、列名の後ろに空白を開けて以下のように書く。

CREATE TABLE table_name(column_name1 INTEGER, column_name2 TEXT);

なお、テーブル作成時には、INTEGER、REAL、TEXT、BLOBの4タイプに加えてNUMERICという型も指定できる。この型は、挿入されるデータをまずINTEGERに変換し、無理ならREALに寄せる。

制約

制約（Constraints）とは、列に保存できる値に定めるルールである。制約と誓約の制約だ。列に制約を掛けるには、以下のようなキーワードを型アフィニティの後ろにつける。型アフィニティを指定しない場合は列名の後ろにつける。

DEFAULTやCOLLATEは果たして制約なのかと思うが、この辺りは黙って飲み込んだほうがいい。

列に主キーを指定

主キーとは、その列に保存される値が一意である（重複がない）ことを保証する制約である。なんらかのIDに使われることが多い。基本的に、主キーは1つのテーブルに1つだけ指定できる。

以下は、primary_col列に整数型の主キーの働きをエラーメッセージで雑に確かめるサンプルコマンドである。

CREATE TABLE primary_key_sample(
  primary_col INTEGER PRIMARY KEY, -- 👈ここ
  normal_col
);
INSERT INTO primary_key_sample (normal_col) VALUES('hi');
INSERT INTO primary_key_sample (primary_col) VALUES(1);
DROP TABLE primary_key_sample;

SQLiteでは、INTEGER PRIMARY KEYでオートインクリメントが発動する。これは、該当列に値を指定しない場合に、自動でインクリメントした値を挿入してくれる仕組みである。

また、標準SQLでは、主キーの列にはNOT NULL制約が掛かるが、以下のケースを除きSQLiteではNULL値を挿入できる。

• 列がINTEGER PRIMARY KEY
• 列がNOT NULL
• STRICTテーブル
• WITHOUT ROWIDテーブル

参考：SQLite Autoincrement

また、INTEGER PRIMARY KEYを指定した列は、rowid列のエイリアスとなる。

rowidとは

WITHOUT ROWIDテーブルを除き、SQLiteのテーブルの全行は、その行を特定するための64ビットの符号つき整数をID(=rowid)を持つ。この列は、列定義に指定しなくても自動で作られる。

ただ、SELCT * FROM table_name;など、列名を明示しない形では参照できない。参照する場合は、rowid、oid、_rowid_のいずれかを使う。

SELECT rowid, oid, _rowid_ FROM table_name;

もし同名の列を定義している場合は、そちらが優先される。

ちなみに"特定のrowidを持つレコード、または指定された範囲内のrowidを持つすべてのレコードを検索する場合、他のPRIMARY KEYやインデックス値を指定して同様の検索を行うよりも約2倍高速"だそうである。

参考：ROWIDs and the INTEGER PRIMARY KEY

複数の列で主キーを指定

複数の列の組み合わせを主キーにすることもできる。この場合、列定義の丸括弧内の末尾に、PRIMARY KEY (col1, ...)を付ける。

CREATE TABLE primary_key_combi(
  depart_id INTEGER,
  member_id INTEGER,
  PRIMARY KEY (depart_id, member_id) -- 👈ここ
);
INSERT INTO primary_key_combi VALUES(1,1);
INSERT INTO primary_key_combi VALUES(1,1);
DROP TABLE primary_key_combi;

NULLを許さない

NOT NULLを使う。

CREATE TABLE not_null_sample(
  colmun_name NOT NULL -- 👈ここ
);
INSERT INTO not_null_sample VALUES(NULL);
DROP TABLE not_null_sample;

重複を許さない

UNIQUEを使う。PRIMARY KEYとの主な違いは、NULLを許すことと、1つのテーブル内で複数の列に指定できる点にある。

CREATE TABLE unique_sample(
  colmun_name UNIQUE -- 👈ここ
);
INSERT INTO unique_sample VALUES('HEY');
INSERT INTO unique_sample VALUES('HEY');
DROP TABLE unique_sample;

保存できる値を条件で絞る

列に保存できる値に条件を付与するには、CHECK (式)を使う。この式の結果は数値にキャストされ、結果が0（整数値0または実数値0.0）の場合に違反と判断される。

CREATE TABLE check_sample(
  in_col CHECK (in_col IN (0,1)), -- 👈ここ
  comp_col CHECK (comp_col = 1),-- 👈ここ
  between_col CHECK (between_col BETWEEN 0 AND 1),-- 👈ここ
  length_col CHECK (LENGTH(length_col) > 2) -- 👈ここ
);
INSERT INTO check_sample (in_col) VALUES(-1);
INSERT INTO check_sample (comp_col) VALUES(0);
INSERT INTO check_sample (between_col) VALUES(2);
INSERT INTO check_sample (length_col) VALUES(3);
DROP TABLE check_sample;

任意の列の値のみを許す

ある列の値を、別の列の値（親子関係を持つデータのidなど）のみに制限したい場合は、外部キー制約を使う。ただし、SQLiteでは、この制約はデフォルトで無効になっている。

そのため、使う前に有効化する必要がある。

PRAGMA foreign_key = on;
CREATE TABLE foreign_sample(
	id_col INTEGER PRIMARY KEY,
    foreign_col INTEGER REFERENCES foreign_sample(id_col) -- 👈ここ
);
INSERT INTO foreign_sample VALUES(1,1);
INSERT INTO foreign_sample VALUES(2,0);
DROP TABLE foreign_sample;

上記のコマンドでは、1つ目のINSERTコマンドは成功し、2つ目のINSERTコマンドは違反する。1つ目のINSERTのように、同じコマンドの中であっても、制約に指定した列に値が入っていれば問題はない。

列単位ではREFERENCES テーブル名(列名1,...)を使うが、テーブル単位の制約もできる。その場合、列定義の丸括弧の末尾にFOREIGN KEY(列名1,...) REFERENCES テーブル名(列名1,...)と書く。

PRAGMA foreign_key = on;
CREATE TABLE foreign_sample(
	id_col INTEGER PRIMARY KEY,
  foreign_col INTEGER,
  FOREIGN KEY(foreign_col) REFERENCES foreign_sample(id_col) -- 👈ここ
);
INSERT INTO foreign_sample VALUES(1,1);
INSERT INTO foreign_sample VALUES(2,0);
DROP TABLE foreign_sample;

サンプルでは同じテーブルの列の値を指定しているが、別のテーブルの列の値を指定してもよい。

列にデフォルト値を指定

列にデフォルト値を指定するには、型アフィニティの後ろにDEFAULT句を追加する。デフォルト値は、INSERTコマンドで列への値が省略された場合に使われる。

CREATE TABLE default_sample(
  normal_col,
  integer_text INTEGER DEFAULT 0, -- 👈ここ
  default_text TEXT DEFAULT 'default value', -- 👈ここ
  time_stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 👈ここ
);
INSERT INTO default_sample (normal_col) VALUES(NULL);
SELECT * FROM default_sample;
DROP TABLE default_sample;

TIMESTAMPというデータ型を指定することで、デフォルト値に以下の特別なキーワードも指定できる。これらは定数であるので、「’」や「"」は不要である。

照合順を指定する

文字列の照合順の指定には、COLLATEを使う。

CREATE TABLE collate_sample(
  binary_col TEXT COLLATE BINARY,
  nocase_col TEXT COLLATE NOCASE,
  rtrim_col TEXT COLLATE RTRIM
);
INSERT INTO collate_sample VALUES('B','B','B');
INSERT INTO collate_sample VALUES('a ','a ','a ');
INSERT INTO collate_sample VALUES('a','a','a');
SELECT binary_col, LENGTH(binary_col) FROM collate_sample ORDER BY binary_col;
SELECT nocase_col, LENGTH(nocase_col) FROM collate_sample ORDER BY nocase_col;
SELECT rtrim_col, LENGTH(rtrim_col) FROM collate_sample ORDER BY rtrim_col;
DROP TABLE collate_sample;

参考：7. Collating Sequences

任意の列の値のみを許す

ある列の値を、別の列の値（親子関係を持つデータのidなど）のみに制限したい場合は、外部キー制約を使う。ただし、SQLiteでは、この制約はデフォルトで無効になっている。

そのため、使う前に有効化する必要がある。

PRAGMA foreign_key = on;
CREATE TABLE foreign_sample (
	id_col INTEGER PRIMARY KEY,
    foreign_col INTEGER REFERENCES foreign_sample(id_col) -- 👈ここ
);
INSERT INTO foreign_sample VALUES (1,1);
INSERT INTO foreign_sample VALUES (2,0);
DROP TABLE foreign_sample;

上記のコマンドでは、1つ目のINSERTコマンドは成功し、2つ目のINSERTコマンドは違反する。1つ目のINSERTのように、同じコマンドの中であっても、制約に指定した列に値が入っていれば問題はない。

列単位ではREFERENCES テーブル名(列名1,...)を使うが、テーブル単位の制約もできる。その場合、列定義の丸括弧の末尾にFOREIGN KEY(列名1,...) REFERENCES テーブル名(列名1,...)と書く。

PRAGMA foreign_key = on;
CREATE TABLE foreign_sample (
	id_col INTEGER PRIMARY KEY,
    foreign_col INTEGER,
    FOREIGN KEY(foreign_col) REFERENCES foreign_sample(id_col)
);
INSERT INTO foreign_sample VALUES (1,1);
INSERT INTO foreign_sample VALUES (2,0);
DROP TABLE foreign_sample;

サンプルでは同じテーブルの列の値を指定しているが、別のテーブルの列の値を指定してもよい。

テーブルに厳格な型チェックを指定

version 3.37.0 (2021-11-27)以降では、テーブル単位で厳格な型チェックを行うテーブル(=STRICTテーブル)を作成できる。

以下のように、CREATE TABLEコマンドのお尻にSTRICTキーワードをつけるだけでいい。

CREATE TABLE table_name(column_name1 INTEGER) STRICT;

STRICTテーブルを作成する際は、各列に以下のいずれかのデータ型を指定する必要がある。

• INT
• INTEGER
• REAL
• TEXT
• BLOB
• ANY

これ以外の型を指定したり、省略したりするとパースエラーが出る。

また、カラムに指定されたアフィニティに変換できないデータを保存しようとすると、ラインタイムエラーが出る。変換できる場合は、ぬるりと保存される。

テーブルの削除

テーブルの削除には、DROP TABLEコマンドを使う。

DROP TABLE table_name;

テーブルに関する変更

テーブルに関する変更は、ALTER TABLEというコマンドと任意のサブコマンドの組み合わせて行う(‘alter’は部分的に変更する、修正するという意味の動詞である)。

テーブル名の変更

テーブル名を変更する場合は、以下のようにRENAME TOというサブコマンドを使う。

ALTER TABLE table_name RENAME TO new_table_name;

列の追加

列の追加には、ADD COLUMNというサブコマンドを使う。

ALTER TABLE table_name ADD COLUMN column_name;

列名の変更

列名の変更には、RENAME COLUMN TOというサブコマンドを使う。

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

列の削除

列の削除には、DROP COLUMNというサブコマンドを使う。

ALTER TABLE table_name DROP COLUMN column_name;

型アフィニティの確認

ドットコマンドの.schemaを使って、該当のテーブルのSQLコマンドで確認する。

.schema sample
-- 出力の例：CREATE TABLE sample(col1 int, col2 int);

型アフィニティの変更

カラムに指定した型アフィニティを変更することはできない。

新しくテーブルを作成し直し、データを丸ごと挿入し直すことで、同様の結果を得るしかない。

-- 念のためのバックアップ。復旧する際は`.read old_table_backup.sql`
.output old_table_backup.sql
.dump old_table
.output
--- ここから新しいテーブルの作成＆データの挿入し直し
CREATE TABLE new_table(column1 new_affinity1, column2 new_affinity2);
INSERT INTO new_table SELECT * FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

インデックス

今後追記予定

ビュー

今後追記予定

参考資料

• Command Line Shell For SQLite
• Query Language Understood by SQLite
• Datatypes In SQLite
• Built-In Scalar SQL Functions
• CREATE TABLEss
• ALTER TABLE