SQLite CTE Abfragen

Einführung

In diesem Lab werden Sie die Leistungsfähigkeit von Common Table Expressions (CTEs) in SQLite erkunden. Sie werden lernen, wie Sie CTEs definieren und verwenden können, um die Lesbarkeit und Wartbarkeit von Abfragen zu verbessern. Sie beginnen mit einfachen CTEs und gehen dann zu rekursiven CTEs über. Am Ende dieses Labs werden Sie in der Lage sein, CTEs zu verwenden, um saubereren, effizienteren und leichter verständlichen SQL-Code zu schreiben.

Skills Graph

Erstellen einer Datenbank und einer Tabelle

In diesem Schritt erstellen Sie eine SQLite-Datenbank und eine employees-Tabelle (Mitarbeitertabelle). Diese Tabelle speichert Mitarbeiterinformationen, die Sie in späteren Schritten verwenden werden, um CTE-Abfragen zu üben.

Öffnen Sie zunächst Ihr Terminal in der LabEx VM. Ihr Standardpfad ist /home/labex/project.

Erstellen wir nun eine SQLite-Datenbank namens company.db. Führen Sie den folgenden Befehl aus, um die Datenbankdatei zu erstellen und das SQLite-Befehlszeilentool zu öffnen:

sqlite3 company.db

Sie sehen eine Eingabeaufforderung, die anzeigt, dass Sie sich jetzt in der SQLite-Shell befinden:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Erstellen Sie als Nächstes eine Tabelle namens employees, um grundlegende Mitarbeiterinformationen zu speichern. Diese Tabelle hat vier Spalten: id, name, department und salary. Geben Sie den folgenden SQL-Befehl an der sqlite>-Eingabeaufforderung ein und drücken Sie die Eingabetaste:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

Dieser Befehl richtet die employees-Tabelle ein, in der:

• id eine Ganzzahl (Integer) ist, die als Primärschlüssel (Primary Key) für jeden Mitarbeiter dient.
• name ein Textfeld für den Namen des Mitarbeiters ist.
• department ein Textfeld für die Abteilung (Department) des Mitarbeiters ist.
• salary ein Ganzzahlfeld für das Gehalt (Salary) des Mitarbeiters ist.

Sie sehen keine Ausgabe, wenn der Befehl erfolgreich ausgeführt wird.

Daten in die Tabelle einfügen

Nachdem Sie die employees-Tabelle erstellt haben, fügen wir einige Daten hinzu. Wir werden fünf Mitarbeiterdatensätze in die Tabelle einfügen.

Fügen Sie fünf Mitarbeiterdatensätze in die employees-Tabelle ein, indem Sie diese Befehle einzeln an der sqlite>-Eingabeaufforderung ausführen:

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

Diese Befehle fügen der employees-Tabelle fünf Zeilen hinzu.

• INSERT INTO employees (name, department, salary) gibt an, dass Sie Daten in die Spalten name, department und salary der employees-Tabelle einfügen.
• VALUES ('Alice', 'Sales', 50000) stellt die Werte bereit, die für jeden Datensatz eingefügt werden sollen.

Um zu bestätigen, dass die Daten korrekt hinzugefügt wurden, führen Sie diesen Befehl aus, um alle Datensätze in der Tabelle anzuzeigen:

SELECT * FROM employees;

Erwartete Ausgabe:

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

Diese Ausgabe zeigt die id, name, department und salary für jeden Datensatz. Der Befehl SELECT * ruft alle Spalten aus der angegebenen Tabelle ab.

Definieren einer einfachen CTE

In diesem Schritt lernen Sie, wie Sie einen einfachen Common Table Expression (CTE) in SQLite definieren und verwenden. CTEs sind temporäre, benannte Ergebnismengen, auf die Sie innerhalb einer einzelnen SQL-Anweisung verweisen können. Sie sind nützlich, um komplexe Abfragen in kleinere, besser handhabbare Teile zu zerlegen, wodurch die Lesbarkeit und Wartbarkeit verbessert werden.

Eine CTE ist im Wesentlichen eine benannte Unterabfrage (Subquery), die nur für die Dauer einer einzelnen Abfrage existiert. Sie definieren eine CTE mit der WITH-Klausel, geben ihr einen Namen und geben die Abfrage an, die die Ergebnismenge generiert. Sie können dann auf den Namen der CTE in der Hauptabfrage verweisen, als wäre es eine reguläre Tabelle.

Grundlegende Syntax:

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Definieren wir eine CTE, um Mitarbeiter aus der Abteilung 'Sales' auszuwählen. Führen Sie die folgende SQL-Anweisung an der sqlite>-Eingabeaufforderung aus:

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

Diese Abfrage definiert zuerst eine CTE namens SalesEmployees, die die id, den name und das salary aller Mitarbeiter in der Abteilung 'Sales' auswählt. Dann wählt die Hauptabfrage die id, den name und das salary aus der SalesEmployees-CTE aus, wobei das Gehalt (Salary) höher als 52000 ist.

Erwartete Ausgabe:

