【Mysql】——进销存系统，创建各张表、索引、视图
超市购物业务处理：选取货品、收银台结账（收银员扫码，系统计算金额，顾客支付）
💻博主现有专栏：
C51单片机（STC89C516），c语言，c++，离散数学，算法设计与分析，数据结构，Python，Java基础，MySQL，linux，基于HTML5的网页设计及应用，Rust（官方文档重点总结），jQuery，前端vue.js，Javaweb开发，设计模式、Python机器学习等
🥏主页链接：
目录
💻顾客user（顾客id，姓名、性别、会员卡号、手机号、卡内余额、可用积分、上次消费时间）
💻货品名称表goods（货品id，货品名称，计量单位，货品条码，零售价，促销价，名称缩写(由触发器自行维护)，货品状态）
💻供应商信息表supplier（供应商id，供应商名称，联系人，联系电话，供应商状态）
💻收银记录表cashaccount（收银id，收银员id，顾客id，扎帐id，收银时间，支付方式，应收金额，实收金额，优惠金额，销售状态（正常 ,已退货））
💻收银明细表cashaccount_detil（收货明细id，收银id，货物id，销售数量，零售价、促销价）
💻货品库存表stock（库存id，货品id，(当前)库存数量，昨日库存(用于日清)，月初库存(用于月结)）
💻扎帐记录表Settle_accounts（扎帐id，收银员id，扎帐时间，应收金额(由系统计算)，实收金额(由收银员输入)，差错原因）
🎯实现功能
超市购物业务处理：选取货品、收银台结账（收银员扫码，系统计算金额，顾客支付）
🎯概念设计
- 分析上述业务中出现了哪些对象？
- 顾客
- 货品
- 收银（结账）（哪个收银员、在什么时间、收了哪个顾客，多少钱）？（卖给了这个顾客哪些货品）（要账实相符）
- 库存
- 分析每个对象有哪些特征？
- 顾客（姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）
- 货品（名称、规格、条码、计量单位、名称缩写（由触发器自行维护）、零售价、促销价）
- 收银（收银员，顾客，收银时间，应收金额、实收金额、支付方式）
- 收银明细（货物，销售数量，零售价、促销价）
- 库存（名称、库存数量、昨日库存、月初库存）
- 供应商（供应商名称、联系电话、联系人、供应商状态）
- 根据表述绘制系统ER图
- 利用ER图转换为关系模型的规则，设计关系模式
将实体型、实体的属性和实体型之间的联系转化为关系模式
转换为则：
一个实体型转换为一个关系模式
一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并（加入对应关系的码和联系本身的属性）。
一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并（合并后关系的属性：在n端关系中加入1端关系的码和联系本身的属性）。
一个m:n联系转换为一个关系模式（关系的属性：与该联系相连的各实体的码以及联系本身的属性，各实体码的组合）
注意：数据库中要求每个表都要有一个标识列（整型，自增），作为主码，表中的外码是为了表达实体之间的联系。
顾客（顾客id，姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）
货品（货品id，名称、规格、条码、计量单位、零售价、促销价）
供应商表（供应商id，供应商名称，联系人，联系电话，供应商状态）
收银员（收银员id、收银员姓名、收银员手机号）
收银（收银id，收银员id，顾客id，收银时间，应收金额、实收金额、支付方式，销售状态）
收银明细（收货明细id，收银id，货物id，销售数量，零售价、促销价）
库存（库存id、货品id，库存数量、昨日库存、月初库存）
扎帐(扎帐id,收银员id,收银id扎帐时间，应收金额，实收金额，差错原因)
后面根据也可以加入支付方式表、销售状态、供应商状态表等。
🎯逻辑结构设计
💻顾客user（顾客id，姓名、性别、会员卡号、手机号、卡内余额、可用积分、上次消费时间）
Field
Type
Comment
uid
int
顾客id，从10000开始
uname
varchar(20)
姓名
sex
varchar(20)
性别
card_number
varchar(20)
会员卡号
phone
varchar(20)
手机号
card_balance
decimal(10, 2)
卡内余额
points
int
积分
last_purchase_date
date
上次消费时间
自增从10000开始，为什么？如何设置
1.有些情况下，要求ID看起来不要太小，让用户感觉平台已经运营一段时间，给用户一种信任感。
2.避免通过ID暴露信息，比如新平台用户很容易就知道自己是第几个注册用户，这在一定程度上可能暴露平台的新旧或用户量大小。
3.某些ID段可能预留给特定类型的用户或其他用途（比如内部测试账户、大客户、会员等），普通用户的ID从更高的值开始。
Sql命令：
CREATE TABLE USER( uid INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT '顾客id', uname VARCHAR(20) NOT NULL COMMENT '姓名', sex VARCHAR(20) COMMENT '性别', card_number VARCHAR(20) COMMENT '会员卡号', phone VARCHAR(20) COMMENT '手机号', card_balance DECIMAL(10, 2) COMMENT '卡内余额', points INT COMMENT '积分', last_purchase_date DATE COMMENT'上次消费时间' )AUTO_INCREMENT=10000;
💻货品名称表goods（货品id，货品名称，计量单位，货品条码，零售价，促销价，名称缩写(由触发器自行维护)，货品状态）
Field
Type
Comment
gid
int
货品id
gname
varchar(20)
货品名称
unit
varchar(20)
计量单位
barcode
varchar(20)
货品条码
retail_Price
decimal(10, 2)
零售价
promotional_Price
decimal(10, 2)
促销价
abbreviations
varchar(20)
名称缩写(由触发器自行维护)
Status
tinyint
货品状态
（0正常，1暂停进货（不能购进，但可以销售），2不再进货（不能购进，也不能销售））
Sql命令代码：
CREATE TABLE goods( gid INT PRIMARY KEY NOT NULL COMMENT'货品id', gname VARCHAR(20) COMMENT'货品名称' NOT NULL, unit VARCHAR(20) COMMENT'计量单位' NOT NULL, barcode VARCHAR(20) COMMENT'货品条码' NOT NULL, retail_Price DECIMAL(10, 2) COMMENT'零售价', promotional_Price DECIMAL(10, 2) COMMENT'促销价' , abbreviations INT COMMENT'名称缩写' NOT NULL, `Status` TINYINT COMMENT'货品状态（0正常，1暂停进货（不能购进，但可以销售），2不再进货（不能购进，也不能销售））' );
💻供应商信息表supplier（供应商id，供应商名称，联系人，联系电话，供应商状态）
Field
Type
Comment
supplierID
int
供应商id
supplier_Name
varchar(50)
供应商名称
contact_Person
varchar(20)
联系人
contact_Number
varchar(20)
联系电话
supplier_Status
tinyint
供应商状态
（0正常往来，1暂停来往，2，不再来往）
Sql命令：
CREATE TABLE supplier( supplierID INT PRIMARY KEY NOT NULL COMMENT '供应商id', supplier_Name VARCHAR(50) NOT NULL COMMENT '供应商名称', contact_Person VARCHAR(20) NOT NULL COMMENT '联系人', contact_Number VARCHAR(20) NOT NULL COMMENT '联系电话', supplier_Status TINYINT COMMENT '供应商状态（0正常往来，1暂停来往，2，不再来往）' );
💻收银员（收银员id、收银员姓名、收银员手机号）
Field
Type
Comment
cashierid
int
收银员id
cashier_Name
varchar(20)
收银员姓名
phone
varchar(20)
收银员手机号
Sql命令：
CREATE TABLE cashier( cashierid INT PRIMARY KEY NOT NULL COMMENT'收银员id', cashier_Name VARCHAR(20) NOT NULL COMMENT'收银员姓名', phone VARCHAR(20) NOT NULL COMMENT'收银员手机号' );
💻收银记录表cashaccount（收银id，收银员id，顾客id，扎帐id，收银时间，支付方式，应收金额，实收金额，优惠金额，销售状态（正常 ,已退货））
Field
Type
Comment
cashaccountid
int
收银id
cashierid
Int
收银员id，外键
uid
int
顾客id(0 普通顾客，1-9999大客户，>=10000会员 外键
settle_accountstid
Int
扎帐id 外键
cashier_time
datetime
收银时间，默认为当前时间
payment
tinyint
支付方式 0现金，1储值卡，2支付宝，3微信支付
amount_money
decimal(18,3)
应收金额
actual_money
decimal(18,3)
实收金额
discount _money
decimal(18,3)
优惠金额
sales_status
tinyint
销售状态（0正常 ,1已退货）
注意：CURRENT_TIMESTAMP的含义？
答：当前时间的时间戳
Sql命令：
CREATE TABLE cashaccount( cashaccountid INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '收银id', cashierid INT COMMENT '收银员id', uid INT COMMENT '顾客id(0 普通顾客，1-9999大客户，>=10000会员', settle_accountstid INT COMMENT '扎帐id', cashier_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '收银时间', payment TINYINT COMMENT '支付方式 0现金，1储值卡，2支付宝，3微信支付', amount_money DECIMAL(18,3) COMMENT '应收金额', actual_money DECIMAL(18,3) COMMENT '实收金额', discount_money DECIMAL(18,3) COMMENT '优惠金额', sales_status TINYINT COMMENT '销售状态（0正常 ,1已退货)', FOREIGN KEY (cashierid)REFERENCES cashier(cashierid), FOREIGN KEY (uid)REFERENCES USER(uid), FOREIGN KEY (settle_accountstid)REFERENCES Settle_accounts(settle_accountsid) );
💻收银明细表cashaccount_detil（收货明细id，收银id，货物id，销售数量，零售价、促销价）
Field
Type
Comment
cashaccountdetailid
int
收货明细id
cashaccountid
Int
收银id，外键
gid
int
货物id，外键
salesquantity
DECIMAL(18,3)
销售数量
retailprice
DECIMAL(18,3)
零售价
promotionalprice
DECIMAL(18,3)
促销价
Sql命令：
CREATE TABLE cashaccount_detil( cashaccountdetailid INT PRIMARY KEY NOT NULL COMMENT '收货明细id', cashaccountid INT COMMENT '收银id', gid INT COMMENT '货物id', salesquantity DECIMAL(18,3) COMMENT '销售数量', retailprice DECIMAL(18,3) COMMENT '零售价', promotionalprice DECIMAL(18,3) COMMENT '促销价', FOREIGN KEY (cashaccountid)REFERENCES cashaccount(cashaccountid), FOREIGN KEY (gid)REFERENCES goods(gid) );
💻货品库存表stock（库存id，货品id，(当前)库存数量，昨日库存(用于日清)，月初库存(用于月结)）
Field
Type
Comment
stockid
int
库存id
gid
Int
货品id，外键
quantity
decimal(18, 3)
(当前)库存数量
yesterday_quantity
decimal(18, 3)
昨日库存(用于日清),系统自动维护
month_quantity
decimal(18, 3)
昨日库存(用于日清),系统自动维护
Sql命令：
CREATE TABLE stock( stockid INT PRIMARY KEY NOT NULL COMMENT '库存id', gid INT COMMENT '货品id', quantity DECIMAL(18, 3) COMMENT '(当前)库存数量', yesterday_quantity DECIMAL(18, 3) COMMENT '昨日库存(用于日清),系统自动维护', month_quantity DECIMAL(18, 3) COMMENT '昨日库存(用于日清),系统自动维护', FOREIGN KEY (gid)REFERENCES goods(gid) );
💻扎帐记录表Settle_accounts（扎帐id，收银员id，扎帐时间，应收金额(由系统计算)，实收金额(由收银员输入)，差错原因）
Field
Type
Comment
settle_accountsid
int
扎帐id
cashierid
Int
收银员id
settle_accounts_time
decimal(18, 3)
扎帐时间，默认值为系统当前时间
amount_money
decimal(18, 3)
应收金额(由系统计算)
actual_money
decimal(18, 3)
实收金额(由收银员输入)
errorReason
varchar(100)
差错原因
Sql命令：
CREATE TABLE Settle_accounts( settle_accountsid INT PRIMARY KEY NOT NULL COMMENT '扎帐id', cashierid INT COMMENT '收银员id', settle_accounts_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '扎帐时间,默认值为系统当前时间', amount_money DECIMAL(18, 3) COMMENT '应收金额(由系统计算)', actual_money DECIMAL(18, 3) COMMENT '实收金额(由收银员输入)', errorReason VARCHAR(100) COMMENT '差错原因' );
🎯插入记录
- 货品名称表goods
Sql命令：
INSERT INTO goods (gid, gname, unit, barcode, retail_Price, promotional_Price, abbreviations, `Status`) VALUES (1, '可口可乐', '瓶', '101', 3.00, 2.50, 'kkkl', 0), (2, '百事可乐', '瓶', '102', 3.00, 2.50, 'bskl', 0), (3, '伊利牛奶', '盒', '103', 5.00, 4.50, 'ylnn', 0), (4, '农夫山泉', '瓶', '105', 2.00, 1.50, 'nfsq', 0), (5, '涪陵榨菜', '袋', '106', 1.00, 0.80, 'flzc', 0), (6, '青岛啤酒', '听', '107', 5.0, 4, 'qdpj', 0);
- 收银员表cashier
Sql命令：
INSERT INTO cashier(cashierid,cashier_Name,phone) VALUES(1,'王小小','13683868928');
- 收银记录表cashaccount
Sql命令： INSERT INTO cashaccount(cashaccountid,cashierid,uid ,settle_accountstid,cashier_time,payment,amount_money,actual_money,discount_money,sales_status) values (1,1,0,1,'2024-03-01',0,6.000,5.000,1.000,0), (2,1,0,1,'2024-03-11',0,12.000,10.600,1.400,0);3、收银明细表symxb
Sql命令：
INSERT INTO cashaccount_detil(`cashaccountid`,`gid`,`salesquantity`,`retailprice`,`promotionalprice`) VALUES(1,1,1.000,3.000,2.500), (1,2,1.000,3.000,2.500), (2,5,2.000,1.000,0.800), (2,3,2.000,5.000,4.500);4、扎帐记录表zzjlb
Sql命令：
INSERT INTO `settle_accounts`(settle_accountsid,`cashierid`,`settle_accounts_time`,`amount_money`,`actual_money`,`errorReason`) VALUES(1,1,'2024-03-11',15.600,15.600,'无差错');5.汉字拼音表Hzpyb
根据发的脚本文件导入数据
其他表数据自行插入。
🎯思考
以上几个表中的数据是人工输入的测试数据，他们之间有什么关系，我们应该如何操作才能保证他们之间的关系是正确的。需要用到哪些数据库技术。
答：
货品名称表 和 收银明细表 之间有关系，因为收银明细需要引用具体的货品。
收银员表 和 收银记录表 之间有关系，收银记录需要标明是哪位收银员完成的交易。
收银记录表 和 扎帐记录表 之间有关系，每一次的收银记录可能需要对应到特定的扎帐记录上。
收银记录表 和 收银明细表 之间有关系，每一条收银记录可能包含多个收银明细。
汉字拼音表 用于货品名称表abbreviation列的辅助修正。
需要用到的数据库技术：
外键约束、触发器、事务控制、约束、数据验证、索引等。
🎯建立视图
- 货品库存数量v_goods_stock
包括货品id，货品名称、计量单位、库存数量
CREATE VIEW v_goods_stock AS SELECT g.gid AS '货品ID', g.gname AS '货品名称', g.unit AS '计量单位', s.quantity AS '库存数量' FROM goods g,stock s where g.gid = s.gid;收银明细视图
包括：明细id，收银id，货品名称、计量单位、销售数量、单价、零售价等
CREATE VIEW v_cashier_details AS SELECT cd.cashaccountdetailid AS '明细ID', cd.cashaccountid AS '收银ID', g.gname AS '货品名称', g.unit AS '计量单位', cd.salesquantity AS '销售数量', cd.promotionalprice AS '单价', g.retail_Price AS '零售价' FROM cashaccount_detil cd, goods g where cd.gid = g.gid;还需要建立那些视图？
1.顾客消费视图v_customer_purchases
CREATE VIEW v_customer_purchases AS SELECT u.uid AS '顾客ID', u.uname AS '姓名', ca.cashaccountid AS '收银ID', ca.cashier_time AS '购买日期', SUM(cd.salesquantity * cd.promotionalprice) AS '总金额' FROM user u JOIN cashaccount ca ON u.uid = ca.uid JOIN cashaccount_detil cd ON ca.cashaccountid = cd.cashaccountid GROUP BY ca.cashaccountid;日销售汇总视图v_daily_sales_summary
CREATE VIEW v_daily_sales_summary AS SELECT DATE(ca.cashier_time) AS '销售日期', COUNT(DISTINCT ca.cashaccountid) AS '交易次数', SUM(cd.salesquantity) AS '销售总量', SUM(cd.salesquantity * cd.promotionalprice) AS '销售总额' FROM cashaccount ca JOIN cashaccount_detil cd ON ca.cashaccountid = cd.cashaccountid GROUP BY DATE(ca.cashier_time);库存紧缺视图v_stock_short
CREATE VIEW v_stock_short AS SELECT g.gid AS '货品ID', g.gname AS '货品名称', s.quantity AS '当前库存' FROM stock s, goods g WHERE s.gid = g.gid and s.quantity < 100;
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
11
0- 0
已为社区贡献4条内容
所有评论(0)