Ansichten (Views ) in MySQL 

Vorbereitung

Zum Nachvollziehen der folgenden Beispiele kann das folgende Skript in einen MySQL-Klienten kopiert werden. Der Inhalt dieses Skripts kann aber ansonsten ignoriert werden. Beim Kopieren ist darauf zu achten, nur genau den Text von »DROP« bis zum letzten »;« zu kopieren und dann die Eingabetaste zu drücken.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE ZAHL ( I VARCHAR ( 255 ), DE VARCHAR ( 255 ), EN VARCHAR ( 255 ) ); 
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( '0', 'NULL', 'ZERO' );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( '1', 'EINS', 'ONE'  );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( '2', 'ZWEI', 'TWO'  );
SELECT * FROM ZAHL;

Dokumentation

-- "Die Zahl I hat den deutschen Namen DE und den englischen Namen EN."

Verwendung von Abfragespezifikationen

Wir zeigen zunächst die Tabelle »ZAHL«.

Konsole (MySQL, E92, C99, C03)

SELECT * FROM ZAHL;

+------+------+------+
| I    | DE   | EN   |
+------+------+------+
| 0    | NULL | ZERO |
| 1    | EINS | ONE  |
| 2    | ZWEI | TWO  |
+------+------+------+

Es kann sein, daß öfter eine Projektion auf die beiden Spalten »DE« und »EN« benötigt wird, die im folgenden gezeigt wird.

Konsole (MySQL, E92, C99, C03)

SELECT EN, DE FROM ZAHL;
-- "Eine bestimmte Zahl hat die englische Bezeichnung EN und die deutsche Bezeichnung DE."

+------+------+
| EN   | DE   |
+------+------+
| ZERO | NULL |
| ONE  | EINS |
| TWO  | ZWEI |
+------+------+

Um die Projektion zu erhalten wurde oben die Abfragespezifikation »SELECT EN, DE FROM ZAHL« verwendet.

Ansichten 

Für eine spätere Wiederverwendung kann eine Abfragespezifikation wie »SELECT EN, DE FROM ZAHL« unter einem Namen abgespeichert  werden. Dies ist besonders bei längeren und komplizierteren Abfragespezifikationen hilfreich.

Solche eine abgespeicherte Abfrage wird auch als eine Ansicht  bezeichnet. Weitere Bezeichnungen dafür sind: „Sicht“ und „View“ (von englisch: “view ”, der Autor dieses Textes bevorzugt das Genus „das View“ [sächlich], man findet aber im Web öfter „der View“ oder „die View“.).

Eine Ansicht ist eine unter einem Namen abgespeicherte SELECT-Abfrage.

ISO-SQL

“A viewed table is a named derived table defined by a <view definition>. A viewed table is sometimes called a view. Base tables and views are identified by <table name>s. The same <table name>, in its fully qualified form, cannot be used for both a base table and a view.”

DIS 9075-2:2014(E), 4.15 Tables

Beispiel  zum Anlegen einer Ansicht

Die folgende Ansichtsdefinition erstellt eine Ansicht namens »ENDE« mit der Abfragespezifikation »SELECT EN, DE FROM ZAHL«.

Konsole (MySQL, E92, C99, C03)

CREATE VIEW ENDE AS
SELECT EN, DE FROM ZAHL;

Die Abfragespezifikation »SELECT EN, DE FROM ZAHL« ist nach Ausführung der Definition dann also unter dem Namen »ENDE« in der Datenbank abgespeichert.

Anlegen  von Ansichten

Eine Ansicht kann mit einer Ansichtsdefinition (CREATE-VIEW-Anweisung) erstellt werden.

Die Ansichtsdefinition (eine Schemadefinitionsanweisung)

 (11.31)
Ansichtsdefinition
     .------.      .----.     .------------.     .--.     .----------------------.
--->( CREATE )--->( VIEW )--->| Bezeichner |--->( AS )--->| Abfragespezifikation |--->
     '------'      '----'     '------------'     '--'     '----------------------'
                                Name der Ansicht            zu speichernde Abfragespezifikation
Abfragespezifikation
                 .------------------------------.
     .------.    |    .---------------------.   v    .--------------.    .------------------.
--->( SELECT )---'--->| Mengenspezifizierer |---'--->| Auswahlliste |--->| Tabellenausdruck |--->
     '------'         '---------------------'        '--------------'    '------------------'

Aussprachehinweise

create kriˈeɪt

view vju

as æz > əz

Namen von Ansichten

Für den Namen einer Ansicht sind alle Namen zulässig, die auch für einen Tabellenspeicher erlaubt sind. Die angelegten Ansichten werden, wie angelegte Tabellenspeicher, in der aktuellen Datenbank (dem aktuellen Schema) gespeichert.

