我的租房網(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ù)庫結(jié)構(gòu)(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 identit

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

3、reetuse Housegocreate table hosstreet(-街道編號，主鍵，標識列從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開始

4、，遞增值為1HTId int identity(1,1) primary key,-房屋類型名稱，非空HTName varchar(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),-街道編號，正空

5、，外鍵StreetID 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),-標題，非空Top

6、ic varchar(50) not null,-描述，非空Contents varchar(100) not null,-發(fā)布時間，非空，默認值為當前日期，要求不大于當前日期HTime datetime not null default(getdate() constraint ck_HTime check(HTime& IJS1II 110 Illi11東方花園z金抵東浮出量世大港店民航小區(qū)ContsntsmII ! mriii irs?i i-ri mri a=全新寄具電器 ：”高簞羹植而二萬三“精裝修,若出租豪華裝怪拎包. -Copy11標指在減孚反百% 1 :-Bin b-ii ii

7、u j: na-.a - j*購物方便京華小區(qū)，拜交通便利，配一圖5三張臨時表(2)添加批量數(shù)據(jù)declare begin datetime,end datetime set begin =getdate()-定義局部變量declare topic varchar(50)declare contents varchar(50)declare copy varchar(50)declare userid intdeclare streetid intdeclare htid intdeclare price decimal(6,2)declare htime datetime-向hos hous

8、e表中插入10000條數(shù)據(jù)-使用事物begin transactiondeclare i intset i=0while i100000beginrollback transactionprint 插入人數(shù)超過上限，插入失敗end else begin commit transaction print 插入成功 endset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 - 單位：s分析過程：定義局部變量，對局部變量進行隨機賦值，利用循環(huán)語句對 hos_house表插入十萬條語句，運用事務對插入語句進行優(yōu)化，縮短插入語句

9、時間。執(zhí)行結(jié)果：如圖6用躲置血StrfEtUKTIdErie(TopicCoitats)rm1 |imId43553 W岷大骷祿期養(yǎng)四11:35 海璐3菊i便札國營W善211311126L5QL 01世熟酒店腳鞋偃郛一忖三期HME 11：巴瑞琳3意匈區(qū)環(huán)胤膜311猛14?c w口注叩期小區(qū)情翱s后陽a2315-11-1C 11； 536 源H匍慟芹甌4 LL3O1$463945.00世鉆出店德修首出租an兩即 n W 3s 263而使E 112061816gl而DO東方邈弱底膽黑ZJ1E-0E-1E 11I15 36 283施震電酒慢W善Q 11205415LQH力東方碗群豹物一忖三231-0

10、4 11:15 / 丈Q割心濟勒美?1120?6&5331鼻 tnW0豪華獎修欄包2015-05-24 1J:：5 36 530誕便札配晝W善B 1L20821435日h骸頻店熟姿修弓也231Hs-12 11:15 36 西0麗傳11209KJI5312M m艮劇嘔一代三11:15 抬 353附冊褊學區(qū)房10LL21012136羽M DJ般大醛豪像像書包315-01-2? 11:15 36 3U即使i ias u：5 s&加部前葬浮理圖7分頁顯示查詢出租房屋信息(2)查詢指定客戶發(fā)布的出租房屋信息-使用內(nèi)聯(lián)接inner join 查詢實現(xiàn) declare begin datetime,end

11、datetime set begin =getdate()selectDName,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_street.SDId =hos_district.DId)inner

12、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 datetime set begin =getdate() create table #n(DId int,DName varchar(50),StreetId int,SName varchar(50),SDI

13、d 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.SDIdselectDName,SName,hos_type.HTName,Topic,Price,Contents,HTime,Copy from hos_house,hos_type,#n,sys_user where sys_user.UserName= 王雪麗and hos_house.

14、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í)行結(jié)果：如圖8、圖9二I結(jié)果.消息FTNaneT/ictContentsHim*Cop*一/一廳民刖區(qū)10L：,00菖物妍忖三河叼941【1:15： M3 奸施翻酉1春善西空河

15、廳民航小區(qū)24?：, 00半裝修，首出租2015-10-C9LJ:15:36,703熱小國環(huán)埔糕麗宴廳1754.0Q髀裝如柜2D15-02-13L 1:1,5： 36. 70T瞬雕5W世貿(mào)切店2BFE.00司單裝悸ff-歸三3015-1-14L1:15:36,13麗便同富朝廳金番苑.而00全箍目電券M15-W-I0Ll;1=;3e,7tT斶臃學甌一里一6物花園因焚.00全驅(qū)的器2015-03-611:15:36,730隔怫字L宣一廳四天旗ism管單裝恒#-世Ll;15;3fi.74O珊方便三室助廳東旅園減00管處愕#-世州15HT-I之:】：15:弼.743麗傕汝玲紅的圖8使用內(nèi)聯(lián)接inner

16、 join查詢結(jié)果n 苣 受卓茜一一 工區(qū)一區(qū) F山反 IIE七二工2 3 4 LO釉區(qū) 豳口區(qū) 青山區(qū)-I結(jié)果哨息Dian*KH奶。Toficfriesfont ent sHTinaCopy1攜而諄禹一室后觥小區(qū)EQ1NM簡單喘度產(chǎn)忖三11g；瓠裔女雕札皤涯2,OE法室助萬颼訕區(qū)1)分析過程：使用having子句篩選出街道數(shù)大于1的區(qū)縣執(zhí)行結(jié)果：如圖10曲結(jié)果為消息HTilarneU 當 erHane DNside UBIIIBU Jill IMM Illi BMl ij二m兩廳嚴德塞江改區(qū)新華街N21至陽1 丁土殍百山區(qū)J刖向咫四室兩廳成龍武昌區(qū)水果湖五室兩廳程嵋江漢區(qū)北湖街E四堂兩仃郛觥

