第五章 租房网 完整版

USE MASTER IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='HOUSE')DROP DATABASE HOUSEGO----创建数据库HOUSECREATE DATABASE HOUSEON(NAME='HOUSE_DATA',FILENAME='E:\HOUSE_DATA.MDF',SIZE=5,FILEGROWTH=1)LOG ON(NAME='HOUSE_LOG',FILENAME='E:\HOUSE.LDF',SIZE=10,FILEGROWTH=10%)GO/*------创建表-----------*/--------建表:sys_user(用户信息表)USE HOUSEGOIF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='sys_user')DROP TABLE sys_userGOCREATE TABLE sys_user(UID INT IDENTITY (1,1) NOT NULL,---用户编号UNAME VARCHAR(20) NOT NULL ,---姓名UPASSWORD VARCHAR(20) NOT NULL---密码)GO------建表sys_user的约束ALTER TABLE sys_userADD CONSTRAINT PK_UID PRIMARY KEY (UID),    CONSTRAINT CK_UPASSWORD CHECK(LEN(UPASSWORD)>6)GO-----建表hos_districtIF EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME='hos_district')DROP TABLE hos_district GOCREATE TABLE hos_district(DID INT IDENTITY (1,1) PRIMARY KEY NOT NULL ,---区县编号(主键)DNAME VARCHAR(20) NOT NULL ----区县名称)GO----建表hos_streetIF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME='hos_street')DROP TABLE hos_streetGOCREATE TABLE hos_street(SID INT IDENTITY(1,1) PRIMARY KEY  NOT NULL,---街道编号(主键)SNAME VARCHAR(100) NOT NULL,---街道名称SDID INT FOREIGN KEY (SDID)  REFERENCES hos_district (DID) NOT NULL )GO-----建表hos_typeIF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME='hos_type')DROP TABLE hos_typeGOCREATE TABLE hos_type(HTID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,----房屋类型编号(主键)HTNAME VARCHAR(20) NOT NULL ---房屋类型)GO-----建表：hos_houseIF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='hos_house')DROP TABLE hos_houseGOCREATE TABLE hos_house(HMID INT IDENTITY(1,1) NOT NULL,---(房屋信息编号)UID INT NOT NULL,----用户编号--DID INT NOT NULL,----区县编号SID INT NOT NULL,----街道编号HTID INT NOT NULL,---房屋类型编号PRICE  DECIMAL(18,2) NOT NULL,--价格TOPIC VARCHAR(20) NOT NULL,---标题CONTENTS VARCHAR(100) NOT NULL,---描述HTIME DATETIME NOT NULL,---发布时间COPY VARCHAR(20) ---备注)GO-----添加约束(house)ALTER TABLE hos_houseADD CONSTRAINT PK_HMID PRIMARY KEY (HMID),    CONSTRAINT DF_PRICE DEFAULT(0) FOR PRICE,    CONSTRAINT CK_PRICE CHECK(PRICE>0),    CONSTRAINT DF_HTIME DEFAULT(GETDATE()) FOR HTIME,    CONSTRAINT CK_HTIME CHECK(HTIME<=GETDATE()),    CONSTRAINT FK_UID FOREIGN KEY (UID) REFERENCES sys_user(UID) ,   -- CONSTRAINT FK_DID FOREIGN KEY (DID) REFERENCES hos_district(DID),    CONSTRAINT FK_SID FOREIGN KEY (SID) REFERENCES hos_street (SID),    CONSTRAINT FK_HTID FOREIGN KEY(HTID) REFERENCES hos_type(HTID)GO--------添加测试数据-----sys_user插入数据INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('张三','s217701')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('李四','s217702')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('王鑫','s217703')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('张建','s217704')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('李剑','s217705')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('蒋以然','s217706')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('王晓超','s217707')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('张冬雪','s217708')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('孙鹏','s217709')INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('蒋连昌','s217710')GO-----表hos_district插入数据INSERT INTO hos_district (DNAME) VALUES('海淀区')INSERT INTO hos_district (DNAME) VALUES('西城区')INSERT INTO hos_district (DNAME) VALUES('东城区')INSERT INTO hos_district (DNAME) VALUES('朝阳区')INSERT INTO hos_district (DNAME) VALUES('景山区')INSERT INTO hos_district (DNAME) VALUES('宣武区')INSERT INTO hos_district (DNAME) VALUES('大兴')INSERT INTO hos_district (DNAME) VALUES('丰台')GO----表hos_street插入数据INSERT INTO hos_street (SNAME,SDID) VALUES('中关村',1)INSERT INTO hos_street (SNAME,SDID) VALUES('苏州街',1)INSERT INTO hos_street (SNAME,SDID) VALUES('万泉庄',1)INSERT INTO hos_street (SNAME,SDID) VALUES('东四',3)INSERT INTO hos_street (SNAME,SDID) VALUES('东单',3)INSERT INTO hos_street (SNAME,SDID) VALUES('西四',2)INSERT INTO hos_street (SNAME,SDID) VALUES('西单',2)INSERT INTO hos_street (SNAME,SDID) VALUES('东湖',4)INSERT INTO hos_street (SNAME,SDID) VALUES('八里庄',4)INSERT INTO hos_street (SNAME,SDID) VALUES('双井',5)INSERT INTO hos_street (SNAME,SDID) VALUES('陶然亭',5)INSERT INTO hos_street (SNAME,SDID) VALUES('南菜园',6)INSERT INTO hos_street (SNAME,SDID) VALUES('兴丰街',7)INSERT INTO hos_street (SNAME,SDID) VALUES('黄村',7)INSERT INTO hos_street (SNAME,SDID) VALUES('南苑街',8)INSERT INTO hos_street (SNAME,SDID) VALUES('东铁营',8)GO-----表hos_type插入数据INSERT INTO hos_type (HTNAME) VALUES('一室一卫')INSERT INTO hos_type (HTNAME) VALUES('一室一厅')INSERT INTO hos_type (HTNAME) VALUES('两室一卫')INSERT INTO hos_type (HTNAME) VALUES('两室一厅')INSERT INTO hos_type (HTNAME) VALUES('三室一厅')INSERT INTO hos_type (HTNAME) VALUES('三室两厅')GO----表hos_house插入数据INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(1,1,2,2600,'中关村','中关村一条街','2009-1-2','中关村')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(2,2,3,3600,'苏州街','苏州街一条街','2009-1-3','苏州街')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(3,3,4,4600,'万泉庄','万泉庄一条街','2009-1-4','万泉庄')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(1,3,2,1500,'万泉庄附近','万泉庄附近一条街','2009-7-2','万泉庄附近')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(1,5,2,2700,'东单','东单很多美食','2009-9-2','东单')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(3,1,2,2600,'中关村','中关村电脑城','2009-4-1','中关村')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(4,4,1,2000,'东四','东四一条街','2009-4-2','东四')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(5,6,3,3600,'西四','西四一条街','2009-1-2','西四')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(5,7,2,3600,'西单','西单购物城','2009-4-2','西单')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(6,2,2,2600,'苏州街','苏州街美食','2009-2-2','苏州街')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(7,8,3,2900,'朝阳东湖','朝阳东湖一景','2009-3-2','朝阳')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(8,3,1,700,'万泉庄','万泉庄一条街','2009-5-2','万泉庄')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(3,2,3,4200,'苏州街','苏州街二条街','2009-1-3','苏州街')INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY)VALUES(4,2,3,4100,'苏州街','苏州街西街','2009-1-3','苏州街')GO---------------------------------------------------------------------------------------------------use HOUSEselect * from hos_house  --房间信息表select * from hos_district  --区县表select * from hos_street    --街道表select * from hos_type    --房间类型表--1.内容扩充：临时表--如果表名前面有#，那么创建的表是临时表，位于tempdb数据库下--的临时表文件夹下。create table #mytemp2222(  id int identity(1,1) primary key not null,  name nvarchar(20))  --使用临时表的好处：临时表中的数据当我们断开连接后自动释放，这样  --可以节省数据库服务器的空间--任务一：分页显示查询出租房信息--经典分页思路(一、双top，双order by 二、Row_number()函数  三、临时表方式(和Row_Number()原理一致))--将出租房屋记录批量插入临时表--*******************方式一：双top，双order by分页************************--查询输出第6条~第10条房屋出租信息--did:区县编号(hos_district)select top 5 *,hos_district.didfrom hos_house,hos_street,hos_districtwhere  hmidnot in(select top 5 hmid from hos_house)and hos_house.sid=hos_street.sidand hos_street.sdid=hos_district.did--*******************方式二：Row_Number() over(order by id)***************select * from (select *,row_number() over(order by hmid) as myidfrom hos_house) as tempwhere myid between 6 and 10--*******************方式三：临时表分页***********************************--查询输出第6条~第10条房屋出租信息use houseselect identity(int,1,1) as myid, UID, SID, HTID, PRICE, TOPIC, CONTENTS, HTIME, COPYinto #housetempfrom hos_house--通过临时表我们发现，其实实现分页只有两中思路，一种是通过双top，双order by方式，--另外一种方式就是用一定的手段(可以使系统提供Row_NUmber()函数，也--可以通过identity(int,1,1)来产生)，在现有表上加上一个自动增长列。select * from #housetempwhere myid between 6 and 10--如何将临时表释放drop table #housetemp--练习2——查询指定客户发布的出租房屋信息--需求说明::--查询“张三”发布的所有出租房屋信息，并显示房屋分布的街道、区县select DNAME as 区县,SNAME as 街道,HTNAME as 户型,price as 价格,topic as 标题,contents as 描述,htime as 时间,copy as 备注from hos_district,hos_street,hos_type,hos_housewhere hos_house.sid=hos_street.sidand hos_house.htid=hos_type.htidand hos_street.sdid=hos_district.didand uid=(  select uid from sys_user  where uname='张三')--阶段3：练习——按区县制作房屋出租清单--根据户型和房屋所在区县和街道,为至少有2个街道有房屋出租的区县制作出租房屋清单--方式一(推荐)：select HTNAME as 户型,UNAME as 姓名,DNAME as 区县, SNAME as 街道from hos_type,sys_user,hos_district,hos_street,hos_housewhere hos_house.sid=hos_street.sidand hos_type.htid=hos_house.htidand sys_user.uid=hos_house.uidand hos_district.did=hos_street.sdidand hos_street.sdid in(  select  hos_street.sdid  from  hos_street,hos_district,  (select distinct sid from hos_house) as temp  where hos_street.sid=temp.sid  and hos_street.sdid=hos_district.did  group by hos_street.sdid  having count(hos_street.sid)>=2)--方式二：select HTNAME as 户型,UNAME as 姓名,DNAME as 区县, SNAME as 街道from hos_type,sys_user,hos_district,hos_street,hos_housewhere hos_house.sid=hos_street.sidand hos_type.htid=hos_house.htidand sys_user.uid=hos_house.uidand hos_district.did=hos_street.sdidand hos_house.SID in(  select hos_street.SID  from hos_street  where sdid in  (    select sdid    from hos_street,(select distinct(SID) from hos_house) as temp   where hos_street.sid=temp.sid   group by sdid  having count(hos_street.SID)>=2  ))--阶段4：按季度统计本年发布的房屋出租数量--要求输出本年1月1日至今的全部出租房屋数量，各区县--出租房屋数量以及各街道、户型出租房屋数量。use houseDECLARE @year int--SET @year = DATEPART(yy,GETDATE())set @year=2009SELECT tmp.quarter AS '季度',hos_district.DNAME AS '区县',hos_street.SNAME AS '街道',hos_type.HTNAME AS '户型',tmp.cnt AS '房屋数量'FROM (  SELECT SID,HTID,COUNT(*) cnt,DATEPART(qq,HTIME) quarter  FROM hos_house  WHERE DATEPART(yy,HTIME)=@year  GROUP BY DATEPART(qq,HTIME),SID,HTID) tmp--INNER JOIN sys_user ON (tmp.UID=sys_user.UID)INNER JOIN hos_street ON(tmp.SID=hos_street.SID)INNER JOIN hos_district ON(hos_street.SDID=hos_district.DID)INNER JOIN hos_type ON(tmp.HTID=hos_type.HTID)UNIONSELECT DATEPART(qq,HTIME),hos_district.DNAME,' 小计 ','  ',COUNT(*) AS '房屋数量'FROM hos_houseINNER JOIN hos_street ON(hos_house.SID=hos_street.SID)INNER JOIN hos_district ON(hos_street.SDID=hos_district.DID)WHERE DATEPART(yy,HTIME)=@yearGROUP BY DATEPART(qq,HTIME),hos_district.DNAMEunionSELECT DATEPART(qq,HTIME),' 合计 ','  ','  ',COUNT(*) AS '房屋数量'FROM hos_houseWHERE DATEPART(yy,HTIME)=@yearGROUP BY DATEPART(qq,HTIME)