• 触发器-MySQL


    © 版权声明:本文为博主原创文章,转载请注明出处

    1.需求

      - 统计同一所学校的学生人数

    2.建表

      - 学生表(student)

    DROP TABLE IF EXISTS student;
    CREATE TABLE `student` (
    	`id` INT NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR(100),
    	`age` SMALLINT,
    	`school` VARCHAR(200) NOT NULL,
    	PRIMARY KEY (`id`)
    )ENGINE = INNODB DEFAULT CHARSET = UTF8
    

      - 学校人数表(school_num)

    DROP TABLE IF EXISTS school_num;
    CREATE TABLE `school_num` (
    	`school` VARCHAR(200) NOT NULL,
    	`num` INT DEFAULT 0,
    	PRIMARY KEY (`school`)
    )ENGINE = INNODB DEFAULT CHARSET = UTF8;
    

    3.触发器

      - add_school_num

    -- 新增时更新学校人数
    DROP TRIGGER IF EXISTS add_school_num;
    CREATE TRIGGER add_school_num
    AFTER INSERT ON `student` FOR EACH ROW
    BEGIN
    	IF (NEW.school IN (SELECT school FROM school_num)) THEN
    		UPDATE school_num SET num = num + 1 WHERE school = NEW.school;
    	ELSE
    		INSERT INTO school_num (school, num) VALUES (NEW.school, 1);
    	END IF;
    END;
    

      - delete_school_num

    -- 删除时更新学校人数
    DROP TRIGGER IF EXISTS delete_school_num;
    CREATE TRIGGER delete_school_num
    AFTER DELETE ON `student` FOR EACH ROW
    BEGIN
    	IF (OLD.school IN (SELECT school FROM school_num)) THEN
    		UPDATE school_num SET num = num -1 WHERE school = OLD.school;
    	END IF;
    END;
    

      - update_school_num

    -- 更新是更新学校人数
    DROP TRIGGER IF EXISTS update_school_num;
    CREATE TRIGGER update_school_num
    AFTER UPDATE ON `student` FOR EACH ROW
    BEGIN
    	IF (NEW.school IN (SELECT school FROM school_num)) THEN
    		UPDATE school_num SET num = num + 1 WHERE school = NEW.school;
    	ELSE
    		INSERT INTO school_num (school, num) VALUES (NEW.school, 1);
    	END IF;
    	IF (OLD.school IN (SELECT school FROM school_num)) THEN
    		UPDATE school_num SET num = num - 1 WHERE school = OLD.school;
    	END IF;
    END;
    

    4.测试

      4.1 插入数据

        4.1.1 执行SQL

    INSERT INTO `student` (name, age, school) VALUES ('张三', 22, '北京大学');
    INSERT INTO `student` (name, age, school) VALUES ('李四', 25, '北京大学');
    INSERT INTO `student` (name, age, school) VALUES ('王五', 21, '清华大学');
    INSERT INTO `student` (name, age, school) VALUES ('赵六', 19, '北邮大学');
    

        4.1.2 student表数据

        4.1.3 school_num表数据

      4.2 删除数据

        4.2.1 执行SQL

    DELETE FROM `student` WHERE name = '赵六';
    

        4.2.2 student表数据

        4.2.3 school_num表数据

      4.3 更新数据(更新为已存在的学校)

        4.3.1 执行SQL

    UPDATE `student` SET school = '北邮大学' WHERE name = '王五';

        4.3.2 student表数据

        4.3.3 school_num表数据

      4.4 更新数据(更新为不存在的学校)

        4.4.1 执行SQL

    UPDATE `student` SET school = '传媒大学' WHERE name = '李四'

        4.4.2 student表数据

        4.4.3 school_num表数据

  • 相关阅读:
    《将Pell方程进行到底》 回复
    《大家来讨论一下,我的文章被撤的概率是多少?》 回复
    Game of Taking Stones【威佐夫博弈】【高精度】
    《机器学习》第二次作业——第四章学习记录和心得
    《机器学习》第一次作业——第一至三章学习记录和心得
    DB tunning 1 索引调优
    SpringBoot 学习笔记1
    Exchanging Gifts 【拓扑】
    SpringBoot 学习笔记2
    Leetcode 刷题记录
  • 原文地址:https://www.cnblogs.com/jinjiyese153/p/6897878.html
Copyright © 2020-2023  润新知