进销存管理系统数据库设计

1、关系数据库标准语言SQL1进销存管理系统数据库设计31. 码表32. 基本信息表43. 单据表54. 账表9SQL 语句建表示例113SQL 语句建表示例215利用SQL语句实现采购管理23利用触发器实现进销存管理2755关系数据库标准语言SQLü关系模型定义三类完整性约束：实体完整性、参照完整性、用户定义的完整性SQL语言的动词SQL 功 能动 词数 据 定 义CREATE，DROP，ALTER数 据 查 询SELECT 数 据 操 纵INSERT，UPDATE，DELETE数 据 控 制GRANT，REVOKE操作语句定义基本表CREATE TABLE <表名> (&

2、lt;列名> <数据类型>列级完整性约束条件 ,<列名> <数据类型>列级完整性约束条件 , <列级完整性约束条件>)；修改基本表ALERT TABLE <表名> ADD<新列名> <数据类型>完整性约束 DROP 完整性约束名MODIFY<列名> <数据类型>；删除基本表DROP TABLE <表名>建立索引CREATE UNIQUE CLUSTER INDEX <索引名> ON <表名> (<列名><次序>, (<

3、;列名><次序>);删除索引DROP INDEX <索引名> ON <表名> (<列名><次序>, (<列名><次序>);查询操作SELECT ALL|DISTINCT<目标列表达式>,<目标列表达式>FROM <表名或视图名>,<表名或视图名>WHERE <条件表达式>GROUP BY <列名1>HAVING<条件表达式>ORDER BY <列名2>ASC|DSC;集函数，包括：COUNT (DISTINCT

4、|ALL *) 统计元组个数COUNT (DISTINCT|ALL <列名>) 统计一列中值的个数SUM (DISTINCT|ALL <列名>) 计算一列值的总和（必须是数值型）AVG (DISTINCT|ALL <列名>) 求一列值的平均值（必须是数值型）MAX (DISTINCT|ALL <列名>) 求一列值中的最大值MIN (DISTINCT|ALL <列名>) 求一列值中的最小值插入操作INSERT INTO <表名>(<属性列1>, <属性列2>)VALUES (<常量1>,

5、<常量2>);INSERT INTO <表名>(<属性列1>, <属性列2>)子查询;修改操作UPDATE <表名> SET <列名>=<表达式>, <列名>= <表达式>WHERE <条件>删除操作DELETE FROM <表名> WHERE <条件>建立视图CREATE VIEW <视图名>(列名>, <列名>) AS <子查询>WITH CHECK OPTION;删除视图DROP VIEW <视图名

6、>用户授权操作权限GRANT <权限>, <权限> ON<对象类型> <对象名> TO <用户>, <用户> WITH GRANT OPTION;对象对象类型操 作 权 限属性列TABLESELECT, INAERT, UPDAT, DELETE, ALL PRIVILEGES视 图TABLE同上基本表TABLE同上ALERT, INDEX数据库DATABASECREATTAB收回权限REVOKE <权限>, <权限> ON<对象类型> <对象名> FROM <用

7、户>, <用户> ;进销存管理系统数据库设计系统表分为码表、基本信息表、单据表和账表。其中码表共五张，包括往来单位码表、商品种类码表、商品计量单位码表、部门码表、商品产地码表。基本信息表共两张，包括操作员表、商品信息表。单据表共九张，包括采购单主项与采购单明细项、销售单主项与销售单明细项、报损单主项与报损单明细项、付款单、收款单、商品盘存表。账表共八张，包括库存初始化库存表、初始化应付账表、初始化应收账表、商品库存表、流水账表、应付账表、应收账表、进销存数量报表。1. 码表 (1) 码表中文名称：商品种类码表，见表1。表 名：C_SORT关 键 字：SORT _CODE备 注

8、： 确定系统所涉及的商品种类。表1 商品种类码表列名中文名称类型与长度空否说明SORT _CODE商品种类编码char(2)否默认取值为：“01” 手机、“02” 相机、“03” 音响。SORT _NAME 商品种类名称char(50)是i (2) 码表中文名称：商品计量单位码表，见表2。表 名：C_UNIT关 键 字：UNIT _CODE备 注：确定系统所应用的商品计量单位。表2 商品计量单位码表列名中文名称类型与长度空否说明UNIT _CODE商品计量单位编码char(2)否默认取值为：“01”部、“02” 台。UNIT _NAME商品计量单位名称char(10)是 (3) 码表中文名称：

9、商品产地码表，见表3。表 名：C_AERO关 键 字：AERO _CODE备 注： 定义系统所涉及的商品产地。表3 商品产地码表列名中文名称类型与长度空否说明AERO _CODE商品产地编码char(10)否AERO _NAME商品产地名称char(100)是 (4) 码表中文名称：往来单位码表，见表4。表 名：C_SUPP关 键 字：SUPP _CODE备 注：设定提供商品的供货单位。表4 往来单位码表列名中文名称类型与长度空否说明SUPP _CODE往来单位编码char(10)否SUPP _NAME往来单位名称char(100)否ZIP邮编char(6)是ADDR地址char(100)是T

10、EL电话char(20)是FAX传真char(20)是EMAIL电子邮箱char(100)是WEB网址char(100)是ACCOUNT账号char(30)是BANK开户行char(100)是 (5) 码表中文名称：部门码表，见表5。表 名：C_DEPT关 键 字：DEPT_CODE备 注：设定企业内部的部门。表5 部门码表列名中文名称类型与长度空否说明DEPT_CODE部门编码char(10)否DEPT_NAME部门名称char(50)是2. 基本信息表 (1) 码表中文名称：操作员表，见表6。表 名：C_OPERATOR关 键 字：OPER_CODE备 注：记录系统的操作员信息，包括编码、

11、姓名、密码、权限等。表6 操作员表列名中文名称类型与长度空否说明OPER_CODE操作员编码char(10)否OPER_NAME 操作员姓名char(20)是PASSWORD密码char(10)是设定操作员登录系统的密码，避免非法用户进入系统。DEPT_CODE部门char(10)是操作员所属部门。POWER权限char(10)是操作员所具有的权限。 (2) 码表中文名称：商品信息表，表7。表 名：C_GOODS关 键 字：GOODS _CODE备 注：系统的所有商品信息。表7 商品信息表列名中文名称类型与长度空否说明GOODS _CODE商品编码char(13)否GOODS_NAME商品名称

12、char(60)是SORT _CODE商品种类char(2)是MODEL规格型号char(50)是UNIT _CODE计量单位char(2)是PRICE_RETAIL零售价decimal(8,2)是PRICE_PLAN计划价decimal(8,2)是AERO_CODE产地char(10)是SUPP_CODE供应商char(10)是NOTE备注varchar(200)是PHOTEFILE图片char(100)是图片文件的文件名为商品编码，格式为.bmp，由系统自动写入。3. 单据表 (1) 单据表中文名称：采购单主项，见表8。表 名：SHEET_CG_MAIN关 键 字：SHEETID备 注：采

13、购单由主项及明细项组成。表8 采购单主项列 名中文名称类型与长度空否说 明SHEETID单据号char(13)否在新增采购单时，采购单据号由系统自动生成。SHEETDATE日期date是采购单的制单日期。OPER_CODE制单人char(10)是默认为登录系统的操作员，不可修改。MONE金额decimal(10,2)是采购商品总金额。SUPP_CODE供应商char(10)是商品采购所对应的供应商。FLAG_JZ记账标记char(2)是取值为：“是”、“否”；已记账的采购单不能修改。MAN_JZ记账人char(10)是默认为登录系统的操作员，不可修改。NOTE备注varchar(200)是对采

14、购业务进行解释说明。 (2) 单据表中文名称：采购单明细项，见表9。表 名：SHEET_CG_ITEM关 键 字：SHEETID + ITEMNO备 注：采购单由主项及明细项组成。表9 采购单明细项列名中文名称类型与长度空否说明SHEETID单据号char(13)否ITEMNO序号integer否GOODS_CODE商品编码char(13)是AMOUNT数量integer是商品购买数量。PRICE单价decimal(8,2)是由系统根据商品编码自动填入，单价为商品售价。MONE金额decimal(10,2)是计算方法：金额=数量*单价。NOTE备注varchar(200)对每项商品的采购情况进

15、行的解释说明。 (3) 单据表中文名称：销售单主项，见表10。表 名：SHEET_XS_MAIN关 键 字：SHEETID备 注：销售单由主项及明细项组成。表10 销售单主项列名中文名称类型与长度空否说明SHEETID单据号char(13)否在新增销售单时，销售单据号由系统自动生成。SHEETDATE日期date是销售单的制单日期OPER_CODE制单人char(10)是默认为登录系统的操作员，不可修改。MONE金额decimal(10,2)是销售商品总金额。FLAG_JZ记账标记char(2)是取值为：“是”、“否”；已记账的销售单不能修改。MAN_JZ记账人char(10)默认为登录系统的

16、操作员，不可修改。NOTE备注varchar(200)是对销售业务进行解释说明。 (4) 单据表中文名称：销售单明细项，见表11。表 名：SHEET_XS_ITEM关 键 字：SHEETID + ITEMNO备 注：销售单由主项及明细项组成。表11 销售单明细项列名中文名称类型与长度空否说明SHEETID单据号char(13)否ITEMNO序号integer否GOODS_CODE商品编码char(13)是AMOUNT数量integer是某项商品销售数量。PRICE单价decimal(8,2)是由系统根据商品编码自动填入，单价为商品售价。DISCOUNT折扣decimal(5,3)是销售折扣。M

17、ONE金额decimal(10,2)是计算方法：金额=单价*数量*折扣。NOTE备注varchar(200)是对每项商品销售情况进行解释说明。 (5) 单据表中文名称：报损单主项，见表12。表 名：SHEET_BS_MAIN关 键 字：SHEETID备 注：报损单由主项及明细项组成。表12 报损单主项列 名中文名称类型与长度空否说 明SHEETID单据号char(13)否在新增报损单时，报损单据号由系统自动生成。SHEETDATE日期date是报损单的制单日期。OPER_CODE制单人char(10)是默认为登录系统的操作员，不可修改。MONE金额decimal(10,2)是报损销售商品总金额

18、。FLAG_JZ记账标记char(2)是取值为：“是”、“否”；已记账的报损单不能修改。MAN_JZ记账人char(10)是默认为登录系统的操作员，不可修改。NOTE备注varchar(200)是对报损情况进行解释说明。 (6) 单据表中文名称：报损单明细项，见表13。表 名：SHEET_BS_ITEM关 键 字：SHEETID + ITEMNO备 注：报损单由主项及明细项组成。表13 报损单明细项列名中文名称类型与长度空否说明SHEETID单据号char(13)否ITEMNO序号integer否GOODS_CODE商品编码char(13)是AMOUNT数量integer是某项商品报损数量。P

19、RICE单价decimal(8,2)是由系统根据商品编码自动填入，单价为商品售价。MONE金额decimal(10，2)是计算方法：金额=数量*单价。NOTE报损原因varchar(200)是说明商品报损的原因。 (7) 单据表中文名称：商品盘存表，见表14。表 名：TABLE_GOODS_PC关 键 字：DATE_PC + GOODS_CODE备 注：定期进行商品盘存处理。获得商品的盈亏数量，并通过盘存，及时调整商品的账面数量。表14 商品盘存表列 名中文名称类型与长度空否说 明DATE_PC盘存日期date否进行商品盘存的日期。GOODS_CODE商品编码char(10)否AMOUNT_Z

20、M账面数量integer是当前商品库存表中的商品数量。AMOUNT_SP实盘数量integer是经过实际盘点的商品数量。AMOUNT_YK盈亏数量integer是盈亏数量=实盘数量 - 账面数量OPER_CODE制单人char(10)是默认为登录系统的操作员，不可修改。FLAG_JZ记账标记char(2)是取值为：“是”、“否”；已记账的商品盘存表不能修改。MAN_JZ记账人char(10)是默认为登录系统的操作员，不可修改。NOTE备注varchar(200)是对商品盘存表的解释说明。 (8)单据表中文名称：收款单，见表15。表 名：SHEET_SK关 键 字：SHEETID备 注：针对销售

21、单据号进行收款处理。表15 收款单列 名中文名称类型与长度空否说 明SHEETID单据号char(13)否在新增收款单时，收款单据号由系统自动生成。SHEETDATE日期date是收款单的制单日期。OPER_CODE制单人char(10)是默认为登录系统的操作员，不可修改。SHEETID_XS销售单据号char(13)是收款单针对的销售单据号。MONE金额decimal(10,2)是收款金额。FLAG_JZ记账标记char(2)是取值为：“是”、“否”；已记账的收款单不能修改。MAN_JZ记账人char(10)是默认为登录系统的操作员，不可修改。NOTE备注varchar(200)是对收款单进

22、行解释说明。 (9) 单据表中文名称：付款单，见表16。表 名：SHEET_FK关 键 字：SHEETID备 注：针对供应商进行付款处理。表16 付款单列名中文名称 类型与长度空否说明SHEETID单据号char(13)否在新增付款单时，付款单据号由系统自动生成。SHEETDATE日期date是付款单的制单日期。OPER_CODE制单人char(10)是默认为登录系统的操作员，不可修改。MONE金额decimal(10,2)是付款金额。SUPP_CODE供应商char(10)是针对供应商付款。FLAG_JZ记账标记char(2)是取值为：“是”、“否”；已记账的付款单不能修改。MAN_JZ记账

23、人char(10)是默认为登录系统的操作员，不可修改。NOTE备注varchar(200)是对付款情况进行解释说明。4. 账表(1) 账表中文名称：初始化库存表，见表17。表 名：INI_GOODS_AMOUNT关 键 字：GOODS_CODE备 注：通过初始化库存，系统可以将初始数据自动转入商品库存表、进销存数量报表。表17 初始化库存表列名中文名称类型与长度空否说明GOODS_CODE商品编码char(13)否AMOUNT库存integer是商品期初库存数量。FLAG_JZ记账标记char(2)是取值为：“是”、“否”。MAN_JZ记账人char(10)是默认为登录系统的操作员编码。 (2

24、) 账表中文名称：初始化应付账表，见表18。表 名：INI_REPORT_YF_MONE关 键 字：SUPP_CODE备 注：本系统针对供应商挂应付账。通过初始化应付账表，系统可以将初始数据将自动转入应付账表。表18 初始化应付账表列 名中文名称类型与长度空否说 明SUPP_CODE供应商char(10)否MONE_YF应付金额decimal(10,2)是系统启用期初应付金额。MONE_FK付款金额decimal(10,2)是系统启用期初已付款金额。MONE_YE余额decimal(10,2)是期初应付账款余额。NOTE备注varchar(200)是对应付账款情况进行解释说明。FLAG_JZ记

25、账标记char(2)是取值为：“是”、“否”。MAN_JZ记账人char(10)是默认为登录系统的操作员编码。（3）账表中文名称：初始化应收账表，见表19。表 名：INI_REPORT_YS_MONE关 键 字：SHEETID_XS备 注：本系统针对销售单据号挂应收账。通过初始化应收账表，系统可以将初始数据将自动转入应收账表。表19 初始化应收账表列 名中文名称类型与长度空否说 明SHEETID_XS销售单据号char(13)否MONE_YS应收金额decimal(10,2)是系统启用期初应收账款金额。MONE_SK收款金额decimal(10,2)是系统启用期初已收款金额。MONE_YE余额

26、decimal(10,2)是期初应收账款余额。NOTE备注varchar(200)是对应收账款情况进行解释说明。FLAG_JZ记账标记char(2)是取值为：“是”、“否”。MAN_JZ记账人char(10)是默认为登录系统的操作员编码。 (4)账表中文名称：商品库存表，见表20。表 名：GOODS_AMOUNT关 键 字：GOODS_CODE备 注：商品当前的库存数量。表20 商品库存表列名中文名称类型与长度空否备注GOODS_CODE商品编码char(13)否AMOUNT库存integer是商品当前的账面库存数量。 (5)账表中文名称：流水账表，见表21。表 名：GOODS_RECORD关

27、 键 字：SHEETDATE + GOODS_CODE + ITEMNO备 注：详细记录每一笔采购、销售、报损、盘存等业务处理的数据情况。表21 流水账表列 名中文名称类型与长度空否说 明SHEETDATE日期date否表示业务发生的日期。GOODS_CODE商品编码char(13)否ITEMNO序号integer否对同一日期、同一商品的商品采购、销售、报损、盘存情况进行排序。SHEETTYPE单据类型char(10)是取值为：“采购”、“销售”、“报损”、“盘存”。SHEETID单据号char(13)是经济业务发生的单据号AMOUNT_ADD增加数量integer是经济业务发生后，导致商品增

28、加的数量。AMOUNT_SUB减少数量integer是经济业务发生后，导致商品减少的数量。PRICE单价decimal(8,2)是商品单价。DISCOUNT折扣decimal(5,3)是商品折扣，与单据上折扣一致。 (6) 账表中文名称：进销存数量报表，见表22。表 名：REPORT_AMOUNT关 键 字：REPORTDATE_QS + GOODS_CODE备 注：在某一时间段内根据已记账的采购单、销售单、报损单、商品盘存表进行的期初库存数量、采购数量、销售数量、报损数量、期末库存账面数量、盈亏数量、期末实盘数量计算结果。表22 进销存数量报表列名中文名称类型与长度空否说明REPORTDAT

29、E_QS起始日期date否进销存数量报表的起始日期。REPORTDATE_JS结束日期date是进销存数量报表的结束日期。GOODS_CODE商品编码char(13)否AMOUNT_QC期初库存数量integer是期初库存数量一般为上一期间的期末实盘数量。AMOUNT_CG采购数量integer是在时间范围内，商品采购数量总和。AMOUNT_XS销售数量integer是在时间范围内，商品销售数量总和。AMOUNT_BS报损数量integer是在时间范围内，商品报损数量总和。AMOUNT_QM期末库存账面数量integer是商品结束日期的账面数量。AMOUNT_YK盈亏数量integer是计算方

30、法：盈亏数量=实盘数量-账面数量。AMOUNT_SP期末实盘数量integer是期末库存账面数量=期初库存数量 + 采购数量 - 销售数量 - 报损数量；期末实盘数量=期末库存账面数量 +盈亏数量。NOTE备注varchar(200)是解释说明。 (7) 账表中文名称：应付账表，见表23。表 名：REPORT_YF_MONE关 键 字：SUPP_CODE + ITEMNO备 注：按供应商进行挂账的应付及付款明细。表23 应付账表列名中文名称类型与长度空否说明SUPP_CODE供应商char(10)否ITEMNO序号integer否SHEETDATE日期date是单据日期。MONE_YF应付金额

31、decimal(10,2)是源自于采购单中的金额。MONE_FK付款金额decimal(10,2)是源自于付款单据中的金额。MONE_YE余额decimal(10,2)是计算方法：余额=应付金额 - 付款金额SHEETTYPE单据类型char(10)是本笔业务对应的单据类型。取值为：“采购”、“付款”。SHEETID单据号char(13)是单据类型所对应得单据号。NOTE备注varchar(200)是解释说明。 (8) 账表中文名称：应收账表，见表24。表 名：REPORT_YS_MONE关 键 字：SHEETID_XS + ITEMNO备 注：按销售单据进行挂账的应收及收款明细。表24 应收

32、账表列名中文名称类型与长度空否说明SHEETID_XS销售单据号char(13)否本笔业务对应的销售单据号。ITEMNO序号integer否SHEETDATE日期date是单据日期。MONE_YS应收金额decimal(10,2)是源自于销售单据中的金额。MONE_SK收款金额decimal(10,2)是源自于收款单据中的金额。MONE_YE余额decimal(10,2)是计算方法：余额=应收金额 - 收款金额。SHEETID_SK收款单据号char(13)是本笔业务对应的收款单据号。NOTE备注varchar(200)是解释说明。SQL 语句建表示例1drop table C_SUPP;dr

33、op table C_SORT;drop table C_UNIT;drop table C_AERO;drop table C_GOODS;create table C_AERO( AERO_CODE char(10) not null, AERO_NAME char(100) not null, primary key (AERO_CODE);create table C_UNIT( UNIT_CODE char(2) not null, UNIT_NAME char(10) not null, primary key (UNIT_CODE);create table C_SORT( SO

34、RT_CODE char(2) not null, SORT_NAME char(50) not null, primary key (SORT_CODE);create table C_SUPP( SUPP_CODE char(10) not null, SUPP_NAME char(100) not null, ZIP char(6) , ADDR char(100) , TEL char(20) , FAX char(20) , EMAIL char(100) , WEB char(100) , ACCOUNT char(30) , BANK char(100) , primary ke

35、y (SUPP_CODE);create table C_GOODS( GOODS_CODE char(13) not null, GOODS_NAME char(60) , SORT_CODE char(2) , MODEL char(50) , UNIT_CODE char(2) , PRICE_RETAIL decimal(8,2) , PRICE_PLAN decimal(8,2) , AERO_CODE char(10) , SUPP_CODE char(10) , NOTE varchar(200) , PHOTOFILE char(100) , primary key (GOOD

36、S_CODE);alter table C_GOODS add foreign key FK_C_GOODS_REF_C_SORT (SORT_CODE) references C_SORT (SORT_CODE) on update restrict on delete restrict;alter table C_GOODS add foreign key FK_C_GOODS_REF_C_UNIT (UNIT_CODE) references C_UNIT (UNIT_CODE) on update restrict on delete restrict;alter table C_GO

37、ODS add foreign key FK_C_GOODS_REF_C_AERO (AERO_CODE) references C_AERO (AERO_CODE) on update restrict on delete restrict;alter table C_GOODS add foreign key FK_C_GOODS_REF_C_SUPP (SUPP_CODE) references C_SUPP (SUPP_CODE) on update restrict on delete restrict;SQL 语句建表示例2drop view View_goods;drop vie

38、w View_sheet_bs;drop view View_sheet_cg;drop view View_sheet_xs;drop table INI_REPORT_YS_MONE;drop table INI_REPORT_YF_MONE;drop table INI_GOODS_AMOUNT;drop table TABLE_GOODS_PC;drop table SHEET_XS_ITEM;drop table SHEET_XS_MAIN;drop table SHEET_BS_ITEM;drop table SHEET_BS_MAIN;drop table SHEET_FK;dr

39、op table SHEET_SK;drop table SHEET_CG_ITEM;drop table SHEET_CG_MAIN;drop table REPORT_YS_MONE;drop table REPORT_YF_MONE;drop table GOODS_RECORD;drop table GOODS_AMOUNT;drop table REPORT_AMOUNT;drop table C_DEPT;drop table C_SUPP;drop table C_OPERATOR;drop table C_SORT;drop table C_GOODS;drop table C

40、_UNIT;drop table C_AERO;create table C_AERO( AERO_CODE char(10) not null, AERO_NAME char(100) not null, primary key (AERO_CODE);create table C_UNIT( UNIT_CODE char(2) not null, UNIT_NAME char(10) not null, primary key (UNIT_CODE);create table C_GOODS( GOODS_CODE char(13) not null, GOODS_NAME char(60

41、) , SORT_CODE char(2) , MODEL char(50) , UNIT_CODE char(2) , PRICE_RETAIL decimal(8,2) , PRICE_PLAN decimal(8,2) , AERO_CODE char(10) , SUPP_CODE char(10) , NOTE varchar(200) , PHOTOFILE char(100) , primary key (GOODS_CODE);create table C_SORT( SORT_CODE char(2) not null, SORT_NAME char(50) not null

42、, primary key (SORT_CODE);create table C_OPERATOR( OPER_CODE char(10) not null, OPER_NAME char(20) , PASSWORD char(10) , DEPT_CODE char(10) , POWER char(100) , primary key (OPER_CODE);create table C_SUPP( SUPP_CODE char(10) not null, SUPP_NAME char(100) not null, ZIP char(6) , ADDR char(100) , TEL char(20) , FAX char(20) , EMAIL char(100) , WEB char(100) , ACCOUNT char(30) , BANK char(100) , primary key (SUPP_CODE);create table C_DEPT( DEPT_CODE char(10) not null, DEPT_NAME char(50) , primary k