课程设计：大创管理系统

作者：肖嘉念，赵美凤 更新日期：2026-06-28

---

项目概述

大学生创新创业训练计划（简称"大创"）是高校培养学生创新实践能力的重要平台。然而，传统的大创项目管理往往依赖纸质文件和线下流程，存在信息不透明、申报流程繁琐、数据统计困难等问题。

本系统基于 Flask + MySQL 技术栈，开发了一套面向学生和管理员双角色的 Web 大创项目管理系统，实现了项目申报、审核、查询和统计的全流程数字化管理。

项目规模： - 后端代码：约 700 行 Python（Flask） - 前端代码：约 3 个 HTML 页面（学生端、管理员端、登录页） - 数据库：9 张关系表 + 1 个视图 + 1 个触发器 - 技术栈：Flask 2.0 / MySQL 8.0 / HTML5 + ES6+ / PyMySQL

---

需求分析

功能需求

系统分为 学生 和 管理员 两大角色，各自拥有独立的操作界面和权限。

学生端功能

功能模块	功能描述
登录认证	输入学号选择学生角色登录，系统验证学号是否存在
个人信息	查看个人学籍信息（学号、姓名、专业、班级、学籍状态等）
我的项目	查看个人参与的所有项目列表（含省级/国家级和校级），点击查看项目详情
项目查询	按项目级别、类型、年份、状态进行多条件组合筛选查询
公告通知	查看管理员发布的最新公告（通知/公告/紧急分类）

管理员端功能

功能模块	功能描述
统计概览	仪表盘展示学生总数、教师总数、项目数、经费总额、待审核报告数等
公告管理	发布公告（通知/公告/紧急）、撤回已发布公告
报告审核	查看所有申报书，对待审核申报书进行通过/拒绝操作（含审核意见）
学生管理	学生信息的增删改查（学号、姓名、学院、专业、班级、学籍状态）
教师管理	指导教师信息的增删改查（工号、姓名、职称、专业、状态）
项目管理	省级/国家级和校级项目的增删改查，按级别子标签切换查看

非功能需求

需求类型	具体指标
并发支持	支持 1000+ 用户同时使用
响应时间	系统响应时间 < 2 秒
数据完整性	外键约束、枚举类型字段保证数据一致性
审核留痕	所有审核操作记录到独立审核记录表，支持事后追溯
文件存储	支持申报书文件上传与下载

---

数据库设计

E-R 图设计

系统包含 7 个核心实体 和 2 个从属实体：

核心实体： - 学生（students） - 指导教师（teachers） - 省级/国家级项目（prov_projects） - 校级项目（uni_projects） - 项目成员（project_members） - 管理员（admins） - 公告（announcements）

从属实体： - 申报书（project_reports） - 审核记录（audit_records）

实体间关系： - 学生与项目之间为 多对多 关系，通过 project_members 关联表实现 - 教师与项目之间为 一对多 指导关系（一个项目有 1-2 名指导教师） - 管理员与公告之间为 一对多 发布关系 - 申报书与审核记录之间为 一对多 关系

表结构设计

E-R 图转换后得到 9 张关系表：

表名	说明	主键	核心字段
students	学生表	sno	sname, school, grade, major, class, status
teachers	指导教师表	tno	tname, title, major, status
prov_projects	省级/国家级项目表	pno	pname, level, type, year, funds, status
uni_projects	校级项目表	pno	pname, level, type, year, funds, status, final_grade
project_members	项目成员关联表	(pno, sno)	plevel, role, join_date, status
admins	管理员表	admin_id	admin_name, password, role
announcements	公告表	id	title, content, type, publish_date, status
project_reports	项目申报书表	id	pno, plevel, report_path, status
audit_records	审核记录表	id	report_id, admin_id, audit_opinion, audit_status

建表语句

-- 1. 学生表
CREATE TABLE students (
    sno VARCHAR(10) NOT NULL COMMENT '学号' PRIMARY KEY,
    sname VARCHAR(50) NOT NULL COMMENT '学生姓名',
    school VARCHAR(20) NULL COMMENT '所在学院',
    grade VARCHAR(5) NOT NULL COMMENT '年级',
    major VARCHAR(50) NOT NULL COMMENT '专业名称',
    class VARCHAR(20) NOT NULL COMMENT '班级名称',
    status ENUM('在校','毕业','退学','休学','保留学籍(参军入伍)') DEFAULT '在校' NULL COMMENT '学籍状态'
) COMMENT '学生表';

-- 2. 指导老师表
CREATE TABLE teachers (
    tno VARCHAR(8) NOT NULL COMMENT '教师工号' PRIMARY KEY,
    tname VARCHAR(50) NOT NULL COMMENT '姓名',
    title ENUM('教授','副教授','讲师','助教','工程师','高级工程师','助理研究员') NULL COMMENT '职称',
    major VARCHAR(50) NULL COMMENT '教师所属专业',
    status ENUM('在职','外聘','离职') DEFAULT '在职' NOT NULL COMMENT '教师状态'
) COMMENT '指导老师表';

