SQLite学习--常用命令

这是我参与11月更文挑战的第2天，活动详情查看：2021最后一次更文挑战

创建数据库

通过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表中。