Sqlite #1 命令行工具sqlite3

SQLite是一个嵌入式关系型数据库引擎，没有服务器。引擎文件非常小，数据库文件可以跨平台、跨架构地移植。

相关概念[^1]

关系型数据库

关系型数据库(relational database)通过表(table)来组织数据，表之间存在关系(relations)。SQL是管理关系型数据库中数据的语言。数据库系统的模式通过SQL来描述，模式定义了表(tables), 字段(fields), 关系(relationships), 视图(views), 索引(indexes), 处理过程(processes), 函数(functions), 队列(queues), 触发器(triggers)和其他元素。

表

表包含水平的行(rows)和竖直的列(columns)，每行代表表中的一条结构化数据，也叫记录；每列是某种特定类型的数据。行和列交汇的是一个字段(field)。

视图(views)是对单表或多表中的数据进行的特定整合，本质上是一个查询结果得到的虚拟表。

索引(indexes)是种数据结构，可以提高查询的速度。

关系

主键(primary key)唯一标识表中的每行记录，外键(foreign key)是表之间的引用约束(referential constraint), 外键定义了一个表中一列或多列与其他表中的一列或多列间的引用关系。

存储过程

触发器(triggers)是一组过程代码，可以响应特定表中的事件而自动执行。

事务(transaction)是数据库操作的原子单元，事务中的代码如果全部成功就会提交给数据库，如果出现失败就会回滚。

查询

结果集(result set)是SELECT语句从数据库查询得到的一组数据行。

sqlite3命令行工具

查看sqlite3命令选项

$ sqlite3 --help                                                        ✘
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -mmap N              default mmap size set to N
   -nullvalue TEXT      set text string for NULL values. Default ''
   -separator SEP       set output field separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS

这些参数基本与交互式上下文的命令一致。

如果只需执行几条语句，可以直接通过sqlite3命令来执行，不必进入交互式上下文。比如备份表

$ sqlite3 ppurl.db3 ".dump books" > books_bak.sql
$ sqlite3 ppurl.db3 < books_bak.sql

创建数据库

$ sqlite3 ppurl.db3

如果ppurl.db3不存在就会被创建，如果存在就会打开数据库; 然后就会进入sqlite3的交互式上下文。

查看元信息

进入sqlite3交互式上下文后，可以通过.help查看所有命令，sqlite3的命令都是以句点开头的，退出使用.quit/.exit/.q

.databases/.database查看数据库

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /Users/dou/ws/py/db/ppurl/ppurl.db3

.tables/.table列出所有表和视图

sqlite> .tables
books

.indices查看索引 .schema查看模式

 sqlite> .schema books
CREATE TABLE books(id integer primary key autoincrement, name varchar(255), link text);
-- sqlite其实不用声明字段的类型，因为可以存储任意类型的数据，sqlite会自动调整

SELECT查询表

SELECT语句要以分号结尾，--表示注释

sqlite> select * from books;

结果太多，可以limit指定记录数

 sqlite> select * from books limit 1;
1|使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf|ed2k://|file|%E4%BD%BF%E7%94%A8Perl%E5%AE%9E%E7%8E%B0%E7%B3%BB%E7%BB%9F%E7%AE%A1%E7%90%86%E8%87%AA%E5%8A%A8%E5%8C%96%28%E7%AC%AC%E4%BA%8C%E7%89%88%29%28ZIP%E5%8D%B72%29.pdf|39561391|aa1bf37d2fc514832f01e212ce54761a|h=xb7y34a2wtnmnmvpcov764xx33l7qgy2|/

默认情况下使用|分隔字段，并且没有打印表头

.show查看默认设置

sqlite> .show
     echo: off
      eqp: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width:

.separator更改分隔符

sqlite> .separator "\r\n"

.headers/.header打开表头开关

sqlite> .headers on

.width设置每列宽度

sqlite> .width 4 10 50

.width只有在column多列模式下才有作用

.mode结果显示模式

sqlite> .mode
Error: mode should be one of: column csv html insert line list tabs tcl

column姿势

id  name
--  -------------------------------------------------------------
1   使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf

csv姿势

id,name
1,"使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf"

html

<TR><TH>id</TH>
<TH>name</TH>
</TR>
<TR><TD>1</TD>
<TD>使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf</TD>
</TR>

insert

INSERT INTO table VALUES(1,'使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf');

line

 id = 1
 name = 使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf

list

id|name
1|使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf
-- csv, tabs会更改separator, 所以如果分隔符是逗点或者制表符不要惊奇

tabs

id    name
1    使用Perl实现系统管理自动化(第二版)(ZIP卷2).pdf

tcl

"id" "name"
"1" "使�\224�Perl�\236�\216�系�\237管�\220\206�\207��\212��\214\226(第�\214�\211\210)(ZIP�\215�2).pdf"
-- 这个乱码的原因有点诡异

导入导出数据

.dump导出表

sqlite> .dump books

默认输出到标准输出(.show列出的output查看输出位置)

.output更改输出位置

sqlite> .output books.sql

这里注意，.output会把所有输出重定向到books.sql，所以导出表之后，要把输出改回来。另外输出重定向是追加模式，所以导出表前，最好不要敲其他命令。对配置的更改不是全局的，下次进入交互式上下文时，所有设置都是默认的。

.read导入表

sqlite> .read books.sql

.import从文件导入数据到表

这样可以恢复表

.save保存内存数据库到文件

sqlite>.save in-memory.db3

sqlite>.shell ls -l *.db3
-rw-r--r--  1 dou  staff  2760704  2  1 15:47 in-memory.db3
-rw-r--r--  1 dou  staff  2760704  1 31 23:37 ppurl.db3
-- 可以看到两个数据库大小一样

.restore从文件恢复数据库 .open打开其他数据库

sqlite>.open in-memory.db3
-- .open会关闭当前数据库

.clone数据库

sqlite>.clone books_clone.db3

其他语句

运行shell命令

在交互式上下文中不想退出去敲各种shell命令，可以使用.shell,.system

sqlite> .system ls -l
sqlite> .shell ls -l

更改提示符

如果觉得sqlite>这种提示符不爽，可以用.prompt修改

sqlite>.prompt books>
books>

[1] http://zetcode.com/db/sqlite/introduction/