SQL Grundlagen – Teil 3

Nachdem ich nun schon einen ersten Teil sowie auch schon einen zweiten Teil geschrieben habe, wird es mal Zeit einen weiteren Teil zu schreiben.
Nämlich ein paar Eigenheiten, die man aber immer wieder brauchen kann.

NULL Felder:
Beispielsweise die Prüfung von Checkbox (Boolean) Feldern isnull(a.wichtigerhaken,0)=0. Diese können unter Umständen gar nicht gefüllt sein, angehakt sein oder wieder abgehakt worden sein. Deshalb ist es sinnvoll den Zustand "nicht gefüllt" mit dem Zustand "nicht angehakt" gleichzusetzen. Das geschieht ganz einfach mit der Funktion isnull(). Im ersten Parameter wird das zu prüfende Feld eingetragen und im zweiten Parameter der Wert eingetragen, wenn es tatsächlich NULL also leer ist. isnull(a.wichtigerhaken,0) prüft also das Feld a.wichtigerhaken und setzt eine 0, wenn der Wert wirklich NULL ist. Und mit =0 wird geprüft ob das Feld dann 0 ist.
Würde man diese Prüfung mit isnull nicht machen, würde der Wert ein NULL zurückgeben und somit niemals auf =0 reagieren. Aber da NULL meistens mit 0 gleichzusetzen ist, muss man die NULL Felder mit 0 Werten deklarieren um die Prüfung korrekt durchführen zu können.

Datum beschränken:
Eine sehr einfache Lösung um die Antwortmenge auf einen bestimmten Zeitraum zu beschränken ist der Tag. Dazu macht man in der WHERE Klausel beispielsweise sowas hier um das Datum vor 3 Tagen und bis 14 Tage in der Zukunft zu haben. Dafür ist DATEADD() eine sehr gute Funktion:
and wa.termin >= DATEADD(DAY,-3,GETDATE())
and wa.TERMIN < DATEADD(DAY,14,GETDATE())

Das GETDATE() gibt einfach das aktuelle Datum zurück. Mit DATEADD(DAY,-3,GETDATE()) wird einer Tag ermittelt, der 3 Tage in ausgehend vom aktuellen Datum berechnet wird. DAY ist der Parameter, der besagt, dass der Tag geändert werden soll. Die -3 gibt an, dass 3 Tage zurückgerechnet werden soll und Getdate() gibt eben das aktuelle Datum vom System zurück. Dies ist also ein sehr einfacher Vergleichswert um zu sehen ob der wa.termin kleiner oder gleich das Datum 3 Tage in der Vergangenheit ist.
DATEADD(DAY,14,GETDATE()) macht das Gleiche analog wie oben. Nur 14 Tage in die Zukunft.

UNION:
Man kann auch mehrere Abfragen zusammenfassen. Dafür ist UNION das Mittel der Wahl. Beispielsweise, wenn man Daten für ein Chart benötigt, die sich auch immer auf den gleichen Zeitraum beziehen.
declare @limitAB int
set @limitAB = 100
SELECT wa.Termin,
'Belegt' as Art,
count(wa.nummer) as anzahl
From Warenausgang wa
Where wa.status = 9
Group by wa.Termin
UNION
SELECT wa.Termin,
'Frei' as Art,
case when count(wa.nummer) > @limitAB then 0 else @limitAB - count(wa.nummer) end as Anzahl
From Warenausgang wa
Where wa.status = 9
Group by wa.Termin
Order By wa.Termin, Art

Das obige Beispiel macht eine Abfrage und sucht erstmal die Anzahl der Warenausgänge pro Tag im angegebenem Zeitraum. Gleichzeitig wird auch eine Grenze von 100 definiert set @limitAB = 100. In der unteren SELECT Abfrage wird diese diese Grenze verwendet und die Anzahl der Warenausgänge abgezogen.

So habe ich am Ende eine Ergebnistabelle, welche mir 2 Werte liefert. Einmal die Anzahl der Warenausgänge als "Belegt" und noch die Restmenge als "Frei".
Würde ich nun in einem Programm ein Diagramm basteln, könnte ich so beide Werte in Abhängigkeit vom Termin verwenden.
UNION ist also ganz hilfreich um verschiedene Abfragen miteinander zu verbinden. Damit es funktioniert müssen die Abfragen aber Gemeinsamkeiten aufweisen. Über freie Felder wie in diesem Beispiel 'Frei' as Art kann man die Werte hinterher wieder unterscheiden.
Möchte man die Abfragen am Ende noch mit Order By sortieren ist es wichtig, diese Sortierung am Ende zu machen.

