Share
Aujourd'hui, nous continuons notre voyage dans le monde du SQL et des systèmes de bases de données relationnelles. Dans cette troisième partie de la série, nous allons apprendre à travailler avec plusieurs tables ayant des relations entre elles. Tout d'abord, nous allons passer en revue certains concepts de base, puis commencer à travailler avec les requêtes JOIN dans SQL.
Vous pouvez également voir les bases de données SQL en action en extrayant les scripts, les applications et les modules complémentaires SQL sur Envato Market..
Lors de la création d'une base de données, le bon sens veut que nous utilisions des tables distinctes pour différents types d'entités. En voici quelques exemples: clients, commandes, articles, messages, etc. Mais nous devons également établir des relations entre ces tables. Par exemple, les clients passent des commandes et les commandes contiennent des articles. Ces relations doivent être représentées dans la base de données. De plus, lors de la récupération de données avec SQL, nous devons utiliser certains types de requêtes JOIN pour obtenir ce dont nous avons besoin..
Il existe plusieurs types de relations de base de données. Aujourd'hui, nous allons couvrir ce qui suit:
Lors de la sélection des données de plusieurs tables avec des relations, nous utiliserons la requête JOIN. Il existe plusieurs types de JOIN, et nous allons en apprendre davantage sur les points suivants:
Nous étudierons également la clause ON et la clause USING.
Disons que vous avez une table pour les clients:
Nous pouvons mettre les informations d'adresse du client sur une table séparée:
Nous avons maintenant une relation entre la table Customers et la table Addresses. Si chaque adresse ne peut appartenir qu'à un seul client, cette relation est "One to One". Gardez à l'esprit que ce type de relation n'est pas très courant. Notre tableau initial comprenant l'adresse avec le client aurait pu fonctionner correctement dans la plupart des cas.
Notez qu'il existe maintenant un champ nommé "adresse_id" dans la table Customers, qui fait référence à l'enregistrement correspondant dans la table Address. Ceci s'appelle une "clé étrangère" et est utilisé pour toutes sortes de relations de base de données. Nous aborderons ce sujet plus tard dans l'article.
Nous pouvons visualiser la relation entre le client et les enregistrements d’adresses comme ceci:
Notez que l'existence d'une relation peut être facultative, comme avoir un enregistrement client qui n'a pas d'enregistrement d'adresse associé..
C'est le type de relation le plus couramment utilisé. Envisagez un site Web de commerce électronique comportant les éléments suivants:
Dans ces cas, nous aurions besoin de créer des relations "un à plusieurs". Voici un exemple:
Chaque client peut avoir zéro, une ou plusieurs commandes. Mais une commande ne peut appartenir qu'à un seul client.
Dans certains cas, vous aurez peut-être besoin de plusieurs instances des deux côtés de la relation. Par exemple, chaque commande peut contenir plusieurs articles. Et chaque article peut aussi être dans plusieurs ordres.
Pour ces relations, nous devons créer une table supplémentaire:
La table Items_Orders n'a qu'un seul but: créer une relation "plusieurs à plusieurs" entre les éléments et les commandes..
Voici comment nous pouvons visualiser ce type de relation:
Si vous souhaitez inclure les enregistrements items_orders dans le graphique, cela peut ressembler à ceci:
Ceci est utilisé quand une table doit avoir une relation avec elle-même. Par exemple, disons que vous avez un programme de référence. Les clients peuvent renvoyer d'autres clients vers votre site Web d'achat. Le tableau peut ressembler à ceci:
Les clients 102 et 103 ont été référés par le client 101.
En fait, cela peut aussi ressembler à une relation "un à plusieurs" puisqu'un client peut désigner plusieurs clients. En outre, il peut être visualisé comme une arborescence:
Un client peut se référer à zéro, un ou plusieurs clients. Chaque client peut être référé par un seul client ou par aucun.
Si vous souhaitez créer une relation d'auto-référencement "plusieurs à plusieurs", vous aurez besoin d'une table supplémentaire comme celle dont nous avons parlé dans la dernière section..
Jusqu'à présent, nous n'avons appris que certains concepts. Il est maintenant temps de leur donner vie en utilisant SQL. Pour cette partie, nous devons comprendre ce que sont les clés étrangères..
Dans les exemples de relation ci-dessus, nous avons toujours eu ces champs "**** _ id" qui référencaient une colonne dans une autre table. Dans cet exemple, la colonne customer_id de la table Orders est une colonne de clé étrangère:
Avec une base de données telle que MySQL, il existe deux façons de créer des colonnes de clés étrangères:
Créons une table clients simple:
CREATE TABLE clients (customer_id INT AUTO_INCREMENT PRIMARY KEY, nom_client VARCHAR (100));
Maintenant, la table des commandes, qui contiendra une clé étrangère:
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, montant DOUBLE, FOREIGN KEY (customer_id) REFERENCES clients (customer_id));
Les deux colonnes (clients.customer_id et orders.customer_id) doivent avoir la même structure de données. Si l'un est INT, l'autre ne doit pas être BIGINT par exemple.
Notez que dans MySQL, seul le moteur InnoDB prend en charge les clés étrangères. Mais d’autres moteurs de stockage vous permettront toujours de les spécifier sans donner d’erreur. La colonne de clé étrangère est également indexée automatiquement, sauf si vous spécifiez un autre index pour celle-ci..
La même table d'ordres peut être créée sans déclarer explicitement la colonne customer_id comme clé étrangère:
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, montant DOUBLE, INDEX (customer_id));
Lors de la récupération de données avec une requête JOIN, vous pouvez toujours traiter cette colonne comme une clé étrangère, même si le moteur de base de données n'est pas conscient de cette relation..
SÉLECTIONNER * DE COMMANDER JOINDRE DES UTILISATEURS EN UTILISANT (customer_id)
Nous allons en apprendre davantage sur les requêtes JOIN plus loin dans l'article.
MySQL Workbench est mon logiciel préféré actuel pour la conception de bases de données et la visualisation des relations entre clés étrangères..
Une fois que vous avez conçu votre base de données, vous pouvez exporter le code SQL et l'exécuter sur votre serveur. Ceci est très pratique pour les conceptions de bases de données plus grandes et plus complexes.
Pour récupérer des données d'une base de données qui a des relations, nous avons souvent besoin d'utiliser des requêtes JOIN.
Avant de commencer, créons les tables et des exemples de données avec lesquels travailler..
Accentsconagua
CREATE TABLE clients (customer_id INT AUTO_INCREMENT PRIMARY KEY, nom_client VARCHAR (100)); CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, montant DOUBLE, FOREIGN KEY (customer_id) REFERENCES clients (customer_id)); INSERT INTO 'clients' ('customer_id', 'customer_name') VALEURS (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERT INTO 'orders' ('order_id', 'customer_id', 'amount') VALUES (1, 1, 19.99), (2, 1, 35.15), (3, 3, 17.56), (4, 4, 12.34) ; Nous avons 4 clients. Un client a deux commandes, deux clients ont une commande chacun et un client n'a pas de commande. Voyons maintenant les différents types de requêtes JOIN que nous pouvons exécuter sur ces tables..
C'est le type par défaut de requête JOIN quand aucune condition n'est spécifiée.
Le résultat est un "produit cartésien" des tables. Cela signifie que chaque ligne de la première table correspond à chaque ligne de la deuxième table. Chaque table ayant 4 lignes, nous avons fini par avoir 16 lignes..
Le mot-clé JOIN peut éventuellement être remplacé par une virgule.
Bien sûr, ce genre de résultat n'est généralement pas utile. Alors regardons les autres types de jointure.
Avec ce type de requête JOIN, les tables doivent avoir un nom de colonne correspondant. Dans notre cas, les deux tables ont la colonne customer_id. Ainsi, MySQL joindra les enregistrements uniquement lorsque la valeur de cette colonne correspond à deux enregistrements..
Comme vous pouvez le constater, la colonne customer_id ne s'affiche qu'une fois, car le moteur de base de données la considère comme la colonne commune. Nous pouvons voir les deux commandes passées par Adam et les deux autres par Joe et Sandy. Enfin, nous obtenons des informations utiles.
Lorsqu'une condition de jointure est spécifiée, une jointure interne est effectuée. Dans ce cas, il serait judicieux de faire correspondre le champ customer_id aux deux tables. Les résultats doivent être similaires à ceux de la jointure naturelle..
Les résultats sont les mêmes sauf une petite différence. La colonne customer_id est répétée deux fois, une fois pour chaque table. La raison en est que nous avons simplement demandé à la base de données de faire correspondre les valeurs sur ces deux colonnes. Mais il n’est pas au courant qu’ils représentent la même information..
Ajoutons encore quelques conditions à la requête.
Cette fois, nous n'avons reçu que les commandes de plus de 15 $.
Avant de passer à d'autres types de jointure, nous devons examiner la clause ON. Ceci est utile pour placer les conditions JOIN dans une clause séparée.
Nous pouvons maintenant distinguer la condition JOIN des conditions de la clause WHERE. Mais il y a aussi une légère différence de fonctionnalité. Nous verrons cela dans les exemples LEFT JOIN.
La clause USING est similaire à la clause ON, mais elle est plus courte. Si une colonne porte le même nom sur les deux tables, nous pouvons le spécifier ici.
En fait, cela ressemble beaucoup à JOIN NATURAL, de sorte que la colonne de jointure (customer_id) ne se répète pas deux fois dans les résultats..
Une jointure à gauche est un type de jointure externe. Dans ces requêtes, s'il n'y a pas de correspondance dans la deuxième table, l'enregistrement de la première table est toujours affiché..
Même si Andy n'a pas d'ordres, son enregistrement est toujours affiché. Les valeurs sous les colonnes de la deuxième table sont définies sur NULL.
Cela est également utile pour rechercher des enregistrements sans relation. Par exemple, nous pouvons rechercher des clients qui n’ont passé aucune commande.
Nous avons simplement cherché des valeurs NULL pour le numéro_ordre..
Notez également que le mot clé OUTER est facultatif. Vous pouvez simplement utiliser LEFT JOIN au lieu de LEFT OUTER JOIN.
Maintenant, regardons une requête avec une condition.
Alors qu'est-il arrivé à Andy et Sandy? LEFT JOIN était censé renvoyer des clients sans commandes correspondantes. Le problème est que la clause WHERE bloque ces résultats. Pour les obtenir, nous pouvons également essayer d'inclure la condition NULL.
Nous avons Andy mais pas Sandy. Cela ne semble toujours pas correct. Pour obtenir ce que nous voulons, nous devons utiliser la clause ON.
Maintenant, nous avons eu tout le monde et toutes les commandes supérieures à 15 $. Comme je l'ai dit précédemment, la clause ON a parfois des fonctionnalités légèrement différentes de la clause WHERE. Dans une jointure externe semblable à celle-ci, les lignes sont incluses même si elles ne correspondent pas aux conditions de la clause ON..
A RIGHT OUTER JOIN fonctionne exactement de la même manière, mais l'ordre des tables est inversé.
Cette fois, nous n’avons aucun résultat NULL car chaque commande a un enregistrement client correspondant. Nous pouvons changer l’ordre des tables et obtenir les mêmes résultats que nous avons obtenus avec LEFT OUTER JOIN.
Maintenant, nous avons ces valeurs NULL parce que la table des clients est sur le côté droit de la jointure.
Merci d'avoir lu l'article. J'espère que tu as aimé! S'il vous plaît laissez vos commentaires et questions, et passez une bonne journée!
N'oubliez pas de consulter les scripts, les applications et les add-ons SQL sur Envato Market. Vous aurez une idée de ce qui est possible avec les bases de données SQL et vous trouverez peut-être la solution idéale pour vous aider dans votre projet en développement actuel..
Suivez-nous sur Twitter ou abonnez-vous au fil RSS Nettuts + pour obtenir les meilleurs tutoriels de développement Web sur le Web..
