数据库触发器

-- DDL: 创建student表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);


-- DML: 插入5条学生信息
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张三', '13800138000', 20, '男', '来自北京的学生，热爱学习'),
('李四', '13900139000', 21, '女', '来自上海的学生，喜欢音乐'),
('王五', '13700137000', 19, '男', '来自深圳的学生，擅长编程'),
('赵六', '13600136000', 22, '女', '来自成都的学生，爱好旅行'),
('孙七', '13500135000', 20, '男', '来自杭州的学生，喜欢阅读');

-- DDL: 创建score表
CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scoreName VARCHAR(50) NOT NULL,
    result INT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE
);


-- DML: 插入10条成绩信息，每个学生ID对应2个科目的成绩

-- 假设科目名称和成绩是随意给定的
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90, 1),
('英语', 85, 1),
('数学', 88, 2),
('物理', 92, 2),
('语文', 87, 3),
('化学', 95, 3),
('英语', 89, 4),
('生物', 91, 4),
('数学', 78, 5),
('历史', 86, 5);




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

CREATE PROCEDURE InsertRandomStudentAndScoreData()
BEGIN
    -- 插入10条随机学生信息
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
        INSERT INTO student (userName, phone, age, sex, introduce)
        VALUES (
            CONCAT('学生', FLOOR(RAND() * 1000)), -- 生成随机用户名
            CONCAT('1', LPAD(FLOOR(RAND() * 100000000), 9, '0')), -- 生成随机电话号码
            FLOOR(RAND() * 30) + 1, -- 生成1到30之间的随机年龄
            IF(RAND() > 0.5, '男', '女'), -- 随机生成性别
            CONCAT('来自随机地点的学生，有随机爱好') -- 生成随机介绍
        );
        SET i = i + 1;
    END WHILE;

    -- 为每个学生插入10条随机成绩信息
    -- 假设每个学生有10个科目，并且我们为每个科目生成一个随机的成绩
    SELECT MAX(id) INTO i FROM student; -- 获取最后插入的学生的ID
    SET i = i - 5; -- 因为已存在5条学生信息，所以从ID 6开始
    WHILE i <= 15 DO -- 因为已经有5个学生，所以这里循环到15，总共插入10个学生的信息
        DECLARE j INT DEFAULT 1;
        WHILE j <= 10 DO -- 为每个学生插入10条成绩
            INSERT INTO score (scoreName, result, studentId)
            VALUES (
                CONCAT('科目', FLOOR(RAND() * 100)), -- 生成随机科目名称
                FLOOR(RAND() * 100) + 1, -- 生成1到100之间的随机成绩
                i -- 学生ID
            );
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;
CALL InsertRandomStudentAndScoreData();





DELIMITER //

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

DELIMITER ;

-- 尝试更新一条记录的scoreName为数学
UPDATE score SET scoreName = '数学' WHERE id = 1;


-- 查询更新后的scoreName
SELECT * FROM score WHERE id = 1;







DELIMITER //

CREATE PROCEDURE GetStudentWithScoresByName(IN studentName VARCHAR(50))
BEGIN
    -- 声明变量来存储从游标中获取的数据
    DECLARE done INT DEFAULT 0;
    DECLARE studentIdTemp INT;
    DECLARE scoreIdTemp INT;
    DECLARE scoreNameTemp VARCHAR(50);
    DECLARE resultTemp INT;
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT s.id, sc.id, sc.scoreName, sc.result
        FROM student s
        JOIN score sc ON s.id = sc.studentId
        WHERE s.userName = studentName;
    
    -- 声明继续处理（NOT FOUND）的条件
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开游标
    OPEN cur;
    
    -- 遍历游标
    read_loop: LOOP
        FETCH cur INTO studentIdTemp, scoreIdTemp, scoreNameTemp, resultTemp;
        
        -- 检查是否到达游标末尾
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 输出结果或进行其他操作
        SELECT 
            CONCAT('学生ID: ', studentIdTemp),
            CONCAT('成绩ID: ', scoreIdTemp),
            CONCAT('科目名称: ', scoreNameTemp),
            CONCAT('成绩: ', resultTemp);
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;
CALL GetStudentWithScoresByName('张三');
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
JOIN 
    score sc ON s.id = sc.studentId
WHERE 
    s.userName = '张三';