子查询
1.用在where和having后面的子查询
单行子查询
查询人口比俄罗斯多的国家
#先查询俄罗斯的人口
select name,population from country where name like '%Russian%';
#将俄罗斯的人口数值作为比较值放入语句中
select * from country where
population >
(select population from country where name like '%Russian%');
多个条件
查询预期寿命和GNP比中国高的国家名称及其GNP和预期寿命
SELECT name, gnp, lifeexpectancy
FROM country WHERE
gnp > (SELECT gnp FROM country WHERE name = 'CHina')
AND lifeexpectancy > (SELECT lifeexpectancy FROM country WHERE name = 'CHina');
having子句中含有子查询
查询最低寿命预期大于非洲平均寿命预期的大洲名称及其最低寿命预期
# ① 查询非洲平均寿命预期
select avg(lifeexpectancy) from country where Continent = 'Africa'; # 52.57193
#查询
SELECT continent, MIN(LifeExpectancy) AS m
FROM country GROUP BY Continent
HAVING m > (SELECT AVG(lifeexpectancy) FROM country WHERE Continent = 'Africa');
2.用在select后面的子查询
显示预期寿命大于75岁的国家数量和国家总数
查询寿命大于75的国家数量
select count(*) from country where LifeExpectancy>75; #1.
将1作为一个字段查询国家总数
select
(select count(*) from country where LifeExpectancy>75) as "预期寿命大于75的国家数量",
count(*) as "国家总数" from country;
out
3.应用在from 后面
查询每个部门的平均工资的工资级别
查询每个部门的平均工资
select avg(sal),deptno from emp group by deptno; #2
**将2作为条件与salgrade连接 **
select deptno,e.avg_sal,s.grade
from (select avg(sal) as avg_sal,deptno from emp group by deptno) e
join salgrade s on
e.avg_sal between s.losal and s.hisal
4.应用在exists后面
查询国家表中议会君主制(Parlementary Monarchy) 国家
select * from country where GovernmentForm = 'Parlementary Monarchy';
查询国家表中是否存在议会君主制的国家？
select exists(select * from country where GovernmentForm = 'Parlementary Monarchy');
查询使用英语但英语不是官方语言的国家信息
方法一
查询使用英语但英语不是官方语言的国家
select distinct CountryCode from countrylanguage where Language='English' and IsOfficial ='F';
查询符合条件的信息
select * from country where code in(select distinct CountryCode from countrylanguage where Language='English' and IsOfficial ='F');
分页查询
select 查询列表 from 表 limit (page-1)*size,size;
示例
select count(*) from country limit 6;
查询人口数量最多的第11-15名的国家名称和人口数量
select population from country order by population desc limit 10,5;
#下标从0开始，10表示第11条记录，5表示从第11条记录开始查询5条
联合查询
将多次查询的结果合并为一个结果
**合并讲英语的国家编码和语言，和伊丽莎白二世（Elisabeth II）为国家元首的国家信息（国家编码和名称） **
select CountryCode,Language from countrylanguage where Language = 'English'
union
select code,name from country where HeadOfState = 'Elisabeth II';
通常情况下用于合并多个不同的数据源的相同字段信息
合并查询讲英语的国家编码，以及讲法语的国家编码
select CountryCode,Language from countrylanguage where Language = 'English'
union
select CountryCode,Language from countrylanguage where Language = 'French';
select count(*) from countrylanguage where Language = 'English'; # 60
select count(*) from countrylanguage where Language = 'French'; # 25
select count(*) from (
select CountryCode,Language from countrylanguage where Language = 'English'
union
select CountryCode,Language from countrylanguage where Language = 'French') a;
