SQLite 数据类型
SQLite 使用动态类型系统，允许在创建表时灵活定义数据类型。在 SQLite 中，数据类型与数据本身相关，而不是与列的容器相关，这使得 SQLite 更加灵活。
SQLite 存储类
SQLite 的每个值都有一个存储类，决定它的存储方式。常见的存储类如下：
| 存储类 | 描述 |
|---|---|
| NULL | 值为 NULL |
| INTEGER | 带符号整数，存储为 1、2、3、4、6 或 8 字节 |
| REAL | 浮点数，8 字节 IEEE 浮动点表示 |
| TEXT | 文本字符串，使用 UTF-8、UTF-16 编码存储 |
| BLOB | 二进制数据，按原始数据存储 |
说明：
- INTEGER 存储类支持不同长度的整数类型。
- REAL 存储类使用 IEEE 标准表示浮点数。
SQLite 亲和类型 (Affinity)
SQLite 支持列的亲和类型，即存储时根据数据类型的亲和关系进行转换。SQLite 支持五种亲和类型：
| 亲和类型 | 描述 |
|---|---|
| TEXT | 将数值类型数据转换为文本格式存储。 |
| INTEGER | 类似 NUMERIC 类型，在 CAST 表达式时有区别。 |
| NUMERIC | 如果插入文本数据并且可以转换为 INTEGER 或 REAL 类型，SQLite 会转换；如果无法转换，保留为 TEXT 类型。 |
| REAL | 类似 NUMERIC，但不会将 “30000.0” 转换为 INTEGER。 |
| NONE | 不进行转换，数据直接按原类型存储。 |
常见 SQLite 数据类型与亲和类型
在创建 SQLite 表时，常见的数据类型和其亲和类型如下：
| 数据类型 | 亲和类型 |
|---|---|
| INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 | INTEGER |
| CHARACTER(n), VARCHAR(n), VARYING CHARACTER(n), NCHAR(n), NVARCHAR(n), TEXT, CLOB | TEXT |
| BLOB | BLOB |
| REAL, DOUBLE, DOUBLE PRECISION, FLOAT | REAL |
| NUMERIC, DECIMAL(p,s), BOOLEAN, DATE, DATETIME | NUMERIC |
Boolean 数据类型
SQLite 没有独立的 BOOLEAN 存储类。布尔值是通过 整数值 0（表示 false）和 1（表示 true）存储。
Date 和 Time 数据类型
SQLite 没有专门的日期和时间存储类，日期和时间可以存储为以下三种格式之一：
| 存储类 | 日期格式 |
|---|---|
| TEXT | 格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的字符串 |
| REAL | 距公元前 4714 年 11 月 24 日正午格林尼治时间的天数 |
| INTEGER | 从 1970-01-01 00:00:00 UTC 起的秒数 |
说明：
- TEXT 格式：用于存储可读性强的日期和时间。
- REAL 格式：存储为距 4714 年的天数，适合数学计算。
- INTEGER 格式：存储为 Unix 时间戳（自 1970 年以来的秒数），适合快速比较和查询日期。
SQLite 数据库操作
SQLite 使用 sqlite3 命令来创建和操作数据库。无需特殊权限即可创建和操作数据库文件。
创建数据库
语法
$ sqlite3 DatabaseName.db
创建数据库时，DatabaseName.db 是数据库的文件名。如果该文件不存在，SQLite 会创建它。
还可以使用 .open 命令来打开或创建数据库：
sqlite>.open myDatabase.db
如果数据库文件已存在，则直接打开它。
创建新的数据库实例
例如，创建一个新的数据库 myShop.db：
$ sqlite3 myShop.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
使用 .databases 命令查看已创建的数据库：
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/myShop.db
退出 SQLite 提示符：
sqlite>.quit
$
导出与恢复数据库
SQLite 提供了 .dump 命令，可以将整个数据库导出为 SQL 脚本文件。
导出数据库：
$sqlite3 myShop.db .dump > myShop.sql
恢复数据库：
$sqlite3 myShop.db < myShop.sql
附加数据库 (ATTACH DATABASE)
多个数据库可以在同一连接中操作。使用 ATTACH DATABASE 语句附加一个数据库。
语法
ATTACH DATABASE 'file_name' AS database_name;
示例
附加一个名为 inventory.db 的数据库：
sqlite> ATTACH DATABASE 'inventory.db' AS 'inventory';
使用 .database 命令查看附加的数据库：
sqlite> .database
seq name file
--- --------------- ----------------------
0 main /home/sqlite/myShop.db
2 inventory /home/sqlite/inventory.db
分离数据库 (DETACH DATABASE)
如果不再需要附加的数据库，可以使用 DETACH DATABASE 命令将其分离。
语法
DETACH DATABASE 'Alias-Name';
示例
分离名为 inventory 的数据库：
sqlite> DETACH DATABASE 'inventory';
再次使用 .databases 查看附加数据库：
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/myShop.db
完整使用案例
以下是一个完整的 SQLite 操作示例，展示了如何创建表、插入数据、附加数据库以及分离数据库。
示例代码
$ sqlite3 myShop.db
-- 创建 PRODUCTS 表
sqlite> CREATE TABLE PRODUCTS (
ID INTEGER PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
PRICE REAL NOT NULL,
STOCK INTEGER DEFAULT 0
);
-- 插入数据
sqlite> INSERT INTO PRODUCTS (NAME, PRICE, STOCK) VALUES ('Laptop', 800.00, 10), ('Smartphone', 400.00, 30);
-- 创建 ORDERS 表
sqlite> CREATE TABLE ORDERS (
ORDER_ID INTEGER PRIMARY KEY NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
QUANTITY INTEGER NOT NULL,
ORDER_DATE TEXT,
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
);
-- 插入订单数据
sqlite> INSERT INTO ORDERS (PRODUCT_ID, QUANTITY, ORDER_DATE) VALUES (1, 2, '2025-04-18'), (2, 1, '2025-04-19');
-- 附加一个名为 inventory.db 的数据库
sqlite> ATTACH DATABASE 'inventory.db' AS 'inventory';
-- 查询主数据库的产品数据
sqlite> SELECT * FROM PRODUCTS;
-- 查询附加数据库的订单数据
sqlite> SELECT * FROM inventory.ORDERS;
-- 分离 inventory 数据库
sqlite> DETACH DATABASE 'inventory';
sqlite> .quit