存储过程&触发器

​
#问题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, -- 假设id是自增主键
    createDate DATE NOT NULL,
    userName VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    age INT,
    sex CHAR(1), -- 假设使用'M'代表男，'F'代表女
    introduce TEXT
);


-- 创建score表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 假设id是自增主键
    scoreName VARCHAR(100) NOT NULL,
    result FLOAT,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) -- 设置外键关联
);


-- 向student表中插入数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 20, 'M', '我是张三，喜欢学习。'),
('2023-01-02', '李四', '13800138001', 21, 'M', '我是李四，爱好运动。'),
('2023-01-03', '王五', '13800138002', 19, 'F', '我是王五，喜欢音乐。'),
('2023-01-04', '赵六', '13800138003', 20, 'F', '我是赵六，喜欢画画。'),
('2023-01-05', '孙七', '13800138004', 22, 'M', '我是孙七，喜欢编程。');



-- 向score表中插入数据
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.0, 1), ('英语', 85.0, 1),
('数学', 88.0, 2), ('英语', 92.0, 2),
('数学', 95.0, 3), ('历史', 87.0, 3),
('物理', 80.0, 4), ('化学', 89.0, 4),
('物理', 93.0, 5), ('化学', 91.0, 5);

#问题2：
根据score表的DDL添加4条语句，要求stuedntId为3和4，每个studentId都添加两条数据，科目是数学和英语。


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


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


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


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

#问题3：
帮我创建一个多表联合查询以student为主表，查看学生的所有科目成绩总分，并根据总分进行倒序排名，在查询第一列添加排名顺序。

SET @rank := 0;
SET @prev_value := NULL;

SELECT 
    (@rank := IF(@prev_value = total_score, @rank, @rank + 1)) AS ranking,
    @prev_value := total_score AS total_score,
    s.id AS student_id,
    s.userName
FROM (
    SELECT 
        studentId, 
        SUM(result) AS total_score 
    FROM 
        score 
    GROUP BY 
        studentId
) AS scores
JOIN student s ON scores.studentId = s.id
ORDER BY total_score DESC;

SELECT 
    RANK() OVER (ORDER BY total_score DESC) AS ranking,
    total_score,
    s.id AS student_id,
    s.userName
FROM (
    SELECT 
        studentId, 
        SUM(result) AS total_score 
    FROM 
        score 
    GROUP BY 
        studentId
) AS scores
JOIN student s ON scores.studentId = s.id;
#问提4：存储过程
帮我根据两个DDL创建一个插入信息的存储过程，并插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，
所有信息随机生成，学生表的自增序号从6开始。
ALTER TABLE student AUTO_INCREMENT = 6;
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_students DO
        INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES (
            CURDATE() - INTERVAL FLOOR(RAND() * 365) DAY, -- 随机创建日期
            CONCAT('Student', FLOOR(RAND() * 1000) + 1),   -- 随机用户名
            CONCAT('13800', FLOOR(RAND() * 10000) + 1000), -- 随机电话
            FLOOR(RAND() * 10 + 15),                       -- 15到24岁随机年龄
            IF(RAND() > 0.5, 'M', 'F'),                    -- 随机性别
            CONCAT('Introduction for ', FLOOR(RAND() * 1000) + 1) -- 随机介绍
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents(10);
DELIMITER //
CREATE PROCEDURE InsertRandomScores(IN num_scores INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_scores DO
        INSERT INTO score (scoreName, result, studentId) VALUES (
            CONCAT('Subject', FLOOR(RAND() * 10) + 1),    -- 随机科目名
            FLOOR(RAND() * 100) + 1,                      -- 1到100随机分数
            FLOOR(RAND() * (SELECT MAX(id) FROM student - MIN(id) + 1)) + MIN(id) -- 随机studentId
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
CALL InsertRandomScores(100);

#问提5：触发器（单表）
 帮我创建一个修次score表scorelName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成（天书）。
 DELIMITER //
CREATE TRIGGER tr_score_before_update
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET NEW.scoreName = '天书';
    END IF;
END;
//
DELIMITER ;


#问题6：游标
 创建一个两个表的综合游标查询，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。
DELIMITER //
CREATE PROCEDURE GetStudentInfoByName(IN student_name VARCHAR(100))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE s_id INT;
    DECLARE s_createDate DATE;
    DECLARE s_userName VARCHAR(100);
    DECLARE s_phone VARCHAR(20);
    DECLARE s_age INT;
    DECLARE s_sex CHAR(1);
    DECLARE s_introduce TEXT;
    DECLARE sc_id INT;
    DECLARE sc_scoreName VARCHAR(100);
    DECLARE sc_result FLOAT;
    -- 声明游标
    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 = student_name;
    -- 声明结束处理器，当游标完成后设置done变量
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 循环遍历游标中的所有记录
    read_loop: LOOP
        FETCH cur INTO 
            s_id, s_createDate, s_userName, s_phone, s_age, s_sex, s_introduce,
            sc_id, sc_scoreName, sc_result;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 在此处处理每一行数据，例如打印或保存到变量中
        -- 可以使用如 SELECT, INSERT, 或其他逻辑操作
        SELECT 
            s_id, s_createDate, s_userName, s_phone, s_age, s_sex, s_introduce,
            sc_id, sc_scoreName, sc_result;
    END LOOP;

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

​