".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.

sqlite> .tables

.tables

android_Metadata bookmarks system

bluetooth_devices secure

sqlite> .tables s%

.tables s%

secure sqlite_sequence system

sqlite>

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>

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,value TEXT);

CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,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>

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,255)"> 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,name

这里sql语句中的"%s"将被你传入的参数代替. 你就可以只显示一部分的schema.

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：

以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".