Introduction à MySQL et PHPMyAdmin

Objectif pédagogique : Découvrir MySQL et apprendre à créer des bases de données avec PHPMyAdmin pour gérer vos données efficacement.

À la fin de ce chapitre, tu seras capable de…

  • Installer et configurer WampServer pour utiliser MySQL
  • Créer une base de données et des tables avec PHPMyAdmin
  • Comprendre les propriétés ACID des transactions en base de données
  • Maîtriser les opérations de base sur les tables MySQL
  • Utiliser l’interface PHPMyAdmin pour gérer vos données

Prérequis : Connaissances de base en informatique et navigation web

Mise en contexte

Vous souhaitez stocker et organiser des données comme le font Google, Facebook ou YouTube ? MySQL est le système de gestion de base de données le plus populaire au monde ! Dans ce chapitre, vous allez apprendre à installer MySQL avec WampServer, créer votre première base de données ‘rentcarpro’ et découvrir comment gérer vos tables avec l’interface intuitive de PHPMyAdmin. Préparez-vous à maîtriser les fondamentaux des bases de données relationnelles !

Introduction :

Ce cours a pour but de faire découvrir quelques commandes SQL et le fonctionnement de la base de données MySQL. Nous allons apprendre à créer et mettre à jour une base de données.

1 – MySQL

MySQL est un système de gestion de bases de données relationnelles (SGBDR) open source utilisé pour stocker et gérer des données. C’est l’un des SGBDR les plus populaires au monde. Il est utilisé par de grandes entreprises telles que Google, Facebook, Yahoo ou encore YouTube. MySQL utilise SQL pour créer et gérer des bases de données. En tant que base de données relationnelle, MySQL stocke les données dans des tables organisées en schémas. MySQL est disponible en 2 principales versions : une version gratuite (Open Source Community Edition) et une version payante (Enterprise Edition). La version gratuite peut être utilisée à travers PHPMyAdmin ou MySQL Workbench. Nous allons partir sur PHPMyAdmin. C’est un logiciel libre conçu pour gérer l’administration de MySQL via le navigateur. Nous aurons besoin d’un serveur pour pouvoir utiliser MySQL dans PHPMyAdmin. Le serveur wampserver (pour windows) ou lampserver pour ceux qui utilise Linux. Pour installer wampserver, il faut aller sur https://www.wampserver.com/ et aller dans l’option télécharger.

Après ces étapes, un fichier .exe est téléchargé. Il ne reste qu’à l’installer. Après l’installation, lancer le wampserver en double cliquant dessus. Ensuite, il suffira d’ouvrir notre navigateur d’y écrire localhost. La page ci-dessous va s’ouvrir.

En cliquant sur l’option encadrée en rouge, nous tombons sur la page suivante :

Par défaut le nom d’utilisateur est root sans mot de passe. Veillons à bien choisir MySQL puis cliquer sur connexion qui nous conduira sur la page d’accueil de PHPMyAdmin :

A droit, on a la version de MySQL et l’utilisateur connecté. A gauche, il y a toutes nos bases de données. Pour créer notre base de données, on clique sur « Nouvelle base de données » (à gauche) puis renseigner le nom de la base, rentcarpro :

Une fois que la base de données est créée, on va pouvoir ajouter un certain nombre de tables. Pour ajouter une table, cliquer sur « Créer une nouvelle table » puis renseigner le nom de la table (par exemple agences) et le nombre de colonnes de la table, 5 par exemple :

Ensuite, on arrive sur la page ci-dessous et il ne reste qu’à renseigner les noms des colonnes, leurs types et indiquer la clé primaire s’il y en a :

On peut remarquer que les colonnes n’ont pas de valeurs par défaut et la clé primaire (id_agence) est « Auto Increment » (AI). L’Auto Increment veut dire qu’à chaque fois qu’il y a une ligne d’insérée dans la table, la valeur de la clé primaire va augmenter de 1 à chaque fois (toute seule). Au niveau type VARCHAR on y a mis 255 caractères maximum. Après avoir cliqué sur « Enregistrer », voici sur la figure ci-dessous les informations de la table :

Et pour rajouter des données à l’intérieur de la table, il faut cliquer sur « Insérer » et renseigner les informations :

