Datenbanken und Internet

Robert Krah
Datenbanken und Internet
Einführung in SQL
Apache-Server
MySQL
PHPMyADMIN
Lehrgang:
Medizinische Dokumentation
Datenbanken und Internet
Robert Krah
Inhaltsverzeichnis
1
Ziele: Was soll vermittelt werden? .................................................................................... 1
2
2.1
2.2
2.2.1
2.2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
Einführung in SQL............................................................................................................. 2
Einführung in SQL über Access-Abfragen ...................................................................... 2
Operatoren in Abfragen .................................................................................................. 6
Vergleichsoperatoren ...................................................................................................... 7
Der Operator in ............................................................................................................... 8
Übungen zum Select-Befehl ........................................................................................... 9
Aggregat-Funktionen .................................................................................................... 13
Übungsaufgaben........................................................................................................... 14
Select-Befehl mit Gruppierung ...................................................................................... 15
Die Having-Klausel ....................................................................................................... 17
Daten ändern: update ................................................................................................... 21
Verbindung von Tabellen (Join) .................................................................................... 22
3
3.1
3.2
3.3
Zugriff auf Internet-Datenbanken ...................................................................................... 28
Einsatz des Apache-Servers......................................................................................... 29
WAMP-Installation ........................................................................................................ 29
Passwort für den localhost einstellen............................................................................ 32
Datenbanken und Internet
1 Ziele: Was soll vermittelt werden?
In diesem Skript geht es um Datenbanken und deren Anbindung an das Internet. Die hier
verfolgten Ziele sind:
1. die Beschäftigung mit der standardisierten Datenbank-Sprache SQL = structured
query language
2. der Einsatz von Datenbanken im Internet unter besonderer Berücksichtigung von
MySQL
3. die Installation des Webservers Apache unter Windows
4. die Verwendung von Tools zur Administration von Web-basierten Datenbanken wie
PHPMyADMIN und MySQL-Control Center
5. eine Einführung in die Skriptsprache PHP
6. und schließlich die Erstellung von interaktiven Websites unter Einbeziehung von
MySQL-Datenbanken: Hier fließen die vorher behandelten Themen zusammen: Der
Einsatz einer Datenbank im Internet setzt einen Webserver wie Apache voraus. In
HTML-Dateien werden PHP-Skripte eingefügt, die über MySQL-Funktionen auf eine
Datenbank zugreifen können. Dazu muss man wissen, wie man eine MySQLDatenbank bedient, und wie man lokal erstellte Datenbanken und Tabellen über einen "Dump" zum Provider transferiert. Die Programmierung der Abfragen aus der
Datenbank und das Einfügen von Daten durch Personen, die dazu berechtigt sind,
setzt die Kenntnis von SQL voraus.
Die Einführung in SQL erfolgt zunächst am Beispiel von Access-Datenbanken. Die vertraute
Oberfläche von Access, dessen Kenntnis hier in den Grundzügen vorausgesetzt wird, soll
dazu dienen, den Teil von SQL kennen zu lernen, der zum Erstellen von Abfragen (query)
aus Tabellen dient.
Anschließend erfolgt die WAMP-Installation: Diese Abkürzung steht für Windows-ApacheMySQL-PHP.
Nun kann die Beschäftigung mit SQL fortgesetzt werden. Der Teil von SQL, der mit DDL
abgekürzt wird (data definition language) wird hier zum Erstellen von Datenbanken und Tabellen, zum Einfügen, Löschen und Ändern von Daten verwendet.
Nach dieser Beschäftigung mit der SQL-Befehlssyntax werden Werkzeuge Wie PHPMyAdmin eingesetzt, die in der Regel auch zur Administration der Datenbank beim Provider verwendet werden.
Jetzt fehlt nur noch die Kenntnis der Sprache PHP, um dann Datenbanken im Internet über
HTML-basierte Dokumente anzubinden. Das Ergebnis kann beispielsweise ein Webshop
sein: Kunden rufen Artikeldaten im Internet ab, füllen einen Warenkorb und bestellen dann
die Ware.
1
Einführung in SQL
Abfragen mit select
2 Einführung in SQL
In der standardisierten Datenbank-Abfragesprache SQL werden drei Bereiche unterschieden:
1.
DDL: Data Definition Language
Einrichten von Datenbanken, Tabellen, Indizes, Views usw.
2.
DML: Data Manipulation Language
Manipulation von Daten, Einfügen., Ändern, Auswählen (Select)
3.
DCL: Data Control Language
Steuerung der Vergabe von ZugriffsrechtenDer Teil von SQL, mit dem wir uns zunächst befassen, dient der Syntax des select-Befehls. Damit werden Abfragen erstellt, die
Sie in Access über die grafische Oberfläche kennengelernt haben. Alle Befehle, deren Syntax hier zunächst besprochen wird, sind der zweiten Gruppe der Befehle von SQL (DML)
zuzuordnen.
2.1 Einführung in SQL über Access-Abfragen
Eine einfache Abfrage zur Tabelle Artikel aus der Datenbank Firma, die die Felder Artikelnummer und Bezeichnung auswählt, führt zu folgendem Ergebnis:
Im Entwurfsbildschirm der Abfrage oder auch in der Datenblattansicht können Sie mit dem ersten Symbol links auch zur SQLAnsicht umschalten:
In der SQL-Ansicht sind die Befehle sichtbar,
die der oben gezeigten Abfrage entsprechen:
die Angabe der Tabelle kann an folgenden
Stellen weggelassen werden:
Auf diese Art – im Vergleich der grafisch orientierten Entwurfsansicht mit den SQL-Befehlen –
lassen sich die Grundlagen von SQL relativ leicht erlernen.
Mit dem Select-Befehl werden Daten aus Tabellen ausgewählt. Dies kann mit Bedingungen
und Sortierungen verbunden werden. Zunächst befassen wir uns mit der vereinfachten Syntax des Select-Befehls, bezogen auf nur eine Tabelle.
2
Einführung in SQL
Abfragen mit select
Die sechs Komponenten einer SQL-Abfrage1
SELECT [ALL | DISTINCT] Spaltenausdruck Zeige mir die Werte aus folgenden Spalten …
[, ...]
(Ausgabedaten)
FROM Tabellenbezeichner [, ...]
… aus folgenden Tabelle(n)
[ WHERE Bedingung ]
… schränke die Auswahl wie folgt ein …
[ GROUP BY Spalte [, ... ] ]
Gruppenbildung
Verdichte die Ausgabe von allen Zeilen mit
gleichem Attributwert zu einer einzigen Ausgabezeile
[ HAVING Having-Bedingung
Zusätzliche Auswahlbedingung auf Gruppenebene
[ ORDER BY [ [ASC | DESC ] Spalte [, ...] ]
…
sortiere Ausgabe anhand folgender Spalten
(Default ist ASC)
Wir wählen zunächst nur den ersten Teil des select-Statements:
SELECT {* | <Spaltenliste>}
FROM <Tabellenname>
[WHERE <Ausdruck>]
Wähle aus die Spaltenliste (oder *=alle)
aus der Tabelle
wo folgendes gilt
An den folgenden Beispielen soll dies gezeigt
werden. Um sie auszuprobieren, öffnen Sie
bitte die Datenbank Firma, Tabelle Artikel, und
erzeugen eine leere Abfrage im Entwurfsbildschirm.
Wenn Sie dann zur SQL-Ansicht umschalten,
sehen Sie den Select-Befehl
SELECT FROM ARTIKEL;
1
diese Übersicht über die sechs Komponenten einer Abfrage ist aus:
http://www.oszhdl.be.schule.de/gymnasium/faecher/informatik/datenbanken.htm
3
Einführung in SQL
Abfragen mit select
Aufgabe: Dort können Sie jetzt die folgenden Beispiele testen. Schalten Sie dabei jeweils
von der SQL-Ansicht, testen Sie mit dem roten Ausrufezeichen
(die Befehle müssen nicht mit Grossbuchstaben eingegeben werden)
SELECT *
alle Datensätze der Tabelle Artikel
FROM ARTIKEL
SELECT BEZEICHNUNG
FROM ARTIKEL
das Feld Bezeichnung aller Datensätze
der Tabelle Artikel
SELECT ARTIKELNUMMER, BEZEICHNUNG
FROM ARTIKEL
die beiden Felder Artikelnummer und Bezeichnung aus der Tabelle Artikel
SELECT *
FROM ARTIKEL
WHERE EINKAUFSPREIS <10
alle Datensätze, bei denen der Einkaufspreis
unter 10 € ist
SELECT BEZEICHNUNG, MENGE, MINDESTBESTAND
FROM ARTIKEL
WHERE EINKAUFSPREIS <10
wie oben, aber nur die Felder Bezeichnung,
Menge und Mindestbestand
SELECT BEZEICHNUNG
FROM ARTIKEL
WHERE LIEFERANTENNUMMER=[BITTE LIEFNR EINGEBEN]
wie Parameter-Abfrage:
das Feld Bezeichnung der Tabelle Artikel
nach der Lieferantennummer wird gefragt
SELECT *
FROM ARTIKEL
WHERE EINKAUFSPREIS <10
AND MINDESTBESTAND >50
alle Datensätze, bei denen der Einkaufspreis
unter 10 € ist und der Mindestbestand über
50
SELECT BEZEICHNUNG, VERKAUFSPREIS
FROM ARTIKEL
WHERE ARTIKELNUMMER >200
OR VERKAUFSPREIS >20
die Felder Bezeichnung und Verkaufspreis,
bei denen entweder die Artikelnummer über
200 ist oder der Verkaufspreis über 20 €
SELECT *
FROM ARTIKEL
ORDER BY BEZEICHNUNG
alle Datensätze der Tabelle Artikel, sortiert
nach Bezeichnung
SELECT BEZEICHNUNG
FROM ARTIKEL
ORDER BY ARTIKELNUMMER ASC
das Feld Bezeichnung der Tabelle Artikel,
sortiert nach Artikelnummer
(ASC=ascending=aufsteigend; ohne diese
Angabe wird immer aufsteigend sortiert
SELECT *
FROM ARTIKEL
ORDER BY BEZEICHNUNG DESC
wie oben, aber absteigend
4
Einführung in SQL
Abfragen mit select
SELECT *
FROM ARTIKEL
ORDER BY LIEFERANTENNUMMER, ARTIKELNUMMER
alle Datensätze der Tabelle Artikel, primär
sortiert nach Lieferantennummer, sekundär
nach Artikelnummer
zuerst alle Datensätze der ersten Lieferantennummer, diese nach Art-Nr sortiert, dann
die nächste Lieferantennummer, nach ArtNummern sortiert, usw.
(macht nur Sinn, wenn das erste Sortierfeld
mehrfach vorkommt)
SELECT ARTIKELNUMMER, BEZEICHNUNG,
MENGE
FROM ARTIKEL
ORDER BY 3
die Felder Artikelnummer, Bezeichnung und
Menge, sortiert nach dem an 3. Stelle genannten Feld (Menge)
SELECT BEZEICHNUNG
FROM ARTIKEL
WHERE BEZEICHNUNG LIKE "D*"
die Felder Bezeichnung der Tabelle Artikel,
aber nur die, bei denen die Bezeichnung mit
einem D anfängt
Aufgabe: Öffnen Sie jetzt die Datenbank Biblio. Erstellen Sie eine neue Abfrage in der
Entwurfsansicht. Wählen Sie nur die Tabelle Bücher. Wie lauten die SQLBefehle für folgende Abfragen:
Notieren Sie die SQL-Befehle!
a) die Titel aller Bücher
b) die Titel der Bücher von Christie
c) Titel und Autor der Bücher, die ausgeliehen sind
(logisches Feld: 0 oder –1! )
d) den Vornamen vom Autor namens Chandler
e) wer schrieb das Buch: Volkswirtschaftslehre ?
f)
alle Titel ab der Katalognummer 400
g) die Autoren (Vorname und Name) deren Buch entweder mit einer Katalognummer ab
700 beginnt oder deren Bücher nicht ausgeliehen sind
h) die Nachnamen der Autoren, deren Vorname mit dem Buchstaben J beginnt
5
Einführung in SQL
Abfragen mit select
2.2 Operatoren in Abfragen
In SQL werden verschiedene Typen von Operatoren beschrieben
es gibt arithmetische, Vergleichs- , Zeichen- ,logische, Mengen- und verschiedene Operatoren.
Bei den meisten Beispielen folge ich zum Teil dem sehr ausführlichen Buch: "SQL in 21 Tagen" aus dem Verlag Markt und Technik2. Zum Teil habe ich die Tabellenbeispiele auf
Deutsch übersetzt.
zunächst Beispiele für arithmetische Operatoren:
Artikel Grosshandel
Betrachten Sie dazu die kleine Tabelle Preis
Tomaten
0,34
Sie besteht nur aus den beiden Spalten
Kartoffeln
0,51
Artikel und Grosshandel (=Großhandelspreis) Bananen
0,67
Rueben
0,45
Kaese
0,89
Beim letzten Datensatz (Orangen) fehlt abAepfel
0,23
sichtlich der Preis (s. u.)
Orangen
Wenn Sie die Abfrage eingeben:
SELECT ARTIKEL, GROSSHANDEL,
GROSSHANDEL + 0.15
FROM PREIS;
dann erhalten Sie:
Es wird also eine neue Spalte erzeugt, in der
das Ergebnis der Berechnung zu finden ist
ARTIKEL GROSSHANDEL Expr1002
Tomaten
0,34
0,49
Kartoffeln
0,51
0,66
Bananen
0,67
0,82
Rueben
0,45
0,6
Kaese
0,89
1,04
Aepfel
0,23
0,38
Orangen
Sie können diese neue Spalte aber auch direkt benennen:
SELECT ARTIKEL, GROSSHANDEL,
GROSSHANDEL + 0.15 as Einzelverkaufspreis
FROM PREIS;
dann erhalten Sie eine neue Spaltenüberschrift:
ARTIKEL GROSSHANDEL Einzelverkaufspreis
Tomaten
0,34
0,49
Kartoffeln
0,51
0,66
Bananen
0,67
0,82
Rueben
0,45
0,6
Kaese
0,89
1,04
Aepfel
0,23
0,38
Orangen
Aufgabe: Zur Tabelle Preis soll eine Tabelle
entstehen, die eine neue Spalte namens Sonderpreis anzeigt. Diese
Spalte soll den Wert des halben
Grosshandelspreises haben.
2
SQL in 21 Tagen, ISBN: 3-8272-2020-3
6
artikel Grosshandel Sonderpreis
Tomaten
0,34
0,17
Kartoffeln
0,51
0,255
Bananen
0,67
0,335
Rueben
0,45
0,225
Kaese
0,89
0,445
Aepfel
0,23
0,115
Orangen
Einführung in SQL
Abfragen mit select
Aufgabe: Erstellen Sie eine Abfrage zur Tabelle
Temperatur.
Sie soll folgendes Ergebnis liefern
SQL-BefehL:
Hochtemp Tieftemp Differenz
45
-10
55
48
-5
53
31
-28
59
2.2.1 Vergleichsoperatoren
mit Vergleichsoperatoren hatten Sie es schon zu tun, wenn Sie die where-Klausel verwendet hatten:
Select * from Bücher
(gibt die Bücher von A. Christie aus der Tawhere Autor ="Christie"
belle Bücher aus)
Bisher noch nicht verwendet wurde:
a) Is Null (für nicht vorhanden) Gegenteil: Is not Null
Achtung: In der Entwurfsansicht von Abfragen schreiben Sie: Ist Null, hier engl. Is Null
z. B.: where Preis Is Null (wenn die Artikel gefunden werden sollen, für die kein Preis vorhanden ist). Das Gegenteil: Is not Null heißt in der Entwurfsansicht auf deutsch: Ist nicht
leer
b) between = zwischen
Artikel Grosshandel
Kartoffeln
0,51
Bananen
0,67
select * from Preis
where grosshandel between 0.5 and 0.8
c) <> für ungleich (Oracle: !=)
SELECT *
FROM Preis
where Artikel <>"Tomaten"
d) distinct
Betrachten Sie dazu die kleine Tabelle
Namen_Orte:
die Abfrage:
SELECT Ort
FROM Namen_Orte;
zeigt alle Orte an, auch doppelt:
Schreiben Sie:
SELECT distinct Ort
listet alle Datensätze bis auf Tomaten
gleich:
where not Artikel = "Tomaten"
Name Vorname
Schmitz Regina
Mayer Ludwig
Appel Hans
Mons Gerd
Schmitz Peter
Jordan Gustav
Mayer Claudia
Jordan Hannes
Ort
Köln
Bonn
Bergheim
Troisdorf
Leverkusen
Köln
Bonn
Leverkusen
Ort
Bergheim
Bonn
7
Ort
Köln
Bonn
Bergheim
Troisdorf
Leverkusen
Köln
Bonn
Leverkusen
Einführung in SQL
FROM Namen_Orte;
um keine Orte doppelt zu sehen:
Abfragen mit select
Köln
Leverkusen
Troisdorf
name
Appel
Jordan
Mayer
Mons
Schmitz
Vorsicht bei den Namen:
SELECT distinct name
FROM Namen_Orte;
hier fehlen natürlich tatsächlich Namen!
name ort
Appel Bergheim
Jordan Köln
Jordan Leverkusen
Mayer Bonn
Mons Troisdorf
Schmitz Köln
Schmitz Leverkusen
Wenn die Orte wieder dabei sind, erscheinen
auch die fehlenden Namen (trotz distinct)
wieder:
SELECT distinct name, ort
FROM Namen_Orte;
2.2.2 Der Operator in
Mit in kann man Werte aus Spalten suchen, die verschiedene Kriterien, die auch mit or zu
verbinden wären, entsprechen.
Vergleichen Sie die folgenden Abfragen, die zum gleichen Ergebnis führen:
in
Ergebnis
or
SELECT Name,Ort
SELECT Name,Ort
Name Ort
FROM Namen_Orte
FROM Namen_Orte
Schmitz Köln
where Ort ="Bonn"
Mayer Bonn
where Ort in
Mons Troisdorf
or
("Bonn","Köln","Troisdorf")
Jordan Köln
Ort ="Köln"
Mayer Bonn
or
Ort ="Troisdorf"
Zeichenverkettung
Aufgabe: Testen Sie an der Tabelle Namen_Orte auch die Verbindung
von mehreren Spalten mit dem &-Zeichen:
SELECT Vorname &" "&Name as Gesamtname
FROM Namen_Orte;
zwischen Vorname und Nachname muss dabei ein Leerschritt eingefügt
werden.
In Oracle-Datenbanken müssen Sie statt & ein doppeltes Pipe-Zeichen ||
verwenden.
8
Gesamtname
Regina Schmitz
Ludwig Mayer
Hans Appel
Gerd Mons
Peter Schmitz
Gustav Jordan
Claudia Mayer
Hannes Jordan
Einführung in SQL
Abfragen mit select
2.3 Übungen zum Select-Befehl
Die folgenden Aufgaben sind dem Buch entnommen:
Norbert Michelmann, Rolf Hettwer: Datenbankentwicklung und –anpassung mit
MS Access und SQL, Troisdorf 2001, S. 119/120
Sie beziehen sich auf die Tabelle Mieter in der Datenbank SQL-Beispiele.mdb
Aufgabe: Erstellen Sie die folgenden Abfragen:
a.
Nachnamen und Vornamen aller Mieter
b.
Anreden, Nachnamen, Vornamen und Orte aller Berliner Mieter
c.
Nachnamen, Orte und Mietkosten von Mietern, deren Mietkosten 0 sind
d.
Anreden, Vornamen und Nachnamen der Mieter, deren Vornamensfeld keinen Wert enthält
e.
Nachnamen, Vornamen, Orte - aufsteigend/absteigend sortiert nach Nachnamen
f.
Nachnamen, Orte und Mietkosten - aufsteigend sortiert nach Orten und innerhalb eines
Ortes absteigend nach den Mietkosten
g.
Orte aller Mieter (ohne Redundanz)
h.
Nachnamen und Mietkosten der Mieter ohne Guthaben
i.
Vornamen, Nachnamen, Orte und Mietkosten der Kunden, die in München wohnen oder
kein Guthaben haben
9
Einführung in SQL
Abfragen mit select
j.
Vornamen, Nachnamen, Orte und Mietkosten aller Münchner Kunden, die kein Guthaben besitzen
k.
Anreden, Nachnamen, Vornamen und Mietkosten von Mietern, deren Kontostand zwischen 420 und 0 liegt
l.
Anreden, Nachnamen, Orte aller Mieter, die in Frankfurt wohnen
m. Anreden, Nachnamen, Postleitzahlen und Orte aller süddeutschen Mieter (Postleitzahl
'70000' und höher)
n.
Anreden, Nachnamen, Vornamen und Orte aller Mieter, die keine Firmen sind
o.
Nachnamen und Orte aller Mieter mit einem sechsstelligen Nachnamen, der mit „S"
beginnt
p.
Nachnamen und Orte aller Mieter, deren Nachname mit „S" beginnt
q.
Nachnamen und Orte aller Mieter mit einem „z" ab der 2. Stelle in ihrem Nachnamen
r.
Nachnamen und Orte aller Mieter mit einem „_" in Ihrem Nachnamen
s.
Nachnamen, Postleitzahlen und Orte aller Mieter, die nicht in Stuttgart oder München
wohnen
t.
Nachnamen, Vornamen und Orte aller Mieter, die keinen Vornamen haben.
u.
Nachnamen und Orte aller Mieter, die keine AG sind (nicht auf AG enden)
10
Einführung in SQL
Abfragen mit select
Lösungen zu den Aufgaben S. 9/10
a)
SELECT Nachname,Vorname
FROM Mieter
b)
SELECT Anrede,Nachname,Vorname,Ort
FROM Mieter
where Ort='Berlin'
c)
SELECT Nachname,Ort,Mietkosten
FROM Mieter
where Mietkosten=0
d)
SELECT Anrede, Vorname, Nachname
FROM Mieter
WHERE Vorname Is Null
e)
SELECT Nachname, Vorname, Ort
FROM Mieter
order by Nachname asc | desc
f)
g)
h)
SELECT Nachname, Mietkosten, Ort
FROM Mieter
order by ort asc, mietkosten desc
SELECT distinct Ort
FROM Mieter
SELECT Nachname
FROM Mieter
where mietkosten <0
i)
SELECT vorname, Nachname, Ort, Mietkosten
FROM Mieter
where ort ="München" or mietkosten <0
j)
SELECT vorname, Nachname, Ort, Mietkosten
FROM Mieter
where ort ="München" and mietkosten <0
SELECT anrede, Nachname, Ort
FROM Mieter
where mietkosten between 0 and 420
k)
SELECT anrede, Nachname, Ort
FROM Mieter
where ort like "Frankfurt*"
m) SELECT anrede, Nachname, PLZ, Ort
FROM Mieter
where plz>="70000"
n) SELECT anrede, Nachname, Vorname, Ort
FROM Mieter
where anrede <>"Firma"
l)
o)
Nachname Ort
Schwin
München
Schwan
Frankfurt a. M.
SELECT Nachname, Ort
FROM Mieter
WHERE nachname like "S?????"
11
Einführung in SQL
p)
Abfragen mit select
SELECT Nachname, Ort
FROM Mieter
WHERE nachname like "S*"
SELECT Nachname, Ort
FROM Mieter
WHERE nachname like "*z*"
r)
SELECT Nachname, Ort
FROM Mieter
WHERE nachname like "*_*"
s)
falsch:
SELECT Nachname,PLZ, Ort
FROM Mieter
WHERE ort <>"Stuttgart" or ort
<>"München"
richtig:
SELECT Nachname,PLZ, Ort
FROM Mieter
WHERE ort <>"Stuttgart" and ort
<>"München"
auch:
WHERE ort not like "Stuttgart" and ort not
like "München"
SELECT Nachname,Vorname, Ort
FROM Mieter
WHERE Vorname is NULL
u)
SELECT Nachname, Ort
FROM Mieter
WHERE Nachname not like "*AG"
Ort
Schwin
München
Schwan
Frankfurt a. M.
SOAG
Hamburg
Schneider
Ulm
Soft_KG
Berlin
Nachname Ort
Weitzel
Düsseldorf
q)
t)
Nachname
12
ergibt alle Datensätze, denn jeder Ort ist
entweder ungleich München bzw. Stuttgart
jeder einzelne Ort ist weder Stuttgart noch
München
Einführung in SQL
Abfragen mit select
2.4 Aggregat-Funktionen
Diese Funktionen werden auch Set-Funktionen genannt. Mit ihnen werden Auswertungen
wie Summe, Mittelwert usw. vorgenommen.
a) count
Die einfachste Auswertung dieser Art erhält man mit dem Befehl count (=zählen). Beispiel,
bezogen auf die Tabelle Preis:
Expr1000
SELECT count (*)
Das Ergebnis ist 7, weil sich in der
7
FROM Preis;
Tabelle 7 Datensätze befinden.
ANZAHL
Besser sieht es aus, wenn man einen SELECT Count(*) AS ANZAHL
7
FROM
Preis;
Aliasnamen verwendet
ANZAHL
3
Auch hier ist natürlich die Angabe von SELECT Count(*) AS ANZAHL
FROM Preis
Bedingungen mit where möglich
WHERE Grosshandel>0.5
b) sum
Mit diesem Befehl bildet man die Summe von Feldinhalten. Mit dem folgenden Beispiel wird
die Summe der Grosshandelspreise gebildet
Summe_Grosshandel
3,09
select sum (Grosshandel) as Summe_Grosshandel
from preis
c) min
Das Minimum von Feldinhalten wird mit der Funktion min ermittelt:
Expr1000
-28
SELECT min (Tieftemp)
FROM Temperatur;
auch hier ist es meistens übersichtlicher, wenn mit einem Aliasnamen gearbeitet wird:
"Tiefste Temperatur"
-28
SELECT min (Tieftemp) as "Tiefste Temperatur"
FROM Temperatur;
d) max
Das Maximum ist entsprechend max:
SELECT max (Hochtemp) as "Höchste Temperatur"
FROM Temperatur;
"Höchste Temperatur"
48
e) avg
Das Wort Mittelwert heißt auf englisch "average", deshalb heißt die Funktion avg
SELECT avg (Hochtemp) as "Durchschnitt der Höchstwerte"
FROM Temperatur;
13
"Durchschnitt der
Höchstwerte"
41,3333333333333
Einführung in SQL
Abfragen mit select
2.5 Übungsaufgaben
Alle Aggregat-Funktionen lassen sich mit where-Klauseln verbinden. Verwenden Sie für die
folgenden Übungsbeispiele die Tabelle Getränke:
1)
Zählen Sie in der Getränke-Tabelle, von wievielen Artikeln mehr als 45 Stück auf Lager
sind. Die Zählung soll überschrieben werden mit über 45 auf Lager
2)
Bilden Sie den Mittelwert der Einkaufspreise von den Artikeln, die vom Lieferanten Maisel
bezogen werden. Verwenden Sie als Überschrift Mittelwert der EKpreise von Maisel
3)
Das Maximum im Bestand der Artikel, die als 0.7-Liter-Flaschen gehandelt werden
4)
Die Summe des Bestands der Spirituosen, deren Einkaufspreis zwischen 8,00 und
12,00 liegt
5)
Das Minimum des Bestands der Artikel, die kein Bier sind.
6)
wie Aufg. 5), aber zusätzliche Bedingung: Die Getränke sollen nicht vom Lieferanten
Müller KG sein.
Noch einige Aufgaben zur Tabelle Mieter:
a) den Mittelwert der Mietkosten der Düsseldorfer Mieter, überschrieben: "Mittelwert D-dorf"
b) die Summe der Mietkosten der Privatpersonen
c) das Minimum der Mietkosten bei den Mietern, deren Postleitzahl mit 0, 1, 2, 3 oder 4
beginnt, überschrieben mit "PLZ unter 50000"
d) die Summe der Mietkosten von den Leuten, deren Nachname mit Sch beginnt.
14
Einführung in SQL
Abfragen mit select
Lösungen zu den Aufgaben S. 14
1) SELECT count (*) as 'über_45 auf Lager'
FROM Getränke
where Bestand >45
2) SELECT avg (EKPREIS) as 'Mittelwert der EKpreise von Maisel'
FROM Getränke
where Lieferant ="Maisel"
3) SELECT max (Bestand)
FROM Getränke
where packung ="0.7"
as 'Höchstzahl von Einzelflaschenl'
4) SELECT sum (Bestand) as 'Summe'
FROM Getränke
where Art ="Spirituosen" and ekpreis between 8 and 12
5) SELECT min (Bestand)
FROM Getränke
where Art<>"Bier"
as 'Minimal'
6) SELECT min (Bestand) as 'Minimal'
FROM Getränke
where Art<>"Bier" and not Lieferant ="Müller KG"
a) SELECT avg (Mietkosten) as "Mittelwert D-dorf"
FROM Mieter
where Ort="Düsseldorf";
b) SELECT sum (Mietkosten)
FROM Mieter
where Anrede <>"Firma"
c) select min (Mietkosten) as "PLZ unter 50000"
FROM Mieter
where PLZ <"5"
d) select sum (Mietkosten)
FROM Mieter
where Nachname like "Sch*"
2.6 Select-Befehl mit Gruppierung
mit dem Zusatz: "group by" lassen sich Gruppen von Ergebnissen bilden, z. B. so:
select Ort, sum (Mietkosten) as "Summe
Ort
"Summe Mietkosten"
Mietkosten"
Aachen
0
from Mieter
group by ort
Statt der ursprünglich 15 Zeilen der Tabelle Mieter
werden nur noch 10 Zeilen angezeigt, weil die Mietkosten von Mietern aus gleichen Orten summiert
wurden.
Berlin
Düsseldorf
Frankfurt a. M.
Frankfurt/Oder
Hamburg
Köln
München
Stuttgart
Ulm
-104,75
10750
-265
210,5
-5423,25
0
-45,75
0
2110,9
"Summe Mietkosten"
wichtig ist für eine sinnvolle Ausgabe, dass man in
0
der ersten Zeile auch das Feld aufführt, nach dem
-104,75
gruppiert wird (hier: Ort), denn sonst weiß man in der
10750
Ausgabe nicht, um was es sich handelt:
-265
15
Einführung in SQL
Abfragen mit select
Aufgabe: Erstellen Sie eine SQL-Abfrage zur Tabelle
1
Mieter, die das folgene Ergebnis erzielt:
gruppiert nach dem Feld Anrede (also getrennt für Firmen, Frauen und Männer) soll
der Mittelwert der Mietkosten ermittelt werden.
anrede "Mittelwert der Mietkosten"
Firma
-2869,375
Frau
2433,75
Herr
359,6
Auch mit dem count-Befehl läßt sich in Gruppierungen arbeiten:
SELECT Anrede, count (*) as Anzahl
FROM Mieter
group by anrede;
Anrede Anzahl
Firma
2
Frau
4
Herr
9
Vorsicht: Diese Abfrage führt
zu einer Fehlermeldung:
SELECT Anrede, count (*)
FROM Mieter
group by Ort;
Es ist nicht möglich, nach einem Feld zu gruppieren, das nicht dem select-Ausdruck entspricht.
Aufgabe: Zählen Sie die Anzahl Mieter in den einzelnen
Orten:
2
Aufgabe: Ermitteln Sie den kleinsten Wert (Minimum) der
Mietkosten, nach Orten gruppiert
3
ort Anzahl Mieter
Aachen
1
Berlin
4
Düsseldorf
2
Frankfurt a. M.
2
Frankfurt/Oder
1
Hamburg
1
Köln
1
München
1
Stuttgart
1
Ulm
1
ort "kleinster Wert"
Aachen
0
Berlin
-315,5
Düsseldorf
750
Frankfurt a. M.
-385
Frankfurt/Oder
210,5
Hamburg
-5423,25
Köln
0
München
-45,75
Stuttgart
0
Ulm
2110,9
Öffnen Sie jetzt die Datenbank Getränke.
Aufgabe: Gruppiert nach Lieferanten soll die An4
zahl der Getränkesorten (durch Zählen
der Artikel) ermittelt werden:
Aufgabe: Gruppiert nach Packung soll das Minimum im Bestand herauskommen:
5
16
Lieferant "Anzahl Getränkesorten"
Bolz GmbH
5
Maisel
4
Müller KG
6
Schnaps & Co
1
packung "geringste Bestandsmenge"
0.7
18
Einweg 6*0.33
35
Kasten 0.5*20
30
Kasten 0.7*12
25
Einführung in SQL
Aufgabe: Der geringste Einkaufspreis innerhalb einer Getränke-Art:
6
Abfragen mit select
art "kleinster Einkaufspreis"
Bier
3,85
Spirituosen
5,48
Tonic
1,21
Wasser
2,86
Aufgabe: Gruppierung nach Lieferanten
und dem Mittelwert vom Verkaufspreis
7
Lieferant "mittlerer Verkaufspreis"
Bolz GmbH
8,682
Maisel
6,68
Müller KG
9,93666666666667
Schnaps & Co
16
Aufgabe: Summe des Bestands, gruppiert
Lieferant "Stück auf Lager"
Bolz GmbH
185
Maisel
185
Müller KG
234
Schnaps & Co
28
nach Lieferant:
8
Aufgabe: Zählung der Artikel je nach
Packungseinheit
9
packung "wieviel Einheiten?"
0.7
7
Einweg 6*0.33
1
Kasten 0.5*20
6
Kasten 0.7*12
2
(Lösungen 1-9 auf der nächsten Seite)
2.7 Die Having-Klausel
Einschränkende Bedingungen mittels where-Klausel haben wir bereits besprochen. Das
folgende Beispiel summiert - zunächst ohne Einschränkung – die Mietkosten der MieterTabelle, gruppiert nach Anrede, also getrennt nach Firmen, Frauen und Männern.
SELECT Anrede, sum(mietkosten) as "Mietkosten"
FROM Mieter
group by Anrede
Anrede "Mietkosten"
Firma
-5738,75
Frau
9735
Herr
3236,4
Wenn wir dasselbe Ergenis, jedoch ohne Firmen, erhalten wollen, dann lautet die SQLAbfrage:
SELECT Anrede, sum(mietkosten) as "Mietkosten"
FROM Mieter
where Anrede <>"Firma"
Anrede "Mietkosten"
Frau
9735
Herr
3236,4
group by Anrede
Die where-Klausel lässt sich also zusammen mit Gruppierungen verwenden, allerdings
nicht, wenn innerhalb der where-Klausel Aggregatfunktionen (sum, avg, min, max, count)
verwendet werden.
Sehen Sie dazu als Beispiel die folgende Abfrage an:
SELECT Anrede, sum(mietkosten) AS "Mietkosten"
FROM Mieter
GROUP BY Anrede
where sum(mietkosten)>5000;
Wenn eine rechnerische Auswertung Teil einer Bedingung sein soll, funktioniert die whereKlausel nicht mehr; stattdessen müssen Sie in dem Fall mit having arbeiten.
SELECT Anrede, sum(mietkosten) AS ["Mietkosten"]
FROM Mieter
GROUP BY Anrede
having sum(mietkosten)>5000;
17
Anrede "Mietkosten"
Frau
9735
Einführung in SQL
Abfragen mit select
Lösungen S. 16/17:
1
select anrede, avg (Mietkosten) as "Mittelwert der Mietkosten"
from Mieter
group by anrede;
2
SELECT ort, count (ort) as "Anzahl Mieter"
FROM Mieter
group by Ort;
3
SELECT ort, Min(Mietkosten) as "kleinster Wert"
FROM Mieter
group by Ort;
4
SELECT Lieferant, count (*) as "Anzahl Getränkesorten"
FROM Getränke
group by Lieferant;
5
SELECT packung, min(bestand) as "geringste Bestandsmenge"
FROM Getränke
group by packung;
6
SELECT art, min(ekpreis) as "kleinster Einkaufspreis"
FROM Getränke
group by art;
7
SELECT Lieferant, avg(VKpreis) as "mittlerer Verkaufspreis"
FROM Getränke
group by Lieferant;
8
SELECT Lieferant, sum(bestand) as "Stück auf Lager"
FROM Getränke
group by Lieferant;
9
SELECT packung,count(*) as "wieviel Einheiten?"
FROM Getränke
group by packung;
Fortsetzung Having-Klausel:
Sehen Sie sich dazu die Tabelle Abteilung an:
Name
Abteilung
Groß
Klein
Peters
Müller
Gardner
Lindemann
Kussnacht
Gernegroß
Hünzer
Klinger
Forschung
Marketing
Personal
Forschung
Marketing
Personal
Forschung
Marketing
Personal
Forschung
Gehalt
2.670,00 €
2.890,00 €
2.230,00 €
2.700,00 €
2.480,00 €
2.200,00 €
2.780,00 €
2.300,00 €
3.100,00 €
4.270,00 €
Abschluss
Ausbildung
FH
FH
Uni
Uni
Ausbildung
FH
Ausbildung
Uni
Uni
UrlaubsKrank_Tage Einstelldatum
tage
32
30
28
30
30
28
28
30
29
30
18
10
12
5
0
7
5
0
20
11
10
01.01.90
01.01.93
01.01.97
01.04.95
01.07.97
01.01.98
01.01.93
01.03.92
01.01.96
01.01.95
Datum
Beförderung
01.06.96
01.06.99
01.03.00
01.03.96
01.06.98
01.01.00
01.01.99
Verheiratet
Ja
Nein
Nein
Ja
Ja
Ja
Nein
Ja
Nein
Ja
Einführung in SQL
Abfragen mit select
Abteilung Gehaltssumme
Forschung
12420
Marketing
7670
Personal
7530
SELECT Abteilung, sum(gehalt) as "Summe der Gehälter"
FROM Abteilung
group by abteilung;
Abteilung Gehaltssumme
Forschung
12420
Aufgabe 1: Welche SQL-Abfrage lässt nur die Abteilung mit
einer Gehaltssumme über 8000 herauskommen?
Aufgabe 2: Bilden Sie die Gehaltssumme aller verheirateten bzw. nicht verheirateten Kollegen
verheiratet Gehaltssumme
Ja
16620
Nein
11000
Aufgabe 3: Zeigen Sie den Mittelwert der Krank_tage , grup-
abschluss
Kranktage
Ausbildung 11,6666666666667
piert nach Abschluss, für die Kollegen an, bei
denen der Mittelwert in der Abschlussgruppe über
10 Krankentage liegt:
Aufgabe 4: Ermitteln Sie das höchste Gehalt in
abteilung "höchstes Gehalt"
Forschung
4270
Marketing
2890
Personal
3100
jeder Abteilung:
Aufgabe 5: Wie Aufgabe 4, aber nur für die Abteilungen, wo
abteilung "höchstes Gehalt"
Forschung
4270
Personal
3100
das höchste Gehalt über 3000 € liegt:
Aufgabe 6: In welcher Abteilung liegt das Maximum der
Urlaubstage über 30?
abteilung "am meisten Urlaub"
Forschung
32
Aufgabe 7: Wie hoch ist die Summe der "krankgefeierten" Tage in
der Abteilung, in der – wie bei Aufg. 6) das Maximum
der Urlaubstage über 30 liegt?
Aufgabe 8: Gefragt sind Name und Abteilung von den Mitarbeitern,
die schon einmal befördert wurden
Abteilung befördert wurden
Aufgabe 10: Nur die Anzahl beförderter Kollegen aus der Abteilung
Aufgabe 11: Name und Datum der Beförderung von den
Kollegen, bei denen zwischen Einstellung und
Beförderung weniger als 4 Jahre vergingen
Aufgabe 12: Abteilung und durchschnittl. Urlaubstage der
Abteilung Marketing
19
Name
Groß
Müller
Gardner
Kussnacht
Gernegroß
Hünzer
Klinger
Abteilung
Forschung
Forschung
Marketing
Forschung
Marketing
Personal
Forschung
befördert Abteilung
4 Forschung
2 Marketing
1 Personal
Aufgabe 9: Jetzt soll gezählt werden, wie viele Kollegen je nach
Marketing
abteilung "Krank"
Forschung
20
befördert Abteilung
2 Marketing
name Datum_Beförderung
Gardner
01.03.00
Kussnacht
01.03.96
abteilung "Durchschnitt Urlaub"
Marketing
30
Einführung in SQL
Abfragen mit select
Aufgabe 13: Abschluss und Gehalt der Angestellten mit
Uni-Abschluss
Aufgabe 14: Das geringste Gehalt in jeder nach Abschluss
gebildeten Gruppe
abschluss
Uni
Uni
Uni
Uni
Gehalt
2.700,00 €
2.480,00 €
3.100,00 €
4.270,00 €
abschluss Expr1001
Ausbildung
2200
FH
2230
Uni
2480
Aufgabe 15: Das geringste Gehalt in der Gruppe, die FH-Abschluss hat
Lösungen zu den Aufgaben S. 19
1. SELECT [Abteilung], sum([Gehalt]) AS Gehaltssumme
FROM Abteilung
GROUP BY [Abteilung]
having sum(gehalt) >8000
2. SELECT verheiratet, sum([Gehalt]) AS Gehaltssumme
FROM Abteilung
GROUP BY verheiratet
3. SELECT abschluss, avg(krank_tage) as Kranktage
FROM Abteilung
GROUP BY Abschluss
having avg(krank_tage)>10
4. SELECT abteilung, max(gehalt) as "höchstes Gehalt"
FROM Abteilung
GROUP BY Abteilung
5. wie 4) letzte Zeile: having max(gehalt) >3000
6. SELECT abteilung, max(urlaubstage) as "am meisten Urlaub"
FROM Abteilung
GROUP BY Abteilung
having max(Urlaubstage)>30
7. SELECT abteilung, sum(krank_tage) as "Krank"
FROM Abteilung
GROUP BY Abteilung
having max(Urlaubstage)>30
8. SELECT Name, Abteilung
FROM Abteilung
where Datum_beförderung is not Null;
9. SELECT count(*) as befördert, Abteilung
FROM Abteilung
where Datum_beförderung is not Null
group by abteilung;
20
abschluss Expr1001
FH
2230
Einführung in SQL
Abfragen mit select
10. SELECT count(*) as befördert, Abteilung
FROM Abteilung
where Datum_beförderung is not Null
group by abteilung
having Abteilung="Marketing"
11. SELECT name,Datum_Beförderung
FROM Abteilung
where Datum_beförderung - Einstelldatum <365*4
12. SELECT abteilung, avg(Urlaubstage) as "Durchschnitt Urlaub"
FROM Abteilung
group by abteilung
having abteilung="Marketing"
13. SELECT abschluss, Gehalt
FROM Abteilung
where Abschluss="Uni"
14. SELECT abschluss, min(Gehalt)
FROM Abteilung
group by abschluss
15. SELECT abschluss, min(Gehalt)
FROM Abteilung
group by abschluss
having abschluss="FH"
2.8 Daten ändern: update
Um Änderungen an den Daten vorzunehmen, brauchen Sie den Befehl update.
Die Syntax lautet:
UPDATE Tabellenname
SET Spaltenname1 = Wert1
[, Spaltenname2 = Wert2]...
WHERE Suchbedingung
Beispiel:
In der Tabelle Artikel wird der
UPDATE Artikel
Ekpreis des Artikels mit der
SET Ekpreis = 120
ArtNr 129 auf 120 € erhöht
WHERE ArtNr = 129;
Vorsicht: ohne where-Klausel werden alle Werte ersetzt:
UPDATE Artikel
alle Artikel hätten dann den Wert 500!
SET EKpreis = 500;
Wenn prozentuale Änderungen des bestehenden Wertes vorgenommen werden sollen, dann
muss das Feld mit seinem alten Wert zur Berechnung herangezogen werden:
UPDATE Artikel
SET Ekpreis = Ekpreis *1.1;
In der Tabelle Artikel wird der
Ekpreis aller Artikel um 10 % erhöht
Es kann auch sinnvoll sein, dabei ein Feld in Abhängigkeit von einem anderen Feld zu
bestimmen. Im folgenden Beispiel soll von allen Artikeln der Verkaufspreis das 1,5-fache des
Einkaufspreises betragen:
UPDATE Artikel
SET VKpreis = Ekpreis *1.5;
21
Einführung in SQL
Abfragen mit select
Das letzte Beispiel, aber nur für einen bestimmten Lieferanten:
UPDATE Artikel
SET VKpreis = Ekpreis *1.5;
where LiefNr=350
Aufgabe: Legen Sie die hier abgebildete Tabelle Abteilung zugrunde, um mit dem updateBefehl einige Änderungen durchzuführen:
1. Das Gehalt des Mitarbeiters Lindemann wird um 100 € erhöht.
2. Die Urlaubstage der Leute aus der Abteilung Marketing werden um 2 Tage erhöht.
3. Das Gehalt aller Mitarbeiter steigt um 3 %.
4. Das Gehalt der Leute mit weniger als 30 Urlaubstagen steigt um 75 €.
5. Der Kollege Klinger wird in die Abteilung Marketing versetzt.
2.9 Verbindung von Tabellen (Join)
Die bisher betrachteten Beispiele haben sich jeweils nur auf eine Tabelle bezogen. Im Folgenden geht es um Select-Abfragen, die ihre Ergebnisse aus verschiedenen Tabellen beziehen (s. auch Buch Michelmann/Hettwer ab S. 143).
Eine wichtige Anforderung an relationale Datenbanken ist die Zerlegung der Informationen in
verschiedene Tabellen. Dazu dient der Prozess der Normalisierung (1.-3. Normalstufe).
Durch Abfragen werden die Informationen, die auf verschiedene Tabellen verteilt sind, zusammengeführt.
Bei der Arbeit mit Access haben Sie gesehen, wie die Verbindung der Tabellen durch Beziehungen erfolgt (1:1 und 1:n-Beziehungen); die Tabellen werden über Primär- und Fremdschlüssel miteinander verbunden.
Dass es Primär- und Fremdschlüssel in den Tabellen gibt, ist auch für Abfragen mittels SQL
Vorbedingung. Die Verbindung der Tabellen muss aber durch entsprechende Befehle erfolgen, bzw. innerhalb des Select-Befehls muss dem Datenbanksystem mitgeteilt werden, welche Verbindung zwischen den Tabellen besteht - SQL "weiß" ja nichts vom Beziehungsfenster in Access, in welchem wir bisher die Beziehungen hergestellt und grafisch dokumentiert
haben.
Wenn Tabellen miteinander in Beziehung gesetzt werden, dann gibt es oft dieselben Feldnamen in Tabelle A und Tabelle B. Auch wenn es keine Anforderung an die Verbindung von
Tabellen durch Primär- und Fremdschlüssel ist, dass die Felder, über die man die Verbindung herstellt, den selben Namen haben, dann macht man es oft der Übersichtlichkeit halber.
Um klar zu machen, um welches Feld aus welcher Tabelle es sich handelt, muss dann entweder immer der komplette Tabellenname zusätzlich angegeben werden, abgetrennt durch
einen Punkt, oder (besser) man verwendet Aliasnamen für die Tabellen. In der Regel ver-
22
Einführung in SQL
Abfragen mit select
fährt man dabei so, dass man den Tabellennamen durch einen Buchstaben abkürzt, also
z. B. die Tabelle Artikel durch A. (Beachten Sie den Unterschied: Die Aliasnamen, die wir
bisher verwendet hatten, bezogen sich auf Spaltenüberschriften).
Das folgende Beispiel zeigt dies, zunächst noch nicht auf die Verbindung von Tabellen bezogen. Sie sehen dabei an der linken Spalte mit Alias, dass der Aliasname A für die Tabelle
Artikel erst in der zweiten Zeile vergeben wird, nachdem man ihn schon der ersten Zeile
verwenden konnte!
ohne Aliasname
Verwendung von Aliasname für Tabelle
SELECT Artikel.Bezeichnung
from Artikel;
SELECT A.Bezeichnung
from Artikel A;
Die Verwendung von Aliasnamen spart dann auch Zeit: Die Tabellen bekommen einen
Buchstaben als Alias zugewiesen. Dann kann immer mit dem Buchstaben auf die Tabelle
verwiesen werden, wenn gesagt werden muss, aus welcher Tabelle ein Feld stammt. Vor
allem bei Abfragen, die aus vier, fünf oder mehr Zeilen bestehen (mit where- und havingKlauseln usw.), in denen immer wieder die Feldnamen angesprochen werden müssen, ist
dies wichtig.
Die folgenden Beispiele beziehen sich auf die Datenbank Firma, die mit Access erstellt wurde. Testen Sie dazu zunächst den sogenannten Cross Join, in dem jedes Feld der einen
Tabelle mit der anderen Tabelle verbunden wird (kartesisches Produkt). Das Ergebnis macht
tatsächlich keinen Sinn, weil alles mit jedem verbunden wird:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A,Lieferant L;
usw., 36 Zeilen bei 12 Artikeln und drei Lieferanten
jeder Lieferant steht bei jedem Artikel!
Bezeichnung
Lieferant
Haube für Tastatur Meyer OHG
Haube für Tastatur Computer-Versand
Haube für Tastatur Schmitz AG
Disketten 3 1/2 DD Meyer OHG
Disketten 3 1/2 DD Computer-Versand
Disketten 3 1/2 DD Schmitz AG
Wenn die Gleichsetzung über das Feld, welches in beiden Tabellen vorkommt (Lieferantennummer), gemacht wird, dann ist ein sinnvolles Ergebnis zu sehen. Man nennt es Equi Join:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A, Lieferant L
where A.Lieferantennummer=L.LiefNr;
mit der where-Klausel wird die Gleichsetzung
erzielt:
Die Zeilen werden angezeigt, wo die Liefnummer der Tabelle Lieferant gleich der Lieferantennummer der Tabelle Artikel ist
Bezeichnung
Lieferant
Haube für Tastatur
Meyer OHG
Disketten 3 1/2 DD
Meyer OHG
Maus seriell
Meyer OHG
Monitor VGA
Meyer OHG
Centronics-Kabel
Meyer OHG
Disketten 5 1/4 HD
Computer-Versand
Tastatur deutsch
Computer-Versand
Disketten 3 1/2 HD
Computer-Versand
Motherboard ASUS A7 V3
Schmitz AG
Disketten 5 1/4 DD
Schmitz AG
Farbband AX 35
Schmitz AG
Farbband AX 55
Schmitz AG
Vorsicht: Die einschränkende Bedingung durch die where-Klausel reicht nicht aus, wo nach
bestimmten Daten gesucht wird.
Angenommen, Sie wollen die Artikelbezeichnung und den Lieferantennamen anzeigen, und
zwar nur für die Artikel, die von Lieferant Meyer OHG geliefert werden. (b.w.!)
23
Einführung in SQL
Abfragen mit select
SELECT A.Bezeichnung, L.Lieferant
from Artikel A, Lieferant L
where L.Lieferant="Meyer OHG";
Die Einschränkung durch die where-Klausel reicht
nicht aus. Es wird das Cross Join gebildet, und bei
jedem Artikel wird Meyer OHG angezeigt
es sind alle Artikel, bei jedem steht Meyer OHG!
Bezeichnung
Haube für Tastatur
Disketten 3 1/2 DD
Disketten 5 1/4 HD
Motherboard ASUS A7 V3
Maus seriell
Disketten 5 1/4 DD
Tastatur deutsch
Monitor VGA
Farbband AX 35
Farbband AX 55
Disketten 3 1/2 HD
Centronics-Kabel
Lieferant
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
So ist es richtig: Equi Join, dann mit and die Einschränkung auf den einen Lieferanten:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A, Lieferant L
where A.Lieferantennummer=L.LiefNr
and L.Lieferant="Meyer OHG";
Bezeichnung
Lieferant
Haube für Tastatur Meyer OHG
Disketten 3 1/2 DD Meyer OHG
Maus seriell
Meyer OHG
Monitor VGA
Meyer OHG
Centronics-Kabel Meyer OHG
Genauso suchen Sie auch nach einem ganz bestimmten Artikel, für den Daten aus den beiden Tabellen benötigt werden:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A ,Lieferant L
where A.Lieferantennummer=L.LiefNr
and A.Bezeichnung="Haube für Tastatur";
Bezeichnung
Lieferant
Haube für Tastatur Meyer OHG
Die folgende Abfrage sucht nach den Artikelnummern von dem Lieferanten, der im Ort
Sprockhövel ansässig ist:
SELECT A.Artikelnummer,L.Lieferant
from Artikel A, Lieferant L
where A.Lieferantennummer=L.LiefNr
and L.Ort="Sprockhövel"
Artikelnummer
129
206
282
391
639
Lieferant
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Die allgemeine Form des Equi Join lautet also:
SELECT Tabelle1.Feld1, Tabelle2.Feld3
FROM Tabelle1, Tabelle2
WHERE Tabelle1.Feld2 = Tabelle2.Feld2
2 beliebige Felder
in zwei Tabellen
in denen ein Feld (Feld2) gleiche Einträge
hat
Die Felder, die in der ersten Zeile mit Select ausgewählt werden, müssen nicht unbedingt
aus beiden Tabellen stammen, dann aber ein Teil der Bedingung – sonst läge gar kein Join
vor. Sehen Sie dazu folgendes Beispiel:
24
Einführung in SQL
Abfragen mit select
Aufgabe: Wie lauten Artikelnummer und Bezeichnung von den Artikeln, deren Lieferantennummer 25 ist und von denen mehr als 100 Stück vorrätig sind?
SELECT A.Artikelnummer,A.Bezeichnung
from Artikel A, Lieferant L
where A.Lieferantennummer=L.LiefNr
and L.LiefNR=25
and A.Menge>100
Artikelnummer Bezeichnung
206 Disketten 3 1/2 DD
208 Disketten 5 1/4 HD
331 Disketten 5 1/4 DD
352 Tastatur deutsch
452 Farbband AX 35
463 Farbband AX 55
530 Disketten 3 1/2 HD
Die Verbindung kann sich
natürlich über mehr als zwei
Tabellen erstrecken.
Durch eine SQL-Abfrage
sollen Artikelbezeichnung
und Name des Kunden von
den Verkaufsdaten angezeigt werden, wo der Kunde
namens Hartfiel etwas gekauft hat. Dazu muss das
Beziehungsmodell der Datenbank herangezogen werden:
Sie sehen dabei, dass die
Verbindung von Kunde zu
Artikel über die beiden Tabellen Verkauf und Verkaufdetails läuft.
Deshalb müssen in der Abfrage alle Verbindungen aufgeführt werden:
SELECT A.Bezeichnung, K.Name, VD.Menge
from Artikel A, Kunde K, Verkauf Vk,
VerkaufDetails VD
where Vk.Kundennummer = K.Kundennummer
and Vk.VerkaufNr = VD.VerkaufNr
and Vd.Artikelnummer=A.Artikelnummer
and K.Name="Hartfiel"
Bezeichnung Name Menge
Centronics-Kabel Hartfiel
50
Versuchen Sie nun, die folgenden Beispiele zur Datenbank Firma zu lösen:
1. Wie lautet die Adresse des Lieferanten, der den Artikel mit der Nummer 129 liefert?
Lieferant
Straße PLZ
Ort
Meyer OHG Haßlinghauser Straße 89 43320 Sprockhövel
2. Wer hat nach dem 15.08.01 etwas bestellt?
(Vorsicht: in der SQL-Version, die in Access zur Anwendung kommt, muss das Datum in der Form #8/15/2001# (Monat/Tag/Jahr) angegeben werden)
Name
Grieswelle
Hartfiel
Bestelldatum
12.11.01
30.09.01
25
Einführung in SQL
Name
Schäfer
Schäfer
Abfragen mit select
Bestelldatum
25.09.01
13.10.01
3. An welchem Datum hat der Kunde mit der Nummer 10903 etwas bestellt?
Bestelldatum
13.05.01
20.05.01
30.06.01
4. Welcher Kunde hat Artikel mit einem Einzelverkaufspreis über 100 Euro bestellt?
Name
Handke
Hillmann
5. Der Mindestbestand und die Artikelbezeichnung von den Artikeln, die Meyer OHG liefert, soll aufgelistet werden.
Mindestbestand
10
15
20
50
10
Bezeichnung
Haube für Tastatur
Disketten 3 1/2 DD
Maus seriell
Monitor VGA
Centronics-Kabel
6. Welcher Lieferant liefert welche bruchempfindlichen Artikel?
Lieferant
Schmitz AG
Meyer OHG
Meyer OHG
Bezeichnung
Motherboard ASUS A7 V3
Monitor VGA
Centronics-Kabel
Centronics-Kabel sind normalerweise nicht
bruchempfindlich, aber so sind sie in der
Tabelle gespeichert!
7. Name und Anschrift des Kunden, der ein Farbband gekauft hat (Achtung: Es gibt
zwei Sorten Farbbänder)
Name
Grieswelle
Handke
Hillmann
Schäfer
Straße
Uferweg 9
Wörterweg 19
Kölner Straße 80
Bergstraße 7
PLZ
50983
80702
40217
60321
Ort
Köln
München
Düsseldorf
Frankfurt
8. Artikelbezeichnung, Name des Kunden, gekaufte Menge und Gesamtpreis (Verkaufspreis mal Menge sowie die Mehrwertsteuer von 16 % von den Verkaufsvorgängen, bei denen Nettopreis mal verkaufte Menge über 2000 Euro lag.
Bezeichnung
Name
Menge
Motherboard ASUS A7 V3
Motherboard ASUS A7 V3
Maus seriell
Centronics-Kabel
Hillmann
Handke
Schäfer
Grieswelle
10
3
30
100
26
Gesamtprei
s
9800
2940
2250
2327
MWSt
1568
470,4
360
372,32
Einführung in SQL
Abfragen mit select
Lösungen zu den Aufgaben S. 25/26
SELECT L.Lieferant, L.Straße, L.PLZ, L.Ort
1.
from Lieferant L, Artikel A
where A.Lieferantennummer=L.LiefNr
and A.Artikelnummer=129;
2.
SELECT distinct K.Name, VK.Bestelldatum
FROM Kunde AS K, Verkauf AS VK, Verkaufdetails AS VD
WHERE K.Kundennummer=VK.Kundennummer
AND VK.VerkaufNr=VD.VerkaufNr
AND VK.Bestelldatum>#8/15/2001#;
3.
SELECT Vk.Bestelldatum
from Kunde K, Verkauf VK
where K.Kundennummer=VK.Kundennummer
and K.Kundennummer=10903;
4.
SELECT distinct K.Name
FROM Artikel A, Kunde K, Verkauf Vk, VerkaufDetails VD
WHERE Vk.Kundennummer=K.Kundennummer
And Vk.VerkaufNr=VD.VerkaufNr
And Vd.Artikelnummer=A.Artikelnummer
and A.Einkaufspreis>100;
5.
SELECT A.Mindestbestand, A.Bezeichnung
FROM Lieferant L, Artikel A
WHERE A.Lieferantennummer=L.LiefNr
And L.Lieferant="Meyer OHG";
6.
SELECT L.Lieferant, A.Bezeichnung
FROM Lieferant L, Artikel A, Lager LG
WHERE A.Lieferantennummer=L.LiefNr
and LG.Artikelnummer=A.Artikelnummer
And LG.bruchempfindlich=-1;
7.
Select distinct K.Name, K.Straße, K.PLZ, K.Ort
from Kunde K, Artikel A, Verkauf VK, Verkaufdetails VD
where K.Kundennummer= VK.Kundennummer
and Vk.Verkaufnr=VD.Verkaufnr
nd Vd.Artikelnummer=A.Artikelnummer
nd A.Bezeichnung like "Farbband*";
8.
select A.Bezeichnung, K.Name, vd.Menge, A.Verkaufspreis*VD.Menge as
Gesamtpreis, A.Verkaufspreis*VD.Menge*0.16 as MWSt
from Kunde K, Artikel A, Verkauf VK, Verkaufdetails VD
where K.Kundennummer= VK.Kundennummer
and Vk.Verkaufnr=VD.Verkaufnr
and Vd.Artikelnummer=A.Artikelnummer
and VD.Menge*A.Verkaufspreis>2000;
27
PHPMyAdmin
Einsatz von MySQL unter Windows
3 Zugriff auf Internet-Datenbanken
Datenbanken im Internet werden für viele Zwecke eingesetzt. Einige Beispiele:
⇒ Autobörsen wie autoscout24.de oder mobile.de
⇒ Webshops, die ein Artikelsortiment anbieten, das über eine Datenbank abgerufen wird
⇒ Auktionen bei ebay
Im Zusammenhang mit der medizinischen Dokumentation lassen sich ebenfalls viele Beispiele finden:
⇒ Abfrage von Verschlüsselungen, z. B. ICD-10 oder die Verschlüsselung von OPS-301
⇒ Medizinische Datenbanken nach Forschungsergebnissen, Veröffentlichungen, Medikamenten usw. abfragen
Um einen Datenbankzugriff mit einer Website zu ermöglichen, muss die Datenbank auf dem
Webserver bereitgestellt werden.
Wenn man den Aufruf einer "normalen" HTML-Datei im Internet durch einen Nutzer darstellt,
dann wird HTML übertragen. Der Browser (z. B. der Internet Explorer) kann dann den Aufbau der Seite darstellen. Wichtig im Unterschied zu Datenbanken ist: Wenn eine Adresse
aufgerufen wird, erfolgt die Übertragung des gesamten HTML-Codes zum Client.
HTML
Webserver
Client (Nutzer)
Wenn auf Inhalte eine Datenbank zugegriffen wird, dann muss beim Provider (dem Anbieter
der Internet-Dienstleistungen, auf dessen Server auch die HTML-Dateien gespeichert sind)
der Zugriff auf die Datenbank erfolgen. Im Unterschied zur einfachen, statischen HTML-Seite
kann natürlich nicht die komplette Datenbank zum Kunden übertragen werden. Nur die Auswahl an Daten, die der Kunde abruft, wird bereitgestellt und zum Kunden übertragen.
HTML
PHP
Datenbank
(z. B. MySQL)
Client (Nutzer)
Webserver
Der Kunde ruft eine HTML-Datei ab, die z. B. ein Formular zur Auswahl von Daten darstellt.
In dieses Formular trägt der Kunde seine Wünsche ein und sendet die Datei ab. De Webserver nimmt auf, was der Kunde gesandt hat, und reicht dies an die Datenbank weiter. Dazu
benötigt der Server z. B. PHP. Diese Programmiersprache wird deshalb als "serverseitige
Skriptsprache" beschrieben.
PHP reicht die Ergebnisse der Abfrage an die Datenbank an den Webserver weiter (vermittelt über PHP). Die Ausgabe an den Kunden erfolgt wieder über reines HTML, vom PHPCode bekommt der Kunde nichts zu sehen.
Um eine Datenbank im Web einzurichten, benötigt man
a)
Kenntnis in MySQL, PHP und SQL (denn SQL enthält die eigentliche Datenbankabfrage)
b)
einen Provider, der MySQL und PHP anbietet. Dies ist in der Regel erst bei den teureren "Internet-Paketen" (ab ca. 5 € / Monat, oft noch wesentlich teurer) der Fall.
28
PHPMyAdmin
Einsatz von MySQL unter Windows
3.1 Einsatz des Apache-Servers
Um auf einem PC die Datenbank MySQL laufen zu lassen, ist es erforderlich, den Computer
als Webserver zu konfigurieren. Dies geschieht z. B. über Apache (Apache Server).
Mit einer Verbreitung von rund 50% ist der Apache-Webserver der weltweit meistverbreitete
Webserver, da dieser extrem leistungsfähig und stabil ist.
Vorteile des Apache:
⇒ gratis
⇒ Open Source
⇒ extrem leistungsfähig und stabil
⇒ wird laufend weiterentwickelt
⇒ für verschiedene Betriebssysteme
⇒ breite Konfigurationsmöglichkeiten
3
⇒ modularer Aufbau
Der Webserver läuft, wie der Name schon sagt, in der Regel als Server bei einem InternetProvider. Um ihn lokal zu nutzen, richten wir ihn als "localhost" ein. Damit ist jeder PC in der
Lage, sowohl als Server für Websites als auch als Client zu fungieren.
3.2 WAMP-Installation
Die Abkürzung WAMP steht für Windows-Apache-MySQL-PHP. Das Gegenstück dazu ist
LAMP (L für Linux). Auch wenn Webserver häufig auf Linux laufen, können wir mit einer
WAMP-Installation dennoch dasselbe Ziel erreichen.
Es ist möglich, die einzelnen Programme separat zu installieren und aufeinander abzustimmen. Das ist jedoch nicht ganz einfach, es verlangt große Sorgfalt in der Abstimmung der
Einzelkomponenten. Die andere Möglichkeit besteht darin, auf eine sogenannte Distribution
zurückzugreifen, das ist ein Komplettpaket mit Installationsroutine. Die beste Empfehlung
dazu ist zur Zeit Xampp (Download über www.apachefriends.org). Dann stehen alle erforderlichen Teile nach der Installation zur Verfügung.
Aufgabe: Kopieren Sie das Programm
vom Netzordner auf
Ihre Festplatte. Starten Sie es dann mit einem Doppelklick.
Bestätigen Sie das erste Dialogfeld mit OK
Klicken Sie auf Weiter.
3
www.webmeister.ch
29
PHPMyAdmin
Einsatz von MySQL unter Windows
Das Lizenzabkommen mit
Annehmen bestätigen.
Im nächsten Dialogfeld wird
c:\xampp
eingestellt.
Klicken Sie dann auf Installieren.
Der Installationsvorgang läuft eine
Weile.
Zum Schluss meldet XAMPP den
Abschluss des Installationsvorgangs.
Klicken Sie auf Fertigstellen.
30
PHPMyAdmin
Einsatz von MySQL unter Windows
Im Ordner C:\xampp finden Sie diese beiden Dateien.
Kopieren Sie auf das Desktop.
Starten Sie aus dem xampp-Ordner zunächst diese Datei:
Wenn diese Meldung erscheint,
drücken Sie eine
Taste.
Starten Sie die Datei xampp_start.exe. Diese Datei muss in Zukunft immer zu Beginn der Arbeit gestartet werden. Das schwarze Fenster nicht schließen, sondern nur minimiert in der
Task-Leiste ablegen.
Es ist zwar auch möglich, Apache und MySQL als sogenannten Dienst in Windows einzurichten, so dass es immer zur Verfügung steht, aber diese Variante benötigt viel Arbeitsspeicher,
auch dann, wenn das Programm gar nicht gebraucht wird.
Hier meldet sich jetzt
Xampp im Internet
Explorer.
Klicken Sie auf
Deutsch.
Xampp meldet sich
jetzt mit dem WillkommensBildschirm.
Klicken Sie zunächst
auf Status, um zu sehen, ob alles läuft.
31
PHPMyAdmin
Einsatz von MySQL unter Windows
Diese Meldung zeigt,
welche Module aktiviert sind.
Aufgabe: Rufen Sie jetzt unter Tools PHPMyAdmin auf.
PHPMyAdmin wird gestartet. Zunächst kümmern wir uns jetzt um die Fehlermeldung, die
unten am Bildschirm erscheint.
3.3 Passwort für den localhost einstellen
Dies ist die Fehlermeldung, die wir jetzt beheben werden:
Ihre Konfigurationsdatei enthält Einstellungen (Benutzer "root" ohne Passwort), welche denen des MySQL-Standardbenutzers entsprechen. Wird Ihr MySQL-Server mit diesen Einstellungen betrieben, so können Unbefugte leicht von außen auf ihn zugreifen. Sie sollten diese
Sicherheitslücke unbedingt schließen!
Aufgabe: Klicken Sie auf Rechte. Sie sehen im PHPMyAdmin:
32
PHPMyAdmin
Einsatz von MySQL unter Windows
Auf diese Zeile kommt es jetzt an. Der root-User hat unter Kennwort den Eintrag Nein.
Aufgabe: Klicken Sie auf das Symbol zum Bearbeiten
.
Scrollen Sie ein Stück tiefer zu
Kennwort ändern. Geben Sie zweimal taw ein und klicken dann auf
OK.
PHPMyAdmin meldet die Änderung
des Passworts. Auch der SQLBefehl wird direkt mit angezeigt.
Aufgabe: Klicken Sie jetzt auf das Home-Symbol links im PHPMyAdmin
Das Ganze wird mit einer Fehlermeldung quittiert:
Aufgabe: Um dies zu beheben, muss zunächst Xampp gestoppt werden
.
Starten Sie dann den HTML-Editor: IM Ordner C:\Xampp\PHPMyadmin finden Sie die Datei
config.inc.php
Scrollen Sie zur Zeile 85 und ergänzen Sie dort taw zwischen die einfachen Hochkommata.
Aufgabe: Speichern Sie dann die Datei und schließen Sie den HTML-Editor.
Aufgabe: Starten Sie jetzt den Xampp neu. Rufen Sie dann wieder PHPMyAdmin auf. Jetzt
erfolgt keine Fehlermeldung mehr
33