第37章实战项目：企业员工管理系统

本章目标

完整设计企业员工管理系统的数据库架构
掌握自引用外键（层级部门）、时间维度数据（考勤、薪资历史）的设计模式
练习编写 30+ 个覆盖多种业务场景的 SQL 查询
掌握存储过程的设计和事务应用
掌握触发器的创建和使用场景
综合运用窗口函数、CTE、子查询等高级 SQL 技巧

前置知识

熟练掌握 CREATE TABLE、INSERT、SELECT、UPDATE、DELETE
掌握 JOIN、子查询、聚合函数、窗口函数
了解存储过程、触发器的基本语法
理解事务 ACID 和隔离级别

37.1 业务需求分析

37.1.1 功能模块

1. 组织架构管理
   - 层级部门（总部 → 分公司 → 部门 → 小组）
   - 部门编制和预算管理

2. 员工管理
   - 员工基本信息（个人资料、联系方式、紧急联系人）
   - 职位体系（P1-P8 级别，薪资范围）
   - 入离职管理

3. 岗位变动管理
   - 晋升、调岗、降级记录
   - 岗位历史轨迹

4. 薪资管理
   - 薪资历史记录（支持补发、调薪）
   - 不允许同一时间段的重叠薪资记录

5. 考勤管理
   - 每日打卡记录
   - 迟到、早退、请假统计

37.1.2 ER 图（文字描述）

departments ──1:N──> departments (self-ref: parent_dept_id)
departments ──1:N──> employees
positions ──1:N──> employees
employees ──1:N──> salaries
employees ──1:N──> job_history
employees ──1:N──> attendance

37.2 完整表结构设计

37.2.1 部门表 (departments)

sql

CREATE TABLE departments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  name VARCHAR(100) NOT NULL COMMENT '部门名称',
  parent_dept_id INT UNSIGNED DEFAULT NULL COMMENT '上级部门ID（NULL=顶级部门）',
  manager_id INT UNSIGNED DEFAULT NULL COMMENT '部门负责人ID',
  budget DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '年度预算',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=正常 0=撤销',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_name (name),
  KEY idx_parent (parent_dept_id),
  CONSTRAINT fk_dept_parent FOREIGN KEY (parent_dept_id)
    REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='部门表';

37.2.2 职位表 (positions)

sql

CREATE TABLE positions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '职位ID',
  title VARCHAR(100) NOT NULL COMMENT '职位名称',
  level TINYINT UNSIGNED NOT NULL COMMENT '职级（1-8）',
  base_salary_min DECIMAL(10,2) NOT NULL COMMENT '薪资下限',
  base_salary_max DECIMAL(10,2) NOT NULL COMMENT '薪资上限',
  description VARCHAR(500) DEFAULT '' COMMENT '职位描述',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='职位表';

37.2.3 员工表 (employees)

sql

CREATE TABLE employees (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  emp_no VARCHAR(20) NOT NULL COMMENT '工号（唯一）',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  gender ENUM('M','F') NOT NULL COMMENT '性别',
  birth_date DATE NOT NULL COMMENT '出生日期',
  hire_date DATE NOT NULL COMMENT '入职日期',
  dept_id INT UNSIGNED NOT NULL COMMENT '所属部门ID',
  position_id INT UNSIGNED NOT NULL COMMENT '职位ID',
  phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
  email VARCHAR(254) NOT NULL COMMENT '邮箱',
  address TEXT COMMENT '住址',
  emergency_contact VARCHAR(50) DEFAULT '' COMMENT '紧急联系人',
  emergency_phone VARCHAR(20) DEFAULT '' COMMENT '紧急联系人电话',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=在职 2=离职 3=停职',
  leave_date DATE DEFAULT NULL COMMENT '离职日期',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_emp_no (emp_no),
  UNIQUE KEY uk_email (email),
  KEY idx_dept_id (dept_id),
  KEY idx_position_id (position_id),
  KEY idx_name (name),
  KEY idx_hire_date (hire_date),
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id),
  CONSTRAINT fk_emp_position FOREIGN KEY (position_id) REFERENCES positions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工表';

37.2.4 薪资表 (salaries)

sql

