数据库-我的租房网

《数据库技术与开发》工程实训指导书

《数据库技术与开发》

项目名称：我的租房网姓 名：谭海兵学 号：专 业：软件工程

1367159121

1

《数据库技术与开发》工程实训指导书

项目实训内容

1、实训一：建立数据库结构

(1) 创建数据库House

使用SSMS向导创建数据库House，如下图所示：

图1．创建数据库House

扩展内容：要求用T-SQL语句建立house1数据库。

CREATE DATABASE House ON PRIMARY

(NAME=N'House',FILENAME=N'd:\\sql\\House.mdf',SIZE=5MB,MAXSIZE=UNLIMITED, FILEGROWTH=1MB) LOG ON

(NAME=N'House_log',FILENAME=N'd:\\sql\\House_log.ldf',SIZE=1MB,MAXSIZE=20MB, FILEGROWTH=10%)

(2) 建立5张数据表

2

《数据库技术与开发》工程实训指导书

USE House

CREATE 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 House

CREATE TABLE hos_district (

DId INT IDENTITY(1,1) PRIMARY KEY, DName VARCHAR(50) NOT null )

USE House

CREATE 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 House

CREATE TABLE hos_type (

HTId INT IDENTITY(1,1) PRIMARY KEY, HTName VARCHAR(50) NOT NULL, )

USE House

CREATE 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,

3

《数据库技术与开发》工程实训指导书

HTime DATETIME NOT NULL CONSTRAINT ck_htime CHECK(HTime<=GETDATE()) DEFAULT(GETDATE()), Copy VARCHAR(50) )

(3) 添加外键约束

USE House

ALTER TABLE hos_house

ADD CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES sys_user(UserId) ALTER TABLE hos_house

ADD CONSTRAINT FK_StreetId FOREIGN KEY(StreetId) REFERENCES hos_street(StreetId) ALTER TABLE hos_house

ADD CONSTRAINT FK_HTId FOREIGN KEY(HTId) REFERENCES hos_type(HTId)

2、实训二：添加测试数据

(1) 主表添加测试数据

USE House

INSERT INTO sys_user(UserName,UserPwd) VALUES('张三','000000'),('李四','000000') INSERT INTO hos_district(DName) VALUES('海淀区'),('朝阳区')

INSERT INTO hos_street(SName,SDId) VALUES('万寿路',1),('中关村',1),('陶然亭',2),('大栅栏',2) INSERT INTO hos_type(HTName) VALUES('两室一厅'),('两室两厅')

4

《数据库技术与开发》工程实训指导书

(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('山水一体')

5

《数据库技术与开发》工程实训指导书

DECLARE @userid INT DECLARE @streetid INT DECLARE @htid INT DECLARE @price DECIMAL DECLARE @htime DATETIME DECLARE @topic VARCHAR(50) DECLARE @contents VARCHAR(50) DECLARE @copy VARCHAR(50) DECLARE @num INT BEGIN TRANSACTION SET @num = 0 WHILE @num<30 BEGIN

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() AS INT)

--发布时间@htime，要求小于当前系统时间，发布时间在当前系统时间一年内

6

《数据库技术与开发》工程实训指导书

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 @topic

SET @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+1 END

COMMIT TRANSACTION

3、实训三：综合查询

(1) 分页显示查询出租房屋信息 查询输出第6-10条出租房屋信息：

? 使用Top关键字实现查询分页显示，用临时表先取出前10条记录，然后再

在临时表里取出第6-第10条记录。

SELECT TOP 10 * INTO #hos_house_top10 FROM hos_house

SELECT * FROM #hos_house_top10 WHERE HMId NOT IN(SELECT TOP 5 HMId FROM #hos_house_top10)

7

《数据库技术与开发》工程实训指导书

? 使用RowNumber函数，要求所有的列标题使用中文，查询结果如下图所示：

SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY HMId) AS rowid,* INTO #temp FROM hos_house

SELECT HMId AS 房源编号,UserId AS 用户编号,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 '房屋类型',hh.Topic '标题', hh.Price '价格',hh.Contents '房源描述',hh.HTime '时间',hh.Copy '备注' FROM hos_house hh

INNER JOIN hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId INNER JOIN sys_user su ON su.UserId=hh.UserId INNER JOIN hos_type ht ON ht.HTId = hh.HTId WHERE su.UserName='张三'

8

《数据库技术与开发》工程实训指导书

? 使用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 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 hos_street hs ON hh.StreetId=hs.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId INNER JOIN sys_user su ON su.UserId=hh.UserId INNER JOIN hos_type ht ON ht.HTId = hh.HTId

WHERE 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 hs.SDId=hd.DId GROUP BY hd.DId HAVING COUNT(*)>1)

ORDER BY hs.StreetId

9

《数据库技术与开发》工程实训指导书

4、实训四：业务统计

(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.StreetId INNER JOIN hos_district hd ON hs.SDId=hd.DId INNER JOIN sys_user su ON su.UserId=hh.UserId INNER JOIN hos_type ht ON ht.HTId = hh.HTId

GROUP BY DATEPART(QUARTER,hh.HTime),hd.DName,hs.SName,ht.HTName

10

《数据库技术与开发》工程实训指导书

(2) 统计出各个季度各个区县出租房屋的数量

SELECT 季度,区县,SUM(数量) 房屋数量 FROM View_QTDst GROUP BY 季度,区县

(3) 统计出各个季度各个区县出租房屋的数量总和及街道户型明细

SELECT 季度,区县,街道,户型,SUM(数量) 房屋数量 FROM View_QTDst GROUP BY 季度,区县,街道,户型 UNION

SELECT 季度,区县,'小计','',SUM(数量) 房屋数量 FROM View_QTDst GROUP BY 季度,区县 UNION

SELECT 季度,'合计','','',SUM(数量) 房屋数量 FROM View_QTDst GROUP BY 季度

11

《数据库技术与开发》工程实训指导书

5、心得体会

写这个作业过程中.让我对临时表有了新的认识.临时表在导航窗口中看不到.只能在当天查询窗口中再次使用.关闭后,临时表便消失了.写随机插入函数时.对自动插入数据有了了解.写3-3按区县显示出租情况时.数据少时,按照正常显示.数据一多.排序就不对了.在数据的多少上费了不少时间,考虑错方向了.原来在后面加上order by就会解决排序问题.但是数据少和数据多默认显示排列不同.还是不太清楚.写4-3时合计在下面.列子是在上面.弄了半天也没弄明白.后来问了别人.才发现合计前面加上空格.排序就到上面了.真是要注意细节啊.

12

本文来源：https://www.bwwdw.com/article/pe63.html