一细睡、背景

數(shù)字經(jīng)濟時代，數(shù)據(jù)處理需求大規(guī)模增長忍宋，數(shù)據(jù)庫在充分挖掘數(shù)據(jù)資產(chǎn)價值痕貌、賦能產(chǎn)業(yè)數(shù)字化轉(zhuǎn)型、推進數(shù)字經(jīng)濟生態(tài)建設(shè)過程中發(fā)揮著重大作用糠排。

經(jīng)營管理一家超市舵稠，無論是商品的管理、商品采購的管理還是商品銷售的管理入宦，如果單純依靠純?nèi)斯す芾聿富玻粌H工作量巨大，還容易出錯乾闰，造成不可預(yù)估的損失落追。基于此涯肩，數(shù)據(jù)庫的選擇與設(shè)計就尤為重要轿钠。

二、目的

本文以零售行業(yè)為場景病苗，設(shè)計數(shù)據(jù)庫模型疗垛，并使用openGauss數(shù)據(jù)庫構(gòu)建零售業(yè)務(wù)場景下的超市進銷存數(shù)據(jù)庫。通過對數(shù)據(jù)庫中對象（表硫朦、數(shù)據(jù)類型贷腕、視圖、約束、存儲過程花履、用戶等）的創(chuàng)建芽世，掌握openGauss數(shù)據(jù)庫基礎(chǔ)SQL語法，并通過對表中數(shù)據(jù)的增刪改查诡壁，模擬零售行業(yè)下的業(yè)務(wù)實現(xiàn)。

三荠割、什么是“進銷存”妹卿，什么是超市進銷存管理系統(tǒng)？

進銷存軟件概念起源于上世紀(jì)80年代蔑鹦，由于電算化的普及夺克，計算機管理的廣泛推廣，不少企業(yè)對于倉庫貨品的進貨嚎朽，存貨铺纽，出貨管理，有了強烈的訴求哟忍，基于此狡门，進銷存軟件的發(fā)展從此便開始了。進入90年代以后锅很，隨著進銷存軟件的應(yīng)用面越來越廣其馏，出現(xiàn)了許多從功能上，從用途上爆安，更為全面的進銷存系統(tǒng)叛复，從單純的貨品數(shù)量管理，擴展為貨品的流程管理扔仓，進銷存軟件對每一批貨品的來源褐奥、存放、去向翘簇，都作了詳細的記錄撬码，隨后即延伸為財務(wù)進銷存一體化的管理模式。進銷存是指企業(yè)管理過程中采購（進）——入庫（存）——銷售（銷）的動態(tài)管理過程缘揪。主要可以分為如下兩條線：1）商品流通企業(yè)的進銷存是從商品的采購（進）——入庫（存）——銷售（銷）的動態(tài)管理過程耍群。2）工業(yè)企業(yè)的進銷存是從原材料的采購（進）——入庫（存）——領(lǐng)料加工——產(chǎn)品入庫(存)——銷售(銷)的動態(tài)管理過程。

四找筝、什么是openGauss數(shù)據(jù)庫蹈垢？

openGauss是一個數(shù)據(jù)庫管理系統(tǒng)。數(shù)據(jù)庫是結(jié)構(gòu)化的數(shù)據(jù)集合袖裕。它可以是任何數(shù)據(jù)曹抬，購物清單、圖片庫或公司網(wǎng)絡(luò)中的大量信息急鳄。數(shù)據(jù)庫管理系統(tǒng)可以對數(shù)據(jù)庫進行統(tǒng)一的管理和控制谤民，以保證數(shù)據(jù)庫的安全性和完整性堰酿。由于計算機非常擅長處理大量數(shù)據(jù)，因此數(shù)據(jù)庫管理系統(tǒng)可以作為獨立程序使用张足，也可以作為其他應(yīng)用程序的一部分在計算中發(fā)揮著核心作用触创。 openGauss數(shù)據(jù)庫是關(guān)系型的。關(guān)系型數(shù)據(jù)庫是指采用了關(guān)系模型來組織數(shù)據(jù)的數(shù)據(jù)庫为牍，其以行和列的形式存儲數(shù)據(jù)哼绑。行和列被稱為表，一組表組成了數(shù)據(jù)庫碉咆。關(guān)系模型可以簡單理解為二維表格模型抖韩，而一個關(guān)系型數(shù)據(jù)庫就是由二維表及其之間的關(guān)系組成的一個數(shù)據(jù)組織。 openGauss的SQL部分代表 “結(jié)構(gòu)化查詢語言”疫铜。SQL是最常用的用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)計算機語言茂浮。根據(jù)系統(tǒng)的編程環(huán)境，用戶可以直接輸入SQL壳咕、將SQL語句嵌入到以另一種語言編寫的代碼中席揽，或者使用包含SQL語法的特定語言 API。openGauss支持標(biāo)準(zhǔn)的SQL92/SQL99/SQL2003/SQL2011規(guī)范囱井。

五驹尼、應(yīng)用與實踐（模擬超市進銷存系統(tǒng)）

