Detaillierte Erläuterung zum Erstellen einer Ansicht (CREATE VIEW) und zu Nutzungsbeschränkungen in MySQL

In diesem Artikel werden die Erstellung von MySQL-Views (CREATE VIEW) und die Nutzungsbeschränkungen anhand von Beispielen beschrieben. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

MySQL 5.x und spätere Versionen unterstützen Datenbankansichten. In MySQL entsprechen fast alle Funktionen von Ansichten dem SQL: 2003-Standard. MySQL verarbeitet Abfragen von Ansichten auf zwei Arten:

• Bei der ersten Möglichkeit erstellt MySQL eine temporäre Tabelle auf Grundlage der Ansichtsdefinitionsanweisung und führt die eingehende Abfrage auf dieser temporären Tabelle aus.
• Bei der zweiten Möglichkeit kombiniert MySQL die eingehende Abfrage mit der Abfragedefinition zu einer Abfrage und führt die kombinierte Abfrage aus.

MySQL unterstützt ein Versionierungssystem für Ansichten. Jedes Mal, wenn eine Ansicht geändert oder ersetzt wird, wird eine Kopie der Ansicht in einem Arc-Ordner (Archiv) gesichert, der sich in einem bestimmten Datenbankordner befindet. Der Name der Sicherungsdatei lautet view_name.frm-00001. Wenn Sie die Ansicht erneut ändern, erstellt MySQL eine neue Sicherungsdatei mit dem Namen view_name.frm-00002. MySQL ermöglicht Ihnen, Ansichten basierend auf anderen Ansichten zu erstellen, das heißt, Sie können in der Select-Anweisung der Ansichtsdefinition auf eine andere Ansicht verweisen.

Nun, ich werde nicht näher darauf eingehen. Versuchen wir als Nächstes, eine Ansicht mit der Anweisung CREATE VIEW zu erstellen. Schauen wir uns zunächst die Syntaxstruktur an:

ERSTELLEN 
  [ALGORITHMUS = {MERGE | TEMPTABLE | UNDEFINED}]
ANSICHT [Datenbankname].[Ansichtsname] 
ALS
[SELECT-Anweisung]

Schauen wir uns dann genauer an, was die verschiedenen Wörter im obigen SQL bedeuten. Zunächst stellt die erste Klammer das Algorithmusattribut zum Erstellen einer Ansicht dar. Damit können wir den von MySQL beim Erstellen einer Ansicht verwendeten Mechanismus steuern. MySQL bietet drei Algorithmen: MERGE, TEMPTABLE und UNDEFINED. Schauen wir uns jeden von ihnen einzeln an:

• Mithilfe des MERGE-Algorithmus kombiniert MySQL zunächst die Eingabeabfrage mit der Select-Anweisung, die die Ansicht definiert, zu einer einzigen Abfrage. MySQL führt dann die kombinierte Abfrage aus, um den Ergebnisset zurückzugeben. Wenn die Select-Anweisung Aggregatfunktionen (wie etwa min, max, sum, count, avg usw.) oder distinct, group by, having, limit, union, union all, subquery enthält, ist der MERGE-Algorithmus nicht zulässig. Wenn die Select-Anweisung nicht auf eine Tabelle verweist, ist der MERGE-Algorithmus nicht zulässig. Wenn der MERGE-Algorithmus nicht zulässig ist, ändert MySQL den Algorithmus in UNDEFINED. Wir sollten beachten, dass das Kombinieren der Eingabeabfrage und der Abfrage in der Ansichtsdefinition zu einer einzigen Abfrage als Ansichtsauflösung bezeichnet wird.
• Mit dem TEMPTABLE-Algorithmus erstellt MySQL zunächst eine temporäre Tabelle basierend auf der SELECT-Anweisung, die die Ansicht definiert, und führt dann die Eingabeabfrage für die temporäre Tabelle aus. Da MySQL eine temporäre Tabelle erstellen muss, um den Ergebnissatz zu speichern und die Daten von der Basistabelle in die temporäre Tabelle zu verschieben, ist der TEMPTABLE-Algorithmus weniger effizient als der MERGE-Algorithmus. Darüber hinaus sind Ansichten, die den TEMPTABLE-Algorithmus verwenden, nicht aktualisierbar.
• UNDEFINED ist der Standardalgorithmus, wenn wir eine Ansicht erstellen, ohne einen expliziten Algorithmus anzugeben. Der UNDEFINED-Algorithmus ermöglicht MySQL die Wahl zwischen der Verwendung des MERGE- oder des TEMPTABLE-Algorithmus. MySQL bevorzugt den MERGE-Algorithmus gegenüber dem TEMPTABLE-Algorithmus, da der MERGE-Algorithmus effizienter ist.

Dann gibt es noch die Phrase nach der Ansicht, die den Namen meint. In der Datenbank teilen sich Ansichten und Tabellen denselben Namespace, daher können Ansichten und Tabellen nicht denselben Namen haben. Darüber hinaus muss der Name der Ansicht den Namenskonventionen für die Tabelle entsprechen.

