05租房网项目

USE MASTER   
GO  
EXEC XP_CMDSHELL 'MKDIR E:\HOUSE1'  
GO  
SET NOCOUNT ON----不显示影响行数  
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='HOUSE')  
DROP DATABASE HOUSE  
GO  
----创建数据库HOUSE  
CREATE DATABASE HOUSE  
ON  
(NAME='HOUSE_DATA',  
FILENAME='E:\HOUSE1\HOUSE_DATA.MDF',  
SIZE=3,FILEGROWTH=1  
)  
LOG ON  
(NAME='HOUSE_LOG',  
FILENAME='E:\HOUSE1\HOUSE_DATA.LDF',  
SIZE=10,FILEGROWTH=10%  
)  
GO  
/*------创建表-----------*/  
--------建表:sys_user(用户信息表)  
USE HOUSE  
GO  
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='sys_user')  
DROP TABLE sys_user  
GO  
CREATE 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_user  
ADD CONSTRAINT PK_UID PRIMARY KEY (UID),  CONSTRAINT CK_UPASSWORD CHECK(LEN(UPASSWORD)>6)  
GO  
-----建表hos_district  
IF EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME='hos_district')  
DROP TABLE hos_district   
GO  
CREATE TABLE hos_district  
(  
DID INT IDENTITY (1,1) PRIMARY KEY NOT NULL ,---区县编号(主键)  
DNAME VARCHAR(20) NOT NULL ----区县名称  
)  
GO  
----建表hos_street  
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME='hos_street')  
DROP TABLE hos_street  
GO  
CREATE 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_type  
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME='hos_type')  
DROP TABLE hos_type  
GO  
CREATE TABLE hos_type  
(  
HTID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,----房屋类型编号(主键)  
HTNAME VARCHAR(20) NOT NULL ---房屋类型  
)  
GO  
-----建表：hos_house  
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='hos_house')  
DROP TABLE hos_house  
GO  
CREATE 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_house  
ADD CONSTRAINT PK_HMID PRIMARY KEY (HMID),  CONSTRAINT DF_PRICE DEFAULT(0) FOR PRICE,  CONSTRAINT CK_PRICE

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！