SQLite命令行_.mode csv

1.入门

SQLite项目提供了一个名为sqlite3（或Windows上的sqlite3.exe）的简单命令行程序 ，该程序允许用户针对SQLite数据库或ZIP存档手动输入和执行SQL语句 。本文档简要介绍了如何使用sqlite3程序。

通过在命令提示符下键入“ sqlite3”来启动sqlite3程序，还可以在其后加上保存SQLite数据库（或ZIP存档）的文件的名称。如果命名文件不存在，将自动创建具有给定名称的新数据库文件。如果在命令行上未指定数据库文件，则会创建一个临时数据库，然后在退出“ sqlite3”程序时将其删除。

在启动时，sqlite3程序将显示一条简短的标语消息，然后提示您输入SQL。输入SQL语句（以分号终止），然后按“ Enter”，将执行SQL。

例如，要用一个名为“ tbl1”的表创建一个名为“ ex1”的新SQLite数据库，您可以这样做：

$ sqlite3 ex1

SQLite version 3.28.0 2019-03-02 15:25:24

Enter ".help" for usage hints.

sqlite> create table tbl1(one varchar(10), two smallint);

sqlite> insert into tbl1 values('hello!',10);

sqlite> insert into tbl1 values('goodbye', 20);

sqlite> select * from tbl1;

hello!|10

goodbye|20

sqlite>

通过键入系统文件结束符（通常是Control-D）来终止sqlite3程序。使用中断字符（通常是Control-C）来停止长时间运行的SQL语句。

确保在每个SQL命令的末尾键入分号！sqlite3程序查找分号以了解您的SQL命令何时完成。如果省略分号，sqlite3将为您提供继续提示，并等待您输入更多文本以添加到当前SQL命令中。此功能使您可以输入跨多行的SQL命令。例如：

sqlite> CREATE TABLE tbl2 (

   ...>   f1 varchar(30) primary key,

   ...>   f2 text,

   ...>   f3 real

   ...> );

sqlite>

2.双击Windows上的启动

Windows用户可以双击sqlite3.exe图标，使命令行外壳弹出运行SQLite的终端窗口。但是，由于双击时将启动sqlite3.exe，而没有命令行参数，因此将没有指定数据库文件，因此SQLite将使用在会话退出时删除的临时数据库。要将永久磁盘文件用作数据库，请在终端窗口启动后立即输入“ .open”命令：

SQLite version 3.28.0 2019-03-02 15:25:24

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .open ex1.db

sqlite>

上面的示例使名为“ ex1.db”的数据库文件被打开和使用。如果“ ex1.db”文件先前不存在，则将创建它。您可能想要使用完整路径名来确保文件位于您认为其所在的目录中。使用正斜杠作为目录分隔符。换句话说，使用“ c：/work/ex1.db”，而不是“ c：\ work \ ex1.db”。

另外，您可以使用默认的临时存储创建一个新数据库，然后使用“ .save”命令将该数据库保存到磁盘文件中：

SQLite version 3.28.0 2019-03-02 15:25:24

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> ... many SQL commands omitted ...

sqlite> .save ex1.db

sqlite>

使用“ .save”命令时要小心，因为它会覆盖任何具有相同名称的现有数据库文件，而不会提示您进行确认。与“ .open”命令一样，您可能希望使用带有正斜杠目录分隔符的完整路径名，以避免产生歧义。

3. sqlite3的特殊命令（点命令）

在大多数情况下，sqlite3只是读取输入行，并将其传递给SQLite库以执行。但是，以点（“.”）开头的输入行会被sqlite3程序本身拦截和解释。这些“点命令”通常用于更改查询的输出格式，或执行某些预打包的查询语句。最初只有几个点命令，但多年来积累了许多新功能，因此今天已有60多个。

有关可用的点命令的列表，可以输入不带参数的“ .help”。或输入“ .help TOPIC”以获取有关TOPIC的详细信息。可用的点命令列表如下：

sqlite> .help

.archive ...管理SQL存档

.auth ON | OFF显示授权者回调

.backup？DB？FILE备份数据库（默认为“ main”）到FILE

.bail on | off遇到错误后停止。默认关闭

.binary on | off打开或关闭二进制输出。默认关闭

.cd目录将工作目录更改为DIRECTORY

.changes on | off显示由SQL更改的行数

.check GLOB  如果由于.testcase的输出不匹配，则检查GLOB失败

.clone NEWDB将数据从现有数据库克隆到NEWDB

.databases列出连接数据库的名称和文件

. dbconfig ?op? ?val?  列出或更改sqlite3_db_config（）选项

.dbinfo？DB？显示有关数据库的状态信息

.dump？TABLE？将数据库内容呈现为SQL

.echo on | off打开或关闭命令回显

.eqp on | off | full | ...启用或禁用自动的EXPLAIN QUERY PLAN

.excel在电子表格中显示下一个命令的输出