Es darf nicht schon eine Tabelle mit dem gewählten Namen im aktuellen Schema vorhanden sein.

Einschränkungen von Ansichten

Die SELECT-Abfrage, welche als Ansicht definiert wird, darf bei MySQL  keinen  Spaltennamen mehrfach  enthalten.

MySQL 8.0

“A view must have unique column names with no duplicates, just like a base table.”

ISO-SQL

ISO-SQL  scheint hier keine eindeutigen Spaltennamen zu verlangen.

Semantik der Ansichtsdefinition

Mit der Ausführung einer Ansichtsdefinition wird die Abfragespezifikation dauerhaft in dem demjenigen Schema, das zuvor mit »USE« eingestellt wurde, unter dem angegebenen Namen abgespeichert.

Verwendung von Ansichten in MySQL 

Der Name einer Ansicht kann hinter »FROM« verwendet werden und steht dann für das Ergebnis  der in der Ansicht gespeicherten Abfrage.

Eine Ansicht wird also wie ein Tabellenspeicher (eine Basistabelle) als Datenquelle  verwendet.

Wir zeigen zunächst die schon bekannte Verwendung der Basistabelle »ZAHL« als Datenquelle hinter »FROM«:

Konsole (MySQL, E92, C99, C03)

SELECT * FROM ZAHL;

+------+------+------+
| I    | DE   | EN   |
+------+------+------+
| 0    | NULL | ZERO |
| 1    | EINS | ONE  |
| 2    | ZWEI | TWO  |
+------+------+------+

Nun zeigen wir, wie auch die Ansicht »ENDE« als Datenquelle hinter »FROM« verwendet werden kann:

Konsole (MySQL, E92, C99, C03)

SELECT * FROM ENDE;

+------+------+
| EN   | DE   |
+------+------+
| ZERO | NULL |
| ONE  | EINS |
| TWO  | ZWEI |
+------+------+

Der Wert der Abfragespezifikation »SELECT * FROM ENDE« (also die angezeigte Tabelle) ist in diesem Fall so, als sei der Wert der gespeicherten Abfragespezifikation »SELECT EN, DE FROM ZAHL« in eine Tabelle »ENDE« gespeichert worden, die nun als Datenquelle in der Abfrage »SELECT * FROM ENDE« verwendet wird.

Ansichten enthalten keine  Daten

In Ansichten werden keine  Werte gespeichert. Die Werte sind nur in den Tabellenspeichern  (Basistabellen) gespeichert. Daher benötigt auch eine Ansicht nur wenig Speicherplatz, auch, wenn ihre Abfrage eine umfangreiche Tabelle ergibt.

Aktualisierung von Ansichten in MySQL 

Bei jeder Verwendung einer Ansicht als Datenquelle wird die gespeicherte Abfrage erneut  ausgewertet, so daß bis zu diesem Zeitpunkt eingetretene Veränderungen an Tabellenspeichern (Basistabellen) sich auf den Wert der Ansicht auswirken.

Wir blicken zunächst durch die Ansicht »ENDE« auf die Basistabelle »ZAHL«:

Konsole (MySQL, E92, C99, C03)

SELECT * FROM ENDE;

+------+------+
| EN   | DE   |
+------+------+
| ZERO | NULL |
| ONE  | EINS |
| TWO  | ZWEI |
+------+------+

Nun fügen wir noch eine weitere Zeile  in die Basistabelle »ZAHL« ein:

Konsole (MySQL, E92, C99, C03)

INSERT INTO ZAHL ( I, DE, EN )
VALUES ( '3', 'DREI', 'THREE'  );

Wenn wir jetzt erneut durch die Ansicht »ENDE« auf die Basistabelle »ZAHL« schauen, so sehen wir auch die gerade eingefügte neue Zeile:

Konsole (MySQL, E92, C99, C03)

SELECT * FROM ENDE;

+-------+------+
| EN    | DE   |
+-------+------+
| ZERO  | NULL |
| ONE   | EINS |
| TWO   | ZWEI |
| THREE | DREI |
+-------+------+ 

Ansichten erscheinen  nur wie Tabellen, aber tatsächlich sind es Ansichten  von Basistabellen. Daher benötigen sie keinen eigenen Datenspeicher und daher wirken sich alle Änderungen an Basistabellen sofort auf alle folgenden Verwendungen von Ansichten aus.

Ein Sicht auf die Basistabelle, bei der die Spalte »I« verdeckt ist

 # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # 
+#-#-#-+------+------+ # # #
# #I# #| DE   | EN   |# # #
+#-#-#-+------+------+ # # #
# # # #| NULL | ZERO |# # #
|# # # | EINS | ONE  | # # #
# # # #| ZWEI | TWO  |# # #
+#-#-#-+------+------+ # # #
# # # # # # # # # # # # # #
 # # # # # # # # # # # # # #