-- 3. 省大创（含国家级）项目表
CREATE TABLE prov_projects (
    pno VARCHAR(15) NOT NULL COMMENT '项目编号' PRIMARY KEY,
    pname VARCHAR(100) NOT NULL COMMENT '项目名称',
    level ENUM('国家级','省级') NULL COMMENT '项目级别',
    type ENUM('创新训练','创业训练','创业实践') NOT NULL COMMENT '项目类型',
    year YEAR NULL COMMENT '年度',
    tno_1 VARCHAR(20) NOT NULL COMMENT '第1指导老师工号',
    tno_2 VARCHAR(20) NULL COMMENT '第2指导老师工号',
    description VARCHAR(500) NULL COMMENT '项目简介',
    funds INT DEFAULT 0 NULL COMMENT '经费(元)',
    status ENUM('在研','已结项','终止','在研(延期)') DEFAULT '在研' NOT NULL COMMENT '项目状态',
    start_date DATE NULL COMMENT '立项时间',
    end_date DATE NULL COMMENT '结项时间',
    CONSTRAINT pro_projects_ibfk_1 FOREIGN KEY (tno_1) REFERENCES teachers(tno) ON UPDATE CASCADE,
    CONSTRAINT pro_projects_ibfk_2 FOREIGN KEY (tno_2) REFERENCES teachers(tno) ON UPDATE CASCADE
) COMMENT '省大创含国家级项目表';

-- 4. 校大创项目表
CREATE TABLE uni_projects (
    pno VARCHAR(15) NOT NULL COMMENT '项目编号' PRIMARY KEY,
    pname VARCHAR(100) NOT NULL COMMENT '项目名称',
    level ENUM('资助','不资助') NULL COMMENT '项目级别',
    type ENUM('创新训练','创业训练','创业实践') NOT NULL COMMENT '项目类型',
    year YEAR NULL COMMENT '年度',
    tno_1 VARCHAR(20) NOT NULL COMMENT '第1指导老师工号',
    tno_2 VARCHAR(20) NULL COMMENT '第2指导老师工号',
    description VARCHAR(500) NULL COMMENT '项目简介',
    funds INT DEFAULT 0 NULL COMMENT '经费(元)',
    status ENUM('在研','已结项','中止','在研(延期)') DEFAULT '在研' NOT NULL COMMENT '项目状态',
    final_grade ENUM('优秀','合格','不合格') NULL COMMENT '结项等级',
    start_date DATE NULL COMMENT '立项时间',
    end_date DATE NULL COMMENT '结项时间',
    CONSTRAINT uni_projects_ibfk_1 FOREIGN KEY (tno_1) REFERENCES teachers(tno),
    CONSTRAINT uni_projects_ibfk_2 FOREIGN KEY (tno_2) REFERENCES teachers(tno)
) COMMENT '校大创项目表';

-- 5. 项目成员表
CREATE TABLE project_members (
    pno VARCHAR(15) NOT NULL COMMENT '项目编号',
    plevel VARCHAR(4) NULL COMMENT '项目级别: prov 或 uni',
    sno VARCHAR(10) NOT NULL COMMENT '学号',
    role ENUM('负责人','组员') NOT NULL COMMENT '成员角色',
    join_date DATE NULL COMMENT '加入时间',
    leave_date DATE NULL COMMENT '离开日期',
    status ENUM('有效','失效') DEFAULT '有效' NOT NULL COMMENT '成员状态',
    PRIMARY KEY (pno, sno),
    INDEX idx_pno (pno),
    CONSTRAINT project_members_ibfk_1 FOREIGN KEY (sno) REFERENCES students(sno)
) COMMENT '项目成员表';

-- 6. 管理员表
CREATE TABLE IF NOT EXISTS admins (
    admin_id VARCHAR(10) NOT NULL COMMENT '管理员ID' PRIMARY KEY,
    admin_name VARCHAR(50) NOT NULL COMMENT '管理员姓名',
    password VARCHAR(50) NOT NULL COMMENT '密码',
    role ENUM('super_admin','admin') NOT NULL COMMENT '角色'
) COMMENT '管理员表';

-- 7. 公告表
CREATE TABLE IF NOT EXISTS announcements (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '公告ID',
    title VARCHAR(100) NOT NULL COMMENT '标题',
    content VARCHAR(1000) NOT NULL COMMENT '内容',
    type ENUM('announcement','notice','urgent') NOT NULL COMMENT '类型',
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
    status VARCHAR(20) DEFAULT 'published' COMMENT '发布状态',
    admin_id VARCHAR(10) NOT NULL COMMENT '发布人',
    FOREIGN KEY (admin_id) REFERENCES admins(admin_id)
) COMMENT '公告表';

