LU08-11a: Abfragen zusammensetzen
Sie kennen inzwischen viele Bausteine: Fremdschlüssel, Zwischentabellen, JOIN, Aggregatfunktionen, GROUP BY und HAVING. Einzeln klappen sie meistens. Schwierig wird es, wenn mehrere in einer Abfrage zusammenkommen. Diese Seite zeigt, wie die Teile zusammenspielen und wie Sie eine Abfrage Schritt für Schritt aufbauen.
Lernziele
- Sie können erklären, wozu die Themen aus LU08 bis LU11 zusammen gebraucht werden.
- Sie können für jede Klausel sagen, was sie mit der Tabelle macht.
- Sie können eine Frage in Alltagssprache mit dem Bauplan in eine SQL-Abfrage übersetzen.
- Sie können ON und WHERE sowie WHERE und HAVING auseinanderhalten.
1. Der rote Faden von LU08 bis LU11
Nehmen Sie diese Frage zum Reiseblog: Welche Kategorien kommen in Posts aus dem Jahr 2025 mindestens zweimal vor? Um sie zu beantworten, brauchen Sie alle vier Lerneinheiten.
| Baustein | Aus | Wozu er hier nötig ist |
|---|---|---|
| Daten auf mehrere Tabellen verteilt, Fremdschlüssel | LU08 | Jede Kategorie steht nur einmal in categories. Der Fremdschlüssel sorgt dafür, dass nur gültige IDs verwendet werden. |
| Zwischentabelle post_category | LU09 | Ein Post hat mehrere Kategorien, eine Kategorie gehört zu mehreren Posts. |
| INNER JOIN … ON | LU10 | Holt die verteilten Daten für die Abfrage wieder zusammen. |
| COUNT, GROUP BY, HAVING | LU11 | Zählt pro Kategorie und behält nur Kategorien mit mindestens zwei Posts. |
Merksatz: LU08 und LU09 zerlegen die Daten sauber in Tabellen. LU10 setzt sie für eine Abfrage wieder zusammen. LU11 fasst sie zu Zahlen zusammen.
2. Das Fliessband: Was jede Klausel mit der Tabelle macht
Sie schreiben eine Abfrage mit SELECT am Anfang. Die Datenbank arbeitet sie aber in einer anderen Reihenfolge ab. Stellen Sie sich ein Fliessband vor: Jede Station bekommt eine Tabelle, verändert sie und gibt sie an die nächste weiter.
| Station | Klausel | Was mit der Tabelle passiert | Beispiel Reiseblog |
|---|---|---|---|
| 1 | FROM, JOIN … ON | Die Tabellen werden zu einer grossen Tabelle zusammengesetzt. ON bestimmt, welche Zeilen zusammengehören. | 7 Posts werden zu 16 Zeilen |
| 2 | WHERE | Einzelne Zeilen werden aussortiert. | 16 Zeilen werden zu 14 |
| 3 | GROUP BY | Zeilen mit dem gleichen Wert werden zu Gruppen zusammengefasst. | 14 Zeilen werden zu 8 Gruppen |
| 4 | HAVING | Ganze Gruppen werden aussortiert. | 8 Gruppen werden zu 4 |
| 5 | SELECT | Spalten werden ausgewählt, Aggregatfunktionen berechnet, Aliase vergeben. | Name und Anzahl |
| 6 | ORDER BY | Das Ergebnis wird sortiert. | grösste Anzahl zuoberst |
Warum werden aus 7 Posts plötzlich 16 Zeilen? Jeder Post erscheint so oft, wie er Kategorien hat. Der Oman-Post hat drei Kategorien und steht deshalb dreimal in der Tabelle. Genau diese Zeilen zählt COUNT später.
Aus dem Fliessband ergeben sich die Regeln, die Sie aus LU11 kennen:
- WHERE kennt noch kein COUNT. WHERE arbeitet an Station 2, gezählt wird erst ab Station 3.
WHERE COUNT(*) >= 2ergibt deshalb den Fehler Invalid use of group function. - HAVING filtert Gruppen. HAVING kommt nach GROUP BY und sieht nur noch Gruppen.
- Nach GROUP BY gibt es pro Gruppe nur noch eine Zeile. Im SELECT dürfen darum nur Spalten stehen, die im GROUP BY vorkommen, oder Aggregatfunktionen.
- Ein Alias aus SELECT ist in WHERE noch unbekannt. SELECT kommt erst an Station 5. In ORDER BY dürfen Sie den Alias verwenden.
3. Der Bauplan: Von der Frage zur Abfrage
Viele beginnen mit SELECT und bleiben dort stecken. SELECT steht zwar zuoberst, setzt aber voraus, dass alles andere schon klar ist. Beantworten Sie deshalb zuerst diese sechs Fragen, am besten auf Papier.
| Frage | Ergibt | Darauf achten |
|---|---|---|
| 1. Was soll am Schluss angezeigt werden? | SELECT | Spalten notieren und dazuschreiben, in welcher Tabelle sie stehen |
| 2. In welchen Tabellen stehen diese Spalten? | FROM, JOIN | Im ERD den Weg suchen. Liegt eine Zwischentabelle dazwischen, gehört sie auch dazu. |
| 3. Wie hängen die Tabellen zusammen? | ON | Immer Fremdschlüssel = Primärschlüssel, auf den er zeigt |
| 4. Welche Zeilen braucht es? | WHERE | Signalwörter: nur, ab 2025, mit dem Titel … |
| 5. Pro was wird gezählt oder gerechnet? | GROUP BY | Signalwörter: pro, je, für jede, wie viele … hat jede … |
| 6. Welche Gruppen sollen bleiben? | HAVING | Bedingung über eine Anzahl oder Summe: mehr als zwei Posts, mindestens zwei |
Nicht jede Abfrage braucht alle sechs Teile. Steht bei Frage 5 nichts, fallen GROUP BY und HAVING weg.
Beispiel
Welche Kategorien kommen in Posts aus dem Jahr 2025 mindestens zweimal vor?
| Frage | Antwort |
|---|---|
| 1. Anzeigen | Name der Kategorie (categories), Anzahl Posts |
| 2. Tabellen | posts, post_category, categories |
| 3. Verbindung | posts.post_id = post_category.post_id, post_category.category_id = categories.category_id |
| 4. Zeilen | nur Posts ab dem 1. Januar 2025 |
| 5. Pro was | pro Kategorie |
| 6. Gruppen | nur Kategorien mit mindestens 2 Posts |
Schreiben Sie die Abfrage jetzt in der Reihenfolge des Fliessbands. Beginnen Sie mit SELECT * und führen Sie die Abfrage nach jedem neuen Teil aus. So sehen Sie, was jeder Schritt verändert.
SELECT categories.name, COUNT(*) AS anzahl_posts FROM posts INNER JOIN post_category ON posts.post_id = post_category.post_id INNER JOIN categories ON post_category.category_id = categories.category_id WHERE posts.created_at >= '2025-01-01' GROUP BY categories.name HAVING COUNT(*) >= 2 ORDER BY anzahl_posts DESC;
Ergebnis: Städtereise 4, Roadtrip 2, Niederlande 2, Abenteuer 2.
4. Was oft verwechselt wird
| Das eine | Das andere | So halten Sie es auseinander |
|---|---|---|
| Primärschlüssel | Fremdschlüssel | Der Primärschlüssel identifiziert die eigene Zeile. Der Fremdschlüssel zeigt auf eine Zeile in einer anderen Tabelle. |
| Elterntabelle 👩🦳👨🏼🦰 | Kindtabelle 🧒 | Der Fremdschlüssel steht immer in der Kindtabelle, auf der «viele»-Seite. |
| ON | WHERE | ON sagt, wie zwei Tabellen zusammenpassen. WHERE sagt, welche Zeilen Sie behalten. |
| WHERE | HAVING | WHERE sortiert einzelne Zeilen vor dem Gruppieren aus. HAVING sortiert ganze Gruppen nach dem Gruppieren aus. |
| RESTRICT | CASCADE | RESTRICT verhindert das Löschen einer Elternzeile, solange Kindzeilen darauf zeigen. CASCADE löscht die Kindzeilen mit. SET NULL setzt den Fremdschlüssel in den Kindzeilen auf NULL. |
| COUNT(*) | COUNT(spalte) | COUNT(*) zählt Zeilen. COUNT(spalte) zählt nur Werte, die nicht NULL sind. |
Gefährlichster Fehler: Ein falsches ON. Wenn Sie posts.post_id = users.user_id schreiben statt posts.author_id = users.user_id, meldet MySQL keinen Fehler. Das Ergebnis sieht plausibel aus und ist trotzdem falsch. Prüfen Sie bei jedem ON: Steht auf einer Seite ein Fremdschlüssel und auf der anderen der Primärschlüssel, auf den er zeigt?
