SQliteで使える言語 について
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
;