Datenfilterung und einfache Abfragen in PostgreSQL

Einführung

In diesem Lab lernen Sie, wie Sie Daten filtern und einfache Abfragen in PostgreSQL durchführen. Sie beginnen mit der Verbindung zu einer PostgreSQL-Datenbank und der Erstellung einer Beispiel-Tabelle employees mit Daten.

Das Lab führt Sie dann durch die Verwendung der WHERE-Klausel, um Daten basierend auf bestimmten Bedingungen zu filtern, die Verwendung von LIKE für Mustervergleiche (pattern matching), das Sortieren von Ergebnissen mit ORDER BY und das Begrenzen der Anzahl der zurückgegebenen Zeilen mit LIMIT und OFFSET.

Skills Graph

Daten mit der WHERE-Klausel filtern

In diesem Schritt lernen Sie, wie Sie die WHERE-Klausel in PostgreSQL verwenden, um Daten basierend auf bestimmten Bedingungen zu filtern. Die WHERE-Klausel ist ein leistungsstarkes Werkzeug, mit dem Sie nur die Zeilen abrufen können, die Ihren Kriterien entsprechen.

Öffnen Sie zunächst ein Terminal in Ihrer LabEx VM. Sie können das Standard-Xfce-Terminal verwenden.

Verbinden Sie sich mit der PostgreSQL-Datenbank mit dem Befehl psql:

sudo -u postgres psql

Sie sollten nun die PostgreSQL-Eingabeaufforderung (postgres=#) sehen.

Erstellen wir nun eine Beispieltabelle namens employees und fügen einige Daten ein. Diese Tabelle speichert Mitarbeiterinformationen, einschließlich ihrer ID, ihres Namens, ihrer Abteilung (department) und ihres Gehalts (salary). Führen Sie die folgenden SQL-Anweisungen aus:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INTEGER
);

INSERT INTO employees (name, department, salary) VALUES
('Alice Smith', 'Sales', 60000),
('Bob Johnson', 'Marketing', 75000),
('Charlie Brown', 'Sales', 55000),
('David Lee', 'Engineering', 90000),
('Eve Wilson', 'Marketing', 80000),
('Frank Miller', 'Engineering', 95000);

Um zu überprüfen, ob die Tabelle korrekt erstellt und gefüllt wurde, führen Sie die folgende SQL-Abfrage aus:

SELECT * FROM employees;

Sie sollten eine ähnliche Ausgabe wie diese sehen:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  6 | Frank Miller    | Engineering |  95000
(6 rows)

Verwenden wir nun die WHERE-Klausel, um die Daten zu filtern. Angenommen, wir möchten nur die Mitarbeiter abrufen, die in der Abteilung 'Sales' arbeiten. Führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE department = 'Sales';

Diese Abfrage gibt nur die Zeilen zurück, in denen die Spalte department gleich 'Sales' ist. Die Ausgabe sollte sein:

 id |     name      | department | salary
----+-----------------+------------+--------
  1 | Alice Smith     | Sales      |  60000
  3 | Charlie Brown   | Sales      |  55000
(2 rows)

Sie können auch andere Vergleichsoperatoren in der WHERE-Klausel verwenden, wie z. B. >, <, >=, <= und <>. Um beispielsweise Mitarbeiter mit einem Gehalt von mehr als 70000 abzurufen, führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE salary > 70000;

Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  2 | Bob Johnson     | Marketing   |  75000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  6 | Frank Miller    | Engineering |  95000
(4 rows)

Sie können auch mehrere Bedingungen mit logischen Operatoren wie AND und OR kombinieren. Um beispielsweise Mitarbeiter abzurufen, die in der Abteilung 'Sales' arbeiten und ein Gehalt von mehr als 58000 haben, führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 58000;

Die Ausgabe sollte sein:

 id |    name     | department | salary
----+---------------+------------+--------
  1 | Alice Smith | Sales      |  60000
(1 row)

Verwenden von LIKE für Mustervergleiche (Pattern Matching)

In diesem Schritt lernen Sie, wie Sie den LIKE-Operator in PostgreSQL für Mustervergleiche (pattern matching) verwenden. Mit dem LIKE-Operator können Sie nach Daten suchen, die einem bestimmten Muster entsprechen. Dies ist besonders nützlich, wenn Sie den genauen Wert, nach dem Sie suchen, nicht kennen.

Wir werden weiterhin die im vorherigen Schritt erstellte Tabelle employees verwenden.

