pos系统开发步骤1-创建数据库以及拼音缩写的实现
🌻 创建数据库possystem
CREATE DATABASE /*!32312 IF NOT EXISTS*/`possystem` /*!40100 DEFAULT CHARACTER SET utf8 */;
注意创建数据库时要选择编码方式和修正方式为utf8
🌻 创建hzpyb表
DROP TABLE IF EXISTS `hzpyb`; CREATE TABLE `hzpyb` ( `hz` CHAR(2) DEFAULT NULL, `jp` CHAR(1) DEFAULT NULL, `py` VARCHAR(10) DEFAULT NULL, KEY `hz` (`hz`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
🌻创建spmcb表
DROP TABLE IF EXISTS `spmcb`; CREATE TABLE `spmcb` ( `spid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '商品名称表', `spmc` VARCHAR(50) DEFAULT NULL COMMENT '商品名称', `sptm` VARCHAR(20) DEFAULT NULL COMMENT '商品条码', `dw` VARCHAR(10) DEFAULT NULL COMMENT '计量单位', `dj` DECIMAL(9,2) DEFAULT NULL COMMENT '零售价', `spzt` TINYINT(4) DEFAULT '0' COMMENT '商品状态(0正常,1下架)', `mcsx` VARCHAR(10) DEFAULT NULL COMMENT '名称缩写(由触发器自行维护)', PRIMARY KEY (`spid`) ) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
🌻创建spkcb表
DROP TABLE IF EXISTS `spkcb`; CREATE TABLE `spkcb` ( `kcid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '库存id', `spid` INT(11) DEFAULT NULL COMMENT '商品id(用来代表商品)', `kcsl` DECIMAL(18,3) DEFAULT '0.000' COMMENT '(当前)库存数量', `zrkc` DECIMAL(18,3) DEFAULT '0.000' COMMENT '昨日库存(用于日清)', `yckc` DECIMAL(18,3) DEFAULT '0.000' COMMENT '月初库存(用于月结)', PRIMARY KEY (`kcid`) ) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
☀️创建pysxcx(拼音缩写查询)函数
DELIMITER $$ USE `possystem`$$ DROP FUNCTION IF EXISTS `pysxcx`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `pysxcx`(`zw` VARCHAR(50)) RETURNS VARCHAR(10) CHARSET utf8 BEGIN SET @l=CHAR_LENGTH(`zw`); SET @i=1; SET @pysx=''; SET @jp=''; WHILE @i<=@l DO SET @hz=SUBSTRING(`zw`,@i,1); SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz; SET @pysx=CONCAT(@pysx,@jp); SET @i=@i+1; END WHILE; RETURN @pysx; END$$ DELIMITER ;
测试pysxcx函数
SELECT pysxcx('张三丰');
☀️编写Spxxwh存储过程
DELIMITER $$ USE `possystem`$$ DROP PROCEDURE IF EXISTS `Spxxwh`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Spxxwh`(spid INT,spmc VARCHAR(50),sptm VARCHAR(20),dw VARCHAR(10),dj NUMERIC(9,2),spzt TINYINT) BEGIN IF spid=0 THEN INSERT INTO spmcb(spmc,sptm,dw,dj) VALUE(spmc,sptm,dw,dj); ELSEIF spid>0 THEN UPDATE spmcb SET `spmcb`.`spmc`=spmc,`spmcb`.`sptm`=sptm,`spmcb`.`dw`=dw,`spmcb`.`dj`=dj,`spmcb`.`spzt`=spzt WHERE `spmcb`.`spid`=spid; ELSE DELETE FROM spmcb WHERE `spmcb`.`spid`=-spid; END IF; END$$ DELIMITER ;
测试
/*增加*/ CALL Spxxwh(0,'可口可乐','102','瓶',3.5,0); SELECT * FROM spmcb; /*修改*/ CALL Spxxwh(4,'王老吉','103','瓶',2.5,0); SELECT * FROM spmcb; /*删除*/ CALL Spxxwh(-1,'百事可乐','101','瓶',3.5,0); SELECT * FROM spmcb;
☀️编写触发器
Clmcsx_before_insert_spmcb插入时对mcsx进行设置
DELIMITER $$ USE `possystem`$$ DROP TRIGGER /*!50032 IF EXISTS */ `Clmcsx_before_insert_spmcb`$$ CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `Clmcsx_before_insert_spmcb` BEFORE INSERT ON `spmcb` FOR EACH ROW BEGIN SET new.mcsx=pysxcx(new.spmc); END; $$ DELIMITER ;
Insert_Spkcb_after_insert在spmcb中执行插入时,对spkcb进行插入
DELIMITER $$ USE `possystem`$$ DROP TRIGGER /*!50032 IF EXISTS */ `Insert_Spkcb_after_insert`$$ CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `Insert_Spkcb_after_insert` AFTER INSERT ON `spmcb` FOR EACH ROW BEGIN INSERT INTO spkcb(spid) VALUE(new.spid); END; $$ DELIMITER ;
Update_mcsx_before_update_spmc更新spmcb时,对mcsx进行更新
DELIMITER $$ USE `possystem`$$ DROP TRIGGER /*!50032 IF EXISTS */ `Update_mcsx_before_update_spmc`$$ CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `Update_mcsx_before_update_spmc` BEFORE UPDATE ON `spmcb` FOR EACH ROW BEGIN IF (new.spmc<>old.spmc) THEN SET new.mcsx=pysxcx(new.spmc); END IF; END; $$ DELIMITER ;