MySQL常见业务系统实现

进销存业务一: 商品信息维护的数据库端

一、基础表:
1、商品信息表
CREATE TABLE `spxxb` (
  `spid` int(11) NOT NULL AUTO_INCREMENT,
  `spmc` varchar(50) DEFAULT NULL,
  `sptm` varchar(20) DEFAULT NULL,
  `jldw` varchar(10) DEFAULT NULL,
  `spzt` int(11) DEFAULT '0',
  `lsj` decimal(9,2) DEFAULT NULL,
  `mcsx` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`spid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2、商品库存表
CREATE TABLE `spkcb` (
  `kcid` int(11) NOT NULL AUTO_INCREMENT,
  `spid` int(11) DEFAULT NULL,
  `kcsl` int(11) DEFAULT '0',
  PRIMARY KEY (`kcid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

二、自定义函数

DELIMITER $$
CREATE FUNCTION `PysxCx`(zw varchar(50)) RETURNS varchar(10) CHARSET utf8mb4
BEGIN
  set @pysx='';
  set @jp='';
  set @l=char_length(zw);
  set @i=1;
  while (@i<=@l) do
    select jp into @jp from hzpyb where hz=substring(zw,@i,1);
    set @pysx=concat(@pysx,@jp);
    set @i=@i+1;
  end while;
  return @pysx;
END */$$
DELIMITER ;

三、存储过程

DELIMITER $$

CREATE PROCEDURE `SpxxWh`(whfs int,spid int,spmc varchar(50),sptm varchar(20),jldw varchar(10),spzt smallint,lsj numeric(9,2))
BEGIN
  if whfs=0 then
    insert into spxxb (spmc,sptm,jldw,lsj) value (spmc,sptm,jldw,lsj);
  elseif whfs=1 then
    update spxxb set `spxxb`.`spmc`=spmc,`spxxb`.`sptm`=sptm,`spxxb`.`jldw`=jldw,`spxxb`.`spzt`=spzt,`spxxb`.`lsj`=lsj where `spxxb`.`spid`=spid;
  else
    delete from spxxb where `spxxb`.`spid`=spid;
  end if;
END */$$
DELIMITER ;

四、触发器

DELIMITER $$
CREATE  TRIGGER `Update_spxxb_mcsx_before_insert_spxxb` BEFORE INSERT ON `spxxb` FOR EACH ROW BEGIN
  set new.mcsx=pysxcx(new.spmc);
END */$$
DELIMITER ;

DELIMITER $$
CREATE  TRIGGER `Update_mcsx_before_update_spxxb_spmc` BEFORE UPDATE ON `spxxb` FOR EACH ROW BEGIN
  if new.spmc<>old.spmc then
    set new.mcsx=pysxcx(new.spmc);
  end if;
END */$$

DELIMITER $$
CREATE  TRIGGER `Delete_spkcb_before_delete_spxxb` BEFORE DELETE ON `spxxb` FOR EACH ROW BEGIN
  delete from spkcb where spid=old.spid;
END */$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `Delete_spkcb_before_delete_spxxb` BEFORE DELETE ON `spxxb` FOR EACH ROW BEGIN
  DELETE FROM spkcb WHERE spid=old.spid;
END;$$
DELIMITER ;

进销存业务二: 销售业务的数据库端代码

一、基础表:
1、收银记录表
CREATE TABLE `syjlb` (
  `syid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '收银id',
  `syyid` INT(11) DEFAULT NULL COMMENT '收银员id',
  `gkid` INT(11) DEFAULT NULL COMMENT '顾客id（0 散客, 1-9999 大客户, >=10000 会员）',
  `sysj` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '收银时间',
  `ysje` DECIMAL(9,2) DEFAULT '0.00' COMMENT '应收金额',
  `czkje` DECIMAL(9,2) DEFAULT '0.00' COMMENT '储值卡金额',
  `qtje` DECIMAL(9,2) DEFAULT '0.00' COMMENT '其他金额',
  `zffs` TINYINT(4) DEFAULT '0' COMMENT '支付方式',
  `syzt` TINYINT(4) DEFAULT '0' COMMENT '收银状态（0 正常, 1 退款）',
  `zzid` INT(11) DEFAULT '0' COMMENT '扎帐id(0 未扎帐, >0 已扎帐)',
  PRIMARY KEY (`syid`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2、销售明细表
CREATE TABLE `xsmxb` (
  `mxid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '明细id',
  `syid` INT(11) DEFAULT NULL COMMENT '收银id(与收银记录表对应)',
  `spid` INT(11) DEFAULT NULL COMMENT '商品id(与商品名称表对应)',
  `sl` DECIMAL(9,3) DEFAULT NULL COMMENT '数量',
  `dj` DECIMAL(9,2) DEFAULT NULL COMMENT '单价',
  PRIMARY KEY (`mxid`)
) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

3、商品库存表
CREATE TABLE `spkcb` (
  `kcid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '库存id',
  `spid` INT(11) DEFAULT NULL COMMENT '商品id',
  `kcsl` INT(11) DEFAULT '0' COMMENT '库存数量',
  PRIMARY KEY (`kcid`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

4、会员信息表
CREATE TABLE `hyxxb` (
  `hyid` INT(11) NOT NULL AUTO_INCREMENT=10000 COMMENT '会员id',
  `hykh` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '会员卡号',
  `hyxm` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '会员姓名',
  `hyxb` VARCHAR(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '会员性别',
  `knye` DECIMAL(11,2) DEFAULT '0.00' COMMENT '卡内余额',
  `kyjf` INT(11) DEFAULT '0' COMMENT '可用积分',
  `scxfsj` DATETIME DEFAULT NULL COMMENT '上次消费时间',
  PRIMARY KEY (`hyid`)
) ENGINE=INNODB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

二、存储过程

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SyjlCp`(syyid INT,hyid INT,ysje NUMERIC(9,2),ssje NUMERIC(9,2),qtje NUMERIC(9,2),zffs TINYINT,xsmx VARCHAR(8000))
BEGIN
  /*--插入Syjlb  */
  INSERT INTO Syjlb (syyid,hyid,ysje,ssje,qtje,zffs) VALUES (syyid,hyid,ysje,ssje,qtje,zffs);
  /*--取刚插入的syid  */
  SET @syid=@@IDENTITY;

  /*--定义临时表*/
  DROP TABLE IF EXISTS `lsb`;  
  CREATE TEMPORARY TABLE lsb (hpid INT,xssl INT);
  /*--解析@xsmx        */
  SET @k=POSITION(',' IN xsmx);
  WHILE (@k>0) DO
      SET @hpid=LEFT(xsmx,@k-1);
      SET xsmx=SUBSTR(xsmx,@k+1);
  
      SET @k=POSITION(',' IN xsmx);
      SET @xssl=LEFT(xsmx,@k-1);
   
      INSERT lsb (hpid,xssl) VALUE (@hpid,@xssl);
      SET xsmx=SUBSTR(xsmx,@k+1);
      SET @k=POSITION(',' IN xsmx);
  END WHILE;    

  INSERT INTO SyjlMxb (syid,hpid,xssl,lsj,cxj) SELECT @syid,lsb.hpid,xssl,lsj,cxj FROM lsb,Hpxxb WHERE lsb.hpid=hpxxb.hpid;

  /*--变更库存(没有缺陷)    */
  /*--需要合并销售数量        */
  DROP TABLE IF EXISTS `kcbglsb`;  
  CREATE TEMPORARY TABLE kcbglsb (hpid INT,xssl NUMERIC(9,3));
  INSERT INTO kcbglsb SELECT hpid,SUM(xssl) FROM lsb GROUP BY hpid;
  UPDATE kcxxb b1,kcbglsb b2 SET b1.kcsl=b1.kcsl-b2.xssl WHERE b1.hpid=b2.hpid;

  /*--变更会员信息*/  
  IF (@hyid>=10000) THEN
    UPDATE hyxxb SET knye=knye-ssje,kyjf=kyjf+FLOOR(ssje),scxfsj=NOW() WHERE hyid=hyid;
  END IF;

END$$

DELIMITER ;