基于C/S的超市管理系统

use db_SuperMarket drop table t_UserInfo --创建一个用户信息表 Create table t_UserInfo ( iUserId int identity(1,1),--用户编号 sUserName nvarchar(50) NOT NULL,--用户姓名 sPsw nchar(10) NOT NULL,--用户密码 tiRole tinyint NOT NULL--用户角色 ) insert into t_UserInfo values(N'程艳','1111','0') insert into t_UserInfo values(N'小红','2222','1') insert into t_UserInfo values(N'小明','3333','0') insert into t_UserInfo values(N'小丽','4444','1') insert into t_UserInfo values(N'小闽','5555','1') insert into t_UserInfo values(N'小强','6666','1') select *from t_UserInfo --创建一个商品类别表 drop table t_GoodsType Create table t_GoodsType ( iGoodsTypeId int identity(1,1),--商品类别编号 sGoodsTypeName varchar(50) NOT NULL,--商品类别名称 iParentId int NOT NULL--父类别编号 ) insert into t_GoodsType values(N'未分类','0') insert into t_GoodsType values(N'日用品','0') insert into t_GoodsType values(N'烟酒','0') insert into t_GoodsType values(N'食品','0') insert into t_GoodsType values(N'抽纸','2') insert into t_GoodsType values(N'茅台','3') insert into t_GoodsType values(N'薯片','4') --update t_GoodsType set iParentId=0 where iGoodsTypeId<=3 --update t_GoodsType set iParentId=1 where iGoodsTypeId>=3 select *from t_GoodsType drop table t_GoodsInfo --创建一个商品信息表 Create table t_GoodsInfo ( iGoodsId int identity(1,1),--商品编号 sGoodsName varchar(50) NOT NULL,--商品名称 EAN nchar(13) NOT NULL,--EAN号 iGoodsTypeId int NOT NULL,--所属商品类别 flPrice float NOT NULL,--价格 sUnit nchar(10) NULL--单位 ) insert into t_GoodsInfo values(N'牙膏','1111222233331','1','11.6','100') insert into t_GoodsInfo values(N'啤酒','1111222233332','2','4.0','100') insert into t_GoodsInfo values(N'蛋糕','1111222233333','3','7.5','100') insert into t_GoodsInfo values(N'餐巾纸','1111222233334','4','5.5','100') select *from t_GoodsInfo drop table t_SellInfo --创建一个销售记录表 Create table t_SellInfo ( bSellId bigint identity(1,1),--销售流水号 iGoodsId int NOT NULL,--商品编号 flPrice float NOT NULL,--销售价格 flCount float NOT NULL,--销售数量 dtSell datetime NOT NULL,--销售时间 ) insert into t_SellInfo values('1','11.6','3','2012-02-7 09:12:00') insert into t_SellInfo values('4','5.5','2','2012-3-17 14:12') insert into t_SellInfo values('3','7.5','1','2012-5-7 14:22:05') insert into t_SellInfo values('1','11.6','2','2012-12-7 10:12:00') insert into t_SellInfo values('4','5.5','1','2012-12-7 14:12') insert into t_SellInfo values('3','7.5','2','2012-12-27 14:22:05') insert into t_SellInfo values('1','11.6','1','2012-12-27 19:12:00') select *from t_SellInfo select *from t_TempSell Create database db_SuperMarket use db_SuperMarket drop table t_UserInfo --创建一个用户信息表 Create table t_UserInfo ( iUserId int identity(1,1),--用户编号 sUserName nvarchar(50) NOT NULL,--用户姓名 sPsw nchar(10) NOT NULL,--用户密码 tiRole tinyint NOT NULL--用户角色 ) insert into t_UserInfo values(N'程艳','1111','0') insert into t_UserInfo values(N'小红','2222','1') insert into t_UserInfo values(N'小明','3333','0') insert into t_UserInfo values(N'小丽','4444','1') insert into t_UserInfo values(N'小闽','5555','1') insert into t_UserInfo values(N'小强','6666','1') select *from t_UserInfo --创建一个商品类别表 drop table t_GoodsType Create table t_GoodsType ( iGoodsTypeId int identity(1,1),--商品类别编号 sGoodsTypeName varchar(50) NOT NULL,--商品类别名称 iParentId int NOT NULL--父类别编号 ) insert into t_GoodsType values(N'未分类','0') insert into t_GoodsType values(N'日用品','0') insert into t_GoodsType values(N'烟酒','0') insert into t_GoodsType values(N'食品','0') insert into t_GoodsType values(N'抽纸','2') insert into t_GoodsType values(N'茅台','3') insert into t_GoodsType values(N'薯片','4') --update t_GoodsType set iParentId=0 where iGoodsTypeId<=3 --update t_GoodsType set iParentId=1 where iGoodsTypeId>=3 select *from t_GoodsType drop table t_GoodsInfo --创建一个商品信息表 Create table t_GoodsInfo ( iGoodsId int identity(1,1),--商品编号 sGoodsName varchar(50) NOT NULL,--商品名称 EAN nchar(13) NOT NULL,--EAN号 iGoodsTypeId int NOT NULL,--所属商品类别 flPrice float NOT NULL,--价格 sUnit nchar(10) NULL--单位 ) insert into t_GoodsInfo values(N'牙膏','1111222233331','1','11.6','100') insert into t_GoodsInfo values(N'啤酒','1111222233332','2','4.0','100') insert into t_GoodsInfo values(N'蛋糕','1111222233333','3','7.5','100') insert into t_GoodsInfo values(N'餐巾纸','1111222233334','4','5.5','100') select *from t_GoodsInfo drop table t_SellInfo --创建一个销售记录表 Create table t_SellInfo ( bSellId bigint identity(1,1),--销售流水号 iGoodsId int NOT NULL,--商品编号 flPrice float NOT NULL,--销售价格 flCount float NOT NULL,--销售数量 dtSell datetime NOT NULL,--销售时间 ) insert into t_SellInfo values('1','11.6','3','2012-02-7 09:12:00') insert into t_SellInfo values('4','5.5','2','2012-3-17 14:12') insert into t_SellInfo values('3','7.5','1','2012-5-7 14:22:05') insert into t_SellInfo values('1','11.6','2','2012-12-7 10:12:00') insert into t_SellInfo values('4','5.5','1','2012-12-7 14:12') insert into t_SellInfo values('3','7.5','2','2012-12-27 14:22:05') insert into t_SellInfo values('1','11.6','1','2012-12-27 19:12:00') select *from t_SellInfo --临时销售记录编号 drop table t_TempSell Create table t_TempSell ( iTempId bigint identity(1,1),--临时销售记录编号 sGoodsName varchar(110) NOT NULL,--商品名称 flCount int NOT NULL,--销售数量 sumPrice float NOT NULL,--销售额 ) insert into t_TempSell values('1gg','1','11.6') select *from t_TempSell