实验四

📝 目录

• 📝 目录
• 🧐 实验目的
• 🏁 实验内容
  • 实验环境
  • 实验步骤
    • 一、服务器端安装mysql
      • 安装mysql
      • 开启远程连接权限
      • 创建MySql用户
    • 二、连接数据库
    • 三、向远程数据库导入数据
    • 四、实现以下查询
      • 1.查询国民生产总值(GNP)大于世界平均水平，且最接近平均的国家’
      • 2.查询城市人口占比不到20%的欧洲国家名称、城市人口总和、国家人口、城市人口占比
      • 3.全世界使用人数最多的前10大语言排行榜(语言，使用人数，倒序)
      • 4.查询超过所属大洲平均人口的国家名称、人口数量
      • 5.所有以英语作为官方语言的国家名
      • 6.查询不使用英语、法语和西班牙语的欧洲国家
      • 7.查询使用了2种以上官方语言的国家名
      • 8.查询使用了2种以上官方语言的国家名及其官方语言的数量
      • 9.没有使用美国(code:USA)使用的任何一种语言的国家名
      • 10.查询使用了希腊(code:GRC)所有语言的其他国家的名称和编号
• ⛏️ 参考
• ✍️ Authors

🧐 实验目的

完成给定查询

🏁 实验内容

1.查询国民生产总值(GNP)大于世界平均水平，且最接近平均的国家
2.查询城市人口占比不到20%的欧洲国家名称、城市人口总和、国家人口、城市人口占比
3.全世界使用人数最多的前10大语言排行榜(语言，使用人数，倒序)
4.查询超过所属大洲平均人口的国家名称、人口数量
5.所有以英语作为官方语言的国家名
6.查询不使用英语、法语和西班牙语的欧洲国家
7.查询使用了2种以上官方语言的国家名
8.查询使用了2种以上官方语言的国家名及其官方语言的数量
9.没有使用美国(code:USA)使用的任何一种语言的国家名
10.查询使用了希腊(code:GRC)所有语言的其他国家的名称和编号

实验环境

• 操作系统：Windows 11 , Ubuntu 22.04.1
• 数据库：MySQL 8.0.36

实验步骤

一、服务器端安装mysql

安装mysql

以sudo用户登录,输入以下命令

sudo apt-get update

更新完毕后,输入以下命令,安装mysql

sudo apt-get install mysql-server mysql-client

验证mysql正在运行

sudo systemctl status mysql

输出如下:

表示已经启动

开启远程连接权限

默认情况下，MySQL 数据库仅监听本地连接。若想允许远程连接数据库，首先需要修改配置文件，让 MySQL 可以监听远程固定 IP 或所有远程 IP。

配置文件 mysqld.cnf 路径一般为 /etc/mysql/mysql.conf.d/mysqld.cnf。

输入以下命令打开编辑：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

找到 bind-address 一行，默认该值为 127.0.0.1，仅监听本地连接。为了将其改为远程连接 IP 可访问，可以使用通配符 IP 地址 0.0.0.0，也可以是固定 IP，仅允许指定 IP 连接。这里修改为 0.0.0.0，允许所有 IP 地址访问。

更改后，保存并退出编辑器。后重启 MySQL 服务，使新配置生效。

sudo systemctl restart mysql

创建MySql用户

以sudo权限进入mysql服务

sudo mysql

遇见以下问题:

这个问题要么是是因为mysql服务没有启动成功，要么是没有找到指定文件.经过验证,这里的问题时后者

解决方法:修改配置文件中的mysql.sock的位置,重新启动mysql服务,会在新的目录生成MySQL.sock文件

成功解决:

创建一个可以远程连接的用户,并设置为使用密码作为验证方式

CREATE USER 'Vanish'@'%' IDENTIFIED WITH mysql_native_password BY '不告诉你';

使用如下命令查看当前所有user

SELECT user,host FROM mysql.user;

赋予该用户拥有所有权限,使其成为新的独立管理用户:

GRANT ALL PRIVILEGES ON *.* TO 'Vanish'@'%' WITH GRANT OPTION;

刷新Mysql系统权限相关表,更新缓存,并退出mysql

FLUSH PRIVILEGES;
exit;

二、连接数据库

输入以下命令连接远程数据库

mysql -u Vanish -h <ip地址> -p

出现错误,无法连接到数据库

首先测试可达性,输入以下命令:

ping <ip地址>

结果如下:

排除网络问题.

检查mysql服务器日志如下:

根据提供的日志，MySQL服务器已经成功启动，并且正在监听在默认端口3306上以及UNIX套接字 /var/run/mysqld/mysqld.sock.

