SQL-Handout

150.422
2
Datenbanken – SQL Handout 2
SS 2017
Stringfunktionen
• Einige nützliche Stringfunktionen • (auch auf numerische Werte anwendbar)
LENGTH(str )
LEFT(str, n)
RIGHT(str, n)
SUBSTRING(str, pos)
SUBSTRING(str, pos, n)
REPLACE(str, alt, neu)
INSTR(str, substr )
LOCATE(substr, str )
LOCATE(substr, str, pos)
CONCAT(str1, str2, . . . )
gibt die Länge (=Anzahl von Zeichen) von str zurück
gibt die ersten n Zeichen von str zurück
gibt die letzten n Zeichen von str zurück
gibt alle Zeichen von str ab Position pos zurück
gibt n Zeichen von str ab Position pos zurück
ersetzt in str alle Vorkommen von alt durch neu
gibt die Position des ersten Vorkommens von substr in str zurück;
gibt 0 zurück, wenn substr in str nicht vorkommt
wie INSTR(str, substr )
liefert die Position des ersten Vorkommens von substr in str ab Position pos
kettet die Strings str1, str2, . . . aneinander
B Beispiel. Gib die Tabelle Order Options mit einer zusätzlichen Spalte aus, die den Preis pro Einheit in US
Dollar (1 EURO = 1.061 USD) und einen entsprechenden Text anzeigt.
SELECT * , CONCAT ( Price_per_Unit *1.061 , ’ US Dollar ’ ,
SUBSTRING ( Price_per_Unit_Text ,5)) FROM Order_Options
1
3
IF und CASE
• IF und CASE • können dazu verwendet werden, den ausgegebenen Wert an eine oder mehrere Bedingungen
zu verknüpfen:
B IF(bedingung, wert1, wert2 ) gibt wert1 zurück, wenn bedingung wahr ist, ansonsten wert2.
B Mit CASE können mit WHEN verschiedene Werte für einen vorgegebenen Ausdruck abgefragt werden. Im
entsprechenden THEN-Teil wird dann der entsprechende Rückgabewert festgelegt. Im ELSE wird der Rückgabewert festelegt, wenn keiner der angegebenen Fälle eintrifft. CASE muss mit END beendet werden.
B Beispiel. Die ersten zwei Buchstaben der P No in der Tabelle Parts geben an, ob es sich um eine Dienstleistung (DL) oder ein Kaufteil (KT) handelt. Die folgende Abfrage enthält eine zusätzliche Spalte, die diese
Information nochmals explizit ausgibt.
SELECT * , IF ( LEFT ( P_No ,2)= ’ KT ’ , ’ Kaufteil ’ , ’ Dienstleistung ’ )
FROM Order_Options
Mit CASE würde die Abfrage wie folgt aussehen:
3
SELECT * , CASE LEFT ( P_No , 2 )
WHEN ’ KT ’ THEN ’ Kaufteil ’
WHEN ’ DL ’ THEN ’ Dienstleistung ’
ELSE ’ unbekannt ’
END AS Type_of_Part FROM Parts
4
Aggregatfunktionen
• Wichtige Aggregatfunktionen • sind MIN(x), MAX(x), AVG(x), und SUM(x). Ohne GROUP BY (s.u.)
berechnen sie Minimum, Maximum, Durchschnitt und Summe des Ausdrucks x über alle Zeilen. Bei der
Berechnung werden NULL-Werte immer ignoriert. Die Funktion COUNT(x) gibt entsprechend die Anzahl jener
Zeilen zurück, in denen der Ausdruck x nicht NULL ist. Mit COUNT(*) lässt sich entsprechend die Anzahl aller
Zeilen einer Tabelle zählen.
B Beispiele. Finde in Order Options den kleinsten Preis für Teil KT12AB321.
SELECT MIN ( Price_per_Unit ) FROM Order_Options
WHERE P_No = ’ KT12AB321 ’
Zeige den Gesamtwert aller bestellten Dienstleistungen in Orders.
SELECT SUM ( No_of_Units_ordered * Price_per_Unit ) FROM Orders
WHERE LEFT ( P_No ,2)= ’ DL ’
Wieviele Zeilen hat die Tabelle Orders?
SELECT COUNT (*) FROM Orders
• DISTINCT •
wird dazu verwendet, um nur unterschiedliche Zeilen/Werte anzuzeigen bzw. zu zählen.
B Beispiele. Zeige die verschiedenen Länder, die in der Tabelle Supplier vorkommen.
SELECT DISTINCT S_Country FROM Supplier
Zähle die verschiedenen Länder in der Tabelle Supplier.
SELECT COUNT ( DISTINCT S_Country ) FROM Supplier
• GROUP BY • Typischerweise möchte man Aggregatfunktionen nicht über die gesamte Tabelle sondern für
Zeilen mit einer bestimmten Eigenschaft berechnen. Mit GROUP BY werden alle Zeilen zusammengefasst, die in
den im GROUP BY angegebenen Spalten bzw. Ausdrücken dieselben Werte haben. Die allgemeine Form lautet:
SELECT spalte1, spalte2, . . . , aggregatfunktion(spalte) FROM tabelle
[WHERE bedingung ]
GROUP BY spalte1, spalte2, . . .
Auch wenn eine Abfrage in MySQL auch dann ausgeführt wird, wenn die Spalten im SELECT (außer der
Aggregatfunktion) nicht mit jenen im GROUP BY übereinstimmen, macht dies im allgemeinen keinen Sinn.
Regel: Die Spalten im SELECT (außer den Aggregatfunktionen) sind dieselben wie im GROUP BY!
B Beispiele. Zeige für jedes Teil in Order Options den kleinsten und den durchschnittlichen Preis.
SELECT P_No , MIN ( Price_per_Unit ) , AVG ( Price_per_Unit ) FROM Order_Options
GROUP BY P_No
Zeige für jeden Supplier und jedes Monat die Anzahl der getätigten Bestellungen.
SELECT S_No , RIGHT ( Date_of_Order , 7 ) AS Monat , COUNT (*) FROM Orders
GROUP BY S_No , Monat
Zeige für jeden Supplier und jedes Monat die Anzahl der bestellten Dienstleistungen.
3
SELECT S_No , RIGHT ( Date_of_Order , 7 ) AS Monat , COUNT (*) FROM Orders
WHERE LEFT ( P_No ,2) = ’ DL ’
GROUP BY S_No , Monat
Zeige für Dienstleistungs- und Kaufteile den Gesamtwert der entsprechenden getätigten Bestellungen.
2
SELECT IF ( LEFT ( P_No ,2)= ’ KT ’ , ’ Kaufteil ’ , ’ Dienstleistung ’ ) ,
SUM ( No_of_Units_ordered * Price_per_Unit ) FROM Orders
GROUP BY 1
NB: Im GROUP BY und im ORDER BY können sowohl Spaltennummern (bezogen auf die Reihenfolge im SELECT)
als auch im SELECT mit AS eingeführte Abkürzungen verwendet werden. Im WHERE funktioniert dies nicht.
• HAVING • wird dazu verwendet, um zusätzliche Bedingungen an Aggregatfunktion abzufragen. HAVING steht
nach dem GROUP BY, d.h. die allgemeine Form einer entsprechenden Abfrage sieht wie folgt aus:
SELECT spalte1, spalte2, . . . , aggregatfunktion(spalte) FROM tabelle
WHERE bedingung1
GROUP BY spalte1, spalte2, . . .
HAVING bedingung2
In der Abfrage werden zunächst alle Zeilen gefiltert, die bedingung1 erfüllen. Diese werden dann gruppiert,
inklusive der Auswertung der angegebenen Aggregatfunktion(en). Die Bedingung im HAVING wird abschließend
auf die aggregierte Ergebnistabelle angewandt. Entsprechend gilt:
Regel: Bedingungen an Aggregatfunktionen müssen immer mit HAVING gestellt werden (nie mit WHERE)!
B Beispiele. Zeige für Supplier und Monate mit mehr als einer Bestellung die Gesamtanzahl an Bestellungen.
2
SELECT S_No , RIGHT ( Date_of_Order ,7) AS Monat , COUNT (*) FROM Orders
GROUP BY S_No , Monat
HAVING COUNT (*) > 1
Zeige die Gesamtanzahl der Bestellungen für all jene Supplier und all jene Monate, in denen der entsprechende
Supplier im entsprechenden Monat mindestens eine Bestellung mit Gesamtwert größer als 1000 Euro bearbeitet
hat.
2
SELECT S_No , RIGHT ( Date_of_Order ,7) AS Monat , COUNT (*) FROM Orders
GROUP BY S_No , Monat
HAVING MAX ( No_of_Units_ordered * Price_per_Unit ) > 1000
Zeige die Gesamtanzahl der Bestellungen von Dienstleistungen für all jene Supplier und all jene Monate, in
denen der entsprechende Supplier im entsprechenden Monat mindestens eine Bestellung von Dienstleistungen
mit Gesamtwert größer als 1000 Euro bearbeitet hat.
2
SELECT S_No , RIGHT ( Date_of_Order ,7) AS Monat , COUNT (*) FROM Orders
WHERE LEFT ( P_No ,2) = ’ DL ’
GROUP BY S_No , Monat
HAVING MAX ( No_of_Units_ordered * Price_per_Unit ) > 1000
NB: Die Aggregatfunktion, deren Wert im HAVING geprüft wird, muss nicht unbedingt in der Ergebnistabelle
angezeigt werden.