超市進銷存之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,'張五','zh[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資料庫，完成最基本的超市進銷存管理任務，其他更多細節，歡交流。

「其他文章」