子查询
来源:互联网 发布:手机淘宝投诉流程 编辑:程序博客网 时间:2024/04/29 08:33
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语的
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 轻松理解AOP(面向切面编程)
- 【笔记】高效程序员的45个习惯:敏捷开发修炼之道
- JS奇技淫巧
- 50. Pow(x, n) 372. Super Pow
- 经典dp 滑雪
- 子查询
- 2016-7-27今天学习的MySQL语句的使用
- 任意文件下载
- 网站开发基础知识篇三 Ajax
- android onSaveInstanceState方法 横坚屏切换的状态保存
- MyBatis和Hibernate相比,优势在哪里?
- Codeforces Round #245 (Div. 1) B. Working out(DP)
- 关于if 与return
- C++ Block(代码块)