insert SQL insert statements for TABLE

line One value per line

list Values delimited by .separator string

tabs Tab-separated values

tcl TCL list elements

.nullvalue STRING Print STRING in place of NULL values

.output FILENAME Send output to FILENAME

.output stdout Send output to the screen

.prompt MAIN CONTINUE Replace the standard prompts

.quit Exit this program

.read FILENAME Execute SQL in FILENAME

.schema ?TABLE? Show the CREATE statements

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.tables ?PATTERN? List names of tables matching a LIKE pattern

.timeout MS Try opening locked tables for MS milliseconds

.width NUM NUM ... Set column widths for "column" mode

sqlite>

Changing Output Formats

The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

sqlite> .mode list

sqlite> select * from tbl1;

hello|10

goodbye|20

sqlite>

You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this:

sqlite> .separator ", "

sqlite> select * from tbl1;

hello, 10

goodbye, 20

sqlite>

In "line" mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign and the column data. Successive records are separated by a blank line. Here is an example of line mode output:

sqlite> .mode line

sqlite> select * from tbl1;

one = hello

two = 10

one = goodbye

two = 20

sqlite>

In column mode, each record is shown on a separate line with the data aligned in columns. For example:

sqlite> .mode column

sqlite> select * from tbl1;

one two

---------- ----------

hello 10

goodbye 20

sqlite>

By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:

sqlite> .width 12 6

sqlite> select * from tbl1;

one two

------------ ------

hello 10

goodbye 20

sqlite>

The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:

sqlite> .header off

sqlite> select * from tbl1;

hello 10

goodbye 20

sqlite>

Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.

When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:

sqlite> .mode insert new_table

sqlite> select * from tbl1;

INSERT INTO 'new_table' VALUES('hello',10);

INSERT INTO 'new_table' VALUES('goodbye',20);

sqlite>

The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning

Writing results to a file

By default, sqlite3 sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to begin writing to standard output again. For example:

sqlite> .mode list

sqlite> .separator |

sqlite> .output test_file_1.txt

sqlite> select * from tbl1;

sqlite> .exit

$ cat test_file_1.txt

hello|10

goodbye|20

$

Querying the database schema

The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.

For example, to see a list of the tables in the database, you can enter ".tables".

sqlite> .tables

tbl1

tbl2

sqlite>

The ".tables" command is similar to setting list mode then executing the following query:

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

In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you'll find exactly the above query.

The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices. We have:

sqlite> .schema

create table tbl1(one varchar(10), two smallint)

CREATE TABLE tbl2 (

f1 varchar(30) primary key,

f2 text,

f3 real

)

sqlite> .schema tbl2

CREATE TABLE tbl2 (

f1 varchar(30) primary key,

f2 text,

f3 real

)

sqlite>

The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:

SELECT sql FROM

(SELECT * FROM sqlite_master UNION ALL

SELECT * FROM sqlite_temp_master)

WHERE type!='meta'

ORDER BY tbl_name, type DESC, name

Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:

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

You can supply an argument to the .schema command. If you do, the query looks like this:

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

The "%s" in the query is replace by your argument. This allows you to view the schema for some subset of the database.

sqlite> .schema %abc%

Along these same lines, the ".table" command also accepts a pattern as its first argument. If you give an argument to the .table command, a "%" is both appended and prepended and a LIKE clause is added to the query. This allows you to list only those tables that match a particular pattern.

The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "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.

sqlite> .databases

Converting An Entire Database To An ASCII Text File

Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

A good way to make an archival copy of a database is this:

$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gzthat contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:

$ createdb ex2

$ sqlite3 ex1 .dump | psql ex2

Other Dot Commands

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

And finally, we mention the ".exit" command which causes the sqlite3 program to exit.

技術特點：

1. Berkeley DB是一個開放原始碼的內嵌式資料庫管理系統，能夠為應用程式提供高效能的資料管理服務。應用它程式設計師只需要呼叫一些簡單的API就可以完成對資料的訪問和管理。(不使用SQL語言)

2. Berkeley DB為許多程式語言提供了實用的API介面，包括C、C++、Java、Perl、Tcl、Python和PHP等。所有同資料庫相關的操作都由Berkeley DB函式庫負責統一完成。

