Wir analysieren Datenbanken und die SQL-Sprache. (Teil 6 – Überprüfung der endgültigen Aufgabe) – „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. Dieser Teil enthält eine Analyse der endgültigen Aufgabe in der Datenbank. Grüße, liebe Leser. Heute analysieren wir die Aufgabe aus dem letzten Artikel zur Datenbank. Es ist interessant, weil es für Interviews auf mittlerer Ebene gedacht ist. Das heißt, nach dieser Aufgabe können Sie bereits zu einem Vorstellungsgespräch gehen und werden zumindest einen Teil der Aufgaben im Zusammenhang mit relationalen Datenbanken erfolgreich bestehen. Ich weiß, wie notwendig dieser Artikel sein kann, und deshalb werde ich meine ganze Erfahrung einsetzen, um ihn nützlich und interessant zu gestalten. Und wenn Sie nicht mitten im Artikel einschlafen, bedeutet das, dass ich mein Ziel erreicht habe. Ich werde nicht die gesamte Aufgabe wiederholen: Ich werde sie vor Abschluss jeder Aufgabe zitieren und sie kursiv unterstreichen. Ich gehe davon aus, dass jeder, der diesen Artikel liest, alle Abfragen in seiner Datenbank ausführen und das Gleiche erhalten wird. Dies wird dem Unternehmen den größtmöglichen Nutzen bringen. Und ich werde ein bisschen glücklicher sein, wenn ich daran denke, dass ich jemandem bei unserer schwierigen Aufgabe geholfen habe)

Übung 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). Wir haben dies bereits getan, daher sollte es keine Probleme geben. Im Skript müssen Sie den Primärschlüssel und ein eindeutiges Feld angeben, das sich vom Primärschlüssel unterscheidet. Erstellen wir zunächst eine neue Datenbank für diese Aufgabe: $ CREATE DATABASE final_task; Und nutzen wir diese Datenbank: $ USE final_task; Sobald die Umgebung eingerichtet und für die Ausführung des Jobs bereit ist, können wir das folgende Skript schreiben: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNIQUE (email ) ); Bisher gibt es nichts Neues im Vergleich zu dem, was wir bereits durchgemacht haben. Irgendwelche Kommentare sind unnötig, lasst uns weitermachen.

Aufgabe 2-3

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. Lassen Sie uns zwei Aufgaben zu einer kombinieren, um sie schneller und bequemer zu machen. Wie man einen separaten Fremdschlüssel hinzufügt, habe ich bereits in früheren Artikeln besprochen. Darüber hinaus müssen wir uns daran erinnern, wie und durch was wir Verbindungen herstellen. Der vorherige Artikel wird Ihnen helfen, und hier ist das Skript: $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id ) ); Auf diese einfache Weise haben wir einen zusammengesetzten Schlüssel für unsere Tabelle PRIMARY KEY (id, title) hinzugefügt , jetzt ist der Schlüssel genau das Paar. Dies bedeutet, dass die Tabelle mehr als einen identischen ID-Feldwert enthalten kann. Und genau das Gleiche gilt für den Titel.

Aufgabe 4

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. Wir bereiten unsere Datenbank weiterhin für Abfragen vor und erstellen eine Lehrertabelle: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id), UNIQUE (E-Mail) ); Bisher ist es nicht schwierig, oder? Drei Aufgaben sind bereits erledigt!

Aufgabe 5

Verknüpfen Sie „Schüler“ und „Lehrer“ mit einer „Schüler“-Viele-zu-Viele-Lehrer-Beziehung. Das ist jetzt interessanter! Wir haben erst letztes Mal darüber gesprochen. Ich möchte Sie daran erinnern, was dazu erforderlich ist: Sie müssen eine Zwischentabelle erstellen, in der Schüler-Lehrer-Paare gespeichert werden. Mit seiner Hilfe wird es möglich sein, eine Viele-zu-Viele-Beziehung herzustellen. Erstellen wir daher eine Tabelle Students_x_techers . Der Benennungsansatz ist offen und könnte auch lauten: student_teacher . $ CREATE TABLE Students_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES teacher(id) ); Wie Sie sehen, ist alles klar und schlüssig erledigt. Wir haben einen zusammengesetzten Schlüssel für zwei Fremdschlüssel: student_id und teacher_id. Warum auch ein Fremdschlüssel? Damit wir sicher sein können, dass für die aufgezeichneten Paare Datensätze in den Schüler- und Lehrertabellen vorhanden sind.

