Archiv für den Autor: Stephan

Verknüpfungen – Cross Joins, Inner Joins und Outer Joins

Bisher haben wir in unserer Datenbank immer nur auf einer Tabelle alleine gearbeitet. Das hätte man genau so gut auch noch in einem Tabellenkalkulationsprogramm wie Excel erledigen können. Das besondere an Datenbanken ist, dass die Tabellen untereinander kombinierbar sind (sein sollen) und somit Spalten aus beliebigen Tabellen zusammengeführt werden können. Dazu verknüpft man die gewünschten Tabellen untereinander. Diese Verknüpfung nennt man “join”.

Die einfachste Form einer Verknüpfung ist ein sogenannter CROSS JOIN. Hier wird ein kartesisches Produkt der Tabellen gebildet die miteinander verknüpft wurden.
Im Beispiel sieht das wie folgt aus:

SELECT k.kd_nr AS “Kundennummer”, k.name AS “Nachname”, k.vorname AS “Vorname”, o.plz AS “Postzleitzahl mal Kunden”
FROM kunden AS k CROSS JOIN ort as o;

CROSSJOIN

Was haben wir gemacht? Wir haben insgesamt 8 Kunden und 6 Orte. Bei einem CROSS JOIN wird jedem Wert der Tabelle Kunde jeder Wert der Tabelle Ort zugewiesen. Wir haben also je Kunde 6mal einen Ort zugewiesen. 8×6 ergibt 48.

Wir wollten aber sicherlich nicht wissen wo unsere Kunden überall wohnen könnten, sondern wir wollen wissen wo sie wohnen. Hier kommt der wohl gebräuchlichste JOIN in Frage. Der sogenannte INNER JOIN. Ein Beispiel:

SELECT k.kd_nr AS “Kundennummer”, k.name AS “Nachname”, k.vorname AS “Vorname”, o.ort AS “Wohnort”
FROM kunden AS k INNER JOIN ort AS o
ON k.plz = o.plz
ORDER BY k.kd_nr;

INNERJOIN

Sieht doch schon viel besser aus. Jetzt ist aber in der Abfrage einiges enthalten was einer Erklärung bedarf. Nehmen wir die Abfrage einmal Zeile für Zeile auseinander.

SELECT k.kd_nr AS “Kundennummer”, k.name AS “Nachname”, k.vorname AS “Vorname”, o.ort AS “Wohnort”

Die SELECT-Anweisung kennen wir. Dass vor “kd_nr” ein “k.” steht ist neu. Ebenso stehen solche Bezeichner auch vor allen anderen Attributen. Warum?
Ganz einfach. Wir arbeiten hier ja mit zwei Tabellen, der Tabelle “kunden” und der Tabelle “ort”. Damit der SQL-Server weiß, welches Attribut aus welcher Tabelle er auswählen soll müssen wir ihm das mitteilen. Wir könnten genauso gut schreiben: SELECT kunden.kd_nr AS “Kundennummer”, kunden.name AS “Nachname”…. In der Praxis wird aber mit kurzen Bezeichnern gearbeitet um den Aufwand gering zu halten. Woher aber weiß die SELECT Anweisung was mit “k.” und “o.” gemeint ist? Wie bereits im Abschnitt Datenbanabfragen angesprochen, wird die SELECT-Anweisung vom Server erst nach der FROM-Klausel ausgeführt. Das heisst:

FROM kunden AS k INNER JOIN ort AS o

In dieser Zeile werden die Bezeichner zugewiesen, die wir in der Zeile darüber bereits verwendet haben. Dieser Umstand führt am Anfang gerne zu Verwirrungen, aber wenn man sich die Reihenfolge der Abarbeitung vor Augen führt, kommt man sehr gut damit klar.

ON k.plz = o.plz

Hier entsteht die eigentliche Verknüpfung. Das “ON” schlägt die Brücke von einer Tabelle zur nächsten und benutzt dazu den Fremdschlüssel der einen Tabelle (kunden) und den Primärschlüssel der anderen Tabelle (ort). Wir haben die Tabellen also anhand ihrer Schlüsselattribute verknüpft.

