Views in SQL: So nutzen Sie virtuelle Tabellen effektiv

Einführung

Structured Query Language (SQL) verwendet verschiedene Datenstrukturen, wobei Tabellen zu den am häufigsten verwendeten gehören. Tabellen haben jedoch gewisse Einschränkungen. Zum Beispiel können Sie den Zugriff der Benutzer nicht nur auf einen Teil einer Tabelle beschränken. Ein Benutzer muss Zugriff auf die gesamte Tabelle erhalten, nicht nur auf einige Spalten davon.

Ein weiteres Beispiel: Angenommen, Sie möchten Daten aus mehreren anderen Tabellen zu einer neuen Struktur zusammenfassen, ohne die ursprünglichen Tabellen zu löschen. Sie könnten eine neue Tabelle erstellen, aber dann hätten Sie redundante Daten an mehreren Stellen gespeichert. Das könnte viele Unannehmlichkeiten verursachen: Wenn sich einige Ihrer Daten ändern, müssten Sie diese an mehreren Stellen aktualisieren. In solchen Fällen sind Views hilfreich.

In SQL ist eine View eine virtuelle Tabelle, deren Inhalt das Ergebnis einer spezifischen Abfrage auf eine oder mehrere Tabellen, sogenannte Basistabellen, ist. Diese Anleitung bietet einen Überblick darüber, was SQL-Views sind und warum sie nützlich sein können. Sie zeigt auch, wie Sie Views mit der Standard-SQL-Syntax erstellen, abfragen, ändern und löschen können.

Voraussetzungen für Views in SQL

Um dieser Anleitung zu folgen, benötigen Sie einen Computer mit einem relationalen Datenbankverwaltungssystem (RDBMS), das SQL verwendet. Die Anweisungen und Beispiele in dieser Anleitung wurden mit der folgenden Umgebung validiert:

• Ein Server mit Ubuntu 20.04, ein Nicht-Root-Benutzer mit Sudo-Administrationsrechten und eine Firewall, die mit UFW konfiguriert ist, wie in unserer Einrichtungsanleitung für Ubuntu 20.04 beschrieben.
• MySQL ist auf dem Server installiert und gesichert, wie in der Anleitung zur Installation von MySQL auf Ubuntu 20.04 beschrieben. Diese Anleitung wurde mit einem neu erstellten Benutzer, wie in Schritt 3 beschrieben, überprüft.

Hinweis: Viele RDBMS verwenden ihre eigenen Implementierungen von SQL. Obwohl die in diesem Tutorial beschriebenen Befehle auf den meisten RDBMS funktionieren, kann die genaue Syntax oder Ausgabe abweichen, wenn Sie sie auf einem anderen System als MySQL testen.

Sie benötigen auch eine Datenbank mit einigen Tabellen, die mit Beispieldaten geladen sind, damit Sie das Erstellen und Arbeiten mit Views in SQL üben können. Wir empfehlen Ihnen, den Abschnitt „Verbinden mit MySQL und Einrichten einer Beispieldatenbank“ zu lesen, um zu erfahren, wie Sie eine Verbindung zu einem MySQL-Server herstellen und die Testdatenbank erstellen, die in den Beispielen dieser Anleitung verwendet wird.

Verbindung zu MySQL herstellen und eine Beispieldatenbank einrichten

Falls Ihr SQL-Datenbanksystem auf einem Remote-Server ausgeführt wird, verbinden Sie sich über SSH mit Ihrem Server von Ihrem lokalen Rechner aus:

ssh sammy@Ihre_Server_IP

Öffnen Sie dann die MySQL-Server-Eingabeaufforderung, wobei „sammy“ durch den Namen Ihres MySQL-Benutzerkontos ersetzt wird:

mysql -u sammy -p

Erstellen Sie in der Eingabeaufforderung eine Datenbank namens views_db:

CREATE DATABASE views_db;

Falls die Datenbank erfolgreich erstellt wurde, erhalten Sie eine Ausgabe wie die folgende:

Output
Query OK, 1 row affected (0.01 sec)

Um die Datenbank views_db auszuwählen, führen Sie die folgende USE-Anweisung aus:

USE views_db;

Output

Database changed

Nach der Auswahl von views_db erstellen Sie ein paar Tabellen darin.

