Johann Mitlöhner 2010-2014
SQL (Structured Query Language) ist eine standardisierte Sprache zur Manipulation von Daten in Datenbanken. In relationalen Datenbanken finden wir als wichtigste Objekte Tabellen. Eine Tabelle hat einen Namen und eine oder mehrere Spalten. Jede Spalte hat ebenfalls einen Namen und einen Datentyp. In eine Tabelle können Datensätze eingefügt werden, die auch als Tupel bezeichnet werden. Operationen mit Tabellen sind
Am Schulungsraum-Server arbeiten wir mit der persönlichen Datenbank-Kennung hmatnr, also z.B. h0012345. Passwort wird in der LV bekanntgegeben. Es wird das Open Source Datenbanksystem Postgres verwendet (Version 8.1!). Postgres können Sie auch auf Ihrem PC installieren, am besten unter Linux; dann unterscheidet sich Ihre Umgebung am eigenen PC und am Schulungsraumserver minimal bis gar nicht.
Um mit Postgres interaktiv zu arbeiten, geben Sie folgenden Befehl auf der Kommandezeile im Terminal ein (am Schulungsraumserver!):
psql
Wenn Sie das Password nicht jedesmal eingeben wollen, legen Sie mit einem Texteditor (z.B. pico/nano) eine Textdatei .pgpass in Ihrem home directory an, die eine Zeile mit folgendem Inhalt enthält, entsprechend DB account (NICHT das powernet password!):
*:*:*:dbuserid:dbpasswd
und dann:
chmod go-r .pgpass
Innerhalb des Interface zur Datenbank haben Sie eine history d.h. mit der Aufwärtstaste (Pfeil nach oben auf der Tastatur) können wir frühere Befehle wiederholen und ändern (Pfeil links/rechts). Dazu ist es sinnvoll, alle SQL-Anweisungen auf einer Zeile einzugeben. In diesen Unterlagen sind längere SQL Kommandos zur besseren Übersichtlichkeit auf mehrere Zeilen verteilt.
Dazu dient der Befehl CREATE TABLE, der Teil der DDL (data definition language) ist. Es wird der Name der Tabelle sowie ihre Spalten mit deren Namen und Datentypen genannt:
create table kunde (
nr integer primary key,
name char(40),
adresse char(80)
);
Im obigen Beispiel wird eine Tabelle kunde angelegt, die drei Spalten hat; die Spalten haben den Datentyp integer (ganzzahlig) bzw. char (character), der eine Längenangabe enthält, das ist die maximale Anzahl der Zeichen. Der Zusatz primary key bei der Spalte nr bewirkt zwei Dinge:
Nachdem die Tabelle angelegt ist, können Datensätze eingefügt werden. Die Feldlängen für Name und Adresse dürfen nicht überschritten werden. Natürlich darf auch die Nummer noch nicht vergeben sein.
insert into kunde (nr, name, adresse) values (10, 'Sigmund Freud', 'Berggasse 19, A-1090 Wien');
insert into kunde (nr, name, adresse) values (12, 'Radiobastler GmbH', 'Kalchberggasse 20, A-8010 Graz');
Es müssen nicht für alle Spalten Werte angegeben werden; im folgenden fehlt die Adresse d.h. sie wird in diesem Datensatz auf NULL gesetzt. Das ist besser, als einen willkürlichen Platzhalter wie Leerzeichen oder Bindestrich zu verwenden.
insert into kunde (nr, name) values (11, 'Peter Cilek');
Die Angabe der Spaltennamen kann auch weggelassen werden; dann müssen die Werte in der Reihenfolge der Definition beim CREATE TABLE angegeben werden:
insert into kunde values (17, 'Armin Berg', '1080 Josefsgasse 3/2');
Das kann Probleme verursachen, wenn z.B. die Tabelle nachträglich mit ALTER TABLE geändert wird. Daher ist die Variante mit Angabe der Spalten vor dem VALUES zu bevorzugen.
Mit dem Befehl
select können Datensätze abgefragt werden:
select * from kunde;
Es werden alle Datensätze angezeigt. Auch Einschränkungen sind möglich:
select * from kunde where nr = 12;
Nur Tupel mit entsprechenden Werten für die Spalte nr werden angezeigt.
select * from kunde order by name;
Sortierung nach Kundenname
Mit dem Befehl update können Datensätze geändert werden.
update kunde set name = 'Radiobastler GmbH' where nr = 12;
Im Datensatz mit der Nummer zwei wird das Feld Name geändert. Wird die Einschränkung where nr = 2 weggelassen, so werden alle Datensätze geändert!
Der Befehl delete dient zum Löschen von Datensätzen.
delete from kunde where nr = 10;
Sigmund Freud wird aus der Kundentabelle entfernt. Wird die Einschränkung where nr = 10 weggelassen, so werden alle Datensätze gelöscht (die leere Tabelle bleibt aber erhalten).
Damit der CREATE TABLE Befehl von der Datenbank erfolgreich ausgeführt werden kann, darf es noch keine Tabelle mit dem Namen 'kunde' geben. Falls eine solche schon existiert, kann sie mit dem Befehl
drop table kunde;
gelöscht werden. Im Gegensatz zu DELETE FROM werden Inhalte und Tabellenstruktur gelöscht.
Datenbanksystem erlauben in unterschiedlicher Weise einen Überblick über angelegte Objekte und deren Eigenschaften. Diese Befehle sind nicht SQL Standard, sondern DBMS-spezifisch. In Postgres z.B.
\d für eine Liste aller Objekte, und
\d kunde für Details zur Tabelle kunde.
Im Vergleich zur Kundentabelle gibt es nicht viel neues; das Format numeric(8,2) erlaubt zwei Nachkommastellen und insgesamt 8 Stellen:
create table artikel (
nr integer primary key,
bez char(30),
preis numeric(8,2),
bestand integer
);
insert into artikel values (1023, 'IR Ser Schnittstelle', 25.90, 4);
insert into artikel values (1045, 'SmartMedia 128 MB', 55.40, 6);
insert into artikel values (1017, 'HD IDE 120 GB 5400rpm', 105.90, 2);
Hier wird festgehalten, welche Artikel von wem bestellt werden. Das könnte man auch besser mit gesonderten Auftrags- und Auftragspositionstabellen lösen; wir entscheiden uns hier für die einfachste Variante.
create table auftrag (
kunde integer references kunde(nr),
artikel integer references artikel(nr),
menge integer,
datum date,
status char(1)
);
Die Spalte Kunde referenziert eine Spalte aus der Tabelle Kunde; Einträge mit Nummern, die nicht in der Kundentabelle vorkommen, sind nicht sinnvoll; ebenso für Artikel.
insert into auftrag (kunde, artikel, menge, datum) values (10, 1023, 20, '2010-04-01');
insert into auftrag (kunde, artikel, menge, datum) values (12, 1045, 10, '2010-01-31');
insert into auftrag (kunde, artikel, menge, datum) values (12, 1023, 10, '2010-09-26');
Mit dem date Format kann gerechnet werden (z.B. welche Aufträge sind in den letzten 10 Tagen eingelangt, mit der Postgres Funktion current_date). Datumswerte können in verschiedenen Formaten eingegeben werden, wir verwenden YYYY-MM-DD in einfachen Hochkommas.
Die references-Klausel definiert foreign keys d.h. Schlüssel aus anderen Tabellen; wenn wir versuchen, einen Auftrag mit einer Kundennummer anzulegen, die in der Kundentabelle nicht existiert, dann bekommen wir eine Fehlermeldung:
insert into auftrag (kunde, artikel, menge, datum) values (22, 1023, 10, '2010-09-26');
ERROR: insert or update on table 'auftrag' violates foreign key constraint 'auftrag_kunde_fkey'
DETAIL: Key (kunde)=(22) is not present in table 'kunde'.
Die Definition der foreign keys hat auch Konsequenzen für das Löschen in Kunde und Artikel:
delete from kunde where nr = 12;
ERROR: update or delete on 'kunde' violates foreign key constraint 'auftrag_kunde_fkey' on 'auftrag'
DETAIL: Key (nr)=(12) is still referenced from table 'auftrag'.
Wenn dieses Verhalten nicht gewünscht ist, kann beim CREATE TABLE der Tabelle AUFTRAG z.B. auch ON DELETE SET NULL verwendet werden:
... kunde integer references kunde(nr) on delete set null, ...
Dann kann der Kunde Nr 12 gelöscht werden, und die entsprechenden Referenzen in AUFTRAG werden auf NULL gesetzt:
delete from kunde where nr = 12 ;
DELETE 1
select * from auftrag;
kunde | artikel | menge | datum
-------+---------+-------+------------
10 | 1023 | 20 | 2010-04-01
| 1045 | 10 | 2010-01-31
| 1023 | 10 | 2010-09-26
Die Entscheidung, welche Optionen gesetzt werden bzw. ob foreign keys überhaupt definiert werden, hängt von der Aufgabe ab und will wohlüberlegt sein.
Eine Abfrage aller Aufträge mittels select * from auftrag liefert die eben eingegebenen Werte. Die Kundennummern sind dabei aber wenig aussagekräftig; wir würden an ihrer Stelle lieber die Namen der Kunden sehen. Wir können dazu die Auftragstabelle mit der Kundentabelle in einer Abfrage verbinden (join). Weil ein Feld mit der Bezeichnung 'nr' in beiden Tabellen vorkommt, verwenden wir die eindeutige Form Tabelle.Spalte:
select name, datum from kunde, auftrag where kunde.nr = auftrag.kunde;
Als Resultat liefert die Datenbank jene Kombinationen der Tupel aus den Tabellen Kunde und Auftrag, wo die Felder kunde.nr und auftrag.kunde identisch sind:
Sigmund Freud | 1999-04-01 |
Radiobastler GmbH | 1998-01-31 |
Radiobastler GmbH | 1998-09-26 |
Der Effekt dieser Abfrage ist die Expansion der Kundennummern aus der Auftragstabelle: statt wenig aussagekräftiger Nummern sehen wird die Namen der Kunden. Die WHERE Klausel selektiert aus allen möglichen Kombinationen von Kunde und Auftrag nur jene, die im aktuellen Zusammenhang Sinn machen. Führen Sie die uneingeschränkten Abfrage select name, datum from kunde, auftrag durch und vergleichen Sie die Resultate!
Noch ein Beispiel: eine Liste der Aufträge mit Artikelbezeichnungen:
select datum, kunde, artikel, bez, menge from artikel, auftrag where artikel.nr = auftrag.artikel;
SQL stellt eine Reihe von Gruppenfunktionen für einfache Datenauswertungen zur Verfügung, u.a. count(), sum(), min(), max(), avg(). Eine solche Funktion wird auf Werte aus mehreren Zeilen angewendet:
select count(*) from auftrag;
zählt die Datensätze in der Tabelle auftrag.
select count(adresse) from kunde;
zählt jene Datensätze in der Tabelle kunde, wo die Adresse nicht NULL ist.
select sum(bestand), min(bestand), max(bestand), count(bestand), avg(bestand) from artikel;
ermittelt den gesamten Artikelbestand, Minimum, Maximum, Anzahl der Datensätze mit Bestand nicht NULL, durchschnittlicher Bestand.
select max(datum) from auftrag;
liefert den höchsten Wert in der Spalte datum.
Mit der group by Klausel können Funktionen für bestimmte Bereiche eingegrenzt werden, die durch den Ausdruck nach GROUP BY definiert werden:
select kunde, count(*) from auftrag group by kunde;
Für jeden Wert in der Spalte Kunde wird gezählt, wieviele Datensätze mit diesem Wert vorhanden sind, m.a.W. wieviele Aufträge von diesem Kunden stammen. Bei der Gruppierung muß beachtet werden, daß jede Spalte entweder als Parameter einer Funktion oder für die Gruppenbildung verwendet wird. Beispiel: in der Auftragsliste soll auch der Name des Kunden ersichtlich sein:
select kunde.nr, kunde.name, count(*) from kunde, auftrag
where auftrag.kunde = kunde.nr group by kunde.nr, kunde.name ;
Das Feld kunde.name muß im group-by angegeben werden, obwohl der Wert ja aus der Kundennummer zwangsläufig folgt!
Die WHERE Klausel kann nicht für Restriktionen in Gruppen verwendet werden:
select kunde, count(*) from auftrag where count(*) > 1 group by kunde;
ERROR: aggregates not allowed in WHERE clause
Dazu kann die HAVING Klausel verwendet werden:
select kunde, count(*) from auftrag group by kunde having count(*) > 1;
Manchmal ist es hilfreich, eine Spalte mit AS umzubenennen. Ein solcher alias kann dann auch in GROUP BY und ORDER BY verwendet werden, nicht aber in WHERE und HAVING (in Postgres).
select kunde, count(*) as auftraege from auftrag group by kunde order by auftraege desc limit 5;
Die Option DESC beim ORDER BY sortiert absteigend, und der Zusatz LIMIT beschränkt auf die ersten 5 Datensätze im Ergebnis.
Reihenfolge der Klauseln im SQL Statement: WHERE - GROUP BY - HAVING - ORDER BY - LIMIT
Anzahl der Positionen pro Kunde und Datum:
select kunde, datum, count(*) from aufpos group by kunde, datum;
Anzahl der insgesamt verkauften Stück pro Artikel:
select artikel, sum(menge) from auftrag group by artikel;
Hierzu auch die Bezeichnung zur Artikelnummer:
select artikel, bez, sum(menge) from artikel, aufpos where artikel.nr = aufpos.artikel group by artikel, bez;
Gesamtbetrag pro Auftrag:
select auftrag, sum(preis * menge) from artikel, aufpos where artikel.nr = aufpos.artikel group by auftrag;
Wozu Backup? Fehlerquellen und Ursachen für Datenverlust sind:
Backup und Restore in Postgres:
Regelmäßige Backups aller Ihrer Daten auf dem Schulungsraumserver sowie sorgfältige Aufbewahrung der Backupmedien können Ihnen viel Zeit und Kummer ersparen. Machen Sie das DB Backup wie beschrieben und kopieren Sie alle Ihre Dateien vom Schulungsraumserver auf Ihren USB Stick, z.B. einmal pro Woche, oder auch am Ende jeder Sitzung; dann können Sie den Schaden begrenzen.
Ein sehr empfehlenswerter Klassiker zum Thema Datenbanken ist:
C. J. Date: An Introduction to Database Systems. Addison Wesley, 1999;
oder 25th Anniversary Edition, 2000.
Neu sehr teuer, aber oft antiquarisch günstig zu haben.
Gibt einen guten und umfassenden Einblick in die
Grundlagen und Design-Ideen des relationalen Modells und damit zusammenhängender
Abfragesprachen, inkl. (aber nicht ausschließlich) SQL.
Für jene, die besonders an der Sprache SQL in der Version SQL-99 interessiert sind:
Panny, W., Taudes, A.: Einführung in den Sprachkern von SQL-99. Springer, 2000.
Für aktuelle Informationen über Postgres siehe postgresql.org