Sqlite 基本概念及使用概述

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: '\n'
   -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 的 <table> 代码
• 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 语句中指定字段类型的，创建表时，除可以使用上述的五种亲缘类型外，还可以使用与其它高级数据库兼容的类型名称。字段的亲缘性由字段的声明类型决定，并按以下规则和顺序转换：

• 1. 如果类型字符串中包含 "INT"，那么该字段的亲缘类型是 INTEGER
• 2. 如果类型字符串中包含 "CHAR"、"CLOB"、或 "TEXT"，那么该字段的亲缘类型是 TEXT，如 VARCHAR
• 3. 如果类型字符串中包含 "BLOB"，或者没有指定类型，那么该字段的亲缘类型是 BLOB
• 4. 如果类型字符串中包含 "REAL"、"FLOA" 或 "DOUB"，那么该字段的亲缘类型是REAL
• 5. 其余情况下，字段的亲缘类型为 NUMERIC

如果某一字段类型同时符合两种亲缘性，那么排在前面的规则将先产生作用。下表列出了创建表时可使用的各种数据类型名称以及对应的 Affinity：

数据类型	Affinity
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB no datatype specified	BLOB
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

示例：

sqlite> CREATE TABLE TEST(ID INTEGER PRIMARY KEY, XX VARCHAR(255), YY TINYINT);
sqlite> INSERT INTO TEST VALUES (NULL, 'a', 1);
sqlite> INSERT INTO TEST VALUES (NULL, 2, '3');
sqlite> INSERT INTO TEST VALUES (NULL, 4.0, 5.0);
sqlite> INSERT INTO TEST VALUES (NULL, '-2.1', 'abc');
sqlite> SELECT * FROM TEST;
ID          XX          YY
----------  ----------  ----------
1           a           1
2           2           3
3           4.0         5
4           -2.1        abc
sqlite> SELECT typeof(id), typeof(xx), typeof(yy) FROM TEST;
typeof(id)  typeof(xx)  typeof(yy)
----------  ----------  ----------
integer     text        integer
integer     text        integer
integer     text        integer
integer     text        text

注意，SQLite 没有单独的 Boolean 数据存储类别。需要存储布尔值时可以使用整数 0（false）和 1（true）代替。

时间/日期类型

SQLite 没有单独的用于存储日期/时间的存储类别，但其能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。各类日期和时间的存储类别如下表所示：

存储类别	日期格式
TEXT	格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期
REAL	从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数
INTEGER	从 1970-01-01 00:00:00 UTC 算起的秒数

可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。

数值比较

在 SQLite3 中支持的比较表达式有："=", "==", "<", "<=", ">", ">=", "!=", "", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT"。

数据的比较结果主要依赖于操作数的存储方式，其规则为：

• 1. 存储方式为 NULL 的数值小于其它存储类型的值
• 2. 存储方式为 INTEGER 和 REAL 的数值小于 TEXT 或 BLOB 类型的值，如果同为 INTEGER 或 REAL，则基于数值规则进行比较
• 3. 存储方式为 TEXT 的数值小于 BLOB 类型的值，如果同为 TEXT，则基于文本规则(ASCII 值)进行比较
• 4. 如果是两个BLOB类型的数值进行比较，其结果为 C 运行时函数 memcmp() 的结果

数据库管理

创建数据库

SQLite 的数据库为一个单一的文件，使用 sqlite3 命令行工具接一个文件名即可创建一个数据库。创建数据库的权限为当前用户创建文件的权限。数据库文件没有特殊的扩展名要求，但通常为 sqlite 数据库文件加上 .sqlite 或 .db 后缀。

使用 sqlite3 命令创建数据库示例：

$ sqlite3 test.db
SQLite version 3.19.3
Enter ".help" for usage hints.
sqlite> .databases
main: /home/huoty/temp/test.db
sqlite> .exit

$ ls
test.db

以上示例会在当前目录下创建一个 test.db 文件，该文件将被 SQLite 引擎用作数据库。数据库被创建后，可以使用 .databases 命令来检查其是否在数据库列表中。

附加数据库

对于连接到一个数据库后，同时又需要访问另一个数据库中的数据的情况，SQLite 提供了一种将外部数据库附加到当前数据库连接的机制，即 ATTACH DATABAS。基本语法：

