Wir analysieren Datenbanken und die SQL-Sprache. (Teil 3) - „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.

• Hausaufgaben überprüfen
  • Hinzufügen eines Primärschlüssels
  • Moldawien hinzufügen
  • Erstellen Sie die Städtetabelle
  • Fügen Sie Städten einen Fremdschlüssel hinzu
  • Bonusteil. Testen
• SELECT-Anweisung
  • WHERE-Parameter
  • ORDER BY-Parameter
  • GROUP BY-Parameter
• Hausaufgaben
• Abschluss

Hallo, meine Damen und Herren, reden wir weiter über Datenbanken, SQL und andere Dinge. Das heutige Material wird teils Theorie und teils Praxis enthalten. Ich möchte Sie daran erinnern, dass wir letztes Mal darüber gesprochen haben, wie man alles einrichtet, wie man eine Datenbank und eine Tabelle erstellt und Daten daraus erhält. Es ist Zeit zu sehen, ob mit der Fernerkundung etwas geklappt hat. Meiner Meinung nach hätte die Hälfte davon nur auf der Grundlage des vorherigen Artikels erledigt werden können. Es stellte sich heraus, dass man, um eine Anwendung richtig zusammenzustellen und alles mehr oder weniger schön zu machen, über Datenbanken sprechen muss, und um darüber zu sprechen, muss man viel Zeit aufwenden.

Hausaufgaben überprüfen

Großer Respekt an alle, die die Aufgaben erfolgreich erledigt haben. Das bedeutet, dass Sie verstehen, dass nur Sie dies brauchen und es nur Ihnen hilft. Für diejenigen, die meine Aufgabe vernachlässigt haben, möchte ich Sie an die Bedingung erinnern:

1. Sie müssen einen Primärschlüssel (PRIMARY KEY) aus dem ID-Feld zum Ländertabellenschema hinzufügen.
2. Fügen Sie der Ländertabelle ein weiteres Land hinzu: Moldawien.
3. Erstellen Sie gemäß dem Schema des vorherigen Artikels eine Tabelle city, die alle beschriebenen Felder enthält. Die Feldnamen lauten wie folgt: ID, Name, Länder-ID, Bevölkerung.
4. Fügen Sie der Stadttabelle einen Primärschlüssel hinzu.
5. Fügen Sie der Stadttabelle einen Fremdschlüssel hinzu.

Um zu beginnen, verwenden wir den ersten Teil des vorherigen Artikels und gehen zum Datenbankterminal.

Hinzufügen eines Primärschlüssels

Sie können einen Primärschlüssel (PRIMARY KEY) auf zwei Arten hinzufügen: sofort beim Erstellen einer Tabelle oder nach der Erstellung mit ALTER TABLE.

Primärschlüssel während der Tabellenerstellung

Da wir bereits eine Tabelle erstellt haben und wir diesen Ansatz ohne deren Löschung nicht in dieser Datenbank zeigen können, erstellen wir einfach eine temporäre Testdatenbank, in der wir alles tun. Geben wir die folgenden Befehle ein:

• Erstellen Sie eine neue Datenbank:

  $CREATE DATABASE-Test;

• Erstellen Sie eine Tabelle und fügen Sie einen Primärschlüssel hinzu:

  $ CREATE TABLE Land(id INT, Name VARCHAR(30), PRIMARY KEY (id));

Im Allgemeinen nichts Kompliziertes. Nach der Deklaration der Variablen wird der folgende Teil PRIMARY KEY (id) hinzugefügt , wobei in Klammern der Name des Feldes übergeben wird, das als Primärschlüssel dienen soll. Und sehen wir uns an, wie sich das Tabellenschema geändert hat: $ DESC Land; Wie Sie sehen können, wurde im Schlüsselfeld für den ID-Datensatz der Wert PRI angezeigt .

Primärschlüssel nach Tabellenerstellung

Wie ich bereits sagte, kann der erste Schlüssel nach dem Erstellen einer Tabelle mit ALTER TABLE zugewiesen werden . Wir werden dieses Beispiel in unserer Städtedatenbank ausführen :

• Gehen wir vom Test zu unserer Datenbank:

  $USE-Städte;

