《我的租房网》实训报告

1、数据库技术与开发工程实训指导书数据库技术与开发项目实训设计报告项目名称：我的租房网姓 名：贺英杰专 业：软件工程指导教师：余金玲完成日期：2015-11-151、 项目背景1、项目任务 T-SQL编程创建事务产生随机测试数据 实现各种业务查询功能2、项目技能目标 使用临时表保存临时查询结果 使用子查询、联接查询、联合查询 使用事务和视图 使用T-SQL编程批量插入测试数据3、需求概述项目组接受开发“我的租房网”软件任务，现在项目组接受数据库设计工作，“我的租房网”数据库House包括客户信息表、区县信息表、街道信息表、房屋类型表和出租房屋信息表共5个表，各表结构如下表1-5所示：表1 客户信息

2、表sys_user结构列名称数据类型说明UserIdint客户编号，主键，标识列从1开始，递增值为1UserNamevarchar客户姓名，该栏必填UserPwdvarchar密码，至少6个字符表2 区县信息表hos_district结构列名称数据类型说明DIdint区县编号，主键，标识列从1开始，递增值为1DNamevarchar区县名称，该栏必填表3 街道信息表hos_street结构列名称数据类型说明StreetIdint街道编号，主键，标识列从1开始，递增值为1SNamevarchar街道名称，该栏必填SDIdvarchar区县编号，表hos_district的外键表4 房屋类型表ho

3、s_type结构列名称数据类型说明HTIdint房屋类型编号，主键，标识列从1开始，递增值为1HTNamevarchar房屋类型名称，该栏必填表5 出租房屋信息表hos_house结构列名称数据类型说明HMIDint出租房屋编号，主键，标识列从1开始，递增值为1UserIdvarchar客户编号，该栏必填，外键StreetIDint街道编号，该栏必填，外键HTIdint房屋类型编号，该栏必填，外键Pricedecimal月租金，该栏必填，默认值为0，要求大于等于0Topicvarchar标题，该栏必填Contentsvarchar描述，该栏必填HTimedatetime发布时间，该栏必填，默认

4、值为当前日期，要求不大于当前日期Copyvarchar备注4、开发环境 数据库：SQL SERVER 2008开发版5、实训进度安排实训进度安排如下表所示：表6 实训进度安排实训内容所需学时提交文档实训一：建立数据库结构4T-SQL源文件实训二：添加测试数据6T-SQL源文件实训三：综合查询6T-SQL源文件实训四：业务统计8T-SQL源文件6、成绩考核 实训项目最终成绩由3部分构成：平日考勤20%+实训文档30%+实训项目完成质量50%， 凡是报优的同学需参加公开答辩，讲解答辩10-15分钟。 凡实训项目雷同，或者考勤不合格直接视同不及格。2、 项目实训内容实训内容由4个实训项目构成，建议在

5、参考代码和实现步骤基础上进行改进，每个实训子项目的T-SQL语句写成1个T-SQL源文件，如item1.sql。1、实训一：建立数据库结构(1) 创建数据库House 根据项目要求，使用T-SQL语句创建数据库House，代码如下：(2) 建立5张数据表使用T-SQL语句建立5张数据表及相应的各种约束，代码如下：根据表7的结构创建sys_user表-（1）创建数据库Housecreate database House on primary(name=House_db,filename=D:DocumentPrivateMicrosoft SQL ServerMicrosoft SQL Serv

6、er DataBaseHouse_db.mdf,size=10MB,maxsize=50Mb,filegrowth=10%)log on(name=House_Log,filename=D:DocumentPrivateMicrosoft SQL ServerMicrosoft SQL Server DataBaseHouse_Log.ldf,size=10MB,maxsize=50Mb,filegrowth=10%)go表7 客户信息表sys_user结构列名称数据类型说明UserIdint客户编号，主键，标识列从1开始，递增值为1UserNamevarchar客户姓名，该栏必填UserPw