Die letzte Anweisung ist die SELECT-Anweisung. Mit der SELECT-Anweisung können Sie Daten aus jeder beliebigen Tabelle oder Ansicht der Datenbank abfragen. Dabei muss die SELECT-Anweisung die folgenden Regeln einhalten:

• Eine SELECT-Anweisung kann Unterabfragen in der Where-Klausel enthalten, aber nicht in der FROM-Klausel.
• Die SELECT-Anweisung kann nicht auf irgendwelche Variablen verweisen, einschließlich lokaler Variablen, Benutzervariablen und Sitzungsvariablen.
• Eine SELECT-Anweisung kann nicht auf Parameter einer vorbereiteten Anweisung verweisen.

Zu beachten ist hierbei, dass die SELECT-Anweisung nicht auf Tabellen verweisen muss. Versuchen wir zum Abschluss, eine Ansicht basierend auf der Tabelle „orderDetails“ zu erstellen, um den Gesamtumsatz jeder Bestellung darzustellen:

Erstellen Sie eine Ansicht SalePerOrder AS
  WÄHLEN 
    Bestellnummer, SUMME(Bestellte Menge * Stückpreis) Gesamt
  AUS
    Bestelldetails
  GRUPPE nach Bestellnummer
  ORDER BY insgesamt DESC;

Wenn wir den Befehl SHOW TABLES verwenden, um alle Tabellen in der Beispieldatenbank (yiibaidb) anzuzeigen, sehen wir auch, dass die Ansicht SalesPerOrder auch in der Tabellenliste angezeigt wird:

mysql> TABELLEN ANZEIGEN;
+--------------------+
| Tabellen_in_yiibaidb |
+--------------------+
| Artikel-Tags |
| Kontakte |
| Kunden |
| Abteilungen |
|Mitarbeiter|
| Büros |
|Büros_bk|
| Büros_USA |
|Bestelldetails|
| Bestellungen |
| Zahlungen |
| Produktlinien |
| Produkte |
|VerkaufperBestellung|
+--------------------+
14 Reihen im Set

Dies liegt daran, dass Ansichten und Tabellen denselben Namespace gemeinsam nutzen. Um herauszufinden, welches Objekt eine Ansicht oder eine Tabelle ist, verwenden Sie den Befehl SHOW FULL TABLES wie folgt:

mysql> VOLLSTÄNDIGE TABELLEN ANZEIGEN;
+--------------------+------------+
| Tabellen_in_yiibaidb | Tabellentyp |
+--------------------+------------+
| Artikel-Tags | BASISTABELLE |
| Kontakte | BASISTABELLE |
| Kunden | BASISTISCH |
| Abteilungen | BASISTISCH |
| Mitarbeiter | BASISTABELLE |
| Büros | BASISTISCH |
| offices_bk | BASISTISCH |
| Büros_USA | BASISTISCH |
| Bestelldetails | BASISTISCH |
| Bestellungen | BASISTISCH |
| Zahlungen | BASISTABELLE |
| Produktlinien | BASE TABLE |
| Produkte | BASISTISCH |
| saleperorder | ANZEIGEN |
+--------------------+------------+
14 Reihen im Set

Die Spalte table_type im Ergebnissatz gibt an, welches Objekt eine Ansicht und welches Objekt eine Tabelle (Basistabelle) ist. Wie oben gezeigt ist der Wert der Spalte „table_type“, die „saleperorder“ entspricht, VIEW. Wenn Sie jedoch den Gesamtverkaufsbetrag für jeden Verkaufsauftrag abfragen möchten, müssen Sie nur eine einfache SELECT-Anweisung in der Ansicht SalePerOrder ausführen, wie unten gezeigt:

WÄHLEN 
  *
AUS
  VerkaufPerBestellung;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie die folgenden Ergebnisse:

+-------------+----------+
| Bestellnummer | Gesamt |
+-------------+----------+
| 10165 | 67392,85 |
| 10287 | 61402,00 |
| 10310 | 61234,67 |
| 10212 | 59830,55 |
|-- viele, viele Daten hier ausgelassen-- |
| 10116 | 1627,56 |
| 10158 | 1491,38 |
| 10144 | 1128,20 |
| 10408 | 615,45 |
+-------------+----------+
327 Zeilen im Set

Lassen Sie uns eine Ansicht basierend auf einer anderen Ansicht erstellen. Beispielsweise können wir eine Ansicht namens BigSalesOrder basierend auf der Ansicht SalesPerOrder erstellen, um alle Verkaufsaufträge mit einer Gesamtsumme von über 60.000 anzuzeigen, wie unten gezeigt:

Erstellen Sie Ansicht BigSalesOrder AS
  WÄHLEN 
    Bestellnummer, RUNDEN(Gesamt,2) als Gesamtsumme
  AUS
    VerkaufperBestellung
  WO
    gesamt > 60000;

Nun können wir Daten aus der BigSalesOrder-Ansicht wie folgt abfragen:

WÄHLEN 
  Bestellnummer, gesamt
AUS
  GroßeVerkaufsbestellung;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie die folgenden Ergebnisse:

