小. 快速. 可靠.
选择任意三个.
Command Line Shell For SQLite

1. Getting Started

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. Double-click Startup On 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. Special commands to sqlite3 (dot-commands)

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

有关可用的点命令的列表,可以输入不带参数的" .help". 或输入" .help TOPIC"以获取有关TOPIC的详细信息. 可用的点命令列表如下:

sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?TABLE? ...        Render all database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in a spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
.iotrace FILE            Enable I/O diagnostic logging to FILE
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode
.nullvalue STRING        Use STRING in place of NULL values
.once (-e|-x|FILE)       Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input from FILE
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?on|off?          Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set column widths for "column" mode
sqlite>

4. Rules for "dot-commands"

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

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

5. Changing Output Formats

sqlite3程序能够以八种不同的格式显示查询的结果:" csv","列"," html","插入","行","列表","引用","选项卡"和" " 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>

默认情况下,每列的宽度在1到10个字符之间,具体取决于列标题名称和第一列数据的宽度. 太宽而无法容纳在列中的数据将被截断. 使用" .width"点命令来调整列宽,如下所示:

sqlite> .width 12 6
sqlite> select * from tbl1;
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>

上例中的" .width"命令将第一列的宽度设置为12,将第二列的宽度设置为6.所有其他列的宽度均保持不变. 您可以根据需要在" .width"中提供尽可能多的参数,以指定查询结果中尽可能多的列的宽度.

如果您将列的宽度指定为0,则列的宽度将自动调整为三个数字的最大值:10,标题的宽度和数据的第一行的宽度. 这样可以使列宽自动调整. 每列的默认宽度设置是此自动调整0值.

对右对齐的列使用负的列宽.

可以使用" .headers"点命令打开和关闭输出的前两行中显示的列标签. 在上面的示例中,列标签处于打开状态. 要关闭它们,您可以执行以下操作:

sqlite> .headers off
sqlite> select * from tbl1;
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". 在这种模式下,sqlite3将查询结果写入XHTML表. 开头的<TABLE>和结尾的</ TABLE>均未写入,但所有中间的<TR>,<TH>和<TD>均已写入. 可以设想html输出模式对于CGI很有用.

6. Writing results to a file

默认情况下,sqlite3将查询结果发送到标准输出. 您可以使用" .output"和" .once"命令进行更改. 只需将输出文件的名称作为.output的参数,所有后续查询结果将写入该文件. 或使用.once命令而不是.output,并且仅在重定向到控制台之前将输出重定向到单个下一个命令. 使用不带任何参数的.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. File I/O Functions

命令行外壳程序添加了两个应用程序定义的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. The edit() SQL function

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. Querying the database schema

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

例如,要查看数据库中表的列表,可以输入" .tables".

sqlite> .tables
tbl1
tbl2
sqlite>

" .tables"命令类似于设置列表模式,然后执行以下查询:

SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1

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

The ".indexes" command works in a similar way to list all of the indexes. If the ".indexes" command is given an argument which is the name of a table, then it shows just indexes on that table.

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

使用" .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 Export

要将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"设置是默认设置,如果以前未打开过标题,则可以省略.)

" causes all query output to go into the named file instead of being printed on the console. " .once "行使所有查询输出进入命名文件,而不是打印在控制台上. 在上面的示例中,该行导致将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. Export to Excel

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

sqlite> .excel
sqlite> SELECT * FROM tab;

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

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

There is also a ".once -e" command which works similarly, except that it names the temporary file with a ".txt" suffix so that the default text editor for the system will be invoked, instead of the default spreadsheet.

10. Accessing ZIP Archives As Database Files

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

A ZIP archive appears to be a database containing a single table with the following schema:

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. How ZIP archive access is implemented

命令行外壳使用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. Converting An Entire Database To An ASCII Text File

使用" .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. Recover Data From a Corrupted Database

像" .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表条目.

ColumnContents
rootpgno 即使可能无法将行归因于特定的数据库表,它也可能是数据库文件中树结构的一部分. 在这种情况下,该树结构的根页号存储在此列中. 或者,如果找到该行的页面不是树结构的一部分,则此列将值的副本存储在" pgno"列中-该行所在的页面的页码. 在许多(尽管不是全部)情况下,lost_and_found表中具有此列中相同值的所有行都属于同一表.
pgno 找到该行的页面的页码.
nfield 此行中的字段数.
id 如果该行来自WITHOUT ROWID表,则此列包含NULL. 否则,它包含该行的64位整数rowid值.
c0,c1,c2 ... 该行每一列的值都存储在这些列中. " .recover"命令创建的lost_and_found表具有最长的孤立行所需要的列数.

如果恢复的数据库模式已经包含名为" 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. Loading Extensions

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

sqlite> .load /path/to/my_extension

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

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

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

14. Cryptographic Hashes Of Database Content

" .sha3sum"点命令计算数据库内容SHA3哈希. 需要明确的是,哈希是根据数据库内容而不是磁盘上的表示进行计算的. 例如,这意味着VACUUM或类似的保留数据的转换不会更改哈希.

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

数据库模式(在sqlite_master表中)通常不包括在哈希中,但是可以通过" --schema"选项添加.

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

" .sha3sum"命令是借助命令行外壳附带的扩展功能" sha3_query()"实现的.

15. Database Content Self-Tests

