#创建数据库、表
-- 先创建数据库
-- 使用数据库
USE `myhouse`;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for biaoqian
-- ----------------------------
DROP TABLE IF EXISTS `biaoqian`;
CREATE TABLE `biaoqian` (
`bqid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '标签id',
`bqname` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标签名称',
PRIMARY KEY (`bqid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '标签表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of biaoqian
-- ----------------------------
INSERT INTO `biaoqian` VALUES (1, '近地铁');
INSERT INTO `biaoqian` VALUES (2, '随时看');
INSERT INTO `biaoqian` VALUES (3, '满二年');
INSERT INTO `biaoqian` VALUES (4, '满五年');
INSERT INTO `biaoqian` VALUES (5, '进公园');
INSERT INTO `biaoqian` VALUES (6, '复式');
INSERT INTO `biaoqian` VALUES (7, '跃层');
INSERT INTO `biaoqian` VALUES (8, 'loft');
INSERT INTO `biaoqian` VALUES (9, '隧道口');
-- ----------------------------
-- Table structure for chaoxiang
-- ----------------------------
DROP TABLE IF EXISTS `chaoxiang`;
CREATE TABLE `chaoxiang` (
`cxid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '朝向id',
`cxname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '朝向名称',
PRIMARY KEY (`cxid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '朝向表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of chaoxiang
-- ----------------------------
INSERT INTO `chaoxiang` VALUES (1, '南北');
INSERT INTO `chaoxiang` VALUES (2, '南');
INSERT INTO `chaoxiang` VALUES (3, '东');
INSERT INTO `chaoxiang` VALUES (4, '西');
INSERT INTO `chaoxiang` VALUES (5, '北');
-- ----------------------------
-- Table structure for district
-- ----------------------------
DROP TABLE IF EXISTS `district`;
CREATE TABLE `district` (
`did` INT(10) NOT NULL AUTO_INCREMENT COMMENT '区域编号',
`dname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '区域名称',
PRIMARY KEY (`did`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '区域表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of district
-- ----------------------------
INSERT INTO `district` VALUES (2, '江宁区');
INSERT INTO `district` VALUES (3, '鼓楼区');
INSERT INTO `district` VALUES (4, '玄武区');
INSERT INTO `district` VALUES (5, '建邺区');
INSERT INTO `district` VALUES (6, '秦淮区');
INSERT INTO `district` VALUES (7, '栖霞区');
INSERT INTO `district` VALUES (8, '雨花台区');
INSERT INTO `district` VALUES (9, '浦口区');
INSERT INTO `district` VALUES (10, '六合区');
INSERT INTO `district` VALUES (11, '溧水区');
INSERT INTO `district` VALUES (12, '高淳区');
INSERT INTO `district` VALUES (13, '南京周边');
-- ----------------------------
-- Table structure for havegoodhouse
-- ----------------------------
DROP TABLE IF EXISTS `havegoodhouse`;
CREATE TABLE `havegoodhouse` (
`yhfid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
`yhfimgurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图标地址',
`yhftitle` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标题',
`yhfsummary` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '简介',
PRIMARY KEY (`yhfid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '有好房' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of havegoodhouse
-- ----------------------------
INSERT INTO `havegoodhouse` VALUES (1, 'img/yhf1.jpg', '麒麟买房攻略', '刚需上车必看');
INSERT INTO `havegoodhouse` VALUES (2, 'img/yhf2.jpg', '迈皋桥商圈', '刚需华丽转身');
INSERT INTO `havegoodhouse` VALUES (3, 'img/yhf3.jpg', '2号线攻略', '做个幸福地铁族');
INSERT INTO `havegoodhouse` VALUES (4, 'img/yhf4.jpg', '健康社区', '看病容易');
-- ----------------------------
-- Table structure for houseinfo
-- ----------------------------
DROP TABLE IF EXISTS `houseinfo`;
CREATE TABLE `houseinfo` (
`hid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '房屋编号',
`information` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '详细信息',
`area` INT(10) NOT NULL COMMENT '面积',
`price` INT(10) NOT NULL COMMENT '价格',
`publishtime` DATE NOT NULL COMMENT '发布时间',
`personno` INT(10) NOT NULL COMMENT '关注人数',
`dkno` INT(10) NOT NULL COMMENT '带看次数',
`zxid` INT(10) NOT NULL COMMENT '装修id',
`zjid` INT(10) NOT NULL COMMENT '中介用户编号',
`bqid` INT(10) NOT NULL COMMENT '标签id',
`llid` INT(10) NOT NULL COMMENT '楼龄id',
`lcid` INT(10) NOT NULL COMMENT '楼层id',
`cxid` INT(10) NOT NULL COMMENT '朝向id',
`sid` INT(10) NOT NULL COMMENT '街道id',
`htid` INT(10) NOT NULL COMMENT '户型id',
`huid` INT(10) NOT NULL COMMENT '房屋用途id',
PRIMARY KEY (`hid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 32 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '房屋信息表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of houseinfo
-- ----------------------------
INSERT INTO `houseinfo` VALUES (1, '新街口 张府园 精装修单室套 全明 低楼层', 80, 123, '2018-03-14', 20, 10, 1, 2, 1, 1, 1, 1, 1, 2, 1);
INSERT INTO `houseinfo` VALUES (2, '上海路 新街口 29中施教区 水利厅房改房', 70, 180, '2018-03-13', 50, 60, 2, 2, 2, 2, 2, 2, 2, 2, 3);
INSERT INTO `houseinfo` VALUES (3, '佳盛花园二室一厅一卫', 60, 130, '2018-03-08', 20, 30, 3, 3, 4, 3, 3, 3, 5, 3, 1);
INSERT INTO `houseinfo` VALUES (4, '俞家巷二室一厅一卫', 50, 210, '2018-03-02', 10, 6, 2, 2, 5, 4, 2, 5, 2, 3, 3);
INSERT INTO `houseinfo` VALUES (5, '马群东 S6旁 锦绣花园 三房精装修 诚心卖', 116, 220, '2004-01-01', 10, 40, 1, 1, 2, 2, 2, 1, 3, 3, 1);
INSERT INTO `houseinfo` VALUES (6, '南站商圈南方花园婚房装修挑高6米买一层送一层适合居家用燃气', 40, 155, '2007-01-01', 32, 32, 2, 1, 2, 2, 3, 4, 4, 2, 1);
INSERT INTO `houseinfo` VALUES (7, '毛坯2房 有电梯 靠灵山站 可按自己喜好装修', 90, 162, '2015-01-01', 34, 13, 2, 3, 4, 3, 3, 1, 3, 2, 1);
INSERT INTO `houseinfo` VALUES (8, '名城世纪园二室一厅一卫', 78, 312, '2017-01-01', 32, 65, 3, 2, 2, 2, 3, 1, 5, 2, 1);
INSERT INTO `houseinfo` VALUES (9, '新上 天启花园 新空房毛坯 未入住 凤凰花园城施教区 有钥匙', 143, 670, '2009-01-01', 36, 65, 3, 2, 2, 3, 3, 2, 5, 3, 1);
INSERT INTO `houseinfo` VALUES (10, '长江路九号 纯毛坯 正规次顶楼跃层 阳光非常好', 244, 1590, '2012-01-01', 54, 76, 2, 3, 7, 2, 3, 1, 7, 5, 1);
INSERT INTO `houseinfo` VALUES (11, '鼓楼玄武湖畔科利华装修好四房楼层好', 95, 460, '2018-01-01', 54, 654, 1, 1, 2, 3, 2, 2, 7, 4, 1);
INSERT INTO `houseinfo` VALUES (12, '新出 双南 一楼带院 成贤街 东大东门 浮桥 市政府边户', 51, 340, '2018-01-01', 434, 76, 1, 1, 3, 2, 1, 2, 6, 2, 1);
INSERT INTO `houseinfo` VALUES (13, '水西门大街 湖西街 南湖东路 双南一楼 房龄新 随时看', 73, 210, '2018-01-01', 543, 654, 2, 1, 1, 1, 1, 1, 8, 2, 1);
INSERT INTO `houseinfo` VALUES (14, '莫园新寓三室 采光好 配套齐全 交通便利', 100, 245, '2017-01-01', 446, 543, 2, 2, 1, 1, 3, 1, 9, 2, 1);
INSERT INTO `houseinfo` VALUES (15, '水西门大街 湖西街 茶南云锦路 全明边户2房 带院子', 56, 188, '2018-01-01', 432, 43, 1, 2, 2, 1, 1, 1, 8, 2, 1);
INSERT INTO `houseinfo` VALUES (16, '新街口张府园站丰富路 朗诗熙园 精装双南 有车位看房方便', 116, 550, '2004-01-01', 3, 32, 1, 1, 1, 1, 2, 2, 10, 2, 1);
INSERT INTO `houseinfo` VALUES (17, '朗诗熙园三室二厅一卫', 149, 580, '2017-01-01', 32, 43, 2, 2, 3, 3, 2, 3, 11, 3, 1);
INSERT INTO `houseinfo` VALUES (18, '新街口 朝天宫西街 楼层好 阳光从早到晚 户型正不临街', 69, 208, '1990-01-01', 22, 44, 3, 3, 2, 3, 2, 3, 11, 1, 1);
INSERT INTO `houseinfo` VALUES (19, '仙林湖 星叶风情水岸 通透两房 纯毛坯 业主诚售 看房方便', 82, 249, '2015-01-01', 22, 11, 2, 2, 3, 1, 2, 2, 1, 1, 1);
INSERT INTO `houseinfo` VALUES (20, '南大和园 低总价 精装好房出售 低总价适合刚需的你', 88, 340, '2018-01-01', 11, 10, 3, 1, 3, 2, 3, 1, 12, 2, 1);
INSERT INTO `houseinfo` VALUES (21, '翠岭银河-银河苑二室一厅一卫', 79, 167, '2017-01-01', 22, 10, 1, 2, 1, 2, 3, 3, 14, 2, 1);
INSERT INTO `houseinfo` VALUES (22, '莲花湖畔 朗诗绿色花园 开发商装修 四季恒温 送部分设施', 67, 118, '2016-01-01', 22, 3, 2, 3, 1, 3, 3, 3, 13, 1, 1);
INSERT INTO `houseinfo` VALUES (23, '满两年 新空毛坯 从未入住 楼层好 随时看房 有钥匙', 77, 278, '2015-01-01', 21, 8, 3, 1, 3, 3, 2, 2, 14, 1, 1);
INSERT INTO `houseinfo` VALUES (24, '威尼斯精装两房 B户型 南北通透 低价快来看 机会不等人', 88, 195, '2018-01-01', 22, 5, 1, 2, 3, 1, 2, 3, 15, 2, 1);
INSERT INTO `houseinfo` VALUES (25, '天润城14街区 刚需两房 中高楼层 采光好 出行方便 诚售', 69, 165, '2017-01-01', 15, 11, 2, 1, 2, 1, 2, 1, 15, 1, 1);
INSERT INTO `houseinfo` VALUES (26, '幸福筑家 晓山北村 2005年房龄 小三房 房主换房诚心出售', 87, 146, '2016-01-01', 33, 22, 3, 2, 3, 2, 3, 2, 16, 3, 1);
INSERT INTO `houseinfo` VALUES (27, '冠城大通蓝郡 中间楼层 大户型', 98, 135, '2017-01-01', 55, 33, 1, 3, 2, 3, 1, 2, 17, 2, 1);
INSERT INTO `houseinfo` VALUES (28, '雨荷苑 满两年毛坯 两室一厅一卫 采光好', 72, 108, '2018-01-01', 22, 10, 2, 3, 1, 3, 2, 3, 17, 2, 1);
INSERT INTO `houseinfo` VALUES (29, '地霸G47旁龙池花园 户型规整 全明通透 S8龙池站诚售', 62, 84, '2017-01-01', 10, 10, 2, 3, 1, 2, 1, 2, 18, 1, 1);
INSERT INTO `houseinfo` VALUES (30, '瑞凯国际一室一厅一卫\r\n', 45, 53, '2018-01-01', 22, 32, 1, 3, 1, 1, 1, 1, 18, 1, 1);
INSERT INTO `houseinfo` VALUES (31, '城开新都雅苑三室二厅一卫', 86, 140, '2017-01-01', 12, 11, 2, 3, 2, 1, 2, 2, 17, 2, 1);
-- ----------------------------
-- Table structure for houseknowledge
-- ----------------------------
DROP TABLE IF EXISTS `houseknowledge`;
CREATE TABLE `houseknowledge` (
`fczsid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
`fczsimgurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图标地址',
`fczstitle` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标题',
`fczssummary` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '简介',
PRIMARY KEY (`fczsid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '房产知识' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of houseknowledge
-- ----------------------------
INSERT INTO `houseknowledge` VALUES (1, '/img/fczs1.jpg', '住房公积金可以续缴吗?', '有些人可能缴纳了一段时间的住房公积金,但是中间却由于种种原因中断了缴纳,住房公积金可以续缴吗?');
INSERT INTO `houseknowledge` VALUES (2, '/img/fczs2.jpg', '签二手房合同要注意哪些细节?', '在二手房交易中,当买卖双方确认好了房屋的价格及相关事宜后,便可以签订《房屋买卖合同》...');
-- ----------------------------
-- Table structure for housepic
-- ----------------------------
DROP TABLE IF EXISTS `housepic`;
CREATE TABLE `housepic` (
`hid` INT(10) NOT NULL COMMENT '房屋编号',
`hpid` INT(10) NOT NULL COMMENT '房屋图片编号',
`hpurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '房屋图片图片地址'
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '房屋图片表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of housepic
-- ----------------------------
INSERT INTO `housepic` VALUES (1, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (1, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (1, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (1, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (1, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (1, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (2, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (2, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (2, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (2, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (2, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (2, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (3, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (3, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (3, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (3, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (3, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (3, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (4, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (4, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (4, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (4, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (4, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (4, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (5, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (5, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (5, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (5, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (5, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (5, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (6, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (6, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (6, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (6, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (6, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (6, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (7, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (7, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (7, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (7, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (7, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (7, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (8, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (8, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (8, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (8, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (8, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (8, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (9, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (9, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (9, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (9, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (9, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (9, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (10, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (10, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (10, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (10, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (10, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (10, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (11, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (11, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (11, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (11, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (11, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (11, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (12, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (12, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (12, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (12, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (12, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (12, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (13, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (13, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (13, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (13, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (13, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (13, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (14, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (14, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (14, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (14, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (14, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (14, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (15, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (15, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (15, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (15, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (15, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (15, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (16, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (16, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (16, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (16, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (16, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (16, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (17, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (17, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (17, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (17, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (17, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (17, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (18, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (18, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (18, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (18, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (18, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (18, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (19, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (19, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (19, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (19, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (19, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (19, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (20, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (20, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (20, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (20, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (20, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (20, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (21, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (21, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (21, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (21, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (21, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (21, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (22, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (22, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (22, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (22, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (22, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (22, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (23, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (23, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (23, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (23, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (23, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (23, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (24, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (24, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (24, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (24, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (24, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (24, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (25, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (25, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (25, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (25, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (25, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (25, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (26, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (26, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (26, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (26, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (26, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (26, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (27, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (27, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (27, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (27, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (27, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (27, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (28, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (28, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (28, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (28, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (28, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (28, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (29, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (29, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (29, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (29, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (29, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (29, 6, 'img/hp16.jpg');
INSERT INTO `housepic` VALUES (30, 1, 'img/hp21.jpg');
INSERT INTO `housepic` VALUES (30, 2, 'img/hp22.jpg');
INSERT INTO `housepic` VALUES (30, 3, 'img/hp23.jpg');
INSERT INTO `housepic` VALUES (30, 4, 'img/hp24.jpg');
INSERT INTO `housepic` VALUES (30, 5, 'img/hp25.jpg');
INSERT INTO `housepic` VALUES (30, 6, 'img/hp26.jpg');
INSERT INTO `housepic` VALUES (31, 1, 'img/hp11.jpg');
INSERT INTO `housepic` VALUES (31, 2, 'img/hp12.jpg');
INSERT INTO `housepic` VALUES (31, 3, 'img/hp13.jpg');
INSERT INTO `housepic` VALUES (31, 4, 'img/hp14.jpg');
INSERT INTO `housepic` VALUES (31, 5, 'img/hp15.jpg');
INSERT INTO `housepic` VALUES (31, 6, 'img/hp16.jpg');
-- ----------------------------
-- Table structure for housetype
-- ----------------------------
DROP TABLE IF EXISTS `housetype`;
CREATE TABLE `housetype` (
`htid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '户型编号',
`htname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '户型名称',
PRIMARY KEY (`htid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '户型表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of housetype
-- ----------------------------
INSERT INTO `housetype` VALUES (1, '一室');
INSERT INTO `housetype` VALUES (2, '二室');
INSERT INTO `housetype` VALUES (3, '三室');
INSERT INTO `housetype` VALUES (4, '四室');
INSERT INTO `housetype` VALUES (5, '五室');
INSERT INTO `housetype` VALUES (6, '五室以上');
-- ----------------------------
-- Table structure for houseuse
-- ----------------------------
DROP TABLE IF EXISTS `houseuse`;
CREATE TABLE `houseuse` (
`huid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '房屋用途编号',
`huname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '房屋用途名称',
PRIMARY KEY (`huid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '房屋用途表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of houseuse
-- ----------------------------
INSERT INTO `houseuse` VALUES (1, '普通住宅');
INSERT INTO `houseuse` VALUES (2, '别墅');
INSERT INTO `houseuse` VALUES (3, '其他');
-- ----------------------------
-- Table structure for louceng
-- ----------------------------
DROP TABLE IF EXISTS `louceng`;
CREATE TABLE `louceng` (
`lcid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '楼层id',
`lcname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '楼层名称',
PRIMARY KEY (`lcid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '楼层表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of louceng
-- ----------------------------
INSERT INTO `louceng` VALUES (1, '低楼层');
INSERT INTO `louceng` VALUES (2, '中楼层');
INSERT INTO `louceng` VALUES (3, '高楼层');
-- ----------------------------
-- Table structure for louling
-- ----------------------------
DROP TABLE IF EXISTS `louling`;
CREATE TABLE `louling` (
`llid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '楼龄id',
`llname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '楼龄名称',
PRIMARY KEY (`llid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '楼龄表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of louling
-- ----------------------------
INSERT INTO `louling` VALUES (1, '5年以内');
INSERT INTO `louling` VALUES (2, '10年以内');
INSERT INTO `louling` VALUES (3, '15年以内');
INSERT INTO `louling` VALUES (4, '20年以内');
INSERT INTO `louling` VALUES (5, '20年以上');
-- ----------------------------
-- Table structure for lunbo
-- ----------------------------
DROP TABLE IF EXISTS `lunbo`;
CREATE TABLE `lunbo` (
`sylbimgid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '图片id',
`sylbimgurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图片地址',
PRIMARY KEY (`sylbimgid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '首页轮播表(第二部分)' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of lunbo
-- ----------------------------
INSERT INTO `lunbo` VALUES (1, 'img/lb1');
INSERT INTO `lunbo` VALUES (2, 'img/lb2');
INSERT INTO `lunbo` VALUES (3, 'img/lb3');
INSERT INTO `lunbo` VALUES (4, 'img/lb4');
INSERT INTO `lunbo` VALUES (5, 'img/lb5');
-- ----------------------------
-- Table structure for picinfo
-- ----------------------------
DROP TABLE IF EXISTS `picinfo`;
CREATE TABLE `picinfo` (
`twid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
`twimgurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图标地址',
`twtitle` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标题',
`twsummary` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '简介',
PRIMARY KEY (`twid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图文信息表(第三部)' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of picinfo
-- ----------------------------
INSERT INTO `picinfo` VALUES (1, 'img/pi.png', '选房卡', '需求太多?我们帮您来管理');
INSERT INTO `picinfo` VALUES (2, 'img/pi.png', '地图找房', '更快捷的找房知识');
INSERT INTO `picinfo` VALUES (3, 'img/pi.png', '经纪人', '自己找房太累?不放找个靠谱的经纪人');
INSERT INTO `picinfo` VALUES (4, 'img/pi.png', '业主委托', '放心委托,专业服务');
-- ----------------------------
-- Table structure for street
-- ----------------------------
DROP TABLE IF EXISTS `street`;
CREATE TABLE `street` (
`sid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '街道id',
`sname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '街道名称',
`did` INT(10) NOT NULL COMMENT '区域id',
PRIMARY KEY (`sid`) USING BTREE,
INDEX `fk_did`(`did`) USING BTREE,
CONSTRAINT `fk_did` FOREIGN KEY (`did`) REFERENCES `district` (`did`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB AUTO_INCREMENT = 23 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '街道表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of street
-- ----------------------------
INSERT INTO `street` VALUES (1, '百家湖', 2);
INSERT INTO `street` VALUES (2, '东郊小镇', 2);
INSERT INTO `street` VALUES (3, '建宁路', 3);
INSERT INTO `street` VALUES (4, '凤凰西街', 3);
INSERT INTO `street` VALUES (5, '北京东路', 4);
INSERT INTO `street` VALUES (6, '长江路', 4);
INSERT INTO `street` VALUES (7, '奥体', 5);
INSERT INTO `street` VALUES (8, '万达广场', 5);
INSERT INTO `street` VALUES (9, '常府街', 6);
INSERT INTO `street` VALUES (10, '朝天宫', 6);
INSERT INTO `street` VALUES (11, '马群', 7);
INSERT INTO `street` VALUES (12, '晓庄', 7);
INSERT INTO `street` VALUES (13, '安德门', 8);
INSERT INTO `street` VALUES (14, '铁心桥', 8);
INSERT INTO `street` VALUES (15, '江浦', 9);
INSERT INTO `street` VALUES (16, '泰山', 9);
INSERT INTO `street` VALUES (17, '大厂街道', 10);
INSERT INTO `street` VALUES (18, '葛塘街道', 10);
INSERT INTO `street` VALUES (19, '白马镇', 11);
INSERT INTO `street` VALUES (20, '溧水', 11);
INSERT INTO `street` VALUES (21, '高淳', 12);
INSERT INTO `street` VALUES (22, '滁州', 13);
-- ----------------------------
-- Table structure for totalareasection
-- ----------------------------
DROP TABLE IF EXISTS `totalareasection`;
CREATE TABLE `totalareasection` (
`tasid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '总面积区间编号',
`tasname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '总面积区间名称',
PRIMARY KEY (`tasid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '总面积区间表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of totalareasection
-- ----------------------------
INSERT INTO `totalareasection` VALUES (1, '50㎡以下');
INSERT INTO `totalareasection` VALUES (2, '50-70㎡');
INSERT INTO `totalareasection` VALUES (3, '70-90㎡');
INSERT INTO `totalareasection` VALUES (4, '90-110㎡');
INSERT INTO `totalareasection` VALUES (5, '110-130㎡');
INSERT INTO `totalareasection` VALUES (6, '130-150㎡');
INSERT INTO `totalareasection` VALUES (7, '150-200㎡');
INSERT INTO `totalareasection` VALUES (8, '200㎡以上');
-- ----------------------------
-- Table structure for totalpricesection
-- ----------------------------
DROP TABLE IF EXISTS `totalpricesection`;
CREATE TABLE `totalpricesection` (
`tpsid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '总价区间编号',
`tpsname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '总价区间名称',
PRIMARY KEY (`tpsid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '总价区间表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of totalpricesection
-- ----------------------------
INSERT INTO `totalpricesection` VALUES (1, '80万以下');
INSERT INTO `totalpricesection` VALUES (2, '80-100万');
INSERT INTO `totalpricesection` VALUES (3, '100-120万');
INSERT INTO `totalpricesection` VALUES (4, '120-150万');
INSERT INTO `totalpricesection` VALUES (5, '150-200万');
INSERT INTO `totalpricesection` VALUES (6, '200-300万');
INSERT INTO `totalpricesection` VALUES (7, '300-500万');
INSERT INTO `totalpricesection` VALUES (8, '500万以上');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`phone` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
`password` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
`usertypeid` INT(10) NOT NULL COMMENT '用户类型编号',
`nickname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '昵称',
`email` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
`personimgurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '头像地址',
PRIMARY KEY (`userid`) USING BTREE,
INDEX `fk_usertypeid`(`usertypeid`) USING BTREE,
CONSTRAINT `fk_usertypeid` FOREIGN KEY (`usertypeid`) REFERENCES `usertype` (`usertypeid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '注册和登录(用户表)' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '13003424255', '123456', 1, '小强', '123465@qq.com', 'img/tx1.jpg');
INSERT INTO `user` VALUES (2, '13003424289', '1234567', 1, '小倩', '123@163.com', 'img/tx2.jpg');
INSERT INTO `user` VALUES (3, '13003424789', '123456', 1, '小达', '123@qq.com', 'img/tx3.jpg');
-- ----------------------------
-- Table structure for usertype
-- ----------------------------
DROP TABLE IF EXISTS `usertype`;
CREATE TABLE `usertype` (
`usertypeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '类型编号',
`usertypename` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类型名称',
PRIMARY KEY (`usertypeid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户类型表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of usertype
-- ----------------------------
INSERT INTO `usertype` VALUES (1, '微信用户');
INSERT INTO `usertype` VALUES (2, 'QQ用户');
INSERT INTO `usertype` VALUES (3, '微博用户');
INSERT INTO `usertype` VALUES (4, '手机用户');
-- ----------------------------
-- Table structure for zhongjie
-- ----------------------------
DROP TABLE IF EXISTS `zhongjie`;
CREATE TABLE `zhongjie` (
`zjid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '中介用户编号',
`zjname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`zjphone` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机',
`zjurl` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '头像地址',
`zjwx` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '微信号',
PRIMARY KEY (`zjid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '中介人表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of zhongjie
-- ----------------------------
INSERT INTO `zhongjie` VALUES (1, '顾盼盼', '13003421692', 'img/zj1.jpg', 'fasf313213');
INSERT INTO `zhongjie` VALUES (2, '罗丹', '13003426263', 'img/zj2.jpg', 'fdsfsd1212');
INSERT INTO `zhongjie` VALUES (3, '辛波', '13003424256', 'img/zj3.jpg', 'fdxz123456');
-- ----------------------------
-- Table structure for zhuangxiu
-- ----------------------------
DROP TABLE IF EXISTS `zhuangxiu`;
CREATE TABLE `zhuangxiu` (
`zxid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '装修id',
`zxname` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '装修名称',
PRIMARY KEY (`zxid`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '装修表' ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of zhuangxiu
-- ----------------------------
INSERT INTO `zhuangxiu` VALUES (1, '精装修');
INSERT INTO `zhuangxiu` VALUES (2, '普通装修');
INSERT INTO `zhuangxiu` VALUES (3, '毛坯房');
SET FOREIGN_KEY_CHECKS = 1;
-- 使用表
USE `myhouse`;
-- 查询表
SELECT * FROM `biaoqian`; -- 满二年标签
SELECT * FROM `chaoxiang`; -- 东西南北朝向标签
SELECT * FROM `district`; -- 区级标签
SELECT * FROM `havegoodhouse`; -- 首页推荐
SELECT * FROM `houseinfo`; -- 房源信息
SELECT * FROM `houseknowledge`; -- 新闻
SELECT * FROM `housepic`; -- 图片路径
SELECT * FROM `housetype`; -- 三室标签
SELECT * FROM `houseuse`; -- 住宅别墅标签
SELECT * FROM `louceng`; -- 中底楼层标签
SELECT * FROM `louling`; -- 5年以上房龄标签
SELECT * FROM `lunbo`; -- 轮播图片路径
SELECT * FROM `picinfo`; -- 新闻图片路径
SELECT * FROM `street`; -- 街道标签,外键关联区级标签
SELECT * FROM `totalareasection`; -- 面积标签
SELECT * FROM `totalpricesection`; -- 80万价格标签
SELECT * FROM `user`; -- 用户
SELECT * FROM `usertype`; -- 用户登录方式
SELECT * FROM `zhongjie`; -- 用户头像图片路径
SELECT * FROM `zhuangxiu`; -- 精装毛坯标签
查询表字段
SELECT
h.*, bq.bqname,
cx.cxname,
hp.hpurl,
ht.htname,
lc.lcname,
s.sname,
zx.zxname
FROM
houseinfo h,
biaoqian bq,
chaoxiang cx,
housepic hp,
housetype ht,
louceng lc,
street s,
zhuangxiu zx
WHERE h.bqid = bq.bqid
AND h.cxid = cx.cxid
AND h.hid = hp.hid
AND hpid = 1
AND h.htid = ht.htid
AND h.lcid = lc.lcid
AND h.sid = s.sid
AND h.zxid = zx.zxid
AND h.information;
联合查询表
SELECT
h.*, bq.bqname,
cx.cxname,
hp.hpurl,
ht.htname,
lc.lcname,
s.sname,
zx.zxname
FROM
houseinfo h,
biaoqian bq,
chaoxiang cx,
housepic hp,
housetype ht,
louceng lc,
street s,
zhuangxiu zx
WHERE h.bqid = bq.bqid
AND h.cxid = cx.cxid
AND h.hid = hp.hid
AND hpid = 1
AND h.htid = ht.htid
AND h.lcid = lc.lcid
AND h.sid = s.sid
AND h.zxid = zx.zxid
AND h.information;
——————————————————————————————————————————
底层Model层(Spring容器,pojo持久化类,Dao层的接口Mapper映射,Service层)
导入pom.xml 配置
导入WEB-INF下的web.xml 配置
导入webapp下的前端框架技术文件夹
创建3个文件夹,并Make as
导入resources下的配置文件
创建java下的包 cn.com
创建cn.com下的4个文件夹
pojo下
创建DB表对应实体类,名字,类型都对应
//二手房源信息
public class Houseinfo {
private int hid; //编号
private String information; //标题
private int area;
private int price;
private Date publishtime; //时间
private int personno;
private int dkno;
private int zxid;
private int zjid;
private int bqid;
private int llid;
private int lcid;
private int cxid;
private int sid;
private int htid;
private int huid;
@Override
public String toString() {
return "Houseinfo{" +
"hid=" + hid +
", information='" + information + '\'' +
", area=" + area +
", price=" + price +
", publishtime=" + publishtime +
", personno=" + personno +
", dkno=" + dkno +
", zxid=" + zxid +
", zjid=" + zjid +
", bqid=" + bqid +
", llid=" + llid +
", lcid=" + lcid +
", cxid=" + cxid +
", sid=" + sid +
", htid=" + htid +
", huid=" + huid +
'}';
}
public int getHid() {
return hid;
}
public void setHid(int hid) {
this.hid = hid;
}
public String getInformation() {
return information;
}
public void setInformation(String information) {
this.information = information;
}
public int getArea() {
return area;
}
public void setArea(int area) {
this.area = area;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public Date getPublishtime() {
return publishtime;
}
public void setPublishtime(Date publishtime) {
this.publishtime = publishtime;
}
public int getPersonno() {
return personno;
}
public void setPersonno(int personno) {
this.personno = personno;
}
public int getDkno() {
return dkno;
}
public void setDkno(int dkno) {
this.dkno = dkno;
}
public int getZxid() {
return zxid;
}
public void setZxid(int zxid) {
this.zxid = zxid;
}
public int getZjid() {
return zjid;
}
public void setZjid(int zjid) {
this.zjid = zjid;
}
public int getBqid() {
return bqid;
}
public void setBqid(int bqid) {
this.bqid = bqid;
}
public int getLlid() {
return llid;
}
public void setLlid(int llid) {
this.llid = llid;
}
public int getLcid() {
return lcid;
}
public void setLcid(int lcid) {
this.lcid = lcid;
}
public int getCxid() {
return cxid;
}
public void setCxid(int cxid) {
this.cxid = cxid;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public int getHtid() {
return htid;
}
public void setHtid(int htid) {
this.htid = htid;
}
public int getHuid() {
return huid;
}
public void setHuid(int huid) {
this.huid = huid;
}
}
dao下
创建映射接口
//房源信息
public interface HouseinfoMapper {
//查询表信息
public List getHouseinfo();
//查询全部表信息
public List<Houseinfo> getHouseinfoAll();
//按条件查询表信息
public List getHouseinfoBy(HashMap conditionMap);
}
创建接口映射的SQL查询语句xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.dao.HouseinfoMapper"> <!--本映射器文件路径-->
<!--区级表SQL映射文件-->
<!--查-->
<!--查所有项目组-->
<!--SELECT * FROM `project`;-->
<select id="getHouseinfoAll" resultType="Houseinfo">
SELECT * FROM `houseinfo`;
</select>
<!--多表联查,自动映射-->
<resultMap id="houseinfo" type="HashMap" autoMapping="true">
<id property="hid" column="hid"></id>
</resultMap>
<select id="getHouseinfo" resultMap="houseinfo">
SELECT h.*,bq.`bqname`,cx.`cxname`,hp.`hpurl`,ht.`htname`,lc.`lcname`,s.`sname`,zx.`zxname`
FROM `houseinfo` h,`biaoqian` bq,`chaoxiang` cx,`housepic` hp,`housetype` ht,`louceng` lc,`street` s,`zhuangxiu` zx
WHERE h.`bqid` = bq.`bqid` AND h.`cxid` = cx.`cxid` AND h.`hid` = hp.`hid` AND hpid = 1 AND h.`htid` = ht.`htid`
AND h.`lcid` = lc.`lcid` AND h.`sid` = s.`sid` AND h.`zxid` = zx.`zxid` AND h.information LIKE "%"
</select>
<!--根据筛选条件查询-->
<select id="getHouseinfoBy" resultMap="houseinfo" parameterType="HashMap">
SELECT h.*,bq.`bqname`,cx.`cxname`,hp.`hpurl`,ht.`htname`,lc.`lcname`,s.`sname`,zx.`zxname`
FROM `houseinfo` h,`biaoqian` bq,`chaoxiang` cx,`housepic` hp,`housetype` ht,`louceng` lc,`street` s,`zhuangxiu`
zx,district d
WHERE h.`bqid` = bq.`bqid` AND h.`cxid` = cx.`cxid` AND h.`hid` = hp.`hid` AND hpid = 1 AND h.`htid` = ht.`htid`
AND h.`lcid` = lc.`lcid` AND h.`sid` = s.`sid` AND h.`zxid` = zx.`zxid` AND d.`did` = s.`did`
<if test="dname != null and dname !=''">
and d.`dname` = #{dname}
</if>
<if test="tpsname != null and tpsname !=''">
and h.`price` BETWEEN #{minPrice} and #{maxPrice}
</if>
<if test="tasname != null and tasname !=''">
and h.`area` BETWEEN #{minArea} and #{maxArea}
</if>
</select>
</mapper>
service下
创建service层类,注入Dao层接口
//房源信息
@Service
public class HouseinfoService implements HouseinfoMapper{
//注入Dao层接口
@Autowired
private HouseinfoMapper houseinfoMapper;
//返回Dao接口方法
//查询表全部数据
@Override
public List getHouseinfo() {
return houseinfoMapper.getHouseinfo();
}
//查询表全部数据
public List<Houseinfo> getHouseinfoAll() {
return houseinfoMapper.getHouseinfoAll();
}
//按条件查询
public List getHouseinfoBy(HashMap conditionMap){
return houseinfoMapper.getHouseinfoBy(conditionMap);
}
}
controller下
创建controller层控制类,注入Service层类
//房源信息
@Controller
public class HouseinfoController {
//引入Service层接口
@Autowired
private HouseinfoService houseinfoService;
//使用Ajax的回调
//查询首页推荐表信息
@RequestMapping("/getHouseinfoAll")
@ResponseBody
public List<Houseinfo> getHouseinfoAll(){
List<Houseinfo> houseinfos = houseinfoService.getHouseinfo();
System.out.println("进入HouseinfoController,getHouseinfoAll,查询房源表方法!"+houseinfos);
return houseinfos;
}
//使用Ajax的回调
//分页查询房源信息
@RequestMapping(value = "/getHouseinfo")
@ResponseBody //返回自己页面
public HashMap getHouseinfo(
@RequestParam(value = "pagenum",required = false,defaultValue = "1") int pageNum){
System.out.println("进入HouseinfoController,getHouseinfoMap,分页查询房源信息方法!");
//创建HashMap对象
HashMap hashMap = new HashMap();
//封装PageHelper,每页显示3个
PageHelper.startPage(pageNum,5);
//查询集合,调用Service层方法
List houseinfos = houseinfoService.getHouseinfo();
//把集合封装进PageInfo
PageInfo pageInfo = new PageInfo(houseinfos);
//把结果集合和分页封装HashMap对象
hashMap.put("houseinfo",houseinfos);
hashMap.put("pageInfo",pageInfo);
System.out.println("封装完成! return hashMap");
/* Set keys = hashMap.keySet();
Iterator iterator=keys.iterator();
while (iterator.hasNext()){
String key=(String)iterator.next();
String value=(String)hashMap.get(key);
System.out.println(key+"+++++"+value);
}*/
//返回值是hashMap对象
return hashMap;
}
//使用Ajax的回调
//条件查询房源信息
@RequestMapping(value = "/getHouseinfoBy")
@ResponseBody //返回自己页面
public HashMap getHouseinfoBy(@RequestParam(value = "pagenum",required = false,defaultValue = "1") int pageNum,
@RequestParam(value = "names",required = false,defaultValue = "") String[] names,
@RequestParam(value = "values",required = false,defaultValue = "") String[] values) {
//条件HashMap集合
HashMap conditionMap = new HashMap();
for (int i = 0; i < names.length; i++) {
conditionMap.put(names[i], values[i]);
}
//判断条件的集合各种包含那几个选项 选项-区域 面积 价格...
//判断是否包含价格
if (conditionMap.containsKey("tpsname")){
System.out.println("判断是否包含价格");
//判断是否区间值
String price = (String)conditionMap.get("tpsname");
if((price.indexOf("-"))!=-1) {
//是区间值 80-100cm berween 80 and 100
String[] arr = price.split("-");
//最小值
int minPrice = Integer.parseInt(arr[0]);
//最大值
String[] arr2 = arr[1].split("万");
int maxPrice = Integer.parseInt(arr2[0]);
//将最大和最小值存放到条件集合中
conditionMap.put("minPrice",minPrice);
conditionMap.put(("maxPrice"),maxPrice);
}else{
//不是区间
if (price.indexOf("万以上")!=-1){
//对50万以下的处理
String[] arr = price.split("万");
int maxPrice = Integer.parseInt(arr[0]);
int minPrice = 0;
//将最大和最小值存放到条件集合中
conditionMap.put("minPrice",minPrice);
conditionMap.put(("maxPrice"),maxPrice);
}else{
//对500万以上的处理
String[] arr = price.split("万");
int minPrice = Integer.parseInt(arr[0]);
int maxPrice = 1000;
//将最大和最小值存放到条件集合中
conditionMap.put("minPrice",minPrice);
conditionMap.put(("maxPrice"),maxPrice);
}
}
}
//判断是否包含面积
if (conditionMap.containsKey("tasname")){
System.out.println("判断是否包含面积!");
//判断是否区间值
String area = (String)conditionMap.get("tasname");
if(area.indexOf("-")!=-1) {
//是区间值 80-100cm berween 80 and 100
String[] arr = area.split("-");
//最小值
int minArea = Integer.parseInt(arr[0]);
//最大值
String[] arr2 = arr[1].split("㎡");
int maxArea = Integer.parseInt(arr2[0]);
//将最大和最小值存放到条件集合中
conditionMap.put("minArea",minArea);
conditionMap.put(("maxArea"),maxArea);
}else{
//不是区间
if ((area.indexOf("以上"))!=-1){
String str = "";
//对于200m以上
for(int i = 0;i<area.length();i++){
if(area.charAt(i)>=48 && area.charAt(i)<=57){
str+=area.charAt(i);
}
}
int minArea = Integer.parseInt(str);
//将最大和最小值存放到条件集合中
conditionMap.put("minArea",minArea);
conditionMap.put(("maxArea"),1000);
}else{
//对50m²以下的处理
String[] arr = area.split("㎡");
int maxArea = Integer.parseInt(arr[0]);
int minArea = 0;
//将最大和最小值存放到条件集合中
conditionMap.put("minArea",minArea);
conditionMap.put(("maxArea"),maxArea);
}
}
}
HashMap hashMap = new HashMap();
PageHelper.startPage(pageNum,5);
List houseinfos = houseinfoService.getHouseinfoBy(conditionMap);
PageInfo pageInfo = new PageInfo(houseinfos);
//把结果集合和分页封装HashMap对象
hashMap.put("houseinfo",houseinfos);
hashMap.put("pageInfo",pageInfo);
System.out.println("条件筛选封装完成! return hashMap");
return hashMap;
}
}