Ansichten auf der Basis von Ansichten in MySQL 

Es ist auch erlaubt, eine Ansicht auf der Basis einer anderen Ansicht  anzulegen.

In dem folgenden Beispiel wird eine Ansicht »DE« angelegt, die eine Abfrage einer anderen Ansicht »ENDE« enthält.

Konsole (MySQL, E92, C99, C03)

CREATE VIEW DE AS
SELECT DE FROM ENDE;
SELECT * FROM DE;

+------+
| DE   |
+------+
| NULL |
| EINS |
| ZWEI |
+------+

Aufeinander aufgebaute Tabellen

   DE       (Ansicht)
   |
   v
  ENDE     (Ansicht)
   |
   v
  ZAHL     (Basistabelle)

Man kann nun den Begriff „Basistabelle“ als „Basis einer Hierarchie von Ansichten“ verstehen.

Ansichten und Tabellenspeicher sind Tabellen

Weil eine Ansicht, wie die Ansicht »ENDE«, in einer SELECT-Abfrage und an manchen anderen Stellen, wie ein Tabellenspeicher  verwendet werden kann, bezeichnen wir Tabellenspeicher und Ansichten hier auch zusammenfassend als Tabellen.

Das MySQL -Kommando »SHOW TABLES;« zeigt dementsprechen auch Ansichten als Tabellen an.

Die Bezeichnung „Tabelle“ ist eigentlich nicht ganz korrekt, da ein Tabellenspeicher ja keine Tabelle ist, sondern sie enthält. Genauso kann eine Ansicht zu unterschiedlichen Zeitpunkten unterschiedliche Tabellen darstellen, wie weiter oben gezeigt wurde. Man muß sich also darüber im klaren sein, daß Tabellenspeicher und Ansichten nicht dauerhaft dieselbe Tabelle darstellen, sondern zu jedem Zeitpunkt für eine bestimmte Tabelle stehen können. Daher wäre die Bezeichnung „Tabellenquelle“ hier noch genauer als „Tabelle“.

Dokumentation von Ansichten

Zur Dokumentation einer Ansicht wird man in der Praxis mindestens das Skript, mit dem sie angelegt wurde und eine Aussage- oder Phrasenform aufbewahren.

Definition

CREATE VIEW ENDE AS SELECT EN, DE FROM ZAHL;

Dokumentation

-- "Eine bestimmte Zahl hat die englische Bezeichnung EN und die deutsche Bezeichnung DE."

Definition

CREATE VIEW DE AS SELECT DE FROM ENDE;

Dokumentation

-- "Eine bestimmte Zahl hat die deutsche Bezeichnung DE."

Es ist nicht möglich, in der Datenbank direkt eine Dokumentation für ein View zu hinterlegen, obwohl man dafür eine eigene Tabelle anlegen könnte.

Nachlesen (Abfragen) der Definition

Wenn in der Praxis Ansichten für den dauerhaften Gebrauch angelegt werden, so werden sie dokumentiert, was auch die Aufbewahrung des Kommandos mit dem sie angelegt wurden beinhaltet.

Wenn man dann später nachlesen will, wie eine bestimmte Ansicht angelegt wurde, greift man also auf ihre Dokumentation zurück.

Es gibt kein  Kommando mit dem die Definition einer Ansicht genau so, wie sie eingegeben wurde, aus der Datenbank wieder erhalten werden kann. Jedoch gibt es ein Kommando, mit dem sich die eingegebene Definition näherungsweise wiedergewinnen läßt, welches wir später kennenlernen werden.

Löschen einer Ansicht

Das folgende Kommando zeigt das Löschen der Ansicht »DE«.

Konsole

DROP VIEW DE;

Die Ansichtslöschanweisung

 (11.33 p903a)
Ansichtsloeschanweisung
     .----.      .----.     .------------.
--->( DROP )--->( VIEW )--->| Bezeichner |--->
     '----'      '----'     '------------'
                              Name der Ansicht

Schemamanipulationsanweisung

Schemamanipulationsanweisung
        .--------------------------.    
---.--->| Tabellenloeschanweisung  |---.--->
   |    '--------------------------'   ^
   |    .--------------------------.   |
   '--->| Ansichtsloeschanweisung  |---'
        '--------------------------'

In Standard-SQL  kann durch »RESTRICT« oder »CASCADE« am Ende der Löschanweisung noch festgelegt werden, was passieren soll, wenn andere Teile der Datenbank die zu löschende Ansicht verwenden. In MySQL  können diese Wörter zwar auch verwendet werden, werden aber ignoriert.

Anlegen oder Ersetzen einer Ansicht

