MySQL案例3 MySQL实验(二)数据库查询

考核内容及实验目的：

使用SQL语句进行查询操作，掌握单表查询、多表查询及嵌套查询操作。

一、实验描述

1、导入一个外部数据库文件(world.sql)到Navicat图形化管理软件中,并进行进行单表查询、多表查询及嵌套查询操作。数据库world包含了country、countryLanguag、city三张表，涵盖了全世界的国家、国家语言和城市的相关信息。

下图是world数据库中的部分表信息

country表

countryLanguage表

city表

2、单表查询

（1）查询全世界使用中文（Language = ‘Chinese’）的国家的个数。

（2）以你家乡地级市名称为条件，查询显示你家乡地级市的名称和人口（Population）以及所属的省份名称（Disrtict）。注意数据库中个别城市的拼写与拼音有所区别，例如北京是Peking，西安是 Xi´an，陕西是Shaanxi。

（3）查询新疆（Xinxiang）的各市平均人口。（提示:avg()函数）

（4）查询显示世界七大洲（continent）的名称和对应各洲所拥有的国家数量（提示:group by），并用降序排序（提示:order by 字段名 desc为降序）。

（5）查询平均国家人口大于一千万的洲（例如亚洲总人数为37亿，国家有51个，除下来平均每个国家人口为七千多万符合条件）的名称和对应该洲的总人口。（提示：avg（）函数、group by 和having字句）。

3、多表查询

（1）查询全世界使用中文为官方语言（IsOfficial=’T’ ）的国家名称（不是代码）。

（2）已知朝鲜名称为North Korea查找朝鲜国家使用的语言。

4、嵌套查询

（1）查询北美洲North America人口最多的国家名称和人口数量。

（2）查询没有一个城市人口超过1000人的国家名称和该国家人口。

二、上机操作

0、实验环境

硬件平台：一台笔电，操作系统为Win10pro 64位
软件环境：

数据库Server版本，8.0.30 MySQL Community Server

图形化管理工具，Navicat Premium 15.0.25

1、用到的SQL语句

单表查询

-- 查询全世界使用中文的国家的个数
select count(CountryCode) from countrylanguage where `Language` = 'Chinese';

-- 查询家乡地级市的名称、人口及所属省份
select Name,Population,District from city where `Name` = 'Ankang';

-- 查询新疆（Xinxiang）的各市平均人口
select avg(Population) from city where District = 'Xinxiang';

-- 查询七大洲名称及各自国家数量，按数量降序
select Continent,count(Code) from country group by Continent order by count(Code) desc;

-- 查询平均国家人口大于一千万的洲及其总人口
select Continent,avg(Population),sum(Population) from country group by Continent having avg(Population) > 10000000;

多表查询

1
2

-- 查询使用中文为官方语言的国家名称（等值连接）
select Name from country co,countrylanguage col where co.Code = col.CountryCode and `Language` = 'Chinese' and IsOfficial = 'T';

1 2	-- 已知朝鲜名称为 North Korea，查找其使用的语言 select Name,Language from country co,countrylanguage col where co.Code = col.CountryCode and `Name` = 'North Korea';

嵌套查询

1 2	-- 查询北美洲人口最多的国家名称和人口数量 select Name,Population from country where Population = any (select max(Population) from country where `Continent` = 'North America');

1
2

-- 查询没有一个城市人口超过 1000 人的国家名称和该国家人口
select Name,Population from country where country.`Code` = any (select distinct city.`CountryCode` from city where city.`Population` < 1000);

2、完整的SQL代码实现

-- 1、单表查询
-- 查询全世界使用中文的国家的个数
select count(CountryCode) from countrylanguage where `Language` = 'Chinese';

-- 查询显示你家乡地级市的名称和人口（Population）以及所属的省份名称（Disrtict）
select Name,Population,District from city where `Name` = 'Ankang';

-- 查询新疆（Xinxiang）的各市平均人口
select avg(Population) from city where District = 'Xinxiang';

-- 查询显示世界七大洲（Continent）的名称和对应各洲所拥有的国家数量，并用降序排序
select Continent,count(Code) from country group by Continent order by count(Code) desc;

-- 查询平均国家人口大于一千万的洲的名称和对应该洲的总人口
select Continent,avg(Population),sum(Population) from country group by Continent having avg(Population) > 10000000;


-- 2、多表查询
-- 查询全世界使用中文为官方语言的国家名称，使用等值连接
select Name from country co,countrylanguage col where co.Code = col.CountryCode and `Language` = 'Chinese' and IsOfficial = 'T';

select Name,Language from country co,countrylanguage col where co.Code = col.CountryCode and `Name` = 'North Korea';

-- 3、嵌套查询
-- 查询北美洲North America人口最多的国家名称和人口数量
-- 子查询是北美洲最多的人口数，外层查询是子查询对应的国家和人口
-- select max(Population) from country where `Continent` = 'North America';
select Name,Population from country where Population = any (select max(Population) from country where `Continent` = 'North America');


-- 查询没有一个城市人口超过1000人的国家名称和该国家人口
-- 子查询：select distinct city.CountryCode from city where city.`Population` < 1000;
select Name,Population from country where country.`Code` = any (select distinct city.`CountryCode` from city where city.`Population` < 1000);