MySQL数据库实训设计——超市管理系统

1 项目需求分析

1.1 项目名称

超市管理系统

1.2 项目功能

商品管理	商品的添加删除查看
	删除不在销售的商品
库存管理	设置库存警报
	自动更新库存信息
	查看全部商品的信息
进货管理	采购订单的增删改查
	生成采购历史信息
供货商管理	供货商信息的增删改查
	生成供货商报告
销售管理	销售记录的增删改查
	生成销售报告
退货管理	退货记录的增改查
	生成退货报告
会员管理	会员的增改查
	删除已注销的会员
员工管理	员工的增改查
	删除离职员工

1.3 项目系统结构图

2 数据库的设计

2.1 概念结构设计

1、局部E-R图

图1-1 员工实体E-R图

图1-2 商品实体E-R图

图1-3 会员实体E-R图

图1-4 仓库实体E-R图

图1-5 退货实体E-R图

图1-6 供货商实体E-R图

2、简化全局E-R图

图2-1 简化全局E-R图

2.2 逻辑结构设计

1、将E-R模型转换为关系模型

员工表（员工编号，员工姓名，身份证号，员工性别，员工年龄，电话，所属部门，职位，工资，员工工龄，入职时间，状态）

商品表（商品编号，商品名称，商品类别，销售单价，采购单价，供货商，状态，仓库编号）

会员表（会员卡卡号，会员姓名，注册日期，电话，余额，累计金额，状态）

仓库表（仓库编号，仓库名称，仓库地址）

供货商表（供货商编号，供货商名称，地址，电话）

退货表（退货记录，交易流水号，商品编号，退货数量，退货金额，退货日期，退货原因，状态）

2、对转化后的关系模型进行规范化处理

员工表（员工编号，员工姓名，身份证号，员工性别，员工年龄，电话，部门编号，职位，工资，员工工龄，入职时间，状态）

部门表（部门编号，部门名称）

商品表（商品编号，商品名称，商品类别，销售单价，采购单价，供货商，状态，仓库编号）

会员表（会员卡卡号，会员姓名，注册日期，电话，余额，累计金额，会员等级，状态）

仓库表（仓库编号，仓库名称，仓库地址）

供货商表（供货商编号，供货商名称，地址，电话）

退货表（退货记录，交易流水号，商品编号，退货数量，退货金额，退货日期，退货原因，状态）

进货表（采购订单号，商品编号，采购数量，总金额，供货商编号，采购日期，员工编号，仓库编号）

商品交易表（交易流水号，员工编号，商品编号，交易日期，交易数量，交易金额，会员卡卡号）

