select

Rückblick: Relationale Algebra
§ Relationale Algebra als Anfragesprache auf Relationen
§ Ausdrücke zusammengesetzt aus Operatoren, z.B.
§ Selektion (σ) zur Auswahl von Tupeln
§ Projektion (π) zur Auswahl von Attributen
§ Kreuzprodukt (×) und Joins (⨝, ⟕, ⟖, ⟗)
zur Verknüpfung von Relationen
§ Mengenoperationen (∩, , −)
zur Verknüpfung von Relationen mit gleichem Schema
§ Mehrdeutige Namen von Attributen und Relationen
können durch Umbenennung eliminiert werden
119
2.5 Structured Query Language (SQL)
§ Structured Query Language (SQL)
§ geht zurück auf den in IBM Almaden (San Jose)
entwickelten Prototypen System R
§ ursprünglich: Structured English Query Language (SEQUEL)
§ auf Englisch wird SQL noch immer sequel gesprochen
120
SQL als Standard
§ SQL standardisiert durch American National Standards
Institute (ANSI) und International Organization for
Standardization (ISO)
§ SQL-86 / SQL-89
§ SQL-92 (z.B. Datentypen für Daten, Mengenoperationen)
§ SQL-99 (z.B. rekursive Anfragen)
§ SQL-2003 (z.B. Unterstützung von XML)
§ SQL-2008 / SQL-2011
§ Oracle, IBM DB2 und Microsoft SQL Server unterstützen
SQL-92 weitgehend und bieten darüber hinaus
proprietäre Funktionalität (z.B. XML-Unterstützung)
121
SQL Sprachbestandteile
§ Data Definition Language (DDL) zur
Schemadefinition (z.B. Anlegen von Tabellen)
§ Data Query Language (DQL) zum
Anfragen (z.B. Auswahl bestimmer Zeilen)
§ Data Manipulation Language (DML) zur
Datenmanipulation (z.B. Einfügen von Daten in Tabellen)
§ Data Control Language (DCL) zur
Rechteverwaltung (z.B. Sperren des Zugriffs auf Tabelle)
§ Transaction Control Language (TCL) zur
Transaktionsverwaltung (z.B. rückgängig machen)
122
2.5.1 Anfragen auf einer Tabelle
§ Anfragen lassen sich mittels des SELECT Kommandos
formulieren; dieses hat folgende Form
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
< Attribute >
< Tabellen >
< Bedingungen >
< Attribute >
§ Beispiel: Vorname und Name von Professoren in Informatik
1
2
3
SELECT Vorname , Name
FROM Professoren
WHERE Fach = ’ Informatik ’
dies entspricht folgendem Ausdruck der Relationenalgebra
fi [ Vorname, Name ] ( ‡ [ Fach = “Informatik” ] ( Professoren ) )
123
Duplikateneliminierung
§ Anfrageergebnisse können, im Gegensatz zu
Ergebnisrelationen der Relationanalgebra,
Duplikate enthalten
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
Vorname
1
2
SELECT Vorname
FROM Professoren
Donald
Albert
Alfred
Donald
Carl
124
Duplikateneliminierung
§ Duplikate im Anfrageergebnis können durch Angabe von
DISTINCT unterdrückt werden
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
Vorname
1
2
SELECT DISTINCT Vorname
FROM Professoren
Donald
Albert
Alfred
Carl
125
Sortierung
§ Anfrageergebnisse können, im Gegensatz zu
Ergebnisrelationen der Relationanalgebra,
eine Ordnung haben
§ Sortierung des Anfrageergebnis nach ein oder mehreren
Attributen durch Angabe durch ORDER BY
§ Natürliche Ordnung der Attribute wird gemäß ihres
Datentyps verwendet, d.h. numerische Attribute (z.B. int
und float) werden nach numerischem Wert, textuelle
Attribute (z.B. char und varchar) lexikografisch sortiert
126
Sortierung
§ Angabe von ASC bzw. DESC bestimmt, ob nach einem
Attribut aufsteigend oder absteigend sortiert wird
§ ASC ist hier Vorgabewert (default) und kann entfallen
§ Beispiel: Vorname, Name und Fach von Professoren,
aufsteigend sortiert nach Nachname und Vorname
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
Vorname , Name , Fach
Professoren
Fach = ’ Mathematik ’
Name , Vorname
127
Sortierung
§ Beispiel: Vorname und Name von Studenten, absteigend
sortiert nach ihrer Anzahl von Semestern
1
2
3
SELECT Vorname , Name
FROM Studenten
ORDER BY Semester DESC
128
Formatierung und Namenskonventionen
§ Ähnlich zu Programmiersprachen, gibt es auch für SQL
verschiedene Konvetionen zur Bennenung von
Bezeichnern und Formatierung von Kommandos
§ Schlüsselwörter (CREATE vs. create)
§ Attributnamen (Bestell_Nr vs. BestellNr)
§ Tabellennamen (Kunden vs. Kunde)
§ Formatierung von Kommandos (Klammern und Umbrüche)
§ Letztlich Geschmacksache,
Konsistenz innerhalb eines Projekts ist jedoch wichtig
129
Mathematische Funktionen
§ SQL unterstützt die mathematischen Operatoren (+, *, -, /)
und eine Vielzahl mathematischer Funktionen, z.B.
§ ABS(A): Betrag
§ SIGN(A): Vorzeichen
§ SQRT(A): Quadratwurzel
§ FLOOR(A): Abrunden auf ganze Zahl
§ CEIL(A): Aufrunden auf ganze Zahl
§ MAX(A,B): Maximum der Attribute A und B
§ MIN(A,B): Minimum der Attribute A und B
§ …
130
Sonstige Funktionen
§ SQL unterstützt zahlreiche Funktionen für
nichtnumerische Datentypen, etwa für Zeichenketten
§ LOWER(A): Zeichenkette in Kleinbuchstaben
§ UPPER(A): Zeichenkette in Großbuchstaben
§ LENGTH(A): Länge der Zeichenkette
§ SUBSTRING(A, start, end): Ausschnitt der Zeichenkette
§ TRIM(A): Zeichenkette ohne umgebende Leerzeichen
131
Aggregatfunktionen
§ SQL unterstützt eine Vielzahl von Aggregatfunktionen,
die auf die Werte eines Attributs in mehreren Tupeln
angewendet werden können, z.B.:
§ MIN(A): minimaler Wert für Attribut A
§ MAX(A): maximaler Wert für A
§ AVG(A): durchschnittlicher Wert für A
§ SUM(A): Summe der Werte für A
§ COUNT(*): Anzahl Tupel
§ COUNT(A): Anzahl Tupel mit Wert ungleich NULL für A
§ COUNT(DISTINCT A): Anzahl Werte ungleich NULL für A
132
Aggregatfunktionen
§ Beispiel:
§ MIN(A) = 1
§ MAX(A) = 3
§ AVG(A) = 8 / 4 = 2
§ COUNT(*) = 5
§ COUNT(A) = 4
A
1
2
NULL
2
3
§ COUNT(DISTINCT A) = 3
133
Aggregatfunktionen
§ Beispiel: Maximale, minimale und durchschnittliche
Semesteranzahl innerhalb Studenten der Physik
1
2
3
4
5
SELECT MAX ( Semester ) ,
MIN ( Semester ) ,
AVG ( Semester )
FROM Studenten
WHERE Fach = ’ Physik ’
134
Selektionsbedingungen
§ Selektionsbedingungen, in WHERE-Klausel, erlauben
mehr als Vergleich zwischen Attribut und Konstante
§ Vergleichsoperatoren (=, <>, >, <, >=, <=)
1
2
3
SELECT Vorname , Name
FROM Studenten
WHERE Semester > 10
§ Vergleiche zwischen Attributen
1
2
3
SELECT *
FROM Bestellungen
WHERE Bestelldatum = Lieferdatum
135
Selektionsbedingungen
§ Boole‘sche Operatoren (NOT, OR, AND) mit der Rangfolge
NOT vor AND vor OR und ggf. Klammerung
§ Beispiel: Studenten weder von Informatik noch Physik
SELECT *
FROM Studenten
WHERE Fach != ’ Informatik ’ AND Fach != ’ Physik ’
1
2
3
oder
1
2
3
SELECT *
FROM Studenten
WHERE NOT ( Fach = ’ Informatik ’ OR Fach = ’ Physik ’)
136
Mustervergleiche in Zeichenketten
§ Textuelle Attribute kann man mittels LIKE gegen ein
Muster vergleichen; hierbei dient % als Platzhalter
(wildcard) und repräsentiert kein oder mehrere Zeichen
§ Beispiel: Vorlesungen mit „Grundlagen“ im Titel
1
2
3
SELECT *
FROM Vorlesungen
WHERE Titel LIKE ’% Grundlagen % ’
wenn auch „Informatikgrundlagen“ gefunden werden soll
1
2
3
SELECT *
FROM Vorlesungen
WHERE LOWER ( Titel ) LIKE ’% grundlagen % ’
137
Wertemengen
§ Mittels des Kommandos IN kann überprüft werden, ob der
Wert eines Attributs in einer gegebenen Wertemenge liegt
§ Beispiel: Studenten mit Vornamen Max oder Moritz
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( ’ Max ’ , ’ Moritz ’)
§ Die Wertemenge darf auch mittels einer
SQL-Unterabfrage bestimmt werden,
dazu später mehr
138
2.5.2 Anfragen über mehrere Tabellen
§ SQL unterstützt verschiedene Kommandos, um zwei oder
mehr Tabellen miteinander zu verknüpfen; hierzu
werden mehrere Tabellen in der FROM-Klausel erwähnt und
Joinprädikate in der WHERE-Klausel angegeben
§ SQL kennt zudem eine alternative Syntax, die dazu dient,
Joinprädikate von Selektionsprädikaten zu trennen
139
Kartesisches Produkt
§ Kartesisches Produkt zweier Tabellen lässt sich durch
deren Nennung in der FROM-Klausel oder durch
Verwendung des CROSS JOIN Kommandos ermitteln
§ Beispiel: Kombinationen von Studenten und Professoren
1
2
SELECT *
FROM Studenten , Professoren
oder
1
2
SELECT *
FROM Studenten CROSS JOIN Professoren
140
Natürlicher Join
§ Natürlicher Join zweier Tabellen lässt sich durch deren
Nennung in der FROM-Klausel und Angabe der
gemeinsamen Attribute in der WHERE-Klausel oder durch
Verwendung des NATURAL JOIN Kommandos ermitteln
§ Beispiel: Vorlesungen mit zugehörigen Professoren
SELECT *
FROM Vorlesungen , Professoren
WHERE Vorlesungen . PersNr = Professoren . PersNr
1
2
3
oder
1
2
SELECT *
FROM Vorlesungen NATURAL JOIN Professoren
141
Allgemeiner Join
§ Allgemeiner Join zweier Tabellen lässt sich durch deren
Nennung in der FROM-Klausel und Angabe des
Joinprädikats in der WHERE-Klausel oder durch
Verwendung des JOIN ON Kommandos ermitteln
§ Beispiel: Professoren und Studenten gleichen Namens
1
2
3
SELECT *
FROM Professoren , Studenten
WHERE Professoren . Name = Studenten . Name
oder
1
2
3
4
SELECT
FROM
JOIN
ON
*
Professoren
Studenten
Professoren . Name = Studenten . Name
142
Mengenoperationen
§ Die Kommandos UNION, INTERSECT und EXCEPT
bilden die Mengenoperationen , ∩ und ab und
dürfen nur auf Tabellen mit kompatiblen Schemata
angewendet werden
§ Beispiel: Namen, die nur bei Studenten, aber nicht bei
Professoren vorkommen
1
2
3
4
5
SELECT
FROM
EXCEPT
SELECT
FROM
Name
Studenten
Name
Professoren
143
Umbenennung
§ Taucht die gleiche Tabelle mehrfach in einem SQL
Kommando auf, können die einzelnen Auftreten
benannt werden
§ Beispiel: Paare von Studenten mit gleichem Fach
1
2
3
SELECT *
FROM Studenten s1 , Studenten s2
WHERE s1 . Fach = s2 . Fach
144
Unterabfragen
§ SQL unterstützt Unterabfragen, d.h. eine SELECT
Kommando kann häufig anstelle einer Tabelle oder
einer Wertemenge verwendet werden
§ Beispiel: Studenten mit einem Vornamen, den es auch
unter den Professoren gibt
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( SELECT Vorname FROM Professoren )
145
Umbenennung
§ SQL erlaubt die Umbenennung von Tabellen und
Attributen; dies ist insbesondere dann nützlich, wenn die
gleiche Tabelle mehrfach verwendet werden soll
§ Beispiel: Studenten, die eine gemeinsame Vorlesung hören
1
2
3
4
5
SELECT
FROM
WHERE
AND
AND
DISTINCT s1 . Name AS StudentEins , s2 . Name AS StudentZwei
Studenten s1 , h ö ren h1 , h ö ren h2 , Studenten s2
s1 . MatrNr = h1 . MatrNr
h1 . VorlNr = h2 . VorlNr
h2 . MatrNr = s2 . MatrNr
146
Äußere Joins
§ SQL unterstützt äußere Joins mittels der
(LEFT|FULL|RIGHT) OUTER JOIN Kommandos
§ Beispiel: Professoren und Studenten mit gleichem Namen
(bei Erhaltung aller Professoren)
1
2
3
SELECT *
FROM Professoren LEFT OUTER JOIN Studenten
ON Professoren . Name = Studenten . Name
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Informatik
Chemie
Informatik
Mathematik
19882
Nora
Nobel
3
18979
Gundula
Gauss
1
147
2.5.3 Gruppierung & Aggregation
§ Aggregatfunktionen wurde bisher nur auf gesamte
Tabellen angewendet; oft möchte man diese
jedoch auf Gruppen von Tupeln anwenden
§ Beispiele:
§ Durchschnittliche Note je Vorlesung
§ Semesterwochenstunden je Professor
§ Anzahl der Studenten pro Fach
148
Gruppierung
§ Aggregatfunktionen lassen sich mittels des GROUP BY
Kommandos auf Gruppen von Tupeln anwenden; dieses
hat folgende allgemeine Form
1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
< Projektionsattribute > , < Aggregate >
< Tabellen >
< Bedingungen auf Attributen >
< Gruppierattribute >
< Bedingungen auf Aggregaten >
< Sortierattribute > , < Aggregate >
149
Gruppierung
§ Tupel mit den gleichen Werten für die angegebenen
Gruppierattribute bilden eine Gruppe, für die ein
Wert der Aggregate berechnet wird
§ Beispiel: Fächer absteigend sortiert nach Anzahl Studenten
1
2
3
4
SELECT
FROM
GROUP BY
ORDER BY
Fach , Count (*) AS Anzahl
Studenten
Fach
Anzahl DESC
150
Gruppierung
§ Beispiel: Anzahl Professoren eines Namens je Fach
1
2
3
SELECT Fach , Name , Count (*)
FROM Professoren
GROUP BY Fach , Name
§ Projektionsattribute sind in der Regel identisch mit den
Gruppierattributen; sie müssen eine Teilmenge sein
§ Sortierattribute müssen eine Teilmenge der
Gruppierattribute sein
151
Gruppierung mit Selektion auf Attributen
§ Soll die Gruppierung nur solche Tupel berücksichtigen,
deren Attribute eine bestimmte Bedingung erfüllen,
so kann dies mittels einer WHERE-Klausel
erreicht werden
§ Beispiel: Anzahl Professoren namens Meier je Fach
1
2
3
4
SELECT
FROM
WHERE
GROUP BY
Fach , Count (*) AS Anzahl
Professoren
Name = ’ Meier ’
Fach
152
Gruppierung mit Selektion auf Aggregaten
§ Soll die Gruppierung nur solche Tupel zurückliefern,
deren Aggregate eine bestimmte Bedingung erfüllen,
so kann dies mittels einer HAVING-Klausel
erreicht werden
§ Beispiel: Fächer mit mehr als 10 Professoren
1
2
3
4
SELECT
FROM
GROUP BY
HAVING
Fach , Count (*) AS Anzahl
Professoren
Fach
Anzahl > 10
153
Gruppierung mit Selektion
§ SQL-Anfrage kann sowohl eine WHERE-Klausel als auch
eine HAVING-Klausel besitzen und es kann
mehrere Aggregate geben
§ Beispiel: Anzahl Professoren in Fächern, die auf
„ik“ enden und mehr als 10 Professoren haben,
alphabetisch sortiert
1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Fach , COUNT (*)
Professoren
Fach LIKE ’% ik ’
Fach
COUNT (*) > 5
Fach
154
Verarbeitung der Gruppierung
§ WHERE
(d.h. betrachte nur Professoren, deren Fach auf „ik“ endet)
§ GROUP BY
(d.h. gruppiere verbleibende Professoren nach Fach)
§ HAVING
(d.h. eliminiere Fächer mit weniger als 5 Professoren)
1
§ ORDER BY
(d.h. sortiere verbleibende Fächer)
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Fach , COUNT (*)
Professoren
Fach LIKE ’% ik ’
Fach
COUNT (*) > 5
Fach
155
Top-k Anfragen
§ Bei Bildung von Aggregaten sind wir häufig nur an einer
bestimmten Anzahl von Tupeln mit höchstem oder
niedrigstem Aggregatwert interessiert; dies lässt sich
durch Verwendung des TOP (MS SQL Server) oder
LIMIT Kommandos (SQLite) erzielen
§ Beispiel: 5 Fächer mit den meisten Professoren
MS SQL Server:
1
2
3
4
SELECT
FROM
GROUP BY
ORDER BY
TOP 5 Fach , COUNT (*) AS Anzahl
Professoren
Fach
Anzahl DESC
156
Top-k Anfragen
§ Beispiel: 5 Fächer mit den meisten Professoren
SQLite:
1
2
3
4
5
SELECT
FROM
GROUP BY
ORDER BY
LIMIT
Fach , COUNT (*) AS Anzahl
Professoren
Fach
Anzahl DESC
5
157
2.5.4 Quantifizierung
§ SQL unterstützt mittels der Kommandos EXISTS und
NOT EXISTS den Existenzquantor
und
den Nichtexistenzquantor
§ Beispiel: Professoren zu denen es Studenten
mit gleichem Vornamen gibt
1
2
3
4
SELECT *
FROM Professoren p
WHERE EXISTS
( SELECT * FROM Studenten s WHERE p . Vorname = s . Vorname )
158
Quantifizierung
§ Allquantor wird nicht explizit unterstützt; kann aber
mittels des Nichtexistenzquantors ausgedrückt werden
§ Beispiel: Studenten, die alle Vorlesungen hören
(d.h. es gibt kein Vorlesung, die der Student nicht hört)
1
2
3
4
5
6
7
8
9
10
SELECT *
FROM Studenten s
WHERE NOT EXISTS
( SELECT *
FROM Vorlesungen v
WHERE NOT EXISTS
( SELECT *
FROM h ö ren
WHERE s . MatrNr = h . MatrNr
AND h . VorlNr = v . VorlNr ))
159
Zusammenfassung
§ Structured Query Language (SQL) als standardisierte
Anfragesprache für relationale Datenbanken
§ Data Query Language zum Formulieren von Anfragen
(SELECT ... FROM ... WHERE ... ORDER BY ...)
§ mathematische Funktionen (z.B. ABS(A) und SIGN(A))
§ Aggregatfunktionen (z.B. MIN(A) und SUM(A))
§ Boole‘sche Operatoren (AND, OR, EXCEPT)
§ Verknüpfungen von mehreren Tabellen (JOINs)
§ Gruppierung (GROUP BY)
§ Quantifizierung (EXISTS)
160
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 4)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 7 & 10)
161