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
上記以外
日時
専用の型が無いので文字列 '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;