人事管理数据库课程设计：从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 '1'='1一贴，整张表的数据就出来了。用绑定参数，一行代码的事。

-- 索引：只为真正会被 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 的结果。写的时候不报错，因为列名和别名都合法，只是语义错位。

解决：给部门表起两个别名分别 JOIN，d_old对old_dep_id，d_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.27，SUM(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 '1'='1`查不出全表