人事管理数据库课程设计：从E-R图到视图与统计查询

简介这份《数据库系统课程设计——人事管理》文档面向高校数据库课程设计学习者与需要完成人事管理课题的开发者围绕多部门企业的人事信息自动化管理给出从需求分析到系统实现的一整套设计思路。内容覆盖部门与员工信息维护、模糊查询、按年月统计出勤、按日期查询部门迟到早退人数、按年统计人员调动等典型业务并串联概念设计中的实体关系梳理、逻辑设计中的关系模式与约束定义、物理设计中的SQL Server索引与存储过程优化以及数据库实施阶段的建库、初始数据填充和增删改查SQL编写。文档还涉及功能实现与测试总结可帮助读者理解ER模型、范式理论及性能优化策略在课程项目中的落地方式。压缩包仅含1个doc文件约1.23MB便于直接查阅和整理报告。目前已有1110人学习适合用作课程设计参考、方案比对与报告撰写辅助。1. 从一份课程设计报告说起人事管理库到底能跑出什么带过几届课程设计我最怕见到的不是写不出 SQL而是报告里 E-R 图画得工工整整真要建库的时候六张表的外键一个都没落。这份人事管理的数据库课程设计属于少见的正例从需求分析、概念设计一路走到物理设计和实施需求也不虚——模糊查询、按年月统计某个职工的出勤、按某天统计某部门的迟到早退人数、按年统计各部门的调入调出每一条都能落成一条带 GROUP BY 的 SQL而不是一句空话。它适合两类人正在做数据库课程设计、需要一个能照着改的完整范例的人以及想复习一遍从 E-R 图到建表、视图、统计查询整条链路的人。下面不按报告的目录顺序念按「表怎么设计、SQL 怎么写、哪里会翻车、怎么改成能演示的系统」走一遍。2. 六张表怎么切从 E-R 图到真能建出来的表结构报告里一共六个实体员工基本信息、员工工作信息、部门信息、员工工资、员工考勤、员工调动。这个切法本身没毛病问题出在字段级——同一个事实被放进了两张表或者干脆用中文描述代替了约束。这一章先把关系模式定死再落成能直接执行的 DDL。2.1 实体、联系与关系模式的拆分口径按 E-R 转关系模式的老规矩一个实体一张表1:N 联系把 1 端的主键塞进 N 端M:N 联系单独建表。落到这个场景上部门与员工是 1:N员工与调动记录是 1:N考勤是「员工 × 日期」这种带复合主键的弱实体工资是「员工 × 月份」。报告里有一处冗余值得当场改掉员工信息表里有「所属部门」员工工作信息表里又有「部门编号」。同一个事实存两份员工一调动就得两边同时改改漏一边就出现「人在这部门、工资算在那部门」的脏数据。我在还原时的处理是当前部门只认员工信息表的一个外键员工工作信息表只保留职称和工龄历史调动全部进调动流水表需要看「谁什么时候从哪去哪」就查流水。这样任何一次调动只有一个写入口也就只有一个可能出错的地方。同样工资表在报告里只有员工编号一个主键意味着一个员工只能存一条工资记录发第二个月就把上个月的覆盖了。加上「工资月份」做复合主键之后历史工资才留得住按年统计才有数据可算。2.2 建表顺序与外键落地外键决定了建表顺序被引用的表先建。所以顺序是部门 → 员工 → 员工工作 → 工资 → 考勤 → 调动。下面这段 DDL 里所有约束都写成了命名约束好处是后面想删或者想改的时候不用去猜数据库自动生成的那串乱码名字。-- 1. 部门表其他表都要引用它的主键必须最先建 CREATE TABLE department_info ( department_id INT NOT NULL, -- 部门编号限定三位数 department_name VARCHAR(20) NOT NULL, -- 部门名称十个汉字以内 manager_name VARCHAR(10) NOT NULL DEFAULT , -- 部门经理姓名 department_tel VARCHAR(11) NOT NULL, -- 部门电话 CONSTRAINT pk_department PRIMARY KEY (department_id), CONSTRAINT uk_department_name UNIQUE (department_name), CONSTRAINT ck_department_id CHECK (department_id BETWEEN 100 AND 999) ); -- 2. 员工基本信息表当前部门只在这里存一份 CREATE TABLE staff_info ( staff_id INT NOT NULL, -- 员工编号 name VARCHAR(10) NOT NULL, -- 姓名 gender VARCHAR(4) NOT NULL, -- 性别 age INT NULL, -- 年龄允许为空 id_card VARCHAR(18) NOT NULL, -- 身份证号 hire_date DATE NOT NULL, -- 入职时间 department_id INT NOT NULL, -- 当前所属部门外键 phone VARCHAR(11) NOT NULL, -- 联系电话 base_salary DECIMAL(10,2) NOT NULL DEFAULT 0, -- 基本工资用定点数 emp_status VARCHAR(10) NOT NULL DEFAULT 在职, -- 在职/离职/退休替代物理删除 CONSTRAINT pk_staff PRIMARY KEY (staff_id), CONSTRAINT uk_staff_idcard UNIQUE (id_card), CONSTRAINT fk_staff_dept FOREIGN KEY (department_id) REFERENCES department_info(department_id), CONSTRAINT ck_staff_gender CHECK (gender IN (男,女)), CONSTRAINT ck_staff_age CHECK (age IS NULL OR age BETWEEN 16 AND 70) );这段的重点不在语法而在三个决定。第一department_id参与了外键意味着你没法往一个不存在的部门里塞人报告里「所属部门 varchar」是做不到这件事的。第二base_salary从FLOAT换成了DECIMAL(10,2)因为金额用浮点累加会出现 0.01 级别的误差工资单上差一分钱演示现场就得解释半小时。第三加了emp_status人事系统的员工「删除」实际上是离职物理删掉的话考勤、工资、调动的历史全部变成孤儿数据。-- 3. 员工工作信息表主键就是员工编号一对一 CREATE TABLE staff_work ( staff_id INT NOT NULL, position VARCHAR(10) NULL, -- 职称 work_age INT NULL, -- 工龄 CONSTRAINT pk_staff_work PRIMARY KEY (staff_id), CONSTRAINT fk_work_staff FOREIGN KEY (staff_id) REFERENCES staff_info(staff_id), CONSTRAINT ck_work_age CHECK (work_age IS NULL OR work_age BETWEEN 0 AND 60) ); -- 4. 工资表复合主键员工 月份历史才留得住 CREATE TABLE staff_salary ( staff_id INT NOT NULL, salary_month CHAR(6) NOT NULL, -- 形如 202403 basic_salary DECIMAL(10,2) NOT NULL, subsidy DECIMAL(10,2) NULL DEFAULT 0, -- 补贴 bonus DECIMAL(10,2) NULL DEFAULT 0, -- 奖金 overtime_pay DECIMAL(10,2) NULL DEFAULT 0, -- 加班费 deduction DECIMAL(10,2) NULL DEFAULT 0, -- 扣款 real_salary DECIMAL(10,2) NOT NULL, -- 实发工资 CONSTRAINT pk_salary PRIMARY KEY (staff_id, salary_month), CONSTRAINT fk_salary_staff FOREIGN KEY (staff_id) REFERENCES staff_info(staff_id), CONSTRAINT ck_salary_month CHECK (salary_month LIKE 20__%) ); -- 5. 考勤表一天一条复合主键天然防重 CREATE TABLE staff_signin ( staff_id INT NOT NULL, sign_date DATE NOT NULL, absent TINYINT NOT NULL DEFAULT 0, -- 缺勤 0/1 late TINYINT NOT NULL DEFAULT 0, -- 迟到 0/1 leave_early TINYINT NOT NULL DEFAULT 0, -- 早退 0/1 CONSTRAINT pk_signin PRIMARY KEY (staff_id, sign_date), CONSTRAINT fk_signin_staff FOREIGN KEY (staff_id) REFERENCES staff_info(staff_id), CONSTRAINT ck_signin_flag CHECK (absent IN (0,1) AND late IN (0,1) AND leave_early IN (0,1)) ); -- 6. 调动流水表只追加不修改 CREATE TABLE staff_transfer ( staff_id INT NOT NULL, old_dep_id INT NOT NULL, -- 原部门 new_dep_id INT NOT NULL, -- 新部门 old_time DATE NOT NULL, -- 调离时间 new_time DATE NOT NULL, -- 调入时间 CONSTRAINT pk_transfer PRIMARY KEY (staff_id, new_time), CONSTRAINT fk_transfer_staff FOREIGN KEY (staff_id) REFERENCES staff_info(staff_id), CONSTRAINT fk_transfer_old FOREIGN KEY (old_dep_id) REFERENCES department_info(department_id), CONSTRAINT fk_transfer_new FOREIGN KEY (new_dep_id) REFERENCES department_info(department_id), CONSTRAINT ck_transfer_diff CHECK (old_dep_id new_dep_id) );考勤表的主键选得很关键(staff_id, sign_date)直接让「同一个员工同一天出现两条打卡记录」这件事在数据库层面不可能发生后面SUM(late)才敢直接当人数用。调动表沿用同样的思路(staff_id, new_time)保证一个人同一时刻不会调两次真要支持一天内多次调动再加一个自增代理键即可但在教学场景里没必要。2.3 类型、长度和约束的取舍报告的数据字典里写了不少长度比如「姓名 varchar 10四个汉字以内」。这里有个新手常踩的坑VARCHAR(10)在不同数据库里按字符还是按字节算结果不一样按字节算的库里一个汉字占 3 字节VARCHAR(10)就只装得下 3 个汉字。稳妥的做法是把姓名放到VARCHAR(20)多占的空间远比一次插入失败省心。身份证号、电话号码统一用字符串不要图省事用数字类型。电话号码带前导零存成整数零就没了身份证号 18 位超出常见整数范围某些库里会直接溢出。工号同理INT会让001001变成1001如果教务或者公司人事的编号规则带前导零打印出来的报表就对不上人。这类「编号类字段」我一律建议CHAR(6)补零存储宁可排序的时候多写一个ORDER BY staff_id。提示约束写得越细后期改起来越疼。CHECK 约束适合写在「业务上绝不可能出现」的字段上比如性别、0/1 标志位像「年龄 16 到 70」这种会随政策变动的范围放在应用层校验更灵活。3. 视图和统计查询把四条业务需求写成 SQL报告的第 6 章列了五张视图方向是对的——把多表 JOIN 封进视图前端只面对「员工编号、姓名、部门名称」这种人类看得懂的列。但原版视图里有几个实打实的问题直接照抄会在演示时被问住。这一章把视图重写一遍再把三条统计需求落成可执行 SQL。3.1 五张视图的拼装顺序与一处必须修的 JOIN「员工个人信息视图」是最常用的那张它要拼员工、部门、职称工龄还要带上最近一次调动时间。报告原版是直接INNER JOIN staff_transfer问题在于调动表是流水一个人调过两次就会出现两行视图里同一个人凭空多出一条记录。正确写法是先取每个员工最新的一条调动CREATE VIEW v_staff_profile AS SELECT s.staff_id AS 员工编号, s.name AS 姓名, s.gender AS 性别, d.department_id AS 部门编号, d.department_name AS 部门名称, w.position AS 职称, w.work_age AS 工龄, t.new_time AS 最近调入时间 FROM staff_info s JOIN department_info d ON d.department_id s.department_id LEFT JOIN staff_work w ON w.staff_id s.staff_id -- 新员工可能还没录职称 LEFT JOIN staff_transfer t ON t.staff_id s.staff_id AND t.new_time (SELECT MAX(t2.new_time) FROM staff_transfer t2 WHERE t2.staff_id s.staff_id); -- 只取最新一条这里有两处 LEFT JOIN含义不同。员工工作信息用 LEFT JOIN是因为「刚入职还没定职称」是真实存在的状态用 INNER JOIN 会把这批人从视图里抹掉查「全公司在册人数」就永远少几个。调动信息用 LEFT JOIN 加子查询是因为老员工可能一次都没调动过而调过多次的只能留最新那条。调动明细视图里有个更隐蔽的 bug报告原版是这样的原部门编号和新部门编号都指向同一张部门表但只 JOIN 了一次部门表结果「原部门名称」显示出来的是新部门的名称。修法很直白——给部门表起两个别名JOIN 两次CREATE VIEW v_transfer_detail AS SELECT t.staff_id AS 员工编号, s.name AS 姓名, t.old_dep_id AS 原部门编号, d_old.department_name AS 原部门名称, -- 必须再 JOIN 一次部门表 t.old_time AS 调离时间, t.new_dep_id AS 新部门编号, d_new.department_name AS 新部门名称, t.new_time AS 调入时间 FROM staff_transfer t JOIN staff_info s ON s.staff_id t.staff_id JOIN department_info d_old ON d_old.department_id t.old_dep_id JOIN department_info d_new ON d_new.department_id t.new_dep_id;这类「同一张表 JOIN 两次」的写法在工资、考勤视图里用不上但只要出现「同一实体的两个角色」比如发货仓和收货仓、上级部门和下级部门就一定会遇到。判断标准很简单视图里每出现一个指向同一张表的外键就得有一个独立的别名。工资视图和考勤视图相对简单注意一点就够了JOIN 的方向要跟业务问题一致。查「所有员工的工资」用员工表 LEFT JOIN 工资表没发工资的人也要出现在列表里显示为空查「本月已发工资的人」用 INNER JOIN。这两种写法在某些月份的结果能差出几十行。3.2 按年月统计出勤、按日统计迟到早退「按年份月份统计某个职工的出勤情况」这条需求落到 SQL 上就是一段带日期范围的聚合。日期范围的写法有讲究我一般不用BETWEEN-- 统计某员工 2024 年 3 月的出勤情况 SELECT s.staff_id AS 员工编号, s.name AS 姓名, COUNT(*) AS 记录天数, SUM(CASE WHEN k.absent 1 THEN 1 ELSE 0 END) AS 缺勤天数, SUM(CASE WHEN k.late 1 THEN 1 ELSE 0 END) AS 迟到次数, SUM(CASE WHEN k.leave_early 1 THEN 1 ELSE 0 END) AS 早退次数 FROM staff_signin k JOIN staff_info s ON s.staff_id k.staff_id WHERE k.staff_id 1001 AND k.sign_date 2024-03-01 AND k.sign_date 2024-04-01 -- 半开区间别用 2024-03-31 GROUP BY s.staff_id, s.name;为什么坚持半开区间因为sign_date哪天被人从DATE改成DATETIME 2024-03-31会把 3 月 31 日当天带时分秒的记录全部漏掉而 2024-04-01不受影响。这个坑我在真实项目里踩过改字段类型的时候没人记得去翻统计 SQL月底报表少一天排查了一下午。参数化的做法是前端传来2024-03应用层拆成年和月算出当月首日和次月首日再作为两个参数塞进去。别在 SQL 里拼字符串也别指望LIKE 2024-03%——日期字段上做字符串匹配索引直接失效。「按某年某月某日统计查询某部门的迟到和早退人数」这条本质是按部门分组、按日期过滤-- 指定某一天统计各部门的迟到、早退人数 SELECT d.department_id AS 部门编号, d.department_name AS 部门名称, COUNT(*) AS 打卡人数, COALESCE(SUM(k.late), 0) AS 迟到人数, COALESCE(SUM(k.leave_early), 0) AS 早退人数 FROM staff_signin k JOIN staff_info s ON s.staff_id k.staff_id JOIN department_info d ON d.department_id s.department_id WHERE k.sign_date 2024-03-15 GROUP BY d.department_id, d.department_name ORDER BY d.department_id;SUM(k.late)能直接当人数用靠的就是考勤表那个复合主键——一人一天一条late 1的行数就是迟到人数。如果哪天主键被改成单列自增这条 SQL 立刻变成错的而且是那种「数字看着像对的」的错特别难发现。这就是为什么我上一章死磕主键。注意SUM在没有任何匹配行时返回 NULL 而不是 0。虽然这里有 GROUP BY 兜底但只要换成子查询或者 LEFT JOIN 场景NULL 就会顺着算术运算污染整列COALESCE该包就包。3.3 按年统计调入调出、模糊查询与索引「按年统计各部门调入调出人数」是这几条里最容易写错的一条。直觉写法是拿调动表 JOIN 部门表但这会漏掉「整年一个人都没调进调出」的部门——报表上这些部门直接消失看报表的人会以为部门被撤销了。正确姿势是以部门表为基准把两张统计结果 LEFT JOIN 上去-- 2024 年各部门调入、调出人数没有调动的部门也要出现显示 0 SELECT d.department_id AS 部门编号, d.department_name AS 部门名称, COALESCE(in_cnt.cnt, 0) AS 调入人数, COALESCE(out_cnt.cnt, 0) AS 调出人数 FROM department_info d LEFT JOIN ( SELECT new_dep_id AS dep_id, COUNT(*) AS cnt FROM staff_transfer WHERE EXTRACT(YEAR FROM new_time) 2024 GROUP BY new_dep_id ) in_cnt ON in_cnt.dep_id d.department_id LEFT JOIN ( SELECT old_dep_id AS dep_id, COUNT(*) AS cnt FROM staff_transfer WHERE EXTRACT(YEAR FROM old_time) 2024 GROUP BY old_dep_id ) out_cnt ON out_cnt.dep_id d.department_id ORDER BY d.department_id;EXTRACT(YEAR FROM ...)是标准写法大部分关系库都认如果你的环境只支持YEAR()之类的函数替换掉这一处即可注意别把函数套在参数上——WHERE EXTRACT(YEAR FROM new_time) 2024会让new_time上的索引失效数据量上十万行以后能明显感觉到。真要优化改成new_time 2024-01-01 AND new_time 2025-01-01让索引派上用场。模糊查询那条需求报告里要求「支持模糊查询」落到 SQL 就是LIKE %关键字%SELECT staff_id, name, gender, hire_date, department_id FROM staff_info WHERE name LIKE %张% AND department_id 1001 ORDER BY staff_id FETCH FIRST 20 ROWS ONLY; -- 分页部分库写 LIMIT 20 OFFSET 0前导百分号的LIKE走不了普通 B-Tree 索引这是常识但在几百条数据的人事库里根本无所谓别为了这点数据量上全文索引纯属给自己找事。真正要管的是另一件事拼 SQL。姓名是用户输入的直接字符串拼接就是一个注 入口子 OR 11一贴整张表的数据就出来了。用绑定参数一行代码的事。-- 索引只为真正会被 WHERE / JOIN 用到的列建 CREATE INDEX idx_signin_date ON staff_signin (sign_date); -- 按日期统计 CREATE INDEX idx_staff_dept ON staff_info (department_id); -- 按部门聚合 CREATE INDEX idx_transfer_new ON staff_transfer (new_time); -- 按年统计调入 CREATE INDEX idx_transfer_old ON staff_transfer (old_time); -- 按年统计调出索引不是越多越好每多一个插入和更新就多一份维护成本。考勤表每天几百条往里灌的时候五个索引和两个索引的差别是能测出来的。判断标准就一条这条 SQL 会不会被频繁执行它的 WHERE 里有没有这个列。4. 数据库实施建库、灌数据、把调动做成一个事务设计和能跑起来之间隔着一层「实施」报告里这一章基本是 SQL 语句的截图缺的是顺序和验证。这一章补上文件怎么放、测试数据怎么造、调动这种跨表操作怎么保证不出半截数据。4.1 数据文件、日志文件与初始化脚本报告里提到数据库由数据文件、次要数据文件和事务日志文件组成这个说法没问题落到实操上要关注两件事日志文件和数据文件不要放在同一块盘上日志是顺序写的数据是随机读的混在一起互相拖后腿以及日志文件的增长要设上限教学环境里几十次批量插入就能把日志撑到几个 G磁盘满了之后连INSERT都失败报的错还特别含糊。初始化脚本我习惯拆成三个文件而不是一个大文件# 目录结构 db/ ├── 01_schema.sql # 建表、约束、索引 ├── 02_view.sql # 五张视图 └── 03_seed.sql # 测试数据# 执行顺序不能乱表 → 视图 → 数据 run_sql 01_schema.sql run_sql 02_view.sql run_sql 03_seed.sql拆开的好处是可重复执行。调表结构的时候只重跑01_schema.sql不用把几百行测试数据也重灌一遍反过来只改测试数据的时候也不用担心把表结构删了重建。每个脚本开头都写上DROP TABLE IF EXISTS按外键逆序删先删子表这样一个脚本能在任何一台机器上从零跑通答辩换电脑也不慌。提示删表顺序必须和建表顺序相反——先删调动、考勤、工资、工作信息再删员工最后删部门。顺序反了会被外键挡住报错信息通常只说「违反引用约束」不说到底卡在哪张表。4.2 造一批能验证统计的测试数据统计 SQL 写得对不对全看测试数据造得刁不刁。全是「每人每天正常打卡」的数据任何一条 SQL 都能跑出漂亮结果也验证不出任何问题。我造数据时会刻意覆盖这几类边界-- 部门至少三个其中一个整年无人调动用来验证 LEFT JOIN INSERT INTO department_info VALUES (101, 人事部, 某经理, 01000000001), (102, 技术部, 某经理, 01000000002), (103, 财务部, 某经理, 01000000003); -- 员工覆盖「新入职未定职称」「已离职」两种状态 INSERT INTO staff_info (staff_id, name, gender, age, id_card, hire_date, department_id, phone, base_salary, emp_status) VALUES (1001, 张三, 男, 28, 110101199601010011, 2021-03-01, 101, 13800000001, 8000.00, 在职), (1002, 李四, 女, 34, 110101199002020022, 2018-07-15, 102, 13800000002, 15000.00, 在职), (1003, 王五, 男, 41, 110101198303030033, 2010-01-04, 102, 13800000003, 22000.00, 在职), (1004, 赵六, 女, 25, 110101199904040044, 2024-02-20, 103, 13800000004, 6500.00, 在职); -- 考勤覆盖月初、月末、连续迟到、整天缺勤 INSERT INTO staff_signin (staff_id, sign_date, absent, late, leave_early) VALUES (1001, 2024-03-01, 0, 0, 0), (1001, 2024-03-04, 0, 1, 0), (1001, 2024-03-05, 0, 1, 1), -- 同一天既迟到又早退 (1001, 2024-03-29, 0, 0, 0), (1001, 2024-03-31, 0, 0, 0), -- 月末最后一天专治 边界 (1002, 2024-03-01, 1, 0, 0), -- 整天缺勤 (1004, 2024-03-29, 0, 1, 0);注意1004这个员工3 月 29 日才入职没几天考勤记录只有一条员工工作信息表里也没有他的职称。这种人最容易在 INNER JOIN 的视图里凭空消失专门造一个出来一眼就能看出视图写得对不对。调动数据也照这个思路造至少一条「同一员工调过两次」的记录用来验证最新调动那条子查询再来一条「调出和调入在同一年」的记录用来验证按年统计会不会重复计数。-- 张三调过两次验证「只取最新一条」 INSERT INTO staff_transfer (staff_id, old_dep_id, new_dep_id, old_time, new_time) VALUES (1001, 101, 102, 2023-06-01, 2023-06-01), (1001, 102, 103, 2024-03-01, 2024-03-01);. . .后面如果要批量造几百条别手写。用一段循环脚本按日期递推生成考勤表一天一条一个月 22 个工作日五个员工就是 110 行两分钟的事。手敲 110 行INSERT的后果是必然打错一个日期然后花半小时怀疑 SQL 写错了。4.3 调动业务一个事务把三张表绑在一起员工调动至少要动两张表往调动流水里插一条把员工信息表的当前部门改掉。这两步中间任何一步失败数据库就处于「流水记了但人还在原部门」或者「人已经到新部门但查不到调动记录」的状态。演示的时候被问一句「这条流水怎么对不上」很难圆。START TRANSACTION; -- 1. 锁住这行顺便确认当前部门确实是原部门 SELECT department_id FROM staff_info WHERE staff_id 1001 FOR UPDATE; -- 应用层拿到当前部门后做校验不等于 old_dep_id 就直接回滚 -- 2. 写调动流水只追加 INSERT INTO staff_transfer (staff_id, old_dep_id, new_dep_id, old_time, new_time) VALUES (1001, 102, 103, 2024-03-01, 2024-03-01); -- 3. 更新当前部门 UPDATE staff_info SET department_id 103 WHERE staff_id 1001; COMMIT;FOR UPDATE解决的是并发问题两个管理员同时给同一个人办调动不加锁的话两边都读到102各自插入一条「从 102 调出」的流水最后这个人的调动历史里就有两条相同起点的记录。教学演示碰不上并发但这段代码写进报告里答辩时是加分项。参数说明里有两个点要盯住old_dep_id必须来自第 1 步查出来的实时值不能让前端传前端传的值可能是十秒前的页面快照new_dep_id和old_dep_id相等时应该在校验层拦掉虽然表上有ck_transfer_diff兜底但让用户看到数据库抛出来的约束错误体验很差。失败路径也要想好。第 2 步成功、第 3 步失败的情况靠ROLLBACK回滚如果是应用层代码里COMMIT之前抛异常一定要在catch里显式回滚别指望连接池帮你收拾——连接被归还到池子里时事务还开着下一个人借到这条连接会莫名其妙地看到上一个人的未提交数据。5. 排查实录五个把库跑翻的坑现象、原因和改法这一章是我在帮人看课程设计时反复遇到的五个问题几乎每一届都会中招至少两个。统一按「现象 → 原因 → 解决」写方便对着自己的库一条条对。5.1 视图查出来行数翻倍现象SELECT COUNT(*) FROM 员工个人信息视图查出 12 行但员工表里明明只有 10 个人。原因视图里INNER JOIN staff_transfer用的是全量调动流水。调过两次的人会在结果里出现两行调过三次就三行。更麻烦的是这种翻倍不带任何提示行数看着像「多了几个人」而不是「同一个人重复了」。解决调动表只取最新一条用相关子查询t.new_time (SELECT MAX(...) ...)或者用窗口函数ROW_NUMBER() OVER (PARTITION BY staff_id ORDER BY new_time DESC) 1。判断视图有没有这个毛病最快的办法是拿主键去GROUP BY ... HAVING COUNT(*) 1跑一遍能出结果就是有问题。5.2 原部门名称串成了新部门现象调动记录里一个人从人事部调到技术部视图显示「原部门技术部新部门技术部」。原因视图里两个部门外键指向同一张部门表但只 JOIN 了一次第二个字段复用了第一个 JOIN 的结果。写的时候不报错因为列名和别名都合法只是语义错位。解决给部门表起两个别名分别 JOINd_old对old_dep_idd_new对new_dep_id。写完先找一条「调出部门和调入部门不同」的记录验证如果两个名称一样立刻回去查 JOIN。5.3 按月统计总少一两条现象三月份的迟到统计手工数是 9 次SQL 跑出来是 7 次。原因WHERE sign_date BETWEEN 2024-03-01 AND 2024-03-31。如果sign_date是带时分秒的类型3 月 31 日 08:30 那条记录大于2024-03-31 00:00:00被排除在外。或者反过来用了 2024-03-31 23:59:59漏掉了 23:59:59.5 这种毫秒级记录。解决一律用半开区间 起始日 AND 次月首日。这条规则我写在团队的 SQL 规范第一条因为它不止影响统计还影响分页和增量同步。5.4 没有调动的部门在报表里消失现象按年统计各部门调入调出财务部整年没人动报表上直接没有这一行。原因FROM staff_transfer JOIN department_info以流水表为驱动表。一个部门没有流水JOIN 之后自然没有它的行。解决以部门表为驱动表两张统计子查询 LEFT JOIN 上去外面套COALESCE(..., 0)。同理统计「每个员工的请假次数」时要以员工表为基准否则从没请过假的人全部消失——而这类人恰恰是 HR 最想看到的。5.5 工资单差一分钱现象五条工资明细手工加是 38650.27SUM(real_salary)出来 38650.26。原因金额字段用了FLOAT。浮点数在二进制里表示不了 0.01累加过程误差不断累积行数越多差得越离谱。解决金额一律用DECIMAL(10,2)参与运算的中间量也保持定点数。已经建好表的加一列DECIMAL再UPDATE过去别指望ROUND()事后补救——ROUND只能修掉末位累积误差的方向是不确定的有时候四舍五入反而错得更远。6. 二次开发把这套表改成能演示的人事系统课程设计交完就扔太浪费。这套表结构离「能演示的小系统」其实只差三件事我按投入产出比排个序。第一件把视图当接口用别把 SQL 散在界面代码里。前端要展示员工列表就查v_staff_profile要展示调动历史就查v_transfer_detail。好处是字段改名、加计算列这类需求只改视图界面不用动。改造点很小但能省掉后面大量的重复劳动。第二件把「删除员工」改成改状态。表里已经有emp_status界面上的删除按钮执行的是UPDATE staff_info SET emp_status 离职 WHERE staff_id ?列表默认只查在职。同时把所有外键的删除策略设成RESTRICT而不是CASCADE——CASCADE意味着删一个员工他的考勤、工资、调动记录全跟着没了人事数据这么干是灾难。第三件给统计加一条对账 SQL改完之后跑一遍。这是我认为最值钱的一条习惯-- 对账汇总口径的迟到人数必须等于明细口径的行数 SELECT (SELECT COUNT(*) FROM staff_signin WHERE sign_date 2024-03-15 AND late 1) AS 明细迟到数, (SELECT COALESCE(SUM(迟到人数), 0) FROM v_dept_daily_attend WHERE 日期 2024-03-15) AS 汇总迟到数;两个数字对不上说明分组或者 JOIN 有问题不用等用户来投诉。这个套路可以套到所有统计上调出人数对staff_transfer里old_time落在当年的行数实发工资总额对明细相加。常见改造点整理成一张表照着做就行改造点动哪张表/对象验证方法当前部门唯一写入口staff_info.department_id改完部门后查流水是否同步离职不物理删staff_info.emp_status离职员工仍能查到历史工资工资按月留存staff_salary.salary_month同员工同月重复插入应失败统计口径统一视图 对账 SQL汇总数与明细数一致模糊查询防注入应用层绑定参数输入 OR 11查不出全表如果还想往上加下一步是给staff_transfer和staff_salary加操作日志表记录谁在什么时候改了什么。触发器能实现但教学环境里我更推荐在应用层写逻辑看得见调试也方便。从那以后我每次做完统计类的需求都会先写一条这样的对账 SQL再去动界面——数据库不会骗人骗人的永远是那句「我这条 SQL 应该没问题」。希望帮到你。本文还有配套的精品资源点击获取