Wir analysieren Datenbanken und die SQL-Sprache. (Teil 5 – Verbindungen und Joins) – „Java-Projekt von A bis Z“

Ein Artikel aus einer Reihe über die Erstellung eines Java-Projekts (Links zu anderen Materialien finden Sie am Ende). Sein Ziel ist es, Schlüsseltechnologien zu analysieren, das Ergebnis ist das Schreiben eines Telegram-Bots.

• Arten von Beziehungen in der Datenbank
  • Eins-zu-viele
  • Eins zu eins (eins zu eins)
  • Viel zu viel
• Verbindungen (Joins)
  • INNER JOIN
  • LINKS BEITRETEN
  • RICHTIG BEITRETEN
  • Sicherung von Verbindungen
• Hausaufgaben

Hallo zusammen, zukünftige Senioren und Senioritas der Software. Wie ich im vorherigen Teil ( Hausaufgabenkontrolle ) gesagt habe, wird es heute neues Material geben. Für besonders Interessierte habe ich eine interessante Hausaufgabe ausgegraben, damit diejenigen, die bereits alles wissen, und diejenigen, die es nicht wissen, es aber googeln möchten, ihre Fähigkeiten üben und testen können. Heute werden wir über Arten von Verbindungen und Verknüpfungen sprechen.

Arten von Beziehungen in der Datenbank

Um zu verstehen, was Beziehungen sind, müssen Sie sich daran erinnern, was ein Fremdschlüssel ist. Für diejenigen, die es vergessen haben: Willkommen zum Anfang der Serie .

Eins-zu-viele

Erinnern wir uns an unser Beispiel mit Ländern und Städten. Es ist klar, dass eine Stadt ein Land haben muss. Wie verbinde ich ein Land mit einer Stadt? Es ist notwendig, jeder Stadt eine eindeutige Kennung (ID) des Landes zuzuordnen, zu dem sie gehört. Dies haben wir bereits getan. Dies wird als eine der Arten von Verbindungen bezeichnet – eins zu viele (es wäre auch gut, die englische Version zu kennen – eins zu viele). Um es anders auszudrücken: Zu einem Land können mehrere Städte gehören. So sollten Sie es sich merken: eine Eins-zu-Viele-Beziehung. Soweit ist es klar, oder? Wenn nicht, dann hier das erste Bild aus dem Internet: Es zeigt, dass es Kunden und deren Bestellungen gibt. Es ist sinnvoll, dass ein Kunde mehr als eine Bestellung haben kann. Es gibt eins-zu-viele :) Oder ein anderes Beispiel: Es gibt drei Tabellen: Verlag, Autor und Buch. Jeder Verlag, der nicht pleitegehen und erfolgreich sein will, hat mehr als einen Autor, finden Sie nicht auch? Jeder Autor kann wiederum mehr als ein Buch haben – daran kann auch kein Zweifel bestehen. Und das bedeutet wiederum die Verbindung eines Autors zu vielen Büchern, eines Verlegers zu vielen Autoren . Es gibt noch viele weitere Beispiele, die angeführt werden können. Die Wahrnehmungsschwierigkeit liegt vielleicht zunächst nur darin, abstrakt denken zu lernen: von außen auf die Tische und ihr Zusammenspiel zu schauen.

Eins zu eins (eins zu eins)

