© 版权声明:本文为博主原创文章,转载请注明出处
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表数据