这意味着MySQL服务器正在正常运行并准备接受连接。

关于连接到远程MySQL服务器时遇到问题的情况，新增了一条警告日志：‘mysql_native_password’ is deprecated and will be removed in a future release. Please use caching_sha2_password instead。

这是因为在最新的MySQL版本中，mysql_native_password加密插件已经被弃用，建议使用caching_sha2_password 替代。

在尝试连接到远程MySQL 服务器时，需要确认连接字符串中的加密插件是正确的。可以在连接命令中明确指定使用caching_sha2_password 加密插件.输入以下命令连接远程数据库:

mysql -u Vanish -h <ip地址> -p --default-auth=caching_sha2_password

然而还是无法连接
事实上,日志中的并没有连接请求的相关记录,说明连接请求没有到达mysql服务器,可能是防火墙的问题,需要检查防火墙设置.

输入以下命令查看UFW防火墙状态:

sudo ufw status

发现mysql的默认端口3306被阻止了,需要允许:

sudo ufw allow 3306

重新连接数据库:

mysql -u Vanish -h <ip地址> -p

三、向远程数据库导入数据

在畅课下载world_deleteTaiWan.sql文件

输入以下命令导入数据:

source <文件路径>

四、实现以下查询

1.查询国民生产总值(GNP)大于世界平均水平，且最接近平均的国家’

select name from country where GNP =(select min(GNP) from country where GNP >(select AVG(GNP) from country));

结果如下:

2.查询城市人口占比不到20%的欧洲国家名称、城市人口总和、国家人口、城市人口占比

select country.name as'国家','城市人口' , country.population as '国家人口',('城市人口'/country.population) as '城市人口占比' from country,(select country code,sum(population) as '城市人口' from city group by countrycode) as city_population where code=countrycode and ('城市人口'/country.population)<0.2;

结果: 错误

3.全世界使用人数最多的前10大语言排行榜(语言，使用人数，倒序)

select language,sum(percentage*country.population) as '使用人数 ' from country,countrylanguage where code=countrycode group by language order by sum(percentage*country.population) desc limit 10;

结果如下:

4.查询超过所属大洲平均人口的国家名称、人口数量

select name,population from country where exists(select * from(select continent,avg(population) as avg_population from country group by continent) as continent_avg where country.continent = continent_avg.continent and country.population > continent_avg.avg_population);

结果如下:

5.所有以英语作为官方语言的国家名

不相关嵌套查询

select name from country where code in (select countrycode from countrylanguage where language = 'English' and isofficial = true);

结果如下:

相关嵌套查询

select name from country where exists (select countrycode from countrylanguage where language = 'English' and isofficial = true and code = countrycode);

结果如下:

6.查询不使用英语、法语和西班牙语的欧洲国家

不相关嵌套查询

select name from country where continent = 'Europe' and code not in (select distinct countrycode from countrylanguage where language in ('English','Spanish','French'));

结果如下:

相关嵌套查询

select name from country where continent = 'Europe' and not exists (select distinct countrycode from countrylanguage where language in ('English','Spanish','French') and code = countrycode);

结果如下:

7.查询使用了2种以上官方语言的国家名

不相关嵌套查询

select name from country where code in (select countrycode from countrylanguage where isofficial = true group by countrycode having count(isofficial) > 2);

结果如下:

相关嵌套查询

select name from country where exists (select countrycode from countrylanguage where isofficial = true group by countrycode having count(isofficial) > 2 and code = countrycode);

结果如下:

8.查询使用了2种以上官方语言的国家名及其官方语言的数量

9.没有使用美国(code:USA)使用的任何一种语言的国家名

不相关嵌套查询

select name from country where code not in(select distinct countrycode from countrylanguage where language in(select language from countrylanguage where countrycode = 'USA'));

结果如下:

相关嵌套查询

select name from country where not exists (select distinct countrycode from countrylanguage where language in (select language from countrylanguage where countrycode = 'UsA') and code = countrycode);

结果同上

10.查询使用了希腊(code:GRC)所有语言的其他国家的名称和编号

select name,countrycode from country,(select countrycode from countrylanguage where language in (select language from countrylanguage where countrycode = 'GRC') group by countrycode having count(language)> 1) as othercountry where code = countrycode;

结果如下:

⛏️ 参考

https://www.cnblogs.com/zewanhuang/p/16595865.html
https://cloud.tencent.com/developer/article/1772444
https://www.cnblogs.com/kenshinobiy/p/7645871.html

✍️ Authors

• @Vanish0314 - Idea & Initial work
• @Yuxiang Huang - Technical support