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

  1. 我对数据库存储过程/触发器的理解
  • 存储过程: 和编程里面的函数类似,只需要掉用,就会执行预先设定好的过程
  • 触发器: 可以在每次sql操作的前后执行动作,钩子函数.(增强sql语句的功能)

  1. 简单练习

    1. 创建数据库
      1
      2
      CREATE DATABASE `foodfactory` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    2. 创建表 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      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. 多表查询

    1
    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

  3. 修改语句

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

  4. 存储过程

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

  5. 触发器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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 ;