Man kann sagen, dass dies ein Sonderfall der Eins-zu-Viele-Kommunikation ist. Eine Situation, in der ein Datensatz in einer Tabelle nur mit einem Datensatz in einer anderen Tabelle verknüpft ist. Welche Beispiele aus dem Leben kann es geben? Wenn wir die Polygamie ausschließen, können wir sagen, dass zwischen Mann und Frau eine Eins-zu-Eins-Beziehung besteht. Selbst wenn wir sagen, dass Polygamie erlaubt ist, kann jede Frau immer noch nur einen Ehemann haben. Das Gleiche gilt auch für die Eltern. Jede Person kann nur einen leiblichen Vater und nur eine leibliche Mutter haben. Explizite Eins-zu-eins-Beziehung. Während ich dies schrieb, kam mir ein Gedanke: Warum sollte ich dann eine Eins-zu-eins-Beziehung in zwei Datensätze in verschiedenen Tabellen aufteilen, wenn diese bereits eine Eins-zu-eins-Beziehung haben? Die Antwort habe ich mir selbst ausgedacht. Diese Datensätze können auch auf andere Weise mit anderen Datensätzen verknüpft werden. Wovon rede ich? Ein weiteres Beispiel für Eins-zu-Eins-Verbindungen besteht zwischen dem Land und dem Präsidenten. Ist es möglich, alle Daten über den Präsidenten in der Tabelle „Land“ aufzuschreiben? Ja, das können Sie, SQL sagt kein Wort. Aber wenn Sie denken, dass der Präsident auch eine Person ist ... Und er kann auch eine Frau (eine weitere Eins-zu-eins-Beziehung) und Kinder (eine weitere Eins-zu-viele-Beziehung) haben, und dann stellt sich heraus, dass es so sein wird notwendig, um das Land mit der Frau und den Kindern des Präsidenten zu verbinden…. Klingt verrückt, oder? :D Für diesen Zusammenhang kann es noch viele andere Beispiele geben. Darüber hinaus können Sie in einer solchen Situation im Gegensatz zu einer Eins-zu-viele-Beziehung einen Fremdschlüssel zu beiden Tabellen hinzufügen.

Viel zu viel

Schon anhand des Namens lässt sich erahnen, worüber wir reden werden. Im Leben, und wir programmieren unser Leben, gibt es oft Situationen, in denen die oben genannten Arten von Verbindungen nicht ausreichen, um die Dinge zu beschreiben, die wir brauchen. Über Verlage, Bücher und Autoren haben wir bereits gesprochen. Hier gibt es einfach so viele Verbindungen ... Jede Veröffentlichung kann mehrere Autoren haben – eine Eins-zu-viele-Verbindung. Gleichzeitig kann jeder Autor mehrere Verlage haben (warum nicht, der Autor wurde an einem Ort veröffentlicht, hatte einen Streit um Geld, ging zum Beispiel zu einem anderen Verlag). Und dies ist wieder eine Eins-zu-Viele-Beziehung. Oder so: Jeder Autor kann mehrere Bücher haben, aber jedes Buch kann auch mehrere Autoren haben. Wieder eine Eins-zu-Viele-Beziehung zwischen Autor und Buch, Buch und Autor. Aus diesem Beispiel können wir eine formalisiertere Schlussfolgerung ziehen: Es war klar, wie man die vorherigen Verbindungstypen in SQL festlegt: Wir übergeben einfach die ID dieses einen an die Datensätze, von denen es viele gibt, oder? Ein Land gibt seine ID als Fremdschlüssel an viele Städte weiter. Was tun mit Viele-zu-Viele -Beziehungen ? Diese Methode ist nicht geeignet. Wir müssen eine weitere Tabelle hinzufügen, die die beiden Tabellen verbindet. Gehen wir zum Beispiel zu MySQL, erstellen eine neue Datenbank Manytomany und erstellen zwei Tabellen, Author und Book, die nur Namen und ihre IDs enthalten: CREATE DATABASE Manytomany; VERWENDEN Sie ManytoMany; CREATE TABLE Autor( id INT AUTO_INCREMENT, Name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); Erstellen wir nun eine dritte Tabelle, die zwei Fremdschlüssel aus unseren Autoren- und Buchtabellen enthält, und dieser Link wird eindeutig sein. Das heißt, es ist nicht möglich, einen Datensatz mit denselben Schlüsseln zweimal hinzuzufügen: CREATE TABLE authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES Autor (id), UNIQUE (book_id, author_id)); Hier haben wir mehrere neue Funktionen verwendet, die gesondert kommentiert werden müssen:

• NOT NULL bedeutet, dass das Feld immer ausgefüllt sein muss. Wenn dies nicht der Fall ist, teilt uns SQL dies mit.
• UNIQUE besagt, dass ein Feld oder eine Reihe von Feldern in der Tabelle eindeutig sein müssen. Es kommt häufig vor, dass zusätzlich zur eindeutigen Kennung ein weiteres Feld für jeden Datensatz eindeutig sein muss. Und genau dafür ist UNIQUE zuständig.