+-------------+----------+
| Bestellnummer | Gesamt |
+-------------+----------+
| 10165 | 67392,85 |
| 10287 | 61402,00 |
| 10310 | 61234,67 |
+-------------+----------+
3 Reihen im Set

Versuchen wir nun, mithilfe des Inner Join eine Ansicht zu erstellen, die die Kundennummer und den vom Kunden gezahlten Gesamtbetrag enthält, wie unten dargestellt:

Erstellen Sie eine Ansicht Kundenbestellungen als
  WÄHLEN 
    c.Kundennummer,
    p.Betrag
  AUS
    Kunden
      Innerer Join
    Zahlungen p ON p.Kundennummer = c.Kundennummer
  GRUPPE NACH c.Kundennummer
  ORDER BY p.Betrag DESC;

Wir verwenden das folgende SQL, um die Daten in der Ansicht „customerOrders“ abzufragen:

+----------------+------------+
| Kundennummer | Betrag |
+----------------+------------+
| 124 | 101244,59 |
| 321 | 85559,12 |
| 239 | 80375,24 |
| **** viele, viele Daten hier ausgelassen ***|
| 219 | 3452,75 |
| 216 | 3101,4 |
| 161 | 2434,25 |
| 172 | 1960,8 |
+----------------+------------+
98 Reihen im Set

Versuchen Sie nun, mithilfe einer Unterabfrage eine Ansicht zu erstellen, die Produkte enthält, deren Preise höher sind als der Durchschnittspreis aller Produkte, und zwar wie folgt:

ERSTELLEN SIE VIEW aboveAvgProducts AS
  WÄHLEN 
    Produktcode, Produktname, Kaufpreis
  AUS
    Produkte
  WO
    kaufenPreis > 
 (WÄHLEN 
        AVG(Kaufpreis)
      AUS
        Produkte)
  ORDER BY Kaufpreis DESC;

Lassen Sie uns die Daten der obigen AvgProducts-Ansicht abfragen:

WÄHLEN 
  *
AUS
  überDurchschnittsprodukte;

Führen Sie die obige Abfrageanweisung aus und erhalten Sie die folgenden Ergebnisse:

+-------------+-----------------------------------------+----------+
| Produktcode | Produktname | Kaufpreis |
+-------------+-----------------------------------------+----------+
| S10_4962 | 1962 LanciaA Delta 16V | 103,42 |
| S18_2238 | 1998 Chrysler Plymouth Prowler | 101,51 |
| S10_1949 | 1952 Alpine Renault 1300 | 98,58 |
|**************** viele, viele Daten werden hier ausgelassen*********************************|
| S18_3320 | 1917 Maxwell Tourenwagen | 57,54 |
| S24_4258 | 1936 Chrysler Airflow | 57,46 |
| S18_3233 | 1985 Toyota Supra | 57,01 |
| S18_2870 | 1999 Indy 500 Monte Carlo SS | 56,76 |
| S32_4485 | 1974 Ducati 350 Mk3 Desmo | 56,13 |
| S12_4473 | 1957 Chevrolet Pickup | 55,7 |
| S700_3167 | F/A 18 Hornet 1/72 | 54,4 |
+-------------+-----------------------------------------+----------+
54 Reihen im Set

Damit haben wir die Erstellung und Verwendung von Ansichten fast abgedeckt. Gibt es jedoch keine Einschränkungen hinsichtlich der Verwendung von Ansichten? Die Antwort ist natürlich „Ja“. Sehen wir sie uns einzeln an.

Erstens können wir keine Indizes für Ansichten erstellen. Zweitens verwendet MySQL beim Abfragen von Daten mit einer Ansicht, die den Merge-Algorithmus verwendet, den Index der zugrunde liegenden Tabelle. Auch für Ansichten, die den Temptation-Algorithmus verwenden, wird der Index nicht verwendet, wenn wir Daten für die Ansicht abfragen.

Beachten Sie außerdem, dass Sie in Versionen vor MySQL 5.7.7 keine Unterabfragen in der FROM-Klausel einer SELECT-Anweisung verwenden können, um eine Ansicht zu definieren.

Wenn Sie die Tabelle, auf der die Ansicht basiert, löschen oder umbenennen, gibt MySQL keine Fehler aus. MySQL erklärt die Ansicht jedoch für ungültig. Wir können die Anweisung CHECK TABLE verwenden, um zu überprüfen, ob die Ansicht gültig ist.

Eine einfache Ansicht kann Daten in einer Tabelle aktualisieren, aber eine Ansicht, die basierend auf einer komplexen Auswahlanweisung mit Verknüpfungen, Unterabfragen usw. erstellt wurde, kann nicht aktualisiert werden.

MySQL unterstützt keine physischen Ansichten wie andere Datenbanksysteme wie Oracle und PostgreSQL. MySQL unterstützt keine physischen Ansichten.

Das ist alles, was ich dieses Mal zu den Ansichten zu sagen habe.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“, „Zusammenfassung der Kenntnisse zu MySQL-Datenbanksperren“ und „Zusammenfassung der allgemeinen MySQL-Funktionen“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.