.exit？CODE？使用返回码CODE退出此程序

.expert实验。建议查询索引

.explain？on | off | auto？更改EXPLAIN格式的模式。默认值：自动

.filectrl CMD ...运行各种sqlite3_file_control（）操作

.fullschema？-缩进？显示模式和sqlite_stat表的内容

.headers on | off打开或关闭标题显示

.help吗？？模式？显示PATTERN的帮助文本

.import FILE TABLE将数据从FILE导入TABLE

.imposter INDEX TABLE在索引INDEX上创建imposter表TABLE

.indexes？TABLE？显示索引名称

.limit？LIMIT？VAL 显示或更改SQLITE_LIMIT的值

.lint选项报告潜在的架构问题。

.load文件？加载扩展库

.log FILE | off打开或关闭登录。FILE可以是stderr / stdout

.mode MODE？TABLE？设定输出模式

.nu​​llvalue STRING使用STRING代替NULL值

。一次？选项？？文件？仅将下一个SQL命令的输出输出到FILE

.open？选项？？文件？关闭现有数据库并重新打开FILE

。输出文件？将输出发送到FILE或stdout（如果省略FILE）

.parameter CMD ...管理SQL参数绑定

.print STRING ...打印文字STRING

.progress N每N个操作码后调用进度处理程序

.prompt主要内容替换标准提示

.quit退出该程序

.read FILE从FILE读取输入

.recover从损坏的数据库中恢复尽可能多的数据。

.restore？DB？FILE从FILE恢复DB的内容（默认为“ main”）

.save FILE将内存数据库写入FILE

.scanstats on | off打开或关闭sqlite3_stmt_scanstatus（）指标

.schema吗？显示与PATTERN匹配的CREATE语句

.selftest？选项？运行SELFTEST表中定义的测试

.separator COL？ROW？更改列和行分隔符

.sha3sum ...计算数据库内容的SHA3哈希

.shell CMD ARGS ...在系统shell中运行CMD ARGS ...

.show显示各种设置的当前值

.stats“开|关”？显示统计信息或打开或关闭统计信息

.system CMD ARGS ...在系统外壳中运行CMD ARGS ...

.tables？TABLE？列出与LIKE模式TABLE匹配的表的名称

.testcase名称开始将输出重定向到'testcase-out.txt'

.testctrl CMD ...运行各种sqlite3_test_control（）操作

.timeout MS尝试打开锁定的表，以毫秒为单位

.timer on | off打开或关闭SQL计时器

.trace？选项？在运行时输出每个SQL语句

.vfsinfo？AUX？有关顶级VFS的信息

.vfslist列出所有可用的VFS

.vfsname？AUX？打印VFS堆栈的名称

.width NUM1 NUM2 ...设置列输出的最小列宽

sqlite>

4. “点命令”的规则

普通的SQL语句是自由格式的，可​​以分布在多行中，并且在任何地方都可以有空格和注释。点命令更具限制性：

• 点命令必须以“.”开头。在左边距没有前面的空格。
• 点命令必须完全包含在单个输入行中。
• 在普通SQL语句的中间不能出现点命令。换句话说，点命令不能在连续提示处出现。
• 点命令不能识别注释。

点命令由sqlite3.exe命令行程序解释，而不由SQLite本身解释。因此，任何点命令都不能用作sqlite3_prepare（）或sqlite3_exec（）之类的SQLite接口的参数。

5.更改输出格式

sqlite3程序能够以14种不同格式显示查询结果：

• ascii
• box
• csv
• column
• html
• insert
• json
• line
• list
• markdown
• quote
• table
• tabs
• tcl

 

您可以使用“ .mode”点命令在这些输出格式之间切换。>默认输出模式是“列表”。在列表模式下，查询结果的每一行都写在输出的一行上，并且该行中的每一列都由特定的分隔符字符串分隔。默认的分隔符是管道符号（“ |”）。当您要将查询的输出发送到另一个程序（例如AWK）进行其他处理时，列表模式特别有用。

sqlite> .mode list

sqlite> select * from tbl1;

hello|10

goodbye|20

sqlite>

使用“ .separator”点命令更改分隔符。例如，要将分隔符更改为逗号和空格，可以执行以下操作：

sqlite> .separator ", "

sqlite> select * from tbl1;

hello, 10

goodbye, 20

sqlite>

下一个“ .mode”命令会将“ .separator”重置为默认值。因此，如果要继续使用非标准的分隔符，则每当更改模式时都需要重复“ .separator”命令。

在“报价”模式下，输出格式为SQL文字。字符串用单引号引起来，内部单引号通过加倍转义。Blob以十六进制Blob文字符号（例如：x'abcd'）显示。数字显示为ASCII文本，而NULL值显示为“ NULL”。所有列均以逗号分隔（或使用“ .separator”选择的任何替代字符）。

sqlite> .mode quote

