租房网设计与实现详解.docx

数据库技术与开发项目实训设计汇报项目名称：我的租房网姓名：专业：指导教师：完毕日期：内蒙古科技大学信息工程学院计算机系数据库技术与应用试验汇报姓名学号试验成绩班级试验日期项目号、试验名称实训项目我的租房网实验要求1、完毕实训项目我的租房网并完毕实训一到实训I4中的上机实践内容2、按照项目实训汇报有关规定，提交一份电子版项目实训汇报实验1、实训一：建立数据库构造(1)创立数据库HOUSe内使用SSMS向导创立数据库House容(2)建立5张数据表一创立客户信息表SysUSercreatetablesys_user(一客户编号，.主键标识列UserIdintidentity(1,1)primarykey,一客户姓名，非空UserNamevarchar(50)notnull,一客户密码，至少6个字符UserPwdvarchar(50)constraintck_UserPwdcheck(Ien(UserPwd)>=6)一创立区县信息表hos_districtuseHousegocreatetablehos_district(一区县编号，主键，标识列从1开始，递增值为1Didintidentity(1,1)primarykey,一区县名称，非空DNamevarchar(50)notnull)一创立街道信息表hos_streetuseHousegocreatetablehos_street(一街道编吊，主键，标识列从i开箱，递增值为1StreetIdintidentity(1,1)primarykey,一街道名称，非空SNamevarchar(50)notnull,一区县编号，表hos_district的(外键SDIdintconstraintfk_SDIdforeignkey(SDId)referenceshos_district(Did)一创立房屋信息表hos_typeuseHousegocreatetablehos_type(一房屋类型编号，主键，标识列从1开始，递增值为1HTldintidentity(1,1)Primarykey,一房屋类型名称，非空HTNamevarchar(50)notnull)一创立出租房屋信息表hos_houseuseHousegocreatetablehoshouse(一出租房屋编号，主键，标识列从1开始，递增值为1HMIDintidentity(1,1)primarykey,一客户编号，非空，外键UserIdintnotnullconstraintfk_UserIdforeignkey(UserId)referencessysuser(UserId),一街道编号，非空，外键StreetIDintnotnullconstraintfkStreetJDforeignkey(StreetID)referenceshos_streetXStreetJD),一房屋类型编号，非空，外键HTIdintnotnullconstraintfkHTIdforeignkey(HTId)referenceshos_type(HTId),一月租金，非空；默认值为0,规定不小于等于0Pricedecimal(6,2)notnulldefault(0)constraintckPricecheck(Price>=0),一标题，非空Topicvarchar(50)notnull,-描述,非空Contentsvarchar(100)notnull,后公布时间，非空，默认值为目前日期，规定不不小于目前日HTimedatetimenotnulldefault(getdate()constraintckHTimecheck(HTime<=getdate(),一蓄注Copyvarchar(80)(3)添加外键约束一给客户信息表中的IUSerName创立非汇集索引createuniquenonclusteredindexIdx_userNameonsys_user(UserName)withfilIfactor=IO;一给区县值息表中的DNanIe创立非汇集索引createuniquenonclusteredindexIdx_dNameonhos_district(DName)withfilIfactor=IO;一给街道信息表中的SName创立非汇集索引createuniquenonclusteredindexIdxsNameOnhosstreet(SNalne)withfillfactor=10;一给房屋信息表中的HTNanle创立非汇集索引createuniquenonclusteredindexIdx_htNameonhos_type（HTName）withfillfactor=10;分析过程：给客户信息表、区县信息表、街道信息表、房屋信息表中添加非汇集索引来提高查询的速度，对常常使用的USerName、DName.SName>HTName进行查询优化2、实训二：添加测试数据（1）主表添加测试数据一向客户信息表sys_user添加多条条测试数据insertintosys_uservalues（'王雪丽'IoooO0'）,（'严德赛'，T00001'）,（'王生高100002'）,（'崔晓宇'，T00003'）,（'卢一帆100004'）,（'张英武'，T00005'）,（'安鹏'，'100006'）,（胖哥'，100007'）,（程峰'，'100008'）,（'马云'，'100009'）,（'王铮'，100010'）,（'刘强东'，TOOOll'）,（'雷舒然'，'100012'）,（成龙'，'100013'）,（'武则天'，100014'）,（'焦旭鹏100015'）,（'郑利泽'，100016'）,（'罗阳光'，T00017'）,（'邱国龙'，'100018'）,C李小龙'100019'）-向区县信息表中添加多条记录insertintohos_districtVaIUeS（'洪山区'），（'武昌区'），（'青山区'），（'江汉区'），（'研口区'）112222333 >444455550s,<, >>>,>>>, hIs,解梭解析时管lil,时知舒舒时 to取"埠牌玉家果鹤卫沟金前家湖春华角正中风 inY广石积杨水黄红新冶厂吴北满新六汉汉长一向房屋信息表中添加多条记录insertintohos_typevalues(,两室一厅'),（'两室两厅'）,一建立三张临时表（'三室两厅'）,（'四室两厅'）,（'五室两厅'）createtabletttttopic(Topicvarchar(50)notnull,)createtableWttcontents(Contentsvarchar(50)notnull,)createtable#COPy(Copyvarchar(50)notnull,)一向三张临时表中插入数据insertinto#topicVaIUeS（东方花园）insertintotttttopicvalues（,金茂东方公寓'）insertinto#topicValUeS（'世贸大酒店'）insertintotttttopicvalues（,民航小区'）insertinto#ContentSvalues（,圣新家俱电需）insertinto#COntentSvalues（,简朴蓑修押一付三'）insertintoSttcontentsVaIUeS（'精装修音出租'）insertintottttcontentsvalues（,豪华装修,拎包入住'）insertinto#COPyValUeS（'环境优雅,学区房'）insertinto#COPyvalues（,财物以便'）insertinto#CoPyvalu6s（'豪华小区，环境优美'）insertinto#COPyvaluesC交通便利，配套完善'）执行成果：如图1、图2、图3、图4、图5结果J消息UserIdUserNameUserPwd11王雪丽10000022产德赛100OOl33王生高10000244崔晓宇100355卢一帆10000466张英武10000577安曜10000688胖哥100007图1客户信息表1DIdDNamei洪山区24江汉区35新口区43青山区52武昌区图2区县信息表StreetIdSNameSDId1T街道口122卓刀泉133广埠屯144石牌岭155积玉桥266杨家园277水果湖288黄鹤楼2图3街道信息表HTIdHTName12:两室两厅21两室一厅34三室两厅45四室两厅56五室两厅63一室一厅图4房屋信息表二I结果消息Topic1:东方花园2金茂东方公里3世贸大酒店4民航小区Contents1I全新家具电器i2简单装修押一付三3精装修,首出租4豪华装修,拎包Copy1;环境优雅,学区房I2购物方便3豪华小区，环4交通便利,酉匕图5三张临时表(2)添加批量数据declare©begindatetime,enddatetimeset©begin=getdate()一定义局部变量declare©topicvarchar(50)declare©contentsvarchar(50)declare©copyvarchar(50)declare©useridintdeclare©streetidintdeclarehtidintdeclare©pricedecimal(6,2)declarehtimedatetime一向hos_house表中插入IoOOO条数据一使用事物begintransactiondeclareiintseti=0whilei<100begin-对局部霁量进行赋值settopic=(selecttop1*fromtttttopicorderbynewid()set©contents=(selecttop1*fromttttcontentsorderbynewid()set©copy=(selecttop1*from#Cc)Pyorderbynewid()SeIeCttop1uSerid二USeridfromSySUSerOrderbyNEWID()一租金在-4000之间随机产生setprice=1000+cast(3000*RAND()asint)一公营吐哪hti野二规定不不小于目前系统时间，公布时间在目箭索统时间一年内sethtime=cast(dateadd(day,-cast(rand()*datepart(dayofyear,getdate()asint),getdate()asdatetime)setstreetid=(selecttop1StreetIdfromhos_streetorderbynewid()sethtid=(selecttop1HTIdfromhos_typeorderbynewid()-向hosjhouse中插入数据insertintohos_housevalues(serid,©streetid,htid,©price,©topic,©contents,htime,copy)seti=i+lenddeclare©recordcountintselect©recordcount=(selectcount(*)fromhos_house)ifrecordcount>100000beginrollbacktransactionPrint'插入人数超过上限，插入失败endelsebegincommittransactionprint插入成功'endSetend=getdate()PRINTDATEDIFF(millisecond,©begin,end)/IOoO.0一单位:s分析过程：定义局部变量，对局部变量进行随机赋值，运用循环语句对hos_house表插入十万条语句，运用事务对插入语句进行优化，缩短插入语句时间。执行成果：如图6j消息三DUserIdStreetIDKrldPriceTopicContentsKTineCopy1H1201,21643553.00世贸大酒店全麻具螭2015-05-1611:15:36.263邈便利周靛善2112Q2111261501.00世贸大酒店简单辘制-柘2015-08-19H45:36.263京驹区，环情优美31120314752161.00就小区精装修，首眈2015-11-1011:15:36.263环副港学区房41120491463945.00世贸大酒店精装修,首雌2015-07-1311:15:36.263购妨便511205181651069.00东方花园全麻鼬署2015-05-1611:15:36.283遍便利,醋福61120641521007.00东方花园简单装翻一框2015-02-0211:15:36.310豪华小区，礴优美7112076653914.00东方花园豪华装修,拎包.2015-09-2411:15:36.330逾便利周靛善8112082743579.00世贸大酒店豪华装卷拎包2015-09-1811:15:36.350贼肪便911209171153284.00民航小区简单辘制一生2015-03-2311:15:36.353环胤滩学区房1011210121363437.00世贸大酒店豪华辘,拎包2015-01-2711:36.377峨昉便图6hos_house表中插入区（数据3、实训三：综合查询(1)分页显示查询出租房屋信息一建立临时表枇,用于寄存查询的数据createtable#t(HMIDintprimarykey,UserIdintnotnull,StreetIDintnotnull,HTIdintnotnull,Pricedecimal(6,2)notnull,Topicvarchar(50)notnull,Contentsvarchar(100)notnull,HTimedatetimenotnull,Copyvarchar(80)一用Seleet-top分页方式查询数据，并将数据插入到临时表中insertinto#t(HMID,Userid,StreetID,HTId,Price,Topic,Contents,HTime,Copy)selecttop10*fromhos_housewhere(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备注，R丽_NUMBER()over(orderbyHMIDdesc)ranfromhos_house分析过程：建立临时表枇用于寄存查询过程，用select-top分页方式查询数据，并将数据插入到临时表中，查询临时表中第6-第10行数据，查询并变化所有列标题。执行成果：如图7MDUserIdStreetIDH11dPriceTopicContentsKTiaeCoPy1,1129616462614.00民豺区精装修首出租2015-10-0211:15:36,72?交通便利,酉！霁善21129721562907.00金麻方公寓全般具蟋2015-08-0311:16:36,727怀胤滩学瓯3112987563392.00东方旗解装哪-佐2015-05-1411:15:36,72?购物方便4112998652376.00东方端豪华装修拎包入住2015-02-1811:15:36.727购物方便5113001432032.00东方磁全解具蟋2015-03-2611:15:36.730怀胤摊，学瓯图7分页显示查询出租房屋信息(2)查询指定客户公布的出租房屋信息-使用内联接innerjoin查询实现declare©begindatetime,enddatetimeset©begin=getdate()selectDName,SName,hostype.HTName,Topic,Price,Contents,HTime,Copyfrom(Hoshouseinnerjoinsys_useronhoshouse.UserId=sys_user.Userid)innerjoinhos_streetonhos_house.StreetID=hos_street.StreetId)innerjoinhos_districtonhos_street.SDId=hos_district.Did)innerjoinhos_typeonhoshouse.HTId=hos_type.HTId)WhereSySuSer.UserName=王雪hIjrSetend=getdateOPRINTDTEDIFF(miIlisecond,©begin,end)/100O.O一单位:s-建立临时表用Where子句和内查询实现declare©begindatetime,enddatetimeset©begin=getdate()createtable#n(Didint,DNamevarchar(50),StreetIdint,SNamevarchar(50),SDIdint)insertintottn(DId,DName,StreetId,SName,SDId)selectDid,DName,StreetId,SName,SDIdfromhos_district,hos_streetwherehos_district.DId=hos_street.SDIdselectDName,SName,hostype.HTName,Topic,Price,Contents,HTime,Copyfromhos_House,hosypen,sys_userwhereSySUSer.UserName=王雪丽andhoshouse.Userld=sys_user.UserIdandhos_house.HTId=hos_type.HTIdandhos_house.StreetID=ttn.StreetIdSetend=getdate()PRINTDATEDIFF(millisecond,©begin,end)/100O.0一单位:s分析过程：使用内联接innerjoin查询实现，建立临时表用Where子句和内查询实现。执行成果：如图8、图9-3结果J消息DNarneSNaneKrNaffieTopicPriceContentsHTimeCoPy1拱山区淖暇一室一厅民航小区1012.00简单装修押一框2015-09-211115:36.397交通便利,酉湾完善2江汉区荡春街两室两厅民航小区2491.00精装修首出租2015-10-091115:36.703豪华小区，环境优美3江汉区薪春街两室一厅世贸大酒店1754.00简单装修押一付三2015-02-131115:36.707购物方便4洪山区卓刀泉五室两厅世贸大酒店2875.00简单装修押一付三2015-01-141115:36,713购物方便5洪山区街道口两室两厅金茂东.1739.00全觞具电器2015-03-101115:36,717环境优雅,学区房6洪山区石一宣一厅东方花园2032.00全篇家具电器2015-0361115:36.730礴优雅,学区房7格口区汉正街一空一厅世贸大酒店1979.00简单装修押一付三2015-04-221115:36,740购肪便8青山区红卫路三室两厅东方花园2297.00简单装修押一付三2015-07-121115:36,743购肪便图8使用内联接innerjoin查询成果口结果.J消息DNameSFane三ameTopicPriceContentsHTimeCopy1捌区；卓7>一室一厅民航小区1012.00简单装修押一付三2015-09-2111:15:36.397交通便利,醛完善2江汉区防春街两室两厅民航小区2491.00懒修首出租2015-10-0911:15:36.703豪华小区，环境优美3江汉区黄春街两室一厅世贸大酒店1754.00简单装修押一付三2015-02-1311:15:36.70?购物方便4洪山区卓刀泉五室两厅世贸大酒店2875.00简鞋翻一付三2015-01-1411:15:36.713购物方便5洪山区街道口两室两厅金茂东1739.00全新家具电器2015-03-1011:15:36,717环情优雅,学瓯6洪岖石懒一室一厅东方花园2032.00全新家具电器2015-03-2611:15:36.730环雅雅,学瓯?曲口区汉正街一室一厅世贸大酉店1979.00简单装修押一付三2015-04-2211:15:36.740购物方便8青山区红呼三室所东方花园2297.00简单装修押一付三2015-07-1211:15:36,743购物方便图9建立临时表用where子句和内查询成果(3)按区县制作房屋出租清单一使用having子句筛选出街道数不小于1时区县selectHTName,UserName,DName,SNamefrom#n,hos_house,sys_user,hos_typewheresys_user.Userld=hos_house.UserIdand#n.StreetId=hos_house.StreetIDandhos_type.HTId=hoshouse.HTIdand#n.SDIdin(selectSDIdfrom#ngroupbySDIdhaving(count(StreetId)>1)分析过程：使用having子句筛选出街道数不小于1时区县执行成果：如图10结果a消息HTNameUserNameDNameSName1:三室两厅:产德赛江汉区新华街2五室两厅王铮青山区厂前街道3四室两厅成龙武昌区水果湖4五室两厅程峨江汉区北湖街5四室两厅罗阳光江汉区新华街6两室两厅崔晓宇江汉区满春街7四室两厅张英武武昌区杨家园8三室两厅严德赛武昌区水果湖9四室两厅郑利泽青山区冶金街10五室两厅刘强东江汉区吴家山图10使用having子句筛选出街道数不小于1的（区县成果4、实训四：业务记录(1)按季度记录本年度公布的房屋出租数量一按季度记录率年度公布的)屋出租数量createviewViewQTDst(HTime,DName,SName,IITName,number)asselectdatepart(quarter,HTime)as'季度'，DNameas'区拜,SNameas'街道'，HTNameas户型'，count(*)as'数量from(hoshouseinnerjoinhos_typeonhos_house.HTId=Hos_type.HTIdJinnerjoinhos_streetonhos_house.StreetID=hos_street.StreetId)innerjoinhos_districtonhos_district.DId=hos_street.SDIdJgroupbydatepart(quarter,HTime),DName,SName,HTNameselect*fromView_QTDst分析过程：按季度记录本年度公布的房屋出租数量执行成果：如图11国结果1盅斗画HTimeDNameSNaJneHTNanienumber11j洪山区广埠屯两室两厅23021洪山区广埠屯三室两厅22431洪山区石牌岭四室两厅21741洪山区石牌岭一室一厅23851洪山区卓刀泉两室两厅22961江汉区北湖街两室两厅24171江汉区北湖街三室两厅22381江汉区新华街两室两厅23491磕口区长风街四室两厅239101研口区长风街一室一厅229图H(2)记录出各个季度各个区县出租房屋的数量一记录出各个季度各个区县出租房屋的数量declare©begindatetime,©enddatetimeset©begin=getdate()selectHTimeas'季度'，DNameas区县，sum(number)as'数量'fromViewQTDstgroupbyHTime,DNameSetend=getdate()PRINTDATEDIFF(miIlisecond,©begin,end)/100o.O一单位:s分析过程：记录出各个季度各个区县出租房屋0数量执行成果：如图12结果J消息!季度区县数里1j江汉区565521研口区560733青山区591441洪山区562154洪山区283064济口区276572青山区575483武昌区568992洪山区5754101江汉区5634图12(3)记录出各个季度各个区县出租房屋的数量总和和街道户型明细厂记录出各个季度各个区县出租房屋的数量总和和街道户型明细一selectsum(number)as'数量'fromViewQTDst一计算表里记录的总数一declare©seasonlintsetseasonl=ldeclareseason2intsetSeaSon2=2declareseason3intsetseason3=3declareseason4intsetseason4=4-第一季度select©seasonlas'季度'，'合计'as'区县'，''as'街道,as'户型，sum(number)fromViewQTDstwhereHTinIeSeaSonIunionallselect©seasonlas'季度'，'洪山区'as区县'，'小计'as街道，''as'户型'，sum(number)fromView_QTDstwhereDNanIe='洪山区'andHTime=SeaSonlunionallselectHTimejDNamerSName,HTName,numberfromViewQTDstwhereDName=洪山区'andHTime=Sseasonlunionallselect©seasonlas季度，武昌区as区县，小计as街道，as'户型'，sum(number)fromViewQTDstwherei)Name=武昌区'andHTime=seasonlunionallselectHTimejDNamexSName,HTName,numberfromViewQTDstwhereDName=武昌区'andHTime=Sseasonlunionallselect©seasonlas季度'，青山区'as'区县，小计as街道，''as'户型',sum(number)fromViewQTDstwhereDNanIe='青山区'andHTime=seasonlunionallselectHTimejDNamerSName,HTName,numberfromViewQTDstwhereDName=青山区'andHTiIne=SeaSonlunionallselect©seasonlas季度'，'江汉区'as区县'，'小计'as街道'，''as户型'，sum(number)fromView_QTDstwhereDName='江汉区'andHTime=SseasonlunionallselectHTimejDNameSName,IlTName,numberfromViewQTDstwhereDName=江汉区'andHTime=seasonlunionallselect©seasonlas'季度'，'研口区'as区县'，'小计'as'街道，''as'户型',sum(number)fromViewQTDstwheret)Name='研口区'andHTime=seasonlunionallselectHTimejDNamexSName,HTName,numberfromView_QTDstwhereDName=研口区'andHTime=Sseasonlunionall-第二季度selectSeaSon2as'季度'，'合计'as区县'，''as'街道,as'户型'，sum(number)fromViewQTDstwhereHTinIe=SeaSon2unionallselect©season?as'季度'，'洪山区'as'区县'，'小计'as'街道'，''as'户型',sum(number)fromViewQTDstwhereDName二'洪山区andHTime=season2unionallselectHTimejDNamexSName,HTName,numberfromView_QTDstwhereDName=洪山区'andHTime=SeaSon2unionallselectseason2as,季度'，武昌区'as'区县，'小计'as街道'，'as'户型'，sum(number)fromViewQTDstwhereDNanie='武昌区'andHTime=season2unionallselectHTime.DNameSName,HTName,numberfromViewQTDstwhereDName=武昌区'andHTiIne=SeaSOn2unionallselectSeaSOn2as'季度'，'青山区'as区县'，'小计'as街道，''as'户型'，sum(number)fromView_QTDstwhere的ame='青山区'andHTime=r0SeaSon2unionallselectHTimejDName1SName,IlTName,numberfromViewQTDstWhereDNam6二青山区'andHTim6=SeaSon2unionallselectSeaSOn2as,季度'，'江汉区'as区县'，'小计'as街道，''as'户型',sum(number)fromViewQTDstwherebName='江汉反'andHTime=season2unionallselectHTime.DNamerSName,HTName,numberfromViewQTDstwhereDName=江汉区'andHTime=SeaSOn2unionallselectseason2as'季度'，'砺口区'as区县'，'小计'as街道'，''as户型'，sum(number)fromView_QTDstwhereDName=，耕口区'andHTime=season2unionallselectIlTimejDNamerSName,IITName,numberfromViewQTDstwhereDName=研口区'andHTiine=SeaSon2unionall一第三季度selectseason3as'季度'，'