hakeの日記

Windows環境でプログラミングの勉強をしています。

SQLiteを使う

個人や小規模のデータベースで使用するSQLiteの勉強
作成されるデータベースファイルは一個なので手軽に管理できる。

入手先

以下のサイトのDownloadから入手する。現時点のバージョンは3.25.3。
Windows環境でプログラムからDLLを使用する場合は、sqlite-dll-win64-x64-3250300.zip または、sqlite-dll-win32-x86-3250300.zip
コマンドプロンプトPowerShellのコンソールから使用する場合は、sqlite-tools-win32-x86-3250300.zip内のsqlite3.exeを使用する。

あと以下のアプリでGUIを使ってデータベースファイルへアクセスできるのでインストールしておくと便利

 

SQLiteで使用できるデータ型

NULL

NULL

INTEGER

INTを含む型名
符号付整数(8バイトまで)

REAL

REAL/FLOA/DOUBを含む型名
浮動小数点数(8バイト)

TEXT

CHAR/CLOB/TEXTを含む型名
テキスト(UTF-8/UTF-16BE/UTF-16LE)

BLOB

バイナリデータ(BinaryLargeObject)
 

カラムで使用できるのデータ型(上記以外)

入力されたデータによって適切な型にして格納されるみたい。でも基本的に使用しない方が良さげ。

NONE

BLOBを含む型名/指定無しの場合

NUMERIC

上記以外
 

文字リテラル

文字列はシングルクォートで囲む

'sample'

シングルクォートのエスケープは2回連続する。

'a''b' -> データ a'b

 

日時

専用の型が無いので文字列 'yyyy-mm-dd hh:mm:ss' とするか、unixepochに変換して整数で管理する。
タイムゾーンutcなので注意。'now' など使用するとutcの日時が返るので、JSTにする場合は、datetime( 日時, 'localtime' ) などで変換する。逆変換は 'localtime' を 'utc' にする。
 

コマンドラインでの起動

C:\> sqlite3.exe  または
C:\> sqlite3.exe  databasefile

SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

プロンプトが"sqlite>"になる
 

コマンド(抜粋)

.help		ヘルプ
.open		データベースファイルオープン
.show		現在の設定を表示
.mode		出力モード設定
	list(初期値)	区切り文字で区切り1行で出力
	csv		カンマ区切り
	column	カラム毎に左揃え
	html	htmlテーブル形式
	insert	insert文とする
	line	カラム毎に改行
	tabs	タブ区切り
	tcl		tlcのlist形式
.tables		TABLE一覧(テーブル名)
.indices	INDEX一覧
.schema		TABLE構造の取得(CREATE文の表示)
			.schema テーブル名
.header		ON/OFF カラム名表示ON/OFF
.version	バージョン表示
.separator	カラム区切りと改行コードの設定(初期値 '|' \n)
			注:.output出力の改行コードがLFなので注意
			  Windows環境の場合は
				.output '|' \r\n を実行する
			  .exitして再接続すると初期値に戻る
.import		テーブルのインポート
			.import ファイル名 作成済みのテーブル名
			.import ./sample.txt TABLE_NAME
.output		出力先を変更する(初期値stdout)
			.output ./test.log
.exit		終了


select * from sqlite_master where type=…; テーブル情報の取得

 

基本操作

sqlite> .header on
sqlite> .mode column

テーブル作成
sqlite> create table AAA(ii integer, tt text);

テーブル名表示
sqlite> .tables
AAA

テーブル構造表示
sqlite> .schema
CREATE TABLE AAA(ii integer, tt text);

レコード入力
sqlite> insert into AAA values(1, 'aaa');
sqlite> insert into AAA values(null,'bbb');
sqlite> insert into AAA(tt) values('ccc');
sqlite> insert into AAA values(4,'ddd'),(5,'eee');

データ表示
sqlite> select * from AAA;
ii          tt
----------  ----------
1           aaa
            bbb
            ccc
4           ddd
5           eee

カラム追加
sqlite> alter table AAA add column xx real;
sqlite> .schema
CREATE TABLE AAA(ii integer, tt text, xx real);
sqlite> insert into AAA values(6,'fff',3.14);
sqlite> select * from AAA;
ii          tt          xx
----------  ----------  ----------
1           aaa
            bbb
            ccc
4           ddd
5           eee
6           fff         3.14


データの更新
sqlite> update AAA set ii=2 where tt='bbb';
sqlite> select * from AAA;
ii          tt          xx
----------  ----------  ----------
1           aaa
2           bbb
            ccc
4           ddd
5           eee
6           fff         3.14


テーブル名変更
sqlite> alter table AAA rename to BBB;
sqlite> .tables
BBB

カラム削除(コマンドがないので新テーブルに必要なカラムをコピー)
sqlite> create table CCC(ii integer, tt text);
sqlite> begin transaction;
sqlite> insert into CCC(ii,tt) select ii,tt from BBB;
sqlite> commit;
sqlite> select * from CCC;
ii          tt
----------  ----------
1           aaa
2           bbb
            ccc
4           ddd
5           eee
6           fff

レコード削除
sqlite> delete from CCC where ii=1;
sqlite> select * from CCC;
ii          tt
----------  ----------
2           bbb
            ccc
4           ddd
5           eee
6           fff

テーブル削除
sqlite> drop table BBB;
sqlite> .tables
CCC

ファイルサイズを縮小(dropしてもファイルサイズは小さくならない)
sqlite> vacuum;