-- 8. 申报书表
CREATE TABLE IF NOT EXISTS project_reports (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '申报书ID',
    pno VARCHAR(15) NOT NULL COMMENT '项目编号',
    plevel VARCHAR(4) NOT NULL COMMENT '项目级别: prov/uni',
    sno VARCHAR(10) NOT NULL COMMENT '申报人学号',
    report_path VARCHAR(200) NOT NULL COMMENT '申报书路径',
    report_name VARCHAR(100) NOT NULL COMMENT '申报书名称',
    upload_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
    status ENUM('pending','approved','rejected') DEFAULT 'pending' COMMENT '审核状态',
    teacher_opinion VARCHAR(500) NULL COMMENT '教师意见',
    FOREIGN KEY (sno) REFERENCES students(sno)
) COMMENT '项目申报书表';

-- 9. 审核记录表
CREATE TABLE IF NOT EXISTS audit_records (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '审核记录ID',
    report_id INT NOT NULL COMMENT '申报书ID',
    admin_id VARCHAR(10) NOT NULL COMMENT '审核人ID',
    audit_opinion VARCHAR(500) NULL COMMENT '审核意见',
    audit_status ENUM('pending','approved','rejected') NOT NULL COMMENT '审核状态',
    audit_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
    FOREIGN KEY (report_id) REFERENCES project_reports(id),
    FOREIGN KEY (admin_id) REFERENCES admins(admin_id)
) COMMENT '审核记录表';

高级数据库对象

视图：按专业统计在研项目数

CREATE VIEW v_major_project_count AS
SELECT 
    s.major AS 专业名称,
    COUNT(DISTINCT pm.pno) AS 在研项目数
FROM students s
JOIN project_members pm ON s.sno = pm.sno
LEFT JOIN prov_projects pp ON pm.pno = pp.pno AND pp.status = '在研'
LEFT JOIN uni_projects up ON pm.pno = up.pno AND up.status = '在研'
WHERE pm.status = '有效'
  AND (pp.pno IS NOT NULL OR up.pno IS NOT NULL)
GROUP BY s.major;

触发器：项目成员状态变更自动记录日志

CREATE TABLE IF NOT EXISTS project_member_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    pno VARCHAR(15) NOT NULL COMMENT '项目编号',
    sno VARCHAR(10) NOT NULL COMMENT '学号',
    old_status ENUM('有效','失效') NOT NULL COMMENT '变更前状态',
    new_status ENUM('有效','失效') NOT NULL COMMENT '变更后状态',
    change_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '变更时间'
) COMMENT '项目成员状态变更日志';

DELIMITER //
CREATE TRIGGER trg_project_member_status
AFTER UPDATE ON project_members
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO project_member_log (pno, sno, old_status, new_status)
        VALUES (OLD.pno, OLD.sno, OLD.status, NEW.status);
    END IF;
END //
DELIMITER ;

---

核心功能实现

1. 学生登录模块

功能描述： 学生在登录页面选择"学生"角色，输入学号后点击登录。前端通过 POST 请求将学号发送到 /api/login 接口，后端在 students 表中查询记录。若学号存在则将学生信息存入 localStorage 并跳转到学生主页；否则返回错误提示。

界面截图：

涉及文件： index.html（登录页）、app.py（后端）

核心代码（后端）：

@app.route('/api/login', methods=['POST'])
def login():
    data = request.json
    sno = data.get('sno')
    get_db_connection()
    cursor = db.cursor()
    cursor.execute('SELECT * FROM students WHERE sno = %s', (sno,))
    student = cursor.fetchone()
    if student:
        columns = [desc[0] for desc in cursor.description]
        student_dict = dict(zip(columns, student))
        return jsonify({'success': True, 'student': student_dict})
    return jsonify({'success': False, 'message': '学号不存在'})

关键SQL语句：

-- 根据学号查询学生信息
SELECT * FROM students WHERE sno = %s;

数据操作说明： 查询操作（单表查询），参数化查询防 SQL 注入，结果映射为字典返回 JSON。

---

2. 项目查询模块

功能描述： 学生可在"项目查询"标签页中，按项目级别（省级/国家级、校级）、类型（创新训练/创业训练/创业实践）、年份、状态进行多条件组合筛选。前端调用 GET /api/projects 接口，后端根据筛选条件动态拼接 SQL，从 prov_projects 和 uni_projects 两张表中分别查询并合并返回，前端以表格形式渲染展示。

界面截图：

涉及文件： dashboard.html（学生端）、app.py（后端路由 /api/projects）

核心代码（后端）：

@app.route('/api/projects', methods=['GET'])
def get_projects():
    level = request.args.get('level', '')
    type_filter = request.args.get('type', '')
    year = request.args.get('year', '')
    status = request.args.get('status', '')

    get_db_connection()
    cursor = db.cursor()

    # 查询省级/国家级项目
    if level == 'prov' or level == '':
        sql_prov = 'SELECT pno, pname, level, type, year, tno_1, description, funds, status, start_date, end_date, \'prov\' as plevel FROM prov_projects WHERE 1=1'
        params = []
        if type_filter:
            sql_prov += ' AND type = %s'
            params.append(type_filter)
        if year:
            sql_prov += ' AND year = %s'
            params.append(year)
        if status:
            sql_prov += ' AND status = %s'
            params.append(status)
        cursor.execute(sql_prov, params)
        prov_result = [dict(zip(columns, p)) for p in prov_projects]

    # 查询校级项目（逻辑同上）
    # ...

    all_projects = prov_result + uni_result
    return jsonify({'success': True, 'projects': all_projects})

