Nous analysons les bases de données et le langage SQL. (Partie 5 - connexions et jointures) - "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.

• Types de relations dans la base de données
  • Un à plusieurs
  • Un à un (un à un)
  • Plusieurs à plusieurs
• Connexions (jointures)
  • JOINTURE INTERNE
  • JOINT GAUCHE
  • REJOINDRE À DROITE
  • Sécuriser les jointures
• Devoirs

Bonjour à tous, futurs Seniors et Senioritas du logiciel. Comme je l'ai dit dans la partie précédente ( vérification des devoirs ), aujourd'hui il y aura du nouveau matériel. Pour ceux qui sont particulièrement impatients, j’ai déniché un devoir intéressant afin que ceux qui savent déjà tout et ceux qui ne savent pas mais veulent le rechercher sur Google puissent pratiquer et tester leurs compétences. Aujourd'hui, nous parlerons des types de connexions et de jointures.

Types de relations dans la base de données

Pour comprendre ce que sont les relations, vous devez vous rappeler ce qu'est une clé étrangère. Pour ceux qui ont oublié, bienvenue au début de la série .

Un à plusieurs

Rappelons notre exemple avec les pays et les villes. Il est clair qu’une ville doit avoir un pays. Comment relier un pays à une ville ? Il faut rattacher à chaque ville un identifiant unique (ID) du pays auquel elle appartient : nous l'avons déjà fait. C'est ce qu'on appelle l'un des types de connexions - un à plusieurs (il serait également bon de connaître la version anglaise - un à plusieurs). Pour paraphraser, on peut dire : plusieurs villes peuvent appartenir à un même pays. C'est ainsi que vous devez vous en souvenir : une relation un-à-plusieurs. Pour l’instant c’est clair, non ? Sinon, voici la première photo prise sur Internet : elle montre qu'il y a des clients et leurs commandes. Il est logique qu’un client puisse avoir plusieurs commandes. Il y a un-à-plusieurs :) Ou un autre exemple : Il y a trois tables : éditeur, auteur et livre. Tout éditeur qui ne veut pas faire faillite et veut réussir a plus d'un auteur, n'est-ce pas ? À son tour, chaque auteur peut avoir plus d’un livre – cela ne fait aucun doute non plus. Et cela signifie, encore une fois, le lien d’ un auteur avec plusieurs livres, d’un éditeur avec plusieurs auteurs . Il existe de nombreux autres exemples qui peuvent être donnés. La difficulté de perception réside peut-être au début dans l’apprentissage de la pensée abstraite : regarder de l’extérieur les tables et leur interaction.

Un à un (un à un)

Cela peut être considéré comme un cas particulier de communication un-à-plusieurs. Situation dans laquelle un enregistrement d'une table est lié à un seul enregistrement d'une autre table. Quels exemples peut-on trouver dans la vie ? Si nous excluons la polygamie, nous pouvons alors dire qu'il existe une relation individuelle entre mari et femme. Même si nous disons que la polygamie est autorisée, chaque femme ne peut avoir qu'un seul mari. On peut en dire autant des parents. Chaque personne ne peut avoir qu’un seul père biologique et qu’une seule mère biologique. Relation explicite un à un. Pendant que j'écrivais ceci, une pensée m'est venue : pourquoi alors diviser une relation un-à-un en deux enregistrements dans des tables différentes, s'ils ont déjà une relation un-à-un ? J'ai trouvé la réponse moi-même. Ces enregistrements peuvent également être liés à d’autres enregistrements par d’autres moyens. De quoi je parle ? Un autre exemple de connexions individuelles est celui entre le pays et le président. Est-il possible d'écrire toutes les données concernant le président dans le tableau « pays » ? Oui, vous pouvez, SQL ne dira pas un mot. Mais si vous pensez que le président est aussi une personne... Et il peut aussi avoir une femme (une autre relation un-à-un) et des enfants (une autre relation un-à-plusieurs) et alors il s'avère que ce sera nécessaire de relier le pays à l'épouse et aux enfants du président…. Cela semble fou, non ? :D Il peut y avoir de nombreux autres exemples pour cette connexion. De plus, dans une telle situation, vous pouvez ajouter une clé étrangère aux deux tables, contrairement à une relation un-à-plusieurs.

Plusieurs à plusieurs