說明：以下庫表涉及到的字段類型、sql語句等均是基于openGauss數(shù)據(jù)庫進行的庞呕。

1新翎、超市進銷存數(shù)據(jù)庫表設(shè)計

本次設(shè)計僅從最基本的核心表設(shè)計出發(fā)，完成最基本的進銷存管理任務(wù)住练，具體涉及如下幾張核心表地啰，如遇臨時表或者視圖或者存儲過程，則均以SQL的形式實現(xiàn)：

1讲逛、用戶管理表（User_info）

2亏吝、供應(yīng)商信息表(supplier_info)

3、訂貨單信息表（order_info）

4盏混、進貨信息表（Purchase_goods_info）

5蔚鸥、倉庫信息表（warehouse_info）

6、商品信息表（goods_info）

7许赃、出庫信息表（Outwarehouse_info)

8止喷、銷售清單信息表（sell_list_info）

9、銷售信息表（sell_info）

10混聊、客戶管理表(customer_info)

2弹谁、創(chuàng)建數(shù)據(jù)庫表

前置條件：已成功安裝openGauss數(shù)據(jù)庫奉狈。2.1飒泻、創(chuàng)建超市（零售行業(yè)）數(shù)據(jù)庫（retail_db）眼五。切換到omm用戶旷太，以操作系統(tǒng)用戶omm登錄數(shù)據(jù)庫主節(jié)點。

su - omm

啟動數(shù)據(jù)庫金句。

gs_om -t start

使用gsql工具登陸數(shù)據(jù)庫贞瞒。

gsql -d postgres -p 26000 -r

創(chuàng)建數(shù)據(jù)庫retail_db乒融。

CREATE DATABASE retail_db ENCODING 'UTF8' template = template0;

連接retail_db數(shù)據(jù)庫。

\connect retail_db

創(chuàng)建名為retail_db的schema，并設(shè)置retail_db為當(dāng)前的schema。

CREATE SCHEMA retail_db;

將默認(rèn)搜索路徑設(shè)為retail_db。

SET search_path TO retail_db;

2.2、創(chuàng)建用戶管理表（User_info）

在SQL編輯框中輸入如下語句壶愤，創(chuàng)建用戶管理表user_info。刪除表 user_info治力。

刪除表

user_infoDROP TABLE IF EXISTS user_info;

創(chuàng)建表 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马澈、創(chuàng)建供應(yīng)商信息表(supplier_info)

在SQL編輯框中輸入如下語句，創(chuàng)建表supplier_info

刪除表 supplier_info

DROP TABLE IF EXISTS supplier_info;

創(chuàng)建表 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国章、創(chuàng)建訂貨單信息表（order_info）

在SQL編輯框中輸入如下語句，創(chuàng)建表order_info

刪除表 order_info

DROP TABLE IF EXISTS order_info;

創(chuàng)建表 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粗恢、創(chuàng)建進貨信息表（Purchase_goods_info）

在SQL編輯框中輸入如下語句，創(chuàng)建表purchase_goods_info

刪除表 Purchase_goods_info

DROP TABLE IF EXISTS Purchase_goods_info;

創(chuàng)建表 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拴魄、創(chuàng)建倉庫信息表（warehouse_info）

在SQL編輯框中輸入如下語句豪诲，創(chuàng)建表warehouse_info

刪除表 warehouse_info

DROP TABLE IF EXISTS warehouse_info;

創(chuàng)建表 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、創(chuàng)建商品信息表（goods_info）

在SQL編輯框中輸入如下語句，創(chuàng)建表goods_info

刪除表 goods_info

DROP TABLE IF EXISTS goods_info;

創(chuàng)建表 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、創(chuàng)建出庫信息表（Outwarehouse_info)

在SQL編輯框中輸入如下語句，創(chuàng)建表outwarehouse_info

刪除表 outwarehouse_info

DROP TABLE IF EXISTS outwarehouse_info;

創(chuàng)建表 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练链、創(chuàng)建銷售清單信息表（sell_list_info）错妖、

在SQL編輯框中輸入如下語句，創(chuàng)建表sell_list_info

刪除表 sell_list_info

DROP TABLE IF EXISTS sell_list_info;

創(chuàng)建表 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));

2.10灯节、創(chuàng)建銷售信息表（sell_info）

在SQL編輯框中輸入如下語句浓若，創(chuàng)建表sell_info

刪除表 sell_info

DROP TABLE IF EXISTS sell_info;

創(chuàng)建表 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浦徊、創(chuàng)建客戶管理表(customer_info)

在SQL編輯框中輸入如下語句蚯妇，創(chuàng)建表customer_info

刪除表 customer_info

DROP TABLE IF EXISTS customer_info;

創(chuàng)建表 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)

);

3务漩、手工插入數(shù)據(jù)

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

3.1對user_info表進行插入數(shù)據(jù)操作，在SQL編輯框中輸入如下語句：

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