ATTACH [DATABASE] 'database_path' As 'alias_name';

取消附加(分离数据库)：

DETACH [DATABASE] 'alias_name';

使用示例：

$ sqlite3 test.db
SQLite version 3.19.3
Enter ".help" for usage hints.
sqlite> create table t1(a, b, c);
sqlite> insert into t1 values (1, 2, 3);
sqlite> .databases
main: /home/huoty/temp/test.db
sqlite> .tables
t1
sqlite> attach 'test2.db' as test2;
sqlite> .databases
main: /home/huoty/temp/test.db
test2: /home/huoty/temp/test2.db
sqlite> create table test2.t2(x, y, z);
sqlite> .tables
t1        test2.t2
sqlite> insert into test2.t2 values (1.1, 1.2, 1.3);
sqlite> select * from t1;
a           b           c
----------  ----------  ----------
1           2           3
sqlite> select * from t2;
x           y           z
----------  ----------  ----------
1.1         1.2         1.3
sqlite> select * from test2.t2;
x           y           z
----------  ----------  ----------
1.1         1.2         1.3
sqlite> detach test2;
sqlite> .databases
main: /home/huoty/temp/test.db

备份与恢复

要备份数据库可以使用 .backup 命令，从备份恢复数据时使用 .restore 命令:

sqlite> .backup 'backup.db'
sqlite> .restore 'backup.db'

实际上，.backup 命令导出的备份文件是原数据库的一个拷贝，其完全可以当成一个完成的数据库来用。 另一种备份方式是，使用 .dump 命令，将完整的数据库或指定的表导出到一个文本文件中，该方式导出的是 SQL 语句。如果不指定表名则导出整个数据库，否则仅导出指定表的内容。示例：

$ sqlite3 test.db .dump > test.db.sql

要从导出的 SQL 文件中恢复数据，采用如下方式：

$ sqlite3 test.db < test.db.sql

或者进入命令行使用 .read 命令：

sqlite> .read test.db.sql

还可以是用 .output 将输出重定向输出文件中。例如通过 .mode 和 .separator 命令可以将输出配置为 csv 格式，然后重定向到文件。如果要从 csv 文件恢复数据，可以使用 .import 命令。

表管理

创建表

创建数据库使用 CREATE TABLE 语句完成，其基本语法为：

CREATE TABLE database_name.table_name(
       column1 datatype  PRIMARY KEY,
       column2 datatype,
       column3 datatype,
       .....
       columnN datatype,
    );

例如创建一个 COMPANY 表：

CREATE TABLE COMPANY(
       ID      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
       NAME    TEXT    NOT NULL UNIQUE,
       AGE     INT     NOT NULL DEFAULT 0,
       ADDRESS CHAR(50),
       SALARY  REAL,
       ADD_TIME    DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
       UPDATE_TIME DATETIME NULL
);

如果设置自增列，则该列必须是 INTEGER 类型，且必须是主键。UNIQUE 关键字可用于约束字段的唯一性。DEFAULT 关键字可以指定缺省值，如果需要设置缺省日期、时间为本地当前时间可以用 datetime 函数实现，如：

DEFAULT (datetime('now','localtime'))

内置表

SQLite 数据库中有一个特殊的名叫 sqlite_master 内置的表，它定义数据库的模式，记录了所有已创建表的信息。该表的结构为：

sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

对于表来说，type 字段永远是 'table'，name 字段永远是表的名字。所以，要获得数据库中所有表的列表， 可以使用 SELECT 语句查询该表：

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

对于索引，type 等于 'index', name 则是索引的名字，tbl_name 是该索引所属的表的名字。 不管是表还是索引，sql 字段是原先用 CREATE TABLE 或 CREATE INDEX 语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARY KEY 或 UNIQUE 约束），sql 字段为 NULL。

SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。 它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。 临时表不会出现在 sqlite_master 表中。临时表及其索引和触发器存放在另外一个叫 sqlite_temp_master 的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多， 但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表， 不管是永久的还是临时的，可以使用类似下面的语句：

SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name;

此外，SQLite 还有一个 sqlite_sequence 的内置表，该表用来保存其他表的 RowID 的最大值设置。当用户创建的表包含自增列时，会在 sqlite_sequence 表中增加一条记录，并记录该表中当前自增值的最大值。如果需要设置表的自增字段起始值，可以用如下语句

