Kleine Einf ¨uhrung in SQL - Institut für Computerlinguistik

Kleine Einführung in SQL
c
Clab-Team
Institut für Computerlinguistik
Universität Zürich
Dez. 2008
Zusammenfassung
Die Structured Query Language, kurz SQL, ist eine Datenbanksprache, die sowohl zur Abfrage, als auch zur Manipulation von
Daten in einer relationalen Datenbank dient.
Kleine Einführung in SQL
1
Voraussetzungen
1
Perl auf Basis des abstrakten Datenbank-Layers
DBI zwar möglich, aufgrund der unterschiedli-
Über das grundsätzliche Ziel des Einsatzes von
L SQL sollten Sie informiert sein, bevor Sie diese
chen SQL-Implementierungen aber nur schwer
zu realisieren ist.
Lerneinheit benützen, ansonsten werden keine
Kenntnisse vorausgesetzt.
2
Lernziel
4
Grundlegendes
In einer relationalen Datenbank werden Informationen in Tabellen gespeichert. Jede Tabelle
SQL ist relativ leicht zu erlernen. In dieser
speichert zu unterschiedlichen Datensätzen (ho-
Lerneinheit finden Sie eine kleine Einführung
rizontal) diverse Informationen (vertikal).
in die Sprache und ihre Verwendung. Die-
Ein Beispiel für eine Datentabelle:
se Einführung ist knapp und beschränkt sich
auf das Wesentliche, ist aber für einen Einstieg ausreichend. Sie stammt von Markus Wolf,
Böblingen. Das Original findet sich auf seiner
Website unter http://www.perlunity.de/
perl/datenbanken/sql.shtml.Am Schluss
Mitarbeiter
MID
Nachname
Vorname
Standort
Gehalt
123
Wolf
Markus
Böblingen
30000
456
Simpson
Homer
Springfield
120000
789
Schmidt
Werner
Bonn
75000
der Lerneinheit finden Sie eine ILAP, in der Sie
das Gelernte vertiefen können. Sie sollten nach
Um Daten in diese Tabelle einzugeben, zu
Durcharbeiten dieser Lerneinheiten einfache L
ändern, zu löschen und abzufragen, kann man
Anfragen an ebenso einfache Datenbanken feh-
die Structured Query Language, kurz SQL, ver-
lerlos in SQL formulieren können. Sie sollten
wenden.
ausserdem die Probleme erkannt haben, wel-
Unser Streifzug durch SQL beginnt bei einfa-
che sich beim Umsetzen von natürlichsprachlich
chen Abfragen über eine Tabelle, Abfragen mit
formulierten Informationsbedürfnissen in SQL
Kriterien und schliesslich Abfragen über meh-
ergeben.
rere Tabellen, so genannte Joins. Abschliessend
werden die Daten einer Tabelle manipuliert, d.h.
3
Einleitung
Daten werden
• hinzugefügt,
Die Structured Query Language, kurz SQL,
wird verwendet, um relationale Datenbanken
anzusprechen. SQL ist sehr umfangreich und
• geändert, und
• gelöscht.
umfasst nicht nur Anweisungen und Befehle
zum Abfragen der Daten - wie man aus dem
Namen schliessen könnte - sondern auch die
Möglichkeiten, Daten zu ändern oder die Datenbank selbst zu beschreiben. Somit kann man mit
SQL so ziemlich alle Aufgaben erledigen, die
zur Administration und Benutzung von Datenbanken anfallen.
Hier sei noch erwähnt, dass es einen SQLStandard gibt, an dem sich die Datenbankher-
5
Abfragen mit SELECT
Eine Abfrage dient dem Suchen und Anzeigen
von vorhandenen Daten. Dies geschieht in SQL
immer mit dem SELECT-Befehl.
Wir wollen aus der Beispieltabelle Mitarbeiter den Namen und Standort aller Mitarbeiter erfahren:
steller leider nur orientieren, aber sich nicht dar-
SELECT Nachname, Vorname, Standort
an halten. Dies hat zur Folge, dass eine da-
FROM Mitarbeiter;
tenbankunabhängige Software-Entwicklung mit
So sieht das Ergebnis aus:
Kleine Einführung in SQL
2
Nachname
Vorname
Standort
SELECT Nachname
Wolf
Markus
Böblingen
FROM Mitarbeiter
Simpson
Homer
Springfield
WHERE Gehalt >= 50000;
Schmidt
Werner
Bonn
die folgenden Datensätze zurückgeliefert:
Was genau haben wir getan: Wir haben uns alle
Nachname
Mitarbeiter aus der Tabelle Mitarbeiter zur An-
Simpson
sicht ausgewählt, und zwar deren Namen und
Schmidt
Standort. Die Mitarbeiter-Nummer (MID) haben
wir dabei ausser Acht gelassen.
Es wurden also alle Mitarbeiter, deren Gehalt
Eine Abfrage funktioniert generell also so:
gleich ist oder über 50000 liegt, ausgewählt. Ein
anderes Beispiel:
SELECT SpaltenName, SpaltenName, ...
FROM TabellenName;
SELECT Gehalt
FROM Mitarbeiter
Um alle Informationen, die in einer Tabelle gespeichert sind, zu sehen, benutzt man folgenden
Ausdruck:
WHERE Nachname = ’Simpson’;
Das Resultat:
Gehalt
SELECT *
FROM TabellenName;
6
SELECT mit Abfragekriterien
120000
7
Mehrere Kriterien in einer
Abfrage
Man kann Kriterien mit logischen OperatoUm eine Abfrage durchzuführen, die nicht
ren verknüpfen, und damit sehr detaillier-
alle Datensätze in der Tabelle zurückliefert,
te und aussagekräftige Abfragen durchführen.
benötigen wir Abfragekriterien. Anhand dieser
Beispiel:
Kriterien werden die Datensätze gefiltert. Die
allgemeine Form einer Abfrage mit Kriterien:
SELECT Nachname, Vorname
FROM Mitarbeiter
SELECT SpaltenName,SpaltenName, ...
WHERE Gehalt > 50000 AND Standort
!= ’Springfield’;
FROM TabellenName
WHERE Kriterium
Wie man im Resultat sieht, wäre Homer
Der Aufbau des Kriterienausdrucks leitet
Simpson aufgrund seines Gehaltes, das grösser
sich von der relationalen Algebra ab. Folgende
als 50000 ist, im Resultat enthalten, allerdings
Operatoren gibt es in SQL, um vergleichende
wurde sein Standort Springfield als Ausschlus-
Ausdrücke zu bilden:
skriterium angegeben:
=
gleich
Nachname
Vorname
<> oder !=
nicht gleich
Schmidt
Werner
<
kleiner als
>
grösser als
<=
kleiner als oder gleich
>=
grösser als oder gleich
Aus der Tabelle Mitarbeiter werden mit der Abfrage
Die beiden Kriterien wurden mit AND verknüpft, das entspricht dem logischen UND (∧).
Das logische ODER (∨) wird durch OR ausgedrückt.
Eine andere Möglichkeit, Aussagen miteinander zu verknüpfen, bieten die Schlüsselwörter
Kleine Einführung in SQL
3
IN und BETWEEN. IN wird verwendet, um in
Das Resultat dieser Anfrage mit LIKE lautet:
Spalten, die Zeichenketten beinhalten, eine L
Nachname
Menge von möglichen Kriterien anzugeben. Für
Schmidt
die folgende SQL-Anfrage werden Nachname
und Vorname eines Datensatzes zurückgeliefert,
Simpson
wenn die in der Klammer angegebene Zeichenkette (nach IN) in der Spalte Standort des Datensatzes vorhanden ist.
Das Prozentzeichen in der Anfrage ist dabei als Wildcard zu sehen, repräsentiert also alle möglichen Zeichenfolgen. Im Folgenden noch
SELECT Nachname, Vorname
ein paar Beispiele für den Einsatz von Wild-
FROM Mitarbeiter
cards: ’Home%’, ’%ome%’, ’%omer’.
WHERE Standort IN (’Böblingen’, ’Bonn’);
Das Resultat dieser Anfrage lautet also:
8
Schlüssel und Indizes
Nachname
Vorname
Damit man dieselben Daten nicht mehrmals
Wolf
Markus
in unterschiedlichen Tabellen speichern muss,
Schmidt
Werner
werden sie indiziert. Dazu bekommt ein Datensatz in einer Tabelle einen Primärschlüssel
Für Zahlenwerte gibt man ein Zahlenintervall
(primary key). Dieser besteht aus einer oder
mit BETWEEN an:
mehreren Spalten. Auf jeden Fall muss der
SELECT Nachname, Vorname
Primärschlüssel eines Datensatzes in dieser Ta-
FROM Mitarbeiter
belle eindeutig sein, d.h. es darf keinen anderen
WHERE Gehalt BETWEEN 20000 AND 50000;
Datensatz mit demselben Schlüssel geben.
Man spricht von einem Fremdschlüssel (for-
Das Resultat dieser Anfrage mit BETWEEN lau-
eign key) bei einer Tabellenspalte, die in einer an-
tet:
deren Tabelle ein Primärschlüssel ist. Das heisst,
Nachname
Vorname
Wolf
Markus
Man kann den ganzen Ausdruck mit NOT
negieren, es werden also die Datensätze abgefragt, die nicht diesen Kriterien entsprechen:
SELECT Nachname, Vorname
FROM Mitarbeiter
WHERE Standort NOT IN (’Böblingen’, ’Bonn’);
Das Resultat dieser Anfrage mit NOT IN lautet:
Nachname
Vorname
Simpson
Homer
Und damit bekommen wir bei dieser Abfrage wieder Homer Simpson. Ein weiteres
Schlüsselwort ist LIKE:
SELECT Nachname
FROM Mitarbeiter
WHERE Vorname LIKE ’H%’;
die beiden Tabellen haben miteinander zu tun,
ihre Datensätze sind untereinander verknüpft.
Für die im nächsten Kapitel behandelten JoinBeispiele gelten folgende Beispieltabellen:
Kleine Einführung in SQL
4
Artikel
ArtikelNr.
Bezeichnung
Preis
0010
Kaffeebecher
5
0020
T-Shirt
20
0030
Mütze
10
0040
Sweatshirt
45
Kunden
KundenNr.
Nachname
Vorname
Strasse
Stadt
99100
Meier
Richard
Hauptstr. 1
70000 Stuttgart
43689
Banner
Werner
Moosweg 5
12334 Aachen
32074
Zeller
Stefanie
Zollstr. 41
09137 Grenzdorf
77077
Schwarz
Eugen
Bahnhofstr. 13
83990 Fulda
BestellNr.
KundenNr.
ArtikelNr.
Menge
1234
99100
0020
5
3645
43689
0010
16
1234
99100
0040
2
9834
32074
0030
9
Bestellungen
In diesem Beispiel hat eine Bestellung zwei
als Fremdschlüssel gespeichert, und dadurch
Fremdschlüssel: zum einen einen Käufer über
weiss ich, welcher Kunde welche Bestellung
die Kundennummer, zum anderen einen Arti-
aufgegeben hat.
kel über die Artikelnummer. Wichtig ist, dass in
dieser Tabelle kein Primärschlüssel vorhanden
ist. Die Bestellnummer kommt doppelt vor, wie
man sieht. Mit diesen Tabellen werden wir nun
kompliziertere Abfragen durchführen: Joins.
Und damit ins Geschehen: wir wollen alle Bestellungen sehen, die Herr Meier aufgegeben
hat:
SELECT BestNr, ArtikelNr, Menge
FROM Bestellungen, Kunden
9
Joins
Es gibt zwei Voraussetzungen, um miteinander
WHERE Bestellungen.KundenNr =
Kunden.KundenNr AND
Nachname = ’Meier’;
in Verbindung stehende Tabellen optimal einzu-
Zu beachten ist in der WHERE-Klausel die No-
setzen:
tation TabellenName.Spaltenname. Dadurch wer-
1. Redundanzfreiheit: Dieselben Daten dürfen
nicht doppelt in unterschiedlichen Tabellen
den die Spalten aus den beiden Tabellen nicht
verwechselt. Das Ergebnis sieht so aus:
Bestellungen
gespeichert werden. Es wäre beispielweise
überflüssig, in der Tabelle Bestellungen die Da-
Bestnr.
Artikelnr.
Menge
ten des Kunden zu speichern, da diese bereits
1234
0020
5
in der Tabelle Kunden erfasst wurden.
1234
0040
2
2. Schlüssel: Woher weiss ich, welcher Kun-
Es gibt Kunden, die noch keine Bestellung
de zu welcher Bestellung gehört? Seine
aufgegeben haben. Die aber, die schon einmal
KundenNr, die in der Tabelle Kunden der
etwas bestellt haben, sollen einen Bonus bekom-
Primärschlüssel ist, wird in der Bestellung
men. Dazu wollen wir herausfinden, wer das ist:
Kleine Einführung in SQL
5
SELECT DISTINCT Kunden.KundenNr,
Nachname, Vorname
FROM Bestellungen, Kunden
WHERE Bestellungen.KundenNr
• COUNT(*): Liefert die Anzahl der Datensätze,
die den Abfragekriterien entsprechen
Ein paar Beispiele:
= Kunden.KundenNr
SELECT SUM(Menge), AVG(Menge)
ORDER BY Nachname, Vorname;
FROM Bestellungen
Normalerweise würde Herr Meier im Ergeb-
Berechnet die Anzahl der Artikel, die bestellt
nis zweimal auftauchen, da er ja zwei Bestellun-
wurden, sowie die durchschnittliche Anzahl, die
gen aufgegeben hat. Mit dem Schlüsselwort DI-
von einem Artikel bestellt wurde.
STINCT wird das aber vermieden, er taucht nur
einmal auf. DISTINCT filtert also Duplikate un-
SELECT MAX(Menge), MIN(Menge)
ter den angeforderten Datensätzen heraus. Um
FROM Bestellungen
noch eins draufzusetzen, haben wir das Ergebnis nach Nachnamen und Vornamen sortieren
lassen, und zwar durch ORDER BY. Das Ergebnis dieser Anfrage sieht so aus:
Kunden
Liefert die Bestellung mit der grössten Menge
und diejenige mit der kleinsten Menge von Artikeln .
SELECT COUNT(*)
FROM Bestellungen
Kundennr.
Nachname
Vorname
43689
Banner
Werner
99100
Meier
Richard
Zählt alle Bestell-Positionen, bei denen mehr als
32074
Zeller
Stefanie
10 Stück von einem Artikel bestellt wurden.
WHERE Menge > 10
Dies also eine kleine Einführung in die Joins,
damit kommt man schon mal weit. Natürlich
geht’s aber noch weitaus komplexer.. Wer
sich dafür interessiert, bzw. tiefer einsteigen
will, findet z.B. bei Wikipedia (http://en.
wikipedia.org/wiki/SQL mehr Informationen.
11
Daten manipulieren
Mit Abfragen lassen sich die Informationen
in einer Datenbank nach Belieben sortieren
und anzeigen. Wie kommen die Daten aber in
die Datenbank? Wie werden sie geändert und
gelöscht? Mit SQL geht das relativ einfach. Als
Beispiel gilt die Tabelle Artikel mit den Spalten
10
Funktionen
5 wichtige Funktionen, die in SQL eingebaut
sind, sollen hier erläutert werden:
ArtikelNr, Bezeichnung und Preis.
11.1
Daten hinzufügen
Zuerst fügen wir einen Datensatz in die Tabelle
• SUM(SpaltenName): Addiert alle Werte in die-
Artikel hinzu:
ser Spalte, wenn es Zahlenwerte sind
INSERT INTO Artikel
• AVG(SpaltenName): Berechnet das arithmeti-
VALUES (’0090’,’duschvorhang’,44.95);
sche Mittel der Werte dieser Sparte
• MAX(SpaltenName): Liefert den grössten Wert
dieser Spalte
• MIN(SpaltenName): Liefert den kleinsten Wert
dieser Spalte
In diesem Fall wird ein neuer Datensatz in
die Tabelle eingefügt. Man kann die Reihenfolge der Werte auch verändern, dann muss aber
auch angegeben werden, welche Spalten man
meint. Dabei lassen wir dieses Mal den Preis mal
aus, weil er noch nicht feststeht (auslassen kann
Kleine Einführung in SQL
6
man alles, ausgenommen die Primärschlüssel,
Lerneinheit, die Formulierung relativ einfacher
die immer vorhanden sein müssen - in diesem
Anfragen in SQL üben. Hier sollen Sie also SQL
Fall ist dies die ArtikelNr).
“in Isolation” erlernen.
(ILAP) Praktische Einführung in SQL
INSERT INTO Artikel
In der zweiten ILAP wird das Augenmerk
(Bezeichnung, ArtikelNr)
auf das Verhältnis zwischen natürlichsprachlich
VALUES (’duschvorhang’,’0090’);
formulierten Informationsbedürfnissen und den
entsprechenden Eingaben in SQL gelegt. Hier
11.2
Daten ändern
sollen Sie Ihr Gefühl dafür schärfen, wo SQL
wir ihn natürlich nachträglich einfügen, indem
einfacher ist als Englisch, und wo es sich umgekehrt verhält:
wir einen bestehenden Datensatz ändern:
(ILAP) SQL und natürliche Sprache
Wenn wir den Preis leergelassen haben, können
UPDATE Artikel
SET Preis = 44.95
WHERE ArtikelNr = ’0090’;
Dies ist natürlich auch möglich, wenn die zu
ändernden Daten bereits bestehen, und auch,
wenn es mehrere sind:
UPDATE Artikel
SET Preis = 44.95
WHERE Preis = 49.95
Dadurch werden alle Artikel, die bisher 49.95
gekostet haben, nun billiger.
11.3
Daten löschen
Zu guter Letzt wollen wir die Daten wieder
löschen:
DELETE FROM Artikel
WHERE ArtikelNr = ’0090’;
Damit wird genau ein Datensatz gelöscht,
nämlich der Duschvorhang, den wir vorhin eingefügt haben. Um alle Duschvorhänge, die in
der TabelleArtikel gespeichert sind, zu löschen,
gehen wir wie folgt vor:
DELETE FROM Artikel
WHERE Bezeichnung = ’duschvorhang’;
12
ILAPs zu SQL
Sie finden im folgenden zwei ILAPs. In der ersten werden Sie, anhand der Beispiele in dieser