数据库-MySQL 实战项目——学校超市管理系统数据库设计与实现（附源码）
一、前言
需求：
商品类别管理（如：饮料、日用、零食、文具等）商品信息管理供货商管理进货库存销售（请考虑教师刷卡消费情况，教师卡及金额管理）备注：
商品类别包含下列属性：类别编号，类别名称
商品信息包含下列属性：商品编号，商品名字，商品单价
供货商包含下列属性：供货商编号，联系人姓名，联系人电话，地址
进货记录：进货编号，进货数量，进货单价，进货日期
库存信息: 库存编号，库存数量
教师卡: 教师卡号，教师姓名，账户余额
销售记录：销售编号，销售数量，销售总价，销售日期
所需环境
MySQL可视化工具：navicat； 数据库：MySql 5.7/8.0等版本均可；二、E-R图
三、关系模式
商品类别: (类别编号, 类别名称)
商品信息：(商品编号，商品名字，商品单价，类别编号，供货商编号)
供货商：(供货商编号，联系人姓名，联系人电话，地址)
进货记录: (进货编号，进货数量，进货单价，进货日期，商品编号)
库存信息: (库存编号，库存数量，商品编号)
教师卡: (教师卡号，教师姓名，账户余额)
销售记录：（销售编号，销售数量，销售总价，销售日期，商品编号，教师卡号）
四、数据表的设计
表1 商品类别 表2 商品信息字段
数据类型
长度
备注
商品编号
varchar
50
主键
商品名字
varchar
50
Not null
商品单价
varchar
50
Not null
类别编号
Varchar
50
外键
供货商编号
Varchar
50
外键
字段
数据类型
长度
备注
进货编号
varchar
50
主键
进货数量
varchar
50
Not null
进货单价
varchar
50
进货日期
date
商品编号
varchar
50
外键
供货商编号
varchar
50
外键
字段
数据类型
长度
备注
销售编号
varchar
50
主键
销售数量
varchar
50
销售总价
varchar
50
Not null
销售日期
date
商品编号
varchar
50
外键
教师卡号
varchar
50
外键
五、创建数据库
创建各实体表：
-- 在新建表前选择数据库
CREATE TABLE 商品类别 (
类别编号 VARCHAR(50) NOT NULL PRIMARY KEY,
类别名称 VARCHAR(50) NOT NULL
);
CREATE TABLE 供货商 (
供货商编号 VARCHAR(50) NOT NULL PRIMARY KEY,
联系人姓名 VARCHAR(50) NOT NULL,
联系人电话 VARCHAR(50) NOT NULL,
);
CREATE TABLE 商品信息 (
商品编号 VARCHAR(50) NOT NULL PRIMARY KEY,
商品名字 VARCHAR(50) NOT NULL,
商品单价 VARCHAR(50) NOT NULL,
类别编号 VARCHAR(50) NOT NULL,
供货商编号 VARCHAR(50) NOT NULL,
FOREIGN KEY (类别编号) REFERENCES 商品类别(类别编号),
FOREIGN KEY (供货商编号) REFERENCES 供货商(供货商编号)
);
CREATE TABLE 进货记录 (
进货编号 VARCHAR(50) NOT NULL PRIMARY KEY,
进货数量 VARCHAR(50) NOT NULL,
进货单价 VARCHAR(50) NOT NULL,
进货日期 DATE NOT NULL,
商品编号 VARCHAR(50) NOT NULL,
FOREIGN KEY (商品编号) REFERENCES 商品信息(商品编号)
);
CREATE TABLE 库存信息 (
库存编号 VARCHAR(50) NOT NULL PRIMARY KEY,
库存数量 VARCHAR(50) NOT NULL,
商品编号 VARCHAR(50) NOT NULL,
FOREIGN KEY (商品编号) REFERENCES 商品信息(商品编号)
);
CREATE TABLE 教师卡 (
教师卡号 VARCHAR(50) NOT NULL PRIMARY KEY,
教师姓名 VARCHAR(50) NOT NULL,
账户余额 VARCHAR(50) NOT NULL
);
CREATE TABLE 销售记录 (
销售编号 VARCHAR(50) NOT NULL PRIMARY KEY,
销售数量 VARCHAR(50) NOT NULL,
销售总价 VARCHAR(50) NOT NULL,
销售日期 DATE NOT NULL,
商品编号 VARCHAR(50) NOT NULL,
教师卡号 VARCHAR(50) NOT NULL,
FOREIGN KEY (商品编号) REFERENCES 商品信息(商品编号),
FOREIGN KEY (教师卡号) REFERENCES 教师卡(教师卡号)
);
插入数据：
1.商品类别INSERT INTO 商品类别 VALUES (N002, 食品饮料);
INSERT INTO 商品类别 VALUES (N003, 家居用品);
INSERT INTO 商品类别 VALUES (N004, 服饰箱包);
INSERT INTO 商品类别 VALUES (N005, 美容保健);
INSERT INTO 商品类别 VALUES (N006, 母婴用品);
INSERT INTO 商品类别 VALUES (N007, 运动健身);
INSERT INTO 商品类别 VALUES (N008, 图书音像);
INSERT INTO 商品类别 VALUES (N009, 汽车用品);
INSERT INTO 商品类别 VALUES (N010, 珠宝饰品);
INSERT INTO 供货商 VALUES (G001, 张三, 13888888888, 北京市朝阳区朝阳路1号);
INSERT INTO 供货商 VALUES (G002, 李四, 13999999999, 上海市浦东新区人民路2号);
INSERT INTO 供货商 VALUES (G003, 王五, 13666666666, 广州市天河区天河路3号);
INSERT INTO 供货商 VALUES (G004, 赵六, 13777777777, 深圳市福田区福田街4号);
INSERT INTO 供货商 VALUES (G005, 钱七, 13666666666, 成都市高新区高科路5号);
INSERT INTO 供货商 VALUES (G006, 马八, 13555555555, 重庆市渝北区渝北路6号);
INSERT INTO 供货商 VALUES (G007, 刘九, 13777777777, 南京市鼓楼区鼓楼路7号);
INSERT INTO 供货商 VALUES (G008, 孙十, 13333333333, 杭州市滨江区江南路8号);
INSERT INTO 供货商 VALUES (G009, 周十一, 13111111111, 武汉市江汉区江汉路9号);
INSERT INTO 供货商 VALUES (G010, 吴十二, 13000000000, 西安市雁塔区丈八路10);
INSERT INTO 商品信息 VALUES (S002, 可口可乐, 3, N002, G002);
INSERT INTO 商品信息 VALUES (S003, 毛巾, 9, N003, G003);
INSERT INTO 商品信息 VALUES (S004, Adidas 运动鞋, 599, N007, G004);
INSERT INTO 商品信息 VALUES (S005, SK-II 面霜, 1500, N005, G005);
INSERT INTO 商品信息 VALUES (S006, 儿童玩具, 99, N006, G006);
INSERT INTO 商品信息 VALUES (S007, 健身器材, 1999, N007, G007);
INSERT INTO 商品信息 VALUES (S008, 平凡的世界, 89, N008, G008);
INSERT INTO 商品信息 VALUES (S009, 汽车脚垫, 199, N009, G009);
INSERT INTO 商品信息 VALUES (S010, 珍珠项链, 9999, N010, G010);
INSERT INTO 进货记录 VALUES (JH001, 100, 2, 2023-05-01, S002);
INSERT INTO 进货记录 VALUES (JH002, 50, 1300, 2023-05-02, S005);
INSERT INTO 进货记录 VALUES (JH003, 200, 5, 2023-05-03, S003);
INSERT INTO 进货记录 VALUES (JH004, 50, 399, 2023-05-04, S004) ;
INSERT INTO 进货记录 VALUES (JH005, 500, 2, 2023-05-05, S002);
INSERT INTO 进货记录 VALUES (JH006, 1000, 150, 2023-05-06, S009);
INSERT INTO 进货记录 VALUES (JH007, 20, 5999, 2023-05-07, S001);
INSERT INTO 进货记录 VALUES (JH008, 10, 599, 2023-05-08, S004);
INSERT INTO 进货记录 VALUES (JH009, 200, 899, 2023-05-09, S007);
INSERT INTO 进货记录 VALUES (JH010, 200, 299, 2023-05-10, S004);
INSERT INTO 库存信息 VALUES (K001, 200, S002);
INSERT INTO 库存信息 VALUES (K002, 500, S005);
INSERT INTO 库存信息 VALUES (K003, 1000, S003);
INSERT INTO 库存信息 VALUES (K004, 50, S004);
INSERT INTO 库存信息 VALUES (K005, 1000, S002);
INSERT INTO 库存信息 VALUES (K006, 2000, S009);
INSERT INTO 库存信息 VALUES (K007, 30, S001);
INSERT INTO 库存信息 VALUES (K008, 20, S004);
INSERT INTO 库存信息 VALUES (K009, 500, S007);
INSERT INTO 库存信息 VALUES (K010, 200, S004);
INSERT INTO 教师卡 VALUES (JS001, 张老师, 500);
INSERT INTO 教师卡 VALUES (JS002, 王老师, 1000);
INSERT INTO 教师卡 VALUES (JS003, 李老师, 200);
INSERT INTO 教师卡 VALUES (JS004, 赵老师, 800);
INSERT INTO 教师卡 VALUES (JS005, 钱老师, 300);
INSERT INTO 教师卡 VALUES (JS006, 马老师, 600);
INSERT INTO 教师卡 VALUES (JS007, 刘老师, 50);
INSERT INTO 教师卡 VALUES (JS008, 孙老师, 150);
INSERT INTO 教师卡 VALUES (JS009, 周老师, 400);
INSERT INTO 教师卡 VALUES (JS010, 吴老师, 700);
INSERT INTO 销售记录 VALUES (XS001, 5, 15, 2023-05-01, S002, JS001);
INSERT INTO 销售记录 VALUES (XS002, 3, 4500, 2023-05-02, S005, JS002);
INSERT INTO 销售记录 VALUES (XS003, 10, 90, 2023-05-03, S003, JS003);
INSERT INTO 销售记录 VALUES (XS004, 1, 599, 2023-05-04, S004, JS004);
INSERT INTO 销售记录 VALUES (XS005, 8, 24, 2023-05-05, S002, JS005);
INSERT INTO 销售记录 VALUES (XS006, 2, 398, 2023-05-06, S009, JS006);
INSERT INTO 销售记录 VALUES (XS007, 1, 6999, 2023-05-07, S001, JS007);
INSERT INTO 销售记录 VALUES (XS008, 3, 1797, 2023-05-08, S004, JS008);
INSERT INTO 销售记录 VALUES (XS009, 2, 3998, 2023-05-09, S007, JS009);
INSERT INTO 销售记录 VALUES (XS010, 5, 2995, 2023-05-10, S004, JS010);
六、创建触发器
1. 更新库存数量
这个触发器在每次向进货记录表插入新行之后触发。它会获取插入的进货记录的进货数量和对应的商品编号，并更新库存信息表中对应商品的库存数量，将其加上进货数量。
AFTER INSERT ON 进货记录
FOR EACH ROW
BEGIN
-- 声明变量
DECLARE v_进货数量 INT;
DECLARE v_商品编号 VARCHAR(50);
-- 获取插入的进货记录信息
SET v_进货数量 = NEW.进货数量;
SET v_商品编号 = NEW.商品编号;
-- 更新库存数量
UPDATE 库存信息
SET 库存数量 = 库存数量 + v_进货数量
WHERE 商品编号 = v_商品编号;
END
2.更新教师卡账户余额
这个触发器在每次向销售记录表插入新行之后触发。它会获取插入的销售记录的销售总价和对应的教师卡号，并更新教师卡表中对应教师卡的账户余额，将其减去销售总价。
AFTER INSERT ON 销售记录
FOR EACH ROW
BEGIN
-- 声明变量
DECLARE v_销售总价 DECIMAL(10, 2);
DECLARE v_教师卡号 VARCHAR(50);
-- 获取插入的销售记录信息
SET v_销售总价 = NEW.销售总价;
SET v_教师卡号 = NEW.教师卡号;
-- 更新教师卡账户余额
UPDATE 教师卡
SET 账户余额 = 账户余额 - v_销售总价
WHERE 教师卡号 = v_教师卡号;
END;
3.更新商品单价
这个触发器在每次向进货记录表插入新行之后触发。它会获取插入的进货记录的进货单价和对应的商品编号，并更新商品信息表中对应商品的商品单价，将其更新为最新的进货单价。
AFTER INSERT ON 进货记录
FOR EACH ROW
BEGIN
-- 声明变量
DECLARE v_进货单价 DECIMAL(10, 2);
DECLARE v_商品编号 VARCHAR(50);
-- 获取插入的进货记录信息
SET v_进货单价 = NEW.进货单价;
SET v_商品编号 = NEW.商品编号;
-- 更新商品单价
UPDATE 商品信息
SET 商品单价 = v_进货单价
WHERE 商品编号 = v_商品编号;
END;
七、跨表查询
1.查询销售数量、销售日期、教师姓名、账户余额
SELECT 销售数量,销售日期,教师姓名,账户余额 ,销售记录.教师卡号,教师卡.教师卡号
FROM 销售记录,教师卡
WHERE 销售记录.教师卡号 =教师卡.教师卡号
2.查询商品名字、商品单价、进货数量、进货日期
SELECT 商品信息.商品名字, 商品信息.商品单价, 进货记录.进货数量, 进货记录.进货日期, 商品信息.商品编号
FROM 进货记录
JOIN 商品信息 ON 进货记录.商品编号 = 商品信息.商品编号
3.查询
查询结果八、答疑解惑
代码有问题或需要其他的查询语句，都可以随时评论留言或联系博主。
博主VX：18884281851
谢谢各位的支持~~
Ongwu博客 版权声明：以上内容未经允许不得转载！授权事宜或对内容有异议或投诉，请联系站长，将尽快回复您，谢谢合作！