Die Gehälter je Team, in einer Frage
Die Leiterin der Buchhaltung fragt: Wie viele Personen arbeiten in jedem Team, und was zahlen wir jedem Team insgesamt?
Die Personen stehen in einer Tabelle. Die Namen der Teams stehen in einer anderen. Die Antwort braucht beide.
Mit Verbinden und Gruppieren beantwortet eine Datenbank solche Fragen.
Am Ende dieser Lektion
Notiz
Ich kann Tabellen verbinden und zusammenfassen.
Schlüsselwörter
| Begriff | Was er bedeutet | Beispiel |
|---|---|---|
| Join | stellt Zeilen zweier Tabellen nebeneinander, wo ein Schlüssel passt | |
| Alias | ein kurzer Name, den Sie einer Spalte oder Tabelle in einer Abfrage geben | AS gehaltssumme |
| Aggregatfunktion | macht aus vielen Zeilen einen einzigen Wert | SUM(gehalt) |
| GROUP BY | bildet eine Ergebniszeile für jeden verschiedenen Wert | |
| HAVING | behält nur die Gruppen, die einen Test bestehen | |
| Left Join | ein Join, der jede Zeile der ersten Tabelle behält |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Ein Join stellt zwei Tabellen nebeneinander
Daten werden auf Tabellen verteilt, damit nichts doppelt steht. Ein Join holt sie wieder zusammen.
Die Klausel ON sagt, welche Spalten zusammenpassen. Meist ist es ein Fremdschlüssel auf der einen und ein Primärschlüssel auf der anderen Seite.
Haben zwei Tabellen eine Spalte gleichen Namens, schreiben Sie die Tabelle davor: suppliers.city.
Starten Sie die Einrichtungszeilen. Sie sind dieselben wie vorher.
Das Zertifikat des Servers annehmen
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfHier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Eine Abkürzung für den Shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Artikel und ihre Herkunft
sql "SELECT products.name, suppliers.city FROM products JOIN suppliers ON suppliers.id = products.supplier_id WHERE suppliers.country = 'Japan' ORDER BY products.name"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein Beispiel: über den Schlüssel zuordnen
| personal.name | personal.abt_id | abteilungen.id | abteilungen.name |
|---|---|---|---|
| Anna | 1 | 1 | Vertrieb |
| Clara | 2 | 2 | Reparatur |
| Finn | 3 | 3 | Recht |
| Eine Person mit abt_id 9 | 9 | keine Zeile | Ein einfacher JOIN lässt sie weg |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
GROUP BY gibt eine Zeile je Wert
Eine Aggregatfunktion macht aus vielen Zeilen einen Wert. COUNT(*) zählt Zeilen. SUM, AVG, MIN und MAX rechnen über eine Spalte.
Allein fasst eine Aggregatfunktion die ganze Tabelle zusammen. Mit GROUP BY erhalten Sie eine Zusammenfassung für jeden verschiedenen Wert einer Spalte.
Geben Sie dem Ergebnis mit AS einen Namen.
Personal und Gehälter je Stellenbezeichnung
sql "SELECT job_title, COUNT(*) AS staff, SUM(salary) AS payroll FROM employees GROUP BY job_title ORDER BY job_title"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Erst verbinden, dann gruppieren
sql "SELECT suppliers.country, COUNT(*) AS items, SUM(products.stock) AS units FROM products JOIN suppliers ON suppliers.id = products.supplier_id GROUP BY suppliers.country ORDER BY suppliers.country"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Probieren Sie es: nach Abteilung zusammenfassen
-- Wie viele Personen und welche Gehaltssumme hat jede Abteilung?
SELECT abt_id, COUNT(*) AS personen, SUM(gehalt) AS gehaltssumme
FROM personal
GROUP BY abt_id
ORDER BY abt_id;
-- Fügen Sie vor ORDER BY die Zeile HAVING COUNT(*) > 1 ein.
HAVING prüft eine Gruppe
WHERE prüft einzelne Zeilen, bevor sie gruppiert werden. Eine Summe gibt es zu diesem Zeitpunkt noch nicht.
HAVING prüft die Gruppen, nachdem sie gebildet sind. Nutzen Sie es mit einer Aggregatfunktion: HAVING COUNT(*) >= 25.
Die Reihenfolge ist: WHERE, dann GROUP BY, dann HAVING, dann ORDER BY.
Wer nahm 25 oder mehr gültige Bestellungen an?
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE status <> 'cancelled' GROUP BY employee_id HAVING COUNT(*) >= 25 ORDER BY orders DESC"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein häufiger Fehler
Warnung
Viele glauben, WHERE COUNT(*) >= 25 filtere die Gruppen. Das ist ein Fehler, denn WHERE läuft, bevor es eine Gruppe gibt. Nehmen Sie WHERE für Zeilen und HAVING für Gruppen.
Der Fehler, echt gestartet
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE COUNT(*) >= 25 GROUP BY employee_id"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein Left Join behält die Zeilen ohne Treffer
Ein einfacher Join lässt eine Zeile ohne Treffer weg. Manchmal ist gerade der fehlende Treffer das, was Sie suchen.
LEFT JOIN behält jede Zeile der ersten Tabelle. Wo nichts passt, steht in den anderen Spalten NULL.
Mit WHERE ... IS NULL listen Sie nur die Zeilen ohne Treffer.
Artikel ohne Kategorie
sql "SELECT products.name, categories.name AS category FROM products LEFT JOIN categories ON categories.id = products.category_id WHERE categories.id IS NULL ORDER BY products.name"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Probieren Sie es: Personen neben ihrem Team
-- Jede Person neben dem Namen ihrer Abteilung.
SELECT personal.name, abteilungen.name AS abteilung
FROM personal
JOIN abteilungen ON abteilungen.id = personal.abt_id
ORDER BY personal.name;
-- Fügen Sie vor ORDER BY die Zeile WHERE abteilungen.name = 'Reparatur' ein.
Weiterführend: Angestellte und ihre Vorgesetzten
In employees zeigt die Spalte manager_id auf eine andere Zeile derselben Tabelle. Verbinden Sie die Tabelle mit sich selbst.
Geben Sie ihr dazu zwei Aliasse, etwa e für die Person und m für die vorgesetzte Person. Zeigen Sie jede Person neben dem Vornamen ihrer vorgesetzten Person.
Wer hat niemanden über sich? Sorgen Sie dafür, dass diese Person trotzdem erscheint.