On peut voir que la clé primaire n’est pas renseignée car elle se remplit toute seule. Nous pouvons ajouter de lignes autant de fois que nous pouvons. L’option « Parcourir » affiche toute la table. Et en rentrant dans « SQL », nous accédons à la page où nous pouvons exécuter des requêtes SQL :

Exercice 1 : Créer dans la base de données rentcarpro la table ‘client’ ci-dessous :
Exercice 2 : Supprimer la ligne de la table agences où la ville est Nice.

2 – Les transactions

Les transactions sont une succession d’opérations nécessitant des accès en lecture ou en écriture et est effectuée via un système de gestion de bases de données. Une transaction ACID est une transaction qui respecte les points suivants :

Atomicité : une transaction doit être effectuée en entier ou abandonnée, c’est à dire que si une opération est refusée, toutes les opérations de la transaction ne seront pas validées. Par exemple, si on crée une réservation dans la table Reservations et qu’on met à jour simultanément le statut d’un véhicule dans la table Vehicules, soit les deux opérations réussissent, soit aucune n’est appliquée.

Cohérence : les contraintes d’intégrité (clés primaires, clés étrangères) doivent être respectées. Par exemple, on ne peut pas créer une réservation avec un id_client qui n’existe pas dans la table Clients car cela violerait la contrainte de clé étrangère.

Isolation : une transaction doit s’effectuer indépendamment des transactions qui s’exécutent en même temps, c’est à dire que les états intermédiaires d’une transaction sont cachés aux autres. Si deux utilisateurs tentent de réserver le même véhicule simultanément, l’isolation garantit qu’ils ne verront pas les modifications non finalisées de l’autre transaction.

Durabilité : une fois qu’une transaction est validée (committed), ses modifications sont permanentes, même en cas de panne système. Si une réservation est confirmée, elle restera dans la base de données même si le serveur redémarre juste après.

La grande majorité des SGBDR vérifient les propriétés ACID des transactions.

3 – Les bases du langage SQL

Maintenant que nous avons mis en marche PHPMyAdmin, créé notre base de données et quelques tables, nous allons apprendre dans cette section les différentes clauses du langage SQL. Nous allons apprendre à extraire des informations pertinentes d’une base de données. Nous allons principalement utiliser l’option ‘SQL’ de la page d’accueil de PHPMyAdmin.

Les requêtes SQL simples

Une requête est une combinaison d’opérations sur des tables. Le résultat d’une requête est aussi une table. Les requêtes d’interrogation s’effectuent via l’opérateur SELECT. L’opérateur qui suit après SELECT dépend de ce que l’on souhaite extraire. Nous devons ensuite préciser la source des données, c’est-à-dire la table dans laquelle celles-ci se trouvent avec le mot-clé FROM suivi du nom de la table.

Par exemple, si nous ne souhaitons obtenir que les colonnes colonne1 et colonne2 de la table table1, nous pouvons utiliser la notation suivante :

SELECT colonne1, colonne2 FROM table1;

Notons que l’ordre des colonnes dans la requête donne l’ordre des colonnes dans la table de résultats. Au contraire, si on veut toutes les colonnes, on peut utiliser * :

SELECT * FROM table1;

Si nous ne voulons que des valeurs uniques et ainsi supprimer les doublons de la table de résultat, il faut alors utiliser le mot-clé DISTINCT. La requête prend nécessairement beaucoup plus de temps :

SELECT DISTINCT * FROM table1;

Exemple : La requête SELECT * FROM agences renvoie une table qui contient tous les tuples de la table agences. S’il y a des doublons dans agences alors ils seront présents dans la table résultante. La clause DISTINCT peut être combinée avec le choix de colonnes. Elle se place avant ce choix. Nous pouvons aussi limiter les résultats grâce au mot-clé LIMIT suivi du nombre de tuples à renvoyer : SELECT * FROM agences LIMIT 5; Cette requête ne retourne que les 5 premiers résultats. NB : un tuple est un vecteur regroupant les informations de la ligne d’une table. Pour la table agences, un tuple regroupe les informations suivantes : (id_agence, code_agence, nom_agence, ville, effectif_personnel). SQL n’est pas sensible à la casse : on peut tout à fait utiliser SELECT ou select sans effet. Toutefois, nous ferons en sorte de garder les mots clés en majuscule pour simplifier la compréhension. Pour créer une base de données rentcarpro par exemple, on utilise les mots clés CREATE DATABASE suivi du nom de la base : CREATE DATABASE rentcarpro; Pour créer la base de données seulement si elle n’existe pas déjà : CREATE DATABASE IF NOT EXISTS rentcarpro; Pour choisir ou utiliser cette base de données : USE rentcarpro; Si nous voulons créer la table clients évoquée plus haut en utilisant une requête SQL plutôt que via l’interface PHPMyAdmin :

