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 die Mustererkennung (pattern matching), das Sortieren von Ergebnissen mit ORDER BY und das Begrenzen der Anzahl der zurückgegebenen Zeilen mit LIMIT und OFFSET.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/data_sort("Sort Query Results") subgraph Lab Skills postgresql/db_access -.-> lab-550898{{"Datenfilterung und einfache Abfragen in PostgreSQL"}} postgresql/table_init -.-> lab-550898{{"Datenfilterung und einfache Abfragen in PostgreSQL"}} postgresql/data_where -.-> lab-550898{{"Datenfilterung und einfache Abfragen in PostgreSQL"}} postgresql/data_sort -.-> lab-550898{{"Datenfilterung und einfache Abfragen in PostgreSQL"}} end

Daten mit der WHERE-Klausel filtern

In diesem Schritt lernen wir, wie man die WHERE-Klausel in PostgreSQL verwendet, 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.

Bevor wir beginnen, verbinden wir uns mit der PostgreSQL-Datenbank. Öffnen Sie ein Terminal in Ihrer LabEx VM. Sie können das Standard-Xfce-Terminal verwenden.

Verbinden Sie sich zunächst mit der PostgreSQL-Datenbank mit dem Befehl psql. Wir verbinden uns mit der postgres-Datenbank mit dem Benutzer postgres. Möglicherweise müssen Sie sudo verwenden, um den Befehl auszuführen.

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).

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. Wir können die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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

Die Ausgabe sollte sein:

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

Beenden Sie abschließend die psql-Shell:

\q

Dadurch kehren Sie zum Terminal des Benutzers labex zurück.

Verwenden von LIKE für die Mustererkennung (Pattern Matching)

In diesem Schritt lernen wir, wie man den LIKE-Operator in PostgreSQL für die Mustererkennung verwendet. 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.

Verbinden wir uns zunächst mit der PostgreSQL-Datenbank. Öffnen Sie ein Terminal in Ihrer LabEx VM.

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

sudo -u postgres psql

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

Wir werden weiterhin die im vorherigen Schritt erstellte Tabelle employees verwenden. Wenn Sie sie noch nicht erstellt haben, lesen Sie bitte den vorherigen Schritt, um die Tabelle zu erstellen und Daten einzufügen.

Sehen wir uns die Daten in der Tabelle employees an:

SELECT * FROM employees;

Sie sollten die Mitarbeiterdaten wie zuvor sehen.

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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.

Beenden Sie abschließend die psql-Shell:

\q

Dadurch kehren Sie zum Terminal des Benutzers labex zurück.

Daten mit ORDER BY sortieren

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

Verbinden wir uns zunächst mit der PostgreSQL-Datenbank. Öffnen Sie ein Terminal in Ihrer LabEx VM.

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

sudo -u postgres psql

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

Wir werden weiterhin die in den vorherigen Schritten erstellte Tabelle employees verwenden. Wenn Sie sie noch nicht erstellt haben, lesen Sie bitte die vorherigen Schritte, um die Tabelle zu erstellen und Daten einzufügen.

Sehen wir uns die Daten in der Tabelle employees an:

SELECT * FROM employees;

Sie sollten die Mitarbeiterdaten wie zuvor sehen.

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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 nach Abteilung (department) in aufsteigender Reihenfolge und dann nach Gehalt in absteigender Reihenfolge zu sortieren, können Sie die folgende Abfrage verwenden:

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)

Illustration for sorting data in PostgreSQL

Beenden Sie abschließend die psql-Shell:

\q

Dadurch kehren Sie zum Terminal des Benutzers labex zurück.

Ergebnisse mit LIMIT und OFFSET begrenzen

In diesem Schritt lernen wir, wie man die LIMIT- und OFFSET-Klauseln in PostgreSQL verwendet, 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, um Paginierung zu implementieren oder eine bestimmte Teilmenge von Daten abzurufen.

Verbinden wir uns zunächst mit der PostgreSQL-Datenbank. Öffnen Sie ein Terminal in Ihrer LabEx VM.

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

sudo -u postgres psql

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

Sehen wir uns die Daten in der Tabelle employees an:

SELECT * FROM employees;

Sie sollten die Mitarbeiterdaten wie zuvor sehen.

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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, können Sie die folgende Abfrage verwenden:

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)

Beenden Sie abschließend die psql-Shell:

\q

Dadurch kehren Sie zum Terminal des Benutzers labex zurück.

Zusammenfassung

In diesem Lab haben wir zunächst eine Verbindung zu einer PostgreSQL-Datenbank mit dem Befehl psql hergestellt und eine Beispiel-Tabelle employees mit Spalten für ID, Name, Abteilung (department) und Gehalt (salary) erstellt. Anschließend haben wir die Tabelle mit Mitarbeiterdaten gefüllt und ihren Inhalt mit einer SELECT-Abfrage überprüft.

Der anfängliche Fokus lag auf der Verwendung der WHERE-Klausel, um Daten basierend auf bestimmten Bedingungen zu filtern. Dies ermöglicht das Abrufen von nur den Zeilen, die die definierten Kriterien erfüllen, und ermöglicht so einen gezielten Datenabruf aus der Tabelle employees.