|
以下是我在CSDN发布的文章好吧
数据库课程设计——某工厂的物料管理系统（附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 = begindate and outstore_date 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;
|