Und damit zurück ins Hauptstadtstudio zum Wetter!

SQL Grundlagen – Teil 2

Natürlich kann SQL noch viel mehr als nur die Standard Selects, wie ich schon im ersten Teil gezeigt habe. Doch es gibt auch noch GROUP BY und ORDER BY.
Wie die Namen schon sagen kann man mit GROUP BY Spalten gruppieren und mit ORDER BY dann auch noch gruppieren.
Mal angenommen wir haben folgende Abfrage:
SELECT ap.Artnr, ap.Bezeichnung, sum(isnull(ap.menge,0))
FROM Auftragposition ap
GROUP BY ap.Artnr, ap.Bezeichnung

Dann wird das SQL Server Management Studio anmerken, dass ein GROUP BY fehlen würde, wenn man es weglässt. Der Grund liegt hierbei, dass wir eine Aggregatfunktion verwenden. Nämlich die Funktion sum(WERT). Damit werden bestimmte Felder summiert.
sum(isnull(ap.menge,0)) besteht in diesem Fall sogar aus zwei Funktionen. Einmal die isnull(ap.menge,0) welche prüft ob das Feld ap.menge NULL also damit leer ist und ersetzt die Menge in diesem Fall durch eine 0. Die 0 ist immerhin ein Wert und kann addiert werden. Diese Funktion ist in die sum() eingebunden.
Die Abfrage wird mir also alle Auftragspositionsmengen gruppiert nach der Artikelnummer und der Bezeichnung ausgeben. Da die Artikelnummer und die Bezeichnung in den allermeisten Fällen immer gleich ist, wird also pro Artikelnummer eine Zeile mit der summierten Menge ausgegeben werden.

Nun kann es aber natürlich auch sein, dass die Ausgabe doch etwas unsortiert herauskommt. Beispielsweise die Artikel 42, danach die 92 und dann die 23. Diese Ausgabe lässt sich natürlich auch ordnen. Dafür setzen wir einfach noch den ORDER BY Befehl drunter und die Abfrage sieht wie folgt aus:
SELECT ap.Artnr, ap.Bezeichnung, sum(isnull(ap.menge,0))
FROM Auftragposition ap
GROUP BY ap.Artnr, ap.Bezeichnung
ORDER BY ap.Artnr

Somit haben wir nun eine eine Ausgabe mit dem summierten Mengen pro Artikelnummer und werden die Ausgabe nach der Position geordnet vorfinden. Mit den Wörtern desc bzw. asc hinter dem Feld in ORDER BY kann man noch bestimmen ob absteigend oder aufsteigend sortiert werden soll.

SQL Grundlagen - Teil 1

Da ich nie studiert habe, fehlen mir manchmal noch die Grundlagen um manchmal die Theorie dahinter zu verstehen. Ich hab allerdings vor vielen Jahren mal eine Ausbildung zum FISI (Fachinformatiker für Systemintegration) abgeschlossen. Da gab es auch mal Datenbanken mit den 3 Normalformen. Letztendlich um Primärschlüssel und Fremdschlüssel zu verwenden und die Tabellen konsistent zu halten und um Daten nicht mehrfach abzuspeichern. Manchmal ist die Normalisierung nervig, wenn man beispielsweise eine Tabelle Auftrag hat und dort nur ein Verweis auf die Adresse besteht. Um beispielsweise dann die Auftragskopfdaten und die Firmenbezeichnung anzuzeigen müssen Auftrag und Adresse verknüpft werden. Andererseits hat dies auch wieder den Vorteil, dass man später die neuen Firmendaten nur an einer Stelle (nämlich in der Adresse) ändern muss.

In meinen Beispielen beziehe ich mich nun ausschließlich auf den MS SQL Dialekt. Bei anderen Datenbanksystemen gibt es definitiv Unterschiede.

Bisher verband ich mit SQL immer nur SELECT * FROM ADRESSE. Damit gebe ich beispielsweise alle Datensätze der Tabelle mit dem Namen ADRESSE aus. Das kann für einen Überblick schon mal sinnvoll sein. Ist es allerdings eine sehr große Tabelle mit mehreren Tausend oder gar Millionen Datensätze ist das eher unpraktisch, da damit sehr viel Last erzeugt wird. Deshalb ist hier schon mal folgendes besser: SELECT TOP(100) * FROM ADRESSE Damit werden die obersten 100 Datensätze ausgegeben. So sieht man immer noch alle Spalten und kann sich ein Bild von der Tabelle machen ohne gleich eine enorme Last zu erzeugen.

