创建租房网House脚本SQL数据库
来源:互联网 发布:警惕网络购物诈骗陷阱 编辑:程序博客网 时间:2024/06/12 01:52
--创建数据库
use master --权限
if exists (select * from sysdatabases wherename='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 wherename='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 wherename='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 wherename='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) referenceshos_district(DID)
go
--创建表hos_type
if exists (select * from sysobjects wherename='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 wherename='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) referencessys_user(UID),
constraint fk_SID foreign key(SID) referenceshos_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 备注
inner join hos_street onhos_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
0 0
- 创建租房网House脚本SQL数据库
- sql server创建数据库脚本
- Sql Server 创建数据库脚本
- oracle创建数据库sql脚本
- 数据库:我的租房网
- 通过SQL 脚本创建数据库方案(oracle)
- SQL脚本文件创建数据库(简单示例)
- 使用sql建库脚本创建数据库
- C#创建数据库,导入sql脚本
- SQLserver2008创建数据库SQL脚本备份语句
- sql server 创建数据库标准脚本
- SQL 第五章 我的租房网
- 数据库设计:我的租房网
- 关于创建SQL数据库脚本没有默认值的情况
- 调用方法执行sql脚本来创建数据库
- 租房网
- 租房网
- SQL脚本创建表
- 封装数据库
- ListView-Details 中 添加
- 在DataGirdView中出现“未将对象引…
- 在VS中通过代码创建数据库(控制台…
- 在vs中创建数据库(窗体程序)
- 创建租房网House脚本SQL数据库
- AWT事件处理演示:点击ok按钮,文…
- AWT事件处理演示:继承适配器代替接…
- AWT编程
- 正则表达式
- 黑马程序员_java基础TCP数据传输
- 定义一个colors.xml颜色资源文件
- textcolor 设置selecter 报错: <item> tag requires a 'color' attribute or child tag defining a drawab
- 创建BankDB银行数据库,创建表,插…