/* 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 */;