MySQL数据库基础练习系列6、考勤管理系统

-- 创建部门表
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID',
    department_name VARCHAR(50) NOT NULL COMMENT '部门名称',
    description TEXT COMMENT '部门描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门信息表';


-- 创建员工表
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    first_name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    department_id INT NOT NULL COMMENT '所属部门ID',
    position VARCHAR(50) NOT NULL COMMENT '职位',
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';


-- 创建考勤规则表
CREATE TABLE attendance_rules (
    rule_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '规则ID',
    department_id INT NOT NULL COMMENT '部门ID',
    work_start_time TIME NOT NULL COMMENT '上班开始时间',
    work_end_time TIME NOT NULL COMMENT '下班结束时间',
    late_threshold TIME COMMENT '迟到阈值',
    early_leave_threshold TIME COMMENT '早退阈值',
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤规则表';


-- 创建考勤记录表
CREATE TABLE attendance_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    employee_id INT NOT NULL COMMENT '员工ID',
    record_date DATE NOT NULL COMMENT '记录日期',
    status ENUM('正常', '迟到', '早退', '请假') NOT NULL COMMENT '考勤状态',
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤记录表';


-- 创建请假表
CREATE TABLE leaves (
    leave_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '请假ID',
    employee_id INT NOT NULL COMMENT '员工ID',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    leave_type VARCHAR(50) NOT NULL COMMENT '请假类型',
    reason TEXT COMMENT '请假原因',
    approved BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否批准',
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='请假信息表';

INSERT INTO departments (department_name, description) VALUES
('技术部', '负责公司的技术开发和维护工作'),
('市场部', '负责公司的市场营销和业务拓展工作'),
('人事部', '负责公司的人力资源管理和招聘工作'),
('财务部', '负责公司的财务管理和会计核算工作'),
('客服部', '负责公司的客户服务和售后支持工作');

INSERT INTO employees (first_name, gender, department_id, position) VALUES
('张三', '男', 1, '软件工程师'),
('李四', '男', 1, '前端工程师'),
('王五', '女', 2, '销售经理'),
('赵六', '女', 3, '人事专员'),
('孙七', '男', 4, '财务主管'),
('周八', '男', 5, '客服代表'),
('吴九', '女', 2, '市场专员'),
('郑十', '女', 4, '出纳'),
('陈十一', '男', 5, '客服经理'),
('卫十二', '男', 3, '人力资源经理');

INSERT INTO attendance_rules (department_id, work_start_time, work_end_time, late_threshold, early_leave_threshold) VALUES
(1, '09:00:00', '18:00:00', '09:30:00', '17:30:00'),
(2, '08:30:00', '17:30:00', '09:00:00', '17:00:00'),
(3, '09:00:00', '17:30:00', '09:15:00', '17:15:00'),
(4, '09:00:00', '18:00:00', '09:15:00', '17:45:00'),
(5, '08:30:00', '18:00:00', '09:00:00', '17:30:00');

INSERT INTO attendance_records (employee_id, record_date, status) VALUES
(1, '2023-10-24', '正常'),
(2, '2023-10-24', '迟到'),
(3, '2023-10-24', '正常'),
(4, '2023-10-24', '请假'),
(5, '2023-10-24', '早退'),
(6, '2023-10-25', '正常'),
(7, '2023-10-25', '正常'),
(8, '2023-10-25', '正常'),
(9, '2023-10-25', '正常'),
(10, '2023-10-25', '正常');

INSERT INTO leaves (employee_id, start_date, end_date, leave_type, reason, approved) VALUES
(4, '2023-10-25', '2023-10-27', '事假', '个人事务处理', TRUE),
(7, '2023-10-30', '2023-10-31', '病假', '身体不适需要休息', TRUE),
(9, '2023-11-01', '2023-11-03', '年假', '计划中的年假', FALSE), -- 假设这条请假还未被批准
(2, '2023-11-05', '2023-11-05', '调休', '上周加班调休', TRUE);