all-project/backend/script/sql/generate_student_data.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
学生信息数据生成脚本
版本：V1.0.0
功能：生成3个新班级和150条学生信息数据
作者：Cursor AI 辅助
时间：2025-01-14
说明：包含完整的学生信息和班级关联数据
"""

import random
import datetime
from datetime import date, timedelta

# 中国常见姓氏
surnames = ['李', '王', '张', '刘', '陈', '杨', '赵', '黄', '周', '吴', '徐', '孙', '胡', '朱', '高', '林', '何', '郭', '马', '罗', '梁', '宋', '郑', '谢', '韩', '唐', '冯', '于', '董', '萧', '程', '曹', '袁', '邓', '许', '傅', '沈', '曾', '彭', '吕', '苏', '卢', '蒋', '蔡', '贾', '丁', '魏', '薛', '叶', '阎']

# 常见名字
male_names = ['明阳', '志强', '浩然', '伟峰', '俊杰', '建华', '志华', '文华', '国强', '建国', '志刚', '志远', '浩宇', '宇航', '晨阳', '子轩', '梓轩', '博涛', '智宸', '正豪', '昊然', '明轩', '绍齐', '伟宸', '梓豪', '子涵', '明哲', '俊驰', '雨泽', '烨磊', '晟睿', '文昊', '修杰', '黎昕', '远航', '旭尧', '鸿涛', '伟祺', '荣轩', '越泽', '瑾瑜', '皓轩', '哲瀚', '鑫磊', '修洁', '天佑', '英杰', '致远', '俊材', '高格']

female_names = ['丽华', '美丽', '秀英', '丽君', '小华', '淑华', '雅静', '美玉', '雪梅', '春花', '秀兰', '丽娟', '小丽', '晓雯', '雅婷', '佳音', '思涵', '梦瑶', '雨婷', '可馨', '欣妍', '佳琦', '雅楠', '语嫣', '桑榆', '欣怡', '美莲', '诗涵', '璟雯', '佳妮', '芸熙', '钰彤', '璟颖', '奕凤', '雨薇', '思颖', '笑薇', '娅楠', '明美', '惠茜', '漫妮', '香怡', '雅芙', '韵寒', '莉姿', '梦璐', '沛玲', '雨嘉', '静香']

# 高中名单
high_schools = ['杭州市第一中学', '杭州市第二中学', '杭州市第三中学', '杭州市第四中学', '杭州市第五中学', '杭州市第六中学', '杭州市第七中学', '杭州市第八中学', '杭州市第九中学', '杭州市第十中学', '杭州市第十一中学', '杭州市第十二中学', '杭州市第十三中学', '杭州市第十四中学', '杭州市第十五中学', '杭州学军中学', '杭州市长河高级中学', '杭州市源清中学', '杭州市绿城育华学校', '杭州市余杭高级中学']

# 银行列表
banks = ['中国工商银行', '中国建设银行', '中国农业银行', '中国银行', '招商银行', '交通银行', '中信银行', '浦发银行', '兴业银行', '光大银行', '民生银行', '华夏银行', '广发银行', '平安银行', '邮储银行']

# 血型
blood_types = ['A', 'B', 'AB', 'O']

# 地址前缀
address_prefixes = ['浙江省杭州市西湖区', '浙江省杭州市上城区', '浙江省杭州市江干区', '浙江省杭州市拱墅区', '浙江省杭州市下城区', '浙江省杭州市滨江区', '浙江省杭州市萧山区', '浙江省杭州市余杭区', '浙江省杭州市临安区', '浙江省杭州市富阳区']

# 道路名称
road_names = ['文一路', '文二路', '文三路', '建国路', '凤起路', '教工路', '莫干山路', '天目山路', '西湖大道', '中河路', '上塘路', '德胜路', '庆春路', '解放路', '延安路', '湖滨路', '学院路', '科技路', '创业路', '发展路']

def generate_student_data():
    """生成学生数据"""
    students = []

    # 三个班级配置
    classes = [
        {'id': 1100001, 'code': 'CS2024001', 'name': '计算机科学与技术2024级1班', 'major': '计算机科学与技术', 'college': '计算机科学与技术学院', 'male_count': 28, 'female_count': 22},
        {'id': 1100002, 'code': 'EE2024001', 'name': '电子信息工程2024级1班', 'major': '电子信息工程', 'college': '电子信息工程学院', 'male_count': 32, 'female_count': 18},
        {'id': 1100003, 'code': 'BM2024001', 'name': '工商管理2024级1班', 'major': '工商管理', 'college': '经济管理学院', 'male_count': 20, 'female_count': 30}
    ]

    student_id = 2100001

    for class_info in classes:
        # 生成男生
        for i in range(class_info['male_count']):
            student_no = f'202401{student_id - 2100000:03d}'
            surname = random.choice(surnames)
            name = surname + random.choice(male_names)

            # 生成身份证号
            area_code = '330' + str(random.randint(100, 199))
            birth_year = random.choice(['2005', '2006'])
            birth_month = f'{random.randint(1, 12):02d}'
            birth_day = f'{random.randint(1, 28):02d}'
            birth_date = f'{birth_year}-{birth_month}-{birth_day}'
            id_card = f'{area_code}{birth_year}{birth_month}{birth_day}{random.randint(1000, 9999)}'

            # 生成联系信息
            phone = f'138{random.randint(10000000, 99999999)}'
            email = f'{name.lower()}@student.edu.cn'
            qq = f'{random.randint(1000000000, 9999999999)}'
            wechat = f'{name.lower()}_{birth_year}'

            # 生成家庭信息
            father_name = random.choice(surnames) + random.choice(['建华', '国强', '志华', '文华', '建国'])
            father_phone = f'139{random.randint(10000000, 99999999)}'
            mother_name = random.choice(surnames) + random.choice(['丽华', '美丽', '秀英', '丽君', '小华'])
            mother_phone = f'137{random.randint(10000000, 99999999)}'

            # 生成其他信息
            height = round(random.uniform(165, 185), 1)
            weight = round(random.uniform(55, 80), 1)
            blood_type = random.choice(blood_types)
            bank_card = f'6222{random.randint(100000000000000, 999999999999999)}'
            bank = random.choice(banks)
            address = random.choice(address_prefixes) + random.choice(road_names) + f'{random.randint(1, 999)}号'
            high_school = random.choice(high_schools)
            score = round(random.uniform(580, 650), 1)

            student = {
                'id': student_id,
                'student_no': student_no,
                'name': name,
                'gender': 'M',
                'birth_date': birth_date,
                'id_card': id_card,
                'phone': phone,
                'email': email,
                'qq': qq,
                'wechat': wechat,
                'father_name': father_name,
                'father_phone': father_phone,
                'mother_name': mother_name,
                'mother_phone': mother_phone,
                'height': height,
                'weight': weight,
                'blood_type': blood_type,
                'bank_card': bank_card,
                'bank': bank,
                'address': address,
                'high_school': high_school,
                'score': score,
                'class_id': class_info['id'],
                'class_name': class_info['name'],
                'major': class_info['major'],
                'college': class_info['college']
            }
            students.append(student)
            student_id += 1

        # 生成女生
        for i in range(class_info['female_count']):
            student_no = f'202401{student_id - 2100000:03d}'
            surname = random.choice(surnames)
            name = surname + random.choice(female_names)

            # 生成身份证号
            area_code = '330' + str(random.randint(100, 199))
            birth_year = random.choice(['2005', '2006'])
            birth_month = f'{random.randint(1, 12):02d}'
            birth_day = f'{random.randint(1, 28):02d}'
            birth_date = f'{birth_year}-{birth_month}-{birth_day}'
            id_card = f'{area_code}{birth_year}{birth_month}{birth_day}{random.randint(1000, 9999)}'

            # 生成联系信息
            phone = f'138{random.randint(10000000, 99999999)}'
            email = f'{name.lower()}@student.edu.cn'
            qq = f'{random.randint(1000000000, 9999999999)}'
            wechat = f'{name.lower()}_{birth_year}'

            # 生成家庭信息
            father_name = random.choice(surnames) + random.choice(['建华', '国强', '志华', '文华', '建国'])
            father_phone = f'139{random.randint(10000000, 99999999)}'
            mother_name = random.choice(surnames) + random.choice(['丽华', '美丽', '秀英', '丽君', '小华'])
            mother_phone = f'137{random.randint(10000000, 99999999)}'

            # 生成其他信息
            height = round(random.uniform(155, 175), 1)
            weight = round(random.uniform(45, 65), 1)
            blood_type = random.choice(blood_types)
            bank_card = f'6222{random.randint(100000000000000, 999999999999999)}'
            bank = random.choice(banks)
            address = random.choice(address_prefixes) + random.choice(road_names) + f'{random.randint(1, 999)}号'
            high_school = random.choice(high_schools)
            score = round(random.uniform(580, 650), 1)

            student = {
                'id': student_id,
                'student_no': student_no,
                'name': name,
                'gender': 'F',
                'birth_date': birth_date,
                'id_card': id_card,
                'phone': phone,
                'email': email,
                'qq': qq,
                'wechat': wechat,
                'father_name': father_name,
                'father_phone': father_phone,
                'mother_name': mother_name,
                'mother_phone': mother_phone,
                'height': height,
                'weight': weight,
                'blood_type': blood_type,
                'bank_card': bank_card,
                'bank': bank,
                'address': address,
                'high_school': high_school,
                'score': score,
                'class_id': class_info['id'],
                'class_name': class_info['name'],
                'major': class_info['major'],
                'college': class_info['college']
            }
            students.append(student)
            student_id += 1

    return students

def generate_insert_sql(students):
    """生成学生信息插入SQL"""
    sql_parts = []

    for i, student in enumerate(students):
        emergency_relation = random.choice(['父亲', '母亲', '爷爷', '奶奶', '舅舅', '叔叔'])
        emergency_name = student['father_name'] if emergency_relation == '父亲' else student['mother_name'] if emergency_relation == '母亲' else random.choice(surnames) + random.choice(['建华', '丽华'])
        emergency_phone = student['father_phone'] if emergency_relation == '父亲' else student['mother_phone'] if emergency_relation == '母亲' else f'139{random.randint(10000000, 99999999)}'

        political_status = random.choice(['01', '02', '03', '13'])  # 党员、预备党员、团员、群众

        sql = f"""({student['id']}, '000000', '{student['student_no']}', '{student['name']}', '{student['gender']}', '{student['birth_date']}', '{student['id_card']}', '汉族', '{political_status}',
 '2024-09-01', 4, '{student['major']}', {student['class_id']}, '{student['class_name']}', '1', '全日制',
 '{student['phone']}', '{student['email']}', '{student['qq']}', '{student['wechat']}', '{student['address']}', '{emergency_name}', '{emergency_phone}', '{emergency_relation}',
 '{student['father_name']}', '{student['father_phone']}', '{student['mother_name']}', '{student['mother_phone']}', {student['height']}, {student['weight']}, '{student['blood_type']}',
 '{student['bank_card']}', '{student['bank']}', '浙江省杭州市', '浙江省杭州市', '城镇', '{student['high_school']}', {student['score']},
 '1', 'manual', 1, NOW(), 1, NOW())"""

        sql_parts.append(sql)

    return ',\n'.join(sql_parts)

def generate_class_student_sql(students):
    """生成班级学生关联SQL"""
    sql_parts = []
    relation_id = 3100001

    for i, student in enumerate(students):
        position = 'member'
        position_level = 9

        # 每个班级设置班长、副班长、学习委员等
        if i % 50 == 0:  # 每班第一个学生为班长
            position = 'monitor'
            position_level = 1
        elif i % 50 == 1:  # 每班第二个学生为副班长
            position = 'deputy'
            position_level = 2
        elif i % 50 == 2:  # 每班第三个学生为学习委员
            position = 'secretary'
            position_level = 3
        elif i % 50 == 3:  # 每班第四个学生为组织委员
            position = 'organizer'
            position_level = 3
        elif i % 50 == 4:  # 每班第五个学生为宣传委员
            position = 'publicity'
            position_level = 3

        sql = f"""({relation_id}, '000000', {student['class_id']}, {student['id']}, '{student['student_no']}', '{student['name']}',
 '{position}', {position_level}, '1', '2024-09-01', {i % 50 + 1}, 1, NOW(), 1, NOW())"""

        sql_parts.append(sql)
        relation_id += 1

    return ',\n'.join(sql_parts)

def generate_complete_sql():
    """生成完整的SQL文件"""
    students = generate_student_data()

    sql_content = """-- ==========================================
