Wir analysieren Datenbanken und die SQL-Sprache. (Teil 4 – Hausaufgaben überprüfen) – „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. Frühere Artikel und Analyse von Hausaufgaben zu Datenbanken: 1 , 2 , 3 . An alle, die die Geduld und Ausdauer hatten, an alle, die diesen vierten Artikel mit mir durchgelesen haben: Gut gemacht. Wie man so schön sagt: Wer geht, kann den Weg meistern. Diese Woche erscheint der letzte Artikel über Datenbanken, in dem wir über Arten von Beziehungen und Verknüpfungen sprechen werden . Aber bevor wir uns mit den neuen Informationen befassen, werfen wir einen Blick auf unsere Hausaufgaben ... Ich habe mich wirklich wie ein Lehrer gefühlt. Seien Sie mir nicht böse: Ich habe keine pädagogische Ausbildung, das steht fest. Da letzte Woche eine detaillierte Überprüfung der Fernbedienung den Löwenanteil des Materials einnahm, habe ich beschlossen, die Analyse der Hausaufgaben und die Überprüfung des neuen Materials in zwei Teile aufzuteilen.

Eigentlich Hausaufgabenanalyse

Ich bin auf jeden Fall froh, dass es Leute gibt, die Parkinson machen und darüber reden. Es ist toll! Ich bin mir absolut sicher, dass einfaches Lesen ohne Festigung des Wissens ein Weg ins Nichts ist. Deshalb gilt allen, die es getan oder versucht haben, Respekt. Ich möchte Sie an die Bedingungen der Aufgaben erinnern:

1. Verstehen Sie den HAVING- Operator und schreiben Sie eine Beispielabfrage für die Tabellen aus unserem Beispiel. Wenn Sie zur besseren Übersichtlichkeit einige Felder oder weitere Werte hinzufügen müssen, fügen Sie diese hinzu. Wenn jemand möchte, schreibt eure Beispiellösung in die Kommentare – so kann ich sie bei Gelegenheit auch mal anschauen.
2. Installieren Sie MySQL Workbench, um über die Benutzeroberfläche mit der Datenbank zu arbeiten. Ich denke, wir haben die Arbeit an der Konsole bereits ausreichend geübt. Stellen Sie eine Verbindung zur Datenbank her. Wenn Sie etwas anderes zum Arbeiten mit der Datenbank verwenden, können Sie diese Aufgabe gerne überspringen. Hier und weiterhin werde ich ausschließlich MySQL Workbench verwenden.
3. Schreiben Sie Empfangsanfragen mit unseren Daten:
   1. das kleinste/bevölkerungsreichste Land;
   2. durchschnittliche Einwohnerzahl im Land;
   3. durchschnittliche Einwohnerzahl in Ländern, deren Namen auf „a“ enden;
   4. die Zahl der Länder mit einer Bevölkerung von mehr als vier Millionen;
   5. Sortieren Sie die Länder nach abnehmender Einwohnerzahl.
   6. Sortieren Sie die Länder nach Namen in natürlicher Reihenfolge.

Reden wir über HABEN

Wenn Sie den Operator „Having“ kennen, können Sie mehr als ein Interview bestehen, bei dem es SQL-Probleme geben wird. Daher ist es äußerst wichtig, es zu verstehen. Es ist einfach so, dass Sie keine Bedingungen für Aggregationsfunktionen (SUM, MIN, MAX, AVG) verwenden können. Darüber hinaus wird HAVING für gruppierte Felder verwendet. Was bedeutet das? Wenn wir beispielsweise Länder erreichen wollen, in denen die durchschnittliche Einwohnerzahl in Städten mehr als 50.000 Einwohner beträgt, können wir auf HAVING nicht verzichten. Soweit ich weiß, geschieht dies, weil die Aggregation nach der Ausführung der WHERE-Anweisung erfolgt und es unmöglich ist, Aggregationswerte hinzuzufügen, die später berechnet werden. Auch wenn meine Meinungen bisher nicht viel zum Verständnis beitragen, können Sie dies einfach als Tatsache akzeptieren und sich darauf einlassen. Beim Programmieren kommt es oft vor, dass, wenn etwas in einem Moment unklar ist, das Gehirn es einfach noch nicht verdaut hat. Schlafen Sie mit diesem Gedanken, und am nächsten Tag wird alles klarer.

MySQL Workbench installieren