Das “ORDER BY k.kd_nr;” hat hier rein kosmetische Gründe um die Ausgabe des Servers geordnet und überprüfbar darzustellen.

So wie wir die beiden Tabellen “kunden” und “ort” miteinander verbunden haben, können auch drei oder mehr Tabellen untereinander verknüpft werden.

Wir wollen wissen, welcher Lieferant welches Produkt herstellt und woher der Lieferant kommt:

SELECT  a.art_nr AS “Artikel Nummer”, a.art_bez AS “Produkt”, l.firmenname AS “Hersteller”, o.ort AS “Herkunftsort”
FROM artikel AS a INNER JOIN lieferanten AS l
ON a.lieferant_nr = l.lieferanten_nr
INNER JOIN ort AS o
ON l.plz = o.plz;

 JOINmitDreiTabellen

Was haben wir gemacht? Wir haben die Tabelle Artikel über das Schlüsselattribut(fremd) “lieferant_nr” mit der Tabelle “lieferanten” und dem dortigen Schlüsselattribut(primär) “lieferanten_nr” verknüpft und wollten aus beiden Tabellen die Attribute “art_nr”, “art_bez” und “firmenname” ausgeben. Darüberhinaus haben wir die verknüpften Tabellen (“artikel” und “lieferanten” ) über das Fremdschlüsselattribut “plz” der Tabelle “lieferanten” mit der Tabelle “ort” über deren Primärschlüssel “plz” miteinander verknüpft und uns den Ort als Herkunftsort ausgeben lassen.

Das Thema “Joins” ist zugegebenermaßen ein sehr komplexes Gebiet und man sollte sich nicht entmutigen lassen, wenn es nicht auf Anhieb eingängig erscheint. Im nächsten Teil widmen wir uns den OUTER JOINS, aber zunächst lassen wir CROSS- und INNER JOIN einmal sacken.

Bedingungen – Filtern von Daten und Datensätzen

Einfache Abfragen mit einer “Where-Klausel” stellen kein Problem mehr da. Die Where-Klausel kann aber noch viel mehr. In ihr können komplexe Abfragemechanismen definiert werden die mehrere Attribute und deren Werte umfassen.

Es gibt darüberhinaus aber mehr als eine reine gleichheits Abfrage.

Die wohl meist verwendeten Abfragen sind jedoch “=”, “<” oder “>”, mit denen entweder genau ein Wert, oder alle Werte unterhalb, bzw. oberhalb des Filters ausgewählt werden.

Zu den rein mathematischen Vergleichen, kommen noch Möglichkeiten hinzu, zum Beispiel nach Zeichenketten, oder nach Teilen von Zeichenketten zu suchen, nach Feldern die keinen Wert enthalten aber mit sogenannten Wildcards suchen.
Weiterlesen

Der Rahmen steht, Zeit ihn zu befüllen

Datenbanken sind permanenten Änderungen unterworfen, Datensätze kommen hinzu, werden geändert oder gelöscht. Man spricht hier von Manipulation der Daten. Manipulation bedeutet nichts anderes als “Änderung” ist aber der Fachterminus und sollte zumindest einmal angesprochen werden. In vielen Fachbüchern wird von Manipulation gesprochen und das kann zu Verwirrungen führen, weiß man nicht um den Zusammenhang. 

Die wichtigsten Anweisungen zur Manipulation von Tabellen sind: 

 

  • INSERT
  • UPDATE und 
  • DELETE

Tragen wir die Daten unseres Datenbankentwurfs in die Datenbank ein. Hier noch einmal der Link zu den Tabellenentwürfen.

INSERT INTO kunden
(kd_nr, name, vorname, strasse, hausnr, plz, mail, telefon)
VALUES (1, ‘Hintermaierberger’, ‘Josef’, ‘Landstr.’, 5,34562,  ‘johimabe@mustermail.de’, null);

