mySql基本语法三

– 子查询
– 在一个查询中嵌套一个查询称为子查询

– 查询最高分学生的信息
SELECT * FROM t_student WHERE s_grade =(SELECT MAX(s_grade) FROM t_student);

– 查询哪些人的成绩比张三好
SELECT * FROM t_student WHERE s_grade > (SELECT s_grade FROM t_student WHERE s_name = ‘张三’);

– 查询每个班最高分学生信息,多个值用IN连接
SELECT * FROM t_student s1 WHERE s_grade IN
(SELECT MAX(s_grade) FROM t_student s2 WHERE
s2.s_class=s1.s_class GROUP BY s_class);

SELECT * FROM bbc;

– 获得到和‘Brazil’同属一个地区的所有国家
SELECT * FROM bbc WHERE region=(SELECT region FROM bbc WHERE contryName=’Brazil’);

– 给出人口多于Russia的国家名称
SELECT * 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 IN(‘India’,’Iran’));

– 给出人均gdp超过’United Kingdom’(英国)的欧州国家
SELECT * FROM bbc WHERE gdp/population >
(SELECT gdp/population FROM bbc WHERE contryName = ‘United Kingdom’) AND region=’Europe’;

– 给出人口比’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 * FROM bbc WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region =’Europe’ );

– 给出每个地区人口最大的国家
SELECT * FROM bbc s1 WHERE population IN
(SELECT MAX(population) FROM bbc s2 WHERE s1.region=s2.region GROUP BY region);

– 给出地区中所有国家的人口总数为0的地区
SELECT region FROM bbc WHERE region=
(SELECT region FROM bbc GROUP BY region HAVING SUM(population)=0);

– 有些国家的人口数比她的周边国家要多三倍，请列出这些国家和地区。
SELECT contryName,region FROM bbc s1 WHERE population >
(SELECT MAX(population) FROM bbc s2 WHERE
s1.contryName!=s2.contryName AND s1.region=s2.region GROUP BY region)*4;