View source for 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.<br \>
In SQL-Anfragen kann eine Vielzahl von '''Funktionen''' zum Einsatz kommen. Funktionen haben stets den gleichen Aufbau: 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''' bzw. '''Argumente''' (= die Daten), die an die Funktion übergeben werden. Funktionen können in fast allen Klauseln eines SQL-Statements (u.a. Feld-Liste, WHERE-Klausel, GROUP BY-Klausel) verwendet 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<ref group="A">Vgl. dev.mysql.com/doc/refman/5.7.</ref>.<br \>
<u>Hinweis:</u> 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.
<br \><br \>
==== 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 [http://dhvlab.gwi.uni-muenchen.de/index.php/Hilfreiche_Funktionen_zur_Datenmodellierung Calc-Manual] durchgearbeitet haben, werden Sie viele bekannte Funktionen wiedererkennen.<br \>

{| class="wikitable"
|-
! scope="col"| '''Funktion'''
! scope="col"| '''Beschreibung'''
|-
! scope="row"| <code>LENGTH(`name`)</code>
| Feststellen der Länge einer Zeichenfolge
|-
! scope="row"| <code>CONCAT(`vorname`, ,`name`)</code>
| Ausgabe von Vorname und Name in einer Spalte, getrennt durch ein Leerzeichen
|-
! scope="row"| <code>CONCAT(`<person>`,`vorname`, ,`name`,`</person>`)</code>
| Beispiel für das beliebige Einbinden von Zusätzen, hier der XML-Tags um Vor- und Nachname
|-
! scope="row"| <code>REVERSE(`name`)</code>
| Ausgabe des Feldinhalts innerhalb einer Spalte in umgekehrter Reihenfolge
|-
! scope="row"| <code>UPPER(`name`)</code>
| Ausgabe des Feldinhalts innerhalb einer Spalte ausschließlich in Großbuchstaben
|-
! scope="row"| <code>LOWER(`name`)</code>
| Ausgabe des Feldinhalts innerhalb einer Spalte ausschließlich in Kleinbuchstaben
|-
! scope="row"| <code>REPLACE(`name`,`oe`,`ö`)</code>
| Ersetzt alle oe-Fälle durch ö in der Spalte `name`
|-
! scope="row"| <code>LEFT(spaltenname, 10) / RIGHT(spaltenname, 10)</code>
| Ausgabe einer bestimmten Anzahl von Zeichen einer Zelle in einer Spalte, beginnend von rechts oder links
|-
! scope="row"| <code>COUNT(*)</code>
| Zählen der Häufigkeit eines gewünschten Parameters und Ausgabe des Ergebnisses als Zahl; <br \>z.B.: <code>COUNT(*) AS Anzahl FROM `Historiker` WHERE `ort` = 'München'</code>
|-
! scope="row"| <code>MIN(), MAX(), SUM(), AVG()</code>
| Neben COUNT() gibt es weitere Aggregatsfunktionen. Sie fassen die Werte mehrerer (gruppierter) Zeilen zu einem Ausgabewert zusammen.
|-
|}
Die letztgenannten '''mathematischen Funktionen''' spielen neben '''Stringfunktionen'''<ref group="A">Vgl. hierfür das [http://dev.mysql.com/doc/refman/5.7/en/string-functions.html Kapitel zu String-Funktionen] der MySQL-Dokumentation.</ref> und '''Gruppierungsfunktionen''' (insb. <code>COUNT()</code> und <code>GROUP_CONCAT()</code>) bei der Beantwortung '''korpuslinguistischer Fragestellungen''' eine gesteigerte Bedeutung<ref group="A">Vgl. den entsprechenden Abschnitt in [https://www.dh-lehre.gwi.uni-muenchen.de/?p=33389 DH-Lehre].</ref>.
<br \><br \>

==== 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:<br \>
<code>SELECT `ort`, count(*) AS Anzahl FROM `Historiker` GROUP BY `ort`;</code>
<br \><br \>
~ Nun zeigt diese Anfrage jedoch zu jedem Ort nur eine der zahlreichen zugehörigen Personen an<ref group="A">Zur besseren Illustration des Problems sei folgende Metapher verwandt: Stellen Sie sich vor, es gibt zwei Personengruppen, eine rot, die andere blau gekleidet. Sie gruppieren beide Farben, sodass sie nun zwei Personenreihen haben. Jedoch ist nur die vorderste, größte Person zu sehen. Um alle Personen sehen zu können, muss ein weiterer Befehl angewendet werden.</ref>. Die '''Lösung des Problems''':<br \>
<code>SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname`) FROM `Historiker` GROUP BY `ort`;</code><br \>
Damit werden nun alle Orte mit ihrem Vorkommen und innerhalb der einzelnen Ortsspalte auch die zugehörigen Personen angezeigt. <br \>
~ Da die Gruppenkonkatenierung standardmäßig auf 1024 Zeichen begrenzt ist, muss diese möglicherweise durch die Ergänzung folgenden Kommandos umgangen werden:<br \>
<code>SET SESSION GROUP_CONCAT_MAX_LEN = 100000</code>
<br \><br \>
Man kann die '''Ausgabe''' nun noch '''weiter verfeinern''':<br \>
~ Zeilenumbruch zwischen den einzelnen Namen ergänzen:<br \>
<code>SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname` SEPARATOR '\n') FROM `Historiker` GROUP BY `ort`;</code><br \>
~ Zuweisen eines Spaltennamens und alphabetische Sortierung der Namen:<br \>
<code>SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname` ORDER BY `name` SEPARATOR '\n') AS 'Historiker' FROM `Historiker` GROUP BY `ort`;</code><br \>
~ 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 (vgl. vorangehende Seite):<br \>
<code>SELECT `ort`, count(*) AS Anzahl, GROUP_CONCAT(`name`, ',', `vorname` ORDER BY `name` SEPARATOR '\n') AS 'Historiker' FROM `Historiker` GROUP BY `ort` HAVING COUNT(*) > 20;</code><br \>
Es würden nun also nur sämtliche Universitätsstandorte aufgelistet werden, denen jeweils mehr als 20 Historiker in der Datenbank zugeordnet wurden.
<br \><br \>
<u>Endergebnis:</u> 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 ===
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<ref group="A">Vgl. hierfür das entsprechende Kapitel in [vgl. hierzu: https://www.dh-lehre.gwi.uni-muenchen.de/?p=33389 dh-lehre].</ref>. 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.
<br \><br \>
Wir werden mit '''drei Beispieltabellen''' arbeiten (Konzeption: [http://www.itg.uni-muenchen.de/personen/luecke_stephan/index.html Stephan Lücke (ITG)]):<br \>
— ''cports'' = eine Liste an Mittelmeerhäfen, die von verschiedenen Fährlinien angesteuert werden<br \>
— ''cbetreiber'' = Liste der betreibenden Fährunternehmen<br \>
— ''cconnections'' = Auflistung aller Fährverbindungen zwischen den Häfen unter Angabe des Betreibers und der saisonalen Verfügbarkeit
<br \>
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'').
<br \><br \>
<div class="image">
[[File:Sqljoins.png|center|border|500px]]
</div>
<br \><br \>
==== 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:<br \>
<code>SELECT * FROM cconnections JOIN cbetreiber;</code>
<br \><br \>
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.<br \>
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:<br \>
<code>SELECT * FROM cconnections JOIN cbetreiber ON (cconnections.betreiber = cbetreiber.betreiber);</code>
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':<br \>
<code>SELECT * FROM cconnections AS a JOIN cbetreiber AS b ON (a.betreiber = b.betreiber);</code><br \>
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''':<br \>
<code>SELECT aid, bid, saison, a.betreiber FROM cconnections AS a JOIN cbetreiber AS b ON (a.betreiber = b.betreiber);</code><br \>
Alternativ kann der INNER JOIN auch mit Hilfe der '''USING-Funktion''' durchgeführt werden:<br \>
<code>SELECT aid, bid, saison, a.betreiber FROM cconnections AS a JOIN cbetreiber AS b USING(betreiber);</code><br \>
(in diesem Fall entfällt der Tabellen-Zusatz)
<br \><br \>
==== 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):<br \>
<code>SELECT * FROM cports AS a JOIN cports AS b ON (a.region LIKE 'Corse' AND b.region NOT LIKE 'Corse');</code>
<br \><br \>
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:<br \>
<code>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);</code>
<br \><br \>
==== 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.<br \>
<u>Merke:</u> <code>Tabelle A LEFT JOIN Tabelle B</code> gibt dasselbe Ergebnis aus wie <code>Tabelle B RIGHT JOIN Tabelle A</code><br \>
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:<br \>
<code>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;</code>
<br \><br \>
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:<br \>
— "dozent" (persid, titel, name, vorname, einrichtung)<br \>
— "kurs" (vlid, titel, beschreibung, art, persid, belid)<br \>
— "belegung" (belid, gebäude, raum, vlid)<br \>
<br \><br \>
* 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): <br \>
<code>SELECT kurs.titel, art, CONCAT(`name`, ',', `vorname`) FROM dozent INNER JOIN kurs ON dozent.persid = kurs.persid;</code>
<br \><br \>
* 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:<br \>
<code>SELECT CONCAT(`name`, ',', `vorname`) FROM dozent LEFT JOIN kurs ON dozent.persid = kurs.persid WHERE kurs.persid IS NOT NULL;</code>
<br \><br \>
~ Ordnen Sie nun noch alle Dozenten absteigend an, beginnend bei dem mit den meisten angebotenen Lehrveranstaltungen; unter Berücksichtigung oben genannter Parameter:<br \>
<code>SELECT COUNT(kurs.persid) AS Anzahl, CONCAT(`name`, ',', `vorname`) FROM dozent LEFT JOIN kurs ON dozent.persid = kurs.persid GROUP BY name DESC;</code><br \><br \>
* INNER JOIN mit drei Tabellen
~ '''Joins''' können auch auf '''mehr als zwei Tabellen''' angewendet werden, wie Sie dem folgendem Beispiel entnehmen können. <u>Dabei gilt stets:</u> 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:<br \>
<code>SELECT dozent.name, dozent.vorname, kurs.titel, belegung.raum FROM dozent, kurs, belegung WHERE kurs.persid = dozent.persid AND kurs.belid = belegung.vlid;</code>
<br \><br \>
==== (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:<br \>
<code>SELECT name FROM dozent FULL OUTER JOIN kurs ON dozent.persid = kurs.persid</code>
<br \><br \>
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 [https://www.dh-lehre.gwi.uni-muenchen.de/?p=33487 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 und kann über verschiedene Tabellen, Datenbanken bis hin zu Servern angewendet werden .<br \>
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:
<code>SELECT spalte1, spalte2 FROM tabelle1 UNION SELECT spalte 1, spalte 2 FROM tabelle2</code>
<br \><br \>
<u>Einfaches Beispiel zur Veranschaulichung:</u> 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:<br \>
<code>SELECT nutzer_id FROM fragen UNION SELECT nutzer_id FROM antworten</code>
<br \><br \>
<u>Tipp:</u>  Sollten die Anzahl Ihrer Spalten nicht übereinstimmen, können Sie sich mit NULL (oder einem beliebigen anderen Wert) behelfen:
<code>SELECT spalte1, spalte2, spalte3 FROM tabelle1 UNION SELECT spalte 1, spalte 2, NULL FROM tabelle2</code>
<br \><br \><br \>
'''Zurück zu [[Erlernen einer Datenbanksprache: Structured Query Language (SQL) I]]''' ⇐⇒ '''Weiter zu [[Datenbank-Praxis für Fortgeschrittene]]'''
== Anmerkungen ==
<references group="A" />