建立数据库和表
🌻创建供应商信息表
CREATE TABLE `possystem`.`bt_gysxxb`( `gysid` INT NOT NULL AUTO_INCREMENT COMMENT '供应商id', `gysmc` VARCHAR(50) DEFAULT 'NULL' COMMENT '供应商名称', `lxren` VARCHAR(50) DEFAULT 'NULL' COMMENT '联系人', `lxdh` VARCHAR(50) DEFAULT 'NULL' COMMENT '联系电话', `gyszt` TINYINT DEFAULT 0 COMMENT '供应商状态(0 正常来往,1 暂停往来,2 不在往来)', PRIMARY KEY (`gysid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
🌻创建货品信息表
CREATE TABLE `possystem`.`tb_hpxxb`( `hpid` INT NOT NULL AUTO_INCREMENT COMMENT '货品信息表id', `hpmc` VARCHAR(50) NOT NULL COMMENT '货品名称', `hptm` VARCHAR(20) NOT NULL COMMENT '货品条码', `jldw` VARCHAR(10) DEFAULT 'NULL' COMMENT '计量单位', `hpzt` TINYINT UNSIGNED DEFAULT 0 COMMENT '0正常 1不在进货 2不在出售', `dj` DECIMAL(18,3) DEFAULT NULL COMMENT '定价', `cxj` DECIMAL(18,3) DEFAULT NULL COMMENT '促销价', `mcsx` VARCHAR(10) COMMENT '名称缩写', PRIMARY KEY (`hpid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
🌻创建货品库存表
CREATE TABLE `possystem`.`tb_hpkcb`( `kcid` INT NOT NULL COMMENT '库存id', `hpid` INT 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`), CONSTRAINT `tb_hpkcb_idfk_1` FOREIGN KEY (`hpid`) REFERENCES `possystem`.`tb_hpxxb`(`hpid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
🌻创建会员信息表
CREATE TABLE `possystem`.`tb_hyxxb`( `hyid` INT NOT NULL AUTO_INCREMENT COMMENT '会员信息表id,从10000开始', `hyxm` VARCHAR(50) DEFAULT 'NULL' COMMENT '会员姓名', `hyxb` CHAR(2) DEFAULT COMMENT '会员性别', `hykh` VARCHAR(20) DEFAULT 'NULL' COMMENT '会员卡号', `sjhm` VARCHAR(20) DEFAULT 'NULL' COMMENT '手机号码', `knye` DECIMAL(9,2) DEFAULT NULL COMMENT '卡内余额', `kyjf` INT DEFAULT NULL COMMENT '可用积分', `scxfsj` DATETIME COMMENT '上次消费时间', PRIMARY KEY (`hyid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
为什么char类型和datetime类型默认值设置为NULL会报错?
USING BTREE?
🌻扎帐记录表
CREATE TABLE `possystem`.`bt_zzjlb`( `zzid` INT NOT NULL AUTO_INCREMENT COMMENT '扎帐记录表', `syyid` INT DEFAULT NULL COMMENT '收银员id', `zzsj` DATETIME DEFAULT 'CURRENT_TIMESTAMP' COMMENT '扎帐时间,默认值为系统当前时间', `ysje` DECIMAL(18,2) DEFAULT NULL COMMENT '应收金额(由系统计算)', `ssje` DECIMAL(18,2) DEFAULT NULL COMMENT '实收金额(由收银员输入)', `ccyy` VARCHAR(200) DEFAULT 'NULL' COMMENT '差错原因', PRIMARY KEY (`zzid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
CURRENT_TIMESTAMP报错?
🌻收银记录表
CREATE TABLE `possystem`.`tb_syjlb`( `syid` INT NOT NULL AUTO_INCREMENT COMMENT '收银记录表id', `syyid` INT DEFAULT NULL COMMENT '收银员id', `gkid` INT DEFAULT NULL COMMENT '顾客id 0 普通顾客,1-9999大客户,>=10000会员', `sysj` DATETIME COMMENT '收银时间', `zffs` TINYINT DEFAULT NULL COMMENT '支付方式 0现金,1储值卡,2支付宝,3微信支付', `ysje` DECIMAL(18,2) DEFAULT NULL COMMENT '应收金额', `ssje` DECIMAL(18,2) DEFAULT NULL COMMENT '实收金额', `yhje` DECIMAL(18,2) DEFAULT NULL COMMENT '优惠金额', `xszt` TINYINT DEFAULT 0 COMMENT '销售状态', `zzid` INT DEFAULT 0 COMMENT '扎帐id', PRIMARY KEY (`syid`), CONSTRAINT `kf_zzjlb_syjlb` FOREIGN KEY (`zzid`) REFERENCES `possystem`.`bt_zzjlb`(`zzid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
CURRENT_TIMESTAMP报错?
🌻收银明细表
CREATE TABLE `possystem`.`tb_symxb`( `mxid` INT NOT NULL AUTO_INCREMENT COMMENT '收银明细id', `syid` INT DEFAULT NULL COMMENT '收银员id', `hpid` INT DEFAULT NULL COMMENT '货品id', `xssl` DECIMAL(18,3) DEFAULT NULL COMMENT '销售数量', `dj` DECIMAL(9,2) DEFAULT NULL COMMENT '定价', `lsj` DECIMAL(9,2) DEFAULT NULL COMMENT '零售价(用来解决货品调价问题)', KEY(`mxid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
KEY
fk_syjlb_symxb
(syid
),KEY
fk_hpmcb_symxb
(hpid
)如何进行设置?
🌻用户信息表
CREATE TABLE `possystem`.`tb_yhxxb`( `syyid` INT NOT NULL AUTO_INCREMENT COMMENT '收银员id', `yhm` VARCHAR(50) NOT NULL COMMENT '收银员用户名', `pwd` VARCHAR(20) NOT NULL COMMENT '密码', `phone` VARCHAR(20) DEFAULT 'NULL' COMMENT '收银员电话', `syylb` INT DEFAULT NULL COMMENT '1 收银员\\r\\n2 经理\\r\\n3服务台', PRIMARY KEY (`syyid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
AUTO_INCREMENT=2如何进行设置?
🌻辅助表hzpyb
货品信息表相关设计
💮创建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 ;
💮为hpxxb创建插入与修改触发器,以维护mcsx列
update_mcsx_before_insert_tb_hpxxb
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `possystem`.`update_mcsx_before_insert_tb_hpxxb` BEFORE INSERT
ON `possystem`.`tb_hpxxb`
FOR EACH ROW BEGIN
SET new.mcsx=pysxcx(new.hpmc);
END$$
DELIMITER ;
update_mcsx_before_update_tb_hpxxb
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `possystem`.`update_mcsx_before_update_tb_hpxxb` BEFORE UPDATE
ON `possystem`.`tb_hpxxb`
FOR EACH ROW BEGIN
IF (new.hpmc<>old.hpmc) THEN
SET new.mcsx=pysxcx(new.hpmc);
END IF;
END$$
DELIMITER ;
💮创建hpxxbcp存储过程,完成hpxxb的增删改操作
DELIMITER $$
USE `possystem`$$
DROP PROCEDURE IF EXISTS `hpxxbcp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `hpxxbcp`(
hpid INT,
hpmc VARCHAR (50),
hptm VARCHAR (20),
jldw VARCHAR (10),
hpzt TINYINT,
dj DECIMAL (18, 3),
cxj DECIMAL (18, 3)
)
BEGIN
/*增加货品*/
IF hpid = 0
THEN
INSERT INTO tb_hpxxb (hpmc, hptm, jldw, dj, cxj, mcsx)
VALUES
(hpmc, hptm, jldw, dj, cxj, pysxcx (hpmc)) ;
SET @hpid = @@identity ;
INSERT INTO tb_hpkcb (hpid, kcsl, zrkc, yckc)
VALUES
(@hpid, 0, 0, 0) ;
/*修改*/
ELSEIF hpid > 0
THEN
UPDATE
tb_hpxxb
SET
`tb_hpxxb`.`hpmc` = hpmc,
`tb_hpxxb`.`hptm` = hptm,
`tb_hpxxb`.`jldw` = jldw,
`tb_hpxxb`.`dj` = dj,
`tb_hpxxb`.`cxj` = cxj,
`tb_hpxxb`.`mcsx` = pysxcx (hpmc),
`tb_hpxxb`.`hptm` = hptm
WHERE `tb_hpxxb`.`hpid` = hpid ;
/*删除*/
ELSE
DELETE FROM tb_hpkcb WHERE `tb_hpkcb`.`hpid`=-hpid;
DELETE FROM tb_hpxxb WHERE `tb_hpxxb`.`hpid`=-hpid;
END IF ;
END$$
DELIMITER ;
💮创建hpxxbcx存储过程,完成hpxxb的查询操作
DELIMITER $$
USE `possystem`$$
DROP PROCEDURE IF EXISTS `hpxxbcx`$$
CREATE DEFINER = `root` @`localhost` PROCEDURE `hpxxbcx` (hpid INT)
BEGIN
/*查询全部货品*/
IF hpid = 0
THEN
SELECT
*
FROM
tb_hpxxb ;
ELSE
SELECT
*
FROM
tb_hpxxb
WHERE `tb_hpxxb`.`hpid` = hpid ;
END IF ;
END $$
DELIMITER ;