毕业设计

-- 注意：表创建顺序很重要，先创建被引用的表，再创建引用它们的表

-- 1. 首先创建学院表 (collage) - 被多个表引用
CREATE TABLE collage (
    collage_id INT PRIMARY KEY AUTO_INCREMENT,
    collage_name VARCHAR(100) NOT NULL COMMENT '学院名称',
    status TINYINT DEFAULT 1 COMMENT '状态：1-启用 0-停用'
) COMMENT '学院表';

-- 2. 创建工作室管理员表 (workroom_admin)
CREATE TABLE workroom_admin (
    admin_id INT PRIMARY KEY AUTO_INCREMENT,
    admin_no VARCHAR(20) UNIQUE NOT NULL COMMENT '管理员编号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    phone VARCHAR(20) COMMENT '联系电话'
) COMMENT '工作室管理员表';

-- 3. 创建工作室表 (workroom) - 引用学院表和管理员表
CREATE TABLE workroom (
    workroom_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '工作室ID',
    workroom_name VARCHAR(100) NOT NULL COMMENT '工作室名称',
    collage_id INT NOT NULL COMMENT '所属学院ID',
    manager_id INT COMMENT '管理员ID',
    location VARCHAR(200) COMMENT '位置',
    status TINYINT DEFAULT 1 COMMENT '状态：1-启用 0-停用',
    INDEX idx_collage (collage_id),
    FOREIGN KEY (collage_id) REFERENCES collage(collage_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES workroom_admin(admin_id) ON DELETE SET NULL ON UPDATE CASCADE
) COMMENT '工作室表';

-- 4. 创建学生表 (student) - 引用学院表和工作室表
CREATE TABLE student (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_no VARCHAR(20) UNIQUE NOT NULL COMMENT '学号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    collage_id INT NOT NULL COMMENT '学院ID',
    workroom_id INT COMMENT '工作室ID',
    contact_info VARCHAR(100) COMMENT '联系方式',
    email VARCHAR(100) COMMENT '邮箱',
    status TINYINT DEFAULT 1 COMMENT '状态：1-正常 2-禁用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_student_no (student_no),
    INDEX idx_workroom (workroom_id),
    INDEX idx_collage (collage_id),
    FOREIGN KEY (collage_id) REFERENCES collage(collage_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (workroom_id) REFERENCES workroom(workroom_id) ON DELETE SET NULL ON UPDATE CASCADE
) COMMENT '学生表';

-- 5. 创建人脸特征表 (face_features) - 引用学生表
CREATE TABLE face_features (
    face_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL COMMENT '学生ID',
    face_embedding BLOB NOT NULL COMMENT '人脸特征向量(512维float数组)',
    face_image_path VARCHAR(255) NOT NULL COMMENT '人脸图像存储路径',
    is_active TINYINT DEFAULT 1 COMMENT '是否启用：1-启用 0-禁用',
    model_version VARCHAR(20) COMMENT '特征提取模型版本',
    UNIQUE INDEX uk_student_active (student_id, is_active),
    INDEX idx_student_id (student_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '人脸特征表';

-- 6. 创建设备表 (device) - 引用学院表和工作室表
CREATE TABLE device (
    device_id INT PRIMARY KEY AUTO_INCREMENT,
    device_sn VARCHAR(50) UNIQUE NOT NULL COMMENT '设备序列号',
    device_name VARCHAR(100) NOT NULL COMMENT '设备名称',
    collage_id INT NOT NULL COMMENT '所属学院ID',
    workroom_id INT COMMENT '所属工作室ID',
    INDEX idx_collage (collage_id),
    INDEX idx_workroom (workroom_id),
    FOREIGN KEY (collage_id) REFERENCES collage(collage_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (workroom_id) REFERENCES workroom(workroom_id) ON DELETE SET NULL ON UPDATE CASCADE
) COMMENT '设备表';

-- 7. 创建考勤记录表 (attendance_records) - 引用学生表、工作室表、设备表
CREATE TABLE attendance_records (
    record_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    student_id INT NOT NULL COMMENT '学生ID',
    workroom_id INT NOT NULL COMMENT '工作室ID',
    check_time DATETIME NOT NULL COMMENT '打卡时间',
    check_type TINYINT NOT NULL COMMENT '打卡类型：1-进入 2-出去',
    device_id INT NOT NULL COMMENT '设备ID',
    face_match_score DECIMAL(5,4) COMMENT '人脸匹配分数(0-1)',
    confidence DECIMAL(5,4) COMMENT '置信度(0-1)',
    image_path VARCHAR(255) COMMENT '打卡时抓拍图片路径',
    status TINYINT DEFAULT 0 COMMENT '状态：0-待确认 1-有效 2-无效 3-异常',
    INDEX idx_student_time (student_id, check_time),
    INDEX idx_workroom_time (workroom_id, check_time),
    FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (workroom_id) REFERENCES workroom(workroom_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (device_id) REFERENCES device(device_id) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '考勤记录表';

-- 8. 创建在线时长表 (online_duration) - 引用学生表、学院表、工作室表
CREATE TABLE online_duration (
    duration_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL COMMENT '学生ID',
    collage_id INT NOT NULL COMMENT '所属学院ID',
    workroom_id INT NOT NULL COMMENT '所属工作室ID',
    date DATE NOT NULL COMMENT '日期',
    start_time DATETIME COMMENT '开始时间',
    end_time DATETIME COMMENT '结束时间',
    duration_minutes INT DEFAULT 0 COMMENT '工作时长(分钟)',
    last_duration_minutes INT DEFAULT 0 COMMENT '截至上次总工作时长(分钟)',
    status TINYINT DEFAULT 0 COMMENT '状态：0-进行中 1-已结束',
    UNIQUE INDEX uk_student_date (student_id, date),
    INDEX idx_date (date),
    INDEX idx_workroom_date (workroom_id, date),
    FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (collage_id) REFERENCES collage(collage_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (workroom_id) REFERENCES workroom(workroom_id) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '在线时长表';

外键约束说明：

删除和更新规则：

1. ON DELETE CASCADE：当父表记录被删除时，子表相关记录也被删除

   • 适用于：学生删除时，其考勤记录、人脸特征等也应删除

2. ON DELETE SET NULL：当父表记录被删除时，子表外键字段设为NULL

   • 适用于：工作室删除时，学生的workroom_id设为NULL

3. ON UPDATE CASCADE：当父表主键更新时，子表外键同步更新

外键关系汇总表：

 

子表	外键字段	父表	删除规则	更新规则
workroom	collage_id	collage	CASCADE	CASCADE
workroom	manager_id	workroom_admin	SET NULL	CASCADE
student	collage_id	collage	CASCADE	CASCADE
student	workroom_id	workroom	SET NULL	CASCADE
face_features	student_id	student	CASCADE	CASCADE
device	collage_id	collage	CASCADE	CASCADE
device	workroom_id	workroom	SET NULL	CASCADE
attendance_records	student_id	student	CASCADE	CASCADE
attendance_records	workroom_id	workroom	CASCADE	CASCADE
attendance_records	device_id	device	CASCADE	CASCADE
online_duration	student_id	student	CASCADE	CASCADE
online_duration	collage_id	collage	CASCADE	CASCADE
online_duration	workroom_id	workroom	CASCADE	CASCADE

使用建议：

1. 执行顺序：必须按照上述顺序执行SQL语句

2. 测试数据：插入测试数据时也要注意依赖关系

3. 性能考虑：外键会增加数据操作的约束检查，但对数据完整性很重要

4. 如果需要移除外键：可以使用 ALTER TABLE 表名 DROP FOREIGN KEY 约束名

一、数据库插入数据

sql

-- 1. 插入学院数据（2个学院）
INSERT INTO collage (collage_id, collage_name, status) VALUES
(1, '计算机科学与技术学院', 1),
(2, '电子信息工程学院', 1);

-- 2. 插入工作室管理员数据（5个管理员）
INSERT INTO workroom_admin (admin_id, admin_no, name, phone) VALUES
(1, 'ADM001', '张明', '13800138001'),
(2, 'ADM002', '李华', '13800138002'),
(3, 'ADM003', '王强', '13800138003'),
(4, 'ADM004', '赵静', '13800138004'),
(5, 'ADM005', '刘洋', '13800138005');

-- 3. 插入工作室数据（5个工作室）
INSERT INTO workroom (workroom_id, workroom_name, collage_id, manager_id, location, status) VALUES
(1, '人工智能实验室', 1, 1, '科技楼301', 1),
(2, '软件开发工作室', 1, 2, '科技楼302', 1),
(3, '嵌入式系统实验室', 2, 3, '工程楼201', 1),
(4, '智能硬件工作室', 2, 4, '工程楼202', 1),
(5, '创新创业中心', 1, 5, '创业园101', 1);

-- 4. 插入学生数据（30个学生）
INSERT INTO student (student_id, student_no, name, collage_id, workroom_id, contact_info, email, status) VALUES
-- 计算机学院学生（前20个）
(1, '202301001', '张三', 1, 1, '13800138101', 'zhangsan001@edu.cn', 1),
(2, '202301002', '李四', 1, 1, '13800138102', 'lisi002@edu.cn', 1),
(3, '202301003', '王五', 1, 1, '13800138103', 'wangwu003@edu.cn', 1),
(4, '202301004', '赵六', 1, 1, '13800138104', 'zhaoliu004@edu.cn', 1),
(5, '202301005', '钱七', 1, 1, '13800138105', 'qianqi005@edu.cn', 1),
(6, '202301006', '孙八', 1, 2, '13800138106', 'sunba006@edu.cn', 1),
(7, '202301007', '周九', 1, 2, '13800138107', 'zhoujiu007@edu.cn', 1),
(8, '202301008', '吴十', 1, 2, '13800138108', 'wushi008@edu.cn', 1),
(9, '202301009', '郑十一', 1, 2, '13800138109', 'zheng11@edu.cn', 1),
(10, '202301010', '王十二', 1, 2, '13800138110', 'wang12@edu.cn', 1),
(11, '202301011', '李明', 1, 2, '13800138111', 'liming011@edu.cn', 1),
(12, '202301012', '张伟', 1, 2, '13800138112', 'zhangwei012@edu.cn', 1),
(13, '202301013', '刘芳', 1, 2, '13800138113', 'liufang013@edu.cn', 1),
(14, '202301014', '陈静', 1, 5, '13800138114', 'chenjing014@edu.cn', 1),
(15, '202301015', '杨帆', 1, 5, '13800138115', 'yangfan015@edu.cn', 1),
(16, '202301016', '黄磊', 1, 5, '13800138116', 'huanglei016@edu.cn', 1),
(17, '202301017', '周杰', 1, 5, '13800138117', 'zhoujie017@edu.cn', 1),
(18, '202301018', '吴彦祖', 1, 5, '13800138118', 'wuyanzu018@edu.cn', 1),
(19, '202301019', '林志玲', 1, 5, '13800138119', 'linzhiling019@edu.cn', 1),
(20, '202301020', '郭富城', 1, 5, '13800138120', 'guofucheng020@edu.cn', 1),
-- 电子信息工程学院学生（后10个）
(21, '202302001', '刘德华', 2, 3, '13800138201', 'liudehua021@edu.cn', 1),
(22, '202302002', '张学友', 2, 3, '13800138202', 'zhangxueyou022@edu.cn', 1),
(23, '202302003', '黎明', 2, 3, '13800138203', 'liming023@edu.cn', 1),
(24, '202302004', '郭靖', 2, 3, '13800138204', 'guojing024@edu.cn', 1),
(25, '202302005', '黄蓉', 2, 3, '13800138205', 'huangrong025@edu.cn', 1),
(26, '202302006', '杨过', 2, 4, '13800138206', 'yangguo026@edu.cn', 1),
(27, '202302007', '小龙女', 2, 4, '13800138207', 'xiaolongnv027@edu.cn', 1),
(28, '202302008', '令狐冲', 2, 4, '13800138208', 'linghuchong028@edu.cn', 1),
(29, '202302009', '任盈盈', 2, 4, '13800138209', 'renyingying029@edu.cn', 1),
(30, '202302010', '东方不败', 2, 4, '13800138210', 'dongfangbubai030@edu.cn', 1);

-- 5. 插入人脸特征数据
INSERT INTO face_features (student_id, face_embedding, face_image_path, is_active, model_version) VALUES
(1, UNHEX(REPEAT('A1B2C3D4', 16)), '/faces/student_001.jpg', 1, 'v1.2'),
(2, UNHEX(REPEAT('E5F6A7B8', 16)), '/faces/student_002.jpg', 1, 'v1.2'),
(3, UNHEX(REPEAT('C9D0E1F2', 16)), '/faces/student_003.jpg', 1, 'v1.2'),
(4, UNHEX(REPEAT('A3B4C5D6', 16)), '/faces/student_004.jpg', 1, 'v1.2'),
(5, UNHEX(REPEAT('E7F8A9B0', 16)), '/faces/student_005.jpg', 1, 'v1.2'),
(6, UNHEX(REPEAT('C1D2E3F4', 16)), '/faces/student_006.jpg', 1, 'v1.2'),
(7, UNHEX(REPEAT('A5B6C7D8', 16)), '/faces/student_007.jpg', 1, 'v1.2'),
(8, UNHEX(REPEAT('E9F0A1B2', 16)), '/faces/student_008.jpg', 1, 'v1.2'),
(9, UNHEX(REPEAT('C3D4E5F6', 16)), '/faces/student_009.jpg', 1, 'v1.2'),
(10, UNHEX(REPEAT('A7B8C9D0', 16)), '/faces/student_010.jpg', 1, 'v1.2'),
(11, UNHEX(REPEAT('E1F2A3B4', 16)), '/faces/student_011.jpg', 1, 'v1.2'),
(12, UNHEX(REPEAT('C5D6E7F8', 16)), '/faces/student_012.jpg', 1, 'v1.2'),
(13, UNHEX(REPEAT('A9B0C1D2', 16)), '/faces/student_013.jpg', 1, 'v1.2'),
(14, UNHEX(REPEAT('E3F4A5B6', 16)), '/faces/student_014.jpg', 1, 'v1.2'),
(15, UNHEX(REPEAT('C7D8E9F0', 16)), '/faces/student_015.jpg', 1, 'v1.2'),
(16, UNHEX(REPEAT('A1B2C3D4', 16)), '/faces/student_016.jpg', 1, 'v1.2'),
(17, UNHEX(REPEAT('E5F6A7B8', 16)), '/faces/student_017.jpg', 1, 'v1.2'),
(18, UNHEX(REPEAT('C9D0E1F2', 16)), '/faces/student_018.jpg', 1, 'v1.2'),
(19, UNHEX(REPEAT('A3B4C5D6', 16)), '/faces/student_019.jpg', 1, 'v1.2'),
(20, UNHEX(REPEAT('E7F8A9B0', 16)), '/faces/student_020.jpg', 1, 'v1.2'),
(21, UNHEX(REPEAT('C1D2E3F4', 16)), '/faces/student_021.jpg', 1, 'v1.2'),
(22, UNHEX(REPEAT('A5B6C7D8', 16)), '/faces/student_022.jpg', 1, 'v1.2'),
(23, UNHEX(REPEAT('E9F0A1B2', 16)), '/faces/student_023.jpg', 1, 'v1.2'),
(24, UNHEX(REPEAT('C3D4E5F6', 16)), '/faces/student_024.jpg', 1, 'v1.2'),
(25, UNHEX(REPEAT('A7B8C9D0', 16)), '/faces/student_025.jpg', 1, 'v1.2'),
(26, UNHEX(REPEAT('E1F2A3B4', 16)), '/faces/student_026.jpg', 1, 'v1.2'),
(27, UNHEX(REPEAT('C5D6E7F8', 16)), '/faces/student_027.jpg', 1, 'v1.2'),
(28, UNHEX(REPEAT('A9B0C1D2', 16)), '/faces/student_028.jpg', 1, 'v1.2'),
(29, UNHEX(REPEAT('E3F4A5B6', 16)), '/faces/student_029.jpg', 1, 'v1.2'),
(30, UNHEX(REPEAT('C7D8E9F0', 16)), '/faces/student_030.jpg', 1, 'v1.2');

-- 6. 插入设备数据
INSERT INTO device (device_id, device_sn, device_name, collage_id, workroom_id) VALUES
(1, 'DEV20231001', '人脸识别考勤机-AI实验室', 1, 1),
(2, 'DEV20231002', '人脸识别考勤机-软件工作室', 1, 2),
(3, 'DEV20231003', '智能考勤机-嵌入式实验室', 2, 3),
(4, 'DEV20231004', '人脸识别门禁-硬件工作室', 2, 4),
(5, 'DEV20231005', '创新中心考勤机', 1, 5);

-- 7. 插入考勤记录数据（每个学生5条记录，共150条）
-- 由于数据较多，这里只展示前10条示例，实际使用时可以循环生成
INSERT INTO attendance_records (student_id, workroom_id, check_time, check_type, device_id, face_match_score, confidence, image_path, status) VALUES
-- 学生1的考勤记录
(1, 1, '2023-10-01 08:30:15', 1, 1, 0.985, 0.960, '/attendance/20231001_083015_1.jpg', 1),
(1, 1, '2023-10-01 17:45:30', 2, 1, 0.982, 0.958, '/attendance/20231001_174530_1.jpg', 1),
(1, 1, '2023-10-02 08:45:20', 1, 1, 0.988, 0.965, '/attendance/20231002_084520_1.jpg', 1),
(1, 1, '2023-10-02 18:00:10', 2, 1, 0.980, 0.955, '/attendance/20231002_180010_1.jpg', 1),
(1, 1, '2023-10-03 09:00:00', 1, 1, 0.975, 0.950, '/attendance/20231003_090000_1.jpg', 2),
-- 学生2的考勤记录
(2, 1, '2023-10-01 08:25:10', 1, 1, 0.990, 0.970, '/attendance/20231001_082510_2.jpg', 1),
(2, 1, '2023-10-01 17:50:45', 2, 1, 0.985, 0.965, '/attendance/20231001_175045_2.jpg', 1),
(2, 1, '2023-10-02 08:35:30', 1, 1, 0.992, 0.975, '/attendance/20231002_083530_2.jpg', 1),
(2, 1, '2023-10-02 18:05:20', 2, 1, 0.988, 0.968, '/attendance/20231002_180520_2.jpg', 1),
(2, 1, '2023-10-03 08:40:15', 1, 1, 0.980, 0.960, '/attendance/20231003_084015_2.jpg', 1);

-- 注：剩余140条记录可以按照类似格式生成

-- 8. 插入在线时长数据（每个学生3天记录，共90条）
INSERT INTO online_duration (student_id, collage_id, workroom_id, date, start_time, end_time, duration_minutes, last_duration_minutes, status) VALUES
-- 学生1的时长记录
(1, 1, 1, '2023-10-01', '2023-10-01 08:30:15', '2023-10-01 17:45:30', 555, 0, 1),
(1, 1, 1, '2023-10-02', '2023-10-02 08:45:20', '2023-10-02 18:00:10', 555, 555, 1),
(1, 1, 1, '2023-10-03', '2023-10-03 09:00:00', '2023-10-03 17:30:00', 510, 1110, 1),
-- 学生2的时长记录
(2, 1, 1, '2023-10-01', '2023-10-01 08:25:10', '2023-10-01 17:50:45', 565, 0, 1),
(2, 1, 1, '2023-10-02', '2023-10-02 08:35:30', '2023-10-02 18:05:20', 570, 565, 1),
(2, 1, 1, '2023-10-03', '2023-10-03 08:40:15', '2023-10-03 17:45:00', 545, 1135, 1);
-- 注：剩余84条记录可以按照类似格式生成

二、Python代码生成模拟图片文件

python

import os
from PIL import Image, ImageDraw, ImageFont
import random

# 创建目录
def create_directories():
    directories = ['./faces', './attendance']
    for directory in directories:
        if not os.path.exists(directory):
            os.makedirs(directory)
            print(f"创建目录: {directory}")

# 生成学生人脸图片
def generate_student_faces():
    print("生成学生人脸图片...")
    for i in range(1, 31):
        # 创建新图片
        img = Image.new('RGB', (300, 300), color=(random.randint(200, 255), 
                                                  random.randint(200, 255), 
                                                  random.randint(200, 255)))
        draw = ImageDraw.Draw(img)
        
        # 画一个简单的脸
        # 脸
        draw.ellipse([50, 50, 250, 250], fill=(255, 220, 180), outline=(0, 0, 0), width=2)
        # 眼睛
        draw.ellipse([100, 100, 130, 130], fill=(0, 0, 0))
        draw.ellipse([170, 100, 200, 130], fill=(0, 0, 0))
        # 嘴巴
        draw.arc([100, 150, 200, 200], 0, 180, fill=(0, 0, 0), width=3)
        
        # 添加文字
        try:
            font = ImageFont.truetype("arial.ttf", 30)
        except:
            font = ImageFont.load_default()
        
        text = f"Student {i:03d}"
        draw.text((75, 250), text, fill=(0, 0, 0), font=font)
        
        # 保存图片
        filename = f"./faces/student_{i:03d}.jpg"
        img.save(filename)
        print(f"生成: {filename}")

# 生成考勤抓拍图片
def generate_attendance_images():
    print("生成考勤抓拍图片...")
    
    # 模拟生成一些考勤图片
    attendance_count = 0
    for day in range(1, 4):  # 3天
        for hour in [8, 9, 14, 17]:  # 4个时间段
            for student_id in range(1, 31):  # 30个学生
                if random.random() > 0.7:  # 70%的出勤率
                    attendance_count += 1
                    
                    # 创建图片
                    img = Image.new('RGB', (640, 480), color=(240, 240, 240))
                    draw = ImageDraw.Draw(img)
                    
                    # 画一个简单的人脸轮廓
                    face_x = 320
                    face_y = 240
                    
                    # 脸
                    draw.ellipse([face_x-80, face_y-100, face_x+80, face_y+100], 
                                fill=(255, 220, 180), outline=(100, 100, 100), width=3)
                    
                    # 添加文字信息
                    try:
                        font = ImageFont.truetype("arial.ttf", 20)
                        font_small = ImageFont.truetype("arial.ttf", 16)
                    except:
                        font = ImageFont.load_default()
                        font_small = ImageFont.load_default()
                    
                    # 顶部信息
                    time_str = f"2023-10-0{day} {hour:02d}:{random.randint(10, 59):02d}:{random.randint(10, 59):02d}"
                    draw.text((10, 10), f"时间: {time_str}", fill=(0, 0, 0), font=font)
                    draw.text((10, 40), f"学号: 2023{student_id:03d}", fill=(0, 0, 0), font=font)
                    draw.text((10, 70), f"姓名: Student {student_id:03d}", fill=(0, 0, 0), font=font)
                    
                    # 底部信息
                    score = round(random.uniform(0.85, 0.99), 3)
                    confidence = round(random.uniform(0.90, 0.98), 3)
                    draw.text((10, 400), f"匹配分数: {score}", fill=(0, 100, 0), font=font_small)
                    draw.text((10, 430), f"置信度: {confidence}", fill=(0, 100, 0), font=font_small)
                    
                    # 保存图片
                    filename = f"./attendance/2023100{day}_{hour:02d}{random.randint(10,59):02d}_{student_id}.jpg"
                    img.save(filename)
                    
                    if attendance_count % 50 == 0:
                        print(f"已生成 {attendance_count} 张考勤图片...")
    
    print(f"总共生成 {attendance_count} 张考勤图片")

# 生成图片文件的SQL更新语句
def generate_image_sql():
    print("\n生成图片路径SQL更新语句...")
    
    # 更新人脸特征表的图片路径
    print("\n-- 更新人脸特征表图片路径")
    for i in range(1, 31):
        print(f"UPDATE face_features SET face_image_path = './faces/student_{i:03d}.jpg' WHERE student_id = {i};")
    
    # 更新考勤记录表的图片路径（示例）
    print("\n-- 更新考勤记录表图片路径（示例）")
    print("UPDATE attendance_records SET image_path = './attendance/20231001_083015_1.jpg' WHERE record_id = 1;")
    print("UPDATE attendance_records SET image_path = './attendance/20231001_174530_1.jpg' WHERE record_id = 2;")

# 主函数
def main():
    print("开始生成测试数据...")
    
    # 1. 创建目录
    create_directories()
    
    # 2. 生成学生人脸图片
    generate_student_faces()
    
    # 3. 生成考勤抓拍图片
    generate_attendance_images()
    
    # 4. 生成SQL更新语句
    generate_image_sql()
    
    print("\n完成！图片文件已生成到以下目录：")
    print("人脸图片: ./faces/")
    print("考勤图片: ./attendance/")

if __name__ == "__main__":
    main()

三、批量生成剩余数据的Python脚本

python

import random
from datetime import datetime, timedelta

def generate_remaining_attendance():
    """生成剩余的考勤记录SQL"""
    print("-- 生成剩余的考勤记录数据")
    
    # 从第11条记录开始（前10条已手动插入）
    record_id = 11
    
    # 日期范围：2023年10月1日到10月7日
    start_date = datetime(2023, 10, 1)
    
    for day in range(7):  # 7天
        current_date = start_date + timedelta(days=day)
        date_str = current_date.strftime('%Y-%m-%d')
        
        for student_id in range(1, 31):  # 30个学生
            # 获取学生的工作室ID
            if student_id <= 5:
                workroom_id = 1
                device_id = 1
            elif student_id <= 13:
                workroom_id = 2
                device_id = 2
            elif student_id <= 15:
                workroom_id = 5
                device_id = 5
            elif student_id <= 20:
                workroom_id = 5
                device_id = 5
            elif student_id <= 25:
                workroom_id = 3
                device_id = 3
            else:
                workroom_id = 4
                device_id = 4
            
            # 80%的出勤率
            if random.random() <= 0.8:
                # 上班打卡（早上8:00-9:30之间）
                check_in_hour = 8 + random.randint(0, 1)
                check_in_minute = random.randint(0, 59)
                check_in_second = random.randint(0, 59)
                
                # 下班打卡（下午17:00-19:00之间）
                check_out_hour = 17 + random.randint(0, 2)
                check_out_minute = random.randint(0, 59)
                check_out_second = random.randint(0, 59)
                
                # 生成上班打卡记录
                print(f"({student_id}, {workroom_id}, '{date_str} {check_in_hour:02d}:{check_in_minute:02d}:{check_in_second:02d}', 1, {device_id}, {random.uniform(0.85, 0.99):.3f}, {random.uniform(0.90, 0.98):.3f}, '/attendance/{current_date.strftime('%Y%m%d')}_{check_in_hour:02d}{check_in_minute:02d}_{student_id}_in.jpg', {1 if check_in_hour < 9 else 2}),")
                record_id += 1
                
                # 生成下班打卡记录
                print(f"({student_id}, {workroom_id}, '{date_str} {check_out_hour:02d}:{check_out_minute:02d}:{check_out_second:02d}', 2, {device_id}, {random.uniform(0.85, 0.99):.3f}, {random.uniform(0.90, 0.98):.3f}, '/attendance/{current_date.strftime('%Y%m%d')}_{check_out_hour:02d}{check_out_minute:02d}_{student_id}_out.jpg', 1),")
                record_id += 1

def generate_remaining_duration():
    """生成剩余的在线时长SQL"""
    print("\n-- 生成剩余的在线时长数据")
    
    duration_id = 7  # 从第7条开始（前6条已手动插入）
    
    start_date = datetime(2023, 10, 1)
    
    for day in range(7):  # 7天
        current_date = start_date + timedelta(days=day)
        date_str = current_date.strftime('%Y-%m-%d')
        
        for student_id in range(1, 31):  # 30个学生
            # 获取学生的工作室和学院ID
            if student_id <= 20:
                collage_id = 1
                if student_id <= 5:
                    workroom_id = 1
                elif student_id <= 13:
                    workroom_id = 2
                else:
                    workroom_id = 5
            else:
                collage_id = 2
                if student_id <= 25:
                    workroom_id = 3
                else:
                    workroom_id = 4
            
            # 生成工作时长（480-600分钟）
            duration = random.randint(480, 600)
            # 模拟累计时长
            last_duration = (day * 30 + student_id) * 30
            
            print(f"({student_id}, {collage_id}, {workroom_id}, '{date_str}', '{date_str} 08:{random.randint(10, 45):02d}:00', '{date_str} 17:{random.randint(10, 59):02d}:00', {duration}, {last_duration}, 1),")
            duration_id += 1

# 运行生成脚本
if __name__ == "__main__":
    print("生成剩余数据的SQL语句：")
    generate_remaining_attendance()
    generate_remaining_duration()

使用说明：

1. 先执行SQL插入语句：将第一部分的所有SQL语句执行到数据库中

2. 运行Python脚本生成图片：执行第二部分Python代码生成图片文件

3. 更新数据库中的图片路径：执行生成的SQL更新语句（可选）

这样你就有了完整的测试数据，包括：

• 2个学院

• 5个工作室

• 5个管理员

• 30个学生

• 对应的图片文件（模拟生成）