《数据库技术与开发》工程实训指导书
《数据库技术与开发》
项目名称：我的租房网姓 名：谭海兵学 号：专 业：软件工程
1367159121
1
《数据库技术与开发》工程实训指导书
项目实训内容
1、实训一：建立数据库结构
(1) 创建数据库House
使用SSMS向导创建数据库House，如下图所示：
图1．创建数据库House
扩展内容：要求用T-SQL语句建立house1数据库。
CREATE DATABASE House ON PRIMARY
(NAME=N'House',FILENAME=N'd:\\sql\\House.mdf',SIZE=5MB,MAXSIZE=UNLIMITED, FILEGROWTH=1MB) LOG ON
(NAME=N'House_log',FILENAME=N'd:\\sql\\House_log.ldf',SIZE=1MB,MAXSIZE=20MB, FILEGROWTH=10%)
(2) 建立5张数据表
2
《数据库技术与开发》工程实训指导书
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,
3
《数据库技术与开发》工程实训指导书
HTime DATETIME NOT NULL CONSTRAINT ck_htime CHECK(HTime<=GETDATE()) DEFAULT(GETDATE()), Copy VARCHAR(50) )
(3) 添加外键约束
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('两室一厅'),('两室两厅')
4
《数据库技术与开发》工程实训指导书
(2) 添加批量数据 创建3个临时表
USE House
CREATE TABLE #Topic (
id INT IDENTITY(1,1), topic VARCHAR(50) )
CREATE TABLE #content (
id INT IDENTITY(1,1), content VARCHAR(50) )
CREATE TABLE #copy (
id INT IDENTITY(1,1), copy VARCHAR(50) )
INSERT INTO #Topic(topic) VALUES('中关村') INSERT INTO #Topic(topic) VALUES('万泉新新家园') INSERT INTO #Topic(topic) values('望园小区') INSERT INTO #Topic(topic) VALUES('福盈家园') INSERT INTO #Topic(topic) VALUES('百子湾号院') INSERT INTO #Topic(topic) VALUES('中关村软件园')
INSERT INTO #content(content) VALUES('经典装修，拎包入住') INSERT INTO #content(content) VALUES('超值公寓火爆出租') INSERT INTO #content(content) VALUES('望京朝阳东北区') INSERT INTO #content(content) VALUES('低价个人入住') INSERT INTO #content(content) VALUES('昌平区回龙观') INSERT INTO #content(content) VALUES('精装修，首出租') INSERT INTO #copy(copy) VALUES('交通便利，配套完善') INSERT INTO #copy(copy) VALUES('环境优雅，学区房') INSERT INTO #copy(copy) VALUES('紧挨号地铁') INSERT INTO #copy(copy) VALUES('购物方便') INSERT INTO #copy(copy) VALUES('紧邻亚运村') INSERT INTO #copy(copy) VALUES('山水一体')
5