毕业设计.sql

VC+SQL SERVER 2000环境下开发的商品销售管理系统

Create table LoginTable(     /*罗东名要做的*/
AccountName      nvarchar(20),  /*帐号名称*/   
Pass          nvarchar(20),    /*密码*/
PowerName     nvarchar(10),    /*权限名称*/
CanUse        int,             /*可用或者禁用*/
AccountNum    int,             /*帐号编号*/
);

select * from LoginTable

insert into LoginTable
values('admin','admin','超级用户',1,0)
insert into LoginTable
values('manager','manager','系统管理员',1,1)


drop table LoginTable


Create table PowTable(     /*罗东名要做的*/
AccountNum      int,           /*帐号编号*/ 
PowerName       nvarchar(20),  /*权限名称*/
RealName        nvarchar(20),  /*使用者的真实姓名*/
CanUse          int,           /*可用或者禁用*/
);
insert into PowTable
values(0,'超级用户','超级用户',1)
insert into PowTable
values(1,'系统管理员','系统管理员',1)

select * from PowTable

drop table PowTable

Create table ManagerModuleTable(
PowerName          nvarchar(20),  /*权限名称*/
ManagerModuleNum   nvarchar(10)   /*可使用的模块的编号*/ 
)

insert into ManagerModuleTable
values('超级用户', 'ABCDEFG')
insert into ManagerModuleTable
values('系统管理员','ABCDEFG')


select * from ManagerModuleTable

drop table ManagerModuleTable

Create table ModuleTable(
ModuleNum  char(1),            /*模块编号*/
ModuleName nvarchar(20)        /*模块名称*/
)

insert into ModuleTable
values('A','用户设置')

insert into ModuleTable
values('B','权限设置')

insert into ModuleTable
values('C','货品设置')

insert into ModuleTable
values('D','存货管理')

insert into ModuleTable
values('E','商品设置')

insert into ModuleTable
values('F','交易查询')

insert into ModuleTable
values('G','统计报表')

drop table ModuleTable

select * from ModuleTable

Create table PowerManagerTable(     /*罗东名要做的*/
PowerName  nvarchar(20),               /*权限名称*/
ManagerPowNum nvarchar(27),             /*可管理的权限编号*/
)

insert into  PowerManagerTable
values('超级用户','B')

insert into  PowerManagerTable
values('系统管理员','')


drop table PowerManagerTable

select * from PowerManagerTable

Create table PowerNameTable(
PowerNum    char(1),                /*权限编号*/
PowerName   nvarchar(20)             /*权限名称*/
)
insert into PowerNameTable
values('A','超级用户')

insert into PowerNameTable
values('B','系统管理员')


select * from PowerNameTable

drop table PowerNameTable;


Create table GoodsManagerTable(
GoodsName    nvarchar(10),   /*货品名称*/
Units        nvarchar(5),       /*单位*/
BuyPrice     nvarchar(10),         /*入货单价*/
SalePrice    nvarchar(10),         /*卖出价钱*/
WarningStore nvarchar(5),         /*警告存量*/
NowStore     int,         /*当前存量*/
OldStore     int,         /*进货前存量*/
ActionData  smalldatetime, /*操作日期*/
ActimeTime  nvarchar(10),  /*操作时间*/
OperatorName nvarchar(10),  /*操作人员*/
TotalNum     nvarchar(10),   /*登记数量*/
TotalPrice   nvarchar(10),   /*登记总价*/
Type         nvarchar(5),
Remark       nvarchar(200)
);


select * from GoodsManagerTable

drop table GoodsManagerTable

Create table GoodsTable(    /*罗东名要做的*/
GoodsName    nvarchar(10),   /*货品名称*/
Units        nvarchar(5),       /*单位*/
BuyPrice     nvarchar(10),         /*入货价钱，单价*/
SalePrice    nvarchar(10),         /*卖出价钱*/
WarningStore nvarchar(5),         /*警告存量*/
NowStore     int, /*现存量*/
);

select * from GoodsTable

drop table GoodsTable

Create table MerGroupbyGoodsTable(   /*罗东名要做的*/
GoodsName    nvarchar(10), /*货品名称*/
GoodsNum     nvarchar(3),  /*货品数目*/
SalePrice    nvarchar(5),
Units        nvarchar(5),
MerchandiseName   nvarchar(20)
)

drop table MerGroupbyGoodsTable;

select * from MerGroupbyGoodsTable

Create table MerchandiseOfGroupTable(  
GroupNumber  int,                /*组别号*/
GroupName    nvarchar(10),           /*组别名称*/
FlageToSale  int            /*可售*/
);


drop table MerchandiseOfGroupTable

select * from MerchandiseOfGroupTable

Create table MerchandiseSetTable(   /*罗东名要做的*/
GroupNumber       int,            /*组别号*/
MerchandiseName   nvarchar(20),   /*商品名称*/
MerID             nvarchar(14),
Units             nvarchar(8),        /*单位*/
SalePrice         float,          /*出售单价*/
LimitSaleNum      nvarchar(10),            /*限制售量*/
SaledNum          int,            /*当前已售*/
Impower           int,       /*授权 0代表需要授权，1代表不需授权*/  
FlageToSale       int        /*可售 1代表可售，0代表不可售*/
);


drop table MerchandiseSetTable

select * from MerChandiseSetTable

Create table ActionTable(   /*罗东名要做的*/
ActionName     nvarchar(10),         /*操作模块名称*/
OperatorName   nvarchar(10),         /*操作人名称*/
ActionDate     smalldatetime,             /*操作时间*/
ActionTime     nvarchar(20),
);



select * from ActionTable

drop table ActionTable

Create table ActionSaleTable(    /*罗东名要做的*/
ActionDate     smalldatetime,             /*操作日期*/
ActionTime     nvarchar(10),              /*操作时间*/
OperatorName   nvarchar(10),
listNum        nvarchar(20),              /*打印出来小票的单号*/
AllSaleNum     nvarchar(10),              /*一次销售总额*/
Refundment     nvarchar(10),              /*退货额,销售的情况下，退货额为0*/
FormerPrice    nvarchar(10),              /*原价*/
)

drop table ActionSaleTable

insert into ActionSaleTable
values('2006-5-12','0:14:00','angle','21422','10','0','10')



select * from ActionSaleTable




Create table SaleMerchandiseTable(
listNum        nvarchar(20),         /*小票号码*/
MerchandiseName   nvarchar(20),      /*商品名称*/
PowerName      nvarchar(20),         /*授权人姓名*/
Units          nvarchar(10),        /*商品单位*/
MerchandiseNum nvarchar(5),        /*买的商品数量*/
SalePrice        nvarchar(10),      /*这个商品的价钱*/
)
insert into SaleMerchandiseTable
values('21422','雅哈大礼包','','包','1','9')



drop table SaleMerchandiseTable