SQLite学习笔记（2024年5月7日更新）

提示：本文用于笔者记录学习内容，其中大量引用各位大神们的现成内容，在此表示尊重和感谢，如引用内容不当请联系我删除。再次感谢各位的无私支持！

---

前言

本文主要用于笔者记录学习SQLite下载、安装、应用方面的内容。

---

一、SQLite下载安装

1.SQLite下载

通过SQLite官网下载页面，找到“Precompiled Binaries for Windows（Windows预编译二进制文件）”区，下载sqlite-dll-win-x64-3450200.zip、sqlite-tools-win-x64-3450200.zip两个文件（适用于64位系统），并解压缩到名为SQLite的同一文件夹中。

Windows预编译二进制文件区共有3个文件，sqlite-dll-win-x86-3450200.zip（适用于32位系统）、sqlite-dll-win-x64-3450200.zip（适用于64位系统）、sqlite-tools-win-x64-3450200.zip（一组用于管理SQLite数据库文件的命令行工具，包括命令行shell程序、sqldiff.exe程序和sqlite3_analyzer.exe程序。适用于64位。）

sqlite-dll-win-x64-3450200.zip解压缩后生成2个文件，sqlite3.def、sqlite3.dll；
sqlite-tools-win-x64-3450200.zip解压缩后生成3个文件，sqldiff.exe、sqlite3.exe、sqlite3_analyzer.exe。

官网下载地址：https://www.sqlite.org/download.html

2.SQLite安装

打开“编辑系统环境变量”，按提示要求添加sqlite文件夹所在路径到PATH环境变量。如下图所示：

最后，在cmd命令中输入sqlite3，显示sqlite版本，就表示安装成功了

接下来就可以使用SQLite命令对SQLite数据库进行操作了。

二、SQLiteStudio下载安装

SQLiteStudio是一款SQLite数据库的可视化工具软件，方便使用者通过友好的用户界面进行操作，具体下载安装步骤如下：

1.SQLiteStudio下载

通过官网下载SQLiteStudio3.4.4发布版。
官网下载地址：https://sqlitestudio.pl/

下载后的文件为SQLiteStudio-3.4.4-windows-x64-installer

2.SQLiteStudio安装

2.1 打开安装程序，点击“next”；

2.2 选择“I accept the agreement（我接受协议）”，点击“next”；

2.3 “Associate SQLite file extensions with SQLiteStudio（关联SQLite文件扩展名与SQLiteStudio）”，选择“YES”，点击“next”；

2.4 选择安装路径，点击“next”；

2.5 开始安装，点击“next”；



安装完成，可以正常使用。

---

三、SQLiteStudio使用介绍

1.概况

1.1 界面功能说明

进入SQLiteStudio后，菜单栏主要包括Database、Structure、View、Tools、Help五项功能，下面介绍其具体功能：

1.1.1 Database菜单包括以下功能：

Connect to the database： 用于连接打开已有数据库。
Disconnect from the database： 用于关闭断开已有数据库。
Add a database： 用于创建新数据库或打开已有数据库。
Edit the database： 用于编辑已有数据库。
Remove the database： 用于删除已有数据库。
Export the database： 用于导出数据库。
Vacuum： 用于释放空闲占用。
Integrity check： 用于检查完整性。
Refresh selected database schema： 用于刷新已选数据库结构。
Refresh all database schemas： 用于刷新全部数据库结构。
Quit： 用于退出关闭SQLiteStudio软件。

1.1.2 Structure菜单包括以下功能：

Create a table： 用于在已选数据库中新建数据表。
Edit the table： 用于编辑已选数据表。
Delete the table： 用于删除已选数据表。
Create an index： 用于在已选数据表中创建索引。
Edit the index： 用于编辑已选数据表索引。
Delete the index： 用于删除已选数据表索引。
Create a trigger： 用于创建触发器。
Edit the trigger： 用于编辑触发器。
Delete the trigger： 用于删除触发器。
Create a view： 用于创建视图。
Edit the view： 用于编辑视图。
Delete the view： 用于删除视图。

1.1.3 View菜单包括以下功能：