sqlite> select * from tbl1;

'hello',10

'goodbye',20

sqlite>

在“行”模式下，数据库行中的每一列都单独显示在一行上。每行包括列名称，等号和列数据。连续的记录由空白行分隔。这是线路模式输出的示例：

sqlite> .mode line

sqlite> select * from tbl1;

one = hello

two = 10

 

one = goodbye

two = 20

sqlite>

在列模式下，每条记录显示在单独的行中，数据按列对齐。例如：

sqlite> .mode column

sqlite> select * from tbl1;

one       two      

--------  ---

hello     10       

goodbye   20       

sqlite>

在“列”模式下（以及在“框”，“表”和“降价”模式下），列宽会自动调整。但是，您可以使用“ .width”命令为每列提供最小宽度来覆盖它。“ .width”的参数是整数，它是分配给每一列的最小空格数。负数表示右对齐。从而：

sqlite> .width 12 -6

sqlite> select * from tbl1;

one              two

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

hello!            10

goodbye           20

sqlite>

宽度0表示自动选择列宽。未指定的列宽度为零。因此，不带参数的命令“ .width”会将所有列宽设置为零，从而使所有列宽自动确定。

“列”模式是表格输出格式。其他表格输出格式为“框”，“降价”和“表格”：

sqlite> .width

sqlite> .mode markdown

sqlite> select * from tbl1;

|   one   | two |

|---------|-----|

| hello!  | 10  |

| goodbye | 20  |

sqlite> .mode table

sqlite> select * from tbl1;

+---------+-----+

|   one   | two |

+---------+-----+

| hello!  | 10  |

| goodbye | 20  |

+---------+-----+

sqlite> .mode box

sqlite> select * from tbl1;

┌─────────┬─────┐

│   one   │ two │

├─────────┼─────┤

│ hello!  │ 10  │

│ goodbye │ 20  │

└─────────┴─────┘

sqlite>

另一个有用的输出模式是“插入”。在插入模式下，将输出格式化为类似于SQL INSERT语句的格式。使用插入模式生成文本，以后可将其用于将数据输入到另一个数据库中。

指定插入模式时，必须提供一个额外的参数，该参数是要插入的表的名称。例如：

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”，“ json”和“ tcl”。自己尝试这些以查看它们的作用。

6.将结果写入文件

默认情况下，sqlite3将查询结果发送到标准输出。您可以使用“ .output”和“ .once”命令进行更改。只需将输出文件的名称作为.output的参数，所有后续查询结果将写入该文件。或使用.once命令而不是.output，并且仅在重定向到控制台之前将输出重定向为单个next命令。使用不带参数的.output重新开始写入标准输出。例如：

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

$

如果“ .output”或“ .once”文件名的第一个字符是管道符号（“ |”），则其余字符将被视为命令，并将输出发送到该命令。这样可以很容易地将查询结果传递到其他过程中。例如，在Mac上，“ open -f”命令将打开一个文本编辑器，以显示其从标准输入中读取的内容。因此，要在文本编辑器中查看查询的结果，可以输入：

sqlite3> .once '|open -f'

sqlite3> SELECT * FROM bigTable;

如果“ .output”或“ .once”命令的参数为“ -e”，则将输出收集到一个临时文件中，并在该文本文件上调用系统文本编辑器。因此，命令“ .once -e”获得与“ .once'| open -f'”相同的结果，但具有可在所有系统之间移植的优点。

如果“ .output”或“ .once”命令具有“ -x”参数，这会使它们在逗号分隔的临时文件中将输出累积为逗号分隔值（CSV），然后调用默认的系统实用程序以查看CSV文件（通常是电子表格程序）在结果上。这是将查询结果发送到电子表格以便于查看的快速方法：

sqlite3> .once -x

sqlite3> SELECT * FROM bigTable;

“ .excel”命令是“ .once -x”的别名。它做的完全一样。

6.1。文件I / O功能

命令行外壳程序添加了两个应用程序定义的SQL函数，这些函数有助于分别从文件中读取内容到表列中以及将列中的内容写入文件中。

readfile（X）SQL函数读取名为X的文件的全部内容，并将该内容作为BLOB返回。这可用于将内容加载到表中。例如：

sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);

sqlite> INSERT INTO images(name,type,img)

   ...>   VALUES('icon','jpeg',readfile('icon.jpg'));

SQL函数writefile（X，Y）将Blob Y写入名为X的文件中，并返回写入的字节数。使用此功能可将单个表列的内容提取到文件中。例如：

sqlite> SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';

请注意，readfile（X）和writefile（X，Y）函数是扩展函数，并且未内置在核心SQLite库中。这些例程 在SQLite源代码存储库中的 ext / misc / fileio.c源文件中可用作可加载扩展。

6.2。edit（）SQL函数

