插入存储过程 触发器 游标

问题1：
帮我创建2个表student与score表，要求student表有id,createDate,userName,phone,age,sex,introduce，
要求score表有id,scoreName,result,studentId(student表的id外键)。
要求student表中插入5条学生信息，都要是中文的。
要求score表中对应studentId插入10条信息，每个学生ID要有2个科目的成绩。
给出DDL与DML语句。 
问题2：
帮我创建一个多表联合查询以student为主表，查看学生的所有科目成绩总分。
问题3：
帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，
所有信息随机生成，学生表的自增序号从6开始。
问题4：
帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。
并且给出测试示例。
问题5：
创建一个两个表的综合【游标查询】，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。

-- 创建student表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);


-- 创建score表
CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scoreName VARCHAR(50) NOT NULL,
    result DECIMAL(5, 2) NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 向student表中插入5条学生信息
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张三', '13800138000', 20, '男', '来自北京的学生，爱好音乐'),
('李四', '13900139000', 21, '女', '来自上海的学生，擅长体育'),
('王五', '13700137000', 19, '男', '来自广州的学生，喜欢阅读'),
('赵六', '13600136000', 22, '女', '来自深圳的学生，对编程感兴趣'),
('钱七', '13500135000', 20, '男', '来自杭州的学生，热爱旅行');


-- 假设我们插入的数据按照张三、李四、王五、赵六、钱七的顺序，每个学生有2个科目成绩

-- 向score表中插入10条成绩信息
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.50, (SELECT id FROM student WHERE userName = '张三')),
('英语', 85.25, (SELECT id FROM student WHERE userName = '张三')),
('物理', 88.75, (SELECT id FROM student WHERE userName = '李四')),
('化学', 92.00, (SELECT id FROM student WHERE userName = '李四')),
('语文', 78.50, (SELECT id FROM student WHERE userName = '王五')),
('历史', 86.00, (SELECT id FROM student WHERE userName = '王五')),
('生物', 93.50, (SELECT id FROM student WHERE userName = '赵六')),
('地理', 89.25, (SELECT id FROM student WHERE userName = '赵六')),
('政治', 81.75, (SELECT id FROM student WHERE userName = '钱七')),
('信息技术', 95.00, (SELECT id FROM student WHERE userName = '钱七'));

SELECT 
    s.id AS student_id,
    s.userName AS student_name,
    COALESCE(SUM(sc.result), 0) AS total_score
FROM 
    student s
LEFT JOIN 
    score sc ON s.id = sc.studentId
GROUP BY 
    s.id, s.userName;

DELIMITER //

CREATE PROCEDURE InsertRandomStudentAndScoreData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_userName VARCHAR(50);
    DECLARE random_phone VARCHAR(20);
    DECLARE random_age INT;
    DECLARE random_sex ENUM('男', '女');
    DECLARE random_introduce TEXT;
    DECLARE random_scoreName VARCHAR(50);
    DECLARE random_result DECIMAL(5, 2);
    
    -- 设置AUTO_INCREMENT的起始值为6
    ALTER TABLE student AUTO_INCREMENT = 6;
    
    -- 插入10条学生信息
    WHILE i <= 10 DO
        -- 生成随机学生信息
        SET random_userName = CONCAT('Student', FLOOR(RAND() * 1000));
        SET random_phone = CONCAT('1', FLOOR(RAND() * 100000000), FLOOR(RAND() * 10000));
        SET random_age = FLOOR(RAND() * (30 - 10 + 1)) + 10; -- 年龄范围假设为10到30岁
        SET random_sex = IF(RAND() > 0.5, '男', '女');
        SET random_introduce = CONCAT('This is a random introduction for ', random_userName);
        
        -- 插入学生信息
        INSERT INTO student (userName, phone, age, sex, introduce) VALUES (random_userName, random_phone, random_age, random_sex, random_introduce);
        
        -- 为当前学生插入10条成绩信息
        SET i = 1;
        WHILE i <= 10 DO
            -- 生成随机成绩信息
            SET random_scoreName = CONCAT('Subject', FLOOR(RAND() * 10));
            SET random_result = FLOOR(RAND() * (100 - 0 + 1)) + 0; -- 成绩范围假设为0到100分
            
            -- 获取最近插入的学生的ID
            SET @lastStudentId = LAST_INSERT_ID();
            
            -- 插入成绩信息
            INSERT INTO score (scoreName, result, studentId) VALUES (random_scoreName, random_result, @lastStudentId);
            
            SET i = i + 1;
        END WHILE;
        
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER trg_update_scoreName_to_tianshu
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET NEW.scoreName = '天书';
    END IF;
END;
//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE GetStudentScoresByName(IN studentName VARCHAR(50))
BEGIN
    -- 声明变量用于存储从游标中获取的数据
    DECLARE done INT DEFAULT FALSE;
    DECLARE student_id INT;
    DECLARE student_username VARCHAR(50);
    DECLARE student_phone VARCHAR(20);
    DECLARE student_age INT;
    DECLARE student_sex ENUM('男', '女');
    DECLARE student_introduce TEXT;
    DECLARE score_id INT;
    DECLARE score_name VARCHAR(50);
    DECLARE score_result DECIMAL(5, 2);

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT 
            s.id, s.userName, s.phone, s.age, s.sex, s.introduce, 
            sc.id, sc.scoreName, sc.result
        FROM student s
        LEFT JOIN score sc ON s.id = sc.studentId
        WHERE s.userName = studentName;

    -- 声明一个 CONTINUE HANDLER，当游标完成时设置 done 标志为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 读取游标中的数据
    read_loop: LOOP
        FETCH cur INTO student_id, student_username, student_phone, 
            student_age, student_sex, student_introduce, 
            score_id, score_name, score_result;
        
        -- 检查是否还有更多行数据
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 输出学生信息
        SELECT 
            CONCAT('Student ID: ', student_id),
            CONCAT('Name: ', student_username),
            CONCAT('Phone: ', student_phone),
            CONCAT('Age: ', student_age),
            CONCAT('Sex: ', student_sex),
            CONCAT('Introduce: ', student_introduce);

        -- 如果有成绩信息，则输出成绩
        IF score_id IS NOT NULL THEN
            SELECT 
                CONCAT('Score ID: ', score_id),
                CONCAT('Score Name: ', score_name),
                CONCAT('Result: ', score_result);
        END IF;
    END LOOP;

    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;