MySchool 05 租房网项目
来源:互联网 发布:淘宝iphone6s 编辑:程序博客网 时间:2024/04/27 14:42
- 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)
1 0
- MySchool 05 租房网项目
- 优化MYSCHOOL租房网
- 优化MySchool 第五章 租房网
- 优化Myschool 第五章 租房网
- 05租房网项目
- 05 租房网
- 第五章:项目:我的租房网
- 租房网
- 租房网
- 使用C#制作MySchool项目代码
- 我的租房网
- 第五章租房网
- 租房
- 租房
- 租房
- 租房
- 租房
- 租房
- Linux下vim的一些常用知识点(待扩充)
- 一天浓缩学习webpack经过
- 今日头条内推-时间不限
- 回答8岁女儿问题:学计算机都是干什么?什么是指令(上)
- Java中一些知识的归纳总结
- MySchool 05 租房网项目
- bashrc文件简介
- 3d图片爆碎效果
- 记录数据中重复数字问题
- C语言中const有什么用途
- PythonTip 分解n!
- 仿华为天气预报UI
- Python 装饰器decorater
- 流量充值对接微信支付宝支付实现流程