home..

SQL Views

Markus Brunsch / April 2022

Database Views

Views sind SQL Queries die in auf dem DB Server gespeichert werden und somit mit einem einfachen Aufruf der View wiederverwendet werden können.

Beispiel View:

CREATE VIEW customerPayments
AS 
SELECT 
    customerName, 
    checkNumber, 
    paymentDate, 
    amount
FROM
    customers
INNER JOIN
    payments USING (customerNumber);

Vor und Nachteile von Views

Pro:

  1. Komplexe SQL Queries können in Views gespeichert werden somit kann der erneute Aufruf vereinfacht werden.
  2. Business Logik zentralisieren, greifen mehrere Programme auf die gleiche Datenbank zu und benötigen die selben Daten. So kann eine View dabei helfen die Queries Programmübergreifend konsistent zu halten.
  3. Zugriffsbeschränkung man kann einem Benutzer gestatten nur die View ausführen zu können, somit ist dieser nicht in der Lage andere Informationen aus der Datenbank zu extrahieren.
  4. Bei Schemaänderungen kann eine View mit dem alten Namen der Tabelle erstellt werden, somit funktionieren Queries die auf das alte Schema referenzieren noch.

Con:

  1. Auf Views können keine Indexes erstellt werden.
  2. Views mit Sub-Queries, Joins oder Aggregatsfunktionen können nicht angepasst werden.

Views Verwalten

Views Erstellen

Syntax:

CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]
AS
  select-statement;

Beispiel:

CREATE VIEW salePerOrder AS

Mit dem WITH CHECK OPTION Parameter wird der Scope der View auf die Daten die die View (dessen WHERE Klausel) ausgibt beschränkt. Somit können keine Datensätze außerhab diese Scopes ausgegeben, gelöscht oder bearbeitet werden.

Da Views auch verschachtelt werden können, gibt es die Möglichkeit die WITH CHECK OPTION weiter zu Bearbeiten mit den Parametern LOCAL und CASCADED ist es Möglich den Scope der Checks einzuschränken.

Mit der WITH LOCAL CHECK OPTION Option werden nur Bedingungen in der Aufgerufenen View beachtet. Mit der WITH CASCADED CHECK OPTION Option dagegen werden alle Bedingungen aller verschachtelten Views beachtet.

Algorithm

Der Algorithmus Parameter bestimmt wie die View beim Aufruf durch MySQL verarbeitet werden soll. Zur Auswahl stehen MERGE, TEMPTABLE und UNDEFINDE. Syntax:

CREATE [OR REPLACE][ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW 
   view_name[(column_list)]
AS 
   select-statement;

Bei einem Merge Aufruf wird der Query String vor dem Ausführen auf der Datenbank vereinfacht.

Aus der View

CREATE ALGORITHM=MERGE VIEW contactPersons(
    customerName, 
    firstName, 
    lastName, 
    phone
) AS
SELECT 
    customerName, 
    contactFirstName, 
    contactLastName, 
    phone
FROM customers;

mit dem SQL Query Aufruf

SELECT * FROM contactPersons
WHERE customerName LIKE '%Co%';

werden die Parameter und der Tabellenname angepasst und eine WHERE Klausel angefügt (falls vorhanden). Das follständige Query sieht dann so aus:

SELECT 
    customerName, 
    contactFirstName, 
    contactLastName, 
    phone
FROM
    customers
WHERE
    customerName LIKE '%Co%';

Der Temptable Algorithmus erstellt eine temporäre Tabelle auf der der SELECT Query dann aufgeführt wird. Dies ist weniger effizient. Auf solch eine View kann auch kein UPDATE Query angewendet werden. Einziger Vorteil ist, dass sich durch die temporäre Tabelle die Lock Dauer auf die Datenbank verringert.

Undefined Beschreibt die Standardauswahl von MySQL, sollte kein Alorithmus angegeben werden. Dabei verwendet MySQL den bevorzugten Merge Algorithmus.

Views Entfernen

Syntax:

DROP VIEW [IF EXISTS] view_name;

Beispiel:

DROP VIEW IF EXISTS customerPayments;

Auflisten aller Views

SHOW FULL TABLES 
WHERE table_type = 'VIEW';

Da SHOW FULL Tables alle Tabellen der Datenbank zurückgibt wird die WHERE Klausel benötigt um die Views zu Filtern.

Views Umbenennen

Syntax:

RENAME TABLE original_view_name 
TO new_view_name;

Beispiel:

RENAME TABLE productLineSales 
TO productLineQtySales;

View Update Queries

Um ein UPDATE Query auf einer View auszuführen, darf die View keine Sub-Queries, Joins oder Aggregatsfunktionen enthalten. Sollte die der Fall sein kann ein UPDATE Query auf der View ausgeführt werden.

Beispiel:

UPDATE officeInfo 
SET 
    phone = '+33 14 723 5555'
WHERE
    officeCode = 4;
© 2023 Markus Brunsch