/*Author:徐宏涛*/
/*以下是关于全球部分国家概况信息(GDP、总人口、面积等)的表,运用聚合函数和分组查询解决求和、排序等问题*/
–要注意SELECT、FROM、WHERE、GROUP BY、ORDER BY 、HAVING的组合运用 :
CREATE TABLE c_country(
c_name VARCHAR(50) PRIMARY KEY NOT NULL,
c_region VARCHAR(60),
c_area DECIMAL(10),
c_population DECIMAL(11),
c_gdp DECIMAL(14)
);
INSERT INTO c_country (c_name,c_region,c_area,c_population,c_gdp) VALUES (‘brazil’,’south
america’,854700,182032604,2676300000000),
(‘canada’,’north america’,997100,32207113,1976300000000),
(‘france’,’europe’,550200,60180529,2776300000000),
(‘italy’,’europe’,301000,57998353,2754300000000),
(‘united kingdom’,’europe’,240500,60094648,2766600000000),
(‘united states’,’north america’,9367000,290342554,1509400000000),
(‘倭寇’,’asia’,378000,127214499,5869400000000),
(‘南非’,’south africa’,122100,42768678,48710000000),
(‘墨西哥’,’north america’,1958000,104907991,1456100000000),
(‘天朝’,’asia’,9601000,1317442552,7298100000000),
(‘安哥拉’,’south africa’,92400,133881703,24780000000),
(‘棒子’,’asia’,92400,48289037,90780000000),
(‘毛子’,’europe’,17075000,144526278,1850400000000),
(‘澳大利亚’,’oceania’,7741000,19731984,1476100000000),
(‘纳粹’,’europe’,357000,82398326,3577000000000),
(‘西班牙’,’europe’,500600,40217413,1576100000000),
(‘阿三’,’asia’,3280800,1049700118,1676100000000);
DROP TABLE c_country;
–1、世界人口总和
SELECT SUM(c_population) FROM c_country;
–2、列出所有的地区,但每一个地区只能显示一次
SELECT c_region FROM c_country GROUP BY c_region;
–3、显示每个地区,以及该地区国家总数
SELECT c_region,COUNT(c_name) FROM c_country GROUP BY c_region;
–4、显示每个地区总人口和总面积,以总人口排序
SELECT c_region,SUM(c_population),SUM(c_area) FROM c_country GROUP BY c_region ORDER BY
SUM(c_population);
–5、显示每个地区以及该地区国家总人口数不少于1000万的国家总数
SELECT c_region,COUNT(c_name) FROM c_country WHERE c_population>=10000000 GROUP BY c_region;
–6、列出总人口数不少于一亿的地区
SELECT c_region FROM c_country GROUP BY c_region HAVING SUM(c_population)>=100000000;
–7、显示欧洲总人口数和GDP
SELECT SUM(c_population),SUM(c_gdp) FROM c_country WHERE c_region=’europe’;
–8、显示每个地区总人口数和面积,以总人口排序,仅显示那些面积超过1000000的地区
SELECT c_region,SUM(c_population),SUM(c_area) FROM c_country WHERE c_area>1000000 GROUP BY c_region ORDER BY SUM(c_population);
未经允许不得转载:徐宏涛博客 » MYSQL基础:聚合函数和分组查询(使用演示)
评论前必须登录!