7、dvarchar密码，至少6个字符use House-创建客户信息表sys_usercreate table sys_user(-客户编号，主键，标识列从1开始，递增值为1UserId int primary key identity(1,1),-客户姓名，非空UserName varchar(50) not null,-密码，非空UserPwd varchar(50) not null,-添加对UserPwd字段的check约束：至少6个字符constraint Ck_UserPwd check(len(UserPwd)=6);根据表8的结构创建hos_district表表8 区县信息表ho

8、s_district结构列名称数据类型说明DIdint区县编号，主键，标识列从1开始，递增值为1DNamevarchar区县名称，该栏必填-创建区县信息表hos_districtcreate table hos_district(-区县编号，主键，标识列从1开始，递增值为1DId int primary key identity(1,1),-区县名称，非空DName varchar(50) not null);根据表9的结构创建hos_street表表9 街道信息表hos_street结构列名称数据类型说明StreetIdint街道编号，主键，标识列从1开始，递增值为1SNamevarchar

9、街道名称，该栏必填SDIdvarchar区县编号，表hos_district的外键-创建街道信息表hos_streetcreate table hos_street(-街道编号，主键，标识列从1开始，递增值为1StreetId int primary key identity(1,1),-街道名称，非空SName varchar(50) not null,-区县编号，表hos_district的外键SDId Int,-为字段hos_district添加外键约束constraint FK_区县编号 foreign key(SDId) references hos_district(DId);根据

10、表10的结构创建hos_type表表10 房屋类型表hos_type结构列名称数据类型说明HTIdint房屋类型编号，主键，标识列从1开始，递增值为1HTNamevarchar房屋类型名称，该栏必填-创建房屋类型表hos_typecreate table hos_type(-房屋类型编号，主键，标识列从1开始，递增值为1HTId int primary key identity(1,1),-房屋类型名称，非空HTName varchar(50) not null,);根据表11的结构创建hos_house表表11 出租房屋信息表hos_house结构列名称数据类型说明HMIDint出租房屋编号

11、，主键，标识列从1开始，递增值为1UserIdvarchar客户编号，该栏必填，外键StreetIDint街道编号，该栏必填，外键HTIdint房屋类型编号，该栏必填，外键Pricedecimal月租金，该栏必填，默认值为0，要求大于等于0Topicvarchar标题，该栏必填Contentsvarchar描述，该栏必填HTimedatetime发布时间，该栏必填，默认值为当前日期，要求不大于当前日期Copyvarchar备注-创建出租房屋信息表hos_housecreate table hos_house(-出租房屋编号，主键，标识列从1开始，递增值为1HMID int primary ke

12、y identity(1,1),-客户编号，非空，外键UserId int not null,-街道编号，非空，外键StreetID int not null,-房屋类型编号，非空，外键HTId int not null,-月租金，非空，默认值为0，要求大于等于0Price decimal default 0 not null,-标题，非空Topic varchar(150) not null,-描述，非空Contents varchar(255) not null,-发布时间，非空，默认值为当前日期，要求不大于当前日期HTime datetime default getdate(),-备注C

13、opy varchar(150),-为字段HTime添加检查约束，要求不大于当前日期constraint Ck_HTime check(DATEDIFF(d,(HTime),getdate()=0);(3) 添加外键约束使用Alter Table语句为hos_house表建立外键关系。-为字段UserId创建外键约束alter table hos_houseadd constraint FK_客户编号 foreign key(UserId) references sys_user(UserId);-为字段StreetID创建外键约束alter table hos_houseadd constr

14、aint FK_街道编号 foreign key(StreetID) references hos_street(StreetID);-为字段HTId创建外键约束alter table hos_houseadd constraint FK_房屋类新编号 foreign key(HTId) references hos_type(HTId);2、实训二：添加测试数据(1) 主表添加测试数据用T-SQL语句向客户信息表、区县信息表各添加6条测试数据，街道信息表里每个区县添加1-3个街道，房屋类型表添加3条。-向客户信息表添加测试数据：use House;goinsert into sys_user

15、(UserName,UserPwd)values(贺英杰,000000),(常乐,111111),(龚佳黎,222222),(兰波波,333333),(黄亚坤,444444),(徐云辉,555555);-向区县信息表添加测试数据：insert into hos_district(DName)values(回民区),(新城区),(赛罕区),(玉泉区),(金川开发区),(如意开发区);-向街道信息表添加测试数据：insert into hos_street(SName,SDId)values(牛街,1),(南马路,1),(北垣街,2),(中专路,3),(爱民街,2),(地质局南街,3),(光明大街

16、,1),(海东路,2),(大学路,3),(大南街,4),(东二环,5);-向房屋类型表添加测试数据：insert into hos_type(HTName)values(两室一厅),(两室两厅),(三室两厅);-遍历四张表select * from sys_user;select * from hos_district;select * from hos_street;select * from hos_type;4个主表的测试数据如下图所示：图1 4个主表的测试数据(2) 添加批量数据 使用事务和While循环，向出租房屋表hos_house增加10000条记录（各个字段的内容随机产生），如

17、下图所示：-（2）添加批量数据use House;go-创建临时表Topiccreate table #Topic(id int primary key identity(1,1),topic varchar(150) not null);-为临时表Topic添加数据insert into #Topic(topic)values(什拉门更村),(王泉新新家园),(望园小区),(福盈家园),(未来城),(百度城);-创建临时表Contentcreate table #Content(id int primary key identity(1,1),content varchar(255) not

18、 null);-为临时表Content添加数据insert into #Content(content)values(经典装修，拎包入住),(超值公寓火爆出租),(望京朝阳东北区),(低价个人住宅),(昌平区回龙观),(精装修，首出租);-创建临时表Copycreate table #Copy(id int primary key identity(1,1),copy varchar(150) not null);-为临时表Copy添加数据insert into #Copy(copy)values(交通便利,配套完善),(环境优雅,学区房),(紧挨六号地铁),(购物便利),(紧邻亚运村),(山

19、水一体);-批量添加数据declare userID int;-客户编号declare streetID int;-街道编号declare htID int;-房屋类别编号declare price int;-租金declare htime datetime;-发布时间declare topic varchar(150);-标题declare content varchar(255);-描述declare copy varchar(150);-备注declare count int;-循环加一的变量select count=0;while count=0);-给临时表hos_house插入数据

20、前十条数据insert into #LinShiChuzu(UserId,StreetID,HTId,Price,Topic,Contents,HTime,Copy)select top 10 UserId,StreetID,HTId,Price,Topic,Contents,HTime,Copy from hos_house ;select top 5 * from #LinShiChuzu where HMID not in (select top 5 HMID from #LinShiChuzu);图3 使用Top关键字和临时表分页显示 使用RowNumber函数，要求所有的列标题使用中

21、文，查询结果如下图所示：-使用 RowNumber 函数实现分页显示select top 40 ROW_NUMBER() over (order by HMID asc) as rownumber, HMID 房源编号, UserId 用户编号, StreetID 街道编号, Topic 标题, Contents 房源描述,Price 月租, HTime 发布时间, Copy 备注 from hos_house;图4 使用RowNumber函数实现分页显示(2) 查询指定客户发布的出租房屋信息 查询贺英杰发布的所有出租房屋信息，并显示房屋分布的街道和区县，查询结果如图5： 使用内联接inner

22、 join查询和子查询通过各个表的主外键实现查询。-使用内联接 inner join 查询select hos_district.DName 区县,hos_street.SName 街道,hos_type.HTName 户型,hos_house.Topic 标题,hos_house.Price 价格,hos_house.Contents 描述,hos_house.HTime 发布时间,hos_house.Copy 备注from sys_user inner join hos_house on sys_user.UserId=hos_house.UserId inner join hos_str

23、eet on hos_house.StreetID=hos_street.StreetId inner join hos_type on hos_house.HTId=hos_type.HTId inner join hos_district on hos_district.DId=hos_street.SDIdwhere sys_user.UserName=贺英杰; 使用where子句和内查询实现。-使用子查询实现select hos_district.DName 区县,hos_street.SName 街道,hos_type.HTName 户型,T.Topic 标题,T.Price 价格,

24、T.Contents 描述,T.HTime 发布时间,T.Copy 备注from (select * from hos_house where hos_house.UserId in (select UserId from sys_user where UserName=贺英杰) T,hos_district,hos_street,hos_typewhere T.StreetID=hos_street.StreetId and T.HTId=hos_type.HTId and hos_district.DId=hos_street.SDId图5 查询贺英杰发布的所有出租房屋信息(3) 按区县制

25、作房屋出租清单通过表连接为至少有2个街道有出租房屋的区县制作出租房屋清单，使用having子句筛选出街道数量大于1的区县，后按区县编号、街道名称排序。查询结果如图6。-（3）按区县制作房屋出租清单select hos_type.HTName,sys_user.UserName,hos_district.DName,hos_street.SName from sys_user inner join hos_house on sys_user.UserId=hos_house.UserId inner join hos_street on hos_house.StreetID=hos_street

26、.StreetId inner join hos_type on hos_house.HTId=hos_type.HTId inner join hos_district on hos_district.DId=hos_street.SDIdwhere hos_district.DId in(select SDId from hos_street where SDId in( select StreetID from hos_house group by StreetID)group by SDIdhaving(count(*)1)order by SDId,hos_street.SName

27、图6 按区县制作房屋出租清单4、实训四：业务统计(1) 按季度统计本年度发布的房屋出租数量创建视图View_QTDst本年各个季度各区县各街道销售的各房型出租数量，运用函数DATENAME中的QUARTER直接返回季度，通过表连接进行查询。结果如图7所示：-（1）按季度统计本年度发布的房屋出租数量-创建视图View_QTDstcreate view View_QTDst as select DATENAME(QUARTER,hos_house.HTime) 季度, hos_district.DName 区县,hos_street.SName 街道,hos_type.HTName 户型,COUN

28、T(*) as 数量from sys_user inner join hos_house on sys_user.UserId=hos_house.UserId inner join hos_street on hos_house.StreetID=hos_street.StreetId inner join hos_type on hos_house.HTId=hos_type.HTId inner join hos_district on hos_district.DId=hos_street.SDIdgroup by DATENAME(QUARTER,hos_house.HTime),hos_district.DName,hos_street.SName,hos_type.HTNamegoselect * from View_QTDst order by 季度,区县,街道,户型,数量 图7 视图View_QTDst查询结果(2) 统计出各个季度各个区县出租房屋的数量在视图View_QTDst上进行汇总统计，结果如图8所示：-（2）统计出各个