创建数据库
通过sqlite3 databaseName.db命令可以创建一个数据库,下面的命令创建了一个名为test.db的数据库:
zyf@zyf-Ubuntu:/media/zyf/移动硬盘/Project/SqliteProject$ sqlite3 test.db
SQLite version 3.32.2 2020-06-04 12:58:43
Enter ".help" for usage hints.
复制代码需要注意的是：在上面的命令中，虽然我们提供了数据库的名称，但是如果该数据库并不存在，Sqlite实际上就没有创建该数据库，直到在数据库内部创建一些内容(例如表或者视图)时，Sqlite才会创建该数据库。这样做的原因是，让我们有机会在数据库结构提交到磁盘之前进行各种数据库设置，例如页面大小等。数据库一旦创建，一些设置例如页面大小，字符集(UTF-8)等数据是不能轻易改变的。
zyf@zyf-Ubuntu:/media/zyf/移动硬盘/Project/SqliteProject$ ls -a
. ..
zyf@zyf-Ubuntu:/media/zyf/移动硬盘/Project/SqliteProject$
复制代码可以看到，文件夹里面是没有数据的。
现在我们使用默认的数据库配置，如果需要在磁盘中创建该数据库，我们可以创建一个表，如下所示:
sqlite> create table test(id integer primary key,value text);
复制代码现在在磁盘中已经有了名为test.db这个数据库文件了，数据库中包含一个名为test的表，根据我们创建表时的语义，这个表中包含两个字段，也就是两列:
-
名为
id的主键列，该列具备默认自动增长的属性。当定义一个整型主键列后，Sqlite会在该列上应用单增函数以便创建一个单增值。也就是说，如果在执行insert语句的时候不提供该列的值，Sqlite会通过查找该列下一个值后自动产生。 -
另一个字段就是名为
value的简单文本域。
现在我们向表中添加几行数据来验证上面的结论:
sqlite> insert into test(id,value) values (1,"one");
sqlite> insert into test(id, value) values (10,"ten");
sqlite> insert into test(value) values ("three");
sqlite> insert into test(value) values ("four");
复制代码在上面的插入语句中，我们一开始指定了需要插入数据的id,value值，后面则只指定了value值。另外，我们指定的id值并不是连续的，也是可以成功插入的。
现在我们来查看之前插入的数据:
sqlite> .mode column
sqlite> .headers on
sqlite>
sqlite>
sqlite> select * from test;
id value
---------- ----------
1 one
10 ten
11 three
12 four
复制代码上面的查询语句中我们首先指定了.mode和.headers属性，通过这两个属性我们可以以自己喜欢的样式来查看数据。
之后我们执行了查询命令，可以看到，就是我们之前插入的数据，后面两条数据虽然我们当时在插入的时候没有指定id值，但是这里会自动根据之前后一条数据的id值进行增长。另外我们也可以发现我们的id值在插入的时候是不连续的，这里查询后的仍然保持我们之前插入的数据。
在上面的数据表中，我们已经知道id是会自动增长的，所以很多时候我们并不会插入id对应的数据，但是我们仍然会关心后一条插入数据的id值是多少，此时我们就可以使用SQLite中的last_insert_rowid()这个函数来获得后插入的自动增长的增量值。
sqlite> select last_insert_rowid();
last_insert_rowid()
-------------------
12
复制代码可以看到，使用这个函数就是返回了我们之前插入数据时的后一条数据的id的值。
在退出之前，我们可以添加一个索引和视图，用于后面的演示:
sqlite> create index test_idx on test (value);
sqlite> create view schema as select * from sqlite_master;
复制代码使用.exit或者.quit命令均可以退出shell，在Windows上还可以使用Ctrl + C退出，在Linux可以使用Ctrl + D退出。
获得数据库的Schema信息
有时候在创建完数据库以后，我们想要获得数据库的相关信息，下面的几个shell命令可以帮助我们做到这点。
.tables [pattern]
这个命令可以得到所有表和视图的列表，其中的pattern可以是任何like操作符理解的SQL，执行这个命令可以返回所有符合条件的表和视图，如果没有提供pattern参数，则返回所有的表和视图。
sqlite> .tables
schema test
复制代码在之前，我们在test.db数据库中创建了一个test表和一个名为schema的视图，通过.tables命令将这两个数据直接输出出来了。
.indices [tableName]
这个命令可以得到我们创建的索引，如果我们指定了数据表的名称，则返回在这个数据表中创建的索引，如果我们没有指定表名，则返回所有的索引信息。
sqlite> .indices test
test_idx
sqlite> .indices
test_idx
复制代码.schema [tableName]
这个命令可以得到一个表或者视图的定义语句，如果没有提供表名，则返回所有数据库对象(包括table,index,view和trigger)的定义语句。
sqlite> .schema test
CREATE TABLE test(id integer primary key,value text);
CREATE INDEX test_idx on test (value);
复制代码上面我们指定了要查询的test表的定义信息，则输出了和这个表相关的数据库对象信息，这个表里面我们之前创建了一个索引，这里也输出了。
sqlite> .schema
CREATE TABLE test(id integer primary key,value text);
CREATE INDEX test_idx on test (value);
CREATE VIEW schema as select * from sqlite_master
/* schema(type,name,tbl_name,rootpage,sql) */;
复制代码在没有指定表名的情况下，这里就输出了和当前数据库对象相关的所有定义语句。
详细信息
更加详细的schema信息则可以通过查询SQLite的重要系统视图sqlite_master得到。这个视图是一个系统目录，其结构如下表所示:
| 编号 | 字段名 | 说明 |
|---|---|---|
| 1 | type | 对象类型(table,index,view,trigger) |
| 2 | name | 对象名称 |
| 3 | tbl_name | 对象关联的表 |
| 4 | Rootpage | 对象根页面在数据库的索引(开始的编号) |
| 5 | sql | 对象的SQL定义(DDL) |
查询当前数据库的sqlite_master表，可以看到以下内容:
sqlite> select type,name,tbl_name,rootpage,sql from sqlite_master;
type name tbl_name rootpage sql
---------- ---------- ---------- ---------- ----------------------------------------------------
table test test 3 CREATE TABLE test(id integer primary key,value text)
index test_idx test 4 CREATE INDEX test_idx on test (value)
view schema schema 0 CREATE VIEW schema as select * from sqlite_master
复制代码从上面输出的信息我们就可以看到当前test.db数据库对象的完整清单包括:一个表，一个索引和一个视图，每一个都有各自初的DLL创建语句。
导出数据
我们可以使用.dump命令将数据库对象导出成SQL格式。不带任何参数时，.dump命令将整个数据库导出为数据库定义语言(DDL)和数据库操作语言(DML)命令，这些命令会被写入文本或者在标准输出上显示，适合重新创建数据库和其中的数据。如果提供了参数，shell将参数解析作为表名或者视图，导出任何匹配给定参数的表或视图，那些不匹配的将会被忽略。在shell模式中，默认情况下,.dump命令的输出定向到屏幕。如果想要将输出重定向到文件，则可以使用.dump [fileName]命令，此命令将所有的输出重定向到指定的文件中。如果要恢复输出到屏幕，只需要执行.output stdout。因此，如果我们需要将当前数据库对象导出到文件test_backup.sql中，则可以使用如下的命令:
sqlite> .output test_backup.sql
sqlite> .dump
sqlite> .output stdout
复制代码上面的命令执行以后，就可以在当前文件夹中找到test_backup.sql这个文件，如果这个文件不存在，那么就会创建这个文件，如果这个文件存在，则会覆盖这个文件，打开这个文件后的内容如下:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(id integer primary key,value text);
INSERT INTO test VALUES(1,'one');
INSERT INTO test VALUES(10,'ten');
INSERT INTO test VALUES(11,'three');
INSERT INTO test VALUES(12,'four');
CREATE INDEX test_idx on test (value);
CREATE VIEW schema as select * from sqlite_master;
COMMIT;
zyf@zyf-
复制代码通过SQL的重定向和各种shell格式设置选项，可以在很大程度上控制导出的数据。
导入数据
有两种方法可以从外部文件中导入数据，使用哪种方法要取决于要导入的文件格式:
-
如果文件由SQL语句构成，可以使用
.read命令并执行文件中包含的命令。 -
如果文件包含由逗号或者其它分隔符分隔的值组成，可以使用
.import[file][table]命令，此命令将尝试解析指定的文件并尝试将数据插入到指定的表中。它通过使用管道字符("|")作为分隔符解析文件中的每一行，并将已分析的列插入到表中。需要注意的是，文件中解析字段的数据应该和表中列相匹配。可以使用.separator命令指定不同的分隔符。如果要查看分隔符的当前值，则可以使用.show命令，如下所示:
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
filename: test.db
复制代码对于.read命令，其用来导入.dump命令创建的文件，如果使用前面作为备份文件导出的test_backup.sql，需要先移除之前已经存在的数据库对象(test表和schema视图)，然后使用.read命令导入:
sqlite> drop table test;
sqlite> drop view schema;
sqlite> .tables
sqlite> select * from sqlite_master;
复制代码上面的命令删除了之前的数据库对象，并验证了删除成功，下面使用.read命令来导入数据库信息:
sqlite> .read test_backup.sql
sqlite> .tables
schema test
sqlite> select * from sqlite_master;
type name tbl_name rootpage sql
---------- ---------- ---------- ---------- ----------------------------------------------------
table test test 3 CREATE TABLE test(id integer primary key,value text)
index test_idx test 4 CREATE INDEX test_idx on test (value)
view schema schema 0 CREATE VIEW schema as select * from sqlite_master
复制代码可以看到，使用.read命令恢复数据库对象后和之前的数据库信息并无二致。
格式化
CLP提供了几个格式化选项命令，这些命令可以使结果集和输出更加简洁整齐。
.echo
这个命令将回显输入的命令，如下所示:
sqlite> .echo on
sqlite> .tables
.tables
schema test
sqlite> select * from test;
select * from test;
1|one
10|ten
11|three
12|four
复制代码在上面的命令中，我们将.echo的属性设置为on，之后我们输入了.tables命令和一条查询命令，然后我们就看到了我们输入的那两条命令和结果。
.headers
这个命令设置为on时，我们将能够看到查询的结果将会显示字段名，如下所示:
sqlite> .headers on
.headers on
sqlite> .tables
.tables
schema test
sqlite> select * from test;
select * from test;
id|value
1|one
10|ten
11|three
12|four
复制代码可以看到，相比于之前的查询命令，这里的输出多了id和value两个字段名。
.nullvalue
当遇到NULL值时，使用.nullvalue命令设置输出的数据。如果需要以一个字符串NULL来表示空值，则可以执行.nullvalue NULL命令来指定。默认情况下，这种null显示时是空字符串。
sqlite> .nullvalue NULL
.nullvalue NULL
sqlite> insert into test(id) values (15);
insert into test(id) values (15);
sqlite> select * from test;
select * from test;
id|value
1|one
10|ten
11|three
12|four
13|2
15|NULL
复制代码上面的代码中我们首先设置了使用NULL来显示空值，接着我们向其中插入了一条数据，这条数据我们只指定了id，没有指定value，也就是说这条数据的value是一个空值，后我们打印数据发现，刚才插入的那条数据的value以NULL进行显示。
.prompt [value]
这个命令可以改变CLP的shell提示符,如下所示:
sqlite>.prompt sqlite3>
.prompt sqlite3>
sqlite3>
复制代码可以看到，一开始Shell的提示符为sqlite，设置之后的提示符为sqlite3.
.mode
这个命令可以设置结果输出的几种格式，可选的格式有csv,column,html,insert,line,list,tabs,tcl。每种格式都有不同的用途。默认值是list,list模式下显示结果集时列间以默认的分隔符分离。其他格式的输出如下:
csv：
sqlite3>select * from test;
select * from test;
id,value
1,one
10,ten
11,three
12,four
13,2
15,this_is_null_value
复制代码可以看到，列间以,进行分隔。
我们之前已经学习过，我们可以自己指定分隔符，除了指定.mode的值以外，我们也可以通过修改分隔符达到上面的效果:
sqlite3>.mode list
.mode list
sqlite3>.separator ,
.separator ,
sqlite3>select * from test;
select * from test;
id,value
1,one
10,ten
11,three
12,four
13,2
15,this_is_null_value
复制代码这里需要注意执行顺序，如果我们首先执行.separator ,再执行.mode list，那么终输出的结果仍然是以|作为分隔符，如果像上面这样先执行.mode list，再执行separator ,，那么终就是以,作为分隔符。
column
sqlite3>.mode column
.mode column
sqlite3>select * from test;
select * from test;
id value
---------- ----------
1 one
10 ten
11 three
12 four
13 2
15 this_is_nu
复制代码html
sqlite3>.mode html
.mode html
sqlite3>select * from test;
select * from test;
<TR><TH>id</TH>
<TH>value</TH>
</TR>
<TR><TD>1</TD>
<TD>one</TD>
</TR>
<TR><TD>10</TD>
<TD>ten</TD>
</TR>
<TR><TD>11</TD>
<TD>three</TD>
</TR>
<TR><TD>12</TD>
<TD>four</TD>
</TR>
<TR><TD>13</TD>
<TD>2</TD>
</TR>
<TR><TD>15</TD>
<TD>this_is_null_value</TD>
</TR>
复制代码insert
sqlite3>select * from test;
select * from test;
INSERT INTO "table"(id,value) VALUES(1,'one');
INSERT INTO "table"(id,value) VALUES(10,'ten');
INSERT INTO "table"(id,value) VALUES(11,'three');
INSERT INTO "table"(id,value) VALUES(12,'four');
INSERT INTO "table"(id,value) VALUES(13,'2');
INSERT INTO "table"(id,value) VALUES(15,NULL);
复制代码line
sqlite3>select * from test;
select * from test;
id = 1
value = one
id = 10
value = ten
id = 11
value = three
id = 12
value = four
id = 13
value = 2
id = 15
value = this_is_null_value
复制代码tabs
select * from test;
id value
1 one
10 ten
11 three
12 four
13 2
15 this_is_null_value
复制代码tcl
sqlite3>.mode tcl
.mode tcl
sqlite3>select * from test;
select * from test;
"id" "value"
"1" "one"
"10" "ten"
"11" "three"
"12" "four"
"13" "2"
"15" "this_is_null_value"
复制代码可以看到，每种样式的输出格式都是有区别的，可以针对我们想要的输出格式设置不同的.mode属性，比如我们需要将数据输出到一个csv文件中来进行预览，则可以执行下面的操作:
sqlite3>.mode csv
.mode csv
sqlite3>.output test_csv.csv
.output test_csv.csv
sqlite3>select * from test;
sqlite3>.output stdout
.output stdout
复制代码首先我们设置了当前模式为csv，之后我们将输出重定向到test_csv.csv文件，然后执行查询语句，查询语句的结果将会输入到指定这个csv文件中，之后我们将输出重定向到了屏幕。
又比如我们希望在html文件中进行预览，则可以执行如下的操作:
//设置输出格式为html
sqlite3>.mode html
.mode html
//重定向输出的位置
sqlite3>.output test_html.html
.output test_html.html
//查询数据
sqlite3>select * from test;
//重定向输出位置到屏幕
sqlite3>.output stdout
.output stdout
复制代码经过上面的操作，我们已经可以得到输出的html文件，只是直接打开并不能按照预想的以表格的形式预览，我们还需要对这个文件添加一些html细节才能够按照表格的形式预览，简单的方法就是在外层直接加上<table></table>标签。
导出带分隔符的数据
上面我们已经学习了一些SQLite的简单命令，比如导入和导出，格式化数据等，有时候我们需要使用指定的分隔符来导入和导出数据，其实结合上面的例子我们已经能够大概了解如何做到这一点，下面首先演示了将test表中以t开头的数据导出到指定的.csv文件中，如下所示:
sqlite> .mode csv
sqlite> .separator ','
sqlite> .output test_first_t.csv
sqlite> select * from test where value like 't%';
sqlite> .output stdout
复制代码通过上面的命令，我们就成功将数据导出到了test_first_t.csv文件中。
如果我们还需要将文件中的数据导入到与test表具有相同结构的另外一张表中，则可以执行下面的命令:
sqlite> .import test_first_t.csv test2
sqlite>
sqlite>
sqlite> .mode column
sqlite> .headers on
sqlite> select * from test2;
id value
---------- ----------
10 ten
11 three
复制代码可以看到，我们成功将test_first_t.csv文件中的数据导入到test2表中。
作者：ZhangYiFan
链接：https://juejin.cn/post/7025971097401032734
