数据库：触发器、游标查询

 问题一：DDL与DML

帮我创建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语句。 

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userName` varchar(50) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `introduce` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userName` (`userName`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


TE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scoreName` varchar(50) NOT NULL,
  `result` decimal(5,2) NOT NULL,
  `studentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `studentId` (`studentId`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

问题二

帮我创建一个多表联合查询以student为主表，查看学生的所有科目成绩总分。

SELECT 
    s.id AS student_id,
    s.userName,
    s.phone,
    s.age,
    s.sex,
    s.introduce,
    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, 
    s.phone, 
    s.age, 
    s.sex, 
    s.introduce;

问题三

帮我根据两个DDL创建一个插入信息的存储过程，使用循环先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，
所有信息随机生成，学生表的自增序号从6开始。不使用DECLARE声明，要使用set声明

DELIMITER //

CREATE PROCEDURE InsertRandomStudentAndScoreData()
BEGIN
    SET @i = 1;
    SET @student_id = 0;
    SET @rand_username = '';
    SET @rand_phone = '';
    SET @rand_age = 0;
    SET @rand_sex = '';
    SET @rand_introduce = '';
    SET @rand_score_name = '';
    SET @rand_result = 0.00;

    -- 重置学生表的AUTO_INCREMENT值
    ALTER TABLE `student` AUTO_INCREMENT = 6;

    -- 插入10条学生信息
    WHILE @i <= 10 DO
        SET @rand_username = CONCAT('Student', FLOOR(RAND() * 10000)); -- 生成随机用户名
        SET @rand_phone = CONCAT('1', FLOOR(RAND() * 900000000) + 100000000); -- 生成随机电话号码
        SET @rand_age = FLOOR(RAND() * 30) + 1; -- 生成1到30的随机年龄
        SET @rand_sex = IF(RAND() > 0.5, '男', '女'); -- 生成随机性别
        SET @rand_introduce = CONCAT('This is a random introduction for ', @rand_username); -- 生成随机介绍

        -- 插入学生信息
        INSERT INTO `student` (`userName`, `phone`, `age`, `sex`, `introduce`)
        VALUES (@rand_username, @rand_phone, @rand_age, @rand_sex, @rand_introduce);

        -- 获取新插入学生的ID
        SET @student_id = LAST_INSERT_ID();

        -- 为该学生插入10条成绩信息
        SET @j = 1;
        WHILE @j <= 10 DO
            SET @rand_score_name = CONCAT('Subject', FLOOR(RAND() * 10)); -- 生成随机科目名
            SET @rand_result = FLOOR(RAND() * 100) + 1; -- 生成1到100的随机成绩

            -- 插入成绩信息
            INSERT INTO `score` (`scoreName`, `result`, `studentId`)
            VALUES (@rand_score_name, @rand_result, @student_id);

            SET @j = @j + 1;
        END WHILE;

        SET @i = @i + 1;
    END WHILE;
END //

DELIMITER ;

CALL InsertRandomStudentAndScoreData();

问题四：触发器

帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。
并且给出测试示例。

-- 创建触发器
DELIMITER //
CREATE TRIGGER trg_update_scoreName
BEFORE UPDATE ON `score`
FOR EACH ROW
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET NEW.scoreName = '天书';
    END IF;
END;
//
DELIMITER ;
UPDATE `score` SET `scoreName` = '数学' WHERE id = 1;

问题五

创建一个两个表的综合【游标查询】，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。

DELIMITER //

CREATE PROCEDURE GetStudentAndScoreByName(IN p_userName VARCHAR(50))
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男','女');
    DECLARE v_introduce TEXT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result DECIMAL(5,2);

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

    -- 声明游标结束的处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 读取游标数据
    read_loop: LOOP
        FETCH cur INTO v_id, v_userName, v_phone, v_age, v_sex, v_introduce, v_scoreName, v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 这里可以处理查询到的数据，比如打印出来或者返回给客户端
        -- 例如，简单地将查询结果输出到控制台
        SELECT 
            v_id AS student_id, 
            v_userName AS student_name, 
            v_phone AS student_phone, 
            v_age AS student_age, 
            v_sex AS student_sex, 
            v_introduce AS student_introduce, 
            v_scoreName AS score_name, 
            v_result AS score_result;
    END LOOP;

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

DELIMITER ;

CALL GetStudentAndScoreByName('目标学生姓名');