Bases de données : du MCD au SQL, implémentation avec phpMyAdmin

# Bases de données : du MCD au SQL, implémentation avec phpMyAdmin ## Introduction La modélisation des données est une étape fondamentale dans la conception de tout système d'information. Au Cameroun, dans le cadre du programme de la série Technologies de l'Information (TI), ce chapitre vous conduit de la modélisation conceptuelle (MCD) à l'implémentation physique d'une base de données relationnelle à l'aide d'un SGBD. Vous apprendrez à transformer un Modèle Conceptuel de Données (MCD) en schéma relationnel, à utiliser le langage SQL pour définir et manipuler les données, et à maîtriser l'environnement phpMyAdmin pour la gestion concrète de vos bases de données. Ces compétences sont essentielles pour concevoir, déployer et maintenir des applications informatiques robustes et performantes. ## I. Transformation d'un MCD en schéma relationnel ### 1.1 Rappel du Modèle Conceptuel de Données (MCD) Le MCD est une représentation graphique des données d'un système d'information, élaborée selon la méthode MERISE. Il utilise trois concepts fondamentaux : - **L'entité** (ou type d'entité) : représente un ensemble d'objets concrets ou abstraits ayant des caractéristiques communes. Exemple : ÉTUDIANT, COURS, PROFESSEUR. - **L'association** (ou type d'association) : représente un lien sémantique entre deux ou plusieurs entités. Exemple : SUIVRE (entre ÉTUDIANT et COURS). - **Les propriétés** (ou attributs) : caractéristiques décrivant les entités ou les associations. Exemple : nom, date_naissance, note. Chaque entité possède un **identifiant** (ou clé) qui permet de distinguer chaque occurrence de manière unique. ### 1.2 Règles de transformation d'un MCD en schéma relationnel Le passage du MCD au schéma relationnel (Modèle Logique de Données) suit des règles précises : **Règle 1 : Transformation d'une entité en relation (table)** Chaque entité du MCD devient une relation (table) dans le schéma relationnel. L'identifiant de l'entité devient la **clé primaire** de la relation. Toutes les propriétés de l'entité deviennent des **attributs** (colonnes) de la relation. Exemple : L'entité ÉTUDIANT (matricule, nom, prenom, date_naissance) devient la relation : ``` ÉTUDIANT (matricule, nom, prenom, date_naissance) ``` La clé primaire est soulignée : matricule. **Règle 2 : Transformation d'une association de cardinalité (1,1) à (1,1)** Si une association relie deux entités avec des cardinalités (1,1) de chaque côté, on peut fusionner les deux entités en une seule relation, ou ajouter la clé primaire de l'une comme clé étrangère dans l'autre. **Règle 3 : Transformation d'une association de cardinalité (1,1) à (0,n) ou (1,n)** La clé primaire de l'entité côté "n" est ajoutée comme **clé étrangère** dans la relation issue de l'entité côté "1". Les propriétés de l'association sont ajoutées dans la relation côté "1". Exemple : L'association "ENSEIGNER" entre PROFESSEUR (1,1) et COURS (0,n) donne : ``` COURS (id_cours, intitule, coefficient, id_professeur) ``` où id_professeur est une clé étrangère référençant PROFESSEUR(id_professeur). **Règle 4 : Transformation d'une association de cardinalité (0,n) à (0,n) ou (1,n) à (1,n)** L'association devient une nouvelle relation dont la clé primaire est la concaténation des clés primaires des entités participantes. Les propriétés de l'association deviennent des attributs de cette nouvelle relation. Exemple : L'association "SUIVRE" entre ÉTUDIANT (0,n) et COURS (0,n) avec la propriété note donne : ``` SUIVRE (matricule, id_cours, note) ``` La clé primaire est le couple (matricule, id_cours), et matricule et id_cours sont des clés étrangères. **Règle 5 : Association réflexive** Si une association relie une entité à elle-même, on applique les mêmes règles en distinguant les rôles (exemple : "être parent de" entre PERSONNE et PERSONNE). ### 1.3 Exemple complet de transformation Considérons le MCD suivant pour une bibliothèque : - Entité LIVRE (id_livre, titre, auteur, annee_publication) - Entité ADHERENT (id_adherent, nom, prenom, telephone) - Association EMPRUNTER entre LIVRE (0,n) et ADHERENT (0,n) avec la propriété date_emprunt Transformation : ``` LIVRE (id_livre, titre, auteur, annee_publication) ADHERENT (id_adherent, nom, prenom, telephone) EMPRUNTER (id_livre, id_adherent, date_emprunt) ``` Avec : - Clé primaire de LIVRE : id_livre - Clé primaire de ADHERENT : id_adherent - Clé primaire de EMPRUNTER : (id_livre, id_adherent) - Clés étrangères : id_livre (référence LIVRE), id_adherent (référence ADHERENT) > **À RETENIR** : La transformation d'un MCD en schéma relationnel suit des règles systématiques : chaque entité devient une table, chaque association devient soit une clé étrangère (si cardinalité 1,n), soit une table de jointure (si cardinalité n,n). La clé primaire est toujours soulignée dans la notation relationnelle. ## II. Éléments caractéristiques d'une table ### 2.1 Définition d'une table Une **table** (ou relation) est la structure de base d'une base de données relationnelle. Elle est composée de lignes et de colonnes. ### 2.2 Champs / Colonnes Un **champ** (ou colonne, ou attribut) représente une propriété spécifique des données. Chaque champ possède : - Un **nom** unique dans la table - Un **type de données** (INT, VARCHAR, DATE, etc.) - Éventuellement des **contraintes** (NOT NULL, UNIQUE, etc.) Exemple dans la table ÉTUDIANT : | matricule | nom | prenom | date_naissance | |-----------|-----|--------|----------------| | 24TI001 | NGO | Marie | 2006-03-15 | | 24TI002 | FOTSO | Jean | 2005-11-22 | Les colonnes sont : matricule, nom, prenom, date_naissance. ### 2.3 Enregistrements / Lignes Un **enregistrement** (ou ligne, ou tuple) représente une occurrence concrète des données. Dans l'exemple ci-dessus, la ligne "24TI001 | NGO | Marie | 2006-03-15" est un enregistrement. ### 2.4 Clé primaire La **clé primaire** (Primary Key) est un champ ou un ensemble de champs qui identifie de manière unique chaque enregistrement d'une table. Ses caractéristiques : - Elle ne peut pas contenir de valeurs NULL - Elle doit être unique pour chaque ligne - Une table ne peut avoir qu'une seule clé primaire > **À RETENIR** : La clé primaire est l'identifiant unique de chaque ligne d'une table. Elle ne peut jamais être NULL et doit être unique. Elle est notée PK (Primary Key) dans les schémas relationnels. ### 2.5 Clé étrangère La **clé étrangère** (Foreign Key) est un champ (ou ensemble de champs) qui référence la clé primaire d'une autre table. Elle permet d'établir des relations entre les tables et garantit l'intégrité référentielle. Exemple : Dans la table EMPRUNTER, le champ id_livre est une clé étrangère qui référence la clé primaire id_livre de la table LIVRE. > **À RETENIR** : La clé étrangère établit un lien entre deux tables. Elle doit correspondre à une valeur existante de la clé primaire référencée, ou être NULL si la contrainte le permet. Elle est notée FK (Foreign Key). ### 2.6 Types de données courants | Type | Description | Exemple | |------|-------------|---------| | INT | Nombre entier | 25, -3, 0 | | VARCHAR(n) | Chaîne de caractères variable (max n) | 'NGO', 'Yaoundé' | | CHAR(n) | Chaîne de caractères fixe (n caractères) | 'OUI', 'NON' | | TEXT | Texte long | 'Description détaillée...' | | DATE | Date (AAAA-MM-JJ) | '2024-09-15' | | DATETIME | Date et heure | '2024-09-15 14:30:00' | | DECIMAL(p,s) | Nombre décimal (p chiffres, s décimales) | 15.75 | | BOOLEAN | Vrai/Faux | TRUE, FALSE | | FLOAT | Nombre à virgule flottante | 3.14159 | ## III. SGBD : Système de Gestion de Bases de Données ### 3.1 Définition et caractéristiques Un **SGBD** (Système de Gestion de Bases de Données) est un logiciel qui permet de créer, gérer, interroger et administrer des bases de données. Il assure la persistance, la cohérence et la sécurité des données. **Caractéristiques principales d'un SGBD :** 1. **Persistance des données** : les données sont stockées de manière durable sur un support physique. 2. **Indépendance physique/logique** : les programmes sont indépendants de la structure physique de stockage. 3. **Gestion des accès concurrents** : plusieurs utilisateurs peuvent accéder simultanément aux données. 4. **Sécurité** : contrôle des accès par identification et autorisations. 5. **Intégrité des données** : respect des contraintes définies (clés, types, etc.). 6. **Sauvegarde et restauration** : possibilité de sauvegarder et restaurer les données. 7. **Langage de requête** : SQL (Structured Query Language) pour interroger et manipuler les données. ### 3.2 Exemples de SGBD | SGBD | Type | Caractéristiques | |------|------|------------------| | MySQL | Relationnel, open source | Très répandu, utilisé avec PHP | | MariaDB | Relationnel, open source | Fork de MySQL, compatible | | PostgreSQL | Relationnel, open source | Avancé, respecte les standards SQL | | Oracle | Relationnel, commercial | Puissant, utilisé en entreprise | | SQL Server | Relationnel, commercial | Édité par Microsoft | | SQLite | Relationnel, embarqué | Léger, sans serveur, pour applications mobiles | | MongoDB | NoSQL, orienté documents | Stockage JSON, grande scalabilité | ### 3.3 Environnement d'implémentation d'une base de données L'implémentation d'une base de données nécessite un environnement comprenant : 1. **Le SGBD** : le logiciel de gestion (exemple : MySQL). 2. **Un serveur de bases de données** : le service qui écoute les requêtes (exemple : serveur MySQL sur le port 3306). 3. **Un outil d'administration** : interface pour gérer les bases (exemple : phpMyAdmin, MySQL Workbench, Adminer). 4. **Un langage de programmation** : pour développer des applications (exemple : PHP, Java, Python). 5. **Un serveur web** : si l'application est web (exemple : Apache, Nginx). **phpMyAdmin** est une application web open source qui permet d'administrer les bases de données MySQL/MariaDB via une interface graphique. Elle est très utilisée dans les environnements LAMP/WAMP/XAMPP. > **À RETENIR** : Un SGBD est un logiciel qui gère les bases de données. MySQL est le SGBD le plus utilisé avec PHP. phpMyAdmin est l'outil d'administration web le plus courant pour MySQL. ## IV. Contraintes d'intégrité Les **contraintes d'intégrité** sont des règles qui garantissent la cohérence et la validité des données dans une base de données. Elles sont définies lors de la création des tables. ### 4.1 Contrainte de clé primaire (PRIMARY KEY) Elle garantit que chaque ligne d'une table est unique et identifiable. La clé primaire ne peut pas être NULL. ```sql CREATE TABLE etudiant ( matricule VARCHAR(10) PRIMARY KEY, nom VARCHAR(50) NOT NULL ); ``` ### 4.2 Contrainte de valeur autorisée (CHECK) Elle vérifie que les valeurs insérées respectent une condition spécifique. ```sql CREATE TABLE etudiant ( matricule VARCHAR(10) PRIMARY KEY, age INT CHECK (age >= 15 AND age <= 30) ); ``` ### 4.3 Contrainte de valeur nulle (NOT NULL) Elle interdit la valeur NULL pour un champ donné. ```sql CREATE TABLE etudiant ( matricule VARCHAR(10) PRIMARY KEY, nom VARCHAR(50) NOT NULL, telephone VARCHAR(15) -- Peut être NULL ); ``` ### 4.4 Contrainte de valeur par défaut (DEFAULT) Elle définit une valeur automatique lorsqu'aucune valeur n'est fournie lors de l'insertion. ```sql CREATE TABLE etudiant ( matricule VARCHAR(10) PRIMARY KEY, date_inscription DATE DEFAULT CURRENT_DATE, statut VARCHAR(20) DEFAULT 'actif' ); ``` ### 4.5 Contrainte d'unicité (UNIQUE) Elle garantit que toutes les valeurs d'un champ (ou combinaison de champs) sont distinctes. ```sql CREATE TABLE etudiant ( matricule VARCHAR(10) PRIMARY KEY, email VARCHAR(100) UNIQUE, telephone VARCHAR(15) UNIQUE ); ``` ### 4.6 Contrainte de format (FORMAT / REGEXP) Elle vérifie que les valeurs respectent un format spécifique (expression régulière). MySQL utilise CHECK avec REGEXP. ```sql CREATE TABLE etudiant ( matricule VARCHAR(10) PRIMARY KEY, telephone VARCHAR(15) CHECK (telephone REGEXP '^[0-9]{9}$') ); ``` ### 4.7 Contrainte de clé étrangère (FOREIGN KEY) Elle garantit l'intégrité référentielle : les valeurs de la clé étrangère doivent exister dans la table référencée. ```sql CREATE TABLE emprunter ( id_livre INT, id_adherent INT, date_emprunt DATE, PRIMARY KEY (id_livre, id_adherent), FOREIGN KEY (id_livre) REFERENCES livre(id_livre), FOREIGN KEY (id_adherent) REFERENCES adherent(id_adherent) ); ``` > **À RETENIR** : Les contraintes d'intégrité garantissent la qualité des données. Les principales sont : PRIMARY KEY (unicité et non-nullité), NOT NULL (obligation de valeur), UNIQUE (unicité), CHECK (condition), DEFAULT (valeur par défaut), FOREIGN KEY (intégrité référentielle). ## V. Fonctions de définition d'une base de données ### 5.1 Créer une base de données ```sql CREATE DATABASE IF NOT EXISTS bibliotheque; ``` ### 5.2 Supprimer une base de données ```sql DROP DATABASE IF EXISTS bibliotheque; ``` ### 5.3 Créer une table ```sql CREATE TABLE IF NOT EXISTS livre ( id_livre INT AUTO_INCREMENT PRIMARY KEY, titre VARCHAR(200) NOT NULL, auteur VARCHAR(100) NOT NULL, annee_publication INT, prix DECIMAL(10,2) DEFAULT 0.00 ); ``` ### 5.4 Supprimer une table ```sql DROP TABLE IF EXISTS livre; ``` ### 5.5 Ajouter une colonne ```sql ALTER TABLE livre ADD COLUMN editeur VARCHAR(100); ``` ### 5.6 Supprimer une colonne ```sql ALTER TABLE livre DROP COLUMN editeur; ``` ### 5.7 Renommer une colonne ```sql ALTER TABLE livre RENAME COLUMN annee_publication TO annee; ``` ### 5.8 Modifier le type d'une colonne ```sql ALTER TABLE livre MODIFY COLUMN prix DECIMAL(12,2); ``` ### 5.9 Modifier le nom d'une table ```sql ALTER TABLE livre RENAME TO ouvrage; ``` ### 5.10 Vider une table (supprimer toutes les lignes) ```sql TRUNCATE TABLE livre; ``` > **À RETENIR** : Les commandes de définition sont : CREATE DATABASE/TABLE, DROP DATABASE/TABLE, ALTER TABLE (ADD, DROP, RENAME, MODIFY COLUMN), TRUNCATE TABLE. Elles modifient la structure de la base de données. ## VI. Création d'une base de données dans un SGBD (phpMyAdmin) ### 6.1 Procédure pas-à-pas avec phpMyAdmin **Étape 1 : Accéder à phpMyAdmin** - Ouvrir le navigateur web - Saisir l'URL : http://localhost/phpmyadmin (ou l'adresse configurée) - Saisir le nom d'utilisateur (root) et le mot de passe **Étape 2 : Créer une base de données** - Cliquer sur l'onglet "Bases de données" - Saisir le nom de la base (exemple : bibliotheque) - Choisir l'interclassement (utf8_general_ci pour le français) - Cliquer sur "Créer" **Étape 3 : Créer une table** - Sélectionner la base créée - Saisir le nom de la table (exemple : livre) - Saisir le nombre de colonnes (exemple : 5) - Cliquer sur "Exécuter" **Étape 4 : Définir les colonnes** - Pour chaque colonne, saisir : - Le nom (exemple : id_livre) - Le type (exemple : INT) - La longueur/valeur (exemple : 11) - L'index (exemple : PRIMARY) - AUTO_INCREMENT si nécessaire - NULL ou NOT NULL - La valeur par défaut - Cliquer sur "Enregistrer" **Étape 5 : Insérer des données** - Cliquer sur l'onglet "Insérer" - Saisir les valeurs pour chaque champ - Cliquer sur "Exécuter" **Étape 6 : Exécuter des requêtes SQL** - Cliquer sur l'onglet "SQL" - Saisir la requête SQL - Cliquer sur "Exécuter" ### 6.2 Exemple complet de création ```sql -- Création de la base CREATE DATABASE bibliotheque; -- Utilisation de la base USE bibliotheque; -- Création de la table livre CREATE TABLE livre ( id_livre INT AUTO_INCREMENT PRIMARY KEY, titre VARCHAR(200) NOT NULL, auteur VARCHAR(100) NOT NULL, annee INT, disponible BOOLEAN DEFAULT TRUE ); -- Création de la table adherent CREATE TABLE adherent ( id_adherent INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, telephone VARCHAR(15) UNIQUE ); -- Création de la table emprunter CREATE TABLE emprunter ( id_livre INT, id_adherent INT, date_emprunt DATE NOT NULL, date_retour DATE, PRIMARY KEY (id_livre, id_adherent, date_emprunt), FOREIGN KEY (id_livre) REFERENCES livre(id_livre), FOREIGN KEY (id_adherent) REFERENCES adherent(id_adherent) ); ``` > **À RETENIR** : Dans phpMyAdmin, la création d'une base de données se fait en 4 étapes : créer la base, créer les tables, définir les colonnes avec leurs types et contraintes, puis insérer les données. L'onglet SQL permet d'exécuter directement des requêtes. ## VII. Requêtes SQL de définition et de manipulation des données ### 7.1 Langage de Définition des Données (LDD) Les commandes LDD (DDL - Data Definition Language) permettent de définir la structure de la base : | Commande | Description | |----------|-------------| | CREATE DATABASE | Créer une base | | DROP DATABASE | Supprimer une base | | CREATE TABLE | Créer une table | | DROP TABLE | Supprimer une table | | ALTER TABLE | Modifier une table | | TRUNCATE TABLE | Vider une table | ### 7.2 Langage de Manipulation des Données (LMD) Les commandes LMD (DML - Data Manipulation Language) permettent de manipuler les données : | Commande | Description | |----------|-------------| | INSERT INTO | Insérer des lignes | | UPDATE | Modifier des lignes | | DELETE | Supprimer des lignes | | SELECT | Interroger les données | ### 7.3 INSERT : Insérer des données ```sql -- Insertion complète INSERT INTO livre (titre, auteur, annee) VALUES ('Le Petit Prince', 'Saint-Exupéry', 1943); -- Insertion multiple INSERT INTO livre (titre, auteur, annee) VALUES ('Candide', 'Voltaire', 1759), ('Les Misérables', 'Victor Hugo', 1862); -- Insertion avec valeurs par défaut INSERT INTO livre (titre, auteur) VALUES ('L\'Étranger', 'Albert Camus'); ``` ### 7.4 UPDATE : Modifier des données ```sql -- Modifier une ligne spécifique UPDATE livre SET annee = 1944 WHERE id_livre = 1; -- Modifier plusieurs colonnes UPDATE livre SET titre = 'Le Petit Prince (édition illustrée)', annee = 1943 WHERE id_livre = 1; -- Modifier plusieurs lignes UPDATE livre SET disponible = FALSE WHERE annee < 1900; ``` ### 7.5 DELETE : Supprimer des données ```sql -- Supprimer une ligne spécifique DELETE FROM livre WHERE id_livre = 3; -- Supprimer plusieurs lignes DELETE FROM livre WHERE annee < 1800; -- Supprimer toutes les lignes DELETE FROM livre; ``` ### 7.6 SELECT : Interroger les données ```sql -- Sélectionner toutes les colonnes SELECT * FROM livre; -- Sélectionner des colonnes spécifiques SELECT titre, auteur FROM livre; -- Sélectionner avec condition SELECT * FROM livre WHERE annee > 1900; -- Sélectionner avec tri SELECT * FROM livre ORDER BY annee DESC; -- Sélectionner avec limite SELECT * FROM livre LIMIT 5; ``` > **À RETENIR** : Le langage SQL se divise en LDD (CREATE, ALTER, DROP, TRUNCATE) pour la structure et LMD (INSERT, UPDATE, DELETE, SELECT) pour les données. SELECT est la commande la plus utilisée pour interroger la base. ## VIII. Opérateurs logiques, de comparaison et fonctions ### 8.1 Opérateurs de comparaison | Opérateur | Description | Exemple | |-----------|-------------|---------| | = | Égal à | WHERE annee = 1943 | | <> ou != | Différent de | WHERE annee <> 1943 | | > | Supérieur à | WHERE annee > 1900 | | < | Inférieur à | WHERE annee < 1900 | | >= | Supérieur ou égal | WHERE annee >= 1943 | | <= | Inférieur ou égal | WHERE annee <= 1943 | ### 8.2 Opérateurs logiques **AND** : Les deux conditions doivent être vraies. ```sql SELECT * FROM livre WHERE annee > 1900 AND auteur = 'Camus'; ``` **OR** : Au moins une des conditions doit être vraie. ```sql SELECT * FROM livre WHERE auteur = 'Camus' OR auteur = 'Hugo'; ``` **NOT** : Inverse la condition. ```sql SELECT * FROM livre WHERE NOT annee < 1900; ``` ### 8.3 Opérateur LIKE Permet la recherche par motif avec des caractères génériques : - `%` : remplace une séquence de caractères (0 ou plusieurs) - `_` : remplace un seul caractère ```sql -- Titres commençant par "Le" SELECT * FROM livre WHERE titre LIKE 'Le%'; -- Titres contenant "petit" SELECT * FROM livre WHERE titre LIKE '%petit%'; -- Auteurs dont le nom a 5 lettres SELECT * FROM livre WHERE auteur LIKE '_____'; ``` ### 8.4 Opérateur BETWEEN Sélectionne les valeurs dans un intervalle (bornes incluses). ```sql SELECT * FROM livre WHERE annee BETWEEN 1900 AND 2000; -- Équivalent à : SELECT * FROM livre WHERE annee >= 1900 AND annee <= 2000; ``` ### 8.5 Opérateur IN Sélectionne les valeurs appartenant à une liste. ```sql SELECT * FROM livre WHERE auteur IN ('Camus', 'Hugo', 'Voltaire'); -- Équivalent à : SELECT * FROM livre WHERE auteur = 'Camus' OR auteur = 'Hugo' OR auteur = 'Voltaire'; ``` ### 8.6 Opérateur NOT IN Sélectionne les valeurs n'appartenant pas à une liste. ```sql SELECT * FROM livre WHERE auteur NOT IN ('Camus', 'Hugo'); ``` ### 8.7 Fonctions d'agrégation **COUNT()** : Compte le nombre de lignes. ```sql -- Nombre total de livres SELECT COUNT(*) FROM livre; -- Nombre de livres de Camus SELECT COUNT(*) FROM livre WHERE auteur = 'Camus'; -- Nombre de valeurs non NULL dans une colonne SELECT COUNT(annee) FROM livre; ``` **AVG()** : Calcule la moyenne des valeurs. ```sql -- Moyenne des années de publication SELECT AVG(annee) FROM livre; -- Moyenne des notes SELECT AVG(note) FROM emprunter; ``` **MIN()** : Retourne la valeur minimale. ```sql -- Année de publication la plus ancienne SELECT MIN(annee) FROM livre; ``` **MAX()** : Retourne la valeur maximale. ```sql -- Année de publication la plus récente SELECT MAX(annee) FROM livre; ``` **SUM()** : Calcule la somme des valeurs. ```sql -- Somme des prix de tous les livres SELECT SUM(prix) FROM livre; ``` > **À RETENIR** : Les opérateurs logiques (AND, OR, NOT) combinent des conditions. LIKE utilise % et _ pour les motifs. BETWEEN définit un intervalle. IN vérifie l'appartenance à une liste. Les fonctions d'agrégation (COUNT, AVG, MIN, MAX, SUM) effectuent des calculs sur les ensembles de lignes. ## IX. Regroupement, tri, jointure et alias ### 9.1 Tri avec ORDER BY Le tri s'effectue avec ORDER BY, par défaut en ordre croissant (ASC), ou décroissant (DESC). ```sql -- Tri croissant SELECT * FROM livre ORDER BY annee; -- Tri décroissant SELECT * FROM livre ORDER BY annee DESC; -- Tri sur plusieurs colonnes SELECT * FROM livre ORDER BY auteur ASC, annee DESC; -- Tri avec limite SELECT * FROM livre ORDER BY annee DESC LIMIT 3; ``` ### 9.2 Regroupement avec GROUP BY GROUP BY regroupe les lignes ayant les mêmes valeurs dans les colonnes spécifiées. Il est généralement utilisé avec les fonctions d'agrégation. ```sql -- Nombre de livres par auteur SELECT auteur, COUNT(*) AS nombre_livres FROM livre GROUP BY auteur; -- Moyenne des années par auteur SELECT auteur, AVG(annee) AS annee_moyenne FROM livre GROUP BY auteur; ``` ### 9.3 Filtrage des groupes avec HAVING HAVING filtre les groupes après le regroupement (contrairement à WHERE qui filtre avant). ```sql -- Auteurs ayant plus de 2 livres SELECT auteur, COUNT(*) AS nombre FROM livre GROUP BY auteur HAVING COUNT(*) > 2; -- Auteurs dont la moyenne des années > 1900 SELECT auteur, AVG(annee) AS moyenne FROM livre GROUP BY auteur HAVING AVG(annee) > 1900; ``` ### 9.4 Alias Les alias permettent de renommer temporairement une colonne ou une table. ```sql -- Alias de colonne SELECT titre AS t, auteur AS a FROM livre; -- Alias de table SELECT l.titre, l.auteur FROM livre AS l WHERE l.annee > 1900; -- Alias sans AS SELECT titre t, auteur a FROM livre; ``` ### 9.5 Jointure de tables La **jointure** permet de combiner les données de plusieurs tables en fonction d'une condition de correspondance. **Jointure interne (INNER JOIN)** : retourne uniquement les lignes ayant une correspondance dans les deux tables. ```sql -- Afficher les emprunts avec les noms des adhérents SELECT adherent.nom, adherent.prenom, emprunter.date_emprunt FROM adherent INNER JOIN emprunter ON adherent.id_adherent = emprunter.id_adherent; -- Avec alias SELECT a.nom, a.prenom, e.date_emprunt FROM adherent AS a INNER JOIN emprunter AS e ON a.id_adherent = e.id_adherent; ``` **Jointure gauche (LEFT JOIN)** : retourne toutes les lignes de la table de gauche, même sans correspondance dans la table de droite. ```sql -- Tous les livres, même ceux jamais empruntés SELECT l.titre, e.date_emprunt FROM livre AS l LEFT JOIN emprunter AS e ON l.id_livre = e.id_livre; ``` **Jointure droite (RIGHT JOIN)** : retourne toutes les lignes de la table de droite. ```sql SELECT l.titre, e.date_emprunt FROM emprunter AS e RIGHT JOIN livre AS l ON l.id_livre = e.id_livre; ``` **Jointure sur plusieurs tables** : ```sql -- Emprunts avec titres des livres et noms des adhérents SELECT l.titre, a.nom, a.prenom, e.date_emprunt FROM emprunter AS e INNER JOIN livre AS l ON e.id_livre = l.id_livre INNER JOIN adherent AS a ON e.id_adherent = a.id_adherent; ``` ### 9.6 Exemple complet combinant toutes les notions ```sql -- Nombre d'emprunts par adhérent, trié par ordre décroissant SELECT a.nom, a.prenom, COUNT(e.id_livre) AS nombre_emprunts FROM adherent AS a LEFT JOIN emprunter AS e ON a.id_adherent = e.id_adherent GROUP BY a.id_adherent HAVING COUNT(e.id_livre) > 0 ORDER BY nombre_emprunts DESC; ``` > **À RETENIR** : ORDER BY trie les résultats, GROUP BY regroupe les lignes pour les fonctions d'agrégation, HAVING filtre les groupes, les alias (AS) renomment colonnes et tables, et les jointures (INNER JOIN, LEFT JOIN, RIGHT JOIN) combinent les tables sur une condition de correspondance. ## X. Export, import et sauvegarde d'une base de données ### 10.1 Export d'une base de données L'export consiste à extraire la structure et/ou les données d'une base dans un fichier (généralement SQL ou CSV). **Export avec phpMyAdmin :** 1. Sélectionner la base de données 2. Cliquer sur l'onglet "Exporter" 3. Choisir la méthode : - "Rapide" : export minimal - "Personnalisé" : options avancées 4. Sélectionner les tables à exporter (ou toutes) 5. Choisir le format (SQL, CSV, JSON, etc.) 6. Cliquer sur "Exécuter" **Export en ligne de commande MySQL :** ```bash mysqldump -u root -p bibliotheque > bibliotheque_sauvegarde.sql ``` **Export d'une seule table :** ```bash mysqldump -u root -p bibliotheque livre > livre_table.sql ``` ### 10.2 Import d'une base de données L'import permet de restaurer une base à partir d'un fichier d'export. **Import avec phpMyAdmin :** 1. Sélectionner la base de données (ou créer une nouvelle base) 2. Cliquer sur l'onglet "Importer" 3. Cliquer sur "Choisir un fichier" 4. Sélectionner le fichier .sql 5. Cliquer sur "Exécuter" **Import en ligne de commande MySQL :** ```bash mysql -u root -p bibliotheque < bibliotheque_sauvegarde.sql ``` ### 10.3 Sauvegarde régulière La sauvegarde est une opération critique pour la sécurité des données. Stratégies recommandées : 1. **Sauvegarde complète** : exporter toute la base (structure + données) périodiquement. 2. **Sauvegarde incrémentale** : ne sauvegarder que les modifications depuis la dernière sauvegarde. 3. **Planification** : automatiser les sauvegardes (cron sous Linux, Planificateur de tâches sous Windows). **Exemple de script de sauvegarde automatisée (Linux) :** ```bash #!/bin/bash # Script de sauvegarde MySQL DATE=$(date +%Y%m%d_%H%M%S) BACKUP_DIR="/var/backups/mysql" DB_NAME="bibliotheque" # Créer le répertoire si nécessaire mkdir -p $BACKUP_DIR # Sauvegarde de la base mysqldump -u root -p$MYSQL_PASSWORD $DB_NAME > $BACKUP_DIR/${DB_NAME}_$DATE.sql # Compression gzip $BACKUP_DIR/${DB_NAME}_$DATE.sql # Supprimer les sauvegardes de plus de 7 jours find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete ``` ### 10.4 Export/Import en CSV Le format CSV (Comma Separated Values) est utile pour échanger des données avec d'autres applications (Excel, etc.). **Export CSV avec phpMyAdmin :** - Onglet "Exporter" - Format : CSV - Options : séparateur de champs (; ou ,), encodage (UTF-8) **Import CSV avec phpMyAdmin :** - Onglet "Importer" - Format : CSV - Spécifier le séparateur et l'encodage > **À RETENIR** : L'export crée un fichier de sauvegarde (SQL, CSV), l'import restaure la base à partir de ce fichier. La sauvegarde régulière est essentielle pour prévenir la perte de données. phpMyAdmin offre une interface graphique pour ces opérations, et mysqldump/mysql pour la ligne de commande. ## XI. Exemple récapitulatif complet ### 11.1 Contexte Nous allons créer une base de données pour la gestion d'une bibliothèque scolaire à Yaoundé. ### 11.2 MCD - Entité LIVRE (id_livre, titre, auteur, annee, genre) - Entité ADHERENT (id_adherent, nom, prenom, classe, telephone) - Entité EMPRUNT (id_emprunt, date_emprunt, date_retour_prevue, date_retour_reelle) - Association EMPRUNTER entre ADHERENT (0,n) et EMPRUNT (1,1) - Association CONCERNER entre LIVRE (0,n) et EMPRUNT (1,1) ### 11.3 Schéma relationnel ``` LIVRE (id_livre, titre, auteur, annee, genre) ADHERENT (id_adherent, nom, prenom, classe, telephone) EMPRUNT (id_emprunt, date_emprunt, date_retour_prevue, date_retour_reelle, id_adherent, id_livre) ``` ### 11.4 Création de la base avec SQL ```sql -- Création de la base CREATE DATABASE IF NOT EXISTS bibliotheque_scolaire; USE bibliotheque_scolaire; -- Table LIVRE CREATE TABLE livre ( id_livre INT AUTO_INCREMENT PRIMARY KEY, titre VARCHAR(200) NOT NULL, auteur VARCHAR(100) NOT NULL, annee INT CHECK (annee >= 1900), genre VARCHAR(50) DEFAULT 'Roman' ); -- Table ADHERENT CREATE TABLE adherent ( id_adherent INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, classe VARCHAR(20) NOT NULL, telephone VARCHAR(15) UNIQUE ); -- Table EMPRUNT CREATE TABLE emprunt ( id_emprunt INT AUTO_INCREMENT PRIMARY KEY, date_emprunt DATE NOT NULL DEFAULT CURRENT_DATE, date_retour_prevue DATE NOT NULL, date_retour_reelle DATE, id_adherent INT NOT NULL, id_livre INT NOT NULL, FOREIGN KEY (id_adherent) REFERENCES adherent(id_adherent), FOREIGN KEY (id_livre) REFERENCES livre(id_livre) ); ``` ### 11.5 Insertion des données ```sql -- Insertion des livres INSERT INTO livre (titre, auteur, annee, genre) VALUES ('Le Petit Prince', 'Saint-Exupéry', 1943, 'Conte'), ('Candide', 'Voltaire', 1759, 'Philosophie'), ('Les Misérables', 'Victor Hugo', 1862, 'Roman'), ('L\'Étranger', 'Albert Camus', 1942, 'Roman'), ('Une si longue lettre', 'Mariama Bâ', 1979, 'Roman'); -- Insertion des adhérents INSERT INTO adherent (nom, prenom, classe, telephone) VALUES ('NGO', 'Marie', 'Tle TI', '691234567'), ('FOTSO', 'Jean', 'Tle TI', '692345678'), ('MBALLA', 'Paul', '1re TI', '693456789'), ('KENFACK', 'Aline', 'Tle TI', '694567890'); -- Insertion des emprunts INSERT INTO emprunt (date_emprunt, date_retour_prevue, id_adherent, id_livre) VALUES ('2024-09-01', '2024-09-15', 1, 1), ('2024-09-02', '2024-09-16', 2, 3), ('2024-09-03', '2024-09-17', 1, 4), ('2024-09-04', '2024-09-18', 3, 2); ``` ### 11.6 Requêtes d'interrogation ```sql -- 1. Liste de tous les livres triés par année décroissante SELECT titre, auteur, annee FROM livre ORDER BY annee DESC; -- 2. Livres de genre 'Roman' publiés après 1900 SELECT titre, auteur FROM livre WHERE genre = 'Roman' AND annee > 1900; -- 3. Nombre de livres par genre SELECT genre, COUNT(*) AS nombre FROM livre GROUP BY genre; -- 4. Adhérents ayant emprunté plus d'un livre SELECT a.nom, a.prenom, COUNT(e.id_emprunt) AS nb_emprunts FROM adherent a INNER JOIN emprunt e ON a.id_adherent = e.id_adherent GROUP BY a.id_adherent HAVING COUNT(e.id_emprunt) > 1; -- 5. Emprunts en cours (non retournés) avec détails SELECT l.titre, a.nom, a.prenom, e.date_emprunt, e.date_retour_prevue FROM emprunt e INNER JOIN livre l ON e.id_livre = l.id_livre INNER JOIN adherent a ON e.id_adherent = a.id_adherent WHERE e.date_retour_reelle IS NULL; -- 6. Livres jamais empruntés SELECT l.titre, l.auteur FROM livre l LEFT JOIN emprunt e ON l.id_livre = e.id_livre WHERE e.id_emprunt IS NULL; -- 7. Moyenne des années de publication des livres empruntés SELECT AVG(l.annee) AS annee_moyenne FROM livre l INNER JOIN emprunt e ON l.id_livre = e.id_livre; -- 8. Adhérents dont le nom commence par 'N' SELECT * FROM adherent WHERE nom LIKE 'N%'; -- 9. Livres publiés entre 1940 et 1980 SELECT * FROM livre WHERE annee BETWEEN 1940 AND 1980; -- 10. Emprunts des adhérents de la classe 'Tle TI' SELECT l.titre, e.date_emprunt FROM emprunt e INNER JOIN adherent a ON e.id_adherent = a.id_adherent INNER JOIN livre l ON e.id_livre = l.id_livre WHERE a.classe = 'Tle TI'; ``` ## XII. Conclusion La maîtrise des bases de données relationnelles constitue une compétence essentielle pour tout technicien en Informatique. Ce chapitre vous a permis de parcourir l'ensemble du processus, depuis la modélisation conceptuelle avec le MCD jusqu'à l'implémentation concrète dans un SGBD comme MySQL via phpMyAdmin. Vous savez désormais : - Transformer un MCD en schéma relationnel en appliquant les règles de passage systématiques - Identifier les éléments caractéristiques d'une table (clés primaires, clés étrangères, champs, enregistrements) - Utiliser les contraintes d'intégrité pour garantir la qualité des données - Créer et gérer une base de données avec les commandes SQL de définition - Manipuler les données avec les commandes INSERT, UPDATE, DELETE et SELECT - Combiner les opérateurs logiques, les fonctions d'agrégation, les regroupements, les tris et les jointures - Exporter, importer et sauvegarder une base de données Ces compétences vous permettront de concevoir et de développer des applications complètes, du stockage des données à leur exploitation. La pratique régulière sur des cas concrets, comme la gestion d'une bibliothèque ou d'un établissement scolaire, est indispensable pour consolider ces acquis. Les outils comme phpMyAdmin facilitent l'apprentissage grâce à leur interface graphique, mais la maîtrise du langage SQL reste fondamentale pour interagir efficacement avec tout système de gestion de bases de données.