[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|zhangsanexample.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。要彻底归零需要两条SQLDELETE 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 的几个不同点根据我的使用经验最直观的区别就下面这些特性SQLiteMySQL部署方式嵌入式单文件服务器-客户端架构数据类型动态类型弱类型严格静态类型用户管理不支持用户权限完善的用户权限系统并发写入只支持单线程写入写锁支持多线程并发写入存储过程不支持支持内置函数较少丰富简单说SQLite适合桌面应用、移动端、嵌入式设备MySQL适合高并发、多用户、需要复杂权限管理的Web服务。五、日常操作小记这一节记几个平时经常用到的操作不算什么高深技术但确实省了不少翻文档的时间。5.1 查看表里的数据最简单的SELECT * FROM A;数据量大的时候我会加上限制免得控制台刷屏SELECT * FROM A LIMIT 100;如果只想看表结构字段名就根据数据库来- SQLitePRAGMA table_info(A); - MySQLDESCRIBE 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 typetable) tables cursor.fetchall() has_data_tables [] for (tbl,) in tables: cursor.execute(fSELECT EXISTS (SELECT 1 FROM [{tbl}])) if cursor.fetchone()[0]: has_data_tables.append(tbl) print(has_data_tables)用EXISTS比COUNT(*)快找到第一行就停了。**PostgreSQL** 可以用统计视图sqlSELECT schemaname, tablename, n_live_tup AS row_countFROM pg_stat_user_tablesWHERE n_live_tup 0;**SQL Server**sqlSELECT t.name AS TableName, p.rows AS RowCountsFROM sys.tables tINNER JOIN sys.partitions p ON t.object_id p.object_idWHERE 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 三大主流数据库对比维度SQLiteMySQLPostgreSQL核心理念嵌入式、零配置服务器端、稳定、流行功能丰富、标准兼容架构类型嵌入式作为库集成客户端-服务器客户端-服务器并发模型单写多读写锁整库多线程行级锁多进程MVCC数据类型动态类型5种基础静态类型较丰富静态扩展支持JSON/数组等标准兼容部分标准缺RIGHT JOIN高度兼容极高兼容性安全性依赖文件权限用户账户SSLRBAC行级安全SSL部署管理零配置需要配置配置相对复杂使用场景移动/桌面/IoTWeb应用、电商复杂查询、分析、GIS典型代表Android、ChromeFacebook、TwitterReddit、Instagram6.2 企业级数据库如果你预算够维度SQLiteSQL ServerOracle设计目标轻量本地存储企业级一站式极致性能高可用功能精简内置ML/AI、JSON/XML超丰富自定义对象并发性能写锁高吞吐量大规模并行顶级并发控制安全性基础透明加密、审计、行级安全最严格金融级成本零成本商业付费按核心价格昂贵按CPU6.3 我自己怎么选这些对比看多了容易晕我给自己总结了一个简单粗暴的选择指南- **手机App、桌面小工具、嵌入式设备** → SQLite。不用配服务器省事。- **个人博客、小型网站** → SQLite也够流量大了再换。- **创业公司的电商网站并发涨得快** → MySQL。社区大人好招扩展方便。- **业务逻辑复杂动不动就连七八张表** → PostgreSQL。对SQL标准支持最好。- **要处理地图、地理位置** → PostgreSQL PostGIS没得说。- **公司预算充足微软全家桶** → SQL Server。和C#、Azure配合很顺。- **银行、国企核心交易系统** → Oracle。虽然贵但出了问题能有人负责。技术选型说到底不是“哪个最好”而是“哪个最不坏”。SQLite的“无服务器”在某些场景下就是杀手锏而大型数据库的存在也说明确实有它们才能扛住的业务。后记这份笔记是我在开发过程中随手记录的。范式教会我如何设计整洁的表结构窗口函数提升了我的查询效率而SQLite的那些小特性则是在踩坑后一点点摸索出来的。没有什么高深的理论都是能直接用上的东西。