Video-Tutorial

Wenn Sie das Video nur anschauen, werden Sie kaum etwas lernen. Arbeiten Sie für den besten Lerneffekt am Rechner direkt mit und vollziehen Sie die Beispiele nach.

Folien



(letzte Synchronisation der PDF-Präsentation: 02.03.2017)

Falls keine PDF-Präsentation zu sehen ist, klicken Sie zum Download hier: Direktdownload PDF-Präsentation

Diese Folge setzt die Kenntnis folgender Inhalte voraus: Einführung in SELECT-Abfragen in MySQL.

Übungen mit ausführlichen Lösungen und Erklärungen finden Sie hier: Übungen zu JOINs in MySQL

MySQL-Dump "kunden-einfach.sql"

Damit Sie die Beispiele in den Folien und im Video-Tutorial nachvollziehen können, laden Sie sich diesen MySQL-Dump herunter: kunden-einfach.sql, öffnen Sie ihn in der Workbench (File -> Open SQL Script …) und lassen ihn laufen. Es wird eine Datenbank "Kunden" angelegt, die zwei Tabellen ("orte" und "kunden") enthält.

Daten aus zwei Tabellen abfragen

Beispielszenario: Im Bild sind zwei verknüpfte Tabellen zu sehen, die mit einem Fremdschlüssel verbunden sind. Überlegen Sie sich, welches die Primärschlüssel sind und welches der Fremdschlüssel.

Zwei Tabellen

Lösung:
kunden(kundeID, kundeName, kundeVorname, kundeTelefon, ↑kundeLand, kundeNummer)
laender(landID, landName, landLieferstatus)

Primärschlüssel sind also kundeID und landID (erkennbar an der Unterstreichung), Fremdschlüssel ist kundeLand in der Tabelle kunden (erkennbar an dem Pfeil vor kundeLand). Der Fremdschlüssel kundeLand verweist auf den Primärschlüssel der Tabelle laender.

Tab. “kunden” enthält also ein Feld “kundeLand”. Hier wird die ID eines Landes eingetragen, die sich auf das Feld landID in der Tabelle “laender” bezieht.

Wenn man bei einer SELECT-Abfrage kundenNamen und landName im Volltext anzeigen will, muss man Daten aus beiden Tabellen holen.

Code zur Generierung dieser beiden Tabellen:

MySQL-Code
  CREATE TABLE kunden (kundeID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, kundeName VARCHAR(50), kundeVorname VARCHAR(50), kundeTelefon VARCHAR(50), kundeLand INT, kundeNummer INT); CREATE TABLE laender (landID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, landName VARCHAR(50), landLieferstatus INT);  

…mit einigen Dummy-Daten:

MySQL-Code
  INSERT INTO kunden VALUES ('', 'Schmitt', 'Fritz', '030-300202', '1', '13'); INSERT INTO kunden VALUES ('', 'Müller', 'Mario', '030-220202', '1', '15'); INSERT INTO kunden VALUES ('', 'Hauser', 'Hannes', '0233-22233', '3', '11'); INSERT INTO kunden VALUES ('', 'Vogel', 'Vasili', '034-3444', '2', '16'); INSERT INTO laender VALUES ('', 'Deutschland', ''); INSERT INTO laender VALUES ('', 'Schweiz', ''); INSERT INTO laender VALUES ('', 'Frankreich', '');  

Möglichkeit 1: JOIN/LEFT JOIN

Syntax:

MySQL-Code
  SELECT kundeID, kundeName, kundeLand, landID, landName FROM kunden JOIN laender WHERE kundeLand = landID  

Wählt aus den beiden Tabellen kunden und laender (kunden JOIN laender) die angegebenen felder (kundeID, kundeName, kundeLand, landID, landName) aus und beschränkt sich auf die Fälle, wo kundeLand mit der landID übereinstimmt (WHERE kundeLand = landID).

Lässt man hier das WHERE-Statement weg, werden alle Datensätze von “kunden” mit allen Datensätzen von “laender” kombiniert, so dass bei 3 Ländern und 4 Kunden insgesamt 12 Zeilen ausgegeben werden (von denen jeweils vier dreifach ausgegeben werden – damit sind 8 redundant).

Dieses sinnlose Ergebnis nennt man das Kartesische Produkt. Überprüfen Sie Ihre Abfrageergebnisse immer daraufhin, ob Sie Ihren Abfrageergebnissen durch korrekte Einschränkungen Sinn gegeben haben.