-- 完整学生信息数据生成脚本
-- 版本：V1.0.0
-- 功能：生成3个新班级和150条学生信息数据
-- 作者：Cursor AI 辅助
-- 时间：2025-01-14
-- 说明：包含完整的学生信息和班级关联数据
-- ==========================================

USE hzdyjs_xg;

-- 设置字符集
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ==============================================
-- 1. 创建3个新班级
-- ==============================================

-- 计算机科学与技术2024级1班
INSERT INTO `xg_class_info` (
  `id`, `tenant_id`, `class_code`, `class_name`, `class_type`, `grade`, `enrollment_year`, `graduation_year`,
  `college_id`, `college_name`, `major`, `major_code`, `training_type`, `degree_type`,
  `counselor_id`, `counselor_name`, `head_teacher_id`, `head_teacher_name`,
  `planned_capacity`, `current_count`, `male_count`, `female_count`, `active_count`,
  `contact_phone`, `qq_group`, `email`, `class_status`, `is_graduation`,
  `create_by`, `create_time`, `update_by`, `update_time`
) VALUES (
  1100001, '000000', 'CS2024001', '计算机科学与技术2024级1班', 'regular', '2024', 2024, 2028,
  110001, '计算机科学与技术学院', '计算机科学与技术', 'CS001', '全日制', '本科',
  10001, '李明华', 10002, '王建国',
  50, 50, 28, 22, 50,
  '0571-88000001', '888888001', 'cs2024001@hzdyjs.edu.cn', '1', 0,
  1, NOW(), 1, NOW()
) ON DUPLICATE KEY UPDATE current_count = VALUES(current_count), male_count = VALUES(male_count), female_count = VALUES(female_count);

