SQLite 与 MySQL、PostgreSQL 的类型系统不太相同。后两者使用静态类型，一个列具有唯一的数据类型；而 SQLite 则使用“灵活类型”（Flexible Type），一个值的类型由值的类型确定，而非列的类型确定。
SQLite 不仅使用了灵活类型，准确来说，SQLite 不使用“数据类型（datatype）”而使用“存储类别（storage class）”来表述一个值。
SQLite 有以下五个存储类别：
- NULL
-
这个值是一个空（null）值
- INTEGER
-
这个值是一个有符号整数，依照值的大小，以 0/1/2/3/4/6/8 字节存储
- REAL
-
这个值是一个浮点数，使用 8 字节 IEEE 浮点数存储
- TEXT
-
这个值是一个文本字符串，使用数据库的编码方式（UTF-8、UTF-16BE、UTF-16LE）存储
- BLOB
-
这个值是一个二进制串，将以值输入的状态存储
-
在定义一个列的时候，列的存储类别是可选的。也就是说，在定义一个列的时候，可以只给出列名。
-
若定义列时提供了存储类别，那么后者可以是任意文本。SQLite 会尝试基于后者的文本缩减值的偏好的存储类别，但这个类别是建议性的，而非强制性的。偏好存储类别被称为“列亲和类别”。
-
当输入值时，SQLite 会尝试将数据转换为“列亲和类别”。若失败，SQLite 会以原始类型存储这个数据。
-
常见的对应关系
列存储类别 该列支持的类别 INTEGER
INTEGER, REAL, TEXT, BLOB
REAL
REAL, TEXT, BLOB
TEXT
TEXT, BLOB
BLOB
INTEGER, REAL, TEXT, BLOB
-
若一个数字字面量不被引号包裹、不含小数点、不含指数，则推定为 INTEGER
-
若一个数字字面量被引号包裹，则推定为 TEXT
-
若一个数字字面量不被引号包裹，但具有小数点或指数，则推定为 REAL
-
若一个数字字面量具有以下形式 x’ABCD' 或 x’abcd'（位数必须为 4 的整数倍），则推定为 BLOB
字面量的类别的推定，可以用 typeof() 函数显示
SELECT
typeof(100),
typeof('100'),
typeof(100.0),
typeof(100e2),
typeof(x'10001234'),
typeof(NULL);-
NULL 具有最低的等级，排在最后，NULL 之间无排序
-
稍高一些的是 INTEGER 和 REAL，它们将作为数值比较大小
-
再高一些的是 TEXT，它们将作为文本排序
-
最高的是 BLOB，它们通过 C 函数
memcmp()比较
因此，在使用 ORDER BY 排序时，SQLite 会先将数据归类为四类：NULL、INTEGER/REAL、TEXT、BLOB，然后再再每类中进行排序。
SQLite 中没有日期和时间特定的存储类别，SQLite 使用 TEXT、REAL、INTEGER，并搭配特定的函数来存储/表示日期和时间值。
-
若使用
TEXT来表示/存储日期和时间值，
那么字符串的格式为 ISO8601 格式的一个子集：// 完整形式 YYYY-MM-DD HH:MM:SS.SSS // 仅日期 YYY-MM-DD // 仅时间（读取时会当作 2000-01-01 当日的时间点） HH:MM:SS // 带小数的时间（读取时会当作 2000-01-01 当日的时间点） HH:MM:SS.SSS比如
2023-01-02 12:06:55.123 -
若使用
REAL来表示/存储日期时间值
那么实际的值是自公元前 4714 年 11 月 24 日午夜记为 0 时刻的儒略历 天数（含小数）
比如 2023-01-02 12:06:55.123 会被记为2459947.00480466 -
若使用
INTEGER来表示/存储日期和时间值
那么实际的值是自 1970-01-01 00:00:00 UTC 起的 整数秒数
比如 2023-01-02 12:06:55.123 会被记为1672661215（忽略了小数）
SQLite 中共有 6 个与日期时间相关的函数
-- 以 YYYY-MM-DD 的形式转换时间值
data(<时间值>, <修饰符>, <修饰符>...)
-- 以 HH:MM:SS 的形式转换时间值
time(<时间值>, <修饰符>, <修饰符>...)
-- 以 YYYY-MM-DD HH:MM:SS 的形式转换时间值
datetime(<时间值>, <修饰符>, <修饰符>...)
-- 将时间值转换为儒略历计时
julianday(<时间值>, <修饰符>, <修饰符>...)
-- 将时间值转换为 UNIX 计时
unixepoch(<时间值>, <修饰符>, <修饰符>...)
-- 以指定的格式格式化时间值
strftime(<格式>, <时间值>, <修饰符>, <修饰符>...)|
Important
|
上面函数的
若要将数值强制以 UNIX 计时处理，需要使用修饰符 |
|
Important
|
默认情况下，SQLite 始终使用 UTC 作为时区。 若要将 UTC 转换为本地时，需要使用修饰符 |
|
Note
|
具体格式化表和修饰符，参见 Date And Time Functions |
创建一个新表
CREATE [TEMPORARY | TEMP] TABLE [IF NOT EXIST] [<数据库名>.]<表名> (
<列名1> [<存储类别1>] [<约束>...],
...
[<表约束>...]
) [WITHOUT ROWID];-
首先，表名不可以以
sqlite_开头，因为这个是 SQLite 预留的内部使用的表。 -
其次，IF NOT EXIST 会检测是否有同名的表，若有，则不创建；
若不指定 IF NOT EXIST，且存在同名的表，则 SQLite 会返回错误。 -
若指定了
TEMPORARY或者TEMP，则表示创建的表是一个“临时的表”，
存放在内存中，一个名为temp的数据库中。 -
可以额外指定数据库名，这样这个表就可以存放在指定的数据库下。若不指定则存放在
main数据库中。
若指定，则数据库名必须为main、temp或任何一个附加的数据库的名称。 -
之后是列的定义，一个列包含一个列名，以及可选的一个存储类型、一系列的列约束。
列定义的结尾以逗号,结尾。
SQLite 支持的列约束包括PRIMARY KEY、UNIQUE、NOT NULL和CHECK -
一个表定义的最后是表约束，表约束包括
PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK -
最后，还有一个
WITHOUT ROWID选项，
默认情况下，SQLite 会为每个表添加一个隐藏的列，
它的类型为 64 位有符号整型，用来在当前的表中唯一确定一个行。
通常这个列被称为rowid、oid、rowid。
而具有rowid列的表，有时候又被称为rowid表。
指定了WITHOUT ROWID后，会关闭这个列。
除了直接创建一个表，还有一种从 SELECT 的结果创建一个表的方法
CREATE [TEMPORARY | TEMP] TABLE [IF NOT EXIST] [<数据库名>.]<表名> AS <SELECT 语句>;我们要创建一个联系人数据库，这个联系人数据库要包含联系人的姓、名、电邮地址、电话号码，且每个电邮地址和电话号码是互不相同的。
而且，我们还需要让每个联系人属于零个或多个组，且每个组可以含有零个或多个联系人。
为了管理方便，这里我们可以创建三个表。
-
contacts 表，这个表包含的列为 联系人 id、姓、名、电邮地址、电话
-
groups 表，这个表包含的列为 组 id、组名
-
contact_group 表，这个表包含两个外键，分别是 联系人 id 和 组 id
这样我们就建立了两个独立的表，以及一个关联表。
-- 创建 contacts 表
-- 这个表记录了联系人的信息
CREATE TABLE contacts (
-- 当一个列的存储类别为 INTEGER 且约束为 PRIMARY KEY 时，它其实是一个 rowid 列的别名
contact_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT UNIQUE,
phone TEXT UNIQUE,
-- 这里设置了一个约束，其要求 first_name 和 last_name 不能同时为空
CHECK ((first_name IS NOT NULL) OR (last_name IS NOT NULL))
-- 这里设置了一个约束，其要求 email 和 phone 不能同时为空
CHECK ((email IS NOT NULL) OR (phone IS NOT NULL))
);
-- 创建 groups 表
-- 就是记录了一个组名
CREATE TABLE groups (
group_id INTEGER PRIMARY KEY,
-- 组名不应为空，且不应相同
name TEXT NOT NULL UNIQUE
);
-- 最后是这个关联表
-- 由于 联系人和群组是 M:N 的对应关系
-- 因此不好直接使用包含关系
-- 另列一张表会比较合适
CREATE TABLE contact_group (
link_id INTEGER PRIMARY KEY,
contact_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
-- 这里要求 contact_id 和 group_id 的组合不可以相同
-- 保证了不会记录多个相同的关联
UNIQUE (contact_id, group_id),
-- 设置了两个外键
-- 将本表中的 contact_id 设置为外键
FOREIGN KEY (contact_id)
-- 其参考的外键为 contacts 表的 contact_id 这个列
REFERENCES contacts (contact_id)
-- 若主表的这个值被删除时，删除子表中所有相关的条目
ON DELETE CASCADE
-- 若主表的这个值被更新时，禁止更新主表的这个值（除非本表中所有相关的条目均被移除）
ON UPDATE NO ACTION,
FOREIGN KEY (group_id)
REFERENCES groups (group_id)
ON DELETE CASCADE
ON UPDATE NO ACTION
);让我们尝试向其中插入一些数据
INSERT INTO
contacts (first_name, last_name, email, phone)
VALUES
('Alice', 'Joe', '[email protected]', NULL),
('Bob', 'Turing', NULL, '123-456789');
INSERT INTO
groups (name)
VALUES
('family'),
('colleague');
INSERT INTO
contact_group (contact_id, group_id)
VALUES
(
(SELECT contact_id FROM contacts WHERE last_name IS 'Joe'),
(SELECT group_id FROM groups WHERE name IS 'family')
),
(
(SELECT contact_id FROM contacts WHERE first_name IS 'Bob'),
(SELECT group_id FROM groups WHERE name IS 'colleague')
);SQLite 支持的 ALTER TABLE 语句的功能仅为 SQL 标准中的一小部分。
SQLite 的 ALTER TABLE 主要负责
-
表名的修改
-
列的添加
-
列的删除
-
列的改名
ALTER TABLE <表名>
RENAME TO <新表名>;
ALTER TABLE <表名>
ADD [COLUMN] <列定义语句>;
ALTER TABLE <表名>
RENAME [COLUMN] <列名> TO <新列名>;
ALTER TABLE <表名>
DROP [COLUMN] <列名>;