Nous analysons les bases de données et le langage SQL. (Partie 6 - Vérification de la mission finale) - "Projet Java de A à Z"

Un article d'une série sur la création d'un projet Java (les liens vers d'autres documents se trouvent à la fin). Son objectif est d'analyser les technologies clés, le résultat est d'écrire un robot télégramme. Cette partie contient une analyse de la tâche finale sur la base de données. Salutations, chers lecteurs. Aujourd'hui, nous analyserons la tâche du dernier article sur la base de données. C'est intéressant car il est destiné aux entretiens au niveau intermédiaire. Autrement dit, après cette tâche, vous pouvez déjà passer un entretien et vous réussirez au moins une partie de ce qui concerne les bases de données relationnelles. Je sais à quel point cet article peut être nécessaire et je mettrai donc toute mon expérience pour le rendre utile et intéressant. Et si vous ne vous endormez pas au milieu de l’article, cela voudra dire que j’ai atteint mon objectif. Je ne répéterai pas l'intégralité de la tâche : je la citerai avant de terminer chaque tâche, en la soulignant en italique. J'espère que tous ceux qui liront cet article exécuteront toutes les requêtes dans leur base de données et obtiendront la même chose. Cela apportera un maximum d’avantages à l’entreprise. Et je serai un peu plus heureux à l'idée d'avoir aidé quelqu'un dans notre tâche difficile)

Exercice 1

Écrivez un script SQL pour créer la table 'Student' avec les champs suivants : id (clé primaire), nom, nom, e_mail (unique). Nous l'avons déjà fait, il ne devrait donc y avoir aucun problème. Dans le script, vous devez spécifier la clé primaire et un champ unique différent de la clé primaire. Tout d'abord, créons une nouvelle base de données pour cette tâche : $ CREATE DATABASE final_task; Et utilisons cette base de données : $ USE final_task; Une fois l'environnement configuré et prêt à exécuter le travail, nous pouvons écrire le script suivant : $ CREATE TABLE étudiant ( id INT AUTO_INCREMENT, nom VARCHAR(40), nom de famille VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( identifiant), UNIQUE (e-mail ) ); Jusqu’à présent, il n’y a rien de nouveau par rapport à ce que nous avons déjà vécu. Tout commentaire est inutile, passons à autre chose.

Tâche 2-3

Écrivez un script SQL pour créer la table 'Book' avec les champs suivants : id, title (id + title = clé primaire). Liez « Étudiant » et « Livre » avec une relation un-à-plusieurs « Livre » « Étudiant ». Combinons deux tâches en une seule pour la rendre plus rapide et plus pratique. J'ai déjà expliqué comment ajouter une clé étrangère distincte dans des articles précédents. Pour ajouter, nous devons nous rappeler comment nous établissons des liens et par quoi. L'article précédent vous aidera, puis voici le script : $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id ) ); De cette manière simple, nous avons ajouté une clé composite pour notre table PRIMARY KEY (id, title) , maintenant la clé sera exactement la paire. Cela signifie qu'il peut y avoir plusieurs valeurs de champ d'identification identiques dans la table. Et exactement pareil pour le titre.

Tâche 4

Écrivez un script SQL pour créer la table 'Teacher' avec les champs suivants : id (clé primaire), nom, nom, e_mail (unique), sujet. Nous continuons à préparer notre base de données pour les requêtes, créons une table professeur : $ CREATE TABLE professeur( id INT AUTO_INCREMENT, nom VARCHAR(30), nom de famille VARCHAR(30), email VARCHAR(100), sujet VARCHAR(40), PRIMARY KEY ( identifiant), UNIQUE (email) ); Jusqu'à présent, ce n'est pas difficile, n'est-ce pas ? Trois tâches sont déjà terminées !

Tâche 5

Liez « Étudiant » et « Enseignant » avec une relation « Étudiant » plusieurs-à-plusieurs Enseignant ». Maintenant, c'est plus intéressant ! Nous venons d'en parler la dernière fois. Permettez-moi de vous rappeler ce qu'il faut pour y parvenir : vous devez créer une table intermédiaire qui stockerait les binômes élèves-professeurs. Avec son aide, il sera possible de créer une relation plusieurs-à-plusieurs. Créons donc une table étudiants_x_techers . L'approche de dénomination est ouverte et pourrait également être : student_teacher . $ CREATE TABLE étudiants_x_teachers ( student_id INT NON NULL, professeur_id INT NON NULL, PRIMARY KEY (student_id, professeur_id), FOREIGN KEY (student_id) RÉFÉRENCES étudiant (id), FOREIGN KEY (teacher_id) RÉFÉRENCES professeur (id) ); Comme vous pouvez le constater, tout est fait de manière claire et cohérente. Nous avons une clé composite pour deux clés étrangères : student_id et professor_id. Pourquoi aussi une clé étrangère ? Afin que nous soyons sûrs que des enregistrements dans les tables des élèves et des enseignants existent pour les paires enregistrées.