17、江漢區(qū)新華街e兩室兩廳崔曉宇江漢區(qū)滿春街四室兩廳旅英武武昌區(qū)福家園s三室兩廳嚴（屬武胃區(qū)水果湖四室兩廳鄭利逢青山區(qū)泊金街五室兩廳M有國東江漢醫(yī)吳家山圖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(*) as 數(shù)量from

18、 (hos_house inner join hos_type on hos_house.HTId=hos_type.HTId)innerjoin hos_street on hos_house.StreetID=hos_street.StreetId)inner joinhos_district on hos_district.DId=hos_street.SDId)group bydatepart(quarter,HTime),DName,SName,HTNameselect * from View_QTDst分析過程：按季度統(tǒng)計本圣度發(fā)布的房屋出租數(shù)量執(zhí)行結(jié)果：如圖11結(jié)果二消息KTim

19、eliKiiain-flli ivfi iMamli-HeFTUamenumber111j洪山區(qū)廣埠屯的室兩廳23。21洪山區(qū)廣埠屯三室曬廳22431洪山區(qū)石牌嶺四室蔭廳21741洪山區(qū)石牌嶺一宣一廳硒51洪山區(qū)卓刀杲的室的廳22961江觀區(qū)北湖街 北湖街兩室蔭廳24171江漢區(qū)二室柄廳陽81江漢區(qū)于華街兩室兩廳23491?？趨^(qū)長隨四室兩廳239101磕口區(qū)長風街圖11一室一廳3(2)統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量-統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量declare begin datetime,end datetimeset begin =getdate()select HTime a

20、s 季度,DName as 區(qū)縣,sum(number) as 數(shù)量fromView_QTDst group by HTime,DNameset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 - 單位：s分析過程：統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量執(zhí)行結(jié)果：如圖12結(jié)果消息季度區(qū)縣數(shù)量1i 2j江漢區(qū)5665z1赫口區(qū)5C0733青山區(qū)591441洪山區(qū)54洪山區(qū)州3064能口區(qū)2766T2青山區(qū)5754*3武昌區(qū)56999洪山區(qū)575410L江漢區(qū)K34圖12(3)統(tǒng)計出各個季度各個區(qū)縣出租房屋的數(shù)量總和及街道戶型明