CLI还有另一个名为edit（）的内置SQL函数。Edit（）接受一个或两个参数。第一个参数是一个值-通常是要编辑的大型多行字符串。第二个参数是文本编辑器的名称。如果省略第二个参数，则使用VISUAL环境变量。edit（）函数将其第一个参数写入临时文件，在该临时文件上调用编辑器，完成编辑器后将文件重新读回到内存中，然后返回编辑后的文本。

edit（）函数可用于更改大文本值。例如：

sqlite> UPDATE docs SET body=edit(body) WHERE name='report-15';

在此示例中，docs.name为“ report-15”的条目的docs.body字段的内容将发送到编辑器。编辑器返回后，结果将写回到docs.body字段中。

edit（）的默认操作是调用文本编辑器。但是，通过在第二个参数中使用替代的编辑程序，您也可以获取它来编辑图像或其他非文本资源。例如，如果要修改碰巧存储在表的字段中的JPEG图像，则可以运行：

sqlite> UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';

通过简单地忽略返回值，该编辑程序也可以用作查看器。例如，仅查看上面的图像，您可以运行：

sqlite> SELECT length(edit(img,'gimp')) WHERE id='pic-1542';

7.查询数据库模式

sqlite3程序提供了一些便捷命令，这些命令对于查看数据库的模式很有用。这些命令没有其他方法无法完成的工作。这些命令纯粹是作为快捷方式提供的。

例如，要查看数据库中表的列表，可以输入“ .tables”。

sqlite> .tables

tbl1

tbl2

sqlite>

“ .tables”命令类似于设置列表模式，然后执行以下查询：

SELECT name FROM sqlite_schema

WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'

ORDER BY 1

但是“ .tables”命令的作用更多。它在sqlite_schema表中查询所有附加数据库，而不仅仅是主数据库。并将其输出排列到整齐的列中。

“ .indexes”命令以类似的方式列出所有索引。如果给“ .indexes”命令提供了一个参数，即表的名称，那么它仅显示该表上的索引。

“ .schema”命令显示数据库或单个表（如果提供了可选的tablename参数）的完整模式：

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>

 “ .schema”命令与设置列表模式大致相同，然后输入以下查询：

SELECT sql FROM sqlite_schema

ORDER BY tbl_name, type DESC, name

与“ .tables”一样，“。schema”命令显示所有附加数据库的模式。如果只想查看单个数据库的架构（也许是“ main”），则可以在“ .schema”中添加参数以限制其输出：

sqlite> .schema main.*

可以通过“ --indent”选项来扩展“ .schema”命令，在这种情况下，它将尝试重新格式化该模式的各种CREATE语句，以使人类更容易阅读它们。

“ .databases”命令显示在当前连接中打开的所有数据库的列表。总会有至少2个。第一个是“ main”，原始数据库已打开。第二个是“临时”，用于临时表的数据库。对于使用ATTACH语句附加的数据库，可能还会列出其他数据库。第一输出列是数据库附加的名称，第二列是外部文件的文件名。

sqlite> .databases

“ .fullschema”点命令的工作方式类似于“ .schema”命令，因为它显示了整个数据库模式。但是，“。fullschema”还包括统计信息表“ sqlite_stat1”，“ sqlite_stat3”和“ sqlite_stat4”的转储（如果存在）。通常，“。fullschema”命令提供针对特定查询完全重新创建查询计划所需的所有信息。当向SQLite开发团队报告SQLite查询计划程序的可疑问题时，要求开发人员提供完整的“ .fullschema”输出作为故障报告的一部分。请注意，sqlite_stat3和sqlite_stat4表包含索引条目的样本，因此可能包含敏感数据，因此请不要发送“ .fullschema”

8. CSV导入

使用“ .import”命令将CSV（逗号分隔值）数据导入到SQLite表中。“ .import”命令采用两个参数，分别是要从中读取CSV数据的磁盘文件的名称和要插入CSV数据的SQLite表的名称。

注意，在运行“ .import”命令之前，将“模式”设置为“ csv”很重要。这对于防止命令行Shell尝试将输入文件文本解释为其他格式是必须的。

sqlite> .import C:/work/somedata.csv tab1

有两种情况需要考虑：（1）表“ tab1”以前不存在，并且（2）表“ tab1”已经存在。

在第一种情况下，当该表先前不存在时，将自动创建该表，并使用输入CSV文件的第一行的内容来确定表中所有列的名称。换句话说，如果该表先前不存在，则CSV文件的第一行将被解释为列名，而实际数据将从CSV文件的第二行开始。

对于第二种情况，当表已存在时，假定CSV文件的每一行（包括第一行）均为实际内容。如果CSV文件包含列标签的初始行，则可以使用“ --skip 1”选项使.import命令跳过该初始行。

9. CSV导出

要将SQLite表（或表的一部分）导出为CSV，只需将“模式”设置为“ csv”，然后运行查询以提取表的所需行。

sqlite> .headers on

sqlite> .mode csv

