使用baidu comate创建代码2

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 DATE NOT NULL,
    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 PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(50) NOT NULL,
    result INT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);

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

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

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

DELIMITER //

CREATE PROCEDURE InsertStudentsAndScores()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE random_date DATE;
    DECLARE random_name VARCHAR(50);
    DECLARE random_phone VARCHAR(20);
    DECLARE random_age INT;
    DECLARE random_sex ENUM('男', '女');
    DECLARE random_introduce TEXT;
    DECLARE random_score_name VARCHAR(50);
    DECLARE random_result INT;
    
    -- 设置学生表的自增序号从6开始
    ALTER TABLE student AUTO_INCREMENT = 6;
    
    -- 插入10条学生表信息
    WHILE i <= 10 DO
        -- 生成随机数据
        SET random_date = DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY);
        SET random_name = CONCAT('Student', FLOOR(RAND() * 1000));
        SET random_phone = CONCAT('1', FLOOR(RAND() * 900000000) + 10000000);
        SET random_age = FLOOR(RAND() * 30) + 5; -- 假设年龄范围是5到34岁
        SET random_sex = IF(RAND() > 0.5, '男', '女');
        SET random_introduce = CONCAT('This is a random introduction for ', random_name);
        
        -- 插入学生信息
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (random_date, random_name, random_phone, random_age, random_sex, random_introduce);
        
        SET i = i + 1;
    END WHILE;
    
    -- 为每个学生插入10条成绩记录
    SET j = 1;
    WHILE j <= 10 DO
        -- 获取学生的id
        SET @studentId = (SELECT id FROM student ORDER BY id ASC LIMIT 1 OFFSET (j - 1));
        
        -- 为该学生插入10条成绩记录
        WHILE i <= 10 DO
            -- 生成随机数据
            SET random_score_name = CONCAT('Subject', FLOOR(RAND() * 10));
            SET random_result = FLOOR(RAND() * 100); -- 假设成绩范围是0到99
            
            -- 插入成绩信息
            INSERT INTO score (scoreName, result, studentId)
            VALUES (random_score_name, random_result, @studentId);
            
            SET i = i + 1;
        END WHILE;
        
        -- 重置i为1以便下一个学生使用
        SET i = 1;
        
        SET j = j + 1;
    END WHILE;
END //

DELIMITER ;

CALL InsertStudentsAndScores();

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

DELIMITER //

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

DELIMITER ;
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 85, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 78, 2);


UPDATE score SET scoreName = '数学' WHERE id = 1;  -- 假设第一条记录的id是1

SELECT * FROM score WHERE id = 1;

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

DELIMITER //

CREATE PROCEDURE GetStudentScoresByName(IN p_userName VARCHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_studentId INT;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男', '女');
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result INT;
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.id AS studentId, 
            s.userName, 
            s.age, 
            s.sex, 
            sc.scoreName, 
            sc.result
        FROM 
            student s
        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_studentId, v_userName, v_age, v_sex, v_scoreName, v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 在这里处理每一行的数据，例如输出到控制台或返回给客户端
        -- 这里只是一个示例，你可以根据需求进行其他操作
        SELECT 
            v_userName AS '学生姓名',
            v_age AS '年龄',
            v_sex AS '性别',
            v_scoreName AS '成绩名称',
            v_result AS '成绩';
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;

CALL GetStudentScoresByName('张三');