MySQL-Sichten (Views) und virtuelle Tabellen

Einführung

In diesem Lab werden Sie lernen, wie Sie mit MySQL-Sichten (views) arbeiten, die virtuelle Tabellen basierend auf dem Ergebnis einer SQL-Anweisung sind. Das Lab umfasst das Erstellen einer Sicht aus einer SELECT-Abfrage, das Aktualisieren der Sichtdefinition mit ALTER VIEW, das Abfragen von Daten über die Sicht und schließlich das Löschen der Sicht zur Aufräumung.

Sie beginnen damit, sich mit einem MySQL-Server zu verbinden, eine Datenbank namens company zu erstellen und sie mit einer employees-Tabelle zu befüllen. Dann erstellen Sie eine Sicht namens sales_employees, die die employees-Tabelle filtert, um nur Mitarbeiter aus der Abteilung 'Sales' anzuzeigen. Sie werden lernen, wie Sie die Struktur der Sicht überprüfen und sie wie eine normale Tabelle abfragen.

Skills Graph

Erstellen einer Sicht aus einer SELECT-Abfrage

In diesem Schritt werden Sie lernen, wie Sie in MySQL eine Sicht (view) aus einer SELECT-Abfrage erstellen. Eine Sicht ist eine virtuelle Tabelle, die auf dem Ergebnis einer SQL-Anweisung basiert. Sichten sind nützlich, um komplexe Abfragen zu vereinfachen, Datenkomplexität zu verbergen und ein gewisses Maß an Abstraktion für den Datenzugriff bereitzustellen.

Zunächst verbinden wir uns mit dem MySQL-Server. Öffnen Sie ein Terminal in der LabEx VM. Sie sollten sich bereits im Verzeichnis ~/project befinden.

mysql -u root -p

Geben Sie bei entsprechender Aufforderung das Root-Passwort ein. Wenn Sie kein Root-Passwort festgelegt haben, drücken Sie einfach die Eingabetaste.

Jetzt erstellen wir eine Datenbank namens company.

CREATE DATABASE company;

Als Nächstes wechseln wir zur company-Datenbank.

USE company;

Erstellen wir eine einfache Tabelle namens employees mit einigen Beispieldaten.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

Jetzt erstellen wir eine Sicht namens sales_employees, die nur die Mitarbeiter aus der Abteilung 'Sales' anzeigt.

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

Diese Anweisung erstellt eine Sicht namens sales_employees. Die Sicht wählt die Spalten id, first_name, last_name und salary aus der employees-Tabelle aus, aber nur für Zeilen, bei denen die department 'Sales' ist.

Um zu überprüfen, ob die Sicht erstellt wurde, können Sie sie beschreiben:

DESCRIBE sales_employees;

Dieser Befehl zeigt die Struktur der sales_employees-Sicht an, ähnlich wie beim Beschreiben einer Tabelle.

Sie können auch die Sicht wie eine normale Tabelle abfragen:

SELECT * FROM sales_employees;

Dies zeigt alle Mitarbeiter der Sales-Abteilung an, basierend auf den Daten in der employees-Tabelle.

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

Sie haben nun erfolgreich eine Sicht aus einer SELECT-Abfrage erstellt.

Aktualisieren der Sichtdefinition mit ALTER VIEW

In diesem Schritt werden Sie lernen, wie Sie die Definition einer bestehenden Sicht (view) in MySQL mit der ALTER VIEW-Anweisung aktualisieren. Dies ist nützlich, wenn Sie die ausgewählten Spalten, die Filterkriterien oder andere Aspekte der zugrunde liegenden Abfrage der Sicht ändern müssen.

Setzen Sie von dem vorherigen Schritt fort. Sie sollten immer noch mit dem MySQL-Server verbunden sein und die company-Datenbank verwenden. Wenn nicht, verbinden Sie sich erneut mit:

mysql -u root -p

Geben Sie bei entsprechender Aufforderung das Passwort ein und führen Sie dann aus:

USE company;

Denken Sie daran, dass wir eine Sicht namens sales_employees erstellt haben, die Mitarbeiter aus der Abteilung 'Sales' anzeigt. Angenommen, wir möchten die department-Spalte zu dieser Sicht hinzufügen. Wir können die ALTER VIEW-Anweisung verwenden, um die Sichtdefinition zu ändern.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

Diese Anweisung ändert die sales_employees-Sicht, um die department-Spalte im Ergebnisset einzuschließen. Die ALTER VIEW-Anweisung ähnelt stark der CREATE VIEW-Anweisung; Sie ersetzen einfach CREATE durch ALTER.

Um zu überprüfen, ob die Sicht aktualisiert wurde, können Sie sie erneut beschreiben:

DESCRIBE sales_employees;

Sie sollten jetzt die department-Spalte in der Ausgabe sehen.

Sie können auch die aktualisierte Sicht abfragen:

SELECT * FROM sales_employees;

Dies zeigt jetzt die department-Spalte zusammen mit den anderen Spalten für Mitarbeiter der Sales-Abteilung an.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

Ändern wir die Sicht erneut, um Mitarbeiter mit einem Gehalt von mehr als 55.000 in der Sales-Abteilung einzuschließen.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales' AND salary > 55000;

Jetzt fragen wir die aktualisierte Sicht ab:

SELECT * FROM sales_employees;

Dies zeigt jetzt die Mitarbeiter in der Sales-Abteilung mit einem Gehalt von mehr als 55.000 an.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

Sie haben nun erfolgreich die Definition einer Sicht mit der ALTER VIEW-Anweisung aktualisiert.

