数据库课程设计——超市进销存系统
本文主要介绍使用MySQL在SQLyog中实现超市进销存系统，进行了需求分析、概念结构设计、逻辑结构设计、数据库设计、系统测试，最终给出了相关的SQL源代码。
文章共16,419字 · 阅读需要大约55分钟
一键AI生成摘要，助你高效阅读
问答
·
前言：
超市进销存系统的设计主要是看你怎么去需求分析，不同分析下的系统可以大不相同。比方说如何处理商品的保质期，对于这个问题，你可以给每件商品贴个单独的标签（假设商店老板很有钱），当然你也可以淡化这个问题，让人工去管理（现实中主要是人工管理的）。超市进销存系统可能无法做到完完全全的使用机器去替代人工，因为这涉及到了商品粒度的划分，太大了人工不好管理，太小了机器不好处理。所以说做好需求分析，做好机器与人工之间的取舍，这样才能设计出合适的超市进销存系统。
本系统使用MySQL在SQLyog上设计。在设计中还参考了银豹收银系统等目前比较成熟的进销存系统。
课题要求：
超市的进销存管理信息系统，首先必须具备的功能是记录仓库存货、销售以及进货情况，通过该系统了解进货渠道、商品单价、数量，库存商品的种类、数量，销售商品种类、价格、数量，以便管理员根据以上信息做出经营管理决策。销售是超市的重要功能之一，收银人员记录客户选购商品的数量，条码，以及总金额，打印商品销售报表，以及每日盘点。查看某一类别，某一商品的库存信息，拥有供货商的详细信息，当发现某一商品销量不好或快到保质期，管理员可将此商品退回供应商。可以查询退货信息，并具有查询供货商信息，添加供应商信息，修改供应商信息。采购部根据库存部提供是信息制定采购计划，提交至超市经理审批。超市经理审批后，如果不通过，驳回计划。审批通过则授权采购部进行商品采购，然后将商品移交给库存部。
SQL文件
未命名/my-database-design-sql - Gitee.com
目录
6.4.2 库存状态函数 FUC_ StockSituation
第一部分：课设报告（部分）
2. 需求分析
2.1 功能框图
超市进销存管理系统主要由采购部、销售部、库存部、系统管理等部分组成，各个部分包含的信息如下：
图2.1 系统功能框图
2.2 功能描述
(1) 采购部的功能
对于采购部，可以根据库存信息制定采购计划，并报超市经理审批；当商品库存为零时，可以进行商品进货的操作；可以查看进货情况。
(2) 销售部的功能
对于销售部，可以进行商品销售操作，销售时可打印销售报表，另外也可以查看每日销售情况以及各个商品的销售情况。
(3) 库存的功能
对于库存部，若部分商品销量不好，或者是快过期，或者是其他商品，可以对这些商品进行退货的操作；可以查看已退货商品的信息；可以查看库存情况。
(4) 系统管理功能
系统管理主要是针对于超市经理，这个部分可以审阅采购计划，展示、管理（查询、添加、修改）供应商信息。
2.3 主要业务流程设计
visio里截的图，大家将就着看吧...
3. 概念结构设计
3.1 实体与属性
该超市进销存管理系统分为三个实体，分为商品（commodity）、供应商（supplier）、系统用户（user）等实体。
商品的实体属性：名称（name）、条码（barcode）、售价（saleprice）、加权后的平均进价（purchaseprice）、库存（stock）、类别（type）、生产日期（mufdate）、保质期（qugdate）；
供应商的实体属性：编号（id）、名称（name）、地址（address）、联系方式（contact）；
系统用户的实体属性：编号（id）、用户名（name）、密码（password）、权限（authority）。
3.2 实体-属性图
3.3 基本 E-R 图
4. 逻辑结构设计
4.1 基本表的设计
表4.1 商品信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | name | varchar | 50 | 否 | 商品名 |
| 2 | barcode | varchar | 20 | 是 | 商品条码 |
| 3 | saleprice | double | 否 | 售价 | |
| 4 | purchaseprice | double | 否 | 加权平均后的进价 | |
| 5 | stock | int | 否 | 库存 | |
| 6 | type | varchar | 50 | 否 | 类型 |
| 7 | mufdate | date | 否 | 生产日期 | |
| 8 | qugdate | int | 否 | 保质期 |
表4.2 供应商信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | id | int | 是 | 供应商编号 | |
| 2 | name | varchar | 50 | 否 | 供应商名称 |
| 3 | address | varchar | 100 | 否 | 供应商地址 |
| 4 | contact | varchar | 50 | 否 | 联系方式 |
表4.3 系统用户信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | id | int | 是 | 用户编号 | |
| 2 | name | varchar | 20 | 否 | 用户名 |
| 3 | password | varchar | 20 | 否 | 密码 |
| 4 | usertype | int | 否 | 用户类别 |
表4.4 进货信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | barcode | varchar | 20 | 否 | 商品条码 |
| 2 | sid | int | 否 | 进货商编号 | |
| 3 | supplynum | int | 否 | 进货数目 | |
| 4 | supplyprice | double | 否 | 单个进价 | |
| 5 | supplytime | double | 否 | 进货时间 |
表4.5 销售信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | orderid | varchar | 20 | 否 | 订单号 |
| 2 | barcode | varchar | 20 | 否 | 商品条码 |
| 3 | time | datetime | 否 | 销售时间 | |
| 4 | num | int | 否 | 销售数目 | |
| 5 | sumsale | double | 否 | 销售总额 | |
| 6 | sumprofit | double | 否 | 销售总利润 | |
| 7 | uid | int | 否 | 销售员编号 |
表4.6 退货信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | time | datetime | 否 | 退货时间 | |
| 2 | barcode | varchar | 20 | 否 | 退货商品条码 |
| 3 | returnnum | int | 否 | 退货数目 | |
| 4 | sid | int | 否 | 供应商编号 | |
| 5 | uid | int | 否 | 操作员编号 |
表4.7 采购计划信息数据表
| 序号 | 数据项名称 | 数据类型 | 范围约束 | 关键数据项 | 含义 |
| 1 | id | int | 否 | 采购计划编号 | |
| 2 | applicantid | int | 否 | 申请人编号 | |
| 3 | time | varchar | 50 | 否 | 申请时间 |
| 4 | content | varchar | 100 | 否 | 申请内容 |
| 5 | isAccepted | int | 否 | 批准标志 | |
| 6 | approverid | int | 否 | 审批人编号 |
4.2 范式分析（部分）
商品信息
关系模式：商品（名称，条码，售价，加权后的平均进价，库存，类别，生产日期，保质期）
主属性：条码
模式判定：商品∈1NF，并且每个非主属性都依赖于条码该候选码，因此商品∈2NF,且每个非主属性都不传递依赖于商品的候选码，所以商品∈3NF。
剩下的略...
6. 数据库的实现
6.1 基本表的创建
详见源代码部分
6.2 基本操作语句的设计
详见源代码部分中的演示代码
6.3 存储过程设计
6.3.1 进货存储过程 PRD_Purchase
为了便于修改库存与进货记录，设计进货存储过程 PRD_Purchase。
向PRD_Purchase传入供应商编号（spid），进货商品条码（barcode），进货数（snum），该供应商的进货价格（Inpprice）。首先判断进价的合法性，如果进价大于等于商品售价，抛出异常，并终止过程。在进价合法的情形下，更新商品信息表（commodity）中的库存（stock）与供货商编号，对进价与该供应商的进货价格做一个加权平均，库存数与供货数分别作为他们的权重，将得到的结果赋给商品信息表中的purchaseprice。最后更新进货信息表（purchase）。
CREATE DEFINER=`root`@`localhost` PROCEDURE `PRD_Purchase`(spid INT, barcode VARCHAR(20), snum INT, Inpprice DOUBLE)
BEGIN
DECLARE salepricetemp DOUBLE;
DECLARE stocktemp INT;
DECLARE purchasepricetemp DOUBLE;
DECLARE ppricetemp DOUBLE;
SELECT saleprice INTO salepricetemp FROM `commodity` WHERE `commodity`.`barcode` = barcode;
SELECT purchaseprice INTO purchasepricetemp FROM `commodity` WHERE `commodity`.`barcode` = barcode;
SELECT `commodity`.`stock` INTO stocktemp FROM `commodity` WHERE `commodity`.`barcode` = barcode;
-- 判断进价合法性，不符合则抛出异常
IF Inpprice >= salepricetemp THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '进价大于售价！';
END IF;
-- 更新 commodity 表
UPDATE commodity SET `commodity`.`stock` = `commodity`.`stock` + snum, sid = spid WHERE `commodity`.`barcode` = barcode;
-- 更新进价，加权平均
SET ppricetemp = (purchasepricetemp * stocktemp + Inpprice * snum) / (stocktemp + snum);
UPDATE commodity SET commodity.`purchaseprice` = ppricetemp WHERE `commodity`.`barcode` = barcode;
-- 更新 purchase 表
INSERT INTO purchase VALUES(barcode, spid, snum, Inpprice, NOW());
END$$
6.3.2 销售存储过程 PRD_Sale
为了便于创建订单、更新销售记录与库存记录，设计销售存储过程 PRD_Sale。
向PRD_Sale传入商品编号（bcode）、销售数目（num）、销售员编号（operatorid），订单创建标记（flag，0为创建新的订单，其他为按时间顺序接着上一份订单）。首先计算该商品的销售额与利润，随后判断购买数的合法性，如果购买数大于库存则抛出异常。在购买数合法的前提下，更新销售记录表（sale），更新商品信息表（commodity）中的库存（stock）。当订单创建标记（flag）为0时，代表创建新订单，为该订单赋予新的订单号（即订单创建时间，以YYYYMMDDHHMMSS格式）；当flag不为0时，先对sale按orderid降序（即订单创建时间），找到最晚创建的时间，把这条销售记录的订单号设为这个时间。
CREATE DEFINER=`root`@`localhost` PROCEDURE `PRD_Sale`(bcode VARCHAR(20), num INT, operatorid INT,flag INT)
BEGIN
DECLARE sumprice DOUBLE;
DECLARE sumprofit DOUBLE;
DECLARE temp DOUBLE;
DECLARE nowtime DATETIME DEFAULT NOW();
DECLARE stocktemp INT;
-- 计算该商品销售额与利润
SELECT saleprice INTO sumprice FROM commodity WHERE bcode = commodity.`barcode`;
SELECT purchaseprice INTO temp FROM commodity WHERE bcode = commodity.`barcode`;
SELECT stock INTO stocktemp FROM commodity WHERE bcode = commodity.`barcode`;
SET sumprofit = (sumprice - temp)*num;
SET sumprice = sumprice * num;
-- 判断购买数的合法性，不符合则抛出异常
IF num > stocktemp THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '购买数大于库存！';
END IF;
-- 更新sale表
INSERT INTO sale(barcode,`time`,num,sumsale,sumprofit,uid) VALUES(bcode,nowtime,num,sumprice,sumprofit,operatorid);
-- 更新commodity表
UPDATE commodity SET stock = stock - num WHERE bcode = commodity.`barcode`;
IF flag = 0 THEN -- 0: 开始订单
UPDATE sale SET orderid = DATE_FORMAT(nowtime,'%Y%m%d%H%i%s') WHERE sale.time = nowtime;
END IF;
-- 其他情况 等于上一个时间
IF flag != 0 THEN
UPDATE sale AS s
SET s.orderid = (
SELECT ss.orderid
FROM (SELECT orderid FROM sale ORDER BY orderid DESC LIMIT 1) AS ss
)
WHERE s.time = nowtime;
END IF;
END$$
6.3.3 退货存储过程 PRD_Return
为了便于更新退货记录与库存记录，设计退货存储过程PRD_Return。
向PRD_Return传入退货商品编号（bcode），退货数目（num），供货商编号（sid），操作员编号（operatorid）。首先判断退货数的合法性，如果退货数大于库存则抛出异常。在退货数目合法的情况下，更新退货记录表（return）与商品信息表（commodity）。
CREATE DEFINER=`root`@`localhost` PROCEDURE `PRD_Return`(bcode VARCHAR(20),num INT, sid INT,operatorid INT)
BEGIN
DECLARE stocktemp INT;
SELECT stock INTO stocktemp FROM commodity WHERE bcode = commodity.`barcode`;
-- 判断退货数的合法性，不符合则抛出异常
IF num > stocktemp THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '购买数大于库存！';
END IF;
-- 更新return表
INSERT INTO `return` VALUES(NOW(), bcode, num, sid, operatorid);
-- 更新commodity表
UPDATE commodity SET stock = stock - num WHERE commodity.`barcode` = bcode;
END$$
6.4 函数设计
6.4.1 订单查询函数 FUC_SearchOrder
为了便于查询订单，设计函数FUC_SearchOrder。该函数只与订单查询视图Order_View结合使用。
这个函数仅仅返回用户变量@searchtime，这个变量需要由用户自行确定。
CREATE DEFINER=`root`@`localhost` FUNCTION `FUC_SearchOrder`() RETURNS DATETIME
RETURN @searchtime$$
6.4.2 库存状态函数 FUC_ StockSituation
为了便于展示库存状态，设计函数FUC_ StockSituation。该函数只与库存信息视图Stock_View结合使用。
这个函数返回库存状态标记。首先判断是否已过期，当现在的时间晚于或等于生产日期加保质期，则说明商品已过期，返回2。若现在的时间晚于或等于生产日期加保质期和的90%，说明该商品的保质期已不足10%，商品即将过期，返回1。当商品销售量少于销售量与库存和的90%，说明商品的销量较少，返回3。其他情况下返回0，表明库存状态正常。
CREATE DEFINER=`root`@`localhost` FUNCTION `FUC_StockSituation`(barcode VARCHAR(20)) RETURNS INT(11)
BEGIN
DECLARE muftime DATE; -- 生产日期
DECLARE deadline DATE; -- 生产日期+保质期
DECLARE qgperiod INT; -- 保质期
DECLARE stk INT; -- 库存
DECLARE snum INT; -- 销售数
SELECT commodity.`mufdate` INTO muftime FROM commodity WHERE commodity.`barcode` = barcode;
SELECT commodity.`qugdate` INTO qgperiod FROM commodity WHERE commodity.`barcode` = barcode;
SELECT commodity.`stock` INTO stk FROM commodity WHERE commodity.`barcode` = barcode;
SELECT SUM(sale.`num`) INTO snum FROM sale WHERE sale.`barcode` = barcode;
-- 已过期
SET deadline = DATE_ADD(muftime, INTERVAL qgperiod DAY); -- INTERVAL X DAY X天时间间隔
IF muftime IS NOT NULL AND CURDATE() >= deadline THEN
RETURN 2;
END IF;
-- 不足10%的保质期，即将过期
SET deadline = DATE_ADD(muftime, INTERVAL FLOOR(qgperiod*0.9) DAY);
IF muftime IS NOT NULL AND CURDATE() > deadline THEN
RETURN 1;
END IF;
-- 销量较少
IF snum < (snum+stk)*0.1 OR snum IS NULL THEN
RETURN 3;
END IF;
RETURN 0;
END$$
6.5 视图设计
详见源代码部分
7. 测试方案
7.1 查询测试
7.1.1 订单查询测试
设定@searchtime的值，并对Order_View进行查找。
1. 设定@searchtime为实际存在的时间
SET @searchtime = '2023-06-27 09:39:00';
SELECT * FROM Order_View;
结果：
图7.1 订单查询测试
2. 设定@searchtime为不存在的时间
执行：
SET @searchtime = '2023-06-28 09:39:00';
SELECT * FROM Order_View;
结果：
图7.2 订单查询测试
3. 设定@searchtime为无效时间
执行：
SET @searchtime = 'aa';
SELECT * FROM Order_View;
结果：
图7.3 订单查询测试
7.1.2 其他查询测试
1. 查询库存信息(全部)
执行：
SELECT * FROM stock_view
结果：
图7.4 查询库存信息
2. 查询某一类别的库存信息
执行：
SELECT * FROM stock_view WHERE `类型` = '休闲食品'
结果：
图7.5 查询某一类别的库存信息
3. 查询某一商品的库存信息
执行：
SELECT * FROM stock_view WHERE `商品名` = '脆脆鲨（巧克力味）'
或
SELECT * FROM stock_view WHERE `商品条码` = '6917878082370'
结果：
图7.6 查询某一商品的库存信息
4. 查询销售记录
执行：
SELECT * FROM sale_view
结果：
图7.7 查询销售记录
5. 查询进货记录
执行：
SELECT * FROM purchase_view
结果：
图7.8 查询进货记录
6. 查询每日盘点
执行：
SELECT * FROM daliysales_view
结果：
图7.9 查询每日盘点
7. 查询退货信息
执行：
SELECT * FROM return_view
结果：
图7.10 查询退货信息
8. 查询供应商信息
执行：
SELECT * FROM supplier
结果：
图7.11 查询供应商信息
9. 查询采购计划
执行：
SELECT * FROM purchaseplan
结果：
图7.12 查询供应商信息
7.2 更新测试
7.2.1 进货测试
1. 对于已经存在的商品进行进货（进价合法）
执行：
CALL PRD_Purchase(3,'4125793569777',100,1.5)
结果：
图7.13 进货测试
查看purchase_view:
图7.14 进货测试
2. 对于已经存在的商品进行进货（进价不合法）
执行：
CALL PRD_Purchase(3,'4125793569777',100,15)
结果：
图7.15 进货测试
3. 对于不存在的商品进行进货
执行：
CALL PRD_Purchase(3,'00000',100,1.5)
结果：
图7.16 进货测试
7.2.2 销售测试
1. 整个销售流程测试
从①开始分步执行：
① CALL PRD_Sale('6917878082370',500,10005,0)
② CALL PRD_Sale('4125793569777',5,10005,1)
③ CALL PRD_Sale('6917878082370',500,10005,1)
④ CALL PRD_Sale('6917878082370',10,10005,0)
结果①：
图7.17 销售测试
查看sale_view:
图7.18 销售测试
结果②：
图7.19 销售测试
查看sale_view:
图7.20 销售测试
结果③：
图7.21 销售测试
结果④：
图7.22 销售测试
查看sale_view:
图7.22 销售测试
查看订单于2023-06-28 16:04创建的订单：
设定@searchtime = '2023-06-28 16:04:00'
对order_view查询：
图7.23 销售测试
2. 测试销售不存在的商品
执行：
CALL PRD_Sale('0000',500,10005,0)
结果：
图7.24 销售测试
7.2.3 退货测试
1. 测试退货存在的商品（退货数合法）
执行：
CALL PRD_Return('4125793569777', 60, 3,10007)
图7.25 退货测试
查看return_view:
图7.26 退货测试
2. 测试退货存在的商品（退货数不合法）
执行：
CALL PRD_Return('4125793569777', 111160, 3,10007)
结果：
图7.27 退货测试
3. 测试退货不存在的商品
执行：
CALL PRD_Return('00000', 10, 3,10007)
结果：
图7.28 退货测试
7.2.4 供应商信息插入与修改测试
此处省略，就是基本的sql操作
7.2.5 采购计划申请与审批测试
此处省略，就是基本的sql操作
第二部分：SQL代码（MySQL）
1. 源代码
在SQLyog中导入数据库即可。
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 8.0.18 : Database - supermarket
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`supermarket` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
/*Table structure for table `commodity` */
DROP TABLE IF EXISTS `commodity`;
CREATE TABLE `commodity` (
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`barcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品条码',
`saleprice` double DEFAULT NULL COMMENT '销售单价',
`purchaseprice` double DEFAULT NULL COMMENT '进货单价',
`stock` int(11) DEFAULT NULL COMMENT '库存',
`type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`mufdate` date DEFAULT NULL COMMENT '生产日期',
`qugdate` int(11) DEFAULT NULL COMMENT '保质期（以天为单位）',
PRIMARY KEY (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `commodity` */
insert into `commodity`(`name`,`barcode`,`saleprice`,`purchaseprice`,`stock`,`type`,`mufdate`,`qugdate`) values ('农夫山泉饮用水 500ml','4125793569718',2,1,499,'酒水饮料','2023-05-05',365),('农夫山泉饮用水 1L','4125793569777',4,1.629805996472663,70,'酒水饮料','2022-06-23',365),('雀巢咖啡-卡布奇诺（10袋装）','4521032888789',24,12.5,14,'休闲食品','2023-01-05',180),('中国李宁男女同款短袖文化衫','4981667326461',258,120,30,'运动服饰',NULL,NULL),('赤兔6PRO男子轻量高回弹竞速跑鞋','5619849467641',599,250,26,'运动服饰',NULL,NULL),('脆脆鲨（巧克力味）','6917878082370',16,8.180392156862744,50,'休闲食品','2023-01-05',365),('G102电竞鼠标','6920377909079',120,56,11,'电脑数码',NULL,NULL),('G502电竞鼠标','6920377909454',559,230,11,'电脑数码',NULL,NULL),('心相印99%消毒湿巾 便携装','6935101400166',7,3,48,'洗漱日化','2023-01-31',730),('蓝月亮洗衣液 1L','6956782620546',14.8,8.8,24,'洗漱日化','2022-11-05',365),('心相印卷纸 10+4卷超值装','7456461326566',21.5,15.5,72,'洗漱日化','2023-05-12',1000);
/*Table structure for table `purchase` */
DROP TABLE IF EXISTS `purchase`;
CREATE TABLE `purchase` (
`barcode` varchar(20) DEFAULT NULL COMMENT '商品条码',
`sid` int(11) DEFAULT NULL COMMENT '进货商编号',
`supplynum` int(11) DEFAULT NULL COMMENT '进货数',
`supplyprice` double DEFAULT NULL COMMENT '进价',
`supplytime` datetime DEFAULT NULL COMMENT '进货时间',
KEY `sid` (`sid`),
KEY `barcode` (`barcode`),
CONSTRAINT `purchase_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `supplier` (`id`),
CONSTRAINT `purchase_ibfk_2` FOREIGN KEY (`barcode`) REFERENCES `commodity` (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `purchase` */
insert into `purchase`(`barcode`,`sid`,`supplynum`,`supplyprice`,`supplytime`) values ('4125793569777',2,20,1.7,'2023-06-27 09:22:10'),('4125793569777',2,20,1.7,'2023-06-27 09:24:10'),('4125793569777',2,20,1.7,'2023-06-27 09:24:26'),('6917878082370',3,100,8,'2023-06-27 09:25:10'),('4125793569777',3,100,1.5,'2023-06-28 15:28:32');
/*Table structure for table `purchaseplan` */
DROP TABLE IF EXISTS `purchaseplan`;
CREATE TABLE `purchaseplan` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`applicantid` int(11) DEFAULT NULL COMMENT '申请人编号',
`time` datetime DEFAULT NULL COMMENT '申请时间',
`content` varchar(100) DEFAULT NULL COMMENT '申请内容',
`isAccepted` int(11) DEFAULT NULL COMMENT '批准标志 0:待审核 1：通过 2：驳回',
`approverid` int(11) DEFAULT NULL COMMENT '审批人编号',
KEY `applicantid` (`applicantid`),
KEY `approverid` (`approverid`),
KEY `id` (`id`),
CONSTRAINT `purchaseplan_ibfk_1` FOREIGN KEY (`applicantid`) REFERENCES `user` (`id`),
CONSTRAINT `purchaseplan_ibfk_2` FOREIGN KEY (`approverid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `purchaseplan` */
insert into `purchaseplan`(`id`,`applicantid`,`time`,`content`,`isAccepted`,`approverid`) values (2,10001,'2023-06-28 16:34:15','申请向脆脆鲨补货200件',1,10000);
/*Table structure for table `return` */
DROP TABLE IF EXISTS `return`;
CREATE TABLE `return` (
`time` datetime DEFAULT NULL COMMENT '退货时间',
`barcode` varchar(20) DEFAULT NULL COMMENT '退货商品条码',
`returnnum` int(11) DEFAULT NULL COMMENT '退货数目',
`sid` int(11) DEFAULT NULL COMMENT '供应商编号',
`uid` int(11) DEFAULT NULL COMMENT '操作员编号',
KEY `uid` (`uid`),
KEY `sid` (`sid`),
KEY `barcode` (`barcode`),
CONSTRAINT `return_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`),
CONSTRAINT `return_ibfk_3` FOREIGN KEY (`sid`) REFERENCES `supplier` (`id`),
CONSTRAINT `return_ibfk_4` FOREIGN KEY (`barcode`) REFERENCES `commodity` (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `return` */
insert into `return`(`time`,`barcode`,`returnnum`,`sid`,`uid`) values ('2023-06-27 09:56:16','4125793569777',60,3,10007),('2023-06-27 09:58:05','4125793569777',60,3,10007),('2023-06-28 16:22:03','4125793569777',60,3,10007);
/*Table structure for table `sale` */
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
`orderid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '订单号',
`barcode` varchar(20) DEFAULT NULL COMMENT '商品条码',
`time` datetime DEFAULT NULL COMMENT '销售时间 YYYY-MM-DD HH:MM:SS',
`num` int(11) DEFAULT NULL COMMENT '销售数目',
`sumsale` double DEFAULT NULL COMMENT '销售额',
`sumprofit` double DEFAULT NULL COMMENT '销售利润',
`uid` int(11) DEFAULT NULL COMMENT '销售员编号',
KEY `uid` (`uid`),
KEY `barcode` (`barcode`),
KEY `orderid` (`orderid`),
CONSTRAINT `sale_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`id`),
CONSTRAINT `sale_ibfk_3` FOREIGN KEY (`barcode`) REFERENCES `commodity` (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `sale` */
insert into `sale`(`orderid`,`barcode`,`time`,`num`,`sumsale`,`sumprofit`,`uid`) values ('20230627093803','4521032888789','2023-06-27 09:38:03',3,72,34.5,10003),('20230627093953','4125793569718','2023-06-27 09:39:53',1,2,1,10004),('20230627093953','6935101400166','2023-06-27 09:40:32',2,14,8,10004),('20230627093953','7456461326566','2023-06-27 09:41:41',3,64.5,18,10004),('20230627094203','6920377909079','2023-06-27 09:42:03',1,120,64,10004),('20230627094203','6917878082370','2023-06-27 10:47:25',500,8000,3909.8039215686276,10005),('20230628160449','6917878082370','2023-06-28 16:04:49',500,8000,3909.8039215686276,10005),('20230628160449','4125793569777','2023-06-28 16:07:27',5,20,11.850970017636687,10005),('20230628160841','6917878082370','2023-06-28 16:08:41',10,160,78.19607843137256,10005);
/*Table structure for table `supplier` */
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`contact` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `supplier` */
insert into `supplier`(`id`,`name`,`address`,`contact`) values (1,'XXX','XXXXX','17896542397'),(2,'YYY','YYYY','19872381329'),(3,'ZZZ','ZZZZZ','12579843654'),(4,'PPP','PPPPPPP','12166463566'),(5,'QQQ','QQQQQ','15684132545'),(6,'MMM','MMMMMMM','12365498745'),(7,'NNN','NNNNNN','1759684562');
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT 'user',
`password` varchar(20) NOT NULL DEFAULT '123456',
`usertype` int(11) NOT NULL DEFAULT '0' COMMENT '0:采购部 1:销售部 2；库存部 3：管理员（经理）',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10009 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `user` */
insert into `user`(`id`,`name`,`password`,`usertype`) values (10000,'经理','admin123',3),(10001,'采购1','123456',0),(10002,'采购2','000000',0),(10003,'销售1','123456',1),(10004,'销售2','000000',1),(10005,'销售3','123456',1),(10006,'销售4','123456',1),(10007,'库存1','123456',2),(10008,'库存2','000000',2);
/* Function structure for function `FUC_SearchOrder` */
/*!50003 DROP FUNCTION IF EXISTS `FUC_SearchOrder` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `FUC_SearchOrder`() RETURNS datetime
return @searchtime */$$
DELIMITER ;
/* Function structure for function `FUC_StockSituation` */
/*!50003 DROP FUNCTION IF EXISTS `FUC_StockSituation` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `FUC_StockSituation`(barcode VARCHAR(20)) RETURNS int(11)
BEGIN
DECLARE muftime DATE; -- 生产日期
DECLARE deadline DATE; -- 生产日期+保质期
DECLARE qgperiod INT; -- 保质期
DECLARE stk INT; -- 库存
DECLARE snum INT; -- 销售数
SELECT commodity.`mufdate` INTO muftime FROM commodity WHERE commodity.`barcode` = barcode;
SELECT commodity.`qugdate` INTO qgperiod FROM commodity WHERE commodity.`barcode` = barcode;
SELECT commodity.`stock` INTO stk FROM commodity WHERE commodity.`barcode` = barcode;
SELECT SUM(sale.`num`) into snum FROM sale WHERE sale.`barcode` = barcode;
-- 已过期
SET deadline = DATE_ADD(muftime, INTERVAL qgperiod DAY); -- INTERVAL X DAY X天时间间隔
IF muftime IS NOT NULL AND CURDATE() >= deadline THEN
RETURN 2;
END IF;
-- 不足10%的保质期，即将过期
SET deadline = DATE_ADD(muftime, INTERVAL FLOOR(qgperiod*0.9) DAY);
IF muftime IS NOT NULL AND CURDATE() > deadline THEN
RETURN 1;
END IF;
-- 销售量较少
IF snum < (snum+stk)*0.1 or snum is null THEN
RETURN 3;
END IF;
RETURN 0;
END */$$
DELIMITER ;
/* Procedure structure for procedure `PRD_Purchase` */
/*!50003 DROP PROCEDURE IF EXISTS `PRD_Purchase` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `PRD_Purchase`(spid int, barcode VARCHAR(20), snum int, Inpprice double)
BEGIN
DECLARE salepricetemp DOUBLE;
DECLARE stocktemp INT;
DECLARE purchasepricetemp DOUBLE;
DECLARE ppricetemp DOUBLE;
SELECT saleprice INTO salepricetemp FROM `commodity` WHERE `commodity`.`barcode` = barcode;
SELECT purchaseprice INTO purchasepricetemp FROM `commodity` WHERE `commodity`.`barcode` = barcode;
SELECT `commodity`.`stock` INTO stocktemp FROM `commodity` WHERE `commodity`.`barcode` = barcode;
-- 判断进价合法性，不符合则抛出异常
IF Inpprice >= salepricetemp THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '进价大于售价！';
END IF;
-- 更新 commodity 表
UPDATE commodity SET `commodity`.`stock` = `commodity`.`stock` + snum WHERE `commodity`.`barcode` = barcode;
-- 更新进价，加权平均
SET ppricetemp = (purchasepricetemp * stocktemp + Inpprice * snum) / (stocktemp + snum);
UPDATE commodity SET commodity.`purchaseprice` = ppricetemp WHERE `commodity`.`barcode` = barcode;
-- 更新 purchase 表
INSERT INTO purchase VALUES(barcode, spid, snum, Inpprice, NOW());
END */$$
DELIMITER ;
/* Procedure structure for procedure `PRD_Return` */
/*!50003 DROP PROCEDURE IF EXISTS `PRD_Return` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `PRD_Return`(bcode varchar(20),num int, sid int,operatorid INT)
BEGIN
declare stocktemp int;
SELECT stock INTO stocktemp FROM commodity WHERE bcode = commodity.`barcode`;
-- 判断退货数的合法性，不符合则抛出异常
IF num > stocktemp THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '购买数大于库存！';
END IF;
-- 更新return表
insert into `return` values(now(), bcode, num, sid, operatorid);
-- 更新commodity表
update commodity set stock = stock - num where commodity.`barcode` = bcode;
END */$$
DELIMITER ;
/* Procedure structure for procedure `PRD_Sale` */
/*!50003 DROP PROCEDURE IF EXISTS `PRD_Sale` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `PRD_Sale`(bcode varchar(20), num int, operatorid int,flag int)
BEGIN
declare sumprice double;
declare sumprofit double;
declare temp double;
DECLARE nowtime datetime default Now();
declare stocktemp int;
-- 计算该商品销售额与利润
select saleprice into sumprice from commodity where bcode = commodity.`barcode`;
select purchaseprice INTO temp FROM commodity WHERE bcode = commodity.`barcode`;
select stock into stocktemp from commodity where bcode = commodity.`barcode`;
set sumprofit = (sumprice - temp)*num;
set sumprice = sumprice * num;
-- 判断购买数的合法性，不符合则抛出异常
IF num > stocktemp THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '购买数大于库存！';
END IF;
-- 更新sale表
insert into sale(barcode,`time`,num,sumsale,sumprofit,uid) values(bcode,nowtime,num,sumprice,sumprofit,operatorid);
-- 更新commodity表
update commodity set stock = stock - num where bcode = commodity.`barcode`;
if flag = 0 then -- 0: 开始订单
UPdate sale set orderid = DAte_format(nowtime,'%Y%m%d%H%i%s') where sale.time = nowtime;
end if;
-- 其他情况 等于上一个时间
if flag != 0 then
UPDATE sale AS s
SET s.orderid = (
SELECT ss.orderid
FROM (SELECT orderid FROM sale ORDER BY orderid DESC LIMIT 1) AS ss
)
WHERE s.time = nowtime;
end if;
END */$$
DELIMITER ;
/*Table structure for table `daliysales_view` */
DROP TABLE IF EXISTS `daliysales_view`;
/*!50001 DROP VIEW IF EXISTS `daliysales_view` */;
/*!50001 DROP TABLE IF EXISTS `daliysales_view` */;
/*!50001 CREATE TABLE `daliysales_view`(
`日期` date ,
`销售数` decimal(32,0) ,
`销售额` varchar(62) ,
`利润` varchar(62)
)*/;
/*Table structure for table `order_view` */
DROP TABLE IF EXISTS `order_view`;
/*!50001 DROP VIEW IF EXISTS `order_view` */;
/*!50001 DROP TABLE IF EXISTS `order_view` */;
/*!50001 CREATE TABLE `order_view`(
`订单号` varchar(20) ,
`商品条码` varchar(20) ,
`商品名` varchar(50) ,
`销售时间` datetime ,
`数目` int(11) ,
`单价` double ,
`销售员编号` int(11)
)*/;
/*Table structure for table `purchase_view` */
DROP TABLE IF EXISTS `purchase_view`;
/*!50001 DROP VIEW IF EXISTS `purchase_view` */;
/*!50001 DROP TABLE IF EXISTS `purchase_view` */;
/*!50001 CREATE TABLE `purchase_view`(
`商品条码` varchar(20) ,
`商品名` varchar(50) ,
`供应单价` varchar(62) ,
`商品类型` varchar(50) ,
`供应商编号` int(11) ,
`供应商` varchar(50) ,
`供应量` int(11) ,
`供应时间` datetime
)*/;
/*Table structure for table `return_view` */
DROP TABLE IF EXISTS `return_view`;
/*!50001 DROP VIEW IF EXISTS `return_view` */;
/*!50001 DROP TABLE IF EXISTS `return_view` */;
/*!50001 CREATE TABLE `return_view`(
`退货时间` datetime ,
`退货商品条码` varchar(20) ,
`商品名` varchar(50) ,
`退货数目` int(11) ,
`供应商编号` int(11) ,
`供应商` varchar(50) ,
`执行用户` int(11)
)*/;
/*Table structure for table `sale_view` */
DROP TABLE IF EXISTS `sale_view`;
/*!50001 DROP VIEW IF EXISTS `sale_view` */;
/*!50001 DROP TABLE IF EXISTS `sale_view` */;
/*!50001 CREATE TABLE `sale_view`(
`商品条码` varchar(20) ,
`商品名` varchar(50) ,
`单价` varchar(62) ,
`数目` decimal(32,0) ,
`类型` varchar(50)
)*/;
/*Table structure for table `stock_view` */
DROP TABLE IF EXISTS `stock_view`;
/*!50001 DROP VIEW IF EXISTS `stock_view` */;
/*!50001 DROP TABLE IF EXISTS `stock_view` */;
/*!50001 CREATE TABLE `stock_view`(
`商品条码` varchar(20) ,
`商品名` varchar(50) ,
`单价` varchar(62) ,
`生产日期` date ,
`保质期` int(11) ,
`库存` int(11) ,
`类型` varchar(50) ,
`商品状态` varchar(4)
)*/;
/*View structure for view daliysales_view */
/*!50001 DROP TABLE IF EXISTS `daliysales_view` */;
/*!50001 DROP VIEW IF EXISTS `daliysales_view` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `daliysales_view` AS select cast(`sale`.`time` as date) AS `日期`,sum(`sale`.`num`) AS `销售数`,format(sum(`sale`.`sumsale`),2) AS `销售额`,format(sum(`sale`.`sumprofit`),2) AS `利润` from `sale` group by `日期` */;
/*View structure for view order_view */
/*!50001 DROP TABLE IF EXISTS `order_view` */;
/*!50001 DROP VIEW IF EXISTS `order_view` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `order_view` AS select `sale`.`orderid` AS `订单号`,`sale`.`barcode` AS `商品条码`,`commodity`.`name` AS `商品名`,`sale`.`time` AS `销售时间`,`sale`.`num` AS `数目`,`commodity`.`saleprice` AS `单价`,`sale`.`uid` AS `销售员编号` from (`sale` join `commodity`) where ((left(`sale`.`orderid`,12) = convert(date_format(`FUC_SearchOrder`(),'%Y%m%d%H%i') using utf8mb4)) and (`sale`.`barcode` = `commodity`.`barcode`)) */;
/*View structure for view purchase_view */
/*!50001 DROP TABLE IF EXISTS `purchase_view` */;
/*!50001 DROP VIEW IF EXISTS `purchase_view` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `purchase_view` AS select `commodity`.`barcode` AS `商品条码`,`commodity`.`name` AS `商品名`,format(`purchase`.`supplyprice`,2) AS `供应单价`,`commodity`.`type` AS `商品类型`,`supplier`.`id` AS `供应商编号`,`supplier`.`name` AS `供应商`,`purchase`.`supplynum` AS `供应量`,`purchase`.`supplytime` AS `供应时间` from ((`commodity` join `supplier`) join `purchase`) where ((`purchase`.`sid` = `supplier`.`id`) and (`purchase`.`barcode` = `commodity`.`barcode`)) */;
/*View structure for view return_view */
/*!50001 DROP TABLE IF EXISTS `return_view` */;
/*!50001 DROP VIEW IF EXISTS `return_view` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `return_view` AS select `return`.`time` AS `退货时间`,`return`.`barcode` AS `退货商品条码`,`commodity`.`name` AS `商品名`,`return`.`returnnum` AS `退货数目`,`supplier`.`id` AS `供应商编号`,`supplier`.`name` AS `供应商`,`return`.`uid` AS `执行用户` from ((`return` join `commodity`) join `supplier`) where ((`return`.`barcode` = `commodity`.`barcode`) and (`return`.`sid` = `supplier`.`id`)) */;
/*View structure for view sale_view */
/*!50001 DROP TABLE IF EXISTS `sale_view` */;
/*!50001 DROP VIEW IF EXISTS `sale_view` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sale_view` AS select `sale`.`barcode` AS `商品条码`,`commodity`.`name` AS `商品名`,format(`commodity`.`saleprice`,2) AS `单价`,sum(`sale`.`num`) AS `数目`,`commodity`.`type` AS `类型` from (`sale` join `commodity`) where (`sale`.`barcode` = `commodity`.`barcode`) group by `sale`.`barcode` */;
/*View structure for view stock_view */
/*!50001 DROP TABLE IF EXISTS `stock_view` */;
/*!50001 DROP VIEW IF EXISTS `stock_view` */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stock_view` AS select `commodity`.`barcode` AS `商品条码`,`commodity`.`name` AS `商品名`,format(`commodity`.`saleprice`,2) AS `单价`,`commodity`.`mufdate` AS `生产日期`,`commodity`.`qugdate` AS `保质期`,`commodity`.`stock` AS `库存`,`commodity`.`type` AS `类型`,(case when (`FUC_StockSituation`(`commodity`.`barcode`) = 1) then '即将过期' when (`FUC_StockSituation`(`commodity`.`barcode`) = 2) then '已过期' when (`FUC_StockSituation`(`commodity`.`barcode`) = 3) then '销量较少' else '正常' end) AS `商品状态` from `commodity` */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2. 演示代码
-- 进货演示 PRD_Pruchase([进货商编号],[进货商品条码],[进货数],[进价])
CALL PRD_Purchase(3,'4125793569777',1000,1.5)
-- 销售演示 PRD_Sale([商品条码],[购买数],[销售员编号],[订单创建标记(创建为0，其余情况不为0)])
CALL PRD_Sale('6917878082370',500,10005,0)
-- 退货演示 PRD_Return([商品条码],[退货数],[进货商编号],[操作员编号])
CALL PRD_Return('4125793569777', 60, 3,10007)
-- 供应商信息的添加
INSERT INTO supplier(`name`,address,contact) VALUES('RRRR','RRRRR','1759684562');
-- 供应商信息的修改
UPDATE supplier SET contact = '12365498745' WHERE id = 6;
-- 采购计划创建
INSERT INTO purchaseplan(applicantid, `time`, content, isAccepted, approverid) VALUES(10001,NOW(),'申请向脆脆鲨补货200件',0,10000);
-- 采购计划审阅 0: 待审阅 1：通过 2：驳回
UPDATE purchaseplan SET isAccepted = 1 WHERE `id` = 1;
-- ---------------------------------------------------------------------------------------------------------
-- 查询库存信息(全部)
SELECT * FROM stock_view
-- 查询某一类别的库存信息
SELECT * FROM stock_view WHERE `类型` = '休闲食品'
-- 查询某一商品的库存信息
SELECT * FROM stock_view WHERE `商品名` = '脆脆鲨（巧克力味）'
-- 或
SELECT * FROM stock_view WHERE `商品条码` = '4521032888789'
-- 查询销售记录
SELECT * FROM sale_view
-- 查询进货记录
SELECT * FROM purchase_view
-- 查询订单（需设定创建时间，精确到分即可）
SET @searchtime = '2023-06-27 09:39:00';
SELECT * FROM Order_View;
-- 查询每日盘点
SELECT * FROM daliysales_view
-- 查询退货信息
SELECT * FROM return_view
-- 查询供应商信息
SELECT * FROM supplier
-- 查询采购计划
SELECT * FROM purchaseplan
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
13
0- 0
已为社区贡献2条内容
所有评论(0)