第三章数据库与表的基本操作（DDL）

3.1 创建与管理数据库（就是一个文件）

在 MySQL / PostgreSQL 里，CREATE DATABASE 是一个正式的操作。但在 SQLite 里，"创建数据库"这件事简单到你可能感觉不到它的存在——打开一个文件，就是创建一个数据库。

创建数据库

bash

# 方式一：命令行直接指定文件名（文件不存在则自动创建）
sqlite3 my_app.db

# 方式二：在 sqlite3 会话中打开另一个数据库
sqlite> .open another.db

数据库就是文件：

  my_app.db       ← 一个完整的关系型数据库
  another.db      ← 另一个独立的数据库
  :memory:        ← 内存数据库（不创建文件）

  没有 CREATE DATABASE 命令。
  没有数据库服务器在管理这些文件。
  你的程序直接读写这些文件。

查看当前连接的数据库

text

sqlite> .databases
main: /Users/you/projects/my_app.db r/w

main 是默认的数据库名称。SQLite 允许你在一个会话中附加（ATTACH）多个数据库文件：

sql

-- 附加另一个数据库文件，给它起名 "analytics"
ATTACH DATABASE 'analytics.db' AS analytics;

-- 现在可以跨库查询
SELECT u.name, a.event_type
FROM main.users u
JOIN analytics.events a ON u.id = a.user_id;

-- 用完后分离
DETACH DATABASE analytics;

text

sqlite> .databases
main: /Users/you/projects/my_app.db r/w
analytics: /Users/you/projects/analytics.db r/w

删除数据库

没有 DROP DATABASE 命令。删除数据库 = 删除文件：

bash

rm my_app.db
# 如果使用了 WAL 模式，可能还有这两个文件：
rm my_app.db-wal my_app.db-shm

对比 MySQL / PostgreSQL：传统数据库需要连接到服务器执行 CREATE DATABASE / DROP DATABASE。SQLite 把这些操作简化成了文件系统操作——创建文件、复制文件、删除文件。这也是"替代 fopen()"设计理念的体现。

3.2 SQLite 的类型系统 —— 类型亲和性（Type Affinity）

这是 SQLite 最独特也最容易让人困惑的特性。理解它，才能避免踩坑。

核心区别：动态类型 vs 静态类型

数据库	类型检查方式	存 "abc" 到 INTEGER 列
MySQL / PostgreSQL	静态类型：列定义了什么类型，就只能存什么类型	❌ 报错！
SQLite	动态类型：类型绑定在值上，而不是列上	✅ 照存不误（默认情况）

text

-- 在 SQLite 中，这完全合法（默认情况下）：
CREATE TABLE demo (value INTEGER);
INSERT INTO demo VALUES (42);       -- 存整数 ✅
INSERT INTO demo VALUES ('hello');  -- 存文本 ✅（没有报错！）
INSERT INTO demo VALUES (3.14);     -- 存浮点 ✅
INSERT INTO demo VALUES (NULL);     -- 存 NULL ✅
INSERT INTO demo VALUES (x'CAFE');  -- 存二进制 ✅

sqlite> SELECT typeof(value), value FROM demo;
integer|42
text|hello
real|3.14
null|
blob|??

五种存储类型（Storage Class）

SQLite 内部只有 5 种存储类型，所有数据最终都以这五种形式之一存储：

┌─────────────────────────────────────────────────┐
│            SQLite 的 5 种存储类型                 │
│                                                 │
│   NULL     → 空值                               │
│   INTEGER  → 有符号整数（1/2/3/4/6/8 字节）      │
│   REAL     → 8 字节 IEEE 浮点数                  │
│   TEXT     → UTF-8 / UTF-16 文本字符串           │
│   BLOB     → 二进制数据，原样存储                 │
│                                                 │
│   注意：没有 BOOLEAN、没有 DATE、没有 DECIMAL     │
│   布尔用 0/1，日期用 TEXT 或 INTEGER 表示         │
└─────────────────────────────────────────────────┘

类型亲和性（Type Affinity）

