网上商城项目数据库表

1、网上商城项目数据库表注册用户表字段名数据类型是否为空默认值说明UserID (主键)in tegeride ntity注册用户IDUserNamen varchar(50)null注册用户用户名UserPwdn varchar(50)null注册用户密码PwdQuesti onn varchar(IOO)null注册用户密码提问PwdA nswern varchar(IOO)null注册用户密码回答UserRealNamen varchar(50)null注册用户真实姓名UserSexn varchar(5)null注册用户性别UserAgen varchar(5)null注册用户年龄User

2、Occupati onn varchar(20)null注册用户工作职业Userl ncomen varchar(20)null注册用户个人收入UserEducati onn varchar(20)null注册用户最高学历UserEmailn varchar(IOO)null注册用户电子邮件UserTelph onen varchar(20)null注册用户固定电话UserMobilen varchar(11)null注册用户移动电话UserProvi neen varchar(20)null注册用户所在省份UserCityn varchar(50)null注册用户所在城市UserAddres

3、sn varchar(WO)null注册用户邮政编码UserVIPin tegernulldefault 0注册用户是否VIPUserCheckin tegernulldefault 1注册用户是否审核UserPoi ntin tegernulldefault 100注册用户商城积分UserVisitCou ntin tegernulldefault 1注册用户访问次数UserLastVisitTimedatetimenulldefault getdate()注册用户最后访问时间UserLastVisitIPn varchar(20)null注册用户最后访问IPUserCreateTimeda

4、tetimenulldefault getdate()注册用户注册时间用户访问日志表字段名数据类型是否为空默认值说明Log IDin tegeride ntity日志IDUserNamen varchar(50)null访问用户名VisitIPn varchar(20)null访问IPVisitTimedatetimenulldefault getdate()访问时间管理员表字段名数据类型是否为空默认值说明Ma nagerlDin tegeride ntity管理员IDMan agerNamen varchar(50)null管理员用户名Man agerPwdn varchar(50)null

5、管理员密码Man agerTypein tegernull管理员类型Man agerTextn varchar(250)null管理员描述GoodsBuyNumin tegernulldefault 0商品卖出数Goods In Timedatetimenulldefault getdate()商品录入时间商品评论表字段名数据类型是否为空默认值说明ReviewIDin tegeride ntity评论IDGoodsIDin tegernull商品IDUserNamen varchar(50)null评论用户名UserIPn varchar(20)null评论用户IPRevie wTexttex

6、tnull评论内容ReviewCheckin tegernulldefault 1评论是否审核ReviewTimedatetimenulldefault getdate()评论时间购物车记录表字段名数据类型是否为空默认值说明Shopp in gCartIDin tegeride ntity购物车IDUserIDin tegernull注册用户IDUserCookIDn varchar(20)null购物车临时CookIDUserIPn varchar(20)null用户IP地址GoodsIDin tegernull商品IDGoodsNamen varchar(IOO)null商品名称Goods

7、PriceShopmoneynull商品市场价GoodsPriceUsermoneynull商品会员价GoodsNumberin tegernulldefault 1商品购买数量Shopp in gCheckin tegernulldefault 0商品审核Shopp in gTimedatetimenulldefault getdate()购物时间收臧夹记录表字段名数据类型是否为空默认值说明CollectIDin tegeride ntity记录IDUserIDin tegernull注册用户IDGoodsIDin tegernull商品IDGoodsNamen varchar(IOO)nu

8、ll商品名GoodsPriceShopmoneynull商品市场价GoodsPriceUsermoneynull商品优惠价CollectTimedatetimenulldefault getdate()收藏记录时间购物订单记录表字段名数据类型是否为空默认值说明OrderlDin tegeride ntity记录IDUserIDin tegernull注册用户IDUserCookIDn varchar(20)null购物车临时CookIDOrderUserRealNamen varchar(50)null收货人姓名OrderUserSex nvarchar(5)null收货人性别OrderUse

9、rAddressn varchar(250)null收货人详细地址OrderUserPostcoden varchar(6)null收货人邮政编码OrderUserTelph onen varchar(20)null收货人联系电话OrderUserEmailn varchar(IOO)null收货人电子邮件OrderGoodsSe ndTypen varchar(50)null送货方式OrderM on eyPayTypen varchar(50)null付款方式OrderWeightin tegernulldefault 1订单状态OrderTimedatetimenulldefault g

