Einführung in SQL: Erstellen von Views
Aus Wikibooks
VIEWs sind Abfragen, die in der Datenbank als Objekt gespeichert sind. Sie können als virtuelle Tabellen verstanden werden, deren Inhalt und Struktur auf anderen Tabellen oder Views basieren, und können in (fast) jedem SELECT-Befehl anstelle einer "echten" Tabelle verwendet werden.
Inhaltsverzeichnis |
[Bearbeiten] Allgemeine Hinweise
Bei einer View wird die Abfrage in der Datenbank gespeichert, aber nicht das Ergebnis. Bei jedem neuen Aufruf der View wird die dahinterliegende Abfrage neu ausgeführt, denn sie soll ja das Ergebnis anhand der aktuellen Daten bestimmen.
Die Abfragen, auf denen Views basieren, können grundsätzlich alle Klauseln wie eine normale Abfrage enthalten. Somit ist es möglich, bestimmte Daten in einer View zu selektieren und zu gruppieren. Hierbei können die Daten aus mehreren Tabellen oder Views selektiert werden.
Je nach DBMS und Situation kann eine einzelne Klausel der View unwirksam sein oder zu unklaren Ergebnissen führen.
- Eine ORDER BY-Klausel der View wird ignoriert, wenn der SELECT-Befehl, der sie benutzt, selbst eine Sortierung verwendet.
- Bei einer Beschränkung durch LIMIT o.ä. weiß das DBMS oft nicht, nach welchen Regeln diese Beschränkung verwirklicht werden soll.
- WHERE-Bedingungen können nur fest eingebaut werden, aber nicht mit variablen Parametern.
Views dienen dazu, die Komplexität der Abfragen für den Anwender zu reduzieren. Die View wird mit ihrer (komplexen) Abfrage angelegt, und die Nutzer können die Daten dieser View abfragen.
Weiterhin können Views genutzt werden, um den Zugriff auf bestimmte Daten einzuschränken. Nutzer können Zugriff nur auf bestimmte Views bekommen. Somit lässt sich der Zugriff für einzelne Nutzer auf bestimmte Daten (Spalten und Datensätze) beschränken.
[Bearbeiten] Eine View anlegen und benutzen
Views werden mit dem Befehl CREATE VIEW mit folgender Syntax angelegt.
CREATE VIEW <View-Name>
[ ( <Spaltennamen> ) ]
AS <Select-Ausdruck> ;
Zu dieser Definition gehören folgende Bestandteile:
- CREATE VIEW kennzeichnet den Befehl.
- Unter <View-Name> ist eine Bezeichnung anzugeben, unter der die View in einem SELECT-Befehl angesprochen wird. Dieser Name muss eindeutig sein und darf auch kein Name einer "echten" Tabelle sein.
- Als <Select-Ausdruck> wird ein (beliebiger) SELECT-Befehl eingetragen.
- Es wird empfohlen, möglichst bei allen Spalten mit einem Alias zu arbeiten.
- Diese können wahlweise vor dem AS in Klammern angegeben werden oder (wie üblich) Teil des <Select-Ausdruck>s sein.
Die View wird dann wie jede Tabelle benutzt, z.B. einfach:
SELECT * FROM <View-Name>
Oder auch als Teil einer komplexen Abfrage:
SELECT <irgendwas>
FROM <Tabelle>
JOIN <View-Name> ON /* usw. */
[Bearbeiten] Eine einfache View
Im einfachsten Fall greifen wir auf eine einfache Verknüpfung zweier Tabellen zu und verbinden dies mit einer festen Suchbedingung.
Erstelle eine View, die eine Liste aller Fahrzeugtypen deutscher Hersteller anzeigt.
SQL-Quelltext:
CREATE VIEW Deutscher_Fahrzeugtyp AS SELECT DISTINCT ft.Bezeichnung AS Fahrzeugtyp, fh.Name AS Hersteller FROM Fahrzeugtyp ft JOIN Fahrzeughersteller fh ON ft.Hersteller_ID = fh.ID WHERE fh.Land = 'Deutschland';
Die Abfrage basiert auf den beiden Tabellen Fahrzeugtyp und Fahrzeughersteller. Es werden nur die Spalten Bezeichnung und Name abgefragt; durch die WHERE-Klausel wird das Ergebnis auf Fahrzeuge deutscher Hersteller beschränkt. Für die Spalten werden Spalten-Aliase genutzt.
Diese View kann dann wie eine "normale" Tabellen in Abfragen genutzt werden.
SQL-Quelltext:
SELECT * FROM Deutscher_Fahrzeugtyp ORDER BY Hersteller;
SQL-Ausgabe:
FAHRZEUGTYP HERSTELLER A3 Audi A4 Audi 325 BMW 525 BMW Z3 BMW Fiesta Ford Focus Ford /* usw. */
In diesem Fall kann die ORDER BY-Klausel ebensogut Teil der View sein; das untersuchen wir später noch.
[Bearbeiten] Eine View mit variabler Selektion
Es klappt leider nicht, in eine View eine WHERE-Klausel einen (variablen) Parameter einzubauen, der erst im SELECT mit einem konkreten Wert versehen wird.
Gesucht wird eine Abfrage über die Mitarbeiter einer Abteilung; am Anfang soll der Abteilungsleiter stehen, danach alphabetisch die betreffenden Mitarbeiter. Die Nummer der Abteilung soll nicht fest vorgegeben werden, sondern variabel sein.
SQL-Quelltext:
CREATE VIEW Mitarbeiter_in_Abteilung AS SELECT Personalnummer, Name, Vorname, Geburtsdatum FROM Mitarbeiter WHERE Abteilung_ID = ? ORDER BY Ist_Leiter, Name, Vorname;
SQL-Ausgabe:
Commit nicht möglich Invalid token.
Auch Alternativen für das Fragezeichen führen nicht zum Ziel. Es bleibt nur ein kleiner Umweg, nämlich die Abteilung_ID in der View zu berücksichtigen und später für WHERE zu nutzen:
SQL-Quelltext:
CREATE VIEW Mitarbeiter_in_Abteilung ( Pers, Name, Vorname, Geburtsdatum, Abt ) AS SELECT Personalnummer, Name, Vorname, Geburtsdatum, Abteilung_ID FROM Mitarbeiter ORDER BY Ist_Leiter, Name, Vorname;
Damit können alle Angaben einer bestimmten Abteilung geholt werden; die Spalte Abt bleibt zur Verdeutlichung stehen:
SQL-Quelltext:
SELECT * FROM Mitarbeiter_in_Abteilung WHERE Abt = 5;
SQL-Ausgabe:
PERS NAME VORNAME GEBURTSDATUM ABT 50001 Pohl Helmut 27.10.1980 5 50002 Braun Christian 05.09.1966 5 50004 Kalman Aydin 17.12.1976 5 50003 Polovic Frantisek 26.11.1961 5
Und siehe da: zuerst kommt der Abteilungsleiter, danach die anderen Mitarbeiter in alphabetischer Reihenfolge.
Hinweis: Eine Alternative zu einer VIEW mit variabler WHERE-Bedingung ist eine StoredProcedure, die diese Abfrage enthält und einen Wert als Parameter entgegennimmt.
[Bearbeiten] Probleme mit der Sortierung
Ändern wir die obige View deutscher Fahrzeuge dahin, dass die Sortierung nach Hersteller fest eingebaut wird.
- Bitte beachten Sie: Wenn Sie oben die View Deutscher_Fahrzeugtyp fest gespeichert haben, müssen Sie in diesem Abschnitt einen anderen Namen verwenden oder stattdessen etwas wie CREATE OR ALTER (siehe die DBMS-Dokumentation) benutzen.
SQL-Quelltext:
CREATE VIEW Deutscher_Fahrzeugtyp ( Typ, Firma ) AS SELECT DISTINCT ft.Bezeichnung, fh.Name AS Firma FROM Fahrzeugtyp ft JOIN Fahrzeughersteller fh ON ft.Hersteller_ID = fh.ID WHERE fh.Land = 'Deutschland' ORDER BY Firma;
Bitte beachten Sie, dass in diesem Fall der Spalten-Alias Firma auch Teil des SELECT-Befehls sein muss, damit er in der ORDER BY-Klausel bekannt ist.
Jetzt kann diese Liste je nach Wunsch mit oder ohne Sortierung abgerufen werden:
SELECT * FROM Deutscher_Fahrzeugtyp; /* automatisch sortiert nach Firma */ SELECT * FROM Deutscher_Fahrzeugtyp ORDER BY Typ; /* speziell sortiert nach Typ */
[Bearbeiten] Views in Verbindung mit JOIN
Die obige Verknüpfung "Fahrzeugtyp plus Hersteller" benötigen wir in der Praxis ständig, nicht nur in der konkreten Abfrage nach deutschen Herstellern. Bisher – zum Beispiel unter Verknüpfung mehrerer Tabellen bei OUTER JOIN – haben wir beide Tabellen separat per JOIN eingebunden, mussten aber immer auf die Art des JOINs aufpassen. Das kann man einmalig durch eine fiktive Tabelle Fahrzeugart, also eine VIEW mit den benötigten Informationen steuern.
Eine solche VIEW erfüllt also mehrere Wünsche:
- Die eigentlichen Informationen werden getrennt gespeichert; es ist nicht nötig, bei jedem Fahrzeugtyp den Hersteller und sein Herkunftsland aufzuführen. Wie wir aus der Wirtschaftspolitik des Jahres 2009 wissen, kann sich das durchaus ändern; nach den Regeln der Normalisierung ist die separate Tabelle der Hersteller nicht nur sinnvoll, sondern notwendig.
- Bei jeder Abfrage des Fahrzeugtyps erhalten wir sofort auch den Hersteller.
- Jede solche Abfrage wird einfacher, weil eine Tabelle weniger benötigt wird.
- Das DBMS kennt seine VIEWs und hat sie "von Haus aus" optimiert; also wird jede solche Abfrage auch schneller ausgeführt.
- Auch bei dieser Aussage bin ich mir nicht theoretisch sicher. Aber nach allem, was ich über interne Datenbankstrukturen gelernt habe, gehe ich davon aus. Benutzer:Juetho
Das obige "einfache Beispiel" der VIEW müssen wir also nur ein wenig umschreiben:
Bereite eine (fiktive) Tabelle Fahrzeugart vor mit allen relevanten Informationen aus den Tabellen Fahrzeugtyp und Fahrzeughersteller.
SQL-Quelltext:
CREATE VIEW Fahrzeugart ( ID, Bezeichnung, Hersteller, Land ) AS SELECT ft.ID, ft.Bezeichnung, fh.Name, fh.Land FROM Fahrzeugtyp ft JOIN Fahrzeughersteller fh ON ft.Hersteller_ID = fh.ID;
Für den Anwender sieht es tatsächlich so aus, als hätten wir eine einfache Tabelle mit allen Angaben:
SQL-Quelltext:
SELECT * FROM Fahrzeugart ORDER BY Land, Hersteller, Bezeichnung;
SQL-Ausgabe: (Auszug)
ID BEZEICHNUNG HERSTELLER LAND 19 C30 Volvo 18 S40 Volvo 12 A3 Audi Deutschland 13 A4 Audi Deutschland 9 325 BMW Deutschland 10 525 BMW Deutschland 11 Z3 BMW Deutschland
Damit kann das letzte der Beispiele von OUTER JOIN vereinfacht werden.
Hole alle Dienstwagen (ggf. mit den zugehörigen Mitarbeitern) und nenne dazu alle Fahrzeugdaten.
SQL-Quelltext:
SELECT mi.Personalnummer AS MitNr, mi.Name, mi.Vorname, dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID, fa.Bezeichnung AS Typ, fa.Hersteller FROM Dienstwagen dw LEFT JOIN Mitarbeiter mi ON mi.ID = dw.Mitarbeiter_ID INNER JOIN Fahrzeugart fa ON fa.ID = dw.Fahrzeugtyp_ID;
SQL-Ausgabe: (Auszug)
MITNR NAME VORNAME DIW KENNZEICHEN TYPID TYP HERSTELLER
80001 Schindler Christina 8 DO-WB 428 14 A160 Mercedes-Benz
90001 Janssen Bernhard 9 DO-WB 429 14 A160 Mercedes-Benz
100001 Grosser Horst 10 DO-WB 4210 14 A160 Mercedes-Benz
110001 Eggert Louis 11 DO-WB 4211 14 A160 Mercedes-Benz
120001 Carlsen Zacharias 12 DO-WB 4212 14 A160 Mercedes-Benz
13 DO-WB 111 16 W211 (E-Klasse) Mercedes-Benz
50002 Braun Christian 14 DO-WB 352 2 Golf Volkswagen
50003 Polovic Frantisek 15 DO-WB 353 3 Passat Volkswagen
50004 Kalman Aydin 16 DO-WB 354 4 Kadett Opel
80002 Aliman Zafer 17 DO-WB 382 2 Golf Volkswagen
80003 Langer Norbert 18 DO-WB 383 3 Passat Volkswagen
80004 Kolic Ivana 19 DO-WB 384 4 Kadett Opel
Einige kleine Änderungen machen alles einfacher: Die Tabelle Fahrzeugtyp wird durch die View Fahrzeugart ersetzt; die JOIN-Verknüpfung auf Fahrzeughersteller wird ersatzlos gestrichen. Lediglich zur Verdeutlichung werden Tabellen-Alias und Spaltennamen geändert.
[Bearbeiten] DROP – eine View löschen
Views werden mit dem Befehl DROP VIEW gelöscht.
SQL-Quelltext:
DROP VIEW Deutscher_Fahrzeugtyp;
Hierbei wird nur die View als Objekt in der Datenbank gelöscht. Die Tabellen und Daten in den Tabellen, auf denen die View basiert, werden nicht gelöscht.
[Bearbeiten] Zusammenfassung
- Views sind Abfragen, die in der Datenbank als Objekt gespeichert werden.
- Views können die Komplexität für den Anwender reduzieren.
- Views können für eine detailierte Zugriffskontrolle genutzt werden.
- Views werden mit CREATE VIEW erstellt.
- Views werden mit DROP VIEW gelöscht.
[Bearbeiten] Übungen
Dies ist der Text von Aufgabe 1
Dies ist die Lösung von Aufgabe 1.
