數(shù)據(jù)庫(kù)-我的租房網(wǎng)

1、數(shù)據(jù)庫(kù)技術(shù)與開(kāi)發(fā)工程實(shí)訓(xùn)指導(dǎo)書(shū)數(shù)據(jù)庫(kù)技術(shù)與開(kāi)發(fā)項(xiàng)目名稱(chēng)：我的租房網(wǎng)姓 名：譚海兵學(xué) 號(hào)：1367159121專(zhuān) 業(yè)：軟件工程項(xiàng)目實(shí)訓(xùn)內(nèi)容1、實(shí)訓(xùn)一：建立數(shù)據(jù)庫(kù)結(jié)構(gòu)(1) 創(chuàng)建數(shù)據(jù)庫(kù)House 使用SSMS向?qū)?chuàng)建數(shù)據(jù)庫(kù)House，如下圖所示：圖1 創(chuàng)建數(shù)據(jù)庫(kù)House擴(kuò)展內(nèi)容：要求用T-SQL語(yǔ)句建立house1數(shù)據(jù)庫(kù)。CREATE DATABASE House ON PRIMARY(NAME=N'House',FILENAME=N'd:sqlHouse.mdf',SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)LOG ON(

2、NAME=N'House_log',FILENAME=N'd:sqlHouse_log.ldf',SIZE=1MB,MAXSIZE=20MB,FILEGROWTH=10%)(2) 建立5張數(shù)據(jù)表USE HouseCREATE TABLE sys_user(UserId INT IDENTITY(1,1) PRIMARY KEY,UserName varchar(50) NOT NULL,UserPwd VARCHAR(50),CONSTRAINT ck_userpwd CHECK(LEN(UserPwd)>=6)USE HouseCREATE TABLE

3、hos_district(DId INT IDENTITY(1,1) PRIMARY KEY,DName VARCHAR(50) NOT null)USE HouseCREATE TABLE hos_street(StreetId INT IDENTITY(1,1) PRIMARY KEY,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)

4、 PRIMARY KEY,HTName VARCHAR(50) NOT NULL,)USE HouseCREATE TABLE hos_house(HMId INT IDENTITY PRIMARY KEY,UserId INT NOT NULL,StreetId INT NOT NULL,HTId INT NOT NULL,Price DECIMAL(8,2) CONSTRAINT ck_price CHECK(Price>=0) DEFAULT(0),Topic varchar(50) NOT NULL,Contents VARCHAR(50) NOT NULL,HTime DATE

5、TIME 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(StreetId) REFERENCES hos_street(St

6、reetId)ALTER TABLE hos_houseADD CONSTRAINT FK_HTId FOREIGN KEY(HTId) REFERENCES hos_type(HTId)2、實(shí)訓(xùn)二：添加測(cè)試數(shù)據(jù)(1) 主表添加測(cè)試數(shù)據(jù)USE HouseINSERT INTO sys_user(UserName,UserPwd) VALUES('張三','000000'),('李四','000000')INSERT INTO hos_district(DName) VALUES('海淀區(qū)'),('朝陽(yáng)區(qū)&#

7、39;)INSERT INTO hos_street(SName,SDId) VALUES('萬(wàn)壽路',1),('中關(guān)村',1),('陶然亭',2),('大柵欄',2) INSERT INTO hos_type(HTName) VALUES('兩室一廳'),('兩室兩廳')(2) 添加批量數(shù)據(jù)創(chuàng)建3個(gè)臨時(shí)表USE HouseCREATE TABLE #Topic ( id INT IDENTITY(1,1), topic VARCHAR(50) ) CREATE TABLE #content ( i

8、d INT IDENTITY(1,1), content VARCHAR(50) ) CREATE TABLE #copy ( id INT IDENTITY(1,1), copy VARCHAR(50) )INSERT INTO #Topic(topic) VALUES('中關(guān)村') INSERT INTO #Topic(topic) VALUES('萬(wàn)泉新新家園') INSERT INTO #Topic(topic) values('望園小區(qū)') INSERT INTO #Topic(topic) VALUES('福盈家園')

9、 INSERT INTO #Topic(topic) VALUES('百子灣號(hào)院') INSERT INTO #Topic(topic) VALUES('中關(guān)村軟件園') INSERT INTO #content(content) VALUES('經(jīng)典裝修，拎包入住') INSERT INTO #content(content) VALUES('超值公寓火爆出租') INSERT INTO #content(content) VALUES('望京朝陽(yáng)東北區(qū)')INSERT INTO #content(content

10、) VALUES('低價(jià)個(gè)人入住') INSERT INTO #content(content) VALUES('昌平區(qū)回龍觀') INSERT INTO #content(content) VALUES('精裝修，首出租') INSERT INTO #copy(copy) VALUES('交通便利，配套完善') INSERT INTO #copy(copy) VALUES('環(huán)境優(yōu)雅，學(xué)區(qū)房') INSERT INTO #copy(copy) VALUES('緊挨號(hào)地鐵') INSERT INTO

