语法要点:
- 当查询结果返回单行单列,可以使用=,<,>,如果子查询返回多行单列,只能使用in或者not in
1.建立了table
2.实训
SELECT * FROM bbc;
-- 获得和巴西同属一个地区的所有国家;
SELECT contryName FROM bbc WHERE region=
(SELECT region FROM bbc WHERE contryName='Brazil');
-- 给出人口多于Russia的国家名称。(当查询结果返回单行单列,可以使用=,<,>,如果子查询返回多行单列,只能使用in或者not in)
SELECT contryName FROM bbc WHERE population>
(SELECT population FROM bbc WHERE contryName='Russia');
-- 给出'India','Iran'所在地区的所有国家信息
SELECT * FROM bbc WHERE region IN
(SELECT region FROM bbc WHERE contryName='India' OR contryName='Iran');
-- 给出人均gdp超过'United Kingdom'(英国)的欧州国家
SELECT contryName FROM bbc WHERE region='Europe' AND gdp/population>
(SELECT gdp/population FROM bbc WHERE contryName='United Kingdom');
-- 给出人口比'Canada'多但少于Algeria的国家的信息
SELECT * FROM bbc WHERE population>
(SELECT population FROM bbc WHERE contryName='Canada')
AND population<(SELECT population FROM bbc WHERE contryName='Algeria');
-- 给出gdp比任何欧州国家都多的国家
SELECT contryName FROM bbc WHERE gdp>
(SELECT MAX(gdp) FROM bbc WHERE region='Europe');
-- 给出每个地区人口最大的国家
SELECT contryName FROM bbc g1 WHERE population=
(SELECT MAX(population) FROM bbc g2 WHERE g1.region=g2.region GROUP BY region);
SELECT DISTINCT region FROM bbc GROUP BY region;
-- 给出地区中所有国家的人口总数为0的地区
SELECT region FROM bbc GROUP BY region HAVING SUM(population)=0;
-- 有些国家的人口数比她的周边国家要多三倍,请列出这些国家和地区。 (查询第二名的固定用法)
SELECT * FROM bbc b1 WHERE population>
4*(SELECT MAX(population) FROM bbc b2 WHERE b1.region=b2.region AND b1.contryName!=b2.contryName GROUP BY region);