[MySQL]超市购物管理系统

数据库结构

一.逻辑结构设计

1.会员信息表hyxxb

会员信息表hyxxb（会员id，姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）
DROP TABLE IF EXISTS hyxxb;
CREATE TABLE hyxxb(
hyid INT NOT NULL PRIMARY KEY  COMMENT '会员信息表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<=9999 OR 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_hpkcb
    AS
    SELECT hpkcb.hpid,hpmc,jldw,kcsl
    FROM hpkcb,hpmcb
    WHERE hpmcb.Hpid=hpkcb.hpid;

DESC v_hpkcb;
SELECT * FROM v_hpkcb;

2.收银明细视图

CREATE VIEW v_symxb
    AS
    SELECT mxid,syid,hpmc,jldw,xssl,dj,symxb.lsj
    FROM symxb,hpmcb
    WHERE 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) DETERMINISTIC
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;
SELECT pysxcx('可口可乐');

五.创建触发器

对于货品信息表创建触发器，自动维护名称缩写列
DROP TRIGGER IF EXISTS Update_mcsx_before_insert_hpmcb;
DELIMITER $$
CREATE TRIGGER Update_mcsx_before_insert_hpmcb 
BEFORE INSERT ON hpmcb
FOR EACH ROW 
BEGIN
SET 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_hpmc
BEFORE UPDATE ON hpmcb
FOR EACH ROW 
BEGIN
IF(new.hpmc<>old.hpmc)
THEN
SET 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 
THEN
INSERT 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>0
THEN
UPDATE hpmcb SET
hpmcb.hpmc=hpmc,
hpmcb.hptm=hptm,
hpmcb.jldw=jldw,
hpmcb.lsj=lsj,
hpmcb.cxj=cxj,
hpmcb.mcsx=pysxcx(hpmc),
hpmcb.hptm=hptm,
hpmcb.hpzt=hpzt
WHERE hpmcb.hpid=hpid;
-- 输入-hpid，删除指定商品信息
ELSE
    DELETE 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=0
THEN
SELECT * FROM hpmcb;
ELSE
-- 输入hpid，查询指定商品
SELECT * FROM hpmcb WHERE hpmcb.hpid=hpid;
END IF;
END $$
DELIMITER;
-- 调用查存储过程
CALL hpxxcx(0);
CALL hpxxcx(20);