我的租房网设计与实现

1、 数据库技术与开发项目实训设计报告项目名称： 我的租房网姓名：专业：指导教师：完成日期：内蒙古科技大学信息工程学院计算机系数据库技术与应用实验报告姓名学号实验成绩班级实验日期项目号、实验名称实训项目我的租房网实 验 要 求1、完成实训项目我的租房网并完成实训一到实训4中的上机实践内容2、按照项目实训报告相关要求，提交一份电子版项目实训报告实 验 内 容1、实训一：建立数据库结构(1)创建数据库House使用SSMS向导创建数据库 House(2)建立5张数据表-创建客户 信息表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) )一-创建区县信息表hos_district use Housegocreate table hos_district(一-区县编号，主键，标识列从1开始，递增值为1Did int identity(1,1) primary key,-区县名称，非空DName varchar(50) not null )-创建街道信息表hos_st

3、reetuse Housegocreate table hosstreet(-街道编号，主键，标识列从1开始，递增值为1StreetId int identity(1,1) primary key,-街道名称，非空SName varchar(50) not null,-区县编号，表hos_district 的外键SDId int constraint fk_SDId foreign key(SDId) references hos_district(DId).-创建房屋信息表hos_typeuse Housegocreate table hos_type(一-房屋类型编号，主键，标识列从1开始

4、，递增值为1HTId int identity(1,1) primary key,-房屋类型名称，非空HTName varchar(50) not null)-创建出租房屋信息表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,-发布时间，非空，默认值为当前日期，要求不大于当前日期HTime datetime not null default(getdate() constraint ck_HTime check(HTime& IJS1II 110 Illi11东方花园z金抵东浮出量世大港店民航小区ContsntsmII ! mriii irs?i i-ri mri a=全新寄具电器 ：”高箪羹植而二万三“精装修,若出租豪华装怪拎包. -Copy11标指在减孚反百% 1 :-Bin b-ii ii

7、u j: na-.a - j*购物方便京华小区，拜交通便利，配一图5三张临时表(2)添加批量数据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条数据-使用事物begin transactiondeclare i intset i=0while i100000beginrollback transactionprint 插入人数超过上限，插入失败end else begin commit transaction print 插入成功 endset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 - 单位：s分析过程：定义局部变量，对局部变量进行随机赋值，利用循环语句对 hos_house表插入十万条语句，运用事务对插入语句进行优化，缩短插入语句

9、时间。执行结果：如图6用躲置血StrfEtUKTIdErie(TopicCoitats)rm1 |imId43553 W岷大骷禄期养四11:35 海璐3菊i便札国营W善211311126L5QL 01世熟酒店脚鞋偃郛一忖三期HME 11：巴瑞琳3意匈区环胤膜311猛14?c w口注叩期小区情翱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附册褊学区房10LL21012136羽M DJ般大醛豪像像书包315-01-2? 11:15 36 3U即使i ias u：5 s&加部前葬浮理图7分页显示查询出租房屋信息(2)查询指定客户发布的出租房屋信息-使用内联接inner join 查询实现 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子句和内查询实现 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 分析过程：使用内联接inner join查询实现，建立临时表用 where子句和内查询实现。 执行结果：如图8、图9二I结果.消息FTNaneT/ictContentsHim*Cop*一/一厅民刖区10L：,00菖物妍忖三河叼941【1:15： M3 奸施翻酉1春善西空河

15、厅民航小区24?：, 00半装修，首出租2015-10-C9LJ:15:36,703热小国环埔糕丽宴厅1754.0Q髀装如柜2D15-02-13L 1:1,5： 36. 70T瞬雕5W世贸切店2BFE.00司单装悸ff-归三3015-1-14L1:15:36,13丽便同富朝厅金番苑.而00全箍目电券M15-W-I0Ll;1=;3e,7tT斶臃学瓯一里一6物花园因焚.00全驱的器2015-03-611:15:36,730隔怫字L宣一厅四天旗ism管单装恒#-世Ll;15;3fi.74O珊方便三室助厅东旅园减00管处愕#-世州15HT-I之:】：15:弼.743丽傕汝玲红的图8使用内联接inner

16、 join查询结果n 苣 受卓茜一一 工区一区 F山反 IIE七二工2 3 4 LO釉区 豳口区 青山区-I结果哨息Dian*KH奶。Toficfriesfont ent sHTinaCopy1携而谆禹一室后觥小区EQ1NM简单喘度产忖三11g；瓠裔女雕札皤涯2,OE法室助万飕讪区1)分析过程：使用having子句筛选出街道数大于1的区县执行结果：如图10曲结果为消息HTilarneU 当 erHane DNside UBIIIBU Jill IMM Illi BMl ij二m两厅严德塞江改区新华街N21至阳1 丁土殍百山区J刖向咫四室两厅成龙武昌区水果湖五室两厅程嵋江汉区北湖街E四堂两仃郛觥

17、江汉区新华街e两室两厅崔晓宇江汉区满春街四室两厅旅英武武昌区福家园s三室两厅严（属武胃区水果湖四室两厅郑利逢青山区泊金街五室两厅M有国东江汉医吴家山图10使用having子句筛选出街道数大丁1的区县纪：果4、实训四：业务统计(1)按季度统计本年度发布的房屋出租数量-按季度统计本年度发布的房屋出租数量create view View_QTDst(HTime,DName,SName,HTName,number)as select datepart(quarter,HTime) as 季度,DName as 区县,SName as 街道,HTName as 户型,count(*) as 数量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分析过程：按季度统计本圣度发布的房屋出租数量执行结果：如图11结果二消息KTim

19、eliKiiain-flli ivfi iMamli-HeFTUamenumber111j洪山区广埠屯的室两厅23。21洪山区广埠屯三室晒厅22431洪山区石牌岭四室荫厅21741洪山区石牌岭一宣一厅硒51洪山区卓刀杲的室的厅22961江观区北湖街 北湖街两室荫厅24171江汉区二室柄厅阳81江汉区于华街两室两厅23491福口区长随四室两厅239101磕口区长风街图11一室一厅3(2)统计出各个季度各个区县出租房屋的数量-统计出各个季度各个区县出租房屋的数量declare begin datetime,end datetimeset begin =getdate()select HTime a

20、s 季度,DName as 区县,sum(number) as 数量fromView_QTDst group by HTime,DNameset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 - 单位：s分析过程：统计出各个季度各个区县出租房屋的数量执行结果：如图12结果消息季度区县数量1i 2j江汉区5665z1赫口区5C0733青山区591441洪山区54洪山区州3064能口区2766T2青山区5754*3武昌区56999洪山区575410L江汉区K34图12(3)统计出各个季度各个区县出租房屋的数量总和及街道户型明

21、细-统计出各个季度各个区县出租房屋的数量总和及街道户型明细-select sum(number) as 数量from View_QTDst - 计算表里记录的 总数一declare season1 intset season1=1declare season2 intset season2=2declare season3 intset season3=3declare season4 intset season4=4- 第一季度select season1 as 季度,合计as 区县Jas 街道Jas 户型,sum(number) from View_QTDst where HTime=se

22、ason1union allselect season1as 季度,洪山区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName=洪山区and HTime=season1union allselect HTime,DName,SName,HTName,number from ViewQTDst whereDName毛山区and HTime=seasonlunion allselect season1as 季度,武昌区as 区县,小计:as 街道Jas户型,sum(number) from View_QTDst where DName

23、=武昌区and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst where DName隶昌区and HTime=season1union allselect season1as 季度,青山区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName= 青山区and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst whereDNa

24、me*山区and HTime=season1union allselect season1as 季度,江汉区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName=江汉区and HTime=season1union allselect HTime,DName,SName,HTName,number from View_QTDst where DName式汉区and HTime=season1union allselect season1as 季度,研口区as 区县,小计as 街道Jas户型,sum(number) from View

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

26、from View_QTDst where DName=洪山区and HTime=season2union allselect HTime,DName,SName,HTName,number from View_QTDst where DName4山区and HTime=season2union allselect season2as 季度,武昌区as 区县,小计:as 街道Jas户型,sum(number) from View_QTDst where DName=武昌区and HTime=season2union allselect HTime,DName,SName,HTName,numb

27、er from View_QTDst where DName隶昌区and HTime=season2union allselect season2as 季度,青山区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName= 青山区and HTime=season2union allselect HTime,DName,SName,HTName,number from View_QTDst where DName*山区and HTime=season2union allselect season2as 季度,江汉区as 区县,小计as

28、街道Jas户型,sum(number) from View_QTDst where DName=江汉区and HTime=season2union allselect HTime,DName,SName,HTName,number from View_QTDst where DName式汉区and HTime=season2union allselect season2as 季度,研口区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName=研口区and HTime=season2union allselect HTime,DNam

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

30、Time,DName,SName,HTName,number from View_QTDst where DName4山区and HTime=season3union allselect season3as 季度,武昌区as 区县,小计:as 街道Jas户型,sum(number) from View_QTDst where DName=武昌区and HTime=season3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName隶昌区and HTime=season3union allselect

31、 season3as 季度,青山区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName= 青山区and HTime=season3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName*山区and HTime=season3union allselect season3as 季度,江汉区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName=江汉区and HTime=seas

32、on3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName式汉区and HTime=season3union allselect season3as 季度,研口区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName=研口区and HTime=season3union allselect HTime,DName,SName,HTName,number from View_QTDst where DName淅口区and HTime=sea

33、son3union all - 第四季度select season4 as 季度,合计as 区县Jas 街道Jas 户 型,sum(number) from View_QTDst where HTime=season4union allselect season4as 季度,洪山区as 区县,小计as 街道Jas户型,sum(number) from View_QTDst where DName=洪山区and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName4山区an

34、d HTime=season4union allselect season4as 季度,武昌区as 区县,小计:as 街道Jas 户型,sum(number) from View_QTDst where DName=武昌区and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName隶昌区and HTime=season4union allselect season4as 季度,青山区as 区县,小计as 街道Jas 户型,sum(number) from View_QTD

35、st where DName= 青山区and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName*山区and HTime=season4union allselect season4as 季度,江汉区as 区县,小计as 街道Jas 户型,sum(number) from View_QTDst where DName=江汉区and HTime=season4union allselect HTime,DName,SName,HTName,number from View_QTDst where DName=1汉区and HTime=season4union allselect season