SQL-Abfragen

SQL-Abfragen
1. Einleitung
• SQL ist eine relational vollständige Datenbanksprache.
• SQL steht für Structured Query Language.
• Der englische Ausdruck Query steht für Abfrage.
• SQL wurde in den siebziger Jahren des letzten Jahrunderts entwickelt und gilt heute
als führende Sprache auf diesem Gebiet.
• Für SQL gibt es einen ISO-Standard. Die meisten aktuellen Datenbanksysteme verwenden mehr oder weniger grosse Teile dieses Standards.
• Wir werden für die folgenden Beispiele exemplarisch das weit verbreitete DatenR
banksystem MySQL
verwenden.
2. Grundstruktur von SQL-Abfragen
SELECT
FROM
[WHERE
merkmalsname [, merkmalsname ...],
tabellenname [, tabellenname ...],
selektionsbedingung ];
• SQL-Schlüsselwörter werden üblicherweise in Grossbuchstaben geschrieben. Auch
Kleinschreibung ist möglich.
• Vorsicht: Bei Tabellen- und Merkmalsnamen wird zwischen Gross- und Kleinschreibung unterschieden.
• Alle Angaben, die bei der Syntaxdefinition in eckigen Klammern [. . . ] stehen, können
weggelassen werden.
• Leerzeichen, Tabulatoren und Zeilenschaltungen können zur besseren Lesbarkeit
eingefügt werden.
• Eine SQL-Anweisung wird mit einem Semikolon (;) abgeschlossen.
1
3. Das ERM der Übungsdatenbank
4. Tabellenschema der Übungsdatenbank
• LIEFERANT(lid, name)
• ARTIKEL(aid, bezeichnung, preis, menge, einheit, lid)
• KUNDE(kid, nachname, vorname, strasse, plz, ort)
• VERKAUF(vid, kid, aid, datum, zeit)
Fremdschlüssel sind blau hervorgehoben.
5. Die Schnittstelle der Übungsdatenbank
http://efi.kantiriederer.ch/mysql/login.php
Username: sqlabfrage
Passwort: x3.kH;jY,97F
6. Projektionen (1)
SELECT
FROM
nachname, ort
kunde;
liefert eine Tabelle mit den Merkmalen nachname und ort aller Tupel aus der Tabelle
kunde.
nachname ort
Meier
Büren NW
Baumann
Stans
...
...
2
6. Projektionen (2)
Da man häufig alle Merkmalsnamen anzeigen möchten, kann man anstelle der Aufzählung
aller Merkmale auch das Zeichen * verwenden.
SELECT
FROM
kid
1
2
...
*
kunde;
nachname
Meier
Baumann
...
vorname
Noemi
Riccardo
...
strasse
Buchhölzlistrasse
Künzlistrasse
...
plz
6382
6370
...
ort
Büren NW
Stans
...
6. Projektionen (3)
SELECT
FROM
plz
6382
6370
6365
...
plz, ort
kunde;
ort
Büren NW
Stans
Kehrsiten
...
Diese Liste ist streng genommen gar keine Tabelle im Sinne des Relationenmodells.
Warum?
Es gibt kein Merkmal bzw. keine Merkmalskombination, die die Tupel innerhalb der Tabelle eindeutig identifiziert.
6. Projektionen (4)
Wird hinter SELECT das Schlüsselwort DISTINCT eingefügt, werden Duplikate eliminiert:
SELECT
FROM
plz
6382
6370
...
DISTINCT plz, ort
kunde;
ort
Büren NW
Stans
...
3
7. Qualifizierte Abfragen (1)
Mit Hilfe von Selektionsbedingungen lassen sich qualifzierte Abfragen durchführen.
Interessieren wir uns für alle Kunden, die in Stans wohnen, so lautet die entsprechende
SQL-Abfrage:
SELECT
FROM
WHERE
kid
2
8
...
*
kunde
ort = ’Stans’;
nachname
Baumann
Moser
...
vorname
Riccardo
Nathan
...
strasse
Künzlistrasse
Röschibachstrasse
...
plz
6370
6370
...
ort
Stans
Stans
...
7. Qualifizierte Abfragen (2)
Die Selektionsprädikate könne auch Ungleichheitsbeziehungen enthalten und mit den logischen Operatoren AND, OR und NOT verknüpft werden:
SELECT *
FROM
artikel
WHERE preis > 3.0 AND (NOT lid = 6);
Welche Artikel sind teurer als 3 Franken und kommen nicht vom Lieferanten mit der ID
6?
aid
3
5
...
bezeichnung
Zopf
Salami
...
preis
4.50
4.20
...
menge
1
100
...
einheit
Stück
Gramm
...
lid
1
2
...
7. Qualifizierte Abfragen (3)
Durch Angabe von Merkmalsnamen wird die Selektion mit einer Projektion verknüpft.
Interessiert man sich im letzten Beispiel nur für die Bezeichnung, so lautet die Abfrage:
SELECT bezeichnung, preis, lid
FROM
artikel
WHERE preis > 3.0 AND (NOT lid = 6);
bezeichnung
Zopf
Salami
Schweinssteak
...
preis
4.50
4.20
5.00
...
lid
1
2
2
...
4
7. Qualifizierte Abfragen (4)
Enthält eine Spalte Zahlenwerte, können einfache Berechnungen damit durchgeführt werden.
Zudem ermöglicht das Schlüsselwort AS die Vergabe eines neuen Namens (Alias) für die
Spaltenüberschrift.
SELECT bezeichnung, preis * 0.025 AS "MWSt"
FROM
artikel;
bezeichnung
Ruchbrot
Gipfeli
Zopf
...
MWSt
0.06250
0.02500
0.11250
...
8. Abfragen mit Zeichenerkennung (1)
Das %-Zeichen steht für eine beliebige Zeichenkette von einem oder mehreren Buchstaben. Mit dem Schlüsselwort LIKE kann in der WHERE-Klausel nach Zeichenketten gesucht
werden, die bestimmte Zeichen enthalten.
SELECT *
FROM
lieferant
WHERE name LIKE ’M%’;
lid name
2
Metzgerei Stierli
4
Molkerei Senn
8. Abfragen mit Zeichenerkennung (2)
Der Unterstrich ( ) steht für genau ein (beliebiges) Zeichen. Mit dem Schlüsselwort LIKE
kann in der WHERE-Klausel nach Zeichenketten bestimmter Länge gesucht werden.
SELECT *
FROM
artikel
WHERE bezeichnung LIKE ’B_____’;
Mit der obigen Abfrage suchen wir nach Artikelbezeichnungen, die mit einem ’B’ beginnen
und aus genau 6 Zeichen bestehen.
aid bezeichnung
9
Banane
14
Butter
preis
2.70
2.70
menge einheit
lid
1
Kilogramm 3
250
Gramm
4
5
9. Abfragen mit Aggregatsfunktionen (1)
Neben den Operatoren der Relationenalgebra existieren in SQL auch sogenannte eingebaute Funktionen, die in der SELECT-Klausel verwendet werden können. Sie gelten für
jeweils eine Tabellenspalte.
• COUNT für die Zählung der Datenwerte
• SUM für die Summenbildung über die Datenwerte
• MAX und MIN um das Maximum bzw. Minimum zu bestimen
• AVG für die Durchschnittbildung (average)
9. Abfragen mit Aggregatsfunktionen (2)
Wie viele Artikel wurden im Monat Juli verkauft?
SELECT
FROM
WHERE
AND
COUNT(vid) AS "Anzahl Verkäufe im Juli"
verkauf
datum >= ’2011-07-01’
datum <= ’2011-07-31’;
Anzahl Verkäufe im Juli
330
9. Abfragen mit Aggregatsfunktionen (3)
Wie viel kostet unser billigster Artikel?
SELECT MIN(preis) AS "Preis"
FROM
artikel;
Preis
0.60
6
10. Abfragen mit dem Verbundoperator (1)
Datenbankabfragen sind besonders interessant, wenn dabei mehrere Tabellen verknüpft
werden. Wir möchten zum Beispiel wissen, wie viele Packungen Spaghetti (à 500 g) seit
dem Bestehen der Datenbank verkauft wurden.
SELECT
FROM
WHERE
AND
aid
19
19
...
*
artikel, verkauf
bezeichnung = ’Spaghetti’
artikel.aid = verkauf.aid;
bezeichnung
Spaghetti
Spaghetti
...
preis
1.80
1.80
...
menge
500
500
...
einheit
Gramm
Gramm
...
...
...
...
...
10. Abfragen mit dem Verbundoperator (2)
Möchte man die Anzahl der Datensätze nicht von Hand zählen, verwendet man von Vorteil
die Aggregationsfunktion COUNT, der wir bereits weiter oben begegnet sind.
SELECT
FROM
WHERE
AND
COUNT(artikel.aid) AS "Spaghettipackungen"
artikel, verkauf
bezeichnung = ’Spaghetti’
artikel.aid = verkauf.aid;
Spaghettipackungen
26
11. Sortieren und Gruppieren von Tupel-Einträgen (1)
Gemäss Definition ist die Reihenfolge der Tupel in einer Tabelle unwesentlich. Oft möchte
man aber, dass die Resultattabelle nach einem bestimmten Merkmal sortiert dargestellt
wird. Dies erreicht man mit dem Schlüsselwort ORDER BY, gefolgt vom einem Merkmalsnamen und einem Zusatz, der die Art der Sortierung präzisiert: ASC = aufsteigend (Voreinstellung), DESC = absteigend.
SELECT *
FROM
kunde
ORDER BY nachname DESC;
kid nachname
vorname strasse
plz
ort
22
Zimmermann Ben
Schanzeneggstrasse 6370 Stans
... ...
...
...
...
...
7
11. Sortieren und Gruppieren von Tupel-Einträgen (2)
Es gibt Situationen, in denen man innerhalb der Resultattabelle die Tupel nach bestimmten Kriterien zusammenfassen möchte. Dazu dient das Schlüsselwort GROUP BY.
Wie viele Artikel werden von jedem Lieferanten geliefert?
SELECT
lid, COUNT(lid) AS "Anzahl Artikel"
FROM
artikel
GROUP BY lid;
id
1
2
...
Anzahl Artikel
3
4
...
11. Sortieren und Gruppieren von Tupel-Einträgen (3)
Wenn man anstelle der Lieferanten-Nummer die konkreten Lieferanten-Namen angezeigt
haben möchte, muss man zusätzlich den Verbund-Operator anwenden.
SELECT
FROM
WHERE
GROUP BY
name, COUNT(lieferant.lid) AS "Anzahl Artikel"
artikel, lieferant
artikel.lid = lieferant.lid
lieferant.lid;
name
Bäckerei Müller
Metzgerei Stierli
...
Anzahl Artikel
3
4
...
11. Sortieren und Gruppieren von Tupel-Einträgen (4)
Aus den mit GROUP BY zusammengefassten Tupeln können wir mit dem Zusatz HAVING ein
Auswahl treffen. Dabei ist zu beachten, dass dieses Auswahlkriterium nur in Verbindung mit
Aggregatsfunktionen zulässig ist.
Wie viele Kunden haben mehr als 30 Artikel gekauft?
SELECT
FROM
GROUP BY
HAVING
v.kid, COUNT(v.vid) AS "Anzahl Artikel"
verkauf AS v
v.kid
COUNT(v.vid) > 30;
Mit AS können auch Aliase für Tabellennamen erzeugt werden. Diese sind im ganzen SQL-Statement gültig.
kid
29
53
68
Anzahl Artikel
37
36
31
8
12. Vereinigung
Der entsprechende Operator heisst UNION. Für eine Vereinigung müssen die Tabellen vereinigungsverträglich sein.
Welche Artikel stammen von Lieferant 3 oder von Lieferant 5?
(SELECT * FROM artikel WHERE lid = 3)
UNION
(SELECT * FROM artikel WHERE lid = 5);
Die Klammern können auch weggelassen werden, da UNION schwächer bindet als SELECT.
aid
8
9
...
16
bezeichnung
Apfel
Banane
...
Ei
preis
2.30
2.70
...
0.60
menge
1
1
...
1
einheit
Kilogramm
Kilogramm
...
Stück
lid
3
3
...
5
13. Durchschnitt und Differenz
MySQL kennt keine direkte Unterstützung für die SQL-Mengenoperationen
• INTERSECT und
• EXCEPT (oder MINUS)
Daher müssen diese Operationen über einen Umweg gebildet werden. Das ersparen wir
uns aber.
14. Kartesisches Produkt
Das kartesische Produkt ist nichts anderes als ein Verbund mit einem leeren Prädikat.
Achtung: Ohne Prädikat führt das kartesische Produkt schnell zu sehr grossen Produkt”
tabellen“.
SELECT *
FROM
artikel, lieferant;
Wie viele Zeilen hat das obige kartesische Produkt aus den Tabellen artikel und lieferant?
243 = 27 · 9
9
15. Geschachtelte Abfragen
Manchmal ist es nötig, eine SQL-Abfrage innerhalb einer anderen zu formulieren.
In einem früheren Beispiel haben wir den tiefsten Preis bestimmt, den ein Artikel in
unserem Sortiment haben kann. Möchte man auch noch wissen, um welchen (welche)
Artikel es sich handelt, müssen SQL-Abfragen geschachtelt werden:
SELECT
FROM
WHERE
bezeichnung, preis
artikel
preis >= ALL (SELECT preis
FROM
artikel);
bezeichnung
Waschmittel
preis
12.00
16. Was es sonst noch gibt
• Tabellen erzeugen und löschen (CREATE TABLE, DROP TABLE)
• Datensätze einfügen und löschen (INSERT, DELETE)
• Datensätze verändern und akutalisieren (ALTER, UPDATE)
• Tabellen mischen (MERGE)
• Benutzerrechte vergeben (GRANT)
• usw.
Quelle
• A. Meier, Relationale und postrelationale Datenbanken, Springer Berlin, 2007
10