mysql怎么双库查询 mysql双表联查

备注：所有查询基于前面章节中建立的中国数据库

/*联合查询*/
-- union
(select CityID,CityName from t_city where CityID between 6 and 10)
union
(select ProName,ProID from t_province where ProID between 6 and 10);


--查询中国共有多少地级市
select count(*) from t_city;

--查询河北省有多少地级市
select count(*) from t_city where ProID = (
  select ProID from t_province where ProName = '河北省'
);

--统计各省地级市的数量
select ProID,count(CityID) from t_city
group by ProID;

--求每个省份中最大的城市ID
select ProID,max(CityID) from t_city
group by ProID;

--地级市最多的省份取前10名
select ProID,count(CityID) as cityCount from t_city
group by ProID
order by cityCount desc
limit 10;

--查询拥有区县最多的城市的前10名
select CityID,count(Id) as disCount from t_district
group by CityID
order by disCount desc
limit 10;


--查询拥有20个以上区县的城市
select CityID,count(Id) as disCount from t_district
group by CityID
having disCount > 19
order by disCount desc;

--打出拥有20个以上区县的城市名字
-- 子查询实现
select * from t_city where CityID
in
(
select CityID from
(
select CityID,count(Id) as disCount from t_district
group by CityID
having disCount > 19
order by disCount desc
)fuck
);

--打出拥有20个以上区县的城市名字
-- join
--内连接：基于左右两表共有的ProID为样本进行查询
--左/右连接：基于【左右两表共有的ProID】+【左/右表独有的ProID】为样本进行查询
select td.CityID,tc.CityName,count(td.Id) discount
from t_district td join t_city tc on td.CityID=tc.CityID
group by CityID
order by discount desc limit 20;




/*
insert into t_district(DisName,CityID)
values
('上帝区',400),
('小鬼区',400),
('妖精区',400);
insert into t_city(CityName,CityID)
values
('你妹市',373);
*/
select tc.CityID,tc.CityName,count(td.Id) discount
from t_district td left join t_city tc on td.CityID=tc.CityID
group by td.CityID
order by tc.CityID desc;


--北京所有的区县
select * from t_district where CityID = (
  select CityID from t_city where CityName = '北京市'
);

select td.CityID,DisName,CityName
from t_district td join t_city tc on td.CityID = tc.CityID
where CityName = "北京市";


--北京有多少区县
/*select count(CityID) from t_district where CityID = (
  select CityID from t_city where CityName = '北京市'
);*/
select count(td.DisName)
from t_district td join t_city tc on td.CityID = tc.CityID
where tc.CityName =  "北京市";

--找出地级市最多的省份
select tc.ProID,count(CityID) cc,ProName
from t_city tc join t_province tp on tc.ProID = tp.ProID
group by tc.ProID
order by cc desc limit 1;


--区县最多的城市是哪个省的什么市，查询结果包含省名、市名、区县数量；


--现有最宜居城市排行榜如下：("宁波市","银川市","宜春市","宜昌市","咸阳市","芜湖市","泰州市","秦皇岛市","南通市","南京市","昆明市","桂林市","丹东市","大连市","长沙市","包头市","遂宁市","绵阳市","河州市")
--求哪个省拥有最多的宜居城市，各有几个？
-- 你家乡所在的省份拥有哪些宜居城市
select ProName,CityName
from t_city tc join t_province tp on tc.ProID = tp.ProID
where CityName in ("宁波市","银川市","哈尔滨市","宜春市","宜昌市","咸阳市","芜湖市","泰州市","绥芬河市","秦皇岛市","南通市","南京市","昆明市","桂林市","丹东市","大连市","长沙市","包头市","遂宁市","绵阳市","河州市")
and ProName = '黑龙江省';

--查询哪个城市拥有最多的“旗”？
select fuck.CityID,CityName,count(fuck.DisName) cfd
from(
select * from t_district where DisName like '%旗'
)fuck join t_city on fuck.CityID = t_city.CityID
group by fuck.CityID
order by cfd desc limit 1;


--查询省级行政区有哪几种？
select distinct ProRemark from t_province;

--查询全国有多少县级市？
select count(*) from t_district where DisName like '%市';

-- 查询叫X县的地级市
select * from t_city where CityName like '%县';

--安徽的县级市数量
select ProName,CityName,DisName from
(t_district td join t_city tc on td.CityID = tc.CityID
join t_province tp on tc.ProID = tp.ProID)
where tp.ProName = '安徽省' and DisName like '%市';

--哪个省的县级市最多？
select tp.ProName,count(td.DisName) cd
from t_province tp join t_city tc on tp.ProID = tc.ProID join t_district td on tc.CityID = td.CityID
where td.DisName like '%市'
group by tp.ProID
order by cd desc;