Der LIKE-Operator wird in einer WHERE-Klausel verwendet, um Zeilen zu finden, die einem bestimmten Muster entsprechen. Das Muster kann Platzhalterzeichen (wildcard characters) enthalten:

• %: Stellt null oder mehr Zeichen dar.
• _: Stellt ein einzelnes Zeichen dar.

Um beispielsweise alle Mitarbeiter zu finden, deren Namen mit 'A' beginnen, führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE name LIKE 'A%';

Diese Abfrage gibt alle Zeilen zurück, in denen die Spalte name mit 'A' beginnt. Die Ausgabe sollte sein:

 id |    name     | department | salary
----+---------------+------------+--------
  1 | Alice Smith | Sales      |  60000
(1 row)

Um alle Mitarbeiter zu finden, deren Namen den Buchstaben 'o' enthalten, führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE name LIKE '%o%';

Diese Abfrage gibt alle Zeilen zurück, in denen die Spalte name den Buchstaben 'o' enthält. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
  5 | Eve Wilson      | Marketing   |  80000
(3 rows)

Um alle Mitarbeiter zu finden, deren Namen mit 'n' enden, führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE name LIKE '%n';

Diese Abfrage gibt alle Zeilen zurück, in denen die Spalte name mit 'n' endet. Die Ausgabe sollte sein:

 id |     name      | department | salary
----+---------------+------------+--------
  2 | Bob Johnson   | Marketing  |  75000
  3 | Charlie Brown | Sales      |  55000
  5 | Eve Wilson    | Marketing  |  80000
(3 rows)

Sie können auch den Platzhalter _ verwenden, um ein einzelnes Zeichen zu finden. Um beispielsweise alle Mitarbeiter zu finden, deren Namen 'e ' als zweites und drittes Zeichen haben, führen Sie die folgende Abfrage aus:

SELECT * FROM employees WHERE name LIKE '_e%';

Diese Abfrage gibt alle Zeilen zurück, in denen die Spalte name 'e' als zweites Zeichen hat. Die Ausgabe sollte sein:

 id | name | department | salary
----+------+------------+--------
(0 rows)

Der LIKE-Operator unterscheidet zwischen Groß- und Kleinschreibung (case-sensitive). Wenn Sie eine Suche ohne Berücksichtigung der Groß- und Kleinschreibung durchführen möchten, können Sie den ILIKE-Operator verwenden. Zum Beispiel:

SELECT * FROM employees WHERE name ILIKE 'a%';

Diese Abfrage gibt alle Zeilen zurück, in denen die Spalte name mit 'a' oder 'A' beginnt.

Daten mit ORDER BY sortieren

In diesem Schritt lernen Sie, wie Sie die ORDER BY-Klausel in PostgreSQL verwenden, um Daten zu sortieren. Mit der ORDER BY-Klausel können Sie das Ergebnis einer Abfrage basierend auf einer oder mehreren Spalten in aufsteigender oder absteigender Reihenfolge sortieren.

Wir werden weiterhin die Tabelle employees verwenden.

Die ORDER BY-Klausel wird verwendet, um das Ergebnis einer Abfrage zu sortieren. Standardmäßig sortiert die ORDER BY-Klausel die Daten in aufsteigender Reihenfolge.

Um beispielsweise die Mitarbeiter nach Gehalt (salary) in aufsteigender Reihenfolge zu sortieren, führen Sie die folgende Abfrage aus:

SELECT * FROM employees ORDER BY salary;

Diese Abfrage gibt alle Zeilen aus der Tabelle employees zurück, sortiert nach der Spalte salary in aufsteigender Reihenfolge. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  3 | Charlie Brown   | Sales       |  55000
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  5 | Eve Wilson      | Marketing   |  80000
  4 | David Lee       | Engineering |  90000
  6 | Frank Miller    | Engineering |  95000
(6 rows)

Um die Daten in absteigender Reihenfolge zu sortieren, können Sie das Schlüsselwort DESC nach dem Spaltennamen verwenden. Um beispielsweise die Mitarbeiter nach Gehalt in absteigender Reihenfolge zu sortieren, führen Sie die folgende Abfrage aus:

SELECT * FROM employees ORDER BY salary DESC;

Diese Abfrage gibt alle Zeilen aus der Tabelle employees zurück, sortiert nach der Spalte salary in absteigender Reihenfolge. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  6 | Frank Miller    | Engineering |  95000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  2 | Bob Johnson     | Marketing   |  75000
  1 | Alice Smith     | Sales       |  60000
  3 | Charlie Brown   | Sales       |  55000