Déjà sur la base du nom, vous pouvez deviner de quoi nous allons parler. Souvent dans la vie, et nous programmons nos vies, il y a des situations où les types de connexions ci-dessus ne suffisent pas à décrire les choses dont nous avons besoin. Nous avons déjà parlé des éditeurs, des livres et des auteurs. Il y a tellement de connexions ici... Chaque publication peut avoir plusieurs auteurs - une connexion un à plusieurs. En même temps, chaque auteur peut avoir plusieurs éditeurs (pourquoi pas, l'écrivain a été publié à un endroit, s'est disputé pour de l'argent, est allé dans une autre maison d'édition, par exemple). Et c’est encore une fois une relation un-à-plusieurs. Ou ceci : chaque auteur peut avoir plusieurs livres, mais chaque livre peut aussi avoir plusieurs auteurs. Encore une fois, une relation un-à-plusieurs entre auteur et livre, livre et auteur. De cet exemple, nous pouvons tirer une conclusion plus formalisée : Il était clair comment définir les types de connexion précédents dans SQL : nous transmettons simplement l'ID de celui-ci à ces enregistrements, qui sont nombreux, n'est-ce pas ? Un pays donne son identifiant comme clé étrangère à de nombreuses villes. Que faire des relations plusieurs-à-plusieurs ? Cette méthode ne convient pas. Nous devons ajouter une autre table qui relierait les deux tables. Par exemple, allons sur MySQL, créons une nouvelle base de données manytomany, créons deux tables, author et book, qui contiendront uniquement les noms et leurs identifiants : CREATE DATABASE manytomany ; UTILISER plusieurs à plusieurs ; CREATE TABLE author( id INT AUTO_INCREMENT, nom VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( id INT AUTO_INCREMENT, nom VARCHAR(100), PRIMARY KEY (id) ); Créons maintenant une troisième table qui contiendra deux clés étrangères de nos tables author et book, et ce lien sera unique. Autrement dit, il ne sera pas possible d'ajouter deux fois un enregistrement avec les mêmes clés : CREATE TABLE author_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author (id ), UNIQUE (book_id, author_id) ); Ici, nous avons utilisé plusieurs nouvelles fonctionnalités qui doivent être commentées séparément :

• NOT NULL signifie que le champ doit toujours être rempli, et si nous ne le faisons pas, SQL nous le dira ;
• UNIQUE indique qu'un champ ou un groupe de champs doit être unique dans la table. Il arrive souvent qu'en plus de l'identifiant unique, un champ supplémentaire doive être unique pour chaque enregistrement. Et UNIQUE est précisément responsable de cette affaire.

D'après ma pratique : lors du passage d'un ancien système à un nouveau, nous, en tant que développeurs, devons stocker les identifiants de l'ancien système pour pouvoir travailler avec lui et créer les nôtres. Pourquoi créer le vôtre et ne pas utiliser les anciens ? Ils ne sont peut-être pas assez uniques, ou cette approche de création d’identifiants peut ne plus être pertinente et limitée. À cette fin, nous avons rendu l'ancien nom d'identification également unique dans le tableau. Pour vérifier cela, vous devez ajouter des données. Ajouter un livre et un auteur : NSERT INTO book (name) VALUES ("book1"); INSERT INTO author (name) VALUES ("author1"); Nous savons déjà grâce aux articles précédents qu'ils auront les identifiants 1 et 1. Par conséquent, nous pouvons immédiatement ajouter un enregistrement à la troisième table : INSERT INTO author_x_books VALUES (1,1) ; Et tout ira bien jusqu'à ce que nous voulions répéter à nouveau la dernière commande : c'est-à-dire écrire à nouveau les mêmes identifiants : le résultat sera naturel - une erreur. Il y aura un doublon. L'entrée ne sera pas enregistrée. C'est ainsi qu'une connexion plusieurs-à-plusieurs sera créée... Tout cela est très cool et intéressant, mais une question logique se pose : comment obtenir ces informations ? Comment combiner les données de différentes tables et obtenir une seule réponse ? C'est ce dont nous parlerons dans la partie suivante))

Connexions (jointures)

Dans la partie précédente, je vous ai préparé à comprendre immédiatement ce que sont les jointures et où les utiliser. Parce que je suis profondément convaincu que dès que la compréhension viendra, tout deviendra immédiatement très simple, et tous les articles sur les jointures seront clairs comme les yeux d'un bébé :D En gros et en général, les jointures obtiennent le résultat de plusieurs tables au moyen d'un JOIN (join de l'anglais join). Et c'est tout...) Et pour rejoindre, vous devez préciser le champ par lequel les tables seront jointes. Le diable n'est pas aussi effrayant qu'il est peint, n'est-ce pas ?) Ensuite, nous parlerons simplement des types de jointures disponibles et de la manière de les utiliser. Il existe de nombreux types de jointures et nous ne les considérerons pas tous. Seulement ceux dont nous avons vraiment besoin. C’est pourquoi nous ne sommes pas intéressés par des jointures aussi exotiques que Cross et Natural. J'ai complètement oublié, nous devons nous rappeler une nuance supplémentaire : les tables et les champs peuvent avoir des alias - des pseudonymes. Ils sont commodément utilisés pour les jointures. Par exemple, vous pouvez faire ceci : SELECT * FROM table1; si la requête utilise souvent table1, alors vous pouvez lui donner un alias : SELECT* FROM table1 as t1; ou encore plus simple à écrire : SELECT * FROM table1 t1; et plus tard dans la requête, il sera possible d'utiliser t1 comme alias pour cette table.

JOINTURE INTERNE

