MySQL数据库实训设计——超市管理系统_超市管理数据库的设计与建立

商品管理

商品的添加删除查看

删除不在销售的商品

库存管理

设置库存警报

自动更新库存信息

查看全部商品的信息

进货管理

采购订单的增删改查

生成采购历史信息

供货商管理

供货商信息的增删改查

生成供货商报告

销售管理

销售记录的增删改查

生成销售报告

退货管理

退货记录的增改查

生成退货报告

会员管理

会员的增改查

删除已注销的会员

员工管理

员工的增改查

删除离职员工

列名

数据类型

长度

约束

说明

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

状态

列名

数据类型

长度

约束

说明

departmentid

varchar

5

主键

部门编号

departmentname

varchar

50

部门名称

列名

数据类型

长度

约束

说明

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

外键

仓库编号

列名

数据类型

长度

约束

说明

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

状态

列名

数据类型

长度

约束

说明

warehouseid

varchar

10

主键

仓库编号

warehousename

varchar

50

仓库名称

address

varchar

50

地址

列名

数据类型

长度

约束

说明

supplierid

varchar

10

主键

供货商编号

suppliername

varchar

50

供货商名称

address

varchar

50

地址

phonenumber

varchar

11

电话

列名

数据类型

长度

约束

说明

returnid

varchar

10

主键

退货记录

transactionid

varchar

10

外键

交易流水号

productid

varchar

10

外键

商品编号

returnquantity

int

退货数量

returnamount

decimal

(10,2)

退货金额

returndate

datetime

退货日期

reason

varchar

255

退货原因

status

varchar

5

状态

列名

数据类型

长度

约束

说明

purchaseorderid

varchar

10

主键

采购订单号

productid

varchar

10

外键

商品编号

purchasequantity

int

采购数量

totalamount

decimal

(10,2)

总金额

supplierid

varchar

10

外键

供货商编号

purchasedate

datetime

采购日期

staffid

varchar

10

外键

员工编号

warehouseid

varchar

10

外键

仓库编号

列名

数据类型

长度

约束

说明

transactionid

varchar

10

主键

交易流水号

staffid

varchar

10

外键

员工编号

productid

varchar

10

外键

商品编号

transactiondate

datetime

交易日期

transactionquantity

int

交易数量

transactionamount

decimal

(10,2)

交易金额

cardnumber

varchar

10

外键

会员卡卡号

列名

数据类型

长度

约束

说明

warehouseid

varchar

10

主键、外键

仓库编号

productid

varchar

10

主键、外键

商品编号

stock

int

库存量

数据库名

定义数据库语句

说明

supermarket

create database supermarket;

创建数据库

表名

定义表语句

说明

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为主外键

数据操纵类型

数据操纵语句

说明

查询数据

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;

删除技术部(包含技术部所有员工及其本身)

视图名

定义视图和查询视图语句

说明

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;

显示员工和所在部门

索引名

定义索引语句

说明

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;

为商品表的商品名称列上创建全文索引

存储过程名

定义及调用存储过程语句

说明

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

存储函数名

定义及调用存储函数语句

说明

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

触发器名

定义及使用触发器语句

说明

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异常，提示余额不足

事件名

定义事件语句

说明

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;

每周清理超过三个月的采购历史记录

存储过程名

定义及调用存储过程语句

说明

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;

采购事务，输入商品编号，商品数量，供货商编号，员工编号参数，进行生成采购记录以及更新库存，并在调用前后查看数据

用户名

定义用户语句

权限分配与回收语句

说明

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的只读权限

操作类型

对应操作的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

数据库导入