超市进销存之openGauss数据库的应用与实践

语言: CN / TW / HK

时间 2023-01-30 17:01:28 Gauss松鼠会

主题: warehouse gsql

一、背景

二、目的

三、什么是“进销存”，什么是超市进销存管理系统？

四、什么是openGauss数据库？

五、应用与实践（模拟超市进销存系统）

1、超市进销存数据库表设计

2、创建数据库表

3、手工插入数据

4、添加约束

5、创建视图

6、创建存储过程

7、新建用户并授权访问

六、总结

一、背景

数字经济时代，数据处理需求大规模增长，数据库在充分挖掘数据资产价值、赋能产业数字化转型、推进数字经济生态建设过程中发挥着重大作用。经营管理一家超市，无论是商品的管理、商品采购的管理还是商品销售的管理，如果单纯依靠纯人工管理，不仅工作量巨大，且容还容易出错，造成不可预估的损失等。基于此，数据库的选择与设计就尤为重要。

二、目的

本文以零售行业为场景，设计数据库模型，并使用openGauss数据库构建零售业务场景下的超市进销存数据库。通过对数据库中对象（表、数据类型、视图、约束、存储过程、用户等）的创建，掌握openGauss数据库基础SQL语法，并通过对表中数据的增删改查，模拟零售行业下的业务实现。

三、什么是“进销存”，什么是超市进销存管理系统？

进销存软件概念起源于上世纪80年代，由于电算化的普及，计算机管理的广泛推广，不少企业对于仓库货品的进货，存货，出货管理，有了强烈的诉求，基于此，进销存软件的发展从此便开始了。

进入90年代以后，随着进销存软件的应用面越来越广，出现了许多从功能上，从用途上，更为全面的进销存系统，从单纯的货品数量管理，扩展为货品的流程管理，进销存软件对每一批货品的来源、存放、去向，都作了详细的记录，随后即延伸为财务进销存一体化的管理模式。

进销存是指企业管理过程中采购（进）——入库（存）——销售（销）的动态管理过程。主要可以分为如下两条线：

商品流通企业的进销存是从商品的采购（进）——入库（存）——销售（销）的动态管理过程。
工业企业的进销存是从原材料的采购（进）——入库（存）——领料加工——产品入库(存)——销售(销)的动态管理过程。

四、什么是openGauss数据库？

openGauss是一个数据库管理系统。数据库是结构化的数据集合。它可以是任何数据，购物清单、图片库或公司网络中的大量信息。数据库管理系统可以对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。由于计算机非常擅长处理大量数据，因此数据库管理系统可以作为独立程序使用，也可以作为其他应用程序的一部分在计算中发挥着核心作用。

openGauss数据库是关系型的。关系型数据库是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据。行和列被称为表，一组表组成了数据库。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

openGauss的SQL部分代表 “结构化查询语言”。SQL是最常用的用于访问和处理数据库的标准计算机语言。根据系统的编程环境，用户可以直接输入SQL、将SQL语句嵌入到以另一种语言编写的代码中，或者使用包含SQL语法的特定语言 API。openGauss支持标准的SQL92/SQL99/SQL2003/SQL2011规范

五、应用与实践（模拟超市进销存系统）

说明：以库表涉及到的字段类型、sql语句等均是基于openGauss数据库进行的。

1、超市进销存数据库表设计

本次设计仅从最基本的核心表设计出发，完成最基本的进销存管理任务，具体涉及如下几张核心表，如遇临时表或者视图或者存储过程，则均以SQL的形式实现：

用户管理表（User_info）

供应商信息表(supplier_info)

订货单信息表（order_info）

进货信息表（Purchase_goods_info）

仓库信息表（warehouse_info）

商品信息表（goods_info）

出库信息表（Outwarehouse_info)

销售清单信息表（sell_list_info）

销售信息表（sell_info）

客户管理表(customer_info)

2、创建数据库表

前置条件：已成功安装openGauss数据库。

2.1、创建超市（零售行业）数据库（retail_db）。

1.切换到omm用户，以操作系统用户omm登录数据库主节点。

2.启动数据库。

3.使用gsql工具登陆数据库。

su - omm

gs_om -t start

gsql -d postgres -p  26000 -r

4.创建数据库retail_db。

CREATE DATABASE retail_db ENCODING 'UTF8' template = template0;

5.连接retail_db数据库。

\connect retail_db

6.创建名为retail_db的schema，并设置retail_db为当前的schema。

CREATE SCHEMA retail_db;

7.将默认搜索路径设为retail_db。

SET search_path TO retail_db;

2.2、创建用户管理表（User_info）

在SQL编辑框中输入如下语句，创建用户管理表user_info。

