实验四:进销存管理系统(2)
- 编写触发器实现:当插入或修改商品名称时,修改名称拼音缩写
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `updatepysx`$$
CREATE
TRIGGER `updatepysx` BEFORE INSERT ON `spb`
FOR EACH ROW BEGIN
SET new.spmcsx=qxmjp(new.xsxm);
END;
$$
DELIMITER ;
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `updatepysx1`$$
CREATE
TRIGGER `updatepysx1` BEFORE UPDATE ON `spb`
FOR EACH ROW BEGIN
IF (old.xsxm != new.xsxm) THEN
SET new.xmjp=qxmjp(new.xsxm);
END IF;
END;
$$
DELIMITER ;
CREATE DEFINER=`root`@`localhost` FUNCTION `qxmjp`(p_name VARCHAR(100) ) RETURNS VARCHAR(100) CHARSET utf8
BEGIN
DECLARE V_COMPARE VARCHAR(255);
DECLARE V_RETURN VARCHAR(255);
DECLARE I INT;
SET I = 1;
SET V_RETURN = '';
WHILE I < LENGTH(P_NAME) DO
SET V_COMPARE = SUBSTR(P_NAME, I, 1);
IF (V_COMPARE != '') THEN
SET V_RETURN = CONCAT(V_RETURN, qpysx(V_COMPARE));
END IF;
SET I = I + 1;
END WHILE;
IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
SET V_RETURN = P_NAME;
END IF;
RETURN V_RETURN;
END$$
DELIMITER ;
DELIMITER $$
USE `pjsjk`$$
DROP FUNCTION IF EXISTS `qpysx`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `qpysx`(p_name VARCHAR(10)) RETURNS VARCHAR(10) CHARSET utf8
BEGIN
DECLARE V_RETURN VARCHAR(255);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
IF v_return IS NOT NULL THEN
RETURN V_RETURN;
ELSE
RETURN p_name;
END IF ;
END$$
DELIMITER ;
- 编写触发器实现:当插入或删除进货明细表时修改库存表的库存数量。
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `updatekcsl`$$
CREATE
TRIGGER `updatekcsl` BEFORE INSERT ON `jhmxb`
FOR EACH ROW BEGIN
Udpate kcb set kcsl = kcsl + new.jhsl where spid=new.spid;
END;
$$
三、缩写存储过程插入进货记录总表和明细表
/*先插入进货总表,取总表的ID,@@IDENTITY; 再依次插入明细表,做成事务,(start transaction;)commit;rollback;*/
DELIMITER $$
/*插入进货表和进货明细表,供应商ID号(gysid)、进货日期(jhrq)、进货金额(jhje)、操作员ID号(czyid)、备注(jhbz)*/
/*进货ID号(jhid)、商品ID号(spid)、进货单价(jhdj)、进货数量(jhsl)*/
CREATE PROCEDURE `jhjlcp`(gysid INT,jhje NUMERIC(18,2),czyid INT,jhbz VARCHAR(100),jhmx VARCHAR(8000))
BEGIN
/* 插入总表,并且将明细表解析*/
DECLARE @strls varchar(8000);
DECLARE @jhid INT;
DECLARE @spid INT;
DECLARE @jhdj NUMERIC(18,2);
DECLARE @jhsl NUMERIC(18,2);
SET autocommit = 0;
/** 标记是否出错 */
DECLARE t_error int default 0;
/** 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
DECLARE continue handler for sqlexception set t_error=1;
START TRANSACTION;
insert into jhzb(gysid,jhrq,jhje,czyid,jhbz)values(gysid,curdate(),jhje,czyid,jhbz)
SET @jhid = @@identity;;
WHILE (jhmx>'') DO
SET @strls = MID(jhmx,1,LOCATE(';',jhmx)-1);
SET @spid=CAST(MID(@strls,1,LOCATE(',',@strls)-1) AS SIGNED);
SET @strls = MID(@strls,LOCATE(',',@strls)+1);
SET @jhdj=CAST(MID(@strls,1,LOCATE(',',@strls)-1) AS DECIMAL);
SET @jhje=CAST(MID(@strls,LOCATE(',',@strls)+1) AS DECIMAL);
INSERT INTO jhmxb(jhid,spid,jhdj,jhsl)values(@jhid,@spid,@jhdj,@jhsl);
SET jhmx = MID(jhmx,LOCATE(';',jhmx)+1);
END WHILE;
IF t_error=1 THEN
ROLLBACK; -- 事务回滚
ELSE
COMMIT; -- 事务提交
END IF;
END */$$
DELIMITER ;
四、备份数据库的结构和数据,导出SQL文件名为:mypos2.sql,将mypos2.sql上传至实验报告四。
参考代码
/*
SQLyog Ultimate v12.08 (32 bit)
MySQL - 8.0.28 : Database - mypos
*********************************************************************
*/
/*!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*/`mypos` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mypos`;
/*Table structure for table `ckb` */
DROP TABLE IF EXISTS `ckb`;
CREATE TABLE `ckb` (
`ckid` int NOT NULL AUTO_INCREMENT,
`ckmc` varchar(20) DEFAULT NULL,
`ckdz` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ckid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `ckb` */
insert into `ckb`(`ckid`,`ckmc`,`ckdz`) values (1,'1号仓库','洛阳市武汉路39号');
/*Table structure for table `czyb` */
DROP TABLE IF EXISTS `czyb`;
CREATE TABLE `czyb` (
`czyid` int NOT NULL AUTO_INCREMENT,
`czydm` varchar(20) DEFAULT NULL,
`czykl` varchar(20) DEFAULT NULL,
`czylb` varchar(20) DEFAULT NULL,
PRIMARY KEY (`czyid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `czyb` */
insert into `czyb`(`czyid`,`czydm`,`czykl`,`czylb`) values (1,'000','000','0'),(2,'001','001','1');
/*Table structure for table `ghsb` */
DROP TABLE IF EXISTS `ghsb`;
CREATE TABLE `ghsb` (
`gysid` int NOT NULL AUTO_INCREMENT,
`gysmc` varchar(20) DEFAULT NULL,
`spmcsx` varchar(20) DEFAULT NULL,
`gysdz` varchar(20) DEFAULT NULL,
`gyslxfs` varchar(20) DEFAULT NULL,
`gysbz` varchar(20) DEFAULT NULL,
PRIMARY KEY (`gysid`),
KEY `index_gysmc` (`gysmc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `ghsb` */
/*Table structure for table `jhmxb` */
DROP TABLE IF EXISTS `jhmxb`;
CREATE TABLE `jhmxb` (
`jhmxid` int NOT NULL AUTO_INCREMENT,
`jhid` int DEFAULT NULL,
`spid` int DEFAULT NULL,
`jhdj` double DEFAULT NULL,
`jhsl` int DEFAULT NULL,
PRIMARY KEY (`jhmxid`),
KEY `jhid` (`jhid`),
KEY `spid` (`spid`),
CONSTRAINT `jhmxb_ibfk_1` FOREIGN KEY (`jhid`) REFERENCES `jhzb` (`jhid`),
CONSTRAINT `jhmxb_ibfk_2` FOREIGN KEY (`spid`) REFERENCES `spb` (`spid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `jhmxb` */
/*Table structure for table `jhzb` */
DROP TABLE IF EXISTS `jhzb`;
CREATE TABLE `jhzb` (
`jhid` int NOT NULL AUTO_INCREMENT,
`gysid` int DEFAULT NULL,
`jhrq` datetime DEFAULT NULL,
`jhje` double DEFAULT NULL,
`czyid` int DEFAULT NULL,
`jhbz` varchar(20) DEFAULT NULL,
PRIMARY KEY (`jhid`),
KEY `gysid` (`gysid`),
KEY `czyid` (`czyid`),
CONSTRAINT `jhzb_ibfk_1` FOREIGN KEY (`gysid`) REFERENCES `ghsb` (`gysid`),
CONSTRAINT `jhzb_ibfk_2` FOREIGN KEY (`czyid`) REFERENCES `czyb` (`czyid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `jhzb` */
/*Table structure for table `kcb` */
DROP TABLE IF EXISTS `kcb`;
CREATE TABLE `kcb` (
`kcid` int NOT NULL AUTO_INCREMENT,
`ckid` int DEFAULT NULL,
`spid` int DEFAULT NULL,
`jhpjdj` double DEFAULT NULL,
`xspjdj` double DEFAULT NULL,
`kcsl` int DEFAULT NULL,
`zgkcl` int DEFAULT NULL,
`zdkcl` int DEFAULT NULL,
PRIMARY KEY (`kcid`),
KEY `ckid` (`ckid`),
KEY `spid` (`spid`),
CONSTRAINT `kcb_ibfk_1` FOREIGN KEY (`ckid`) REFERENCES `ckb` (`ckid`),
CONSTRAINT `kcb_ibfk_2` FOREIGN KEY (`spid`) REFERENCES `spb` (`spid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `kcb` */
insert into `kcb`(`kcid`,`ckid`,`spid`,`jhpjdj`,`xspjdj`,`kcsl`,`zgkcl`,`zdkcl`) values (1,1,1,0,0,0,9999,10),(2,1,2,0,0,0,9999,10),(3,1,3,0,0,0,9999,10),(4,1,4,0,0,0,9999,10),(5,1,5,0,0,0,9999,10),(6,1,6,0,0,0,9999,10);
/*Table structure for table `khb` */
DROP TABLE IF EXISTS `khb`;
CREATE TABLE `khb` (
`khid` int NOT NULL AUTO_INCREMENT,
`khmc` varchar(20) DEFAULT NULL,
`khmcsx` varchar(20) DEFAULT NULL,
`khdz` varchar(20) DEFAULT NULL,
`khlxfs` varchar(20) DEFAULT NULL,
`khbz` varchar(20) DEFAULT NULL,
PRIMARY KEY (`khid`),
KEY `index_khmc` (`khmc`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `khb` */
insert into `khb`(`khid`,`khmc`,`khmcsx`,`khdz`,`khlxfs`,`khbz`) values (1,'洛阳前进超市',NULL,'洛阳市开元大道40号','13703790379',NULL);
/*Table structure for table `spb` */
DROP TABLE IF EXISTS `spb`;
CREATE TABLE `spb` (
`spid` int NOT NULL AUTO_INCREMENT,
`spmc` varchar(20) DEFAULT NULL,
`spmcsx` varchar(20) DEFAULT NULL,
`sptm` varchar(20) DEFAULT NULL,
`spdw` varchar(20) DEFAULT NULL,
`spbz` varchar(20) DEFAULT NULL,
PRIMARY KEY (`spid`),
KEY `index_spmc` (`spmc`),
KEY `index_spmcsx` (`spmcsx`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `spb` */
insert into `spb`(`spid`,`spmc`,`spmcsx`,`sptm`,`spdw`,`spbz`) values (1,'可口可乐','kkkl','101','瓶',NULL),(2,'百事可乐','bskl','102','瓶',NULL),(3,'伊利牛奶','ylnn','103','盒',NULL),(4,'农夫山泉','nfsq','104','瓶',NULL),(5,'涪陵榨菜','flzc','105','袋',NULL),(6,'青岛啤酒','qdpj','106','听',NULL);
/*Table structure for table `xsmxb` */
DROP TABLE IF EXISTS `xsmxb`;
CREATE TABLE `xsmxb` (
`xsmxid` int NOT NULL AUTO_INCREMENT,
`xsid` int DEFAULT NULL,
`spid` int DEFAULT NULL,
`xsdj` double DEFAULT NULL,
`xssl` int DEFAULT NULL,
PRIMARY KEY (`xsmxid`),
KEY `spid` (`spid`),
KEY `xsid` (`xsid`),
CONSTRAINT `xsmxb_ibfk_1` FOREIGN KEY (`spid`) REFERENCES `spb` (`spid`),
CONSTRAINT `xsmxb_ibfk_2` FOREIGN KEY (`xsid`) REFERENCES `xszb` (`xsid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `xsmxb` */
/*Table structure for table `xszb` */
DROP TABLE IF EXISTS `xszb`;
CREATE TABLE `xszb` (
`xsid` int NOT NULL AUTO_INCREMENT,
`khid` int DEFAULT NULL,
`xsrq` datetime DEFAULT NULL,
`xsje` double DEFAULT NULL,
`czyid` int DEFAULT NULL,
`xsbz` varchar(20) DEFAULT NULL,
PRIMARY KEY (`xsid`),
KEY `khid` (`khid`),
KEY `czyid` (`czyid`),
CONSTRAINT `xszb_ibfk_1` FOREIGN KEY (`khid`) REFERENCES `khb` (`khid`),
CONSTRAINT `xszb_ibfk_2` FOREIGN KEY (`czyid`) REFERENCES `czyb` (`czyid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `xszb` */
/* Trigger structure for table `jhmxb` */
DELIMITER $$
/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `updatekcsl` */$$
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `updatekcsl` BEFORE INSERT ON `jhmxb` FOR EACH ROW BEGIN
update kcb set kcsl = kcsl + new.jhsl where spid=new.spid;
END */$$
DELIMITER ;
/* Trigger structure for table `spb` */
DELIMITER $$
/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `updatepysx` */$$
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `updatepysx` BEFORE INSERT ON `spb` FOR EACH ROW BEGIN
SET new.spmcsx=qxmjp(new.spmc);
END */$$
DELIMITER ;
/* Trigger structure for table `spb` */
DELIMITER $$
/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `updatepysx1` */$$
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `updatepysx1` BEFORE UPDATE ON `spb` FOR EACH ROW BEGIN
IF (old.spmc!= new.spmc) THEN
SET new.spmc=qxmjp(new.spmc);
END IF;
END */$$
DELIMITER ;
/* Function structure for function `qpysx` */
/*!50003 DROP FUNCTION IF EXISTS `qpysx` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `qpysx`(p_name VARCHAR(10)) RETURNS varchar(10) CHARSET utf8mb3
BEGIN
DECLARE V_RETURN VARCHAR(255);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
IF v_return IS NOT NULL THEN
RETURN V_RETURN;
ELSE
RETURN p_name;
END IF ;
END */$$
DELIMITER ;
/* Function structure for function `qxmjp` */
/*!50003 DROP FUNCTION IF EXISTS `qxmjp` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `qxmjp`(p_name VARCHAR(100) ) RETURNS varchar(100) CHARSET utf8mb3
BEGIN
DECLARE V_COMPARE VARCHAR(255);
DECLARE V_RETURN VARCHAR(255);
DECLARE I INT;
SET I = 1;
SET V_RETURN = '';
WHILE I < LENGTH(P_NAME) DO
SET V_COMPARE = SUBSTR(P_NAME, I, 1);
IF (V_COMPARE != '') THEN
SET V_RETURN = CONCAT(V_RETURN, qpysx(V_COMPARE));
END IF;
SET I = I + 1;
END WHILE;
IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
SET V_RETURN = P_NAME;
END IF;
RETURN V_RETURN;
END */$$
DELIMITER ;
/* Procedure structure for procedure `jhjlcp` */
/*!50003 DROP PROCEDURE IF EXISTS `jhjlcp` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `jhjlcp`(gysid INT,jhje NUMERIC(18,2),czyid INT,jhbz VARCHAR(100),jhmx VARCHAR(8000))
BEGIN
DECLARE strls varchar(8000);
DECLARE jhid INT(10);
DECLARE spid INT(10);
DECLARE jhdj NUMERIC(18,2);
DECLARE jhsl NUMERIC(18,2);
SET autocommit = 0;
/* 标记是否出错 */
/*DECLARE t_error int default 0; */
/* 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
insert into jhzb(gysid,jhrq,jhje,czyid,jhbz)values(gysid,curdate(),jhje,czyid,jhbz);
SET jhid = @@identity;
WHILE (jhmx>'') DO
SET strls = MID(jhmx,1,LOCATE(';',jhmx)-1);
SET spid=CAST(MID(strls,1,LOCATE(',',strls)-1) AS SIGNED);
SET strls = MID(strls,LOCATE(',',strls)+1);
SET jhdj=CAST(MID(strls,1,LOCATE(',',strls)-1) AS DECIMAL);
SET jhje=CAST(MID(strls,LOCATE(',',strls)+1) AS DECIMAL);
INSERT INTO jhmxb(jhid,spid,jhdj,jhsl)values(jhid,spid,jhdj,jhsl);
SET jhmx = MID(jhmx,LOCATE(';',jhmx)+1);
END WHILE;
END */$$
DELIMITER ;
/*Table structure for table `view_jhb` */
DROP TABLE IF EXISTS `view_jhb`;
/*!50001 DROP VIEW IF EXISTS `view_jhb` */;
/*!50001 DROP TABLE IF EXISTS `view_jhb` */;
/*!50001 CREATE TABLE `view_jhb`(
`jhid` int ,
`gysid` int ,
`jhrq` datetime ,
`jhje` double ,
`czyid` int ,
`jhbz` varchar(20) ,
`jhdj` double ,
`jhmxid` int ,
`jhsl` int ,
`spid` int ,
`spbz` varchar(20) ,
`spdw` varchar(20) ,
`spmc` varchar(20) ,
`sptm` varchar(20) ,
`gysbz` varchar(20) ,
`gysdz` varchar(20) ,
`gyslxfs` varchar(20) ,
`gysmc` varchar(20) ,
`spmcsx` varchar(20)
)*/;
/*Table structure for table `view_kc` */
DROP TABLE IF EXISTS `view_kc`;
/*!50001 DROP VIEW IF EXISTS `view_kc` */;
/*!50001 DROP TABLE IF EXISTS `view_kc` */;
/*!50001 CREATE TABLE `view_kc`(
`kcid` int ,
`ckid` int ,
`spid` int ,
`jhpjdj` double ,
`xspjdj` double ,
`kcsl` int ,
`zgkcl` int ,
`zdkcl` int ,
`ckdz` varchar(20) ,
`ckmc` varchar(20) ,
`spbz` varchar(20) ,
`spdw` varchar(20) ,
`spmc` varchar(20) ,
`spmcsx` varchar(20) ,
`sptm` varchar(20)
)*/;
/*Table structure for table `view_xsb` */
DROP TABLE IF EXISTS `view_xsb`;
/*!50001 DROP VIEW IF EXISTS `view_xsb` */;
/*!50001 DROP TABLE IF EXISTS `view_xsb` */;
/*!50001 CREATE TABLE `view_xsb`(
`xsid` int ,
`khid` int ,
`xsrq` datetime ,
`xsje` double ,
`czyid` int ,
`xsbz` varchar(20) ,
`xsmxid` int ,
`spid` int ,
`xsdj` double ,
`xssl` int ,
`khbz` varchar(20) ,
`khdz` varchar(20) ,
`khlxfs` varchar(20) ,
`khmc` varchar(20) ,
`khmcsx` varchar(20) ,
`spbz` varchar(20) ,
`spdw` varchar(20) ,
`spmc` varchar(20) ,
`spmcsx` varchar(20) ,
`sptm` varchar(20)
)*/;
/*View structure for view view_jhb */
/*!50001 DROP TABLE IF EXISTS `view_jhb` */;
/*!50001 DROP VIEW IF EXISTS `view_jhb` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_jhb` AS select `jhzb`.`jhid` AS `jhid`,`jhzb`.`gysid` AS `gysid`,`jhzb`.`jhrq` AS `jhrq`,`jhzb`.`jhje` AS `jhje`,`jhzb`.`czyid` AS `czyid`,`jhzb`.`jhbz` AS `jhbz`,`jhmxb`.`jhdj` AS `jhdj`,`jhmxb`.`jhmxid` AS `jhmxid`,`jhmxb`.`jhsl` AS `jhsl`,`jhmxb`.`spid` AS `spid`,`spb`.`spbz` AS `spbz`,`spb`.`spdw` AS `spdw`,`spb`.`spmc` AS `spmc`,`spb`.`sptm` AS `sptm`,`ghsb`.`gysbz` AS `gysbz`,`ghsb`.`gysdz` AS `gysdz`,`ghsb`.`gyslxfs` AS `gyslxfs`,`ghsb`.`gysmc` AS `gysmc`,`ghsb`.`spmcsx` AS `spmcsx` from (((`jhzb` join `jhmxb`) join `ghsb`) join `spb`) where ((`jhzb`.`jhid` = `jhmxb`.`jhid`) and (`jhzb`.`gysid` = `ghsb`.`gysid`) and (`spb`.`spid` = `jhmxb`.`spid`)) */;
/*View structure for view view_kc */
/*!50001 DROP TABLE IF EXISTS `view_kc` */;
/*!50001 DROP VIEW IF EXISTS `view_kc` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_kc` AS select `kcb`.`kcid` AS `kcid`,`kcb`.`ckid` AS `ckid`,`kcb`.`spid` AS `spid`,`kcb`.`jhpjdj` AS `jhpjdj`,`kcb`.`xspjdj` AS `xspjdj`,`kcb`.`kcsl` AS `kcsl`,`kcb`.`zgkcl` AS `zgkcl`,`kcb`.`zdkcl` AS `zdkcl`,`ckb`.`ckdz` AS `ckdz`,`ckb`.`ckmc` AS `ckmc`,`spb`.`spbz` AS `spbz`,`spb`.`spdw` AS `spdw`,`spb`.`spmc` AS `spmc`,`spb`.`spmcsx` AS `spmcsx`,`spb`.`sptm` AS `sptm` from ((`kcb` join `spb`) join `ckb`) where ((`kcb`.`ckid` = `ckb`.`ckid`) and (`kcb`.`spid` = `spb`.`spid`)) */;
/*View structure for view view_xsb */
/*!50001 DROP TABLE IF EXISTS `view_xsb` */;
/*!50001 DROP VIEW IF EXISTS `view_xsb` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_xsb` AS select `xszb`.`xsid` AS `xsid`,`xszb`.`khid` AS `khid`,`xszb`.`xsrq` AS `xsrq`,`xszb`.`xsje` AS `xsje`,`xszb`.`czyid` AS `czyid`,`xszb`.`xsbz` AS `xsbz`,`xsmxb`.`xsmxid` AS `xsmxid`,`xsmxb`.`spid` AS `spid`,`xsmxb`.`xsdj` AS `xsdj`,`xsmxb`.`xssl` AS `xssl`,`khb`.`khbz` AS `khbz`,`khb`.`khdz` AS `khdz`,`khb`.`khlxfs` AS `khlxfs`,`khb`.`khmc` AS `khmc`,`khb`.`khmcsx` AS `khmcsx`,`spb`.`spbz` AS `spbz`,`spb`.`spdw` AS `spdw`,`spb`.`spmc` AS `spmc`,`spb`.`spmcsx` AS `spmcsx`,`spb`.`sptm` AS `sptm` from (((`xszb` join `xsmxb`) join `khb`) join `spb`) where ((`xszb`.`xsid` = `xsmxb`.`xsid`) and (`xszb`.`khid` = `khb`.`khid`) and (`xsmxb`.`spid` = `spb`.`spid`)) */;
/*!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 */;