Introduction aux jointures de base de données avec MariaDB et MySQL Join Exemples

Objectif

Apprenez à connaître les différents types de jointures et comment les utiliser en travaillant avec les bases de données MySQL ou MARIADB

Exigences

• Aucune exigence particulière

Conventions

• # - exige que la commande Linux soit exécutée avec des privilèges racine
  directement en tant qu'utilisateur racine ou en utilisant Sudo commande
• $ - Étant donné la commande Linux à exécuter en tant qu'utilisateur non privilégié régulier

Introduction

Dans un système de base de données relationnel, les données sont organisées en tableaux, composées par des lignes et des colonnes. Chaque ligne est une instance de l'entité représentée par le tableau, avec les colonnes utilisées comme propriétés. Les relations entre les tableaux sont établies par l'utilisation de clés étrangères et la déclaration avec laquelle nous pouvons effectuer des requêtes qui s'étendent sur plusieurs tables, elle s'appelle un rejoindre. Dans ce tutoriel, nous verrons les différents types de jointures disponibles lorsque vous utilisez MySQL ou MariAdb.

La base de données «film_store»

Qu'est-ce que nous allons faire dans ce tutoriel, c'est reproduire certains cas de béton dans lesquels les jointures peuvent nous aider à accomplir ce que nous voulons.

La première chose à faire est de créer une base de données de test. Disons que nous possédons une boutique de cinéma et que nous devons garder une trace des titres dont nous disposons: nous allons créer une base de données «film_store» et une table pour héberger des informations sur les réalisateurs de films:

MariaDB [(Aucun)]> Créer une base de données Movie_Store; MariaDB [(Aucun)]> Utilisez Movie_Store; MARIADB [film_store]> Créer un réalisateur de table (-> id smallInt (1) non signé pas null auto_increment, -> name varchar (35) pas null, -> date de naissance non nul, -> clé primaire (id)); 

Voici la représentation visuelle du tableau que nous venons de créer:

Mariadb [films]> Décrivez le réalisateur; + ----------- + ---------------------- + ------ + ----- + - -------- + ---------------- + | Champ | Type | NULL | Clé | Par défaut | Extra | + ----------- + ---------------------- + ------ + ----- + - -------- + ---------------- + | ID | smallInt (1) Unsigned | Non | Pri | NULL | auto_increment | | Nom | varchar (35) | Non | | NULL | | | Date de naissance | Date | Non | | NULL | | +-----------+----------------------+------+-----+---------+----------------+ 

Nous avons d'abord créé la base de données film_store, que nous l'avons «entré» en utilisant le UTILISER déclaration, et a finalement créé le tableau du réalisateur. Comme nous l'avons dit précédemment, chaque ligne d'un tableau représente une «instance» de l'entité représentée par le tableau lui-même, dans ce cas un réalisateur.

Chaque réalisateur a des propriétés qui sont représentées par les colonnes de table, donc par exemple, chaque réalisateur a un nom et un anniversaire. Chaque ligne a un identifiant unique, qui est la valeur de la colonne qui est la clé primaire de la table.

Dans cet exemple, la clé principale est également ce qu'on appelle un Clé de substitution. Ce type de clé est un identifiant «artificiel», dans le sens où il n'est pas lié à la nature de l'entité (un répertoire dans ce cas): il n'a pas de sens sémantique, et il est généré et utilisé par le système pour son propre travail interne. La clé est générée automatiquement, et comme il a le INCRÉMENTATION AUTOMATIQUE Propriété, il est inséré progressivement chaque fois que nous créons une nouvelle ligne, nous n'avons donc pas besoin de l'insérer explicitement:

Mariadb [film_store]> Insérer dans le réalisateur ('name', 'naissance') VALEURS -> ('George Lucas', '1944-05-14'), -> ('George Romero', '1940-02-04' ), -> ('John McTiernan', '1951-01-08'), -> ('Rian Johnson', '1973-12-17'); 

Notre table contient désormais quatre réalisateurs:

+----+----------------+------------+ | ID | Nom | Date de naissance | + ---- + ---------------- + ------------ + | 1 | George Lucas | 1944-05-14 | | 2 | George Romero | 1940-02-04 | | 3 | John McTiernan | 1951-01-08 | | 4 | Rian Johnson | 1973-12-17 | +----+----------------+------------+ 

Chacun de ces réalisateurs a un ou plusieurs films qui lui sont associés: comment pourrions-nous les représenter ? Nous ne pouvons pas ajouter d'informations sur les films dans ce tableau: cela signifierait avoir beaucoup de données répétées: chaque fois que nous ajoutons un film, nous répéterons ses informations de réalisateur, et ce serait horrible pour le moins. Nous devons créer une table dédiée pour héberger des informations sur les films, et en même temps, nous devons être en mesure de créer une référence entre elle et son réalisateur. C'est ce que clés étrangères sont pour:

