SQL-Grundlagen - Informatikdidaktik

Einführung in die Wirtschaftsinformatik
Akademisches Jahr 2006/07
Prof. Andreas Meier
Assistentin:
Andreea Ionas
SQL-Grundlagen
1
2
3
4
5
6
Einleitung 1
Abfragen mit SELECT 2
2.1 Attribute auswählen (select-Klausel) 3
2.2 Auswählen von Tupeln (where-Klausel) 3
2.3 zusammengesetzte Suchbedingungen 4
2.4 Vermeidung von Mehrfachnennungen (distinct) 5
2.5 Sortieren (order-Klausel) 5
2.6 verschachtelte Abfragen 5
2.7 Tabellen multiplizieren (kartesisches Produkt) 6
2.8 Verbinden von Relationen (join) 7
2.9 Vereinigen von Tabellen (union) 8
2.10 Die Relationen-Algebra mit SQL 8
weitere Abfragemöglichkeiten 9
3.1 Platzhalter-Suche 9
3.2 Mengen-Abfragen 9
3.3 Der Exists-Operator 10
Rechnen in Tabellen 10
4.1 Das Rechnen mit Spalten 10
4.2 Rechnen mit Zeilen 11
Datenmanipulation und -definition mit SQL 12
Beispieldatenbank Terra 14
1 Einleitung
Die in einer Datenbank enthaltenen Daten können auf unterschiedliche Art organisiert sein. Das
Muster oder die Struktur, nach der die Organisation erfolgt, wird als Datenmodell bezeichnet.
Heute wird oftmals das von Codd definierte relationale Datenmodell verwendet. 1)
Das Grundelement einer relationalen Datenbank ist die Tabelle. Eine Tabelle setzt sich aus Spalten
und Zeilen zusammen; die Spalten sind die Attribute und die Zeilen werden Tupel genannt.
Die Begriffe Relation, Attribut und Tupel stammen aus der Mathematik. Sie bedeuten dasselbe
wie Tabelle, Spalte und Zeile und werden in diesem Papier synonym verwendet.
1)
Weitere mögliche Datenmodelle sind: hierarchisches Datenmodell, Netzwerk-Datenmodell, objektorientiertes Datenmodell.
Die abgebildete Tabelle Kunde enthält sechs Attribute und sieben Tupel:
Die Schnittpunkte von Kolonnen und Zeilen nennen wir (in Anlehnung an die Tabellenkalkulation)
Datenzellen. Sie enthalten die Datenwerte. Ein Datenwert kann eine Zahl sein (z.B. 1700), es kann
sich dabei aber auch um Text (z.B. Rosenweg 26) handeln oder um einen Wahrheitswert (booleschen Variable wahr/falsch).
Innerhalb einer Kolonne müssen alle Datenwerte denselben Datentyp haben (je nachdem welcher
Datentyp für die betreffende Spalte vereinbart wurde). In der Spalte PLZ sind z.B. alle Datenwerte
vom Typ « ganze Zahl » (Integer), in der Spalte Strasse hingegen vom Typ « Text ». 2)
In jeder Tabelle einer relationalen Datenbank existiert zudem ein (oder mehrere) Bezeichner, der
jede Zeile der Tabelle eindeutig definiert. Dieser Bezeichner kann entweder aus einer Spalte oder
aus einer Kombination mehrerer Spalten bestehen. Im relationalen Modell heisst dieser Bezeichner
Primärschlüssel. Im obigen Beispiel ist die Spalte KundenNr der Primärschlüssel der Tabelle
Kunde.
2 Abfragen mit SELECT
Der Grundbefehl, um Daten aus Relationen abzufragen heisst SELECT. Dieser Befehl ist erweiterbar
und sehr mächtig. Die formale Beschreibung (Syntax) sieht wie folgt aus:
SELECT [DISTINCT] spaltenname [, spaltenname …]
FROM tabellenname [, tabellenname …]
[WHERE suchausdruck]
[ORDER BY spaltenname [DESC][, spaltenname [DESC] …]] ;
• Alles was bei Syntaxdefinitionen in eckigen Klammern steht, ist fakultativ und kann je nach
Bedarf weggelassen werden.
• Die Wörter in Grossbuchstaben sind die SQL-Schlüsselwörter. Sie bilden das Gerüst der
Abfrage und müssen exakt 3) übernommen werden.
• Was kursiv (engl. italic) geschrieben wurde, ist ein Platzhalter und muss ersetzt werden.
Die ersten drei Teile (Klauseln) sind die wichtigsten. Sie haben im wesentlichen die folgenden
Bedeutung:
1. In der FROM-Klausel (zweite Zeile der Syntaxbeschreibung) wird gesagt, aus welcher Tabelle
Daten abgefragt werden sollen.
2)
3)
Bei Text spricht man auch von Zeichenketten (engl. string).
Gross-, Kleinschreibung ist dabei nicht relevant.
SQL-Grundlagen
2
2. In der SELECT-Klausel wird angegeben, welche Attribute der Tabelle angezeigt werden sollen.
3. In der WHERE-Klausel hat man die Möglichkeit, die Abfrage auf gewisse Tupel einzuschränken.
In den folgenden Abschnitten lernen Sie die verschiedenen Elemente dieses Befehls kennen.
In diesen Unterlagen verweisen die meisten Beispiele auf der Terra Datenbank: http://marvin.sn.schule.de/terra/. Die notwendigen Informationen zur Terra Datenbank findet ihr im Kapitel
6 Beispieldatenbank Terra
2.1 Attribute auswählen (select-Klausel)
Bei jeder Abfrage muss man angeben, welche Attribute der Tabelle angezeigt werden sollen. Die
gewünschten Attribute werden in der Select-Klausel mit ihrem Namen aufgeführt. So liefert z.B. der
Befehl
SELECT
Hauptstadt
FROM
LAND;
als Ergebnis die Spalte mit den Namen der Hauptstädte.
Sollen mehrere Attribute angezeigt werden, so trennt man sie durch Kommas:
SELECT
Hauptstadt, Flaeche, Einwohner
FROM
LAND;
Sollen alle Attribute der Tabelle angezeigt werden, so muss man die Merkmalsnamen nicht alle einzeln eintippen, sondern kann einen Stern als Platzhalter verwenden:
SELECT
*
FROM
LAND;
Dieser Befehl liefert alle Attribute (und Tupel) der Relation Land. Man kann sich damit also ganze
Tabellen anschauen.
2.2 Auswählen von Tupeln (where-Klausel)
Interessiert man sich nur für gewisse Datensätze (Tupel), so muss man diese durch eine Suchbedingung beschreiben. Die Suchbedingung wird mithilfe des Schlüsselworts WHERE an die SelectAbfrage angehängt.
Suchen von Zeichenketten
Durch die Abfrage
SELECT
*
FROM
LAND
WHERE
Hauptstadt='Vaduz';
wird nur der Datensatz des Landes dessen Hauptstadt Vaduz ist angezeigt .
Die gesuchte Zeichenkette muss in SQL immer zwischen Hochkommas stehen. Die Gross-/Kleinschreibung der abgefragten Werte ist bei manchen Datenbanken relevant.
Suchen von Zahlenwerten
Hingegen liefert der Befehl
SELECT
*
FROM
4)
LAND
WHERE
Einwohner>10000000;
anhand der Einwohnerzahl die Daten von grösseren Länder. Bei nummerischen Attributen können
zum Vergleichen die Operatoren =, <> (ungleich), <, >, <=, >= verwendet werden.
4)
3
Für Strings müssen Hochkommas verwendet werden, bei Zahlenwerte darf man keine verwenden.
SQL-Grundlagen
Suchen von Teilen einer Zeichenkette
Falls in der WHERE-Klausel nach Teilen einer Zeichenkette gesucht wird, so kann auch mit den
Platzhaltern % und _ gearbeitet werden. Anstelle vom Gleichheitszeichen « = » muss dann das
Schlüsselwort LIKE verwendet werden.
SELECT *
FROM
LAND
WHERE
L_Name
LIKE
'Schwe%';
Diese Abfrage findet alle Länder, deren Name mit « Schwe » beginnt; die Abfrage
« LIKE 'Schwe_ _' » (zwei Unterstriche) würde das Land Schweiz finden (nicht aber Schweden).
Das Prozentzeichen steht für eine beliebige Anzahl von Zeichen (auch für kein Zeichen), während
der Unterstrich genau ein Zeichen ersetzt.
Suchen von fehlenden Werten
Zum Abfragen von nicht ausgefüllten Zellen wird der Operator is null gebraucht. Dieser listet
jene Tupel auf, die im betreffenden Attribut keinen Eintrag aufweisen (also nicht jene, wo die Zahl 0
oder ein Leerzeichen steht). Das Gegenteil kann mittels is not null erreicht werden.
SELECT
*
FROM
LAND
WHERE
LT_ID is null;
2.3 Zusammengesetzte Suchbedingungen
Durch die logische Verknüpfung mit AND, OR und NOT können komplexe Suchbedingungen realisiert werden. Dazu je ein Beispiel:
SELECT L_Name, Flaeche, Einwohner, Hauptstadt FROM LAND
WHERE Hauptstadt = 'Wien' OR Hauptstadt = 'Bern';
Liste der Länder mit der Haupstadt Wien oder Bern.
SELECT *
WHERE
FROM LAND
Einwohner > 5000000 AND Einwohner < 25000000;
die Daten der Länder die mehr als 5 Mio. Einwohner und weniger als 25 Mio. haben
Werden mehr als zwei logische Bedingungen miteinander verknüpft, so müssen ev. runde Klammern gesetzt werden, damit die Verknüpfung in der richtigen Reihenfolge ausgeführt wird. Werden
keine Klammern gesetzt, so bindet der NOT-Operator am stärksten, danach AND und als letztes die
Verknüpfung mit OR.
Beispiel: Sie suchen alle Länder die eine grössere Fläche als 100000 haben und deren Name mit
dem Buchstaben 'P' oder 'R' anfängt. Das korrekte SQL-Statement für diese Situation lautet:
SELECT L_Name, Einwohner FROM LAND
WHERE Flaeche > 100000 AND
(L_Name like 'P%' OR L_Name like 'R%');
Frage:Welche Tupel liefert die obige Abfrage, wenn die Klammern weggelassen werden? Begründen Sie!
2.4 Vermeidung von Mehrfachnennungen (distinct)
Das Ergebnis einer Abfrage kann identische Tupel enthalten. Ein Beispiel hierfür liefert die Abfrage
SELECT INSELGRUPPE FROM INSEL;
In der Ergebnistabelle erscheinen die Inselgruppen mehrfach (für jede Insel). Falls Sie keine Mehrfachnennung identischer Tupel möchten, verwenden Sie den Zusatz DISTINCT. Der Befehl
SELECT
DISTINCT INSELGRUPPE FROM INSEL;
SQL-Grundlagen
4
liefert dieselbe Auflistung wie vorhin, aber jede Inselgruppe wird nur einmal genannt.
2.5 Sortieren (order-Klausel)
Mittels SELECT aufgerufene Tupel werden in beliebiger Reihenfolge angezeigt. Nun möchte man
die Tupel aber oftmals in einer bestimmten Reihenfolge angezeigt bekommen. Hier hilft der Zusatz
ORDER BY. Der Befehl
SELECT
*
FROM
INSEL
ORDER BY
Flaeche;
führt dazu, dass die Insel sortiert nach der Fläche ausgegeben werden. Standardmässig wird in aufsteigender Reihenfolge sortiert. Falls in absteigender Reihenfolge sortiert werden soll, wird hinter
das Sortiertattribut der Befehl DESC gestellt. 5)
SELECT
*
FROM
INSEL
ORDER BY
Flaeche DESC;
Ebenfalls möglich ist es, nach mehreren Attributen sortieren zu lassen, z.B. einmal absteigend nach
der Inselgruppe (erster Schlüssel), und bei Insel auf der selben Inselgruppe nach der Fläche (zweiter
Schlüssel). Man schreibt die Sortierschlüssel dann hintereinander:
SELECT
*
FROM INSEL ORDER BY Inselgruppe DESC, Flaeche;
2.6 Verschachtelte Abfragen
Es ist möglich, Abfragebefehle ineinander zu verschachteln. Dies ist häufig sinnvoll bei Abfragen,
bei denen Daten aus mehreren Tabellen benötigt werden. Man arbeitet dabei sinnvollerweise mit
Klammern, um die Auswertungsreihenfolge klarzustellen.
Beispiel:
SELECT L_Name
FROM LAND
WHERE L_ID IN(SELECT L_ID FROM LANDTEIL WHERE Einwohner>10000000);
Hier wird nach den Namen derjenigen Länder gesucht, die Landteile mit mehr als 1 Mio. Einwohner
haben. Klammern werden immer von innen nach aussen abgearbeitet: Zuerst wird nach der L_ID
der Länder mit grossen Landteilen geschaut, und anschliessend wird nach denjenigen Ländern
gesucht, bei denen im Attribut L_ID die ermittelte Nummer vermerkt ist.
2.7 Tabellen multiplizieren (kartesisches Produkt)
Beim Multiplizieren zweier Tabellen wird das kartesische Produkt gebildet, d.h. jedes Tupel der
ersten Relation wird mit jedem Tupel der zweiten Relation kombiniert. Das kartesische Produkt der
Tabellen Land und Kontinent sieht wie folgt aus:
5)
5
DESC: engl. descending
SQL-Grundlagen
Das kartesische Produkt wird immer dann erzeugt, wenn in der FROM-Klausel mehrere Tabellen
angegeben werden. Der SQL-Befehl zum Erzeugen des obigen kartesischen Produktes (LAND ×
KONTINENT) lautet wie folgt:
SELECT
*
FROM
LAND, KONTINENT;
Das kartesische Produkt kann auch mit mehr als zwei Tabellen gebildet werden. Dann wird jedes
Tupel der ersten Relation mit jedem Tupel jeder anderen Relation kombiniert.
Dabei entsteht viel « Datenmüll ». Betrachten Sie z. B. die ersten Tupel. Alle Länder wurden auf
dem Kontinent Afrika gesetzt.
2.8 Verbinden von Relationen (join)
Aus dem kartesischen Produkt kann man nun mit der WHERE-Klausel die sinnvollen Kombinationen heraussuchen.
Betrachten Sie die Tabellen LAND, KONTINENT und UMFASST (die Flächen bei LAND und
KONTINENT haben unterschiedliche Masseinheiten):
SQL-Grundlagen
6
Wie erhält man eine Liste aller Länder, in der auch die Namen der Kontinente erscheinen?
Müsste man eine solche Liste ohne Hilfe des Computers erstellen, würde man bei jedem Land
zunächst nach der ID des Landes schauen (z. B. L_ID = A in der Tabelle Land), und dann in der
Tabelle UMFASST den Name des entsprechenden Kontinentes raussuchen (K_Name = Europa in
der Tabelle UMFASST) und falls man noch mehr Infos zum Kontinenten möchte, diese Informationen in der Tabelle Kontinent suchen. D. h. man kombiniert jedes Tupel aus der Land-Tabelle mit
demjenigen Tupel aus der Kontinent-Tabelle, bei denen eine Kombination in der Tabelle UMFASST
existiert.
Genau dies tut auch der folgende SQL-Befehl:
SELECT L_Name, Flaeche, K_Name
FROM LAND, UMFASST
WHERE LAND.L_ID = UMFASST.L_ID;
Resultat:
Bemerkung:
Beim Abfragen mehrerer Relationen kann der Fall auftreten, dass Attribute aus verschiedenen
Tabellen denselben Namen haben. Im obigen Beispiel- das Attribut L_ID muss durch Angabe des
Tabellennamens klar gemacht werden, somit wird spezifiziert aus welcher Relation das Attribut
geholt werden soll. Der Tabellenname wird dabei jeweils in der Form table_name.column_name
angegeben.
Es können auch mehr als zwei Tabellen miteinander verbunden werden.
7
SQL-Grundlagen
So z.B. möchte man auch die Fläche des jeweilgen Kontinenten kennen, muss auch die Tabelle
KONTINENT in die obere Abfrage einbezogen werden:
SELECT L_Name, LAND.Flaeche, KONTINENT.K_Name, KONTINENT.Flaeche
FROM LAND, UMFASST, KONTINENT
WHERE LAND.L_ID = UMFASST.L_ID
AND UMFASST.K_NAME=KONTINENT.K_NAME;
Beachten Sie, dass der WHERE-Teil eine einzige zusammengesetzte logische Bedingung bildet, die
auf das kartesische Produkt mehrerer Tabellen angewendet wird (vgl. Kap. 2.7).
2.9 Vereinigen von Tabellen (union)
Unter gewissen Voraussetzungen können die Ergebnisse zweier SELECT-Abfragen in einer einzigen Tabelle zusammengefasst werden. Dies geschieht durch den Befehl UNION. Die Grundform
davon sieht wie folgt aus:
select_statement1
UNION
select_statement2
Betrachten wir dazu ein Beispiel (es ist ein fiktives Beispiel):
SELECT
UNION
SELECT
Name, Vorname, Ort
FROM
Tennisclub
Name, Vorname, Ort
FROM
Badmintonclub;
Die resultierende Tabelle enthält sowohl Daten des einen Sportclubs als auch des anderen. Natürlich
können auch komplexere Abfragen als Bausteine verwendet werden. Wichtig ist nur, dass die beiden
SELECT-Befehle vereinigungskonforme Ergebnistabellen liefern, d.h. diese müssen
• gleich viele Attribute haben und
• die Attribute aus beiden Select-Abfragen müssen in der derselben Reihenfolge dieselben Datentypen aufweisen.
• Die Namen der Attribute brauchen jedoch nicht übereinzustimmen.
Bemerkung: Beim Verbinden von Relationen (join) werden die Attribute verschiedener Tabellen zu
einer neuen Tabelle zusammengeführt, beim Vereinigen (union) die Tupel. Joins sind sehr üblich,
unions sehr selten.
2.10 Die Relationen-Algebra mit SQL
Eine Tabelle ist bekanntermassen eine Menge von Datensätzen (Tupeln). Mit Tabellen kann man
deswegen alles machen, was man mit Mengen auch machen kann, nämlich z.B.
SQL-Grundlagen
8
• eine Teilmenge auswählen
• eine Menge auf ein Merkmal (Dimension) projizieren
• Mengen vereinigen
• Mengen schneiden
• das kartesische Produkt von Mengen bilden
Alle diese Operationen lassen sich mit dem SELECT-Befehl realisieren. In diesem Kapitel haben
Sie die meisten dieser Operationen bereits kennengelernt:
• Mithilfe der WHERE-Klausel kann eine Teilmenge von Tupeln ausgewählt werden
• Mit SELECT kann eine Tabelle auf ein oder mehrere Merkmale projiziert werden.
• Mit UNION können zwei (vereinigungskonforme) Tabellen vereinigt werden.
• Der Befehl zum Schneiden von Tabellen lautet INTERSECT. Er wird allerdings von vielen
Datenbank-Programmen nicht unterstützt.
• Das kartesische Produkt wird gebildet, indem in der FROM-Klausel mehrere Tabellen angegeben werden. Es wird auch als Verbund ohne Verbundprädikat bezeichnet.
3 Weitere Abfragemöglichkeiten
3.1 Platzhalter-Suche 6)
Falls in der WHERE-Klausel mit einer Zeichenkette verglichen wird, so kann auch mit den Platzhaltern % und _ gearbeitet werden, wobei
% für beliebig viele (evtl. kein) Zeichen steht und
_
für genau ein Zeichen steht.
Anstelle des Operators « = » muss dann das Schlüsselwort LIKE geschrieben werden. So liefert z.B.
der Befehl
SELECT
*
FROM
INSEL
WHERE
I_Name LIKE 'M%';
die Datensätze der Insel, deren Name mit M beginnt.
Bemerkungen:
1. In einem Suchmuster können mehrere Platzhalter miteinander kombiniert werden: z.B. «_e%»
für alle Zeichenketten, deren zweiter Buchstabe ein e ist.
2. In manchen Datenbank-Programmen (z.B. Access) werden anstelle der Zeichen % und _ die Zeichen * und ? als Platzhalter verwendet.
3.2 Mengen-Abfragen
SQL unterstützt nicht nur den Vergleich mit einzelnen Werten. Häufig möchte man mehrere Werte
für ein Merkmal zulassen. SQL erlaubt es, diese Werte in einer Menge aufzuzählen. Dazu wird das
Schlüsselwort IN verwendet. Der Befehl
SELECT *
WHERE
6)
9
FROM INSEL
Inselgruppe IN
('Madagaskar', 'Komoren', 'Balearen');
Dies wurde bereits in den SQL-Grundlagen in Kapitel 2.2 angetönt.
SQL-Grundlagen
liefert z.B. die Daten der Insel, die zu den Inselgruppen Madagaskar, Komoren oder Balearen gehören, und ist äquivalent zum Befehl
SELECT * FROM INSEL
WHERE Inselgruppe='Madagaskar' OR Inselgruppe='Komoren' OR
Inselgruppe='Balearen';
Durch eine Mengen-Abfrage können also viele OR-Verknüpfungen eingespart werden.
Bemerkung: Eine Aufzählungsmenge kann beliebig viele Werte enthalten.
3.3 Der Exists-Operator
Manchmal interessiert man sich nur dafür, ob gewisse Tupel vorhanden sind, und nicht für den
Inhalt der Tupel selber.
Dies soll anhand des Beispiels aus der Übungsdatenbank veranschaulicht werden. Jedes Tupel in der
Tabelle «UMFASST» steht für die Zugehörigkeit eines Landes zu einem Kontinent. Möchte man
nun wissen, für welche Länder noch keine Zugehörigkeit in der Tabelle UMFASST definiert wurde,
so ist dies mit den bisherigen SQL-Abfragen nicht möglich.
Hier hilft der EXISTS-Operator, welcher zu einer vorgegebenen SQL-Abfrage angibt, ob sie Tupel
zurückliefert oder nicht. Es handelt sich dabei um eine verschachtelte Abfrage-Technik, wie das
Beispiel deutlich macht.
SELECT * FROM LAND
WHERE NOT EXISTS
(SELECT * FROM UMFASST WHERE UMFASST.L_ID=LAND.L_ID);
Es passiert folgendes: für jedes Tupel aus der LAND-Tabelle wird die Landnummer gelesen
(LAND.L_ID), und es wird in der Umfassttabelle nachgeschaut, ob ein oder mehrere Tupel mit dieser Landnummer vorkommen. Falls dies nicht zutrifft, werden die Informationen zu dem entsprechendem Land ausgegeben.
4 Rechnen in Tabellen
Mit SQL kann man in Tabellen rechnen. Man unterscheidet prinzipiell zwei Arten zu rechnen:
1. das Rechnen mit einer oder mehreren Spalten (Grundrechenarten +, *, ... sowie Text-Funktionen)
2. das Rechnen mit Tupeln (Aggregatfunktionen).
4.1 Das Rechnen mit Spalten
In SQL kann man Spalten addieren, multiplizieren usw. Dafür muss man nur den zu berechnenden
Ausdruck in die SELECT-Klausel schreiben.
Betrachten Sie die Tabelle Zahlen
SQL-Grundlagen
10
Der SQL-Befehl, um die beiden Spalten zu addieren, lautet:
SELECT
a, b, a+b
FROM
Zahlen;
Die Datenbank vergibt selbständig einen Namen für die Spalte mit dem Ergebnis. Man kann auch
einen aussagekräftigen Namen für die Spalte mit dem Ergebnis angeben. Dazu benötigt man das
Schlüsselwort AS:
SELECT
a, b, a+b
AS
Summe
FROM
Zahlen;
Man kann auch mit einer einzigen Spalte rechnen (z.B. a+1 oder ähnliches). Wichtig ist, dass man
auch mit Spalten vom Typ «Datum» rechnen kann. Dies geschieht tageweise. Angenommen man hat
eine Tabelle Kurs mit den Spalten KursNr, Bezeichnung, Preis, Datum und will man z.B. in der Kurstabelle vom Datum aller Kurse eine Woche abziehen (Anmeldeschluss), so benützt man den Befehl
SELECT KursNr, Bezeichnung, Datum-7
FROM Kurs;
AS
Anmeldeschluss
Man kann aber auch mit Text-Spalten «rechnen»; z.B. kann man
1. Zeichenketten hintereinanderhängen
Dazu benötigt man in den meisten Datenbank-Programmen das Pluszeichen +
2. Umwandlung in Gross- oder Kleinschreibung:
Dazu dienen die Funktionen UPPER/LOWER resp. UCASE/LCASE (Access)
Beispiele: gültige Ausdrücke sind
• Vorname + Name
• Vorname + ' ' + Name
(Vorname und Nachname durch Leerzeichen getrennt)
• UCASE(Name)
4.2 Rechnen mit Zeilen
Zum Rechnen mit Tupeln stehen in SQL die sogenannten Aggregatfunktionen zur Verfügung. Mit
diesen kann man:
• das Maximum/Minimum innerhalb einer Spalte bestimmen,
• Durchschnitte berechnen,
• alle Werte innerhalb einer Spalte aufsummieren,
• Tupel zählen (genauer: diejenigen Tupel zählen, für die ein bestimmtes Feld nicht leer ist).
Dazu je ein Beispiel (es handelt sich um fiktive Beispiele):
SELECT
Min(Preis)
FROM Buch;
der Preis des billigsten Buches
SELECT
Avg(Preis)
FROM Buch;
der durchschnittliche Preis der Bücher
SELECT
Sum(Preis)
FROM
Verkäufe;
die gesamten Einnahmen aus den Verkäufe
SELECT
Count(VerkaufNr)
FROM Verkäufe;
Anzahl Verkäufe
Das Ergebnis wird in Form einer einzelligen Mini-Tabelle angezeigt.
11
SQL-Grundlagen
Es besteht die Möglichkeit, diese Funktionen zu «verfeinern», d.h. man kann Gruppen von Datensätzen bilden, für welche die Ergebnisse separat ausgewiesen werden. Dies geschieht mit Hilfe des
Zusatzes GROUP BY.
Z.B. können die Anzahl Länder je Kontinent gezählt werden durch den Befehl
SELECT K_Name, Count(L_Id)
FROM UMFASST
GROUP BY K_Name;
Sinnvollerweise lässt man nicht nur die Ergebnisse anzeigen, sondern auch das Attribut, über dessen
Wert die Gruppierung vorgenommen wurde, um zu wissen, welchen Datensätzen welche Ergebnisse
zuzuordnen sind.
Möchte man auch andere Attribute angezeigt bekommen, so muss man diese in die GROUP BYKlausel aufnehmen:
SELECT KONTINENT.K_Name,Flaeche, Count(L_Id)
FROM UMFASST, KONTINENT
WHERE UMFASST.K_Name=KONTINENT.K_NAME
GROUP BY KONTINENT.K_Name,Flaeche;
AS Anzahl_Länder
Bemerkung: Auch die Ergebnisse der anderen Aggregat-Funktionen lassen sich mithilfe der
GROUP BY-Klausel gruppieren.
5 Datenmanipulation und -definition mit SQL
Neben den vorgestellten SQL-Befehlen zum Abfragen und Auswerten von Daten umfasst SQL eine
Reihe von Befehlen zum Erstellen, zum Ändern und zum Löschen von Datenbankobjekten- Datenbanken, Tabellen, Views. Die Beispiele in diesem Kapitel sind fiktive Beispiele, ihr könnt sie mit
der Übungsumgebung nicht durchführen.
5.1 INSERT
Mit der INSERT-Anweisung können Tupel (Zeilen) in einer bestehenden Tabelle eingefügt werden.
INSERT INTO tab_name [(spalte_1, ...)] VALUES ( ausdruck_1, ...)
Mit dieser Anweisung wird ein Tupel in der Tabelle tab_name eingefügt. spalte_1, spalte_2,... sind
die Namen der Spalten (Attribute) der Tabelle tab_name und ausdruck_1, ausdruck_2,... sind die
Werte, die in diesen Spalten eingefügt werden sollen. Jeder eingefügte Datenwert muss denselben
Datentyp haben, wie die Spalte, in der er eingefügt wird. Dabei sollen die alphanumerischen Werte
und die Datums- und Zeitangaben immer in Apostrophen eingeschlossen werden, während man
numerische Werte ohne Apostrophe schreiben kann.
Die Angabe der Spaltenliste ist optional. Falls alle Spalten einer Tabelle eingefügt werden sollen,
kann auf die Angabe der Liste verzichtet werden. (aber die Reihenfolge muss eingehalten werden)
insert into mitarbeiter values(25348,'Keller','Hans')
oder
insert into mitarbeiter(m_nr,m_name,m_vorname) values
(8413‚'Lotter','Wolfgang')
ein Datensatz wird in der Tabelle mitarbeiter eingefügt
Es besteht noch eine zweite Variante der INSERT-Klausel, die es erlaubt, mehrere Datensätze
gleichzeitig einzufügen. Die Syntax lautet:
SQL-Grundlagen
12
INSERT INTO tab_name [(spalte_1, ...)] select_anweisung
Hierbei werden die Reihen einer existierenden Tabelle ausgewählt und in einer anderen Tabelle
(oder derselben) Tabelle eingefügt.
INSERT INTO muench_abt (abt_nr, abt_name) SELECT abt_nr, abt_name FROM
abteilung WHERE stadt='München'
5.2 UPDATE
Die UPDATE-Anweisung ändert die Datenwerte der Tupel einer Tabelle. Diese Anweisung hat folgende Form:
UPDATE tab_name SET spalte_1=ausdr_1 [{,spalte_2=ausdr_2}...] [WHERE bedingung]
UPDATE arbeiten SET aufgabe='Gruppenleiter' WHERE m_nr=18316 AND
pr_nr='p2'
Die Aufgabe des Mitarbeiters mit der Personalnummer 18316 im Projekt p2 ist neu Gruppenleiter
5.3 DELETE
Mit der DELETE-Anweisung werden Tupel aus einer Tabelle gelöscht.
DELETE [FROM] tab_name [WHERE bedingung]
Delete from arbeiten where aufgabe ='Gruppenleiter'
alle Einträge aus der Tabelle arbeiten die den Wert 'Gruppenleiter' haben werden gelöscht
Delete from arbeiten
alle Einträge der Tabelle arbeiten werden gelöscht, Struktur der Tabelle (mit Name, Spaltennamen, etc.) bleibt erhalten
5.4 Views
Häufig benötigte SQL-Abfragen kann man in der Datenbank abspeichern und anschliessend wie
eine Tabelle abrufen. Man spricht dabei von Benutzersichten (engl. view). Man benützt dafür den
Befehl CREATE VIEW. Die Syntax lautet:
CREATE VIEW view_name AS select-statement
wobei view_name für den Namen der Benutzersicht steht, unter dem die Abfrage später wiederabgerufen werden soll, und select-statement für die abzuspeichernde SQL-Abfrage.
Bemerkung: In Access heissen Views Queries und werden in der Karteikarte "Queries" verwaltet
(und nicht mit dem CREATE-View-Befehl erzeugt)
CREATE VIEW Gruppenleiter AS
SELECT mitarbeiter.m_nr,m_name,m_vorname, pr_nr
FROM mitarbeiter,arbeiten
WHERE mitarbeiter.m_nr=arbeiten.m_nr and
aufgabe ='Gruppenleiter'
Es wird eine view der Gruppenleiter, mit den Informationen-m_nr, m_name, m_vorname, pr_nrerstellt, wobei die Tabellen mitarbeiter und arbeiten über die m_nr verbunden werden.
Um diese Liste abzurufen, gibt man dann ein:
SELECT * FROM Gruppenleiter
13
SQL-Grundlagen
Bemerkung: Benutzersichten lassen sich genau gleich wie Tabellen einsetzen! Deswegen
bezeichnet man Views auch als virtuelle Tabellen.
Zum Löschen einer view dient folgender Befehl:
DROP VIEW view_name
drop view Gruppenleiter
Die view Gruppenleiter wird gelöscht.
Bemerkung:
DELETE löscht einen oder mehrere Einträge in einer Tabelle oder einer view, die Tabelle
bleibt aber erhalten (eventuell ohne Einträge);
DROP löscht die Tabelle als Objekt.
Es ist wichtig zu beachten, dass Views bei jedem Aufruf neu ausgewertet werden (durch Ausführen
des entsprechenden SQL-Befehls). Somit erhält man durch das Abfragen einer View immer die zum
jeweiligen Zeitpunkt aktuellen Daten, auch wenn die Informationen in der zugrundeliegenden
Tabelle sich zwischenzeitlich geändert haben.
6 Beispieldatenbank Terra
Zur Unterstützung der SQL-Übungen für die Einführung in die Wirtschaftsinformatik wird die Terra
Datenbank benutzt. Der Zugang zur Datenbank geschieht über die URL http://marvin.sn.schule.de/terra.
Bei (1) kann man seine Abfrage eingeben, und mit dem Knopf Anfrage diese ausführen.
SQL-Grundlagen
14
Klickt man auf Interface bekommt man die untenstehende Struktur, man kann verschiedene Optionen auswählen, z.B. Struktur oder Inhalt der Tabellen einsehen.
Die Terra Datenbank enthält folgende Tabellen:
Es werden hier nicht alle Tabellen verwendet, ihr könnt selber damit experimentieren. In diesen
Unterlagen und Übungsserie werden dieTabellen LAND, UMFASST KONTINENT, LANDTEIL
und INSEL verwendet. Die Abbildung oberhalb zeigt die Primärschlüssel der Tabellen und die
Beziehungen zwischen ihnen.
Im Weiteren sind ein paar Datensätze der Tabellen LAND, UMFASST, KONTINENT, LANDTEIL
und INSEL aufgeführt.
15
SQL-Grundlagen
LAND
KONTINENT
UMFASST
LANDTEIL
INSEL
SQL-Grundlagen
16