-- 电子信息工程2024级1班
INSERT INTO `xg_class_info` (
  `id`, `tenant_id`, `class_code`, `class_name`, `class_type`, `grade`, `enrollment_year`, `graduation_year`,
  `college_id`, `college_name`, `major`, `major_code`, `training_type`, `degree_type`,
  `counselor_id`, `counselor_name`, `head_teacher_id`, `head_teacher_name`,
  `planned_capacity`, `current_count`, `male_count`, `female_count`, `active_count`,
  `contact_phone`, `qq_group`, `email`, `class_status`, `is_graduation`,
  `create_by`, `create_time`, `update_by`, `update_time`
) VALUES (
  1100002, '000000', 'EE2024001', '电子信息工程2024级1班', 'regular', '2024', 2024, 2028,
  110002, '电子信息工程学院', '电子信息工程', 'EE001', '全日制', '本科',
  10003, '张丽萍', 10004, '刘德华',
  50, 50, 32, 18, 50,
  '0571-88000002', '888888002', 'ee2024001@hzdyjs.edu.cn', '1', 0,
  1, NOW(), 1, NOW()
) ON DUPLICATE KEY UPDATE current_count = VALUES(current_count), male_count = VALUES(male_count), female_count = VALUES(female_count);

-- 工商管理2024级1班
INSERT INTO `xg_class_info` (
  `id`, `tenant_id`, `class_code`, `class_name`, `class_type`, `grade`, `enrollment_year`, `graduation_year`,
  `college_id`, `college_name`, `major`, `major_code`, `training_type`, `degree_type`,
  `counselor_id`, `counselor_name`, `head_teacher_id`, `head_teacher_name`,
  `planned_capacity`, `current_count`, `male_count`, `female_count`, `active_count`,
  `contact_phone`, `qq_group`, `email`, `class_status`, `is_graduation`,
  `create_by`, `create_time`, `update_by`, `update_time`
) VALUES (
  1100003, '000000', 'BM2024001', '工商管理2024级1班', 'regular', '2024', 2024, 2028,
  110003, '经济管理学院', '工商管理', 'BM001', '全日制', '本科',
  10005, '陈建华', 10006, '赵文强',
  50, 50, 20, 30, 50,
  '0571-88000003', '888888003', 'bm2024001@hzdyjs.edu.cn', '1', 0,
  1, NOW(), 1, NOW()
) ON DUPLICATE KEY UPDATE current_count = VALUES(current_count), male_count = VALUES(male_count), female_count = VALUES(female_count);

