sqlite3提供了多個命令來查看資料庫的schema
".tables"命令可以查看當前資料庫所有的表比如，樣本14：sqlite> .tablestbl1tbl2sqlite>".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.tablesandroid_metadata bookmarks systembluetooth_devices securesqlite> .tables s%.tables s%secure sqlite_sequence systemsqlite>".indices"命令列出指定表的所有indices（索引）。第一個參數為表的名字。比如，樣本15：sqlite> .schema system.schema systemCREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT REPLACE,value TEXT);CREATE INDEX systemIndex1 ON system (name);sqlite> .tables.tablesandroid_metadata bookmarks systembluetooth_devices securesqlite> .indices system.indices systemsqlite_autoindex_system_1systemIndex1sqlite>".schema"命令，在沒有參數的情況，它會顯示最初用於建立資料庫的CREATE
TABLE和CREATE INDEX的SQL語句。比如，樣本16".schema"命令可以包含一個參數，它是一個pattern，用於對錶進行過濾，這時只會顯示滿足條件的表和所有索引的SQL語句。比如，樣本15和樣本17.樣本16：sqlite> .schema.schemaCREATE TABLE android_metadata (locale TEXT);CREATE TABLE bluetooth_devices (_id INTEGER PRIMARY KEY,name TEXT,addr TEXT,channel INTEGER,type INTEGER);CREATE TABLE bookmarks (_id INTEGER PRIMARY KEY,title TEXT,folder TEXT,intent TEXT,shortcut INTEGER,ordering INTEGER);CREATE TABLE secure (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT REPLACE,value TEXT);CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT 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 CONFLICT REPLACE,value TEXT);CREATE TABLE sqlite_sequence(name,seq);CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT 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 ex2The ".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> .explainsqlite> 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 0The ".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會先把他們轉化為分號";"，然後再傳遞到該函數.在源碼中編譯sqlite3The 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".
