SQLite数据库管理

笔记内容来自《The Definitive Guide to SQLite》

导出数据

.dump命令可以将数据库对象导出成SQL格式。不带任何参数时，.dump将整个数据库导出为数据库定义语言（DDL）和数据操作语言（DML）命令。如下例子，将数据库导出到文件file.sql，输入如下命令：

sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout

file.sql里的内容可能如下：

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(id integer primary key, value text);
INSERT INTO "test" VALUES(1,'eenie');
INSERT INTO "test" VALUES(2,'meenie');
INSERT INTO "test" VALUES(3,'miny');
INSERT INTO "test" VALUES(4,'mo');
CREATE INDEX test_idx on test (value);
CREATE VIEW schema as select * from sqlite_master;
COMMIT;

导入数据

两种方法可以导入数据：

• 如果文件由SQL语句构成，使用.read命令
• 如果文件包含由逗号或者其他分隔符分隔的值（comma-separated values, CSV）组成，使用.import[file][table]命令，此命令将解析指定的文件并尝试将数据插入到指定的表中。

.read命令用来导入.dump命令创建的文件。如果使用前面作为备份文件所导出的file.sql，需要先移除已经存在的数据库对象（test表和schema视图），用下面的方法重新导入：

sqlite> drop table test;
sqlite> drop view schema;
sqlite> .read file.sql

格式化

.headers 设置为on时，查询结果显示时带有字段名。

.mode命令可以设置结果数据的几种输出格式。可选的格式有csv、column、html、insert、line、list、tabs和tcl，每种格式都有不同的用途。默认值是list。

.separator命令指定不同的分隔符。

.show命令可以查看分隔符的当前值。

sqlite> .output file.csv
sqlite> .separator ,
sqlite> select * from test;
sqlite> .output stdout

因为Shell中已经定义了一个CSV模式，所以下面的命令会得到相似的结果：

sqlite> .output file.csv
sqlite> .mode csv
sqlite> select * from test;
sqlite> .output stdout

文件file.csv内容如下所示：

1,eenie
2,meenie
3,miny
4,mo

导出带有分隔符的数据

例如，导出test表中以字母m开始的值并以逗号分隔。导出到text.csv文件。

sqlite> .output text.csv
sqlite> .separator ,
sqlite> select * from test where value like 'm%';
sqlite> .output stdout

将csv数据导入到与test表结构类似的表（称为test2），可以执行如下的命令：

sqlite> create table test2(id integer primary key, value text);
sqlite> .import text.csv test2

执行无人值守维护

可以使用命令模式批量处理运行CLP命令。有两种方法在命令行模式调用CLP。

第一种是提供SQL或SQLite shell命令，如.dump和.schema.

sqlite3 test.db .dump > test.sql

第二种是将定向到的一个文件作为输入流。例如从数据库dump文件test.sql中创建新的数据库test2.db,执行以下操作：

sqlite3 test2.db < test.sql

从test.sql文件创建数据库的另一种方式是使用init选项，并将test.sql作为参数：

sqlite3 -init test.sql test3.db

备份数据库

有两种方式可以完成数据库的备份。

1.SQL转储也许是移植性最好的备份。生成转储的标准方式是使用CLP.dump命令。

sqlite3 test.db .dump > test.sql

2.在Shell中，可以将输出重定向到外部文件。

sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout
sqlite> .exit

同样，容易将SQL转储作为CLP的输入流实现数据导入：

sqlite3 test.db < test.sql

备份二进制文件,一般而言二进制文件没有备份SQL移植性好

sqlite3 test.db vacuum
cp test.db test.Backup