sqlite3命令详解（中）

文章参照：www.sqlite.org/sqlite.html<wbr style="line-height:25px"><wbr style="line-height:25px"><div style="line-height:25px"><span style="line-height:25px; font-family:Arial,Helvetica,simsun,u5b8bu4f53"><span style="color:#003366; line-height:25px">在sqlite3中我们可以通过".help"命令来查所有的非SQL命令。</span></span></div> <div style="line-height:25px"><span style="line-height:25px; font-family:Arial,Helvetica,simsun,u5b8bu4f53">比如，<span style="line-height:25px">示例4</span>：</span></div> <div style="line-height:25px"> <span style="line-height:25px; font-family:Arial,Helvetica,simsun,u5b8bu4f53"></span> <div style="line-height:25px">sqlite&gt;<span style="color:#0000ff; line-height:25px">.help</span> </div> <div style="line-height:25px">.help</div> <div style="line-height:25px"> <span style="color:#0000ff; line-height:25px">.</span><span style="line-height:25px">backup ?DB? FILE</span> Backup DB (default "main") to FILE</div> <div style="line-height:25px">.bail ON|OFF Stop after hitting an error. Default OFF</div> <div style="line-height:25px"> <span style="line-height:25px">.databases</span> List names and files of attached databases</div> <div style="line-height:25px"> <span style="line-height:25px">.dump ?TABLE?</span>... <span style="color:#000080; line-height:25px">Dump the database in an SQL text format</span> </div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> If TABLE specified, only dump tables matching</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> LIKE pattern TABLE.</span></div> <div style="line-height:25px"> <span style="line-height:25px">.echo ON|OFF</span> Turn command echo on or off</div> <div style="line-height:25px"> <span style="line-height:25px">.exit</span> <span style="color:#ff6600; line-height:25px">Exit this program</span> </div> <div style="line-height:25px">.explain ?ON|OFF?<span style="line-height:25px"></span> Turn output mode suitable for EXPLAIN on or off.</div> <div style="line-height:25px"> With no args, it turns EXPLAIN on.</div> <div style="line-height:25px"> <span style="line-height:25px">.</span>genfkey ?OPTIONS? Options are:</div> <div style="line-height:25px"> --no-drop: Do not drop old fkey triggers.</div> <div style="line-height:25px"> --ignore-errors: Ignore tables with fkey errors</div> <div style="line-height:25px"> --exec: Execute generated SQL immediately</div> <div style="line-height:25px"> See file tool/genfkey.README in the source</div> <div style="line-height:25px"> distribution for further information.</div> <div style="line-height:25px"> <span style="line-height:25px">.header(s) ON|OFF</span> Turn display of headers on or off</div> <div style="line-height:25px"> <span style="line-height:25px">.help</span> Show this message</div> <div style="line-height:25px">.import FILE TABLE Import data from FILE into TABLE</div> <div style="line-height:25px">.<span style="line-height:25px">indices ?TABLE?</span> Show names of all indices</div> <div style="line-height:25px"> If TABLE specified, only show indices for tables</div> <div style="line-height:25px"> matching LIKE pattern TABLE.</div> <div style="line-height:25px">.<span style="line-height:25px">load FILE ?ENTRY?</span> Load an extension library</div> <div style="line-height:25px"> <span style="line-height:25px">.log FILE|off</span> Turn logging on or off. FILE can be stderr/stdout</div> <div style="line-height:25px"> <span style="line-height:25px">.mode MODE ?TABLE?</span> <span style="color:#000080; line-height:25px">Set output mode where MODE is one of:</span> </div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> csv Comma-separated values</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> column Left-aligned columns. (See .width)</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> html HTML &lt;table&gt; code</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> insert SQL insert statements for TABLE</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> line One value per line</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> list Values delimited by .separator string</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> tabs Tab-separated values</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> tcl TCL list elements</span></div> <div style="line-height:25px"> <span style="line-height:25px">.nullvalue STRING</span> <span style="color:#000080; line-height:25px">Print STRING in place of NULL values</span> </div> <div style="line-height:25px"> <span style="line-height:25px">.output FILENAME</span> <span style="color:#000080; line-height:25px">Send output to FILENAME</span> </div> <div style="line-height:25px"> <span style="line-height:25px">.output stdout</span> <span style="color:#000080; line-height:25px">Send output to the screen</span> </div> <div style="line-height:25px">.prompt MAIN CONTINUE Replace the standard prompts</div> <div style="line-height:25px"> <span style="line-height:25px">.quit</span> <span style="color:#ff6600; line-height:25px">Exit this program</span> </div> <div style="line-height:25px"> <span style="line-height:25px">.read FILENAME</span> Execute SQL in FILENAME</div> <div style="line-height:25px"> <span style="line-height:25px">.restore ?DB? FILE</span> Restore content of DB (default "main") from FILE</div> <div style="line-height:25px"> <span style="line-height:25px">.schema ?TABLE?</span> <span style="color:#000080; line-height:25px">Show the CREATE statements</span> </div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> If TABLE specified, only show tables matching</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> LIKE pattern TABLE.</span></div> <div style="line-height:25px"> <span style="line-height:25px">.separator STRING</span> <span style="color:#ff6600; line-height:25px">Change separator used by output mode and .import</span> </div> <div style="line-height:25px">.show Show the current values for various settings</div> <div style="line-height:25px"> <span style="line-height:25px">.tables ?TABLE?</span> <span style="color:#000080; line-height:25px"> List names of tables</span> </div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> If TABLE specified, only list tables matching</span></div> <div style="line-height:25px"><span style="color:#000080; line-height:25px"> LIKE pattern TABLE.</span></div> <div style="line-height:25px">.timeout MS Try opening locked tables for MS milliseconds</div> <div style="line-height:25px"> <span style="line-height:25px">.width NUM1 NUM2 ...</span><span style="color:#000080; line-height:25px">Set column widths for "column" mode</span> </div> <div style="line-height:25px">.timer ON|OFF Turn the CPU timer measurement on or off</div> <div style="line-height:25px">sqlite&gt;</div> <div style="line-height:25px"> <span style="color:#003366; line-height:25px">sqlite3能以8种不同的方式显示查询返回的结果：</span><strong>"</strong><span style="color:#ff6600; line-height:25px">csv</span><strong>"</strong><span style="color:#003366; line-height:25px">, "</span><span style="color:#ff6600; line-height:25px">column</span><span style="color:#003366; line-height:25px">", "</span><span style="color:#ff6600; line-height:25px">html</span><span style="color:#003366; line-height:25px">", "</span><span style="color:#ff6600; line-height:25px">insert</span><span style="color:#003366; line-height:25px">", "</span><span style="color:#ff6600; line-height:25px">line</span><span style="color:#003366; line-height:25px">", "</span><span style="color:#ff6600; line-height:25px">list</span><span style="color:#003366; line-height:25px">","</span><span style="color:#ff6600; line-height:25px">tabs</span><span style="color:#003366; line-height:25px">", "</span><span style="color:#ff6600; line-height:25px">tcl</span><span style="color:#003366; line-height:25px">".</span> </div> </div> </wbr></wbr>

