SQL-Abfragen: Unterschied zwischen den Versionen

Version vom 25. Februar 2013, 11:31 Uhr

Die Lerneinheit "SQL-Abfragen" dient der Erläuterung unterschiedlicher Anwendungsfälle der SQL-Anweisung "SELECT", die jeweils mit interaktiv ausführbaren Beispielen hinterlegt sind. Die interaktiv ausführbaren Beispiele beziehen sich auf die Trainingsdatenbank [1].

Die SELECT Anweisung

Relationale Datenbanken können mit Hilfe der SELECT-Anweisung ausgewertet werden, wenn die entsprechenden Zugriffsrechte vorliegen. Nachfolgende Beispiele beziehen sich auf die Implementierung eines SQL-Trainingsservers, welcher als interaktive Anwendung auch unter http://sql.idv.edu erreichbar ist. Die Zugriffsrechte auf die Lehr- und Übungsdatenbank über die vorliegende Schnittstelle erlauben lediglich die Anwendung der SELECT-Anweisung. Das Ausführun von Anweisungen, welche die Struktur oder den Inhalt der Datenbank ändern könnten, werden abgewiesen.

Den interaktiven Abfragen liegt folgendes Datenmodell zu Grunde:

Auswählen und Ausgeben von Datensätzen

Bei der Formulierung von Abfragen mittels SELECT-Anweisung ist die Anwendung von mengentheoretischen Grundkenntnissen sehr hilfreich. Grundsätzlich sind mittels SELECT alle Datensätze aller Tabellen einer Datenbank auswählbar (selectierbar). Die Kunst der richtigen Anwendung besteht aber darin, durch das Formulieren von Schnitt- und Vereinigungsmengen das richtige Ergebnis (= der gestellten Aufgabe entsprechende Menge) zu bilden. Beispiele für Aufgaben aus der vorliegenden Übungs-Datenbank sind:

Die vollständigen Adressen aller Kunden in Nieder- und Oberösterreich.
Eine Liste aller Chemie-Bücher des Verlages 'oev'.
Die kumulierten Umsätze des Jahres 2000 aller Kunden, absteigend aufgelistet nach Bundesländern.

Alle Ergebnisse einer SELECT-Anweisung werden in Form von Listen (bzw. Tabellen) erzielt. In der Praxis sind die erzielten Ergebnisse Teil einer (Computer)Anwendung, welche aus dem unermesslichen Repertoire Anwendungen stammt und an dieser Stelle keine weitere Behandlung mehr bedarf. Siehe: Datenbank-Grundlagen#Was_sind_und_wozu_dienen_Datenbanken.3F

Die Idee der Auswertung dieser Übungs-Datenbank ist die Weiterverarbeitung und Tabellenkalkulationsprogrammen. Zu diesem Zwecke wird am Ende jedes Ergebnisses die Funktion "Download as CSV" angeboten.

SELECT- Die Grundform

Das erste Beispiel erzeugt eine Liste aller Vornamen der Kunden (aus der Tabelle Kunde). Die Grundform verlangt mindestens die Komponenten SELECT und FROM. Nach der Komponente SELECT wollen die Namen der Spalten (Felder) genannt werden, nach dem FROM jene Tabelle(n) aus denen die gesuchten Felder stammen. Als Einstieg wir lediglich aus einer Tabelle selektiert.

SELECT Vorname
FROM Kunde

Möchte man mehr als ein Feld aufgelistet haben, so werden diese durch Komma getrennt nacheinander angeführt. Nachfolgendes Beispiel sucht nach den Inhalten der Felder Vorname, Nachname und Plz der Kunden (aus der Tabelle Kunde).

SELECT Vorname, Nachname, Plz
FROM Kunde

Bemerkenswert ist, dass alle Datensätze der Tabelle "Kunde" ausgewählt und ausgegeben werden und diese prinzipiell unsortiert sind.

Ist die Anzahl der Felder, bzw. deren Namen unbekannt, so führt folgendes SELECT zur Auswahl aller Datensätze mit all ihren Feldern. Der gesamte Inhalt der Tabelle "Kunde" wird demnach selektiert und ausgegeben. 999 Kunden sollen gefunden und angezeigt werden.