(6 rows)

Sie können die Daten auch nach mehreren Spalten sortieren. Um beispielsweise die Mitarbeiter zuerst nach Abteilung (department) in aufsteigender Reihenfolge und dann nach Gehalt in absteigender Reihenfolge zu sortieren, führen Sie die folgende Abfrage aus:

SELECT * FROM employees ORDER BY department ASC, salary DESC;

Diese Abfrage sortiert die Daten zuerst nach der Spalte department in aufsteigender Reihenfolge. Innerhalb jeder Abteilung werden die Daten nach der Spalte salary in absteigender Reihenfolge sortiert. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  4 | David Lee       | Engineering |  90000
  6 | Frank Miller    | Engineering |  95000
  2 | Bob Johnson     | Marketing   |  75000
  5 | Eve Wilson      | Marketing   |  80000
  1 | Alice Smith     | Sales       |  60000
  3 | Charlie Brown   | Sales       |  55000
(6 rows)

Ergebnisse mit LIMIT und OFFSET begrenzen

In diesem Schritt lernen Sie, wie Sie die Klauseln LIMIT und OFFSET in PostgreSQL verwenden, um die Anzahl der von einer Abfrage zurückgegebenen Zeilen zu begrenzen bzw. eine bestimmte Anzahl von Zeilen zu überspringen. Diese Klauseln sind nützlich für die Implementierung von Paginierung (pagination) oder das Abrufen einer bestimmten Teilmenge von Daten.

Wir werden weiterhin die Tabelle employees verwenden.

Die LIMIT-Klausel wird verwendet, um die Anzahl der von einer Abfrage zurückgegebenen Zeilen zu begrenzen. Um beispielsweise nur die ersten 3 Mitarbeiter abzurufen, führen Sie die folgende Abfrage aus:

SELECT * FROM employees LIMIT 3;

Diese Abfrage gibt die ersten 3 Zeilen aus der Tabelle employees zurück. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
(3 rows)

Die OFFSET-Klausel wird verwendet, um eine bestimmte Anzahl von Zeilen zu überspringen, bevor mit der Rückgabe von Zeilen begonnen wird. Sie wird oft in Verbindung mit der LIMIT-Klausel verwendet, um Paginierung zu implementieren. Um beispielsweise die nächsten 3 Mitarbeiter abzurufen, nachdem die ersten 2 übersprungen wurden, führen Sie die folgende Abfrage aus:

SELECT * FROM employees LIMIT 3 OFFSET 2;

Diese Abfrage überspringt die ersten 2 Zeilen und gibt dann die nächsten 3 Zeilen aus der Tabelle employees zurück. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  3 | Charlie Brown   | Sales       |  55000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
(3 rows)

Sie können LIMIT und OFFSET auch mit der ORDER BY-Klausel kombinieren. Um beispielsweise die 2 bestbezahlten Mitarbeiter abzurufen, führen Sie die folgende Abfrage aus:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2;

Diese Abfrage sortiert die Mitarbeiter zuerst nach Gehalt in absteigender Reihenfolge und gibt dann die ersten 2 Zeilen zurück. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  6 | Frank Miller    | Engineering |  95000
  4 | David Lee       | Engineering |  90000
(2 rows)

Um die dritt- und viertbestbezahlten Mitarbeiter abzurufen, führen Sie die folgende Abfrage aus:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2;

Diese Abfrage sortiert die Mitarbeiter zuerst nach Gehalt in absteigender Reihenfolge, überspringt die ersten 2 Zeilen und gibt dann die nächsten 2 Zeilen zurück. Die Ausgabe sollte sein:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  5 | Eve Wilson      | Marketing   |  80000
  2 | Bob Johnson     | Marketing   |  75000
(2 rows)

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Datenfilterung und einfache Abfragen in PostgreSQL durchführt. Sie begannen mit der Verbindung zu einer PostgreSQL-Datenbank mithilfe des Befehls psql und erstellten eine Beispiel-Tabelle employees.

Anschließend lernten Sie, wie Sie die WHERE-Klausel verwenden, um Daten basierend auf bestimmten Bedingungen zu filtern, den LIKE-Operator für Mustervergleich (pattern matching), die ORDER BY-Klausel zum Sortieren von Daten und die LIMIT- und OFFSET-Klauseln, um die Größe des Ergebnissatzes (result set) zu steuern. Dies sind grundlegende Fähigkeiten für die Arbeit mit relationalen Datenbanken wie PostgreSQL.