sql for sqlite

2018-05-12 00:00

SQliteは、型を指定しなくても CREATE TABLE できるのが特徴。型指定するなら、integer, real, text, blob型を使える。

CREATE TABLE IF NOT EXISTS mytable (
id INT,
description TEXT,
score REAL
);

トランザクション

BEGIN TRANSACTION;
:
COMMIT or ROLLBACK;

クエリ計画の表示

EXPLAIN QUERY PLAN ～;

パフォーマンス対策

REINDEX [target];
VACUUM;
ANALYZE;

日付・時刻関数

日付・時刻の型は無いが、関数を使って扱うことができる。

現在日時の例(デフォルトでUTCとして扱われる)

SELECT current_timestamp, date('now'), time();

文字列で特定日時を指定する。

SELECT datetime('1970-01-01 00:00:00')||' はUTCの時刻';

修飾子にlocaltimeを指定すると、ローカルのタイムゾーンに変換。(JSTの場合、9時間後)

SELECT datetime('1970-01-01 00:00:00+09:00')||' はUTCなので、JSTは '||datetime('1970-01-01 00:00:00+09:00', 'localtime');
SELECT datetime('now')||' はUTCなので、JSTは '|| datetime('now', 'localtime')x;

修飾子にutcを指定すると、対象の日付がローカルのタイムゾーンのものとして、utcに変換。(JSTの場合、9時間前)

SELECT datetime('now')||' がJSTだとしたら、UTCは '||datetime('now', 'utc');

その他、修飾子を使うことができる。

SELECT datetime('now', '+1years', '+1months', '+1days', '+1hours', '+1minutes', '+1seconds');
SELECT '今年の第1月曜日は '||date(datetime('now', 'start of year'), 'weekday 1');

条件式として使うことができる

SELECT * FROM mytable WHERE date BETWEEN datetime('now', '-3hours') AND datetime('now');
SELECT * FROM mytable WHERE date(date) = date('now');

strftime書式を使うことができる

SELECT strftime('%s', '1970-02-01 00:00:00', '-1 minutes');
SELECT datetime(strftime('%s', '1970-01-01 00:00:00'), 'unixepoch');

集約関数

ccount, avg, min, maxなどの集約関数が使える。

SELECT col1 AS c1, aggregate(col2) AS c2
FROM mytable
WHERE condition 集計する対象を絞り込む。
GROUP BY c1
HAVING c2 <> 0; 集計結果から、出力する対象を絞り込む

コア関数

引数の中で、最初に現れたNULLでない値を返す (この例では「全部null」)

SELECT coalesce(null, null+'any', null+1, null*1, null/1, 1/0, '全部null');

制約について

CHECK制約の列制約の例

CREATE TABLE IF NOT EXISTS point (
  pointname TEXT CHECK(length(pointname) <= 8),
  latitude  REAL NOT NULL CHECK(latitude  BETWEEN  -90.0 AND  90.0),
  longitude REAL NOT NULL CHECK(longitude BETWEEN -180.0 AND 180.0),
  CONSTRAINT point_primary PRIMARY KEY (pointname)
);

表制約の例(制約名は省略可能)

DROP TABLE IF EXISTS point;
CREATE TABLE IF NOT EXISTS point (
  pointname TEXT PRIMARY KEY,
  latitude  REAL NOT NULL,
  longitude REAL NOT NULL,
  CHECK(length(pointname) <= 8),
  CHECK(latitude  BETWEEN  -90.0 AND  90.0),
  CHECK(longitude BETWEEN -180.0 AND 180.0)
);

外部キーの例

列制約

CREATE TABLE IF NOT EXISTS route (
  routename TEXT CHECK(length(routename) <= 8),
  seq       INT  NOT NULL,
  pointname TEXT NOT NULL REFERENCES point(pointname),
  PRIMARY KEY (routename, seq)
);

表制約

DROP TABLE IF EXISTS route;
CREATE TABLE IF NOT EXISTS route (
  routename TEXT CHECK(length(routename) <= 8),
  seq       INT  NOT NULL,
  pointname TEXT NOT NULL,
  PRIMARY KEY(routename, seq),
  FOREIGN KEY(pointname) REFERENCES point(pointname)
);

SQlite 3.6.19から外部キーの機能が導入されたが、デフォルトでは機能しない。 (外部キーのDDLはエラーにならない。外部キー制約で拒否されるはずの値が拒否されない。) 制約を有効にするためには(データベースを開くたびに)設定が必要。

PRAGMA foreign_keys;
0→オフ
1→オン
PRAGMA foreign_keys = [ON|OFF];