The ".selftest" command attempts to verify that a database is intact and is not corrupt. The .selftest command looks for a table in schema named "selftest" and defined as follows:

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 Support

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

Option长期权Purpose
-c--create创建一个包含指定文件的新存档.
-x--extract从存档中提取指定的文件.
-i--insert将文件添加到现有存档.
-t--list列出存档中的文件.
-u--update如果文件已更改, 则将它们添加到现有存档中.

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

Option长期权Purpose
-v--verbose列出每个正在处理的文件.
-f文件--file文件 如果指定,请使用文件FILE作为存档. 否则,假定当前的"主"数据库是要操作的存档.
-一份文件-追加文件像--file一样,将文件FILE用作存档,但是使用apndvfs VFS打开文件,以便在FILE已经存在的情况下将存档附加到FILE的末尾.
-C你-目录DIR如果指定,则将所有相对路径解释为相对于DIR的,而不是相对于当前工作目录的.
-n--dryrun显示将执行以执行归档操作的SQL,但实际上不做任何更改.
----所有后续的命令行单词都是命令参数,而不是选项.

对于命令行用法,请在" -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 Archive Create Command

创建一个新的存档,覆盖任何现有的存档(在当前的"主"数据库中或在--file选项指定的文件中). 选项后面的每个参数都是要添加到存档中的文件. 目录是递归导入的. 请参阅上面的示例.

16.2. SQLite Archive Extract Command

从存档中提取文件(到当前工作目录或--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 Archive List Command

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

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

16.4. SQLite Archive Insert And Update Commands

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

对于--insert命令,列出的所有文件都将插入到存档中. 对于--update命令,仅在文件以前不存在于归档中,或者文件的" mtime"或" mode"与归档中当前文件不同时才插入文件.

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

16.5. Operations On ZIP Archives

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

传统指挥等效的sqlite3.exe命令
解压缩archive.zipsqlite3 -Axf archive.zip
解压-l archive.zipsqlite3 -Atvf archive.zip
zip -r archive2.zip目录sqlite3 -Acf archive2.zip目录

16.6. SQL Used To Implement SQLite Archive Operations

各种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 —此扩展名添加了sqlar_compress()和sqlar_uncompress()函数,这些函数在从SQLite存档插入和提取文件内容时对其进行压缩和解压缩.

  3. zipfile.c —此扩展实现了用于读取ZIP归档文件的表值函数" zipfile(FILE)". 仅当读取ZIP存档而不是SQLite存档时才需要此扩展名.

  4. appendvfs.c-此扩展实现了一个新的VFS ,该功能允许将SQLite数据库附加到其他文件(例如可执行文件). 仅当使用.archive命令的--append选项时,才需要此扩展名.

17. SQL Parameters

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. Index Recommendations (SQLite Expert)

Note: This command is experimental. It may be removed or the interface modified in incompatible ways at some point in the future.

对于大多数平凡的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"命令接受以下选项:

Option Purpose
--verbose 如果存在,则为每个分析的查询输出更详细的报告.
--sample PERCENT 默认情况下,".expert"命令仅根据查询和数据库架构推荐索引. 如果用户没有在数据库上运行ANALYZE命令来生成数据分布统计信息,则这类似于SQLite查询计划器为查询选择索引的方式.
如果将此选项传递给非零参数,则" .expert"命令将根据当前存储在每个数据库表中的行的PERCENT百分比,为所考虑的所有索引生成类似的数据分布统计信息. 对于数据分布异常的数据库,这可能会导致更好的索引建议,尤其是在应用程序打算运行ANALYZE的情况下.
对于小型数据库和现代CPU,通常没有理由不通过" --sample 100". 但是,对于大型数据库表,收集数据分发统计信息可能会很昂贵. 如果操作太慢,请尝试为--sample选项传递一个较小的值.

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

19. Other Dot Commands

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

20. Using sqlite3 in a shell script

在外壳程序脚本中使用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. Ending shell commands

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

22. Compiling the sqlite3 program from sources

要在带有MinGW的unix系统和Windows上编译命令行外壳,通常的configure-make命令可以运行:

sh configure; make

无论您是从源代码树的规范源还是从合并的捆绑包中进行构建,configure-make都可以工作. 依赖很少. 从规范来源进行构建时,需要有效的tclsh . 如果使用合并包,则通常已经由tclsh完成的所有预处理工作已经完成,并且仅需要常规的构建工具.

为了使.archive命令运行,需要一个有效的zlib压缩库 .

在具有MSVC的Windows上,将nmake与Makefile.msc结合使用:

nmake /f Makefile.msc

为使.archive命令正确运行,请将zlib源代码的副本复制到源树的compat / zlib子目录中,并通过以下方式进行编译:

nmake /f Makefile.msc USE_ZLIB=1

22.1. Do-It-Yourself Builds

sqlite3命令行界面的源代码位于一个名为" shell.c"的文件中. shell.c源文件是从其他来源生成的,但是大多数shell.c代码可以在src / shell.c.in中找到. (通过从规范的源代码树中键入" make shell.c"来重新生成shell.c.) 编译 shell.c文件(与sqlite3库源代码一起 )以生成可执行文件. 例如:

gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread -lz -lm

建议使用以下附加的编译时选项,以提供功能齐全的命令行外壳程序:

by  ICOPY.SITE