虽然 SQLite 不强制类型，但建表时写的类型名不是完全没用的——它决定了列的类型亲和性，影响数据的自动类型转换行为。

SQLite 根据你写的类型名，按以下规则匹配亲和性：

规则（按优先级）	类型名包含	亲和性	示例
规则 1	`INT`	INTEGER	`INTEGER`, `BIGINT`, `SMALLINT`, `INT2`
规则 2	`CHAR`, `CLOB`, `TEXT`	TEXT	`VARCHAR(255)`, `TEXT`, `CLOB`
规则 3	`BLOB`（或不写类型）	BLOB	`BLOB`, 无类型名
规则 4	`REAL`, `FLOA`, `DOUB`	REAL	`REAL`, `FLOAT`, `DOUBLE`
规则 5	其他所有情况	NUMERIC	`NUMERIC`, `DECIMAL`, `BOOLEAN`, `DATE`

亲和性的实际效果：当你往某列插入数据时，SQLite 会尝试把数据转换为该列的亲和类型。但如果转换不了，它不会报错，而是按原样存储。

sql

CREATE TABLE example (
    a INTEGER,    -- 亲和性 INTEGER
    b TEXT,       -- 亲和性 TEXT
    c REAL        -- 亲和性 REAL
);

INSERT INTO example VALUES ('42', 42, '3.14');

-- 实际存储结果：
-- a = 42      (TEXT '42' 被转成了 INTEGER 42)
-- b = '42'    (INTEGER 42 被转成了 TEXT '42')
-- c = 3.14    (TEXT '3.14' 被转成了 REAL 3.14)

日期和时间怎么存？

SQLite 没有专门的日期类型，通常有三种约定：

存储方式	类型	示例	优势
ISO 8601 字符串	TEXT	`'2024-03-15 14:30:00'`	可读性好
Unix 时间戳	INTEGER	`1710510600`	运算方便
儒略日	REAL	`2460385.1042`	天文学常用

SQLite 内置了日期函数来处理这些格式：

sql

-- 获取当前时间
SELECT datetime('now');               -- 2024-03-15 06:30:00
SELECT strftime('%Y年%m月%d日', 'now'); -- 2024年03月15日

-- Unix 时间戳 ↔ 日期字符串
SELECT datetime(1710510600, 'unixepoch');  -- 转为时间字符串
SELECT strftime('%s', 'now');              -- 转为时间戳

实用建议：大多数应用选 ISO 8601 TEXT 格式，可读性和排序都没问题。如果需要做大量时间运算，用 INTEGER 时间戳更高效。

3.3 创建与修改表

CREATE TABLE 完整语法

sql

CREATE TABLE IF NOT EXISTS students (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE,
    age         INTEGER CHECK (age > 0 AND age < 150),
    gpa         REAL DEFAULT 0.0,
    enrolled_at TEXT DEFAULT (datetime('now')),
    bio         TEXT
);

逐行解读：

列定义	说明
`id INTEGER PRIMARY KEY AUTOINCREMENT`	自增主键，每行唯一标识
`name TEXT NOT NULL`	文本类型，不允许为空
`email TEXT UNIQUE`	文本类型，值不可重复
`age INTEGER CHECK (age > 0 AND age < 150)`	整数，带范围检查约束
`gpa REAL DEFAULT 0.0`	浮点数，默认值 0.0
`enrolled_at TEXT DEFAULT (datetime('now'))`	默认为插入时的当前时间
`bio TEXT`	普通文本列，可为空

IF NOT EXISTS 是一个好习惯——如果表已存在，不会报错。

INTEGER PRIMARY KEY 的特殊身份

在 SQLite 中，INTEGER PRIMARY KEY 有一个特殊行为——它会自动成为 rowid 的别名：