3. Berkeley DB輕便靈活（Portable），可以執行於幾乎所有的UNIX和Linux系統及其變種系統、Windows作業系統以及多種嵌入式實時作業系統之下。Berkeley DB被連結到應用程式中，終端使用者一般根本感覺不到有一個資料庫系統存在。

4. Berkeley DB是可伸縮（Scalable）的，這一點表現在很多方面。Database library本身是很精簡的（少於300KB的文字空間），但它能夠管理規模高達256TB的資料庫。它支援高併發度，成千上萬個使用者可同時操縱同一個資料庫。Berkeley DB能以足夠小的空間佔用量執行於有嚴格約束的嵌入式系統。

Berkeley DB在嵌入式應用中比關聯式資料庫和物件導向資料庫要好，有以下兩點原因：

（1）因為資料庫程式庫同應用程式在相同的地址空間中執行，所以資料庫操作不需要程式間的通訊。在一臺機器的不同程式間或在網路中不同機器間進行程式通訊所花費的開銷，要遠遠大於函式呼叫的開銷；

（2）因為Berkeley DB對所有操作都使用一組API介面，因此不需要對某種查詢語言進行解析，也不用生成執行計劃，大大提高了執行效。

OpenBASE Lite是東軟集團股份有限公司開發的嵌入式資料庫產品。它是一個典型的輕量級資料庫，定製的資料庫引擎大小在250KB到600KB之間伸縮，可支援多種桌面作業系統、主流嵌入式系統平臺及不同的處理器。作為一款功能全面的關係型資料庫系統，OpenBASE Lite支援標準的SQL語法、ACID事務特性、備份/恢復等功能，提供了標準化開發介面JDBC、ODBC，能夠在嵌入式環境下沿用關聯式資料庫的經驗繼續來進行應用的開發。OpenBASE Lite提供了記憶體資料庫執行模式，提供高速的資料訪問與更新能力。

產品特色

1.完善的資料管理功能

OpenBASE Lite嵌入式資料庫具有完善的資料管理功能，提供了對SQL92標準子集的支援；提供對標準資料型別以及BLOB/CLOB型別的支援；支援資料庫完整性控制；具有完整的資料管理能力，可以處理GB級的資料量；並提供對空間資料的管理能力。

2.廣泛的平臺通用性

OpenBASE Lite嵌入式資料庫可執行於Windows 2000/2003/XP/Vista/Win 7、Windows Mobile 5&6、Windows CE、Linux、Embedded Linux、VxWorks、Symbian、Android等多種作業系統平臺。

3.微小的核心核心

OpenBASE Lite嵌入式資料庫具有微核心特性，可根據需求定製和裁剪，核心大小在250KB~600KB之間伸縮。

4.真正的零管理

在OpenBASE Lite嵌入式資料庫的使用過程中無需對資料庫進行配置，在移動終端應用中實現了“零管理”。

5.出色的處理效能

OpenBASE Lite嵌入式資料庫可以作為記憶體資料庫進行使用，實現了高速的資料訪問與更新，單條資料處理時間不超過15us；在併發處理效能上，提供庫級鎖與表級鎖併發訪問控制，提高了程式與多執行緒對資料庫併發訪問處理效能。

6.充分的安全保障

OpenBASE Lite嵌入式資料庫支援使用者身份認證以及資料庫物件的自主訪問控制，可以有效防止使用者資料的非法訪問；支援128-bit AES儲存加密，以保證資料庫檔案的安全性。

7.快速的故障恢復

提供了日誌和故障恢復機制有效地保障了事務的ACID特性，另外還提供了資料庫的聯機熱備與主從複製功能，使使用者可以簡單快速地提高應用系統的可靠性。

8.標準的訪問介面

OpenBASE Lite嵌入式資料庫為嵌入式應用的開發提供了C API介面以及JDBC/ODBC標準訪問介面。

9.豐富的實用工具

OpenBASE Lite提供了豐富的實用工具進行資料庫管理，包括資料庫圖形管理工具、建庫工具、查詢工具、備份恢復工具、匯入匯出工具及資料庫加解密工具等。