人事数据的数据库连接

介绍

在本项目中，你将学习如何使用连接查询从人事数据库的 emp 表、dept 表和 salgrade 表中检索相关数据。

👀 预览

🎯 任务

在本项目中，你将学习：

• 如何访问 MySQL 数据库并导入人事数据
• 如何使用等值连接（内连接）从员工表中检索姓名、入职日期和相应的部门名称
• 如何使用非等值连接从员工表中检索所有员工的姓名、薪资和相应的薪资等级信息
• 如何使用自连接检索名为“SMITH”的员工的上级信息
• 如何使用左外连接检索所有部门信息及其相应的员工信息

🏆 成果

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

• 理解不同类型的连接查询以及何时使用它们
• 使用连接查询从数据库中的多个表中检索和操作数据
• 应用高级 SQL 概念解决复杂的数据检索问题

访问 MySQL 并导入数据

在这一步中，你将学习如何访问 MySQL 数据库并导入人事数据。

1. 打开终端并运行以下命令启动 MySQL 服务：

sudo service mysql start

2. 使用sudo命令无密码访问 MySQL 命令行界面：

sudo mysql

3. 将人事数据从/home/labex/project/personnel.sql文件导入 MySQL 数据库：

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

现在你已成功访问 MySQL 数据库并导入了人事数据。你可以进入下一步。

检索员工姓名、入职日期和部门名称

在这一步中，你将学习如何使用等值连接（内连接）从员工表中检索姓名、入职日期和相应的部门名称。

1. 在文本编辑器中打开 join_query.sql 文件，并添加以下代码：

SELECT e.ename AS `Name`, e.hiredate AS HireDate, d.dname AS DepartmentName
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

此查询在 emp 表和 dept 表之间执行内连接，将 emp 表中的 deptno 列与 dept 表中的 deptno 列进行匹配。结果输出将包括员工姓名、入职日期和部门名称。

2. 保存 join_query.sql 文件。

检索员工姓名、薪资和薪资等级

在这一步中，你将学习如何使用非等值连接从员工表中检索所有员工的姓名、薪资和相应的薪资等级信息。

1. 打开 join_query.sql 文件，并在之前的查询下方添加以下代码：

SELECT e.ename AS `Name`, e.sal AS Salary,
       CASE s.grade
           WHEN 1 THEN 'A'
           WHEN 2 THEN 'B'
           WHEN 3 THEN 'C'
           WHEN 4 THEN 'D'
           WHEN 5 THEN 'E'
           ELSE 'Unknown'
       END AS Grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

此查询在 emp 表和 salgrade 表之间执行非等值连接，将员工的薪资（e.sal）与薪资等级范围（s.losal 和 s.hisal）进行匹配。CASE 语句用于将数字薪资等级转换为相应的字母等级。

2. 保存 join_query.sql 文件。

检索名为“SMITH”的员工的上级信息

在这一步中，你将学习如何使用自连接来检索名为“SMITH”的员工的上级信息。

1. 打开 join_query.sql 文件，并在之前的查询下方添加以下代码：

SELECT e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.ename = 'SMITH';

此查询在 emp 表上执行自连接，将一名员工（e1）的 empno 列与另一名员工（e2）的 mgr 列进行匹配。WHERE 子句过滤结果，只包含名为“SMITH”的员工的上级信息。

2. 保存 join_query.sql 文件。

检索所有部门信息及其对应的员工信息

在这一步中，你将学习如何使用左外连接来检索所有部门信息及其对应的员工信息。如果某个部门没有员工，员工信息将用 NULL 填充。

1. 打开 join_query.sql 文件，并在之前的查询下方添加以下代码：

SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno,
       d.deptno AS deptno_dept, d.dname, d.loc
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno;

此查询在 dept 表和 emp 表之间执行左外连接，匹配两个表中的 deptno 列。结果输出将包括所有部门信息以及相应的员工信息。如果某个部门没有员工，员工信息将用 NULL 填充。

2. 保存 join_query.sql 文件。

现在你已经完成了项目中的所有任务。你可以在 MySQL 命令行界面中运行 join_query.sql 脚本以查看查询结果：

MariaDB [personnel]> SOURCE /home/labex/project/join_query.sql;

总结

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