Aufgabe 6

Wählen Sie „Student“ aus, dessen Nachname „oro“ enthält, zum Beispiel „Sidorov“, „Voronovsky“. Um es für uns interessant und anschaulich zu machen, schlage ich vor, zunächst mehrere Studierende hinzuzufügen, sodass einige von ihnen für diese Anfrage geeignet sind und andere nicht. Schreiben wir daher diejenigen auf, die als Ergebnis der Anfrage einbezogen werden sollen : $ INSERT INTO student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Und diejenigen, die nicht reinkommen sollten: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Lassen Sie uns das Ergebnis überprüfen und uns die Liste der Daten in der Tabelle „students“ ansehen: $ SELECT * FROM student; und wir erhalten: Insgesamt gibt es vier Datensätze, zwei davon sollten passen und zwei nicht. Nachdem wir alle Daten für die Anfrage vorbereitet haben, können wir eine Anfrage für die Aufgabe selbst stellen: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Daraufhin gingen Ivan und Nikolai die Liste durch.

Aufgabe 7

Die nächste Aufgabe lesen wir: 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

Aus Gründen der Übersichtlichkeit müssen Sie weitere Daten hinzufügen. Fügen wir ohne weiteres die Petrovs, Ivanovs und Sidorovs hinzu, die ihre Verwandtschaft nicht kennen ;) Ich werde keine E-Mail-Adresse erfinden, sondern sie einfach aus den neuen Einträgen ausschließen. Lassen Sie uns den folgenden Befehl 12 Mal ausführen: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Fügen wir 15 Petrovs hinzu: $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); Und zwei Sidorovs (wir haben bereits einen))): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); Die Daten sind nun fertig. Um solche Daten zu erhalten, müssen Sie eine Gruppierung durchführen. Um eine Gruppierung durchzuführen, müssen Sie den Operator „Gruppieren nach“ verwenden, und zwar anhand des Felds „last_name“. Sie können auch feststellen, dass die Anzahl der Wiederholungen als Menge bezeichnet wird , und hier müssen Sie sich auch daran erinnern, wie man Aliase in SQL erstellt: $ SELECT last_name, COUNT(*) as amount FROM student GROUP BY last_name ORDER BY COUNT(*) DESC ; Also habe ich es mit den Petrovs übertrieben - es stellte sich heraus, dass es 16 waren))

Aufgabe 8

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

Name	Menge
Alexander	27
Sergej	10
Peter	7

Oh, zu diesem Zweck haben wir bereits Ivans, Peters und Sidors. Daher besteht keine Notwendigkeit, sie hinzuzufügen. Wir wissen bereits, wie man sortiert. Das Einzige, worüber wir heute nicht gesprochen haben, ist die Auswahl einer bestimmten Anzahl von Datensätzen. Dies ist bereits in früheren Lösungen für Datenbankprobleme aufgetaucht. Wer es noch nicht gelesen hat, sollte es lesen. Im Übrigen kommen wir gleich zur Sache: $ SELECT name, COUNT(*) as amount FROM student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; Wie aus der Abfrage hervorgeht, treten bei der Ausführung einer solchen Abfrage keine Probleme auf, wenn Sie die Reihenfolge der Operatoren in einer SELECT-Abfrage kennen. Und diese Aufgabe liegt noch immer bei uns. Und die zuvor präsentierten Erkenntnisse reichen völlig aus, um dieses Problem zu lösen.

Aufgabe 9

Aufgabenbedingung: Wählen Sie den „Schüler“ aus, der über die meisten „Bücher“ und zugehörigen „Lehrer“ verfügt. Sortieren Sie nach Menge in absteigender Reihenfolge. Es sollte so aussehen:

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

