Funktionen
Im SQL-Standard werden verschiedene Funktionen festgelegt, die in jedem SQL-Dialekt vorkommen. In aller Regel ergänzt jedes DBMS diese Funktionen durch weitere eigene.
- Skalarfunktionen verarbeiten Werte oder Ausdrücke aus einzelnen Zahlen, Zeichenketten oder Datums- und Zeitwerten. Sofern die Werte aus einer Spalte geholt werden, handelt es sich immer um einen Wert aus einer einzelnen Zeile.
- Spaltenfunktionen verarbeiten alle Werte aus einer Spalte.
- Ergänzend kann man für beide Varianten auch benutzerdefinierte Funktionen erstellen; dies wird unter Programmierung angesprochen.
Dieses Kapitel enthält als Grundlage nur die wichtigsten Skalarfunktionen. Unter Funktionen (2) gibt es viele Ergänzungen.
Allgemeine Hinweise
[Bearbeiten]Die Funktionen können überall dort verwendet werden, wo ein SQL-Ausdruck möglich ist. Wichtig ist, dass das Ergebnis der Funktion zu dem Datentyp passt, der an der betreffenden Stelle erwartet wird. Auch wenn der SQL-Standard nur einige wenige Funktionen vorschreibt, können eine ganze Reihe von Funktionen als Standard angesehen werden, weil sie immer (oder fast immer) vorhanden sind. An vielen Stellen ist aber auf Unterschiede im Namen oder in der Art des Aufrufs hinzuweisen.
Firebird kennt ab Version 2.1 viele Funktionen, die vorher als benutzerdefinierte Funktionen (user defined functions, UDF) erstellt werden mussten.
Schreibweise für Funktionen: Bei Funktionen müssen die Parameter (auch „Argumente“ genannt) immer in Klammern gesetzt werden. Diese Klammern sind auch bei einer Funktion mit konstantem Wert wie PI erforderlich. Eine Ausnahme sind lediglich die Systemfunktionen CURRENT_DATE u. ä.
Schreibweise der Beispiele: Aus Platzgründen werden Beispiele meistens nur einfach in einen Rahmen gesetzt. Für Funktionen ohne Bezug auf Tabellen und Spalten genügt in der Regel ein einfacher SELECT-Befehl; in manchen Fällen muss eine Tabelle oder eine fiktive Quelle angegeben werden:
SELECT 2 * 3; /* Normalfall */ SELECT 2 * 3 FROM rdb$database; /* bei Firebird und Interbase */ SELECT 2 * 3 FROM dual; /* bei Oracle */ SELECT 2 * 3 FROM SYSIBM.SYSDUMMY1; /* bei IBM DB2 */
In diesem Kapitel und auch im zweiten Kapitel zu Funktionen werden diese Verfahren durch eine verkürzte Schreibweise zusammengefasst:
SELECT 2 * 3 [from fiktiv];
Das ist so zu lesen: Die FROM-Klausel ist für Firebird, Interbase und Oracle notwendig und muss die jeweils benötigte Tabelle angeben; bei allen anderen DBMS muss sie entfallen.
Funktionen für Zahlen
[Bearbeiten]Bei allen numerischen Funktionen müssen Sie auf den genauen Typ achten. Es ist beispielsweise ein Unterschied, ob das Ergebnis einer Division zweier ganzer Zahlen als ganze Zahl oder als Dezimalzahl behandelt werden soll.
Operatoren
[Bearbeiten]Für Zahlen stehen die üblichen Operatoren zur Verfügung:
+ Addition - Subtraktion, Negation * Multiplikation / Division
Dafür gelten die üblichen mathematischen Regeln (Punkt vor Strich, Klammern zuerst). Bitte beachten Sie auch folgende Besonderheit:
- Bei der Division ganzer Zahlen ist auch das Ergebnis eine ganze Zahl; man nennt das „Integer-Division“:
SELECT 3 / 5 [from fiktiv]; /* Ergebnis: 0 */
- Wenn Sie das Ergebnis als Dezimalzahl haben wollen, müssen Sie (mindestens) eine der beiden Zahlen als Dezimalzahl vorgeben:
SELECT 3.0 / 5 [from fiktiv]; /* Ergebnis: 0.6 */
- Ausnahme: MySQL liefert auch bei "3/5" eine Dezimalzahl. Für die „Integer-Division“ gibt es die DIV-Funktion:
SELECT 3 DIV 5; /* Ergebnis: 0 */
Division durch 0 liefert zunächst eine Fehlermeldung "division by zero has occurred" und danach das Ergebnis NULL.
MOD – der Rest einer Division
[Bearbeiten]Die Modulo-Funktion MOD bestimmt den Rest bei einer Division ganzer Zahlen:
MOD( <dividend>, <divisor> ) /* allgemein */ <dividend> % <divisor> /* bei MS-SQL und MySQL */
Beispiele:
SELECT MOD(7, 3) [from fiktiv]; /* Ergebnis: 1 */ SELECT ID FROM Mitarbeiter WHERE MOD(ID, 10) = 0; /* listet IDs mit '0' am Ende '/
CEILING, FLOOR, ROUND, TRUNCATE – die nächste ganze Zahl
[Bearbeiten]Es gibt mehrere Möglichkeiten, zu einer Dezimalzahl die nächste ganze Zahl zu bestimmen.
CEILING oder CEIL liefert die nächstgrößere ganze Zahl, genauer: die kleinste Zahl, die größer oder gleich der gegebenen Zahl ist.
SELECT CEILING(7.3), CEILING(-7.3) [from fiktiv]; /* Ergebnis: 8, -7 */
FLOOR ist das Gegenstück dazu und liefert die nächstkleinere ganze Zahl, genauer: die größte Zahl, die kleiner oder gleich der gegebenen Zahl ist.
SELECT FLOOR(7.3), FLOOR(-7.3) [from fiktiv]; /* Ergebnis: 7, -8 */
TRUNCATE oder TRUNC schneidet den Dezimalanteil ab.
SELECT TRUNCATE(7.3),TRUNCATE(-7.3) [from fiktiv]; /* Ergebnis: 7, -7 */ SELECT TRUNCATE(Schadenshoehe) FROM Schadensfall; /* Euro-Werte ohne Cent */
ROUND liefert eine mathematische Rundung: ab 5 wird aufgerundet, darunter wird abgerundet.
ROUND( <Ausdruck> [ , <Genauigkeit> ] )
<Ausdruck> ist eine beliebige Zahl oder ein Ausdruck, der eine beliebige Zahl liefert.
- Wenn <Genauigkeit> nicht angegeben ist, wird 0 angenommen.
- Bei einer positiven Zahl für <Genauigkeit> wird auf entsprechend viele Dezimalstellen gerundet.
- Bei einer negativen Zahl für <Genauigkeit> wird links vom Dezimaltrenner auf entsprechend viele Nullen gerundet.
Beispiele:
SELECT ROUND(12.248,-2) [from fiktiv]; /* Ergebnis: 0,000 */ SELECT ROUND(12.248,-1) [from fiktiv]; /* Ergebnis: 10,000 */ SELECT ROUND(12.248, 0) [from fiktiv]; /* Ergebnis: 12,000 */ SELECT ROUND(12.248, 1) [from fiktiv]; /* Ergebnis: 12,200 */ SELECT ROUND(12.248, 2) [from fiktiv]; /* Ergebnis: 12,250 */ SELECT ROUND(12.248, 3) [from fiktiv]; /* Ergebnis: 12,248 */ SELECT ROUND(12.25, 1) [from fiktiv]; /* Ergebnis: 12,300 */ SELECT ROUND(Schadenshoehe) FROM Schadensfall; /* Euro-Werte gerundet */
Funktionen für Zeichenketten
[Bearbeiten]Zur Bearbeitung und Prüfung von Zeichenketten (Strings) werden viele Funktionen angeboten.
Verknüpfen von Strings
[Bearbeiten]Als Operatoren, um mehrere Zeichenketten zu verbinden, stehen zur Verfügung:
|| als SQL-Standard + für MS-SQL oder MySQL CONCAT für MySQL oder Oracle
Der senkrechte Strich wird als „Verkettungszeichen“ bezeichnet und oft auch „Pipe“-Zeichen genannt. Es wird auf der deutschen PC-Tastatur unter Windows durch die Tastenkombination Alt Gr + < > erzeugt.
Ein Beispiel in diesen Varianten:
SELECT Name || ', ' || Vorname from Mitarbeiter; SELECT Name + ', ' + Vorname from Mitarbeiter; SELECT CONCAT(Name, ', ', Vorname) from Mitarbeiter;
Alle diese Varianten liefern das gleiche Ergebnis: Für jeden Datensatz der Tabelle Mitarbeiter werden Name und Vorname verbunden und dazwischen ein weiterer String gesetzt, bestehend aus Komma und einem Leerzeichen.
Länge von Strings
[Bearbeiten]Um die Länge einer Zeichenkette zu erfahren, gibt es die folgenden Funktionen:
CHARACTER_LENGTH( <string> ) SQL-Standard CHAR_LENGTH( <string> ) SQL-Standard Kurzfassung LEN( <string> ) nur für MS-SQL
Beispiele:
SELECT CHAR_LENGTH('Hello World') [from fiktiv]; /* Ergebnis: 11 */
SELECT CHAR_LENGTH(’’) [from fiktiv]; /* Ergebnis: 0 */
SELECT CHAR_LENGTH( NULL ) [from fiktiv]; /* Ergebnis: <null> */
SELECT Name FROM Mitarbeiter ORDER BY CHAR_LENGTH(Name) DESC;
/* liefert die Namen der Mitarbeiter, absteigend sortiert nach Länge */
UPPER, LOWER – Groß- und Kleinbuchstaben
[Bearbeiten]UPPER konvertiert den gegebenen String zu Großbuchstaben; LOWER gibt einen String zurück, der nur aus Kleinbuchstaben besteht.
SELECT UPPER('Abc Äöü Xyzß ÀÉÇ àéç') [from fiktiv]; /* Ergebnis: 'ABC ÄÖÜ XYZß ÀÉÇ ÀÉÇ' */
SELECT LOWER('Abc Äöü Xyzß ÀÉÇ àéç') [from fiktiv]; /* Ergebnis: 'abc äöü xyzß àéç àéç' */
SELECT UPPER(Kuerzel), Bezeichnung FROM Abteilung; /* Kurzbezeichnungen in Großbuchstaben */
Ob die Konvertierung bei Umlauten richtig funktioniert, hängt vom verwendeten Zeichensatz ab.
SUBSTRING – Teile von Zeichenketten
[Bearbeiten]SUBSTRING ist der SQL-Standard, um aus einem String einen Teil herauszuholen:
SUBSTRING( <text> FROM <start> FOR <anzahl> ) /* SQL-Standard */ SUBSTRING( <text> , <start> , <anzahl> ) /* MS-SQL, MySQL, Oracle */
Diese Funktion heißt unter Oracle SUBSTR und kann auch bei MySQL so bezeichnet werden.
Der Ausgangstext wird von Position 1 an gezählt. Der Teilstring beginnt an der hinter FROM genannten Position und übernimmt so viele Zeichen wie hinter FOR angegeben ist:
SELECT SUBSTRING('Abc Def Ghi' FROM 6 FOR 4) [from fiktiv]; /* Ergebnis: 'ef G' */
SELECT CONCAT(Name, ', ', SUBSTRING(Vorname FROM 1 FOR 1), '.') from Mitarbeiter;
/* liefert den Namen und vom Vornamen den Anfangsbuchstaben */
Wenn der <anzahl>-Parameter fehlt, wird alles bis zum Ende von <text> übernommen:
SELECT SUBSTRING('Abc Def Ghi' FROM 6) [from fiktiv]; /* Ergebnis: 'ef Ghi' */
Wenn der <anzahl>-Parameter 0 lautet, werden 0 Zeichen übernommen, man erhält also einen leeren String.
MySQL bietet noch eine Reihe weiterer Varianten.
Hinweis: Nach SQL-Standard liefert das Ergebnis von SUBSTRING seltsamerweise einen Text von gleicher Länge wie der ursprüngliche Text; die jetzt zwangsläufig folgenden Leerzeichen müssen ggf. mit der TRIM-Funktion (im zweiten Kapitel über Funktionen) entfernt werden.
Funktionen für Datums- und Zeitwerte
[Bearbeiten]Bei den Datums- und Zeitfunktionen gilt das gleiche wie für Datum und Zeit als Datentyp: Jeder SQL-Dialekt hat sich seinen eigenen „Standard“ ausgedacht. Wir beschränken uns deshalb auf die wichtigsten Funktionen, die es so ähnlich „immer“ gibt, und verweisen auf die DBMS-Dokumentationen.
Vor allem MySQL bietet viele zusätzliche Funktionen an. Teilweise sind es nur verkürzte und spezialisierte Schreibweisen der Standardfunktionen, teilweise liefern sie zusätzliche Möglichkeiten.
Systemdatum und -uhrzeit
[Bearbeiten]Nach SQL-Standard werden aktuelle Uhrzeit und Datum abgefragt:
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP
In Klammern kann als <precision> die Anzahl der Dezimalstellen bei den Sekunden angegeben werden.
Beispiele:
SELECT CURRENT_TIMESTAMP [from fiktiv]; /* Ergebnis: '19.09.2009 13:47:49' */ UPDATE Versicherungsvertrag SET Aenderungsdatum = CURRENT_DATE WHERE irgendetwas;
Bei MS-SQL gibt es nur CURRENT_TIMESTAMP als Standardfunktion, dafür aber andere Funktionen mit höherer Genauigkeit.
Teile von Datum oder Uhrzeit bestimmen
[Bearbeiten]Für diesen Zweck gibt es vor allem EXTRACT als Standardfunktion:
EXTRACT ( <part> FROM <value> )
<value> ist der Wert des betreffenden Datums und/oder der Uhrzeit, die aufgeteilt werden soll. Als <part> wird angegeben, welcher Teil des Datums gewünscht wird:
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
Bei einem DATE-Feld ohne Uhrzeit sind HOUR usw. natürlich unzulässig, bei einem TIME-Feld nur mit Uhrzeit die Bestandteile YEAR usw. Beispiele:
SELECT ID, Datum, EXTRACT(YEAR FROM Datum) AS Jahr, EXTRACT(MONTH FROM Datum) AS Monat FROM Schadensfall ORDER BY Jahr, Monat; SELECT 'Stunde = ' || EXTRACT(HOUR FROM CURRENT_TIME) [from fiktiv]; /* Ergebnis: 'Stunde = 14' */
Bei MS-SQL heißt diese Standardfunktion DATEPART; als <part> können viele weitere Varianten genutzt werden.
Sehr oft gibt es weitere Funktionen, die direkt einen Bestandteil abfragen, zum Beispiel:
YEAR( <value> ) liefert das Jahr MONTH( <value> ) liefert den Monat usw. MINUTE( <value> ) liefert die Minute usw. DAYOFWEEK( <value> ) gibt den Wochentag (als Zahl, 1 für Sonntag usw.) an
Wie gesagt: Lesen Sie in der DBMS-Dokumentation nach, was es sonst noch gibt.
Funktionen für logische und NULL-Werte
[Bearbeiten]Wenn man es genau nimmt, gehören dazu auch Prüfungen wie „Ist ein Wert NULL?“. Der SQL-Standard hat dazu und zu anderen Prüfungen verschiedene spezielle Ausdrücke vorgesehen. Diese gehören vor allem zur WHERE-Klausel des SELECT-Befehls:
= < > usw. Größenvergleich zweier Werte BETWEEN AND Werte zwischen zwei Grenzen LIKE Ähnlichkeiten (1) CONTAINS u.a. Ähnlichkeiten (2) IS NULL null-Werte prüfen IN genauer Vergleich mit einer Liste EXISTS schneller Vergleich mit einer Liste
Alle diese Ausdrücke liefern einen der logischen Werte TRUE, FALSE – also WAHR oder FALSCH – und ggf. NULL – also <unbekannt> – zurück und können als boolescher Wert weiterverarbeitet oder ausgewertet werden.
Operatoren
[Bearbeiten]Zur Verknüpfung logischer Werte gibt es die „booleschen Operatoren“ NOT, AND, OR (nur bei MySQL auch XOR):
NOT als Negation AND als Konjunktion OR als Adjunktion XOR als Kontravalenz
Auch diese Operatoren werden bei der WHERE-Klausel behandelt.
Zur Verknüpfung von NULL-Werten gibt es vielfältige Regeln, je nach dem Zusammenhang, in dem das von Bedeutung ist. Man kann sich aber folgende Regel merken:
- Wenn ein Ausgangswert NULL ist, also <unbekannt>, und dieser „Wert“ mit etwas verknüpft wird (z. B. mit einer Zahl addiert wird), kann das Ergebnis nur <unbekannt> sein, also NULL lauten.
Konvertierungen
[Bearbeiten]In der EDV – also auch bei SQL-Datenbanken – ist der verwendete Datentyp immer von Bedeutung. Mit Zahlen kann gerechnet werden, mit Zeichenketten nicht. Größenvergleiche von Zahlen gelten immer und überall; bei Zeichenketten hängt die Reihenfolge auch von der Sprache ab. Ein Datum wird in Deutschland durch '11.09.2001' beschrieben, in England durch '11/09/2001' und in den USA durch '09/11/2001'. Wenn wir etwas aufschreiben (z. B. einen SQL-Befehl), dann benutzen wir zwangsläufig immer Zeichen bzw. Zeichenketten, auch wenn wir Zahlen oder ein Datum meinen.
In vielen Fällen „versteht“ das DBMS, was wir mit einer solchen Schreibweise meinen; dann werden durch „implizite Konvertierung“ die Datentypen automatisch ineinander übertragen. In anderen Fällen muss der Anwender dem DBMS durch eine Konvertierungsfunktion erläutern, was wie zu verstehen ist.
Implizite Konvertierung
[Bearbeiten]Datentypen, die problemlos vergleichbar sind, werden „automatisch“ ineinander übergeführt.
- Die Zahl 17 kann je nach Situation ein INTEGER, ein SMALLINT, ein BIGINT, aber auch NUMERIC oder FLOAT sein.
- Die Zahl 17 kann in einem SELECT-Befehl auch als String '17' erkannt und verarbeitet werden.
SELECT ID, Name, Vorname FROM Mitarbeiter WHERE ID = '17';
- Je nach DBMS kann ein String wie '11.09.2001' als Datum erkannt und verarbeitet werden. Vielleicht verlangt es aber auch eine andere Schreibweise wie '11/09/2001'. Die Schreibweise '2009-09-11' nach ISO 8601 sollte dagegen immer richtig verstanden werden (aber auch da gibt es Abweichungen).
Es gibt bereits durch den SQL-Standard ausführliche Regeln, welche Typen immer, unter bestimmten Umständen oder niemals ineinander übergeführt werden können. Jedes DBMS ergänzt diese allgemeinen Regeln durch eigene.
Wenn ein solcher Befehl ausgeführt wird, dürfte es niemals Missverständnisse geben, sondern er wird „mit an Sicherheit grenzender Wahrscheinlichkeit“ korrekt erledigt. Wenn ein Wert nicht eindeutig ist, wird das DBMS eher zu früh als zu spät mit einer Fehlermeldung wie "conversion error from string ..." reagieren. Dann ist es Zeit für eine explizite Konvertierung, meistens durch CAST.
CAST
[Bearbeiten]Die CAST-Funktion ist der SQL-Standard für die Überführung eines Wertes von einem Datentyp in einen anderen.
CAST ( <expression> AS <type> )
Als <expression> ist etwas angegeben, was den „falschen“ Typ hat, nämlich ein Wert oder Ausdruck. Mit <type> wird der Datentyp angegeben, der an der betreffenden Stelle gewünscht oder benötigt wird. Das Ergebnis des CASTings (der Konvertierung) ist dann genau von diesem Typ.
Beispiele für Datum:
SELECT EXTRACT(DAY FROM '07.14.2009') [from fiktiv];
/* expression evaluation not supported */
SELECT EXTRACT(DAY FROM CAST('07.14.2009' as date)) [from fiktiv];
/* conversion error from string "07.14.2009" */
SELECT EXTRACT(DAY FROM CAST('14.07.2009' as date)) [from fiktiv];
/* Ergebnis: '14' */
SELECT Name, Vorname, CAST(Geburtsdatum AS CHAR(10)) FROM Versicherungsnehmer;
/* Ergebnis wird in der Form '1953-01-13' angezeigt */
Kürzere Zeichenketten können schnell verlängert werden, wenn es nötig ist:
SELECT ID, CAST(Kuerzel AS CHAR(20)) FROM Abteilung; -- 20 Zeichen Länge statt eigentlich 10 Zeichen
Das Verkürzen funktioniert nicht immer so einfach. Ob bei Überschreitung einer Maximallänge einfach abgeschnitten wird oder ob es zu einer Fehlermeldung "string truncation" kommt, hängt vom DBMS ab; dann müssen Sie ggf. eine SUBSTRING-Variante benutzen.
-- vielleicht funktioniert es so: SELECT CAST(Name AS CHAR(15)) || Vorname from Versicherungsnehmer; -- aber sicherer klappt es so: SELECT SUBSTRING(Name FROM 1 FOR 15) || Vorname from Versicherungsnehmer; -- unter Berücksichtigung des Hinweises bei SUBSTRING: SELECT TRIM( SUBSTRING(Name FROM 1 FOR 15) ) || Vorname from Versicherungsnehmer;
Bitte lesen Sie in Ihrer SQL-Dokumentation nach, zwischen welchen Datentypen implizite Konvertierung möglich ist und wie die explizite Konvertierung mit CAST ausgeführt wird.
CONVERT
[Bearbeiten]Nach dem SQL-Standard ist CONVERT vorgesehen zum Konvertieren von Zeichenketten in verschiedenen Zeichensätzen:
CONVERT ( <text> USING <transcoding name> ) SQL-Standard CONVERT ( <text>, <transcoding name> ) alternative Schreibweise
Firebird kennt diese Funktion überhaupt nicht. MS-SQL benutzt eine andere Syntax und bietet vor allem für Datums- und Zeitformate viele weitere Möglichkeiten:
CONVERT ( <type>, <text> [ , <style> ] )
Wegen dieser starken Abweichungen verzichten wir auf weitere Erläuterungen und verweisen auf die jeweilige Dokumentation.
Datum und Zeit
[Bearbeiten]Vor allem für die Konvertierung mit Datums- und Zeitangaben bieten die verschiedenen DBMS Erweiterungen. Beispiele:
- MS-SQL hat die Syntax von CONVERT für diesen Zweck erweitert.
- MySQL ermöglicht die Konvertierung mit STR_TO_DATE und DATE_FORMAT.
- Oracle kennt eine Reihe von Funktionen wie TO_DATE usw..
- Wo es so etwas nicht gibt, kann man Day/Month/Year extrahieren, per CAST konvertieren und dann mit || neu zusammensetzen.
Noch ein Grund für das Studium der Dokumentation...
Spaltenfunktionen
[Bearbeiten]Die Spaltenfunktionen werden auch als Aggregatfunktionen bezeichnet, weil sie eine Menge von Werten – nämlich aus allen Zeilen einer bestimmten Spalte – zusammenfassen und einen gemeinsamen Wert bestimmen. In der Regel wird dazu eine Spalte aus einer der beteiligten Tabellen verwendet; es kann aber auch ein sonstiger gültiger SQL-Ausdruck sein, der als Ergebnis einen einzelnen Wert liefert. Das Ergebnis der Funktion ist dann ein Wert, der aus allen passenden Zeilen der Abfrage berechnet wird.
Bei Abfragen kann das Ergebnis einer Spaltenfunktion auch nach den Werten einer oder mehrerer Spalten oder Berechnungen gruppiert werden. Die Aggregatfunktionen liefern dann für jede Gruppe ein Teilergebnis. Näheres siehe unter Gruppierungen.
COUNT – Anzahl
[Bearbeiten]Die Funktion COUNT zählt alle Zeilen, die einen eindeutigen Wert enthalten, also nicht NULL sind. Sie kann auf alle Datentypen angewendet werden, da für jeden Datentyp NULL definiert ist. Beispiel:
SELECT COUNT(Farbe) AS Anzahl_Farbe FROM Fahrzeug;
Die Spalte Farbe ist als VARCHAR(30), also als Text variabler Länge, definiert und optional. Hier werden also alle Zeilen gezählt, die in dieser Spalte einen Eintrag haben. Dasselbe funktioniert auch mit einer Spalte, die numerisch ist:
SELECT COUNT(Schadenshoehe) AS Anzahl_Schadenshoehe FROM Schadensfall;
Hier ist die Spalte numerisch und optional. Die Zahl 0 ist bekanntlich nicht NULL. Wenn in der Spalte eine 0 steht, wird sie mitgezählt.
Ein Spezialfall ist der Asterisk '*' als Parameter. Dies bezieht sich dann nicht auf eine einzelne Spalte, sondern auf eine ganze Zeile. So wird also die Anzahl der Zeilen in der Tabelle gezählt:
SELECT COUNT(*) AS Anzahl_Zeilen FROM Schadensfall;
Die Funktion COUNT liefert niemals NULL zurück, sondern immer eine Zahl; wenn alle Werte in einer Spalte NULL sind, ist das Ergebnis die Zahl 0 (es gibt 0 Zeilen mit einem Wert ungleich NULL in dieser Spalte).
SUM – Summe
[Bearbeiten]Die Funktion SUM kann (natürlich) nur auf numerische Datentypen angewendet werden. Im Gegensatz zu COUNT liefert SUM nur dann einen Wert zurück, wenn wenigstens ein Eingabewert nicht NULL ist. Als Parameter kann nicht nur eine einzelne numerische Spalte, sondern auch eine Berechnung übergeben werden, die als Ergebnis eine einzelne Zahl liefert. Ein Beispiel für eine einzelne numerische Spalte ist:
SELECT SUM(Schadenshoehe) AS Summe_Schadenshoehe FROM Schadensfall;
Als Ergebnis werden alle Werte in der Spalte Schadenshoehe aufsummiert. Als Parameter kann aber auch eine Berechnung übergeben werden.
Hier werden Euro-Beträge aus Schadenshoehe zuerst in US-Dollar nach einem Tageskurs umgerechnet und danach aufsummiert.
SELECT SUM(Schadenshoehe * 1.5068) AS Summe_Schadenshoehe_Dollar FROM Schadensfall;
Eine Besonderheit ist das Berechnen von Vergleichen. Ein Vergleich wird als WAHR oder FALSCH ausgewertet. Sofern das DBMS (wie bei MySQL oder Access) das Ergebnis als Zahl benutzt, ist das Ergebnis eines Vergleichs daher 1 oder 0 (bei Access –1 oder 0). Um alle Fälle zu zählen, deren Schadenshöhe größer als 1000 ist, müsste der Befehl so aussehen:
SELECT SUM(Schadenshoehe > 1000) AS Anzahl_Schadenshoehe_gt_1000 FROM Schadensfall;
Dabei werden nicht etwa die Schäden aufsummiert, sondern nur das Ergebnis des Vergleichs, also 0 oder 1, im Grunde also gezählt. Die Funktion COUNT kann hier nicht genommen werden, da sie sowohl die 1 als auch die 0 zählen würde.
Einige DBMS (z. B. DB2, Oracle) haben eine strengere Typenkontrolle; Firebird nimmt eine Zwischenstellung ein. Dabei haben Vergleichsausdrücke grundsätzlich ein boolesches Ergebnis, das nicht summiert werden kann. Dann kann man sich mit der CASE-Funktion behelfen, die dem Wahrweitswert TRUE eine 1 zuordnet und dem Wert FALSE eine 0:
SELECT SUM(CASE WHEN Schadenshoehe > 1000 THEN 1
ELSE 0
END) AS Anzahl_Schadenshoehe_gt_1000
FROM Schadensfall;
MAX, MIN – Maximum, Minimum
[Bearbeiten]Diese Funktionen können auf jeden Datentyp angewendet werden, für den ein Vergleich ein gültiges Ergebnis liefert. Dies gilt für numerische Werte, Datumswerte und Textwerte, nicht aber für z. B. BLOBs (binary large objects). Bei Textwerten ist zu bedenken, dass die Sortierreihenfolge je nach verwendetem Betriebssystem, DBMS und Zeichensatzeinstellungen der Tabelle oder Spalte unterschiedlich ist, die Funktion demnach auch unterschiedliche Ergebnisse liefern kann.
Suche den kleinsten, von NULL verschiedenen Schadensfall.
SELECT MIN(Schadenshoehe) AS Minimum_Schadenshoehe FROM Schadensfall;
Kommen nur NULL-Werte vor, wird NULL zurückgegeben. Gibt es mehrere Zeilen, die den kleinsten Wert haben, wird trotzdem nur ein Wert zurückgegeben. Welche Zeile diesen Wert liefert, ist nicht definiert.
Für MAX gilt Entsprechendes wie für MIN.
AVG – Mittelwert
[Bearbeiten]AVG (average = Durchschnitt) kann nur auf numerische Werte angewendet werden. Das für SUM Gesagte gilt analog auch für AVG. Um die mittlere Schadenshöhe zu berechnen, schreibt man:
SELECT AVG(Schadenshoehe) AS Mittlere_Schadenshoehe FROM Schadensfall;
NULL-Werte fließen dabei nicht in die Berechnung ein, Nullen aber sehr wohl.
STDDEV – Standardabweichung
[Bearbeiten]Die Standardabweichung STDDEV oder STDEV kann auch nur für numerische Werte berechnet werden. NULL-Werte fließen nicht mit in die Berechnung ein, Nullen schon. Wie bei SUM können auch Berechnungen als Werte genommen werden. Die Standardabweichung der Schadensfälle wird so berechnet:
SELECT STDDEV(Schadenshoehe) AS StdAbw_Schadenshoehe FROM Schadensfall;
Zusammenfassung
[Bearbeiten]In diesem Kapitel lernten wir die wichtigsten „eingebauten“ Funktionen kennen:
- Für Zahlen gibt es vor allem die Operatoren, dazu die modulo-Funktionen und Möglichkeiten für Rundungen.
- Für Zeichenketten gibt es vor allem das Verknüpfen und Aufteilen, dazu die Längenbestimmung und die Umwandlung in Groß- und Kleinbuchstaben.
- Für Datums- und Zeitwerte gibt es neben Systemfunktionen die Verwendung einzelner Teile.
- Für logische und NULL-Werte gibt es vor allem Vergleiche und Kombinationen durch Operatoren.
- Konvertierungen – implizite, CAST, CONVERT – dienen dazu, dass ein Wert des einen Datentyps anstelle eines anderen Typs verwendet werden kann.
Mit Spaltenfunktionen werden alle Werte einer Spalte gemeinsam ausgewertet.
Übungen
[Bearbeiten]
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Feststellungen sind richtig, welche sind falsch?
- Eine Skalarfunktion bestimmt aus allen Werten eines Feldes einen gemeinsamen Wert.
- Eine Spaltenfunktion bestimmt aus allen Werten eines Feldes einen gemeinsamen Wert.
- Eine Skalarfunktion kann keine Werte aus Spalten einer Tabelle verarbeiten.
- Eine benutzerdefinierte Funktion kann als Skalarfunktion, aber nicht als Spaltenfunktion dienen.
- Wenn einer Funktion keine Argumente übergeben werden, kann auf die Klammern hinter dem Funktionsnamen verzichtet werden.
- Wenn eine Funktion für einen SQL-Ausdruck benutzt wird, muss das Ergebnis der Funktion vom Datentyp her mit dem übereinstimmen, der an der betreffenden Stelle erwartet wird.
| Übung 2 | Definitionen | Zur Lösung |
Welche der folgenden Funktionen sind Spaltenfunktionen? Welche sind Skalarfunktionen, welche davon sind Konvertierungen?
- Bestimme den Rest bei einer Division ganzer Zahlen.
- Bestimme die Länge des Namens des Mitarbeiters mit der ID 13.
- Bestimme die maximale Länge aller Mitarbeiter-Namen.
- Bestimme die Gesamtlänge aller Mitarbeiter-Namen.
- Verwandle eine Zeichenkette, die nur Ziffern enthält, in eine ganze Zahl.
- Verwandle eine Zeichenkette, die keine Ziffern enthält, in einen String, der nur Großbuchstaben enthält.
- Bestimme das aktuelle Datum.
| Übung 3 | Funktionen mit Zahlen | Zur Lösung |
Benutzen Sie für die folgenden Berechnungen die Spalte Schadenshoehe der Tabelle Schadensfall. Welche Datensätze benutzt werden, also der Inhalt der WHERE-Klausel, soll uns dabei nicht interessieren. Auch geht es nur um die Formeln, nicht um einen SELECT-Befehl.
- Berechnen Sie (ohne AVG-Funktion) die durchschnittliche Schadenshöhe.
- Bestimmen Sie den prozentualen Anteil eines bestimmten Schadensfalls an der gesamten Schadenshöhe.
| Übung 4 | Funktionen mit Zeichenketten | Zur Lösung |
Schreiben Sie Name, Vorname und Abteilung der Mitarbeiter in tabellarischer Form (nehmen wir an, dass das Kuerzel der Abteilung in der Tabelle Mitarbeiter stünde); benutzen Sie dazu nacheinander die folgenden Teilaufgaben:
- Bringen Sie die Namen auf eine einheitliche Länge von 20 Zeichen.
- Bringen Sie die Namen auf eine einheitliche Länge von 10 Zeichen.
- Bringen Sie die Vornamen ebenso auf eine Länge von 10 Zeichen.
- Setzen Sie diese Teilergebnisse zusammen und fügen Sie dazwischen je zwei Leerzeichen ein.
| Übung 5 | Funktionen mit Datum und Zeit | Zur Lösung |
Gegeben ist ein Timestamp-Wert mit dem Spaltennamen Zeitstempel und dem Inhalt "16. Dezember 2009 um 19:53 Uhr". Zeigen Sie diesen Wert als Zeichenkette im Format "12/2009; 16. Tag; 7 Minuten vor 20 Uhr" an. (Für die String-Verknüpfung benutzen wir jetzt das Plus-Zeichen. Die Leerzeichen zwischen den Bestandteilen können Sie ignorieren. Auch muss es keine allgemeingültige Lösung sein, die alle Eventualitäten beachtet.)
| Übung 6 | Funktionen mit Datum und Zeit | Zur Lösung |
Gegeben ist eine Zeichenkette datum mit dem Inhalt "16122009". Sorgen Sie dafür, dass das Datum für jedes DBMS gültig ist. Zusatzfrage: Muss dafür CAST verwendet werden und warum bzw. warum nicht?
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Richtig sind 2 und 6, falsch sind 1, 3, 4, 5.
| Lösung zu Übung 2 | Definitionen | Zur Übung |
Spaltenfunktionen sind 3 und 4; beide benutzen das Ergebnis von Skalarfunktionen. Alle anderen sind Skalarfunktionen, wobei 5 eine Konvertierung ist und 7 eine Systemfunktion, die ohne Klammern geschrieben wird.
| Lösung zu Übung 3 | Funktionen mit Zahlen | Zur Übung |
- SUM(Schadenshoehe) / COUNT(Schadenshoehe)
- ROUND( Schadenshoehe * 100 / SUM(Schadenshoehe) )
| Lösung zu Übung 4 | Funktionen mit Zeichenketten | Zur Übung |
1. CAST(Name AS CHAR(20)) 2. SUBSTRING( CAST(Name AS CHAR(20)) FROM 1 FOR 10 ) 3. SUBSTRING( CAST(Vorname AS CHAR(20)) FROM 1 FOR 10 ) 4. SUBSTRING( CAST(Name AS CHAR(20)) FROM 1 FOR 10 ) || ' ' || SUBSTRING( CAST(Vorname AS CHAR(20)) FROM 1 FOR 10 ) || ' ' || Kuerzel
| Lösung zu Übung 5 | Funktionen mit Datum und Zeit | Zur Übung |
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) + '/' + EXTRACT(YEAR FROM CURRENT_TIMESTAMP) + ';' + EXTRACT(DAY FROM CURRENT_TIMESTAMP) + '.Tag; ' + CAST((60 - EXTRACT(MINUTE FROM CURRENT_TIMESTAMP)) AS Varchar(2)) + ' Minuten vor ' + CAST( (EXTRACT(HOUR FROM CURRENT_TIMESTAMP) + 1) AS Varchar(2)) + ' Uhr'
| Lösung zu Übung 6 | Funktionen mit Datum und Zeit | Zur Übung |
SUBSTRING(datum FROM 5 FOR 4) + '-' + SUBSTRING(datum FROM 3 FOR 2) + '-' + SUBSTRING(datum FROM 1 FOR 2)
Auf CAST kann (fast immer) verzichtet werden, weil mit dieser Substring-Verwendung die Standardschreibweise '2009-12-16' nach ISO 8601 erreicht wird.
Siehe auch
[Bearbeiten]Einige Hinweise sind in den folgenden Kapiteln zu finden:
- SQL-Befehle beschreibt auch den Begriff „SQL-Ausdruck“.
- Datentypen
Weitere Erläuterungen stehen bei Wikipedia:
- Senkrechter Strich oder „Pipe“-Zeichen
- Boolesche Operatoren
- ISO 8601 zur Schreibweise von Datumsangaben