通过点选可在界面中显示或关闭Database（数据库）、Status（状态栏）、Database toolbar（数据库工具栏）、Structure toolbar（结构工具栏）、Tools（工具）、Window list（窗口列表）、View toolbar（视图工具栏） 等窗口栏目。
打开多个功能窗口后，可通过选择Tile windows（平铺窗口）、Tile windows horizontally（水平排列窗口）、Tile windows vertically （垂直排列窗口）、Cascade windows（层叠窗口） 选择不同窗口布局。
根据窗口活动状态，可选择Close current windows（关闭当前窗口）、Close all windows（关闭全部窗口）、Close other windows（关闭其他窗口）、Close windows on the left（关闭左窗口）、Close windows on the right（关闭右窗口），还可以选择Restore recently closed windows（还原最近关闭窗口）、Rename selected window（重命名选中窗口）。
windows list： 窗口列表

1.1.4 Tools菜单包括以下功能：

Open SQL editor： 用于打开SQL编辑器。
Open DDL history： 用于打开DDL历史。
Open SQL functions editor： 用于打开SQL函数编辑器。
Open code snippets editor： 用于打开代码片段编辑器。
Open collations editor： 用于打开字符序编辑器。
Open extension manager： 用于打开扩展管理器。
Import： 用于导入。
Export： 用于导出。
Open configuration dialog： 用于打开配置对话框。

1.1.5 Help菜单包括以下功能：

user manual： 用户手册
SQLite documentation： SQLite文档
Open home page： 访问主页
Check for updates： 检查更新
Report a bug： 提报bug
Propose a new feature： 提议新增功能
Bugs and feature requests： bug与功能请求
Licenses： 许可协议
Donate： 捐赠
About： 关于

1.2 设置

1.2.1 将SQLiteStudio转换为中文模式

双击打开SQLiteStudio->点选菜单栏“tool”选项->选择“open configuration dialog”选项

点击“look&feel”->在“language”下选择“简体中文”->关闭SQLiteStudio后重启即可生效。

四、SQLite数据类型

BIGINT
BLOB
BOOLEAN
CHAR
DATE
DATETIME
DECIMAL
DOUBLE
INTEGER
INT
NONE
NUMERIC
REAL
STRING
TEXT
TIME
VARCHAR

大多数 SQL 数据库引擎 （据我们所知，除 SQLite 之外的所有 SQL 数据库引擎）都使用严格的静态类型。使用静态类型，值的类型便由它的容器 – 存储值的特定的列 – 来决定。
SQLite 使用更通用的动态类型系统。在 SQLit 中，值的数据类型与值本身相关，而不是与它的容器。SQLite 的动态类型系统与其它数据库引擎的常用静态类型系统是向后兼容的，在这个意义上，工作在静态类型数据库上的 SQL 语句应该以同样的方式工作在 SQLite 中。然而，SQLite 中的动态类型允许它做传统的严格类型的数据库所不能做的事。

1.0存储类型与数据类型
存储在 SQLite 数据库中的每个值（或是由数据库引擎所操作的值）都有一个以下的存储类型：

NULL. 值是空值。
INTEGER. 值是有符号整数，根据值的大小以1，2，3，4，6 或8字节存储。
REAL. 值是浮点数，以8字节 IEEE 浮点数存储。
TEXT. 值是文本字符串，使用数据库编码（UTF-8, UTF-16BE 或 UTF-16LE）进行存储。
BLOB. 值是一个数据块，按它的输入原样存储。
注意，存储类型比数据类型更笼统。以 INTEGER 存储类型为例，它包括6种不同的长度不等的整数类型，这在磁盘上是不同的。但是只要 INTEGER 值从磁盘读取到内存进行处理，它们就被转换为更为一般的数据类型（8字节有符号整型）。因此在一般情况下，“存储类型” 与 “数据类型” 没什么差别，这两个术语可以互换使用。

SQLite 版本3数据库中的任何列，除了整型主键列，都可用于存储任何存储类型的值。

SQL 语句中的任何值，无论它们是嵌入到 SQL 语句中的字面量还是绑定到预编译 SQL 语句中的参数，都有一个隐含的存储类型。在下述情况下，数据库引擎会在执行查询时在数值存储类型（INTEGER 和 REAL）和 TEXT 之间进行转换。
1.1 布尔类型
SQLite 并没有单独的布尔存储类型，而是将布尔值存储为整数 0 (false) 和 1 (true)。

1.2 日期和时间类型
SQLite 没有另外的存储类型来存储日期和时间。SQLite 的内置的日期和时间函数能够将日期和时间存为 TEXT、REAL 或 INTEGER 值：

