-- 检查陪伴员表中擅长科目字段的数据情况

-- 1. 查看所有陪伴员的擅长科目数据
SELECT 
    id,
    teacher_name AS '姓名',
    phone AS '手机号',
    subjects AS '擅长科目',
    CASE 
        WHEN subjects IS NULL THEN '字段为NULL'
        WHEN subjects = '' THEN '字段为空字符串'
        ELSE '有数据'
    END AS '数据状态'
FROM teacher
ORDER BY id DESC
LIMIT 20;

-- 2. 统计擅长科目字段的数据情况
SELECT 
    COUNT(*) AS '总数',
    COUNT(subjects) AS '非NULL数量',
    SUM(CASE WHEN subjects IS NOT NULL AND subjects != '' THEN 1 ELSE 0 END) AS '有数据数量',
    SUM(CASE WHEN subjects IS NULL THEN 1 ELSE 0 END) AS 'NULL数量',
    SUM(CASE WHEN subjects = '' THEN 1 ELSE 0 END) AS '空字符串数量'
FROM teacher;

-- 3. 查看 teacher 表的字段定义
SHOW FULL COLUMNS FROM teacher LIKE 'subjects';

-- 4. 查看最近注册的陪伴员的完整信息
SELECT 
    id,
    teacher_name,
    phone,
    subjects,
    education,
    major,
    experience,
    teacher_level,
    audit_status,
    created_at
FROM teacher
ORDER BY id DESC
LIMIT 5;