[SQL]数据库设计手记：从范式到窗口函数，一个开发者的实战笔记

目录

# 数据库设计手记：从范式到窗口函数，一个开发者的实战笔记

## 一、范式：为什么我的表越拆越多？

## 二、窗口函数：不减少行数的“分组计算”

## 三、SQLite 的“坑”与“解”

### 3.1 清空表后自增ID为什么不归零？

### 3.2 Qt 连接 SQLite：为什么要写“QSQLITE”？

### 3.3 SqliteStudio 的小设置

## 五、日常操作小记

### 5.1 查看表里的数据

### 5.2 找出哪些表里有数据

### 5.3 注释怎么写

## 六、SQLite 与更多数据库的较量

### 6.1 三大主流数据库对比

### 6.2 企业级数据库（如果你预算够）

### 6.3 我自己怎么选

## 后记

引用

记一次项目重构的真实经历，以及那些被我踩过的坑

一、范式：为什么我的表越拆越多？

去年接手了一个“学生选课系统”的后台维护。打开数据库一看，一张“学生信息表”里有50多个字段，其中“联系方式”一栏存的是“138****0000|zhangsan@example.com”这样的字符串。查询时要用`LIKE`去匹配，慢得要命。

这就是典型的**第一范式（1NF）**问题——字段不是原子的。解决办法很简单：拆成“电话号码”和“邮箱地址”两列。这是范式中最基础的，也是我最早学会的。

真正让我头疼的是**第二范式（2NF）**。当时有一张“订单明细表”，主键是（订单编号，产品编号）。里面有个“产品名称”字段，按理说应该只依赖于“产品编号”，可它却被塞在主表里。结果同一产品在不同订单中重复存储了上百次“产品名称”，改一次名字得更新几十行。拆出一张“产品表”后，问题迎刃而解。

**第三范式（3NF）**的例子更贴近日常。某张“员工表”里既有“部门编号”又有“部门名称”。部门名称依赖于部门编号，而部门编号依赖于员工编号——这就是传递依赖。后来我把部门信息单独拎出来做成“部门表”，主表里只留一个部门编号。

至于**BC范式**和**第四范式（4NF）**，说实话在实际项目中用得不多。BC范式要求每个决定因素都包含候选键——有一次在设计“学生-导师-专业”表时，因为“专业”依赖于“导师”而导师又不是候选键，导致数据冗余。拆成两张表就解决了。4NF处理的是多值依赖问题，比如“课程-教师-教材”那种一门课对应多个教师和多个教材、但教师和教材之间无关的情况，拆成“课程-教师”和“课程-教材”两张表即可。

**结论**：我一般做到3NF就停下来。除非有明显性能或冗余问题，才会考虑更高范式。过度拆分反而会增加关联查询的复杂度。

二、窗口函数：不减少行数的“分组计算”

以前做排名统计，我习惯用子查询或者临时表。直到有一次需要同时显示“每条订单的金额”和“该用户的总金额”时，窗口函数给了我一记直拳般的效率提升。

SELECT 订单号, 用户ID, 金额, SUM(金额) OVER(PARTITION BY 用户ID) AS 用户总金额 FROM 订单表;

这就是**聚合类窗口函数**——它不减少行数，只是在每行后面追加聚合结果。

**排序类**有三个，容易混淆：

- `ROW_NUMBER()`：1,2,3,4… 每行一个号，不重复

- `RANK()`：1,1,3,4… 并列后跳过下个序号

- `DENSE_RANK()`：1,1,2,3… 并列后不跳过

我用`ROW_NUMBER()`做分页最顺手，用`RANK()`做成绩排名。

**偏移类**的`LAG()`和`LEAD()`也很实用。比如对比当前销售额与上个月的：

SELECT 月份, 销售额, LAG(销售额, 1) OVER(ORDER BY 月份) AS 上月销售额 FROM 销售表;

窗口函数的学习曲线不陡，但需要多用才能形成条件反射。

三、SQLite 的“坑”与“解”

3.1 清空表后自增ID为什么不归零？

刚开始用SQLite时，我用`DELETE FROM 表名`清空数据，然后插入新记录，发现ID从上次的最大值+1继续，而不是从1开始。翻文档才知道：SQLite有一个内部隐藏表`sqlite_sequence`，记录了每个自增表的当前最大ID。

要彻底归零，需要两条SQL：

DELETE FROM 表名; DELETE FROM sqlite_sequence WHERE name = '表名';

或者用`UPDATE sqlite_sequence SET seq = 0 WHERE name = '表名'`也行。但更省事的办法是：如果不需要保留ID连续性，直接用`TRUNCATE`？不好意思，SQLite没有`TRUNCATE`命令，只能用上面两条。

3.2 Qt 连接 SQLite：为什么要写“QSQLITE”？

在Qt里连接SQLite，标准写法是：

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "kc_db");

