子查询

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语的