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

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

2、erOccupationnvarchar(20)null注冊(cè)用戶工作職業(yè)UserIncomenvarchar(20)null注冊(cè)用戶個(gè)人收入U(xiǎn)serEducationnvarchar(20)null注冊(cè)用戶最高學(xué)歷UserEmailnvarchar(100)null注冊(cè)用戶電子郵件UserTelphonenvarchar(20)null注冊(cè)用戶固定電話UserMobilenvarchar(11)null注冊(cè)用戶移動(dòng)電話UserProvincenvarchar(20)null注冊(cè)用戶所在省份UserCitynvarchar(50)null注冊(cè)用戶所在城市UserAddressnvarchar(1

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

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

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

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

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

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

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

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

11、dentity購(gòu)物車IDUserID integer null注冊(cè)用戶IDUserCookID nvarchar(20) null購(gòu)物車臨時(shí)CookIDUserIP nvarchar(20) null用戶IP地址GoodsIDintegernull商品IDGoodsName nvarchar(100) null商品名稱GoodsPriceShop money null商品市場(chǎng)價(jià)GoodsPriceUser money null商品會(huì)員價(jià)GoodsNumber integernull default 1商品購(gòu)買數(shù)量ShoppingCheck integernulldefault 0商品審核Sho

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

13、默認(rèn)值說明OrderID integeridentity記錄IDUserID integer null注冊(cè)用戶IDUserCookID nvarchar(20) null購(gòu)物車臨時(shí)CookIDOrderUserRealName nvarchar(50) null收貨人姓名OrderUserSex nvarchar(5)null收貨人性別OrderUserAddress nvarchar(250)null收貨人詳細(xì)地址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í)間商品庫(kù)存表字段名數(shù)據(jù)類型是否為空默認(rèn)值說明GoodsSaveID integer identity庫(kù)存IDGoodsID integer null商品IDGoodsName nvarchar(100) null商

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

16、null default getdate()配置創(chuàng)建時(shí)間*' 添加商品記錄同步添加庫(kù)存記錄的觸發(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'*' 刪除商品記錄同步刪除庫(kù)存記錄的觸發(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'*' 重新同步商品在庫(kù)存表中的對(duì)應(yīng)記錄的存儲(chǔ)過程'*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)計(jì)注冊(cè)人數(shù)的存儲(chǔ)過程'*CREATE procedure GetRegUsersid intASdeclare table table(ID int IDENTITY(1,1),RegDate nvarchar(10),RegNum int)declare tempId int -臨時(shí)表記錄IDdeclare tempRegDate nvarchar(10) -臨時(shí)表日期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)計(jì)用戶訂單的存儲(chǔ)過程'*CREATE procedure GetUserOrdersid intASdeclare table table(ID int IDENTITY(1,1),RegDate nvarchar(10),RegNum int)declare tempId int -臨時(shí)表記錄IDdeclare tempRegDate nvarchar(10) -臨時(shí)表日期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