数据库-我的租房网

1、数据库技术与开发工程实训指导书数据库技术与开发项目名称：我的租房网姓 名：谭海兵学 号：1367159121专 业：软件工程项目实训内容1、实训一：建立数据库结构(1) 创建数据库House 使用SSMS向导创建数据库House，如下图所示：图1 创建数据库House扩展内容：要求用T-SQL语句建立house1数据库。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张数据表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、实训二：添加测试数据(1) 主表添加测试数据USE HouseINSERT INTO sys_user(UserName,UserPwd) VALUES('张三','000000'),('李四','000000')INSERT INTO hos_district(DName) VALUES('海淀区'),('朝阳区&#

7、39;)INSERT INTO hos_street(SName,SDId) VALUES('万寿路',1),('中关村',1),('陶然亭',2),('大栅栏',2) INSERT INTO hos_type(HTName) VALUES('两室一厅'),('两室两厅')(2) 添加批量数据创建3个临时表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('中关村') INSERT INTO #Topic(topic) VALUES('万泉新新家园') INSERT INTO #Topic(topic) values('望园小区') INSERT INTO #Topic(topic) VALUES('福盈家园')

9、 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

10、) 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

11、 #copy(copy) VALUES('购物方便') INSERT INTO #copy(copy) VALUES('紧邻亚运村') 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之间随机产生SET price=1000+CAST(3000*RAND

13、() AS INT)-发布时间htime，要求小于当前系统时间，发布时间在当前系统时间一年内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、实训三：综合查询(1) 分页显示查询出租房屋信息查询输出第6-10条出租房屋信息：Ø 使用Top关键字实现查询分页显示，用临时表先取出前10条记录，然后再在临时表里取出第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函数，要求所有的列标题使用中文，查询结果如下图所示：SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY HMId) AS rowid,* INTO #temp FROM hos_houseSELECT HMId AS 房源编号,UserId AS 用

16、户编号,StreetID AS 街道编号,Topic AS 标题,Contents AS 房源描述,Price AS 月租,HTime AS 发布时间,Copy AS 备注 FROM #temp WHERE rowid BETWEEN 6 AND 10(2) 查询指定客户发布的出租房屋信息 查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县，要求分别按下述2种方法实现图7的查询结果：Ø 使用内联接inner join查询和子查询实现。SELECT hd.DName '区县',hs.SName '街道',ht.HTName '房屋类型&#

17、39;,hh.Topic '标题',hh.Price '价格',hh.Contents '房源描述',hh.HTime '时间',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子句和内查询实现。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_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) 按区县制作房屋出租清单SELECT ht.HTName '户型',su.UserName '姓名',hd.DName '区县',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、实训四：业务统计(1) 按季度统计本年度发布的房屋出租数量CREATE VIEW View_QTDst AS SELECT DATEPART(QUARTER,hh.HTime) '季度',hd.DName '区县',hs.SName '街道',ht.HTName '户型',COUNT(hh.HMId) '数量' 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