Erlernen einer Datenbanksprache: Structured Query Language (SQL) II

Einsatz von Funktionen

Im vorangehenden Kapitel haben wir die Grundlagen der Datenbanksprache SQL kennengelernt und haben uns mit dem Aufbau der Abfragen und verschiedenen Anwendungsbeispielen vertraut gemacht.
In SQL-Anfragen kann eine Vielzahl von Funktionen zum Einsatz kommen. Eine Funktion besteht aus dem Funktionsnamen, gefolgt von einer sich öffnenden und schließenden Klammer (), ohne Leerzeichen. Innerhalb der Klammern befinden sich der oder die Parameter, die an die Funktion übergeben werden. Mit Hilfe von Funktionen können praktische Veränderungen am Datenbestand vorgenommen werden. Eine ausführliche Übersicht zu Funktionen mit ausführlicher Funktionsbeschreibung bietet die MySQL-Dokumentation.
Hinweis: Solange sie nur Bestandteil einer SELECT-Abfrage sind, kann nichts passieren! Es wird stets nur eine Ausgabe erzeugt und keine dauerhafte Änderung am Datenbestand vorgenommen.

Funktionen in SQL - eine Auswahl

Im Folgenden sollen nur ein paar grundlegende Beispiele vorgestellt werden, um die Wirkung von Funktionen zu verdeutlichen. Wenn Sie das Calc-Manual durchgearbeitet haben, werden Sie viele bekannte Funktionen wiedererkennen.
To Do: Weitere wichtige Funktionen hier ergänzen.

Funktion	Beschreibung
LENGTH(`name`)	Feststellen der Länge einer Zeichenfolge
CONCAT(`vorname`, ,`name`)	Ausgabe von Vorname und Name in einer Spalte, getrennt durch ein Leerzeichen
CONCAT(`<person>`,`vorname`, ,`name`,`</person>`)	Beispiel für das beliebige Einbinden von Zusätzen, hier der XML-Tags um Vor- und Nachname
REVERSE(`name`)	Ausgabe des Feldinhalts innerhalb einer Spalte in umgekehrter Reihenfolge
REPLACE(`name`,`oe`,`ö`)	Ersetzt alle oe-Fälle durch ö in der Spalte `name`
COUNT(*)	Zählen der Häufigkeit eines gewünschten Parameters und Ausgabe des Ergebnisses als Zahl; z.B.: COUNT(*) AS Anzahl FROM `Historiker` WHERE `ort` = 'München'
MIN(), MAX(), SUM(), AVG()	Neben COUNT() gibt es weitere Aggregatsfunktionen. Sie fassen die Werte mehrerer (gruppierter) Zeilen zu einem Ausgabewert zusammen.

Die letztgenannten mathematischen Funktionen spielen neben Stringfunktionen und Gruppierungsfunktionen (insb. COUNT() und GROUP_CONCAT()) bei der Beantwortung korpuslinguistischer Fragestellungen eine gesteigerte Bedeutung.

Anwendungsbeispiel einer Funktion: COUNT()

~ Bei folgender Ausgabe wird jeder Ort mit der jeweiligen Anzahl an Vorkommen angeführt, wobei durch "GROUP BY" die Zeilen mit gleichem Gruppierungswert zu einer Zeile zusammengefasst werden:
SELECT `ort`, count(*) AS Anzahl FROM `Historiker` GROUP BY `ort`;

~ Nun zeigt diese Anfrage jedoch zu jedem Ort nur eine der zahlreichen zugehörigen Personen an. Die Lösung des Problems:
SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname`) FROM `Historiker` GROUP BY `ort`;
Damit werden nun alle Orte mit ihrem Vorkommen und innerhalb der einzelnen Ortsspalte auch die zugehörigen Personen angezeigt.
~ Da die Gruppenkonkatenierung standardmäßig auf 1024 Zeichen begrenzt ist, muss diese möglicherweise durch die Ergänzung folgenden Kommandos umgangen werden:
SET SESSION GROUP_CONCAT_MAX_LEN = 100000

Man kann die Ausgabe nun noch weiter verfeinern:
~ Zeilenumbruch zwischen den einzelnen Namen ergänzen:
SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname` SEPARATOR '\n') FROM `Historiker` GROUP BY `ort`;
~ Zuweisen eines Spaltennamens und alphabetische Sortierung der Namen:
SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname` ORDER BY `name` SEPARATOR '\n') AS 'Historiker' FROM `Historiker` GROUP BY `ort`;
~ Die Mit Hilfe von GROUP BY ermittelte Anzahl an Zeilen könnte schließlich noch durch eine HAVING-Klausel eingeschränkt werden. Die Bedingung einer HAVING-Klausel bezieht (im Unterschied zu WHERE) immer auf das Ergebnis einer Aggregationsfunktion:
SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname` ORDER BY `name` SEPARATOR '\n') AS 'Historiker' FROM `Historiker` GROUP BY `ort` HAVING COUNT(*) > 20;
Es würden nun also nur sämtliche Universitätsstandorte aufgelistet werden, denen jeweils mehr als 20 Historiker in der Datenbank zugeordnet wurden.

