高级查询练习 MySQL数据库05 _出租

高级查询练习
(添加一些练习数据)
我的租房网
客户信息表
CREATE TABLE `sys_user` (`UID` INT(4) NOT NULL AUTO_INCREMENT,`uName` VARCHAR(10) NOT NULL,`uPassWord` VARCHAR(20) DEFAULT NULL,PRIMARY KEY (`UID`)) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;INSERTINTO `sys_user`(`UID`,`uName`,`uPassWord`) VALUES (1,'张三','11111');
执行命令得
区县信息表
DROP TABLE IF EXISTS `hos_district`;CREATE TABLE `hos_district` (`DID` INT(4) NOT NULL AUTO_INCREMENT,`dName` VARCHAR(4) NOT NULL,PRIMARY KEY (`DID`)) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERTINTO `hos_district`(`DID`,`dName`) VALUES (1,'海淀'),(2,'朝阳'),(3,'丰台');
执行命令得
街道信息表
DROP TABLE IF EXISTS `hos_street`;CREATE TABLE `hos_street` (`SID` INT(4) NOT NULL AUTO_INCREMENT,`sName` VARCHAR(50) NOT NULL,`sDID` INT(4) NOT NULL,PRIMARY KEY (`SID`),KEY `fk_street_district` (`sDID`),CONSTRAINT `fk_street_district` FOREIGN KEY (`sDID`) REFERENCES `hos_district` (`DID`)) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;/*Data for the table `hos_street` */INSERTINTO `hos_street`(`SID`,`sName`,`sDID`) VALUES (1,'万泉庄',1),(2,'苏州街',1),(3,'中关村',1),(4,'东四',2),(5,'三里屯',2),(6,'望京',2);
执行命令得

文章插图
房屋类型表
【高级查询练习MySQL数据库05】

 DROP TABLE IF EXISTS `hos_type`;CREATE TABLE `hos_type` (`hTID` INT(4) NOT NULL AUTO_INCREMENT,`hTName` VARCHAR(10) NOT NULL,PRIMARY KEY (`hTID`)) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*Data for the table `hos_type` */INSERTINTO `hos_type`(`hTID`,`hTName`) VALUES (1,'一室一厅'),(2,'两室一厅'),(3,'三室一厅');

