sqlite3命令详解（下）
sqlite3提供了多个命令来查看数据库的schema
".tables"命令可以查看当前数据库所有的表
比如，示例14：
sqlite> .tables
tbl1
tbl2
sqlite>
".tables"和在list模式下执行下面的语句相似:
SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1
实际上, 如果你查看sqlite3程序的源码(found in the source tree in the file src/shell.c)，you'll find exactly the above query.
另外，".tables"命令后也可以跟一参数，它是一个pattern，这样命令就只列出表名和该参数匹配的表。
比如，示例14-1：
sqlite> .tables
.tables
android_metadata bookmarks system
bluetooth_devices secure
sqlite> .tables s%
.tables s%
secure sqlite_sequence system
sqlite>
".indices"命令列出指定表的所有indices（索引）。第一个参数为表的名字。
比如，示例15：
sqlite> .schema system
.schema system
CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON C
ONFLICT REPLACE,value TEXT);
CREATE INDEX systemIndex1 ON system (name);
sqlite> .tables
.tables
android_metadata bookmarks system
bluetooth_devices secure
sqlite> .indices system
.indices system
sqlite_autoindex_system_1
systemIndex1
sqlite>
".schema"命令，在没有参数的情况，它会显示最初用于创建数据库的CREATE TABLE和CREATE INDEX的SQL语句。比如，示例16
".schema"命令可以包含一个参数，它是一个pattern，用于对表进行过滤，这时只会显示满足条件的表和所有索引的SQL语句。
比如，示例15和示例17.
示例16：
sqlite> .schema
.schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE bluetooth_devices (_id INTEGER PRIMARY KEY,name TEXT,addr TEXT,chan
nel INTEGER,type INTEGER);
CREATE TABLE bookmarks (_id INTEGER PRIMARY KEY,title TEXT,folder TEXT,intent TE
XT,shortcut INTEGER,ordering INTEGER);
CREATE TABLE secure (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON C
ONFLICT REPLACE,value TEXT);
CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON C
ONFLICT REPLACE,value TEXT);
CREATE INDEX bookmarksIndex1 ON bookmarks (folder);
CREATE INDEX bookmarksIndex2 ON bookmarks (shortcut);
CREATE INDEX secureIndex1 ON secure (name);
CREATE INDEX systemIndex1 ON system (name);
sqlite>
示例17：
sqlite> .schema s%
.schema s%
CREATE TABLE secure (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON C
ONFLICT REPLACE,value TEXT);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON C
ONFLICT REPLACE,value TEXT);
CREATE INDEX secureIndex1 ON secure (name);
CREATE INDEX systemIndex1 ON system (name);
sqlite>
".schema"命令功能和下面的语句相似:
SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' ORDER BY tbl_name, type DESC, name
如果你传了一个参数给".schema"，以表明只想得到表的schema而包括索引的schema，那么SQL语句应该如下:
SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type,2,1), name
如果你想 “.schema”支持参数. 那么SQL语句应该如下:
SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE tbl_name LIKE '%s' AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type,2,1), name
这里SQL语句中的"%s"将被你传入的参数代替. 你就可以只显示一部分的schema.
事实上".tables"的也是采用这种"LIKE"的方式，进行pattern查询的。
".databases"命令将列出当前connection中所有的数据库。
一般至少包含2个，一个是"main", the original database opened.另一个是"temp", the database used for temporary tables.
There may be additional databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with, and the second column is the filename of the external file.
".dump"命令将把database的内容转化为一个ASCII编码的文本文件。
This file can be converted back into a database by piping it back into sqlite3.
把一个数据库进行archival备份可以用如下的命令：
$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz
这样将生产一个名叫ex1.dump.gz的文件，它包含了重新构建数据库的所有信息
重新构建数据库。只需要如下的语句: www.zzzyk.com
$ zcat ex1.dump.gz | sqlite3 ex2
因为文本格式是纯SQL的 ，所以你可以通过.dump命令把你的数据库导入到另外的更常用的数据库引擎.
比如:
$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2
The ".explain" dot command can be used to set the output mode to "column" and
to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command.
The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging.
If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed.
Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result.
For example:
sqlite> .explain
sqlite> explain delete from tbl1 where two<20;
addr opcode p1 p2 p3
---- ------------ ----- ----- -------------------------------------
0 ListOpen 0 0
1 Open 0 1 tbl1
2 Next 0 9
3 Field 0 1
4 Integer 20 0
5 Ge &nb
