SQLite View Erstellung

Einführung

In diesem Lab werden wir die Erstellung von SQLite-Views (Sichten) untersuchen, wobei wir uns auf den Aufbau komplexer Views, das Abfragen durch diese, das Aktualisieren über aktualisierbare Views und das Entfernen veralteter Views konzentrieren.

Wir beginnen mit dem Verständnis von Views als virtuelle Tabellen, die auf den Ergebnismengen von SQL-Anweisungen basieren und nützlich sind, um Abfragen zu vereinfachen und den Datenzugriff zu steuern. Das Lab führt Sie durch die Erstellung einfacher Views aus einzelnen Tabellen, wie z. B. einer employees-Tabelle (Mitarbeitertabelle), und geht dann zu komplexeren Views über, die Joins (Verbindungen) zwischen Tabellen beinhalten, wie z. B. das Verbinden von employees- und departments-Tabellen (Abteilungstabellen). Sie lernen, wie Sie diese Views abfragen, als wären es reguläre Tabellen, und erkunden die Möglichkeiten, Daten über aktualisierbare Views zu aktualisieren, sowie wie Sie Views ordnungsgemäß entfernen, wenn sie nicht mehr benötigt werden.

Tabellen erstellen: Mitarbeiter (employees) und Abteilungen (departments)

In diesem Schritt erstellen Sie zwei Tabellen, employees (Mitarbeiter) und departments (Abteilungen), und fügen einige Beispieldaten ein. Diese Tabellen werden verwendet, um in den folgenden Schritten Views (Sichten) zu erstellen und abzufragen.

Öffnen Sie zunächst die SQLite-Shell, indem Sie den folgenden Befehl im Terminal ausführen:

sqlite3 /home/labex/project/employees.db

Dieser Befehl öffnet die SQLite-Shell und verbindet sich mit der employees.db-Datenbank. Wenn die Datenbankdatei nicht existiert, wird sie von SQLite erstellt.

Erstellen Sie nun die employees-Tabelle mit der folgenden SQL-Anweisung:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

Diese SQL-Anweisung erstellt eine Tabelle namens employees mit fünf Spalten: id, first_name, last_name, department und salary. Die Spalte id ist der Primärschlüssel (primary key) für die Tabelle.

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

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

Diese SQL-Anweisung fügt vier Zeilen in die employees-Tabelle ein.

Erstellen Sie nun die departments-Tabelle mit der folgenden SQL-Anweisung:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

Diese SQL-Anweisung erstellt eine Tabelle namens departments mit drei Spalten: id, name und location. Die Spalte id ist der Primärschlüssel für die Tabelle.

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

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

Diese SQL-Anweisung fügt drei Zeilen in die departments-Tabelle ein.

Sie können die Tabellenerstellung und Dateneinfügung überprüfen, indem Sie die Tabellen abfragen:

SELECT * FROM employees;
SELECT * FROM departments;

Diese Befehle zeigen den Inhalt der Tabellen employees bzw. departments an.

Eine einfache View erstellen

In diesem Schritt erstellen Sie eine einfache View (Sicht) namens employee_info, die bestimmte Spalten aus der employees-Tabelle (Mitarbeitertabelle) auswählt.

Eine View ist eine virtuelle Tabelle, die auf dem Result-Set (Ergebnismenge) einer SQL-Anweisung basiert. Sie vereinfacht komplexe Abfragen und bietet eine Abstraktionsebene.

Um die employee_info-View zu erstellen, führen Sie die folgende SQL-Anweisung in der SQLite-Shell aus:

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Diese SQL-Anweisung erstellt eine View namens employee_info, die die Spalten id, first_name, last_name und department aus der employees-Tabelle auswählt.

Sie können die View nun abfragen, als wäre sie eine Tabelle:

SELECT * FROM employee_info;

Dieser Befehl zeigt den Inhalt der employee_info-View an, die eine Teilmenge der Spalten in der employees-Tabelle ist.

Eine komplexe View mit Joins erstellen

In diesem Schritt erstellen Sie eine komplexere View (Sicht) namens employee_department_info, die die Tabellen employees (Mitarbeiter) und departments (Abteilungen) verknüpft (join).

Das Verknüpfen von Tabellen (Joining tables) ermöglicht es Ihnen, Daten aus mehreren Tabellen basierend auf einer zugehörigen Spalte zu kombinieren.

Um die employee_department_info-View zu erstellen, führen Sie die folgende SQL-Anweisung in der SQLite-Shell aus:

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

Diese SQL-Anweisung erstellt eine View namens employee_department_info, die die Tabellen employees und departments über die Spalte department verknüpft (joined). Sie wählt den Vornamen, Nachnamen und die Abteilung des Mitarbeiters sowie den Standort der Abteilung aus.

Sie können die View nun abfragen, um die kombinierten Daten anzuzeigen:

SELECT * FROM employee_department_info;

Dieser Befehl zeigt den Inhalt der employee_department_info-View an und zeigt Mitarbeiterinformationen zusammen mit dem Standort ihrer Abteilung.

Eine View mit Aggregatfunktionen erstellen