VALUES (001,'張一','zhangyi@openGauss.com','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,'張二','zhanger@openGauss.com','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,'張三','zhangsan@openGauss.com','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,'張四','zhangsi@openGauss.com','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,'張五','zhangwu@openGauss.com','2023005','12345678905','openGauss_005',5);

3.2對supplier_info表進行插入數(shù)據(jù)操作，在SQL編輯框中輸入如下語句：

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

VALUES(001,'xxx水果批發(fā)商','西安市高陵區(qū)xxx','12345678901',1,'');

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

VALUES(002,'xxx日用百貨批發(fā)商','西安市雁塔區(qū)xxx','12345678902',2,'');

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

VALUES(003,'xxx煙酒飲料批發(fā)商','西安市未央?yún)^(qū)xxx','12345678903',3,'');

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

VALUES(004,'xxx零食批發(fā)商','西安市碑林區(qū)xxx','12345678904',4,'');

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

VALUES(005,'xxx柴米油鹽醬醋批發(fā)商','西安市新城區(qū)xxx','12345678905',5,'');

3.3 對warehouse _info表進行插入數(shù)據(jù)操作，在SQL編輯框中輸入如下語句：

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

VALUES(001,'園區(qū)100-1','1號倉庫',4,'');

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

VALUES(002,'園區(qū)100-2','2號倉庫',4,'');

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

VALUES(003,'園區(qū)100-3','3號倉庫',4,'');

3.4對sell_list_info表進行插入數(shù)據(jù)操作啃洋，在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传货、添加約束

現(xiàn)實場景中，銷售清單信息表（sell_list_info）中的商品售價不可能為負(fù)數(shù)宏娄，因此針對表中金額的屬性问裕，增加大于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,'');

執(zhí)行失敗孵坚，失敗原因：new row for relation " sell_list_info" violates check constraint "c_sell_goods_amount "粮宛。

5窥淆、創(chuàng)建視圖

視圖是一個虛擬表，是sql的查詢結(jié)果巍杈，其內(nèi)容由查詢定義忧饭。對于來自多張關(guān)聯(lián)表的復(fù)雜查詢，就不得不使用十分復(fù)雜的SQL語句進行查詢筷畦，造成極差的體驗感词裤。使用視圖之后，可以極大的簡化操作鳖宾，使用視圖不需要關(guān)心相應(yīng)表的結(jié)構(gòu)吼砂、關(guān)聯(lián)條件等。

場景：創(chuàng)建一視圖攘滩，統(tǒng)計2023-01-16這一天所有銷售商品的銷售總數(shù)量和總銷售額帅刊，以及其對應(yīng)的倉庫編號和供應(yīng)商編號（倉庫和供應(yīng)商可進一步關(guān)聯(lián)），并按銷售總數(shù)量降序排列漂问。

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;

創(chuàng)建表 sell_info

調(diào)用存儲過程CALL insert_data();

7赖瞒、新建用戶并授權(quán)訪問

假設(shè)新增了一個管理用戶，該用戶想訪問零售數(shù)據(jù)庫（retail_db）蚤假，則該員工需要向sys申請?zhí)砑酉嚓P(guān)權(quán)限栏饮，具體操作如下：

7.1、連接數(shù)據(jù)庫后磷仰，進入SQL命令界面袍嬉。創(chuàng)建用戶user002，密碼為openGauss@123灶平。

CREATE USER user002 IDENTIFIED BY 'openGauss@123';

7.2伺通、 給用戶user002授予retail_db數(shù)據(jù)庫下的訂貨單信息表(order_info)的查詢和插入權(quán)限：

GRANT SELECT,INSERT ON order_info TO user002;

7.3、退出數(shù)據(jù)庫：

postgres=#\q

7.4逢享、新用戶連接數(shù)據(jù)庫用gsql登錄數(shù)據(jù)庫罐监，使用新用戶連接。使用操作系統(tǒng)omm用戶在新的窗口登陸并執(zhí)行以下命令瞒爬，并輸入對應(yīng)的密碼：

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

7.5弓柱、訪問order_info數(shù)據(jù)庫的表order_info：select * from order_info;

六、總結(jié)

超市進銷存管理系統(tǒng)是一個龐大復(fù)雜的系統(tǒng)侧但，進銷存軟件涉及的模塊也是非常之多矢空，主要包括前端的交互（銷售過程管理）、后端的管理（客戶管理禀横、用戶管理屁药、貨品采購管理、貨品入庫/出庫管理柏锄、財務(wù)總賬管理者祖、維護管理立莉、優(yōu)化改進管理等等），甚至可以上升到后臺整個企業(yè)的管理（ERP）七问。由此可以延伸到整個物理架構(gòu)設(shè)計蜓耻、邏輯架構(gòu)設(shè)計、數(shù)據(jù)管理（治理）方案械巡、數(shù)據(jù)存儲策略等刹淌。

本文設(shè)計僅從最基本的核心表設(shè)計出發(fā)，使用openGauss數(shù)據(jù)庫讥耗，完成最基本的超市進銷存管理任務(wù)有勾，其他更多細節(jié)，歡迎交流古程。