关键SQL语句：

-- 按条件查询省级项目（动态拼接条件）
SELECT pno, pname, level, type, year, tno_1, description, funds, status, 
       start_date, end_date, 'prov' as plevel
FROM prov_projects
WHERE type = %s AND year = %s AND status = %s;

-- 按条件查询校级项目（含结项等级字段）
SELECT pno, pname, level, type, year, tno_1, description, funds, status, 
       start_date, end_date, final_grade, 'uni' as plevel
FROM uni_projects
WHERE type = %s AND year = %s AND status = %s;

数据操作说明： 条件查询（多表分查合并），动态 SQL 拼接，参数化查询，两个结果集合并返回。

---

3. 我的项目与项目详情查看

功能描述： 学生登录后主页自动加载个人参与的项目列表，通过 GET /api/student/projects/{sno} 查询。点击项目可弹出详情弹窗展示项目信息、指导教师列表和项目成员列表。

界面截图：

涉及文件： dashboard.html、app.py（路由 /api/student/projects/<sno>、/api/project/detail）

核心代码（后端）：

@app.route('/api/student/projects/<sno>', methods=['GET'])
def get_student_projects(sno):
    get_db_connection()
    cursor = db.cursor()
    cursor.execute('''
        SELECT pm.pno, pm.plevel, pm.role, pm.join_date, pm.leave_date, pm.status,
               pp.pname AS project_name, pp.level AS project_level, 
               pp.type, pp.year, pp.status AS project_status,
               up.pname AS uni_project_name, up.level AS uni_level, 
               up.type AS uni_type, up.year AS uni_year, up.status AS uni_status, up.final_grade
        FROM project_members pm
        LEFT JOIN prov_projects pp ON pm.pno = pp.pno AND pm.plevel = 'prov'
        LEFT JOIN uni_projects up ON pm.pno = up.pno AND pm.plevel = 'uni'
        WHERE pm.sno = %s AND pm.status = '有效'
    ''', (sno,))
    projects = cursor.fetchall()
    # 合并 prov 和 uni 的项目字段
    result = []
    for project in projects:
        project_dict = dict(zip(columns, project))
        project_dict['project_name'] = project_dict['project_name'] or project_dict['uni_project_name']
        project_dict['project_level'] = project_dict['project_level'] or project_dict['uni_level']
        # ...
        result.append(project_dict)
    return jsonify({'success': True, 'projects': result})

项目详情查询（含成员和老师）：

if plevel == 'prov':
    cursor.execute('SELECT * FROM prov_projects WHERE pno = %s', (pno,))
    project = cursor.fetchone()
    # 查询项目成员
    cursor.execute('''SELECT s.sno, s.sname, pm.role, pm.join_date 
                      FROM project_members pm JOIN students s ON pm.sno = s.sno 
                      WHERE pm.pno = %s AND pm.plevel = %s''', (pno, plevel))
    # 查询指导教师
    for tno_key in ['tno_1', 'tno_2']:
        tno = project_dict.get(tno_key)
        if tno:
            cursor.execute('SELECT tno, tname, title, major FROM teachers WHERE tno = %s', (tno,))

关键SQL语句：

-- 查询学生参与的项目（左连接两项目表）
SELECT pm.*, pp.pname AS project_name, up.pname AS uni_project_name
FROM project_members pm
LEFT JOIN prov_projects pp ON pm.pno = pp.pno AND pm.plevel = 'prov'
LEFT JOIN uni_projects up ON pm.pno = up.pno AND pm.plevel = 'uni'
WHERE pm.sno = %s AND pm.status = '有效';

-- 查询项目成员
SELECT s.sno, s.sname, pm.role, pm.join_date
FROM project_members pm
JOIN students s ON pm.sno = s.sno
WHERE pm.pno = %s AND pm.plevel = %s;

---

4. 申报书审核模块

功能描述： 管理员在"报告审核"标签页查看所有学生提交的申报书，可按审核状态（全部/待审核/已通过/已拒绝）筛选。对待审核的申报书可点击"通过/拒绝"按钮进行审核，审核时需输入审核意见（拒绝必填）。操作同时更新 project_reports 表状态并写入 audit_records 表实现审核留痕。已审核通过的申报书支持下载。

界面截图：

涉及文件： admin-dashboard.html、app.py（路由 /api/admin/reports、/api/admin/audit-report、/api/admin/download-report/<id>）

核心代码（后端）：