• Überprüfen wir, ob wir uns definitiv in unserer Datenbank befinden (dort sollte es ein weiteres Feld geben – Bevölkerung). Dazu schreiben wir:

  $ DESC-Bevölkerung;

Alles ist in Ordnung, der Tisch gehört uns. Schreiben wir Folgendes:

$ ALTER TABLE Land ADD PRIMARY KEY (id);

• und überprüfen Sie es sofort mit dem Befehl:

  $DESC Land;

Wie Sie auf dem Bild sehen können, stimmt alles, der PRI- Wert ist genau dort, wo er sein sollte. Wir haben übrigens mit einer Testdatenbank gearbeitet. Jetzt müssen wir es löschen: Warum müssen wir den Server überladen, oder? Dazu verwenden wir einen ziemlich bekannten Befehl: $ DROP DATABASE test;

Moldawien hinzufügen

Zuerst müssen wir entscheiden, was wir aufnehmen wollen. Unsere nächste ID wird 4 sein. Der Name wird Moldawien sein und die Einwohnerzahl beträgt 3550900. Deshalb führen wir den Befehl INSERT INTO aus, den wir bereits kennen: $ INSERT INTO land VALUES (4, 'Moldawien', 3550900); Und wir prüfen, ob dieser Wert genau in der Datenbank vorhanden ist: $ SELECT * FROM country WHERE id = 4; In der Datenanfrage habe ich sofort festgelegt, welches Feld durchsucht werden soll, sodass wir nur einen Datensatz erhielten, den wir brauchten.

Erstellen Sie die Städtetabelle

Anhand des Diagramms aus dem ersten Artikel zur Datenbank erhalten wir die notwendigen Informationen zur Tabelle. Es enthält die folgenden Felder:

• id – eindeutige Kennung;
• Name – Name der Stadt;
• Country_id – Länderfremdschlüssel;
• Bevölkerung – Bevölkerung der Stadt.

Es ist ein bisschen stressig, jedes Mal eine eindeutige ID zu schreiben, finden Sie nicht? Ich möchte dies den MySQL- Behörden überlassen . Und es gibt so einen Weg – AUTO INCREMENT . Wir müssen dies zum digitalen Feld hinzufügen, und wenn wir die Werte nicht explizit übergeben, erhöht MySQL selbst die ID im Vergleich zur vorherigen um eins. Daher sieht das Erstellen einer Tabelle wie folgt aus: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); Schauen wir uns das Tabellendiagramm an, um zu sehen, ob alles richtig gemacht wurde: $ DESC city; Wie Sie dem Tabellendiagramm entnehmen können, haben wir eine neue Beschreibung für das ID-Feld – auto_increment. Also haben wir alles richtig gemacht. Lassen Sie uns die Daten einer vollständig konfigurierten Tabelle überprüfen. Dazu erledigen wir den letzten Teil der Aufgabe – den Fremdschlüssel.

Fügen Sie Städten einen Fremdschlüssel hinzu

Für einen Fremdschlüssel gibt es diesen Befehl: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); Und schauen wir uns gleich an, was mit dem Tabellenschema nicht stimmt: Hat es sich im Laufe einer Stunde geändert? $DESC Stadt;

Bonusteil. Testen