UPDATE sqlite_sequence SET seq=<n> WHERE name='tablename'

清空表

清空表使用不带 WHERE 条件的 DELETE 语句，该语句是一行一行的删除记录，直至全部删除。如果表设置了自增字段，delete 表数据后计数器不会归零，所以还需在 sqlite_sequence 表中将自增起始值置为 0。语句如下：

DELETE FROM tablename;
UPDATE sqlite_sequence SET seq=0 where name='tablename';

删除表

要删除表定义及其所有相关数据、索引、触发器、约束，使用 DROP TABLE 语句：

DROP TABLE tablename;

修改表

修改表使用 ALTER TABLE 语句，该语句可以重命名表，以及给表添加额外的列。除此外，该语句不支持其他操作。基本语句：

-- 重命名表
ALTER TABLE tablename RENAME TO new_tablename;

-- 添加一列
ALTER TABLE table_name ADD COLUMN column_def...;

复制表

有时可能需要将一个表的数据复制到另一个表，或者整体复制一个表的结构和数据。复制表的操作语法如下：

-- 仅复制数据
INSERT INTO dest_table SELECT * FROM source_table [WHERE ...];

-- 仅复制表结构
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 0;

-- 同时复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM old_table;

约束

约束用于确保数据库中数据的准确性和可靠性。约束通过在表的数据列上强制执行的规则，从而限制可以插入到表中的数据的类型。SQLite 中支持的约束包括

• NOT NULL 约束： 确保某列不能有 NULL 值
• DEFAULT 约束： 当某列没有指定值时，为该列提供默认值
• UNIQUE 约束： 确保某列中的所有值是不同的
• PRIMARY KEY 约束： 唯一标识数据库表中的各行/记录
• CHECK 约束： CHECK 约束确保某列中的所有值满足一定条件
• FOREIGN KEY 约束： 外键约束，默认不支持，需要手动开启

一个表中可以有多个 UNIQUE 列，但只能有一个主键。如果设置自增主键，则该列必须是 INTEGER 类型。主键可以由一个或多个字段组成，当多个字段作为主键时，其被称为复合键。声明复合键的方式为：

CREATE TABLE tablename (
  column1,
  column2,
  column3,
  PRIMARY KEY (column1, column2)
);

CHECK 约束会在插入记录时检查值是否符合条件。如果条件值为 false，则记录违反约束，且不能插入到表。示例：

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL UNIQUE,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

SQLite 不支持修改表中字段的约束条件，用于修改表结构的语句 ALTER TABLE 仅允许重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的，只有在创建表时就定义好约束条件。

SQLite 从版本 3.6.19 (2009-10-14) 开始支持 外键约束(Foreign Key)。外键约束用于关联两个表，使之存在关系，这也是关系型数据库的重要特点之一。SQLite 的外键支持默认是关闭的，必须在运行时手动开启。开启外键支持需使用 PRAGMA 语句：

PRAGMA foreign_keys=ON;

通常，外键约束的列在父表里需要是主键。如果不是主键，那么也必须受一个 UNIQUE 约束或者有一个 UNIQUE 索引。即外键在父表中必须是唯一的。创建外键的基本语法

