Opérations sur plusieurs tables en MySQL

Introduction

Dans ce laboratoire (lab), nous explorerons les bases du travail avec plusieurs tables dans MySQL. Comprendre comment les tables sont liées les unes aux autres et comment combiner des données issues de plusieurs tables est essentiel pour développer des applications réelles. Nous apprendrons les relations entre les tables, les différents types d'opérations de jointure (JOIN) et comment maintenir l'intégrité des données à l'aide de contraintes de clé étrangère (foreign key constraints). À la fin de ce laboratoire, vous aurez acquis une expérience pratique en matière de création de tables liées, d'interrogation de données entre les tables et de gestion des relations entre les tables.

Comprendre les relations entre les tables

Dans cette étape, nous explorerons le concept des relations entre les tables et examinerons comment nos tables sont connectées. Comprendre les relations entre les tables est essentiel pour travailler avec des bases de données relationnelles.

Tout d'abord, connectons-nous à MySQL :

sudo mysql -u root

Une fois connecté, sélectionnons notre base de données :

USE bookstore;

Examinons la structure de nos tables et leurs relations :

SHOW CREATE TABLE books;

Vous verrez une sortie qui inclut les contraintes de clé étrangère (foreign key constraints) :

| books | CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `publisher_id` int(11) DEFAULT NULL,
  `publication_year` int(11) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `author_id` (`author_id`),
  KEY `publisher_id` (`publisher_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`),
  CONSTRAINT `books_ibfk_2` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`publisher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

Décortiquons les relations dans notre base de données :

1. Chaque livre a un seul auteur (relation plusieurs-à-un, many-to-one relationship)

   • Le author_id dans la table books fait référence au author_id dans la table authors. Cela signifie que le author_id dans la table books doit exister en tant que author_id dans la table authors.
   • Plusieurs livres peuvent avoir le même auteur.

2. Chaque livre a un seul éditeur (relation plusieurs-à-un, many-to-one relationship)

   • Le publisher_id dans la table books fait référence au publisher_id dans la table publishers.
   • Plusieurs livres peuvent avoir le même éditeur.

Pour voir ces relations en action, essayons une requête simple pour voir combien de livres chaque auteur a écrit :

SELECT author_id, COUNT(*) as book_count
FROM books
GROUP BY author_id;

La sortie ressemblera à ceci :

+-----------+------------+
| author_id | book_count |
+-----------+------------+
|         1 |          2 |
|         2 |          1 |
|         3 |          1 |
|         4 |          1 |
+-----------+------------+

Cela nous indique que l'auteur avec author_id 1 a 2 livres, et les auteurs 2, 3 et 4 ont chacun 1 livre. Cela illustre la relation plusieurs-à-un : plusieurs enregistrements de livres peuvent pointer vers un seul enregistrement d'auteur.

Opérations de jointure interne (INNER JOIN) de base

Dans cette étape, nous apprendrons à utiliser le INNER JOIN, un type d'opération de jointure crucial. Un INNER JOIN ne retourne que les lignes pour lesquelles il y a une correspondance dans les deux tables jointes. Imaginez-le comme un diagramme de Venn où le INNER JOIN vous donne l'intersection des deux tables. Si un enregistrement dans une table n'a pas de correspondance dans l'autre, il est exclu du résultat.

Commençons par un simple INNER JOIN pour obtenir les titres des livres ainsi que les noms de leurs auteurs :

SELECT
    books.title,
    authors.first_name,
    authors.last_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

Cette requête :

1. Commence par la table books (FROM books).
2. La joint à la table authors (INNER JOIN authors).
3. Spécifie la condition de jointure avec ON books.author_id = authors.author_id. C'est ainsi que la base de données sait quelles lignes de books correspondent à quelles lignes de authors : elle recherche les enregistrements où les valeurs de author_id sont égales dans les deux tables.
4. Sélectionne les colonnes que nous souhaitons voir : books.title, authors.first_name et authors.last_name.

Vous devriez voir une sortie comme celle-ci :

+----------------------------+------------+-----------+
| title                      | first_name | last_name |
+----------------------------+------------+-----------+
| The MySQL Guide            | John       | Smith     |
| Data Design Patterns       | Emma       | Wilson    |
| Database Fundamentals      | Michael    | Brown     |
| SQL for Beginners          | Sarah      | Johnson   |
| Advanced Database Concepts | John       | Smith     |
+----------------------------+------------+-----------+

Dans la déclaration SELECT, vous voyez que nous utilisons books.title, authors.first_name et authors.last_name. Cela indique explicitement à MySQL de quelle table chaque colonne provient. Si les noms de colonnes sont uniques dans les tables, vous pouvez omettre le préfixe de table (par exemple, utiliser simplement title). Cependant, il est recommandé de toujours spécifier le préfixe de table pour éviter toute ambiguïté, surtout lors de jointures multiples.

Nous pouvons également joindre la table publishers :

SELECT
    books.title,
    publishers.name as publisher_name,
    books.publication_year,
    books.price
FROM books
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;

Dans ce cas, publishers.name as publisher_name signifie que nous sélectionnons la colonne name de la table publishers et la renommons en publisher_name dans la sortie. Cela rend plus clair ce que représente la colonne.

+----------------------------+--------------------+------------------+-------+
| title                      | publisher_name     | publication_year | price |
+----------------------------+--------------------+------------------+-------+
| The MySQL Guide            | Tech Books Pro     |             2023 | 45.99 |
| Data Design Patterns       | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Database Press     |             2023 | 34.99 |
| Advanced Database Concepts | Query Publications |             2023 | 54.99 |
+----------------------------+--------------------+------------------+-------+

Opérations de jointure gauche (LEFT JOIN)

Dans cette étape, nous explorerons les opérations de LEFT JOIN. Une LEFT JOIN (parfois appelée LEFT OUTER JOIN) retourne tous les enregistrements de la table "de gauche" (la première table mentionnée dans la clause FROM) et les enregistrements correspondants de la table "de droite". La principale différence avec le INNER JOIN est que même s'il n'y a pas de correspondance dans la table de droite, les enregistrements de la table de gauche sont toujours inclus dans le résultat, avec des valeurs NULL là où il n'y a pas de correspondance dans la table de droite.

Ajoutons un auteur qui n'a pas encore publié de livres :

INSERT INTO authors (first_name, last_name, email)
VALUES ('David', 'Clark', 'david.clark@email.com');

Maintenant, utilisons une LEFT JOIN pour voir tous les auteurs, y compris ceux qui n'ont pas publié de livres :

SELECT
    authors.first_name,
    authors.last_name,
    COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id;

Vous devriez voir une sortie comme celle-ci :

+------------+-----------+------------+
| first_name | last_name | book_count |
+------------+-----------+------------+
| John       | Smith     |          2 |
| Emma       | Wilson    |          1 |
| Michael    | Brown     |          1 |
| Sarah      | Johnson   |          1 |
| David      | Clark     |          0 |
+------------+-----------+------------+

Notez que David Clark apparaît dans les résultats avec un nombre de livres égal à 0, même s'il n'a pas publié de livres. Cela se produit car la LEFT JOIN inclut toutes les lignes de la table authors et pour David, il n'y a pas de livres correspondants dans la table books, donc COUNT(books.book_id) donne 0, et non NULL. Cela illustre la différence cruciale : la LEFT JOIN garantit que toutes les lignes de la table de gauche apparaîtront dans le résultat, tandis que le INNER JOIN n'inclut que les lignes correspondantes dans les deux tables. S'il y a un livre, le compte sera un entier, sinon il sera égal à 0 en raison de COUNT().

Jointures de plusieurs tables

Dans cette étape, nous apprendrons à joindre plus de deux tables ensemble. Cela est souvent nécessaire lorsque vous devez combiner des données issues de plusieurs tables liées pour avoir une vision plus globale de vos données.

Créons une requête qui combine les informations des trois tables :

SELECT
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    p.name as publisher_name,
    b.publication_year,
    b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY b.title;

Analysons ce que fait cette requête :

1. FROM books b : Cela indique que nous commençons avec la table books et que nous lui donnons un alias b. Utiliser des alias (b, a, p) est une bonne pratique qui rend la requête plus courte et plus facile à lire.
2. INNER JOIN authors a ON b.author_id = a.author_id : Cela joint la table books avec la table authors en fonction de l'author_id. Les lignes ne seront retournées que s'il y a un author_id correspondant dans les deux tables. a est l'alias de la table authors.
3. INNER JOIN publishers p ON b.publisher_id = p.publisher_id : Cela joint le résultat de la jointure précédente avec la table publishers en fonction de publisher_id. Les lignes sont retournées uniquement s'il y a une correspondance sur publisher_id dans les deux tables. p est l'alias de la table publishers.
4. SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) as author_name, p.name as publisher_name, b.publication_year, b.price : Nous sélectionnons le titre de la table books, combinons le prénom et le nom de l'auteur dans une colonne author_name en utilisant CONCAT, utilisons la colonne du nom de l'éditeur et la renommons en publisher_name, ainsi que l'année de publication et le prix de leurs tables respectives.
5. ORDER BY b.title : Cela trie la sortie par le titre du livre par ordre croissant.

+----------------------------+---------------+--------------------+------------------+-------+
| title                      | author_name   | publisher_name     | publication_year | price |
+----------------------------+---------------+--------------------+------------------+-------+
| Advanced Database Concepts | John Smith    | Query Publications |             2023 | 54.99 |
| Data Design Patterns       | Emma Wilson   | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Michael Brown | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Sarah Johnson | Database Press     |             2023 | 34.99 |
| The MySQL Guide            | John Smith    | Tech Books Pro     |             2023 | 45.99 |
+----------------------------+---------------+--------------------+------------------+-------+

Cette requête montre comment vous pouvez rassembler des données de différentes tables pour obtenir une image plus complète. Chaque entrée de livre est associée aux informations sur l'auteur et l'éditeur.

Essayons un autre exemple qui montre les livres et leurs auteurs, y compris les auteurs sans livres, ainsi que les informations sur l'éditeur lorsque disponibles :

SELECT
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    b.title,
    p.name as publisher_name
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY author_name;

Dans cet exemple, nous utilisons une LEFT JOIN. Cela garantit que tous les auteurs sont affichés, qu'ils aient publié un livre ou non. La sortie pour un auteur sans livre affichera NULL pour le titre et le nom de l'éditeur.

+---------------+----------------------------+--------------------+
| author_name   | title                      | publisher_name     |
+---------------+----------------------------+--------------------+
| David Clark   | NULL                       | NULL               |
| Emma Wilson   | Data Design Patterns       | Tech Books Pro     |
| John Smith    | The MySQL Guide            | Tech Books Pro     |
| John Smith    | Advanced Database Concepts | Query Publications |
| Michael Brown | Database Fundamentals      | Database Press     |
| Sarah Johnson | SQL for Beginners          | Database Press     |
+---------------+----------------------------+--------------------+

Travailler avec les contraintes de clé étrangère (Foreign Key Constraints)

Dans cette étape finale, nous explorerons comment les contraintes de clé étrangère (foreign key constraints) contribuent à maintenir l'intégrité des données entre les tables liées. Les contraintes de clé étrangère garantissent que les relations entre les tables restent valides en empêchant les opérations qui créeraient des enregistrements orphelins ou des données incohérentes.

Comprenons comment fonctionnent les contraintes de clé étrangère grâce à quelques exemples :

Tout d'abord, essayons d'ajouter un livre avec un author_id invalide :

-- Cette requête échouera en raison de la contrainte de clé étrangère
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);

Vous verrez un message d'erreur car l'author_id 999 n'existe pas dans la table authors :

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))

Ce message d'erreur indique que la contrainte de clé étrangère sur la colonne author_id dans la table books nous empêche d'ajouter un livre qui fait référence à un author_id qui n'existe pas dans la table authors.

De même, nous ne pouvons pas supprimer un auteur qui a publié des livres sans traiter d'abord ses livres :

-- Cette requête échouera en raison de la contrainte de clé étrangère
DELETE FROM authors WHERE author_id = 1;

Tenter de supprimer directement l'auteur avec author_id = 1 entraîne une erreur de clé étrangère car il existe des livres qui font référence à cet author_id. C'est ainsi que MySQL s'assure que vos données restent cohérentes.

Pour supprimer en toute sécurité un auteur et ses livres, nous devons soit :

1. Supprimer d'abord les livres, puis l'auteur. Cela garantit que nous n'aurons pas d'enregistrements orphelins.
2. Utiliser CASCADE DELETE (que nous explorerons dans les laboratoires avancés).

Voyons comment supprimer correctement un livre et son auteur en supprimant d'abord le livre :

-- Tout d'abord, supprimez les livres de cet auteur
DELETE FROM books WHERE author_id = 1;

-- Maintenant, nous pouvons supprimer en toute sécurité l'auteur
DELETE FROM authors WHERE author_id = 1;

En supprimant d'abord le(s) livre(s), vous supprimez les références de clé étrangère, permettant ainsi de supprimer l'auteur sans violer les contraintes.

Résumé

Dans ce laboratoire, nous avons couvert les aspects essentiels de la manipulation de plusieurs tables dans MySQL :

1. Comprendre les relations entre les tables et la manière dont elles sont implémentées à l'aide de clés primaires (primary keys) et de clés étrangères (foreign keys).
2. Utiliser le INNER JOIN pour combiner les enregistrements correspondants de plusieurs tables, en montrant comment sélectionner les colonnes pertinentes à l'aide de préfixes et d'alias.
3. Utiliser le LEFT JOIN pour inclure tous les enregistrements d'une table, y compris ceux qui n'ont pas d'enregistrements correspondants dans l'autre, et comprendre comment interpréter les valeurs NULL.
4. Combiner les données de plusieurs tables à l'aide de jointures multiples, en illustrant comment obtenir des données de trois tables à la fois.
5. Travailler avec les contraintes de clé étrangère pour maintenir l'intégrité des données, empêcher les enregistrements orphelins et démontrer comment MySQL gère les relations entre les tables.

Ces compétences constituent la base pour travailler efficacement avec les bases de données relationnelles. Comprendre comment combiner et relier des données entre plusieurs tables est essentiel pour construire des applications de base de données robustes.