Diese Aufgabe ist also eindeutig schwieriger als die vorherige, oder? Kein Wunder: Es riecht nach Join ... und nach mehr als einem.) Zuerst müssen wir verstehen, was zu tun ist ... Es ist klar, dass die Menge des Buches eine Gruppierung erfordert. Aber was? Und warum sollten wir sie gruppieren? Die Abfrage umfasst drei Tabellen, Gruppierung und Sortierung. Gemessen an der Tatsache, dass Datensätze, in denen keine Bücher vorhanden sind, nicht angezeigt werden, bedeutet dies, dass Sie INNER JOIN verwenden müssen. Wir werden auch eine Anfrage für LEFT JOIN stellen, damit es hierbei keine Probleme gibt. Und es gibt mehrere Möglichkeiten. Als Erstes fügen wir drei Tabellen zu einem Datensatz zusammen. Als nächstes gruppieren wir nach Schülern und fügen den Namen des Lehrers hinzu. Was werden wir wählen? Der Name des Lehrers, des Schülers und die Anzahl der Bücher. Fügen wir Daten für die Anfrage hinzu:

• drei Lehrer;
• zehn Bücher;
• Verbinden Sie zwei Schüler mit drei Lehrern.

Drei Lehrer

$ INSERT INTO teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO teacher(last_name) VALUES ('Shevchenko'); $ INSERT INTO teacher(last_name) VALUES ('Vasilenko');

10 Bücher

Ich nehme die Ausweise der Erst- und Zweitschüler mit. Ich werde ihnen Bücher beifügen. Da AUTO_INCREMENT nicht gesetzt wurde, müssen Sie Folgendes tun, um nicht jedes Mal eine neue ID zu schreiben: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Als nächstes fügen Sie Bücher für den ersten Schüler hinzu: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO book (title, student_id) VALUES('book2', 1); $ INSERT INTO book (title, student_id) VALUES('book3', 1); $ INSERT INTO book (title, student_id) VALUES('book4', 1); $ INSERT INTO book (title, student_id) VALUES('book5', 1); $ INSERT INTO book (title, student_id) VALUES('book6', 1); Und Bücher für den zweiten Schüler: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO book (title, student_id) VALUES('book8', 2); $ INSERT INTO book (title, student_id) VALUES('book9', 2); $ INSERT INTO book (title, student_id) VALUES('book10', 2);

Verbindungen zwischen Lehrern und Schülern

Fügen Sie dazu Students_x_teachers zur Tabelle hinzu: $ INSERT INTO Students_x_teachers VALUES (1,1); $INSERT INTO Students_x_teachers VALUES(1,2); $INSERT INTO Students_x_teachers VALUES(2,3);

Lassen Sie uns die Anfrage umsetzen

Wir machen den ersten Schritt – wir verknüpfen drei Tabellen in einem Datensatz: $ SELECT * FROM teacher tch INNER JOIN Students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; Natürlich haben wir noch keine Aufzeichnungen, aber wir können sehen, dass wir drei Tabellen erfolgreich verbunden haben. Jetzt fügen wir Buchgruppierung, -sortierung und die Felder hinzu, die wir benötigen: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) als Bücher FROM student st INNER JOIN book b ON st.id = b.student_id INNER JOIN Students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN teacher tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY Books DESC; Wir erhalten jedoch einen Fehler in SQL und die folgende Antwort: Fehlercode: 1055. Ausdruck Nr. 1 der SELECT-Liste ist nicht in der GROUP BY-Klausel und enthält die nicht aggregierte Spalte „final_task.tch.last_name“, die funktional nicht von Spalten in GROUP BY abhängig ist Klausel Es funktioniert nicht, diese Elemente zu verwenden, da zwischen Lehrer und Schüler eine Viele-zu-Viele-Beziehung besteht. Und es stimmt: Wir können nicht nur einen Lehrer pro Schüler bekommen. Gehen wir also in die andere Richtung. Lassen Sie uns etwas namens View SQL verwenden. Was ist die Idee: Wir erstellen eine separate Ansicht, eine neue Tabelle, bereits mit der Gruppierung, die wir benötigen. Und zu dieser Tabelle werden wir die notwendigen Namen der Lehrer hinzufügen. Da wir jedoch berücksichtigen, dass es mehrere Lehrkräfte geben kann, werden die Einträge wiederholt. Erstellen Sie eine Ansicht: $ CREATE VIEW studentBooks as SELECT st.last_name,st.id,COUNT(*) as Books FROM student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY Books DESC; Als Nächstes arbeiten wir mit dieser Ansicht als einfache Tabelle mit drei Feldern: Nachname des Studenten, Studenten-ID und Anzahl der Bücher. Basierend auf der Schüler-ID können wir auch einen Lehrer über zwei Joins hinzufügen: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from studentbook sbw INNER JOIN Students_x_teachers stch ON sbw. id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id; Und jetzt wird das Ergebnis sein: Huh! Das ist eine Anfrage, oder?) Es stellte sich wie erwartet heraus: Ein Schüler mit der ID=1 hat sechs Bücher und zwei Lehrer, und ein Schüler mit der ID=2 hat vier Bücher und einen Lehrer.