CREATE TABLE clients ( id_client INT AUTO_INCREMENT PRIMARY KEY, prenom VARCHAR(255) NOT NULL, nom VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, telephone VARCHAR(20), ville VARCHAR(255), type_client VARCHAR(50) DEFAULT ‘Particulier’ );

Nous avons rajouté certains mots clés tels que :

  • la colonne id_client est désignée comme clé primaire qui s’incrémente automatiquement en utilisant les mots clés AUTO_INCREMENT et PRIMARY KEY
  • NOT NULL : oblige à renseigner le prénom, le nom et l’email
  • UNIQUE : garantit qu’un email ne peut être utilisé qu’une seule fois
  • DEFAULT ‘Particulier’ : définit ‘Particulier’ comme valeur par défaut pour le type_client

Pour insérer les 5 premières lignes dans la table clients, on utilise les mots clés INSERT INTO … VALUES : INSERT INTO clients (prenom, nom, email, telephone, ville, type_client) VALUES (‘Lea’, ‘Durand’, ‘lea.durand@yahoo.fr’, ‘0685623111’, ‘Grenoble’, ‘Particulier’), (‘Luc’, ‘Leroy’, ‘luc_leroy@yahoo.fr’, ‘7593236658’, ‘Nantes’, ‘Particulier’), (‘Marc’, ‘Simon’, ‘marc.simon@outlook.fr’, ‘725681943’, ‘Nice’, ‘Particulier’), (‘Laura’, ‘Moreau’, ‘laura.moreau@orange.fr’, ‘7697447246’, ‘Rennes’, ‘Professionnel’), (‘Julie’, ‘Lefebvre’, ‘julie.lefebvre@gmail.com’, ‘771289636’, ‘Nice’, ‘VIP’);

Nous allons voir un autre exemple de table, reservations, avec des clés primaire et étrangères vers les tables vehicules et clients. CREATE TABLE IF NOT EXISTS reservations ( id_reservation INT AUTO_INCREMENT PRIMARY KEY, code_reservation VARCHAR(50) NOT NULL UNIQUE, id_client INT NOT NULL, id_vehicule INT NOT NULL, id_agence_depart INT, id_agence_retour INT, date_reservation DATETIME, date_debut_souhaitee DATETIME, duree_souhaitee_jours INT, type_contrat_initial VARCHAR(50), statut_reservation VARCHAR(50), source_reservation VARCHAR(100), motif_annulation VARCHAR(255), FOREIGN KEY (id_client) REFERENCES Clients(id_client), FOREIGN KEY (id_vehicule) REFERENCES Vehicules(id_vehicule) ); On note que :

  • la colonne code_reservation VARCHAR(50) NOT NULL, le code de réservation est obligatoire (avec les mots clés NOT NULL)
  • FOREIGN KEY (id_client) REFERENCES Clients(id_client) : définit id_client comme clé étrangère faisant référence à la table Clients
  • FOREIGN KEY (id_vehicule) REFERENCES Vehicules(id_vehicule) : définit id_vehicule comme clé étrangère faisant référence à la table Vehicules

Nous pouvons insérer 5 lignes dans la table reservations : INSERT INTO reservations