CREATE TABLE salaries (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '薪资记录ID',
  emp_id INT UNSIGNED NOT NULL COMMENT '员工ID',
  amount DECIMAL(10,2) NOT NULL COMMENT '薪资金额',
  effective_from DATE NOT NULL COMMENT '生效日期',
  effective_to DATE DEFAULT NULL COMMENT '失效日期（NULL=当前生效中）',
  reason VARCHAR(200) DEFAULT '' COMMENT '调薪原因',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_emp_effective (emp_id, effective_from),
  KEY idx_emp_id (emp_id),
  CONSTRAINT fk_salary_emp FOREIGN KEY (emp_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='薪资表';

-- 添加约束：同一员工的薪资时间段不能重叠
-- 注意：MySQL 不支持 CHECK 约束中的子查询，需要在应用层或触发器实现
-- 这里是示例性的注释，实际项目在应用层保证

37.2.5 岗位变动记录表 (job_history)

sql

CREATE TABLE job_history (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '变动记录ID',
  emp_id INT UNSIGNED NOT NULL COMMENT '员工ID',
  old_dept_id INT UNSIGNED DEFAULT NULL COMMENT '原部门ID',
  new_dept_id INT UNSIGNED DEFAULT NULL COMMENT '新部门ID',
  old_position_id INT UNSIGNED DEFAULT NULL COMMENT '原职位ID',
  new_position_id INT UNSIGNED DEFAULT NULL COMMENT '新职位ID',
  change_type ENUM('promotion','transfer','demotion','probation_pass','resign') NOT NULL COMMENT '变动类型',
  change_date DATE NOT NULL COMMENT '变动日期',
  remark TEXT COMMENT '备注',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_emp_date (emp_id, change_date),
  CONSTRAINT fk_job_emp FOREIGN KEY (emp_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位变动记录表';

37.2.6 考勤表 (attendance)

sql

CREATE TABLE attendance (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '考勤记录ID',
  emp_id INT UNSIGNED NOT NULL COMMENT '员工ID',
  att_date DATE NOT NULL COMMENT '考勤日期',
  clock_in DATETIME DEFAULT NULL COMMENT '上班打卡时间',
  clock_out DATETIME DEFAULT NULL COMMENT '下班打卡时间',
  status ENUM('normal','late','early','absent','leave') NOT NULL DEFAULT 'normal' COMMENT '考勤状态',
  leave_type ENUM('annual','sick','personal','marriage','bereavement','other') DEFAULT NULL COMMENT '请假类型',
  leave_hours DECIMAL(4,1) DEFAULT 0.0 COMMENT '请假时数',
  remark VARCHAR(200) DEFAULT '' COMMENT '备注',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_emp_date (emp_id, att_date),
  KEY idx_emp_date (emp_id, att_date),
  KEY idx_att_date (att_date),
  CONSTRAINT fk_att_emp FOREIGN KEY (emp_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考勤表';

37.3 示例数据

sql

-- 插入部门（层级结构）
INSERT INTO departments (id, name, parent_dept_id, budget) VALUES
(1, '集团总部', NULL, 100000000.00),
(2, '技术中心', 1, 50000000.00),
(3, '产品中心', 1, 30000000.00),
(4, '后端开发部', 2, 20000000.00),
(5, '前端开发部', 2, 15000000.00),
(6, '测试部', 2, 10000000.00),
(7, '产品设计部', 3, 15000000.00),
(8, '市场营销部', 1, 25000000.00);

-- 插入职位
INSERT INTO positions (id, title, level, base_salary_min, base_salary_max, description) VALUES
(1, '初级工程师', 1, 8000, 12000, 'P1级别'),
(2, '中级工程师', 2, 12000, 18000, 'P2级别'),
(3, '高级工程师', 3, 18000, 25000, 'P3级别'),
(4, '资深工程师', 4, 25000, 35000, 'P4级别'),
(5, '技术专家', 5, 35000, 50000, 'P5级别'),
(6, '技术经理', 6, 40000, 60000, 'M1级别'),
(7, '高级经理', 7, 50000, 80000, 'M2级别'),
(8, '总监', 8, 60000, 100000, 'M3级别'),
(9, '产品经理', 5, 30000, 50000, 'P5-产品线'),
(10, '测试工程师', 2, 10000, 18000, 'QA级别');

-- 插入员工
INSERT INTO employees (id, emp_no, name, gender, birth_date, hire_date, dept_id, position_id, phone, email) VALUES
(1, 'E2020001', '张建国', 'M', '1980-05-15', '2020-01-01', 1, 8, '13800000001', 'zhangjiang@company.com'),
(2, 'E2020002', '李大明', 'M', '1985-08-22', '2020-03-15', 2, 7, '13800000002', 'lidaming@company.com'),
(3, 'E2020003', '王小红', 'F', '1990-12-10', '2021-06-01', 4, 4, '13800000003', 'wangxiaohong@company.com'),
(4, 'E2021004', '赵铁柱', 'M', '1992-03-08', '2021-07-01', 4, 3, '13800000004', 'zhaotiezhu@company.com'),
(5, 'E2021005', '钱美丽', 'F', '1993-07-20', '2021-08-15', 5, 3, '13800000005', 'qianmeili@company.com'),
(6, 'E2021006', '孙浩然', 'M', '1988-11-03', '2020-06-15', 4, 5, '13800000006', 'sunhaoran@company.com'),
(7, 'E2022007', '周小燕', 'F', '1995-02-14', '2022-03-01', 4, 1, '13800000007', 'zhouxiaoyan@company.com'),
(8, 'E2022008', '吴大伟', 'M', '1994-09-28', '2022-04-01', 5, 2, '13800000008', 'wudawei@company.com'),
(9, 'E2022009', '郑晓霞', 'F', '1991-06-30', '2022-05-15', 6, 4, '13800000009', 'zhengxiaoxia@company.com'),
(10, 'E2022010', '冯小刚', 'M', '1996-01-20', '2022-07-01', 4, 2, '13800000010', 'fengxiaogang@company.com'),
(11, 'E2023011', '陈美丽', 'F', '1997-04-05', '2023-01-10', 7, 9, '13800000011', 'chenmeili@company.com'),
(12, 'E2023012', '褚大鹏', 'M', '1995-08-15', '2023-02-20', 4, 2, '13800000012', 'chudapeng@company.com'),
(13, 'E2023013', '卫中华', 'M', '1993-10-25', '2023-03-01', 5, 2, '13800000013', 'weizhonghua@company.com'),
(14, 'E2023014', '蒋兰兰', 'F', '1994-12-12', '2023-04-15', 8, 5, '13800000014', 'jianglanlan@company.com'),
(15, 'E2024015', '沈明哲', 'M', '1998-05-08', '2024-01-05', 4, 1, '13800000015', 'shenmingzhe@company.com'),
(16, 'E2024016', '韩晶', 'F', '1996-07-18', '2024-02-10', 6, 2, '13800000016', 'hanjing@company.com'),
(17, 'E2024017', '杨帆', 'M', '1997-09-22', '2024-03-15', 5, 1, '13800000017', 'yangfan@company.com'),
(18, 'E2024018', '朱小红', 'F', '1995-11-30', '2024-04-01', 8, 2, '13800000018', 'zhuxiaohong@company.com'),
(19, 'E2024019', '秦浩然', 'M', '1993-01-08', '2024-05-10', 7, 2, '13800000019', 'qinhaoran@company.com'),
(20, 'E2024020', '尤海燕', 'F', '1999-03-16', '2024-06-01', 4, 1, '13800000020', 'youhaiyan@company.com');

-- 插入薪资记录（每人至少2条，展示薪资变化）
INSERT INTO salaries (emp_id, amount, effective_from, effective_to, reason) VALUES
-- 张建国：总监
(1, 80000.00, '2020-01-01', '2021-12-31', '入职定薪'),
(1, 90000.00, '2022-01-01', '2023-12-31', '年度调薪'),
(1, 100000.00, '2024-01-01', NULL, '年度调薪'),
-- 李大明：高级经理
(2, 60000.00, '2020-03-15', '2021-12-31', '入职定薪'),
(2, 70000.00, '2022-01-01', '2023-12-31', '年度调薪'),
(2, 78000.00, '2024-01-01', NULL, '年度调薪'),
-- 王小红：资深工程师
(3, 18000.00, '2021-06-01', '2022-06-30', '入职定薪'),
(3, 22000.00, '2022-07-01', '2023-12-31', '晋升调薪'),
(3, 28000.00, '2024-01-01', NULL, '年度调薪'),
-- 赵铁柱：高级工程师
(4, 15000.00, '2021-07-01', '2022-06-30', '入职定薪'),
(4, 18000.00, '2022-07-01', '2023-12-31', '年度调薪'),
(4, 22000.00, '2024-01-01', NULL, '晋升调薪'),
-- 后续员工省略，保持示例简洁...
(5, 18000.00, '2021-08-15', '2023-06-30', '入职定薪'),
(5, 22000.00, '2023-07-01', NULL, '年度调薪'),
(6, 35000.00, '2020-06-15', '2022-12-31', '入职定薪'),
(6, 45000.00, '2023-01-01', NULL, '年度调薪'),
(7, 9000.00, '2022-03-01', '2023-06-30', '入职定薪'),
(7, 11000.00, '2023-07-01', NULL, '转正调薪'),
(8, 12000.00, '2022-04-01', '2023-12-31', '入职定薪'),
(8, 15000.00, '2024-01-01', NULL, '年度调薪'),
(9, 25000.00, '2022-05-15', '2023-12-31', '入职定薪'),
(9, 30000.00, '2024-01-01', NULL, '年度调薪'),
(10, 13000.00, '2022-07-01', '2023-12-31', '入职定薪'),
(10, 16000.00, '2024-01-01', NULL, '年度调薪');

-- 插入岗位变动记录
INSERT INTO job_history (emp_id, old_dept_id, new_dept_id, old_position_id, new_position_id, change_type, change_date, remark) VALUES
(3, 4, 4, 3, 4, 'promotion', '2022-07-01', '从高级工程师晋升为资深工程师'),
(4, 4, 4, 3, 3, 'probation_pass', '2021-10-01', '试用期转正'),
(4, 4, 4, 3, 3, 'promotion', '2024-01-01', '晋升为高级工程师'),
(7, 4, 4, 1, 1, 'probation_pass', '2022-06-01', '试用期转正');

-- 插入考勤记录（2024年6月部分数据）
INSERT INTO attendance (emp_id, att_date, clock_in, clock_out, status) VALUES
(3, '2024-06-03', '2024-06-03 08:55:00', '2024-06-03 18:05:00', 'normal'),
(3, '2024-06-04', '2024-06-04 09:15:00', '2024-06-04 18:00:00', 'late'),
(3, '2024-06-05', '2024-06-05 08:50:00', '2024-06-05 17:30:00', 'early'),
(4, '2024-06-03', '2024-06-03 09:00:00', '2024-06-03 18:00:00', 'normal'),
(4, '2024-06-04', '2024-06-04 08:45:00', '2024-06-04 18:10:00', 'normal'),
(4, '2024-06-05', NULL, NULL, 'absent', 'sick', 8.0, '感冒请假'),
(7, '2024-06-03', '2024-06-03 08:30:00', '2024-06-03 18:00:00', 'normal'),
(7, '2024-06-04', '2024-06-04 08:45:00', '2024-06-04 18:15:00', 'normal'),
(7, '2024-06-05', '2024-06-05 08:55:00', '2024-06-05 18:00:00', 'normal'),
(10, '2024-06-03', '2024-06-03 08:50:00', '2024-06-03 18:00:00', 'normal'),
(10, '2024-06-04', '2024-06-04 09:10:00', '2024-06-04 18:00:00', 'late'),
(10, '2024-06-05', '2024-06-05 08:55:00', '2024-06-05 18:00:00', 'normal');

37.4 业务 SQL 查询（30+ 示例）

37.4.1 基础查询

sql

-- 查询 1：所有在职员工基本信息（含部门和职位名）
SELECT e.emp_no, e.name, e.gender, e.hire_date,
       d.name AS dept_name, p.title AS position_title
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN positions p ON e.position_id = p.id
WHERE e.status = 1
ORDER BY e.hire_date;

-- 查询 2：部门平均薪资排名
SELECT d.name AS dept_name,
       ROUND(AVG(s.amount), 2) AS avg_salary,
       COUNT(DISTINCT e.id) AS headcount
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
WHERE e.status = 1
GROUP BY d.id, d.name
ORDER BY avg_salary DESC;

-- 查询 3：部门薪资高于部门平均的员工
WITH dept_avg AS (
  SELECT dept_id, AVG(s2.amount) AS avg_sal
  FROM employees e2
  JOIN salaries s2 ON e2.id = s2.emp_id AND s2.effective_to IS NULL
  WHERE e2.status = 1
  GROUP BY dept_id
)
SELECT e.name, p.title, s.amount, d.name AS dept_name,
       ROUND(da.avg_sal, 2) AS dept_avg_salary
FROM employees e
JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
JOIN departments d ON e.dept_id = d.id
JOIN positions p ON e.position_id = p.id
JOIN dept_avg da ON e.dept_id = da.dept_id
WHERE s.amount > da.avg_sal AND e.status = 1
ORDER BY s.amount - da.avg_sal DESC;

-- 查询 4：Top 5 最高薪资员工
SELECT e.name, d.name AS dept_name, p.title, s.amount
FROM employees e
JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
JOIN departments d ON e.dept_id = d.id
JOIN positions p ON e.position_id = p.id
WHERE e.status = 1
ORDER BY s.amount DESC
LIMIT 5;

-- 查询 5：员工入职年限（到今日）
SELECT e.emp_no, e.name, e.hire_date,
       TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS years_of_service,
       TIMESTAMPDIFF(MONTH, e.hire_date, CURDATE()) % 12 AS extra_months
FROM employees e
WHERE e.status = 1
ORDER BY years_of_service DESC;

37.4.2 统计与聚合

sql

-- 查询 6：各部门人数统计
SELECT d.name AS dept_name,
       COUNT(*) AS headcount,
       SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END) AS male_count,
       SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id AND e.status = 1
GROUP BY d.id, d.name
ORDER BY headcount DESC;

-- 查询 7：每个员工的最新薪资
SELECT e.name, s.amount, s.effective_from
FROM employees e
JOIN salaries s ON e.id = s.emp_id
WHERE s.effective_to IS NULL
ORDER BY s.amount DESC;

-- 查询 8：员工薪资增长幅度（入职薪资 vs 当前薪资）
SELECT e.name,
       first_sal.amount AS first_salary,
       curr_sal.amount AS current_salary,
       curr_sal.amount - first_sal.amount AS increase,
       ROUND((curr_sal.amount - first_sal.amount) / first_sal.amount * 100, 2) AS increase_pct
FROM employees e
JOIN salaries first_sal ON e.id = first_sal.emp_id
JOIN salaries curr_sal ON e.id = curr_sal.emp_id AND curr_sal.effective_to IS NULL
WHERE first_sal.effective_from = (
  SELECT MIN(effective_from) FROM salaries WHERE emp_id = e.id
) AND e.status = 1
ORDER BY increase_pct DESC;

-- 查询 9：部门预算利用率
SELECT d.name AS dept_name,
       d.budget AS annual_budget,
       COALESCE(SUM(s.amount) * 12, 0) AS annual_salary_cost,
       ROUND(COALESCE(SUM(s.amount) * 12, 0) / d.budget * 100, 2) AS budget_utilization_pct
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id AND e.status = 1
LEFT JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
GROUP BY d.id, d.name, d.budget
ORDER BY budget_utilization_pct DESC;

-- 查询 10：一年内发生 2 次以上岗位变动的员工
SELECT e.name,
       COUNT(*) AS change_count,
       GROUP_CONCAT(jh.change_type ORDER BY jh.change_date) AS change_types
FROM employees e
JOIN job_history jh ON e.id = jh.emp_id
WHERE jh.change_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY e.id, e.name
HAVING COUNT(*) >= 2;

37.4.3 递归与层级查询

sql

-- 查询 11：组织架构层级（递归 CTE）
WITH RECURSIVE dept_tree AS (
  -- 锚点：顶级部门
  SELECT id, name, parent_dept_id, 0 AS depth, CAST(name AS CHAR(500)) AS path
  FROM departments
  WHERE parent_dept_id IS NULL

  UNION ALL

  -- 递归：下级部门
  SELECT d.id, d.name, d.parent_dept_id,
         dt.depth + 1,
         CONCAT(dt.path, ' > ', d.name)
  FROM departments d
  JOIN dept_tree dt ON d.parent_dept_id = dt.id
)
SELECT id,
       CONCAT(REPEAT('  ', depth), name) AS dept_name_indented,
       depth,
       path
FROM dept_tree
ORDER BY path;

-- 查询 12：某员工的所有下属（通过部门层级找下属，假设 manager_id 关系）
-- 先给 departments 设置 manager_id
UPDATE departments SET manager_id = 2 WHERE id = 4;  -- 李大明管理后端开发部
UPDATE departments SET manager_id = 3 WHERE id = 4;  -- 王小红管理后端开发部（示例）

-- 找某人的所有下属
SELECT e.name, d.name AS dept_name, p.title
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN positions p ON e.position_id = p.id
WHERE d.id IN (
  WITH RECURSIVE sub_depts AS (
    SELECT id FROM departments WHERE manager_id = 3
    UNION ALL
    SELECT d.id FROM departments d
    JOIN sub_depts sd ON d.parent_dept_id = sd.id
  )
  SELECT id FROM sub_depts
) AND e.id != 3
ORDER BY d.name, e.name;

37.4.4 考勤分析

sql

-- 查询 13：月度考勤汇总
SELECT e.emp_no, e.name,
       COUNT(*) AS total_days,
       SUM(CASE WHEN a.status = 'normal' THEN 1 ELSE 0 END) AS normal_days,
       SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) AS late_days,
       SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) AS early_days,
       SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) AS absent_days,
       SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) AS leave_days,
       COALESCE(SUM(a.leave_hours), 0) AS total_leave_hours
FROM employees e
JOIN attendance a ON e.id = a.emp_id
WHERE a.att_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY e.id, e.emp_no, e.name
ORDER BY late_days DESC;

-- 查询 14：某月全勤员工
SELECT e.name
FROM employees e
JOIN attendance a ON e.id = a.emp_id
WHERE a.att_date BETWEEN '2024-06-01' AND '2024-06-30'
  AND e.status = 1
GROUP BY e.id, e.name
HAVING SUM(CASE WHEN a.status IN ('late','early','absent') THEN 1 ELSE 0 END) = 0;

-- 查询 15：各部门平均请假时数
SELECT d.name AS dept_name,
       ROUND(AVG(COALESCE(dept_leave.total_hours, 0)), 1) AS avg_leave_hours_per_person
FROM departments d
LEFT JOIN (
  SELECT e.dept_id, SUM(a.leave_hours) AS total_hours
  FROM employees e
  JOIN attendance a ON e.id = a.emp_id
  WHERE a.att_date BETWEEN '2024-06-01' AND '2024-06-30'
  GROUP BY e.id, e.dept_id
) AS dept_leave ON d.id = dept_leave.dept_id
GROUP BY d.id, d.name;

-- 查询 16：各月出勤率排行
SELECT e.name,
       COUNT(*) AS total_workdays,
       SUM(CASE WHEN a.status NOT IN ('absent','leave') THEN 1 ELSE 0 END) AS present_days,
       ROUND(SUM(CASE WHEN a.status NOT IN ('absent','leave') THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS attendance_rate
FROM employees e
JOIN attendance a ON e.id = a.emp_id
WHERE a.att_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY e.id, e.name
ORDER BY attendance_rate DESC;

37.4.5 高级分析

sql

-- 查询 17：各部门职级分布
SELECT d.name AS dept_name,
       p.level AS position_level,
       p.title,
       COUNT(*) AS count
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN positions p ON e.position_id = p.id
WHERE e.status = 1
GROUP BY d.id, d.name, p.level, p.title
ORDER BY d.name, p.level;

-- 查询 18：即将退休员工（55岁以上）
SELECT e.name, e.gender,
       TIMESTAMPDIFF(YEAR, e.birth_date, CURDATE()) AS age,
       e.hire_date, d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE TIMESTAMPDIFF(YEAR, e.birth_date, CURDATE()) >= 55
  AND e.status = 1;

-- 查询 19：近 3 个月入职的新员工及其部门最资深员工
WITH new_hires AS (
  SELECT e.id, e.name, e.dept_id
  FROM employees e
  WHERE e.hire_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
    AND e.status = 1
),
senior_in_dept AS (
  SELECT e.dept_id, e.name AS mentor_name, e.hire_date,
         ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.hire_date) AS rn
  FROM employees e
  WHERE e.status = 1
)
SELECT nh.name AS new_employee,
       d.name AS dept_name,
       sd.mentor_name AS mentor
FROM new_hires nh
JOIN departments d ON nh.dept_id = d.id
JOIN senior_in_dept sd ON nh.dept_id = sd.dept_id AND sd.rn = 1;

-- 查询 20：薪资分布直方图
SELECT
  CASE
    WHEN s.amount < 10000 THEN '0-1万'
    WHEN s.amount < 20000 THEN '1-2万'
    WHEN s.amount < 30000 THEN '2-3万'
    WHEN s.amount < 50000 THEN '3-5万'
    WHEN s.amount < 80000 THEN '5-8万'
    ELSE '8万以上'
  END AS salary_range,
  COUNT(*) AS count
FROM employees e
JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
WHERE e.status = 1
GROUP BY salary_range
ORDER BY MIN(s.amount);

-- 查询 21：跨部门调动分析
SELECT e.name,
       jh.old_dept_id, d1.name AS old_dept,
       jh.new_dept_id, d2.name AS new_dept,
       jh.change_date, jh.remark
FROM job_history jh
JOIN employees e ON jh.emp_id = e.id
LEFT JOIN departments d1 ON jh.old_dept_id = d1.id
LEFT JOIN departments d2 ON jh.new_dept_id = d2.id
WHERE jh.change_type = 'transfer'
  AND jh.old_dept_id != jh.new_dept_id;

-- 查询 22：管理者管辖范围（管多少人）
SELECT m.name AS manager_name,
       COUNT(e.id) AS direct_reports
FROM employees m
JOIN employees e ON e.dept_id = m.dept_id AND e.id != m.id
WHERE m.position_id IN (6, 7, 8)  -- 管理岗位
  AND e.status = 1 AND m.status = 1
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

-- 查询 23：各部门性别比例
SELECT d.name AS dept_name,
       COUNT(*) AS total,
       SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END) AS male,
       SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) AS female,
       ROUND(SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS female_pct
FROM departments d
JOIN employees e ON d.id = e.dept_id AND e.status = 1
GROUP BY d.id, d.name;

-- 查询 24：平均首次晋升年限
WITH first_hire AS (
  SELECT id, hire_date FROM employees
),
first_promotion AS (
  SELECT emp_id, MIN(change_date) AS first_promotion_date
  FROM job_history
  WHERE change_type = 'promotion'
  GROUP BY emp_id
)
SELECT AVG(TIMESTAMPDIFF(MONTH, fh.hire_date, fp.first_promotion_date)) / 12.0 AS avg_years_to_first_promotion
FROM first_hire fh
JOIN first_promotion fp ON fh.id = fp.emp_id;

-- 查询 25：当月生日员工列表
SELECT e.name, e.birth_date, d.name AS dept_name,
       TIMESTAMPDIFF(YEAR, e.birth_date, CURDATE()) AS turning_age
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE MONTH(e.birth_date) = MONTH(CURDATE())
  AND e.status = 1
ORDER BY DAY(e.birth_date);

-- 查询 26：年度人数增长
SELECT YEAR(hire_date) AS year,
       COUNT(*) AS hires,
       SUM(COUNT(*)) OVER (ORDER BY YEAR(hire_date)) AS cumulative_headcount
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY year;

-- 查询 27：各部门最频繁的变动类型
SELECT d.name AS dept_name,
       jh.change_type,
       COUNT(*) AS count
FROM job_history jh
JOIN employees e ON jh.emp_id = e.id
JOIN departments d ON e.dept_id = d.id
GROUP BY d.id, d.name, jh.change_type
ORDER BY d.name, count DESC;

-- 查询 28：本月没有考勤记录的员工
SELECT e.name, d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.status = 1
  AND e.id NOT IN (
    SELECT DISTINCT emp_id FROM attendance
    WHERE att_date BETWEEN '2024-06-01' AND '2024-06-30'
  );

-- 查询 29：部门内薪资排名（窗口函数 ROW_NUMBER）
SELECT d.name AS dept_name,
       e.name AS emp_name,
       s.amount,
       ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY s.amount DESC) AS salary_rank_in_dept,
       RANK() OVER (PARTITION BY e.dept_id ORDER BY s.amount DESC) AS salary_rank_dense
