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

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);

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;

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 ;

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;

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 ;