(id_reservation, code_reservation, id_client, id_vehicule, id_agence_depart, id_agence_retour, date_reservation, date_debut_souhaitee, duree_souhaitee_jours, type_contrat_initial, statut_reservation, source_reservation, motif_annulation) VALUES (‘RES-000001’, 84, 151, 5, 5, ‘2024-07-18 13:33:00’, ‘2024-07-31 13:33:00’, 13, ‘JD’, ‘Confirmée’, ‘Téléphone’, NULL), (‘RES-000002’, 425, 43, 4, 4, ‘2024-05-27 10:38:00’, ‘2024-05-31 10:38:00’, 19, ‘MD’, ‘Confirmée’, ‘Agence Physique’, NULL), (‘RES-000003’, 362, 57, 7, 7, ‘2024-10-14 11:25:00’, ‘2024-10-28 11:25:00’, 5, ‘CD’, ‘Confirmée’, ‘Application Mobile’, NULL), (‘RES-000004’, 410, 78, 3, 3, ‘2024-12-15 17:41:00’, ‘2024-12-25 17:41:00’, 22, ‘MD’, ‘Confirmée’, ‘Téléphone’, NULL), (‘RES-000005’, 347, 130, 2, 2, ‘2024-04-26 10:32:00’, ‘2024-04-27 10:32:00’, 18, ‘JD’, ‘Confirmée’, ‘Partenaire’, NULL);

Utilisation de WHERE

Pour spécifier une condition au niveau d’une colonne de la table on peut utiliser le mot-clé WHERE. Il est suivi d’une condition sur les valeurs prises par les colonnes de la table que l’on appelle prédicat. Le prédicat peut prendre plusieurs formes, en voici quelques unes :

  • Comparaison simple : utilisation d’opérateurs tels que =, !=, <, >, <=, >= pour comparer une colonne à une valeur.
  • Conditions multiples : combinaison de plusieurs prédicats à l’aide des opérateurs logiques AND, OR et NOT.
  • Appartenance à un ensemble : utilisation du mot-clé IN pour vérifier si une valeur appartient à une liste donnée.
  • Intervalle de valeurs : utilisation de BETWEEN pour tester si une valeur se situe dans un intervalle.
  • Recherche de motifs : utilisation de LIKE pour effectuer des recherches partielles dans des chaînes de caractères.
  • Valeurs nulles : utilisation de IS NULL ou IS NOT NULL pour tester la présence ou l’absence de valeur.

Ces différentes formes de prédicats permettent d’exprimer des conditions précises et adaptées aux besoins de sélection des données. Par exemple, si l’on souhaite obtenir toutes les lignes pour lesquelles la colonne colonne1 vaut la valeur valeur1, on peut faire :

SELECT * FROM table1 WHERE colonne1=valeur1;

Exemples :

  • Récupérer tous les clients de type VIP

SELECT * FROM clients WHERE type_client = ‘VIP’;

  • Trouver tous les clients habitant à Paris

SELECT * FROM clients WHERE ville = ‘Paris’;

  • Trouver les clients de Nantes qui sont VIP

SELECT * FROM clients WHERE ville = ‘Nantes’ AND type_client = ‘VIP’;

  • Récupérer tous les véhicules de type SUV

SELECT * FROM vehicules WHERE type_vehicule = ‘SUV’;

  • Récupérer tous les véhicules qui ne sont pas des citadines

SELECT * FROM Vehicules WHERE type_vehicule != ‘Citadine’;

Les expressions régulières

Les expressions régulières permettent d’identifier des agencements de lettres ou mots dans des chaînes de caractères. C’est un outil très utile pour effectuer des recherches de modèles dans une chaîne de caractères. On présente ici la façon la plus flexible d’utiliser des expressions régulières avec SQL, à savoir l’utilisation du mot clé LIKE et des Wildcards. On distingue 2 types de caractères spéciaux comme Wildcards :

  • le % désigne n’importe quelle combinaison de caractère de n’importe quelle longueur.
  • le _ désigne n’importe quel caractère unique.

LIKE s’utilise dans le prédicat de la clause WHERE et est suivi d’un modèle entre apostrophes. P% aura une correspondance avec n’importe quelle chaîne de caractères qui commence par un P. On peut aussi utiliser le modèle %P% qui aura une correspondance avec n’importe quelle chaîne de caractères qui contient un P. Dans une requête SQL, cela donne : SELECT * FROM table1 WHERE colonne1 LIKE ‘%P’; A l’aide de ces deux Wildcards, on peut déjà effectuer un large éventail de recherches de modèles. Si on souhaite aller plus loin, on peut faire appel aux expressions régulières qui ont un format qui leur est propre. Exemple : Trouver tous les véhicules de marque Peugeot SELECT * FROM vehicules WHERE modele_vehicule LIKE ‘Peugeot%’; Voici un tableau récapitulatif des caractères spéciaux pour les expressions régulières :

