数据库设计：我的租房网

--创建house数据库
create database House
on(
  name='house',
  filename='e:\yangbo\house.mdf',
  size=5
)
log on(
  name='house_log',
  filename='e:\yangbo\house_log.log',
  size=1,
  maxsize=5
)

use house
go

--创建客户信息表
create table sys_user(
  UID INT not null primary key(uid) identity(1,1),--客户编号，主键，标识列从1开始，递增值为1
  UName varchar(50) not null,--客户姓名，该栏必填
  UpassWord varchar(50) check(len(upassword)>6)--密码，至少6个字符
)
--创建区县信息表
create table hos_district(
  DID INT PRIMARY KEY(DID) identity(1,1),--区县编号，主键，标识列从1开始，递增值为1
  DName varchar(50) not null--区县名称，该栏必填
)

--创建街道信息表

create table hos_street(
  SID INT PRIMARY KEY(SID) IDENTITY(1,1),--街道编号，主键，标识列从1开始，递增值为1
  SName varchar(50) not null,-- 街道名称，该栏必填
  SDID INT --区县编号，表hos_district 的外键
)
alter table hos_street
add constraint sdid_fk foreign key(sdid)references hos_district(did)

--创建房屋类型表hos_type

create table hos_type(
   HTID INT PRIMARY KEY(HTID) IDENTITY(1,1),--房屋类型编号，主键，标识列从1开始，递增值为1
   HTName varchar(50) not null--房屋类型名称，该列必填
)

--创建出租房屋信息表

create table hos_house(
   HMID INT PRIMARY KEY(HMID) IDENTITY(1,1),--出租房屋编号，主键，标示符从1开始，递增值为1
   UID INT NOT NULL,--客户编号，该栏必填，外键
   SID INT NOT NULL,--街道编号，该栏必填，外键
   HTID INT NOT NULL,--房屋类型编号，该栏必填，外键
   PRICE DECIMAL NOT NULL DEFAULT(0) CHECK(PRICE>0 OR PRICE=0),--每月租金，该栏必填，默认值为0，要求小于等于当前日期
   TOPIC VARCHAR(50) NOT NULL,--标题，该栏必填
   CONTENTS VARCHAR(50) NOT NULL,--描述，该栏必填
   HTIME DATETIME NOT NULL DEFAULT(GETDATE()) CHECK(HTIME<GETDATE()),--发布时间，该栏必填，默认值为当前日期，要求小于等于当前日期
   COPY VARCHAR(50)--备注
)
alter table hos_house
add constraint uid_fk foreign key(uid) references sys_user(uid)
alter table hos_house
add constraint sid_fk foreign key(sid) references hos_street(sid)
alter table hos_house
add constraint htid_fk foreign key(htid) references hos_type(htid)

use house
go
select *from hos_house
select *from sys_user
--阶段1:知道————分页显示查询出租房屋信息
--需求说明：查询出第4-6条出租房屋信息

select top 3*
from hos_house
where HMID not in (select top 1 HMID from hos_house)

select *
from hos_house
where HMID>1 and HMID<5

--阶段2：练习————查询指定客户发布的出租房屋信息
--需求说明：查询张三发布所有出租房屋信息，并显示房屋分布的街道和区县

use House
go
select hos_district.DName as '区县',hos_street.SName as '街道',hos_type.HTName as'房屋类型',hos_house.PRICE as '价格',
       hos_house.TOPIC as'标题', hos_house.CONTENTS as'描述',hos_house.HTIME as '时间',
       hos_house.COPY as'备注'
from hos_house
inner join hos_street on hos_house.SID=hos_street.SID
inner join hos_district on hos_street.SDID=hos_district.DID
inner join sys_user on sys_user.UID=hos_house.UID
inner join hos_type on hos_type.HTID=hos_house.HTID
where sys_user.UName='张三'

--阶段3：联系————按区县制作房屋出租清单
--需求说明:根据户型和房屋所在区县和街道，为至少有俩个街道有出租房屋的区县制作出房屋清单

select hos_type.HTName as '户型', sys_user.UName as'姓名', hos_district.DName as'区县', hos_street.SName as '街道'
from hos_house
inner join hos_street on hos_house.SID=hos_street.SID
inner join hos_district on hos_street.SDID=hos_district.DID
inner join sys_user on sys_user.UID=hos_house.UID
inner join hos_type on hos_type.HTID=hos_house.HTID
where hos_district.DID in(select hos_district.DID
                       from hos_house
                       inner join hos_street on hos_house.SID=hos_street.SID
                       inner join hos_district on hos_street.SDID=hos_district.DID
                       group by hos_district.DID
                       having COUNT(*)=2 or COUNT(*)>2 )
                      
--阶段4：练习————按季度统计本年发布的房屋出租数量
--需求说明：按季度统计出本年各区各县个街道各种户型房屋出租数量
--          要求输出本年1月1日至今的全部出租房屋数量，各区县出租房屋数量以及各街道，户型出租房屋数量
declare @year int

set @year=DATEPART(YY,GETDATE())
--查询出以季节，街道，房屋类型来分类的房屋数量
select tmp.quarter as '季度',hos_district.DName as '区县',hos_street.SName as'街道',hos_type.HTName as'户型',tmp.cnt as'房屋数量'
from (
    select SID,HTID,COUNT(*) as cnt,DATEPART(QQ,HTIME) as quarter
    from hos_house
    where DATEPART(YY,HTIME)=@year
    group by DATEPART(QQ,HTIME),SID,HTID --以季节，街道，房屋类型分类
)tmp                 --派生出tmp表
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 hos_type.HTID=tmp.HTID
union
--查询出以季节，区县分类的房屋数量
select  DATEPART(QQ,hos_house.HTIME),hos_district.DName,'小计',' ',COUNT(*) as '房屋数量'
from hos_house
inner join hos_street on hos_house.SID=hos_street.SID
inner join hos_district on hos_street.SDID=hos_district.DID
where DATEPART(YY,hos_house.HTIME)=@year
group by DATEPART(qq,hos_house.HTIME),hos_district.DName
union
--查询出以季节分类的房屋数量
select DATEPART(QQ,hos_house.HTIME),'合计',' ',' ',COUNT(*) as '房屋数量'
from hos_house
inner join hos_street on hos_house.SID=hos_street.SID
inner join hos_district on hos_street.SDID=hos_district.DID
where DATEPART(YY,hos_house.HTIME)=@year
group by DATEPART(qq,hos_house.HTIME)