Um den Beispielen in dieser Anleitung zu folgen, stellen Sie sich vor, Sie betreiben einen Hundebetreuungsservice zu Hause. Sie beschließen, eine SQL-Datenbank zu verwenden, um Informationen über jeden Hund zu speichern, den Sie für den Service angemeldet haben, sowie über jeden Hundebetreuer, den Ihr Service beschäftigt. Um alles organisiert zu halten, benötigen Sie zwei Tabellen: eine für die Mitarbeiter und eine für die betreuten Hunde. Die Tabelle für Ihre Mitarbeiter enthält die folgenden Spalten:

• emp_id: Eine Identifikationsnummer für jeden von Ihnen angestellten Hundebetreuer, dargestellt mit dem Datentyp „int“. Diese Spalte dient als Primärschlüssel der Tabelle, was bedeutet, dass jeder Wert als eindeutiger Bezeichner für die jeweilige Zeile fungiert. Da jeder Wert in einem Primärschlüssel eindeutig sein muss, wird auf diese Spalte auch eine UNIQUE-Beschränkung angewendet.
• emp_name: Der Name jedes Mitarbeiters, dargestellt durch den Datentyp „varchar“ mit maximal 20 Zeichen.

Führen Sie die folgende CREATE TABLE-Anweisung aus, um eine Tabelle namens „employees“ mit diesen beiden Spalten zu erstellen:

CREATE TABLE employees (
emp_id int UNIQUE,
emp_name varchar(20),
PRIMARY KEY (emp_id)
);

Die andere Tabelle, die jeden Hund darstellt, enthält die folgenden sechs Spalten:

• dog_id: Eine Identifikationsnummer für jeden Hund, der im Hotel bleibt, dargestellt mit dem Datentyp „int“. Wie die Spalte „emp_id“ in der „employees“-Tabelle dient auch diese Spalte als Primärschlüssel der „dogs“-Tabelle.
• dog_name: Der Name jedes Hundes, dargestellt durch den Datentyp „varchar“ mit maximal 20 Zeichen.
• walker: Diese Spalte speichert die Mitarbeiter-ID-Nummer des Mitarbeiters, der für die Betreuung des jeweiligen Hundes zuständig ist.
• walk_distance: Die Entfernung, die jeder Hund bei einem Spaziergang zurücklegen sollte, dargestellt durch den Datentyp „decimal“. In diesem Fall gibt die Dezimal-Deklaration eine Genauigkeit von drei mit einer Skalierung von zwei an, was bedeutet, dass alle Werte in dieser Spalte maximal drei Stellen haben können, wobei zwei dieser Stellen rechts vom Dezimalkomma liegen.
• meals_perday: Das Hundehotel versorgt jeden Hund mit einer bestimmten Anzahl von Mahlzeiten pro Tag. Diese Spalte enthält die Anzahl der Mahlzeiten, die jeder Hund pro Tag erhalten soll, wie vom Besitzer gewünscht, und verwendet „int“ als Ganzzahl.
• cups_permeal: Diese Spalte listet auf, wie viele Tassen Trockenfutter jeder Hund pro Mahlzeit erhalten soll. Wie die Spalte „walk_distance“ wird auch diese Spalte als „decimal“ dargestellt. Allerdings hat sie eine Skalierung von drei mit einer Genauigkeit von zwei, was bedeutet, dass Werte in dieser Spalte bis zu drei Stellen haben können, wobei zwei dieser Stellen rechts vom Dezimalkomma liegen.

Um sicherzustellen, dass die Spalte „walker“ nur Werte enthält, die gültige Mitarbeiter-ID-Nummern darstellen, beschließen Sie, eine Fremdschlüsselbeschränkung auf die Spalte „walker“ anzuwenden, die sich auf die Spalte „emp_ID“ der „employees“-Tabelle bezieht. Eine Fremdschlüsselbeschränkung drückt eine Beziehung zwischen zwei Tabellen aus, indem sie erfordert, dass Werte in der Spalte, auf die der Fremdschlüssel angewendet wurde, bereits in der Spalte existieren müssen, auf die er verweist. Im folgenden Beispiel erfordert die Fremdschlüsselbeschränkung, dass jeder Wert, der der Spalte „walker“ in der „dogs“-Tabelle hinzugefügt wird, bereits in der Spalte „emp_ID“ der „employees“-Tabelle vorhanden ist.