sqlite> .once c:/work/dataout.csv

sqlite> SELECT * FROM tab1;

sqlite> .system c:/work/dataout.csv

在上面的示例中，“。headers on”行使列标签被打印为输出的第一行。这意味着结果CSV文件的第一行将包含列标签。如果不需要列标签，请改为设置“ .headers off”。（“ .headers off”设置是默认设置，如果以前未打开过标题，则可以省略。）

“ .once FILENAME ”行使所有查询输出进入命名文件，而不是打印在控制台上。在上面的示例中，该行导致将CSV内容写入名为“ C：/work/dataout.csv”的文件中。

该示例的最后一行（“ .system c：/work/dataout.csv”）具有与在Windows中双击c：/work/dataout.csv文件相同的效果。通常，这将启动一个电子表格程序以显示CSV文件。

该命令只能按Windows上的说明工作。在Mac上，等效行为：

sqlite> .system open dataout.csv

在Linux和其他UNIX系统上，您将需要输入以下内容：

sqlite> .system xdg-open dataout.csv

9.1。导出到Excel

为了简化导出到电子表格的过程，CLI提供了“ .excel”命令，该命令捕获单个查询的输出并将该输出发送到主机上的默认电子表格程序。像这样使用它：

sqlite> .excel

sqlite> SELECT * FROM tab;

上面的命令以CSV格式将查询的输出写入一个临时文件，调用CSV文件的默认处理程序（通常是首选的电子表格程序，例如Excel或LibreOffice），然后删除该临时文件。这实质上是执行上述“ .csv”，“。once”和“ .system”命令序列的简便方法。

“ .excel”命令实际上是“ .once -x”的别名。.once的-x选项使它以CSV格式将结果写入以“ .csv”后缀命名的临时文件中，然后为CSV文件调用系统默认处理程序。

还有一个“ .once -e”命令，其功能类似，不同之处在于它使用“ .txt”后缀命名临时文件，以便将调用系统的默认文本编辑器，而不是默认的电子表格。

10.将 ZIP存档作为数据库文件访问

除了读写SQLite数据库文件之外，sqlite3程序还将读写ZIP归档文件。只需在初始命令行或“ .open”命令中指定一个ZIP存档文件名来代替SQLite数据库文件名，sqlite3就会自动检测到该文件是ZIP存档而不是SQLite数据库，并以这样。无论文件后缀如何，此方法均有效。因此，您可以打开JAR，DOCX和ODP文件以及任何其他真正为ZIP存档的文件格式，SQLite会为您读取它。

ZIP存档似乎是一个包含具有以下架构的单个表的数据库：

CREATE TABLE zip(

  name,     // Name of the file

  mode,     // Unix-style file permissions

  mtime,    // Timestamp, seconds since 1970

  sz,       // File size after decompression

  rawdata,  // Raw compressed file data

  data,     // Uncompressed file content

  method    // ZIP compression method code

);

因此，举例来说，如果您想查看ZIP压缩文件中所有文件的压缩效率（表示为压缩内容的大小相对于原始未压缩文件的大小），从最高压缩到最低压缩，则可以运行查询是这样的：

sqlite> SELECT name, (100.0*length(rawdata))/sz FROM zip ORDER BY 2;

或使用文件I / O功能，您可以提取ZIP归档文件的元素：

sqlite> SELECT writefile(name,content) FROM zip

   ...> WHERE name LIKE 'docProps/%';

10.1。如何实现ZIP存档访问

命令行外壳使用Zipfile虚拟表访问ZIP存档。您可以通过在打开ZIP归档文件时运行“ .schema”命令来查看此内容：

sqlite> .schema

CREATE VIRTUAL TABLE zip USING zipfile('document.docx')

/* zip(name,mode,mtime,sz,rawdata,data,method) */;

打开文件时，如果命令行客户端发现该文件是ZIP存档而不是SQLite数据库，则它实际上会打开一个内存数据库，然后在该内存数据库中创建Zipfile虚拟表的实例，已附加到ZIP存档。

打开ZIP存档的特殊处理是命令行外壳的技巧，而不是核心SQLite库。因此，如果要在应用程序中将ZIP存档作为数据库打开，则需要激活Zipfile虚拟表模块，然后运行适当的 CREATE VIRTUAL TABLE语句。

11.将整个数据库转换为ASCII文本文件

使用“ .dump”命令将数据库的全部内容转换为单个ASCII文本文件。可以通过将其通过管道传输回sqlite3来将其转换回数据库。

制作数据库档案副本的好方法是：

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

这将生成一个名为ex1.dump.gz的文件，其中包含稍后或在另一台计算机上重建数据库所需的所有内容。要重建数据库，只需键入：

$ zcat ex1.dump.gz | sqlite3 ex2

文本格式是纯SQL，因此您也可以使用.dump命令将SQLite数据库导出到其他流行的SQL数据库引擎中。像这样：