Endergebnis: Es wird damit eine sehr übersichtlich gestaltete Anzeige ausgegeben. Für eine anschließende Visualisierung erscheint eine gewichtete Abfrage zur statistischen Auswertung anstelle der absoluten Werte sinnvoll; auf gewichtete Abfragen werden wir im folgenden Kapitel zurückkommen.

Verknüpfung von Tabellen: Joins

vgl. hierzu: https://www.dh-lehre.gwi.uni-muenchen.de/?p=33389 Bisher haben wir jeweils nur mit einer Tabelle gearbeitet. Das war für das Kennenlernen der einzelnen Bestandteile von SQL-Abfragen sinnvoll. Nun möchten wir uns den Joins und damit mit der Möglichkeit, verbundener Tabellenabfragen, zuwenden. Mit Hilfe der IDs können (beliebig viele) verschiedene Tabellen und ihre Inhalte in Verbindung gesetzt werden. Bildlich gesprochen werden mehrere Tabellen nebeneinander gelegt. Es bestehen verschiedene Arten von Joins. Im Folgenden sollen die für die Arbeit mit Datenbanken wichtigsten Joins (INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN) vorgestellt werden.

Wir werden mit drei Beispieltabellen arbeiten (Konzeption: Stephan Lücke (ITG)):
— cports = eine Liste an Mittelmeerhäfen, die von verschiedenen Fährlinien angesteuert werden
— cbetreiber = Liste der betreibenden Fährunternehmen
— cconnections = Auflistung aller Fährverbindungen zwischen den Häfen unter Angabe des Betreibers und der saisonalen Verfügbarkeit
Die Beispieltabellen befinden sich in der Datenbank "lab_dhsummerschool1". Kopieren Sie zunächst, wie bereits beschrieben, die Tabellen in Ihre persönliche Datenbank (labuser_mmustermann).

INNER JOIN

~ 'Joint' man zwei Tabellen miteinander, ohne dabei ein Kriterium anzugeben, würde man ein Kreuzprodukt (auch kartesisches Produkt genannt) erhalten, d.h. eine Kombination aller Spalten der Tabelle A mit allen Spalten der Tabelle B - einen Datenwust also, der niemandem etwas nützt. Probieren Sie es dennoch einmal aus:
SELECT * FROM cconnections JOIN cbetreiber;

Die Abfrage muss daher spezifiziert werden: Der INNER JOIN führt all diejenigen Datensätze zweier Tabellen zusammen, bei denen die im Join angegebenen Kriterien erfüllt werden. Sobald ein Kriterium nicht erfüllt ist, taucht der entsprechende Datensatz nicht in der Ausgabe auf.
Wir erweitern die eben getätigte Abfrage um eine ON-Bedingung, in der der Inhalt der Spalte "betreiber" aus Tabelle cconnections dem Inhalt der Spalte "betreiber" der Tabelle cbetreiber entspricht:
SELECT * FROM cconnections JOIN cbetreiber ON (cconnections.betreiber = cbetreiber.betreiber); Gleichlautende (ambiguous) Spaltennamen müssen durch das Voranstellen des jeweiligen Tabellennamen, verbunden durch einen Punkt, kenntlich gemacht werden. Um sich gerade bei längeren Anfragen das immer wiederkehrende Eingeben der Tabellennamen abzukürzen, kann man den Tabellen (wie schon gelernt) Korrelationsnamen zuweisen; in unserem Fall: cconnections wird dem Buchstaben 'a' zugewiesen, cbetreiber wird zu 'b':
SELECT * FROM cconnections AS a JOIN cbetreiber AS b ON (a.betreiber = b.betreiber);
Sie erhalten nun eine Ausgabe mit allen Informationen (weil: *) beider Tabellen in einer Tabelle ausgegeben. Nehmen wir an, wir möchten nur den Betreiber, den Start- und Zielhafen (hier erstmal nur in Form der ID) und die Saison ausgegeben bekommen, so schränken wir die Auswahl der Spalten wie folgt ein:
SELECT aid, bid, saison, a.betreiber FROM cconnections AS a JOIN cbetreiber AS b ON (a.betreiber = b.betreiber);
Alternativ kann der INNER JOIN auch mit Hilfe der USING-Funktion durchgeführt werden:
SELECT aid, bid, saison, a.betreiber FROM cconnections AS a JOIN cbetreiber AS b USING(betreiber);
(in diesem Fall entfällt der Tabellen-Zusatz)