Abfragen von Daten mit der Sicht

In diesem Schritt werden Sie lernen, wie Sie Daten mithilfe der Sicht abfragen, die Sie in den vorherigen Schritten erstellt und modifiziert haben. Sichten können genauso wie normale Tabellen abgefragt werden, sodass Sie spezifische Daten basierend auf der Definition der Sicht abrufen können.

Setzen Sie von dem vorherigen Schritt fort. Sie sollten immer noch mit dem MySQL-Server verbunden sein und die company-Datenbank verwenden. Wenn nicht, verbinden Sie sich erneut mit:

mysql -u root -p

Geben Sie bei entsprechender Aufforderung das Passwort ein und führen Sie dann aus:

USE company;

Denken Sie daran, dass wir eine Sicht namens sales_employees haben, die derzeit Mitarbeiter aus der Abteilung 'Sales' mit einem Gehalt von mehr als 55.000 anzeigt.

Um alle Daten aus der sales_employees-Sicht abzurufen, können Sie eine einfache SELECT-Anweisung verwenden:

SELECT * FROM sales_employees;

Dies zeigt alle Spalten und Zeilen an, die die Definition der Sicht erfüllen. Basierend auf dem vorherigen Schritt sollten Sie nur den/die Mitarbeiter aus der Sales-Abteilung mit einem Gehalt von mehr als 55.000 sehen.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

Sie können auch WHERE-Klauseln verwenden, um die aus der Sicht abgerufenen Daten weiter zu filtern. Beispielsweise, um den Mitarbeiter mit id = 1 aus der sales_employees-Sicht zu finden:

SELECT * FROM sales_employees WHERE id = 1;

Dies gibt nur die Zeile zurück, in der die id 1 ist, aber nur wenn dieser Mitarbeiter auch die Definition der Sicht erfüllt (Sales-Abteilung und Gehalt von mehr als 55.000).

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

Sie können auch bestimmte Spalten aus der Sicht auswählen:

SELECT first_name, last_name FROM sales_employees;

Dies zeigt nur die Spalten first_name und last_name für Mitarbeiter in der sales_employees-Sicht an.

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
+------------+-----------+
1 row in set (0.00 sec)

Sie können auch Aggregatfunktionen mit Sichten verwenden. Beispielsweise, um das durchschnittliche Gehalt der Mitarbeiter in der sales_employees-Sicht zu finden:

SELECT AVG(salary) FROM sales_employees;

Dies berechnet und zeigt das durchschnittliche Gehalt an.

+-------------+
| AVG(salary) |
+-------------+
|  60000.0000 |
+-------------+
1 row in set (0.00 sec)

Indem Sie die Sicht abfragen, fragen Sie effektiv die zugrunde liegende employees-Tabelle ab, aber mit den in der Sicht definierten Einschränkungen. Dies vereinfacht komplexe Abfragen und bietet ein gewisses Maß an Abstraktion.

Die Sicht löschen, um aufzuräumen

In diesem Schritt werden Sie lernen, wie Sie die Sicht, die Sie in den vorherigen Schritten erstellt haben, löschen. Das Löschen einer Sicht entfernt sie aus der Datenbank. Es ist wichtig, Ressourcen aufzuräumen, wenn sie nicht mehr benötigt werden.

Setzen Sie von dem vorherigen Schritt fort. Sie sollten immer noch mit dem MySQL-Server verbunden sein und die company-Datenbank verwenden. Wenn nicht, verbinden Sie sich erneut mit:

mysql -u root -p

Geben Sie bei entsprechender Aufforderung das Passwort ein und führen Sie dann aus:

USE company;

Wir haben eine Sicht namens sales_employees, die wir erstellt und modifiziert haben. Um diese Sicht zu löschen, verwenden Sie die DROP VIEW-Anweisung:

DROP VIEW sales_employees;

Diese Anweisung entfernt die sales_employees-Sicht aus der company-Datenbank.

Um zu überprüfen, ob die Sicht gelöscht wurde, können Sie versuchen, sie zu beschreiben:

DESCRIBE sales_employees;

Dies sollte eine Fehlermeldung zurückgeben, die darauf hinweist, dass die Tabelle (oder Sicht) nicht existiert.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Alternativ können Sie versuchen, aus der Sicht auszuwählen:

SELECT * FROM sales_employees;

Dies wird ebenfalls eine Fehlermeldung zurückgeben, die darauf hinweist, dass die Tabelle (oder Sicht) nicht existiert.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Schließlich löschen wir die employees-Tabelle und die company-Datenbank, um vollständig aufzuräumen.

DROP TABLE employees;
DROP DATABASE company;

Sie haben nun erfolgreich die Sicht gelöscht und die Datenbank aufgeräumt.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie in MySQL eine Sicht (View) aus einer SELECT-Abfrage erstellen. Dies beinhaltete die Verbindung zum MySQL-Server, die Erstellung einer Datenbank und einer Tabelle namens employees mit Beispieldaten. Anschließend haben Sie eine Sicht namens sales_employees erstellt, die die employees-Tabelle filtert, um nur Mitarbeiter aus der Abteilung 'Sales' anzuzeigen.

Das Lab hat gezeigt, wie Sie eine Sicht mit der CREATE VIEW-Anweisung definieren, die einzuschließenden Spalten und die Filterkriterien angeben. Sie haben auch gelernt, wie Sie die Erstellung der Sicht mit DESCRIBE überprüfen und wie Sie die Sicht wie eine normale Tabelle mit SELECT abfragen.