Wildcards in SQL: So nutzen Sie Platzhalter für flexible Datenabfragen
Einführung
Wie viele Programmiersprachen erlaubt SQL die Verwendung verschiedener Wildcard-Zeichen. Wildcards in SQL sind spezielle Platzhalterzeichen, die ein oder mehrere andere Zeichen oder Werte darstellen können. Dies ist eine nützliche Funktion in SQL, da es Ihnen ermöglicht, Ihre Datenbank nach Daten zu durchsuchen, ohne die genauen Werte zu kennen, die darin gespeichert sind.
Dieser Leitfaden erklärt, wie Sie Daten mit Wildcards in SQL abrufen können.
Voraussetzungen für Wildcards in SQL
Um diesem Leitfaden zu folgen, benötigen Sie einen Computer mit einem relationalen Datenbankmanagementsystem (RDBMS), das SQL verwendet. Die Anweisungen und Beispiele in diesem Leitfaden wurden mit der folgenden Umgebung validiert:
- Ein Server mit Ubuntu 20.04, einem nicht-root Benutzer mit administrativen Berechtigungen und einer Firewall, die mit UFW konfiguriert wurde, wie in unserem ersten Server-Setup-Leitfaden für Ubuntu 20.04 beschrieben.
- MySQL, das auf dem Server installiert und gesichert wurde, wie in „Wie man MySQL auf Ubuntu 20.04 installiert“ beschrieben. Dieser Leitfaden wurde mit einem nicht-root MySQL-Benutzer validiert, der mit dem in Schritt 3 beschriebenen Prozess erstellt wurde.
Hinweis: Bitte beachten Sie, dass viele RDBMS ihre eigenen einzigartigen Implementierungen von SQL verwenden. Obwohl die in diesem Tutorial beschriebenen Befehle auf den meisten RDBMS funktionieren, kann die genaue Syntax oder das Ergebnis abweichen, wenn Sie sie auf einem anderen System als MySQL testen.
Sie benötigen auch eine Datenbank und eine Tabelle, die mit Beispieldaten gefüllt sind, mit denen Sie die Verwendung von Wildcards in SQL üben können. Wenn Sie dies nicht haben, können Sie den Abschnitt „Mit MySQL verbinden und eine Beispieldatenbank einrichten“ lesen, um zu erfahren, wie Sie eine Datenbank und eine Tabelle erstellen, die in den Beispielen dieses Leitfadens verwendet wird.
Mit MySQL verbinden und eine Beispieldatenbank einrichten
Wenn Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, stellen Sie über SSH eine Verbindung zu Ihrem Server von Ihrem lokalen Rechner her:
ssh sammy@your_server_ip
Öffnen Sie dann die MySQL-Serveraufforderung und ersetzen Sie „sammy“ durch den Namen Ihres MySQL-Benutzerkontos:
mysql -u sammy -p
Erstellen Sie eine Datenbank namens wildcardsDB:
CREATE DATABASE wildcardsDB;
Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie folgende Ausgabe:
Query OK, 1 row affected (0.01 sec)
Um die wildcardsDB-Datenbank auszuwählen, führen Sie den folgenden USE-Befehl aus:
USE wildcardsDB;
Ausgabe:
Database changed
Nachdem Sie die Datenbank ausgewählt haben, können Sie eine Tabelle mit folgendem Befehl erstellen. Angenommen, Sie möchten eine Tabelle namens user_profiles erstellen, die Profilinformationen von Benutzern einer Anwendung enthält. Diese Tabelle wird die folgenden fünf Spalten enthalten:
- user_id: Diese Spalte enthält Werte des Typs int und dient als Primärschlüssel der Tabelle, wobei jeder Wert als eindeutiger Bezeichner für die jeweilige Zeile fungiert.
- name: Der Name jedes Benutzers, der mit dem Datentyp varchar (max. 30 Zeichen) dargestellt wird.
- email: Diese Spalte enthält die E-Mail-Adressen der Benutzer, ebenfalls im Datentyp varchar, jedoch mit maximal 40 Zeichen.
- birthdate: Diese Spalte enthält das Geburtsdatum jedes Benutzers im Datentyp date.
- quote: Das Lieblingszitat jedes Benutzers. Um genügend Zeichen für Zitate bereitzustellen, verwendet diese Spalte ebenfalls den Datentyp varchar, aber mit maximal 300 Zeichen.
Führen Sie den folgenden Befehl aus, um diese Beispieltabelle zu erstellen:
CREATE TABLE user_profiles (
user_id int,
name varchar(30),
email varchar(40),
birthdate date,
quote varchar(300),
PRIMARY KEY (user_id)
);
Ausgabe:
Database changed
Fügen Sie nun einige Beispieldaten in die leere Tabelle ein:
INSERT INTO user_profiles
VALUES
(1, 'Kim', 'bd_eyes@example.com', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
(2, 'Ann', 'cantstandrain@example.com', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
(3, 'Phoebe', 'poetry_man@example.com', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
(4, 'Jim', 'u_f_o@example.com', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'),
(5, 'Timi', 'big_voice@example.com', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
(6, 'Taeko', 'sunshower@example.com', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'),
(7, 'Irma', 'soulqueen_NOLA@example.com', '1941-02-18', '"You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss'),
(8, 'Iris', 'our_town@example.com', '1961-01-05', '"You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');
Ausgabe:
Query OK, 8 rows affected (0.00 sec)
Jetzt können Sie mit dem Rest des Leitfadens fortfahren und lernen, wie Sie Wildcards verwenden, um Daten in SQL abzufragen.
Abfragen von Daten mit Wildcards in SQL
Wie in der Einführung erwähnt, sind Wildcards spezielle Platzhalterzeichen, die ein oder mehrere andere Zeichen oder Werte darstellen können.
In SQL gibt es nur zwei definierte Wildcard-Zeichen:
- _: Ein Unterstrich repräsentiert ein einzelnes Zeichen. Zum Beispiel würde s_mmy mit sammy, sbmmy oder sxmmy übereinstimmen.
- %: Das Prozentzeichen stellt null oder mehr Zeichen dar. Zum Beispiel würde s%mmy mit sammy, saaaaaammy oder smmy übereinstimmen.
Diese Wildcards in SQL werden ausschließlich in der WHERE-Klausel einer Abfrage mit den Operatoren LIKE oder NOT LIKE verwendet.
Um mit den Beispieldaten aus dem Abschnitt „Voraussetzungen“ zu arbeiten, nehmen wir an, Sie wissen, dass mindestens einer der Benutzer in der Tabelle user_profiles einen Namen hat, der aus drei Buchstaben besteht und mit „im“ endet, aber Sie wissen nicht, wer es ist. Da Sie nur über den ersten Buchstaben dieser Namen unsicher sind, könnten Sie die folgende Abfrage ausführen, die den _-Wildcard verwendet, um herauszufinden, wer es ist:
SELECT * FROM user_profiles WHERE name LIKE '_im';
Ausgabe:
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| user_id | name | email | birthdate | quote |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| 1 | Kim | bd_eyes@example.com | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
| 4 | Jim | u_f_o@example.com | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Hinweis: In diesem Beispiel folgt ein Sternchen (*) dem SELECT. Dies ist eine Abkürzung in SQL und stellt „jede Spalte“ dar.
In einigen Anwendungen und Programmiersprachen und sogar in einigen Implementierungen von SQL werden Sternchen als Wildcard-Zeichen verwendet, die null oder mehr Zeichen darstellen, genauso wie das Prozentzeichen in diesem Beispiel. Das Sternchen in diesem Beispiel ist jedoch keine Wildcard, da es etwas Spezifisches darstellt — nämlich jede Spalte in der Tabelle user_profiles — und nicht ein oder mehrere unbekannte Zeichen.
Der Operator NOT LIKE hat den gegenteiligen Effekt von LIKE. Anstatt jedes Datensatz zurückzugeben, der dem Wildcard-Muster entspricht, gibt er jede Zeile zurück, die nicht dem Muster entspricht. Um dies zu veranschaulichen, führen Sie die vorherige Abfrage erneut aus, aber ersetzen Sie LIKE durch NOT LIKE:
SELECT * FROM user_profiles WHERE name NOT LIKE '_im';
Diesmal wird jede Zeile, bei der der Wert in der Spalte name mit _im übereinstimmt, aus dem Ergebnis entfernt:
Ausgabe:
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+|
| user_id | name | email | birthdate | quote |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+|
| 2 | Ann | cantstandrain@example.com | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
| 3 | Phoebe | poetry_man@example.com | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 5 | Timi | big_voice@example.com | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
| 6 | Taeko | sunshower@example.com | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky |
| 7 | Irma | soulqueen_NOLA@example.com | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
| 8 | Iris | our_town@example.com | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+|
6 rows in set (0.00 sec)
Als weiteres Beispiel, nehmen wir an, Sie wissen, dass mehrere der Benutzer in der Datenbank Namen haben, die mit „I“ beginnen, aber Sie können sich nicht an alle erinnern. Sie könnten das % Wildcard verwenden, um alle aufzulisten, wie in der folgenden Abfrage gezeigt:
SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';
Ausgabe:
+---------+------+----------------------------+|
| user_id | name | email |
+---------+------+----------------------------+|
| 7 | Irma | soulqueen_NOLA@example.com |
| 8 | Iris | our_town@example.com |
+---------+------+----------------------------+|
2 rows in set (0.00 sec)
Hinweis: In MySQL sind die LIKE- und NOT LIKE-Operatoren standardmäßig nicht groß- und kleinschreibungsempfindlich. Dies bedeutet, dass die vorherige Abfrage auch dann die gleichen Ergebnisse zurückgibt, wenn Sie das „I“ im Wildcard-Muster nicht großschreiben:
SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';
Ausgabe:
+---------+------+----------------------------+|
| user_id | name | email |
+---------+------+----------------------------+|
| 7 | Irma | soulqueen_NOLA@example.com |
| 8 | Iris | our_town@example.com |
+---------+------+----------------------------+|
2 rows in set (0.00 sec)
Beachten Sie, dass Wildcards sich von regulären Ausdrücken unterscheiden. Im Allgemeinen bezeichnet ein Wildcard ein Zeichen, das in der Mustererkennung mit „Glob“-Stil verwendet wird, während reguläre Ausdrücke eine reguläre Sprache zur Mustererkennung von Zeichenfolgen verwenden.
Escape von Wildcard-Zeichen
Es kann Zeiten geben, in denen Sie nach Daten suchen möchten, die eines der SQL-Wildcard-Zeichen enthalten. In solchen Fällen können Sie ein Escape-Zeichen verwenden, das SQL anweist, die Wildcard-Funktion von % oder _ zu ignorieren und diese stattdessen als normalen Text zu interpretieren.
Angenommen, Sie wissen, dass mindestens einige der Benutzer in der Datenbank ein Lieblingszitat haben, das ein Prozentzeichen enthält, aber Sie wissen nicht, wer sie sind.
Sie könnten die folgende Abfrage ausführen:
SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';
Diese Abfrage wird jedoch nicht sehr hilfreich sein. Da das Prozentzeichen als Platzhalter für eine beliebige Zeichenfolge beliebiger Länge dient, gibt sie jede Zeile in der Tabelle zurück:
Ausgabe
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+|
| user_id | name | quote |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+|
| 1 | Kim | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
| 2 | Ann | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
| 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 4 | Jim | "Whoever is happy will make others happy too." -Anne Frank |
| 5 | Timi | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
| 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky |
| 7 | Irma | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
| 8 | Iris | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+|
8 rows in set (0.00 sec)
Um das Prozentzeichen zu entkommen, können Sie es mit einem Backslash (\) versehen, MySQL’s Standard-Escape-Zeichen:
SELECT * FROM user_profiles WHERE quote LIKE '\%';
Diese Abfrage wird jedoch auch nicht hilfreich sein, da sie angibt, dass der Inhalt der Spalte quote nur aus einem Prozentzeichen bestehen soll. Daher wird das Ergebnis leer sein:
Ausgabe
Empty set (0.00 sec)
Um dies zu korrigieren, müssen Sie Wildcards mit Prozentzeichen am Anfang und Ende des Suchmusters nach dem LIKE-Operator einfügen:
SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';
Ausgabe:
+---------+--------+----------------------------------------------------------------------------+|
| user_id | name | quote |
+---------+--------+----------------------------------------------------------------------------+|
| 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky |
+---------+--------+----------------------------------------------------------------------------+|
2 rows in set (0.00 sec)
In dieser Abfrage entkommt der Backslash nur dem zweiten Prozentzeichen, während das erste und dritte immer noch als Wildcards fungieren. Daher gibt diese Abfrage jede Zeile zurück, deren Zitat-Spalte mindestens ein Prozentzeichen enthält.
Beachten Sie, dass Sie auch benutzerdefinierte Escape-Zeichen mit der ESCAPE-Klausel definieren können, wie im folgenden Beispiel:
SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';
Ausgabe:
+---------+--------+----------------------------+|
| user_id | name | email |
+---------+--------+----------------------------+|
| 1 | Kim | bd_eyes@example.com |
| 3 | Phoebe | poetry_man@example.com |
| 4 | Jim | u_f_o@example.com |
| 5 | Timi | big_voice@example.com |
| 7 | Irma | soulqueen_NOLA@example.com |
+---------+--------+----------------------------+|
5 rows in set (0.00 sec)
Diese Abfrage definiert das @-Zeichen als Escape-Zeichen und gibt jede Zeile zurück, deren E-Mail-Spalte mindestens einen Unterstrich enthält. Wenn Sie die ESCAPE-Klausel entfernen, gibt die Abfrage jede Zeile aus der Tabelle zurück, da jede Zeile mindestens ein @-Zeichen enthält.
Fazit zu Wildcards in SQL
Durch das Lesen dieses Leitfadens haben Sie gelernt, wie Sie Wildcards in SQL-basierten Datenbanken verwenden und entkommen können. Die hier beschriebenen Befehle sollten auf jedem Datenbankmanagementsystem, das SQL verwendet, funktionieren. Beachten Sie, dass jede SQL-Datenbank ihre eigene einzigartige Implementierung der Sprache verwendet, sodass Sie die offizielle Dokumentation Ihres DBMS konsultieren sollten, um eine vollständige Beschreibung jedes Befehls und seiner vollständigen Optionen zu erhalten.
Wenn Sie mehr über die Arbeit mit SQL erfahren möchten, empfehlen wir Ihnen, die anderen Tutorials dieser Serie zu „How To Use SQL“ zu lesen.
