SQLite

是一个进程内的库，实现自给自足，无服务器，零配置，具有事务性的 SQL 数据库。

官方地址 提供了最新的 SQLite 版本，咨询，和教程。

使用场景

• 不需要单独的服务器进程或操作的系统。
• SQLite 不需要配置，也就是说不需要安装或管理。
• 一个完整的 SQLite 数据库就是一个单一的文件。
• SQLite 非常小，完全配置时小于 400KiB, 省略可选功能则会小于 250 KiB。
• 是自给自足的，即不需要外部依赖。
• 事物兼容 ACID，使得可以多进程或线程安全访问。
• 支持 SQL92 标准的大部分查询语言功能。
• 使用 ANSI-C 编写，提供了简单易用的 API。
• 跨平台运行。

局限性

SQLite 不支持的 SQL92 标准特性有：

特性	描述
RIGHT OUTER JOIN	仅实现 LEFT OUTER JOIN
FULL OUTER JOIN	同上
ALTER TABLE	仅支持 RENAME， ADD COLUMN，不支持 DROP COLUMN，ALTER COLUMN，ADD CONSTRANT
Trigger 支持	支持 FOR EACH ROW 触发器，不支持 FOR EACHSTATEMENT 触发器
VIEWS	视图是只读的，不能做 DELETE， INSERT， UPDATE
GRANT 和 REVOKE	可应用的唯一访问权限只有底层操作系统的正常文件访问权限

命令

基于操作性质可分为：

DDL （Data Definition Language）

数据定义语言。

命令	描述
CREATE	创建新的表，表视图，或其他对象
ALTER	修改某个已有数据库对象
DROP	删除表，表视图，或其他对象

DML (Data Manipulation Language)

数据修改语言。

命令	描述
INSERT	创建记录
UPDATE	更新记录
DELETE	删除记录

DQL (Data Query Language)

数据查询语言。

命令	描述
SELECT	从一个或多个表中搜索记录

DCL (Data Control Language)

数据控制语言。

比如给予撤销授权，回滚或提交等。

点命令

不以分号结尾。

可以使用 .help 获取帮助。

命令	描述
.backup ?DB ?FILE	备份 DB 到 FILE 文件
.bail ON|OFF	发生错误后停止 默认 OFF
.database	列出数据库名称和依附文件
.dump ?TABLE?	SQL 格式存储数据库，指定了表则只存储匹配 LIKE 模式的表
.echo ON|OFF	开关 echo
.exit| .quit	退出
.explain ON|OFF	开关适合 EXPLAN 的输出格式 无参默认开启
.header(s) ON|OFF	开关头部显示
.help	帮助
.import FILE TABLE	导入 FILE 文件的数据到 TABLE 表
.include ?TABLE?	显示所有索引的名称，指定 TABLE 会进行 LIKE 匹配
.load FILE ?ENTRY?	加载扩展库
.mode MODE	输出模式1
.nullvalue STRING	在 NULL 值地方输出 STRING 字符串
.output FILENAME	发送输出到 FILENAME 文件
.output stdout	发送输出到屏幕
.print STRING	逐字输出 STRING 字符串
.prompt MAIN CONTINUE	替换标准提示符
.read FILENAME	执行 FILENAME 中的 SQL
.schema ?TABLE?	显示表的 CREATE 语句，指定 TABLE 会进行 LIKE 匹配
.separator STRING	改变输出模式和 .import 所使用的分隔符
.show	显示各种设置的当前值
.stats ON|OFF	开关统计
.tables ?PATTERN?	列出 LIKE 的表名称
.timeout MS	尝试打开锁定表的 MS 毫秒
.width NUM NUM	为 column 输出模式设置列宽度
.timer ON|OFF	开关 CPU 定时器

输出模式

• csv 逗号分割值。
• column 左对齐列。
• html html 格式的 table。
• insert TABLE 的 SQL INSERT语句。
• line 每行一个值。
• list 由 .separator 字符串分割的值。
• tabs 由 Tab 分割的值。
• tcl TCL 列表元素。

SQLite 语法

SQLite 是不区分大小写的,但某些命令大小写敏感
比如 GLOB 和 glob 就具有不同的含义。

---

注释

