数据库管理与 SQL 自连接

介绍

在本项目中，你将学习如何创建数据库、表以及向表中插入数据。你还将学习如何对数据执行自连接查询，以找出某个城市所属的省份。

👀 预览

🎯 任务

在本项目中，你将学习：

• 如何启动 MySQL 服务，并使用 sudo 命令无密码访问 MySQL。
• 如何创建数据库、表以及向表中插入数据。
• 如何在 division 表上编写自连接查询，以找出每个城市所属的省份。
• 如何在 MySQL 中运行自连接查询脚本。

🏆 成果

完成本项目后，你将能够：

• 理解在 MySQL 中创建数据库、表以及插入数据的基本操作。
• 执行自连接查询以从表中检索相关信息。
• 应用你对 SQL 查询的知识来解决实际问题。

启动 MySQL 服务并访问 MySQL

在本步骤中，你将学习如何启动 MySQL 服务，并使用 sudo 命令无密码访问 MySQL。

1. 启动 MySQL 服务：

sudo service mysql start

2. 访问 MySQL：

sudo mysql

现在你应该能够无密码访问 MySQL 提示符了。

创建数据库、表并插入数据

在本步骤中，你将学习如何创建数据库、表以及向表中插入数据。

1. 创建 division 数据库：

CREATE DATABASE division;

2. 使用 division 数据库：

USE division;

3. 创建 division 表：

CREATE TABLE division (
  id INT PRIMARY KEY,
  name VARCHAR(22),
  province_id INT
);

4. 向 division 表中插入数据：

INSERT INTO division VALUES
(1, '安大略省', NULL),
(2, '多伦多', 1),
(3, '魁北克省', NULL),
(4, '蒙特利尔', 3);

编写自连接查询

在本步骤中，你将学习如何在 division 表上编写自连接查询，以找出某个城市所属的省份。

1. 在 /home/labex/project 目录下创建一个名为 getProvince.sql 的新文件。

2. 在 getProvince.sql 文件中，添加以下查询：

SELECT city.name AS City, province.name AS Province
FROM division AS city
JOIN division AS province ON city.province_id = province.id
WHERE city.province_id IS NOT NULL;

此查询在 division 表上执行自连接，其中 city 表与 province 表基于 province_id 列进行连接。WHERE 子句确保结果中只包含 province_id 不为空的城市。

结果表将有标为 City 和 Province 的列。

运行自连接查询

在本步骤中，你将学习如何在 MySQL 中运行自连接查询脚本。

1. 返回 MySQL 提示符：

sudo mysql

2. 运行 getProvince.sql 脚本：

SOURCE /home/labex/project/getProvince.sql;

你应该会看到以下输出：

MariaDB [division]> SOURCE /home/labex/project/getProvince.sql;
+----------+----------+
| City     | Province |
+----------+----------+
| 多伦多    | 安大略省   |
| 蒙特利尔   | 魁北克省   |
+----------+----------+
2 rows in set (0.000 sec)

此输出显示了自连接查询检索到的城市及其对应的省份。

恭喜你！你已通过创建数据库、表、插入数据以及执行自连接查询来找出每个城市所属的省份，成功完成了该项目。

总结

恭喜你！你已完成本项目。你可以在 LabEx 中练习更多实验来提升你的技能。