SELFJOIN

Bei einem Selfjoin greift man zweimal auf dieselbe Tabelle zu. Um die beiden Zugriffe voneinander zu unterscheiden, weisen wir Korrelatsnamen (hier wiederum a und b) zu. In unserem Fall sind alle Häfen in der Tabelle cports verzeichnet. Nun möchten wir allen Häfen, die auf Korsika liegen, die potentiellen Verbindungsziele am Festland zuweisen (≈ alle Häfen, die nicht auf Korsika liegen):
SELECT * FROM cports AS a JOIN cports AS b ON (a.region LIKE 'Corse' AND b.region NOT LIKE 'Corse');

Schnell merken wir, dass diese Auflistung nicht zufriedenstellend sein kann, da noch nicht geklärt ist, welche dieser potentiellen Verbindungen denn nun tatsächlich Fährbetrieb aufweisen. Hierzu wird ein weiterer Join, mit der Tabelle cconnections, benötigt:
SELECT * FROM cports AS a JOIN cports AS b ON (a.id NOT LIKE b.id AND a.region LIKE 'Corse' AND b.region NOT LIKE 'Corse') JOIN cconnections AS c ON (a.id = c.aid AND b.id = c.bid);

LEFT JOIN (LEFT OUTER JOIN)/ RIGHT JOIN (RIGHT OUTER JOIN)

Ausgegeben werden beim Left Join alle Datensätze der linken Tabelle, auch dann, wenn es seitens der gejointen rechten Tabelle keine korrespondierenden Einträge gibt. Korrespondierende Informationen der rechten Tabelle werden, sofern vorhanden, den entsprechenden Einträgen angefügt.
Merke: Tabelle A LEFT JOIN Tabelle B gibt dasselbe Ergebnis aus wie Tabelle B RIGHT JOIN Tabelle A
Wird bei der vorgenannten SQL-Abfrage der zweite JOIN zur Tabelle cconnections zu einem LEFT JOIN, so werden damit auch alle Häfen angezeigt, zwischen denen keine Fährverbindung besteht:
SELECT * FROM cports AS a JOIN cports AS b ON (a.id NOT LIKE b.id AND a.region LIKE 'Corse' AND b.region NOT LIKE 'Corse') LEFT JOIN cconnections AS c ON (a.id = c.aid AND b.id = c.bid) WHERE c.aid IS NULL;

Um unsere eben erlernten Kenntnisse im Bereich der verknüpften Abfrage von Tabellen zu intensivieren, sehen wir uns ein weiteres Beispiel für INNER JOIN/LEFT JOIN an: Beispiel Vorlesungsverzeichnis:
— "dozent" (persid, titel, name, vorname, einrichtung)
— "kurs" (vlid, titel, beschreibung, art, persid, belid)
— "belegung" (belid, gebäude, raum, vlid)
To Do: Beispieldateien erstellen, ablegen und Pfad verlinken/angeben

• INNER JOIN

~ Listen Sie alle Dozenten und die zugehörigen Lehrveranstaltungen auf unter Berücksichtigung des Titels und der Art der Veranstaltung sowie Name und Vorname, zusammengefasst (Name, Vorname):
SELECT kurs.titel, art, CONCAT(`name`, ',', `vorname`) FROM dozent INNER JOIN kurs ON dozent.persid = kurs.persid;

