课程设计（大作业）报告
数据库原理与应用实验大作业使学生通过对数据库基础知识的学习，掌握数据库基本操作，使学生掌握应用数据库所必须的所有核心概念和内容，在这个过程中激发学生学习的兴趣，并为后续的专业课程的学习打下坚实的基础。在课内，给学生布置一个数据库设计应用的任务。学生在教师的指导下，以任务驱动方式使学生们熟练掌握数据库的基本应用。
具体要求：
1.各小组自选课题设计管理系统数据库，鼓励选题具有新颖性；
2.请将所学知识和技能充分运用到所设计的管理系统数据库中（数据库至少有3个关系）；
3.建议数据库系统设计要有基本的安全管理，数据库系统能正常运行，可进行查询；查询的复杂程度将决定最终得分的高低。（自拟查询任务并实现）；
4.在所设计的数据库中创建数据库对象要求种类尽可能丰富，基本完成表、视图、索引、存储过程和触发器；
5.文档撰写规范、整齐，图文并茂，每人一份大作业报告（内容正文，宋体四号字，单倍行间距；参考文献，宋体小四号字）；
6.参考文献不少于六个。
课程设计（大作业）报告
1.题目要求
1.1 设计项目
员工管理数据库系统设计与实现
1.2设计任务
1.2.1系统目标
构建一个高效、稳定、易维护的员工管理信息系统，实现企业人力资源的数字化管理，提高管理效率，降低管理成本
1.2.2 实现过程
1.2.2.1需求分析
1.2.2.1.1业务需求
人力资源管理核心业务
员工生命周期管理：从员工入职、在职期间的信息维护到离职处理的全流程管理
组织架构管理：部门设置、职位配置、层级关系管理
薪酬管理：基本薪资设定、薪资记录维护、薪资统计分析
考勤管理：日常考勤记录、工时统计、加班管理
数据统计与分析：员工分布统计、薪资分析、流失率分析等决策支持
业务流程支持
新员工入职流程：录入基本信息、分配部门职位、设定薪资标准
员工信息变更：部门调动、职位晋升、薪资调整
离职处理：状态变更、离职原因记录、数据归档
1.2.2.2 功能需求
员工信息管理
|
员工基本信息录入、修改、查询、删除 |
|
员工工号自动生成和管理 |
|
员工状态管理（在职、离职、休假、停薪留职） |
|
支持按部门、职位、状态等条件进行筛选查询 |
部门管理
|
部门信息的增删改查 |
|
部门编码规范管理 |
|
部门经理指定 |
|
部门员工统计和分析 |
职位管理
|
职位信息维护 |
|
职位级别设定（1-10级） |
|
薪资范围定义 |
|
职位描述管理 |
薪资管理
|
员工基本薪资设定 |
|
月度薪资记录管理 |
|
薪资构成管理（基本工资、加班费、奖金、津贴、扣款等） |
|
薪资计算和发放记录 |
|
薪资统计报表 |
考勤管理
|
日常考勤记录 |
|
上下班打卡时间记录 |
|
工作时长和加班时长计算 |
|
考勤状态管理（正常、迟到、早退、旷工、请假） |
|
考勤统计分析 |
统计分析功能
|
员工数量统计（按部门、职位、年龄段） |
|
薪资分析（平均薪资、薪资区间分布） |
|
流失率统计 |
|
入职趋势分析 |
|
各类报表生成 |
数据查询功能
|
多条件组合查询 |
|
排序功能（按薪资、入职时间等） |
|
分页显示 |
|
数据导出功能 |
1.2.2.3非功能需求：
1. 性能要求
|
数据库查询响应时间 |
< |
2秒 |
|
支持并发用户数 |
≥ |
100 |
|
系统可用性 |
≥ |
99.5% |
|
数据备份恢复时间 |
< |
30分钟 |
2. 安全性要求
|
用户身份认证和权限控制 |
|
敏感数据加密存储（如身份证号） |
|
操作日志记录和审计 |
|
数据访问权限分级管理 |
3. 可靠性要求
|
数据完整性约束 |
|
外键关联保证数据一致性 |
|
事务处理确保操作原子性 |
|
定期数据备份机制 |
4. 可维护性要求
|
良好的数据库设计规范 |
|
完整的注释和文档 |
|
模块化的存储过程和视图 |
|
标准化的命名规范 |
5. 可扩展性要求
|
支持新增业务模块 |
|
支持数据量增长 |
|
支持新的统计分析需求 |
|
预留接口扩展能力 |
6. 用户体验要求
|
界面友好，操作简便 |
|
错误提示信息明确 |
|
数据展示直观清晰 |
|
支持批量操作功能 |
7. 兼容性要求
|
支持主流数据库管理系统 |
|
支持多种操作系统环境 |
|
支持不同浏览器访问 |
|
数据导入导出格式多样化 |
8. 数据质量要求
|
数据格式校验（邮箱、电话、身份证等） |
|
重复数据检测和预防 |
|
数据完整性检查 |
|
历史数据版本控制 |
1.2.3概念结构设计
1.2.3.1实体定义：
-
系统中的主要实体
erDiagram
DEPARTMENTS
{
int department_id PK "部门ID，主键"
varchar department_name UK "部门名称，唯一"
varchar department_code UK "部门代码，唯一"
varchar manager_name "部门经理"
text description "部门描述"
timestamp created_at "创建时间"
timestamp updated_at "更新时间"
}
POSITIONS
{
int position_id PK "职位ID，主键"
varchar position_name "职位名称"
int position_level "职位级别(1-10)"
decimal min_salary "最低薪资"
decimal max_salary "最高薪资"
text description "职位描述"
timestamp created_at "创建时间"
}
EMPLOYEES
{
int employee_id PK "员工ID，主键"
varchar employee_code UK "员工工号，唯一"
varchar name "姓名"
enum gender "性别(男/女/其他)"
date birth_date "出生日期"
varchar phone "电话号码"
varchar email UK "邮箱地址，唯一"
varchar id_card "身份证号"
text address "家庭地址"
int department_id FK "部门ID，外键"
int position_id FK "职位ID，外键"
date hire_date "入职日期"
date contract_start "合同开始日期"
date contract_end "合同结束日期"
decimal salary "基本薪资"
decimal bonus "奖金"
enum status "员工状态(在职/离职/休假/停薪留职)"
date quit_date "离职日期"
text quit_reason "离职原因"
timestamp created_at "创建时间"
timestamp updated_at "更新时间"
}
SALARY_RECORDS
{
int record_id PK "记录ID，主键"
int employee_id FK "员工ID，外键"
date salary_month UK "薪资月份，与员工ID组成唯一键"
decimal basic_salary "基本工资"
decimal overtime_pay "加班费"
decimal bonus "奖金"
decimal allowance "津贴"
decimal deduction "扣款"
decimal social_insurance "社保扣除"
decimal tax "个人所得税"
decimal net_salary "实发工资"
timestamp created_at "创建时间"
}
ATTENDANCE_RECORDS
{
int record_id PK "记录ID，主键"
int employee_id FK "员工ID，外键"
date attendance_date UK "考勤日期，与员工ID组成唯一键"
time check_in_time "上班打卡时间"
time check_out_time "下班打卡时间"
decimal work_hours "工作小时数"
decimal overtime_hours "加班小时数"
enum status "考勤状态(正常/迟到/早退/旷工/请假)"
text remark "备注"
timestamp created_at "创建时间"
}
实体间关系
DEPARTMENTS ||--o{ EMPLOYEES : "包含"
POSITIONS ||--o{ EMPLOYEES : "担任"
EMPLOYEES ||--o{ SALARY_RECORDS : "拥有"
EMPLOYEES ||--o{ ATTENDANCE_RECORDS : "拥有"
2.2 ER图：
1.2.3.2 职工管理系统总ER图
1.2.4逻辑结构设计
1.2.4.1实体定义
1.2.4.1.1 员工实体 (Employee)
员工ID (employee_id) 主键，唯一标识符
员工工号 (employee_code) 唯一工号
姓名 (name) 员工姓名
性别 (gender) 性别信息
出生日期 (birth_date) 出生日期
联系电话 (phone) 手机号码
邮箱地址 (email) 电子邮箱
身份证号 (id_card) 身份证号码
家庭地址 (address) 详细地址
入职日期 (hire_date) 入职时间
合同开始日期 (contract_start) 合同起始日期
合同结束日期 (contract_end) 合同截止日期
基本薪资 (salary) 月薪
绩效奖金 (bonus) 奖金数额
在职状态 (status) 在职/离职/休假
1.2.4.1.2 部门实体 (Department)
部门ID (department_id) 主键
部门名称 (department_name) 部门全称
部门代码 (department_code) 部门编码
部门经理 (manager_name) 负责人姓名
部门描述 (description) 部门职能说明
创建时间 (created_at) 部门设立时间
1.2.4.1.3 职位实体 (Position)
实体描述: 公司内的职位岗位信息
属性:
职位ID (position_id) 主键
职位名称 (position_name) 岗位名称
职位级别 (position_level) 岗位层级
薪资下限 (min_salary) 该职位最低薪资
薪资上限 (max_salary) 该职位最高薪资
职位描述 (description) 岗位职责说明
创建时间 (created_at) 职位设立时间
1.2.4.1.4 薪资记录实体 (Salary_Record)
实体描述: 员工每月薪资发放的详细记录
属性:
记录ID (record_id) 主键
薪资月份 (salary_month) 工资所属月份
基本工资 (basic_salary) 基础薪资
加班费 (overtime_pay) 加班补贴
绩效奖金 (bonus) 当月奖金
各项补贴 (allowance) 交通、餐补等
扣除项目 (deduction) 请假扣款等
社会保险 (social_insurance) 社保个人部分
个人所得税 (tax) 税费
实发工资 (net_salary) 最终到手金额
创建时间 (created_at) 记录生成时间
1.2.4.1.5 考勤记录实体 (Attendance_Record)
实体描述: 员工日常考勤打卡记录
属性:
考勤ID (attendance_id) 主键
考勤日期 (attendance_date) 打卡日期
上班时间 (check_in_time) 上班打卡时间
下班时间 (check_out_time) 下班打卡时间
工作时长 (work_hours) 实际工作小时数
加班时长 (overtime_hours) 加班小时数
考勤状态 (status) 正常/迟到/早退/缺勤
备注说明 (remarks) 请假原因等备注
创建时间 (created_at) 记录创建时间
1.2.4.2关系定义（各表之间的联系）：
1.2.4.2.1 员工与部门关系 (Employee Department)
关系类型: 多对一 (N:1)
关系描述: 一个员工只能属于一个部门，一个部门可以有多个员工
实现方式: 员工表中包含部门ID作为外键
约束条件:
员工必须属于某个部门 (NOT NULL)
部门被删除时需要先处理该部门下的员工
1.2.4.2.2 员工与职位关系 (Employee Position)
关系类型: 多对一 (N:1)
关系描述: 一个员工只能担任一个职位，一个职位可以有多个员工担任
实现方式: 员工表中包含职位ID作为外键
约束条件:
员工必须有明确的职位 (NOT NULL)
员工薪资应该在职位薪资范围内
1.2.4.2.3 员工与薪资记录关系 (Employee Salary_Record)
关系类型: 一对多 (1:N)
关系描述: 一个员工可以有多条薪资记录，每条记录只属于一个员工
实现方式: 薪资记录表中包含员工ID作为外键
约束条件:
每个员工每月只能有一条薪资记录
员工离职后不再产生新的薪资记录
1.2.4.2.4 员工与考勤记录关系 (Employee Attendance_Record)
关系类型: 一对多 (1:N)
关系描述: 一个员工可以有多条考勤记录，每条记录只属于一个员工
实现方式: 考勤记录表中包含员工ID作为外键
约束条件:
每个员工每天只能有一条考勤记录
考勤记录的日期不能超过当前日期
1.2.4.2.5 部门与职位关系 (Department Position)
关系类型: 多对多 (M:N)
关系描述: 一个部门可以有多个职位，一个职位也可能存在于多个部门
实现方式: 通过员工表间接关联，不需要单独的关联表
约束条件: 职位薪资范围应该符合部门薪资政策
1.2.5物理结构设计
1.2.5.1表结构设计
1.2.5.1.1 员工表 (employees)
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID，主键',
employee_code VARCHAR(20) UNIQUE NOT NULL COMMENT '员工工号，唯一',
name VARCHAR(100) NOT NULL COMMENT '员工姓名',
gender ENUM('男', '女', '其他') DEFAULT '男' COMMENT '性别',
birth_date DATE COMMENT '出生日期',
phone VARCHAR(20) COMMENT '联系电话',
email VARCHAR(100) COMMENT '邮箱地址',
id_card VARCHAR(18) UNIQUE COMMENT '身份证号',
address TEXT COMMENT '家庭地址',
department_id INT NOT NULL COMMENT '所属部门ID',
position_id INT NOT NULL COMMENT '职位ID',
hire_date DATE NOT NULL COMMENT '入职日期',
contract_start DATE COMMENT '合同开始日期',
contract_end DATE COMMENT '合同结束日期',
salary DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '基本薪资',
bonus DECIMAL(10,2) DEFAULT 0.00 COMMENT '绩效奖金',
status ENUM('在职', '离职', '休假') DEFAULT '在职' COMMENT '在职状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
-- 外键约束
FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE RESTRICT,
FOREIGN KEY (position_id) REFERENCES positions(position_id) ON DELETE RESTRICT,
-- 索引
INDEX idx_employee_code (employee_code),
INDEX idx_department_id (department_id),
INDEX idx_position_id (position_id),
INDEX idx_hire_date (hire_date),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';
1.2.5.1.2 部门表 (departments)
CREATE TABLE departments (
department_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID，主键',
department_name VARCHAR(100) NOT NULL COMMENT '部门名称',
department_code VARCHAR(20) UNIQUE COMMENT '部门代码',
manager_name VARCHAR(100) COMMENT '部门经理姓名',
description TEXT COMMENT '部门描述',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-- 索引
INDEX idx_department_code (department_code),
INDEX idx_department_name (department_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门信息表';
1.2.5.1.3 职位表 (positions)
CREATE TABLE positions (
position_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '职位ID，主键',
position_name VARCHAR(100) NOT NULL COMMENT '职位名称',
position_level INT DEFAULT 1 COMMENT '职位级别',
min_salary DECIMAL(10,2) DEFAULT 0.00 COMMENT '薪资下限',
max_salary DECIMAL(10,2) DEFAULT 0.00 COMMENT '薪资上限',
description TEXT COMMENT '职位描述',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-- 检查约束
CHECK (max_salary >= min_salary),
-- 索引
INDEX idx_position_name (position_name),
INDEX idx_position_level (position_level),
INDEX idx_salary_range (min_salary, max_salary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='职位信息表';
1.2.5.1.4 薪资记录表 (salary_records)
CREATE TABLE salary_records (
record_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键',
employee_id INT NOT NULL COMMENT '员工ID',
salary_month DATE NOT NULL COMMENT '薪资月份',
basic_salary DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '基本工资',
overtime_pay DECIMAL(10,2) DEFAULT 0.00 COMMENT '加班费',
bonus DECIMAL(10,2) DEFAULT 0.00 COMMENT '绩效奖金',
allowance DECIMAL(10,2) DEFAULT 0.00 COMMENT '各项补贴',
deduction DECIMAL(10,2) DEFAULT 0.00 COMMENT '扣除项目',
social_insurance DECIMAL(10,2) DEFAULT 0.00 COMMENT '社会保险',
tax DECIMAL(10,2) DEFAULT 0.00 COMMENT '个人所得税',
net_salary DECIMAL(10,2) NOT NULL COMMENT '实发工资',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-- 外键约束
FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE,
-- 唯一约束（每个员工每月只能有一条记录）
UNIQUE KEY uk_employee_month (employee_id, salary_month),
-- 索引
INDEX idx_employee_id (employee_id),
INDEX idx_salary_month (salary_month),
INDEX idx_net_salary (net_salary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资记录表';
1.2.5.1.5 考勤记录表 (attendance_records)
CREATE TABLE attendance_records (
attendance_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '考勤ID，主键',
employee_id INT NOT NULL COMMENT '员工ID',
attendance_date DATE NOT NULL COMMENT '考勤日期',
check_in_time TIME COMMENT '上班时间',
check_out_time TIME COMMENT '下班时间',
work_hours DECIMAL(4,2) DEFAULT 0.00 COMMENT '工作时长（小时）',
overtime_hours DECIMAL(4,2) DEFAULT 0.00 COMMENT '加班时长（小时）',
status ENUM('正常', '迟到', '早退', '缺勤', '请假') DEFAULT '正常' COMMENT '考勤状态',
remarks TEXT COMMENT '备注说明',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-- 外键约束
FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE,
-- 唯一约束（每个员工每天只能有一条记录）
UNIQUE KEY uk_employee_date (employee_id, attendance_date),
-- 索引
INDEX idx_employee_id (employee_id),
INDEX idx_attendance_date (attendance_date),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤记录表';
1.2.6常用语句
-- 1. 查询所有在职员工信息
SELECT
e.employee_code AS '工号',
e.name AS '姓名',
e.gender AS '性别',
d.department_name AS '部门',
p.position_name AS '职位',
e.salary AS '薪资',
e.hire_date AS '入职日期',
e.status AS '状态'
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN positions p ON e.position_id = p.position_id
WHERE e.status = '在职'
ORDER BY e.hire_date DESC;
-- 2. 按部门统计员工数量
SELECT
d.department_name AS '部门',
COUNT(e.employee_id) AS '员工数量',
AVG(e.salary) AS '平均薪资',
MAX(e.salary) AS '最高薪资',
MIN(e.salary) AS '最低薪资'
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id AND e.status = '在职'
GROUP BY d.department_id, d.department_name
ORDER BY COUNT(e.employee_id) DESC;
-- 3. 查询薪资排名前10的员工
SELECT
e.employee_code AS '工号',
e.name AS '姓名',
d.department_name AS '部门',
p.position_name AS '职位',
e.salary AS '薪资'
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN positions p ON e.position_id = p.position_id
WHERE e.status = '在职'
ORDER BY e.salary DESC
LIMIT 10;
-- 4. 查询入职超过1年的员工
SELECT
e.employee_code AS '工号',
e.name AS '姓名',
e.hire_date AS '入职日期',
DATEDIFF(CURDATE(), e.hire_date) AS '入职天数',
ROUND(DATEDIFF(CURDATE(), e.hire_date) / 365, 1) AS '工作年限'
FROM employees e
WHERE e.status = '在职'
AND DATEDIFF(CURDATE(), e.hire_date) > 365
ORDER BY e.hire_date;
-- 5. 按年龄段统计员工分布
SELECT
CASE
WHEN YEAR(CURDATE()) - YEAR(birth_date) < 25 THEN '25岁以下'
WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 25 AND 30 THEN '25-30岁'
WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 31 AND 35 THEN '31-35岁'
WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 36 AND 40 THEN '36-40岁'
ELSE '40岁以上'
END AS '年龄段',
COUNT(*) AS '员工数量'
FROM employees
WHERE status = '在职' AND birth_date IS NOT NULL
GROUP BY
CASE
WHEN YEAR(CURDATE()) - YEAR(birth_date) < 25 THEN '25岁以下'
WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 25 AND 30 THEN '25-30岁'
WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 31 AND 35 THEN '31-35岁'
WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 36 AND 40 THEN '36-40岁'
ELSE '40岁以上'
END
ORDER BY MIN(YEAR(CURDATE()) - YEAR(birth_date));
1.2.7 复杂查询：
-- 1. 员工流失率统计（按部门）
SELECT
d.department_name AS '部门',
COUNT(CASE WHEN e.status = '在职' THEN 1 END) AS '在职人数',
COUNT(CASE WHEN e.status = '离职' THEN 1 END) AS '离职人数',
COUNT(e.employee_id) AS '总人数',
ROUND(
COUNT(CASE WHEN e.status = '离职' THEN 1 END) * 100.0 /
NULLIF(COUNT(e.employee_id), 0), 2
) AS '离职率(%)'
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY COUNT(CASE WHEN e.status = '离职' THEN 1 END) DESC;
-- 2. 月度入职统计
SELECT
DATE_FORMAT(hire_date, '%Y-%m') AS '入职月份',
COUNT(*) AS '入职人数'
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(hire_date, '%Y-%m')
ORDER BY hire_date DESC;
-- 3. 薪资区间分布
SELECT
CASE
WHEN salary < 8000 THEN '8K以下'
WHEN salary BETWEEN 8000 AND 12000 THEN '8K-12K'
WHEN salary BETWEEN 12001 AND 18000 THEN '12K-18K'
WHEN salary BETWEEN 18001 AND 25000 THEN '18K-25K'
ELSE '25K以上'
END AS '薪资区间',
COUNT(*) AS '员工数量',
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees WHERE status = '在职'), 1) AS '占比(%)'
FROM employees
WHERE status = '在职'
GROUP BY
CASE
WHEN salary < 8000 THEN '8K以下'
WHEN salary BETWEEN 8000 AND 12000 THEN '8K-12K'
WHEN salary BETWEEN 12001 AND 18000 THEN '12K-18K'
WHEN salary BETWEEN 18001 AND 25000 THEN '18K-25K'
ELSE '25K以上'
END
ORDER BY MIN(salary);
1.2.8详细视图：
— 员工详细视图
CREATE VIEW v_employee_details AS
SELECT
e.employee_id,
e.employee_code AS '工号',
e.name AS '姓名',
e.gender AS '性别',
e.birth_date AS '出生日期',
YEAR(CURDATE()) - YEAR(e.birth_date) AS '年龄',
e.phone AS '电话',
e.email AS '邮箱',
d.department_name AS '部门',
p.position_name AS '职位',
e.hire_date AS '入职日期',
DATEDIFF(CURDATE(), e.hire_date) AS '入职天数',
e.salary AS '薪资',
e.status AS '状态',
e.created_at AS '创建时间',
e.updated_at AS '更新时间'
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN positions p ON e.position_id = p.position_id;
-- 部门统计视图
CREATE VIEW v_department_stats AS
SELECT
d.department_id,
d.department_name AS '部门名称',
d.manager_name AS '部门经理',
COUNT(e.employee_id) AS '总员工数',
COUNT(CASE WHEN e.status = '在职' THEN 1 END) AS '在职员工数',
ROUND(AVG(CASE WHEN e.status = '在职' THEN e.salary END), 2) AS '平均薪资',
MAX(CASE WHEN e.status = '在职' THEN e.salary END) AS '最高薪资',
MIN(CASE WHEN e.status = '在职' THEN e.salary END) AS '最低薪资'
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name, d.manager_name;
4.6存储过程
-- 员工薪资调整存储过程
CREATE PROCEDURE AdjustSalary(
IN emp_code VARCHAR(20),
IN adjustment_type ENUM('increase', 'decrease'),
IN adjustment_amount DECIMAL(10,2),
OUT result_message VARCHAR(200)
)
BEGIN
DECLARE emp_count INT DEFAULT 0;
DECLARE current_salary DECIMAL(10,2);
DECLARE new_salary DECIMAL(10,2);
-- 检查员工是否存在
SELECT COUNT(*), salary INTO emp_count, current_salary
FROM employees
WHERE employee_code = emp_code AND status = '在职';
IF emp_count = 0 THEN
SET result_message = '错误：员工不存在或已离职';
ELSE
-- 计算新薪资
IF adjustment_type = 'increase' THEN
SET new_salary = current_salary + adjustment_amount;
ELSE
SET new_salary = current_salary - adjustment_amount;
END IF;
-- 确保薪资不为负数
IF new_salary < 0 THEN
SET result_message = '错误：调整后薪资不能为负数';
ELSE
-- 更新薪资
UPDATE employees
SET salary = new_salary, updated_at = CURRENT_TIMESTAMP
WHERE employee_code = emp_code;
SET result_message = CONCAT('成功：薪资从 ', current_salary, ' 调整为 ', new_salary);
END IF;
END IF;
END //
1.2.9用户分级
用户账户信息
|
用户角色 |
用户名 |
默认密码 |
权限级别 |
|
系统管理员 |
admin |
Admin@123456 |
最高权限 |
|
人事经理 |
hr_manager |
HR@Manager123 |
员工管理权限 |
|
部门经理 |
dept_manager |
Dept@Manager123 |
部门管理权限 |
|
普通员工 |
employee |
Employee@123 |
查看权限 |
|
数据库管理员 |
dba |
DBA@123456 |
数据库管理权限 |
权限矩阵
|
功能模块 |
admin |
hr_manager |
dept_manager |
employee |
dba |
|
查看员工信息 |
✅ |
✅ |
✅ |
✅ |
✅ |
|
添加员工 |
✅ |
✅ |
❌ |
❌ |
❌ |
|
更新员工信息 |
✅ |
✅ |
✅ |
❌ |
❌ |
|
删除员工 |
✅ |
✅ |
❌ |
❌ |
❌ |
|
部门管理 |
✅ |
✅ |
❌ |
❌ |
❌ |
|
职位管理 |
✅ |
✅ |
❌ |
❌ |
❌ |
|
薪资管理 |
✅ |
✅ |
❌ |
❌ |
❌ |
|
统计报表 |
✅ |
✅ |
❌ |
❌ |
❌ |
|
数据库管理 |
✅ |
❌ |
❌ |
❌ |
✅ |
1.3心得体会
当我第一次接触数据库课程时，说不紧张是假的。面对那些陌生的概念，什么是实体、关系、范式，我完全摸不着头脑。特别是老师布置要做一个员工管理系统的时候，我甚至不知道从哪里开始。
最开始我用C++写前端连接数据库，那个界面简陋得让人不忍直视，只能在控制台里输入输出，连个像样的菜单都做不出来。配置MySQL环境的时候更是折腾了好几天，各种编译错误让我怀疑自己是不是不适合学编程。
真正的转折点是学习ER图设计。当我开始思考员工、部门、职位之间的关系时，突然发现数据库设计其实就像搭积木一样有规律可循。我学会了如何抽象现实世界中的对象，理解一对一、一对多、多对多的关系，知道哪些信息应该作为实体的属性。当我画出第一张完整的ER图时，那种成就感真的很棒。
从ER图到实际的表结构，我明白了主键、外键、唯一性约束的重要性，也知道了为什么要建立索引来提高查询效率。学会了规范的建表语句，知道如何设计字段类型和约束条件。
最让我有成就感的是学会了写触发器。从最简单的数据验证到复杂的业务逻辑，比如自动生成员工工号，让系统自动按部门编码生成唯一工号；操作日志记录，每次增删改都有完整的审计记录；数据备份保护，删除数据前自动备份，防止误操作。写触发器让我感受到了编程的乐趣，原来代码可以让系统变得这么智能。
通过员工管理系统项目，我学会了如何从业务需求中抽取数据结构，如何设计一个完整的数据库系统，遇到错误时如何定位和解决问题。我开始考虑数据的完整性和一致性，系统的性能优化，用户体验的改善，代码的可维护性。
这次学习让我有几个深刻的收获。首先是理论与实践结合的重要性，课本上的概念只有在实际项目中才能真正理解。比如外键约束，只有在实际使用中遇到问题，才知道它的价值。其次是系统性思维的建立，数据库设计不是孤立的技术，它需要考虑整个系统的架构，从前端界面到后端存储，每个环节都要统筹考虑。
在学习方法上，我学会了遇到问题先自己思考和尝试，查阅官方文档和技术资料，向同学和老师请教，在实践中验证理论知识。从最初的茫然无措，到现在能够独立设计和实现一个数据库系统，这种成长让我对学习编程更有信心了。
未来我计划深入学习数据库优化，学习更高级的索引策略和查询优化；探索新技术，了解NoSQL数据库、大数据处理等新领域；提升前端技能，学习现代前端框架，做出更好看的界面；积累更多实战项目经验，参与更多实际项目开发。
感谢老师的耐心指导和同学们的互相帮助。这门课程不仅让我掌握了数据库技术，更重要的是培养了我的工程思维和解决问题的能力。虽然现在的水平还很有限，但我相信通过持续的学习和实践，一定能够成为一名优秀的软件开发者。数据库课程是我编程路上的重要一步，我会继续努力，不断提升自己的技术水平。
这次学习经历让我明白，编程不仅仅是敲代码，更是一种思维方式的训练。从迷茫到清晰，从困难到有趣，这个过程虽然艰辛，但收获满满。
二、参考文献
[1] 林子雨,杨冬青,王腾蛟,张东站.基于关系数据库的关键词查询[J].软件学报. 2010(10)
[2] 王珊，萨师煊.数据库系统概论（第4版）.北京：高等教育出版社，2006
[3] 陈业斌，吴福英，梁长梅，童启，申元霞. 数据库原理及应用（MySQL版|在线实训版）.人民邮电出版社
[4] MySQL官方文档(https://dev.mysql.com/doc/)
[5]郑戟明，董明朝，肖宇，钱之琳.MySQL数据库课程设计[J].电脑知识技术,2020,16(03):3-4
[6]胡强.MySQL数据库常见问题分析与研究[J].电脑编程技巧与维护，2019（12）:91-92
三、前端代码（可选）
#include <iostream>
#include <string>
#include <sstream>
#include <map>
#include <vector>
#include <iomanip>
#include <mysql/mysql.h>
#include <cstdlib>
#include <algorithm>
#include <fstream>
using namespace std;
// 用户角色映射
map<string, int> roleMap = {
{"admin", 1}, // 系统管理员 (最高权限)
{"hr_manager", 2}, // 人事经理
{"dept_manager", 3}, // 部门经理
{"employee", 4}, // 普通员工
{"dba", 5} // 数据库管理员
};
map<string, string> Emap = {
// {"admin", 1}, // 系统管理员 (最高权限)
{"hr_manager", "HR001"}, // 人事经理
{"dept_manager", "PRD001"}, // 部门经理
{"employee", "EMP001"}, // 普通员工
// {"dba", 5} // 数据库管理员
};
MYSQL* conn = nullptr;
const char* host = "127.0.0.1";
const char* db = "employee_management"; // 使用我们设计的数据库
const unsigned int port = 3306;
// 工具函数：清屏
void clearScreen() {
system("clear");
}
// 工具函数：暂停
void pauseScreen() {
cout << "\n按任意键继续...";
cin.get();
}
// 工具函数：输入验证
bool isValidEmail(const string& email) {
return email.find("@") != string::npos && email.find(".") != string::npos;
}
bool isValidPhone(const string& phone) {
return phone.length() >= 10 && phone.length() <= 15;
}
// 基础用户类
class User {
protected:
string currentUser;
int userRole;
public:
User(const string& username, int role) : currentUser(Emap[username]), userRole(role) {cout<<currentUser<<endl;}
virtual ~User() {}
// 虚函数 - 各种操作
virtual void viewEmployees() { showNoPermission(); }
virtual void addEmployee() { showNoPermission(); }
virtual void updateEmployee() { showNoPermission(); }
virtual void deleteEmployee() { showNoPermission(); }
virtual void manageDepartments() { showNoPermission(); }
virtual void managePositions() { showNoPermission(); }
virtual void viewReports() { showNoPermission(); }
virtual void manageSalary() { showNoPermission(); }
virtual void databaseAdmin() { showNoPermission(); }
// 显示权限不足消息
void showNoPermission() {
cout << "❌ 权限不足：您没有执行此操作的权限！" << endl;
pauseScreen();
}
// 显示查询结果
void displayQueryResult(const string& query) {
if (mysql_query(conn, query.c_str())) {
cerr << "❌ 查询执行失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_RES* res = mysql_store_result(conn);
if (!res) {
cout << "✅ 操作完成，没有返回结果" << endl;
return;
}
int fieldn = mysql_num_fields(res);
MYSQL_FIELD* fields = mysql_fetch_fields(res);
cout << "\n" << string(80, '=') << endl;
// 显示表头
for (int i = 0; i < fieldn; i++) {
cout << setw(15) << left << fields[i].name;
}
cout << endl << string(80, '-') << endl;
// 显示数据行
MYSQL_ROW row;
int rowCount = 0;
while ((row = mysql_fetch_row(res)) != nullptr) {
for (int i = 0; i < fieldn; i++) {
cout << setw(15) << left << (row[i] ? row[i] : "NULL");
}
cout << endl;
rowCount++;
}
cout << string(80, '=') << endl;
cout << "📊 共查询到 " << rowCount << " 条记录" << endl;
mysql_free_result(res);
}
// 执行SQL语句（增删改）
bool executeQuery(const string& query) {
if (mysql_query(conn, query.c_str())) {
cerr << "❌ 操作失败: " << mysql_error(conn) << endl;
return false;
}
cout << "✅ 操作成功完成！" << endl;
return true;
}
};
// 普通员工类
class Employee : public User {
public:
Employee(const string& username) : User(username, 4) {}
void viewEmployees() override {
cout << "\n🔍 查看员工信息" << endl;
cout << "1. 查看个人信息\n2. 查看同事信息\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
string query;
if (choice == 1) {
query = "SELECT *FROM v_employee_details WHERE 工号 = '" + currentUser + "'";
} else if (choice == 2) {
query = "SELECT *FROM v_employee_details WHERE 部门= (SELECT 部门 FROM v_employee_details WHERE 工号 = '" + currentUser + "')";
} else {
cout << "❌ 无效选择" << endl;
return;
}
displayQueryResult(query);
pauseScreen();
}
};
// 部门经理类
class DeptManager : public Employee {
public:
DeptManager(const string& username) : Employee(username) { userRole = 3; }
void viewEmployees() override {
cout << "\n🔍 查看员工信息" << endl;
cout << "1. 查看个人信息\n2. 查看部门员工\n3. 查看所有员工\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
string query;
//--------------------这里懒得搞了，记得别输入字母，否则会把缓冲区撑爆，~
//--------------------中间2，3一样，因为根本没数据
switch (choice) {
case 1:
query = "SELECT * FROM v_employee_details WHERE 工号 = '" + currentUser + "'";
break;
case 2:
query = "SELECT *FROM v_employee_details WHERE 部门= (SELECT 部门 FROM v_employee_details WHERE 工号 = '" + currentUser + "')";
break;
case 3:
query = "SELECT *FROM v_employee_details WHERE 部门= (SELECT 部门 FROM v_employee_details WHERE 工号 = '" + currentUser + "')";
break;
default:
cout << "❌ 无效选择" << endl;
return;
}
displayQueryResult(query);
pauseScreen();
}
void updateEmployee() override {
cout << "\n✏️ 更新员工信息" << endl;
cout << "输入员工工号: ";
string empCode;
cin >> empCode;
cin.ignore();
cout << "更新选项:\n1. 职位\n2. 薪资\n3. 状态\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
string query;
switch (choice) {
case 1: {
cout << "输入新职位ID: ";
int positionId;
cin >> positionId;
query = "UPDATE employees SET position_id = " + to_string(positionId) +
" WHERE employee_code = '" + empCode + "'";
break;
}
case 2: {
cout << "输入新薪资: ";
double salary;
cin >> salary;
query = "UPDATE employees SET salary = " + to_string(salary) +
" WHERE employee_code = '" + empCode + "'";
break;
}
case 3: {
cout << "输入新状态(在职/离职/休假): ";
string status;
cin >> status;
query = "UPDATE employees SET status = '" + status +
"' WHERE employee_code = '" + empCode + "'";
break;
}
default:
cout << "❌ 无效选择" << endl;
return;
}
executeQuery(query);
pauseScreen();
}
};
// 人事经理类
class HRManager : public DeptManager {
public:
HRManager(const string& username) : DeptManager(username) { userRole = 2; }
void addEmployee() override {
cout << "\n➕ 添加新员工" << endl;
string empCode, name, gender, phone, email, idCard, address;
string birthDate, hireDate, contractStart, contractEnd;
int deptId, positionId;
double salary, bonus;
cout << "工号: "; cin >> empCode; cin.ignore();
cout << "姓名: "; getline(cin, name);
cout << "性别(男/女/其他): "; cin >> gender; cin.ignore();
cout << "出生日期(YYYY-MM-DD): "; cin >> birthDate; cin.ignore();
cout << "电话: "; cin >> phone; cin.ignore();
cout << "邮箱: "; cin >> email; cin.ignore();
if (!isValidEmail(email)) {
cout << "❌ 邮箱格式不正确！" << endl;
return;
}
cout << "身份证号: "; cin >> idCard; cin.ignore();
cout << "地址: "; getline(cin, address);
cout << "部门ID: "; cin >> deptId;
cout << "职位ID: "; cin >> positionId;
cout << "入职日期(YYYY-MM-DD): "; cin >> hireDate; cin.ignore();
cout << "合同开始日期(YYYY-MM-DD): "; cin >> contractStart; cin.ignore();
cout << "合同结束日期(YYYY-MM-DD): "; cin >> contractEnd; cin.ignore();
cout << "基本薪资: "; cin >> salary;
cout << "奖金: "; cin >> bonus;
//emm 累死了，懒得测试了，毕竟我也懒得添加数据，就假设你对了
stringstream ss;
ss << "INSERT INTO employees (employee_code, name, gender, birth_date, phone, email, "
<< "id_card, address, department_id, position_id, hire_date, contract_start, "
<< "contract_end, salary, bonus, status) VALUES ('"
<< empCode << "', '" << name << "', '" << gender << "', '" << birthDate << "', '"
<< phone << "', '" << email << "', '" << idCard << "', '" << address << "', "
<< deptId << ", " << positionId << ", '" << hireDate << "', '" << contractStart << "', '"
<< contractEnd << "', " << salary << ", " << bonus << ", '在职')";
executeQuery(ss.str());
pauseScreen();
}
void deleteEmployee() override {
cout << "\n🗑️ 删除员工记录" << endl;
cout << "⚠️ 警告：此操作将永久删除员工记录！" << endl;
cout << "输入员工工号: ";
string empCode;
cin >> empCode;
cin.ignore();
cout << "确认删除？(yes/no): ";
string confirm;
cin >> confirm;
//谁知道你会不会输入Yes或者Y? 相信你不会这么无聊吧
if (confirm == "yes" || confirm == "y") {
string query = "DELETE FROM employees WHERE employee_code = '" + empCode + "'";
executeQuery(query);
} else {
cout << "❌ 操作已取消" << endl;
}
pauseScreen();
}
void manageDepartments() override {
cout << "\n🏢 部门管理" << endl;
cout << "1. 查看部门列表\n2. 添加部门\n3. 更新部门\n4. 删除部门\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
switch (choice) {
case 1: {
displayQueryResult("SELECT * FROM v_department_stats ORDER BY 部门名称");
break;
}
case 2: {
cout << "部门名称: ";
string deptName, deptCode, manager, desc;
getline(cin, deptName);
cout << "部门代码: "; cin >> deptCode; cin.ignore();
cout << "部门经理: "; getline(cin, manager);
cout << "部门描述: "; getline(cin, desc);
string query = "INSERT INTO departments (department_name, department_code, manager_name, description) VALUES ('"
+ deptName + "', '" + deptCode + "', '" + manager + "', '" + desc + "')";
executeQuery(query);
break;
}
case 3: {
cout << "部门ID: ";
int deptId;
cin >> deptId;
cin.ignore();
cout << "新经理姓名: ";
string newManager;
getline(cin, newManager);
string query = "UPDATE departments SET manager_name = '" + newManager +
"' WHERE department_id = " + to_string(deptId);
executeQuery(query);
break;
}
case 4: {
cout << "部门ID: ";
int deptId;
cin >> deptId;
string query = "DELETE FROM departments WHERE department_id = " + to_string(deptId);
executeQuery(query);
break;
}
default:
cout << "❌ 无效选择" << endl;
}
pauseScreen();
}
void managePositions() override {
cout << "\n👔 职位管理" << endl;
cout << "1. 查看职位列表\n2. 添加职位\n3. 更新职位\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
switch (choice) {
case 1: {
displayQueryResult("SELECT position_id, position_name, position_level, min_salary, max_salary FROM positions ORDER BY position_level");
break;
}
case 2: {
string posName, desc;
int level;
double minSalary, maxSalary;
cout << "职位名称: "; getline(cin, posName);
cout << "职位级别(1-10): "; cin >> level;
cout << "最低薪资: "; cin >> minSalary;
cout << "最高薪资: "; cin >> maxSalary;
cin.ignore();
cout << "职位描述: "; getline(cin, desc);
stringstream ss;
ss << "INSERT INTO positions (position_name, position_level, min_salary, max_salary, description) VALUES ('"
<< posName << "', " << level << ", " << minSalary << ", " << maxSalary << ", '" << desc << "')";
executeQuery(ss.str());
break;
}
case 3: {
int posId;
double minSalary, maxSalary;
cout << "职位ID: "; cin >> posId;
cout << "新最低薪资: "; cin >> minSalary;
cout << "新最高薪资: "; cin >> maxSalary;
stringstream ss;
ss << "UPDATE positions SET min_salary = " << minSalary
<< ", max_salary = " << maxSalary << " WHERE position_id = " << posId;
executeQuery(ss.str());
break;
}
default:
cout << "❌ 无效选择" << endl;
}
pauseScreen();
}
void manageSalary() override {
cout << "\n💰 薪资管理" << endl;
cout << "1. 查看薪资记录\n2. 添加薪资记录\n3. 批量调薪\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
switch (choice) {
case 1: {
cout << "输入查询月份(YYYY-MM): ";
string month;
cin >> month;
string query = "SELECT e.name, sr.* FROM salary_records sr "
"JOIN employees e ON sr.employee_id = e.employee_id "
"WHERE DATE_FORMAT(sr.salary_month, '%Y-%m') = '" + month + "' "
"ORDER BY e.name";
displayQueryResult(query);
break;
}
case 2: {
int empId;
string salaryMonth;
double basicSalary, overtimePay, bonus, allowance, deduction, socialInsurance, tax;
cout << "员工ID: "; cin >> empId;
cout << "薪资月份(YYYY-MM-DD): "; cin >> salaryMonth; cin.ignore();
cout << "基本工资: "; cin >> basicSalary;
cout << "加班费: "; cin >> overtimePay;
cout << "奖金: "; cin >> bonus;
cout << "津贴: "; cin >> allowance;
cout << "扣款: "; cin >> deduction;
cout << "社保扣除: "; cin >> socialInsurance;
cout << "个人所得税: "; cin >> tax;
double netSalary = basicSalary + overtimePay + bonus + allowance - deduction - socialInsurance - tax;
stringstream ss;
ss << "INSERT INTO salary_records (employee_id, salary_month, basic_salary, overtime_pay, "
<< "bonus, allowance, deduction, social_insurance, tax, net_salary) VALUES ("
<< empId << ", '" << salaryMonth << "', " << basicSalary << ", " << overtimePay << ", "
<< bonus << ", " << allowance << ", " << deduction << ", " << socialInsurance << ", "
<< tax << ", " << netSalary << ")";
executeQuery(ss.str());
break;
}
case 3: {
cout << "调薪选项:\n1. 按部门调薪\n2. 按职位调薪\n请选择: ";
int adjustChoice;
cin >> adjustChoice;
if (adjustChoice == 1) {
int deptId;
double percentage;
cout << "部门ID: "; cin >> deptId;
cout << "调薪幅度(例如1.1表示上调10%): "; cin >> percentage;
string query = "UPDATE employees SET salary = salary * " + to_string(percentage) +
" WHERE department_id = " + to_string(deptId) + " AND status = '在职'";
executeQuery(query);
} else if (adjustChoice == 2) {
int posId;
double percentage;
cout << "职位ID: "; cin >> posId;
cout << "调薪幅度: "; cin >> percentage;
string query = "UPDATE employees SET salary = salary * " + to_string(percentage) +
" WHERE position_id = " + to_string(posId) + " AND status = '在职'";
executeQuery(query);
}
break;
}
default:
cout << "❌ 无效选择" << endl;
}
pauseScreen();
}
void viewReports() override {
cout << "\n📊 统计报表" << endl;
cout << "1. 部门统计报表\n2. 薪资分析报表\n3. 员工流失分析\n4. 年龄分布分析\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
string query;
switch (choice) {
case 1:
query = "SELECT * FROM v_department_stats ORDER BY 在职员工数 DESC";
break;
case 2:
query = "SELECT "
"CASE "
"WHEN salary < 8000 THEN '8K以下' "
"WHEN salary BETWEEN 8000 AND 12000 THEN '8K-12K' "
"WHEN salary BETWEEN 12001 AND 18000 THEN '12K-18K' "
"WHEN salary BETWEEN 18001 AND 25000 THEN '18K-25K' "
"ELSE '25K以上' END AS 薪资区间, "
"COUNT(*) AS 员工数量, "
"ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees WHERE status = '在职'), 1) AS 占比 "
"FROM employees WHERE status = '在职' "
"GROUP BY CASE "
"WHEN salary < 8000 THEN '8K以下' "
"WHEN salary BETWEEN 8000 AND 12000 THEN '8K-12K' "
"WHEN salary BETWEEN 12001 AND 18000 THEN '12K-18K' "
"WHEN salary BETWEEN 18001 AND 25000 THEN '18K-25K' "
"ELSE '25K以上' END "
"ORDER BY MIN(salary)";
break;
case 3:
query = "SELECT d.department_name AS 部门, "
"COUNT(CASE WHEN e.status = '在职' THEN 1 END) AS 在职人数, "
"COUNT(CASE WHEN e.status = '离职' THEN 1 END) AS 离职人数, "
"ROUND(COUNT(CASE WHEN e.status = '离职' THEN 1 END) * 100.0 / "
"NULLIF(COUNT(e.employee_id), 0), 2) AS 离职率 "
"FROM departments d "
"LEFT JOIN employees e ON d.department_id = e.department_id "
"GROUP BY d.department_id, d.department_name "
"ORDER BY 离职率 DESC";
break;
case 4:
query = "SELECT "
"CASE "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) < 25 THEN '25岁以下' "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 25 AND 30 THEN '25-30岁' "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 31 AND 35 THEN '31-35岁' "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 36 AND 40 THEN '36-40岁' "
"ELSE '40岁以上' END AS 年龄段, "
"COUNT(*) AS 员工数量 "
"FROM employees "
"WHERE status = '在职' AND birth_date IS NOT NULL "
"GROUP BY CASE "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) < 25 THEN '25岁以下' "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 25 AND 30 THEN '25-30岁' "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 31 AND 35 THEN '31-35岁' "
"WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 36 AND 40 THEN '36-40岁' "
"ELSE '40岁以上' END "
"ORDER BY MIN(YEAR(CURDATE()) - YEAR(birth_date))";
break;
default:
cout << "❌ 无效选择" << endl;
return;
}
displayQueryResult(query);
pauseScreen();
}
};
// 系统管理员类
class Admin : public HRManager {
public:
Admin(const string& username) : HRManager(username) { userRole = 1; }
void databaseAdmin() override {
cout << "\n🔧 数据库管理" << endl;
cout << "1. 数据库备份\n2. 数据库恢复\n3. 查看数据库状态\n4. 执行SQL语句\n请选择: ";
int choice;
cin >> choice;
cin.ignore();
switch (choice) {
case 1: backupDatabase(); break;
case 2: restoreDatabase(); break;
case 3: showDatabaseStatus(); break;
case 4: executeSQLCommands(); break;
default: cout << "❌ 无效选择" << endl;
}
pauseScreen();
}
private:
void backupDatabase() {
cout << "输入备份文件名 (不含扩展名): ";
string filename;
cin >> filename;
filename += ".sql";
string cmd = "mysqldump -h" + string(host) + " -uroot -p " + string(db) + " > " + filename;
cout << "正在执行备份，请输入数据库密码..." << endl;
int result = system(cmd.c_str());
if (result == 0) {
cout << "✅ 数据库备份成功！文件: " << filename << endl;
} else {
cout << "❌ 备份失败！" << endl;
}
}
void restoreDatabase() {
cout << "输入恢复文件名: ";
string filename;
cin >> filename;
string cmd = "mysql -h" + string(host) + " -uroot -p " + string(db) + " < " + filename;
cout << "正在执行恢复，请输入数据库密码..." << endl;
int result = system(cmd.c_str());
if (result == 0) {
cout << "✅ 数据库恢复成功！" << endl;
} else {
cout << "❌ 恢复失败！" << endl;
}
}
void showDatabaseStatus() {
cout << "\n📈 数据库状态信息" << endl;
displayQueryResult("SHOW TABLE STATUS FROM employee_management");
cout << "\n📊 数据统计概览:" << endl;
displayQueryResult("SELECT "
"(SELECT COUNT(*) FROM employees) AS 总员工数, "
"(SELECT COUNT(*) FROM employees WHERE status = '在职') AS 在职员工数, "
"(SELECT COUNT(*) FROM departments) AS 部门数, "
"(SELECT COUNT(*) FROM positions) AS 职位数");
}
void executeSQLCommands() {
cout << "\n💻 SQL命令执行器 (输入 'exit' 退出)" << endl;
string command;
while (true) {
cout << "SQL> ";
getline(cin, command);
if (command == "exit" || command == "quit") {
break;
}
if (command.empty()) {
continue;
}
// 检查是否为查询语句
string upperCommand = command;
transform(upperCommand.begin(), upperCommand.end(), upperCommand.begin(), ::toupper);
if (upperCommand.substr(0, 6) == "SELECT" || upperCommand.substr(0, 4) == "SHOW" ||
upperCommand.substr(0, 4) == "DESC" || upperCommand.substr(0, 8) == "DESCRIBE") {
displayQueryResult(command);
} else {
executeQuery(command);
}
}
}
};
// 数据库管理员类
class DBA : public User {
public:
DBA(const string& username) : User(username, 5) {}
void databaseAdmin() override {
Admin admin(currentUser);
admin.databaseAdmin();
}
void viewEmployees() override {
cout << "\n🔍 数据库查询" << endl;
displayQueryResult("SELECT * FROM v_employee_details ORDER BY 部门, 姓名");
pauseScreen();
}
};
// 用户工厂函数
User* createUser(const string& username, const string& password, MYSQL*& conn) {
conn = mysql_init(nullptr);
if (!conn) {
cerr << "❌ MySQL初始化失败！" << endl;
return nullptr;
}
if (!mysql_real_connect(conn, host, username.c_str(), password.c_str(), db, port, nullptr, 0)) {
cerr << "❌ 数据库连接失败: " << mysql_error(conn) << endl;
mysql_close(conn);
return nullptr;
}
// 设置字符集
mysql_set_character_set(conn, "utf8mb4");
// 根据用户名创建对应角色的用户对象
auto it = roleMap.find(username);
if (it == roleMap.end()) {
cout << "❌ 用户名不存在！" << endl;
return nullptr;
}
switch (it->second) {
case 1: return new Admin(username);
case 2: return new HRManager(username);
case 3: return new DeptManager(username);
case 4: return new Employee(username);
case 5: return new DBA(username);
default: return nullptr;
}
}
// 显示主菜单
void showMainMenu(User* user, int userRole) {
clearScreen();
cout << "\n" << string(60, '=') << endl;
cout << "🏢 员工后台管理系统 v2.0" << endl;
cout << string(60, '=') << endl;
// 根据用户角色显示不同菜单
if (userRole == 1) { // 系统管理员
cout << "👤 当前用户: 系统管理员" << endl;
cout << "1. 👥 查看员工信息\n";
cout << "2. ➕ 添加员工\n";
cout << "3. ✏️ 更新员工信息\n";
cout << "4. 🗑️ 删除员工\n";
cout << "5. 🏢 部门管理\n";
cout << "6. 👔 职位管理\n";
cout << "7. 💰 薪资管理\n";
cout << "8. 📊 统计报表\n";
cout << "9. 🔧 数据库管理\n";
cout << "0. 🚪 退出系统\n";
} else if (userRole == 2) { // 人事经理
cout << "👤 当前用户: 人事经理" << endl;
cout << "1. 👥 查看员工信息\n";
cout << "2. ➕ 添加员工\n";
cout << "3. ✏️ 更新员工信息\n";
cout << "4. 🗑️ 删除员工\n";
cout << "5. 🏢 部门管理\n";
cout << "6. 👔 职位管理\n";
cout << "7. 💰 薪资管理\n";
cout << "8. 📊 统计报表\n";
cout << "0. 🚪 退出系统\n";
} else if (userRole == 3) { // 部门经理
cout << "👤 当前用户: 部门经理" << endl;
cout << "1. 👥 查看员工信息\n";
cout << "2. ✏️ 更新员工信息\n";
cout << "0. 🚪 退出系统\n";
} else if (userRole == 4) { // 普通员工
cout << "👤 当前用户: 员工" << endl;
cout << "1. 👥 查看员工信息\n";
cout << "0. 🚪 退出系统\n";
} else if (userRole == 5) { // 数据库管理员
cout << "👤 当前用户: 数据库管理员" << endl;
cout << "1. 👥 查看员工信息\n";
cout << "2. 🔧 数据库管理\n";
cout << "0. 🚪 退出系统\n";
}
cout << string(60, '=') << endl;
cout << "请选择操作: ";
}
// 处理用户菜单选择
void handleMenuChoice(User* user, int userRole, int choice) {
switch (choice) {
case 1: user->viewEmployees(); break;
case 2:
if (userRole <= 2) user->addEmployee();
else if (userRole == 3) user->updateEmployee();
else if (userRole == 5) user->databaseAdmin();
else user->showNoPermission();
break;
case 3: user->updateEmployee(); break;
case 4: user->deleteEmployee(); break;
case 5: user->manageDepartments(); break;
case 6: user->managePositions(); break;
case 7: user->manageSalary(); break;
case 8: user->viewReports(); break;
case 9: user->databaseAdmin(); break;
case 0:
cout << "\n👋 感谢使用员工管理系统！再见！" << endl;
break;
default:
cout << "❌ 无效选择，请重新输入！" << endl;
pauseScreen();
break;
}
}
// 主函数
int main() {
clearScreen();
cout << "\n" << string(60, '=') << endl;
cout << "🏢 员工后台管理系统 v2.0" << endl;
cout << "✨ 基于 MySQL 数据库的完整员工管理解决方案" << endl;
cout << string(60, '=') << endl;
cout << "\n📝 用户角色说明:" << endl;
cout << "• admin - 系统管理员 (最高权限)" << endl;
cout << "• hr_manager - 人事经理" << endl;
cout << "• dept_manager - 部门经理" << endl;
cout << "• employee - 普通员工" << endl;
cout << "• dba - 数据库管理员" << endl;
cout << "\n🔐 请登录:" << endl;
string username, password;
cout << "用户名: ";
cin >> username;
cout << "密码: ";
cin >> password;
cin.ignore(); // 清除缓冲区
User* user = createUser(username, password, conn);
if (!user) {
cout << "❌ 登录失败！请检查用户名和密码。" << endl;
return 1;
}
cout << "✅ 登录成功！欢迎使用员工管理系统，" << username << "！" << endl;
pauseScreen();
int userRole = roleMap[username];
int choice;
do {
showMainMenu(user, userRole);
cin >> choice;
cin.ignore();
if (choice != 0) {
handleMenuChoice(user, userRole, choice);
}
} while (choice != 0);
// 清理资源
delete user;
if (conn) {
mysql_close(conn);
}
return 0;
}
转载自CSDN-专业IT技术社区
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/2301_81831423/article/details/148570423
