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 | 数据库导入 |