05租房网项目
来源：互联网 发布：方形的风管知乎 编辑：程序博客网 时间：2024/04/27 18:04
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 CHECK(PRICE>0), CONSTRAINT DF_HTIME DEFAULT(GETDATE()) FOR HTIME, CONSTRAINT CK_HTIME CHECK(HTIME<=GETDATE()), CONSTRAINT FK_UID FOREIGN KEY (UID) REFERENCES sys_user(UID) , -- CONSTRAINT FK_DID FOREIGN KEY (DID) REFERENCES hos_district(DID), CONSTRAINT FK_SID FOREIGN KEY (SID) REFERENCES hos_street (SID), CONSTRAINT FK_HTID FOREIGN KEY(HTID) REFERENCES hos_type(HTID) GO --------添加测试数据 -----sys_user插入数据 INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('张三','s217701') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('李四','s217702') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('王鑫','s217703') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('张建','s217704') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('李剑','s217705') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('蒋以然','s217706') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('王晓超','s217707') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('张冬雪','s217708') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('孙鹏','s217709') INSERT INTO sys_user(UNAME,UPASSWORD) VALUES('蒋连昌','s217710') GO -----表hos_district插入数据 INSERT INTO hos_district (DNAME) VALUES('海淀区') INSERT INTO hos_district (DNAME) VALUES('西城区') INSERT INTO hos_district (DNAME) VALUES('东城区') INSERT INTO hos_district (DNAME) VALUES('朝阳区') INSERT INTO hos_district (DNAME) VALUES('景山区') INSERT INTO hos_district (DNAME) VALUES('宣武区') INSERT INTO hos_district (DNAME) VALUES('大兴') INSERT INTO hos_district (DNAME) VALUES('丰台') GO ----表hos_street插入数据 INSERT INTO hos_street (SNAME,SDID) VALUES('中关村',1) INSERT INTO hos_street (SNAME,SDID) VALUES('苏州街',1) INSERT INTO hos_street (SNAME,SDID) VALUES('万泉庄',1) INSERT INTO hos_street (SNAME,SDID) VALUES('东四',3) INSERT INTO hos_street (SNAME,SDID) VALUES('东单',3) INSERT INTO hos_street (SNAME,SDID) VALUES('西四',2) INSERT INTO hos_street (SNAME,SDID) VALUES('西单',2) INSERT INTO hos_street (SNAME,SDID) VALUES('东湖',4) INSERT INTO hos_street (SNAME,SDID) VALUES('八里庄',4) INSERT INTO hos_street (SNAME,SDID) VALUES('双井',5) INSERT INTO hos_street (SNAME,SDID) VALUES('陶然亭',5) INSERT INTO hos_street (SNAME,SDID) VALUES('南菜园',6) INSERT INTO hos_street (SNAME,SDID) VALUES('兴丰街',7) INSERT INTO hos_street (SNAME,SDID) VALUES('黄村',7) INSERT INTO hos_street (SNAME,SDID) VALUES('南苑街',8) INSERT INTO hos_street (SNAME,SDID) VALUES('东铁营',8) GO -----表hos_type插入数据 INSERT INTO hos_type (HTNAME) VALUES('一室一卫') INSERT INTO hos_type (HTNAME) VALUES('一室一厅') INSERT INTO hos_type (HTNAME) VALUES('两室一卫') INSERT INTO hos_type (HTNAME) VALUES('两室一厅') INSERT INTO hos_type (HTNAME) VALUES('三室一厅') INSERT INTO hos_type (HTNAME) VALUES('三室两厅') GO ----表hos_house插入数据 INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(1,1,2,2600,'中关村','中关村一条街','2009-1-2','中关村') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(2,2,3,3600,'苏州街','苏州街一条街','2009-1-3','苏州街') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(3,3,4,4600,'万泉庄','万泉庄一条街','2009-1-4','万泉庄') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(1,3,2,1500,'万泉庄附近','万泉庄附近一条街','2009-7-2','万泉庄附近') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(1,5,2,2700,'东单','东单很多美食','2009-9-2','东单') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(3,1,2,2600,'中关村','中关村电脑城','2009-4-1','中关村') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(4,4,1,2000,'东四','东四一条街','2009-4-2','东四') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(5,6,3,3600,'西四','西四一条街','2009-1-2','西四') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(5,7,2,3600,'西单','西单购物城','2009-4-2','西单') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(6,2,2,2600,'苏州街','苏州街美食','2009-2-2','苏州街') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(7,8,3,2900,'朝阳东湖','朝阳东湖一景','2009-3-2','朝阳') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(8,3,1,700,'万泉庄','万泉庄一条街','2009-5-2','万泉庄') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(3,2,3,4200,'苏州街','苏州街二条街','2009-1-3','苏州街') INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,CONTENTS,HTIME,COPY) VALUES(4,2,3,4100,'苏州街','苏州街西街','2009-1-3','苏州街') GO [sql] view plain copy 在CODE上查看代码片派生到我的代码片use HOUSE select * from hos_house --房间信息表 select * from hos_district --区县表 select * from hos_street --街道表 select * from hos_type --房间类型表 --1.内容扩充：临时表 --如果表名前面有#，那么创建的表是临时表，位于tempdb数据库下 --的临时表文件夹下。 create table #mytemp2222 ( id int identity(1,1) primary key not null, name nvarchar(20) ) --使用临时表的好处：临时表中的数据当我们断开连接后自动释放，这样 --可以节省数据库服务器的空间 --任务一：分页显示查询出租房信息 --经典分页思路(一、双top，双order by 二、Row_number()函数 三、临时表方式(和Row_Number()原理一致)) --将出租房屋记录批量插入临时表 --*******************方式一：双top，双order by分页************************ --查询输出第6条~第10条房屋出租信息 --did:区县编号(hos_district) select top 5 *,hos_district.did from hos_house,hos_street,hos_district where hmid not in(select top 5 hmid from hos_house) and hos_house.sid=hos_street.sid and hos_street.sdid=hos_district.did --*******************方式二：Row_Number() over(order by id)*************** select * from (select *,row_number() over(order by hmid) as myid from hos_house) as temp where myid between 6 and 10 --*******************方式三：临时表分页*********************************** --查询输出第6条~第10条房屋出租信息 use house select identity(int,1,1) as myid, UID, SID, HTID, PRICE, TOPIC, CONTENTS, HTIME, COPY into #housetemp from hos_house --通过临时表我们发现，其实实现分页只有两中思路，一种是通过双top，双order by方式， --另外一种方式就是用一定的手段(可以使系统提供Row_NUmber()函数，也 --可以通过identity(int,1,1)来产生)，在现有表上加上一个自动增长列。 select * from #housetemp where myid between 6 and 10 --如何将临时表释放 drop table #housetemp --练习2——查询指定客户发布的出租房屋信息 --需求说明:: --查询“张三”发布的所有出租房屋信息，并显示房屋分布的街道、区县 select DNAME as 区县,SNAME as 街道,HTNAME as 户型,price as 价格,topic as 标题,contents as 描述,htime as 时间,copy as 备注 from hos_district, hos_street, hos_type, hos_house where hos_house.sid=hos_street.sid and hos_house.htid=hos_type.htid and hos_street.sdid=hos_district.did and uid= ( select uid from sys_user where uname='张三' ) --阶段3：练习——按区县制作房屋出租清单 --根据户型和房屋所在区县和街道,为至少有2个街道有房屋出租的区县制作出租房屋清单 --方式一(推荐)： select HTNAME as 户型,UNAME as 姓名,DNAME as 区县, SNAME as 街道 from hos_type,sys_user,hos_district,hos_street,hos_house where hos_house.sid=hos_street.sid and hos_type.htid=hos_house.htid and sys_user.uid=hos_house.uid and hos_district.did=hos_street.sdid and hos_street.sdid in ( select hos_street.sdid from hos_street,hos_district, (select distinct sid from hos_house) as temp where hos_street.sid=temp.sid and hos_street.sdid=hos_district.did group by hos_street.sdid having count(hos_street.sid)>=2 ) --方式二： select HTNAME as 户型,UNAME as 姓名,DNAME as 区县, SNAME as 街道 from hos_type,sys_user,hos_district,hos_street,hos_house where hos_house.sid=hos_street.sid and hos_type.htid=hos_house.htid and sys_user.uid=hos_house.uid and hos_district.did=hos_street.sdid and hos_house.SID in ( select hos_street.SID from hos_street where sdid in ( select sdid from hos_street,(select distinct(SID) from hos_house) as temp where hos_street.sid=temp.sid group by sdid having count(hos_street.SID)>=2 ) ) --阶段4：按季度统计本年发布的房屋出租数量 --要求输出本年1月1日至今的全部出租房屋数量，各区县 --出租房屋数量以及各街道、户型出租房屋数量。 use house DECLARE @year int --SET @year = DATEPART(yy,GETDATE()) set @year=2009 SELECT tmp.quarter AS '季度',hos_district.DNAME AS '区县',hos_street.SNAME AS '街道', hos_type.HTNAME AS '户型',tmp.cnt AS '房屋数量' FROM ( SELECT SID,HTID,COUNT(*) cnt,DATEPART(qq,HTIME) quarter FROM hos_house WHERE DATEPART(yy,HTIME)=@year GROUP BY DATEPART(qq,HTIME),SID,HTID ) tmp --INNER JOIN sys_user ON (tmp.UID=sys_user.UID) INNER JOIN hos_street ON(tmp.SID=hos_street.SID) INNER JOIN hos_district ON(hos_street.SDID=hos_district.DID) INNER JOIN hos_type ON(tmp.HTID=hos_type.HTID) UNION SELECT DATEPART(qq,HTIME),hos_district.DNAME,' 小计 ',' ',COUNT(*) AS '房屋数量' FROM hos_house INNER JOIN hos_street ON(hos_house.SID=hos_street.SID) INNER JOIN hos_district ON(hos_street.SDID=hos_district.DID) WHERE DATEPART(yy,HTIME)=@year GROUP BY DATEPART(qq,HTIME),hos_district.DNAME union SELECT DATEPART(qq,HTIME),' 合计 ',' ',' ',COUNT(*) AS '房屋数量' FROM hos_house WHERE DATEPART(yy,HTIME)=@year GROUP BY DATEPART(qq,HTIME) 0 0
- 05租房网项目
- MySchool 05 租房网项目
- 05 租房网
- 第五章：项目：我的租房网
- 租房网
- 租房网
- 我的租房网
- 优化MYSCHOOL租房网
- 第五章租房网
- 租房
- 租房
- 租房
- 租房
- 租房
- 租房
- 租房
- 租房
- 基于JavaEE平台实战WebService框架Xfire+Ajax框架DWR(租房网项目实战)
- Java多线程
- 利用runloop开启常驻线程
- 探究react-native通信机制
- Oracle 'open cursor loop fetch into' and 'for in cursor loop'
- Android 自定义View (二) 进阶
- 05租房网项目
- 深入.NET 第六章 上机1
- 网络编程学习笔记二（实现一个基于简单TCP的用户注册程序）
- RTSP协议详解
- MySQL数据库——基础运算符和函数总结(一步到位)
- MySql数据库笔试题总结
- python 2to3
- Java并发编程的艺术 读后记要(1)
- JSON net.sf.json 依赖的jar包
