10.1.SQLite-CSDN博客

本文链接：https://blog.csdn.net/chxii/article/details/148239109

什么是 SQLite？

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。

为什么要用 SQLite？

不需要一个单独的服务器进程或操作的系统（无服务器的）。
SQLite 不需要配置，这意味着不需要安装或管理。
一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB
SQLite 是自给自足的，这意味着不需要任何外部的依赖。
SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。
SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。

SQLite 凭借其体积小、速度快、开源、跨平台、零配置、无服务器等特点，在移动设备、嵌入式系统、桌面应用等众多领域得到了广泛应用

SQLite 局限性

在 SQLite 中，SQL92 不支持的特性如下所示：

特性	描述
RIGHT OUTER JOIN	只实现了 LEFT OUTER JOIN。
FULL OUTER JOIN	只实现了 LEFT OUTER JOIN。
ALTER TABLE	支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。
Trigger 支持	支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。
VIEWs	在 SQLite 中，视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。
GRANT 和 REVOKE	可以应用的唯一的访问权限是底层操作系统的正常文件访问权限。

Windows 系统安装 SQLiteStudio

下载安装包：访问SQLiteStudio 官方网站，点击下载。
运行安装程序：下载完成后，双击运行安装文件。安装过程中会弹出许可证协议，阅读并接受条款后点击 “下一步” 等按钮继续安装，通常会自解压并生成快捷方式到桌面或者开始菜单。
配置语言（可选）：安装完成首次打开 SQLiteStudio 时，会弹出语言选择界面，可勾选 “简体中文” 等所需语言，点击 “OK” 按钮进入主界面
新建数据库：在主界面左侧，选择 “新建” ，选择文件夹路径，在文件名称里输入文件名，然后点击OK，即可开始使用 SQLiteStudio 管理 SQLite 数据库。

SQLite数据类型

SQLite 的数据类型系统与传统关系型数据库（如 MySQL、Oracle）有所不同，它采用动态类型系统（Dynamic Typing），允许将任何类型的数据存储在任意列中，而不严格要求数据类型与列定义的类型匹配。不过，SQLite 仍然支持类型亲和性（Type Affinity），这使得列在存储数据时会优先选择特定类型。

一、基本数据类型

SQLite 支持以下五种存储类（Storage Classes），它们是实际存储在数据库中的数据类型：

NULL
表示空值或缺失值。
INTEGER
有符号整数，根据值的大小自动存储为 1、2、3、4、6 或 8 字节。
示例：123, -456, 0
REAL
浮点数，存储为 8 字节的 IEEE 浮点数。
示例：3.14, -0.001, 1.2e10
TEXT
1. 文本字符串，编码可以是 UTF-8、UTF-16BE 或 UTF-16LE。
2. 示例：'Hello', "世界"
3. TEXT(100)或TEXT不加任何数字，SQLite都不会限制实际存储的文本长度。你可以存储任意长度的文本字符串。
BLOB
二进制大对象，存储为原始二进制数据，不进行类型转换。
示例：图片、音频文件的二进制内容。

二、类型亲和性（Type Affinity）

虽然 SQLite 允许将任何类型的数据存入任何列，但列本身有类型亲和性，即列在存储数据时会优先选择的类型。SQLite 定义了以下五种亲和性：

TEXT
优先存储为文本类型。若存入数值，会自动转换为文本。
示例列定义：name TEXT, description VARCHAR
NUMERIC
优先存储为数值类型（INTEGER 或 REAL）。若存入文本，会尝试转换为数值，失败则存储为 TEXT。
示例列定义：age NUMERIC, price DECIMAL(10,2)
INTEGER
与 NUMERIC 类似，但在 CAST 操作中更倾向于整数。
示例列定义：id INTEGER, count INT
REAL
优先存储为浮点数。
示例列定义：height REAL, score FLOAT
NONE
不偏好任何类型，直接存储原始数据。
示例列定义：data BLOB, raw ANY

三、数据类型转换规则

当数据类型与列的亲和性不匹配时，SQLite 会按以下规则处理：

存入 INTEGER/REAL 列
- 文本值（如 '123'）会转换为数值。
- 空字符串 '' 转换为 0。
- NULL 保持为 NULL。
- 无法转换时保持原值
  当存入的值无法转换为数字（如 'hello'）时，SQLite 会保留值的原始类型（TEXT ）。
存入 TEXT 列
- 数值（如 123）会转换为文本 '123'。
- NULL 保持为 NULL。
存入 BLOB 列
- 直接存储原始数据，不进行转换。

四、列定义与类型映射

在创建表时，列的数据类型名称会被映射到上述五种亲和性之一。例如：

CREATE TABLE example (
    col1 INTEGER,          -- INTEGER 亲和性
    col2 VARCHAR(10),      -- TEXT 亲和性
    col3 DECIMAL(5,2),     -- NUMERIC 亲和性
    col4 BOOLEAN,          -- NUMERIC 亲和性（SQLite 无 BOOLEAN 类型，存为 0/1）
    col5 DATETIME,         -- NUMERIC 亲和性（日期时间通常存为 TEXT 或 INTEGER）
    col6 BLOB              -- NONE 亲和性
);

五、特殊数据类型处理

日期和时间
SQLite 没有专门的 DATE 或 TIME 类型，通常用以下方式存储：
- TEXT：格式为 YYYY-MM-DD HH:MM:SS（如 '2023-01-01 12:00:00'）。
- INTEGER：Unix 时间戳（从 1970-01-01 开始的秒数）。
- REAL：Julian 日数（自公元前 4714 年 11 月 24 日起的天数）。
布尔值
用 INTEGER 存储，0 表示 FALSE，1 表示 TRUE。
JSON
SQLite 3.38.0+ 支持 JSON 函数，但 JSON 数据仍以 TEXT 形式存储。

六、示例演示

创建一个包含不同亲和性列的表，并插入混合类型的数据：

-- 创建表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age NUMERIC,
    is_student INTEGER,  -- 布尔值用 INTEGER 存储
    created_at TEXT      -- 日期用 TEXT 存储
);

-- 插入数据（注意类型混合）
INSERT INTO users (name, age, is_student, created_at)
VALUES 
('Alice', '25', 1, '2023-01-01'),  -- age 文本转数值，is_student 布尔转整数
('Bob', 30.5, false, CURRENT_TIMESTAMP);  -- age 浮点数，is_student 转整数

-- 查询数据
SELECT * FROM users;

id  name   age   is_student  created_at
-----------------------------------------
1   Alice  25    1           2023-01-01
2   Bob    30.5  0           2023-05-26 12:00:00  -- CURRENT_TIMESTAMP 自动生成