@app.route('/api/admin/audit-report', methods=['POST'])
def audit_report():
    data = request.json
    report_id = data.get('report_id')
    admin_id = data.get('admin_id')
    audit_status = data.get('audit_status')
    audit_opinion = data.get('audit_opinion')

    get_db_connection()
    cursor = db.cursor()

    new_status = 'approved' if audit_status == '通过' else 'rejected'

    # 更新申报书状态
    cursor.execute('UPDATE project_reports SET status = %s WHERE id = %s', (new_status, report_id))

    # 写入审核记录
    cursor.execute('INSERT INTO audit_records (report_id, admin_id, audit_opinion, audit_status) VALUES (%s, %s, %s, %s)', 
                   (report_id, admin_id, audit_opinion, audit_status))

    db.commit()
    return jsonify({'success': True, 'message': '审核完成'})

申报书列表查询（含项目名称关联）：

cursor.execute('''
    SELECT pr.*, s.sname, s.major, s.class,
           CASE 
               WHEN pr.plevel = 'prov' THEN pp.pname
               ELSE up.pname
           END as project_name
    FROM project_reports pr
    JOIN students s ON pr.sno = s.sno
    LEFT JOIN prov_projects pp ON pr.pno = pp.pno AND pr.plevel = 'prov'
    LEFT JOIN uni_projects up ON pr.pno = up.pno AND pr.plevel = 'uni'
    WHERE pr.status = %s
    ORDER BY pr.upload_date DESC
''', (status_filter,))

关键SQL语句：

-- 更新申报书审核状态
UPDATE project_reports SET status = 'approved' WHERE id = %s;

-- 插入审核记录（留痕）
INSERT INTO audit_records (report_id, admin_id, audit_opinion, audit_status)
VALUES (%s, %s, %s, 'approved');

-- 利用 CASE 表达式关联不同项目表获取项目名称
SELECT pr.*, s.sname,
       CASE 
           WHEN pr.plevel = 'prov' THEN pp.pname
           ELSE up.pname
       END as project_name
FROM project_reports pr
JOIN students s ON pr.sno = s.sno
LEFT JOIN prov_projects pp ON pr.pno = pp.pno AND pr.plevel = 'prov'
LEFT JOIN uni_projects up ON pr.pno = up.pno AND pr.plevel = 'uni'
ORDER BY pr.upload_date DESC;

数据操作说明： 事务操作（UPDATE + INSERT 组合），通过 CASE 表达式实现多表条件关联，审核操作同步两张表保证数据一致性。

---

5. 管理员数据统计模块

功能描述： 管理员登录后默认进入"统计概览"页面，系统自动调用 /api/admin/statistics 接口，后端通过多次 COUNT 和 SUM 查询从 student、teachers、prov_projects、uni_projects、project_reports 等表中汇总数据，以卡片形式展示统计概览仪表盘。

界面截图：

涉及文件： admin-dashboard.html、app.py（路由 /api/admin/statistics）

核心代码（后端）：

@app.route('/api/admin/statistics', methods=['GET'])
def get_statistics():
    get_db_connection()
    cursor = db.cursor()
    stats = {}

    cursor.execute('SELECT COUNT(*) FROM students')
    stats['total_students'] = cursor.fetchone()[0]

    cursor.execute('SELECT COUNT(*) FROM teachers')
    stats['total_teachers'] = cursor.fetchone()[0]

    cursor.execute('SELECT COUNT(*) FROM prov_projects')
    stats['total_prov_projects'] = cursor.fetchone()[0]

    cursor.execute('SELECT COUNT(*) FROM uni_projects')
    stats['total_uni_projects'] = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM prov_projects WHERE status = '在研'")
    stats['prov_in_progress'] = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM uni_projects WHERE status = '在研'")
    stats['uni_in_progress'] = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM project_reports WHERE status = 'pending'")
    stats['pending_reports'] = cursor.fetchone()[0]

    cursor.execute('SELECT SUM(funds) FROM prov_projects')
    stats['total_prov_funds'] = cursor.fetchone()[0] or 0

    cursor.execute('SELECT SUM(funds) FROM uni_projects')
    stats['total_uni_funds'] = cursor.fetchone()[0] or 0

    return jsonify({'success': True, 'statistics': stats})

关键SQL语句：

-- 9 条统计查询示例
SELECT COUNT(*) FROM students;                    -- 学生总数
SELECT COUNT(*) FROM teachers;                     -- 教师总数
SELECT COUNT(*) FROM prov_projects;                -- 省级项目数
SELECT COUNT(*) FROM uni_projects;                 -- 校级项目数
SELECT COUNT(*) FROM prov_projects WHERE status = '在研';  -- 省级在研数
SELECT COUNT(*) FROM uni_projects WHERE status = '在研';   -- 校级在研数
SELECT COUNT(*) FROM project_reports WHERE status = 'pending';  -- 待审核数
SELECT SUM(funds) FROM prov_projects;              -- 省级经费总额
SELECT SUM(funds) FROM uni_projects;               -- 校级经费总额

