创建租房网House脚本SQL数据库

--创建数据库

use master --权限

if exists (select * from sysdatabases where name='House')

drop database House

go

create database House 

on primary

(

name='House_data',

filename='E:\House_data.mdf',

size=15MB,

maxsize=20MB,

filegrowth=15%

)

log on

(

name='House_log',

filename='E:\House_log.ldf',

size=15MB,

maxsize=20MB,

filegrowth=15%

)

go

use House --在数据库House创建表及操作数据

--创建表sys_user

if exists (select * from sysobjects where name='sys_user')

drop table sys_user

go

create table sys_user

(

UID int identity(1,1),

UName varchar(20) not null,

UPASSWORD varchar(20)

)

go

--添加表sys_user约束

alter table sys_user

add constraint pk_UID primary key(UID),

constraint ck_UPASSWORD check(len(UPASSWORD)>=6)

go

--创建表hos_district

if exists (select * from sysobjects where name='hos_district')

drop table hos_district

go

create table hos_district

(

DID int identity(1,1),

DName varchar(20) not null

)

go

--添加表hos_district约束

alter table hos_district

add constraint pk_DID primary key(DID)

go

--创建表hos_street

if exists (select * from sysobjects where name='hos_street')

drop table hos_street

go

create table hos_street

(

SID int identity(1,1),

SName varchar(20) not null,

SDID int not null

)

go

--添加表hos_street约束

alter table hos_street

add constraint pk_SID primary key(SID),

constraint fk_SDID foreign key(SDID) references hos_district(DID)

go

--创建表hos_type

if exists (select * from sysobjects where name='hos_type')

drop table hos_type

go

create table hos_type

(

HTID int identity(1,1),

HTName varchar(20) not null

)

go

--创建表hos_house

if exists (select * from sysobjects where name='hos_house')

drop table hos_house

go

create table hos_house

(

HMID int   identity(1,1),

UID int not null,

SID int not null,

HTID int not null,

PRICE decimal not null,

TOPIC varchar(20) not null,

CONTENTS varchar(20) not null,

HTIME datetime not null,

COPY varchar(100)

)

go

--添加表hos_house约束

alter table hos_house

add constraint pk_HMID primary key(HMID),

constraint fk_UID foreign key(UID) references sys_user(UID),

constraint fk_SID foreign key(SID) references hos_street(SID),

constraint df_price default(0) for PRICE, --价格默认值0

constraint ck_price check(price>=0), --价格要求大于等于0

constraint df_HTIME default(getdate()) for HTIME, --日起默认为当前日期

constraint ck_HTIME check(HTIME<=getdate()) --日期要小于等于当前日期

go

--向表中添加数据

--sys_user

insert into sys_user values ('杨洋','1234567')

insert into sys_user values ('张三','1234567')

insert into sys_user values ('李四','1234567')

insert into sys_user values ('王娜','1234567')

insert into sys_user values ('王鸥','1234567')

insert into sys_user values ('吴鹏','1234567')

insert into sys_user values ('方尺','1234567')

go

--hos_district

insert into hos_district values('海淀区')

insert into hos_district values('东城区')

go

--hos_street

insert into hos_street values('中关村',1)

insert into hos_street values('万庄泉',1)

insert into hos_street values('东单',2)

insert into hos_street values('苏州街',1)

insert into hos_street values('西四',2)

go

--hos_type

insert into hos_type values ('两室一厅')

insert into hos_type values ('两室二厅')

insert into hos_type values ('一室一厅')

insert into hos_type values ('三室一厅')

insert into hos_type values ('两室两厅')

go

--hos_house

insert into hos_house values (1,1,1,50,'中关村','中关村电脑城','2014-4-7','中关村copy')

insert into hos_house values (2,1,2,50,'万庄泉','万庄泉电脑城','2014-1-7','万庄泉copy')

insert into hos_house values (3,2,3,60,'中关村','中关村电脑城','2014-6-7','中关村copy')

insert into hos_house values (4,4,4,100,'万庄泉','万庄泉电脑城','2014-8-7','万庄泉copy')

insert into hos_house values (6,3,2,200,'苏州街','苏州街电脑城','2014-5-7','苏州街copy')

insert into hos_house values (7,2,1,500,'东单','东单电脑城','2014-4-4','东单copy')

go

--查询第2-3条出租房屋信息

select TOP 2 * from hos_house

--查找指定客户发布的出租房屋信息

select 

hos_district.DName as 区县,

hos_street.SName as 街道,

hos_type.HTName as 户型,

PRICE as 价格,

TOPIC as 标题,

CONTENTS as 描述,

HTIME as 时间,

COPY as 备注

  from hos_house

inner join hos_street   on hos_street.SID=hos_house.SID

inner join hos_type on hos_type.HTID=hos_house.HTID

inner join hos_district on hos_street.SDID=hos_house.SID

  where UID in (select UID from sys_user where UName='张三')