Ich habe vergessen, es der Aufgabe hinzuzufügen – geben Sie die Daten ein, die im Screenshot des ersten Teils waren. Ich habe es vergessen, also mache ich es jetzt selbst. Und wer Interesse hat: Ihr könnt es selbst ohne mich machen und dann schauen wir nach ;) Es waren Charkow, Kiew, Minsk, Odessa, Woronesch, und wir werden auch Chisinau hinzufügen. Aber dieses Mal werden wir keine IDs übermitteln, wir werden sie überspringen: $ INSERT INTO city (name, country_id, population) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Woronesch', 2, 1058261), ('Kischinjow', 4, 695400); Wie Sie sehen, können Sie mit einem INSERT INTO-Befehl mehrere Einträge gleichzeitig vornehmen. Denken Sie daran, eine praktische Sache) Und sehen wir uns sofort an, was in der Tabelle steht: $ SELECT * FROM city; AUTO_INCREMENT – hat genau so funktioniert, wie wir es wollten. Die Ausweisdateien sind alle ausgefüllt, auch wenn wir sie nicht eingereicht haben. Ein Fremdschlüssel ist eine abhängige Sache. Um zu überprüfen, ob es korrekt funktioniert, können Sie versuchen, einen Fremdschlüssel zu schreiben, der in der Fremdtabelle nicht vorhanden ist. Nehmen wir an, wir entscheiden, dass id = 5 Kasachstan ist. Aber in Wirklichkeit steht es nicht in der Ländertabelle. Und um zu überprüfen, ob die Datenbank schwört, fügen Sie die Stadt hinzu: Astana: $ INSERT INTO city (name, country_id, population) VALUES ('Astana', 5, 1136156); Und wir bekommen natürlich den Fehler: Jetzt sorgt der Fremdschlüssel dafür, dass wir nicht versuchen, der Stadt ein Land zuzuordnen, das nicht in unserer Datenbank ist. Dieser Teil der Hausaufgabe kann als erledigt betrachtet werden – weiter geht es mit dem neuen :)

SELECT-Anweisung

Nun, es scheint doch nicht mehr alles so beängstigend zu sein, oder? Ich möchte noch einmal darauf hinweisen, dass für Java-Entwickler Kenntnisse der Datenbank ein Muss sind. Ohne eine Datenbank kommt man nirgendwo hin. Ja, ich möchte bereits mit dem Schreiben einer Bewerbung beginnen, ich stimme zu. Aber es ist notwendig. Wir werden also so weitermachen. Mit der SELECT-Anweisung rufen wir Daten aus der Datenbank ab. Das heißt, dies ist eine typische DML-Operation (haben Sie schon vergessen, was das ist?...))) Lesen Sie die Artikel VORHER noch einmal. Welche Vorteile bieten relationale Datenbanken? Sie verfügen über hervorragende Funktionen zum Aggregieren und Abrufen von Daten. Hierfür wird die SELECT-Anweisung verwendet. Es scheint, dass daran nichts Kompliziertes sein könnte, oder? Aber es stellt sich heraus, dass es noch viel zu verstehen gibt. Es ist wichtig, dass wir die Grundlagen verstehen, auf denen wir aufbauen können. Die einfachste Abfrage mit einer SELECT-Anweisung besteht darin, alle Daten aus einer Tabelle auszuwählen. Mir gefiel die Beschreibung aus dem Wiki, in der die Reihenfolge der Operatoren in einer SELECT-Abfrage genau angegeben ist, deshalb kopiere ich sie dreist hier:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Hier sehen Sie, dass Sie nicht zuerst den GROUP BY-Operator und dann den WHERE-Operator setzen können. Dies gilt es zu bedenken, damit es später nicht zu Ressentiments über Fehler kommt, bei denen nicht klar ist, woher sie kommen. $SELECT * FROM Stadt; Aber das Herauskratzen aller Daten macht uns offensichtlich keinen Spaß. Das ist genau das Gleiche, wenn wir Nägel mit einem Mikroskop einschlagen wollten [1] , [2] . Da die Datenbank Filter-, Sortier- und Aggregationsvorgänge viel schneller durchführt als Java-Code, ist es besser, diese Angelegenheit der Datenbank zu überlassen. Daher werden wir durch die Komplizierung der Aufgaben neue Funktionen eröffnen.

WHERE-Parameter

