SQL-Einführung

SQL-Einführung
Skript zur grundlegenden Orientierung innerhalb des Datenbestandes mittels SQL. Unterstrichene
Begriffe können für weitere Details in Wikipedia eingegeben werden (www.wikipedia.at).
Einführung Datenbanken
Grundsätzlich dient jedes Datenbanksystem (DBS) dazu, große Datenmengen effizient,
widerspruchsfrei und dauerhaft zu speichern.
Im Zuge der Weiterentwicklung der EDV haben sich aber auch Datenbanken über die Jahre verändert.
Waren früher viele hierarchische oder proprietäre Systeme (also diverse Eigenentwicklungen, vor
allem auf Großrechnerebene) vorrangig, so haben sich bereits in den 70ern sogenannte relationale
Datenbanksysteme durchgesetzt. Relationale Datenbanken bestehen grundsätzlich aus Tabellen,
welche durch Beziehungen miteinander in Verbindung stehen. Die Erstellung eines solchen
Tabellenkonzeptes nennt man Datenmodellierung, das Ergebnis ein Datenbankmodell.
Einführung SQL
SQL bedeutet Structured Query Language und wurde mit der Entstehung von relationalen
Datenbanksystemen (RDBMS) entwickelt. Es handelt sich dabei um einen Abfragestandard für
Tabelleninhalte – wobei leider jede Datenbankumgebung im Laufe der Jahre ihren eigenen „Dialekt“
entwickelt hat. Es gibt also viele Gemeinsamkeiten aber eben auch einige Unterschiede, die vor allem
im Detail zu finden sind. Im Folgenden wird speziell auf die MSSQL-Syntax eingegangen, wobei
Unterschiede zu anderen Datenbanksystemen bei Bedarf angeführt werden.
Beispieldaten
Um die Erläuterungen in diesem Skript besser verständlich zu machen, soll nachfolgendes einfaches
Datenbankschema dienen:
Titel
Id
Name
Person
Id
Vorname
Nachname
Ref_Titel_Id
Ref_Adresse_Id
Geburtsdatum
Adresse
Id
Strasse
Hausnummer
Ref_Ort_Id
Ort
Plz
Name
Die Basistabelle beinhaltet Personen. Diese Personen haben unter Umständen einen Titel. Damit
dieser nicht hundertfach zu jeder Person erfasst werden muss, wird der Text in eine eigene Tabelle
ausgelagert und nur die Referenz zum jeweiligen Titel gespeichert. Ebenso verhält es sich mit den
Adressen. Da mehrere Personen an einer Adresse wohnen können, werden diese Anschriften nur ein
mal gespeichert und über die Referenzen eingebunden. Innerhalb der Adressen sind wiederum die
Orte mehrfach vorhanden und daher ebenfalls ausgelagert.
Diese Auslagerung von Daten und deren Verbindung durch Relationen dient dazu Redundanzen
(mehrfach vorkommende Daten selben Inhalts) zu verhindern. Man nennt dies Normalisierung.
Seite 1 von 7
Grundsätzlicher Aufbau eines SQL-Statements
SELECT feld(er)
FROM tabelle [ ,...tabelle ]
[ [ INNER | LEFT | RIGHT | OUTER ] JOIN ] tabelle ON (tabelle1.feld = tabelle2.feld)
WHERE bedingung(en)
GROUP BY gruppierfeld [ ,...gruppierfeld ]
ORDER BY sortierfeld [ ,...sortierfeld ] ;
SELECT
Jede Abfrage beginnt mit den Schlüsselwort SELECT. Mit SELECT * kann man alle Spalten einer
(oder mehrerer) Tabellen auslesen. Dies ist aber aus Performancegründen bei großen Datenmengen
nicht zu empfehlen. Besser ist die gezielte Angabe der gewünschten Spalten.
FROM
Um dem Datenbanksystem mitzuteilen, in welcher Tabelle die im SELECT angegebenen Felder zu
finden sind, gibt es das Schlüsselwort FROM. Hier wird eine Tabelle angegeben.
Beispiel:
SELECT Vorname, Nachname
FROM Person;
JOIN
Wenn die gewünschten Daten über mehrere Tabellen verteilt sind, so müssen diese entsprechend
miteinander verknüpft werden. Dazu dient das JOIN-Schlüsselwort. Zu jedem JOIN müssen die
Verknüpfungsparameter angegeben werden (ON ...). Es können auch mehrere Tabellen verknüpft
werden. Hierbei können verschiedene Arten der Verknüpfung angegeben werden:
INNER JOIN
Liefert nur Datensätze, die in beiden Tabellen vorkommen.
SELECT Titel.Name, Person.Vorname, Person.Nachname
FROM Person
INNER JOIN Titel ON (Titel.Id = Person.Ref_Titel_Id);
Liefert nur Datensätze aus Personen, die auch einen Titel haben.
Seite 2 von 7
OUTER JOIN
Liefert alle Datensätze aus beiden Tabellen. Leere Felder werden hierbei mit NULL befüllt.
SELECT Titel.Name, Person.Vorname, Person.Nachname
FROM Person
OUTER JOIN Titel ON (Titel.Id = Person.Ref_Titel_Id);
Liefert alle verknüpften Datensätze aus Personen und Titeln! Es kann Personen geben, die
keinen Titel haben – aber auch Titel ohne Vor- und Nachnamen!
LEFT JOIN
Aus der auf der linken Seite angeführten Tabelle werden alle Datensätze selektiert.
SELECT Titel.Name, Person.Vorname, Person.Nachname
FROM Person
LEFT JOIN Titel ON (Titel.Id = Person.Ref_Titel_Id);
Liefert alle Personen (= „linke Tabelle“) – manche davon haben jedoch keinen Titel.
RIGHT JOIN
Aus der auf der rechten Seite angeführten Tabelle werden alle Datensätze selektiert.
SELECT Titel.Name, Person.Vorname, Person.Nachname
FROM Person
RIGHT JOIN Titel ON (Titel.Id = Person.Ref_Titel_Id);
Liefert alle Titel (= „rechte Tabelle“) – manche davon haben jedoch keinen Vor- oder
Nachnamen.
Ein Komplettbeispiel für unsere Beispieldatenbank würde demnach so aussehen:
SELECT t.Name, p.Vorname, p.Nachname, a.Strasse, a.Hausnummer, o.Plz, o.Name
FROM Person p
LEFT JOIN Titel t ON (t.Id = p.Ref_Titel_Id)
LEFT JOIN Adresse a ON (a.Id = p.Ref_Adresse_Id)
LEFT JOIN Ort o ON (o.PLZ = a.Ref_Ort_Id) ;
Dieses Statement liefert alle Personen mit ihrem Titel, der Adresse und dem zugehörigen Ort.
Sind Felder einer oder mehrerer verknüpften Tabellen leer, werden diese mit NULL ausgegeben.
Seite 3 von 7
WHERE
Über das Schlüsselwort WHERE werden Daten genauer eingeschränkt und gefiltert. Dabei können
auch mehrere Bedingungen kombiniert werden. Es gelten hierbei die Klammernregeln. In einigen
Datenbankensystemen ist der Feldtyp nicht so ausschlaggebend und man kann grundsätzlich jede
Bedingung in Hochkomma setzen. Bei MSSQL ist jedoch der Feldtyp relevant und man muss diesen
auch in den Bedingungen berücksichtigen:
Zahlen werden ohne Hochkomma angegeben. Bei Dezimalzahlen ist ein Hochkomma jedoch zulässig
(damit das Komma nicht als Feldseparator interpretiert wird).
Text ist immer in Hochkomma zu setzen.
Datumswerte sind besonders zu behandeln. Hier ist auf das Datenformat der Datenbank zu achten!
Bedingungsoperatoren:
=, <, >, <=, >=, LIKE, IN, NOT IN, BETWEEN
SELECT t.Name, p.Vorname, p.Nachname, a.Strasse, a.Hausnummer, o.Plz, o.Name
FROM Person p
LEFT JOIN Titel t ON (t.Id = p.Ref_Titel_Id)
LEFT JOIN Adresse a ON (a.Id = p.Ref_Adresse_Id)
LEFT JOIN Ort o ON (o.PLZ = a.Ref_Ort_Id)
WHERE (t.Name = ‘Ing.’ OR t.Name = ‘Dr.’)
AND (o.plz = 1230)
AND (p.Nachname LIKE ‘Hof%’ OR p.Nachname LIKE ‘Höf%’)
AND p.Geburtsdatum >= '20.11.1990’;
Liefert alle Personen, die entweder den Titel “Ing.“ oder „Dr.“ tragen, in 1230 wohnen, deren
Nachname mit „Hof...“ oder „Höf...“ beginnt und die am oder nach dem 20.11.1990 geboren wurden.
Achtung! Gibt man bei Datumsfeldern keine Uhrzeit mit an, wird vom System 00:00 Uhr angenommen.
Dies kann zu ungewüschten Effekten führen, wenn man zB genau auf ein bestimmtes Datum abfragen
möchte!
SELECT Vorname, Nachname
FROM Person
WHERE Geburtsdatum = '20.11.1990’;
Diese Abfrage liefert wahrscheinlich keine Ergebnismenge – es sei denn, eine Person hat tatsächlich
genau um 00:00 Uhr dieses Tages Geburtstag!
Seite 4 von 7
Hier empfiehlt sich folgende Abfrage:
SELECT Vorname, Nachname
FROM Person
WHERE Geburtsdatum >= '20.11.1990’
AND Geburtsdatum < '21.11.1990’;
GROUP BY
Gibt es mehrere Einträge zu bestimmten Suchkriterien, kann man diese nach Bedarf auch gruppiert
ausgeben lassen. Es werden dann Spalten mit gleichem Inhalt gemäß der Group-By-Angaben
zusammengefasst. Andere Spalten können mithilfe von Aggregatfunktionen kalkuliert werden (siehe
dazu Aggregatfunktionen weiter unten).
Wichtig hierbei ist, dass sämtliche Spalten, nach denen gruppiert werden soll auch in der Feldliste des
SELECTs stehen müssen!
SELECT Nachname, COUNT(Vorname)
FROM Person
GROUP BY Nachname
Liefert pro Nachnamen eine Zeile – in der zweiten Spalte steht dann jeweils die Anzahl der
Datensätze zu diesem Nachnamen.
ORDER BY
Um die Ausgabe entsprechend zu sortieren, gibt es die Order-By-Klausel. Hier werden wie auch
schon in der Select-Liste die Feldnamen getrennt durch Beistriche angeführt. Zustätzlich kann hier pro
Feld der Zusatz ASC (Ascending = aufsteigend) oder DESC (Descending = absteigend) angegeben
werden.
SELECT t.Name, p.Vorname, p.Nachname, a.Strasse, a.Hausnummer, o.Plz, o.Name
FROM Person p
LEFT JOIN Titel t ON (t.Id = p.Ref_Titel_Id)
LEFT JOIN Adresse a ON (a.Id = p.Ref_Adresse_Id)
LEFT JOIN Ort o ON (o.PLZ = a.Ref_Ort_Id)
WHERE (t.Name = ‘Ing.’ OR t.Name = ‘Dr.’)
AND (o.plz = 1230)
AND (p.Nachname LIKE ‘Hof%’ OR p.Nachname LIKE ‘Höf%’)
AND p.Geburtsdatum >= '20.11.1990’
ORDER BY p.Nachname ASC, p.Vorname DESC;
Seite 5 von 7
Selbe Ergebnismenge wie im WHERE-Beispiel – nun aber nach Nachnamen sortiert. Innerhalb von
gleichen Nachnamen werden die Vornamen absteigend ausgegeben (also beginnend mit „Z...“).
Aggregatfunktionen
TOP xx
Ausgabe auf xx Zeilen einschränken
AVG(Ausdruck)
Berechnet den Durchschnittswert einer Spalte, die durch den Ausdruck bestimmt wird
COUNT(Ausdruck)
Zählt die vom Ausdruck definierten Zeilen
COUNT(*)
Zählt alle Zeilen in der angegebenen Tabelle oder dem View
MIN(Ausdruck)
Findet den kleinsten Wert in der durch den Ausdruck angegebenen Spalte
MAX(Ausdruck)
Findet den größten Wert in der durch den Ausdruck angegebenen Spalte
SUM(Ausdruck)
Berechnet die Summe der durch den Ausdruck angegebenen Spaltenwerte
Beispiel:
SELECT TOP 100 COUNT(*), p.Vorname, p.Nachname, MIN(p.Geburtsdatum), AVG(o.Plz)
FROM Person p
LEFT JOIN Adresse a ON (a.Id = p.Ref_Adresse_Id)
LEFT JOIN Ort o ON (o.PLZ = a.Ref_Ort_Id)
GROUP BY p.Vorname, p.Nachname;
Listet maximal 100 Zeilen der gesamten Ergebnismenge. Die Ausgabe wird in Vorname + Nachname
gruppiert (also je eine Zeile für eine Vorname + Nachname-Kombination). Für jede Zeile wird die
Anzahl der Datensätze, das kleinste Geburtsdatum und der Durchschnitt der Postleitzahlen (wozu
auch immer) ausgegeben.
Seite 6 von 7
Eine typische Abfrage könnte also wie folgt aussehen:
Base-Select:
SELECT
Add-Select:
p.firstname AS 'Vorname',
p.lastname AS 'Nachname',
p.birthdate AS 'Geburtsdatum',
p.gender AS 'Geschlecht',
p.visit_date AS 'Erste Visite'
Base-From:
FROM WA_ADB_Patient p
INNER JOIN WC_ADB_CHPO c on (c.PatientID = p.PatientID)
Add-From:
Base-Where:
WHERE c.kst_id = '91631260'
AND c.storno = 0
Add-Where:
AND c.Visitendatum < '20.10.2008'
AND p.birthdate BETWEEN '01.01.1930' AND '31.12.2000'
AND p.lastname LIKE 'B%'
Order by:
ORDER BY p.lastname ASC
Seite 7 von 7