Hier und weiterhin werde ich Workbench für Abfragen verwenden. Ich zeige Ihnen, was Sie zur Installation und zum Herstellen einer Verbindung zur Datenbank benötigen. Da es sich um ein Produkt von Oracle handelt, müssen Sie lediglich auf deren Website die gewünschte Version und das gewünschte Betriebssystem auswählen. Folgen Sie dazu diesem Link : Hier können Sie genau das Betriebssystem auswählen, das Sie benötigen. Klicken Sie auf „Herunterladen“ , aber anstatt herunterzuladen, sehen wir dieses Fenster: Verlieren Sie sich nicht, suchen Sie einfach nach der Schaltfläche „ Nein danke, starten Sie einfach meinen Download“ , und der Download beginnt. Warum tun sie das? Damit sich mehr Leute bei ihnen registrieren, ist uns das wahrscheinlich nicht wichtig. Führen Sie nach erfolgreichem Download die Installationsdatei aus. Unter MacOS sieht das so aus: Einfach das Icon ziehen und fertig, die Installation ist abgeschlossen. Nicht so schwierig wie die Installation von MySQL selbst, oder? Oder wir haben uns einfach daran gewöhnt und sind erfahrener geworden ;) Der zweite Teil dieser Aufgabe besteht darin, eine Verbindung zu unserer Datenbank herzustellen. Was wird dafür benötigt? Klicken Sie auf das Pluszeichen neben MySQL-Verbindungen: Geben Sie im angezeigten Fenster die erforderlichen Daten ein:

• Verbindungsname – der Name unserer Verbindung. Schreiben Sie möglichst eindeutige Namen, damit es später keine Probleme bei der Identifizierung gibt. Ich gebe dieser Verbindung den Namen JRTB_DB ;
• Hostname – wird bereits als lokal 127.0.0.1 (auch bekannt als „localhost“) festgelegt . In unserem Fall muss nichts geändert werden, da die Datenbank auf dem Computer installiert ist. Wenn sich die Datenbank jedoch woanders befindet, muss der Host (IP des Computers, auf dem die Datenbank ausgeführt wird) entsprechend geändert werden.
• Benutzername – Sie können bei Bedarf auch Ihren Benutzer angeben. Wenn Sie diesem Schlüssel nichts hinzugefügt haben, lassen Sie ihn unverändert.
• Passwort – Klicken Sie auf „Im Schlüsselbund speichern“ und legen Sie genau das Passwort fest, das Sie selbst festgelegt haben. Ich habe alles einfach gelassen – root .

Um zu überprüfen, ob die Verbindung funktioniert, klicken Sie auf Verbindung testen : Nun, wenn alles richtig gemacht wurde, wird das Ergebnis nicht lange auf sich warten lassen: Jetzt haben wir eine gespeicherte Verbindung in der Datenbank und müssen keine Verbindung erstellen und füllen Geben Sie jedes Mal Ihren Namen und Ihr Passwort ein. Und dieses Glück wird so aussehen: Wir gehen in die neu erstellte Verbindung und sehen das Anfragefenster. Um sicherzustellen, dass alles korrekt ist, überprüfen wir die Liste der Datenbanken, gehen zu unserer und holen uns alle Daten über die Städte: Und hier erhalten wir einen Freibrief für das, was wir brauchen. Der erste Abschnitt ist für das Skript verantwortlich, das wir eingeben. Als nächstes sehen wir im Ergebnisraster das Ergebnis der letzten Operation im Skript. Und die Aktionsausgabe zeigt eine Liste der Vorgänge und deren Ergebnisse. Eine sehr nützliche Sache möchte ich Ihnen sagen: Mit seiner Hilfe können Sie die Ausführungsgeschwindigkeit bestimmter Skripte überwachen. Warum ist es wichtig? Eines der häufigsten Probleme bei der Geschwindigkeit der Aufgabenausführung in einer Anwendung ist die Geschwindigkeit der Abfrageausführung in der Datenbank. Hier können Sie diese schnell und bequem per Hand überprüfen.

Wir schreiben die notwendigen Anfragen

Wir müssen nur noch 7 Anfragen bearbeiten, los geht's!

1. Holen Sie sich das bevölkerungsreichste Land. Hier kann man geschickt vorgehen und mehrere Routen einschlagen:

• Laut Ländertabelle

Dann müssen wir unsere Abfrage nur noch nach Bevölkerung sortieren und nur einen Datensatz verwenden. Dazu müssen Sie am Ende des Skripts einen LIMIT- Operator hinzufügen und die erforderliche Menge angeben: $ SELECT * FROM Country ORDER BY Population DESC LIMIT 1;

• Laut Stadttabelle

Hier ist alles interessanter, da die Anfrage komplexer, aber auch interessanter wird. Da wir noch keine Ahnung von Joins haben, können wir nur die Länder-ID erhalten: $ SELECT land_id, SUM(bevölkerung) FROM city GROUP BY land_id ORDER BY SUM(bevölkerung) DESC LIMIT 1; Hier haben wir etwas Cooles gemacht: Wir haben die Summe der Bevölkerung aller bekannten Städte in jedem Land gesammelt, nach dieser Summe sortiert und das erste Element genommen. Nun, ist es großartig? Ich bin begeistert :D Danach fühlt man sich sofort wie ein Abfrage-Guru ... (natürlich nicht lange))

