租房網(wǎng)需求-作業(yè)

1、.sql 上機(jī) 一 /* 創(chuàng)建數(shù)據(jù)庫housedb1. 建立目錄 d:project2. 建庫時(shí)按命名規(guī)則指定數(shù)據(jù)庫的邏輯名稱及物理名稱。設(shè)數(shù)據(jù)庫初始大小10,文件增長：15% */二 建表 建表要求：客戶信息表 字段名稱 類型列特征及說明uidint客戶編號，主鍵，標(biāo)識列從1開始，遞增值為1unamevarchar（10）客戶姓名，該欄必填upasswordvarchar（30）密碼，至少6個(gè)字符區(qū)縣信息表：字段名稱 類型列特征didint區(qū)縣編號，主鍵，標(biāo)識列從1開始，遞增1dnamevarchar(50)區(qū)縣名稱，必填街道信息表:字段名稱 類型列特征sidint街道編號，主鍵，標(biāo)識列從1

2、開始，遞增1snamevarchar(100)街道名稱，必填sdidint區(qū)縣編號，表hos_district的外鍵房屋類型表：字段名稱 類型列特征htidint房屋類型編號，主鍵，標(biāo)識列從1開始，遞增1htnamevarchar(50)房屋類型名稱，必填出租房屋信息表：字段名稱 類型列特征hmidint出租房屋編號，主鍵，標(biāo)識列從1開始，遞增1uidint客戶編號，必填，外鍵sidint街道編號，必填，外鍵htidint房屋類型編號，必填，外鍵pricedecimal每月租金，必填，默認(rèn)為0，要求大于等于0topicvarchar標(biāo)題，必填contentsvarchar描述，必填htimed

3、atetime發(fā)布時(shí)間，必填，默認(rèn)值為當(dāng)前日期，要求小于等于當(dāng)前日期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-區(qū)縣信息表insert into hos_district (dname) values(海淀區(qū))insert into hos_district (dname) values(西城區(qū))insert into hos_district (dname) values(東城區(qū))insert into hos_district (dname) values(朝陽區(qū))insert into hos_district (dname) values(景山區(qū))insert into hos_district (dname)

6、 values(宣武區(qū))insert into hos_district (dname) values(大興)insert into hos_district (dname) values(豐臺)go-街道信息表insert into hos_street (sname,sdid) values(中關(guān)村,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(一室一衛(wèi))insert into hos_type (htname) values(一室一廳)insert into hos_type (htname) values(兩室一衛(wèi))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,中關(guān)村,中關(guān)村一條街,2009-1-2,中關(guān)村)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,中關(guān)村,中關(guān)村電腦城,20

12、09-4-1,中關(guān)村)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語句按需求實(shí)現(xiàn)如下功能1. /*查詢顯

15、示街道為中關(guān)村附近的租房信息,顯示街道，價(jià)格，房屋類型名稱,標(biāo)題,描述*/ 2. /*查詢顯示一室一廳2009年1月份的租房信息,顯示發(fā)布人，區(qū)縣名，價(jià)格，描述，時(shí)間*/3. /*統(tǒng)計(jì)街道為萬泉莊一室一廳的均價(jià)是否高于1800，如果均價(jià)高于1800，就輸出現(xiàn)萬泉莊一室一廳的均價(jià)否則將均價(jià)調(diào)整為1800*/4. /*按價(jià)格升序查詢輸出hos_house表中第6-10行的出租房屋信息*/-1)使用top提示：-select no=identity(int,1,1),* into 新表（物理表|臨時(shí)表） from table1-select 列. into #臨時(shí)表名from 表-2)自行查找?guī)椭褂胷ow_number()函數(shù)-3) /*使用row_number()函數(shù)實(shí)現(xiàn)對hos_house表的分頁功能-提示：例：declare pageindex int,pagesize intset pagesize=5set pageindex=2應(yīng)顯示每頁3條記錄時(shí)，第2頁