网上商城项目数据库表

网上商城项目数据库表注册用户表字段名数据类型是否为空默认值说明UserID （主键）integeridentity注册用户IDUserNamenvarchar(50)null注册用户用户名UserPwdnvarchar(50)null注册用户密码PwdQuestionnvarchar(100)null注册用户密码提问PwdAnswernvarchar(100)null注册用户密码回答UserRealNamenvarchar(50)null注册用户真实姓名UserSexnvarchar(5)null注册用户性别UserAgenvarchar(5)null注册用户年龄UserOccupationnvarchar(20)null注册用户工作职业UserIncomenvarchar(20)null注册用户个人收入UserEducationnvarchar(20)null注册用户最高学历UserEmailnvarchar(100)null注册用户电子邮件UserTelphonenvarchar(20)null注册用户固定电话UserMobilenvarchar(11)null注册用户移动电话UserProvincenvarchar(20)null注册用户所在省份UserCitynvarchar(50)null注册用户所在城市UserAddressnvarchar(100)null注册用户邮政编码UserVIPintegernulldefault 0注册用户是否VIPUserCheckintegernulldefault 1注册用户是否审核UserPointintegernulldefault 100注册用户商城积分UserVisitCountintegernulldefault 1注册用户访问次数UserLastVisitTimedatetimenulldefault getdate()注册用户最后访问时间UserLastVisitIPnvarchar(20)null注册用户最后访问IPUserCreateTimedatetimenulldefault getdate()注册用户注册时间用户访问日志表字段名数据类型是否为空默认值说明LogIDintegeridentity日志IDUserNamenvarchar(50)null访问用户名VisitIPnvarchar(20)null访问IPVisitTimedatetimenulldefault getdate()访问时间管理员表字段名数据类型是否为空默认值说明ManagerIDintegeridentity管理员IDManagerNamenvarchar(50)null管理员用户名ManagerPwdnvarchar(50)null管理员密码ManagerTypeintegernull管理员类型ManagerTextnvarchar(250)null管理员描述ManagerCheckintegernulldefault 1管理员是否审核ManagerVisitCountintegernulldefault 1管理员访问次数ManagerLastVisitTimedatetimenulldefault getdate()管理员最后访问时间ManagerLastVisitIPnvarchar(20)null管理员最后访问IPManagerCreateTimedatetimenull default getdate()管理员录入时间商品二级分类表字段名数据类型是否为空默认值说明BorderID integeridentity二级分类IDTypeIDintegernull一级分类IDBorderName nvarchar(50)null二级分类名称BorderText nvarchar(250) null二级分类描述BorderOrder integernull default 0二级分类显示级别BorderCheck integernull default 0二级分类显示校验BorderTime datetime null default getdate()二级分类录入时间商品信息表字段名数据类型是否为空默认值说明GoodsIDinteger identity商品IDTypeID integer null商品一级分类IDBorderID integer null商品二级分类IDGoodsCode nvarchar(100) null商品编号GoodsTypeCode nvarchar(100) null商品型号GoodsName nvarchar(100) null商品名称GoodsKeys nvarchar(100) null商品关键字GoodsText nvarchar(250) null商品简介GoodsContent text null商品描述GoodsPicSmall nvarchar(150) null商品小图片GoodsPicLarge nvarchar(150)null商品大图片GoodsPT nvarchar(50)null商品品牌GoodsPriceShop money null商品市场价GoodsPriceUser money null商品优惠价GoodsPriceVIP moneynull商品VIP价GoodsPricePer integer null商品折扣数GoodsOnTime datetime null default getdate()商品上市时间GoodsIsNew integer null default 1商品是否新品GoodsIsVouch integer null default 1商品是否推荐GoodsOrder integernull default 0商品显示级别GoodsCheck integernulldefault 0商品是否审核GoodsCheckAdmin nvarchar(50) null商品审核员GoodsHits integer null default 0商品点击数GoodsReviews integer null default 0商品评论数GoodsOrderNum integer null default 0商品下单数GoodsBuyNum integernull default 0商品卖出数GoodsInTime datetime null default getdate()商品录入时间商品评论表字段名数据类型是否为空默认值说明ReviewID integer identity评论IDGoodsIDintegernull商品IDUserName nvarchar(50) null评论用户名UserIP nvarchar(20) null评论用户IPReviewText text null评论内容ReviewCheck integer null default 1评论是否审核ReviewTime datetimenull default getdate()评论时间购物车记录表字段名数据类型是否为空默认值说明ShoppingCartID integer identity购物车IDUserID integer null注册用户IDUserCookID nvarchar(20) null购物车临时CookIDUserIP nvarchar(20) null用户IP地址GoodsIDintegernull商品IDGoodsName nvarchar(100) null商品名称GoodsPriceShop money null商品市场价GoodsPriceUser money null商品会员价GoodsNumber integernull default 1商品购买数量ShoppingCheck integernulldefault 0商品审核ShoppingTime datetimenull default getdate()购物时间收藏夹记录表字段名数据类型是否为空默认值说明CollectID integer identity记录IDUserID integernull注册用户IDGoodsID integernull商品IDGoodsName nvarchar(100) null商品名GoodsPriceShop money null商品市场价GoodsPriceUser moneynull商品优惠价CollectTime datetime null default getdate()收藏记录时间购物订单记录表字段名数据类型是否为空默认值说明OrderID integeridentity记录IDUserID integer null注册用户IDUserCookID nvarchar(20) null购物车临时CookIDOrderUserRealName nvarchar(50) null收货人姓名OrderUserSex nvarchar(5)null收货人性别OrderUserAddress nvarchar(250)null收货人详细地址OrderUserPostcode nvarchar(6)null收货人邮政编码OrderUserTelphone nvarchar(20) null收货人联系电话OrderUserEmail nvarchar(100) null收货人电子邮件OrderGoodsSendType nvarchar(50) null送货方式OrderMoneyPayType nvarchar(50) null付款方式OrderWeight integer null default 1订单状态OrderTime datetime null default getdate()下单时间商品库存表字段名数据类型是否为空默认值说明GoodsSaveID integer identity库存IDGoodsID integer null商品IDGoodsName nvarchar(100) null商品名称SaveNum integernull default 0商品库存数量SaveAddNum integer null default 0商品增加数量SaveTime datetime null default getdate()商品入库时间SaveAddTime datetimenull default getdate()商品增加时间商品库存配置表字段名数据类型是否为空默认值说明GoodsSaveInfoID integer identity库存配置IDAllNuminteger null商品满库数量AlertNum integer null库存预警数量AddTime datetime null default getdate()配置创建时间* 添加商品记录同步添加库存记录的触发器*IF EXISTS (SELECT name FROM sysobjects WHERE name = AddGoodsInfo AND type = TR)DROP TRIGGER AddGoodsInfoGOCREATE TRIGGER AddGoodsInfo ON dbo.GoodsInfo FOR INSERTASINSERT INTO GoodsSave select GoodsID,GoodsName,0,0,getdate(),getdate() from insertedGO* 删除商品记录同步删除库存记录的触发器*IF EXISTS (SELECT name FROM sysobjects WHERE name = DelGoodsInfo AND type = TR)DROP TRIGGER DelGoodsInfoGOCREATE TRIGGER DelGoodsInfo ON dbo.GoodsInfo FOR DELETEASDELETE from GoodsSave where GoodsID = (SELECT GoodsID FROM deleted)GO* 重新同步商品在库存表中的对应记录的存储过程*CREATE PROCEDURE BindGoodsToSave ASIF EXISTS (SELECT name FROM sysobjects WHERE name = GoodsSave AND type = U)BEGINDROP TABLE GoodsSavecreate table GoodsSave (GoodsSaveID integer identity,GoodsID integer null,GoodsName nvarchar(100) null,SaveNum integer null default 0,SaveAddNum integer null default 0,SaveTime datetime null default getdate(),SaveAddTime datetime null default getdate(),constraint PK_GOODSSAVE primary key (GoodsSaveID) ENDELSEBEGINcreate table GoodsSave (GoodsSaveID integer identity,GoodsID integer null,GoodsName nvarchar(100) null,SaveNum integer null default 0,SaveAddNum integer null default 0,SaveTime datetime null default getdate(),SaveAddTime datetime null default getdate(),constraint PK_GOODSSAVE primary key (GoodsSaveID) ENDINSERT INTO GoodsSave(GoodsID,GoodsName) select GoodsID,GoodsName from GoodsInfo order by GoodsIDGO* 按日期和月份统计注册人数的存储过程*CREATE procedure GetRegUsersid intASdeclare table table(ID int IDENTITY(1,1),RegDate nvarchar(10),RegNum int)declare tempId int -临时表记录IDdeclare tempRegDate nvarchar(10) -临时表日期set nocount onif (id = 0)begin insert into table(RegDate)select distinct convert(char(10),UserCreateTime,120) from Users order by convert(char(10),UserCreateTime,120) desc declare _cursor cursor for select Id,RegDate from tableopen _cursorfetch next from _cursor into tempId,tempRegDatewhile fetch_status = 0beginupdate table set RegNum=(select count(UserID) from Users where convert(char(10),UserCreateTime,120) = tempRegDate) where ID = tempIdfetch next from _cursor into tempId,tempRegDateendclose _cursordeallocate _cursorend elsebegininsert into table(RegDate)select distinct convert(char(7),UserCreateTime,120) from Users order by convert(char(7),UserCreateTime,120) descdeclare _cursor cursor for select Id,RegDate from tableopen _cursorfetch next from _cursor into tempId,tempRegDatewhile fetch_status = 0beginupdate table set RegNum=(select count(UserID) from Users where convert(char(7),UserCreateTime,120) = tempRegDate) where ID = tempIdfetch next from _cursor into tempId,tempRegDateendclose _cursordeallocate _cursorend set nocount offselect * from table where RegDate is not nullGO* 按日期和月份统计用户订单的存储过程*CREATE procedure GetUserOrdersid intASdeclare table table(ID int IDENTITY(1,1),RegDate nvarchar(10),RegNum int)declare tempId int -临时表记录IDdeclare tempRegDate nvarchar(10) -临时表日期set nocount onif (id = 0)begin insert into table(RegDate)select distinct convert(char(10),OrderTime,120) from GoodsOrder order by convert(char(10),OrderTime,120) descdeclare _cursor cursor for select Id,RegDate from tableopen _cursorfet