sqlite3の基本操作まとめ

Ruby on Railsが2.0になってdefaultのDBがsqlite3になりました。　その関係でsqlite3を調査したまとめ。

0.概要
MySQLと違って常駐プロセスは無い。ユーザはsqlite3コマンドでファイルのデータベースを操作する。１ファイルが１データベース。


1.sqlite3の起動
テーブルとか作成した時点でディレクトリ内にDBファイルが生成されるので、初回指定時にはDBファイルは存在していなくてもよい。
$ sqlite3 <DBファイル>

例：mydatabase.dbファイルをデータベースファイルとしてsqlite3を起動する。
$ sqlite3 mydatabase.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>
sqlite>

2.sqlite3の終了
sqlite> .exit または
sqlite> .quit

3.sqlite3のヘルプ
sqlite> .help

4.テーブルの作成
sqliteプロンプト上でcreate文を発行するだけ。
sqlite> create table sampletb1(one varchar(10), two smallint);
sqlite> insert into sampletb1 values('hello',10);
sqlite> insert into sampletb1 values('goodbye',20);
sqlite> select * from sampletb1;
hello|10
goodbye|20
sqlite>

5.マスターテーブルの参照
データベース管理用テーブル（sqlite_master）の参照。
sqlite> select * from sqlite_master;
table|sampletb1|sampletb1|2|CREATE TABLE sampletb1(one varchar(10), two smallint)
sqlite>

6.スキーマの参照
sqlite> .schema　または
sqlite> .schema <テーブル名>
例：
sqlite> .schema
CREATE TABLE sampletb1(one varchar(10), two smallint);
sqlite> .schema tbl2;
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
);
sqlite>

7.利用しているデータベースの参照
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/arai/sqlite3/mydatabase.db

8.テーブルのダンプ
sqlite> .dump sampletb1
BEGIN TRANSACTION;
CREATE TABLE sampletb1(one varchar(10), two smallint);
INSERT INTO "sampletb1" VALUES('hello', 10);
INSERT INTO "sampletb1" VALUES('goodbye', 20);
COMMIT;

9.環境設定情報の参照
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:

10.コマンドエコーのON/OFF
sqlite> .echo ON
sqlite>
←リターンのエコー
sqlite> .echo OFF
.echo OFF
sqlite>

11.結果出力時のヘッダーのON/OFF
sqlite> .headers ON
sqlite> seleect * from sampletb1;
one|two ←ヘッダー表示
hello|10
goodbye|20
sqlite>
sqlite> .headers OFF
sqlite> select * from sampletb1;
hello|10
goodbye|20
sqlite>

12.インデックスの表示
sqlite> .indices tbl2
sqlite_autoindex_tbl2_1
sqlite>

13.出力モードの設定（line）
sqlite> .mode line
sqlite> select * from tbl2;
f1 = hello
f2 = today is a fine day.
f3 = 123.45
sqlite>

14.出力モードの設定（insert）
sqlite> .mode insert
sqlite> select * from tbl2;
INSERT INTO table VALUES('hello','today is a fine day.',123.45);
sqlite>

15.出力モードの設定（column）
sqlite> .mode column
sqlite> select * from tbl2;
hello today is a fine day. 123.45
sqlite>

16.出力モードの設定（html）
sqlite> .mode html
sqlite> select * from tbl2;
<TR><TD>hello</TD>
<TD>today is a fine day.</TD>
<TD>123.45</TD>
</TR>
sqlite>

17.出力モードの設定（html）かつヘッダーがON
sqlite> .mode html
sqlite> .headers ON
sqlite> select * from tbl2;
<TR><TH>f1</TH><TH>f2</TH><TH>f3</TH></TR>
<TR><TD>hello</TD>
<TD>today is a fine day.</TD>
<TD>123.45</TD>
</TR>
sqlite>

18.出力モードの設定（list）
sqlite> .mode list
sqlite> select * from tbl2;
hello|today is a fine day.|123.45
sqlite>

19.結果のファイル出力
sqlite> .output <出力ファイル名>
例：
sqlite> .output mydatabase.output ←出力ファイル（mydatabase.output）の指定
sqlite> select * from tbl2; ←結果はmydatabase.outputに出力
sqlite> select * from sampletb1; ←結果はmydatabase.outputに出力
sqlite> .output stdout ←標準出力に戻す
sqlite> .quit
$ ls
mydatabase.db mydatabase.output　　←ファイルができている。

20.区切り文字の設定
sqlite> .separator ] ←結果の区切り文字を']'に設定
sqlite> select * from tbl2;
hello]today is a fine day.]123.45
]null data check]0.0
sqlite>
sqlite> .separator | ←元に戻す
sqlite>
sqlite> select * from tbl2;
hello|today is a fine day.|123.45
|null data check|0.0
sqlite>

21.結果がnull値だった時に表示する文字の指定
sqlite> .nullvalue "NULL"
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: "NULL" ←NULLが設定
output: stdout
separator: "|"
width:
sqlite>

22.表示項目の表示幅の設定
sqlite> .width 10 20 5　←項目1:10文字分、項目2:20文字分、項目3:5文字分の幅を指定
sqlite> .mode column ←column時に有効
sqlite> .select * from tbl2;
f1 f2 f3
---------- -------------------- -----
hello today is a fine day. 123.4
null data check 0.0
sqlite>

23.テーブルの表示
sqlite> .tables
sampletb1 tbl2
sqlite>

24.データベース内容のダンプ
$ sqlite3 <DBファイル> .dump
例：
$ sqlite3 mydatabase.db .dump
BEGIN TRANSACTION;
CREATE TABLE sampletb1(one varchar(10), two smallint);
INSERT INTO "sampletb1" VALUES('hello', 10);
INSERT INTO "sampletb1" VALUES('goodbye', 20);
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
);
INSERT INTO "tbl2" VALUES('hello', 'today is a fine day.', 123.45);
INSERT INTO "tbl2" VALUES('', 'null data check', 0.0);
COMMIT;
$

あとはSQLを理解していれば大抵のことは問題ないはず。

以上

コメント 2

非常にまとまっていて、
大変参考になりました。
私もruby/rails勉強はじめたところなのですが、
ブログもはじめたばっかりです。よかったらみて
やってください。
また寄せていただきます。
by tkmrkt (2010-03-06 01:16) 

どもっ。thkmrktさん。コメントありがとうございます。
Ruby on Railを使って、とりあえず書籍リストと模型リストのアプリを作りました。ホントにお手軽にできますね。DBもエクセル代替程度なのでSQLiteで十分ですし。
by terry (2010-03-06 20:31)