数据操作说明： 多维聚合查询，9 次独立 COUNT/SUM 统计，返回统一 JSON 结构供前端仪表盘展示。

---

6. 公告管理模块

功能描述： 管理员可以发布公告（支持通知/公告/紧急三种类型），已发布公告支持撤回操作（软删除，更新 status 为"撤回"）。学生端可查看最新发布的 10 条公告。

界面截图：

涉及文件： admin-dashboard.html、dashboard.html、app.py

核心代码（后端）：

# 管理员发布公告
@app.route('/api/admin/announcements', methods=['POST'])
def add_announcement():
    data = request.json
    admin_id = data.get('admin_id')
    title = data.get('title')
    content = data.get('content')
    announcement_type = data.get('type', '通知')
    cursor.execute('INSERT INTO announcements (title, content, type, admin_id) VALUES (%s, %s, %s, %s)',
                   (title, content, announcement_type, admin_id))
    db.commit()
    return jsonify({'success': True, 'message': '公告发布成功'})

# 撤回公告（软删除）
@app.route('/api/admin/announcements/<int:announcement_id>', methods=['DELETE'])
def delete_announcement(announcement_id):
    cursor.execute("UPDATE announcements SET status = '撤回' WHERE id = %s", (announcement_id,))
    db.commit()
    return jsonify({'success': True, 'message': '公告已撤回'})

# 学生端查看最新公告（限制10条）
@app.route('/api/student/announcements', methods=['GET'])
def get_student_announcements():
    cursor.execute('''
        SELECT a.*, admin.admin_name 
        FROM announcements a 
        LEFT JOIN admins admin ON a.admin_id = admin.admin_id 
        WHERE a.status = 'published' 
        ORDER BY a.publish_date DESC 
        LIMIT 10
    ''')

关键SQL语句：

-- 发布公告
INSERT INTO announcements (title, content, type, admin_id) VALUES (%s, %s, %s, %s);

-- 撤回公告（软删除：更新状态而非物理删除）
UPDATE announcements SET status = '撤回' WHERE id = %s;

-- 查询最新公告（联表查询 + 限制条数）
SELECT a.*, admin.admin_name
FROM announcements a
LEFT JOIN admins admin ON a.admin_id = admin.admin_id
WHERE a.status = 'published'
ORDER BY a.publish_date DESC
LIMIT 10;

数据操作说明： 插入操作 + 软删除（通过 UPDATE 状态实现而非 DELETE），左连接查询管理员姓名，LIMIT 限制返回条数。

---

7. 学生信息管理模块（管理员端 CRUD）

功能描述： 管理员可对学生信息进行完整的增删改查操作。删除时级联清理该学生的关联数据（项目成员关系、申报书记录），保证数据完整性。

界面截图：

涉及文件： admin-dashboard.html、app.py（路由 /api/admin/students）

核心代码（后端）：

# 查询所有学生
cursor.execute('SELECT * FROM students')

# 添加学生
cursor.execute('INSERT INTO students (sno, sname, school, grade, major, class, status) VALUES (%s, %s, %s, %s, %s, %s, %s)', ...)

# 更新学生（动态字段拼接）
cursor.execute(f'UPDATE students SET {", ".join(fields)} WHERE sno = %s', values)

# 删除学生（级联清理）
cursor.execute('DELETE FROM project_members WHERE sno = %s', (sno,))
cursor.execute('DELETE FROM project_reports WHERE sno = %s', (sno,))
cursor.execute('DELETE FROM students WHERE sno = %s', (sno,))

关键SQL语句：

-- 动态更新（仅更新前端传入的字段）
UPDATE students SET sname = %s, major = %s, status = %s WHERE sno = %s;

-- 级联删除（先清理关联表再删主表）
DELETE FROM project_members WHERE sno = %s;
DELETE FROM project_reports WHERE sno = %s;
DELETE FROM students WHERE sno = %s;

---

8. 登录/退出双端路由

功能描述： Flask 配置了静态文件路由，学生端、管理员端的前端页面通过 send_file 提供服务。管理员登录认证同样采用 ID 查询方式，与学生的登录逻辑对称。

界面截图：

# 静态页面路由
@app.route('/')
@app.route('/index.html')
def index():
    return send_file('index.html')

@app.route('/dashboard.html')
def dashboard():
    return send_file('dashboard.html')

# 管理员登录
@app.route('/api/admin/login', methods=['POST'])
def admin_login():
    data = request.json
    admin_id = data.get('admin_id')
    cursor.execute('SELECT * FROM admins WHERE admin_id = %s', (admin_id,))
    admin = cursor.fetchone()
    if admin:
        return jsonify({'success': True, 'admin': dict(zip(columns, admin))})
    return jsonify({'success': False, 'message': '管理员ID不存在'})

---

系统优化

索引设计

当前数据库已经存在的索引及建议添加的索引：

