(添加一些练习数据)
我的租房网
客户信息表
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; INSERT INTO `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; INSERT INTO `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` */ INSERT INTO `hos_street`(`SID`,`sName`,`sDID`) VALUES (1,'万泉庄',1),(2,'苏州街',1),(3,'中关村',1),(4,'东四',2),(5,'三里屯',2),(6,'望京',2);执行命令得
房屋类型表
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` */ INSERT INTO `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` */ INSERT INTO `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会自动删除表并释放所有空间.
语法:
CREATE TEMPORARY TABLE 表名(
....)
例:使用LIMIT关键字实现查询数据分页显示
使用临时表保存临时的查询结果
查询输出第6条~第10条出尊房屋信息
CREATE TEMPORARY TABLE lin(#创建一个临时表lin SELECT * FROM hos_house#获取hos_house表的全部信息 LIMIT 5,5#显示第6条~第10条 ) SELECT * FROM lin当使用SHOW TABLES命令显示数据表列表时,你将无法看到lin表.如果推出MySQL会话,再使用select命令来读取原先创建的临时表数据,会发现数据库中没有该表存在,因为退出时该临时表已经被销毁了
2.查询指定客户发布的出租房屋信息
需求说明:查询张三发布的所有出租房屋信息,并显示房屋分布的街道和区县
提示:结果数据来源于出租房屋信息表 客户信息表 区县信息表 街道信息表
使用连接查询和子查询两种方式关联多表数据实现
#连接查询 SELECT `dName`,`sName`,h.*#获取出租房屋所有信息 街道和区县信息 FROM `hos_house` AS h INNER JOIN `sys_user` AS u ON u.UID=h.UID INNER JOIN `hos_street` AS s ON s.SID=h.SID INNER 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 h INNER JOIN `hos_street` AS s ON s.`SID`=h.`SID` WHERE h.`UID`=( SELECT `UID` FROM `sys_user` WHERE `uName`='张三' )执行命令得
3.按曲线制作房屋出租清单
需求说明 :
根据户型和房屋所在区县和街道,为至少有两个街道有出租房屋的区县制作出租房屋清单
提示
先用HAVING子句筛选出接到数量大于1的区县
SELECT `hTName`,`uName`,`dName`,`sName`#获取租房信息 FROM `hos_house` AS h INNER 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 SID FROM `hos_house` GROUP BY `SID`# ) GROUP BY sdid HAVING COUNT(*)>1#至少有两个街道 )解释:
步骤1:列出大框架四表内连接
步骤2:先获取所有街道均属于哪个区 结果为 :1区3条街道 2区 3条街道
步骤3:通过子查询获取出租房屋信息中存在哪些街道,结果为:(1,2,3,4号街道)
步骤4:筛选插入自查询和having语句 一区1,2,3号街道均有出租房 二区4号街道有出租房5,6接到没有不符合题意至少两条街道有出租房所以排除 答案只有一区
执行命令得
4.按季度统计本年发布的房屋出租数量
需求说明:
按季度统计出本年各区县各街道各种户型房屋出租数量
输出2016年从1月1日起至今的全部出租房屋数量,各区县出租房屋数量及各街道 户型出租房屋数量
SELECT QUARTER(`hTime`) AS 季度,' 合计'AS 区县,'' AS 街道,'' AS 户型,COUNT(*) AS 出租数量 FROM `hos_house` GROUP BY 季度 UNION SELECT QUARTER(`hTime`) AS 季度,dname AS 区县,' 小计' AS 街道,'' AS 户型,COUNT(*) AS 出租数量 FROM `hos_house` AS h INNER JOIN hos_street AS s ON s.sid =h.sid INNER JOIN hos_district AS d ON d.did=s.sdid GROUP BY 季度,d.did UNION SELECT QUARTER(`hTime`) AS 季度,dname AS 区县,sname AS 街道,htname AS 户型,COUNT(*) AS 出租数量 FROM `hos_house` AS h INNER JOIN hos_street AS s ON s.sid =h.sid INNER JOIN hos_district AS d ON d.did=s.sdid INNER JOIN hos_type AS t ON t.htid=h.htid GROUP BY 季度,d.did,s.sid,t.htid ORDER BY 1,2,3,4执行命令得