┌─────────────────────────────────────────────────┐
│   SQLite 的隐藏 rowid                            │
│                                                 │
│   每张表（非 WITHOUT ROWID 表）都有一个           │
│   隐藏的 64 位整数列 rowid，自动递增              │
│                                                 │
│   当你定义 id INTEGER PRIMARY KEY 时：            │
│   id 和 rowid 指向同一个值                       │
│   → 不额外占存储空间                              │
│   → 查询速度最快（直接 B-Tree 查找）              │
│                                                 │
│   如果加了 AUTOINCREMENT：                        │
│   → 保证 id 永远递增，不重用被删除的值             │
│   → 但有轻微性能开销（维护 sqlite_sequence 表）    │
└─────────────────────────────────────────────────┘

实际建议：大多数情况下用 INTEGER PRIMARY KEY 就够了（不加 AUTOINCREMENT）。只有当你明确需要"id 不能被重用"的语义时，才加 AUTOINCREMENT。

ALTER TABLE —— 修改表结构

SQLite 的 ALTER TABLE 功能比较有限，不像 MySQL / PostgreSQL 那样灵活：

操作	SQLite 支持	示例
重命名表	✅	`ALTER TABLE students RENAME TO learners;`
重命名列	✅（3.25+）	`ALTER TABLE students RENAME COLUMN bio TO biography;`
添加列	✅	`ALTER TABLE students ADD COLUMN phone TEXT;`
删除列	✅（3.35+）	`ALTER TABLE students DROP COLUMN bio;`
修改列类型	❌	不支持！
修改列约束	❌	不支持！
删除约束	❌	不支持！

遇到不支持的操作怎么办？ 用"重建表"四步法：

sql

-- 1. 创建新结构的表
CREATE TABLE students_new (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,   -- 加了 NOT NULL 约束
    age   INTEGER
);

-- 2. 迁移数据
INSERT INTO students_new (id, name, email, age)
SELECT id, name, email, age FROM students;

-- 3. 删除旧表
DROP TABLE students;

-- 4. 重命名新表
ALTER TABLE students_new RENAME TO students;

注意：重建表时，如果有外键引用、索引、触发器，需要一并重建。生产环境建议在事务中执行这组操作。

DROP TABLE —— 删除表

sql

DROP TABLE students;           -- 表不存在会报错
DROP TABLE IF EXISTS students; -- 表不存在也不报错（推荐）

创建表时复制结构

sql

-- 复制表结构 + 数据
CREATE TABLE students_backup AS SELECT * FROM students;

-- 只复制结构，不复制数据
CREATE TABLE students_empty AS SELECT * FROM students WHERE 0;

注意：CREATE TABLE ... AS SELECT 创建的新表不会保留约束（PRIMARY KEY、UNIQUE 等）。如果需要保留约束，应该手写 CREATE TABLE 语句。

3.4 约束（Constraints）

约束是数据库保证数据质量的"守门员"。SQLite 支持以下几种约束：

PRIMARY KEY（主键）

sql

-- 方式一：列级约束
CREATE TABLE users (
    id INTEGER PRIMARY KEY
);

-- 方式二：复合主键（表级约束）
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

NOT NULL（非空）

sql

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL  -- 插入时必须提供 name，否则报错
);

INSERT INTO users (id) VALUES (1);
-- Error: NOT NULL constraint failed: users.name

UNIQUE（唯一）

sql

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE  -- 不同行的 email 不能重复
);

INSERT INTO users VALUES (1, 'a@test.com');
INSERT INTO users VALUES (2, 'a@test.com');
-- Error: UNIQUE constraint failed: users.email

注意：在 SQLite 中，UNIQUE 约束允许多个 NULL 值（NULL ≠ NULL）。

DEFAULT（默认值）

sql

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    title      TEXT NOT NULL,
    status     TEXT DEFAULT 'draft',
    created_at TEXT DEFAULT (datetime('now'))
);

-- 不提供 status 和 created_at 时，自动填入默认值
INSERT INTO posts (title) VALUES ('我的第一篇文章');

CHECK（检查约束）

sql

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0)
);

INSERT INTO products VALUES (1, '键盘', -99, 10);
-- Error: CHECK constraint failed: price > 0

FOREIGN KEY（外键）

外键用于维护表与表之间的引用关系。SQLite 默认不启用外键检查，需要手动开启！

sql

-- ⚠️ 必须先开启外键支持（每次连接都要执行）
PRAGMA foreign_keys = ON;