表名	已存在索引	说明	建议新增索引
project_members	idx_pno (pno)	加速按项目编号查询成员	idx_sno (sno) — 加速按学号查询参与项目
prov_projects	主键索引	按编号查询	idx_prov_year_status (year, status) — 加速筛选查询
uni_projects	主键索引	按编号查询	idx_uni_year_status (year, status) — 加速筛选查询
project_reports	外键索引	—	idx_report_status (status) — 加速按审核状态筛选
audit_records	外键索引	—	idx_audit_report_id (report_id) — 加速按申报书ID查询审核记录

推荐执行的索引 SQL：

-- 项目成员表：加学号索引加速反向查询
CREATE INDEX idx_pm_sno ON project_members(sno);

-- 项目表：加速条件筛选查询（覆盖 year + status 常用查询组合）
CREATE INDEX idx_prov_year_status ON prov_projects(year, status);
CREATE INDEX idx_uni_year_status ON uni_projects(year, status);

-- 申报书表：加速审核状态筛选
CREATE INDEX idx_report_status ON project_reports(status);

-- 审核记录：加速按申报书查询审核历史
CREATE INDEX idx_audit_report_id ON audit_records(report_id);

性能优化

1. 数据库连接池优化

当前代码使用全局单连接模式，高并发下会存在连接竞争。优化方案：

# 使用连接池替代单连接
from dbutils.pooled_db import PooledDB

pool = PooledDB(
    creator=pymysql,
    maxconnections=20,        # 最大连接数
    mincached=5,             # 最小空闲连接
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    database='data_demo'
)

def get_db_connection():
    return pool.connection()

2. 查询优化

• 合并多次 COUNT 为单次查询：统计概览中的 9 次独立 COUNT 可考虑使用子查询合并为一条 SQL
• 使用 LIMIT 限制结果集：公告查询已使用 LIMIT 10，其他列表查询应考虑分页
• 避免 SELECT *：当前多处使用 SELECT *，建议明确指定所需字段

-- 合并统计查询示例
SELECT 
    (SELECT COUNT(*) FROM students) AS total_students,
    (SELECT COUNT(*) FROM teachers) AS total_teachers,
    (SELECT COUNT(*) FROM prov_projects) AS total_prov,
    (SELECT COUNT(*) FROM uni_projects) AS total_uni,
    (SELECT SUM(funds) FROM prov_projects) AS prov_funds,
    (SELECT SUM(funds) FROM uni_projects) AS uni_funds;

3. 前端优化

• 引入 CSS 文件和 JavaScript 文件分离，减少 HTML 内联代码量
• 使用模板引擎（Jinja2）替代前端直接拼接 HTML 字符串
• 增加数据懒加载，减少首屏加载数据量

---

测试用例

功能测试

学生端功能测试

测试编号	测试模块	测试用例	预期结果	实际结果
ST-001	学生登录	输入已存在的学号 "2315304167"	登录成功，跳转学生主页	✅ 通过
ST-002	学生登录	输入不存在的学号 "0000000000"	提示"学号不存在"	✅ 通过
ST-003	项目查询	选择级别"省级/国家级"，点击查询	显示省级项目列表	✅ 通过
ST-004	项目查询	选择年份 2025 + 类型"创新训练"	筛选出符合条件的项目	✅ 通过
ST-005	项目查询	不选择任何筛选条件直接点击查询	显示全部项目	✅ 通过
ST-006	项目详情	点击项目列表中的一个项目	弹出详情弹窗，含成员和老师信息	✅ 通过
ST-007	公告查看	切换到"公告通知"标签页	显示最新公告列表	✅ 通过

管理员端功能测试

测试编号	测试模块	测试用例	预期结果	实际结果
AD-001	管理员登录	输入管理员 ID "admin001"	登录成功，跳转管理后台	✅ 通过
AD-002	统计概览	登录后默认进入统计概览	展示 9 项统计数据卡片	✅ 通过
AD-003	公告发布	填写标题、内容，选择"通知"类型发布	公告出现在已发布列表中	✅ 通过
AD-004	公告撤回	点击已发布公告的"撤回"按钮	公告从列表中消失	✅ 通过
AD-005	报告审核	对待审核申报书点击"通过"，输入审核意见	状态变为"已通过"	✅ 通过
AD-006	报告审核	对待审核申报书点击"拒绝"，不输入意见	提示"拒绝时必须填写审核意见"	✅ 通过
AD-007	学生管理	添加新学生（学号 + 姓名必填）	学生出现在列表中	✅ 通过
AD-008	学生管理	删除有项目关联的学生	级联删除关联数据，学生被移除	✅ 通过
AD-009	项目管理	添加省级项目，填写全部字段	项目出现在表中	✅ 通过
AD-010	项目管理	切换子标签"省级/国家级"和"校级"	按级别筛选显示	✅ 通过

性能测试

数据库查询性能测试（基于当前数据量估计）

