数据库-我的租房网总汇.doc

《数据库技术与开发》 项目名称：我的租房网 姓 名：谭海兵 学 号：1367159121 专 业：软件工程 项目实训内容 1、实训一：建立数据库结构 (1) 创建数据库House 使用SSMS向导创建数据库House，如下图所示： 创建数据库House 扩展内容：要求用T-SQL语句建立house1数据库。 CREATE DATABASE House ON PRIMARY (NAME=NHouse,FILENAME=Nd:\sql\House.mdf,SIZE=5MB,MAXSIZE=UNLIMITED, FILEGROWTH=1MB) LOG ON (NAME=NHouse_log,FILENAME=Nd:\sql\House_log.ldf,SIZE=1MB,MAXSIZE=20MB, FILEGROWTH=10%) 建立5张数据表 USE House CREATE TABLE sys_user ( UserId INT IDENTITY(1,1) PRIMARY KEY, UserName varchar(50) NOT NULL, UserPwd VARCHAR(50), CONSTRAINT ck_userpwd CHECK(LEN(UserPwd)=6) ) USE House CREATE TABLE hos_district ( DId INT IDENTITY(1,1) PRIMARY KEY, DName VARCHAR(50) NOT null ) USE House CREATE TABLE hos_street ( StreetId INT IDENTITY(1,1) PRIMARY KEY, SName VARCHAR(50) NOT NULL, SDId INT CONSTRAINT fk_id_1 FOREIGN KEY(SDId) REFERENCES hos_district(DId) ) USE House CREATE TABLE hos_type ( HTId INT IDENTITY(1,1) PRIMARY KEY, HTName VARCHAR(50) NOT NULL, ) USE House CREATE TABLE hos_house ( HMId INT IDENTITY PRIMARY KEY, UserId INT NOT NULL, StreetId INT NOT NULL, HTId INT NOT NULL, Price DECIMAL(8,2) CONSTRAINT ck_price CHECK(Price=0) DEFAULT(0), Topic varchar(50) NOT NULL, Contents VARCHAR(50) NOT NULL, HTime DATETIME NOT NULL CONSTRAINT ck_htime CHECK(HTime=GETDATE()) DEFAULT(GETDATE()), Copy VARCHAR(50) ) 添加外键约束 USE House ALTER TABLE hos_house ADD CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES sys_user(UserId) ALTER TABLE hos_house ADD CONSTRAINT FK_StreetId FOREIGN KEY(StreetId) REFERENCES hos_street(StreetId) ALTER TABLE hos_house ADD CONSTRAINT FK_HTId FOREIGN KEY(HTId) REFERENCES hos_type(HTId) 2、实训二：添加测试数据 (1) 主表添加测试数据 USE House INSERT INTO sys_user(UserName,UserPwd) VALUES(张三,000000),(李四,000000) INSERT INTO hos_district(DName) VALUES(海淀区),(朝阳区) INSERT INTO hos_street(SName,SDId) VALUES(万寿路,1),(中关村,1),(陶然亭,2),(大栅栏,2) INSERT INTO hos_type(HTName) VALUES(两室一厅),(两室两厅) 添加批量数据 创建3个临时表 USE House CREATE TABLE #Topic ( id INT IDENTIT