Aus meiner Praxis: Wenn wir von einem alten System auf ein neues umsteigen, müssen wir als Entwickler die IDs des alten Systems speichern, um damit arbeiten und unsere eigenen erstellen zu können. Warum eigene erstellen und nicht alte verwenden? Sie sind möglicherweise nicht eindeutig genug oder dieser Ansatz zur Erstellung von IDs ist möglicherweise nicht mehr relevant und eingeschränkt. Zu diesem Zweck haben wir den alten ID-Namen auch in der Tabelle eindeutig gemacht. Um dies zu überprüfen, müssen Sie Daten hinzufügen. Fügen Sie ein Buch und einen Autor hinzu: NSERT INTO book (name) VALUES ("book1"); INSERT INTO Autor (Name) VALUES ("Autor1"); Aus früheren Artikeln wissen wir bereits, dass sie die IDs 1 und 1 haben werden. Daher können wir sofort einen Datensatz zur dritten Tabelle hinzufügen: INSERT INTO authors_x_books VALUES (1,1); Und alles wird gut, bis wir den letzten Befehl noch einmal wiederholen wollen: also die gleichen IDs noch einmal aufschreiben: Das Ergebnis wird natürlich sein – ein Fehler. Es wird ein Duplikat geben. Der Eintrag wird nicht erfasst. Auf diese Weise entsteht eine Viele-zu-Viele-Verbindung... Das alles ist sehr cool und interessant, aber es stellt sich die logische Frage: Wie erhält man diese Informationen? Wie kombiniere ich Daten aus verschiedenen Tabellen und erhalte eine Antwort? Darüber werden wir im nächsten Teil sprechen))

Verbindungen (Joins)

Im vorherigen Teil habe ich Sie darauf vorbereitet, sofort zu verstehen, was Verknüpfungen sind und wo sie verwendet werden. Weil ich zutiefst davon überzeugt bin, dass, sobald das Verständnis da ist, alles sofort sehr einfach wird und alle Artikel über Verknüpfungen so klar sind wie die Augen eines Babys :D Grob und im Allgemeinen erhalten Verknüpfungen ein Ergebnis aus mehreren Tabellen mit Mitteln eines JOIN (Join von englisch „join“). Und das ist alles...) Und um beizutreten, müssen Sie das Feld angeben, durch das die Tabellen verbunden werden. Der Teufel ist nicht so gruselig, wie er dargestellt wird, oder?) Als Nächstes sprechen wir einfach darüber, welche Arten von Verknüpfungen es gibt und wie man sie verwendet. Es gibt viele Arten von Verknüpfungen und wir werden nicht alle berücksichtigen. Nur die, die wir wirklich brauchen. Deshalb haben wir kein Interesse an so exotischen Verbindungen wie Cross und Natural. Ich habe es völlig vergessen, wir müssen uns noch eine Nuance merken: Tabellen und Felder können Aliase haben – Pseudonyme. Sie werden bequem für Joins verwendet. Sie können beispielsweise Folgendes tun: SELECT * FROM table1; Wenn die Abfrage häufig Tabelle1 verwendet, können Sie ihr einen Alias ​​geben: SELECT* FROM table1 as t1; oder noch einfacher zu schreiben: SELECT * FROM table1 t1; und später in der Abfrage wird es dann möglich sein, t1 als Alias ​​für diese Tabelle zu verwenden.

INNER JOIN