-- ==============================================
-- 2. 生成150条学生信息数据
-- ==============================================

INSERT INTO `xg_student_info` (
  `id`, `tenant_id`, `student_id`, `name`, `gender`, `birth_date`, `id_card`, `nation`, `political_status`,
  `admission_date`, `education_length`, `major`, `class_id`, `class_name`, `student_status`, `training_type`,
  `phone`, `email`, `qq_number`, `wechat_number`, `home_address`, `emergency_contact`, `emergency_phone`, `emergency_relation`,
  `father_name`, `father_phone`, `mother_name`, `mother_phone`, `height`, `weight`, `blood_type`,
  `bank_card_number`, `bank_name`, `birthplace`, `native_place`, `hukou_type`, `high_school`, `admission_score`,
  `info_status`, `data_source`, `create_by`, `create_time`, `update_by`, `update_time`
) VALUES
"""

    # 添加学生数据
    sql_content += generate_insert_sql(students)

    sql_content += """
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  gender = VALUES(gender),
  class_name = VALUES(class_name),
  student_status = VALUES(student_status),
  update_time = NOW();

-- ==============================================
-- 3. 创建班级学生关联关系
-- ==============================================

INSERT INTO `xg_class_student` (
  `id`, `tenant_id`, `class_id`, `student_id`, `student_number`, `student_name`,
  `class_position`, `position_level`, `relation_status`, `join_time`, `student_order`,
  `create_by`, `create_time`, `update_by`, `update_time`
) VALUES
"""

    # 添加班级学生关联数据
    sql_content += generate_class_student_sql(students)

    sql_content += """
