Sitzungsnavigation: keine Veranstaltung ausgewählt. (Veranstaltung wählen.)

Summerschool 2016: Projektion, Funktionen, Sortierung, Gruppierung, Join, Union (ITG/slu)

Bei der Formulierung von SQL-Statements ist die auf die korrekte Reihenfolge der einzelnen Klauseln zu achten:

0.0.0.1. Reihenfolge der Klauseln

select [Feldnamen]
from [Tabellen]
join [Tabellen]
where
group by
having
order by
limit
;

0.0.0.2. Projektion (Auswahl von Spalten)

select port, region from cports;
select region, port, port, region, region from cports;

0.0.0.3. Sortierung

select region, port, port from cports order by region;

0.0.0.4. Funktionen

MySQL verfügt über einer große Anzahl von z.T. sehr nützlichen Funktionen. Aus Sicht der Korpuslinguistik sind die Stringfunktionen (http://dev.mysql.com/doc/refman/5.7/en/string-functions.html), die Gruppierungsfunktionen (hier vor allem count(*) und group_concat()) sowie einige mathematische Funktionen (z.B. sum(), min(), max() oder avg()) von größerer Bedeutung. Die korrekte Syntax einer Funktion besteht grundsätzlich aus dem Namen der Funktion und zwei sich unmittelbar – ohne Leerzeichen! – anschließende runde Klammern, zwischen die die Parameter (auch: „Argumente“) geschrieben werden, die an die Funktion übergeben werden. Die Anzahl der Parameter ist funktionsspezifisch; manche Funktionen verlangen einen, andere zwei oder auch drei. Jede Funktion produziert bei ihrer Verwendung ein Ergebnis, das auch als „Rückgabewert“ bezeichnet wird.

select 
 reverse(port), 
 port, 
 'hier steht irgendwas', 
 concat(port,' xyz ',region),
 replace(port,'a','ä')
from cports
;
select left(port,2) from cports;
select length(port) from cports;
SELECT length('ü'), char_length('ü');

Funktionen können geschachtelt werden. Dabei kann das Ergebnis einer Funktion als Argument für eine weitere Funktion dienen. Im folgenden Beispiel werden die Tokens der Tabelle gtokens, gruppiert nach Nummer des Textes und Nummer des Satzes, zunächst mit der Funktion group_concat() gruppenweise konkateniert, dann mit der Funktion reverse() invertiert und schließlich mit der Funktion replace() nacheinander ‚ä‘ durch ‚ae‘, ‚ö‘ durch ‚oe‘ und ‚ü‘ durch ‚ue‘ ersetzt. Um den Überblick über solche Schachtelungen zu behalten, empfiehlt es sich, die Syntax durch Absätze und Einrückungen zu paraphrasieren:

SELECT 
 replace(
  replace(
   replace(
    reverse(
     group_concat(
      token order by id separator ' '
     )
    ),'ä','ae'
   ),'ö','oe'
  ),'ü','ue'
 ) as beispiel 
FROM `gtokens`
group by textnr, satznr
;

0.0.0.5. Korrelatsnamen

select
 concat('<region>',region,'</region>') as xmlregion,
 port as Hafen,
 length(port) `Stringlänge in Bytes`,
 char_length(port) `Stringlänge in Zeichen`
from cports
order by Hafen desc -- desc: absteigend; asc [Standard]: aufsteigend
;

0.0.0.6. Gruppierung

select * from cports group by region;
select count(*) from cports;
select count(*), region from cports group by region;
select count(*), region, port from cports group by region;
select 
 count(*), 
 region, 
 group_concat(port order by port desc separator '\n') as Häfen
from cports 
group by region
;

ACHTUNG: Die Gruppenkonkatenierung ist standardmäßig auf 1024 Zeichen beschränkt! Zur Umgehung muss vor der Abfrage folgendes Kommando gegeben werden:

SET SESSION group_concat_max_len = 1000000;

0.0.0.7. JOIN

select * 
from cbetreiber as a 
join cconnctions as b on (a.betreiber=b.betreiber)
;
select * from cbetreiber a 
join cconnections b using(betreiber)
;

0.0.0.8. Self JOIN

Eine Tabelle lässt sich auch mit sich selbst verbinden. Dazu muss sie einfach mit zwei unterschiedlichen Korrelatsnamen angesprochen werden. Dieses Verfahren ist in unserem Beispiel nötig, um die korsischen Häfen mit den nicht-korsischen zu kombinieren.

Erster Schritt: Einfacher self join – alle Häfen werden mit allen kombiniert:

select *
from cports a
join cports b
;

Zweiter Schritt: Join unter der Bedingung, dass die Häfen in Tabelle `a` auf Korsika liegen, die in Tabelle `b` nicht. Man beachte: auch in der on-Klausel sind die gleichen Operatoren verwendbar wie in der where-Klausel:

select *
from cports a
join cports b on (a.region like 'Corse' and b.region not like 'Corse')
;

Dritter Schritt: Kombination mit der Tabelle der Connections, wobei sich das Feld `aid` stets auf die Häfen auf Korsika bezieht:

select * 
from cports a 
join cports b on (a.id != b.id and a.region = 'Corse' and b.region != 'Corse') 
join cconnections c on (a.id=c.aid and b.id=c.bid)
;

0.0.0.9. Outer Joins

Sog. Outer Joins zeigen auch Datensätze an, die bei einem Join nur in einer der miteinander verbundenen Tabellen besitzt. Man unterscheidet den Left Join, der sämtlich Datensätze der linken Tabelle liefert, und den Right Join, der sämtliche Datensätze der rechten Tabelle anzeigt. Im gegebenen Beispiel bewirkt die Anbindung der Tabelle cconnections mit Left Join, dass auch Häfen angezeigt werden, zwischen denen *keine* Fährverbindung besteht. 

select * 
from cports a 
join cports b on (a.id != b.id and a.region = 'Corse' and b.region != 'Corse') 
left join cconnections c on (a.id=c.aid and b.id=c.bid)
;

Zur Selektion von Datensätzen, die in bestimmten Feldern einen sog. NULL-Wert aufweisen, muss der Operator „is null“ verwendet werden:

select * 
from cports a 
join cports b on (a.id != b.id and a.region = 'Corse' and b.region != 'Corse') 
left join cconnections c on (a.id=c.aid and b.id=c.bid) 
where c.aid is null 
;

0.0.0.10. Union

Die Ergebnisse getrennter Abfragen können dergestalt miteinander vereinigt werden, dass sie untereinander erscheinen. Voraussetzung ist, dass alle Teilabfragen eine identische Anzahl von Spalten enthält. Sofern die Teilabfragen identische Datensätze enthalten, werden Duplikate entfernt. Um die Duplikate zu bewahren, muss das Statement „union all“ verwendet werden:

select 'Häfen mit Fährverbindung:' as id, '', '','','','','','','',''

union all

select * 
from cports a 
join cports b on (a.id != b.id and a.region = 'Corse' and b.region != 'Corse') 
left join cconnections c on (a.id=c.aid and b.id=c.bid)
where c.aid is not null

union all

select 'Häfen ohne Fährverbindung:' as id, '', '','','','','','','',''

union all
select * 
from cports a 
join cports b on (a.id != b.id and a.region = 'Corse' and b.region != 'Corse') 
left join cconnections c on (a.id=c.aid and b.id=c.bid) 
where c.aid is null 
;

http://www.fileformat.info/ -> UTF-8, Bytes

Schreibe einen Kommentar