Tâche 6

Sélectionnez « Étudiant » dont le nom de famille contient « oro », par exemple « Sidorov », « Voronovsky ». Pour que cela soit intéressant et visuel pour nous, je suggère d'ajouter d'abord plusieurs étudiants, afin que certains d'entre eux conviennent à cette demande, et d'autres non. Par conséquent, notons ceux qui devraient être inclus à la suite de la demande : $ INSERT INTO student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSÉRER DANS l'étudiant (nom, nom, email) VALEURS ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Et ceux qui ne devraient pas entrer : $ INSERT INTO student (nom, nom, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO étudiant (nom, nom, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Vérifions le résultat, regardons la liste des données dans la table des étudiants : $ SELECT * FROM student; et nous obtenons : il y a quatre enregistrements au total, deux d'entre eux devraient correspondre et deux ne devraient pas l'être. Après avoir préparé toutes les données pour la requête, nous pouvons faire une requête pour la tâche elle-même : $ SELECT * FROM student WHERE last_name LIKE '%oro%'; En conséquence, Ivan et Nikolai ont parcouru la liste.

Tâche 7

La tâche suivante, nous lisons : Sélectionnez dans la table 'Étudiant' tous les noms de famille ("last_name") et le nombre de leurs répétitions. Considérez qu'il y a des homonymes dans la base de données. Trier par quantité par ordre décroissant. Ça devrait ressembler à ça:

nom de famille	quantité
Petrov	15
Ivanov	12
Sidorov	3

Pour plus de clarté, vous devez ajouter plus de données. Sans plus attendre, ajoutons les Petrov, Ivanov et Sidorov, qui ne connaissent pas leur parenté ;) Je n'inventerai pas d'adresse email, je vais juste l'exclure des nouvelles entrées. Exécutons la commande suivante 12 fois : $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Ajoutons 15 Petrovs : $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); Et deux Sidorov (nous en avons déjà un))) : $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); Les données sont maintenant prêtes. Pour obtenir de telles données, vous devez effectuer un regroupement ; pour effectuer un regroupement, vous devez utiliser l'opérateur Group By, et vous devez le faire via le champ last_name. Vous pouvez également remarquer que le nombre de répétitions est désigné comme quantité , et ici vous devez également vous rappeler comment créer des alias en SQL : $ SELECT nom_nom, COUNT(*) comme quantité FROM student GROUP BY nom_nom ORDER BY COUNT(*) DESC ; Alors j'en ai exagéré avec les Petrov - il s'est avéré que c'était 16))

Tâche 8

Condition : Sélectionnez les 3 noms les plus répétés dans « Étudiant ». Trier par quantité par ordre décroissant. Ça devrait ressembler à ça:

nom	quantité
Alexandre	27
Sergueï	dix
Pierre	7

Oh, pour cela, nous avons déjà des Ivans, des Peters et des Sidors. Il n’est donc pas nécessaire de les ajouter. Nous savons déjà trier. La seule chose dont nous n'avons pas parlé aujourd'hui est la manière de sélectionner un certain nombre d'enregistrements. Cela est déjà apparu dans des solutions précédentes aux problèmes de bases de données. Pour ceux qui ne l'ont pas lu, lisez-le. Pour le reste, allons droit au but : $ SELECT nom, COUNT(*) comme quantité FROM student GROUP BY nom ORDER BY COUNT(*) DESC LIMIT 3; Comme le montre la requête, si vous connaissez l'ordre des opérateurs dans une requête SELECT, l'exécution d'une telle requête ne posera aucun problème. Et cette tâche nous appartient toujours. Et les connaissances présentées précédemment sont tout à fait suffisantes pour résoudre ce problème.

Tâche 9

Condition de la tâche : Sélectionnez l'« Étudiant » qui possède le plus grand nombre de « Livre » et d'« Enseignant » associé. Trier par quantité par ordre décroissant. Ça devrait ressembler à ça:

Nom de famille de l'enseignant	Nom de l'élève	Quantité du livre
Petrov	Sidorov	7
Ivanov	Forgeron	5
Petrov	Kankava	2

