Datenbankverknüpfungen für Personalien-Daten

Einführung

In diesem Projekt lernst du, wie du mit Join-Abfragen relevante Daten aus der Tabelle emp, der Tabelle dept und der Tabelle salgrade in der Personal-Datenbank abrufen kannst.

👀 Vorschau

🎯 Aufgaben

In diesem Projekt wirst du lernen:

• Wie du auf die MySQL-Datenbank zugreifst und die Personal-Daten importierst
• Wie du eine Gleichheitsverknüpfung (Inner-Join) verwendest, um die Namen, Einstellungsdatum und die entsprechenden Abteilungsnamen aus der Mitarbeitertabelle abzurufen
• Wie du eine Ungleichheitsverknüpfung verwendest, um die Namen, Gehälter und die entsprechende Gehaltsstufeninformation für alle Mitarbeiter aus der Mitarbeitertabelle abzurufen
• Wie du eine Selbstverknüpfung verwendest, um die Vorgesetzteninformationen des Mitarbeiters mit dem Namen "SMITH" abzurufen
• Wie du eine linke äußere Verknüpfung verwendest, um alle Abteilungsinformationen und ihre entsprechenden Mitarbeiterinformationen abzurufen

🏆 Errungenschaften

Nach Abschluss dieses Projekts wirst du in der Lage sein:

• Die verschiedenen Arten von Join-Abfragen zu verstehen und wann sie verwendet werden sollen
• Daten aus mehreren Tabellen in einer Datenbank mithilfe von Join-Abfragen abzurufen und zu manipulieren
• Fortgeschrittene SQL-Konzepte anzuwenden, um komplexe Datenabrufsaufgaben zu lösen

Zugang zu MySQL und Import der Daten

In diesem Schritt lernst du, wie du auf die MySQL-Datenbank zugreifst und die Personal-Daten importierst.

1. Öffne ein Terminal und führe den folgenden Befehl aus, um den MySQL-Dienst zu starten:

sudo service mysql start

2. Greife auf die MySQL-Befehlszeilenschnittstelle zu, indem du den sudo-Befehl ohne Passwort verwendest:

sudo mysql

3. Importiere die Personal-Daten aus der Datei /home/labex/project/personnel.sql in die MySQL-Datenbank:

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

Jetzt hast du erfolgreich auf die MySQL-Datenbank zugegriffen und die Personal-Daten importiert. Du kannst mit dem nächsten Schritt fortfahren.

Abrufen von Mitarbeiternamen, Einstellungsdatum und Abteilungsnamen

In diesem Schritt lernst du, wie du eine Gleichheitsverknüpfung (Inner-Join) verwendest, um die Namen, Einstellungsdatum und die entsprechenden Abteilungsnamen aus der Mitarbeitertabelle abzurufen.

1. Öffne die Datei join_query.sql in einem Texteditor und füge den folgenden Code hinzu:

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

Diese Abfrage führt eine Inner-Join zwischen den Tabellen emp und dept durch, indem die deptno-Spalte aus der emp-Tabelle mit der deptno-Spalte aus der dept-Tabelle übereinstimmt. Die resultierende Ausgabe wird den Mitarbeiternamen, das Einstellungsdatum und den Abteilungsnamen enthalten.

2. Speichere die Datei join_query.sql.

Abrufen von Mitarbeiternamen, Gehältern und Gehaltsstufen

In diesem Schritt lernst du, wie du eine Ungleichheitsverknüpfung verwendest, um die Namen, Gehälter und die entsprechende Gehaltsstufeninformation für alle Mitarbeiter aus der Mitarbeitertabelle abzurufen.

1. Öffne die Datei join_query.sql und füge den folgenden Code unter der vorherigen Abfrage hinzu:

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;

Diese Abfrage führt eine Ungleichheitsverknüpfung zwischen den Tabellen emp und salgrade durch, indem das Gehalt des Mitarbeiters (e.sal) mit dem Gehaltsstufenbereich (s.losal und s.hisal) übereinstimmt. Die CASE-Anweisung wird verwendet, um die numerische Gehaltsstufe in eine entsprechende Buchstabenstufe umzuwandeln.

2. Speichere die Datei join_query.sql.

Abrufen der Vorgesetzteninformationen des Mitarbeiters mit dem Namen "SMITH"

In diesem Schritt lernst du, wie du eine Selbstverknüpfung verwendest, um die Vorgesetzteninformationen des Mitarbeiters mit dem Namen "SMITH" abzurufen.

1. Öffne die Datei join_query.sql und füge den folgenden Code unter den vorherigen Abfragen hinzu:

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';

Diese Abfrage führt eine Selbstverknüpfung auf der emp-Tabelle durch, indem die empno-Spalte eines Mitarbeiters (e1) mit der mgr-Spalte eines anderen Mitarbeiters (e2) übereinstimmt. Die WHERE-Klausel filtert die Ergebnisse, um nur die Vorgesetzteninformationen des Mitarbeiters mit dem Namen "SMITH" zu enthalten.

2. Speichere die Datei join_query.sql.

Abrufen aller Abteilungsinformationen und ihrer zugehörigen Mitarbeiterinformationen

In diesem Schritt lernst du, wie du eine linke äußere Verknüpfung verwendest, um alle Abteilungsinformationen und ihre zugehörigen Mitarbeiterinformationen abzurufen. Wenn ein Abteilung keine Mitarbeiter hat, werden die Mitarbeiterinformationen mit NULL befüllt.

1. Öffne die Datei join_query.sql und füge den folgenden Code unter den vorherigen Abfragen hinzu:

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;

Diese Abfrage führt eine linke äußere Verknüpfung zwischen den Tabellen dept und emp durch, indem die deptno-Spalte beider Tabellen übereinstimmt. Die resultierende Ausgabe wird alle Abteilungsinformationen sowie die zugehörigen Mitarbeiterinformationen enthalten. Wenn ein Abteilung keine Mitarbeiter hat, werden die Mitarbeiterinformationen mit NULL befüllt.

2. Speichere die Datei join_query.sql.

Jetzt hast du alle Aufgaben im Projekt abgeschlossen. Du kannst das Skript join_query.sql in der MySQL-Befehlszeilenschnittstelle ausführen, um die Ergebnisse der Abfragen zu sehen:

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

Zusammenfassung

Herzlichen Glückwunsch! Du hast dieses Projekt abgeschlossen. Du kannst in LabEx weitere Übungen absolvieren, um deine Fähigkeiten zu verbessern.