Erstellen Sie eine Tabelle namens „dogs“ mit diesen Spalten mit dem folgenden Befehl:

CREATE TABLE dogs (
dog_id int UNIQUE,
dog_name varchar(20),
walker int,
walk_distance decimal(3,2),
meals_perday int,
cups_permeal decimal(3,2),
PRIMARY KEY (dog_id),
FOREIGN KEY (walker)
REFERENCES employees(emp_ID)
);

Nun können Sie beide Tabellen mit einigen Beispieldaten laden. Führen Sie die folgende INSERT INTO-Operation aus, um drei Zeilen Daten zu der „employees“-Tabelle hinzuzufügen, die drei Ihrer Mitarbeiter darstellen:

INSERT INTO employees
VALUES
(1, 'Peter'),
(2, 'Paul'),
(3, 'Mary');

Führen Sie dann die folgende Operation aus, um sieben Datenzeilen in die Tabelle „dogs“ einzufügen:

INSERT INTO dogs
VALUES
(1, 'Dottie', 1, 5, 3, 1),
(2, 'Bronx', 3, 6.5, 3, 1.25),
(3, 'Harlem', 3, 1.25, 2, 0.25),
(4, 'Link', 2, 2.75, 2, 0.75),
(5, 'Otto', 1, 4.5, 3, 2),
(6, 'Juno', 1, 4.5, 3, 2),
(7, 'Zephyr', 3, 3, 2, 1.5);

Damit sind Sie bereit, dem Rest der Anleitung zu folgen und zu lernen, wie Sie Views in SQL verwenden.

Verständnis und Erstellung von Views in SQL

Je nach Szenario können SQL-Abfragen überraschend komplex werden. In der Tat ist einer der Hauptvorteile von SQL, dass es viele verschiedene Optionen und Klauseln enthält, mit denen Sie Ihre Daten mit einem hohen Maß an Granularität und Spezifität filtern können. Wenn Sie häufig komplexe Abfragen ausführen müssen, kann es schnell frustrierend werden, diese immer wieder manuell einzugeben. Eine Möglichkeit, diese Probleme zu umgehen, ist die Verwendung von Views.

Wie in der Einführung erwähnt, sind Views in SQL virtuelle Tabellen. Das bedeutet, dass eine View zwar funktional einer Tabelle ähnlich ist, jedoch eine andere Art von Struktur darstellt, da die View keine eigenen Daten enthält. Stattdessen werden Daten aus einer oder mehreren Basistabellen geladen, die die tatsächlichen Daten enthalten. Das einzige, was ein DBMS über eine View speichert, ist deren Struktur. Views werden manchmal auch als gespeicherte Abfragen bezeichnet, weil sie im Wesentlichen das sind: Abfragen, die unter einem bestimmten Namen gespeichert wurden, um einen bequemen Zugriff zu ermöglichen.

Um Views in SQL besser zu verstehen, betrachten Sie das folgende Beispiel. Angenommen, Ihr Hundebetreuungsunternehmen läuft gut und Sie müssen einen täglichen Zeitplan für alle Ihre Mitarbeiter ausdrucken. Der Plan sollte jeden Hund auflisten, der vom Service betreut wird, den Mitarbeiter, der für ihn verantwortlich ist, die tägliche Gehstrecke jedes Hundes, die Anzahl der Mahlzeiten, die er pro Tag bekommen soll, und die Menge an Trockenfutter, die er bei jeder Mahlzeit erhalten soll.

Mit Ihren SQL-Kenntnissen erstellen Sie eine Abfrage mit den Beispieldaten aus dem vorherigen Schritt, um all diese Informationen für den Plan abzurufen. Beachten Sie, dass diese Abfrage die JOIN-Syntax enthält, um Daten aus den Tabellen „employees“ und „dogs“ zu ziehen:

SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal
FROM employees JOIN dogs ON emp_ID = walker;

Output

+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+
7 rows in set (0.00 sec)

Angenommen, Sie müssen diese Abfrage regelmäßig ausführen. Es könnte mühsam werden, die Abfrage wiederholt einzugeben, insbesondere wenn es sich um längere und komplexere Abfragen handelt. Auch wenn Sie kleinere Änderungen vornehmen oder die Abfrage erweitern müssten, könnte es frustrierend sein, bei der Fehlersuche auf potenzielle Syntaxfehler zu stoßen.