(補足: sqlite_version()関数でバージョンを確認できる。)

デフォルト値

DROP TABLE IF EXISTS track;
CREATE TABLE IF NOT EXISTS track (
  trackname TEXT,
  timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
  latitude  REAL NOT NULL,
  longitude REAL NOT NULL,
  PRIMARY KEY(trackname, timestamp),
  CHECK(length(trackname) <= 8),
  CHECK(datetime(timestamp) IS NOT NULL
    AND datetime(timestamp) >= datetime('1970-01-01 00:00:00')),
  CHECK(latitude  BETWEEN  -90.0 AND  90.0),
  CHECK(longitude BETWEEN -180.0 AND 180.0)
);

結合

内部結合

SELECT r.routename, r.seq, p.latitude, p.longitude FROM point AS p, route AS r
WHERE r.pointname = p.pointname;

SELECT r.routename, r.seq, p.latitude, p.longitude FROM point AS p
JOIN route AS r
WHERE r.pointname = p.pointname;

SELECT r.routename, r.seq, p.latitude, p.longitude FROM point AS p
JOIN route AS r
ON r.pointname = p.pointname;

SELECT r.routename, r.seq, p.latitude, p.longitude FROM point AS p
JOIN route AS r
USING (pointname);

外部結合 (LEFT OUTER JOINは実装されているが、RIGHT と FULL OUTER JOINは実装されていない。)

SELECT r.routename, r.seq, p.latitude, p.longitude FROM point AS p
LEFT OUTER JOIN route AS r
ON r.pointname = p.pointname;

自然結合

SELECT r.routename, r.seq, p.latitude, p.longitude FROM point AS p
NATURAL JOIN route AS r;

その他

CASE文

SELECT
  CASE strftime('%M','now') % 3
    WHEN 0 THEN 'is 0'
    WHEN 1 THEN 'is 1'
    ELSE 'is 2'
  END,
  CASE
    WHEN strftime('%M','now') % 2 = 0 THEN 'even'
    ELSE 'odd'
  END
;

sqlite

2017-08-19 00:00

SQLiteはRDBMSのデータベースエンジン。サーバではなくライブラリで、とても小さく軽量。

ドキュメント

ここではコマンドラインインタフェース sqlite3 の利用について記載する。

データベースファイルを指定して起動

sqlite3 database.db
-column 左寄せのカラム形式で表示
-header 列の見出しを表示

コマンドラインでSQL実行

sqlite3 database.db 'SELECT count(*) FROM mytable;'

echo 'SELECT count(*) FROM mytable;' | sqlite3 database.db

cat << quit | sqlite3 database.db
SELECT count(*) FROM mytable;
quit

sqlite3 database.db < filename.sql

主なメタコマンド

データベースファイルの確認

.databases

存在する表の確認

.tables

表のDDLの確認

.schema

表のインデックス名の確認

.indices 表名

DDLとデータ内容をSQLでダンプ

.dump [表名]
表名を省略すると全てのテーブルが対象。

ファイルに記載されたSQLを実行

.read ファイル名

CSV形式で表示

.header on
.mode csv
.output ファイル名
SELECT * FROM ...;
.output stdout

CSVファイルの内容を表へインポート

.import database.csv mytable

データベース全体のダンプとリストア

sqlite3 database.db .dump | gzip -c > database.dump.gz
zcat database.dump.gz | sqlite3 database_test.db
(ダンプはSQLなので、sqlite以外の環境へもリストア可能)

バックアップとリストア

cat >> test1.sql << END
SELECT count(*) FROM test;
.backup tmp.backup
DROP TABLE test;
SELECT count(*) FROM test;
.restore tmp.backup
SELECT count(*) FROM test;
END

dump sqlite3 and import to postgresql

$ sqlite3 database_test.db << quit
> .output dump.sql
> .dump
> quit
$ sed -i.bak -e '/^PRAGMA/ s/^/-- /' dump.sql
$ sudo docker run --rm -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -d postgres:alpine
    $ sudo docker exec -i xxxxx psql -U postgres << quit
> CREATE USER dbuser;
> CREATE DATABASE dbuser;
> ALTER ROLE dbuser WITH PASSWORD 'dbuserpassword';
> GRANT ALL PRIVILEGES ON DABABASE dbuser TO dbuser;
> ALTER DATABASE dbuser OWNER TO dbuser;
    > quit
$ cat dump.sql | sudo docker exec -i xxxx psql -U dbuser

tech log

Posts about rdbms

sql for sqlite

sqlite