如何在mysql中设计员工考勤系统_mysql员工考勤项目实战

设计一个MySQL员工考勤系统，关键在于合理的数据表结构、清晰的业务逻辑和高效的数据查询。下面从需求分析到数据库设计再到常用SQL操作，带你一步步完成一个实用的员工考勤系统。

1. 明确考勤系统的功能需求

一个基本的员工考勤系统通常需要支持以下功能：

基于这些需求，我们可以设计对应的数据库表结构。

2. 数据库表结构设计

以下是核心表的设计建议：

（1）员工表（employees）

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    position VARCHAR(50),
    hire_date DATE,
    status TINYINT DEFAULT 1 COMMENT '1-在职, 0-离职'
);

（2）打卡记录表（attendance_records）

CREATE TABLE attendance_records (
    record_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    punch_time DATETIME NOT NULL,
    device_type VARCHAR(20) COMMENT '打卡方式: 手机, 门禁机等',
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

（3）考勤规则表（attendance_rules）

CREATE TABLE attendance_rules (
    rule_id INT PRIMARY KEY AUTO_INCREMENT,
    work_date_type ENUM('weekday', 'weekend', 'holiday') DEFAULT 'weekday',
    start_time TIME DEFAULT '09:00:00',
    end_time TIME DEFAULT '18:00:00',
    late_tolerance INT DEFAULT 10 COMMENT '迟到容忍分钟数',
    early_leave_tolerance INT DEFAULT 10
);

（4）每日考勤汇总表（daily_attendance）

用于存储每日计算结果，提高查询效率。

CREATE TABLE daily_attendance (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    work_date DATE NOT NULL,
    clock_in TIME,
    clock_out TIME,
    status ENUM('正常', '迟到', '早退', '缺卡', '旷工') DEFAULT '正常',
    remarks VARCHAR(200),
    UNIQUE KEY unique_emp_date (emp_id, work_date)
);

3. 实现打卡数据处理逻辑

每次员工打卡，先插入到 attendance_records 表。然后通过定时任务或触发器，按日整理打卡记录，生成每日最早和最晚打卡时间。

示例：提取某员工某天的上下班时间

SELECT 
    emp_id,
    DATE(punch_time) AS work_date,
    MIN(TIME(punch_time)) AS first_punch,
    MAX(TIME(punch_time)) AS last_punch
FROM attendance_records 
WHERE emp_id = 1001 AND DATE(punch_time) = '2024-04-01'
GROUP BY emp_id, DATE(punch_time);

判断是否迟到（假设上班时间是09:00，容忍10分钟）

SELECT 
    emp_id,
    work_date,
    first_punch,
    CASE 
        WHEN first_punch > '09:10:00' THEN '迟到'
        ELSE '正常'
    END AS late_status
FROM (
    SELECT 
        emp_id,
        DATE(punch_time) AS work_date,
        MIN(TIME(punch_time)) AS first_punch
    FROM attendance_records 
    WHERE DATE(punch_time) = '2024-04-01'
    GROUP BY emp_id, DATE(punch_time)
) t;

4. 自动生成每日考勤汇总

可以写一个存储过程，每天凌晨运行，处理前一天的打卡数据并更新 daily_attendance 表。

简化版存储过程逻辑示意：

DELIMITER //
CREATE PROCEDURE GenerateDailyAttendance(IN target_date DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_emp_id INT;
    DECLARE v_first_punch, v_last_punch TIME;
    DECLARE cur CURSOR FOR 
        SELECT emp_id, MIN(TIME(punch_time)), MAX(TIME(punch_time))
        FROM attendance_records 
        WHERE DATE(punch_time) = target_date
        GROUP BY emp_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
<pre class='brush:php;toolbar:false;'>OPEN cur;
read_loop: LOOP
    FETCH cur INTO v_emp_id, v_first_punch, v_last_punch;
    IF done THEN LEAVE read_loop; END IF;
    -- 判断状态（简化）
    INSERT INTO daily_attendance (emp_id, work_date, clock_in, clock_out, status)
    VALUES (
        v_emp_id, 
        target_date, 
        v_first_punch, 
        v_last_punch,
        CASE 
            WHEN v_first_punch IS NULL THEN '旷工'
            WHEN v_first_punch > '09:10:00' THEN '迟到'
            WHEN v_last_punch < '17:50:00' THEN '早退'
            ELSE '正常'
        END
    ) ON DUPLICATE KEY UPDATE 
        clock_in = v_first_punch,
        clock_out = v_last_punch,
        status = CASE 
            WHEN v_first_punch IS NULL THEN '旷工'
            WHEN v_first_punch > '09:10:00' THEN '迟到'
            WHEN v_last_punch < '17:50:00' THEN '早退'
            ELSE '正常'
        END;
END LOOP;
CLOSE cur;

END// DELIMITER ;

5. 常用查询示例

查询某员工月度考勤统计

SELECT 
    status,
    COUNT(*) AS days
FROM daily_attendance 
WHERE emp_id = 1001 AND work_date BETWEEN '2024-04-01' AND '2024-04-30'
GROUP BY status;

查询所有员工某天的考勤情况

SELECT 
    e.emp_name,
    d.clock_in,
    d.clock_out,
    d.status
FROM daily_attendance d
JOIN employees e ON d.emp_id = e.emp_id
WHERE d.work_date = '2024-04-01';