La jointure la plus courante et la plus simple. Il dit que lorsque nous avons deux tables et un champ par lequel elles peuvent être jointes, tous les enregistrements dont les relations existent dans les deux tables seront sélectionnés. C'était difficile à dire d'une manière ou d'une autre. Regardons un exemple : ajoutons un enregistrement à notre base de données de villes. Une entrée pour les villes et une pour les pays : $ INSERT INTO country VALUES(5, "Ouzbékistan", 34036800); et $ INSERT INTO city (nom, population) VALUES("Tbilissi", 1171100); Nous avons ajouté un pays qui n'a pas de ville dans notre tableau, et une ville qui n'est associée à aucun pays dans notre tableau. Ainsi, INNER JOIN s'occupe de délivrer tous les enregistrements pour les connexions qui se trouvent dans deux tables. Voici à quoi ressemble la syntaxe générale lorsque l'on veut joindre deux tables table1 et table2 : SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; puis tous les enregistrements ayant une relation dans les deux tables seront renvoyés. Dans notre cas, lorsque nous souhaitons recevoir des informations sur les pays ainsi que sur les villes, cela ressemblera à ceci : $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Ici, bien que les noms soient les mêmes, on voit bien que les champs des villes viennent en premier, puis les champs des pays. Mais les deux entrées que nous avons ajoutées ci-dessus ne sont pas là. Parce que c'est exactement ainsi que fonctionne INNER JOIN.

JOINT GAUCHE

Il y a des cas, et assez souvent, où nous ne sommes pas satisfaits de la perte de champs de la table principale en raison du fait qu'il n'y a aucun enregistrement pour cela dans la table adjacente. C'est à cela que sert un LEFT JOIN. Si dans notre demande précédente nous spécifions LEFT au lieu de INNER, nous ajouterons une autre ville dans la réponse - Tbilissi : $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Il y a une nouvelle entrée sur Tbilissi et tout ce qui concerne le pays est nul . C’est souvent ainsi qu’on l’utilise.

REJOINDRE À DROITE

Ici, il y aura une différence avec LEFT JOIN en ce sens que tous les champs seront sélectionnés non pas à gauche, mais à droite dans la connexion. Autrement dit, pas les villes, mais tous les pays seront pris : $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Il est désormais clair que dans ce cas, il n’y aura pas de Tbilissi, mais nous aurons l’Ouzbékistan. Quelque chose comme ca…))

Sécuriser les jointures

Maintenant, je veux vous montrer une image typique que les juniors préparent avant un entretien pour les convaincre qu'ils comprennent l'essence des jointures : Ici, tout est montré sous forme d'ensembles, chaque cercle est un tableau. Et les endroits où il est peint sont les parties qui seront affichées dans SELECT. Regardons:

• INNER JOIN n'est que l'intersection d'ensembles, c'est-à-dire les enregistrements qui ont des connexions à deux tables - A et B ;
• LEFT JOIN regroupe tous les enregistrements de la table A, y compris tous les enregistrements de la table B qui ont une intersection (connexion) avec A ;
• RIGHT JOIN est exactement le contraire de LEFT JOIN - tous les enregistrements de la table B et les enregistrements de A qui ont une relation.

Après tout cela, cette image devrait être claire))

Devoirs

Cette fois, les tâches seront très intéressantes et tous ceux qui les résoudront avec succès peuvent être assurés qu'ils sont prêts à commencer à travailler du côté SQL ! Les tâches ne sont pas mâchées et ont été écrites pour les collégiens, donc ce ne sera pas facile et ennuyeux pour vous :) Je vous donnerai une semaine pour effectuer les tâches vous-même, puis je publierai un article séparé avec une analyse détaillée de la solution aux tâches que je vous ai confiées.

La tâche réelle :

1. Écrivez un script SQL pour créer la table 'Student' avec les champs suivants : id (clé primaire), nom, nom, e_mail (unique).
2. É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 ».
3. Écrivez un script SQL pour créer la table 'Teacher' avec les champs suivants : id (clé primaire), nom, nom, e_mail (unique), sujet.
4. Liez « Étudiant » et « Enseignant » avec une relation « Étudiant » plusieurs-à-plusieurs Enseignant ».
5. Sélectionnez « Étudiant » dont le nom de famille contient « oro », par exemple « Sid oro v », « V oro novsky ».
6. Sélectionnez dans le tableau 'É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

7. 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

8. Sélectionnez les « Étudiants » qui possèdent le plus grand nombre de « Livre » et d'« Enseignant » associé. Triez 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>

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

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

10. Sélectionnez 'Professeur' dont le numéro de 'Livre' pour l'ensemble de ses 'Elèves' 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

11. Imprimez tous les « nom de famille » 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

12. Ajoutez une colonne « taux » au tableau « Étudiant » existant, qui stockera le cours suivi actuellement par l'étudiant (valeur numérique de 1 à 6).
13. Cet élément 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.

Conclusion

La série sur la base de données a un peu traîné. Accepter. Cependant, nous avons parcouru un long chemin et nous en ressortons donc en connaissance de cause ! Merci à tous d'avoir lu, je vous rappelle que tous ceux qui souhaitent avancer et suivre le projet doivent créer un compte sur GitHub et s'abonner à mon compte :) Plus à venir - parlons de Maven et Docker. Merci à tous d'avoir lu. Je le répète encore une fois : celui qui marche maîtrisera la route ;)

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