第五章 SQLite数据库：2、SQLite 数据类型及数据库、附加库及表等基本操作-CSDN博客

本文链接：https://blog.csdn.net/qq_62848032/article/details/147259758

SQLite 数据类型

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