TEXT ISO8601 字符串 (“YYYY-MM-DD HH:MM:SS.SSS”)。
REAL 儒略日数 (Julian Day Numbers)，按照前公历，自格林威治时间公元前4714年11月24日中午以来的天数。
INTEGER Unix 时间，自 1970-01-01 00:00:00 UTC 以来的秒数。
应用可以选择这些格式中的任一种存储日期和时间，并使用内置的日期和时间函数在这些格式间自由转换。
2.0 类型亲和性
为了最大限度地提高 SQLite 和其它数据库引擎之间的兼容性，SQLite 支持列的“类型亲和性”的概念。列的类型亲和性是指数据存储于该列的推荐类型。这里重要的思想是类型是推荐的，而不是必须的。任何列仍可以存储任何类型的数据。这只是让一些列有选择性地优先使用某种存储类型。一个列的首选存储类型被称为它的“亲和性”。

每个 SQLite 3 数据库中的列都归于以下的类型亲和性中的一种：

TEXT
NUMERIC
INTEGER
REAL
NONE
一个具有 TEXT 亲和性的列使用存储类型 NULL、 TEXT 或 BLOB 存储所有数据。如果数值数据被插入到一个具有 TEXT 亲和性的列，则数据在存储前被转换为文本形式。
数值亲和性的列可能包含了使用所有五个存储类的值。当插入文本数据到数值列时，该文本的存储类型被转换成整型或实数（按优先级排序）如果这种转换是无损或可逆的的话。对于文本与实数类型之间的转换，如果前15个重要十进制数字被保留的话，SQLite认为这种转换是无损并可逆的。如果文本不能无损地转换成整型或实数，那这个值将以文本类型存储。不要试图转换NULL或BLOB值。

一个字符串可能看上去像带有小数点和/或指数符的浮点文字，但只要这个值可以用一个整型表示，数值亲和性就会把它转换成一个整型。因此，字符串‘3.0e+5’以整型300000，而不是浮点值30000.0的形式存储在一个数值亲和性的列里。
一个使用整型亲和性的列与具有数值亲和性的列表现一致。只是在CAST表达式里，它们之间的区别体现得明显。

除了强制将整型值转换成浮点表示外，一个具有实数亲和性的列与具有数值亲和性的列表现一致（作为一个内部的优化，为了少占用空间，无小数部分且存储在实数亲和性列上的小浮点值以整型形式写到磁盘，读出时自动转换回浮点值。在SQL级别，这种优化是完全不可见的，并且只能通过检查数据库文件的原始比特检测到）。

一个具有NONE亲和性的列不能从一种存储类型转换成另一种，也不要试图强制对它进行转换。
2.1 列亲和性测定
列的亲和性是由它的声明类型决定的，按照以下顺序所示的规则：

1. 如果声明类型包含字符串“INT”，那它被指定为整型亲和性；

2. 如果列的声明类型包含任何“CHAR”、“CLOB”或“TEXT”字符串，那么该列具有文本亲和性。注意：VARCHAR类型包含“CHAR”并且被指定为文本亲和性；

3. 如果列的声明类型包含“BLOB”或者没有指定类型，那这列具有NONE亲和性；

4. 如果列的声明类型包含任何“REAL”、“FLOA”或“DOUB”字符串，则该列具有实数亲和性；

5. 否则，它将具有数值亲和性。

注意：判定列亲和性规则的顺序是很重要的。一个具有“CHARINT”声明类型的列将匹配规则1和2，但是规则1优先所有该列具有整型亲和性。
2.2 亲和性名字实例
下表显示了有多少从更传统的SQL实现的常用数据类型名，通过上一节介绍的五个规则被转换成各种亲和性类型。这张表只显示了SQLite可接受的一小部分数据类型名。注意：跟在类型名后，括号内数值参数（如：VARCHAR（255））将被SQLite忽略 - SQLite不对字符串、BLOBs或数值的长度强加任何限制（除了大型全局SQLITE_MAX_LENGTH限制）。

Example Typenames From The
CREATE TABLE Statement
or CAST Expression Resulting Affinity Rule Used To Determine Affinity
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8 INTEGER 1
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB TEXT 2
BLOB
no datatype specified NONE 3
REAL
DOUBLE
DOUBLE PRECISION
FLOAT REAL 4
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME NUMERIC 5