Um eine Auswahl zu filtern, wird das WHERE- Wort verwendet . Dies sollte wie folgt interpretiert werden: SELECT * FROM Tabellenname (wählen Sie alle Felder aus der Tabelle Tabellenname aus) WHERE talbe_row = 1 (wobei in den Datensätzen das Feld table_row gleich 1 ist). Es ist wichtig zu beachten, dass die Reihenfolge der Schlüsselwörter in der Abfrage wichtig ist. Sie können nicht WHERE a =1 FROM Tabellenname SELECT * schreiben. Für die russische Sprache ist das in Ordnung, und für manche mag es vielleicht nicht so schlimm erscheinen, aber für SQL ist das inakzeptabel. Wir schreiben die folgende Abfrage: $ SELECT * FROM city WHERE country_id = 1; Und wir haben ukrainische Städte ausgewählt. Nicht schlecht, oder? Was wäre, wenn wir nicht nur Ukrainisch, sondern auch Weißrussisch wollen? Zu diesem Zweck können wir die Sammlung von Werten auflisten, die das Feld annehmen kann: $SELECT * FROM city WHERE country_id IN(1, 3); Und wir haben bereits Städte aus zwei Ländern als Antwort erhalten. Was passiert, wenn mehrere Bedingungen gefiltert werden müssen? Nehmen wir an, wir wollen Städte mit mehr als zwei Millionen Einwohnern? Verwenden Sie dazu die Wörter OR und AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000; Großartig, aber was ist, wenn wir eine weitere Bedingung hinzufügen müssen – die Suche nach Namen mithilfe eines regulären Ausdrucks (ich werde reguläre Ausdrücke hier nicht beschreiben: Hier ist eine Person, die dies „kurz“ in 4 Teilen durchgeführt hat )? Wir erinnern uns zum Beispiel daran, wie man eine Stadt buchstabiert, aber nicht vollständig... Dazu können Sie das Schlüsselwort LIKE in den Filterausdruck einfügen : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR Name LIKE „%hark%“; Und auf diese Weise haben wir auch Charkow erreicht. Im Ergebnis können wir sagen, dass unsere Suche sehr gut verlaufen ist. Ich möchte aber nicht nach ID, sondern nach Bevölkerung sortieren, aber wie? Ja, ganz einfach...

ORDER BY-Parameter

Mit ORDER BY können wir die erhaltenen Datensätze nach einem bestimmten Feld sortieren. Es sortiert sowohl Zahlen als auch Zeichenfolgen. Erweitern wir die vorherige Abfrage, sortieren nach Bevölkerung und fügen ORDER BY Population hinzu: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE „%hark%“ ORDER BY population; Wie wir sehen können, erfolgte die Sortierung in natürlicher Reihenfolge, also in aufsteigender Reihenfolge. Was wäre, wenn wir das Gegenteil wollen? Dazu müssen Sie das Wort DESC hinzufügen: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE „%hark%“ ORDER BY population DESC; Jetzt basiert die Sortierung auf der Bevölkerungsreduzierung. Und das erledigt die Datenbank sehr schnell: Kein Collections.sort ist dort vergleichbar. Sortieren wir nun nach Zeile und nach Namen in umgekehrter Reihenfolge: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE „%hark%“ ORDER BY name DESC;

GROUP BY-Parameter

Wird zum Gruppieren von Datensätzen nach bestimmten Feldern verwendet. Dies ist normalerweise erforderlich, um Aggregatfunktionen zu verwenden... Was sind Aggregatfunktionen?)) Es ist sinnvoll, nach einigen Feldern zu gruppieren, wenn diese für verschiedene Datensätze gleich sind. Schauen wir uns anhand unseres Beispiels an, was das bedeutet. Nehmen wir an, Städte haben Fremdschlüssel – Länder-IDs. Daher ist die ID für Städte aus demselben Land gleich. Daher können Sie Datensätze nach ihnen nehmen und gruppieren: $ SELECT land_id, COUNT(*) FROM city GROUP BY land_id; Aber ohne Aggregationsfunktionen sieht es etwas dürftig aus, das müssen Sie zugeben. Schauen wir uns daher einige der häufigsten Funktionen an:

• COUNT – Anzahl der Datensätze, kann ohne Gruppierung verwendet werden, verwendet als COUNT(*) . Im Falle einer Gruppierung nach einem Feld - COUNT(groupped_field);
• MAX – findet den Maximalwert für ein bestimmtes Feld;
• MIN – findet den Mindestwert für ein bestimmtes Feld;
• SUM – findet die Summe für ein bestimmtes Feld;
• AVG – ermittelt den Durchschnittswert.

