数据库课程设计——基于JavaEE的企业进销存系统（附Java源码与课程设计报告）

以下是我在优快云发布的文章好吧

数据库课程设计——某工厂的物料管理系统（附Java源码与课程设计报告）
数据库课程设计——某商店进销存管理系统（附Java源码与课程设计报告）
数据库课程设计——某煤气公司送气管理系统（附课设报告）
数据库课程设计——基于JavaEE的企业进销存系统（附Java源码与课程设计报告）
Java课程设计——哈夫曼编码译码系统的Java语言实现
C语言课程设计——班级成绩管理系统（附课设报告）
C语言课程设计——排班系统 DFS解决（附课设报告）

O、1277750893 O、

题目：

模拟企业实际进销存（进货、销售、存储）情况。
商品信息：商品编号，生产时间，进货时间，保质期等。
进货信息：生产厂家，数量，进价等情况。
存储信息：总量，销售量，存储时间，保质期等。
销售小票信息：货号、货名、销售价格、数量、销售总金额、实收金额、找零、积分等。创建表间关系。

系统功能分析：

界面展示：

课程设计报告：

数据字典：

数据库模型：

数据库源码

/*
 Navicat Premium Data Transfer

 Source Server         : mysql57
 Source Server Type    : MySQL
 Source Server Version : 50735
 Source Host           : localhost:13306
 Source Schema         : shop_psi

 Target Server Type    : MySQL
 Target Server Version : 50735
 File Encoding         : 65001

 Date: 09/06/2022 18:58:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for psi_bill
-- ----------------------------
DROP TABLE IF EXISTS `psi_bill`;
CREATE TABLE `psi_bill`  (
  `bill_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '小票单号',
  `staff_id` int(11) NULL DEFAULT NULL COMMENT '员工编号',
  `vip_id` int(11) NULL DEFAULT NULL COMMENT '会员编号',
  `bill_total` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '销售总额',
  `bill_paid` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '实收金额',
  `bill_change` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '找零',
  `bill_date` date NULL DEFAULT NULL COMMENT '日期',
  `bill_socre` int(11) NULL DEFAULT 0 COMMENT '积分',
  PRIMARY KEY (`bill_id`) USING BTREE,
  INDEX `f1`(`staff_id`) USING BTREE,
  INDEX `f2`(`vip_id`) USING BTREE,
  CONSTRAINT `f1` FOREIGN KEY (`staff_id`) REFERENCES `psi_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f2` FOREIGN KEY (`vip_id`) REFERENCES `psi_vip` (`vip_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_goods
-- ----------------------------
DROP TABLE IF EXISTS `psi_goods`;
CREATE TABLE `psi_goods`  (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品编号',
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `goods_cat` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类别',
  `goods_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单位',
  `goods_mfd` date NULL DEFAULT NULL COMMENT '生产日期',
  `goods_exp` date NULL DEFAULT NULL COMMENT '保质日期',
  `goods_pd` date NULL DEFAULT NULL COMMENT '进货日期',
  `goods_pprice` decimal(10, 2) NULL DEFAULT NULL COMMENT '进价',
  `goods_stock` int(11) NOT NULL DEFAULT 0 COMMENT '总库存量',
  `goods_insuf` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '商品库存不足' COMMENT '库存不足预警',
  `goods_mature` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '商品未临期' COMMENT '临期预警',
  PRIMARY KEY (`goods_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_goods_whse
-- ----------------------------
DROP TABLE IF EXISTS `psi_goods_whse`;
CREATE TABLE `psi_goods_whse`  (
  `goods_id` int(11) NOT NULL COMMENT '商品编号',
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `whse_id` int(11) NOT NULL COMMENT '仓库编号',
  `whse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
  `stock` int(11) NULL DEFAULT 0 COMMENT '存储数量',
  PRIMARY KEY (`goods_id`, `whse_id`) USING BTREE,
  INDEX `f6`(`whse_id`) USING BTREE,
  CONSTRAINT `f5` FOREIGN KEY (`goods_id`) REFERENCES `psi_goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f6` FOREIGN KEY (`whse_id`) REFERENCES `psi_whse` (`whse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_instore
-- ----------------------------
DROP TABLE IF EXISTS `psi_instore`;
CREATE TABLE `psi_instore`  (
  `supplier_id` int(11) NOT NULL COMMENT '供应商编号',
  `supplier_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '供应商名称',
  `goods_id` int(11) NOT NULL COMMENT '商品编号',
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `whse_id` int(11) NOT NULL COMMENT '仓库编号',
  `in_num` int(11) NOT NULL DEFAULT 0 COMMENT '入库数量',
  `in_price` decimal(10, 2) NOT NULL COMMENT '入库单价',
  `instore_date` date NULL DEFAULT NULL COMMENT '入库时间',
  INDEX `f7`(`supplier_id`) USING BTREE,
  INDEX `f8`(`goods_id`) USING BTREE,
  INDEX `f9`(`whse_id`) USING BTREE,
  CONSTRAINT `f7` FOREIGN KEY (`supplier_id`) REFERENCES `psi_supplier` (`supplier_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f8` FOREIGN KEY (`goods_id`) REFERENCES `psi_goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f9` FOREIGN KEY (`whse_id`) REFERENCES `psi_whse` (`whse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_outstore
-- ----------------------------
DROP TABLE IF EXISTS `psi_outstore`;
CREATE TABLE `psi_outstore`  (
  `bill_id` int(11) NOT NULL COMMENT '小票单号',
  `goods_id` int(11) NOT NULL COMMENT '商品编号',
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `whse_id` int(11) NOT NULL COMMENT '仓库编号',
  `outstore_num` int(11) NOT NULL COMMENT '销售出库数量',
  `outstore_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '销售价格',
  `outstore_date` datetime(0) NULL DEFAULT NULL COMMENT '销售出库日期',
  INDEX `f10`(`bill_id`) USING BTREE,
  INDEX `f11`(`goods_id`) USING BTREE,
  INDEX `f12`(`whse_id`) USING BTREE,
  CONSTRAINT `f10` FOREIGN KEY (`bill_id`) REFERENCES `psi_bill` (`bill_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f11` FOREIGN KEY (`goods_id`) REFERENCES `psi_goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f12` FOREIGN KEY (`whse_id`) REFERENCES `psi_whse` (`whse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_re
-- ----------------------------
DROP TABLE IF EXISTS `psi_re`;
CREATE TABLE `psi_re`  (
  `re_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '退换货记录编号',
  `re_rtn_exc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '退/换货',
  `bill_id` int(11) NOT NULL COMMENT '小票单号',
  `re_num` int(11) NOT NULL COMMENT '退换数量',
  `goods_id` int(11) NOT NULL COMMENT '商品编号',
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `whse_id` int(11) NULL DEFAULT NULL COMMENT '仓库编号',
  `re_date` date NULL DEFAULT NULL COMMENT '退换日期',
  `re_total` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '退款金额',
  PRIMARY KEY (`re_id`) USING BTREE,
  INDEX `f13`(`bill_id`) USING BTREE,
  INDEX `f14`(`goods_id`) USING BTREE,
  INDEX `f15`(`whse_id`) USING BTREE,
  CONSTRAINT `f13` FOREIGN KEY (`bill_id`) REFERENCES `psi_bill` (`bill_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f14` FOREIGN KEY (`goods_id`) REFERENCES `psi_goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f15` FOREIGN KEY (`whse_id`) REFERENCES `psi_whse` (`whse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_staff
-- ----------------------------
DROP TABLE IF EXISTS `psi_staff`;
CREATE TABLE `psi_staff`  (
  `staff_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `staff_bd` date NULL DEFAULT NULL COMMENT '出生日期',
  `staff_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系方式',
  `staff_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '家庭住址',
  `staff_sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`staff_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_supplier
-- ----------------------------
DROP TABLE IF EXISTS `psi_supplier`;
CREATE TABLE `psi_supplier`  (
  `supplier_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '供货商编号',
  `supplier_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `supplier_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系方式',
  `supplier_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所在地',
  PRIMARY KEY (`supplier_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_trf
-- ----------------------------
DROP TABLE IF EXISTS `psi_trf`;
CREATE TABLE `psi_trf`  (
  `goods_id` int(11) NOT NULL COMMENT '商品编号',
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `from_whse_id` int(11) NOT NULL COMMENT '转出仓库编号',
  `to_whse_id` int(11) NOT NULL COMMENT '转入仓库编号',
  `trf_num` int(11) NOT NULL COMMENT '转移商品数量',
  `trf_date` date NULL DEFAULT NULL COMMENT '转移时间',
  INDEX `f16`(`goods_id`) USING BTREE,
  INDEX `f17`(`from_whse_id`) USING BTREE,
  INDEX `f18`(`to_whse_id`) USING BTREE,
  CONSTRAINT `f16` FOREIGN KEY (`goods_id`) REFERENCES `psi_goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f17` FOREIGN KEY (`from_whse_id`) REFERENCES `psi_whse` (`whse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `f18` FOREIGN KEY (`to_whse_id`) REFERENCES `psi_whse` (`whse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_vip
-- ----------------------------
DROP TABLE IF EXISTS `psi_vip`;
CREATE TABLE `psi_vip`  (
  `vip_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '会员编号',
  `vip_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `vip_sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `vip_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '家庭住址',
  `vip_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系方式',
  `vip_score` int(11) NULL DEFAULT 0 COMMENT '积分',
  PRIMARY KEY (`vip_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for psi_whse
-- ----------------------------
DROP TABLE IF EXISTS `psi_whse`;
CREATE TABLE `psi_whse`  (
  `whse_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '仓库编号',
  `whse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
  `whse_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库联系方式',
  `whse_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库地址',
  PRIMARY KEY (`whse_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Procedure structure for gagaga
-- ----------------------------
DROP PROCEDURE IF EXISTS `gagaga`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `gagaga`(
	in begindate date,
	in enddate date
)
begin
	select psi_goods.goods_id as '商品编号', sum(in_num) as '进货量', sum(outstore_num) as '销售量'
	from psi_goods, psi_instore, psi_outstore	
	where instore_date >= begindate and instore_date <= enddate and outstore_date >= begindate and outstore_date <= enddate and psi_goods.goods_id = psi_instore.goods_id and psi_goods.goods_id = psi_outstore.goods_id
	group by psi_goods.goods_id;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_bill
-- ----------------------------
DROP TRIGGER IF EXISTS `t12`;
delimiter ;;
CREATE TRIGGER `t12` BEFORE UPDATE ON `psi_bill` FOR EACH ROW begin
	set new.bill_socre = new.bill_total;
	update psi_vip set vip_score = vip_score + new.bill_total where vip_id = new.vip_id;
	set new.bill_change = new.bill_paid - new.bill_total;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_goods
-- ----------------------------
DROP TRIGGER IF EXISTS `t10`;
delimiter ;;
CREATE TRIGGER `t10` BEFORE INSERT ON `psi_goods` FOR EACH ROW begin
	if new.goods_stock > 50 then
		set new.goods_insuf = '商品库存充足';
	else
		set new.goods_insuf = '商品库存不足';
	end if;
	
	if now() > new.goods_exp then
		set new.goods_mature = '商品临期';
	else
		set new.goods_insuf = '商品未临期';
	end if;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_goods
-- ----------------------------
DROP TRIGGER IF EXISTS `t9`;
delimiter ;;
CREATE TRIGGER `t9` BEFORE UPDATE ON `psi_goods` FOR EACH ROW begin
	if new.goods_stock > 50 then
		set new.goods_insuf = '商品库存充足';
	else
		set new.goods_insuf = '商品库存不足';
	end if;
	
	if now() > new.goods_exp then
		set new.goods_mature = '商品临期';
	else
		set new.goods_insuf = '商品未临期';
	end if;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_goods_whse
-- ----------------------------
DROP TRIGGER IF EXISTS `t2`;
delimiter ;;
CREATE TRIGGER `t2` BEFORE INSERT ON `psi_goods_whse` FOR EACH ROW begin
	set new.goods_name = (select goods_name from psi_goods where goods_id = new.goods_id);
	set new.whse_name = (select whse_name from psi_whse where whse_id = new.whse_id);
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_goods_whse
-- ----------------------------
DROP TRIGGER IF EXISTS `t8`;
delimiter ;;
CREATE TRIGGER `t8` AFTER UPDATE ON `psi_goods_whse` FOR EACH ROW begin
	update psi_goods set goods_stock = goods_stock + (new.stock - old.stock) where goods_id = new.goods_id;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_instore
-- ----------------------------
DROP TRIGGER IF EXISTS `t3`;
delimiter ;;
CREATE TRIGGER `t3` BEFORE INSERT ON `psi_instore` FOR EACH ROW begin
	set new.goods_name = (select goods_name from psi_goods where goods_id = new.goods_id);
	set new.supplier_name = (select supplier_name from psi_supplier where supplier_id = new.supplier_id);
	set new.instore_date = now();
	update psi_goods set goods_pd = now() where goods_id = new.goods_id;
	update psi_goods set goods_pprice = new.in_price where goods_id = new.goods_id;
	if (select goods_id from psi_goods_whse where goods_id = new.goods_id and whse_id = new.whse_id) is null then
		insert into psi_goods_whse(goods_id, whse_id, stock) VALUES(new.goods_id, new.whse_id, new.in_num);
	else
		update psi_goods_whse set stock = stock + new.in_num where goods_id = new.goods_id and whse_id = new.whse_id;
	end if;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_outstore
-- ----------------------------
DROP TRIGGER IF EXISTS `t4`;
delimiter ;;
CREATE TRIGGER `t4` BEFORE INSERT ON `psi_outstore` FOR EACH ROW begin
	set new.goods_name = (select goods_name from psi_goods where goods_id = new.goods_id);
	set new.outstore_price = (select goods_pprice from psi_goods where goods_id = new.goods_id) * 1.2;
	set new.outstore_date = (select bill_date from psi_bill where bill_id = new.bill_id);
	update psi_bill set bill_total = bill_total + new.outstore_price * new.outstore_num where bill_id = new.bill_id;
	set @selected_whse_id = (select whse_id from psi_goods_whse where goods_id = new.goods_id and stock > new.outstore_num limit 1);
	update psi_goods_whse set stock = stock - new.outstore_num where goods_id = new.goods_id and whse_id = @selected_whse_id;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_re
-- ----------------------------
DROP TRIGGER IF EXISTS `t5`;
delimiter ;;
CREATE TRIGGER `t5` BEFORE INSERT ON `psi_re` FOR EACH ROW begin
	set new.goods_name = (select goods_name from psi_goods where goods_id = new.goods_id);
	set new.re_date = now();
	if new.re_rtn_exc = '1' then
		update psi_goods_whse set stock = stock + new.re_num where goods_id = new.goods_id and whse_id = new.whse_id;
		set new.re_total = (new.re_num * (select goods_pprice from psi_goods where goods_id = new.goods_id) * 1.2);
	end if;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table psi_trf
-- ----------------------------
DROP TRIGGER IF EXISTS `t6`;
delimiter ;;
CREATE TRIGGER `t6` BEFORE INSERT ON `psi_trf` FOR EACH ROW begin
	set new.goods_name = (select goods_name from psi_goods where goods_id = new.goods_id);
	set new.trf_date = now();
	update psi_goods_whse set stock = stock + new.trf_num where goods_id = new.goods_id and whse_id = new.to_whse_id;
	update psi_goods_whse set stock = stock - new.trf_num where goods_id = new.goods_id and whse_id = new.from_whse_id;
end
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;