10、etdate()下单时间商品库存表字段名数据类型是否为空默认值说明GoodsSavelDin tegeride ntity库存IDGoodsIDin tegernull商品IDGoodsNamen varchar(IOO)null商品名称SaveNumin tegernulldefault 0商品库存数量SaveAddNumin tegernulldefault 0商品增加数量SaveTimedatetimenulldefault getdate()商品入库时间SaveAddTimedatetimenulldefault getdate()商品增加时间商品库存配直表字段名数据类型是否为空默认值

11、说明GoodsSavel nfolDin tegeride ntity库存配置IDAllNumin tegernull商品满库数量AlertNumin tegernull库存预警数量AddTimedatetimenulldefault getdate()配置创建时间U VkVU VkVzT7 T添加商品记录同步添加库存记录的触发器K K J K1 K J kV1 K J kV1 K J kV1%1%zj1%1%zj1%1%IF EXISTS (SELECT name FROM sysobjects WHERE name = AddGoodsl nfo AND type = TR)DROP TR

12、IGGER AddGoodsl nfoGOCREATE TRIGGER AddGoodsl nfo ON dbo.Goods InfoFOR INSERTASINSERT INTO GoodsSave select GoodslD5GoodsName,O,O,getclate(),getdate() fromin sortedGO* ,删除商品记录同步删除库存记录的触发器IF EXISTS (SELECT name FROM sysobjects WHERE name = DelGoodsInfo AND type = TR) DROP TRIGGER DelGoodsInfoGOCREATE

13、 TRIGGER DelGoodsInfo ON dbo.GoodslnfoFOR 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 GoodsSa

14、ve (GoodsSavelD integer identity,GoodsID integer null,GoodsName nvarchar(IOO) 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(GoodsSavelD)ENDELSEBEGINcreate

15、 table GoodsSave (GoodsSavelD 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(GoodsSavelD)ENDI

16、NSERT INTO GoodsSave(GoodslD,GoodsName) select GoodsID,GoodsName from Goodslnfo order by GoodsIDGO*,按日期和月份统计注册人数的存储过程*CREATE procedure GetRegllsersid intASdeclare table table(ID int IDENTITY(1,1),RegDate nvarchar(10),RegNum int) declare templdint 临时表记录IDdeclare tempRegDate nvarchar(10)-临时表日期set noco

17、unt onif (id = 0)begininsert into table(RegDate)select distinct convert(char(10),UserCreateTime,120) from Users order byconvert(char(10),UserCreateTime,120) descdeclare _cursor cursor for select ld,RegDate from table open _cursorfetch next from _cursor into templd,tempRegDatewhile fetch_status = 0be

18、ginupdate table set RegNum=(select count(UserlD) from Users whereconvert(char(10),UserCreateTime,120) = tempRegDate) where ID = templdfetch next from cursor into templd,tempRegDate endclose _cursordeallocate _cursorendelsebegininsert into table(RegDate)select distinct convert(char(7),UserCreateTime,

19、120) from Users order byconvert(char(7),UserCreateTime,120) descdeclare _cursor cursor for select ld,RegDate from table open _cursorfetch next from _cursor into templd,tempRegDatewhile fetch_status = 0beginupdate table set RegNum=(select count(UserlD) from Users whereconvert(char(7),UserCreateTime,1

20、20) = tempRegDate) where ID = templdfetch next from _cursor into templd,tempRegDate endclose _cursor deallocate cursor end set nocount offselect * from table where Reg Date is not null GO* ,按日期和月份统计用户订单的存储过程*CREATE procedure GetUserOrdersid intASdeclare table table(ID int IDENTITY(1,1),RegDate nvarc

21、har(10),RegNum int)declare templd int -临时表记录 IDdeclare tempRegDate nvarchar(10)-临时表日期set nocount onif (id = 0)begininsert into table(RegDate)select distinct convert(char(10)JOrderTime,120) from GoodsOrder order byconvert(char(10)QrderTime,120) descdeclare _cursor cursor for select ld,RegDate from tableopen _cursorfetch next from _cursor into templd,tempRegDatewhile fetch_status = 0beginupdate table set RegNum=(select count(UserlD) from GoodsOr