Im Allgemeinen können diese Funktionen ohne Gruppierung verwendet werden, nur dann wird nur ein Feld angezeigt. Probieren wir sie für unsere Stadtbevölkerung aus: $ SELECT COUNT(*) FROM city; Was sie verlangten, war, was sie bekamen. Nur die Anzahl der Datensätze. Manchmal ist das nützlich. Zum Beispiel, wenn wir die Anzahl der Artikel eines bestimmten Autors herausfinden müssen. Es besteht keine Notwendigkeit, sie aus der Datenbank herauszusuchen und zu zählen. Sie können einfach COUNT() verwenden. $ SELECT AVG(population) FROM city; $ SELECT MIN(population) FROM city; Und hier kommt die Gruppierung ins Spiel. Die Aufgabe besteht beispielsweise darin, die kleinste Stadt des Landes zu erobern. Wissen Sie schon, wie das geht? Probieren Sie es selbst aus und schauen Sie sich dann Folgendes an: $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id; Bisher sehen wir nur die ID des Landes, aber das macht nichts – beim nächsten Mal machen wir alles. Und so gibt es bereits ein Ergebnis, und wir haben bekommen, was wir wollten – die kleinste Stadt des Landes mit ID = 1. Die restlichen Funktionen bleiben die gleichen. Es ist wichtig zu beachten, dass das Herausstreichen aller Felder durch * bei Verwendung von Gruppierung und Aggregation nicht funktioniert! Denk darüber nach ;)

Hausaufgaben

Basierend auf den Ergebnissen der vorherigen Artikel ist klar, dass die Hausaufgaben gemacht werden, also machen wir weiter)) Ja, jeder, der die Hausaufgaben macht, wird weiterhin „+“ in die Kommentare setzen. Mir ist es wichtig, dass das Thema Hausaufgaben für Sie interessant ist, damit ich es auch in Zukunft weiter mache. Ja, ich lese Ihre Kommentare regelmäßig. Natürlich antworte ich seltener. Ich habe gesehen, dass sie darum gebeten haben, schwierigere SQL-Aufgaben zu geben. Bis wir die Zusammenhänge gelernt haben, wird es keine interessanten Probleme geben, daher wird es solche geben, die ich für weiteres Material benötige.

Aufgaben:

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 auch mal ausprobieren, wenn ich Zeit habe.
1. Installieren Sie MySQL Workbench, um über die Benutzeroberfläche mit der Datenbank zu arbeiten. Ich denke, wir haben die Arbeit an der Konsole schon genug 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.
2. Schreiben Sie Anfragen zum Erhalt unserer Daten:
   1. das kleinste/bevölkerungsreichste Land;
   2. durchschnittliche Einwohnerzahl im Land;
   3. durchschnittliche Einwohnerzahl in Ländern, deren Namen mit „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.

Abschluss

Heute haben wir ausführlich die Hausaufgaben aus der letzten Lektion besprochen. Darüber hinaus halte ich dies sowohl für diejenigen, die es nicht getan haben, als auch für diejenigen, die es getan haben, für wichtig. Für erstere ist dies eine Gelegenheit, die Antwort herauszufinden und für letztere, sie mit Ihrem Ergebnis zu vergleichen. Abonnieren Sie mein GitHub-Konto , um über Änderungen am Projekt auf dem Laufenden zu bleiben. Ich werde dort die gesamte Codebasis pflegen. Alles wird in dieser Organisation stattfinden . Als nächstes haben wir die SELECT-Anweisung besprochen. Er ist für uns der Wichtigste. Über ihn werden alle Datenanfragen abgewickelt, und wir müssen das verstehen. Das Wichtigste ist, sich die Reihenfolge zu merken, in der Parameter hinzugefügt werden (WHERE, ORDER BY, GROUP BY usw.). Ja, ich habe nicht alles erzählt, was möglich war, aber ich habe mir auch kein solches Ziel gesetzt. Ja, ich weiß, dass Sie bereits Lust darauf haben, eine Bewerbung zu schreiben. Seien Sie geduldig, das ist alles, was Sie brauchen. Sowohl für das Projekt als auch für Ihre berufliche Weiterentwicklung. Stellen Sie beim Warten sicher, dass Ihnen Git bereits bekannt ist. Ich werde es standardmäßig als bekanntes Tool verwenden. Vielen Dank an alle fürs Lesen. Im nächsten Artikel werden wir über Datenbankverbindungen und Joins sprechen. Da werden die coolen Aufgaben sein))

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