Aufgabe 10

Bedingung: Wählen Sie den „Lehrer“ aus, der unter all seinen „Schülern“ die größte Anzahl an „Büchern“ hat. Sortieren Sie nach Menge in absteigender Reihenfolge. Es sollte so aussehen:

Nachname des Lehrers	Buchmenge
Petrow	9
Iwanow	5

Hier können wir eine vorgefertigte Anfrage aus der vorherigen Aufgabe verwenden. Was müssen wir daran ändern? Wir haben diese Daten bereits, wir müssen nur noch eine weitere Gruppierung hinzufügen und den Namen des Schülers aus den Ausgabedaten entfernen. Aber zuerst fügen wir dem Lehrer einen weiteren Schüler hinzu, um das Ergebnis interessanter zu machen. Dazu schreiben wir: $ INSERT INTO Students_x_teachers VALUES (2, 1); Und die Abfrage selbst: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch . teacher_id GROUP BY tch.id; Als Ergebnis erhalten wir: Dieser Lehrer Wassilenko hat 10 Bücher und Schewtschenko hat 6...)

Aufgabe 11

Bedingung: Wählen Sie einen „Lehrer“, dessen Anzahl an „Büchern“ 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

Hier werden wir HAVING verwenden. Wir haben über ihn gesprochen. Die Anfrage ist genau die gleiche wie zuvor, nur müssen Sie die Bedingung hinzufügen, dass die Anzahl der Bücher in einem bestimmten Bereich liegt. Und wie ich in früheren Artikeln gesagt habe, müssen wir, wenn wir während der Gruppierung und/oder bei Aggregationsfunktionen filtern müssen, HAVING verwenden : $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; Ich habe den Teil hervorgehoben, den ich hinzugefügt habe. Und tatsächlich das erwartete Ergebnis: Nur Vasilenko hat diese Runde bestanden))

Aufgabe 12

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

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

Das heißt, wir müssen zwei Ausgänge kombinieren, und genau dafür ist UNION da. Mit anderen Worten, wir nehmen Aufzeichnungen von Schülern und Lehrern und drucken sie zusammen aus: $ SELECT last_name, 'teacher' as type from teacher UNION ALL select last_name, 'student' as type from student ORDER BY last_name; Und es wird sowohl Lehrer als auch Schüler geben. Es scheint, dass alles einfach ist, aber dann sehen wir uns bereits das Ergebnis an. Sie müssen also zwei Dinge erraten.

Aufgabe 13

Bedingung: 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). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (Rate > 0 UND Rate < 7); Hier fügen wir über ALTER TABLE und CHECK ein Feld hinzu, um den Grenzwert für dieses Feld von 1 auf 6 festzulegen.

Aufgabe 14

Bedingung: Dieser Artikel ist nicht erforderlich, aber von Vorteil. Schreiben Sie eine Funktion, die alle „Bücher“ durchgeht und alle „Titel“ durch Kommas getrennt ausgibt. Hier müssen Sie als Ergebnis der Anfrage lediglich eine Zeile zurückgeben, die alle Titel der Bücher enthält. Auch hier musste ich googeln. Es gibt eine solche Funktion – GROUP_CONCAT , mit der das ganz einfach geht: $ SELECT GROUP_CONCAT(title) from book; Und das ist alles...)) Alle 14 Aufgaben sind fertig.

Schlussfolgerungen

Uuhhh... Es war nicht einfach. Es war interessant. Die Aufgaben haben sich gelohnt, da bin ich mehr als sicher. Während wir diese Aufgaben erledigten, gingen wir auf viele Dinge ein, die vorher nicht bekannt waren:

• SQL-ANSICHT
• GROUP_CONCAT
• UNION

usw. Vielen Dank an alle, die die Kraft hatten, zu lesen und zu wiederholen, was ich getan habe. Wer weiß, wie man Anfragen besser stellt - schreibt in die Kommentare, ich werde sie auf jeden Fall lesen)

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