我的租房网设计与实现代码Word文档格式.docx
《我的租房网设计与实现代码Word文档格式.docx》由会员分享,可在线阅读,更多相关《我的租房网设计与实现代码Word文档格式.docx(24页珍藏版)》请在冰豆网上搜索。
![我的租房网设计与实现代码Word文档格式.docx](https://file1.bdocx.com/fileroot1/2023-2/2/67774290-fdec-4991-b416-83d37d83ccb3/67774290-fdec-4991-b416-83d37d83ccb31.gif)
UserNamevarchar(50)notnull,
--客户密码,至少6个字符
UserPwdvarchar(50)constraintck_UserPwdcheck(len(UserPwd)>
=6)
)
--创建区县信息表hos_district
useHouse
go
createtablehos_district
--区县编号,主键,标识列从1开始,递增值为1
DIdintidentity(1,1)primarykey,
--区县名称,非空
DNamevarchar(50)notnull
--创建街道信息表hos_street
createtablehos_street
--街道编号,主键,标识列从1开始,递增值为1
StreetIdintidentity(1,1)primarykey,
--街道名称,非空
SNamevarchar(50)notnull,
--区县编号,表hos_district的外键
SDIdintconstraintfk_SDIdforeignkey(SDId)referenceshos_district(DId)
--创建房屋信息表hos_type
createtablehos_type
--房屋类型编号,主键,标识列从1开始,递增值为1
HTIdintidentity(1,1)primarykey,
--房屋类型名称,非空
HTNamevarchar(50)notnull
--创建出租房屋信息表hos_house
createtablehos_house
--出租房屋编号,主键,标识列从1开始,递增值为1
HMIDintidentity(1,1)primarykey,
--客户编号,非空,外键
UserIdintnotnullconstraintfk_UserIdforeignkey(UserId)referencessys_user(UserId),
--街道编号,非空,外键
StreetIDintnotnullconstraintfk_StreetIDforeignkey(StreetID)referenceshos_street(StreetID),
--房屋类型编号,非空,外键
HTIdintnotnullconstraintfk_HTIdforeignkey(HTId)referenceshos_type(HTId),
--月租金,非空,默认值为0,要求大于等于0
Pricedecimal(6,2)notnulldefault(0)constraintck_Pricecheck(Price>
=0),
--标题,非空
Topicvarchar(50)notnull,
--描述,非空
Contentsvarchar(100)notnull,
--发布时间,非空,默认值为当前日期,要求不大于当前日期
HTimedatetimenotnulldefault(getdate())constraintck_HTimecheck(HTime<
=getdate()),
--备注
Copyvarchar(80)
(3)添加外键约束
--给客户信息表中的UserName创建非聚集索引
createuniquenonclusteredindexIdx_userName
onsys_user(UserName)
with
fillfactor=10;
--给区县信息表中的DName创建非聚集索引
createuniquenonclusteredindexIdx_dName
onhos_district(DName)
--给街道信息表中的SName创建非聚集索引
createuniquenonclusteredindexIdx_sName
onhos_street(SName)
--给房屋信息表中的HTName创建非聚集索引
createuniquenonclusteredindexIdx_htName
onhos_type(HTName)
分析过程:
给客户信息表、区县信息表、街道信息表、房屋信息表中添加非聚集索引来提高查询的速度,对经常使用的UserName、DName、SName、HTName进行查询优化
2、实训二:
添加测试数据
(1)主表添加测试数据
--向客户信息表sys_user添加多条条测试数据
insertintosys_user
values('
王雪丽'
'
100000'
),
('
严德赛'
100001'
王生高'
100002'
崔晓宇'
100003'
卢一帆'
100004'
张英武'
100005'
安鹏'
100006'
胖哥'
100007'
程峰'
100008'
马云'
100009'
王铮'
100010'
刘强东'
100011'
雷舒然'
100012'
成龙'
100013'
武则天'
100014'
焦旭鹏'
100015'
郑利泽'
100016'
罗阳光'
100017'
邱国龙'
100018'
李小龙'
100019'
--向区县信息表中添加多条记录
insertintohos_district
洪山区'
武昌区'
青山区'
江汉区'
硚口区'
--向街道信息表中添加多条记录
insertintohos_street
街道口'
1'
卓刀泉'
广埠屯'
石牌岭'
积玉桥'
2'
杨家园'
水果湖'
黄鹤楼'
红卫路'
3'
新沟桥'
冶金街'
厂前街道'
吴家山'
4'
北湖街'
满春街'
新华街'
六角亭'
5'
汉正街'
汉中街'
长风街'
--向房屋信息表中添加多条记录
insertintohos_type
两室一厅'
两室两厅'
一室一厅'
三室两厅'
四室两厅'
五室两厅'
--建立三张临时表
createtable##topic
createtable##contents
Contentsvarchar(50)notnull,
createtable##copy
Copyvarchar(50)notnull,
--向三张临时表中插入数据
insertinto##topic
东方花园'
金茂东方公寓'
世贸大酒店'
民航小区'
insertinto##contents
全新家具电器'
简单装修押一付三'
精装修,首出租'
豪华装修,拎包入住'
insertinto##copy
环境优雅,学区房'
购物方便'
豪华小区,环境优美'
交通便利,配套完善'
执行结果:
如图1、图2、图3、图4、图5
图1客户信息表
图2区县信息表
图3街道信息表
图4房屋信息表
图5三张临时表
(2)添加批量数据
declare@begindatetime,@enddatetime
set@begin=getdate()
--定义局部变量
declare@topicvarchar(50)
declare@contentsvarchar(50)
declare@copyvarchar(50)
declare@useridint
declare@streetidint
declare@htidint
declare@pricedecimal(6,2)
declare@htimedatetime
--向hos_house表中插入10000条数据
--使用事物
begintransaction
declare@iint
set@i=0
while@i<
100
begin
--对局部变量进行赋值
set@topic=(selecttop1*from##topicorderbynewid())
set@contents=(selecttop1*from##contentsorderbynewid())
set@copy=(selecttop1*from##copyorderbynewid())
selecttop1@userid=useridfromsys_userorderbyNEWID()
--租金在-4000之间随机产生
set@price=1000+cast(3000*RAND()asint)
--发布时间@htime,要求小于当前系统时间,发布时间在当前系统时间一年内
set@htime=cast(dateadd(day,-cast(rand()*datepart(dayofyear,getdate())asint),getdate())asdatetime)
set@streetid=(selecttop1StreetIdfromhos_streetorderbynewid())
set@htid=(selecttop1HTIdfromhos_typeorderbynewid())
--向hos_house中插入数据
insertintohos_house
values(@userid,@streetid,@htid,@price,@topic,@contents,@htime,@copy)
set@i=@i+1
end
declare@recordcountint
select@recordcount=(selectcount(*)fromhos_house)
if@recordcount>
100000
begin
rollbacktransaction
print'
插入人数超过上限,插入失败'
end
else
committransaction
插入成功'
set@end=getdate()
PRINTDATEDIFF(millisecond,@begin,@end)/1000.0--单位:
s
定义局部变量,对局部变量进行随机赋值,利用循环语句对hos_house表插入十万条语句,运用事务对插入语句进行优化,缩短插入语句时间。
如图6
图6hos_house表中插入的数据
3、实训三:
综合查询
(1)分页显示查询出租房屋信息
--建立临时表#t,用于存放查询的数据
createtable#t
HMIDintprimarykey,
UserIdintnotnull,
StreetIDintnotnull,
HTIdintnotnull,
Pricedecimal(6,2)notnull,
HTimedatetimenotnull,
--用select-top分页方式查询数据,并将数据插入到临时表中
insertinto#t(HMID,UserId,StreetID,HTId,Price,Topic,Contents,HTime,Copy)selecttop10*fromhos_house
where(HMIDnotin(selecttop90HMIDfromhos_houseorderbyHMID))orderbyHMID
--显示临时表中的数据
select*from#t
--查询临时表中第6-第10行数据
selecttop(5)*from#twhereHMIDnotin(selecttop(5)HMIDfrom#t)
--查询并改变所有列标题
selectHMIDas房屋编号,
UserIdas用户编号,
StreetIDas街道编号,
HTIdas房屋类型编号,
Priceas价格,
Topicas标题,
Contentsas房屋描述,
HTimeas发布时间,
Copyas备注,ROW_NUMBER()over(orderbyHMIDdesc)rankfromhos_house
建立临时表#t用于存放查询过程,用select-top分页方式查询数据,并将数据插入到临时表中,查询临时表中第6-第10行数据,查询并改变所有列标题。
如图7
图7分页显示查询出租房屋信息
(2)查询指定客户发布的出租房屋信息
--使用内联接innerjoin查询实现
selectDName,SName,hos_type.HTName,Topic,Price,Contents,HTime,Copyfrom((((hos_houseinnerjoinsys_useronhos_house.UserId=sys_user.UserId)
innerjoinhos_streetonhos_house.StreetID=hos_street.StreetId)
innerjoinhos_districtonhos_street.SDId=hos_district.DId)
innerjoinhos_typeonhos_house.HTId=hos_type.HTId)
wheresys_user.UserName='
--建立临时表用where子句和内查询实现
createtable#n
DIdint,
DNamevarchar(50),
StreetIdint,
SNamevarchar(50),
SDIdint
insertinto#n(DId,DName,StreetId,SName,SDId)selectDId,DName,StreetId,SName,SDIdfromhos_district,hos_streetwherehos_district.DId=hos_street.SDId
selectDName,SName,hos_type.HTName,Topic,Price,Contents,HTime,Copyfromhos_house,hos_type,#n,sys_user
wheresys_user.UserName='
andhos_house.UserId=sys_user.UserIdandhos_house.HTId=hos_type.HTIdandhos_house.StreetID=#n.StreetId
使用内联接innerjoin查询实现,建立临时表用where子句和内查询实现。
如图8、图9
图8使用内联接innerjoin查询结果
图9建立临时表用where子句和内查询结果
(3)按区县制作房屋出租清单
--使用having子句筛选出街道数大于1的区县
selectHTName,UserName,DName,SNamefrom#n,hos_house,sys_user,hos_type
wheresys_user.UserId=hos_house.UserIdand#n.StreetId=hos_house.StreetIDandhos_type.HTId=hos_house.HTIdand#n.SDIdin(selectSDIdfrom#ngroupbySDIdhaving(count(StreetId)>
1))
使用having子句筛选出街道数大于1的区县
如图10
图10使用having子句筛选出街道数大于1的区县结果
4、实训四:
业务统计
(1)按季度统计本年度发布的房屋出租数量
--按季度统计本年度发布的房屋出租数量
createviewView_QTDst(HTime,DName,SName,HTName,number)
asselectdatepart(quarter,HTime)as'
季度'
DNameas'
区县'
SNameas'
街道'
HTNameas'
户型'
count(*)as'
数量'
from(((hos_houseinnerjoinhos_typeonhos_house.HTId=hos_type.HTId)
innerjoinhos_streetonhos_house.StreetID=hos_street.StreetId)
innerjoinhos_districtonhos_district.DId=hos_street.SDId)
groupbydatepart(quarter,HTime),DName,SName,HTName
select*fromView_QTDst
按季度统计本年度发布的房屋出租数量
如图11
图11
(2)统计出各个季度各个区县出租房屋的数量
--统计出各个季度各个区县出租房屋的数量
selectHTimeas'
sum(number)as'
fromView_QTDstgroupbyHTime,DName
统计出各个季度各个区县出租房屋的数量
如图12
图12
(3)统计出各个季度各个区县出租房屋的数量总和及街道户型明细
--统计出各个季度各个区县出租房屋的数量总和及街道户型明细
--selectsum(number)as'
fromView_QTDst--计算表里记录的总数
declare@season1int
set@season1=1
declare@season2int
set@season2=2
declare@season3int
set@season3=3
declare@season4int
set@season4=4
--第一季度
select@season1as'
合计'
as'
'
as'
sum(number)fromView_QTDstwhereHTime=@season1
unionall
小计'
sum(number)fromView_QTDstwhereDName='
andHTime=@season1
un