注意： 因为在“POINT”末尾的“INT”，一个“ FLOATING POINT”声明类型 会被赋予整型亲和性，而不是实数亲和性。而且“STRING”声明类型具有数值亲和性，而不是文本亲和性。
2.3 列亲和性行为实例
以下SQL演示当有值插入到一张表时，SQLite如何使用列亲和性实现类型转换的：

CREATE TABLE t1(
t TEXT, – text affinity by rule 2
nu NUMERIC, – numeric affinity by rule 5
i INTEGER, – integer affinity by rule 1
r REAL, – real affinity by rule 4
no BLOB – no affinity by rule 3
);

– Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.（值分别以文本、整型、整型、实数、文本形式存储）
INSERT INTO t1 VALUES(‘500.0’, ‘500.0’, ‘500.0’, ‘500.0’, ‘500.0’);
SELECT typeof(t), typeof(nu), typeof(i), typeof®, typeof(no) FROM t1;
text|integer|integer|real|text

– Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof®, typeof(no) FROM t1;
text|integer|integer|real|real

– Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof®, typeof(no) FROM t1;
text|integer|integer|real|integer

– BLOBs are always stored as BLOBs regardless of column affinity. DELETE FROM t1;
INSERT INTO t1 VALUES(x’0500’, x’0500’, x’0500’, x’0500’, x’0500’);
SELECT typeof(t), typeof(nu), typeof(i), typeof®, typeof(no) FROM t1;
blob|blob|blob|blob|blob

– NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof®, typeof(no) FROM t1;
null|null|null|null|null
3.0 比较表达式
同标准SQL一样，SQLite 3支持如下的比较操作符：“=”, “==”, “<”, “<=”, “>”, “>=”, “!=”, “<>”, “IN”, “NOT IN”, “BETWEEN”, “IS”, 以及 “IS NOT”。

3.1 排序规则
比较的结果与操作数的存储类型有关，同时依据以下的规则：

NULL值小于其他任何值（包括另外一个NULL）
INTEGER或REAL小于TEXT，BLOB值；若两个INTEGER(或者REAL)比较，则按照实际的数值进行。
TEXT小于BLOB，若两个TEXT比较，结果则由适当的整理顺序决定
若两个BLOD比较，与memcmp()的结果一致
3.2 操作数进行比较时的相似性
在进行值的比较之前，SQLite会尝试在存储类INTEGER、REAL和/或TEXT之间进行值的转换。在比较之前尝不尝试进行转换完全取决于操作数的相似性。操作数相似性的判定规则如下：

只是对一个列中的值进行引用的表达式同被引用的列具有完全相同的相似性。注意，如果X、Y.Z代表的是列的名称，那么+X和+Y.Z可以认为是为了判定其相似性的表达式。

"CAST(expr AS type)"所表示的表达式同类型定义为"type"的列具有完全相同的相似性。

其它情况下的表达式具有NONE相似性。
3.3 比较前的类型转换
“应用相似性”（“apply affinity”）的意思是，当且仅当所涉及的转换是无损且可逆的情况下，将一个操作数转换为某特定的存储类型。在进行比较之前对比较运算符的操作数应用相似性的规则如下按顺序所示：

如果其中的一个操作数具有INTEGER、REAL或者NUMERIC相似性而另外一个操作数具有TEXT或者NONE相似性，那么就要对这另外一个操作数应用NUMERIC 相似性。

如果其中的一个操作数具有TEXT相似性而另外一个具有NONE相似性，那么就要对这另外一个操作数应用TEXT相似性。

其它情况下不会应用任何相似性，两个操作数按照各自的原样进行比较。

将表达式"a BETWEEN b AND c"看作两个单独的二元比较运算"a >= b AND a <= c"，即使这么一来，可能会造成其中的a在两次比较中会被应用不同的相似性，也要这么处理。Datatype conversions in comparisons of the form 在"x IN (SELECT y …)“这种形式的比较中，数据类型的转换完全同"x=y"一样进行处理。表达式"a IN (x, y, z, …)” 同"a = +x OR a = +y OR a = +z OR …“等价。换句话说，IN运算符右侧的值(本例中就是"x”, “y”, and “z”)被看作是无相似性的，即使它们凑巧是某列的值或者是CAST表达式。
4.0 操作符
所有的数学运算符(+, -, *, /, %, <<, >>, &, and |)在展开前会将两个操作数放入 NUMERIC 储存类。即使这个过程是有损和不可逆转的。一个 NULL 操作数在数学运算符上产生一个 NULL 结果。在数算运算符上的操作数不被视为数字，NULL 并不会被转为0或0.0。
5.0 排序, 分组 和 组合查询
当查询结果使用 ORDER BY 子句排序时, 存储类型的NULL空值是排在第一位的, 其次是INTEGER和散布在数字顺序的REAL数据, 其次是按照核对序列顺序的TEXT值, 最后为memcmp() order 的BLOB值. 排序之前不会出现任何存储类型转换.