删除表 user_info

DROP TABLE IF EXISTS user_info;

创建表 user_info

CREATE TABLE user_info

(

 u_id int PRIMARY KEY

,u_name char(20) not null

,u_mail char(20) unique not null

,u_id_card char(20) not null

,u_phone char(20) not null

,u_password char(20) not null

,u_role int not null

);

2.3、创建供应商信息表(supplier_info)

在SQL编辑框中输入如下语句，创建表supplier_info

删除表 supplier_info

DROP TABLE IF EXISTS supplier_info;



创建表 supplier_info

CREATE TABLE supplier_info

(

 supplier_id int PRIMARY KEY

,supplier_name char(20) not null

,supplier_address varchar(100) not null

,supplier_phone char(20) not null

,supplier_type int not null

,remark varchar(100)

);

2.4、创建订货单信息表（order_info）

在SQL编辑框中输入如下语句，创建表order_info

删除表 order_info

DROP TABLE IF EXISTS order_info;



创建表 order_info

CREATE TABLE order_info

(

 order_id int PRIMARY KEY

,order_date date not null

,supplier_id int not null

,goods_id char(20) not null

,goods_name char(20) not null

,goods_home varchar(100) not null

,goods_number int not null

,goods_amount int not null

,goods_sum_amount int not null

,remark varchar(100)

);

2.5、创建进货信息表（Purchase_goods_info）

在SQL编辑框中输入如下语句，创建表purchase_goods_info

删除表 Purchase_goods_info

DROP TABLE IF EXISTS Purchase_goods_info;



创建表 Purchase_goods_info

CREATE TABLE Purchase_goods_info

(

 Purchase_goods_id int PRIMARY KEY

,goods_id char(30) not null

,Purchase_goods_number int not null

,Purchase_goods_amount int not null

,Purchase_goods_date date not null

,supplier_id int not null

,operator int not null

,remark varchar(100)

);

2.6、创建仓库信息表（warehouse_info）

在SQL编辑框中输入如下语句，创建表warehouse_info

删除表 warehouse_info

DROP TABLE IF EXISTS warehouse_info;



创建表 warehouse_info

CREATE TABLE warehouse_info

(

 warehouse_id int PRIMARY KEY

,warehouse_address varchar(100) not null

,warehouse_name varchar(100) not null

,warehouse_operator int not null

,remark varchar(100)

);

2.7、创建商品信息表（goods_info）

在SQL编辑框中输入如下语句，创建表goods_info

删除表 goods_info

DROP TABLE IF EXISTS goods_info;



创建表 goods_info

CREATE TABLE goods_info

(

 goods_id char(20) PRIMARY KEY

,goods_code varchar(50) not null

,goods_name char(20) not null

,goods_home varchar(100) not null

,goods_number int not null

,Purchase_goods_amount int not null

,sell_goods_amount int not null

,goods_type char(20) not null

,supplier_id int not null

,warehouse_id int not null

,remark varchar(100)

);

2.8、创建出库信息表（Outwarehouse_info)

在SQL编辑框中输入如下语句，创建表outwarehouse_info

删除表 outwarehouse_info

DROP TABLE IF EXISTS outwarehouse_info;



创建表 outwarehouse_info

CREATE TABLE outwarehouse_info

(

 outwarehouse_id int PRIMARY KEY

,goods_id char(20) not null

,Outwarehouse_number int not null

,Outwarehouse_date date not null

,operator int not null

,remark varchar(100)

);

2.9、创建销售清单信息表（sell_list_info）

在SQL编辑框中输入如下语句，创建表sell_list_info

删除表 sell_list_info

DROP TABLE IF EXISTS sell_list_info;



创建表 sell_list_info

CREATE TABLE sell_list_info

(

 sell_id int PRIMARY KEY

,sell_date date not null

,goods_id char(20) not null

,goods_name char(20) not null

,goods_number int not null

,goods_measurement_unit char(10) not null

,sell_goods_amount int not null

,remark varchar(100)

);v

2.10、创建销售信息表（sell_info）

在SQL编辑框中输入如下语句，创建表sell_info

删除表 sell_info

DROP TABLE IF EXISTS sell_info;



创建表 sell_info

CREATE TABLE sell_info

(

 sell_id int PRIMARY KEY

,sell_goods_sum_amount int not null

,customer_phone char(20) not null

,remark varchar(100)

);

2.11、创建客户管理表(customer_info)

在SQL编辑框中输入如下语句，创建表customer_info

删除表 customer_info

DROP TABLE IF EXISTS customer_info;



创建表 customer_info

CREATE TABLE customer_info

