《我的租房网设计与实现》 代码

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

3、 Housegocreate table hos_street(-街道编号，主键，标识列从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开始，递增值为1HTI

4、d int identity(1,1) primary key,-房屋类型名称，非空HTNamevarchar(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), -街道编号，非空，外键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,-发布时间，非空，默认值为当前日期，要求不大于当前日期HTime datetime not null default(getdate() constraint ck_HTime check(HTime<=getdate(),-备注Copy varchar(80)(3) 添加外键约束-给客户信息表中的UserName创建非聚集索引create unique nonclustered index Idx_userNameon sys_user(UserName)withfillf

7、actor=10;-给区县信息表中的DName创建非聚集索引create unique nonclustered index Idx_dNameon hos_district(DName)withfillfactor=10;-给街道信息表中的SName创建非聚集索引create unique nonclustered index Idx_sNameon hos_street(SName)withfillfactor=10;-给房屋信息表中的HTName创建非聚集索引create unique nonclustered index Idx_htNameon hos_type(HTName)wit

8、hfillfactor=10;分析过程：给客户信息表、区县信息表、街道信息表、房屋信息表中添加非聚集索引来提高查询的速度，对经常使用的UserName、DName、SName、HTName进行查询优化2、实训二：添加测试数据(1) 主表添加测试数据-向客户信息表sys_user添加多条条测试数据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')-向区县信息表中添加多条记录insert into hos_districtvalues('洪山区&

11、#39;), ('武昌区'), ('青山区'), ('江汉区'), ('硚口区')-向街道信息表中添加多条记录 insert into hos_streetvalues('街道口','1'), ('卓刀泉','1'), ('广埠屯','1'), ('石牌岭','1'), ('积玉桥','2'), ('杨家园','2'), ('水果湖&

12、#39;,'2'), ('黄鹤楼','2'), ('红卫路','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,)-向三张临时表中插入数据insert into #topicvalues('东方花园')insert into #topicvalues('金茂东方公寓')insert into #topicvalues('世贸大酒店')insert into #topicvalues('民航小区')insert into #contentsvalues(

15、'全新家具电器')insert into #contentsvalues('简单装修押一付三')insert into #contentsvalues('精装修,首出租')insert into #contentsvalues('豪华装修,拎包入住')insert into #copyvalues('环境优雅,学区房')insert into #copyvalues('购物方便')insert into #copyvalues('豪华小区，环境优美')insert into #cop

16、yvalues('交通便利,配套完善')执行结果：如图1、图2、图3、图4、图5图1客户信息表图2区县信息表图3街道信息表图4房屋信息表 图5三张临时表(2) 添加批量数据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条数据-使用事物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之间随机产生set price=1000+cast(3000*RAND() as int)-发布时间htime，要求小于当前系统时间，发布时间在当前系统时间一年内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中插入数据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 '插入人数超过上限，插入失败' end else begin commit transaction print '插入成功' endset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 -单位：s分析过程：定义局部变量，对局部变量进行随机赋值，利用循环语句对hos_house表插入十万条语句，运用事务对插入语句进行优化，缩短插入语句时间。执行结果：如图6图6 hos_house表中插入的数据3、实训三：综合查询(1) 分页显示查询出租房屋信息-建立临时表#t,用于存放查询的数

21、据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分页方式查询数据，并将数据插入到临时表中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-显示临时表中的数据select * from #t-查询临时表中第6-第10行数据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 发布时间, Copy as 备注,ROW_NUMBER() over(order by HMID desc)rank from hos_house分析过程：建立临时表#t用于存放查询过程，用select-top分页方式查询数据，并将数据插入到临时表中，查询临时表中第6-第10行数据，查询并改变所有列标题。执行结果：如图7图7分页显示查询出租房屋信息(2) 查询指定客户发布的出租房屋信息-使用内联接inner

24、join查询实现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子句和内查询实现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分析过程：使用内联接inner join查询实现，建立临时表用where子句和内查询实现。执行结果：如图8、图9图8使用内联接inner join查询结果图9建立临时表

28、用where子句和内查询结果(3) 按区县制作房屋出租清单-使用having子句筛选出街道数大于1的区县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子句筛选出街道数大于1的区县执行结果：如图10图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(*

30、) as '数量' 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分析过程：按季度统计本年

31、度发布的房屋出租数量执行结果：如图11图11 (2) 统计出各个季度各个区县出租房屋的数量-统计出各个季度各个区县出租房屋的数量declare begin datetime,end datetimeset begin =getdate()select HTime as '季度',DName as '区县',sum(number) as '数量' from View_QTDst group by HTime,DNameset end=getdate()PRINT DATEDIFF(millisecond, begin, end)/1000.0 -单

32、位：s分析过程：统计出各个季度各个区县出租房屋的数量执行结果：如图12图12(3) 统计出各个季度各个区县出租房屋的数量总和及街道户型明细-统计出各个季度各个区县出租房屋的数量总和及街道户型明细-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

33、season1 as '季度','合计' as '区县',''as '街道',''as '户型',sum(number) from View_QTDst where HTime=season1 union allselect season1 as '季度','洪山区' as '区县','小计'as '街道',''as '户型',sum(number) from View_Q

34、TDst where DName='洪山区' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='洪山区' and HTime=season1 union allselect season1 as '季度','武昌区' as '区县','小计'as '街道',''as '户型',sum(number) from Vi

35、ew_QTDst where DName='武昌区' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='武昌区' and HTime=season1 union allselect season1 as '季度','青山区' as '区县','小计'as '街道',''as '户型',sum(number) fro

36、m View_QTDst where DName='青山区' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='青山区' and HTime=season1 union allselect season1 as '季度','江汉区' as '区县','小计'as '街道',''as '户型',sum(number)

37、 from View_QTDst where DName='江汉区' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='江汉区' and HTime=season1 union allselect season1 as '季度','硚口区' as '区县','小计'as '街道',''as '户型',sum(num

38、ber) from View_QTDst where DName='硚口区' and HTime=season1 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='硚口区' and HTime=season1 union all -第二季度select season2 as '季度','合计' as '区县',''as '街道',''as '户型',

39、sum(number) from View_QTDst where HTime=season2 union allselect season2 as '季度','洪山区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='洪山区' and HTime=season2 union allselect HTime,DName,SName,HTName,number from Vie

40、w_QTDst where DName='洪山区' and HTime=season2 union allselect season2 as '季度','武昌区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='武昌区' and HTime=season2 union allselect HTime,DName,SName,HTName,number from

41、 View_QTDst where DName='武昌区' and HTime=season2 union allselect season2 as '季度','青山区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='青山区' and HTime=season2 union allselect HTime,DName,SName,HTName,number

42、from View_QTDst where DName='青山区' and HTime=season2 union allselect season2 as '季度','江汉区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='江汉区' and HTime=season2 union allselect HTime,DName,SName,HTName,num

43、ber from View_QTDst where DName='江汉区' and HTime=season2 union allselect season2 as '季度','硚口区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='硚口区' and HTime=season2 union allselect HTime,DName,SName,HTName

44、,number from View_QTDst where DName='硚口区' and HTime=season2union all -第三季度select season3 as '季度','合计' as '区县',''as '街道',''as '户型',sum(number) from View_QTDst where HTime=season3 union allselect season3 as '季度','洪山区' as &

45、#39;区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='洪山区' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='洪山区' and HTime=season3 union allselect season3 as '季度','武昌区'

46、as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='武昌区' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='武昌区' and HTime=season3 union allselect season3 as '季度','青山区&#

47、39; as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='青山区' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='青山区' and HTime=season3 union allselect season3 as '季度','江

48、汉区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='江汉区' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='江汉区' and HTime=season3 union allselect season3 as '季度',&#

49、39;硚口区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='硚口区' and HTime=season3 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='硚口区' and HTime=season3union all -第四季度select season4 as '季

50、度','合计' as '区县',''as '街道',''as '户型',sum(number) from View_QTDst where HTime=season4 union allselect season4 as '季度','洪山区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName=

51、'洪山区' and HTime=season4 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='洪山区' and HTime=season4 union allselect season4 as '季度','武昌区' as '区县','小计'as '街道',''as '户型',sum(number) from View_QTDst where DName='武昌区' and HTime=season4 union allselect HTime,DName,SName,HTName,number from View_QTDst where DName='武昌区' and HTime=season4 uni