仓库存货表(仓库编号，商品编号，存货量）

2.3 物理结构设计

（1）员工表staff

列名	数据类型	长度	约束	说明
staffid	varchar	10	主键	员工编号
staffname	varchar	5		员工姓名
idnumber	varchar	18		身份证号
gender	varchar	1		员工性别
age	int			员工年龄
phonenumber	varchar	11		电话
departmentid	varchar	5	外键	部门编号
position	varchar	10		职位
salary	decimal	(10,2)		工资
workexperience	int			员工工龄
hiredate	datetime			入职日期
status	varchar	5		状态

（2）部门表department

列名	数据类型	长度	约束	说明
departmentid	varchar	5	主键	部门编号
departmentname	varchar	50		部门名称

（3）商品表product

列名	数据类型	长度	约束	说明
productid	varchar	10	主键	商品编号
productname	varchar	50		商品名称
category	varchar	10		商品类别
sellingprice	decimal	(10,2)		销售单价
purchaseprice	decimal	(10,2)		采购单价
supplierid	varchar	10	外键	供货商编号
status	varchar	5		状态
warehouseid	varchar	10	外键	仓库编号

（4）会员表member

列名	数据类型	长度	约束	说明
cardnumber	varchar	10	主键	会员卡卡号
membername	varchar	5		会员姓名
registrationdate	datetime			注册日期
phonenumber	varchar	11		电话
balance	decimal	(10,2)		余额
totalamount	decimal	(10,2)		累计金额
level	varchar	10		会员等级
status	varchar	5		状态

（5）仓库表warehouse

列名	数据类型	长度	约束	说明
warehouseid	varchar	10	主键	仓库编号
warehousename	varchar	50		仓库名称
address	varchar	50		地址

（6）供货商表supplier

列名	数据类型	长度	约束	说明
supplierid	varchar	10	主键	供货商编号
suppliername	varchar	50		供货商名称
address	varchar	50		地址
phonenumber	varchar	11		电话

（7）退货表returninfo

列名	数据类型	长度	约束	说明
returnid	varchar	10	主键	退货记录
transactionid	varchar	10	外键	交易流水号
productid	varchar	10	外键	商品编号
returnquantity	int			退货数量
returnamount	decimal	(10,2)		退货金额
returndate	datetime			退货日期
reason	varchar	255		退货原因
status	varchar	5		状态

（8）进货表purchase

列名	数据类型	长度	约束	说明
purchaseorderid	varchar	10	主键	采购订单号
productid	varchar	10	外键	商品编号
purchasequantity	int			采购数量
totalamount	decimal	(10,2)		总金额
supplierid	varchar	10	外键	供货商编号
purchasedate	datetime			采购日期
staffid	varchar	10	外键	员工编号
warehouseid	varchar	10	外键	仓库编号

（9）商品交易表transaction

列名	数据类型	长度	约束	说明
transactionid	varchar	10	主键	交易流水号
staffid	varchar	10	外键	员工编号
productid	varchar	10	外键	商品编号
transactiondate	datetime			交易日期
transactionquantity	int			交易数量
transactionamount	decimal	(10,2)		交易金额
cardnumber	varchar	10	外键	会员卡卡号

（9）仓库存货表warestock

列名	数据类型	长度	约束	说明
warehouseid	varchar	10	主键、外键	仓库编号
productid	varchar	10	主键、外键	商品编号
stock	int			库存量

3 数据库的实现

3.1 数据库

数据库名	定义数据库语句	说明
supermarket	create database supermarket;	创建数据库

3.2 表

表名	定义表语句	说明
department	create table department (   departmentid varchar(5) primary key,   departmentname varchar(50) );	部门表 departmentid为主键
member	create table member (   cardnumber varchar(10) primary key,   membername varchar(5),   registrationdate date,   phonenumber varchar(11),   balance decimal(10,2),   totalamount decimal(10,2), level varchar(10),   status varchar(5) );	会员表 cardnumber为主键
warehouse	create table warehouse (   warehouseid varchar(10) primary key,   warehousename varchar(50),   address varchar(50) );	仓库表 warehouseid为主键
supplier	create table supplier (   supplierid varchar(10) primary key,   suppliername varchar(50),   address varchar(50),   phonenumber varchar(11) );	供货商表 supplierid为主键
staff	create table staff(   staffid varchar(10) primary key,   staffname varchar(5),   idnumber varchar(18),   gender varchar(1),   age int,   phonenumber varchar(11),   departmentid varchar(5),   position varchar(50),   salary decimal(10,2),   workexperience int,   hiredate datetime,   status varchar(20), foreign key (departmentid) references department(departmentid) );	员工表 staffid为主键 departmentid为外键
product	create table product (   productid varchar(10) primary key,   productname varchar(50),   category varchar(10),   sellingprice decimal(10,2),   purchaseprice decimal(10,2),   supplierid varchar(10),   status varchar(5), warehouseid varchar(10), foreign key (supplierid) references supplier(supplierid), foreign key (warehouseid)references warehouse(warehouseid) );	商品表 productid为主键 supplierid,warehouse为外键
purchase	create table purchase (   purchaseorderid varchar(10) primary key,   productid varchar(10),   purchasequantity int,   totalamount decimal(10,2),   supplierid varchar(10),   purchasedate datetime,   staffid varchar(10), warehouseid varchar(10),   foreign key (productid) references product(productid),   foreign key (supplierid) references supplier(supplierid),   foreign key (staffid) references staff(staffid), foreign key (warehouseid)references warehouse(warehouseid) );	进货表 purchaseorderid为主键 productid,supplierid,staffid,warehouseid为外键
transaction	create table transaction (   transactionid varchar(10) primary key,   staffid varchar(10),   productid varchar(10),   transactiondate datetime,   transactionquantity int,   transactionamount decimal(10,2),   cardnumber varchar(10),   foreign key (staffid) references staff(staffid),   foreign key (productid) references product(productid),   foreign key (cardnumber) references member(cardnumber) );	商品交易表 transactionid为主键 staffid,productid,cardnumber为外键
returninfo	create table returninfo (   returnid varchar(10) primary key,   transactionid varchar(10),   productid varchar(10),   returnquantity int,   returnamount decimal(10,2),   returndate datetime,   reason varchar(255),   status varchar(5), foreign key (transactionid) references transaction(transactionid), foreign key (productid) references product(productid) );	退货表 returnid为主键 transactionid,productid为外键
warestock	create table warestock (     warehouseid varchar(10),     productid varchar(10),     stock int default 0,     primary key (warehouseid, productid),     foreign key (warehouseid) references warehouse(warehouseid),     foreign key (productid) references product(productid) );	仓库存货表 warehouseid,productid为主外键

3.3 数据操纵

数据操纵类型	数据操纵语句	说明
查询数据	select   suppliername as '供应商名称',   coalesce(sum(case when transactionamount > 0 then transactionamount else 0 end), 0) as '销售总额',   coalesce(sum(case when returnamount > 0 then returnamount else 0 end), 0) as '退货总额' from   supplier left join   product on supplier.supplierid = product.supplierid left join   transaction on product.productid = transaction.productid left join   returninfo on transaction.transactionid = returninfo.transactionid group by   suppliername;	查询每个供应商的销售总额和退货总额
插入数据	insert into department (departmentid, departmentname) values ('d001', '人事部'), ('d002', '商品部'), ('d003', '营业部'),('d004', '财务部'), ('d005', '市场部'), ('d006', '技术部'); insert into member (cardnumber, membername, registrationdate, phonenumber, balance, totalamount,level,status) values   ('m001', '黄一', '2021-05-01', '77774321856', 2645.00, 15000.00,'普通会员','正常'),   ('m002', '盛二', '2022-01-15', '77778765234', 3285.00, 26512.00,'普通会员', '正常'),   ('m003', '王三, '2023-07-23', '77778765562', 5628.00, 96152.00, '普通会员','正常'),   ('m004', '李四', '2023-08-10', '77778765500', 4263.00, 26541.00, '普通会员','正常'),   ('m005', '王大明', '2023-09-05', '77778765501', 6253.00, 30085.00, '普通会员','正常'); insert into warehouse (warehouseid, warehousename, address) values ('w001', '主仓库', '河南省漯河市源汇区'), ('w002', '备用仓库', '上海市浦东新区'),('w003', '备用仓库2', '北京市朝阳区'), ('w004', '临时仓库', '广东省深圳市龙华区'); insert into supplier (supplierid, suppliername, address, phonenumber) values ('s001', 'abc电子', '河南省信阳市平桥区', '55512349864'), ('s002', 'xyz生产厂家', '河南省郑州市金水区', '55556786853'), ('s003', 'nbr厂家', '浙江省杭州市西湖区', '66666786895'), ('s004', 'EFG科技', '北京市海淀区', '55588993456'), ('s005', 'LMN制造厂', '上海市黄浦区', '55512348765'), ('s006', 'OPQ公司', '广东省广州市天河区', '66666987654'); insert into staff (staffid, staffname, idnumber, gender, age, phonenumber, departmentid, position, salary, workexperience, hiredate, status) values   ('u001', '宋一, '1000001', '男', 30, '12345678901', 'd001', '经理', 7000.00, 10, '2013-01-01', '在职'),   ('u002', '郑二', '1000002', '女', 28, '98765432109', 'd002', '采购员', 4000.00, 8, '2015-02-15', '在职'),   ('u003', '刘三', '1000003', '男', 35, '45678901234', 'd003', '工程师', 6000.00, 12, '2011-03-10', '休假'),   ('u004', '张小华', '1000004', '男', 25, '12345678902', 'd002', '销售员', 3500.00, 3, '2020-05-20', '在职'),   ('u005', '李红', '1000005', '女', 32, '98765432108', 'd003', '客户服务', 4500.00, 6, '2018-07-10', '在职'); insert into product (productid, productname, category, sellingprice, purchaseprice, supplierid, status,warehouseid) values   ('p001', '智能手机', '电子产品', 8000.00, 6000.00, 's001', '在售','w001'),   ('p002', '打印机', '办公用品', 600.00, 450.00, 's002', '在售','w001'),   ('p003', '笔记本', '书本', 12.00, 10.00,'s003', '在售','w001'),   ('p004', '平板电脑', '电子产品', 5000.00, 4000.00, 's004', '在售','w001'),   ('p005', '办公椅', '办公家具', 300.00, 250.00, 's005', '在售','w001'),   ('p006', '水杯', '日用品', 5.00, 3.00, 's006', '在售','w001'),   ('p007', '羽绒服', '服装', 300.00, 200.00, 's005', '在售','w001'); insert into transaction (transactionid, staffid, productid, transactiondate, transactionquantity, transactionamount, cardnumber) values   ('t1001', 'u001', 'p002', '2023-12-01', 3, 1800.00, 'm002'),   ('t1002', 'u003', 'p003', '2023-12-02', 2, 24.00, 'm001'),   ('t1003', 'u004', 'p001', '2023-12-2', 2, 16000.00, 'm004'),   ('t1004', 'u005', 'p003', '2023-11-20', 1, 12.00, 'm005'),   ('t1005', 'u002', 'p004', '2023-10-10', 1, 5000.00, 'm003'),   ('t1006', 'u003', 'p005', '2023-9-20', 4, 1200.00, 'm004'); insert into purchase (purchaseorderid, productid, purchasequantity, totalamount, supplierid, purchasedate, staffid,warehouseid) VALUES   ('c1001', 'p001', 5, 30000.00, 's001', '2023-09-01', 'u001','w001'),   ('c1002', 'p002', 3, 1800.00, 's002', '2023-10-15', 'u003','w001'),   ('c1003', 'p003', 5, 60.00, 's003', '2023-8-01', 'u002','w001'),   ('c2001', 'p004', 2, 8000.00, 's004', '2023-11-15', 'u002','w001'),   ('c2002', 'p005', 2, 500.00, 's005', '2023-05-15', 'u001','w001'),   ('c2003', 'p006', 100, 300.00, 's006', '2023-09-21', 'u005','w001'); insert into returninfo (returnid, transactionid, productid, returnquantity, returnamount, returndate, reason, status) values   ('r001', 't1001', 'p001', 2, 1200.00, '2022-12-05', '七天无理由', '成功'),   ('r002', 't1003', 'p004', 1, 8000.00, '2023-12-3', '质量问题', '拒绝'); insert into warestock (warehouseid, productid, stock) values ('w001', 'p001', 50), ('w001', 'p002', 30), ('w001', 'p003', 20), ('w001', 'p004', 10), ('w001', 'p005', 15), ('w001', 'p006', 140);	插入数据
修改数据	select staffname,salary from staff where staffname = '张小华'; update staff set salary = 4000 where staffname = '张小华'; select staffname,salary from staff where staffname = '张小华';	将员工 '张小华' 的工资增加到 4000
删除数据	select * from department; delete from staff where departmentid = 'd006'; delete from department where departmentid = 'd006'; select * from department;	删除技术部(包含技术部所有员工及其本身)

3.4 视图

视图名	定义视图和查询视图语句	说明
view_selling	create view view_selling as select   product.productid as 商品编号,   product.productname as 商品名称,   product.sellingprice as 销售单价,   supplier.suppliername as 供货商名称,   max(transaction.transactionamount) as 最高销售额 from   product join   supplier on product.supplierid = supplier.supplierid join   transaction on product.productid = transaction.productid group by   product.productid, product.productname, product.sellingprice, supplier.suppliername; select * from view_selling;	显示销售额最高的商品和供应商
view_return	create view view_return as select   product.productid as 商品编号,   product.productname as 商品名称,   coalesce(sum(transaction.transactionquantity), 0) as 销售数量,   coalesce(sum(returninfo.returnquantity), 0) as 退货数量 from   product left join   transaction on product.productid = transaction.productid left join   returninfo on transaction.transactionid = returninfo.transactionid group by   product.productid, product.productname; select * from view_return;	显示每个商品的销售数量和退货数量
view_staff	create view view_staff as select   staff.staffid as 员工编号,   staff.staffname as 员工姓名,   staff.position as 职位,   department.departmentname as 部门名称 from   staff join   department on staff.departmentid = department.departmentid; select * from view_staff;	显示员工和所在部门

3.5 索引

索引名	定义索引语句	说明
idx_address	create index idx_address on supplier (address); show index from supplier\G;	为供货商表的地址列创建普通索引
idx_productid	create unique index idx_productid on product(productid); show index from product\G;	为商品表的商品编号列上创建唯一索引
idx_name	create fulltext index idx_name on product(productname); show index from product\G;	为商品表的商品名称列上创建全文索引

3.6 存储过程

存储过程名	定义及调用存储过程语句	说明
pr_staff	delimiter $$ create procedure pr_staff(in sname varchar(255)) begin   select     staff.staffid as 员工编号,     staff.staffname as 员工姓名,     coalesce(sum(transaction.transactionamount), 0) as 销售总额   from     staff   left join     transaction on staff.staffid = transaction.staffid   where     staff.staffname = sname   group by     staff.staffid, staff.staffname; end $$ delimiter ; call pr_staff('张小华');	根据员工姓名查询其销售总额
pr_uprice	select productname as 商品名称,sellingprice as 销售单价 from product; delimiter $$ create procedure pr_uprice(in discount decimal(5, 2)) begin   update product   set sellingprice = sellingprice * (1 - discount / 100); end $$ delimiter ; call pr_uprice(10); select productname as 商品名称,sellingprice as 销售单价 from product;	促销打折时候，批量更新商品的销售单价,传入百分比，例如当打一折时，传入10

3.7 存储函数

存储函数名	定义及调用存储函数语句	说明
fu_high	delimiter $$ create function fu_high(pdid varchar(255)) returns boolean reads sql data begin     declare totalsalesamount decimal(10, 2);     select coalesce(sum(transactionamount), 0)     into totalsalesamount     from transaction     where staffid = pdid; return totalsalesamount > 50000; end $$ delimiter ; select fu_high('u001');	判断某个员工是否为高销售员工,当某员工总销售额大于50000则为高销售员工，返回1，否则返回0
fu_bestselling	delimiter $$ create function fu_bestselling(productid varchar(255)) returns boolean reads sql data begin     declare totalsalesquantity int;     select coalesce(sum(transactionquantity), 0)     into totalsalesquantity     from transaction     where productid = productid; return totalsalesquantity > 100; end $$ delimiter ; select fu_bestselling('p001');	判断某商品是否畅销商品,当某商品销量大于100则为畅销产品，返回1，否则返回0

3.8 触发器

触发器名	定义及使用触发器语句	说明
tr_move	select productname,status,warehouseid from product where productid='p007'; delimiter $$ create trigger tr_move before update on product for each row begin     if new.status = '停售' and old.status <> '停售' then         set new.warehouseid = 'w002';     end if; end $$ delimiter ; update product set status = '停售' where productid = 'p007'; select productname,status,warehouseid from product where productid='p007';	自动将不在销售的商品从主仓库移入备用仓库
tr_mem	delimiter $$ create trigger tr_mem before insert on transaction for each row begin     declare memberbalance decimal(10, 2);     select balance into memberbalance     from member     where cardnumber = new.cardnumber;     if memberbalance < new.transactionamount then         signal sqlstate '45000'         set message_text = '会员余额不足，无法完成交易';     end if; end $$ delimiter ; insert into transaction values ('t1010', 'u004', 'p004', '2023-12-06', 10, 50000.00, 'm005');	当会员余额不足时，阻止交易记录的插入,会生成一个SQL异常，提示余额不足

3.9 事件

事件名	定义事件语句	说明
ev_level	set @@global.event_scheduler = true; select membername,totalamount,level from member; delimiter $$ create event ev_level on schedule every 1 month starts now() do begin     update member     set level =         case             when totalamount > 50000 then '金卡会员'             when totalamount > 30000 then '银卡会员'             else '普通会员'         end; end $$ delimiter ; select membername,totalamount,level from member;	每月检查会员消费金额并更新会员等级
ev_clear	set @@global.event_scheduler = true; select purchase.purchaseorderid,purchase.productid, product.productname, purchase.purchasedate from product join purchase on product.productid = purchase.productid; delimiter $$ create event ev_clear on schedule every 1 week starts now() do begin     delete from purchase     where purchasedate < now() - interval 3 month; end $$ delimiter ; select purchase.purchaseorderid,purchase.productid, product.productname, purchase.purchasedate from product join purchase on product.productid = purchase.productid;	每周清理超过三个月的采购历史记录

3.10 事务（在存储过程中使用事务）

存储过程名	定义及调用存储过程语句	说明
pr_shopping	select member.cardnumber, member.balance as 购买前余额, member.totalamount as 购买前累计金额, warestock.stock as 购买前库存量 from member join warestock  on member.cardnumber = 'm001' join product on product.productid = 'p003' and product.productid = warestock.productid where warestock.warehouseid = 'w001'; delimiter $$ create procedure pr_shopping(     in p_product_id varchar(10),     in p_member_card_number varchar(10),     in p_quantity int,     in p_staff_id varchar(10) ) begin     declare purchase_amount decimal(10, 2);     set purchase_amount = (select sellingprice * p_quantity from product where productid = p_product_id);     start transaction;     update member     set balance = balance - purchase_amount     where cardnumber = p_member_card_number;     update member     set totalamount = totalamount + purchase_amount     where cardnumber = p_member_card_number;     insert into transaction  values (concat('t', LPAD(FLOOR(RAND() * 1000000000), 9, '0')), p_staff_id, p_product_id, current_date, p_quantity, purchase_amount, p_member_card_number);     update warestock     set stock = stock - p_quantity     where productid = p_product_id;     commit; end $$ delimiter ; call pr_shopping('p003', 'm001', 3, 'u001'); select member.cardnumber, member.balance as 购买后余额, member.totalamount as 购买后累计金额, warestock.stock as 购买后库存量 from member join warestock  on member.cardnumber = 'm001' join product on product.productid = 'p003' and product.productid = warestock.productid where warestock.warehouseid = 'w001';	购物事务， 输入商品编号，会员编号，购买数量，员工编号参数，进行扣除余额、更新累计金额、生成交易记录、更新库存， 并在调用前后查看数据
pr_buy	select     purchase.*,     warestock.stock from     purchase join warestock on purchase.productid = warestock.productid; delimiter $$ create procedure pr_buy(     in p_productid varchar(10), in p_quantity int, in p_supplierid varchar(10), in p_staff varchar(10), in p_warehouseid varchar(10) ) begin declare purchase_amount decimal(10, 2); start transaction;     set purchase_amount = (select purchaseprice * p_quantity from product where productid = p_productid);     insert into purchase (purchaseorderid, productid, purchasequantity, totalamount, supplierid, purchasedate, staffid,warehouseid)values (concat('c', lpad(floor(rand() * 1000000000), 9, '0')), p_productid, p_quantity,purchase_amount,p_supplierid,now(), p_staff,p_warehouseid);     update warestock     set stock = stock + p_quantity where productid = p_productid; commit; end $$ delimiter ; call pr_buy('p002',3,'s003','u005','w001'); select     purchase.*,     warestock.stock from     purchase join warestock on purchase.productid = warestock.productid;	采购事务，输入商品编号，商品数量，供货商编号，员工编号参数，进行生成采购记录以及更新库存，并在调用前后查看数据

3.11 数据库用户及权限分配

用户名	定义用户语句	权限分配与回收语句	说明
admin	create user 'admin'@'localhost' identified by 'admin_password';	grant all privileges on supermarket to 'admin'@'localhost'; show grants for 'admin'@'localhost'; revoke all privileges on supermarket from 'admin'@'localhost'; show grants for 'admin'@'localhost';	创建admin用户，为其分配并回收对数据库supermarket的所有权限
user	create user 'user'@'localhost' identified by 'user_password';	grant select on supermarket.* to 'user'@'localhost'; show grants for 'user'@'localhost'; revoke select on supermarket.* from 'user'@'localhost'; show grants for 'user'@'localhost';	创建user用户，为其分配并回收对数据库supermarket的只读权限

3.12 备份与恢复

操作类型	对应操作的SQL语句	说明
备份	mysqldump -u root -p supermarket > d:/supermarket_backup.sql	数据库备份
恢复	mysql -u root -p supermarket < d:/supermarket_backup.sql	数据库恢复
导出	mysqldump -u root -p supermarket  > d:/supermarket_out.sql	数据库导出
导入	mysql -u root -p supermarket < d:/supermarket_out.sql	数据库导入