Alors, cette tâche est clairement plus difficile que la précédente, non ? Pas étonnant : ça sent la jointure... et plus d'un) Tout d'abord, nous devons comprendre quoi faire... Il est clair que la quantité de Book nécessite un regroupement. Mais quoi? Et pourquoi devrions-nous les regrouper ? La requête implique trois tables, un regroupement et un tri. À en juger par le fait que les enregistrements dans lesquels il n'y a pas de livres ne sont pas affichés, cela signifie que vous devez prendre INNER JOIN. Nous ferons également une demande de LEFT JOIN afin qu'il n'y ait aucun problème avec cela. Et il existe plusieurs options. La première chose que nous faisons est de joindre trois tables en un seul enregistrement. Ensuite, nous regroupons par élève et y ajoutons le nom de l’enseignant. Que choisirons-nous ? Le nom de l'enseignant, de l'élève et le nombre de livres. Ajoutons des données pour la requête :

• trois enseignants ;
• dix livres ;
• connecter deux étudiants avec trois enseignants.

Trois professeurs

$ INSERT INTO professeur(nom_famille) VALUES ('Matvienko'); $ INSERT INTO professor(last_name) VALUES (« Shevchenko ») ; $ INSERT INTO professeur(nom_famille) VALUES ("Vasilenko");

10 livres

Je prendrai les cartes d'identité des élèves de 1ère et 2ème. Je leur joindrai des livres. Puisque AUTO_INCREMENT n'a pas été défini, afin de ne pas écrire un nouvel identifiant à chaque fois, vous devez procéder comme suit : $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Ensuite, ajoutez des livres pour le premier élève : $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO livre (titre, étudiant_id) VALUES('book2', 1); $ INSERT INTO livre (titre, étudiant_id) VALUES('book3', 1); $ INSERT INTO livre (titre, étudiant_id) VALUES('book4', 1); $ INSERT INTO livre (titre, étudiant_id) VALUES('book5', 1); $ INSERT INTO livre (titre, étudiant_id) VALUES('book6', 1); Et des livres pour le deuxième élève : $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO livre (titre, étudiant_id) VALUES('book8', 2); $ INSERT INTO livre (titre, étudiant_id) VALUES('book9', 2); $ INSERT INTO livre (titre, étudiant_id) VALUES('book10', 2);

Connexions enseignant-élève

Pour ce faire, ajoutez Students_x_teachers au tableau : $ INSERT INTO Students_x_teachers VALUES (1,1) ; $INSERT INTO étudiants_x_teachers VALUES(1,2); $INSERT INTO étudiants_x_teachers VALUES(2,3);

Mettons en œuvre la demande

Nous effectuons la première étape - nous lions trois tables en un seul enregistrement : $ SELECT * FROM professeur tch INNER JOIN étudiants_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 ; Bien sûr, nous n’avons pas encore d’enregistrements, cependant, nous pouvons voir que nous avons réussi à connecter trois tables. Maintenant, nous ajoutons le regroupement de livres, le tri et les champs dont nous avons besoin : $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM student st INNER JOIN book b ON st.id = b.student_id INNER REJOIGNEZ étudiants_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN professeur tch ON tch.id = st_x_tch.teacher_id GROUPE PAR st.id ORDER PAR livres DESC ; Mais nous obtenons une erreur dans SQL et la réponse suivante : Code d'erreur : 1055. L'expression n°1 de la liste SELECT n'est pas dans la clause GROUP BY et contient la colonne non agrégée 'final_task.tch.last_name' qui ne dépend pas fonctionnellement des colonnes de GROUP BY. clause Cela ne fonctionne pas avec ces éléments car il existe une relation plusieurs-à-plusieurs entre l’enseignant et l’élève. Et c’est vrai : on ne peut pas avoir un seul professeur par élève. Alors allons dans l'autre sens. Utilisons quelque chose appelé View SQL. Quelle est l'idée : nous créons une vue séparée, qui est une nouvelle table, déjà avec le regroupement dont nous avons besoin. Et à ce tableau nous ajouterons les noms nécessaires des enseignants. Mais nous tenons compte du fait qu'il peut y avoir plus d'un enseignant, les entrées seront donc répétées. Créer une vue : $ 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 ; Ensuite, nous travaillons avec cette vue comme un tableau simple comportant trois champs : nom_étudiant, identifiant_étudiant et nombre de livres. Selon l'identifiant de l'élève, nous pouvons également ajouter un enseignant via deux jointures : $ 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 professeur tch ON tch.id = stch.teacher_id; Et maintenant le résultat sera : Hein ! C'est une demande, n'est-ce pas ?) Cela s'est avéré comme prévu : un élève avec l'identifiant=1 a six livres et deux professeurs, et un élève avec l'identifiant=2 a quatre livres et un enseignant.

Tâche 10

Condition : Sélectionnez le « Professeur » qui possède le plus grand nombre de « Livres » parmi tous ses « Étudiants ». Trier par quantité par ordre décroissant. Ça devrait ressembler à ça:

