服裝管理系統(tǒng)數(shù)據(jù)庫代碼知識交流

1、/*員工信息表(employee)*/createtableemployee( TOC o 1-5 h z ygidintprimarykey,/*員工編號，主鍵*/ygnamevarchar(10)notnull,/*員工姓名*/ygtitlevarchar(10)notnull,/*職稱*/ygsexvarchar(2)notnull,/*員工性別*/ygtelephonevarchar(11),/*員工電話*/ygpasswordvarchar(32)notnull,/*密碼*/ygstatevarchar(10)notnull/*狀態(tài)*/)/*會員信息表(associator)*/cr

2、eatetableassociator( TOC o 1-5 h z hyidintprimarykey,/*會員編號，主鍵*/hynamevarchar(10)notnull,/*會員姓名*/hysexvarchar(2)notnull,/*會員性別*/hytelephonevarchar(11),/*會員電話*/hytotalfloatnotnull/*消費(fèi)總額*/)/*供應(yīng)商信息表(supplier)*/createtablesupplier(gysidintprimarykey,/*編號供應(yīng)商，主鍵*/* 供應(yīng)商電話*/* 供應(yīng)商地址*/* 聯(lián)系人 */gysnamevarchar(3

3、0)unique,/*供應(yīng)商名稱*/gystelephonevarchar(11)notnull,gysaddressvarchar(30)notnull,gyslinkmanvarchar(10)notnull,gysremarkvarchar(50)/*備注*/*服裝基本信息表(clothing)*/* 服裝編號，主鍵*/* 服裝名稱*/* 服裝類型*/* 服裝類型1*/* 服裝類型2*/*適合季節(jié)*/* 尺碼 */createtableclothing(fzidvarchar(14)primarykey,fznamevarchar(30)notnull,fztypevarchar(10)

4、notnull,fztype1varchar(10)notnull,fztype2varchar(10)notnull,fzseasonvarchar(10)notnull,fzsizevarchar(10)notnull,fzcolourvarchar(10)notnull,/*顏色*/fzpricefloatnotnull/*服裝單價(jià)*/)/*服裝類型基本信息表(type)*/*入庫信息表(record)/入庫臨時信息表(record1)*/createtablerecord( TOC o 1-5 h z rkidint,/*入庫編號*/fzidvarchar(14),/*服裝編號*/rk

5、numberintnotnull,/*入庫數(shù)量*/rkpricefloatnotnull,/*進(jìn)貨單價(jià)*/gysidintnotnull,/*供應(yīng)商編號*/rkdatedatetimenotnull,/*入庫日期*/rknamevarchar(10)notnull/*操作人*/primarykey(rkid,fzid)/*主鍵(入庫編號、服裝編號)*/)/*庫存信息表(stock)/庫存臨時信息表(stock1)*/createtablestock(fzidvarchar(14)primarykey,/*服裝編號*/kcnumberintnotnull/*庫存數(shù)量*/)/*銷售信息表(sell

6、)/銷售臨時信息表(sell1)*/createtablesell(xsidint,/*銷售編號*/fzidvarchar(14),/*服裝編號*/xsnumberintnotnull,/*銷售數(shù)量*/xspricefloatnotnull,/*原價(jià)*/xsdiscountfloatnotnull,/*折扣*/xstotalfloatnotnull,/*銷售總價(jià)*/xsdatedatetimenotnull,/*銷售日期*/xstabvarchar(10),/*退貨標(biāo)記*/xsnamevarchar(10)notnull,/*操作人*/primarykey(xsid,fzid)/*主鍵(銷售編

7、號、服裝編號)*/)/* 客戶表 (customer)*/ create table customer( xsid int primary key, khmoney float not null, sfmoney float not null, thmoney float not null )/* 銷售編號，主鍵*/* 客戶金額*/* 實(shí)付金額*/* 找回金額*/*退貨信息表(back)*/createtableback(xsidint,/*銷售編號*/fzidvarchar(14),/*服裝編號*/ TOC o 1-5 h z thnumberintnotnull,/*退貨數(shù)量*/thtot

8、alfloatnotnull,/*退回金額*/thdatedatetimenotnull,/*退貨日期*/thnamevarchar(10)notnull,/*操作人*/primarykey(xsid,fzid)/*主鍵(銷售編號、服裝編號)*/)/*盤點(diǎn)信息表(inventory)/盤點(diǎn)臨時信息表(inventory1)*/createtableinventory(pdidint,/*盤點(diǎn)編號*/fzidvarchar(14),/*服裝編號*/kcnumberintnotnull,pdnumberintnotnull,pypknumberintnotnull,pddatedatetimeno

9、tnull, TOC o 1-5 h z /*庫存數(shù)量*/*實(shí)際數(shù)量*/*盤盈盤虧數(shù)量*/*盤點(diǎn)日期*/pdnamevarchar(10)notnull,/*操作人*/ TOC o 1-5 h z primarykey(pdid,fzid)/*主鍵(盤點(diǎn)編號、服裝編號)*/*金額統(tǒng)計(jì)信息表(profit)*/createtableprofit(fzidvarchar(14)primarykey,/*服裝編號，主鍵*/rknumberintnotnull,/*入庫數(shù)量*/averagepricefloatnotnull,/*平均單價(jià)*/totalcostfloatnotnull,/*總成本*/x

10、snumberintnotnull,/*銷售數(shù)量*/xscostfloatnotnull,/*銷售成本*/xstotalfloatnotnull,/*銷售總額*/xsprofitfloatnotnull/*銷售總利潤*/ TOC o 1-5 h z )/*金額統(tǒng)計(jì)信息表(profit1)*/createtableprofit1(fzidvarchar(14)primarykey,/*服裝編號，主鍵*/rknumberintnotnull,/*入庫數(shù)量*/averagepricefloatnotnull,/*平均單價(jià)*/totalcostfloatnotnull,/*總成本*/)/*金額統(tǒng)計(jì)信息

11、表(profit2)*/createtableprofit2( TOC o 1-5 h z fzidvarchar(14)primarykey,/*服裝編號，主鍵*/xsnumberintnotnull,/*銷售數(shù)量*/xstotalfloatnotnull,/*銷售總額*/)/*服裝銷售統(tǒng)計(jì)信息表(statistic)/服裝銷售統(tǒng)計(jì)臨時信息表(statistic1)/服裝銷售統(tǒng)計(jì)臨時信息表(statistic2)*/createtablestatistic( TOC o 1-5 h z fzidvarchar(14)primarykey,/*服裝編號或員工名稱，主鍵*/januaryint,/*一月*/februaryint,/*二月*/m