MySQL[练习|面试题]-我的租房网

# 创建数据库
CREATE DATABASE Tenement;

# 切换数据库
USE Tenement;
# 创建客户信息表 sys_user
CREATE TABLE sys_user(
  uid INT PRIMARY KEY AUTO_INCREMENT,
  uName VARCHAR(20) NOT NULL,
  uPassWord VARCHAR(20)
);
DESC sys_user;

# 创建区县信息表
CREATE TABLE IF NOT EXISTS hos_district(
  did INT PRIMARY KEY AUTO_INCREMENT,
  dName VARCHAR(50) NOT NULL
);
DESC hos_district;

# 创建街道信息表
CREATE TABLE IF NOT EXISTS hos_street(
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sName VARCHAR(50) NOT NULL,
  sDid INT NOT NULL
); 
DESC hos_street;
# 为sDid 字段添加外键
ALTER TABLE hos_street ADD CONSTRAINT
FOREIGN KEY(sDid)
REFERENCES hos_district(did);

# -----创建房屋类型表------
CREATE TABLE IF NOT EXISTS hos_type(
  hTid INT PRIMARY KEY AUTO_INCREMENT,
  htName VARCHAR(20) NOT NULL 
);
DESC hos_type;

# ------创建出租房信息表--------
CREATE TABLE IF NOT EXISTS hos_house(
  hMid INT PRIMARY KEY AUTO_INCREMENT,
  uid INT NOT NULL,
  sid INT NOT NULL,
  hTid INT NOT NULL,
  price DECIMAL DEFAULT 0 NOT NULL,
  topic VARCHAR(50) NOT NULL,
  contents VARCHAR(255) NOT NULL
);
ALTER TABLE hos_house ADD hTime DATETIME ;
ALTER TABLE hos_house ADD copy VARCHAR(50);

DESC hos_house;
# 为hos_house表添加主外键
ALTER TABLE hos_house ADD CONSTRAINT
FOREIGN KEY (uid)
REFERENCES sys_user(uid);

ALTER TABLE hos_house ADD CONSTRAINT
FOREIGN KEY(sid)
REFERENCES hos_street(sid);

ALTER TABLE hos_house ADD CONSTRAINT 
FOREIGN KEY(hTid)
REFERENCES hos_type(hTid);

 /*
 *客户信息表
 */

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');
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_user(UNAME,UPASSWORD) VALUES('蒋连昌','s217710');
/*
*区县信息
*/

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) VALUES('宣武区');
INSERT INTO hos_district (DNAME) VALUES('大兴');
INSERT INTO hos_district (DNAME) VALUES('丰台');
/*
*街道信息表
*/

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);
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 INTO 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);
/*
*房屋类型表
*/

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('三室一厅');
INSERT INTO hos_type (HTNAME) VALUES('三室两厅');
/*
*出租房屋信息表
*/

INSERT INTO hos_house(UID,SID,HTID,PRICE,TOPIC,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_house(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,'中关村','中关村电脑城','2009-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,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,CONTENTS,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','苏州街');

# 上机4 分页显示查询出租房屋信息 并用 临时表存放
CREATE TABLE hos_houseTemp
SELECT * FROM hos_house
LIMIT 5,5;

SELECT * FROM hos_houseTemp;



# 上机5 查询张三所发布的房屋信息,显示房屋分布的街道和区县
SELECT d.`dName` AS 区县,s.`sName` AS 街道,h.htid AS 户型,
h.`price` AS 价格,h.`topic` AS 标题,h.`contents` AS 内容,h.`hTime` AS 时间,h.copy AS 备注
FROM hos_house h 
INNER JOIN hos_street s ON s.`sid`=h.`sid`
INNER JOIN hos_district d ON d.`did`=s.`sDid`
WHERE h.uid = (
  SELECT uid 
  FROM sys_user WHERE uName='张三'
);

# 另一种做法
SELECT *
FROM hos_house WHERE uid = (
  SELECT uid FROM sys_user
  WHERE uname='张三'
)
# 子查询前置 生成一个查询结果表 然后再进行多表查询
# 这样优化查询速度

SELECT d.`dName` AS 区县,s.`sName` AS 街道,h.htid AS 户型,
     h.`price` AS 价格,h.`topic` AS 标题,h.`contents` AS 内容,h.`hTime` AS 时间,h.copy AS 备注
FROM (
  SELECT *
  FROM hos_house WHERE uid = (
    SELECT uid FROM sys_user
    WHERE uname='张三'
  )
) AS h INNER JOIN hos_street s ON h.sid = s.sid
INNER JOIN hos_district d ON s.sdid = d.did


# 上机6 根据户型和房屋所在区县和街道,为至少有两个街道有出租房屋的区县制作出租房屋清单
SELECT t.htName AS 户型,u.uName AS 姓名,d.dName AS 区县,s.sName AS 街道名
FROM hos_house h 
INNER JOIN hos_type t ON h.hTid=t.hTid
INNER JOIN sys_user u ON h.uid = u.uid
INNER JOIN hos_street s ON h.sid=s.sid
INNER JOIN hos_district d ON s.sDid = d.did
WHERE d.did IN (
  SELECT d.did
  FROM hos_house h  INNER JOIN hos_street s ON h.sid=s.sid
  INNER JOIN hos_district d ON s.sDid = d.did
  GROUP BY d.did
  HAVING COUNT(DISTINCT s.sid)>1
)
ORDER BY d.dName


# 上机7 房屋出租统计合并
SELECT 1 AS 季度,'合计' AS 区县,' ' AS 街道 ,' ' AS 户型,COUNT(*) AS 房屋数量
FROM hos_house h
WHERE MONTH(htime)>=1 AND MONTH(htime)<=3
UNION
SELECT 1 AS 季度,d.dName AS 区县,' ' AS 街道 ,' ' AS 户型,COUNT(*) AS 房屋数量
FROM hos_house h INNER JOIN hos_street s ON h.sid=s.sid
INNER JOIN hos_district d ON s.sdid=d.did
WHERE MONTH(htime)>=1 AND MONTH(htime)<=3
GROUP BY d.did
UNION
SELECT 1 AS 季度,d.dName AS 区县,s.sname AS 街道 ,t.htName AS 户型,COUNT(*) AS 房屋数量
FROM hos_house h INNER JOIN hos_street s ON h.sid=s.sid
INNER JOIN hos_district d ON s.sdid=d.did
INNER JOIN hos_type t ON h.htid=t.htid
WHERE MONTH(htime)>=1 AND MONTH(htime)<=3
GROUP BY d.did,s.sid,t.htid

#优化 不能一个季度一个季度的合并
# 直接使用QUARTER()季度函数
SELECT QUARTER(htime) AS 季度,'合计' AS 区县,' ' AS 街道 ,' ' AS 户型, COUNT(*) AS 房屋数量
FROM hos_house
GROUP BY QUARTER(htime)
UNION
SELECT QUARTER(htime) AS 季度,d.dName AS 区县,' ' AS 街道 ,' ' AS 户型, COUNT(*) AS 房屋数量
FROM hos_house h,hos_street s,hos_district d
WHERE h.sid =s.sid AND s.sdid=d.did
GROUP BY QUARTER(htime),d.dname
UNION
SELECT QUARTER(htime) AS 季度,d.dName AS 区县,s.sname AS 街道 ,t.htName AS 户型, COUNT(*) AS 房屋数量
FROM hos_house h,hos_street s,hos_district d,hos_type t
WHERE h.sid =s.sid AND s.sdid=d.did AND h.htid = t.htid
GROUP BY QUARTER(htime),d.dname,s.sname
ORDER BY 季度,区县,街道