Der gebräuchlichste und einfachste Join. Es besagt, dass, wenn wir zwei Tabellen und ein Feld haben, durch das sie verbunden werden können, alle Datensätze ausgewählt werden, deren Beziehungen in den beiden Tabellen bestehen. Es war irgendwie schwer zu sagen. Schauen wir uns ein Beispiel an: Fügen wir einen Datensatz zu unserer Städtedatenbank hinzu. Ein Eintrag für Städte und einer für Länder: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); und $ INSERT INTO Stadt (Name, Bevölkerung) VALUES("Tbilisi", 1171100); Wir haben ein Land hinzugefügt, das keine Stadt in unserer Tabelle hat, und eine Stadt, die keinem Land in unserer Tabelle zugeordnet ist. Daher beschäftigt sich INNER JOIN mit der Ausgabe aller Datensätze für die Verbindungen, die sich in zwei Tabellen befinden. So sieht die allgemeine Syntax aus, wenn wir zwei Tabellen, Tabelle1 und Tabelle2, verbinden möchten: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; und dann werden alle Datensätze zurückgegeben, die eine Beziehung in den beiden Tabellen haben. Wenn wir in unserem Fall Informationen zu Ländern und Städten erhalten möchten, sieht das so aus: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Obwohl die Namen gleich sind, ist hier deutlich zu erkennen, dass die Felder der Städte an erster Stelle stehen, dann die Felder der Länder. Aber die beiden Einträge, die wir oben hinzugefügt haben, sind nicht vorhanden. Denn genau so funktioniert INNER JOIN.

LINKS BEITRETEN

Es gibt Fälle, und zwar recht häufig, in denen wir mit dem Verlust von Feldern der Haupttabelle nicht zufrieden sind, weil in der angrenzenden Tabelle kein Eintrag dafür vorhanden ist. Dafür ist ein LEFT JOIN gedacht. Wenn wir in unserer vorherigen Anfrage LEFT statt INNER angeben, fügen wir in der Antwort eine weitere Stadt hinzu – Tiflis: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Es gibt einen neuen Eintrag über Tiflis und alles, was das Land betrifft, ist auf Null . So wird es oft verwendet.

RICHTIG BEITRETEN

Hier besteht der Unterschied zu LEFT JOIN darin, dass alle Felder nicht links, sondern rechts in der Verbindung ausgewählt werden. Das heißt, es werden keine Städte, sondern alle Länder übernommen: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Jetzt ist klar, dass es in diesem Fall kein Tiflis, sondern Usbekistan geben wird. So ähnlich…))

Sicherung von Verbindungen

Jetzt möchte ich Ihnen ein typisches Bild zeigen, das Junioren vor einem Vorstellungsgespräch pauken, um sie davon zu überzeugen, dass sie die Essenz von Joins verstehen: Hier wird alles in Form von Mengen dargestellt, jeder Kreis ist eine Tabelle. Und die Stellen, an denen es übermalt wird, sind die Teile, die in SELECT angezeigt werden. Lass uns nachsehen:

• INNER JOIN ist nur die Schnittmenge von Mengen, also den Datensätzen, die Verbindungen zu zwei Tabellen haben – A und B;
• LEFT JOIN sind alle Datensätze aus Tabelle A, einschließlich aller Datensätze aus Tabelle B, die eine Schnittmenge (Verbindung) mit A haben;
• RIGHT JOIN ist genau das Gegenteil von LEFT JOIN – alle Datensätze in Tabelle B und Datensätze aus A, die eine Beziehung haben.

Nach all dem sollte dieses Bild klar sein))

Hausaufgaben

Diesmal werden die Aufgaben sehr interessant sein und alle, die sie erfolgreich lösen, können sicher sein, dass sie bereit sind, mit der Arbeit auf der SQL-Seite zu beginnen! Die Aufgaben sind nicht zerkaut und für Mittelschüler geschrieben, sodass es für Sie nicht einfach und langweilig wird :) Ich gebe Ihnen eine Woche Zeit, um die Aufgaben selbst zu erledigen, und veröffentliche dann einen separaten Artikel mit einer detaillierten Analyse der Lösung der Aufgaben, die ich Ihnen gegeben habe.

Die eigentliche Aufgabe:

1. Schreiben Sie ein SQL-Skript, um die Tabelle „Student“ mit den folgenden Feldern zu erstellen: ID (Primärschlüssel), Name, Nachname, E-Mail (eindeutig).
2. Schreiben Sie ein SQL-Skript, um die Tabelle „Book“ mit den folgenden Feldern zu erstellen: ID, Titel (ID + Titel = Primärschlüssel). Verknüpfen Sie „Student“ und „Buch“ mit einer „Student“-Eins-zu-Viele-„Buch“-Beziehung.
3. Schreiben Sie ein SQL-Skript, um die Tabelle „Teacher“ mit den folgenden Feldern zu erstellen: ID (Primärschlüssel), Name, Nachname, E-Mail (eindeutig), Betreff.
4. Verknüpfen Sie „Schüler“ und „Lehrer“ mit einer „Schüler“-Viele-zu-Viele-Lehrer-Beziehung.
5. Wählen Sie „Student“ aus, dessen Nachname „oro“ enthält, zum Beispiel „Sid oro v“, „V oro novsky“.
6. Wählen Sie aus der Tabelle „Student“ alle Nachnamen („last_name“) und die Anzahl ihrer Wiederholungen aus. Bedenken Sie, dass es in der Datenbank Namensvetter gibt. Sortieren Sie nach Menge in absteigender Reihenfolge. Es sollte so aussehen:

   Familienname, Nachname	Menge
   Petrow	15
   Iwanow	12
   Sidorow	3

7. Wählen Sie aus „Student“ die drei am häufigsten wiederholten Namen aus. Sortieren Sie nach Menge in absteigender Reihenfolge. Es sollte so aussehen:

   Name	Menge
   Alexander	27
   Sergej	10
   Peter	7

8. Wählen Sie „Studenten“ aus, die die meisten „Bücher“ und zugehörigen „Lehrer“ haben. Sortieren Sie nach Anzahl in absteigender Reihenfolge. Es sollte so aussehen:

   Nachname des Lehrers	Nachname des Schülers	Buchmenge
   Petrow	Sidorow	7
   Iwanow	Schmied	5
   Petrow	Kankava	2>

9. Wählen Sie den „Lehrer“ aus, der von allen seinen „Schülern“ die meisten „Bücher“ hat. Sortieren Sie nach Menge in absteigender Reihenfolge. Es sollte so aussehen:

   Nachname des Lehrers	Buchmenge
   Petrow	9
   Iwanow	5

10. Wählen Sie „Lehrer“, dessen „Buchnummer“ für alle seine „Schüler“ zwischen 7 und 11 liegt. Sortieren Sie nach Menge in absteigender Reihenfolge. Es sollte so aussehen:

    Nachname des Lehrers	Buchmenge
    Petrow	elf
    Sidorow	9
    Iwanow	7

11. Drucken Sie alle „Nachnamen“ und „Namen“ aller „Lehrer“ und „Schüler“ mit dem Feld „Typ“ (Schüler oder Lehrer). Alphabetisch nach „Nachname“ sortieren. Es sollte so aussehen:

    Familienname, Nachname	Typ
    Iwanow	Student
    Kankava	Lehrer
    Schmied	Student
    Sidorow	Lehrer
    Petrow	Lehrer

12. Fügen Sie der vorhandenen Tabelle „Student“ eine Spalte „Rate“ hinzu, in der der Kurs gespeichert wird, in dem sich der Student gerade befindet (numerischer Wert von 1 bis 6).
13. Dieser Artikel ist nicht erforderlich, aber von Vorteil. Schreiben Sie eine Funktion, die alle „Bücher“ durchgeht und alle „Titel“ durch Kommas getrennt ausgibt.

Abschluss

Die Serie über die Datenbank hat sich etwas in die Länge gezogen. Zustimmen. Wir haben jedoch einen langen Weg zurückgelegt und sind daher mit Sachkenntnis aus der Sache hervorgegangen! Vielen Dank fürs Lesen. Ich erinnere Sie daran, dass jeder, der weitermachen und das Projekt verfolgen möchte, ein Konto auf GitHub erstellen und mein Konto abonnieren muss :) Weitere folgen – sprechen wir über Maven und Docker. Vielen Dank an alle fürs Lesen. Ich wiederhole es noch einmal: Wer geht, wird den Weg meistern ;)

Eine Liste aller Materialien der Serie finden Sie am Anfang dieses Artikels.