毕业设计

-- 注意：表创建顺序很重要，先创建被引用的表，再创建引用它们的表-- 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 约束名