3|Charlie|55000

Dies zeigt, dass Charlie der einzige Mitarbeiter in der Vertriebsabteilung (Sales department) mit einem Gehalt von mehr als 52000 ist.

Erstellen einer rekursiven CTE

In diesem Schritt lernen Sie, wie Sie eine rekursive Common Table Expression (CTE) in SQLite erstellen und verwenden. Rekursive CTEs werden verwendet, um hierarchische oder baumartige Daten abzufragen. Sie ermöglichen es Ihnen, Beziehungen innerhalb einer Tabelle zu durchlaufen und Daten auf verschiedenen Ebenen der Hierarchie abzurufen.

Eine rekursive CTE ist eine CTE, die sich selbst referenziert. Sie besteht aus zwei Teilen:

• Anker-Member (Anchor Member): Die anfängliche SELECT-Anweisung, die den Basisfall oder den Startpunkt der Rekursion definiert.
• Rekursiver Member (Recursive Member): Eine SELECT-Anweisung, die sich auf die CTE selbst bezieht. Dieser Teil führt den rekursiven Schritt aus und baut auf den Ergebnissen der vorherigen Iteration auf.

Der Anker-Member und der rekursive Member werden mit dem Operator UNION ALL kombiniert. Die Rekursion wird fortgesetzt, bis der rekursive Member eine leere Ergebnismenge zurückgibt.

Erstellen wir zunächst eine Tabelle namens employees_hierarchy mit der folgenden Struktur:

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

Diese Tabelle stellt die Mitarbeiterhierarchie dar, wobei manager_id sich auf die id des Managers des Mitarbeiters bezieht. Führen Sie den obigen Befehl an der sqlite>-Eingabeaufforderung aus.

Fügen Sie als Nächstes einige Beispieldaten in die Tabelle employees_hierarchy ein:

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

Hier ist Alice die CEO (kein Manager), Bob und Charlie berichten an Alice, David berichtet an Bob, Eve berichtet an Charlie, Frank berichtet an David und Grace berichtet an Eve. Führen Sie die obigen Befehle an der sqlite>-Eingabeaufforderung aus.

Erstellen wir nun eine rekursive CTE, um die gesamte Hierarchie unter Alice (der CEO) abzurufen. Führen Sie die folgende SQL-Anweisung aus:

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

Diese Abfrage definiert eine rekursive CTE namens EmployeeHierarchy. Der Anker-Member wählt den CEO aus (wobei manager_id NULL ist). Der rekursive Member verbindet die Tabelle employees_hierarchy mit der EmployeeHierarchy-CTE, um Mitarbeiter zu finden, die an die Mitarbeiter berichten, die in der vorherigen Ebene ausgewählt wurden. Die Spalte level verfolgt die Tiefe in der Hierarchie.

Erwartete Ausgabe:

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

Dies zeigt die gesamte Mitarbeiterhierarchie, wobei die level die Berichtsebene angibt.

Integrieren von CTEs in komplexe Abfragen

In diesem Schritt lernen Sie, wie Sie CTEs in komplexere Abfragen in SQLite integrieren. Sie werden sehen, wie Sie mehrere CTEs innerhalb einer einzigen Abfrage verwenden können.

Sie können mehrere CTEs in einer einzigen Abfrage definieren, indem Sie sie durch Kommas trennen. Dies ermöglicht es Ihnen, eine komplexe Abfrage in mehrere logische Schritte zu unterteilen, die jeweils durch eine CTE dargestellt werden.

Erstellen wir eine neue Tabelle namens department_salaries mit der folgenden Struktur:

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

Diese Tabelle speichert das Gesamtgehalt (total salary) für jede Abteilung (department). Führen Sie den obigen Befehl an der sqlite>-Eingabeaufforderung aus.

Verwenden wir nun CTEs, um das Gesamtgehalt für jede Abteilung zu berechnen und die Ergebnisse in die Tabelle department_salaries einzufügen. Führen Sie die folgende SQL-Anweisung aus:

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

Diese Abfrage definiert zuerst eine CTE namens DepartmentTotalSalaries, die das Gesamtgehalt für jede Abteilung mithilfe der Tabelle employees berechnet. Anschließend werden die Ergebnisse aus der DepartmentTotalSalaries-CTE in die Tabelle department_salaries eingefügt. Schließlich werden alle Daten aus der Tabelle department_salaries ausgewählt, um die Ergebnisse anzuzeigen.

Erwartete Ausgabe:

Sales|105000
Marketing|122000
Engineering|70000

Dies zeigt das Gesamtgehalt für jede Abteilung.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Common Table Expressions (CTEs) in SQLite definieren und verwenden. Sie begannen mit einfachen CTEs, um Daten aus einer Tabelle auszuwählen, und gingen dann zu rekursiven CTEs über, um hierarchische Daten abzufragen. Schließlich haben Sie gelernt, wie Sie CTEs in komplexere Abfragen integrieren. CTEs sind ein leistungsstarkes Werkzeug, um saubereren, effizienteren und leichter verständlichen SQL-Code zu schreiben.