目录
数据库结构
一.逻辑结构设计
1.会员信息表hyxxb
2.货品名称表hpmcb
3.供应商信息表gysxxb
4.收银记录表
5.收银明细表symxb
6.货品库存表
7.扎帐记录表
8.汉字拼音表
二.索引
1.汉字拼音表索引
三.视图
1.货品库存数量视图
2.收银明细视图
四.创建函数
五.创建触发器
六.创建存储过程
1.货品信息维护HpxxWh存储过程
2.货品信息查询HpxxCx存储过程
数据库结构
一.逻辑结构设计
1.会员信息表hyxxb
会员信息表hyxxb（会员id，姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）DROP TABLE IF EXISTS hyxxb;CREATE TABLE hyxxb(hyid INT NOT NULL PRIMARY KEYCOMMENT '会员信息表id',hyxm VARCHAR(50) COMMENT '会员姓名',hyxb CHAR(2) COMMENT '会员性别',hykh VARCHAR(20) COMMENT '会员卡号',sjhm VARCHAR(20) COMMENT '手机号码',knye DECIMAL(9,2) COMMENT '卡内余额',kyjf INT COMMENT '可用积分',scxfsj DATETIME COMMENT '上次消费时间')CHARSET=utf8;ALTER TABLE hyxxb AUTO_INCREMENT=1000;SELECT * FROM hyxxb;DESC hyxxb;2.货品名称表hpmcb
2、货品名称表hpmcb（货品id，货品名称，计量单位，货品条码，零售价，促销价，名称缩写(由触发器自行维护)，货品状态）DROP TABLE IF EXISTS hpmcb;CREATE TABLE hpmcb(hpid INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '货品id',hpmc VARCHAR(50) COMMENT '货品名称',jldw VARCHAR(10) COMMENT '计量单位',hptm VARCHAR(20) COMMENT '货品条码',lsj DECIMAL(9,2) COMMENT '零售价',cxj DECIMAL(9,2) COMMENT '促销价',mcsx VARCHAR(10) COMMENT '名称缩写',hpzt TINYINT CHECK (hpzt IN(0,1,2))COMMENT '货品状态')CHARSET=utf8;SELECT * FROM hpmcb;INSERT INTO hpmcb VALUES(9,'可口可乐','瓶','101',3.00,2.50,'kkkl',0),(10,'百事可乐','瓶','102',3.00,2.50,'bskl',0),(11,'伊利牛奶','盒','103',5.00,4.50,'ylnn',0),(12,'农夫山泉','瓶','105',2.00,1.50,'nfsq',0),(13,'涪陵榨菜','袋','106',1.00,0.80,'flzc',0),(14,'青岛啤酒','听','107',5.00,4.00,'qdpj',0);3.供应商信息表gysxxb
供应商信息表gysxxb（供应商id，供应商名称，联系人，联系电话，供应商状态）DROP TABLE IF EXISTS gysxxb;CREATE TABLE gysxxb(gtsid INT NOT NULL PRIMARY KEY COMMENT '供应商id',gysmc VARCHAR(50) COMMENT '供应商名称',lxren VARCHAR(50) COMMENT '联系人',lxdh VARCHAR(50) COMMENT '联系电话',gyszt TINYINT CHECK (gyszt IN(0,1,2))COMMENT '供应商状态')CHARSET=utf8;SELECT * FROM gysxxb;4.收银记录表
收银记录表syjlb（收银id，收银员id，顾客id，收银时间，支付方式，应收金额，实收金额，优惠金额，销售状态（正常 ,已退货），扎帐id）DROP TABLE IF EXISTS syjlb;CREATE TABLE syjlb(syid INT NOT NULL PRIMARY KEY,syyid INT COMMENT '收银员id',gkid INT CHECK (gkid=0 OR gkid>=1 AND gkid=10000)COMMENT '顾客id',sysj DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '收银时间',zffs TINYINT CHECK (zffs IN(0,1,2,3))COMMENT '支付方式',ysje DECIMAL(18,2) COMMENT '应收金额',ssje DECIMAL(18,2) COMMENT '实收金额',yhje DECIMAL(18,2) COMMENT '优惠金额',xszt TINYINT CHECK (xszt IN(0,1))COMMENT '销售状态',zzid INT COMMENT '扎帐id',CONSTRAINT fk_syjlb_zzjlb FOREIGN KEY(zzid) REFERENCES zzjlb(zzid))CHARSET=utf8;INSERT INTO syjlb VALUES(8,66,0,CURRENT_TIMESTAMP,0,6.00,5.00,1.00,0,5),(9,66,0,CURRENT_TIMESTAMP,0,12.00,10.00,2.00,0,5);SELECT * FROM syjlb;5.收银明细表symxb
收银明细表symxb（收货明细id，收银id，货物id，销售数量，零售价、促销价）DROP TABLE IF EXISTS symxb;CREATE TABLE symxb(mxid INT NOT NULL COMMENT '收银明细id',syid INT COMMENT '收银id',hpid INT COMMENT '货品id',xssl DECIMAL(18,3) COMMENT '销售数量',dj DECIMAL(9,2) COMMENT '定价',lsj DECIMAL(9,2) COMMENT '零售价',CONSTRAINT fk_syjlb_symxb FOREIGN KEY(syid) REFERENCES syjlb(syid),CONSTRAINT fk_hpmcb_symxb FOREIGN KEY(hpid) REFERENCES hpmcb(Hpid))CHARSET=utf8;INSERT INTO symxb VALUES(16,8,9,1.000,2.50,3.00),(17,8,10,1.000,2.50,3.00),(19,9,14,1.000,4.00,5.00),(20,9,11,1.000,4.50,5.00),(21,9,12,1.000,1.50,2.00);SELECT * FROM symxb;6.货品库存表
6、货品库存表hpkcb（库存id，货品id，(当前)库存数量，昨日库存(用于日清)，月初库存(用于月结)）DROP TABLE IF EXISTS hpkcb;CREATE TABLE hpkcb(kcid INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '库存id',hpid INT COMMENT '货品id',kcsl DECIMAL(18,3) COMMENT '库存数量',zrkc DECIMAL(18,3) COMMENT '昨日库存',yckc DECIMAL(18,3) COMMENT '月初库存',CONSTRAINT fk_hpmcb_hpkcb FOREIGN KEY(hpid) REFERENCES hpmcb(hpid))CHARSET=utf8;SELECT * FROM hpkcb;7.扎帐记录表
扎帐记录表zzjlb（扎帐id，收银员id，扎帐时间，应收金额(由系统计算)，实收金额(由收银员输入)，差错原因）DROP TABLE IF EXISTS zzjlb;CREATE TABLE zzjlb(zzid INT NOT NULL PRIMARY KEY COMMENT '扎帐记录表',syyid INT COMMENT '收银员id',zzsj DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '扎帐时间',ysje DECIMAL(18,2) COMMENT '应收金额',ssje DECIMAL(18,2) COMMENT '实收金额',ccyy VARCHAR(200) COMMENT '差错原因')CHARSET=utf8;SELECT * FROM zzjlb;INSERT INTO zzjlb VALUES(5,66,CURRENT_TIMESTAMP,15.00,15.00,'本次结账无差错');8.汉字拼音表
辅助表 汉字拼音表Hzpyb（汉字，简拼，拼音）DROP TABLE IF EXISTS Hzpyb;CREATE TABLE `hzpyb` (hz VARCHAR(6) COMMENT '汉字',jp VARCHAR(3) COMMENT '简拼',py VARCHAR(30) COMMENT '拼音') ENGINE=INNODB DEFAULT CHARSET=utf8;SELECT * FROM hzpyb;二.索引
1.汉字拼音表索引
CREATE INDEX index_hz ON hzpyb(hz);CREATE INDEX index_jp ON hzpyb(jp);CREATE INDEX index_py ON hzpyb(py);SHOW INDEX FROM hzpyb;三.视图
1.货品库存数量视图
CREATE VIEW v_hpkcbASSELECT hpkcb.hpid,hpmc,jldw,kcslFROM hpkcb,hpmcbWHERE hpmcb.Hpid=hpkcb.hpid;DESC v_hpkcb;SELECT * FROM v_hpkcb;2.收银明细视图
CREATE VIEW v_symxbASSELECT mxid,syid,hpmc,jldw,xssl,dj,symxb.lsjFROM symxb,hpmcbWHERE hpmcb.Hpid=symxb.hpid;DESC v_symxb;SELECT * FROM v_symxb;四.创建函数
创建PysxCx函数，能够根据输入的汉字，返回其拼音首字母缩写DROP FUNCTION IF EXISTS pysxcx;DELIMITER $$CREATE FUNCTION `pysxcx`(zw VARCHAR(50)) RETURNS VARCHAR(10) DETERMINISTICBEGINSET @l=CHAR_LENGTH(zw);SET @i=1;SET @pysx='';SET @jp='';WHILE @i<=@l DOSET @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;SELECT pysxcx('可口可乐');五.创建触发器
对于货品信息表创建触发器，自动维护名称缩写列DROP TRIGGER IF EXISTS Update_mcsx_before_insert_hpmcb;DELIMITER $$CREATE TRIGGER Update_mcsx_before_insert_hpmcb BEFORE INSERT ON hpmcbFOR EACH ROW BEGINSET new.mcsx=pysxcx(new.hpmc);END$$DELIMITER ;DROP TRIGGER IF EXISTS Update_mcsx_before_update_hpmcb_hpmc;DELIMITER $$CREATE TRIGGER Update_mcsx_before_update_hpmcb_hpmcBEFORE UPDATE ON hpmcbFOR EACH ROW BEGINIF(new.hpmcold.hpmc)THENSET new.mcsx=pysxcx(new.hpmc);END IF;END$$DELIMITER;六.创建存储过程
1.货品信息维护HpxxWh存储过程
创建货品信息维护HpxxWh存储过程,能够实现对货品信息的增删改操作DROP PROCEDURE IF EXISTS hpxxwh;DELIMITER $$CREATE PROCEDURE hpxxwh(hpid INT,hpmc VARCHAR(50),jldw VARCHAR(10),hptm VARCHAR(20),lsj DECIMAL(9,2),cxj DECIMAL(9,2),hpzt TINYINT)BEGIN-- 输入0，增加货品IF hpid=0 THENINSERT INTO hpmcb(hpmc,hptm,jldw,lsj,cxj,mcsx,hpzt)VALUES(hpmc,hptm,jldw,lsj,cxj,pysxcx(hpmc),hpzt);SET @hpid=@@identity;INSERT INTO hpkcb(hpid,kcsl,zrkc,yckc)VALUES(@hpid,0,0,0);-- 输入hpid，修改货品信息ELSEIF hpid>0THENUPDATE hpmcb SEThpmcb.hpmc=hpmc,hpmcb.hptm=hptm,hpmcb.jldw=jldw,hpmcb.lsj=lsj,hpmcb.cxj=cxj,hpmcb.mcsx=pysxcx(hpmc),hpmcb.hptm=hptm,hpmcb.hpzt=hpztWHERE hpmcb.hpid=hpid;-- 输入-hpid，删除指定商品信息ELSEDELETE FROM hpkcb WHERE hpkcb.hpid=-hpid;DELETE FROM hpmcb WHERE hpmcb.hpid=-hpid;END IF;END$$DELIMITER;-- 调用增删改存储过程CALL hpxxwh(-22,'香蕉','个','001',4.00,2.00,0);CALL hpxxwh(0,'苹果','个','001',4.00,2.00,0);CALL hpxxwh(1,'梨','个','001',4.00,2.00,0);2.货品信息查询HpxxCx存储过程
创建货品信息查询HpxxCx存储过程,能够实现对货品信息的查询操作DROP PROCEDURE IF EXISTS hpxxcx;DELIMITER $$CREATE PROCEDURE hpxxcx(hpid INT)BEGIN-- 输入0，查询全部商品IF hpid=0THENSELECT * FROM hpmcb;ELSE-- 输入hpid，查询指定商品SELECT * FROM hpmcb WHERE hpmcb.hpid=hpid;END IF;END $$DELIMITER;-- 调用查存储过程CALL hpxxcx(0);CALL hpxxcx(20);