SQL 結合チュートリアル | MySQL データベース | 人事データ

はじめに

このプロジェクトでは、人事データベースの emp テーブル、dept テーブル、および salgrade テーブルから関連データを取得するための結合クエリの使い方を学びます。

👀 プレビュー

SQL join query example

🎯 タスク

このプロジェクトでは、以下を学びます。

MySQL データベースにアクセスして人事データをインポートする方法
等値結合（内部結合）を使って従業員テーブルから名前、採用日、および対応する部署名を取得する方法
非等値結合を使って従業員テーブルからすべての従業員の名前、給与、および対応する給与等級情報を取得する方法
自己結合を使って「SMITH」という名前の従業員の上司情報を取得する方法
左外部結合を使ってすべての部署情報とそれに対応する従業員情報を取得する方法

🏆 成果

このプロジェクトを完了すると、以下ができるようになります。

さまざまな種類の結合クエリとその使用時期を理解すること
結合クエリを使ってデータベースの複数のテーブルからデータを取得して操作すること
高度な SQL 概念を適用して複雑なデータ取得問題を解決すること

MySQL にアクセスしてデータをインポートする

このステップでは、MySQL データベースにアクセスして人事データをインポートする方法を学びます。

ターミナルを開き、次のコマンドを実行して MySQL サービスを起動します。

sudo service mysql start

sudo コマンドを使用してパスワードなしで MySQL コマンドラインインターフェイスにアクセスします。

sudo mysql

/home/labex/project/personnel.sql ファイルの人事データを MySQL データベースにインポートします。

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

これで、正常に MySQL データベースにアクセスして人事データをインポートできました。次のステップに進むことができます。

従業員の名前、採用日、および部署名を取得する

このステップでは、等値結合（内部結合）を使って従業員テーブルから名前、採用日、および対応する部署名を取得する方法を学びます。

テキストエディタで 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 列を一致させます。結果の出力には、従業員名、採用日、および部署名が含まれます。

join_query.sql ファイルを保存します。

従業員の名前、給与、および給与区分を取得する

このステップでは、非等値結合を使って従業員テーブルからすべての従業員の名前、給与、および対応する給与等級情報を取得する方法を学びます。

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 文は、数値の給与等級を対応する文字等級に変換するために使用されます。

join_query.sql ファイルを保存します。

「SMITH」という名前の従業員の上司情報を取得する

このステップでは、自己結合を使って「SMITH」という名前の従業員の上司情報を取得する方法を学びます。

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 テーブルで自己結合を行い、1 人の従業員 (e1) の empno 列と別の従業員 (e2) の mgr 列を一致させます。WHERE 句は、結果をフィルタリングして、「SMITH」という名前の従業員の上司情報のみを含めます。

join_query.sql ファイルを保存します。

すべての部署情報とそれに対応する従業員情報を取得する

このステップでは、左外部結合を使ってすべての部署情報とそれに対応する従業員情報を取得する方法を学びます。部署に従業員がいない場合、従業員情報は NULL で埋められます。

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 で埋められます。

join_query.sql ファイルを保存します。

これで、プロジェクトのすべてのタスクが完了しました。MySQL コマンドラインインターフェイスで join_query.sql スクリプトを実行して、クエリの結果を確認できます。

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

SQL query execution result

まとめ

おめでとうございます！このプロジェクトを完了しました。実力を向上させるために、LabEx でさらに多くの実験を行って練習してください。

人事データのためのデータベース結合