mysql 存储过程和触发器综合例题-白红宇

mysql 存储过程和触发器综合例题

阅读量：4314 次

发布时间：2019-06-06

本文共 6472 字，大约阅读时间需要 21 分钟。

/*SQLyog Ultimate v12.09 (64 bit)MySQL - 5.7.20-log : Database - lianxi**********************************************************************//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/`lianxi` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `lianxi`;/*Table structure for table `rizhi` */DROP TABLE IF EXISTS `rizhi`;CREATE TABLE `rizhi` (  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志编号',  `biao` varchar(100) DEFAULT NULL COMMENT '修改了那个表',  `stime` datetime DEFAULT NULL COMMENT '时间',  `leixing` varchar(100) DEFAULT NULL COMMENT '执行了什么',  `new` varchar(100) DEFAULT NULL COMMENT '修改之前的存款',  `old` varchar(100) DEFAULT NULL COMMENT '修改之后的存款',  `yinhangid` varchar(100) DEFAULT NULL COMMENT '银行ID',  `username` varchar(100) DEFAULT NULL COMMENT '谁使用了',  PRIMARY KEY (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;/*Data for the table `rizhi` */insert  into `rizhi`(`sid`,`biao`,`stime`,`leixing`,`new`,`old`,`yinhangid`,`username`) values (1,'zhanghu','2018-01-22 14:49:02','insert','1002',NULL,NULL,'root@'),(2,'zhanghu','2018-01-22 14:49:24','insert','1003',NULL,NULL,'root@'),(3,'zhanghu','2018-01-22 15:08:41','insert','1004',NULL,NULL,'root@'),(4,'zhanghu','2018-01-22 15:53:10','insert',NULL,NULL,'1005','root@'),(5,'zhanghu','2018-01-22 15:55:21','insert',NULL,NULL,'新增用户1006','root@'),(6,'zhanghu','2018-01-22 16:05:27','insert','1458','1335','1000','root@'),(7,'zhanghu','2018-01-22 16:05:28','insert','1581','1458','1000','root@'),(8,'zhanghu','2018-01-22 16:05:28','insert','1704','1581','1000','root@'),(9,'zhanghu','2018-01-22 16:05:28','insert','1827','1704','1000','root@');/*Table structure for table `zhanghu` */DROP TABLE IF EXISTS `zhanghu`;CREATE TABLE `zhanghu` (  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '账户编号',  `sname` varchar(100) DEFAULT NULL COMMENT '用户名字',  `yhid` int(11) DEFAULT NULL COMMENT '用户ID',  `mima` int(11) DEFAULT NULL COMMENT '用户密码',  `dianhua` int(11) DEFAULT NULL COMMENT '电话',  `yue` int(11) DEFAULT NULL COMMENT '用户余额',  PRIMARY KEY (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;/*Data for the table `zhanghu` */insert  into `zhanghu`(`sid`,`sname`,`yhid`,`mima`,`dianhua`,`yue`) values (13,'11111aa',1000,123,123,1827),(14,'111111aa',1001,123,123,13),(15,'1111111aa',1002,123,123,13),(16,'111111561aa',1003,123,123,13),(17,'1121',1004,123,123,13),(18,'111111561aa1',1005,123,123,13),(19,'1111115611aa1',1006,123,123,13);/* Trigger structure for table `zhanghu` */DELIMITER $$/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `insert` */$$/*!50003 CREATE */ /*!50017 DEFINER = 'skip-grants user'@'skip-grants host' */ /*!50003 TRIGGER `insert` BEFORE INSERT ON `zhanghu` FOR EACH ROW     BEGIN    insert into rizhi(`biao`,`stime`,`leixing`,`yinhangid`,`username`)    values('zhanghu',sysdate(),'insert',concat('新增用户',new.`yhid`),user());    END */$$DELIMITER ;/* Trigger structure for table `zhanghu` */DELIMITER $$/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `cunqu` */$$/*!50003 CREATE */ /*!50017 DEFINER = 'skip-grants user'@'skip-grants host' */ /*!50003 TRIGGER `cunqu` AFTER UPDATE ON `zhanghu` FOR EACH ROW     BEGIN    INSERT INTO rizhi(`biao`,`stime`,`leixing`,`new`,`old`,`yinhangid`,`username`)    VALUES('zhanghu',SYSDATE(),'insert',new.`yue`,old.`yue`,CONCAT(new.`yhid`),USER());    END */$$DELIMITER ;/* Procedure structure for procedure `chongzhi` *//*!50003 DROP PROCEDURE IF EXISTS  `chongzhi` */;DELIMITER $$/*!50003 CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `chongzhi`(in id int,in mima int ,in jine int)BEGIN    DECLARE cs1 int;    DECLARE cs2 INT;    /*判断一下银行ID存在不*/    if exists(select * from zhanghu where id=yhid)then    select z.mima into cs1 from zhanghu z where id=z.yhid;    /*判断密码正确不*/    if cs1=mima then      update zhanghu set yue=jine+yue where id=yhid;      select yue into cs2 from zhanghu where  id=yhid;      select concat('充值成功,余额为：',cs2);    else      select '密码错误';    end if;    else    select '没有此用户，请先创建账户';    end if;    END */$$DELIMITER ;/* Procedure structure for procedure `chuangjian` *//*!50003 DROP PROCEDURE IF EXISTS  `chuangjian` */;DELIMITER $$/*!50003 CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `chuangjian`(in `kname` varchar(100),in `mima` int,in `dianhua` int,in `yue` int)BEGIN        DECLARE sc int;        /*先判断账户是否重复*/        if EXISTS(select sname from zhanghu z where kname=z.sname) then        select '此用户以存在';                 else        if exists(SELECT yhid FROM zhanghu z ORDER BY yhid DESC LIMIT 1 ) then        select yhid into sc from zhanghu z order by yhid desc limit 1;        set sc=sc+1;        INSERT INTO `zhanghu`(`sname`,`yhid`,`mima`,`dianhua`,`yue`)VALUES(kname,sc,mima,dianhua,yue);        select concat('银行ID为：',sc);        else        /*定制初始*/        insert into `zhanghu`(`sname`,`yhid`,`mima`,`dianhua`,`yue`)values(kname,1000,mima,dianhua,yue);        SELECT CONCAT('银行ID为：',1000);        END IF;        end if;    END */$$DELIMITER ;/* Procedure structure for procedure `quqian` *//*!50003 DROP PROCEDURE IF EXISTS  `quqian` */;DELIMITER $$/*!50003 CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `quqian`(IN id INT,IN mima INT ,IN jine INT)BEGIN    DECLARE cs1 INT;    DECLARE cs2 INT;    DECLARE cs3 INT;    DECLARE cs4 INT;    /*先判断银行ID存在不*/    IF EXISTS(SELECT * FROM zhanghu WHERE id=yhid)THEN    SELECT z.mima INTO cs1 FROM zhanghu z WHERE id=z.yhid;    /*判断密码正确不*/    IF cs1=mima THEN      /*取钱金额大小*/      if jine>1 then      SELECT c.yue INTO cs3 FROM zhanghu c WHERE id=c.yhid;      IF cs3>jine THEN      UPDATE zhanghu SET yue=yue-jine WHERE id=yhid;      SELECT yue INTO cs4 FROM zhanghu WHERE  id=yhid;      SELECT CONCAT('充值成功,余额为：',cs4);      ELSE      SELECT '余额不足';      END IF;      else      select '取钱金额过少，最低2块';      end if;    ELSE      SELECT '密码错误';    END IF;    ELSE    SELECT '没有此用户，请先创建账户';    END IF;    END */$$DELIMITER ;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

转载于:https://www.cnblogs.com/wangdong123/p/8329758.html

你可能感兴趣的文章

java 多线程 Future callable

查看>>

字符串操作练习：星座、凯撒密码、99乘法表

查看>>

Java实现字符串转换十六进制MD5值

查看>>

MySQL数据库8（十七）数据库的备份还原

查看>>

tensorflow 梯度下降以及summary

查看>>