$ createdb ex2

$ sqlite3 ex1 .dump | psql ex2

12.从损坏的数据库中恢复数据

像“ .dump”命令一样，“。recover”尝试将数据库文件的全部内容转换为文本。区别在于，“。recover”不是使用普通的SQL数据库接口读取数据，而是尝试根据直接从尽可能多的数据库页面中提取的数据来重组数据库。如果数据库已损坏，则“ .recover”通常能够从数据库的所有未损坏部分恢复数据，而“ .dump”在遇到第一个损坏迹象时会停止。

如果“ .recover”命令恢复了无法归因于任何数据库表的一个或多个行，则输出脚本将创建一个“ lost_and_found”表来存储孤立行。lost_and_found表的架构如下：

CREATE TABLE lost_and_found(

    rootpgno INTEGER,             -- root page of tree pgno is a part of

    pgno INTEGER,                 -- page number row was found on

    nfield INTEGER,               -- number of fields in row

    id INTEGER,                   -- value of rowid field, or NULL

    c0, c1, c2, c3...             -- columns for fields of row

);

从数据库中恢复的每个孤立行，“ lost_and_found”表都包含一行。此外，每个恢复的索引条目都有一行不能归因于任何SQL索引。这是因为在SQLite数据库中，使用相同的格式来存储SQL索引条目和WITHOUT ROWID表条目。

如果恢复的数据库模式已包含名为“ lost_and_found”的表，则“ .recover”命令将使用名称“ lost_and_found0”。如果名称“ lost_and_found0”也已经被使用，则“ lost_and_found1”，依此类推。通过使用--lost-and-found开关调用“ .recover”，可以覆盖默认名称“ lost_and_found”。例如，要使输出脚本调用表“ orphaned_rows”：

sqlite> .recover --lost-and-found orphaned_rows

13.加载扩展

您可以在运行时使用“ .load”命令将新的自定义应用程序定义的SQL函数， 排序序列，虚拟表和VFS添加到命令行外壳。首先，将扩展转换为DLL或共享库（如“ 运行时可加载扩展”文档中所述），然后键入：

sqlite> .load /path/to/my_extension

请注意，SQLite会自动将适当的扩展名后缀（在Windows上为“ .dll”，在Mac上为“ .dylib”，在大多数其他Unix上为“ .so”）添加到扩展名文件名。通常，最好指定扩展名的完整路径名。

SQLite根据扩展名文件名计算扩展的入口点。要覆盖此选择，只需将扩展名作为第二个参数添加到“ .load”命令。

可以 在SQLite源树的ext / misc子目录中找到一些有用的扩展的源代码 。您可以按原样使用这些扩展，也可以将其作为创建自己的自定义扩展以解决自己的特定需求的基础。

14.数据库内容的加密哈希

“ .sha3sum”点命令可计算 数据库内容的SHA3哈希。明确地说，哈希是根据数据库内容而不是磁盘上的表示来计算的。例如，这意味着VACUUM 或类似的保留数据的转换不会更改哈希。

“ .sha3sum”命令支持选项“ --sha3-224”，“-sha3-256”，“-sha3-384”和“ --sha3-512”，以定义要使用哪种SHA3版本哈希。默认值为SHA3-256。

数据库模式（在sqlite_schema表中）通常不包括在哈希中，但是可以通过“ --schema”选项添加。

“ .sha3sum”命令采用单个可选参数，该参数是 LIKE模式。如果存在此选项，则仅对名称与LIKE模式匹配的表进行哈希处理。

“ .sha3sum”命令是借助 命令行外壳附带的 扩展功能“ sha3_query（）”实现的。

15.数据库内容自检

“ .selftest”命令尝试验证数据库是否完整且未损坏。.selftest命令在名为“ selftest”的架构中查找表，定义如下：

CREATE TABLE selftest(

  tno INTEGER PRIMARY KEY,  -- Test number

  op TEXT,                  -- 'run' or 'memo'

  cmd TEXT,                 -- SQL command to run, or text of "memo"

  ans TEXT                  -- Expected result of the SQL command

);

.selftest命令以selftest.tno顺序读取selftest表的行。对于每个“备注”行，它将“ cmd”中的文本写入输出。对于每个“运行”行，它将作为SQL运行“ cmd”文本，并将结果与​​“ ans”中的值进行比较，如果结果不同，则会显示一条错误消息。

如果没有自检表，则“ .selftest”命令运行 PRAGMA integrity_check。

如果“ .selftest --init”命令尚不存在，则会创建该表，然后追加检查所有表内容的SHA3哈希的条目。随后的“ .selftest”运行将验证数据库没有任何更改。要生成测试以验证表的子集未更改，只需运行“ .selftest --init”，然后删除引用非恒定表的自测行。

16. SQLite存档支持

