MySQL存储过程_触发器_游标——Baidu Comate

红目香薰

发布于 2024-05-26 16:34:28

890

发布于 2024-05-26 16:34:28

文章被收录于专栏：CSDNToQQCode

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

DDL+DML

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);

CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(255) NOT NULL,
    result FLOAT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01', '张三', '13800138000', 20, '男', '学习刻苦'),
('2023-04-02', '李四', '13800138001', 21, '女', '成绩优异'),
('2023-04-03', '王五', '13800138002', 22, '男', '热爱运动'),
('2023-04-04', '赵六', '13800138003', 20, '女', '文静内向'),
('2023-04-05', '孙七', '13800138004', 21, '男', '乐于助人');

-- 张三的成绩
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 ('数学', 88, 2);
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 92, 2);


-- 王五的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 80, 3);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 85, 3);


-- 赵六的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 95, 4);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 89, 4);


-- 孙七的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 78, 5);
INSERT INTO score (scoreName, result, studentId) VALUES ('生物', 87, 5);

问题2：

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

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

问题3：

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

DELIMITER //
CREATE PROCEDURE InsertRandomData()
BEGIN
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_student_id INT;
    DECLARE v_random_age INT;
    DECLARE v_random_score FLOAT;
    DECLARE v_random_sex ENUM('男', '女');
    DECLARE v_date DATE;
    DECLARE v_user_name VARCHAR(255);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_score_name VARCHAR(255);
    
    -- 设置自增ID从6开始
    SET @@auto_increment_increment = 1;
    ALTER TABLE student AUTO_INCREMENT = 6;

    -- 插入学生信息
    WHILE v_counter < 10 DO
        SET v_random_age = FLOOR(RAND() * 10 + 15);  -- 假设年龄在15到25之间
        SET v_random_sex = IF(RAND() > 0.5, '男', '女');
        SET v_date = CURDATE() - INTERVAL FLOOR(RAND() * 365) DAY;  -- 随机生成一个过去的日期
        SET v_user_name = CONCAT('User', FLOOR(RAND() * 1000));  -- 随机用户名
        SET v_phone = CONCAT('138', LPAD(FLOOR(RAND() * 10000000), 7, '0'));  -- 随机电话
        
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (v_date, v_user_name, v_phone, v_random_age, v_random_sex, 'Random Introduction');
        
        SET v_counter = v_counter + 1;
    END WHILE;
    
    SET v_counter = 0;  -- 重置计数器
    
    -- 插入成绩信息
    WHILE v_counter < 100 DO
        SET v_student_id = FLOOR(RAND() * 10) + 6;  -- 随机选择一个已存在的学生ID（6到15之间）
        SET v_random_score = FLOOR(RAND() * 101);  -- 随机分数0到100
        SET v_score_name = CONCAT('Subject', FLOOR(RAND() * 10));  -- 随机科目名
        
        INSERT INTO score (scoreName, result, studentId)
        VALUES (v_score_name, v_random_score, v_student_id);
        
        SET v_counter = v_counter + 1;
    END WHILE;
    
    -- 恢复自增增量为默认值
    SET @@auto_increment_increment = 1;
END //
DELIMITER ;

调用

call InsertRandomData();

问题4：

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

DELIMITER //

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

//

DELIMITER ;

-- 查询
SELECT * FROM score where id=5;
-- 修改
UPDATE score SET scoreName = '数学' WHERE id = 5; 
-- 验证查询
SELECT * FROM score where id=5;

问题5：

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

DELIMITER //

CREATE PROCEDURE GetStudentInfoByName(IN studentName VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE student_id INT;
    DECLARE student_userName VARCHAR(255);
    DECLARE student_phone VARCHAR(20);
    DECLARE student_age INT;
    DECLARE student_sex ENUM('男', '女');
    DECLARE student_introduce TEXT;
    DECLARE score_id INT;
    DECLARE score_scoreName VARCHAR(255);
    DECLARE score_result FLOAT;
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.id,
            s.userName,
            s.phone,
            s.age,
            s.sex,
            s.introduce,
            sc.id,
            sc.scoreName,
            sc.result
        FROM student s
        LEFT JOIN score sc ON s.id = sc.studentId
        WHERE s.userName = studentName;
    -- 声明结束处理器，当游标完成后设置done变量为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 循环遍历游标中的所有记录
    read_loop: LOOP
        -- 从游标中取出数据
        FETCH cur INTO 
            student_id,
            student_userName,
            student_phone,
            student_age,
            student_sex,
            student_introduce,
            score_id,
            score_scoreName,
            score_result;
        -- 如果游标已经处理完所有记录，则退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 在这里可以对每一行数据进行处理，例如打印或进行其他操作
        SELECT 
            student_id,
            student_userName,
            student_phone,
            student_age,
            student_sex,
            student_introduce,
            score_id,
            score_scoreName,
            score_result;
    END LOOP;

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

DELIMITER ;

调用游标