数据库: 存储过程/触发器

1. 我对数据库存储过程/触发器的理解:

1.1 存储过程: 和编程里面的函数类似,只需要掉用,就会执行预先设定好的过程

1.2 触发器: 可以在每次sql操作的前后执行动作,钩子函数.(增强sql语句的功能)

2. 简单练习

0. 创建数据库

   CREATE DATABASE `foodfactory` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
   

1. 创建表

   CREATE TABLE `food` (
     `id` int NOT NULL AUTO_INCREMENT,
     `fname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
     `stock` varchar(255) DEFAULT NULL,
     `date` datetime DEFAULT NULL,
     PRIMARY KEY (`id`)
   );
   CREATE TABLE `provider` (
     `id` int NOT NULL AUTO_INCREMENT,
     `pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
     `addr` varchar(255) DEFAULT NULL,
     `tel` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ;
   CREATE TABLE `f2p_fk` (
     `id` int NOT NULL AUTO_INCREMENT,
     `f_id` int DEFAULT NULL,
     `p_id` int DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `f_id` (`f_id`),
     KEY `p_id` (`p_id`),
     CONSTRAINT `f_id` FOREIGN KEY (`f_id`) REFERENCES `food` (`id`) ON DELETE SET NULL,
     CONSTRAINT `p_id` FOREIGN KEY (`p_id`) REFERENCES `provider` (`id`) ON DELETE SET NULL
   ) ;
   
   
   

2. 多表查询

   select * from food ,provider ,f2p_fk  WHERE f2p_fk.id = 1 AND f2p_fk.p_id = provider.id AND f2p_fk.f_id = food.id

4. 修改语句

   UPDATE foodfactory.food SET stock = '200' WHERE id = 3;

4. 存储过程

   CREATE PROCEDURE reStock(IN f_id INTEGER) BEGIN　 UPDATE foodfactory.food SET stock = '0' WHERE id = f_id; END^

4. 触发器

delimiter ^
CREATE TRIGGER upd_check BEFORE UPDATE ON food
FOR EACH ROW
 BEGIN
　　IF NEW.stock < 0 THEN
　　SET NEW.stock = 0;
　END IF;
    END^
delimiter ;