FROM employees e
JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
JOIN departments d ON e.dept_id = d.id
WHERE e.status = 1
ORDER BY d.name, salary_rank_in_dept;

-- 查询 30：完整员工360视图
SELECT e.emp_no, e.name, e.gender,
       TIMESTAMPDIFF(YEAR, e.birth_date, CURDATE()) AS age,
       TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS tenure_years,
       d.name AS dept_name, p.title AS position,
       s.amount AS current_salary,
       (SELECT COUNT(*) FROM job_history WHERE emp_id = e.id) AS job_changes,
       (SELECT COUNT(*) FROM attendance WHERE emp_id = e.id
        AND att_date BETWEEN '2024-06-01' AND '2024-06-30'
        AND status IN ('late','early','absent')) AS bad_attendance_days
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN positions p ON e.position_id = p.id
JOIN salaries s ON e.id = s.emp_id AND s.effective_to IS NULL
WHERE e.status = 1
ORDER BY e.emp_no;

37.5 存储过程

37.5.1 员工调岗

sql

DELIMITER //

CREATE PROCEDURE sp_employee_transfer(
  IN p_emp_id INT UNSIGNED,
  IN p_new_dept_id INT UNSIGNED,
  IN p_new_position_id INT UNSIGNED,
  IN p_change_date DATE,
  IN p_remark VARCHAR(500)
)
BEGIN
  DECLARE v_old_dept_id INT UNSIGNED;
  DECLARE v_old_position_id INT UNSIGNED;
  DECLARE v_change_type ENUM('promotion','transfer','demotion','probation_pass','resign');
  DECLARE v_old_level TINYINT;
  DECLARE v_new_level TINYINT;

  -- 错误处理
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转岗失败，事务已回滚';
  END;

  START TRANSACTION;

  -- 获取当前部门和职位
  SELECT dept_id, position_id INTO v_old_dept_id, v_old_position_id
  FROM employees WHERE id = p_emp_id FOR UPDATE;

  -- 获取职级
  SELECT level INTO v_old_level FROM positions WHERE id = v_old_position_id;
  SELECT level INTO v_new_level FROM positions WHERE id = p_new_position_id;

  -- 判断变动类型
  IF v_old_position_id != p_new_position_id THEN
    IF v_new_level > v_old_level THEN
      SET v_change_type = 'promotion';
    ELSEIF v_new_level < v_old_level THEN
      SET v_change_type = 'demotion';
    ELSE
      SET v_change_type = 'transfer';
    END IF;
  ELSE
    SET v_change_type = 'transfer';
  END IF;

  -- 更新员工信息
  UPDATE employees
  SET dept_id = p_new_dept_id,
      position_id = p_new_position_id
  WHERE id = p_emp_id;

  -- 记录变动
  INSERT INTO job_history (emp_id, old_dept_id, new_dept_id,
                           old_position_id, new_position_id,
                           change_type, change_date, remark)
  VALUES (p_emp_id, v_old_dept_id, p_new_dept_id,
          v_old_position_id, p_new_position_id,
          v_change_type, p_change_date, p_remark);

  COMMIT;
  SELECT CONCAT('员工 ', p_emp_id, ' 转岗成功 (', v_change_type, ')') AS result;