当使用GROUP BY 子句分组时不同类型的值被认为是不同的数据, 除了INTEGER 和 REAL 值如果他们数值相等则被认为是相同的的数据. 没有任何亲和性适用于GROUP BY 子句结果的任意值.

组合查询使用 UNION, INTERSECT 和 EXCEPT 在数据之间执行隐式的比较. 没有任何亲和性适用于与UNION, INTERSECT, 或者 EXCEPT关联的隐式比较的运算数 - 数据的比较就像这样.
6.0 整理序列
当 SQLite 比较两个字符串时，它使用一个整理序列或整理函数（一物两表）来决定当两个字符串相同时，哪个字符串值更高。SQLite 拥有三个内建整理函数：BINARY, NOCASE, 和 RTRIM。

BINARY - 使用 memcmp() 比较字符串，无视文本编码。
NOCASE - 与二进制比较相同，除了 ASCII 的26个大写字母在比较前将会转为其小写形势。注意，只有 ASCII 字符会大小写转化。 由于表大小的需求，SQLite 并不会尝试 UTF 大小写转化。
RTRIM - 与二进制比较相同，除了尾部空格符将被忽略。
应用可以通过 sqlite3_create_collation() 接口注册额外的整理函数。
6.1 设定SQL中的排列顺序
每个表中的每一个列都具有一个相关的排序函数。如果没有显式地定义排序函数，那么，就会缺省使用BINARY作为排序函数。列定义中的COLLATE子句可为列定义一个可选的排序函数。

对于二元比较运算符(=, <, >, <=, >=, !=, IS, and IS NOT)来说，判定到底使用哪个排序函数的规则按顺序如下所列：

如果两个操作数中有任意一个操作数具有使用后缀COLLATE运算符显式定义的排序函数，那么就会用该函数进行比较，如果两个操作数都有的情况下，优先使用左操作数的排序函数。

如果两个操作数中任意一个操作数是一个列，那么就会使用该列的排序函数进行比较，但在两个操作数都是列的情况下，优先使用左操作数对应的列的排序函数。为了达到这句话的目的，列名前带有1个或多个一元运算符"+"的，仍然按原列名处理。

其它情况下，采用BINARY排序函数进行比较。
比较运算中的操作数，如果在它的任何子表达式中使用了后缀 COLLATE运算符，就可以认为是具有显式的排序函数（上文中的规则1）。 再者，如果在比较表达式中的任何地方使用了 COLLATE运算符，那么该运算符所定义的排序函数就会用于字符串的比较，而无论在表达式中出现了表中的哪一列。如果在比较中的任何地方出现了两个或多个 COLLATE运算符子表达式，无论在表达式中嵌入得多深，也无论表达式是怎么使用括号的，都会使用出现在最左侧的显式排序函数。
表达式"x BETWEEN y and z"从逻辑上讲，同"x >= y AND x <= z"这两个比较运算完全等价，在使用排序函数时它们俩要象两个本来就是独立的比较运算一样进行处理。在判定排列顺序时，表达式"x IN (SELECT y …)"处理方式完全同表达式"x = y"一样，形如"x IN (y, z, …)"的表达式，排列顺序完全同X的排列顺序一样。

作为 SELECT语句的一个部分，ORDER BY子句中排序条件也可以通过使用COLLATE运算符设定排列顺序，如果设定了排序时就要按照设定的排序函数进行排序。否则，如果ORDER BY子句使用的排序表达式是一个列，那么该列的排列顺序就用于判定排列顺序。如果该排序表达式不是列并且也无COLLATE子句，就会使用BINARY排列顺序。

---

总结

1.通过学习各位大神的内容和亲身实践，我对SQLite下载安装更加熟悉，再次感谢。
2.后续将根据学习进展进一步完善相关内容。