Diese Eingabe führt zu folgendem Fehler:

fehlenderFK

Warum? Ganz einfach! Die Postleitzahl “plz” ist ein Fremdschlüssel in der Tabelle “kunden”. Da wir unsere Fremdschlüssel so angelegt haben, dass diese nur gültig sind, wenn Sie in der Tabelle in der sie Primärschlüssel sind, auch existieren, streikt der SQL-Server, da plz ‘34562’ in der Tabelle “ort” nicht existiert.

Wir können unseren Kunden nun entweder ohne “plz” anlegen und später durch die UPDATE-Anweisung ergänzen, oder wir befüllen zuerst die Tabelle “ort”.


PRAXISTIPP: Schreibt eure SQL-Anweisungen in einem Programm wie Notepad oder einem anderen Texteditor, da ihr in der SQL-Konsole nur Zeilenweise korrigieren könnt. Kopiert eure Anweisungen einfach in die Konsole und führt sie dann aus, so ist euer Code einfacher zu korrigieren. 

Jetzt kommt die Fleißaufgabe: Tragt alle Daten aus den Tabellenentwürfen in die Tabellen des mySQL-Servers ein. Wie immer findet ihr die Codezeilen auch auf der Codseite. Diese Übung sollte man sich aber nicht entgehen lassen.

Die Tabelle ist nun befüllt. Wie wir sehen, haben wir bei einigen Kunden eine Telefonnummer, bei anderen leider nicht.
Der Kunde Stefan Meier hat uns eine Email geschrieben in der Fragen zu unserem Produktkatalog hat und bittet um Rückruf. Seine Nummer hat er uns in der Mail mitgeschickt, 04567/334562. Da wir natürlich nicht immer die Mail aufrufen wollen, übertragen wir die Nummer in die Datenbank. Wir bringen sie sozusagen “auf den neuesten Stand” (wir machen ein Update).

UPDATE kunden
SET telefon =’04567/33462′
WHERE kd_nr =2;

Und schon können wir jederzeit auf die Nummer unseres Kunden zugreifen.
Mit der UPDATE Anweisung können wir alle Daten abändern.

Würden wir einen Kunden aus der Datenbank löschen wollen, würden wir folgendermaßen vorgehen:

DELETE
FROM kunden
WHERE kd_nr = 9;

Und schon ist Susi Bork nicht mehr Teil unserer Kundendatenbank.

 

 

Die Beiträge im Überblick

Um euch die Navigation zu erleichtern und auch um den Lernfortschritt festzuhalten bietet diese Seite den Überblick über alle bisherigen Beiträge und ihren Inhalt.

1.Was ist eine Datenbank – Grundlagen

2. Schlüssel einer Datenbank – kurzer Exkurs

3. Die Datentypen in mySQL

4. Wie entwirft man eine Datenbank?

5. Die Installation von mySQL

6. Die Datenbank aus dem Entwurf in mySQL übertragen

7. Die Datenbank verfeinern und “Schwachstellen” ausbessern – Fremdschlüssel und Auto_INCREMENT

8. INSERT, UPDATE und DELETE – Die Datensätze verändern

9. Einfache Abfragen und ihre Funktionsweise

10. Filtern und bedingte Abfragen

11. CROSS-JOIN, INNER-JOIN und OUTER-JOIN

 

Copyrights

Das Copyright

Alle Grafiken und Beispiele auf dieser Seite habe ich selbst erstellt, erhebe aber keinerlei Ansprüche darauf. Die Inhalte stehen zur freien Verwendung zur Verfügung und können zum Selbststudium beliebig verwendet werden. Solltet ihr euch aber erkenntlich zeigen wollen, so könnt ihr einen kleinen Beitrag zu den Betriebskosten über den Spendenbutton in der rechten Spalte gerne machen.

Viel Spaß beim Lesen und hoffentlich viel Erfolg beim Lernen.