END //

DELIMITER ;

-- 调用示例
-- CALL sp_employee_transfer(7, 5, 2, '2024-07-01', '从前端开发部转入后端开发部');

37.5.2 部门预算报告

sql

DELIMITER //

CREATE PROCEDURE sp_department_budget_report(
  IN p_dept_id INT UNSIGNED,
  IN p_year INT
)
BEGIN
  DECLARE v_budget DECIMAL(14,2);
  DECLARE v_salary_total DECIMAL(14,2);

  -- 获取预算
  SELECT budget INTO v_budget FROM departments WHERE id = p_dept_id;

  -- 计算全年薪资支出（简化计算：按月统计实际发放）
  SELECT COALESCE(SUM(monthly_salary), 0) INTO v_salary_total
  FROM (
    SELECT e.id,
           s.amount * (
             -- 该薪资记录在 p_year 中的有效月数
             LEAST(
               IFNULL(s.effective_to, CONCAT(p_year, '-12-31')),
               CONCAT(p_year, '-12-31')
             ) - GREATEST(s.effective_from, CONCAT(p_year, '-01-01'))
           ) / 30 AS monthly_salary  -- 简化：天数/30
    FROM employees e
    JOIN salaries s ON e.id = s.emp_id
    JOIN departments d ON e.dept_id = d.id
    WHERE d.id = p_dept_id
      AND s.effective_from <= CONCAT(p_year, '-12-31')
      AND (s.effective_to IS NULL OR s.effective_to >= CONCAT(p_year, '-01-01'))
  ) AS t;

  -- 输出报告
  SELECT
    d.name AS dept_name,
    v_budget AS annual_budget,
    v_salary_total AS salary_expenditure,
    v_budget - v_salary_total AS remaining,
    ROUND(v_salary_total / v_budget * 100, 2) AS utilization_pct
  FROM departments d
  WHERE d.id = p_dept_id;