• LEFT JOIN

~ Listen Sie alle Dozenten mit Lehrveranstaltung auf, aber auch diejenigen, die aktuell keine Lehrveranstaltung anbieten. Vorlesungen, denen noch keine Lehrperson zugeordnet wurde, sollen nicht angezeigt werden; unter Berücksichtigung oben genannter Parameter:
SELECT CONCAT(`name`, ',', `vorname`) FROM dozent LEFT JOIN kurs ON dozent.persid = kurs.persid WHERE kurs.persid IS NOT NULL;

~ Ordnen Sie nun noch alle Dozenten absteigend an, beginnend bei dem mit den meisten angebotenen Lehrveranstaltungen; unter Berücksichtigung oben genannter Parameter:
SELECT COUNT(kurs.persid) AS Anzahl, CONCAT(`name`, ',', `vorname`) FROM dozent LEFT JOIN kurs ON dozent.persid = kurs.persid GROUP BY name DESC;

• INNER JOIN mit drei Tabellen

~ Joins können auch auf mehr als zwei Tabellen angewendet werden, wie Sie dem folgendem Beispiel entnehmen können. Dabei gilt stets: Bei der Verbindung von n Tabellen sind stets n-1 Join-Kriterien erforderlich. Listen Sie alle Dozenten (Name, Vorname) mit den zugehörigen Lehrveranstaltungen und den entsprechenden Vorlesungssälen auf:
SELECT dozent.name, dozent.vorname, kurs.titel, belegung.raum FROM dozent, kurs, belegung WHERE kurs.persid = dozent.persid AND kurs.belid = belegung.vlid;

(FULL) OUTER JOIN

Ausgegeben werden beim FULL OUTER JOIN alle Datensätze der linken und rechten Tabelle, eingegrenzt ggf. durch eine WHERE-Klausel. Es handelt sich also um eine Kombination aus LEFT und RIGHT JOIN. Datensätze, deren Verknüpfungsbedingungen übereinstimmen, werden in einer Zeile ausgegeben. Wo dies nicht der Fall ist, wird in den Zellen der Zeile (beider Tabellen) ein NULL-Wert ausgegeben:
SELECT name FROM dozent FULL OUTER JOIN kurs ON dozent.persid = kurs.persid

To Do: An dieser Stelle ein Join-Beispiel ergänzen, in dem ein zusätzlicher Join eingefügt ist.

Nun haben Sie sich mit den verschiedenen Arten von Joins das Rüstzeug für einen vertieften Zugang zu Datenbanken erarbeitet. Für einen praktischen Einstieg in die Arbeit mit SQL-Datenbanken unter korpuslinguistischen Aspekten sei auf das Beispiel "Grimmsche Märchen" auf DH-Lehre verwiesen.

UNION

Neben Joins gibt es auch die Möglichkeit, zwei Abfrageergebnisse untereinander zu kombinieren, sofern sie beide eine identische Anzahl an Spalten aufweisen und die hierfür ausgewählten Spalten den gleichen Datentyp besitzen. Diese Art der Verbindung wird als UNION bezeichnet.
Bei einer UNION-Abfrage werden alle Duplikate eliminiert (vergleichbar mit einer SELECT-Abfrage mit DISTINCT-Ergänzung). Um auch die Duplikate zu erhalten, muss das Statement UNION ALL verwendet werden.

Einfaches Beispiel zur Veranschaulichung: Die Datenbank eines Forums wird unterteilt eine Tabelle für die Fragen und eine Tabelle für die darauf bezogenen Antworten. Nun gibt es Nutzer, die Fragen und/oder Antworten verfasst haben. Um alle Nutzer aufzulisten, die bisher im Forum entweder durch Fragen oder Antworten (oder beides) aktiv geworden sind, kann man den UNION-Befehl einsetzen:
SELECT nutzer_id FROM fragen UNION SELECT nutzer_id FROM antworten

To Do: ggf. auch hier Beispieldateien erstellen, ablegen und Pfad verlinken/angeben


Zurück zu Erlernen einer Datenbanksprache: Structured Query Language (SQL) I ⇐⇒ Weiter zu Datenbank-Praxis für Fortgeschrittene