查询操作	涉及表	数据量估计	预期响应时间
学生登录查询	students	1000 行	< 10ms
项目条件筛选	prov_projects + uni_projects	200 + 500 行	< 50ms
统计概览（9 次 COUNT）	6 张表	—	< 100ms
申报书列表（含 CASE 关联）	4 张表 JOIN	500 行	< 80ms
项目详情（含成员和老师）	3 次查询	—	< 30ms

索引优化前后对比（预计）

查询场景	优化前（全表扫描）	优化后（索引扫描）	提升倍数
按学年状态筛选项目	~0.5ms（数据量小）	~0.05ms	10×
按审核状态筛选申报书	~0.3ms	~0.03ms	10×
查询学生参与项目（按学号）	~0.4ms	~0.04ms	10×

注：当前示例数据量较小，性能差异不明显。随着数据量增长至万级，索引优化的效果将更加显著。

---

项目总结

技术收获

1. Flask 后端开发流程：掌握了 Flask 路由设计、请求参数解析、JSON 响应构建、CORS 跨域配置等核心技能。通过 RESTful API 设计完成了学生和管理员两套接口体系。

2. MySQL 数据库设计实践：从 E-R 图分析到关系表转换，深刻理解了实体关系映射、外键约束、枚举类型、视图和触发器的实际应用场景。特别在项目成员与项目的多对多关系设计中，通过关联表（project_members）实现了灵活的双向查询。

3. 前后端分离开发：采用 Flask 提供 API + 原生 HTML 前端调用的模式，理解了 JSON 数据交互、localStorage 状态管理和异步请求处理。

4. 数据完整性保障：通过外键约束保证引用完整性，使用触发器实现自动化日志记录，利用事务保证审核操作的原子性。

项目亮点

1. 双角色分权管理：系统区分学生和管理员两种角色，各自拥有独立的操作界面和功能权限，学生端侧重查看和申报，管理员端侧重审核和配置。

2. 两级项目模型：同时支持省级/国家级项目和校级项目两类大创项目，各自拥有独立的字段和状态枚举（如校级项目特有 final_grade 结项等级字段），并有各自完整的查询、统计和审核流程。

3. 完整审核留痕：申报书审核不仅更新状态，还通过独立的审核记录表（audit_records）记录每一次审核操作（审核人、审核意见、审核时间、审核结果），便于事后追溯和审计。

4. 数据库完整性保证：使用外键约束、枚举类型、视图和触发器，确保数据的一致性和完整性。触发器能够在项目成员状态变更时自动记录变更日志，便于历史追溯。

5. 动态 SQL 拼接：项目查询模块支持多条件组合筛选，后端根据前端传入的参数动态拼接 WHERE 条件，兼顾查询灵活性和安全性（参数化查询防 SQL 注入）。

6. 级联数据清理：删除学生时自动清理其项目成员关系和申报书记录，删除教师时自动将项目中关联的教师置空，避免产生孤立数据。

---

扩展功能

以下功能可作为后续迭代方向：

1. 密码认证与 JWT 安全机制

当前系统仅凭学号/工号即可登录，存在安全隐患。可增加密码登录和 JWT Token 认证：

# JWT 认证示例
import jwt
import hashlib

# 密码哈希存储
hashed_password = hashlib.sha256(password.encode()).hexdigest()

# JWT Token 生成
token = jwt.encode({'sno': sno, 'exp': datetime.utcnow() + timedelta(hours=2)}, 
                   'SECRET_KEY', algorithm='HS256')

2. 申报书在线编辑与提交

当前申报书通过文件上传方式提交，可扩展为在线表单编辑：

• 学生在线填写申报书表单（项目名称、成员信息、指导老师、研究内容等）
• 支持草稿保存和正式提交
• 申报书模板下载（Word/PDF 格式）

3. 项目中期检查与结项管理

• 中期检查报告提交与审核
• 结项材料上传（结项报告、成果证明等）
• 结项评审打分（专家组在线评分）
• 项目成果展示（论文、专利、软著等）

4. 数据可视化大屏

在现有统计概览基础上增加更丰富的数据可视化：

• 按学院/专业的项目分布饼图
• 按年份的项目数量趋势折线图
• 经费使用情况柱状图
• 各专业在研项目数排行榜

5. 消息通知系统

• 审核结果通知（审核通过/拒绝时自动提醒学生）
• 公告推送（新公告发布时向学生推送）
• 进度提醒（项目到期提醒、中期检查提醒）

6. 分页与导入导出

• 列表数据分页查询（后端 LIMIT + OFFSET）
• 数据导出为 Excel/CSV 格式
• 批量导入学生和项目数据

7. 前端框架重构

当前使用原生 JavaScript，代码结构较为松散。可考虑重构为：

• Vue 3 + Element Plus：组件化开发，数据双向绑定
• React + Ant Design：丰富的 UI 组件库
• 使用前端路由实现 SPA 单页应用

8. 系统监控与日志

• 操作日志记录（谁在什么时间做了什么操作）
• 系统访问统计（PV/UV）
• 异常监控与告警

---