多表联合查询+触发器+游标查询

1.schooldb库——utf8字符集——utf8_general_ci排序规则

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

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

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

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

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

3.DDL

-- 创建student表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    createDate DATETIME NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex VARCHAR(10) 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
);

4.DML

-- 插入学生信息到student表
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-07-19 10:00:00', '张三', '13800138000', 20, '男', '张三是一个勤奋好学的学生。'),
('2023-07-18 15:30:00', '李四', '13900139000', 21, '女', '李四对编程非常感兴趣。'),
('2023-07-17 09:15:00', '王五', '13700137000', 19, '男', '王五喜欢阅读和运动。'),
('2023-07-16 14:45:00', '赵六', '13600136000', 22, '女', '赵六是班级的文艺骨干。'),
('2023-07-15 08:30:00', '孙七', '13500135000', 20, '男', '孙七擅长数学和物理。');


-- 插入成绩信息到score表
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.75, (SELECT id FROM student WHERE userName = '王五')),
('地理', 94.00, (SELECT id FROM student WHERE userName = '赵六')),
('生物', 89.25, (SELECT id FROM student WHERE userName = '赵六')),
('政治', 82.50, (SELECT id FROM student WHERE userName = '孙七')),
('信息技术', 91.75, (SELECT id FROM student WHERE userName = '孙七'));

5.SQL

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;
		
		

DELIMITER //
CREATE PROCEDURE InsertRandomStudentAndScoreInfo()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE student_id INT;
    DECLARE random_name VARCHAR(50);
    DECLARE random_phone VARCHAR(20);
    DECLARE random_age INT;
    DECLARE random_sex VARCHAR(10);
    DECLARE random_introduce TEXT;
    DECLARE random_score_name VARCHAR(50);
    DECLARE random_result DECIMAL(5, 2);

    -- 设置student表的自增ID从6开始
    ALTER TABLE student AUTO_INCREMENT = 6;

    -- 插入10条随机学生信息
    WHILE i <= 10 DO
        SET random_name = CONCAT('Student', FLOOR(RAND() * 10000));
        SET random_phone = CONCAT('1', LPAD(FLOOR(RAND() * 100000000), 8, '0'));
        SET random_age = FLOOR(RAND() * 30) + 5; -- 假设年龄范围为5到35岁
        SET random_sex = IF(RAND() > 0.5, '男', '女');
        SET random_introduce = CONCAT('这是一个关于', random_name, '的随机介绍');

        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (NOW() - INTERVAL FLOOR(RAND() * 365) DAY, random_name, random_phone, random_age, random_sex, random_introduce);

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

        -- 为当前学生插入10条随机成绩信息
        SET j = 1;
        WHILE j <= 10 DO
            SET random_score_name = CONCAT('科目', FLOOR(RAND() * 10) + 1); -- 假设科目从科目1到科目10
            SET random_result = FLOOR(RAND() * 100) + 50; -- 假设成绩范围是50到100分

            INSERT INTO score (scoreName, result, studentId)
            VALUES (random_score_name, random_result, student_id);

            SET j = j + 1;
        END WHILE;

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

CALL InsertRandomStudentAndScoreInfo();

DELIMITER //
CREATE TRIGGER tr_update_scoreName
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
('英语', 85.50, 1),
('数学', 90.25, 2),
('物理', 78.00, 3);

UPDATE score SET scoreName = '数学' WHERE id = 2;

SELECT * FROM score;

DELIMITER //

CREATE PROCEDURE GetStudentScoresByName(IN p_userName VARCHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_studentId INT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result DECIMAL(5, 2);
    DECLARE cur CURSOR FOR 
        SELECT s.id, 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_scoreName, v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 这里你可以处理每一行的数据，例如输出到控制台或返回给调用者
        SELECT v_studentId, v_scoreName, v_result;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

CALL GetStudentScoresByName('要查询的学生姓名');