《数据库-我的租房网.》由会员分享，可在线阅读，更多相关《数据库-我的租房网.（12页珍藏版）》请在金锄头文库上搜索。
1、数据库技术与开发工程实训指导书数据库技术与开发项目名称：我的租房网姓 名：谭海兵学 号：1367159121专 业：软件工程项目实训内容1、实训一：建立数据库结构(1) 创建数据库House 使用SSMS向导创建数据库House，如下图所示：图1 创建数据库House扩展内容：要求用T-SQL语句建立house1数据库。CREATE DATABASE House ON PRIMARY(NAME=NHouse,FILENAME=Nd:sqlHouse.mdf,SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)LOG ON(NAME=NHouse_log,FILE
2、NAME=Nd: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 hos_district(DId INT IDENTITY(1,1) PRIMARY K
3、EY,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) PRIMARY KEY,HTName VARCHAR(50) NOT NULL,)US
4、E 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 DATETIME NOT NULL CONSTRAINT ck_htime CHECK(HTime=GE
5、TDATE() 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(StreetId)ALTER TABLE hos_houseADD CONSTRAINT FK_HTId F
6、OREIGN 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),(大栅栏,2) INSERT INTO hos_type(HTName) VALUES
7、(两室一厅),(两室两厅)(2) 添加批量数据创建3个临时表USE HouseCREATE 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) VALU
8、ES(万泉新新家园) 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) VALUE
9、S(望京朝阳东北区)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(购物
10、方便) 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(50)DECLARE num INTBEGIN TRANSACTIONSET num = 0WHILE
11、 num30BEGIN 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，要求小于当前系统时间，发布时间在当前系统时间一年内SET htime=
12、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 copy FROM #copy ORDER BY NEWID()INSERT INTO hos_house(User
13、Id,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条记录。SELECT TOP 10 * INTO #hos_house_top10 FROM hos_houseSELECT *
14、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 用户编号,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 房源
