MySQL-Zertifikat, Kapitel 14 ohne 14.2.1-14.2.3

14.1 Reasons to Use Views

Views sind spezielle Tabellen in MySQL, welche aus bestehenden Tabellen oder bereits erstellten Views generiert werden. Bei Views spricht man oftmals auch von „virtuellen Tabellen“, was die Funktion von Views andeutet.


Eine so genannte „virtuelle Tabelle“ wird aus einem gewöhnlichen SELECT-Befehl, welcher Daten aus einer oder mehreren Tabellen bzw. Views ausliest, generiert. Somit ist es beispielsweise möglich, in der neu erstellten View-Tabelle Operationen durchzuführen, ohne die ursprünglichen Daten zu verändern.


Views können auch dazu benutzt werden, einige bestimmte Spalten aus einer oder mehreren Tabellen zu lesen und diese in einer neuen Tabelle übersichtlich darzustellen. Durch die separate Rechtevergabe auf die View-Tabelle können bestimmte Daten nur erwünschten Usern zugänglich gemacht werden, was die Sicherheit erhöht.


Folgendes Szenario soll die Funktionsweise von Views erklären: Die Tabelle buchhaltung einer Firmendatenbank enthält sämtliche Infos über die Finanzen. Um beispielsweise für die örtliche Presse Teile dieser Tabelle zugänglich zu machen, aber wichtige Spalten auszublenden (umsatz, gewinn, etc), kann man einen View erstellen und diesem separate Rechte zuweisen:

CREATE VIEW presse AS
SELECT firma, name, nachname, strasse, plz 
FROM buchhaltung;

GRANT SELECT ON presse TO 'sqlPresse'@'%' IDENTIFIED BY '12345';

In diesem Fall wurde eine weitere Tabelle aus einer bereits bestehenden Tabelle generiert und einem bestimmten User separate Rechte zugewiesen.

14.2 Creating Views

Die Syntax zum Erstellen eines Views lautet folgendermaßen:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE OR REPLACE

Die OR REPLACE Anweisung ist optional und wird dann verwendet, wenn ein gleichnamiger View bereits existiert. Dann wird der bestehende View überschrieben. CREATE an sich erstellt einen neuen View.


VIEW

Der Name der neuen View-Tabelle. Wenn die Datenbank unbekannt ist, in der der neue View erstellt werden soll, kann man diese mitangeben: VIEW db02.view001


ALGORITHM

Legt den Algorithmus fest, der die Anzeige des Views definiert


column_list

Wenn man nach der View-Bezeichnung die Spalten angibt, werden die Namen der ursprünglichen Spalten überschrieben.


WITH CHECK OPTION

Diese Angabe ermöglicht es, dass Änderungen überprüft werden. Wenn bestimmte Bedingungen nicht erfüllt werden, werden die Änderungen nicht durchgeführt.


Wichtig:


- Views dürfen keinen Namen bekommen, dem bereits einer normalen Tabelle oder View zugewiesen wurde

- Spalten in einem View dürfen nicht doppelt vorkommen. Folgendes würde eine Fehlermeldung provozieren:

# Error: View mit identischen Spaltennamen!
CREATE VIEW v_names AS
SELECT maedchen.name, jungen.namen 
FROM maedchen, jungen;
# Lösung
CREATE VIEW v_names (nameM, nameJ) AS
SELECT maedchen.name, jungen.namen 
FROM maedchen, jungen;

14.3 Altering Views

Mit dem Befehl ALTER wird der aktuelle View einfach neu angelegt. In folgendem Beispiel werden die aktuellen Daten verworfen und der View mit neuen Datensätzen gefüllt:

ALTER VIEW v_names (nameM, nameJ) AS
SELECT maedchen.name, jungen.namen 
FROM maedchen, jungen
WHERE alter >= 10;

14.4 Dropping Views

Mit dem Befehl DROP kann man einen oder mehrere Views löschen. Hierbei gilt dieselbe Syntax wie bei DROP TABLE:

# Löschen von Views
DROP VIEW IF EXISTS v_kunden, v_namen, v_bestand;

14.5 Checking Views

Mit dem Befehl CHECK TABLE kann man VIEWS überprüfen. Da VIEWS aus anderen Tabellen und Views generiert werden, kann es zu fehlerhaften Verlinkungen kommen, wenn beispielsweise eine Basis-Tabelle umbenannt wird:

mysql> CREATE TABLE t1 (a INT, b INT);

mysql> CREATE VIEW v1 AS 
    -> SELECT * FROM t1;

mysql> RENAME TABLE t1 TO t2;

mysql> CHECK TABLE v1\G;
*************************** 1. row ***************************
   Table: test.v1
      Op: check
Msg_type: Error
Msg_text: Table 'test.t1' doesn't exist

Mit CHECK TABLE kann man Tabellen und Views auf diese Problematik hin überprüfen.

14.6 Obtaining View Metadata

Mit folgenden Befehlen kann man sich Informationen über die VIEWS einholen:

 

mysql> SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v1' AND TABLE_SCHEMA = 'test'\G;
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: v1
VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
   CHECK_OPTION: NONE
   IS_UPDATABLE: YES
        DEFINER: root@localhost
  SECURITY_TYPE: DEFINER

mysql> SHOW CREATE VIEW v1\G;
*************************** 1. row ***************************
       View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> DESCRIBE v1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       | 
| b     | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+

Um herauszufinden, ob eine Tabelle nun eine herkömmliche Tabelle oder ein VIEW ist kann man den Befehl SHOW FULL TABLES verwenden:

mysql> SHOW FULL TABLES FROM test;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| logging        | BASE TABLE | 
| t1             | BASE TABLE | 
| tbl01          | BASE TABLE | 
| v1             | VIEW       | 
| zeit           | BASE TABLE | 
+----------------+------------+

14.7 Privileges Required for Views

Welche Rechte werden für den Zugriff auf und Verwaltung von VIEWS benötigt?


CREATE a VIEW

Um einen VIEW zu erstellen benötigt man neben dem CREATE VIEW Recht auch noch spezielle Rechte, um auf die darunterliegenden Tabellen zugreifen zu können. Wenn man lediglich Datensätze aus den Tabellen lesen möchte, reicht das SELECT-Recht auf die Tabelle. Ansonsten muss man die Rechte um INSERT und UPDATE erweitern.


OR REPLACE

Wenn man in einem CREATE VIEW Befehl noch zusätzlich OR REPLACE verwendet (CREATE VIEW OR REPLACE), so benötigt man neben den CREATE VIEW- noch das DROP-Recht.


DROP

Um ein View löschen zu dürfen, benötigt man das DROP-Privileg


SHOW CREATE VIEW

Benötigt lediglich das SELECT-Recht.


Hinweise:

- Um über einen View die darunterliegende Tabelle zu modifizieren, benötigt man die UPDATE, DELETE und INSERT-Rechte auf diese Tabelle.

- Um Datensätze aus dem View lesen zu dürfen, benötigt man SELECT-Rechte auf den View.