2. Holen Sie sich das kleinste Land. Hier kann man geschickt vorgehen und mehrere Routen nehmen

In diesem Fall wird alles genau gleich sein. Der einzige Unterschied besteht darin, dass die Sortierung umgekehrt wird – das ist alles. Daher schreibe ich einfach Anfragen:

• Laut Stadttabelle

$ SELECT land_id, SUM(bevölkerung) FROM city GROUP BY land_id ORDER BY SUM(bevölkerung) LIMIT 1;

• Laut Ländertabelle

$ SELECT * FROM Land ORDER BY Population LIMIT 1; Und überzeugen Sie sich selbst vom Ergebnis!

3. Durchschnittliche Einwohnerzahl des Landes

Auch hier ist die technische Spezifikation irgendwie nicht korrekt, als ob der Manager sie geschrieben hätte ... Warum habe ich mich dafür entschieden? Weil nicht klar ist, in welcher Tabelle gearbeitet werden soll. Aber das ist normal: Es gibt einfach keine Aufgaben, bei denen alles sofort klar und verständlich ist. Deshalb müssen Sie die Aufgaben sorgfältig lesen und wenn Sie Fragen haben, stellen Sie diese sofort! Genau, eine Bemerkung. Unter Berücksichtigung der Daten, die wir in der Datenbank haben, werden wir anhand von Daten aus Städten suchen. Schreiben Sie dazu die folgende Abfrage: $ SELECT Country_id, AVG(population) FROM city GROUP BY Country_id; Hier ist es ganz einfach: Wir nutzen die AVG-Funktion und gruppieren unsere Stadtdatensätze nach Ländern.

4. Durchschnittliche Einwohnerzahl in Ländern, deren Namen mit „a“ enden

Auf Anfrage wird es hier eine kleine Änderung geben. Wir müssen die Filterung nach Namen hinzufügen, bevor wir die Gruppierung durchführen. Wie alle Schüler mache ich meine Hausaufgaben, bevor ich diesen Artikel veröffentliche, und ich verstehe, dass dieses Problem ohne Verknüpfungen nicht gelöst werden kann. Warum? Denn neben der Länder-ID benötigen wir auch seinen Namen. Und dies ist nicht möglich, ohne zwei Tabellen zu einem Datensatz zusammenzufassen. Deshalb werde ich diese Aufgabe natürlich erledigen, aber das ist mein Pfosten...))) Ich wollte mir eine Aufgabe ausdenken, die LIKE in der Anfrage verwendet...) $ SELECT ci.country_id, AVG(ci.population ) FROM Stadt ci INNER JOIN Land co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id; Was ist hier passiert? Zuerst haben wir Datensätze aus den Stadt- und Ländertabellen mit dem Fremdschlüssel „country_id“ zusammengeführt, nach Ländernamen gefiltert, sodass sie auf „a“ endeten, und erst dann nach „country_id“ gruppiert.

5. Anzahl der Länder mit einer Bevölkerung von mehr als vier Millionen

Hier müssen wir nur die COUNT-Funktion verwenden und eine Filterung nach Bevölkerung hinzufügen: $ SELECT COUNT(*) from country WHERE population > 4000000; Als Ergebnis stellen wir fest, dass es drei solcher Länder gibt. Ist das richtig? Ja, nur Moldawien schafft es nicht, diesen Meilenstein zu erreichen.

6. Sortieren Sie die Länder nach abnehmender Einwohnerzahl

Dazu müssen wir den uns bereits bekannten ORDER BY-Operator verwenden. Beachten Sie jedoch, dass die Sortierung standardmäßig in natürlicher Reihenfolge erfolgt. Bei Zahlen bedeutet dies, dass aufsteigend sortiert wird, bei Zeichenfolgen, dass ab dem ersten Zeichen sortiert wird. Wenn wir eine absteigende Sortierung benötigen, benötigen wir die Umkehrung der natürlichen Sortierung: $ SELECT * FROM Country ORDER BY Population DESC;

7. Sortieren Sie die Länder in natürlicher Reihenfolge nach Namen

Hier ist die Kenntnis der natürlichen Ordnung von Nutzen. Da es die Standardeinstellung ist, ist es für uns ein Kinderspiel: $ SELECT * FROM country ORDER BY name;

Statt Ausgabe

Zufälligerweise wird der Umfang der Hausaufgabenlösung sehr groß sein, daher machen wir eine Ausnahme: Ich veröffentliche diesen Artikel mit einem Scheck und am Freitag werde ich neues Material mit Verbindungen und Verknüpfungen veröffentlichen. Vielen Dank an alle fürs Lesen. Bis Freitag!

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