SQLite 注释是附加的注释，可以出现在任何空白地方，但是不能嵌套。

-- 这是一行注释
sqlite>.help -- 这也是一行注释

/**
多行注释
*/

语句

可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等。

需要以分号结尾。

数据类型

存储类

每一个存储在 SQLite 中都具有以下存储类之一：

名称	描述
NULL	值为 NULL
INTEGER	值为带符号整数，根据值大小存储在 1,2,3,4,6,8 字节中
REAL	值为浮点数，存储为 8 字节的 IEEE 浮点
TEXT	值为文本，使用数据库编码存储
BLOB	值为 blob 数据，根据输入存储

亲和 (Affinity) 类型

支持列的亲和类型。列可以存储任何类型的数据，插入数据时会优先使用亲和类型作为值存储方式。

亲和类型	描述
TEXT	数值类型被插入前需要被首先转换为文本
NUMERIC	当文本插入时，转换操作不对会导致数据丢失，SQLite 会将该文本转换为 INTEGER 或 REAL 类型。失败则使用 TEXT。对于 NULL 或 BLOB 则不做转换，直接存储
INTEGER	规则等同 NUMERIC, 但执行 CAST 时会不一样
NONE	不做处理，直接以数据所属类型存储

数据类型	亲和类型
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)、TEXTCLOB	TEXT
BLOB、未指定类型	BLOB
REAL、DOUBLE、DOUBLE PRECISION、FLOAT	REAL
NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME	NUMERIC

---

Boolean 类型会被存储为整数 0 和 1，代表 false 和 true。

---

SQLite 没有单独的 TIME 或 DATE 存储类型，但是可以将它们存储为：

• REAL （从公元前 4714 年 11 月 24 日格林尼治时间正午开始的天数）。
• TEXT （YYYY-MM-DD HH:MM:SS.SSS）。
• INTEGER (从 1970-01-01 00:00:00 UTC 开始的秒数)。

---

SQLITE 操作

数据库操作

创建

$sqlite3 DatabaseName.db

建立一个 DatabaseName 名称的数据库，也可以使用 .open 建立。

.open DatabaseName.db

两者都会将数据库文件建立于执行目录。