Wenn in den Tabellen Felder mit identischen Namen sind, muss angegeben werden, aus welcher Tabelle der Wert geholt wird, Syntax: tabellenname.feldname

MySQL-Code
  SELECT kundeName, kunden.land, laender.land FROM kunden JOIN laender WHERE kunden.land = laender.land  

LEFT JOIN

Ist in der Tabelle “kunden” ein Wert für kundeLand angegeben, der nicht in der Tabelle “laender” gefunden werden kann, dann wird dieser Datensatz nicht ausgegeben. Ein solcher fehlerhafter Datensatz wäre ergänzend zum SQL-Listing oben z.B.

MySQL-Code
  INSERT INTO kunden VALUES ('', 'Supermann', 'Sabine', '034-233444', '9', '11');  

Die landID 9 existiert nicht, also wird der Datensatz bei der obigen Abfrage nicht ausgegeben.

Mit LEFT JOIN werden alle Datensätze der linken (“left”) Tabelle ausgegeben:

MySQL-Code
  SELECT kundeID, kundeName, kundeLand, landID, landName FROM kunden LEFT JOIN laender ON kundeLand = landID  

Achtung: “WHERE” wird hier ersetzt durch “ON”.

Möglichkeit 2: Implizite Schreibweise

Statt explizit JOIN zu schreiben, können wir die abzufragenden Tabellen auch einfach mit Kommas trennen:

MySQL-Code
  SELECT * FROM ort, kunde  

Übrigens entspricht INNER JOIN exakt JOIN. Man spricht von syntaktischem Zucker (syntactic sugar).

Übungen

Alle Übungen finden Sie in der Materialsammlung (dort auch alle zusätzlichen Dateien wie Bilder, Klassendiagramme oder HTML-Vorlagen!).

Die aktuelle Übung können Sie hier als txt-File herunterladen.


***** Übungen: MySQL - SELECT: Mehrere Tabellen mit JOINs abfragen *****


MySQL06_1: Kartesisches Produkt

Erklären Sie in eigenen Worten, warum diese Abfrage kein sinnvolles Ergebnis gibt:

MySQL-Code
SELECT * FROM kunden, orte  





MySQL06_2: Einfache Abfragen über zwei Tabellen - DB Kunden-einfach

Benutzen Sie für diese Übung diese Datenbank: 07mysql/_dumps/kunden/kunden-einfach.sql

Hier das zugehörige ER-Diagramm:
07mysql/_dumps/kunden/kunde_einfach_ERD.png

A) Geben Sie Name, Postleitzahl und Wohnort aller Kunden aus. Die Liste enthält den Kundennamen und den Namen des Orts, wo er wohnt.

B) Geben Sie Name und Wohnort aller Kunden aus, die die Postleitzahl 79312 haben.

C) Geben Sie Name und Wohnort aller Kunden aus, die in Emmendingen wohnen (Einschränkungskriterium ist NICHT die Postleitzahl, sondern 'Emmendingen').

D) Geben Sie Name, Wohnort und Einwohnerzahl für alle Kunden aus, die in einem Ort mit mehr als 70000 Einwohnern wohnen.

E) Geben Sie alle Orte aus, die weniger als 1000000 Einwohner haben.

F) Geben Sie Kundename und Ortname aus für alle Kunden, die in Orten mit einer Einwohnerzahl zwischen 100.000 und 1.500.000 leben.

G) Geben Sie Kundename, Postleitzahl und Ortname aus für alle Kunden, deren Name ein "e" enthält und alle Orte, die ein "u" oder ein "r" enthalten (frEd aus stUden wird also genau so angezeigt wie jEssE aus bRnz, frEd aus salzen aber nicht und martin aus hambURg auch nicht).




MySQL06_2a: Übungen zu Abfragen über zwei Tabellen: Buchladen

Verwenden Sie für diese Übung diese Datenbank:
07mysql/_dumps/buchladen/buchladen.sql

Das ER-Diagramm:
07mysql/_dumps/buchladen/buchladen_ERD.png

A) Geben Sie alle Lieferanten aus, die in Reute wohnen. Ausgabe: Lieferantename, Ortname, Postleitzahl

B) Geben Sie eine Liste aller Verlage aus, deren Name mit L beginnt. Ausgabe: Verlagname, Ortname; bitte nach Ortsname absteigend sortieren.

C) Geben Sie eine Liste aller Lieferanten aus, deren Name mit Schus... oder Loe.. beginnt und die in Emmendingen sitzen. Ausgabe: Lieferantename, Ortname, sortiert nach Lieferantenname aufsteigend.