In solchen Fällen könnte eine View nützlich sein, da sie im Wesentlichen eine Tabelle ist, die aus den Ergebnissen einer Abfrage abgeleitet wird.

Um eine View zu erstellen, verwenden die meisten RDBMS die folgende Syntax:

Beispiel für CREATE VIEW-Syntax

CREATE VIEW view_name
AS
SELECT statement;

Nach der CREATE VIEW-Anweisung definieren Sie einen Namen für die View, den Sie später zur Referenzierung verwenden werden. Nach dem Namen geben Sie das Schlüsselwort „AS“ und dann die SELECT-Abfrage ein, deren Ausgabe Sie speichern möchten. Die Abfrage, die Sie zur Erstellung Ihrer View verwenden, kann jede gültige SELECT-Anweisung sein. Die von Ihnen eingefügte Anweisung kann eine oder mehrere Basistabellen abfragen, solange Sie die richtige Syntax verwenden.

Versuchen Sie, eine View mit der vorherigen Beispielabfrage zu erstellen. Diese CREATE VIEW-Operation benennt die View „walking_schedule“:

CREATE VIEW walking_schedule
AS
SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal 
FROM employees JOIN dogs
ON emp_ID = walker;

Daraufhin können Sie diese View wie jede andere Tabelle verwenden und mit ihr interagieren. Zum Beispiel könnten Sie die folgende Abfrage ausführen, um alle in der View enthaltenen Daten zurückzugeben:

SELECT * FROM walking_schedule;

Output

+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+
7 rows in set (0.00 sec)

Obwohl diese View aus zwei anderen Tabellen abgeleitet ist, können Sie mit der View keine Daten aus diesen Tabellen abfragen, es sei denn, sie sind bereits in der View vorhanden. Die folgende Abfrage versucht, die Spalte „walker“ aus der „walking_schedule“-View abzurufen, aber diese Abfrage schlägt fehl, da die View keine Spalten mit diesem Namen enthält:

SELECT walker FROM walking_schedule;

Output

ERROR 1054 (42S22): Unknown column 'walker' in 'field list'

Diese Ausgabe liefert eine Fehlermeldung, da die Spalte „walker“ Teil der „dogs“-Tabelle ist, jedoch nicht in der von Ihnen erstellten View enthalten war.

Sie können auch Abfragen ausführen, die Aggregatfunktionen enthalten und die Daten innerhalb einer View manipulieren. Das folgende Beispiel verwendet die MAX-Aggregatfunktion zusammen mit GROUP BY, um die längste Entfernung zu ermitteln, die jeder Mitarbeiter an einem bestimmten Tag gehen muss:

SELECT emp_name, MAX(walk_distance) AS longest_walks
FROM walking_schedule GROUP BY emp_name;

Output

+----------+---------------+
| emp_name | longest_walks |
+----------+---------------+
| Peter    |          5.00 |
| Paul     |          2.75 |
| Mary     |          6.50 |
+----------+---------------+
3 rows in set (0.00 sec)

Wie bereits erwähnt, können Views in SQL auch dazu verwendet werden, den Zugriff eines Benutzers auf eine Datenbank zu beschränken, sodass er nur auf eine View und nicht auf eine gesamte Tabelle oder Datenbank zugreifen kann.

Angenommen, Sie stellen einen Büroleiter ein, um Ihnen bei der Verwaltung des Zeitplans zu helfen. Sie möchten ihm Zugang zu den Planungsinformationen gewähren, jedoch zu keinen anderen Daten in der Datenbank. Dazu könnten Sie ein neues Benutzerkonto für ihn in Ihrer Datenbank erstellen:

CREATE USER 'office_mgr'@'localhost' IDENTIFIED BY 'password';

Sie könnten diesem neuen Benutzer dann Lesezugriff auf die View „walking_schedule“ und nur auf diese View gewähren, indem Sie eine GRANT-Anweisung wie die folgende verwenden:

GRANT SELECT ON views_db.walking_schedule to 'office_mgr'@'localhost';

Daraufhin könnte jeder, der Zugriff auf das MySQL-Benutzerkonto „office_mgr“ hat, nur SELECT-Abfragen auf der „walking_schedule“-View ausführen.

