租房网需求-作业

1、.sql 上机 一 /* 创建数据库housedb1. 建立目录 d:project2. 建库时按命名规则指定数据库的逻辑名称及物理名称。设数据库初始大小10,文件增长：15% */二 建表 建表要求：客户信息表 字段名称 类型列特征及说明uidint客户编号，主键，标识列从1开始，递增值为1unamevarchar（10）客户姓名，该栏必填upasswordvarchar（30）密码，至少6个字符区县信息表：字段名称 类型列特征didint区县编号，主键，标识列从1开始，递增1dnamevarchar(50)区县名称，必填街道信息表:字段名称 类型列特征sidint街道编号，主键，标识列从1

2、开始，递增1snamevarchar(100)街道名称，必填sdidint区县编号，表hos_district的外键房屋类型表：字段名称 类型列特征htidint房屋类型编号，主键，标识列从1开始，递增1htnamevarchar(50)房屋类型名称，必填出租房屋信息表：字段名称 类型列特征hmidint出租房屋编号，主键，标识列从1开始，递增1uidint客户编号，必填，外键sidint街道编号，必填，外键htidint房屋类型编号，必填，外键pricedecimal每月租金，必填，默认为0，要求大于等于0topicvarchar标题，必填contentsvarchar描述，必填htimed

3、atetime发布时间，必填，默认值为当前日期，要求小于等于当前日期copyvarchar(500)备注三/*向各表中插入测试记录（10分）*/-用户表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)

4、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_u

5、ser(uname,upassword) values(蒋连昌,s217710)go-区县信息表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)

6、 values(宣武区)insert into hos_district (dname) values(大兴)insert into hos_district (dname) values(丰台)go-街道信息表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

7、)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 int

8、o 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-房屋类型表insert in

9、to 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-出租房屋信息表insert into hos_house(uid,sid,htid,price,topic,

10、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_hous

11、e(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,中关村,中关村电脑城,20

12、09-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

13、,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,content

14、s,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语句按需求实现如下功能1. /*查询显

15、示街道为中关村附近的租房信息,显示街道，价格，房屋类型名称,标题,描述*/ 2. /*查询显示一室一厅2009年1月份的租房信息,显示发布人，区县名，价格，描述，时间*/3. /*统计街道为万泉庄一室一厅的均价是否高于1800，如果均价高于1800，就输出现万泉庄一室一厅的均价否则将均价调整为1800*/4. /*按价格升序查询输出hos_house表中第6-10行的出租房屋信息*/-1)使用top提示：-select no=identity(int,1,1),* into 新表（物理表|临时表） from table1-select 列. into #临时表名from 表-2)自行查找帮助使用row_number()函数-3) /*使用row_number()函数实现对hos_house表的分页功能-提示：例：declare pageindex int,pagesize intset pagesize=5set pageindex=2应显示每页3条记录时，第2页