CREATE TABLE departments (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
                ON DELETE CASCADE    -- 部门删除时，员工也删除
                ON UPDATE CASCADE    -- 部门 ID 变更时，员工的 dept_id 跟着变
);

外键的级联操作选项：

选项	效果
`ON DELETE CASCADE`	父行删除 → 子行也删除
`ON DELETE SET NULL`	父行删除 → 子行外键列设为 NULL
`ON DELETE SET DEFAULT`	父行删除 → 子行外键列设为默认值
`ON DELETE RESTRICT`	父行有子行引用时，禁止删除（默认）

sql

-- 测试外键约束
INSERT INTO departments VALUES (1, '技术部');
INSERT INTO employees VALUES (1, '张三', 1);

-- 尝试引用不存在的部门
INSERT INTO employees VALUES (2, '李四', 99);
-- Error: FOREIGN KEY constraint failed

-- 删除部门，员工也自动删除（CASCADE）
DELETE FROM departments WHERE id = 1;
SELECT * FROM employees;  -- 空的，张三被级联删除了

重要提醒：PRAGMA foreign_keys = ON 是连接级别的设置，每次打开数据库都要执行。在代码中建议放在连接创建后的第一行。

本章小结

知识点	要点
创建数据库	打开文件即创建，`sqlite3 file.db`，无 `CREATE DATABASE`
多库操作	`ATTACH DATABASE` 附加其他数据库文件，支持跨库查询
类型系统	动态类型，5 种存储类型，列的类型名决定亲和性（影响自动转换）
日期存储	推荐 ISO 8601 TEXT 或 Unix 时间戳 INTEGER
CREATE TABLE	支持 `IF NOT EXISTS`；`INTEGER PRIMARY KEY` = rowid 别名
ALTER TABLE	支持重命名表/列、添加/删除列；不支持修改列类型和约束
约束	PRIMARY KEY / NOT NULL / UNIQUE / DEFAULT / CHECK / FOREIGN KEY
外键	默认关闭！每次连接需 `PRAGMA foreign_keys = ON`

下一章预告：学习数据的增删改——INSERT 的多种用法、UPDATE、DELETE，以及 SQLite 3.24+ 的 UPSERT 语法。

第三章 数据库与表的基本操作（DDL） ​

3.1 创建与管理数据库（就是一个文件） ​

创建数据库 ​

查看当前连接的数据库 ​

删除数据库 ​

3.2 SQLite 的类型系统 —— 类型亲和性（Type Affinity） ​

核心区别：动态类型 vs 静态类型 ​

五种存储类型（Storage Class） ​

类型亲和性（Type Affinity） ​

日期和时间怎么存？ ​

3.3 创建与修改表 ​

CREATE TABLE 完整语法 ​

INTEGER PRIMARY KEY 的特殊身份 ​

ALTER TABLE —— 修改表结构 ​

DROP TABLE —— 删除表 ​

创建表时复制结构 ​

3.4 约束（Constraints） ​

PRIMARY KEY（主键） ​

NOT NULL（非空） ​

UNIQUE（唯一） ​

DEFAULT（默认值） ​

CHECK（检查约束） ​

FOREIGN KEY（外键） ​

本章小结 ​

第三章数据库与表的基本操作（DDL）

3.1 创建与管理数据库（就是一个文件）

创建数据库

查看当前连接的数据库

删除数据库

3.2 SQLite 的类型系统 —— 类型亲和性（Type Affinity）

核心区别：动态类型 vs 静态类型

五种存储类型（Storage Class）

类型亲和性（Type Affinity）

日期和时间怎么存？

3.3 创建与修改表

CREATE TABLE 完整语法

INTEGER PRIMARY KEY 的特殊身份

ALTER TABLE —— 修改表结构

DROP TABLE —— 删除表

创建表时复制结构

3.4 约束（Constraints）

PRIMARY KEY（主键）

NOT NULL（非空）

UNIQUE（唯一）

DEFAULT（默认值）

CHECK（检查约束）

FOREIGN KEY（外键）

本章小结