Ändern und Löschen von Views in SQL

Falls Sie Daten in einer der Tabellen hinzufügen oder ändern, aus denen die View abgeleitet wird, werden die relevanten Daten automatisch in die View hinzugefügt oder aktualisiert. Führen Sie die folgende INSERT INTO-Anweisung aus, um eine weitere Zeile zur Tabelle „dogs“ hinzuzufügen:

INSERT INTO dogs VALUES (8, 'Charlie', 2, 3.5, 3, 1);

Rufen Sie dann alle Daten aus der „walking_schedule“-View erneut ab:

SELECT * FROM walking_schedule;

Output

+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Paul     | Charlie  |          3.50 |            3 |         1.00 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+
8 rows in set (0.00 sec)

Diesmal gibt es eine weitere Zeile in den Abfrageergebnissen, die die Daten widerspiegelt, die Sie der Tabelle „dogs“ hinzugefügt haben.

Die View zieht jedoch weiterhin die gleichen Daten aus den gleichen Basistabellen, sodass dieser Vorgang die View selbst nicht verändert hat.

Viele RDBMS ermöglichen es Ihnen, die Struktur einer View nach deren Erstellung zu aktualisieren, indem Sie die Syntax CREATE OR REPLACE VIEW verwenden:

Beispiel für CREATE OR REPLACE VIEW-Syntax

CREATE OR REPLACE VIEW view_name
AS
new SELECT statement

Mit dieser Syntax wird das Datenbanksystem, falls eine View mit dem Namen „view_name“ bereits existiert, diese aktualisieren, sodass sie die durch die neue SELECT-Anweisung zurückgegebenen Daten darstellt. Falls noch keine View mit diesem Namen existiert, wird das DBMS eine neue erstellen.

Angenommen, Sie möchten die View „walking_schedule“ ändern, um die tägliche Gesamtfuttermenge pro Hund anzugeben. Sie können die View mit folgendem Befehl ändern:

CREATE OR REPLACE VIEW walking_schedule
AS
SELECT emp_name, dog_name, walk_distance, meals_perday, (cups_permeal * meals_perday) AS total_kibble 
FROM employees JOIN dogs ON emp_ID = walker;

Wenn Sie diese View nun abfragen, wird das Ergebnis die neuen Daten der View widerspiegeln:

SELECT * FROM walking_schedule;

Output

+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | total_kibble |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         3.00 |
| Peter    | Otto     |          4.50 |            3 |         6.00 |
| Peter    | Juno     |          4.50 |            3 |         6.00 |
| Paul     | Link     |          2.75 |            2 |         1.50 |
| Paul     | Charlie  |          3.50 |            3 |         3.00 |
| Mary     | Bronx    |          6.50 |            3 |         3.75 |
| Mary     | Harlem   |          1.25 |            2 |         0.50 |
| Mary     | Zephyr   |          3.00 |            2 |         3.00 |
+----------+----------+---------------+--------------+--------------+
8 rows in set (0.00 sec)

Wie die meisten anderen in SQL erstellbaren Strukturen, können Sie Views in SQL mit der DROP-Syntax löschen:

Beispiel für DROP VIEW-Syntax

DROP VIEW view_name;

Falls Sie beispielsweise die View „walking_schedule“ löschen möchten, können Sie dies mit dem folgenden Befehl tun:

DROP VIEW walking_schedule;

Dies entfernt die View „walking_schedule“ aus Ihrer Datenbank, aber keine der in der View enthaltenen Daten. Diese werden erst gelöscht, wenn sie aus den Basistabellen entfernt werden.

Fazit

Durch diese Anleitung haben Sie gelernt, was SQL-Views sind und wie Sie sie erstellen, abfragen, ändern und löschen.

Obwohl die Befehle aus unseren Beispielen in den meisten relationalen Datenbanken funktionieren sollten, beachten Sie, dass jede SQL-Datenbank ihre eigene Implementierung der Sprache verwendet. Sie sollten die offizielle Dokumentation Ihres DBMS konsultieren, um eine vollständigere Beschreibung jedes Befehls und ihrer gesamten Optionen zu erhalten.

Falls Sie mehr über SQL erfahren möchten, empfehlen wir Ihnen, die anderen Tutorials dieser Serie zu lesen.