数据库我的租房网

数据库技术与开发项目名称：我的租房网号：专 业：软件工程项目实训内容1、实训一：建立数据库结构(1)创建数据库House使用SSMS向导创建数据库House，如下图所示:图1.创建数据库House扩展内容：要求用语句建立加嘶2数据库。CREATEDATABASEHouse ON PRIMARY(NAME=NHouse,FILENAME=Nd:sqlHouse.mdf,SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=1 MB)LOG ON(NAME=NHouse_log,FILENAME=Nd:sqlHouse_log.ldf,SIZE= 1MB,MAXSIZE： 20MB,FILEGROWTH=10%)(2)建立5张数据表USE HouseCREATE TABLE sys_user(UserId INT IDENTITY(1,1) PRIMARY KE ,UserName varchar(50) NOT NULL,UserPwd VARCHAR(50),CONSTRAINT ck_userpwd CHECK(LEN(UserPwd)=6)USE HouseCREATE TABLE hos_district(DId INT IDENTITY(1,1) PRIMARY KE ,DName VARCHAR(50) NOT null)USE HouseCREATE TABLE hos_street(StreetId INT IDENTITY(1,1) PRIMARY KE ,SName VARCHAR(50) NOT NULL,SDId INT CONSTRAINT fk_id_1 FOREIGN KEY(SDId) REFERENCES hos_district(DId) )USE HouseCREATE TABLE hos_type(HTId INT IDENTITY(1,1) PRIMARY KE ,HTName VARCHAR(50) NOT NULL,)USE HouseCREATE TABLE hos_house(HMId INT IDENTITY PRIMARY KE ,UserId INT NOT NULL,StreetId INT NOT NULL,HTId INT NOT NULL,Price DECIMAL(8,2) CONSTRAINT ck_price CHECK(Price=0) DEFAUL (0),Topic varchar(50) NOT NULL,Contents VARCHAR(50) NOT NULL,HTime DATETIME NOT NULL CONSTRAINT ck_htime CHECK(HTime=GETDATE()DEFAULT(GETDATE(),Copy VARCHAR(50)(3)添加外键约束USE HouseALTER TABLE hos_houseADD CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES sys_user(UserId)ALTER TABLE hos_houseADD CONSTRAINT FK_StreetId FOREIGN KEY(Streetld) REFERENCES hos_street(StreetId)ALTER TABLE hos_houseADD CONSTRAINT FK_HTId FOREIGN KEY(HTId) REFERENCES hos_type(HTId)2、实训二：添加测试数据(1) 主表添加测试数据USE HouseINSERT INTO sys_user(UserName,UserPwd) VALUES(张三,000000),(李四,000000)INSERT INTO hos_district(DName) VALUES(海淀区),(朝阳区)INSERT INTO hos_street(SName,SDId) VALUES(万寿路1),(中关村1 ,(陶然亭,2 ,(大栅栏,2INSERT INTO hos_type(HTName) VALUES(两室一厅),(两室两厅) SE1E匚二 * FROMSE1EC7 * FROMdba. rics district5E1EC7 * FRCM1- SELECT * FROMdbo.hos street.myajiAauskzdba.has cypeJ结果消息Did DName11 I海婕区22 朝阳区Street IdSNaneSDId11万寿路122中关路1332斗4大柵栏2HTId HTName1 rrj两室一厅2 2诙室爾厅Userid UserName UserPwd1 fl強三DDDDDD2 2李四mm(2) 添加批量数据 创建3 个临时表 USE House CREATE TABLE #Topic ( id INT IDENTITY(1,1), topic VARCHAR(50) ) CREATE TABLE #content ( id INT IDENTITY(1,1), content VARCHAR(50) ) CREATE TABLE #copy ( id INT IDENTITY(1,1), copy VARCHAR(50) )INSERT INTO #Topic(topic) VALUES中关村)INSERT INTO #Topic(topic) VALUES万泉新新家园)INSERT INTO #Topic(topic) values望园小区)INSERT INTO #Topic(topic) VALUES福盈家园)INSERT INTO #Topic(topic) VALUES(百子湾号院)INSERT INTO #Topic(topic) VALUES(中关村软件园)INSERT INTO #content(content) VALUES(经典装修，拎包入住)INSERT INTO #content(content) VALUES(超值公寓火爆出租)INSERT INTO #content(content) VALUES(望京朝阳东北区)INSERT INTO #content(content) VALUES(低价个人入住)INSERT INTO #content(content) VALUES(昌平区回龙观)INSERT INTO #content(content) VALUES(精装修，首出租)INSERT INTO #copy(copy) VALUES(交通便利，配套完善)INSERT INTO #copy(copy) VALUES(环境优雅，学区房)INSERT INTO #copy(copy) VALUES(紧挨号地铁)INSERT INTO #copy(copy) VALUES(购物方便)INSERT INTO #copy(copy) VALUES(紧邻亚运村)INSERT INTO #copy(copy) VALUES(山水一体)SELECT * FROM #ccntentSELECT * FROMlSE1EC2 * FROM /TcpicU结果J消息idcontent1经典装修，拎包入住22超值公車火幌出租33望京朝阳东北区44低价个人人住55昌平因回龙观66精装僱，首出租idcopy11交通便利，酉离善22环墳优雅！学区膳33鎏挨号地铁44甌物方便55察邹亚运対CE山水一体idtopic1中关村2323万泉新諦家园望园小区44福盈事园55百子淹号院6中对嫩件园DECLARE userid INTDECLARE streetid INTDECLARE htid INTDECLARE price DECIMALDECLARE htime DATETIMEDECLARE topic VARCHAR(50)DECLARE contents VARCHAR(50)DECLARE copy VARCHAR(50)DECLARE num INTBEGIN TRANSACTIONSET num = 0WHILE num30BEGINSET userid=(SELECT TOP 1 UserId FROM sys_user ORDER BY NEWID()SET streetid=(SELECT TOP 1 streetid FROM hos_street ORDER BY NEWID()SET htid=(SELECT TOP 1 htid FROM hos_type ORDER BY NEWID()-租金在-4000之间随机产生SET price=1000+CAST(3000*RAND() AS INT)-发布时间htime,要求小于当前系统时间，发布时间在当前系统时间一年内SET htime=CAST(DATEADD(D,-CAST(RAND()*DATEPAR(DAYOFYEAR,GETDATE()AS INT),GETDATE() AS DATE)SET topic=(SELECT TOP 1 topic FROM #topic ORDER BY NEWID()PRINT topicSET contents=(SELECT TOP 1 content FROM #content ORDER BY NEWID()SET copy=(SELECT TOP 1 copy FROM #copy ORDER BY NEWID()INSERT INTO hos_house(UserId,StreetId,HTId,Price,Topic,Contents,HTime,Copy)VALUES (userid, streetid, htid, price, topic, contents, htime, copy)SET num=num+lENDCOMMIT TRANSACTIONSELECT - FT.OM hcs=H结灵J常良HMHUser-dStreeildHTIdRiceTop亡ContentsHTmeCopy1222B4D望园d、区昌平区回龙观23154E-3E紧邻亚运村2131215G万泉新新窗园超值越宅火垠出祖2015-D21G Ht:OOflO.H：交通便科,现套完善31A11011百理号慌低价个人入住2O15M20OT:0Ofra.M哀违便帀一，配匡冗更A702412295望副范吕I區匕兀讥2015-01 14 恤 WkMhNJO交逋梗利,鸯匡咒吾571222532福盈郵园低价平从住201H)2WW):00：00.X)E722411773201OOWffl.OCiD山取_体7731321135百一迢兮侯低悄t人入住2D1 M2-(J7 WkOOtKlftM髀E地烘la742213833中却经典装似拎包九住20154E-13他物方便3751112927中邂if超值独宝火艰出租201H8囲收 OOOO.WJO女通惟利,理童冥建1761123669拮装斷首出租201525 Ht:OO.OO.M环悄优雅,学区房-1772-196B中対撇件园精装修,首出祖154JHE罷.阳：叮却山水_体127S1323S74中邂撇件园超值甚宅火惧出祖2015-0526 00:00110.紧挨号地铁13792A212S3中君击校件园吕T匡巨兀观20150201 HJ:OOfra.M-哀违便帀一，配匡冗更2麗1122947福盘專园昌I匕匕兀利2O15D6-OS 观他幵境优雅,宇区尿152211921中关村吕I Irl兀烈2O154)&4QO0:0OlX.M離挨号地诜1412117至回小工低价平从住201 M&41E OOWffl.OCiD他如方便17S31323EK13百宁适兮9吕干区巨尤四201 屈切3环境优雅学区矣1BSA2321221昌平区叵救20154)1-13 OT:OOflO.)C女理闻利嘅言宾善IS51111795家园更京朝阳东北区2D1M&24MJ:OO.OO.M 使用RowNumber函数，要求所有的列标题使用中文，查询结果如下图所示:SELECT TOP 10 ROW_NUMBER ) OVER(ORDER BY HMId) AS rowid,* INTO #temp FROMhos_houseSELECTHMIdAS 房源编号,UserIdAS 用户编号,StreetIDAS 街道编号,TopicAS 标题Contents AS 房源描述,Price AS 月租,HTimeAS 发布时间,CopyAS 备注 FROM #temp WHERE rowid BETWEEN 6 AND 10用戸衍E倩谓艇拆题月租財时间17224福盈家园昌平区回龙观17Z32rHM5-1Q0D.0C：M.(M：CLI水一作27113口子俺号逞1138201 貞広070Q0Q:恤 00037422中关村境典奘惨拎旦扎ft3B阳ajiyQZ-isodoofl.Mfl方便A7511中关村2927aO15(H-29DCC:DD.!：C文直使朴讎完善576I1福盔泰回书黑惦臣二租祐閔201 却殴250&00:.000(2) 査询指定客户发布的出租房屋信息查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县，要求分 别按下述2种方法实现图7的查询结果： 使用内联接inner join查询和子查询实现。SELECT hd.DName 区县,hs.SName 街道；ht.HTName 房屋类型；hh.Topic 标题， hh.Price 价格,hh.Contents 房源描述,hh.HTime 时间,hh.Copy 备注FROM hos_house hhINNER JOIN hos_street hs ON hh.StreetId=hs.StreetIdINNER JOIN hos_district hd ON hs.SDId=hd.DIdINNER JOIN sys_user su ON su.UserId=hh.UserIdINNER JOIN hos_type ht ON ht.HTId = hh.HTIdWHERE su.UserName=张三J咤也韬区县房屋罠型祈悟时间*1洱淀区*1 - 12340昌平邑回龙1301EhD6-M.凳-IU2MLL-.E. J丿一宗打球宁国2156迪|號扁火想出祖2fllEM)2-ia .3虫I区总主.T百f谴号憧1011K-：-心住测54M2d .4虫I L冃主區：T百f邃号悅113SU-：-心住20152-07 .紧艇地铁5沁区万S3&芸辜二 1;2327期惜腔虫火担出祖2EI15-DB-2B.；朋诵利，即言完苫Eh宇ft嗨韬裝囂.兰二in30154)5-25.萍境优雅,7HTEF国耀亍kf|:关村就性园3.974趨盟瞅焜出祖30154)6-20.8月辽卜J.加j2847吕平匡回龙飙20154J54J3 .yJwiTtb宇区毎g舸日童大昭担:-r2117K-： 从住2015-054)3 .lflWI区挂主區：7T百子邃号悅3303巳平电回龙肌勿54舟制.刑誠船尝丄N11F寺诂代福31亲园1735里京翱阳5t北区2A15-D5-24.以旳12删日区IIP：三刊辜活万杲霸祈塞园1935经肉號隐，拎也.2EH5-C+13.劉霍地钱-i-n1 iL&h3E1 II II =WOE .i. rrrm3 亠 nan4l.-rrTwh e m i rtrIX-LnrrJ - LJ曲已族讯齐.I (locsD 別 RTM | 汕(5E) | Hou汨 | 1532 ir使用where子句和内查询实现。SELECT hd.DName 区县；hs.SName 街道；ht.HTName 房屋类型；hh.Topic 标题， hh.Price 价格,hh.Contents 房源描述,hh.HTime 时间,hh.Copy 备注FROM hos_house hh,hos_street hs,hos_district hd,sys_user su,hos_type htWHERE hh.StreetId=hs.StreetId AND hs.SDId=hd.DId AND su.UserId=hh.UserId AND ht.HTId =| (local) (1(X50 RTM)| sa (531 |HcueeOMMtW1532 万hh.HTId AND su.UserName=张三Wit1海淀区产字河T2S40昌平区回龙iSl201-00.000紧耙因12哪君1.三一J万网酹园2156遇悟鱼耳火厲出粗2O1EM)2-1 A3茂宝.7百子追戟1011-K-：羽 154)1 也血JDflO.OOT刼硕口鱼二屯4百子邃號1138哽八、：、:汕2015-C24J7ffi?Oa.KK闵曰对；51看語確一厅1:六卜-2S27：21ME-2&W.X.Ki.DOD2向1?= 1：凸二611吐1福艷屎园痂71庄1叫.J中去村就件园3974超鱼再火J3出粗E：j分茂主性.T福監竄园2347昌平电回走规药巧仍溯 时MHK.OOT朝阳区+卅尸专2117-貯、：、:汕2d15-C5潮阳匡产字西Tzm:3.903昌平邑回龙乱2-3154：r3-2&.-00.000环境饥学区屈11|住一J為益211745望京朝阳车北区20154)5-2412朝阳区曲宝茂：丁万哀暮诉囲1935经與英協推包人住药154)1凋时備flO.OOTh r4 亠 KTu+ 11 J . (T7Viti r24 血！i4lrrrwi4 r -i 4 he nju.nr. Jin run.iP-kDTT14:J查询已盅功机况(3) 按区县制作房屋出租清单SELECT ht.HTName 户型,su.UserName 姓名；hd.DName 区县,hs.SName 街道FROM hos_house hhINNER JOIN hos_street hs ON hh.StreetId=hs.StreetIdINNER JOIN hos_district hd ON hs.SDId=hd.DIdINNER JOIN sys_user su ON su.UserId=hh.UserIdINNER JOIN hos_type ht ON ht.HTId = hh.HTIdWHERE hd.DId IN(SELECT hd.DId FROM hos_house hhINNER JOIN hos_street hs ON hh.StreetId=hs.StreetIdINNER JOIN hos_district hd ON hs.SDId=hd.DId GROUP BY hd.DId HAVING COUNT(*)1)ORDER BY hs.StreetId二结果消息户型区县街道1两室两厅张三海淀区万寿蹈2两室一厅李四海淀区万寿路3關室两斤张三海淀区万寿路4两室两厅张三海蛙区万寿蹈5两室一厅李四海淀区万寿路6两室一厅张三海淀区万寿路7两室一厅李四海淙区万寿路8两室两厅李四海淀区万寿路9两室两厅李四海浣区中关路10沏室两厅张三海淀区11肠室两厅李四海淀区中关路12两室一厅李四海淀区中关路13两室两厅张三朝阳区14两室一厅张三朝阳区15两室一厅李四朝阳区啕然事4、实训四：业务统计(1)按季度统计本年度发布的房屋出租数量CREATE VIEW View_QTDst ASSELECT DATEPAR (QUARTER,hh.HTime)季度,hd.DName 区县；hs.SName 街道,ht.HTName 户型；COUNT(hh.HMId)数量FROM hos_house hhINNER JOIN hos_street hs ON hh.StreetId=hs.StreetIdINNER JOIN hos_district hd ON hs.SDId=hd.DIdINNER JOIN sys_user su ON su.UserId=hh.UserIdINNER JOIN hos_type ht ON ht.HTId = hh.HTIdGROUP BY DATEPAR (QUARTER,hh.HTime),hd.DName,hs.SName,ht.HTName3结果 Illi Illi Illi II1 i i2 13 14 15 16 17 18 19 210 211217-7消息日日日日 nF nMr nu- nu- 剌却区区X 日日日 np nr nu.OF iEfgnnnFv犬柵栏两室两厅104大柵栏两室一厅105两窒两厅92两室一厅128万寿路两室两厅1DD万寿路两室一厅9S中关路S1两窒两厅由关路两室一厅104大柵栏103两窒两厅夭柵栏两室一厅117131两窒两厅（2）统计出各个季度各个区县出租房屋的数量SELECT季度，区县,SUM（数量）房屋数量FROM View_QTDst（3）统计出各个季度各个区县出租房屋的数量总和及街道户型明细SELECT季度，区县，街道，户型,SUM（数量）房屋数量FROMView_QTDstGROUP BY季度，区县，街道，户型UNIONSELECT季度,区县,小计,SUM（数量）房屋数量FROM View_QTDstGROUP BY季度，区县UNIONSELECT 季度,合计,SUM（数量）房屋数量 FROM View_QTDstGROUP BY 季度二结果消息季度区县街道户型房屋数重11合计碗21朝阳区小i+43531朝阳区犬柵栏两至阳1 丁12141朝阳区大柵栏两至一厅11351朝阳区两至两厅10S61朝阳区两至一厅3271海淀区小计425&1海淀区万寿路两至的J 丁103&1海淀区万寿路两室一厅103101中关路两至阳1 丁111111海淀区两至一厅102-fl-|1(3查询已成功执丘5、心得体会写这个作业过程中让我对临时表有了新的认识.临时表在导航窗口中看不到. 只能在当天查询窗口中再次使用.关闭后，临时表便消失了 写随机插入函数时.对 自动插入数据有了了解写3-3按区县显示出租情况时.数据少时，按照正常显示. 数据一多.排序就不对了 在数据的多少上费了不少时间,考虑错方向了 原来在后 面加上order by就会解决排序问题但是数据少和数据多默认显示排列不同还是 不太清楚写4-3时合计在下面列子是在上面.弄了半天也没弄明白后来问了别人. 才发现合计前面加上空格排序就到上面了.真是要注意细节啊.