Expression régulière Description
. correspond à n’importe quel caractère seul
* correspond à n’importe quel caractère
+ correspond à au moins une instance de l’expression précédente
^ correspond à un commencement en début de ligne
$ recherche à la fin de la ligne
< correspondance uniquement si le mot commence en ce point
> correspondance uniquement si le mot finit en ce point
\n correspondance si il y a un retour à la ligne
[] correspondance si il y a présence de n’importe quel des caractères entre crochets
[^…] correspondance avec n’importe quel caractère listé après ^
[ABQ]% la chaîne de caractères doit commencer par un A, B ou Q et peut être de n’importe quelle longueur
[AB][CD]% la chaîne de caractères doit commencer par un A ou B et le deuxième caractère doit être C ou D
[A-Z]% la chaîne doit commencer par un caractère de A à Z et peut être de n’importe quelle longueur
[A-Z0-9]% la chaîne doit commencer par une lettre de A à Z ou un chiffre de 0 à 9 et peut être de n’importe quelle longueur
[^A-C]% la chaîne ne peut pas commencer par un caractère de A à C mais peut être de n’importe quelle longueur
%[A-Z] la chaîne de caractère doit se terminer par un caractère de A à Z et peut être de n’importe quelle longueur
%[%$#@]% la chaîne de caractères peut être de n’importe quelle longueur et doit au moins avoir une correspondance avec un caractère entre crochets

Exemples :

  • Trouver tous les clients dont le prénom commence par ‘L’ (résultat : Lea, Luc, Laura) :

SELECT * FROM clients WHERE prenom LIKE ‘L%’;

  • Trouver tous les clients dont le nom se termine par ‘and’ (résultat : Durand)

SELECT * FROM clients WHERE nom LIKE ‘%and’;

  • Trouver tous les clients avec un email Outlook (résultat : marc.simon@outlook.fr, pierre.thomas@outlook.fr)

SELECT * FROM Clients WHERE email LIKE ‘%@outlook.fr%’;

Utilisation de ORDER BY

On peut spécifier l’ordre des résultats à l’aide du mot-clé ORDER BY. L’ordre dans lequel sont rangées les données est ascendant par défaut. On peut spécifier si les valeurs doivent être ordonnées de manière décroissante en ajoutant à la suite le mot-clé DESC. Il est possible de trier les données sur une ou plusieurs colonnes, par ordre ascendant ou descendant. On peut ainsi avoir la requête suivante :

SELECT * FROM table1 ORDER BY colonne1;
SELECT * FROM table1 ORDER BY colonne1 DESC;

Exemples :

  • Trier les clients par ordre alphabétique de nom (A à Z)
SELECT * FROM clients ORDER BY nom;
  • Trier les clients par ordre alphabétique décroissant de nom (Z à A)
SELECT * FROM clients ORDER BY nom DESC;
  • Trier les clients par prénom
SELECT * FROM clients ORDER BY prenom;
  • Trier d’abord par ville, puis par nom dans chaque ville
SELECT * FROM clients ORDER BY ville, nom;
  • Trier par type de client (croissant), puis par nom (décroissant)
SELECT * FROM clients ORDER BY type_client ASC, nom DESC;

Agrégation de requête

Pour agréger des données selon une colonne, on peut utiliser le mot-clé GROUP BY suivi de la colonne d’agrégation. On peut alors préciser des fonctions d’agrégation sur les différentes colonnes que l’on souhaite garder. Ceci est très pratique lorsqu’on essaie de récupérer des statistiques dans une base de données. Par exemple, pour agréger nos résultats par rapport à la colonne colonne2 et récupérer pour chacune des valeurs de cette colonne la moyenne de la colonne colonne1, on peut faire :

SELECT AVG(colonne1) FROM table1 GROUP BY colonne2;

Dans le tableau ci-dessous, nous avons mis les fonctions d’agrégation les plus utilisées sur des valeurs numériques :

Fonction Description
AVG la moyenne
COUNT le nombre d’élément
MAX le maximum
MIN le minimum
STDDEV l’écart-type
SUM la somme
VARIANCE la variance

N.B. : En précisant comme nom de colonne une fonction d’agrégats appliquée à une colonne, la table résultante va créer une nouvelle colonne. Quand on utilise GROUP BY, la statistique utilisée s’applique à chacun des agrégats. Le mot clé AS est utilisé pour renommer une table ou une colonne de table. C’est ce qu’on appelle l’Alias.

Exemples :

  • Compter le nombre total de clients
SELECT COUNT(*) as nombre_total_clients FROM clients;
  • Compter le nombre de clients VIP
SELECT COUNT(*) as nombre_vip FROM clients WHERE type_client = ‘VIP’;
  • Compter le nombre de clients par ville
SELECT ville, COUNT(*) as nombre_clients FROM clients GROUP BY ville;
  • Nombre de clients par ville, trié par nombre décroissant
SELECT ville, COUNT(*) as nombre_clients FROM clients GROUP BY ville ORDER BY nombre_clients DESC;

Utilisation de HAVING

Cette clause fonctionne exactement comme la clause WHERE mais pour des fonctions d’agrégats. Elle permet de spécifier un prédicat sur le résultat d’un GROUP BY, elle est donc forcément précédée par cette dernière clause. Si on utilise la clause WHERE, on élimine des lignes avant l’agrégation.

Exemples :

  • Trouver les villes ayant plus de 2 clients
SELECT ville, COUNT(*) as nombre_clients FROM Clients GROUP BY ville HAVING COUNT(*) > 2;
  • Trouver les types de clients avec au moins 5 personnes
SELECT type_client, COUNT(*) as effectif FROM Clients GROUP BY type_client HAVING COUNT(*) >= 5;
  • Trouver les clients ayant fait plus de 3 réservations
SELECT id_client, COUNT(*) as nombre_reservations FROM Reservations GROUP BY id_client HAVING COUNT(*) > 3;
  • Trouver les véhicules réservés au moins 2 fois
SELECT id_vehicule, COUNT(*) as nombre_locations FROM Reservations GROUP BY id_vehicule HAVING COUNT(*) >= 2;

Exercices

Exercice 1 : Écrivez une requête pour afficher tous les clients de type « VIP » habitant à Rennes.
Exercice 2 : Écrivez une requête pour trouver tous les clients dont l’email se termine par « @gmail.com ».
Exercice 3 : Écrivez une requête pour afficher tous les véhicules triés par type de véhicule (ordre alphabétique), puis par modèle.
Exercice 4 : Écrivez une requête pour compter le nombre total de réservations ayant le statut « Confirmée ».
Exercice 5 : Écrivez une requête pour afficher le nombre de véhicules par type de véhicule.
Exercice 6 : Écrivez une requête pour afficher les villes qui ont plus de 2 clients, triées par nombre de clients décroissant.
Exercice 7 : Écrivez une requête pour afficher les types de clients (VIP ou Entreprise uniquement) ayant fait au moins 3 réservations, avec le nombre de réservations pour chaque type.
Exercice 8 : Écrivez une requête pour afficher les 5 véhicules ayant le coefficient d’utilisation le plus élevé, avec leur modèle et leur coefficient.

4 – Conclusion

Dans ce cours, nous avons découvert les bases de données relationnelles, MySQL et leur utilisation avec WampServer. Nous avons appris comment se connecter à une base de données et exécuter des requêtes à l’aide de phpMyAdmin. Nous avons également vu les bases du langage SQL, ce qui nous permet maintenant de récupérer des informations utiles à partir d’une base de données.

À retenir :

  • La commande SELECT retourne des enregistrements dans une table de résultat.
  • La commande WHERE dans une requête SQL permet d’extraire les lignes d’une base de données qui respectent un prédicat.
  • La commande ORDER BY permet de trier les lignes dans un résultat d’une requête SQL.
  • La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats et utiliser une fonction de totaux sur un groupe de résultat.
  • La commande HAVING permet de filtrer en utilisant des fonctions telles que SUM(), COUNT(), AVG(), MIN() ou MAX() utilisées par la commande GROUP BY.
  • L’opérateur LIKE est utilisé dans la clause WHERE des requêtes SQL. A l’aide de Wildcards, on peut rechercher des agencements de mots ou lettres dans les chaînes de caractères d’attributs.
PrécédentSuivant