SELECT *
FROM Kunde

Sortieren der Datensätze und Ausschließen von Wiederholungen

Gleiche Inhalte von Feldern in unterschiedlichen Datensätzen ist keine Seltenheit. Es würde überraschen, wenn bei 999 Kunden manche Vornamen nicht mehrfach vorkämen. Eine einfache Methode dies zu überprüfen ist, die Reihenfolge der Vornamen der Kunden alphabetisch zu sortieren. Das SELECT-FROM wird um die Komponente ORDER BY ergänzt. "ASC" steht für ascending, was aufsteigend sortiert bedeutet.

SELECT Vorname
FROM Kunde
ORDER BY Vorname ASC

Haben Sie das Ergebnis überprüft? Wer hätte gedacht, dass sich 9 Achims in der Kundenliste befinden? Sollen alle Duplikate aus der Liste verschwinden, so kommt die Komponente DISTINCT zum Einsatz.

SELECT DISTINCT Vorname
FROM Kunde
ORDER BY Vorname ASC

Selektieren von Datensätzen - bilden von Teil- und Vereinigungsmengen

Zu Beginn der Lerneinheit war von mengentheoretischen Überlegungen die Rede. Die Komponente DISTINCT hat zwar zur Bildung einer Teilmenge aus der Menge aller Vornamen bereits beigetragen, aber diese Methode ist für viele Anfragen an Datenbanken nicht ausreichend.

Die WHERE-Komponente in Verbindung mit den nachfolgend angeführten Operatoren ermöglicht das Bilden von Vereinigungs- und Teilmengen unter Verwendung von Klammern, Vergleichen, Rechenoperationen und logischen Verbindungen.

( )
* /
+ -
= <> > < like
NOT AND OR

Neun Kunden mit dem Vornamen Achim befinden sich in der Datenbank. Gesucht sind nun der Vorname, der Nachname und die Postleitzahl jener Kunden, deren Vornamen auf Achim lauten. Aufgelistet sollten diese aufsteigend nach dem Nachnamen werden.

SELECT Vorname, Nachname, PLZ
FROM Kunde
WHERE Vorname='Achim'
ORDER BY Nachname ASC

Wie wäre es mit der eingangs erwähnten Auflistung aller Chemiebücher, des Verlages 'oev' ? Die Lösung soll in kleinen Schritten erfolgen. Zuerst werden die Chemiebücher gesucht. Chemiebücher enthalten im Titel den Text "Chemie". Das "%-Zeichen" steht jeweils für einen beliebigen Text vor und nach dem gesuchten Textteil.

SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
ORDER BY Verlag

Im zweiten Schritt wird die Einschränkung (Bilden einer weiteren Teilmenge) auf Bücher des Verlages 'oev' unter Nutzung des logischen Operators "AND" hinzugefügt.

SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
AND Verlag = 'oev'
ORDER BY Verlag

Verbinden von Tabellen

Zur Unterstützung zahlreicher Aufgaben aus dem betrieblichen Alltag werden Daten benötigt, die über mehrere Tabellen hinweg verteilt sind. Das Zusammenführen von Feldern aus mehreren Tabellen heißt auch Join und wird ebenfalls in der WHERE-Komponente definiert.

Bei Abfragen, die über mehrere Tabellen gehen, ist es ratsam, die Feldnamen in Verbindung mit dem Tabellennamen anzuführen. Aus Vorname wird Kunde.Vorname, aus Preis wird Buch.Preis usw. Diese Maßnahme ist dann unerlässlich, wenn in ein und derselben Abfrage idente Feldbezeichnungen auftauchen. Das Feld Kunde.Nr muss z. B. von der Artikel.Nr und diese von der Buch.Nr unterschieden werden können.

Die Verbindung der beteiligten Tabellen erfolgt über die Schlüsselfelder. Da im vorliegenden Beispiel ausschließlich 1:n Verbindungen die Tabellen verbinden wird immer ein Primärschlüssel einer Tabelle mit einem Fremdschlüssel einer weiteren Tabelle verbunden. Folgen Sie den gerichteten Kanten in der http://sql.idv.edu/skalierbar/datenmodell_3.swf. Die gerichteten Kanten weisen den Weg der Verbindungen (Joins).

