MySQL——存储过程、触发器、游标

DDL+DML

-- 创建student表
CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  createDate DATE,
  userName VARCHAR(100),
  phone VARCHAR(20),
  age INT,
  sex CHAR(1),
  introduce TEXT
);


-- 创建score表
CREATE TABLE score (
  id INT PRIMARY KEY AUTO_INCREMENT,
  scoreName VARCHAR(100),
  result FLOAT,
  studentId INT,
  FOREIGN KEY (studentId) REFERENCES student(id)
);

-- 插入5条学生信息到student表
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01', '张三', '13800138000', 20, '男', '我是张三，喜欢学习。'),
('2023-04-02', '李四', '13800138001', 21, '女', '我是李四，擅长数学。'),
('2023-04-03', '王五', '13800138002', 19, '男', '我是王五，热爱体育。'),
('2023-04-04', '赵六', '13800138003', 20, '女', '我是赵六，喜欢音乐。'),
('2023-04-05', '孙七', '13800138004', 22, '男', '我是孙七，对科学感兴趣。');


-- 插入10条成绩信息到score表（每个学生2条）
INSERT INTO score (scoreName, result, studentId) VALUES
('语文', 85.5, 1), ('数学', 90.0, 1),
('语文', 78.0, 2), ('数学', 88.5, 2),
('语文', 82.5, 3), ('体育', 95.0, 3),
('音乐', 92.0, 4), ('数学', 81.0, 4),
('物理', 87.5, 5), ('化学', 91.0, 5);

-- 为studentId为3的学生插入数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', YOUR_SCORE_VALUE, 3);


-- 为studentId为3的学生插入英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', YOUR_SCORE_VALUE, 3);


-- 为studentId为4的学生插入数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', YOUR_SCORE_VALUE, 4);


-- 为studentId为4的学生插入英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', YOUR_SCORE_VALUE, 4);

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_score FLOAT;
    DECLARE v_random_score_name VARCHAR(100);
    DECLARE v_random_string VARCHAR(100);
    
    -- 设置自增ID从6开始
    ALTER TABLE student AUTO_INCREMENT = 6;
    
    -- 插入10条随机学生信息
    WHILE v_counter < 10 DO
        -- 生成随机用户名、电话等（这里简化为示例文本）
        SET v_random_string = CONCAT('User', FLOOR(RAND() * 1000));
        
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (
            NOW(),
            v_random_string,
            CONCAT('+1', FLOOR(RAND() * 8999999999) + 1000000000), -- 随机电话号码
            FLOOR(RAND() * 10 + 16), -- 随机年龄在16到25之间
            IF(RAND() > 0.5, 'M', 'F'), -- 随机性别
            'Random introduction text.' -- 简介文本
        );
        
        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
        SET v_random_score = FLOOR(RAND() * 100); -- 随机分数0到99之间
        SET v_random_score_name = CONCAT('Subject ', FLOOR(RAND() * 100)); -- 随机科目名
        
        INSERT INTO score (scoreName, result, studentId)
        VALUES (v_random_score_name, v_random_score, v_student_id);
        
        SET v_counter = v_counter + 1;
    END WHILE;
    
    -- Reset the AUTO_INCREMENT counter for future inserts (optional)
    ALTER TABLE student AUTO_INCREMENT = 1;
END //

DELIMITER ;
CALL InsertRandomData();

触发器 

 

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 ;
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (NOW(), 'Test Student', '1234567890', 20, 'M', 'Test Introduction');

INSERT INTO score (scoreName, result, studentId)
VALUES ('数学', 90.0, LAST_INSERT_ID());
UPDATE score SET scoreName = '数学' WHERE id = LAST_INSERT_ID();
SELECT * FROM score WHERE id = LAST_INSERT_ID();
DROP TRIGGER IF EXISTS tr_before_update_scoreName;

游标 

SELECT s.id AS student_id, 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 = '要查询的学生姓名'; -- 这里替换为实际的学生姓名
DELIMITER //

CREATE PROCEDURE GetStudentInfoByName(IN studentName VARCHAR(100))
BEGIN
    SELECT s.id AS student_id, 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 = studentName;
END //

DELIMITER ;
CALL GetStudentInfoByName('要查询的学生姓名'); -- 这里替换为实际的学生姓名