我的租房網(wǎng)設計與實現(xiàn)代碼

1、數(shù)據(jù)庫技術與開發(fā)項目實訓設計報告項目名稱：我的租房網(wǎng)姓 名：專 業(yè)：指導教師： 完成日期：內(nèi)蒙古科技大學信息工程學院計算機系數(shù)據(jù)庫技術與應用實驗報告姓名學號實驗成績班級實驗日期項目號、實驗名稱實訓項目我的租房網(wǎng)實驗要求1、完成實訓項目我的租房網(wǎng)并完成實訓一到實訓4中的上機實踐內(nèi)容2、按照項目實訓報告相關要求，提交一份電子版項目實訓報告實驗內(nèi)容1、實訓一：建立數(shù)據(jù)庫結構(1) 創(chuàng)建數(shù)據(jù)庫House使用SSMS向?qū)?chuàng)建數(shù)據(jù)庫House(2) 建立5張數(shù)據(jù)表-創(chuàng)建客戶信息表sys_usercreate table sys_user(-客戶編號，主鍵標識列UserId int identity(1,1

2、) primary key,-客戶姓名，非空UserName varchar(50) not null,-客戶密碼，至少6個字符UserPwd varchar(50) constraint ck_UserPwd check(len(UserPwd)>=6)-創(chuàng)建區(qū)縣信息表hos_districtuse Housegocreate table hos_district(-區(qū)縣編號，主鍵，標識列從1開始，遞增值為1DId int identity(1,1) primary key,-區(qū)縣名稱，非空DName varchar(50) not null)-創(chuàng)建街道信息表hos_streetuse

3、 Housegocreate table hos_street(-街道編號，主鍵，標識列從1開始，遞增值為1StreetId int identity(1,1) primary key,-街道名稱，非空SName varchar(50) not null,-區(qū)縣編號，表hos_district的外鍵SDId int constraint fk_SDId foreign key(SDId) references hos_district(DId)-創(chuàng)建房屋信息表hos_typeuse Housegocreate table hos_type(-房屋類型編號，主鍵，標識列從1開始，遞增值為1HTI

4、d int identity(1,1) primary key,-房屋類型名稱，非空HTNamevarchar(50) not null)-創(chuàng)建出租房屋信息表hos_houseuse Housegocreate table hos_house(-出租房屋編號，主鍵，標識列從1開始，遞增值為1HMID int identity(1,1) primary key,-客戶編號，非空，外鍵UserId int not null constraint fk_UserId foreign key(UserId) references sys_user(UserId), -街道編號，非空，外鍵StreetI

5、D int not null constraint fk_StreetID foreign key(StreetID) references hos_street(StreetID), -房屋類型編號，非空，外鍵HTId int not null constraint fk_HTId foreign key(HTId) references hos_type(HTId),-月租金，非空，默認值為0，要求大于等于0Price decimal(6,2) not null default(0) constraint ck_Price check(Price>=0) ,-標題，非空Topic v

6、archar(50) not null,-描述，非空Contents varchar(100) not null,-發(fā)布時間，非空，默認值為當前日期，要求不大于當前日期HTime datetime not null default(getdate() constraint ck_HTime check(HTime<=getdate(),-備注Copy varchar(80)(3) 添加外鍵約束-給客戶信息表中的UserName創(chuàng)建非聚集索引create unique nonclustered index Idx_userNameon sys_user(UserName)withfillf

7、actor=10;-給區(qū)縣信息表中的DName創(chuàng)建非聚集索引create unique nonclustered index Idx_dNameon hos_district(DName)withfillfactor=10;-給街道信息表中的SName創(chuàng)建非聚集索引create unique nonclustered index Idx_sNameon hos_street(SName)withfillfactor=10;-給房屋信息表中的HTName創(chuàng)建非聚集索引create unique nonclustered index Idx_htNameon hos_type(HTName)wit

8、hfillfactor=10;分析過程：給客戶信息表、區(qū)縣信息表、街道信息表、房屋信息表中添加非聚集索引來提高查詢的速度，對經(jīng)常使用的UserName、DName、SName、HTName進行查詢優(yōu)化2、實訓二：添加測試數(shù)據(jù)(1) 主表添加測試數(shù)據(jù)-向客戶信息表sys_user添加多條條測試數(shù)據(jù)insert into sys_uservalues('王雪麗','100000'), ('嚴德賽','100001'), ('王生高','100002'), ('崔曉宇','1000

9、03'), ('盧一帆','100004'), ('張英武','100005'), ('安鵬','100006'), ('胖哥','100007'), ('程峰','100008'), ('馬云','100009'), ('王錚','100010'), ('劉強東','100011'), ('雷舒然','100

10、012'), ('成龍','100013'), ('武則天','100014'), ('焦旭鵬','100015'), ('鄭利澤','100016'), ('羅陽光','100017'), ('邱國龍','100018'), ('李小龍','100019')-向區(qū)縣信息表中添加多條記錄insert into hos_districtvalues('洪山區(qū)&

11、#39;), ('武昌區(qū)'), ('青山區(qū)'), ('江漢區(qū)'), ('硚口區(qū)')-向街道信息表中添加多條記錄 insert into hos_streetvalues('街道口','1'), ('卓刀泉','1'), ('廣埠屯','1'), ('石牌嶺','1'), ('積玉橋','2'), ('楊家園','2'), ('水果湖&

12、#39;,'2'), ('黃鶴樓','2'), ('紅衛(wèi)路','3'), ('新溝橋','3'), ('冶金街','3'), ('廠前街道','3'), ('吳家山','4'), ('北湖街','4'), ('滿春街','4'), ('新華街','4'), ('六角亭','

13、;5'), ('漢正街','5'), ('漢中街','5'), ('長風街','5')-向房屋信息表中添加多條記錄insert into hos_typevalues('兩室一廳'), ('兩室兩廳'), ('一室一廳'), ('三室兩廳'), ('四室兩廳'), ('五室兩廳')-建立三張臨時表create table #topic(Topic varchar(50) not null,)cre

14、ate table #contents(Contents varchar(50) not null,)create table #copy(Copy varchar(50) not null,)-向三張臨時表中插入數(shù)據(jù)insert into #topicvalues('東方花園')insert into #topicvalues('金茂東方公寓')insert into #topicvalues('世貿(mào)大酒店')insert into #topicvalues('民航小區(qū)')insert into #contentsvalues(

15、'全新家具電器')insert into #contentsvalues('簡單裝修押一付三')insert into #contentsvalues('精裝修,首出租')insert into #contentsvalues('豪華裝修,拎包入住')insert into #copyvalues('環(huán)境優(yōu)雅,學區(qū)房')insert into #copyvalues('購物方便')insert into #copyvalues('豪華小區(qū)，環(huán)境優(yōu)美')insert into #cop

16、yvalues('交通便利,配套完善')執(zhí)行結果：如圖1、圖2、圖3、圖4、圖5圖1客戶信息表圖2區(qū)縣信息表圖3街道信息表圖4房屋信息表 圖5三張臨時表(2) 添加批量數(shù)據(jù)declare begin datetime,end datetimeset begin =getdate()-定義局部變量declare topic varchar(50)declare contents varchar(50)declare copy varchar(50)declare userid intdeclare streetid intdeclare htid intdeclare price

17、 decimal(6,2)declare htime datetime-向hos_house表中插入10000條數(shù)據(jù)-使用事物begin transactiondeclare i intset i=0while i<100begin -對局部變量進行賦值set topic=(select top 1* from #topic order by newid()set contents=(select top 1* from #contents order by newid()set copy=(select top 1* from #copy order by newid()select

18、top 1 userid=userid from sys_user order by NEWID()-租金在-4000之間隨機產(chǎn)生set price=1000+cast(3000*RAND() as int)-發(fā)布時間htime，要求小于當前系統(tǒng)時間，發(fā)布時間在當前系統(tǒng)時間一年內(nèi)set htime=cast(dateadd(day,-cast(rand()*datepart(dayofyear,getdate() as int),getdate() as datetime)set streetid= (select top 1 StreetId from hos_street order by

19、 newid()set htid=(select top 1 HTId from hos_type order by newid()-向hos_house中插入數(shù)據(jù)insert into hos_housevalues(userid,streetid,htid,price,topic,contents,htime,copy)set i=i+1enddeclare recordcount intselect recordcount=(select count(*) from hos_house)if recordcount>100000 begin rollback transaction

20、 print '插入人數(shù)超過上限，插入失敗' end else begin commit transaction print '插入成功' endset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 -單位：s分析過程：定義局部變量，對局部變量進行隨機賦值，利用循環(huán)語句對hos_house表插入十萬條語句，運用事務對插入語句進行優(yōu)化，縮短插入語句時間。執(zhí)行結果：如圖6圖6 hos_house表中插入的數(shù)據(jù)3、實訓三：綜合查詢(1) 分頁顯示查詢出租房屋信息-建立臨時表#t,用于存放查詢的數(shù)

21、據(jù)create table #t(HMID int primary key,UserId int not null , StreetID int not null , HTId int not null ,Price decimal(6,2) not null ,Topic varchar(50) not null,Contents varchar(100) not null,HTime datetime not null ,Copy varchar(80)-用select-top分頁方式查詢數(shù)據(jù)，并將數(shù)據(jù)插入到臨時表中insert into #t(HMID,UserId,StreetID,H

22、TId,Price,Topic,Contents,HTime,Copy) select top 10 * from hos_house where(HMID not in(select top 90 HMID from hos_house order by HMID) order by HMID-顯示臨時表中的數(shù)據(jù)select * from #t-查詢臨時表中第6-第10行數(shù)據(jù)select top(5) * from #t where HMID not in(select top(5) HMID from #t)-查詢并改變所有列標題select HMID as 房屋編號, UserId as

23、 用戶編號, StreetID as 街道編號, HTId as 房屋類型編號, Price as 價格, Topic as 標題, Contents as 房屋描述, HTime as 發(fā)布時間, Copy as 備注,ROW_NUMBER() over(order by HMID desc)rank from hos_house分析過程：建立臨時表#t用于存放查詢過程，用select-top分頁方式查詢數(shù)據(jù)，并將數(shù)據(jù)插入到臨時表中，查詢臨時表中第6-第10行數(shù)據(jù)，查詢并改變所有列標題。執(zhí)行結果：如圖7圖7分頁顯示查詢出租房屋信息(2) 查詢指定客戶發(fā)布的出租房屋信息-使用內(nèi)聯(lián)接inner

24、join查詢實現(xiàn)declare begin datetime,end datetimeset begin =getdate()select DName,SName,hos_type.HTName,Topic,Price,Contents,HTime,Copy from (hos_house inner join sys_user on hos_house.UserId =sys_user.UserId) inner join hos_street on hos_house.StreetID =hos_street.StreetId) inner join hos_district on hos

25、_street.SDId =hos_district.DId) inner join hos_type on hos_house.HTId =hos_type.HTId) where sys_user.UserName='王雪麗'set end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 -單位：s-建立臨時表用where子句和內(nèi)查詢實現(xiàn)declare begin datetime,end datetimeset begin =getdate()create table #n(DId int,DName var

26、char(50),StreetId int,SName varchar(50),SDId int)insert into #n(DId,DName,StreetId,SName,SDId) select DId,DName,StreetId,SName,SDId from hos_district,hos_street where hos_district.DId=hos_street.SDIdselect DName,SName,hos_type.HTName,Topic,Price,Contents,HTime,Copy from hos_house,hos_type,#n,sys_use

27、r where sys_user.UserName='王雪麗' and hos_house.UserId=sys_user.UserId and hos_house.HTId =hos_type.HTId and hos_house.StreetID=#n .StreetId set end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 -單位：s分析過程：使用內(nèi)聯(lián)接inner join查詢實現(xiàn)，建立臨時表用where子句和內(nèi)查詢實現(xiàn)。執(zhí)行結果：如圖8、圖9圖8使用內(nèi)聯(lián)接inner join查詢結果圖9建立臨時表

28、用where子句和內(nèi)查詢結果(3) 按區(qū)縣制作房屋出租清單-使用having子句篩選出街道數(shù)大于1的區(qū)縣select HTName,UserName,DName,SName from #n,hos_house,sys_user,hos_type where sys_user.UserId=hos_house.UserId and #n.StreetId=hos_house.StreetID and hos_type.HTId=hos_house.HTId and #n.SDId in(select SDId from #n group by SDId having(count(StreetId

29、 )>1) 分析過程：使用having子句篩選出街道數(shù)大于1的區(qū)縣執(zhí)行結果：如圖10圖10使用having子句篩選出街道數(shù)大于1的區(qū)縣結果4、實訓四：業(yè)務統(tǒng)計(1) 按季度統(tǒng)計本年度發(fā)布的房屋出租數(shù)量-按季度統(tǒng)計本年度發(fā)布的房屋出租數(shù)量create view View_QTDst(HTime,DName,SName,HTName,number)as select datepart(quarter,HTime) as '季度',DName as '區(qū)縣',SName as '街道',HTName as '戶型',count(*

30、) as '數(shù)量' from (hos_house inner join hos_type on hos_house.HTId=hos_type.HTId) inner join hos_street on hos_house.StreetID=hos_street.StreetId) inner join hos_district on hos_district.DId=hos_street.SDId) group by datepart(quarter,HTime),DName,SName,HTNameselect * from View_QTDst分析過程：按季度統(tǒng)計本年

31、度發(fā)布的房屋出租數(shù)量執(zhí)行結果：如圖11圖11 (2) 統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量-統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量declare begin datetime,end datetimeset begin =getdate()select HTime as '季度',DName as '區(qū)縣',sum(number) as '數(shù)量' from View_QTDst group by HTime,DNameset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 -單

32、位：s分析過程：統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量執(zhí)行結果：如圖12圖12(3) 統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量總和及街道戶型明細-統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量總和及街道戶型明細-select sum(number) as '數(shù)量' from View_QTDst -計算表里記錄的總數(shù)declare season1 intset season1=1declare season2 intset season2=2declare season3 intset season3=3declare season4 intset season4=4 -第一季度select

33、season1 as '季度','合計' as '區(qū)縣',''as '街道',''as '戶型',sum(number) from View_QTDst where HTime=season1 union allselect season1 as '季度','洪山區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_Q

34、TDst where DName='洪山區(qū)' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='洪山區(qū)' and HTime=season1 union allselect season1 as '季度','武昌區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from Vi

35、ew_QTDst where DName='武昌區(qū)' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='武昌區(qū)' and HTime=season1 union allselect season1 as '季度','青山區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) fro

36、m View_QTDst where DName='青山區(qū)' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='青山區(qū)' and HTime=season1 union allselect season1 as '季度','江漢區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number)

37、 from View_QTDst where DName='江漢區(qū)' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='江漢區(qū)' and HTime=season1 union allselect season1 as '季度','硚口區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(num

38、ber) from View_QTDst where DName='硚口區(qū)' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='硚口區(qū)' and HTime=season1 union all -第二季度select season2 as '季度','合計' as '區(qū)縣',''as '街道',''as '戶型',

39、sum(number) from View_QTDst where HTime=season2 union allselect season2 as '季度','洪山區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='洪山區(qū)' and HTime=season2 union allselect HTime,DName,SName,HTName,number from Vie

40、w_QTDst where DName='洪山區(qū)' and HTime=season2 union allselect season2 as '季度','武昌區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='武昌區(qū)' and HTime=season2 union allselect HTime,DName,SName,HTName,number from

41、 View_QTDst where DName='武昌區(qū)' and HTime=season2 union allselect season2 as '季度','青山區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='青山區(qū)' and HTime=season2 union allselect HTime,DName,SName,HTName,number

42、from View_QTDst where DName='青山區(qū)' and HTime=season2 union allselect season2 as '季度','江漢區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='江漢區(qū)' and HTime=season2 union allselect HTime,DName,SName,HTName,num

43、ber from View_QTDst where DName='江漢區(qū)' and HTime=season2 union allselect season2 as '季度','硚口區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='硚口區(qū)' and HTime=season2 union allselect HTime,DName,SName,HTName

44、,number from View_QTDst where DName='硚口區(qū)' and HTime=season2union all -第三季度select season3 as '季度','合計' as '區(qū)縣',''as '街道',''as '戶型',sum(number) from View_QTDst where HTime=season3 union allselect season3 as '季度','洪山區(qū)' as &

45、#39;區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='洪山區(qū)' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='洪山區(qū)' and HTime=season3 union allselect season3 as '季度','武昌區(qū)'

46、as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='武昌區(qū)' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='武昌區(qū)' and HTime=season3 union allselect season3 as '季度','青山區(qū)&#

47、39; as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='青山區(qū)' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='青山區(qū)' and HTime=season3 union allselect season3 as '季度','江

48、漢區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='江漢區(qū)' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='江漢區(qū)' and HTime=season3 union allselect season3 as '季度',&#

49、39;硚口區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='硚口區(qū)' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='硚口區(qū)' and HTime=season3union all -第四季度select season4 as '季

50、度','合計' as '區(qū)縣',''as '街道',''as '戶型',sum(number) from View_QTDst where HTime=season4 union allselect season4 as '季度','洪山區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName=

51、'洪山區(qū)' and HTime=season4 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='洪山區(qū)' and HTime=season4 union allselect season4 as '季度','武昌區(qū)' as '區(qū)縣','小計'as '街道',''as '戶型',sum(number) from View_QTDst where DName='武昌區(qū)' and HTime=season4 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='武昌區(qū)' and HTime=season4 u