MYSQL基本语法操作二

DROP TABLE bbc;
CREATE TABLE bbc(
contryName VARCHAR(50) NOT NULL – 国家名称
,region VARCHAR(60) – 地区
,contryArea DECIMAL(10) – 面积
,population DECIMAL(11) – 人口
,gdp DECIMAL(14) – GDP
,PRIMARY KEY (contryName)
)DEFAULT CHARSET=utf8;

INSERT INTO bbc VALUES (‘Afghanistan’,’South Asia’,652225,26000000,NULL);
INSERT INTO bbc VALUES (‘Albania’,’Europe’,28728,3200000,6656000000);
INSERT INTO bbc VALUES (‘Algeria’,’Middle East’,2400000,32900000,75012000000);

– 1、显示世界人口总和
SELECT SUM(population) FROM bbc;

– 2、列出所有的地区，每个地区只显示一次
SELECT DISTINCT region’地区’ FROM bbc;
SELECT region’地区’ FROM bbc GROUP BY region;

– 3、显示GDP超过所有Africa国家总和的国家 – 子查询
SELECT contryName’国家’ FROM bbc WHERE gdp > (SELECT SUM(gdp) FROM bbc WHERE region = ‘Africa’);

– 4、显示每个地区及该地区国家总数
SELECT region,COUNT(*) FROM bbc GROUP BY region;

– 5、显示每个地区的总人口数和总面积，以总人口排序
SELECT region,SUM(population)’总人口’,SUM(contryArea)’总面积’ FROM bbc GROUP BY region
ORDER BY SUM(population) DESC;

– 6、显示每个地区以及该地区国家总人口数不少于10000000的国家总数

SELECT region’地区’,COUNT(CASE WHEN population >=10000000 THEN ‘1’ END )’国家总数’
FROM bbc GROUP BY region ; //方法一

SELECT region’地区’,COUNT(population )’国家总数’ FROM bbc
WHERE population >=10000000 GROUP BY region ; // 方法二

– 7、列出人口总数不少于100000000的地区
SELECT region’地区’ ,SUM(population)’人口’ FROM bbc GROUP BY region
HAVING SUM(population) >=100000000;

– 8、显示欧州人口总数和总GDP
SELECT SUM(population)’人口总数’,SUM(gdp)’总GDP’ FROM bbc
WHERE region=’Europe’;

– 9、显示每个地区的总人口数和总面积，以总人口排序，仅显示那些面积超过1000000的地区
SELECT region,SUM(population)’人口总数’,SUM(contryArea)’总面积’
FROM bbc GROUP BY region
HAVING SUM(contryArea)>=1000000
ORDER BY SUM(population);