MySQL——存储过程，触发器

BaiduComate:

# 问题1：
# 问题1：
帮我创建两个表student与score表，要求student表有id，createDate，userName，phone，age，sex，introduce，
要求score表有id，scoreName，result，result，studentId(student表的id外键)。
要求student表中插入5条学生信息，都要是中文的。
要求student表中对应studentId插入10条信息，每个学生ID要有2个科目的成绩。
给出DDL与DML语句。

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 学生ID，自增主键
    createDate DATETIME NOT NULL,       -- 创建日期
    userName VARCHAR(255) NOT NULL,     -- 用户名
    phone VARCHAR(20),                   -- 电话号码
    age INT NOT NULL,                    -- 年龄
    sex ENUM('男', '女', '其他'), -- 性别
    introduce TEXT                       -- 简介
);
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,      -- 成绩ID，自增主键
    scoreName VARCHAR(255) NOT NULL,        -- 成绩名称（科目）
    result INT NOT NULL,                     -- 成绩结果
    studentId INT,                           -- 学生ID，外键
    FOREIGN KEY (studentId) REFERENCES student(id) -- 外键约束
);

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01 08:00:00', '张三', '13800138000', 20, '男', '来自计算机科学专业'),
('2023-04-01 08:10:00', '李四', '13800138001', 21, '男', '数学系高材生'),
('2023-04-01 08:20:00', '王五', '13800138002', 19, '女', '物理系新生'),
('2023-04-01 08:30:00', '赵六', '13800138003', 20, '女', '化学系学生'),
('2023-04-01 08:40:00', '孙七', '13800138004', 22, '男', '文学爱好者');



-- 张三的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 95, (SELECT id FROM student WHERE userName = '张三')),
('英语', 88, (SELECT id FROM student WHERE userName = '张三'));


-- 李四的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('物理', 92, (SELECT id FROM student WHERE userName = '李四')),
('化学', 85, (SELECT id FROM student WHERE userName = '李四'));


-- 王五的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('计算机', 90, (SELECT id FROM student WHERE userName = '王五')),
('历史', 80, (SELECT id FROM student WHERE userName = '王五'));


-- 赵六的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('生物', 85, (SELECT id FROM student WHERE userName = '赵六')),
('地理', 82, (SELECT id FROM student WHERE userName = '赵六'));


-- 孙七的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('政治', 89, (SELECT id FROM student WHERE userName = '孙七')),
('音乐', 93, (SELECT id FROM student WHERE userName = '孙七'));

DQL

多表联合

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

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

		

存储过程

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

DELIMITER //
CREATE PROCEDURE InsertStudentAndScoreData()
BEGIN
    DECLARE v_student_id INT;
    DECLARE v_counter INT;
    DECLARE v_random_score_count INT;
    
    -- 设置学生ID自增值为6
    ALTER TABLE student AUTO_INCREMENT = 6;
    
    -- 插入10条随机学生信息
    WHILE (SELECT COUNT(*) FROM student) < 10 DO
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (
            NOW(),
            CONCAT('Student_', FLOOR(RAND() * 10000)),
            CONCAT('+1', FLOOR(RAND() * 899999999) + 100000000), -- 随机电话号码
            FLOOR(RAND() * (50 - 18 + 1)) + 18, -- 随机年龄在18到50之间
            ELT(1 + FLOOR(RAND() * 3), '男', '女', '其他'), -- 随机性别
            CONCAT('Introduction for ', FLOOR(RAND() * 10000)) -- 随机简介
        );
        SET v_student_id = LAST_INSERT_ID(); -- 获取最近插入的学生ID
        
        -- 为该学生随机插入1到18条成绩记录
        SET v_random_score_count = FLOOR(RAND() * 18) + 1; -- 随机决定成绩记录数
        SET v_counter = 0;
        WHILE v_counter < v_random_score_count DO
            INSERT INTO score (scoreName, result, studentId)
            VALUES (
                CONCAT('Subject_', FLOOR(RAND() * 100)), -- 随机科目名
                FLOOR(RAND() * 101), -- 随机分数0到100
                v_student_id -- 学生ID
            );
            SET v_counter = v_counter + 1;
        END WHILE;
    END WHILE;
END //
DELIMITER ;

CALL InsertStudentAndScoreData();

DELIMITER //
CREATE PROCEDURE InsertStudentAndScoreData()
BEGIN
    DECLARE v_student_id INT;
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_total_scores_to_insert INT DEFAULT 18;
    DECLARE v_scores_inserted INT DEFAULT 0;
    DECLARE v_random_student_index INT;
    DECLARE v_random_score_name VARCHAR(255);
    DECLARE v_random_result INT;
    
    -- 设置学生表的自增序号从6开始
    ALTER TABLE student AUTO_INCREMENT = 6;

    -- 插入10条随机学生信息
    WHILE v_counter < 10 DO
        INSERT INTO student(createDate, userName, phone, age, sex, introduce)
        VALUES (
            NOW(),
            CONCAT('User', FLOOR(RAND() * 10000)),
            CONCAT('+', FLOOR(RAND() * 9999999999)),
            FLOOR(RAND() * 10 + 18),
            ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other'),
            'Random Introduction'
        );
        SET v_counter = v_counter + 1;
    END WHILE;
    
    -- 初始化计数器
    SET v_counter = 0;
    
    -- 插入成绩，直到达到18条记录
    WHILE v_scores_inserted < v_total_scores_to_insert DO
        -- 随机选择一个已插入的学生ID
        SET v_random_student_index = FLOOR(RAND() * 10) + 6; -- 学生ID范围从6到15
        
        -- 生成随机成绩数据和科目名称
        SET v_random_score_name = CONCAT('Subject', FLOOR(RAND() * 10));
        SET v_random_result = FLOOR(RAND() * 101); -- 随机分数从0到100
        
        -- 插入成绩记录
        INSERT INTO score(scoreName, result, studentId)
        VALUES (v_random_score_name, v_random_result, v_random_student_index);
        
        -- 更新已插入成绩计数
        SET v_scores_inserted = v_scores_inserted + 1;
    END WHILE;
END //
DELIMITER ;

CALL InsertStudentAndScoreData();

#问题4: 触发器
帮我创建一个修改score表scoreName的触发器，当修改scoreNlame的时候脸发，判断修改的scoreName是否是数学，如果是数学就改成(天书).
并且给出测试示例。
#问题5:游标
创建一个两个表的综合游标查询，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。