Die Augenfälligste Anwendung der Verbindung von Tabellen wird dann gefordert, wenn vollständige Kundenadressen benötigt werden. Da die Ortsnamen der 3. Normalform wegen in eine eigene Tabelle mit der Bezeichnung PLZ ausgelagert wurden, ist eine Verbindung der Tabellen PLZ und Kunde notwendig um vollständige Adressen zu erlagen.

SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz
ORDER BY Ort, Nachname ASC

Gruppieren von Datensätzen

Die GROUP BY-Klausel bewirkt, dass die aus einer Abfrage resultierende Tabelle in Gruppen gegliedert wird. Dazu muss in der GROUP BY-Klausel angegeben werden, nach welchem Kriterium die Gruppierung erfolgen soll. Dieses Kriterium, der Gruppierungsschlüssel, ist im einfachsten Fall ein Attribut der Ausgangstabelle. Alle Zeilen mit demselben Wert des Gruppierungsschlüssels fallen in dieselbe Gruppe. Eine Gruppe ist also nichts anderes als eine Teiltabelle. Im Ergebnis wird jede Gruppe durch die Anwendung von Gruppierungsfunktionen auf eine einzelne Zeile zusammengefasst.

Als typische Gruppenfunktionen bzw. Aggregatsfunktionen gelten:

COUNT
SUM
AVG
MAX
MIN

Die Gruppenfunktionen werden analog zu gängigen Funktionen in Tabellenkalkulationsprogrammen angewandt und notiert, z. B. SUM(Auftragspos.Menge * Buch.Preis).

Diese Abfrage gibt das gruppierte Feld Region und die Anzahl der Orte, die "kirche" enthalten, aus der Tabelle PLZ aus und sortiert diese neue Tabelle absteigend anhand der zweiten Spalte.

SELECT Region, COUNT(*)
FROM PLZ
WHERE Ort LIKE '%kirche%'
GROUP BY Region
ORDER BY 2 DESC

Mit der Gruppenfunktion generierte Spalten können mit AS benannt und dadurch auch für weitere Aktionen (z. B. ORDER BY) innerhalb derselben Abfrage verwendet werden. Die folgende Abfrage stellt bereits eine eher komplexe Abfrage mit einigen Joins dar. Sie gibt die Spalten Kunde.Nr, Kunde.Nachname, PLZ.Ort und den errechneten Umsatz aus den Tabellen PLZ, Kunde, Auftrag, Auftragspos und Buch aus, gruppiert das Ergebnis nach der Kundennummer und sortiert es am Nachnamen.

SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz=Kunde.Plz
AND Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort
ORDER BY Kunde.Nachname

Selektion (Einschränkung) auf eine Gruppierung (Aggregierung) von Datensätzen

Besteht der Bedarf, das Ergebnis einer Aggregatsfunktion einzuschränken, so geschieht dies nicht in der WHERE Klausel, sondern in der HAVING Klausel, welche nach dem GROUP BY zur Anwendung kommt.

Beispiel: Der ermittelte Kundenumsatz aus obigem Beispiel soll auf jene Kunden eingeschränkt werden, deren Umsatz den Betrag von 100000 erreicht oder übersteigt. Erwartet wird eine Liste der Kundenumsätze mit den Merkmalen: Nummer, Nachname, Ort und Summe, sortiert nach dem Nachnamen des Kunden.

SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz=Kunde.Plz
AND Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort
HAVING SUM(Auftragspos.Menge*Buch.Preis >= 100000
ORDER BY Kunde.Nachname

<html> <form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz FROM PLZ, Kunde, Auftrag, Auftragspos, Buch WHERE PLZ.Plz=Kunde.Plz AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort HAVING SUM(Auftragspos.Menge*Buch.Preis) >= 100000 ORDER BY Kunde.Nachname"> <input type="submit" value="Ausführen"> </form> </html>

Literatur

Weiterführende Links

http://www.sql-und-xml.de/sql-tutorial/

Zitiervorschlag

Mittendorfer in Pils, Informationsverarbeitung I (24. 2. 2013), SQL-Abfragen (mussswiki.idv.edu/iv1)