SQLite 是一个开源的嵌入式关系型数据库,是一个自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 具有如下特点:
- 无需单独的服务器进程,是无服务器的
- 不需要配置,无需复杂的安装或管理步骤
- 数据库存储在一个单一的跨平台的磁盘文件中
- 非常小的,轻量级的
- 自给自足的,不需要任何外部的依赖
- 事务是完全兼容 ACID,允许从多个进程或线程安全访问
- 支持 SQL92(SQL2)标准的大多数查询语言的功能
- 使用 ANSI-C 编写的,并提供了简单易用 API
- 兼容类 UNIX 平台(Linux, Mac OS-X, Android, iOS)和 Windows(Win32, WinCE, WinRT)平台
与其他高级的关系型数据库相比,SQLite 不支持用户管理,不支持表的访问权限管理,数据库的访问权限跟随系统文件,不支持使用各种技巧来进行额外的性能优化。其适用于需要频繁直接读/写磁盘文件的应用,需要迁移且不需要扩展的应用,完成产品功能测试等。
SQLite 是不区分大小写的。其不支持表和字段注释,但支持在 SQL 语句中添加附加注释,以增加可读性,附加注释以 --
开头。每个 SQL 语句以 ;
结束。
常用命令
SQLite 提供了命令行工具 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:
-ascii set output mode to 'ascii'
-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'
-lookaside SIZE N use N entries of SZ bytes for lookaside memory
-mmap N default mmap size set to N
-newline SEP set output row separator. Default: '
'
-nullvalue TEXT set text string for NULL values. Default ''
-pagecache SIZE N use N slots of SZ bytes each for page cache memory
-scratch SIZE N use N slots of SZ bytes each for scratch memory
-separator SEP set output column separator. Default: '|'
-stats print memory stats before each finalize
-version show SQLite version
-vfs NAME use NAME as the default VFS
又帮助信息可知,该工具直接接一个数据库文件路径来打开并连接一个数据库,如果文件不存在则创建一个新的数据库。进入命令行模式后便可对数据库进行操作:
$ sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite3
工具的内置命令都以 .
开头,否则都认为是 SQL 语句。输入 .help
可以查看所有支持的内置命令,以下列举一些常用的命令:
命令 | 描述 |
---|---|
.backup ?DB? FILE | 备份 DB 数据库(默认是 "main")到 FILE 文件 |
.bail ON/OFF | 发生错误后停止。默认为 OFF |
.databases | 列出附加数据库的名称和文件 |
.dump ?TABLE? | 以 SQL 文本格式转储数据库;如果指定了TABLE表,则只转储匹配LIKE模式的TABLE表 |
.echo ON/OFF | 开启或关闭 echo 命令 |
.exit 或 .quit | 退出命令行模式 |
.explain ON/OFF | 开启或关闭适合于 EXPLAIN 的输出模式,默认为 ON |
.header(s) ON/OFF | 开启或关闭头部显示 |
.import FILE TABLE | 导入来自 FILE 文件的数据到 TABLE 表中 |
.indices ?TABLE? | 显示所有索引的名称;如果指定了 TABLE 表,则只显示匹配LIKE模式的TABLE表的索引 |
.load FILE ?ENTRY? | 加载一个扩展库 |
.log FILE/off | 开启或关闭日志;FILE文件可以是 stderr(标准错误)/stdout(标准输出) |
.mode MODE | 设置查询输出模式 |
.nullvalue STRING | 在 NULL 值的地方输出 STRING 字符串 |
.output FILENAME | 发送输出到 FILENAME 文件 |
.output stdout | 发送输出到屏幕 |
.print STRING... | 逐字地输出 STRING 字符串 |
.prompt MAIN CONTINUE | 替换标准提示符 |
.read FILENAME | 执行 FILENAME 文件中的 SQL |
.schema ?TABLE? | 显示 CREATE 语句;如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表 |
.separator STRING | 改变输出模式和 .import 所使用的分隔符 |
.show | 显示各种设置的当前值 |
.stats ON/OFF | 开启或关闭统计 |
.tables ?PATTERN? | 列出匹配 LIKE 模式的表的名称 |
.timeout MS | 尝试打开锁定的表 MS 微秒 |
.width NUM NUM | 为 "column" 模式设置列宽度 |
.timer ON/OFF | 开启或关闭 CPU 时间测量 |
打开如下配置可以格式化查询输出,使输出更易读:
.headers on
.mode column
使用 .mode MODE
设置输出模式时,MODE 可以是以下类型:
- csv 逗号分隔的值
- column 左对齐的列
- html HTML 的 代码
- insert TABLE 表的 SQL 插入(insert)语句
- line 每行一个值
- list 由 .separator 字符串分隔的值
- tabs 由 Tab 分隔的值
- tcl TCL 列表元素
设置格式化输出还可以通过指定命令行参数实现:
$ sqlite3 -column -header <dbfile> "select * from <tablename>"
数据类型
SQLite 数据类型是一个用来指定任何对象的数据类型的属性。一般的高级数据库采用的是固定的静态数据类型,而 SQLite 采用的是动态数据类型。在 SQLite 中,值的数据类型与值本身是相关的,而不是与它的容器(比如列)相关。因此,虽然可以在创建表的时候为列指定数据类型,但这并不是必须的,SQLite 会根据存入值自动判断它的类型。
SQLite 数据库可以不为列指定数据类型,其一个字段中存储的值可以有多种数据类型,因此如下的操作是合法的:
sqlite> create table test(x, y, z);
sqlite> insert into test values (1, '2', 3.0);
sqlite> insert into test values ('a', 'b', 'c');
sqlite> insert into test values (4, 5, null);
sqlite> select * from test;
x y z
---------- ---------- ----------
1 2 3.0
a b c
4 5
这得益于 SQLite 使用的一个更普遍的动态类型系统。它将数据分别存储为不同的五种类别,又实现了 Type Affinity 以兼容 SQL 标准。
存储类别(Storage Classes)
SQLite 数据库中的值被存储为以下存储类别之一:
- NULL: 值是一个 NULL 值
- INTEGER: 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中
- REAL: 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字
- TEXT: 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储
- BLOB: 值是一个 blob 数据,完全根据它的输入存储,即直接存储为二进制
SQLite 存储类别 的概念比 数据类型 的概念更为通用。 例如,INTEGER 存储类别包括 6 种不同长度的不同整数数据类型,这仅仅在磁盘存储上有所不同。但是一旦从磁盘读取 INTEGER 值并进入内存进行处理时,其会被转换为最通用的数据类型(8字节有符号整数)。 因此,在 SQLite 中,“存储类别” 与 “数据类型” 无法明确区分,并且这两个术语可以互换使用。
除了 整形主键(INTEGER PRIMARY KEY) 列之外,SQLite3 数据库中的任何列都可用于存储任何存储类别的值。对于 SQLite 而言,即使在表声明中明确指定了字段类型,也仍然可以在该字段中存储其它类型的数据。然而,考虑到数据库的移植性问题,在实际的应用中应该尽可能的保证数据类型的存储和声明的一致性。除非有极为充分的理由,或者不用考虑移植性问题,此时确实可以使用 SQLite 提供的这种特征。
类型亲缘(Type Affinity)
为了兼容 SQL 标准,以及兼容其他数据库引擎的数据类型(方便在不同数据库之间移植),SQLite 提出了 类型亲缘性(Type Affinity)
的概念。简单的理解这一概念就是,在表字段被声明之后,SQLite 会根据字段声明时的类型为其选择一种亲缘类型,当数据插入时,该字段的数据将会 优先采用亲缘类型作为该值的存储方式,除非亲缘类型不匹配或无法转换当前数据到该亲缘类型,这时再考虑其它更适合该值的类型存储该值。SQLite 支持以下五种亲缘类型:
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB
Storage Class
是 SQLite 中数据的存储类型,它们代表的是数据实际在磁盘上面的存放类型。而 Type Affinity
则可以理解为是数据字段的推荐类型。因为 SQLite 的数据类型是动态的,只能给这个字段推荐一个类型,而不能规定这个字段只能是某一类型。
亲缘类型类型是用于 CREATE TABLE 语句中指定字段类型的,创建表时,除可以使用上述的五种亲缘类型外,还可以使用与其它高级数据库兼容的类型名称。字段的亲缘性由字段的声明类型决定,并按以下规则和顺序转换:
-
- 如果类型字符串中包含 "INT",那么该字段的亲缘类型是 INTEGER
-
- 如果类型字符串中包含 "CHAR"、"CLOB"、或 "TEXT",那么该字段的亲缘类型是 TEXT,如 VARCHAR
-
- 如果类型字符串中包含 "BLOB",或者没有指定类型,那么该字段的亲缘类型是 BLOB
-
- 如果类型字符串中包含 "REAL"、"FLOA" 或 "DOUB",那么该字段的亲缘类型是REAL
-
- 其余情况下,字段的亲缘类型为 NUMERIC
如果某一字段类型同时符合两种亲缘性,那么排在前面的规则将先产生作用。下表列出了创建表时可使用的各种数据类型名称以及对应的 Affinity: