Einführung
In diesem Lab lernen Sie, wie Sie mit MySQL-Views arbeiten. Eine View ist eine virtuelle Tabelle, die auf dem Ergebnis einer SQL-Anweisung basiert. Views sind nützlich, um komplexe Abfragen zu vereinfachen, Datenkomplexität zu verbergen und eine Abstraktionsebene für den Datenzugriff bereitzustellen. Sie lernen, wie Sie MySQL-Views erstellen, aktualisieren, abfragen und löschen.
Sie beginnen damit, eine Verbindung zu einem MySQL-Server herzustellen, eine Datenbank namens company zu erstellen und diese mit einer employees-Tabelle zu füllen. Anschließend erstellen Sie eine View namens sales_employees, die die employees-Tabelle filtert, um nur Mitarbeiter aus der Abteilung 'Sales' anzuzeigen. Sie lernen, wie Sie die Struktur der View überprüfen und sie wie eine normale Tabelle abfragen.
Mit MySQL verbinden und Datenbank/Tabelle erstellen
In diesem Schritt verbinden Sie sich mit dem MySQL-Server und richten die notwendige Datenbank und Tabelle für das Lab ein. Dies beinhaltet die Erstellung einer Datenbank namens company und einer employees-Tabelle darin, die mit Beispieldaten gefüllt wird.
Öffnen Sie zuerst ein Terminal in der LabEx VM. Sie sollten sich bereits im Verzeichnis ~/project befinden.
Verbinden Sie sich als Root-Benutzer mit dem MySQL-Server. Da Sie über sudo-Berechtigungen verfügen und der Root-Benutzer in MySQL für die Verwendung des auth_socket-Plugins konfiguriert ist, können Sie sich ohne Passwort mit sudo verbinden.
sudo mysql -u root
Sie befinden sich nun in der MySQL-Shell. Die Eingabeaufforderung ändert sich zu mysql>.
Erstellen Sie in der MySQL-Shell eine Datenbank namens company:
CREATE DATABASE company;
Wechseln Sie zur Datenbank company, damit nachfolgende Befehle innerhalb dieser Datenbank ausgeführt werden:
USE company;
Erstellen Sie nun eine Tabelle namens employees, um Mitarbeiterinformationen zu speichern:
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2),
department VARCHAR(50)
);
Fügen Sie einige Beispieldaten in die employees-Tabelle ein:
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');
Sie können die Daten in der employees-Tabelle überprüfen, indem Sie alle Zeilen auswählen:
SELECT * FROM employees;
Die Ausgabe sollte die eingefügten Mitarbeiterdaten anzeigen:
+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department |
+----+------------+-----------+----------+-------------+
| 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 |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)
Sie haben erfolgreich eine Verbindung zu MySQL hergestellt, eine Datenbank erstellt und eine Tabelle mit Daten gefüllt.
Eine View erstellen
In diesem Schritt erstellen Sie eine View basierend auf der employees-Tabelle. Eine View ist eine gespeicherte SQL-Abfrage, die wie eine virtuelle Tabelle fungiert. Sie speichert selbst keine Daten, sondern präsentiert Daten aus einer oder mehreren zugrunde liegenden Tabellen.
Sie sollten sich immer noch in der MySQL-Shell befinden und mit der Datenbank company verbunden sein. Falls nicht, verbinden Sie sich erneut mit sudo mysql -u root und dann mit USE company;.
Wir erstellen eine View namens sales_employees, die nur die Mitarbeiter anzeigt, die in der Abteilung 'Sales' arbeiten. Diese View wählt bestimmte Spalten aus der employees-Tabelle aus.
CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';
Diese Anweisung definiert eine View namens sales_employees. Die Definition der View ist die SELECT-Abfrage, die auf das Schlüsselwort AS folgt. Diese Abfrage wählt die Spalten id, first_name, last_name und salary aus der employees-Tabelle aus, jedoch nur für Zeilen, bei denen die Spalte department gleich 'Sales' ist.
Um zu bestätigen, dass die View erstellt wurde, können Sie die Tabellen und Views in der aktuellen Datenbank auflisten:
SHOW TABLES;
Sie sollten sowohl employees als auch sales_employees aufgelistet sehen.
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
| sales_employees |
+-------------------+
2 rows in set (0.00 sec)
Sie können die Struktur der View auch beschreiben, genau wie Sie es bei einer Tabelle tun würden:
DESCRIBE sales_employees;
Dies zeigt die in der View enthaltenen Spalten an:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Sie haben erfolgreich eine View namens sales_employees erstellt.
Daten mit der View abfragen
In diesem Schritt lernen Sie, wie Sie Daten über die von Ihnen erstellte sales_employees-View abfragen. Views können wie reguläre Tabellen abgefragt werden und bieten eine vereinfachte Möglichkeit, auf bestimmte Datenausschnitte zuzugreifen.
Sie sollten sich immer noch in der MySQL-Shell befinden und mit der Datenbank company verbunden sein. Falls nicht, verbinden Sie sich erneut mit sudo mysql -u root und dann mit USE company;.
Um alle Daten aus der sales_employees-View abzurufen, verwenden Sie eine Standard-SELECT-Anweisung:
SELECT * FROM sales_employees;
Diese Abfrage führt die zugrunde liegende SELECT-Anweisung aus, die in der View definiert ist, und gibt das Ergebnis zurück. Sie sollten nur die Mitarbeiter aus der Abteilung 'Sales' sehen:
+------+------------+-----------+----------+
| id | first_name | last_name | salary |
+------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
| 3 | Robert | Jones | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)
Sie können auch zusätzliche Filter oder Sortierungen auf die aus der View abgerufenen Daten anwenden. Zum Beispiel, um den Vertriebsmitarbeiter mit einem Gehalt über 55000 zu finden:
SELECT * FROM sales_employees WHERE salary > 55000;
Dies gibt nur die Zeilen aus der View zurück, bei denen das Gehalt größer als 55000 ist:
+------+------------+-----------+----------+
| id | first_name | last_name | salary |
+------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)
Sie können auch bestimmte Spalten aus der View auswählen:
SELECT first_name, last_name FROM sales_employees;
Dies zeigt nur die Vor- und Nachnamen der Vertriebsmitarbeiter an:
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Robert | Jones |
+------------+-----------+
2 rows in set (0.00 sec)
Das Abfragen einer View ist im Wesentlichen dasselbe wie das Abfragen einer Tabelle, aber die View bietet einen vordefinierten Filter und eine Auswahl von Spalten aus der zugrunde liegenden Datenquelle.
View-Definition mit ALTER VIEW aktualisieren
In diesem Schritt lernen Sie, wie Sie die Definition einer bestehenden View mit der Anweisung ALTER VIEW ändern. Dies ist nützlich, wenn Sie die in der View enthaltenen Spalten oder die Filterkriterien ändern müssen.
Sie sollten sich immer noch in der MySQL-Shell befinden und mit der Datenbank company verbunden sein. Falls nicht, verbinden Sie sich erneut mit sudo mysql -u root und dann mit USE company;.
Derzeit enthält die View sales_employees die Spalten id, first_name, last_name und salary. Ändern wir die View so, dass sie auch die Spalte department enthält.
ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';
Diese Anweisung verwendet ALTER VIEW, gefolgt vom View-Namen und der neuen SELECT-Abfrage, die die View definiert. Die neue Abfrage enthält nun die Spalte department.
Um zu überprüfen, ob die View aktualisiert wurde, beschreiben Sie sie erneut:
DESCRIBE sales_employees;
Sie sollten nun die Spalte department in der Ausgabe sehen:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| department | varchar(50) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Fragen Sie die aktualisierte View ab, um die neue Spalte anzuzeigen:
SELECT * FROM sales_employees;
Die Ausgabe enthält nun die Spalte department:
+------+------------+-----------+----------+------------+
| 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)
Sie haben die Definition der View sales_employees erfolgreich aktualisiert.
View löschen und bereinigen
In diesem letzten Schritt lernen Sie, wie Sie die View löschen und die während dieses Labs erstellte Datenbank und Tabelle aufräumen.
Sie sollten sich immer noch in der MySQL-Shell befinden und mit der Datenbank company verbunden sein. Falls nicht, verbinden Sie sich erneut mit sudo mysql -u root und dann mit USE company;.
Um die View sales_employees zu entfernen, verwenden Sie die Anweisung DROP VIEW:
DROP VIEW sales_employees;
Dieser Befehl entfernt die View sales_employees dauerhaft aus der Datenbank company.
Um zu bestätigen, dass die View gelöscht wurde, können Sie versuchen, sie zu beschreiben:
DESCRIBE sales_employees;
Dies sollte zu einer Fehlermeldung führen, die besagt, dass die View nicht existiert:
ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist
Nun räumen wir die Tabelle employees und die Datenbank company auf.
Löschen Sie die Tabelle employees:
DROP TABLE employees;
Löschen Sie die Datenbank company:
DROP DATABASE company;
Sie können die MySQL-Shell verlassen, indem Sie Folgendes eingeben:
exit
Sie haben die View, die Tabelle und die Datenbank erfolgreich gelöscht und die in diesem Lab verwendeten Ressourcen aufgeräumt.
Zusammenfassung
In diesem Lab haben Sie gelernt, wie man mit MySQL Views arbeitet. Sie haben damit begonnen, sich mit dem MySQL-Server zu verbinden und eine Datenbank und Tabelle einzurichten. Anschließend haben Sie eine View namens sales_employees erstellt, um eine gefilterte Ansicht der Tabelle employees bereitzustellen.
Sie haben das Abfragen der View mit SELECT-Anweisungen geübt und gezeigt, wie Views den Datenzugriff vereinfachen. Sie haben auch gelernt, wie Sie die Definition einer bestehenden View mit der Anweisung ALTER VIEW ändern, um zusätzliche Spalten einzufügen. Schließlich haben Sie gelernt, wie Sie eine View mit der Anweisung DROP VIEW entfernen und die Datenbank und Tabelle aufgeräumt.
Sie verstehen nun die grundlegenden Konzepte und Operationen für die Arbeit mit MySQL Views, die wertvolle Werkzeuge für die Verwaltung und den Zugriff auf Daten in einer Datenbank sind.