CREATE TABLE table_name
(
    column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    ...
    CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

-- 其中 CONSTRAINT fk_column 表示为定义的外键声明一个名字

示例：

-- 父表
CREATE TABLE pt(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(20) NOT NULL
);

-- 拥有外键的表
CREATE TABLE st(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    p_name VARCHAR(20) NOT NULL,
    value FLOAT,
    CONSTRAINT fk_p_name FOREIGN KEY (p_name) REFERENCES pt(name)
);

索引

索引(Index)用来排序数据以加快搜索和排序操作的速度，其是一种特殊的查找表，是一个指向表中数据的指针。索引具有如下特点：

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能
• 索引数据可能占用大量的存储空间
• 索引用于数据过滤和数据排序
• 可以指定多个列来组成一个索引，即复合索引（或称多列索引）
• 索引可以创建或删除，但不影响原始数据
• 索引可以指定唯一性，即唯一索引

创建索引的基本语法如下：

-- 单列索引
CREATE INDEX index_name ON table_name (column_name);

-- 唯一索引
CREATE UNIQUE INDEX index_name on table_name (column_name);

-- 组合索引
CREATE INDEX index_name on table_name (column1, column2);

索引自动创建为

数据库会为拥有主键约束和唯一约束的列自动创建索引，这样的索引可以称之为隐式索引。索引可以使用 DROP 语句删除，基本语法如下：

-- 删除索引
DROP INDEX index_name;

在操作表时，可以显式的指定使用哪个索引或者指定不使用索引。显式指定索引用 INDEXED BY 语句，禁用索引用 NOT INDEXED 语句。基本语法如下：

SELECT|DELETE|UPDATE column1, column2...
[INDEXED BY (index_name) | NOT INDEXED]
table_name
WHERE (CONDITION);

并非所有数据都适合做索引，使用索引时应该考虑下列准则：

• 索引不应该使用在较小的表上
• 索引不应该使用在有频繁的大批量的更新或插入操作的表上
• 索引不应该使用在含有大量的 NULL 值的列上
• 索引不应该使用在频繁操作的列上
• 索引不应该使用在有较少可能值的列上

触发器

SQLite 的触发器是数据库的回调函数，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、 UPDATE 和 DELETE 操作（或它们的组合）相关联。触发器的一些常见用途如下：

• 保证数据一致性，如在 INSERT 或 UPDATE 操作时对数据进行统一转化
• 计算列的值或更新时间戳，如每次 UPDATE 时更新 update_time 字段
• 某个表中的记录变化时在其他表上执行活动，如更新或删除数据时将审计跟踪记录写入日志表
• 验证输入数据是否符合预期，如限定 age 字段必须大于 18

创建触发器的基本语法如下：

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- Trigger logic goes here....
END;

BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。接着的 event_name 可以是 INSERT，DELETE 和 UPDATE 数据库操作。

SQLite 目前仅支持 FOR EACH ROW 触发器，没有 FOR EACH STATEMENT 触发器。因此，不用明确指定 FOR EACH ROW 语句，可以省略。

如果提供了 WHEN 子句，则只针对 WHEN 子句为真的行执行 SQL 语句，否则针对所有行执行 SQL 语句。

WHEN 子句和触发器动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素。其中 column-name 是从与触发器关联的表的列的名称，NEW 只出现在 INSERT 和 UPDATE 时，OLD 只出现在 UPDATE 和 DELETE 时，在 INSERT 时 NEW 表示新插入的行数据，UPDATE 时 NEW 表示要替换的新数据、OLD 表示要被更改的原来的数据行， DELETE 时 OLD 表示要被删除的数据。

日期与时间函数

SQLite 支持如下的时间/日期处理函数：

函数	说明
date(timestring, modifier, modifier, ...)	返回 YYYY-MM-DD 格式的日期
time(timestring, modifier, modifier, ...)	返回 HH:MM:SS 格式的时间
datetime(timestring, modifier, modifier, ...)	返回 YYYY-MM-DD HH:MM:SS 格式的日期时间
julianday(timestring, modifier, modifier, ...)	返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数
strftime(format, timestring, modifier, modifier, ...)	根据第一个参数指定的格式字符串返回格式化的日期

以上函数将时间字符串作为参数，通过指定零个或者多个 modifier 修饰符来输出时间。支持的时间字符串换样式如下：

时间字符串	示例
YYYY-MM-DD	date('2010-12-30')
YYYY-MM-DD HH:MM	datetime('2010-12-30 12:10')
YYYY-MM-DD HH:MM:SS.SSS	datetime('2010-12-30 12:10:04.100')
HH:MM	datetime('12:10')
YYYY-MM-DD T HH:MM	datetime('2010-12-30T12:10')
HH:MM:SS	datetime('12:10:01')
now	datetime('now')

时间字符串后边可跟着零个或多个的修饰符，这将改变时间/日期函数的输出，修饰符从左到右依次生效。SQLite 支持的修饰符如下：

修饰符	说明
N days	增加 N 天(N 可为负，下同)
N hours	增加 N 小时
N minutes	增加 N 分钟
N.N seconds	增加 N.N 秒
N months	增加 N 月
N years	增加 N 年
start of month	月的开始
start of year	年的开始
start of day	天的开始
weekday N	增加到下个星期 N
unixepoch	从 1970-01-01 开始算起的秒数
localtime	本地时间
utc	协调世界时

一些使用示例：

sqlite> SELECT DATE('2006-10-17', '+1 day', '+1 year');
2007-10-18
sqlite> SELECT TIME('now','localtime');
15:52:20
sqlite> SELECT DATETIME('2016-10-17 00:20:00', '+1 hour', '-12 minute');
2016-10-17 01:08:00
sqlite> SELECT DATETIME('now', 'localtime');
2018-07-22 15:52:47
sqlite> SELECT DATETIME('now', '+8 hour');
2018-07-22 15:52:55
sqlite> SELECT DATETIME('now', 'localtime', '+2 hour' , '+20 minute' , '-20 second');
2018-07-22 18:12:52
sqlite> SELECT DATETIME ('now', 'localtime', 'start of year');
2018-01-01 00:00:00
sqlite> SELECT DATETIME('2016-10-17 00:20:00', '+1 hour', '-12 minute', 'start of month');
2016-10-01 00:00:00
sqlite> SELECT DATETIME('now', 'localtime', 'start of day');
2018-07-22 00:00:00
sqlite> SELECT DATE('now', 'start of month', '+1 month', '-1 day');
2018-07-31
sqlite> SELECT STRFTIME('%Y.%m.%d %H:%M:%S','now','localtime');
2018.07.22 15:55:57

锁机制

SQLite 基于锁来实现并发控制，以保证数据的安全和完成性。SQLite 的锁是粗粒度的，并不像其他高级数据引擎一样拥有更加细粒度的表锁或行锁。当有连接在写数据库时，数据库则被锁住直到写事务结束，所以所有其它的连接都无法对数据库进行读写操作。SQLite 在 3.7.0 版本开始引入了 WAL 技术，全称叫 Write Ahead Log（预写日志），WAL 技术可以解决读写互斥的问题。

锁状态

SQLite 的数据库连接有 5 种锁状态：未加锁(unlocked)、 共享锁(shared)、 预留锁(reserved)、 预留锁(reserved)、 未决锁(pending)、 排它锁(exclusive)。SQLite 使用锁逐步上升机制，如果需要写数据库，连接必须逐级地获得排它锁。

• 未加锁: 未加锁状态的情况包括，未和数据库建立连接，已建立连接但是还没访问数据库，已用 BEGIN 开始了一个事务但未开始读写数据库。该状态为缺省状态，其它的连接可以在数据库上执行任意的读写操作
• 共享锁: 当连接要从数据库中读取数据时，必须先申请获得一个共享锁，如果获得成功，则进入到共享状态。在该状态下，数据库可以被读取但是不能被写入。在同一时刻可以有任意数量的连接在同一个数据库上持有共享锁，因此读操作是并发的。也就是说，只要有一个或多个共享锁处于活动状态，就不再允许有数据库文件写入的操作存在
• 预留锁: 当连接需要写数据库时，需先申请一个预留锁。一个数据库同时只能有一个预留锁，预留锁可以与共享锁共存，保留锁是写数据库的第一阶段。保留锁即不阻止其它拥有共享锁的连接继续读数据库，也不阻止其它连接获得新的共享锁。获得预留锁后进入预留状态，这时会先在缓冲区中进行需要的修改、更新操作，操作后的结果依然保存在缓冲区中，未真正写入数据库
• 未决锁: 当连接需要从预留升为排它锁以完成写操作（提交修改）时，需要先升级到未决锁，此时其它连接就不能再获得共享锁，但已经拥有共享锁的连接仍然可以继续正常读数据库。拥有未决锁的连接会等待其它拥有共享锁的连接完成读操作并释放其共享锁后，提成到排它锁
• 排它锁: 当连接需要提交修改时，需要先获得排它锁。排它锁不允许其它连接获得任何锁，直到当前连接的排它锁被释放。获得排它锁的连接可以自由地对数据库进行修改，所有以前对缓冲区所做的修改都会被写到数据库文件

死锁

在使用事务的情况下，SQLite的锁机制存在死锁的可能性。如以下例子：

执行顺序	连接A	连接B
1	BEGIN;
2		BEGIN;
3		INSERT INTO foo VALUES (‘x’);
4	SELECT * FROM foo;
5		COMMIT;
6		SQL error: database is locked
7	INSERT INTO foo VALUES (‘x’);
8	SQL error: database is locked

• 执行顺序3：连接B要执行写操作，获得预留锁
• 执行顺序4：连接A要执行读操作，获得共享锁
• 执行顺序5：连接B要提交修改，预留锁升级为未决锁
• 执行顺序6：连接B想要升级为排它锁，必须先等待连接A释放共享锁
• 执行顺序7：连接A要执行写操作，需要获得预留锁
• 执行顺序8：连接A获得预留锁失败，必须先等待连接B释放未决锁

于是连接 A 和连接 B 相互等待对方，发生死锁。可以通过正确的使用事务类型来解决以上死锁问题。

WAL 技术

WAL 技术的原理是，修改并不直接写入到数据库文件中，而是写入到另外一个称为 WAL 的文件中；如果事务失败，WAL 中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。

WAL 使用检查点将修改写回数据库，默认情况下，当 WAL 文件发现有 1000 页修改时，将自动调用检查点。这个页数大小可以自行配置。WAL 技术带来以下优点：

• 读写操作不再互相阻塞，一定程度上解决了处理高并发上的性能瓶颈
• 磁盘 I/O 行为更容易被预测
• 数据达到 GB 级时，性能可能会降低。

启用 WAL 需用 PRAGMA 执行以下语句：

PRAGMA journal_mode=WAL;

与 MySQL 差异

以下总结了在工作和学习过程中发现的 SQLite 与 MySQL 之间的一些差异：

• SQLite 不支持表和列注释，不支持存储过程
• MySQL 采用的是静态数据类型，每个字段中值得类型在创建表时被确定，而 SQLite 采用的是动态类型，一个字段中存储的值的类型可以不同
• 自增键 MySQL 为 AUTO_INCREMENT， SQLite 为 AUTOINCREMENT
• SQLite 自增主键只能是 INTEGER 类型
• SQLite 必须在定义字段时声明为 PRIMARY KEY 才能用 AUTOINCREMENT

`ID`  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

• MySQL 可以直接在定义表结构时指定自增字段的起始值，而 SQLite 需要手动在 内置表中设置：

-- MySQL 通过 AUTO_INCREMENT 来设置自增起始值
CREATE TABLE `test` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `DATA` FLOAT DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=520 DEFAULT CHARSET=utf8;

-- SQLite 通过更新 sqlite_sequence 表来说设置自增起始值
CREATE TABLE `test` (
`ID`  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`DATA` REAL,
PRIMARY KEY (`ID`)
);
UPDATE sqlite_sequence SET seq=520 WHERE name='test';

• SQLite 不能自己填充自增主键字段，即主键不能缺失，需要将自增主键设为 NULL 才能自动自增：

INSERT INTO test VALUES (NULL, 1);

• 设置字段的缺省日期/时间为本地当前时间：

-- MySQL 用 CURRENT_TIMESTAMP 实现设置缺省值为当前时间
-- 但是包含 CURRENT_TIMESTAMP 缺省值的字段只能有一个
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

-- MySQL 还可以用 ON UPDATE 指定在更新记录是自动填充该字段为当前时间
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP


-- SQLite 也支持 CURRENT_TIMESTAMP 字段，但其不能返回当前时区的时间
-- 要返回当前时区的时间需要使用日期/时间函数，并指定 localtime 修饰符
`add_time`    DATETIME NOT NULL DEFAULT (datetime('now', 'localtime'))

-- SQLite 支持多个字段存在缺省日期/时间，但不像 MySQL 那样支持 ON UPDATE
-- 要实现 ON UPDATE 的功能，可以用触发器实现

• 存在则更新不存在则插入，MySQL 用 ON DUPLICATE KEY UPDATE，而 SQLite 只能用 REPLACE INTO。需要注意的是，MySQL 也支持 REPLACE INTO，该语句的原理是，首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则 先删除此行数据，然后插入新的数据。

参考资料

• https://www.sqlite.org/datatype3.html
• https://doc.yonyoucloud.com/doc/wiki/project/sqlite/index.html
• https://blog.csdn.net/yanbober/article/details/45567149
• https://dba.stackexchange.com/questions/40656/how-to-properly-format-sqlite-shell-output
• http://liuduo.me/2016/04/02/sqlitelockandwal/