你可以通过.mode来设置显示的方式。默认的是"list"方式，这时返回结果的一条记录显示一行，每列的内容之间用设定的分隔符隔开，

默认的分隔符是"|".

示例5：

sqlite>.mode list

sqlite>select * from tbl1;

hello|10

goodbye|20

sqlite>

你可以通过".separator"来设置"list"模式下的分隔符。比如我们想把", "作为分隔符，可以这样：

示例6：

sqlite>.separator ", "

sqlite>select * from tbl1;

hello, 10

goodbye, 20

sqlite>

"line"模式下, 每行只显示数据库的一行的一列。每行由列名，等号和列的值组成。每条记录之间由一个空行隔开。

比如，示例7

sqlite>.mode line

sqlite>select * from tbl1;

one = hello

two = 10

one = goodbye

two = 20

sqlite>

"column"模式下，每条记录都在单独一行显示。第一行显示的是列名，第二行只是用于分割列名和记录的数据，第三行开始才是记录的内容。

比如，示例8:

sqlite>.mode column

sqlite>select * from tbl1;

one two

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

hello 10

goodbye 20

sqlite>

默认情况下，每列的宽度最多只能显示10个字符。所以如果数据包含的字符如果他太多，可能就显示不完。

但是我们可以通过".width"设置每列的宽度。

比如，示例9：

sqlite>.width 12 6

sqlite>select * from tbl1;

one two

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

hello 10

goodbye 20

sqlite>

示例9就把第1列和第二列的宽度分别设置为了12和6个字符，其他列的宽度并没改变。

如果你把列宽设置为0，那么调整为以下三个的最大值：10，该列的列名字符数，第一行记录该列的字符数。这样列的宽度就可以自动调整。

默认的列宽就是0，以便它可以自动调整。

可以通过".header"命令可以设置是否显示头（头包括第一行，列名，第二行，分隔行）。

比如，示例10：

sqlite>.header off

sqlite>select * from tbl1;

hello 10

goodbye 20

sqlite>

"insert"模式下，返回的查询结果将以SQL的INSERT语句形式进行显示。

比如，示例11：

sqlite>.mode insert new_table

sqlite>select * from tbl1;

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

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

sqlite>

"html"模式下，查询返回结果将以XHTML table的形式进行显示，

它并不以<TABLE>作为开头和</TABLE>作为结尾。 但是每条记录以<TR>作为开始，</TR>作为结束，记录的数据以<TD>作为开始，以</TD>作为结束

比如，示例12：

sqlite>select * from system;

select * from system;

<TR><TD>1</TD>

<TD>volume_music</TD>

<TD>7</TD>

</TR>

<TR><TD>4</TD>

<TD>volume_voice</TD>

<TD>4</TD>

</TR>

........省略.........

sqlite>

".output"命令可以把查询返回结果的输出定向到文件上。该命令的第一个参数即是要定向的位置。在把输出定向了文件后，可以通过".output stdout"把输出重新定向到标准输出上。

示例13：

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

$