Nom de famille de l'enseignant	Quantité du livre
Petrov	9
Ivanov	5

Ici, nous pouvons utiliser une requête toute faite de la tâche précédente. Que devons-nous changer à ce sujet ? Nous disposons déjà de ces données, il nous suffit d’ajouter un autre regroupement et de supprimer le nom de l’élève des données de sortie. Mais d’abord, ajoutons un élève supplémentaire au professeur pour rendre le résultat plus intéressant. Pour ce faire, nous écrivons : $ INSERT INTO Students_x_teachers VALUES (2, 1) ; Et la requête elle-même : $ 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 professeur tch ON tch.id = stch .prof_id GROUPE PAR tch.id; Le résultat, nous obtenons : ce professeur Vasilenko a 10 livres, et Shevchenko en a 6...)

Tâche 11

Condition : Sélectionnez un « Professeur » dont le nombre de « Livres » pour l'ensemble de ses « Étudiants » est compris entre 7 et 11. Trier par quantité par ordre décroissant. Ça devrait ressembler à ça:

Nom de famille de l'enseignant	Quantité du livre
Petrov	onze
Sidorov	9
Ivanov	7

C'est là que nous utiliserons HAVING. Nous avons parlé de lui. La demande sera exactement la même que précédemment, il vous suffit d'ajouter la condition que le nombre de livres soit dans une certaine fourchette. Et comme je l'ai dit dans les articles précédents, lorsque nous devons faire du filtrage lors du regroupement et/ou sur les fonctions d'agrégation, nous devons utiliser HAVING : $ SELECT tch.last_name comme 'Professeur', SUM(sbw.books) comme 'Livres' de studentbook sbw INNER JOIN étudiants_x_teachers stch ON sbw.id = stch.student_id INNER JOIN professeur tch ON tch.id = stch.teacher_id GROUPE PAR tch.id AYANT SOMME (sbw.books) > 6 ET SOMME (sbw.books) < 12 ; J'ai mis en évidence la partie que j'ai ajoutée. Et, en fait, le résultat attendu : seul Vasilenko a réussi ce tour))

Tâche 12

Condition : Imprimez tous les "nom" et "nom" de tous les "Enseignant" et "Étudiant" avec le champ "type" (étudiant ou enseignant). Trier par ordre alphabétique par « nom de famille ». Ça devrait ressembler à ça:

nom de famille	taper
Ivanov	étudiant
Kankava	professeur
Forgeron	étudiant
Sidorov	professeur
Petrov	professeur

Autrement dit, nous devons combiner deux sorties, et c'est exactement à cela que sert UNION. En d'autres termes, nous prendrons les enregistrements des étudiants et des enseignants et les imprimerons ensemble : $ SELECT nom_de_famille, 'enseignant' comme type de l'enseignant UNION ALL select nom_de_famille, 'étudiant' comme type de l'élève ORDER BY nom_de_famille; Et il y aura à la fois des enseignants et des étudiants. Il semblerait que tout soit simple, mais c'est à ce moment-là qu'on regarde déjà le résultat. Vous devez donc deviner deux choses.

Tâche 13

Condition : Ajouter une colonne « taux » au tableau « Étudiant » existant, qui stockera le cours suivi actuellement par l'étudiant (valeur numérique de 1 à 6). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (taux > 0 ET taux < 7) ; Ici, nous ajoutons un champ via ALTER TABLE et CHECK pour définir la limite de ce champ de 1 à 6.

Tâche 14

Condition : Cet article n’est pas obligatoire, mais sera un plus. Écrivez une fonction qui parcourra tous les « Livres » et affichera tous les « titres » séparés par des virgules. Ici, il vous suffit de renvoyer une ligne à la suite de la requête, qui contiendra tous les titres des livres. Là encore, j'ai dû chercher sur Google. Il existe une telle fonction - GROUP_CONCAT , avec laquelle cela se fait très simplement : $ SELECT GROUP_CONCAT(title) from book; Et c'est tout...)) Les 14 tâches sont prêtes.

conclusions

Uuhhh... Ce n'était pas facile. C'etait intéressant. Les tâches en valaient la peine, j'en suis plus que sûr. Pendant que nous effectuions ces tâches, nous avons passé en revue de nombreuses choses qui n'étaient pas connues auparavant :

• VUE SQL
• GROUP_CONCAT
• SYNDICAT

et ainsi de suite. Merci à tous ceux qui ont eu la force de lire et de répéter ce que j'ai fait. Qui sait mieux faire des demandes - écrivez dans les commentaires, je les lirai certainement)

Une liste de tous les matériaux de la série se trouve au début de cet article.