MARIADB [film_store]> Créer un titre de table (-> id smallInt (1) non signé pas null auto_increment, -> name varchar (35) pas null, -> release_date date non nul, -> genre varchar (10) pas null, -> Director_id smallInt (1) Unsigned Not Null, -> Clé primaire (ID), -> Foreign Key (Director_id) Références Directeur (ID)); 

Nous avons créé le tableau comme avant, définissant une clé primaire et ajoutant une contrainte de clé étrangère. C'est ainsi que nous permettons une relation entre deux tableaux: en gros, nous imposons que pour une ligne à insérer, la valeur de la colonne Director_ID doit correspondre à une valeur dans la colonne ID du tableau Director (ce qui est unique, car c'est le Table Clé primaire). En d'autres termes, chaque titre doit avoir une référence à un directeur existant dans notre base de données, sinon une erreur sera déclenchée: cela garantit la cohérence.

Insérons quelques titres dans notre table:

MariaDB [film_store]> Insérer dans le titre ('name', 'release_date', 'genre', 'metting_id') valeurs -> ('Night of the Living Dead', '1968-10-01', 'Horror', 2 ), -> («Revenge of the Sith», «2005-05-19», «Space Opera», 1), -> («Die Hard», «1988-07-15», «Action», 3) ; 

C'est ça, nous avons un titre. Nous avons d'abord inséré ce chef-d'œuvre d'un film qui est «Night of the Living Dead», réalisé par George Romero: observer que le 2 Dans la colonne Director_id correspond à l'ID de George Romero dans la table du réalisateur.

