MySql数据库--第四章：高级查询（二）

EXISTS 和 NOT EXISTS子查询

#在执行 create 或drop 语句前，可以先使用exists语句判断该数据库对象是否存在，返回值是true或者false
语法：

SELECT ... FROM 表名 WHERE EXISTS(子查询)；

NOT EXISTS 表示取反

我的住房网 数据设计

CREATE DATABASE house;

#1.用户表添加数据

INSERT INTO sys_user(uName,upassword)VALUES('张三','zs123'),('李四','ls123'),('王五','ww123')

#2.区县表添加数据

INSERT INTO hos_district(dName) VALUES('洪山'),('武昌'),('汉口'),('汉阳');

#3.房屋类型表添加数据

INSERT INTO hos_type(htName) VALUES('一室一厅'),('两室一厅'),('三室一厅'),('两室两厅'),('三室两厅');

#4.街道表添加数据

INSERT INTO hos_street(sName,sDid) VALUES('八一路',1),('珞瑜路',2),('雄楚大道',1),('解放大道',3);

#5.房屋信息表添加数据

INSERT INTO hos_house(uid,sid,hTid,price,topic,contents,htime,copy) 
		VALUES(1,1,1,1000,'地铁上盖','交通方便,拎包入住,家电齐全。。。','2021-03-03','精装修'),
			(2,2,1,1200,'一线望江','位置极好,交通便利','2021-03-01','简装修'),
			(3,1,2,1800,'宽敞大房','双阳台，采光好','2021-02-28','精装修');

#查询输出第2条~第3条出租房屋信息 共两条记录：显示用户的姓名，街道名称，房屋类型，房屋价格，标题
#第一种方法 where 子句

SELECT h.hMid AS 房屋编号,u.uName AS 客户姓名,s.sName AS 街道名称,t.htName AS 房屋类型,price AS 租金,topic AS 标题
FROM hos_house AS h,sys_user AS u,hos_street AS s,hos_type AS t
WHERE h.uId=u.uid AND h.sid=s.sid AND h.hTid=t.hTid
LIMIT 1,2;

#第二种方法：内连接 inner join

SELECT h.hMid AS 房屋编号,u.uName AS 客户姓名,s.sName AS 街道名称,t.htName AS 房屋类型,price AS 租金,topic AS 标题
FROM hos_house AS h
INNER JOIN sys_user AS u ON h.uId=u.uid
INNER JOIN hos_street AS s ON h.sid=s.sid
INNER JOIN hos_type AS t ON h.hTid=t.hTid
LIMIT 1,2;

使用临时表保存查询结果（临时表只在当前连接可用，当这个连接关闭时，会自动删除，不占用数据库空间）

#CREATE TEMPORARY TABLE 表名(查询语句)；

CREATE TEMPORARY TABLE result_house(SELECT h.hMid AS 房屋编号,u.uName AS 客户姓名,s.sName AS 街道名称,t.htName AS 房屋类型,price AS 租金,topic AS 标题

FROM hos_house AS h
INNER JOIN sys_user AS u ON h.uId=u.uid
INNER JOIN hos_street AS s ON h.sid=s.sid
INNER JOIN hos_type AS t ON h.hTid=t.hTid
LIMIT 1,2);