使用 ``.databases` 查看是否存在数据库列表中。

---

导出

也可以使用 .dump 将数据库导出。

$sqlite3 DatabaseName.db .dump > export.sql

导入

$sqlite3 DatabaseName.db < export.sql

---

附加数据库

即同一时间内多个数据库可用，但只使用其中一个。

ATTACH DATABASE FileName AS DatabaseName;

若数据库尚未创建，则创建一个，否则使用 DatabaseName 作为该数据库别名使用。

打开的数据库和使用附加的数据库必须位于同一文件夹下。

分离数据库

将命名数据库从一个数据库连接分离和游离出。

连接是通过 ATTACH 附加创建的。

同一数据库文件如果被附加多个别名，则只会分离指名连接。

无法分离 main 或 temp 数据库。

如果数据库是内存中的或属于临时数据库，则将被摧毁，内容也会丢失。

DETACH DATABASE 'AliasName';
-- AliasName 为使用 ATTACH 创建的别名

---

表操作

使用 .tables 可以查看数据库中存在的所有表。

使用 .schema 可以查看表的完整信息。

创建表

-- 创建表 数据库下的为名 xx 的表
-- 也可以直接 xx 表而无需带有数据库名称
CREATE TABLE DatabaseName.TableName (
  -- 字段 字段类型 字段约束
 FieldName DataType Constraints,
  ...
);

删除表

需要注意！删除表时，表中信息将永远丢失。

DROP TABLE DatabaseName.TableName;

插入表

-- 仅插入指定字段的数据 需要确保值顺序与插入字段顺序一致！
INSERT INTO TableName (FieldName, ...) VALUES (Value,...);

-- 插入所有字段数据 需要确保值顺序与字段在表中顺序一致！
INSERT INTO TableName VALUES (value,...);

-- 通过其他表
INSERT INTO TableName1 (FiledName) SELECT FiledName FROM TableName2;

查询表

-- 获取指定字段
SELECT FieldName FROM TableName;

-- 获取所有字段
SELECT * FROM TableName;

-- 字段别名
SELECT FieldName as FN FROM TableName;

---

更新表

UPDATE TableName SET FieldName = Value WHERE ...;

---

删除表

DELETE FROM TableName WHERE ...;

---

Where

SELECT * FROM TableName WHERE ...;

运算符

-- 算术运算符
SELECT 10 + 20; -- 30
SELECT 10 - 20; -- -10
SELECT 10 * 20; -- 200
SELECT 10 / 5; -- 2
SELECT 12 % 5; -- 2

-- 比较运算符
SELECT * FROM TableName WHERE FieldName >  50;  -- 大于 50
SELECT * FROM TableName WHERE FieldName =  50;  -- 等于 50
SELECT * FROM TableName WHERE FieldName == 50;  -- 等于 50
SELECT * FROM TableName WHERE FieldName <  50;  -- 小于 50
SELECT * FROM TableName WHERE FieldName != 50;  -- 不等于 50
SELECT * FROM TableName WHERE FieldName <> 50;  -- 不等于 50
SELECT * FROM TableName WHERE FieldName >= 50;  -- 大于等于 50
SELECT * FROM TableName WHERE FieldName <= 50;  -- 小于等于 50
SELECT * FROM TableName WHERE FieldName !< 50;  -- 不小于 50
SELECT * FROM TableName WHERE FieldName !> 50;  -- 不大于 50

-- 逻辑运算符
SELECT * FROM TableName WHERE FieldName > 50 AND FieldName < 60; -- 大于 50 且 小于 60
SELECT * FROM TableName WHERE FieldName = 50 OR FieldName = 45; -- 等于 50 或 等于 45
SELECT * FROM TableName WHERE FieldName IS NULL; -- 为 NULL
SELECT * FROM TableName WHERE FieldName NOT IS NULL; -- 不为 NULL
SELECT * FROM TableName WHERE FieldName IS NOT NULL; -- 为非 NULL
SELECT * FROM TableName WHERE FieldName LIKE 'K%'; -- K 开头
SELECT * FROM TableName WHERE FieldName LIKE 'K*'; -- K 开头
SELECT * FROM TableName WHERE FieldName IN (1, 2, 3); -- 在给定的 1,2,3 范围内
SELECT * FROM TableName WHERE FieldName NOT IN (1, 2, 3); -- 不在给定的 1,2,3 范围内
SELECT * FROM TableName WHERE FieldName BETWEEN 10 AND 20; -- 在 10 ~ 20 范围内
SELECT * FROM TableName WHERE EXISTS (SELECT FieldName FROM TableName WHERE FieldName < 50); -- 子句查询 查询存在于查询结果中的数据

-- 位运算符
SELECT 60 | 13;   -- 位或
SELECT 60 & 13;   -- 位与
SELECT (~60);     -- 异或
SELECT (60 << 2); -- 左移
SELECT (60 >> 2); -- 右移

LIKE

有两个通配符，% 和 _。

% 代表零个，一个或多个字符，_ 则代表单一字符。

GLOB

用于匹配通配符指定文本。大小写敏感。

遵循 UNIX 语法。

* 零个，一个或多个字符。? 单一字符，[...] 指定范围内字符，[^...] 排除指定范围字符。

SELECT * FROM TableName WHERE FieldName GLOB '[^A]?[BC]*';

LIMIT

-- 限制 10 条 偏移 10 条
SELECT * FROM TableName LIMIT 10 OFFSET 10;

ORDER BY

用于排序。

SELECT FieldName1 FROM TableNAME ORDER BY FieldName1 ASC; -- 以 FieldName1 升序排序
-- DESC 降序排序

GROUP BY

跟随 WHERE 后， ORDER BY 前。

SELECT * FROM TableName WHERE FieldName > 50 GROUP BY FieldName; -- 以 FieldName 为标准分组

Having

过滤分组结果。

跟随 GROUP BY 后，须在 ORDER BY 前。

SELECT F1,F2 FROM TableName WHERE F1 > 20 GROUP BY F2 HAVING COUNT (F2) > 2; -- 过滤为 F2 字段数据重复大于 2 的

DISTINCT

用于消除重复记录。

SELECT DISTINCT F1 FROM TableName;