Das folgende Kommando zeigt das Anlegen der Ansicht »ENDE«. Dank des »OR REPLACE« gelingt dies auch, falls eine Ansicht mit dem Namen »ENDE« schon existieren sollte. Diese wird dann gelöscht, indem sie durch die neue Ansicht ersetzt wird.

Konsole (MySQL)

CREATE OR REPLACE VIEW ENDE AS SELECT EN, DE FROM ZAHL;

»OR REPLACE« ist eine Erweiterung von MySQL und kein  Teil von Standard-SQL.

Ein »CREATE OR REPLACE TABLE« ist weder in MySQL noch in Standard-SQL möglich.

Die Nutzen von Ansichten

• Ansichten können nützlich sein, um einmal erstellte Abfragen für eine spätere Verwendung abzuspeichern.
• Außerdem können komplizierte Abfragen übersichtlich in mehrere einfache Ansichten zerlegt werden.
• Ansichten erlauben es auch, komplizierte Abfragen für Benutzer, die SQL nicht so gut beherrschen, bereitzustellen.
• Es ist möglich, daß die Ergebnisse von Ansichten bei bestimmten Änderungen  an ihren Basistabellen unverändert  bleiben können (wenn die Abfragen der Ansichten entsprechend angepaßt werden, um Änderungen auszugleichen). Dann erlauben sie es, alle auf sie aufbauenden Abfragen unverändert beizubehalten, ohne daß diese erst aufwendig an die Änderungen der Basistabellen angepaßt werden müssen. Entsprechend sind sie auch gut als Zugangspunkte für Programme einer Programmiersprache geeignet, die dann bei Änderungen an Basistabellen nicht  angepaßt werden müssen.
• Es ist auch oft möglich, eine Tabelle durch eine Ansicht zu ersetzen, ohne daß danach Anpassungen an Zugriffen über den Namen jener Tabelle notwendig werden.
• Benutzern können Zugriffsrechte  gegeben werden, die auf bestimmte Ansichten eingeschränkt  sind. Auf diese Weise kann kontrolliert werden, welche Möglichkeiten ein Benutzer genau haben soll.
• Die Normalisierungsanforderungen gelten nur für Basistabellen, nicht  für abgeleitete Tabellen und Views. Daher eröffnen Views die Möglichkeit Tabellen, die nicht in Normalform sind, ohne schlechtes Gewissen abzulegen.

Hinweis zu Übungsaufgaben

Ab jetzt können bei der Bearbeitung von Übungsaufgaben immer auch Ansichten angelegt  werden, falls dies als hilfreich erscheint, auch wenn das Anlegen von Ansichten in der Aufgabenstellung nicht  explizit verlangt oder erwähnt wird.

Übungsaufgaben

Zur Vorbereitung der folgenden Übungsaufgaben sollte das folgende Skript erneut ausgeführt werden, damit alle zwischenzeitlich angelegten Ansichten gelöscht  werden.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE ZAHL ( I VARCHAR ( 255 ), DE VARCHAR ( 255 ), EN VARCHAR ( 255 ) ); 
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( '0', 'NULL', 'ZERO' );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( '1', 'EINS', 'ONE'  );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( '2', 'ZWEI', 'TWO'  );

/   Ansichten unter Bezug auf Basistabellen definieren

Erstellen Sie eine Ansicht »ENGLISCH« basierend auf der Basistabelle »ZAHL«, so daß die folgende SELECT-Anweisung die folgende Ausgabe erzeugt.

Protokoll

SELECT * FROM ENGLISCH;

+------+-------+
| I    | EN    |
+------+-------+
| 0    | ZERO  |
| 1    | ONE   |
| 2    | TWO   |
+------+-------+

Zur Überprüfung und besseren Übersicht, sollte eine Ansicht direkt nach dem Anlegen immer mit »SELECT * FROM« abgefragt werden, um die abgeleitete Tabelle zu sehen.

/   Ansichten unter Bezug auf andere Ansichten definieren

Erstellen Sie eine Ansicht »EN« (die keinen direkten Bezug auf die Basistabelle »ZAHL« enthält) basierend auf der Ansicht »ENGLISCH«, so daß die folgende SELECT-Anweisung die folgende Ausgabe erzeugt.

Protokoll

SELECT * FROM EN;

+-------+
| EN    |
+-------+
| ZERO  |
| ONE   |
| TWO   |
+-------+

Quellen ⃗

Quellen laut Normentwurf von 2011

11.31 <view definition>

Quellen laut Normentwurf von 2014

11.32 <view definition>

11.33 <drop view statement>

Zitate zu Ansichten ⃗

aus „Datenbanken“ von Stefan Brass, Universität Halle, 2006.

● Sichten können auch in der Definition anderer Sichten verwendet werden.

● Auf diese Art können komplexe Anfragen Schritt für Schritt aufgebaut werden.

http://users.informatik.uni-halle.de/~brass/db06/dc_views.pdf