En utilisant le même principe, nous avons inséré un film de George Lucas (ID 1 dans la table du réalisateur), «Revenge of the Sith» et «Die Hard», un célèbre film d'action réalisé par John McTiernan (ID 3 dans le réalisateur Table). Pour le moment, nous n'avons pas de films de Rian Johnson: il y a une raison à cela (à part le fait que j'ai été déçu par le dernier Jedi), et nous le verrons plus tard. Maintenant que nous avons configuré une structure de base de données très basique, nous pouvons commencer à parler se joindre à.

Combien de types de joints?

Différents noms sont utilisés pour référencer le même type de jointures, mais en gros, nous avons intérieur et extérieur se joindre à. Les premiers sont également appelés jointures croisées ou simplement se joindre à (Ce sont des synonymes à MySQL - Mariadb). Cette dernière catégorie comprend gauche et droite se joindre à.

Jointure intérieure

Une jointure intérieure, faisons correspondre les lignes dans une table avec des lignes dans une autre. Cette association peut être basée sur la relation entre les deux tables ou peut être faite quelle que soit celle-ci: dans ce cas, toutes les lignes d'une table seront jointes à toutes les lignes de l'autre, produisant ce qu'elle s'appelle un produit cartésien. Cela n'a pas beaucoup de sens dans notre exemple, mais permet de le démontrer:

MARIADB [FIGMENT_STORE]> SELECT * From Director Join Title; + ---- + ---------------- + ------------ + ---- + --------- ----------------- + -------------- + ------------ + ---- --------- + | ID | Nom | Date de naissance | ID | Nom | release_date | genre | Director_id | + ---- + ---------------- + ------------ + ---- + --------- ----------------- + -------------- + ------------ + ---- --------- + | 1 | George Lucas | 1944-05-14 | 1 | Nuit des morts vivants | 1968-10-01 | Horreur | 2 | | 1 | George Lucas | 1944-05-14 | 2 | Vengeance des Sith | 2005-05-19 | Space Oper | 1 | | 1 | George Lucas | 1944-05-14 | 3 | Mourir dur | 1988-07-15 | Action | 3 | | 2 | George Romero | 1940-02-04 | 1 | Nuit des morts vivants | 1968-10-01 | Horreur | 2 | | 2 | George Romero | 1940-02-04 | 2 | Vengeance des Sith | 2005-05-19 | Space Oper | 1 | | 2 | George Romero | 1940-02-04 | 3 | Mourir dur | 1988-07-15 | Action | 3 | | 3 | John McTiernan | 1951-01-08 | 1 | Nuit des morts vivants | 1968-10-01 | Horreur | 2 | | 3 | John McTiernan | 1951-01-08 | 2 | Vengeance des Sith | 2005-05-19 | Space Oper | 1 | | 3 | John McTiernan | 1951-01-08 | 3 | Mourir dur | 1988-07-15 | Action | 3 | | 4 | Rian Johnson | 1973-12-17 | 1 | Nuit des morts vivants | 1968-10-01 | Horreur | 2 | | 4 | Rian Johnson | 1973-12-17 | 2 | Vengeance des Sith | 2005-05-19 | Space Oper | 1 | | 4 | Rian Johnson | 1973-12-17 | 3 | Mourir dur | 1988-07-15 | Action | 3 | +----+----------------+------------+----+--------------------------+--------------+------------+-------------+ 

Comme vous pouvez le voir, chaque ligne d'une table a été combinée avec chaque rangée de l'autre, produisant 12 lignes.

Voyons maintenant un cas d'utilisation différent pour une jointure. Disons que nous voulons inspecter notre base de données pour vérifier tous les films réalisés par George Lucas que nous avons en magasin. Pour accomplir cette tâche, nous devons restreindre la jonction avec un SUR clause, afin qu'il soit basé sur la relation entre les titres et leur directeur:

MARIADB [film_store]> Sélectionner le réalisateur.nom, titre.Nom comme film_title du réalisateur -> Rejoignez le titre sur le réalisateur.id = titre.Director_id -> où le directeur.name = "George Lucas" 

Voici le résultat de la requête ci-dessus:

+--------------+---------------------+ | Nom | film_title | + -------------- + --------------------- + | George Lucas | Vengeance des Sith | +--------------+---------------------+ 

En utilisant une jointure restreinte, basée sur la relation entre les deux tables, nous avons découvert que nous n'avons qu'un seul titre de George Lucas en magasin: Revenge of the Sith. Non seulement nous avons limité la jointure sur la base de la relation existant entre les deux tables, mais nous avons encore limité la requête aux films réalisés par Lucas, en utilisant le OÙ déclaration. Si nous l'avions omis, la requête aurait produit une table avec toute la correspondance du réalisateur existant:

+----------------+--------------------------+ | Nom | film_title | + ---------------- + -------------------------- + | George Lucas | Vengeance des Sith | | George Romero | Nuit des morts vivants | | John McTiernan | Mourir dur | +----------------+--------------------------+ 

Notez que Rian Johnson n'est pas inclus dans la requête. Pourquoi cela se produit? Ceci est une caractéristique des jointures intérieures: ils ne montrent que des lignes où un match existe dans les deux tables. Étant donné qu'aucune correspondance pour Rian Johnson n'existe dans la table destinées, nous n'avons aucun résultat pour ce réalisateur.

Jointures extérieures

L'autre type de jointures que nous avons sont les jointures extérieures. Cette catégorie est elle-même divisée en se joint à gauche et à droite se joint. Quelle est la différence avec les jointures intérieures que nous avons vues ci-dessus ? Contrairement à ce qui se passe avec une jointure intérieure, une jointure extérieure montre des correspondances même lorsqu'une correspondance n'existe pas dans les deux tables. Quand ce sera le cas, il affichera une valeur nulle dans la ou les colonnes demandées du tableau où le match n'existe pas.Cela peut être utile, par exemple, si nous voulons savoir s'il y a des réalisateurs associés à aucun film. Dans notre cas, nous savons déjà que c'est le cas, mais le vérifions à l'aide d'une jointure de gauche:

MARIADB [film_store]> Sélectionner le réalisateur.nom, titre.Nom as Movie_title -> Depuis le réalisateur gauche jointe le titre sur le titre.Director_id = directeur.identifiant 

Le résultat de la requête:

+----------------+--------------------------+ | Nom | film_title | + ---------------- + -------------------------- + | George Romero | Nuit des morts vivants | | George Lucas | Vengeance des Sith | | John McTiernan | Mourir dur | | Rian Johnson | NULL | +----------------+--------------------------+ 

Le seul réalisateur qui n'a pas de films dans notre magasin est Rian Johnson. Lors de l'utilisation d'un jointure extérieure, l'ordre dans lequel nous spécifions les tables est importante. Par exemple, en utilisant un JOINT GAUCHE, Comme nous l'avons fait ci-dessus, lorsque la ligne de la table de gauche (dans ce réalisateur de cas) n'a pas de correspondance dans les rangées de la table droite (titre), un NUL La valeur est spécifiée dans chaque colonne demandée de ce dernier; Lorsqu'une correspondance est trouvée, sa valeur s'affiche à la place comme cela se produit avec une jointure intérieure.

UN À droite fonctionne tout de même, la seule différence est que le rôle des tables est inversé. Dans la droite, jointurez toute la ligne de la table droite qui n'a pas de correspondance dans la table de gauche est marquée d'une valeur nul.

Cette propriété des jointures externes est très utile, mais il y a des cas dans lesquels une petite confusion peut survenir, en particulier lorsqu'un tableau a une valeur nulle autorisée dans certaines de ses colonnes.

Tutoriels Linux connexes:

• Installez MySQL sur Ubuntu 20.04 LTS Linux
• Choses à installer sur Ubuntu 20.04
• Comment installer MySQL sur Almalinux
• Comment changer le mot de passe de l'utilisateur de MariaDB
• Introduction aux moteurs de stockage MySQL
• Applications d'interface utilisateur graphique (GUI) pour la gestion…
• Ubuntu 20.04 WordPress avec installation Apache
• Une introduction à l'automatisation Linux, des outils et des techniques
• Installation de WordPress OpenLitesPeed
• Ampache Raspberry Pi Installation