(

 customer_id int PRIMARY KEY

,customer_name char(20) not null

,customer_phone char(20) not null

,customer_point int not null

,remark varchar(100)

);v

3、手工插入数据

示例（模拟初始化部分表）：

3.1对user_info表进行插入数据操作，在SQL编辑框中输入如下语句：

INSERT INTO user_info (u_id,u_name,u_mail,u_id_card,u_phone,u_password,u_role)

VALUES (001,'张一','[email protected]','2023001','12345678901','openGauss_001',1);

INSERT INTO user_info (u_id,u_name,u_mail,u_id_card,u_phone,u_password,u_role)

VALUES (002,'张二','[email protected]','2023002','12345678902','openGauss_002',2);

INSERT INTO user_info (u_id,u_name,u_mail,u_id_card,u_phone,u_password,u_role)

VALUES (003,'张三','[email protected]','2023003','12345678903','openGauss_003',3);

INSERT INTO user_info (u_id,u_name,u_mail,u_id_card,u_phone,u_password,u_role)

VALUES (004,'张四','[email protected]','2023004','12345678904','openGauss_004',4);

INSERT INTO user_info (u_id,u_name,u_mail,u_id_card,u_phone,u_password,u_role)

VALUES (005,'张五','[email protected]','2023005','12345678905','openGauss_005',5);

3.2对supplier_info表进行插入数据操作，在SQL编辑框中输入如下语句：

INSERT INTO supplier_info(supplier_id,supplier_name,supplier_address,supplier_phone,supplier_type,remark)

VALUES(001,'xxx水果批发商','西安市高陵区xxx','12345678901',1,'');

INSERT INTO supplier_info(supplier_id,supplier_name,supplier_address,supplier_phone,supplier_type,remark)

VALUES(002,'xxx日用百货批发商','西安市雁塔区xxx','12345678902',2,'');

INSERT INTO supplier_info(supplier_id,supplier_name,supplier_address,supplier_phone,supplier_type,remark)

VALUES(003,'xxx烟酒饮料批发商','西安市未央区xxx','12345678903',3,'');

INSERT INTO supplier_info(supplier_id,supplier_name,supplier_address,supplier_phone,supplier_type,remark)

VALUES(004,'xxx零食批发商','西安市碑林区xxx','12345678904',4,'');

INSERT INTO supplier_info(supplier_id,supplier_name,supplier_address,supplier_phone,supplier_type,remark)

VALUES(005,'xxx柴米油盐酱醋批发商','西安市新城区xxx','12345678905',5,'');v

3.3

对warehouse _info表进行插入数据操作，在SQL编辑框中输入如下语句：

INSERT INTO  warehouse_info (warehouse_id,warehouse_address,warehouse_name,warehouse_operator,remark)

VALUES(001,'园区100-1','1号仓库',4,'');

INSERT INTO  warehouse_info (warehouse_id,warehouse_address,warehouse_name,warehouse_operator,remark)

VALUES(002,'园区100-2','2号仓库',4,'');

INSERT INTO  warehouse_info (warehouse_id,warehouse_address,warehouse_name,warehouse_operator,remark)

VALUES(003,'园区100-3','3号仓库',4,'');

3.4对sell_list_info表进行插入数据操作，在SQL编辑框中输入如下语句：

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number, goods_measurement_unit,sell_goods_amount,remark)

VALUES(2023001,'2023-01-16 10:01:00','CS001','食用油',1,'桶',98,'');

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number, goods_measurement_unit,sell_goods_amount,remark)

VALUES(2023001,'2023-01-16 10:01:00','CS002','猪肉',10,'斤',160,'');

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number, goods_measurement_unit,sell_goods_amount,remark)

VALUES(2023002,'2023-01-16 11:01:00','CS001','食用油',1,'桶',98,'');

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number, goods_measurement_unit,sell_goods_amount,remark)

VALUES(2023002,'2023-01-16 11:01:00','CS002','猪肉',10,'斤',160,'');

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number, goods_measurement_unit,sell_goods_amount,remark)

VALUES(2023003,'2023-01-16 12:01:00','CS001','食用油',1,'桶',98,'');

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number, goods_measurement_unit,sell_goods_amount,remark)

VALUES(2023003,'2023-01-16 12:01:00','CS002','猪肉',10,'斤',160,'');

……

4、添加约束

现实场景中，销售清单信息表（sell_list_info）中的商品售价不可能为负数，因此针对表中金额的属性，增加大于0的约束条件。

为销售清单信息表（sell_list_info）的 sell_goods_amount字段增加大于0的约束条件:

ALTER table sell_list_info ADD CONSTRAINT c_sell_goods_amount CHECK (sell_goods_amount >=0);

尝试手工插入一条金额小于0的记录:

