all-project/test/学院专业班级字段修复验证.sql

-- ==============================
-- 学院专业班级字段修复验证脚本
-- 创建时间: 2025-01-15
-- 说明: 验证修复后的学院、专业、班级字段写入情况
-- ==============================

USE `hzdyjs_xg`;

-- 1. 清理之前的测试数据
DELETE FROM xg_student_info WHERE student_no = 'TEST004';

-- 2. 测试插入数据（包含学院、专业、班级的代码和名称）
INSERT INTO xg_student_info (
    student_no, id_card, name, gender, nationality, political_status, student_status, 
    enrollment_year, school_system, 
    college_code, college_name, major_code, major_name, class_code, class_name, grade,
    phone, email, info_status, data_source, is_complete, completion_rate, 
    remark, create_dept, create_by, create_time, update_by, update_time, tenant_id
) VALUES (
    'TEST004', '666666666666666666', '测试学生4', '男', '汉族', '团员', '1', 
    2025, 4, 
    'CS', '计算机学院', 'CS001', '计算机科学与技术', 'CS2025001', '计算机科学与技术2025级1班', '2025',
    '13800000003', 'test4@example.com', 'active', 'manual', 1, 100.00, 
    '测试学院专业班级字段修复', 103, 1, NOW(), 1, NOW(), '000000'
);

-- 3. 验证插入成功，检查所有相关字段
SELECT '验证学院专业班级字段插入成功...' as message;
SELECT 
    student_no, 
    name, 
    college_code, 
    college_name,
    major_code, 
    major_name,
    class_code, 
    class_name,
    grade,
    info_status
FROM xg_student_info
WHERE student_no = 'TEST004';

-- 4. 检查字段是否为空
SELECT '检查字段是否为空...' as message;
SELECT 
    CASE WHEN college_code IS NULL OR college_code = '' THEN '学院代码为空' ELSE CONCAT('学院代码: ', college_code) END as college_code_status,
    CASE WHEN college_name IS NULL OR college_name = '' THEN '学院名称为空' ELSE CONCAT('学院名称: ', college_name) END as college_name_status,
    CASE WHEN major_code IS NULL OR major_code = '' THEN '专业代码为空' ELSE CONCAT('专业代码: ', major_code) END as major_code_status,
    CASE WHEN major_name IS NULL OR major_name = '' THEN '专业名称为空' ELSE CONCAT('专业名称: ', major_name) END as major_name_status,
    CASE WHEN class_code IS NULL OR class_code = '' THEN '班级代码为空' ELSE CONCAT('班级代码: ', class_code) END as class_code_status,
    CASE WHEN class_name IS NULL OR class_name = '' THEN '班级名称为空' ELSE CONCAT('班级名称: ', class_name) END as class_name_status
FROM xg_student_info
WHERE student_no = 'TEST004';

-- 5. 测试更新操作
UPDATE xg_student_info 
SET 
    college_code = 'IT', 
    college_name = '信息技术学院',
    major_code = 'SE001', 
    major_name = '软件工程',
    class_code = 'SE2025001', 
    class_name = '软件工程2025级1班',
    update_by = 1,
    update_time = NOW()
WHERE student_no = 'TEST004';

-- 6. 验证更新成功
SELECT '验证更新成功...' as message;
SELECT 
    student_no, 
    name, 
    college_code, 
    college_name,
    major_code, 
    major_name,
    class_code, 
    class_name,
    grade
FROM xg_student_info
WHERE student_no = 'TEST004';

-- 7. 检查数据完整性
SELECT '检查数据完整性...' as message;
SELECT 
    CASE 
        WHEN college_code IS NOT NULL AND college_name IS NOT NULL THEN '学院信息完整' 
        ELSE '学院信息不完整' 
    END as college_integrity,
    CASE 
        WHEN major_code IS NOT NULL AND major_name IS NOT NULL THEN '专业信息完整' 
        ELSE '专业信息不完整' 
    END as major_integrity,
    CASE 
        WHEN class_code IS NOT NULL AND class_name IS NOT NULL THEN '班级信息完整' 
        ELSE '班级信息不完整' 
    END as class_integrity
FROM xg_student_info
WHERE student_no = 'TEST004';

-- 8. 清理测试数据
DELETE FROM xg_student_info WHERE student_no = 'TEST004';

-- 9. 验证清理成功
SELECT '验证清理成功...' as message;
SELECT COUNT(*) as remaining_test_records
FROM xg_student_info
WHERE student_no = 'TEST004';

-- 10. 完成测试
SELECT '学院专业班级字段修复验证完成！' as message;
SELECT '前端修复后，学院、专业、班级的代码和名称都应该能正确写入数据库' as result;