Kapitel DB-4: SQL Inhalt - Universität Basel | Informatik

Herbstsemester 2013
CS261 Web Data Management
Kapitel DB-4: SQL
H. Schuldt
Inhalt
Datenmanipulationssprache SQL:
•
SQL (Structured Query Language)
– ist die Standardsprache für die Datendefinition und Datenmanipulation in
relationalen Datenbanksystemen
– Der in SQL für die Datendefinition zuständige Teil wird Data Definition
Language (DDL) genannt
– Der Teil der Sprache für Einfügen, Ändern und Löschen heisst Data
Manipulation Language (DML).
•
SQL umfasst:
– Interaktives (“stand-alone”) SQL
– Anweisungen zur Integritätssicherung und Zugriffskontrolle
– Anweisungen zur physischen Datenorganisation (Indizes etc.)
– Eingebettetes (“embedded”) SQL (für die gängigen Programmiersprachen)
– Integration von SQL in “4th Generation Language”
HS 2013
Web Data Management (CS261) – SQL
DB-4-2
1
Historie von SQL
•
•
•
•
•
SEQUEL
(= Structured English Query Language)
– Forschungsprototyp in System R von IBM (1975)
SQL2 (= SQL 92)
SQL3 (= SQL 99)
– Ergänzung von SQL2 um objektorientierte Konstrukte (objektrelationale DB)
SQL:2003 Erweiterung der objektrelationalen Konstrukte
Im Folgenden werden wir uns auf den Basis-Sprachumfang von SQL3,
der auch bereits Bestandteil von SQL2 war, beschränken
HS 2013
Web Data Management (CS261) – SQL
DB-4-3
Wiederholung (Relationenmodell)
In relationalen Datenbanken werden Daten in Relationen verwaltet.
•
Relationen enthalten Mengen von gleich strukturierten Datensätzen (Tupel)
•
Jedes Tupel besitzt dieselbe Menge von Attributen
•
Jedes Attribut besitzt einen Wertebereich (Domäne)
•
In einer Relation wird ein einzelnes Attribut bzw. eine Gruppe von Attributen
als Schlüssel (Primärschlüssel) gekennzeichnet. Der Wert dieses Attributs
(bzw. der Gruppe von Attributen) muss innerhalb der Ausprägung der Relation
eindeutig sein
•
Beziehungen zwischen Tabellen sind möglich, indem man erzwingt, dass
beliebige Werte von Attributen A einer Relation mit den Primärschlüsseln
einer anderen Relation übereinstimmen müssen. Die Attribute A werden
dann auch als Fremdschlüssel bezeichnet.
HS 2013
Web Data Management (CS261) – SQL
DB-4-4
2
Wiederholung: Beispieldatenbank …
Kunden
Produkte
KNr
Name
Stadt
Saldo
Rabatt
1
Legrand
Genf
-1080,00
0.10
2
Marty
Basel
-800,00
0.20
3
Frei
Basel
0,00
0,10
4
Janvier
Genf
0,00
0,10
5
Rosi
Lugano
0,00
0,05
6
Meier
Zürich
-3800,00
0,05
PNr
Bez
Gewicht
Preis
Lagerort
Vorrat
1
Papier
2,000
20,00
Basel
10000
2
Platte
1,000
2500,00
Zürich
400
3
Drucker
5,000
2000,00
Genf
200
4
Bildschirm
5,000
3000,00
Genf
80
5
CDs
0,500
20,00
Basel
5000
6
Maus
0,250
100,00
Basel
200
7
Speicher
0,100
200,00
Bern
2000
HS 2013
Web Data Management (CS261) – SQL
DB-4-5
Wiederholung: … Beispieldatenbank
Bestellungen
HS 2013
BestNr
Monat
Tag
KNr
PNr
Menge
1
7
16
1
1
100
Summe
1800,00
Status
bezahlt
2
7
21
1
1
100
1800,00
bezahlt
3
9
30
1
2
4
9000,00
bezahlt
4
9
30
1
3
1
1800,00
bezahlt
5
9
30
1
4
10
27000,00
bezahlt
6
10
15
1
5
50
900,00
bezahlt
7
10
28
1
6
2
180,00
geliefert
8
11
2
1
7
5
900,00
neu
9
10
26
2
1
100
1600,00
10
11
2
2
5
50
800,00
bezahlt
neu
11
9
28
3
5
50
900,00
bezahlt
12
10
28
3
7
10
1800,00
bezahlt
13
4
15
4
1
50
900,00
bezahlt
14
5
31
6
1
200
3800,00
bezahlt
15
6
30
6
7
10
1900,00
geliefert
16
7
31
6
1
100
1900,00
geliefert
Web Data Management (CS261) – SQL
DB-4-6
3
Einfache Datendefinition
Syntax der Datendefinition in SQL:
[user “.”] table “(“ column_element {“,” column_element}
{“,” table_constraint} “)”
CREATE TABLE
mit:
column_element =
column data_type [DEFAULT expr] [column_constraint]
column_constraint =
[NOT NULL]
[PRIMARY KEY | UNIQUE]
[REFERENCES [user “.”] table [ “(“ column “)” ]]
[CHECK “(“ condition “)”" ]
table_constraint =
[ (PRIMARY KEY | UNIQUE) “(“ column {“,” column} “)” ]
[ FOREIGN KEY “(“ column {“,” column} “)”
REFERENCES [user “.”] table [ “(“ column {“,” column} “)” ]
[CHECK “(“ condition “)” ]
HS 2013
Web Data Management (CS261) – SQL
DB-4-7
Datendefinition: Beispiele
1)
Definition der Produkt-Tabelle
CREATE TABLE Produkte (
PNr
INTEGER
CHECK (PNr > 0) PRIMARY KEY,
Bez
VARCHAR(30) NOT NULL UNIQUE,
Gewicht FLOAT
CHECK (Gewicht > 0.0),
2)
...)
Definition der Bestellungs-Tabelle
CREATE TABLE Bestellungen (
BestNr
INTEGER
CHECK (BestNr > 0) PRIMARY KEY,
Monat
INTEGER
NOT NULL CHECK (Monat BETWEEN 1 AND 12),
...,
FOREIGN KEY (PNr)
REFERENCES Produkte (PNr),
FOREIGN KEY (KNr)
REFERENCES Kunden (KNr),
UNIQUE (Monat, Tag, PNr, KNr)
)
HS 2013
Web Data Management (CS261) – SQL
DB-4-8
4
Unterschiede zum „reinen“ Relationenmodell
•
Die Festlegung eines Primärschlüssels wird nicht erzwungen
•
Wenn aber ein Primärschlüssel spezifiziert ist, dann wird die Einhaltung der
Primärschlüsselbedingung garantiert.
•
Analoges gilt für die Fremdschlüsselbedingung.
•
Falls kein Primärschlüssel (und kein Schlüsselkandidat) spezifiziert ist, dürfen
Tabellen Duplikate enthalten, d.h. es handelt sich um Multimengen („bags“).
HS 2013
Web Data Management (CS261) – SQL
DB-4-9
Anfragen (Queries)
Syntax von SQL-Abfragen:
Query = SelectBlock { [UNION | INTERSECTION | EXCEPT] SelectBlock }
[ORDER BY result_column [ASC | DESC] {“,” result_column [ASC | DESC]}
SelectBlock =
SELECT [ALL | DISTINCT] (column | expression) {“,” (column | expression)}
FROM table [correlation_var] {“,” table [correlation_var]}
[WHERE SearchCondition]
[GROUP BY column {“,” column} [HAVING SearchCondition]]
• Alle Joinbedingungen müssen in SQL explizit in der WHERE-Klausel
angegeben werden
• Standardmässig erfolgt keine Duplikateliminierung; diese kann mit DISTINCT
explizit spezifiziert werden
HS 2013
Web Data Management (CS261) – SQL
DB-4-10
5
SQL vs. Relationenalgebra bzw. SQL vs. Tupel-Relationenkalkül
•
Gegeben sei der SQL-Ausdruck
SELECT
A, B, ...
FROM
R, S, ..., T, ...
WHERE F
(so dass A, B, ... zu R, S, ... gehören, nicht aber zu T, ...,
und F über R, S, ..., T, ... definiert ist). Dann entspricht dieser Ausdruck …
•
… in der Relationenalgebra:
p[A, B, ...] (s[F] ( R μ S μ ... μ T μ ... ))
•
… und im Tupel-Relationenkalkül
{x.A, y.B, ... | x œ R  y œ S  ...  $ z œ T ... : F(x, y, ..., z, ...)}
HS 2013
Web Data Management (CS261) – SQL
DB-4-11
Beispiele: Select ...
1)
Gesucht sind (die Namen) alle(r) Kunden mit negativem Saldo.
SELECT KNr, Name, Stadt, Saldo, Rabatt
FROM Kunden
WHERE Saldo < 0.0
alle Attribute (Kurzschreibweise)
oder:
SELECT * FROM Kunden WHERE Saldo < 0.0
bzw.:
SELECT Name FROM Kunden WHERE Saldo < 0.0
Nur explizit aufgeführte Attribute
2)
Gesucht sind die Namen aller Kunden, die eine unbezahlte Bestellung haben,
die vor Anfang Oktober erfolgte
SELECT
FROM
WHERE
AND
HS 2013
Name
Kunden, Bestellungen
Monat < 10 AND Status <> 'bezahlt'
Kunden.KNr = Bestellung.KNr
Web Data Management (CS261) – SQL
DB-4-12
6
… Beispiele: Select
3)
Gesucht sind die Namen der Basler Kunden, die seit Anfang September ein
Produkt aus Basel geliefert bekommen haben, jeweils mit der Bezeichnung
des entsprechenden Produkts.
4)
Finden Sie die Rechnungssumme der Bestellung mit BestNr 111
(ohne auf das Attribut Summe der Relation Bestellungen zuzugreifen).
SELECT Menge*Preis*(1.0-Rabatt)
FROM
Bestellungen, Produkte, Kunden
WHERE BestNr = 111
AND Bestellungen.PNr = Produkte.PNr
AND Bestellungen.KNr = Kunden.KNr
HS 2013
Web Data Management (CS261) – SQL
DB-4-13
Komplexe Suchprädikate
5)
Finden Sie alle Paare von Kunden, die in derselben Stadt wohnen.
SELECT K1.Name, K2.Name FROM Kunden K1, Kunden K2
WHERE
K1.Stadt = K2.Stadt AND K1.KNr < K2.KNr
Korrelationsvariable (Tupelvariable): eindeutige Benennung mehrerer
„Inkarnationen“ derselben Relation
6)
Finden Sie die Kunden, deren Rabatt zwischen 10 und 20 Prozent liegt.
SELECT * FROM Kunden
WHERE Rabatt BETWEEN 0.10 AND 0.20
BETWEEN ... AND ... zur einfachen Formulierung von Bereichsanfragen
(„range queries”)
7)
Finden Sie Kunden mit Doppelnamen, die mit Meier oder Maier oder Meyer, ...
beginnen
SELECT * FROM Kunden WHERE Name LIKE 'M_ _er-%'
Pattern-Matching in Zeichenketten: Schüsselwort LIKE, sowie % als Platzhalter
(„wild card“) für eine beliebige Zeichenkette der Länge >= 0 und _ als
Platzhalter für ein beliebiges Zeichen.
HS 2013
Web Data Management (CS261) – SQL
DB-4-14
7
Test auf Nullwert / Mitgliedschaft in Menge
8)
Finden Sie alle Produkte, die grundsätzlich in keinem Lager geführt werden.
SELECT * FROM Produkte WHERE Lagerort IS NULL
Test auf Nullwert: IS NULL ist das einzige Vergleichsprädikat, das von einem
Nullwert erfüllt wird.
Konsequenz: Die Anfrage
SELECT * FROM Produkte WHERE Vorrat >= 0 OR Vorrat < 0
liefert (nur) alle Tupel der Relation Produkte, deren Wert bzgl. des Attributs
Vorrat kein Nullwert ist.
9)
Finden Sie alle Kunden aus Zürich, Genf und Basel.
SELECT * FROM Kunden WHERE Stadt IN ( 'Zürich', 'Genf',
'Basel')
Der Test auf Mitgliedschaft eines Werts in einer Menge erfolgt über das
IN-Prädikat.
HS 2013
Web Data Management (CS261) – SQL
DB-4-15
IN -Prädikat mit geschachtelten Select-Blöcken
Syntax: (column | expression) [NOT] IN "(" select_block ")"
10) Finden Sie die Namen aller Kunden, die eine unbezahlte Bestellung haben, die
vor Anfang Oktober erfolgte.
SELECT Name FROM Kunden
WHERE KNr IN ( SELECT KNr FROM Bestellungen
WHERE Status <> 'bezahlt' AND Monat < 10)
11) Finden Sie alle Kunden aus Städten, in denen es mindestens zwei Kunden gibt.
HS 2013
Web Data Management (CS261) – SQL
DB-4-16
8
Quantifizierte Vergleiche
Die Bedingung “ Wert Q ANY Menge” mit Q œ {=, π, <, >, £, ≥} ist erfüllt, wenn
es in der Menge ein Element gibt, für welches das Prädikat “Wert Q Element “ gilt
(= ANY ist äquivalent zu IN).
Die Bedingung “ Wert Q ALL Menge” mit Q œ {=, π, <, >, £, ≥} ist erfüllt, wenn
für alle Elemente der Menge gilt: “Wert Q Element”
(π ALL ist äquivalent zu NOT IN).
12) Finden Sie die Kunden mit dem geringsten Rabatt.
SELECT * FROM Kunden
WHERE
Rabatt £ ALL ( SELECT Rabatt FROM Kunden )
Achtung: Das Prädikat Wert =ALL Menge ist nur bei einer einelementigen
Menge erfüllbar.
SELECT KNr FROM Bestellungen
WHERE PNr = ALL ( SELECT PNr FROM Produkte )
Die Anfrage kann also nur dann ein nichtleeres Resultat liefern, wenn es nur
ein einziges Produkt gibt!
HS 2013
Web Data Management (CS261) – SQL
DB-4-17
Test, ob eine Menge leer oder nichtleer ist
13) Finden Sie die Namen aller Kunden, die eine unbezahlte Bestellung haben, die
vor Anfang Oktober erfolgte.
SELECT Name FROM Kunden
WHERE EXISTS ( SELECT * FROM Bestellungen
WHERE Status <> 'bezahlt'
AND Monat < 10
AND Bestellungen.KNr = Kunden.KNr )
14) Finden Sie die Kunden, für die keine Bestellung registriert ist.
SELECT * FROM Kunden
WHERE NOT EXISTS ( SELECT * FROM Bestellungen
WHERE Bestellungen.KNr = Kunden.KNr )
HS 2013
Web Data Management (CS261) – SQL
DB-4-18
9
Anfragen „ … für alle …“
Der Zusammenhang zum Allquantor des Relationenkalküls bzw. zur Division der
Relationenalgebra ist in SQL über Suchbedingungen der Form „... für alle ...“
gegeben. Diese „… für alle …“ Anfragen können in SQL mit NOT EXISTS
realisiert werden.
15) Finden Sie die Kunden, die alle überhaupt lieferbaren Produkte irgendwann
bestellt haben.
HS 2013
Web Data Management (CS261) – SQL
DB-4-19
Aggregationsfunktionen
Aggregationsfunktionen haben zum Ziel, eine (Multi-) Menge von Werten auf einen
Wert abzubilden. Syntax der Aggregation in SQL:
(MAX | MIN | AVG | SUM | COUNT) "(" (ALL | DISTINCT)
(column | expression | "*") ")"
16) Finden Sie den höchsten Rabatt aller Kunden.
SELECT MAX (Rabatt) FROM Kunden
17) An wie vielen Lagerorten werden Produkte gelagert?
SELECT COUNT (DISTINCT Lagerort) FROM Produkte
18) Welche Kunden haben einen überdurchschnittlichen Rabatt?
SELECT * FROM Kunden WHERE Rabatt >
(SELECT AVG (Rabatt) FROM Kunden)
19) Wie hoch ist der Gesamtumsatz?
SELECT SUM (b.Menge*p.Preis*(1.0-k.Rabatt))
FROM
Bestellungen b, Produkte p, Kunden k
WHERE b.PNr = p.PNr
AND
b.KNr = k.KNr
HS 2013
Web Data Management (CS261) – SQL
DB-4-20
10
Aggregation mit Gruppenbildung
Idee der Gruppenbildung: Eine Tupelmenge wird aufgrund der Werte eines Attributs
oder eines Ausdrucks in Gruppen (Teilmengen) partitioniert und über ein Prädikat
ausgewählt
20) Bestimmen Sie für alle Produkte deren Gesamtverkaufszahl (ab Anfang
September).
SELECT PNr, SUM(Menge) FROM Bestellungen
WHERE Monat >= 9
GROUP BY PNr
Alle Tupel mit übereinstimmender PNr werden gruppiert und SUM(Menge)
wird jeweils nur für die Gruppenmitglieder berechnet
HS 2013
Web Data Management (CS261) – SQL
DB-4-21
Aggregation mit Gruppenbildung und Filterung
21) Bestimmen Sie die Gesamtverkaufszahl für alle Produkte, die ab Anfang
September mehr als 1000-mal verkauft worden sind.
SELECT PNr, SUM(Menge)
FROM Bestellungen
WHERE Monat >= 9
Auswahl bestimmter Gruppen
GROUP BY PNr
HAVING SUM(Menge) > 1000
22) Bestimmen Sie für jede Stadt mit mehr als 10 Kunden den durchschnittlichen
Rabatt dieser Kunden. Die Ausgabe soll nach Rabatten absteigend sortiert sein.
SELECT Stadt, AVG(Rabatt) FROM Kunden
GROUP BY Stadt
HAVING COUNT(*) > 10
ORDER BY 2 DESC
Sortierung nach Attribut an zweiter Stelle
in SELECT-Klausel – also hier: AVG(Rabatt)
HS 2013
Web Data Management (CS261) – SQL
DB-4-22
11
Mächtigkeit und Grenzen von SQL …
•
•
•
SQL ist relational vollständig, d.h. man kann in SQL alle Anfragen stellen,
die man auch mit der Relationenalgebra formulieren kann
SQL ist sogar mächtiger als die Relationenalgebra
– da arithmetische Ausdrücke erlaubt und Aggregatsfunktionen vorhanden sind;
zusätzlich existieren GROUP BY und HAVING für Subtupelmengen
Aber: SQL ist nicht Turing-vollständig, d.h., es gibt berechenbare Funktionen, die
nicht in SQL ausdrückbar sind.
Beispiele für Anfragen, die nicht in SQL ausdrückbar sind:
•
Bestimmen Sie die Varianz der Rabatte, die den Kunden gewährt werden.
•
Erstellen Sie eine Liste der folgenden Kundenkategorien: alle Kunden mit einem
Umsatz zwischen 0.00 und 10‘000.00 Franken, alle Kunden mit einem Umsatz
zwischen 10‘000.01 und 20‘000.00 Franken, ..., sowie alle Kunden mit einem
Umsatz über 100‘000.00 Franken. Für jede Kategorie sind die Kundenanzahl und
der Gesamtumsatz auszudrucken.
HS 2013
Web Data Management (CS261) – SQL
DB-4-23
… Mächtigkeit und Grenzen von SQL
Beispiele für Anfragen, die nicht in SQL ausdrückbar sind (Fortsetzung):
•
Gegeben sei eine Relation mit direkten Flugverbindungen. Bestimmen Sie die
Orte, die man mit dem Flugzeug von Basel aus erreichen kann.
Flüge FlugNr
Ansatz:
Abflugort
Zielort
…
LX4711
Basel
Frankfurt
…
LH0815
Frankfurt
New York
…
AF123
New York
Paris
…
BA999
New York
London
…
OS111
Frankfurt
Wien
…
SELECT Fn.Zielort
FROM Flüge F1, Flüge F2, Flüge F3, ..., Flüge Fn
WHERE F1.Abflugort = 'Basel' AND
F1.Zielort = F2.Abflugort AND
F2.Zielort = F3.Abflugort AND ... AND
Fn-1.Zielort = Fn.Abflugort
Hier sind jedoch Anfragen mit variablem n gesucht. Rekursion ist in SQL aber nicht
möglich. Abhilfe ist die Entwicklung von geeigneten Anwendungsprogrammen
(T Kapitel DB-7)
HS 2013
Web Data Management (CS261) – SQL
DB-4-24
12
Datenmodifikation: Einfügen von Tupeln
SQL-Syntax der DML (Data Manipulation Language):
INSERT INTO table [ "(" column {"," column} ")" ]
( VALUES "(" expression {"," expression} ")" | unordered_query)
23) Einfügen eines neuen Kunden
INSERT INTO Kunden VALUES (7, 'Imboden', 'Luzern', 0.0, 0.0)
24) Einfügen von KNr, Name, Stadt eines neuen Kunden
INSERT INTO Kunden (KNr, Name, Stadt)
VALUES (7, 'Imboden', 'Luzern')
25) Annahme: es existiert eine Tabelle Mahnungen (BestNr ...) mit demselben
Schema wie Bestellungen
INSERT INTO Mahnungen
SELECT * FROM Bestellungen
WHERE Status = 'geliefert' AND Monat<10
Attributwerte, die beim Einfügen nicht spezifiziert sind, werden auf den DefaultWert oder den Nullwert gesetzt.
HS 2013
Web Data Management (CS261) – SQL
DB-4-25
Datenmodifikation: Ändern von Tupeln
SQL-Syntax der DML (Änderungen):
UPDATE table [correlation_var]
SET column "=" expression {"," column "=" expression} WHERE search_condition
26) Kunde mit KNr=1 zieht nach Lausanne um
UPDATE Kunden SET Stadt = 'Lausanne' WHERE KNr = 1
27) Erhöhe den Rabatt von Kunden, die Waren im Wert von über 100000.00
bestellt haben, um 5 Prozent
UPDATE Kunden SET Rabatt = Rabatt + 0.05
WHERE Saldo > -100000.0
AND KNr IN (
SELECT KNr FROM Bestellungen
GROUP BY KNr
HAVING SUM(Summe) > 100000.0 )
HS 2013
Web Data Management (CS261) – SQL
DB-4-26
13
Datenmodifikation: Löschen von Tupeln
SQL-Syntax der DML (Löschen):
DELETE FROM table [correlation_var] [WHERE search_condition]
28) Lösche alle Bestellungen, die vor Juli erfolgt sind
DELETE FROM Bestellungen WHERE Monat < 7
HS 2013
Web Data Management (CS261) – SQL
DB-4-27
Schema-Änderungen
SQL-Syntax der DDL (Schema-Änderungen):
ALTER TABLE table ADD column datatype [column_constraint]
{"," column data_type [column_constraint]}
29) Füge zusätzliche Integritätsbedingung zu Bestellungen hinzu
ALTER TABLE Bestellungen
ADD Frist INTEGER CHECK (Frist > 0)
Existierende Tupel werden bezüglich neuer Attribute implizit auf den
Nullwert gesetzt.
HS 2013
Web Data Management (CS261) – SQL
DB-4-28
14