In diesem Schritt erstellen Sie eine View (Sicht) namens department_salary_stats, die Aggregatfunktionen (Aggregatfunktionen) verwendet, um das durchschnittliche Gehalt für jede Abteilung zu berechnen.

Aggregatfunktionen führen Berechnungen an einer Menge von Werten durch und geben ein einzelnes Ergebnis zurück.

Um die department_salary_stats-View zu erstellen, führen Sie die folgende SQL-Anweisung in der SQLite-Shell aus:

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

Diese SQL-Anweisung erstellt eine View namens department_salary_stats, die das durchschnittliche Gehalt für jede Abteilung mithilfe der AVG-Funktion berechnet und die Ergebnisse mithilfe der GROUP BY-Klausel nach Abteilung gruppiert.

Sie können die View nun abfragen, um das durchschnittliche Gehalt für jede Abteilung anzuzeigen:

SELECT * FROM department_salary_stats;

Dieser Befehl zeigt den Inhalt der department_salary_stats-View an und zeigt das durchschnittliche Gehalt für jede Abteilung.

Datenaktualisierung über eine aktualisierbare View

In diesem Schritt lernen Sie die Einschränkungen von SQLite Views kennen und wie Sie mithilfe von INSTEAD OF Triggern wirklich aktualisierbare Views erstellen können.

Wichtig: Standardmäßig sind SQLite Views schreibgeschützt. Sie können Daten nicht direkt über eine View mit UPDATE, INSERT oder DELETE aktualisieren, einfügen oder löschen. Sie können jedoch mithilfe von INSTEAD OF Triggern aktualisierbare Views erstellen.

Zuerst wollen wir die Einschränkung verstehen, indem wir versuchen, eine direkte Aktualisierung auf unserer bestehenden View durchzuführen:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Dies schlägt mit einem Fehler fehl, da SQLite Views standardmäßig schreibgeschützt sind.

Um eine wirklich aktualisierbare View zu erstellen, müssen wir INSTEAD OF Trigger verwenden. Lassen Sie uns die employee_info View neu erstellen und einen INSTEAD OF Trigger für Aktualisierungen hinzufügen:

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Erstellen Sie nun einen INSTEAD OF Trigger, um UPDATE-Operationen zu verarbeiten:

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

Jetzt können Sie Daten über die View aktualisieren:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Diese UPDATE-Anweisung funktioniert jetzt, da der INSTEAD OF Trigger die Aktualisierung an die zugrunde liegende employees Tabelle weiterleitet.

Sie können die Aktualisierung überprüfen, indem Sie die employees Tabelle direkt abfragen:

SELECT * FROM employees WHERE id = 1;

Dieser Befehl zeigt die Zeile in der employees Tabelle mit id = 1 an, und Sie sollten sehen, dass die Spalte department auf 'HR' aktualisiert wurde.

Veraltete Views und Trigger entfernen

In diesem Schritt entfernen Sie die Views und Trigger, die Sie in den vorherigen Schritten erstellt haben.

Wenn sich Ihre Datenbank weiterentwickelt, können einige Views und Trigger veraltet oder unnötig werden. Es ist wichtig, diese Objekte zu entfernen, um ein sauberes und effizientes Datenbankschema zu erhalten.

Entfernen Sie zuerst den INSTEAD OF Trigger:

DROP TRIGGER IF EXISTS update_employee_info;

Entfernen Sie dann die Views. Um eine View zu entfernen, verwenden Sie die Anweisung DROP VIEW. Um beispielsweise die View employee_info zu entfernen, führen Sie den folgenden Befehl aus:

DROP VIEW IF EXISTS employee_info;

Die Klausel IF EXISTS verhindert einen Fehler, falls die View nicht existiert.

Entfernen Sie auch die anderen Views:

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

Sie können überprüfen, ob die Views entfernt wurden, indem Sie die Tabelle sqlite_master abfragen:

SELECT name FROM sqlite_master WHERE type='view';

Dieser Befehl sollte ein leeres Ergebnis zurückgeben, was darauf hindeutet, dass sich keine Views in der Datenbank befinden.

Beenden Sie abschließend die SQLite-Shell:

.exit

Dieser Befehl schließt die Verbindung zur Datenbank employees.db und bringt Sie zurück zum Linux-Terminal.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man in SQLite Views erstellt, abfragt, aktualisiert und entfernt. Sie begannen mit der Erstellung einfacher Views, die auf einer einzelnen Tabelle basieren, und entwickelten sich dann zu komplexeren Views, die Joins und Aggregatfunktionen umfassten. Sie haben festgestellt, dass SQLite Views standardmäßig schreibgeschützt sind, aber gelernt, wie man mithilfe von INSTEAD OF Triggern wirklich aktualisierbare Views erstellt. Sie haben auch gelernt, wie man veraltete Views und Trigger ordnungsgemäß entfernt, um ein sauberes Datenbankschema zu erhalten. Diese Fähigkeiten sind unerlässlich, um komplexe Abfragen zu vereinfachen, den Datenzugriff zu steuern und Ihre SQLite-Datenbanken effektiv zu verwalten.