INSERT INTO sell_list_info(sell_id,sell_date,goods_id,goods_name,goods_number,measurement_unit,sell_goods_amount,remark)

VALUE(2023003,'2023-01-16 12:01:00','CS002','猪肉',10,'斤',-160,'');

执行失败，失败原因：new row for relation " sell_list_info" violates check constraint "c_sell_goods_amount "。

5、创建视图

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。对于来自多张关联表的复杂查询，就不得不使用十分复杂的SQL语句进行查询，造成极差的体验感。使用视图之后，可以极大的简化操作，使用视图不需要关心相应表的结构、关联条件等。

场景：创建一视图，统计2023-01-16这一天所有销售商品的销售总数量和总销售额，以及其对应的仓库编号和供应商编号（仓库和供应商可进一步关联），并按销售总数量降序排列。

create view v_goods_sell_sum as

select t1.goods_id

      ,t1.goods_name

  ,t1.sum_num

  ,t1.sum_amount

      ,t2.supplier_id

      ,t2.warehouse_id

from

(SELECT goods_id

      ,goods_name

  ,sum(goods_number) as sum_num

  ,sum(sell_goods_amount) as sum_amount

from sell_list_info

where SUBSTR(sell_date,1,10) ='2023-01-16'

group by goods_id ,goods_name

)t1

left join goods_info t2

on t1.goods_id =t2.goods_id

order by sum_num desc

使用视图进行查询

select * from v_goods_sell_sum;

6、创建存储过程

存储过程是能够完成特定功能的SQL语句集。用户可以进行反复调用，从而减少SQL语句的重复编写数量，提高工作效率。

场景：定义一个定时任务，将每天各个收银台生成的销售数据定时同步到生产表。假定超市有5个收银台，对应5个结构完全相同的基础表和一个生产表（这样设计的目的之一是防止瓶颈或锁表），然后在每天销售工作结束后，启动定时任务。

基础表结构(1-5)：

CREATE TABLE sell_list_info_tmp1

(

 sell_id int PRIMARY KEY

,sell_date date not null

,goods_id char(20) not null

,goods_name char(20) not null

,goods_number int not null

,goods_ measurement_unit char(10) not null

,sell_goods_amount int not null

);

……

生产表结构（实际场景可设计成分区表，按天存储）

CREATE TABLE sell_list_info

(

 sell_id int PRIMARY KEY

,sell_date date not null

,goods_id char(20) not null

,goods_name char(20) not null

,goods_number int not null

,goods_ measurement_unit char(10) not null

,sell_goods_amount int not null

);

--定义存储过程（在SQL编辑框中输入如下语句，最后输入“/” 执行）

CREATE PROCEDURE insert_data（）  

IS

 BEGIN

 INSERT INTO sell_list_info select * from sell_list_info_tmp1;  

 INSERT INTO sell_list_info select * from sell_list_info_tmp2;  

 INSERT INTO sell_list_info select * from sell_list_info_tmp3;  

 INSERT INTO sell_list_info select * from sell_list_info_tmp4;  

 INSERT INTO sell_list_info select * from sell_list_info_tmp5;  

END;

/

示例截图：

--调用存储过程

CALL insert_data();

7、新建用户并授权访问

假设新增了一个管理用户，该用户想访问零售数据库（retail_db），则该员工需要向sys申请添加相关权限，具体操作如下：

7.1 连接数据库后，进入SQL命令界面。创建用户user002，密码为openGauss@123。

CREATE USER user002 IDENTIFIED BY 'openGauss@123';

7.2 给用户user002授予retail_db数据库下的订货单信息表(order_info)的查询和插入权限：

GRANT SELECT,INSERT ON order_info TO user002;

7.3退出数据库：

postgres=#\q

7.4新用户连接数据库

用gsql登录数据库，使用新用户连接。使用操作系统omm用户在新的窗口登陆并执行以下命令，并输入对应的密码：

gsql -d retail_db -U user002 -p 26000 –r

7.5访问order_info数据库的表order_info：

select * from order_info;

六、总结

超市进销存管理系统是一个庞大复杂的系统，进销存软件涉及的模块也是非常之多，主要包括前端的交互（销售过程管理）、后端的管理（客户管理、用户管理、货品采购管理、货品入库/出库管理、财务总账管理、维护管理、优化改进管理等等），甚至可以上升到后台整个企业的管理（ERP）。由此可以延伸到整个物理架构设计、逻辑架构设计、数据管理（治理）方案、数据存储策略等。

本文设计仅从最基本的核心表设计出发，使用openGauss数据库，完成最基本的超市进销存管理任务，其他更多细节，欢交流。

「其他文章」