第一个参数`"QSQLITE"`是驱动类型。Qt的数据库模块采用“插件工厂”机制——`QSqlDatabase`本身不直接操作数据库，它根据传入的字符串去加载对应的驱动插件（比如`qsqlite.dll`）。第二个参数`"kc_db"`是给这个连接起的名字，方便后续通过`QSqlDatabase::database("kc_db")`再次获取。

如果忘记指定连接名，Qt会创建一个默认连接。但多次调用`addDatabase`而不改名字会导致覆盖。所以给每个连接起不同的名字是个好习惯。

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "kc_db"); db.setDatabaseName("/path/to/my_data.db"); if (!db.open()) { qDebug() << "打开失败" << db.lastError().text(); } // 操作... db.close();

3.3 SqliteStudio 的小设置

用SqliteStudio写SQL时，默认只执行光标所在行——这个设计坑了我好几次。后来发现按`F10`，取消勾选“只执行输入符所在行的语句”，就能正常执行选中的多行语句了。

四、SQLite 与 MySQL 的几个不同点

根据我的使用经验，最直观的区别就下面这些：

简单说：SQLite适合桌面应用、移动端、嵌入式设备；MySQL适合高并发、多用户、需要复杂权限管理的Web服务。

五、日常操作小记

这一节记几个平时经常用到的操作，不算什么高深技术，但确实省了不少翻文档的时间。

5.1 查看表里的数据

最简单的：

SELECT * FROM A;

数据量大的时候我会加上限制，免得控制台刷屏：

SELECT * FROM A LIMIT 100;

如果只想看表结构（字段名），就根据数据库来：

- SQLite：`PRAGMA table_info(A);` - MySQL：`DESCRIBE A;`

5.2 找出哪些表里有数据

有时候接手一个陌生的数据库，想知道哪些表不是空的。不同数据库的写法不一样。

**MySQL** 可以直接查系统表：

SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_ROWS > 0;

注意`TABLE_ROWS`对InnoDB只是近似值，想要精确就得挨个`SELECT COUNT(*)`。

**SQLite** 没有内置的行数统计表，我一般写个简单的Python脚本：

import sqlite3 conn = sqlite3.connect('your.db') cursor = conn.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table'") tables = cursor.fetchall() has_data_tables = [] for (tbl,) in tables: cursor.execute(f"SELECT EXISTS (SELECT 1 FROM [{tbl}])") if cursor.fetchone()[0]: has_data_tables.append(tbl) print(has_data_tables)

用`EXISTS`比`COUNT(*)`快，找到第一行就停了。

**PostgreSQL** 可以用统计视图：

```sql

SELECT schemaname, tablename, n_live_tup AS row_count

FROM pg_stat_user_tables

WHERE n_live_tup > 0;

```

**SQL Server**：

```sql

SELECT t.name AS TableName, p.rows AS RowCounts

FROM sys.tables t

INNER JOIN sys.partitions p ON t.object_id = p.object_id

WHERE p.index_id IN (0,1) AND p.rows > 0;

```

如果你用的是`.db3`文件（SQLite），懒得写代码也可以用图形工具。我试过**DB Browser for SQLite**，打开文件后直接切到“浏览数据”选项卡，下拉菜单里一个个看哪个表有数据就行。或者用**SQLiteStudio**，同样直观。

5.3 注释怎么写

SQL里的注释两种都支持，和大多数数据库一样：

```sql

-- 这是单行注释，后面加个空格比较安全

SELECT * FROM users;

/*

这是多行注释

可以跨好几行

*/

SELECT * FROM products;

SELECT /* 注释塞在语句中间也行 */ name, age FROM employees;

```

六、SQLite 与更多数据库的较量

之前只对比了SQLite和MySQL，后来我又整理了一份更全的对比，涵盖了PostgreSQL、SQL Server和Oracle。不是为了比谁更好，而是搞清楚各自适合什么场合。

6.1 三大主流数据库对比

6.2 企业级数据库（如果你预算够）

6.3 我自己怎么选

这些对比看多了容易晕，我给自己总结了一个简单粗暴的选择指南：

- **手机App、桌面小工具、嵌入式设备** → SQLite。不用配服务器，省事。

- **个人博客、小型网站** → SQLite也够，流量大了再换。

- **创业公司的电商网站，并发涨得快** → MySQL。社区大，人好招，扩展方便。

- **业务逻辑复杂，动不动就连七八张表** → PostgreSQL。对SQL标准支持最好。

- **要处理地图、地理位置** → PostgreSQL + PostGIS，没得说。

- **公司预算充足，微软全家桶** → SQL Server。和C#、Azure配合很顺。

- **银行、国企核心交易系统** → Oracle。虽然贵，但出了问题能有人负责。

技术选型说到底不是“哪个最好”，而是“哪个最不坏”。SQLite的“无服务器”在某些场景下就是杀手锏，而大型数据库的存在也说明确实有它们才能扛住的业务。

后记

这份笔记是我在开发过程中随手记录的。范式教会我如何设计整洁的表结构，窗口函数提升了我的查询效率，而SQLite的那些小特性则是在踩坑后一点点摸索出来的。没有什么高深的理论，都是能直接用上的东西。