D) Geben Sie eine Liste aller Orte aus, aber nur die Orte, in denen ein Verlag sitzt, der im Namen ein n hat.




MySQL06_3: JOIN vs. LEFT JOIN

Benutzen Sie für diese Übung diese Datenbank: 07mysql/_dumps/kunden/kunden-einfach.sql

Hier das zugehörige ER-Diagramm:
07mysql/_dumps/kunden/kunde_einfach_ERD.png

Betrachten Sie die beiden folgenden Statements und erklären Sie, worin die Ergebnistabellen sich unterscheiden werden. Probieren Sie danach die Befehle aus und überprüfen Sie Ihre Antwort.

MySQL-Code
-- Statement 1: LEFT JOIN SELECT k.kunde_id, k.name, o.name FROM kunden AS k LEFT JOIN orte AS o ON o.postleitzahl = k.ort_postleitzahl ORDER BY k.kunde_id   -- Statement 2: EQUI-JOIN SELECT k.kunde_id, k.name, o.name FROM kunden AS k, orte AS o WHERE o.postleitzahl = k.ort_postleitzahl ORDER BY k.kunde_id  




MySQL06_4: Übungen zu Abfragen über bis zu 5 Tabellen: Buchladen

Verwenden Sie für diese Übung diese Datenbank:
07mysql/_dumps/buchladen/buchladen.sql

Das ER-Diagramm:
07mysql/_dumps/buchladen/buchladen_ERD.png


A) Gesucht sind alle Lieferanten, die ihren Sitz in Freiburg haben. Geben Sie Lieferantenname, Lieferantenort und Postleitzahl aus.
(Lösungshinweis: Es werden drei Lieferanten ausgegeben.)

B) Gesucht sind alle Verlage, die ihren Sitz in München haben. Geben Sie Verlagsname und Verlagsort aus.
(Lösungshinweis: Es werden drei Verlage ausgegeben.)

C) Gesucht sind alle Bücher, die im Verlag Assal erschienen sind. Geben Sie Buchtitel, Erscheinungsjahr und Verlagsname aus, absteigend sortiert nach Erscheinungsjahr.
(Lösungshinweis: Es werden vier Bücher ausgegeben.)

D) Gesucht sind alle Bücher des Lieferanten Schustermann. Geben Sie Buchtitel und Lieferantenname aus.
(Lösungshinweis: Es werden drei Bücher ausgegeben.)

E) Gesucht sind alle Thriller. Geben Sie Buchtitel und Sparte aus, sortiert nach Buchtitel.
(Lösungshinweis: Es werden fünf Bücher ausgegeben, das erste ist "Ein letzter Kuss".)

F) Gesucht sind alle Liebesromane. Geben Sie Buchtitel, Sparte und Verlagsname aus, sortiert nach Buchtitel aufsteigend.
(Lösungshinweis: Es werden sieben Datensätze ausgegeben, "Der Landarzt und seine Sekretärin" zuerst.)

G) Gesucht sind alle Bücher von Sabrina Müller. Geben Sie Autorennachname, Autorenvorname und Buchtitel aus, sortiert nach Buchtitel absteigend.
(Lösungshinweis: Es werden vier Datensätze ausgegeben, "Wilde Jagd auf John Smith - Die Vergeltung" zuerst.)

H) Gesucht sind alle Thriller von Sabrina Müller. Geben Sie Autorenname, Buchtitel und Sparte aus.
(Lösungshinweis: Es werden zwei Datensätze ausgegeben.)

(Achtung: Das können Sie eigentlich noch nicht!)
Gesucht sind alle Bücher von Sabrina Müller, die in die Sparten Thriller oder Humor eingeordnet werden können. Geben Sie Autorenname, Buchtitel und Sparte aus.

Damit das Buch "Wilde Jagd auf John Smith - Die Rache" nicht zweimal auftaucht, müssen Sie das Ergebnis nach Buchtitel gruppieren, indem Sie vor ORDER BY schreiben:

MySQL-Code
GROUP BY titel  

Damit nun auch noch für dieses Buch beide Sparten in der Spalte "bezeichnung" angezeigt werden, benutzen Sie für die Ausgabe der Sparte nicht einfach den Feldnamen "bezeichnung", sondern
MySQL-Code
GROUP_CONCAT(bezeichnung)  
Damit werden für Bücher, die in mehreren Sparten eingeordnet sind, alle Sparten mit Komma getrennt in einem Feld ausgegeben.
(Lösungshinweis: Es werden drei Datensätze ausgegeben.)