ON DUPLICATE KEY UPDATE
  student_name = VALUES(student_name),
  class_position = VALUES(class_position),
  update_time = NOW();

-- ==============================================
-- 4. 验证数据创建结果
-- ==============================================

-- 验证班级信息
SELECT '=== 班级信息验证 ===' as info;
SELECT id, class_code, class_name, college_name, major, current_count, male_count, female_count
FROM xg_class_info
WHERE id IN (1100001, 1100002, 1100003);

-- 验证学生信息统计
SELECT '=== 学生信息统计 ===' as info;
SELECT
  class_id,
  class_name,
  COUNT(*) as total_count,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) as male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) as female_count
FROM xg_student_info
WHERE class_id IN (1100001, 1100002, 1100003)
GROUP BY class_id, class_name;

-- 验证班级学生关联
SELECT '=== 班级学生关联验证 ===' as info;
SELECT
  cs.class_id,
  ci.class_name,
  COUNT(*) as student_count,
  SUM(CASE WHEN cs.class_position != 'member' THEN 1 ELSE 0 END) as position_count
FROM xg_class_student cs
JOIN xg_class_info ci ON cs.class_id = ci.id
WHERE cs.class_id IN (1100001, 1100002, 1100003)
GROUP BY cs.class_id, ci.class_name;

-- 验证各班级学生分布
SELECT '=== 各班级学生分布详情 ===' as info;
SELECT
  ci.class_name,
  si.gender,
  COUNT(*) as count,
  ROUND(AVG(si.admission_score), 2) as avg_score,
  MIN(si.admission_score) as min_score,
  MAX(si.admission_score) as max_score
FROM xg_student_info si
JOIN xg_class_info ci ON si.class_id = ci.id
WHERE si.class_id IN (1100001, 1100002, 1100003)
GROUP BY ci.class_name, si.gender
ORDER BY ci.class_name, si.gender;

-- 验证班级干部分布
SELECT '=== 班级干部分布 ===' as info;
SELECT
  ci.class_name,
  cs.class_position,
  cs.student_name,
  si.gender
FROM xg_class_student cs
JOIN xg_class_info ci ON cs.class_id = ci.id
JOIN xg_student_info si ON cs.student_id = si.id
WHERE cs.class_id IN (1100001, 1100002, 1100003)
AND cs.class_position != 'member'
ORDER BY cs.class_id, cs.position_level;

-- 恢复外键检查
SET FOREIGN_KEY_CHECKS = 1;

SELECT '🎉 学生信息数据生成完成！' as result;
SELECT '📊 总计：3个班级，150条学生信息，150条关联关系' as summary;
SELECT '👥 班级分布：' as distribution;
SELECT '   - 计算机科学与技术2024级1班：50人（男28，女22）' as class1;
SELECT '   - 电子信息工程2024级1班：50人（男32，女18）' as class2;
SELECT '   - 工商管理2024级1班：50人（男20，女30）' as class3;
"""

    return sql_content

if __name__ == "__main__":
    print("正在生成学生信息数据...")

    # 生成完整的SQL文件
    sql_content = generate_complete_sql()

    # 写入文件
    with open('complete-student-data.sql', 'w', encoding='utf-8') as f:
        f.write(sql_content)

    print("✅ 完整的学生数据SQL文件已生成完成！")
    print("📁 文件路径：complete-student-data.sql")
    print("📊 包含内容：")
    print("   - 3个班级信息")
    print("   - 150条学生信息")
    print("   - 150条班级学生关联关系")
    print("   - 完整的数据验证查询")
    print("\n🚀 您可以直接执行SQL文件来导入数据！")