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.

Skills Graph

Tabellen erstellen: employees und 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.

Daten über eine aktualisierbare View aktualisieren

In diesem Schritt aktualisieren Sie Daten in der Tabelle employees (Mitarbeiter) über die View employee_info.

Nicht alle Views sind aktualisierbar (updatable). Eine View ist im Allgemeinen aktualisierbar, wenn sie Daten aus einer einzelnen Tabelle auswählt und den Primärschlüssel (primary key) dieser Tabelle enthält.

Lassen Sie uns zunächst die View employee_info löschen und neu erstellen, um sicherzustellen, dass sie die Spalte id enthält, die der Primärschlüssel der Tabelle employees ist:

DROP VIEW IF EXISTS employee_info;

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

Aktualisieren Sie nun die Abteilung (department) eines Mitarbeiters mit id = 1 auf 'HR':

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

Diese SQL-Anweisung aktualisiert die Spalte department in der Tabelle employees für den Mitarbeiter mit id = 1 über die View employee_info.

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

SELECT * FROM employees WHERE id = 1;

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

Veraltete Views entfernen

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

Im Laufe der Entwicklung Ihrer Datenbank können einige Views veraltet oder unnötig werden. Es ist wichtig, diese Views zu entfernen, um ein sauberes und effizientes Datenbankschema zu erhalten.

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

DROP VIEW IF EXISTS employee_info;

Die IF EXISTS-Klausel verhindert einen Fehler, wenn 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 hinweist, 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 kehrt zum Linux-Terminal zurück.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Views in SQLite erstellt, abfragt, aktualisiert und entfernt. Sie begannen mit der Erstellung einfacher Views, die auf einer einzelnen Tabelle basieren, und gingen dann zu komplexeren Views über, die Joins und Aggregatfunktionen (aggregate functions) beinhalten. Sie haben auch gelernt, wie man Daten über aktualisierbare Views (updatable views) aktualisiert und wie man veraltete Views 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.