21、細-統(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 season1 as 季度,合計as 區(qū)縣Jas 街道Jas 戶型,sum(number) from View_QTDst where HTime=se

22、ason1union allselect season1as 季度,洪山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=洪山區(qū)and HTime=season1union allselect HTime,DName,SName,HTName,number from ViewQTDst whereDName毛山區(qū)and HTime=seasonlunion allselect season1as 季度,武昌區(qū)as 區(qū)縣,小計:as 街道Jas戶型,sum(number) from View_QTDst where DName

23、=武昌區(qū)and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst where DName隸昌區(qū)and HTime=season1union allselect season1as 季度,青山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName= 青山區(qū)and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst whereDNa

24、me*山區(qū)and HTime=season1union allselect season1as 季度,江漢區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=江漢區(qū)and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst where DName式漢區(qū)and HTime=season1union allselect season1as 季度,研口區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View

25、_QTDst where DName=研口區(qū)and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst whereDName淅口區(qū)and HTime=season1union all -第二季度select season2 as 季度,合計as 區(qū)縣Jas 街道Jas 戶 型,sum(number) from View_QTDst where HTime=season2union allselect season2as 季度,洪山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number)

26、from View_QTDst where DName=洪山區(qū)and HTime=season2union allselect HTime,DName,SName,HTName,number from View_QTDst where DName4山區(qū)and HTime=season2union allselect season2as 季度,武昌區(qū)as 區(qū)縣,小計:as 街道Jas戶型,sum(number) from View_QTDst where DName=武昌區(qū)and HTime=season2union allselect HTime,DName,SName,HTName,numb

27、er from View_QTDst where DName隸昌區(qū)and HTime=season2union allselect season2as 季度,青山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName= 青山區(qū)and HTime=season2union allselect HTime,DName,SName,HTName,number from View_QTDst where DName*山區(qū)and HTime=season2union allselect season2as 季度,江漢區(qū)as 區(qū)縣,小計as

28、街道Jas戶型,sum(number) from View_QTDst where DName=江漢區(qū)and HTime=season2union allselect HTime,DName,SName,HTName,number from View_QTDst where DName式漢區(qū)and HTime=season2union allselect season2as 季度,研口區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=研口區(qū)and HTime=season2union allselect HTime,DNam

29、e,SName,HTName,number from View_QTDst where DName淅口區(qū)and HTime=season2union all 一第二季度select season3 as 季度,合計as 區(qū)縣Jas 街道Jas 戶型,sum(number) from View_QTDst where HTime=season3union allselect season3as 季度,洪山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=洪山區(qū)and HTime=season3union allselect H

30、Time,DName,SName,HTName,number from View_QTDst where DName4山區(qū)and HTime=season3union allselect season3as 季度,武昌區(qū)as 區(qū)縣,小計:as 街道Jas戶型,sum(number) from View_QTDst where DName=武昌區(qū)and HTime=season3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName隸昌區(qū)and HTime=season3union allselect

31、 season3as 季度,青山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName= 青山區(qū)and HTime=season3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName*山區(qū)and HTime=season3union allselect season3as 季度,江漢區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=江漢區(qū)and HTime=seas

32、on3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName式漢區(qū)and HTime=season3union allselect season3as 季度,研口區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=研口區(qū)and HTime=season3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName淅口區(qū)and HTime=sea

33、son3union all - 第四季度select season4 as 季度,合計as 區(qū)縣Jas 街道Jas 戶 型,sum(number) from View_QTDst where HTime=season4union allselect season4as 季度,洪山區(qū)as 區(qū)縣,小計as 街道Jas戶型,sum(number) from View_QTDst where DName=洪山區(qū)and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName4山區(qū)an

34、d HTime=season4union allselect season4as 季度,武昌區(qū)as 區(qū)縣,小計:as 街道Jas 戶型,sum(number) from View_QTDst where DName=武昌區(qū)and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName隸昌區(qū)and HTime=season4union allselect season4as 季度,青山區(qū)as 區(qū)縣,小計as 街道Jas 戶型,sum(number) from View_QTD

35、st where DName= 青山區(qū)and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName*山區(qū)and HTime=season4union allselect season4as 季度,江漢區(qū)as 區(qū)縣,小計as 街道Jas 戶型,sum(number) from View_QTDst where DName=江漢區(qū)and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName=1漢區(qū)and HTime=season4union allselect season