Kennt man die Tabelle schon etwas genauer und möchte nur einen bestimmten Datensatz aufrufen kommt der WHERE Befehle ins Spiel. Beispielsweise um den Datensatz mit der Id 42 aufzurufen könnte ich folgenden Befehl abfeuern:
SELECT *
FROM ADRESSE
WHERE Id = 42

Trotzdem bekomme ich hiermit vermutlich wieder zu viele Informationen zurück. Ich habe mit dem WHERE Id = 42 zwar den Datensatz auf die Id 42 eingegrenzt, aber bekomme trotzdem weiterhin alle Spalten angezeigt. Möchte ich beispielsweise nur den Namen, die Straße, die PLZ und den Ort bekommen, könnte folgendes Beispiel sinnvoll sein.
SELECT Name, Straße, PLZ, Ort
FROM ADRESSE
WHERE Id = 42

Das ist dann quasi schon ziemlich der einfache Standard, wenn es nur um Spalten innerhalb einer Tabelle geht.

Möchte man aber nun Daten ausgeben, die in verschiedenen Tabellen sind, ist eine Verknüpfung möglich. Dabei verknüpft man immer mit einer Spalte, die in den beiden Tabellen vorhanden ist und nach Möglichkeit auch eindeutig. Dabei gibt es INNER JOIN, LEFT JOIN und RIGHT JOIN. Der INNER JOIN verknüpft die Datensätze, die in beiden Tabellen vorhanden sind. LEFT JOIN dagegen gibt alle Datensätze von der linken Tabelle aus und ergänzt mit den Datensätze, die in der rechten vorhanden sind. RIGHT JOIN macht es entsprechend umgekehrt. So, dass bei LEFT und RIGHT eine Tabelle führend ist und nicht nur die gemeinsamen Datensätze aus beiden Tabellen ausgegeben werden.

Nehmen wir beispielsweise an, wir möchten nun einen Auftrag und seine Positionen ausgeben. Dann wird rein logisch betrachtet der Auftrag mit seinen Kopfdaten und die Auftragspositionen mit den Positionen jeweils eigene Positionen sein. Sicherlich gibt es dazu aber in beiden Tabellen eindeutige Schlüsselspalten. Beispielsweise die Auftragsnummer. Da hier aller Wahrscheinlichkeit in beiden Tabellen immer mindestens ein Auftragskopf und mindestens eine Position vorhanden ist, wird ein INNER JOIN ausreichen. Ein LEFT JOIN wäre dann interessant, wenn man alle Aufträge haben will und dabei auch Aufträge OHNE Positionen erwartet.
SELECT a.Auftragsnr, a.Kdnr, ap.PosNr, ap.Artikelnr, ap.Menge, ap.Preis
FROM AUFTRAG a (NOLOCK)
INNER JOIN AUFTRAGPOSITION ap (NOLOCK) ON ap.Auftragsnr = a.Auftragsnr
WHERE a.KdNr = 23

Ich habe im obigen Beispiel jeder Tabelle auch noch einen Alias verpasst. Für Auftrag hier die a und für Auftragsposition ap. Der Grund dafür ist relativ einfach. Da wir nun mehrere Tabellen verknüpft haben, sind die einzelnen Spaltennamen nicht mehr eindeutig. Beispielsweise kommt Auftragsnr in beiden Tabellen vor. Um uns und dem SQL Server die Angabe der Tabellen leichter zu machen, verwende ich hier kleine Aliase. Mit a.Auftragsnr drücke ich also aus, dass ich die Auftragsnummer aus der Tabelle AUFTRAG haben möchte.
Außerdem hab ich mit INNER JOIN AUFTRAGPOSITION ap (NOLOCK) ON ap.Auftragsnr = a.Auftragsnr klar gemacht, dass ich eine Verknüpfung der Auftragstabelle mit der Auftragspositionentabelle wünsche und nur die gleichen Datensätze verknüpft werden, wo jeweils die Auftragsnummer gleich ist.
Außerdem hab ich die Ausgabe auf die Datenzahlen vom Kunden mit der Kundennummer 23 eingegrenzt.

Das (NOLOCK) Statement macht klar, dass der Befehl ohne Schreibzugriff erfolgen soll. Das ist besonders interessant, wenn in der Datenbank immer wieder Abfragen erfolgen, die auch mal zeitintensiv sind. Ein gutes Beispiel zu den Auswirkungen von (NOLOCK) findet man hier. Generell macht ein (NOLOCK) also nur Sinn, wenn im Hintergrund noch viele Daten geändert werden und man trotzdem schon mal Daten erhalten möchte.

Im nächsten Teil geht es dann weiter mit GROUP BY und ORDER BY.