数据库-我的租房网

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

12、sys_user su ON su.UserId=hh.UserIdINNER JOIN hos_type ht ON ht.HTId = hh.HTIdGROUP BY DATEPART(QUARTER,hh.HTime),hd.DName,hs.SName,ht.HTName(2) 统计出各个季度各个区县出租房屋的数量SELECT 季度,区县,SUM(数量) 房屋数量 FROM View_QTDstGROUP BY 季度,区县(3) 统计出各个季度各个区县出租房屋的数量总和及街道户型明细SELECT 季度,区县,街道,户型,SUM(数量) 房屋数量 FROM View_QTDstGROUP BY 季度,区县,街道,户型 UNIONSELECT 季度,区县,小计,SUM(数量) 房屋数量 FROM View_QTDstGROUP BY 季度,区县UNIONSELECT 季度,合计,SUM(数量) 房屋数量 FROM View_QTDstGROUP BY 季度5、心得体会写这个作业过程中.让我对临时表有了新的认识.临时表在导航窗口中看不到.只能在当天查询窗口中再次使用.关闭后,临时表便