Mysql 设计超市经营管理系统，包括员工库存表（stock） 和 仓库表（warehouse）

互联网技术学院周测机试题（三）

 

一、需求分析

为进一步完善连锁超市经营管理，提高管理效率，减少管理成本，决定开发一套商品管理系统，用于日常的管理。本系统分为商品管理、员工管理、店铺管理，库存管理等功能模块。本次开发，主要针对库存管理功能，具备显示各类商品库存量、进货管理等功能，具体数据库设计如下，请你根据需求完成数据库的设计与相应功能。

二、数据字典

表1：库存表（stock）

序号	字段名	说明	数据类型	约束	备注
1	Sid	库存编号	Int	主键，自动增长
2	Goodsname	商品名称	Varchar	长度50，非空
3	Num	库存数量	Int
4	Wid	仓库编号	Int	外键，引用仓库表的主键
5	Title	注意事项	Varchar	长度20

CREATE TABLE stock(
sid int PRIMARY KEY auto_increment,
goodsname VARCHAR(50) NOT NULL,
num int,
wid int,
CONSTRAINT fk_wid FOREIGN KEY(wid) REFERENCES warehouse(wid),
title VARCHAR(20) NOT NULL
);

表2：仓库表（warehouse）

序号	字段名	说明	数据类型	约束	备注
1	wid	仓库编号	Int	主键，自动增长
2	wname	仓库名称	Varchar	长度20，非空

CREATE TABLE warehouse(
wid int PRIMARY KEY auto_increment,
wname VARCHAR(20) NOT NULL
);

测试数据

stock表：

Sid	Goodsname	Num	Wid	Title
1	飞利浦SP100净化器	20	1	贵重商品
2	山东红富士苹果（一级果）	60	3	快消商品
3	康师傅桶装方便面（红烧牛肉面）	100	2	快消商品
4	格力NS-8823变频空调	10	1	库存较少
5	泰国山竹特级	150	3	临近保质期

INSERT INTO `stock` VALUES (1, '飞利浦SP100净化器', 20, 1, '贵重商品');
INSERT INTO `stock` VALUES (2, '山东红富士苹果（一级果）', 60, 3, '快消商品');
INSERT INTO `stock` VALUES (3, '康师傅桶装方便面（红烧牛肉面）', 100, 2, '快消商品');
INSERT INTO `stock` VALUES (4, '格力NS-8823变频空调', 10, 1, '库存较少');
INSERT INTO `stock` VALUES (5, '泰国山竹特级', 150, 3, '临近保质期');

warehouse：

wid	wname
1	华北1号仓库
2	华北2号仓库
3	华南2号仓库

INSERT INTO `warehouse` VALUES (1, '华北1号仓库');
INSERT INTO `warehouse` VALUES (2, '华北2号仓库');
INSERT INTO `warehouse` VALUES (3, '华南2号仓库');

三、功能实现

1、在华南2号仓库增加30辆捷安特BT22自行车，注意事项为贵重商品。

INSERT INTO stock VALUES(null,'捷安特BT22自行车',30,3,'贵重商品')

2、显示华北所有仓库的商品名称与库存数量。

SELECT wname,goodsname,num FROM stock s,warehouse w WHERE s.wid=w.wid AND wname LIKE '华北%'

3、统计各仓库的库存商品总量，显示仓库名称和库存商品总量。

SELECT wname,SUM(num) FROM stock s,warehouse w WHERE s.wid=w.wid GROUP BY wname

4、修改泰国山竹特级销售100KG后，剩余的库存量。

UPDATE stock SET num=(num-100) WHERE goodsname='泰国山竹特级'

5、更改康师傅桶装方便面（红烧牛肉面）的仓库名称为“华北4号仓库”。

UPDATE warehouse SET wname='华北4号仓库' WHERE wid=(SELECT wid FROM stock 
WHERE goodsname='康师傅桶装方便面（红烧牛肉面）')

数据库完整导入代码：

-- ----------------------------
-- Table structure for stock
-- ----------------------------
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `goodsname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `num` int(11) NULL DEFAULT NULL,
  `wid` int(11) NULL DEFAULT NULL,
  `title` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `fk_wid`(`wid`) USING BTREE,
  CONSTRAINT `fk_wid` FOREIGN KEY (`wid`) REFERENCES `warehouse` (`wid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of stock
-- ----------------------------
INSERT INTO `stock` VALUES (1, '飞利浦SP100净化器', 20, 1, '贵重商品');
INSERT INTO `stock` VALUES (2, '山东红富士苹果（一级果）', 60, 3, '快消商品');
INSERT INTO `stock` VALUES (3, '康师傅桶装方便面（红烧牛肉面）', 100, 4, '快消商品');
INSERT INTO `stock` VALUES (4, '格力NS-8823变频空调', 10, 1, '库存较少');
INSERT INTO `stock` VALUES (5, '泰国山竹特级', 50, 3, '临近保质期');

-- ----------------------------
-- Table structure for warehouse
-- ----------------------------
DROP TABLE IF EXISTS `warehouse`;
CREATE TABLE `warehouse`  (
  `wid` int(11) NOT NULL AUTO_INCREMENT,
  `wname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`wid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of warehouse
-- ----------------------------
INSERT INTO `warehouse` VALUES (1, '华北1号仓库');
INSERT INTO `warehouse` VALUES (2, '华北2号仓库');
INSERT INTO `warehouse` VALUES (3, '华南2号仓库');
INSERT INTO `warehouse` VALUES (4, '华北4号仓库');