服装管理系统数据库代码.doc

/*员工信息表(employee)*/create table employee( ygid int primary key, /*员工编号，主键*/ ygname varchar(10) not null, /*员工姓名*/ ygtitle varchar(10) not null, /*职称*/ ygsex varchar(2) not null, /*员工性别*/ ygtelephone varchar(11) , /*员工电话*/ ygpassword varchar(32) not null, /*密码*/ ygstate varchar(10) not null /*状态*/)/*会员信息表(associator)*/ create table associator( hyid int primary key, /*会员编号，主键*/ hyname varchar(10) not null, /*会员姓名*/ hysex varchar(2) not null, /*会员性别*/ hytelephone varchar(11), /*会员电话*/ hytotal float not null /*消费总额*/)/*供应商信息表(supplier)*/create table supplier(gysid int primary key, /*编号供应商，主键*/gysname varchar(30) unique, /*供应商名称*/gystelephone varchar(11) not null, /*供应商电话*/gysaddress varchar(30) not null, /*供应商地址*/gyslinkman varchar(10) not null, /*联系人*/gysremark varchar(50) /*备注*/)/*服装基本信息表(clothing)*/create table clothing(fzid varchar(14) primary key, /*服装编号，主键*/fzname varchar(30) not null, /*服装名称*/fztype varchar(10) not null, /*服装类型*/fztype1 varchar(10) not null, /*服装类型1*/fztype2 varchar(10) not null, /*服装类型2*/fzseason varchar(10) not null, /*适合季节*/fzsize varchar(10) not null, /*尺码*/fzcolour varchar(10) not null, /*颜色*/fzprice float not null /*服装单价*/)/*服装类型基本信息表(type)*/*入库信息表(record)/入库临时信息表(record1)*/create table record(rkid int , /*入库编号*/fzid varchar(14), /*服装编号*/rknumber int not null, /*入库数量*/rkprice float not null, /*进货单价*/gysid int not null, /*供应商编号*/rkdate datetime not null, /*入库日期*/rkname varchar(10) not null /*操作人*/primary key (rkid,fzid) /*主键（入库编号、服装编号）*/)/*库存信息表（stock）/库存临时信息表(stock1)*/create table stock(fzid varchar(14) primary key, /*服装编号*/kcnumber int not null /*库存数量*/)/*销售信息表（sell）/销售临时信息表(sell1)*/create table sell(xsid int , /*销售编号*/fzid varchar(14) , /*服装编号*/xsnumber int not null, /*销售数量*/xsprice float not null, /*原价*/xsdiscount float not null, /*折扣*/xstotal float not null, /*销售总价*/xsdate datetime not null, /*销售日期*/xstab varchar(10), /*退货标记*/xsname varchar(10) not null, /*操作人*/primary key (xsid,fzid) /*主键（销售编号、服装编号）*/)/*客户表(customer)*/create table customer(xsid int primary key, /*销售编号，主键*/khmoney float not null, /*客户金额*/sfmoney float not null, /*实付金额*/thmoney float not null /*找回金额*/)/*退货信息表(back)*/create table back(xsid int, /*销售编号*/fzid varchar(14), /*服装编号*/thnumber int not null, /*退货数量*/thtotal float not null, /*退回金额*/thdate datetime not null, /*退货日期*/thname varchar(10) not null, /*操作人*/primary key (xsid,fzid) /*主键（销售编号、服装编号）*/)/*盘点信息表(inventory)/盘点临时信息表(inventory1)*/create table inventory(pdid int , /*盘点编号*/fzid varchar(14), /*服装编号*/kcnumber int not null, /*库存数量*/pdnumber int not null, /*实际数量*/pypknumber int not null, /*盘盈盘亏数量*/pddate datetime not null, /*盘点日期*/pdname varchar(10) not null, /*操作人*/primary key(pdid,fzid) /*主键(盘点编号、服装编号)*/)/*金额统计信息表(profit)*/create table profit(fzid varchar(14) primary key, /*服装编号，主键*/rknumber int not null, /*入库数量*/averageprice float not null, /*平均单价*/totalcost float not null, /*总成本*/xsnumber int not null, /*销售数量*/xscost float not null, /*销售成本*/xstotal float not null, /*销售总额*/xsprofit float not null /*销售总利润*/)/*金额统计信息表(profit1)*/create table profit1(fzid varchar(14) primary key, /*服装编号，主键*/rknumber int not null, /*入库数量*/averageprice float not null, /*平均单价*/totalcost float not null, /*总成本*/)/*金额统计信息表(profit2)*/create table profit2(fzid varchar(14) primary key, /*服装编号，主键*/xsnumber int not null, /*销售数量*/xstotal float not null, /*销售总额*/)/*服装销售统计信息表(statistic)/服装销售统计临时信息表(statistic1)/服装销售统计临时信息表(statistic2)*/create table statistic(fzid varchar(14) primary key, /*服装编号或员工名称，主键*/january int , /*一月*/february int , /*二月*/march int , /*三月*/apri