11、 #copy(copy) VALUES('購(gòu)物方便') INSERT INTO #copy(copy) VALUES('緊鄰亞運(yùn)村') INSERT INTO #copy(copy) VALUES('山水一體')DECLARE userid INTDECLARE streetid INTDECLARE htid INTDECLARE price DECIMALDECLARE htime DATETIMEDECLARE topic VARCHAR(50)DECLARE contents VARCHAR(50)DECLARE copy VARCHAR

12、(50)DECLARE num INTBEGIN TRANSACTIONSET num = 0WHILE num<30BEGIN SET 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之間隨機(jī)產(chǎn)生SET price=1000+CAST(3000*RAND

13、() AS INT)-發(fā)布時(shí)間htime，要求小于當(dāng)前系統(tǒng)時(shí)間，發(fā)布時(shí)間在當(dāng)前系統(tǒng)時(shí)間一年內(nèi)SET htime=CAST(DATEADD(DAY,-CAST(RAND()*DATEPART(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 co

14、py 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+1ENDCOMMIT TRANSACTION3、實(shí)訓(xùn)三：綜合查詢(xún)(1) 分頁(yè)顯示查詢(xún)出租房屋信息查詢(xún)輸出第6-10條出租房屋信息：Ø 使用Top關(guān)鍵字實(shí)現(xiàn)查詢(xún)分頁(yè)顯示，用臨時(shí)表先取出前10條記錄，然后再在臨時(shí)表里取出第6-第10條記錄

15、。SELECT TOP 10 * INTO #hos_house_top10 FROM hos_houseSELECT * FROM #hos_house_top10 WHERE HMId NOT IN(SELECT TOP 5 HMId FROM #hos_house_top10)Ø 使用RowNumber函數(shù)，要求所有的列標(biāo)題使用中文，查詢(xún)結(jié)果如下圖所示：SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY HMId) AS rowid,* INTO #temp FROM hos_houseSELECT HMId AS 房源編號(hào),UserId AS 用

16、戶(hù)編號(hào),StreetID AS 街道編號(hào),Topic AS 標(biāo)題,Contents AS 房源描述,Price AS 月租,HTime AS 發(fā)布時(shí)間,Copy AS 備注 FROM #temp WHERE rowid BETWEEN 6 AND 10(2) 查詢(xún)指定客戶(hù)發(fā)布的出租房屋信息 查詢(xún)張三發(fā)布的所有出租房屋信息，并顯示房屋分布的街道和區(qū)縣，要求分別按下述2種方法實(shí)現(xiàn)圖7的查詢(xún)結(jié)果：Ø 使用內(nèi)聯(lián)接inner join查詢(xún)和子查詢(xún)實(shí)現(xiàn)。SELECT hd.DName '區(qū)縣',hs.SName '街道',ht.HTName '房屋類(lèi)型&#

17、39;,hh.Topic '標(biāo)題',hh.Price '價(jià)格',hh.Contents '房源描述',hh.HTime '時(shí)間',hh.Copy '備注' FROM hos_house hh INNER 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 O

18、N ht.HTId = hh.HTIdWHERE su.UserName='張三'Ø 使用where子句和內(nèi)查詢(xún)實(shí)現(xiàn)。SELECT hd.DName '區(qū)縣',hs.SName '街道',ht.HTName '房屋類(lèi)型',hh.Topic '標(biāo)題',hh.Price '價(jià)格',hh.Contents '房源描述',hh.HTime '時(shí)間',hh.Copy '備注' FROM hos_house hh,hos_street hs,hos_di

19、strict hd,sys_user su,hos_type ht WHERE hh.StreetId=hs.StreetId AND hs.SDId=hd.DId AND su.UserId=hh.UserId AND ht.HTId = hh.HTId AND su.UserName='張三'(3) 按區(qū)縣制作房屋出租清單SELECT ht.HTName '戶(hù)型',su.UserName '姓名',hd.DName '區(qū)縣',hs.SName '街道'FROM hos_house hh INNER JOIN h

20、os_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 hh INNER JOIN hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON

21、 hs.SDId=hd.DId GROUP BY hd.DId HAVING COUNT(*)>1) ORDER BY hs.StreetId4、實(shí)訓(xùn)四：業(yè)務(wù)統(tǒng)計(jì)(1) 按季度統(tǒng)計(jì)本年度發(fā)布的房屋出租數(shù)量CREATE VIEW View_QTDst AS SELECT DATEPART(QUARTER,hh.HTime) '季度',hd.DName '區(qū)縣',hs.SName '街道',ht.HTName '戶(hù)型',COUNT(hh.HMId) '數(shù)量' FROM hos_house hh INNER 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.UserIdINNE