END //

DELIMITER ;

-- CALL sp_department_budget_report(4, 2024);

37.5.3 考勤统计

sql

DELIMITER //

CREATE PROCEDURE sp_attendance_summary(
  IN p_emp_id INT UNSIGNED,
  IN p_year INT,
  IN p_month INT
)
BEGIN
  DECLARE v_start_date DATE;
  DECLARE v_end_date DATE;

  SET v_start_date = CONCAT(p_year, '-', LPAD(p_month, 2, '0'), '-01');
  SET v_end_date = LAST_DAY(v_start_date);

  SELECT
    e.name,
    COUNT(*) AS work_days,
    SUM(CASE WHEN a.status = 'normal' THEN 1 ELSE 0 END) AS normal,
    SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) AS late,
    SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) AS early,
    SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) AS absent,
    SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) AS leave_days,
    COALESCE(SUM(a.leave_hours), 0) AS leave_hours,
    ROUND(
      SUM(CASE WHEN a.status NOT IN ('absent', 'leave') THEN 1 ELSE 0 END)
      / COUNT(*) * 100, 1
    ) AS attendance_rate
  FROM employees e
  JOIN attendance a ON e.id = a.emp_id
  WHERE e.id = p_emp_id
    AND a.att_date BETWEEN v_start_date AND v_end_date
  GROUP BY e.id, e.name;
