網(wǎng)上商城項目數(shù)據(jù)庫表

1、精選優(yōu)質(zhì)文檔-傾情為你奉上網(wǎng)上商城項目數(shù)據(jù)庫表注冊用戶表字段名數(shù)據(jù)類型是否為空默認值說明UserID （主鍵）integeridentity注冊用戶IDUserNamenvarchar(50)null注冊用戶用戶名UserPwdnvarchar(50)null注冊用戶密碼PwdQuestionnvarchar(100)null注冊用戶密碼提問PwdAnswernvarchar(100)null注冊用戶密碼回答UserRealNamenvarchar(50)null注冊用戶真實姓名UserSexnvarchar(5)null注冊用戶性別UserAgenvarchar(5)null注冊用戶年齡Us

2、erOccupationnvarchar(20)null注冊用戶工作職業(yè)UserIncomenvarchar(20)null注冊用戶個人收入UserEducationnvarchar(20)null注冊用戶最高學歷UserEmailnvarchar(100)null注冊用戶電子郵件UserTelphonenvarchar(20)null注冊用戶固定電話UserMobilenvarchar(11)null注冊用戶移動電話UserProvincenvarchar(20)null注冊用戶所在省份UserCitynvarchar(50)null注冊用戶所在城市UserAddressnvarchar(1

3、00)null注冊用戶郵政編碼UserVIPintegernulldefault 0注冊用戶是否VIPUserCheckintegernulldefault 1注冊用戶是否審核UserPointintegernulldefault 100注冊用戶商城積分UserVisitCountintegernulldefault 1注冊用戶訪問次數(shù)UserLastVisitTimedatetimenulldefault getdate()注冊用戶最后訪問時間UserLastVisitIPnvarchar(20)null注冊用戶最后訪問IPUserCreateTimedatetimenulldefault

4、getdate()注冊用戶注冊時間用戶訪問日志表字段名數(shù)據(jù)類型是否為空默認值說明LogIDintegeridentity日志IDUserNamenvarchar(50)null訪問用戶名VisitIPnvarchar(20)null訪問IPVisitTimedatetimenulldefault getdate()訪問時間管理員表字段名數(shù)據(jù)類型是否為空默認值說明ManagerIDintegeridentity管理員IDManagerNamenvarchar(50)null管理員用戶名ManagerPwdnvarchar(50)null管理員密碼ManagerTypeintegernull管理員

5、類型ManagerTextnvarchar(250)null管理員描述ManagerCheckintegernulldefault 1管理員是否審核ManagerVisitCountintegernulldefault 1管理員訪問次數(shù)ManagerLastVisitTimedatetimenulldefault getdate()管理員最后訪問時間ManagerLastVisitIPnvarchar(20)null管理員最后訪問IPManagerCreateTimedatetimenull default getdate()管理員錄入時間商品二級分類表字段名數(shù)據(jù)類型是否為空默認值說明Bord

6、erID integeridentity二級分類IDTypeIDintegernull一級分類IDBorderName nvarchar(50)null二級分類名稱BorderText nvarchar(250) null二級分類描述BorderOrder integernull default 0二級分類顯示級別BorderCheck integernull default 0二級分類顯示校驗BorderTime datetime null default getdate()二級分類錄入時間商品信息表字段名數(shù)據(jù)類型是否為空默認值說明GoodsIDinteger identity商品IDTyp

7、eID integer null商品一級分類IDBorderID integer null商品二級分類IDGoodsCode nvarchar(100) null商品編號GoodsTypeCode nvarchar(100) null商品型號GoodsName nvarchar(100) null商品名稱GoodsKeys nvarchar(100) null商品關(guān)鍵字GoodsText nvarchar(250) null商品簡介GoodsContent text null商品描述GoodsPicSmall nvarchar(150) null商品小圖片GoodsPicLarge nvarc

8、har(150)null商品大圖片GoodsPT nvarchar(50)null商品品牌GoodsPriceShop money null商品市場價GoodsPriceUser money null商品優(yōu)惠價GoodsPriceVIP moneynull商品VIP價GoodsPricePer integer null商品折扣數(shù)GoodsOnTime datetime null default getdate()商品上市時間GoodsIsNew integer null default 1商品是否新品GoodsIsVouch integer null default 1商品是否推薦GoodsO

9、rder integernull default 0商品顯示級別GoodsCheck integernulldefault 0商品是否審核GoodsCheckAdmin nvarchar(50) null商品審核員GoodsHits integer null default 0商品點擊數(shù)GoodsReviews integer null default 0商品評論數(shù)GoodsOrderNum integer null default 0商品下單數(shù)GoodsBuyNum integernull default 0商品賣出數(shù)GoodsInTime datetime null default get

10、date()商品錄入時間商品評論表字段名數(shù)據(jù)類型是否為空默認值說明ReviewID integer identity評論IDGoodsIDintegernull商品IDUserName nvarchar(50) null評論用戶名UserIP nvarchar(20) null評論用戶IPReviewText text null評論內(nèi)容ReviewCheck integer null default 1評論是否審核ReviewTime datetimenull default getdate()評論時間購物車記錄表字段名數(shù)據(jù)類型是否為空默認值說明ShoppingCartID integer i

11、dentity購物車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商品購買數(shù)量ShoppingCheck integernulldefault 0商品審核Sho

12、ppingTime datetimenull default getdate()購物時間收藏夾記錄表字段名數(shù)據(jù)類型是否為空默認值說明CollectID integer identity記錄IDUserID integernull注冊用戶IDGoodsID integernull商品IDGoodsName nvarchar(100) null商品名GoodsPriceShop money null商品市場價GoodsPriceUser moneynull商品優(yōu)惠價CollectTime datetime null default getdate()收藏記錄時間購物訂單記錄表字段名數(shù)據(jù)類型是否為空

13、默認值說明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收貨人聯(lián)系電話OrderUse

14、rEmail nvarchar(100) null收貨人電子郵件OrderGoodsSendType nvarchar(50) null送貨方式OrderMoneyPayType nvarchar(50) null付款方式OrderWeight integer null default 1訂單狀態(tài)OrderTime datetime null default getdate()下單時間商品庫存表字段名數(shù)據(jù)類型是否為空默認值說明GoodsSaveID integer identity庫存IDGoodsID integer null商品IDGoodsName nvarchar(100) null商

15、品名稱SaveNum integernull default 0商品庫存數(shù)量SaveAddNum integer null default 0商品增加數(shù)量SaveTime datetime null default getdate()商品入庫時間SaveAddTime datetimenull default getdate()商品增加時間商品庫存配置表字段名數(shù)據(jù)類型是否為空默認值說明GoodsSaveInfoID integer identity庫存配置IDAllNuminteger null商品滿庫數(shù)量AlertNum integer null庫存預警數(shù)量AddTime datetime

16、null default getdate()配置創(chuàng)建時間*' 添加商品記錄同步添加庫存記錄的觸發(fā)器'*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

17、(),getdate() from insertedGO'*' 刪除商品記錄同步刪除庫存記錄的觸發(fā)器'*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

18、 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 nvarch

19、ar(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,G

20、oodsName 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 fro

21、m GoodsInfo order by GoodsIDGO'*' 按日期和月份統(tǒng)計注冊人數(shù)的存儲過程'*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)sel

22、ect 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) f

23、rom 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),UserCre

24、ateTime,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

25、from _cursor into tempId,tempRegDateendclose _cursordeallocate _cursorend set nocount offselect * from table where RegDate is not nullGO'*' 按日期和月份統(tǒng)計用戶訂單的存儲過程'*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 _cu