13.1 子查询的类型
1. Scalar subqueries 返回单独的值
2.Row subqueries 返回单独的行
3.column subqueries 返回单独的列可能有多行
4.table subqueris 返回结果带有多行或多列
select language
from countryLanguage
where CountryCode =(select code
from country
where Name='Finland');
下面的子查询决定哪个城市有最多的居民
select country.name
from country,city
where contry.code=city.countrycode
and city.population=(select max(population) from city);
可以把子查询转换为连接查询因为比子查询更加高效
13.2 subqueries as scalar expression
select concat('The country code for finland is:',(select code from country where name='Finland')) as s1;
计算ration,一个城市占整个国家的比例
select (select sum(population) from city)/(selet sum(population) from country) as ration;
set @city_pop =(select sum(population) from City);
set @country_pop=(select sum(population) from country);
select @city_pop/@country_pop;
13.3 相关的子查询
如下计算哪个城市人口最多
select 。。。。
from contry c
where population =(select Max()
from country c2
where c coni=c2.c);
13.4 比较子查询
13.4.1 使用ALL ,ANY和SOME
ALL对所有的子查询都有效
select name,population
from country
where population >all(select avg(popoulation) from country group by continent) order by name;
如果产生多列的值时就会出错
如:>all(select cotinent,avg(population) from country group by continent)
Any任何一个
select name
from country
where continent=‘Europe’
and code=any(select countrycode from countryLanguage where language='Spanish') order by name;
Some 是ANY的别名
select name
from country
where continent=‘Europe’
And Code <>Any (SELECT CountryCode from countryLanguage where Language ='Spanish')
order by name;
13.4.2 使用IN
select Name
from Country
where code IN ('DEU','USA','JPN');
is functionally equivalent to =Any(note that the =sign is part of the equivalence).
select name from country where continent ='Europe' and code in(select countryCode from contryLanguage where language='Spanish') order by name;
not in和《》ALL等同
13.4.3 使用EXISTS
这EXISTS执行一个简单test
select code c,Name
from country
where contient ='Europe
and exists (select * from contryLanguage where contryCode=c and language ='Spanish');
如果not exists将返回不说Spanish语的