“ .archive”点命令和“ -A”命令行选项提供对SQLite存档格式的内置支持 。该接口类似于unix系统上的“ tar”命令。每次对“ .ar”命令的调用都必须指定一个命令选项。以下命令可用于“ .archive”：

除命令选项外，每次调用“ .ar”都可以指定一个或多个修饰符选项。有些修饰符选项需要一个参数，而有些则不需要。以下修饰符选项可用：

对于命令行用法，请在“ -A”之后立即添加简短样式的命令行选项，中间不要插入空格。所有后续参数都被视为.archive命令的一部分。例如，以下命令是等效的：

sqlite3 new_archive.db -Acv file1 file2 file3

sqlite3 new_archive.db ".ar -cv file1 file2 file3"

长和短样式选项可能混合在一起。例如，以下内容等效：

-- Two ways to create a new archive named "new_archive.db" containing

-- files "file1", "file2" and "file3".

.ar -c --file new_archive.db file1 file2 file3

.ar -f new_archive.db --create file1 file2 file3

或者，“。ar”之后的第一个参数可以是所有必需选项的简短形式的串联（不包括“-”字符）。在这种情况下，接下来需要从命令行中读取需要它们的选项的参数，而其余的单词都将被视为命令参数。例如：

-- Create a new archive "new_archive.db" containing files "file1" and

-- "file2" from directory "dir1".

.ar cCf dir1 new_archive.db file1 file2 file3

 16.1。SQLite存档创建命令

创建一个新的存档，覆盖任何现有的存档（在当前的“主”数据库中或在--file选项指定的文件中）。选项后面的每个参数都是要添加到存档中的文件。目录是递归导入的。参见上面的示例。

16.2。SQLite存档提取命令

从存档中提取文件（到当前工作目录或--directory选项指定的目录中）。如果选项后没有参数，则从存档中提取所有文件。或者，如果有参数，它们就是要从存档中提取的文件的名称。任何指定的目录都是递归提取的。如果任何指定的文件不包含在归档中，这是一个错误。

-- Extract all files from the archive in the current "main" db to the

-- current working directory. List files as they are extracted.

.ar --extract --verbose

 

-- Extract file "file1" from archive "ar.db" to directory "dir1".

.ar fCx ar.db dir1 file1

 16.3。SQLite存档列表命令

列出档案的内容。如果未指定任何参数，则列出所有文件。否则，只有那些指定为参数的参数才是。当前，--verbose选项不会更改此命令的行为。将来可能会改变。

-- List contents of archive in current "main" db..

.ar --list

 16.4。SQLite存档插入和更新命令

--update和--insert命令的工作方式与--create命令相同，区别在于它们在开始之前不会删除当前归档。文件的新版本将以相同的名称静默替换现有文件，但否则存档的初始内容（如果有）保持不变。

对于--insert命令，列出的所有文件都将插入到存档中。对于--update命令，仅在文件先前不存在于归档文件中或者文件的“ mtime”或“ mode”与归档文件中当前文件不同的情况下才插入文件。

兼容性节点：在SQLite 3.28.0（2019-04-16）之前，仅支持--update选项，但该选项的作用与--insert相同，因为它始终重新插入每个文件，而不管其是否已更改。

16.5。ZIP档案上的操作

如果FILE是ZIP存档而不是SQLite存档，则“ .archive”命令和“ -A”命令行选项仍然有效。这是使用zipfile扩展名完成的。因此，以下命令大致等效，仅在输出格式上有所不同：

16.6。用于实现SQLite存档操作的SQL

各种SQLite存档存档命令是使用SQL语句实现的。应用程序开发人员可以通过运行适当的SQL轻松地向自己的项目添加SQLite Archive归档读写支持。

要查看用于执行SQLite Archive操作的SQL语句，请添加--dryrun或-n选项。这将导致显示SQL，但禁止执行SQL。

用于实现SQLite Archive操作的SQL语句利用了各种可加载的扩展。这些扩展都可以在ext / misc /子文件夹的SQLite源代码树中找到 。完整的SQLite Archive支持所需的扩展包括：

1. fileio.c —此扩展添加了SQL函数readfile（）和writefile（），用于从磁盘上的文件读取和写入内容。fileio.c扩展名还包括fsdir（）表值函数（用于列出目录的内容）和lsname（）函数，用于将stat（）系统调用中的数字st_mode整数转换为以“ ls -l“命令。
2. sqlar.c —此扩展添加了在从SQLite存档插入和提取文件内容时压缩和解压缩文件内容所需的sqlar_compress（）和sqlar_uncompress（）函数。
3. zipfile.c —此扩展实现了“ zipfile（FILE）”表值函数，该函数用于读取ZIP存档。仅当读取ZIP存档而不是SQLite存档时才需要此扩展名。
4. appendvfs.c —此扩展实现了一个新的VFS，该功能允许将SQLite数据库附加到其他文件（例如可执行文件）。仅当使用.archive命令的--append选项时，才需要此扩展名。

