【无标题】

DDL

-- 创建 student 表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userName VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女', '其他') NOT NULL,
    introduce TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 创建 score 表
CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scoreName VARCHAR(255) NOT NULL,
    result DECIMAL(5, 2) NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DML

-- 插入学生信息
INSERT INTO student (userName, age, sex, introduce) VALUES
('张三', 20, '男', '热爱学习，成绩优异。'),
('李四', 21, '女', '性格开朗，乐于助人。'),
('王五', 19, '男', '沉默寡言，专注力强。'),
('赵六', 20, '女', '多才多艺，擅长绘画。'),
('孙七', 22, '其他', '热爱运动，体育健将。');


-- 插入成绩信息，假设每个学生有数学和英语两个科目的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.00, 1),
('英语', 85.00, 1),
('数学', 88.00, 2),
('英语', 92.00, 2),
('数学', 95.00, 3),
('英语', 87.00, 3),
('数学', 82.00, 4),
('英语', 89.00, 4),
('数学', 78.00, 5),
('英语', 80.00, 5);

问题1
帮我创建2个student与score表，要求student表有id,createDate,userName,age,sex,introduce,要求score表有id,scoreName,result,studentId(student表的外键)，要求student表中插入5条学生信息，都是要中文的。要求score表中对应studentId插入10条信息，每个学生ID要有2个科目的成绩，给出DDL与DML语句
问题2
根据score表的DDL添加4条语句，要求studentId为3和4，每个studentId都添加2条数据，科目是数学和英语。

-- 为studentId为3的学生添加数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 95.50, 3);

-- 为studentId为3的学生添加英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 87.50, 3);


-- 为studentId为4的学生添加数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 82.50, 4);

-- 为studentId为4的学生添加英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 89.50, 4);
问题3
帮我创建一个多表联合查询以student为主表，查看学生的所有科目成绩总分，并根据总分进行倒序排名，在查询第一列添加排名顺序
SELECT 
    (@row_number:=@row_number + 1) AS ranking,
    s.id,
    s.userName,
    SUM(sc.result) AS total_score
FROM
    student s
JOIN 
    score sc ON s.id = sc.studentId
JOIN 
    (SELECT @row_number:=0) r -- 初始化排名变量
GROUP BY 
    s.id, s.userName
ORDER BY 
    total_score DESC;
-
问题4 存储过程  
帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，
所有信息随机生成，学生表的自增序号从6开始。
DELIMITER //

CREATE PROCEDURE InsertRandomData()
BEGIN
    DECLARE v_student_counter INT DEFAULT 0;
    DECLARE v_score_counter INT DEFAULT 0;
    DECLARE v_random_student_id INT;
    DECLARE v_random_age INT;
    DECLARE v_random_sex ENUM('男', '女', '其他');
    DECLARE v_random_score DECIMAL(5, 2);
    DECLARE v_random_score_name VARCHAR(255);
    
    -- 插入10条随机学生信息
    WHILE v_student_counter < 10 DO
        SET v_random_age = FLOOR(RAND() * 10 + 10); -- 随机生成10到19岁之间的年龄
        SET v_random_sex = ELT(1 + FLOOR(RAND() * 3), '男', '女', '其他'); -- 随机选择性别
        
        -- 插入学生记录，并获取自增ID（如果需要从6开始，确保之前已有5条记录或自增起始值已设置）
        INSERT INTO student(userName, age, sex, introduce)
        VALUES (CONCAT('Student', FLOOR(RAND() * 1000)), v_random_age, v_random_sex, 'Random Introduction');
        
        SET v_student_counter = v_student_counter + 1;
    END WHILE;
    
    -- 插入100条随机成绩信息
    WHILE v_score_counter < 100 DO
        -- 随机选择一个已存在的学生ID（假设学生ID从1自增到10）
        SET v_random_student_id = FLOOR(RAND() * 10) + 1;
        SET v_random_score = FLOOR(RAND() * 100) + 1; -- 随机生成1到100之间的分数
        SET v_random_score_name = CONCAT('Score', FLOOR(RAND() * 10)); -- 随机生成成绩名称
        
        -- 插入成绩记录
        INSERT INTO score(scoreName, result, studentId)
        VALUES (v_random_score_name, v_random_score, v_random_student_id + 5); -- 假设前面已有5个学生，ID从6开始
        
        SET v_score_counter = v_score_counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL InsertRandomData();

问题5
帮我创建一个修改score表scoreName的触发器，当修改scoreName是否数学，如果是数学就改成（天书）
DELIMITER //

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

//

DELIMITER ;
问题6 演示
创建一个两个表的综合游标查询，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。
DELIMITER //

DELIMITER //

CREATE PROCEDURE GetStudentAndScoreInfo(IN student_name VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_student_id INT;
    DECLARE v_score_id INT;
    DECLARE v_score_name VARCHAR(255);
    DECLARE v_result DECIMAL(5, 2);
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT s.id, sc.id AS score_id, sc.scoreName, sc.result
        FROM student s
        JOIN score sc ON s.id = sc.studentId
        WHERE s.userName = student_name;
    -- 声明结束处理器，当游标完成时设置done = 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开游标
    OPEN cur;
    
    read_loop: LOOP
        -- 从游标中取值
        FETCH cur INTO v_student_id, v_score_id, v_score_name, v_result;
        -- 检查是否达到了结果集的末尾
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出结果
        SELECT v_student_id AS StudentID, v_score_id AS ScoreID, v_score_name AS ScoreName, v_result AS Result;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;

DELIMITER ;