END //

DELIMITER ;

-- CALL sp_attendance_summary(3, 2024, 6);

37.6 触发器

37.6.1 薪资变动审计

sql

-- 审计表
CREATE TABLE salary_audit (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  emp_id INT UNSIGNED NOT NULL,
  old_amount DECIMAL(10,2),
  new_amount DECIMAL(10,2),
  old_effective_from DATE,
  new_effective_from DATE,
  operation_type ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  changed_by VARCHAR(50) DEFAULT USER(),
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER //

CREATE TRIGGER trg_salary_insert_audit
AFTER INSERT ON salaries
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (emp_id, new_amount, new_effective_from, operation_type)
  VALUES (NEW.emp_id, NEW.amount, NEW.effective_from, 'INSERT');
END //

CREATE TRIGGER trg_salary_update_audit
AFTER UPDATE ON salaries
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (emp_id, old_amount, new_amount,
                            old_effective_from, new_effective_from, operation_type)
  VALUES (OLD.emp_id, OLD.amount, NEW.amount,
          OLD.effective_from, NEW.effective_from, 'UPDATE');
END //

CREATE TRIGGER trg_salary_delete_audit
AFTER DELETE ON salaries
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (emp_id, old_amount, old_effective_from, operation_type)
  VALUES (OLD.emp_id, OLD.amount, OLD.effective_from, 'DELETE');
END //

DELIMITER ;

37.6.2 考勤数据验证

sql

DELIMITER //

CREATE TRIGGER trg_attendance_validation
BEFORE INSERT ON attendance
FOR EACH ROW
BEGIN
  -- 验证打卡时间
  IF NEW.clock_in IS NOT NULL AND NEW.clock_out IS NOT NULL THEN
    IF NEW.clock_out <= NEW.clock_in THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '下班打卡时间必须晚于上班打卡时间';
    END IF;
  END IF;

  -- 验证：正常状态必须有打卡记录
  IF NEW.status = 'normal' AND (NEW.clock_in IS NULL OR NEW.clock_out IS NULL) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '正常出勤必须有上下班打卡记录';
  END IF;

  -- 验证：请假必须有请假类型和时数
  IF NEW.status = 'leave' AND (NEW.leave_type IS NULL OR NEW.leave_hours <= 0) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '请假必须指定请假类型和时数';
  END IF;

  -- 验证：旷工不应有打卡记录
  IF NEW.status = 'absent' AND (NEW.clock_in IS NOT NULL OR NEW.clock_out IS NOT NULL) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '旷工不应有打卡记录';
  END IF;
END //

DELIMITER ;

常见错误

错误 1：薪资记录时间段重叠

问题：同一员工的 salary 记录 overlapping，导致查询当前薪资时返回多条。

预防：
  - 应用层：插入新薪资前，先关闭已有记录的 effective_to
  - 数据库层：通过触发器检查或使用 CHECK 约束的代理方案

错误 2：考勤唯一约束未生效

问题：attendance 表 uk_emp_date (emp_id, att_date) 确保每人每天一条记录，
     但应用层使用 INSERT 而不是 INSERT ... ON DUPLICATE KEY UPDATE，
     导致重复打卡时直接报错。

解决：使用 INSERT ... ON DUPLICATE KEY UPDATE 处理重复打卡场景
     INSERT INTO attendance (emp_id, att_date, clock_in)
     VALUES (?, ?, ?)
     ON DUPLICATE KEY UPDATE clock_in = VALUES(clock_in);

错误 3：递归 CTE 无限循环

问题：部门层级表中的数据形成了环（A 的 parent 是 B，B 的 parent 是 A），
     递归 CTE 无限循环。

解决：
  WITH RECURSIVE cte AS (
    ...
    UNION ALL
    SELECT ... WHERE depth < 50  -- 限制最大深度
  )
  
  更重要的是：通过应用逻辑或触发器防止数据形成环

错误 4：触发器中的 SIGNAL 未被正确使用

问题：在 BEFORE INSERT 触发器中使用 SIGNAL 抛出错误，
     但忘记指定正确的 SQLSTATE 值（必须为 45xxx 系列）。

正确做法：
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误信息';
  必须以 45 开头，表示用户自定义异常。

本章练习

练习 1：完整的建表和数据初始化

执行本章所有 CREATE TABLE 语句
执行所有示例数据 INSERT 语句
添加外键约束并验证数据完整性
为 departments 表设置所有记录的 manager_id

练习 2：复杂查询编写

独立编写以下查询（不参考本章代码）：

各部门本年度累计招聘人数
职级分布饼图数据（各职级人数和占比）
跨年度薪资增长率 TOP10
每月入离职人数趋势
部门间人员流动热力图数据

练习 3：存储过程开发

编写以下存储过程：

sp_new_hire：处理入职流程（插入 employee + salary + 生成工号）
sp_resignation：处理离职流程（更新 employee status + 保存最后薪资到 end_date）
sp_annual_increment：批量年度调薪（所有在职员工涨 5%，需事务保护）

练习 4：触发器开发

编写以下触发器：

员工离职时自动记录 job_history（change_type='resign'）
考勤表 INSERT 前自动计算 status（根据 clock_in 时间判断是否迟到）
部门变更时自动记录岗位变动

练习 5：报表 SQL

编写以下报表查询：

年度人力成本总报告（按月汇总薪资）
部门绩效报告（人数、平均薪资、离职率、平均入职年限）
组织健康度报告（晋升率、离职率、薪酬竞争力）

第37章 实战项目：企业员工管理系统 ​

本章目标 ​

前置知识 ​

37.1 业务需求分析 ​

37.1.1 功能模块 ​

37.1.2 ER 图（文字描述） ​

37.2 完整表结构设计 ​

37.2.1 部门表 (departments) ​

37.2.2 职位表 (positions) ​

37.2.3 员工表 (employees) ​

37.2.4 薪资表 (salaries) ​

37.2.5 岗位变动记录表 (job_history) ​

37.2.6 考勤表 (attendance) ​

37.3 示例数据 ​

37.4 业务 SQL 查询（30+ 示例） ​

37.4.1 基础查询 ​

37.4.2 统计与聚合 ​

37.4.3 递归与层级查询 ​

37.4.4 考勤分析 ​

37.4.5 高级分析 ​

37.5 存储过程 ​

37.5.1 员工调岗 ​

37.5.2 部门预算报告 ​

37.5.3 考勤统计 ​

37.6 触发器 ​

37.6.1 薪资变动审计 ​

37.6.2 考勤数据验证 ​

常见错误 ​

错误 1：薪资记录时间段重叠 ​

错误 2：考勤唯一约束未生效 ​

错误 3：递归 CTE 无限循环 ​

错误 4：触发器中的 SIGNAL 未被正确使用 ​

本章练习 ​

练习 1：完整的建表和数据初始化 ​

练习 2：复杂查询编写 ​

练习 3：存储过程开发 ​

练习 4：触发器开发 ​

练习 5：报表 SQL ​

第37章实战项目：企业员工管理系统

本章目标

前置知识

37.1 业务需求分析

37.1.1 功能模块

37.1.2 ER 图（文字描述）

37.2 完整表结构设计

37.2.1 部门表 (departments)

37.2.2 职位表 (positions)

37.2.3 员工表 (employees)

37.2.4 薪资表 (salaries)

37.2.5 岗位变动记录表 (job_history)

37.2.6 考勤表 (attendance)

37.3 示例数据

37.4 业务 SQL 查询（30+ 示例）

37.4.1 基础查询

37.4.2 统计与聚合

37.4.3 递归与层级查询

37.4.4 考勤分析

37.4.5 高级分析

37.5 存储过程

37.5.1 员工调岗

37.5.2 部门预算报告

37.5.3 考勤统计

37.6 触发器

37.6.1 薪资变动审计

37.6.2 考勤数据验证

常见错误

错误 1：薪资记录时间段重叠

错误 2：考勤唯一约束未生效

错误 3：递归 CTE 无限循环

错误 4：触发器中的 SIGNAL 未被正确使用

本章练习

练习 1：完整的建表和数据初始化

练习 2：复杂查询编写

练习 3：存储过程开发

练习 4：触发器开发

练习 5：报表 SQL