执行命令得
出租房屋信息表

 DROP TABLE IF EXISTS `hos_house`;CREATE TABLE `hos_house` (`hMID` INT(4) NOT NULL AUTO_INCREMENT,`UID` INT(4) NOT NULL,`SID` INT(4) NOT NULL,`hTID` INT(4) NOT NULL,`price` DECIMAL(10,2) DEFAULT '0.00',`topic` VARCHAR(50) NOT NULL,`contents` VARCHAR(255) NOT NULL,`hTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,`copy` VARCHAR(500) DEFAULT NULL,PRIMARY KEY (`hMID`),KEY `fk_house_district` (`UID`),KEY `fk_house_street` (`SID`),KEY `fk_house_type` (`hTID`),CONSTRAINT `fk_house_district` FOREIGN KEY (`UID`) REFERENCES `sys_user` (`UID`),CONSTRAINT `fk_house_street` FOREIGN KEY (`SID`) REFERENCES `hos_street` (`SID`),CONSTRAINT `fk_house_type` FOREIGN KEY (`hTID`) REFERENCES `hos_type` (`hTID`)) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;/*Data for the table `hos_house` */INSERTINTO `hos_house`(`hMID`,`UID`,`SID`,`hTID`,`price`,`topic`,`contents`,`hTime`,`copy`) VALUES (1,1,1,1,'800.00','万泉庄','万泉庄一号','2016-12-14 17:03:55',NULL),(2,1,1,2,'800.00','万泉庄','万泉庄二号','2016-04-14 17:03:55',NULL),(3,1,2,1,'800.00','苏州街','苏州街一号','2016-01-14 17:03:55',NULL),(4,1,2,3,'800.00','苏州街','苏州街二号','2016-09-14 17:03:55',NULL),(5,1,3,1,'800.00','中关村','中关村一号','2016-10-14 17:03:55',NULL),(6,1,3,3,'800.00','中关村','中关村二号','2016-03-14 17:03:55',NULL),(7,1,4,2,'800.00','东四','东四二号','2016-09-14 17:03:55',NULL),(8,1,4,1,'800.00','东四','东四一号','2016-11-14 17:03:55',NULL),(9,1,4,3,'800.00','东四','东四三号','2016-09-14 17:03:55',NULL);

执行命令得

文章插图
1.临时表
MySQL临时表在我们需要保存一些临时数据时是非常有用的.临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间.
语法:
TABLE 表名(
....)
例:使用LIMIT关键字实现查询数据分页显示
使用临时表保存临时的查询结果
查询输出第6条~第10条出尊房屋信息

 CREATE TEMPORARY TABLE lin(#创建一个临时表linSELECT * FROM hos_house#获取hos_house表的全部信息LIMIT 5,5#显示第6条~第10条)SELECT * FROM lin

当使用SHOW 命令显示数据表列表时,你将无法看到lin表.如果推出MySQL会话,再使用命令来读取原先创建的临时表数据,会发现数据库中没有该表存在,因为退出时该临时表已经被销毁了
2.查询指定客户发布的出租房屋信息
需求说明:查询张三发布的所有出租房屋信息,并显示房屋分布的街道和区县
提示:结果数据来源于出租房屋信息表客户信息表区县信息表街道信息表
使用连接查询和子查询两种方式关联多表数据实现

 #连接查询SELECT `dName`,`sName`,h.*#获取出租房屋所有信息街道和区县信息FROM `hos_house` AS hINNER JOIN `sys_user` AS u ON u.UID=h.UIDINNER JOIN `hos_street` AS s ON s.SID=h.SIDINNER JOIN `hos_district` AS d ON d.DID=s.`sDID`#通过内连接4表连查分别将关联字段匹配WHERE u.`uName`='张三'#条件为张三发布#子查询SELECT (SELECT `dName` FROM `hos_district` AS d WHERE d.`DID`=s.`sDID` ),`sName`, h.*FROM `hos_house` AS hINNER JOIN `hos_street` AS s ON s.`SID`=h.`SID`WHERE h.`UID`=(SELECT `UID` FROM `sys_user` WHERE `uName`='张三')

执行命令得

文章插图
3.按曲线制作房屋出租清单
需求说明 :
根据户型和房屋所在区县和街道,为至少有两个街道有出租房屋的区县制作出租房屋清单
提示
先用子句筛选出接到数量大于1的区县

 SELECT `hTName`,`uName`,`dName`,`sName`#获取租房信息FROM `hos_house` AS hINNER JOIN `hos_type` AS t ON t.`hTID`=h.`hTID`INNER JOIN `hos_street` AS s ON s.`SID`=h.`SID`INNER JOIN `hos_district` AS d ON d.`DID`=s.`sDID`INNER JOIN `sys_user` AS u ON u.uid=h.`UID`#四表连查关系匹配WHERE d.`DID` IN (SELECT sDID FROM `hos_street`#获取所有街道所在区县WHERE SID IN( #条件 这些街道均在出租房屋信息中存在SELECT SIDFROM `hos_house`GROUP BY `SID`#)GROUP BY sdidHAVING COUNT(*)>1#至少有两个街道)

解释:
步骤1:列出大框架四表内连接
步骤2:先获取所有街道均属于哪个区结果为 :1区3条街道 2区 3条街道
步骤3:通过子查询获取出租房屋信息中存在哪些街道,结果为:(1,2,3,4号街道)
步骤4:筛选插入自查询和语句一区1,2,3号街道均有出租房二区4号街道有出租房5,6接到没有不符合题意至少两条街道有出租房所以排除答案只有一区
执行命令得

文章插图
4.按季度统计本年发布的房屋出租数量
需求说明:
按季度统计出本年各区县各街道各种户型房屋出租数量
输出2016年从1月1日起至今的全部出租房屋数量,各区县出租房屋数量及各街道户型出租房屋数量

 SELECT QUARTER(`hTime`) AS 季度,' 合计'AS 区县,'' AS 街道,'' AS 户型,COUNT(*) AS 出租数量FROM `hos_house`GROUP BY 季度UNIONSELECT QUARTER(`hTime`) AS 季度,dname AS 区县,' 小计' AS 街道,'' AS 户型,COUNT(*) AS 出租数量FROM `hos_house` AS hINNER JOIN hos_street AS s ON s.sid =h.sidINNER JOIN hos_district AS d ONd.did=s.sdidGROUP BY 季度,d.didUNION SELECT QUARTER(`hTime`) AS 季度,dname AS 区县,sname AS 街道,htname AS 户型,COUNT(*) AS 出租数量FROM `hos_house` AS hINNER JOIN hos_street AS s ON s.sid =h.sidINNER JOIN hos_district AS d ONd.did=s.sdidINNER JOIN hos_type AS t ON t.htid=h.htidGROUP BY 季度,d.did,s.sid,t.htidORDER BY 1,2,3,4

执行命令得

文章插图