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的文件，它包含了重新构建数据库的所有信息

重新构建数据库。只需要如下的语句:

$ 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 0 2

6 Key 0 0

7 ListWrite 0 0

8 Goto 0 2

9 Noop 0 0

10 ListRewind 0 0

11 ListRead 0 14

12 Delete 0 0

13 Goto 0 11

14 ListClose 0 0

The ".timeout" command sets the amount of time that the sqlite3 program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.

".exit和“.quite"命令用于退出sqlite3程序.他们好像没有什么区别

如何以Shell脚本的方式使用sqlite3命令

一种方式是：用"echo"或"cat"命令输出一些sqlite3命令到一个文件，然后执行程序sqlite3，并把该文件作为sqlite3的输入流。这种方式不错，很多程序都可以这样。

另一种方式是：以SQL语句作为sqlite3的第二个参数，在执行SQL操作。为了方便， sqlite3允许在第一个参数数据库名后，再跟一个参数，来指定要执行的SQL语句。如果sqlite3带2个参数进行启动的话，第二个参数将做为SQL传递给SQLite library来处理, 返回结果将以list模式在标准输出中进行显示，然后sqlite3程序也退出了。

比如，示例17：

# sqlite3 /data/data/com.android.providers.settings/databases/settings.db "select * from system;select * from system"

以SQL语句作为sqlite3的第二个参数，这种方式主要是为了sqlite3和其他程序（比如"awk"）联合使用.

比如，示例18:

$sqlite3 ex1 'select * from tbl1' |

>awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'

<tr><td>hello<td>10

<tr><td>goodbye<td>20

$

SQL语句的结束符

一般sqlite3的SQL语句的结束符是分号";". 然而你在shell中运行sqlite3时你还可以使用 "GO" (大写) 或 "/"来作为一条SQL语句结束的标志. 他们分别在SQL Server和Oracle中被使用. 但是他在sqlite3_exec()不能使用shell会先把他们转化为分号";"，然后再传递到该函数.

在源码中编译sqlite3

The sqlite3 program is built automatically when you compile the 当你编译SQLite library的时候，sqlite程序就自动被编译了. Just get a copy of the source tree, run "configure" and then "make".