MySQL存储过程_触发器_游标——Baidu Comate

问题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语句。

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


-- 创建score表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    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
);

问题2：

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

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

问题3：

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

DELIMITER //

CREATE PROCEDURE InsertRandomStudentsAndScores()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE student_id INT;
    DECLARE random_name VARCHAR(50);
    DECLARE random_phone VARCHAR(15);
    DECLARE random_age INT;
    DECLARE random_sex ENUM('男', '女');
    DECLARE random_introduce TEXT;
    DECLARE random_score_name VARCHAR(50);
    DECLARE random_result DECIMAL(5,2);
    
    -- 设置AUTO_INCREMENT的起始值为6
    ALTER TABLE student AUTO_INCREMENT = 6;
    
    -- 插入10条学生信息
    WHILE i < 10 DO
        -- 生成随机学生信息
        SET random_name = CONCAT('学生', FLOOR(RAND() * 1000));
        SET random_phone = CONCAT('1', FLOOR(RAND() * 10000000000), FLOOR(RAND() * 10000));
        SET random_age = FLOOR(18 + (RAND() * 10));
        SET random_sex = IF(RAND() > 0.5, '男', '女');
        SET random_introduce = CONCAT('学生', FLOOR(RAND() * 1000), '的自我介绍...');
        
        -- 插入学生信息
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (NOW(), random_name, random_phone, random_age, random_sex, random_introduce);
        
        -- 获取刚插入的学生的ID
        SELECT LAST_INSERT_ID() INTO student_id;
        
        -- 为该学生插入10条成绩信息
        SET i = 0;
        WHILE i < 10 DO
            -- 生成随机成绩信息
            SET random_score_name = CONCAT('科目', FLOOR(RAND() * 10));
            SET random_result = FLOOR(60 + (RAND() * 41)) / 10.0; -- 生成60到100之间的随机小数
            
            -- 插入成绩信息
            INSERT INTO score (scoreName, result, studentId)
            VALUES (random_score_name, random_result, student_id);
            
            SET i = i + 1;
        END WHILE;
        
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

调用：

CALL InsertRandomStudentsAndScores();

问题4：

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

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 ;


-- 插入学生数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (NOW(), '张三', '1234567890', 20, '男', '这是张三的个人介绍');


-- 假设上面插入的学生id为1，插入成绩数据
INSERT INTO score (scoreName, result, studentId)
VALUES ('数学', 90.50, 1);


-- 尝试更新scoreName为数学
UPDATE score SET scoreName = '数学' WHERE id = 1;


-- 检查scoreName是否被正确修改为(天书)
SELECT * FROM score WHERE id = 1;

 

问题5：

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

DELIMITER //

CREATE PROCEDURE GetStudentScoresByName(IN p_userName VARCHAR(50))
BEGIN
    -- 声明变量来存储从游标中检索到的数据
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_student_id INT;
    DECLARE v_createDate DATETIME;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_phone VARCHAR(15);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男', '女');
    DECLARE v_introduce TEXT;
    DECLARE v_score_id INT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result DECIMAL(5,2);

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

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

    -- 打开游标
    OPEN cur;

    -- 遍历游标中的数据
    read_loop: LOOP
        FETCH cur INTO v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce,
            v_score_id, v_scoreName, v_result;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 这里你可以根据需要处理检索到的数据，例如输出到控制台或返回给客户端等
        -- 在这里我们仅打印出数据
        SELECT
            v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce,
            v_score_id, v_scoreName, v_result;
    END LOOP;

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

DELIMITER ;

 调用游标：

CALL GetStudentScoresByName('张三');