Wie man Funktionen in SQL verwendet
Einführung
Beim Arbeiten mit relationalen Datenbanken und der Structured Query Language (SQL) können Sie Funktionen in SQL verwenden, um Daten im relationalen Datenbankverwaltungssystem zu speichern, zu verwalten und abzurufen. SQL kann Daten in der Form abrufen, wie sie in der Datenbank gespeichert sind.
SQL kann auch Berechnungen durchführen und Daten mithilfe von Funktionen manipulieren. Zum Beispiel können Sie Funktionen verwenden, um Produktpreise auf den nächsten Dollar zu runden, die durchschnittliche Anzahl von Produktkäufen zu berechnen oder die Anzahl der Tage bis zum Ablauf der Garantie auf einen bestimmten Kauf zu ermitteln.
In diesem Tutorial verwenden Sie verschiedene SQL-Funktionen, um mathematische Berechnungen durchzuführen, Zeichenketten und Datumsangaben zu manipulieren und Zusammenfassungen mit Aggregatfunktionen zu berechnen.
Voraussetzungen für Funktionen in SQL
Um dieser Anleitung folgen zu können, benötigen Sie einen Computer mit einem SQL-basierten relationalen Datenbankverwaltungssystem (RDBMS). Die Anweisungen und Beispiele in dieser Anleitung wurden in der folgenden Umgebung validiert:
- Ein Server mit Ubuntu 20.04, mit einem Nicht-Root-Benutzer mit administrativen Rechten und einer Firewall, die mit UFW konfiguriert ist, wie in unserem initialen Server-Setup-Leitfaden für Ubuntu 20.04 beschrieben.
- MySQL installiert und auf dem Server gesichert, wie in „How To Install MySQL on Ubuntu 20.04“ beschrieben. Diese Anleitung wurde mit einem Nicht-Root-MySQL-Benutzer überprüft, der in Schritt 3 erstellt wurde.
- Grundlegende Kenntnisse im Ausführen von SELECT-Abfragen zum Abrufen von Daten aus der Datenbank, wie in unserem Leitfaden „How To SELECT Rows FROM Tables in SQL“ beschrieben.
Hinweis: Viele RDBMS verwenden ihre eigene SQL-Implementierung. Auch wenn die in diesem Tutorial beschriebenen Befehle in den meisten RDBMS funktionieren, gibt die standardisierte SQL-Syntax nur eine begrenzte Anzahl von Funktionen vor. Zudem variiert die Unterstützung für die Standard-Syntax je nach Datenbank-Engine. Die genaue Syntax oder Ausgabe kann abweichen, wenn Sie die Befehle auf einem anderen System als MySQL testen.
Sie benötigen außerdem eine Datenbank mit einigen Tabellen, die mit Beispieldaten geladen sind, damit Sie das Verwenden von Funktionen üben können. Wir empfehlen, den Abschnitt „Verbinden mit MySQL und Einrichten einer Beispieldatenbank“ durchzugehen, um mehr über die Verbindung zu einem MySQL-Server und das Erstellen der in dieser Anleitung verwendeten Testdatenbank zu erfahren.
Verbinden mit MySQL und Einrichten einer Beispieldatenbank
In diesem Abschnitt verbinden Sie sich mit einem MySQL-Server und erstellen eine Beispieldatenbank, sodass Sie den Beispielen in dieser Anleitung folgen können.
Falls Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, verbinden Sie sich per SSH von Ihrem lokalen Rechner aus:
ssh sammy@your_server_ip
Öffnen Sie anschließend die MySQL-Befehlszeile, indem Sie „sammy“ durch den Namen Ihres MySQL-Benutzerkontos ersetzen:
mysql -u sammy -p
Erstellen Sie eine Datenbank namens bookstore:
CREATE DATABASE bookstore;
Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie eine Ausgabe wie folgt:
Output
Query OK, 1 row affected (0.01 sec)
Um die bookstore-Datenbank auszuwählen, führen Sie die folgende USE-Anweisung aus:
USE bookstore;
Sie erhalten folgende Ausgabe:
Output
Database changed
Nachdem Sie die Datenbank ausgewählt haben, können Sie Beispieltabellen darin erstellen. Für dieses Tutorial verwenden wir ein imaginäres Buchgeschäft, das Bücher von verschiedenen Autoren verkauft.
Die Tabelle inventory enthält Daten über die Bücher im Buchgeschäft. Sie wird die folgenden Spalten enthalten:
- book_id: Diese Spalte enthält die Kennung für jedes Buch, dargestellt durch den Datentyp int. Diese Spalte wird der Primärschlüssel der Tabelle, wobei jeder Wert zu einem eindeutigen Bezeichner für die jeweilige Zeile wird.
- author: Diese Spalte enthält den Namen des Buchautors, ausgedrückt mit dem Datentyp varchar mit maximal 50 Zeichen.
- title: Diese Spalte enthält den Titel des gekauften Buches, ausgedrückt mit dem Datentyp varchar mit maximal 200 Zeichen.
- introduction_date: Mithilfe des Datentyps date enthält diese Spalte das Datum, an dem jedes Buch vom Buchgeschäft eingeführt wurde.
- stock: Diese Spalte enthält die Anzahl der Bücher, die das Buchgeschäft in seinem Bestand hat, unter Verwendung des int-Datentyps.
- price: Diese Spalte speichert den Verkaufspreis des Buches mithilfe des Datentyps decimal mit maximal 5 Stellen vor dem Dezimalpunkt und 2 Stellen danach.
Erstellen Sie die Beispiel-Tabelle mit dem folgenden Befehl:
CREATE TABLE inventory (
book_id int,
author varchar(50),
title varchar(200),
introduction_date date,
stock int,
price decimal(5, 2),
PRIMARY KEY (book_id)
);
Wenn die folgende Ausgabe angezeigt wird, wurde die Tabelle erstellt:
Output
Query OK, 0 rows affected (0.00 sec)
Danach laden Sie die Tabelle purchases mit einigen Beispieldaten, indem Sie die folgende INSERT INTO-Anweisung ausführen:
INSERT INTO inventory
VALUES
(1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),
(2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),
(3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),
(4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),
(5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);
Die INSERT INTO-Anweisung fügt der Tabelle inventory fünf Bücher mit den angegebenen Werten hinzu. Die folgende Ausgabe zeigt an, dass alle fünf Zeilen hinzugefügt wurden:
Output
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Damit sind Sie bereit, den Rest des Leitfadens zu befolgen und Funktionen in SQL zu verwenden.
Verstehen von Funktionen in SQL
SQL-Funktionen sind benannte Ausdrücke, die Werte annehmen, Berechnungen durchführen und einen neuen Wert zurückgeben. Sie ähneln mathematischen Funktionen; zum Beispiel berechnet log(x) den Logarithmus von x.
Typischerweise nutzen Sie die SELECT-Abfrage, um Daten aus einer relationalen Datenbank abzurufen. Zum Beispiel können Sie die folgende Abfrage ausführen, um Buchtitel und Preise in absteigender Reihenfolge anzuzeigen:
SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;
Die Ausgabe zeigt die Buchtitel, Preise und Einführungsdaten sortiert nach Preis. Spaltennamen wie title und price werden direkt aus der Datenbank abgerufen, ohne sie zu verändern.
SQL-Funktionen ermöglichen jedoch auch eine Verarbeitung der abgerufenen Werte. Sie können etwa Preise runden, Buchtitel in Großbuchstaben umwandeln oder das Einführungsjahr extrahieren. SQL-Funktionen lassen sich in Kategorien einteilen:
- Mathematische Funktionen: Arbeiten mit Zahlen, z. B. Runden, Logarithmen.
- String-Manipulationsfunktionen: Transformieren Texte, z. B. in Großbuchstaben.
- Datums- und Zeitfunktionen: Arbeiten mit Datumswerten, z. B. Hinzufügen von Tagen.
- Aggregatfunktionen: Berechnen Werte über mehrere Zeilen, z. B. Durchschnittspreise.
Die meisten relationalen Datenbanken, einschließlich MySQL, erweitern die SQL-Standardsyntax mit eigenen Funktionen. Einige Funktionen sind standardisiert, andere sind spezifisch für eine Datenbank-Engine. In der MySQL-Dokumentation finden Sie eine Übersicht zu eingebauten Funktionen und Operatoren.
Ein Beispiel für die Anwendung einer fiktiven Funktion EXAMPLE auf die Preiswerte:
SELECT EXAMPLE(price) AS new_price FROM inventory;
Im folgenden Abschnitt werden mathematische Funktionen für häufige Berechnungen verwendet.
Verwendung von mathematischen Funktionen in SQL
Mathematische Funktionen arbeiten mit numerischen Werten, wie z. B. dem Buchpreis oder der Anzahl der Bücher im Lager in der Beispieldatenbank. Sie können verwendet werden, um Berechnungen innerhalb der Datenbank durchzuführen und die Ergebnisse an Ihre Anforderungen anzupassen.
Das Runden ist eine der häufigsten Anwendungen mathematischer Funktionen in SQL. Angenommen, Sie müssen Preise für alle Bücher abrufen, sind aber nur an den auf den nächsten vollen Dollar gerundeten Werten interessiert. Dazu können Sie die ROUND-Funktion verwenden, die die Rundungsoperation durchführt.
Führen Sie die folgende Anweisung aus:
SELECT title, price, ROUND(price) AS rounded_price FROM inventory;
Die folgende Ausgabe wird auf dem Bildschirm angezeigt:
Output
+------------------------------------+-------+---------------+
| title | price | rounded_price |
+------------------------------------+-------+---------------+
| The Picture of Dorian Gray | 20.83 | 21 |
| Pride and Prejudice | 42.13 | 42 |
| The Time Machine | 21.99 | 22 |
| Frankenstein | 17.43 | 17 |
| The Adventures of Huckleberry Finn | 23.15 | 23 |
+------------------------------------+-------+---------------+
5 rows in set (0.000 sec)
Die Abfrage wählt die Werte aus den Spalten title und price unverändert aus, zusammen mit einer temporären Spalte rounded_price mit den Ergebnissen der Funktion ROUND(price). Diese Funktion nimmt ein Argument, den Spaltennamen (in diesem Fall price), und gibt die Werte aus dieser Spalte in der Tabelle zurück, gerundet auf den nächsten ganzzahligen Wert.
Die Rundungsfunktion kann auch ein zusätzliches Argument akzeptieren, das die Anzahl der Dezimalstellen definiert, auf die gerundet werden soll, sowie arithmetische Operationen anstelle eines einzelnen Spaltennamens. Versuchen Sie beispielsweise, die folgende Abfrage auszuführen:
SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;
Sie erhalten die folgende Ausgabe:
Output
+------------------------------------+-------+-------+-------------+
| title | stock | price | stock_price |
+------------------------------------+-------+-------+-------------+
| The Picture of Dorian Gray | 4 | 20.83 | 83.3 |
| Pride and Prejudice | 12 | 42.13 | 505.6 |
| The Time Machine | 7 | 21.99 | 153.9 |
| Frankenstein | 9 | 17.43 | 156.9 |
| The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 |
+------------------------------------+-------+-------+-------------+
5 rows in set (0.000 sec)
Die Ausführung von ROUND(price * stock, 1) multipliziert zunächst den Einzelbuchpreis mit der Anzahl der Bücher auf Lager und rundet dann den resultierenden Preis auf die erste Dezimalstelle. Das Ergebnis wird in der temporären Spalte stock_price angezeigt.
Weitere in MySQL integrierte mathematische Funktionen umfassen trigonometrische Funktionen, Quadratwurzeln, Potenzen, Logarithmen und Exponentialfunktionen.
Verwendung von String-Manipulationsfunktionen in SQL
String-Manipulationsfunktionen in SQL ermöglichen es Ihnen, die in Textspalten gespeicherten Werte zu verändern, wenn die SQL-Abfrage verarbeitet wird. Sie können unter anderem verwendet werden, um Zeichen in Groß- oder Kleinbuchstaben umzuwandeln. Des Weiteren können sie Daten aus mehreren Spalten verknüpfen oder Such- und Ersetzungsoperationen durchführen.
Sie rufen alle Buchtitel ab und konvertieren sie in Kleinbuchstaben, um mit Zeichenkettenfunktionen zu beginnen. Führen Sie die folgende Anweisung aus:
SELECT LOWER(title) AS title_lowercase FROM inventory;
Die folgende Ausgabe wird auf dem Bildschirm angezeigt:
Output
+------------------------------------+
| title_lowercase |
+------------------------------------+
| the picture of dorian gray |
| pride and prejudice |
| the time machine |
| frankenstein |
| the adventures of huckleberry finn |
+------------------------------------+
5 rows in set (0.001 sec)
Die SQL-Funktion namens LOWER nimmt ein einzelnes Argument und konvertiert dessen Inhalt in Kleinbuchstaben. Durch den Spaltenalias AS title_lowercase werden die resultierenden Daten in der temporären Spalte title_lowercase angezeigt.
Rufen Sie nun alle Autoren ab, diesmal in Großbuchstaben umgewandelt. Führen Sie die folgende SQL-Abfrage aus:
SELECT UPPER(author) AS author_uppercase FROM inventory;
Sie erhalten die folgende Ausgabe:
Output
+----------------------+
| author_uppercase |
+----------------------+
| OSCAR WILDE |
| JANE AUSTEN |
| HERBERT GEORGE WELLS |
| MARY SHELLEY |
| MARK TWAIN |
+----------------------+
5 rows in set (0.000 sec)
Statt der LOWER-Funktion verwenden Sie die UPPER-Funktion, die ähnlich funktioniert, aber den Text in Großbuchstaben konvertiert. Verwenden Sie beide Funktionen, um beim Abrufen von Daten eine Konsistenz der Zeichen sicherzustellen.
Eine weitere nützliche Zeichenkettenmanipulationsfunktion ist CONCAT, die mehrere Argumente mit Textwerten verknüpft. Versuchen Sie, die Buchautoren und Titel innerhalb einer einzigen Spalte abzurufen. Führen Sie dazu die folgende Anweisung aus:
SELECT CONCAT(author, ': ', title) AS full_title FROM inventory;
Diese Anweisung gibt die folgende Ausgabe zurück:
Output
+------------------------------------------------+
| full_title |
+------------------------------------------------+
| Oscar Wilde: The Picture of Dorian Gray |
| Jane Austen: Pride and Prejudice |
| Herbert George Wells: The Time Machine |
| Mary Shelley: Frankenstein |
| Mark Twain: The Adventures of Huckleberry Finn |
+------------------------------------------------+
5 rows in set (0.001 sec)
Die CONCAT-Funktion hat mehrere Zeichenketten zusammengefügt und wurde mit drei Argumenten ausgeführt. Zuerst bezieht sich author auf die Spalte mit den Autorennamen. Zweitens ist „: “ ein willkürlicher Zeichenkettenwert, um Autoren und Buchtitel mit einem Doppelpunkt zu trennen. Schließlich bezieht sich title auf die Spalte mit den Buchtiteln.
In der Ausgabe dieser Abfrage werden die Autoren und Titel in einer einzigen temporären Spalte namens full_title zurückgegeben, direkt von der Datenbank-Engine verkettet.
Weitere in MySQL integrierte Zeichenkettenfunktionen umfassen Funktionen zum Suchen und Ersetzen von Zeichenketten, zum Abrufen von Unterzeichenketten, zum Auffüllen und Kürzen von Zeichenkettenwerten sowie zum Anwenden regulärer Ausdrücke und mehr.
Verwendung von Datums- und Zeitfunktionen in SQL
Datums- und Zeitfunktionen in SQL ermöglichen die Manipulation von Datums- und Zeitstempeln in Spalten. Sie können Teile der Datumsinformationen extrahieren, Datumsarithmetik durchführen oder Datumsformate nach Bedarf anpassen.
Angenommen, Sie möchten das Einführungsdatum eines Buches in Jahr, Monat und Tag aufteilen. Nutzen Sie dazu folgende SQL-Anweisung:
SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;
Die Ausgabe zeigt das Einführungsdatum, unterteilt in Jahr, Monat und Tag:
Output +——————-+——+——-+——+ | introduction_date | year | month | day | +——————-+——+——-+——+ | 2022-10-01 | 2022 | 10 | 1 | | 2022-10-04 | 2022 | 10 | 4 | | 2022-09-23 | 2022 | 9 | 23 | | 2022-07-23 | 2022 | 7 | 23 | | 2022-10-01 | 2022 | 10 | 1 | +——————-+——+——-+——+ 5 rows in set (0.000 sec)
Hier werden die Funktionen YEAR, MONTH und DAY verwendet, um einzelne Teile des Datums abzurufen.
Eine weitere nützliche Funktion ist DATEDIFF, die die Tage zwischen zwei Daten berechnet. Die folgende Abfrage gibt die Tage seit dem Einführungsdatum bis zum aktuellen Datum zurück:
SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;
Die DATEDIFF-Funktion ermittelt die Differenz in Tagen zwischen zwei Datumsangaben und kann negative Werte zurückgeben, wenn das Enddatum früher liegt.
Hinweis: DATEDIFF ist nicht im offiziellen SQL-Standard enthalten; die Syntax kann je nach Datenbank variieren.
Verwendung von Aggregatfunktionen
In allen bisherigen Beispielen haben Sie SQL-Funktionen verwendet, um Berechnungen für einzelne Spaltenwerte innerhalb einer Zeile durchzuführen, die ein Buch in einer Buchhandlung darstellt. Mit SQL führen Sie Berechnungen über mehrere Zeilen hinweg aus, um aggregierte Informationen über das gesamte Dataset zu erhalten.
Die wichtigsten Aggregatfunktionen in SQL sind:
AVG: Berechnet den Durchschnitt. COUNT: Zählt die Anzahl der Werte. MAX: Findet den maximalen Wert. MIN: Findet den minimalen Wert. SUM: Berechnet die Summe aller Werte.
Sie können mehrere Aggregatfunktionen in Ihrer SELECT-Abfrage verwenden. Angenommen, Sie möchten die Anzahl der Bücher, den Höchstpreis eines Buches und den Durchschnittspreis über das gesamte Katalog abrufen. Führen Sie dazu die folgende Anweisung aus:
SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;
Diese Anweisung gibt die folgende Ausgabe zurück:
Output +——-+———–+———–+ | count | max_price | avg_price | +——-+———–+———–+ | 5 | 42.13 | 25.106000 | +——-+———–+———–+ 1 row in set (0.001 sec)
Die obige Abfrage verwendet drei Aggregatfunktionen gleichzeitig. COUNT zählt die Zeilen, MAX berechnet den höchsten Wert aus der Spalte price, und AVG berechnet den Durchschnitt aller Preise.
Durch die Verwendung von Aggregatfunktionen auf diese Weise gibt die Datenbank eine einzelne Zeile mit temporären Spalten zurück, die die Werte der Aggregatberechnungen darstellen. Die Abfrage verwendet die Quellzeilen intern für die Berechnung, ohne sie zurückzugeben. In diesem Beispiel berechnen Sie mit Aggregatfunktionen statistische Werte für die gesamte Tabelle inventory.
SQL ermöglicht es auch, Tabellenzeilen zu gruppieren und Werte für jede Gruppe separat zu berechnen. Sie können den Durchschnittspreis pro Autor berechnen, um herauszufinden, welcher Autor die teuersten Bücher veröffentlicht.
Fazit
Mit diesem Leitfaden haben Sie gelernt, was SQL-Funktionen sind und wie Sie Zahlen, Text und Daten manipulieren. Sie haben ROUND verwendet, um numerische Werte zu runden, CONCAT, um mehrere Spalten in einer zu verknüpfen, und DATEDIFF, um die Anzahl der Tage zwischen zwei Zeitpunkten zu berechnen. Schließlich haben Sie auch Aggregatfunktionen wie COUNT, SUM oder AVG verwendet, um Zusammenfassungen über mehrere Zeilen hinweg zu erstellen.
Sie können Funktionen verwenden, um einige der Datenmanipulationen und Berechnungen auf die Datenbank-Engine auszulagern. Dieses Tutorial hat nur die Grundlagen der Verwendung von Funktionen zu diesem Zweck behandelt. Für robustes Abrufen und Analysieren von Daten können Sie Funktionen mit WHERE-Klauseln und Gruppierung kombinieren.
Diese Befehle funktionieren in den meisten Datenbanken, aber jede SQL-Datenbank verwendet ihre eigene Sprachimplementierung. Sie sollten die offizielle Dokumentation Ihres DBMS konsultieren, um eine vollständige Beschreibung jedes Befehls und seiner vollständigen Optionssätze zu erhalten.