17. SQL参数

SQLite允许绑定参数出现在允许文字值的任何地方的SQL语句中。这些参数的值是使用sqlite3_bind _...（） API系列设置的。

参数可以命名或不命名。未命名的参数是单个问号（“？”）。命名参数是“？” 紧随其后的是数字（例如：“？15”或“？123”）或字符“ $”，“：”或“ @”之一，后跟字母数字名称（例如：“ $ var1”，“：” xyz”，“ @ bingo”）。

此命令行外壳使未命名的参数保持未绑定状态，这意味着它们将具有SQL NULL的值，但已命名的参数可能会被分配值。如果存在名为“ sqlite_parameters”的TEMP表，其格式如下：

CREATE TEMP TABLE sqlite_parameters(

  key TEXT PRIMARY KEY,

  value ANY

) WITHOUT ROWID;

并且如果该表中有一个条目，其中键列与参数名称完全匹配（包括首字母“？”，“ $”，“：”或“ @”字符），则将为参数分配参数的值。值列。如果不存在任何条目，则参数默认为NULL。

存在“ .parameter”命令以简化对该表的管理。如果不存在temp.sqlite_parameters表，则“ .parameter init”命令（通常缩写为“ .param init”）会创建该表。“ .param list”命令显示temp.sqlite_parameters表中的所有条目。“ .param clear”命令删除temp.sqlite_parameters表。“ .param set KEY VALUE”和“ .param unset KEY”命令从temp.sqlite_parameters表中创建或删除条目。

temp.sqlite_parameters表仅提供命令行外壳程序中参数的值。temp.sqlite_parameter表对直接使用SQLite C语言API运行的查询无效。各个应用程序应实现自己的参数绑定。您可以在命令行外壳程序源代码中搜索“ sqlite_parameters”， 以查看命令行外壳程序如何进行参数绑定，并将其用作如何自己实现的提示。

18.索引建议（SQLite专家）

注意：此命令是实验性的。将来某个时候可能会以不兼容的方式将其删除或修改接口。

对于大多数平凡的SQL数据库，性能的关键是创建正确的SQL索引。在这种情况下，“正确的SQL索引”表示那些导致应用程序需要优化的查询的运行速度很快。“ .expert”命令可以通过提出可能有助于特定查询的索引（如果它们存在于数据库中）来帮助解决此问题。

首先发出“ .expert”命令，然后在单独的行上执行SQL查询。例如，考虑以下会话：

sqlite> CREATE TABLE x1(a, b, c);                  -- Create table in database

sqlite> .expert

sqlite> SELECT * FROM x1 WHERE a=? AND b>?;        -- Analyze this SELECT

CREATE INDEX x1_idx_000123a7 ON x1(a, b);

 

0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?)

 

sqlite> CREATE INDEX x1ab ON x1(a, b);             -- Create the recommended index

sqlite> .expert

sqlite> SELECT * FROM x1 WHERE a=? AND b>?;        -- Re-analyze the same SELECT

(no new indexes)

 

0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)

在上面，用户创建数据库模式（单个表-“ x1”），然后使用“ .expert”命令分析查询，在这种情况下为“ SELECT * FROM x1 WHERE a =？AND b>？ ”。Shell工具建议用户创建一个新索引（索引“ x1_idx_000123a7”）并以EXPLAIN QUERY PLAN格式输出该查询将使用的计划。然后，用户使用等效的架构创建索引，并再次对同一查询运行分析。这次，shell工具不建议任何新索引，并在给定现有索引的情况下输出SQLite将用于查询的计划。

“ .expert”命令接受以下选项：

使用 SQLite专家扩展代码，本节中描述的功能可以集成到其他应用程序或工具中 。

19.其他点命令

命令行外壳中还有许多其他的点命令。有关任何特定版本和SQLite版本的完整列表，请参见“ .help”命令。

20.在shell脚本中使用sqlite3

在外壳程序脚本中使用sqlite3的一种方法是使用“ echo”或“ cat”在文件中生成一系列命令，然后在重定向生成的命令文件中的输入时调用sqlite3。这可以正常工作，并且在许多情况下都适用。但是，为增加便利，sqlite3允许在命令行上输入一个SQL命令作为数据库名称之后的第二个参数。使用两个参数启动sqlite3程序时，第二个参数传递到SQLite库进行处理，查询结果以列表模式打印在标准输出上，然后程序退出。该机制旨在使sqlite3易于与“ awk”之类的程序结合使用。例如：

$ 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

$

 21.结束shell命令

SQLite命令通常以分号终止。在外壳程序中，您还可以在一行上单独使用单词“ GO”（不区分大小写）或斜杠字符“ /”来结束命令。这些分别由SQL Server和Oracle使用。它们在sqlite3_exec（）中不起作用，因为shell在将它们传递给该函数之前将其转换为分号。