RÉSUMÉ THÉORIQUE – FILIÈRE INTELLIGENCE ARTIFICIELLE

M103 – Manipuler les bases de données

120 Heures
 1. Concevoir une base de données
Analyser le cahier de charges
SOMMAIRE Modéliser les données
Normaliser les données
Préparer l'environnement
2. Manipuler un SGBD relationnel
Créer une base de données
Réaliser des requêtes SQL
Administrer une base de données
3. Exploiter les fonctionnalités avancées
d'un SGBD relationnel
Maîtriser le langage de programmation procédurale
Optimiser une base de données MySQL
4. S'initier aux bases de données NoSQL
Définir les bases de données NoSQL
Identifier les caractéristiques des BD NoSQL
MODALITÉS PÉDAGOGIQUES

1 2 3 4 5
LE GUIDE DE LA VERSION PDF DES CONTENUS DU CONTENU DES RESSOURCES
SOUTIEN Une version PDF TÉLÉCHARGEABLES INTERACTIF EN LIGNES
Il contient le est mise en ligne Les fiches de Vous disposez de Les ressources sont
résumé théorique sur l'espace résumés ou des contenus consultables en
et le manuel des apprenant et exercices sont interactifs sous synchrone et en
travaux pratiques formateur de la téléchargeables forme d'exercices asynchrone pour
plateforme sur WebForce Life et de cours à s'adapter au
WebForce Life utiliser sur rythme de
WebForce Life l'apprentissage

Copyright - Tout droit réservé - OFPPT 3

 PARTIE 1
Concevoir une base de données

Dans ce module, vous allez :

• Analyser les données d'un cahier de charges
• Construire des modèles conceptuels (MCD) et logiques (MLD)
de données
• Maîtriser la normalisation
• Connaître les règles de passage du MCD au MLD normalisé
• Préparer l'environnement

40 Heures
 CHAPITRE 1
ANALYSER UN CAHIER DE CHARGES

Ce que vous allez apprendre dans ce chapitre :

• La lecture et l'analyse d'un cahier de charges

• L'identification des limites du projet
• L'analyse des données et des traitements de la situation
présentée

05 Heures
 CHAPITRE 1
ANALYSER UN CAHIER DE CHARGES

1. Lecture d'un cahier de charges

2. Description des limites du projet
3. Analyse des données et des traitements de la situation
présentée
 01 - ANALYSER UN CAHIER DE CHARGES
Lecture d'un cahier de charges

Introduction
• Dans chaque organisation, Il y'a une quantité importante d'informations qui sont échangées afin d'assurer le bon fonctionnement de cette
organisation ainsi que la communication avec son environnement, dans le but d'être utilisables dans les activités opérationnelles quotidiennes
ou encore dans la prise de décision. Ces informations doivent êtres bien organisées et stockées. Il est donc nécessaire pour chaque organisme
d'avoir une structure fonctionnelle et technique de gestion de l'information.
Le système d'information (SI)
• Le système d'information représente l'ensemble des éléments participants aux activités d'acquérir, de stocker, de traiter et de communiquer les
informations au sein d'une organisation. Il se compose des acteurs suivants :

Les individus Le matériel Les logiciels et les procédures Les données

• En plus des spécialistes des SI chargés • Il s'agit de tout dispositif • Ce sont les programmes qui • Elles constituent la
de la conception, la mise en œuvre et la physique permettant sont nécessaires au matière première des
gestion du SI, cette catégorie comprend d'émettre, manipuler ou fonctionnement du SI ainsi traitements, qu'elles
aussi les personnes qui utilisent ce stocker l'information. que les procédures qui soient saisies, déduites
dernier pour acquérir, communiquer, gèrent les traitements ou calculées.
PARTIE 1

stocker ou traiter des informations. manuels et automatisés.

• Dans le cadre d'un système d'information, un projet informatique a pour objectif de construire une application informatique (logiciel et base de
données) qui va servir comme un support informatisé, inclus dans un système d'information organisationnel.

Copyright - Tout droit réservé - OFPPT 7

 01 - ANALYSER UN CAHIER DE CHARGES
Lecture d'un cahier de charges

Définitions : Un projet informatique

• Un projet informatique est un projet dont les livrables sont des outils ou services informatiques (logiciels, systèmes d'information, sites
web, etc.). Il s'agit de projets généralement complexes. Ceci est principalement dû à la grande diversité des intervenants (techniciens,
responsables métier, marketeurs, gestionnaires, etc.) ainsi qu'à la difficulté de définir toutes les exigences.
• Le processus de développement d'un projet informatique passe par 5 phases :

1 Elaboration du schéma directeur

Il s'agit d'une étude globale du système d'information à construire. Le but de cette étape est de réaliser le schéma directeur ainsi que le
plan de développement informatique
2 Etude préalable
Il s'agit d'une étude critique de l'existant et de la définition des objectifs du nouveau système. Le but de cette étape est de produire
un dossier d'étude et la prise de décision du choix de la solution.
3 Etude détaillée
Il s'agit de fournir avec précision la description de la solution souhaitée : Définir logiquement les données et les traitements
informatiques, les interfaces, le matériel, etc. et construire le planning de réalisation. Le but de cette étape est de produire un
PARTIE 1

cahier de charges fonctionnel et technique.

4 Réalisation
Il consiste à la production du logiciel, l'implantation des bases de données et la mise en place de la solution.
5 Mise en œuvre de la solution et maintenance
Adapter la solution aux évolutions de l'environnement.
Copyright - Tout droit réservé - OFPPT 8
 01 - ANALYSER UN CAHIER DE CHARGES
Lecture d'un cahier de charges

Définitions : Un cahier de charges

• Le cahier de charges est un document essentiel à l'élaboration et la réalisation d'un projet. Il s'agit du document sur lequel les développeurs
se basent pour concevoir et implémenter une base de données.
• Il présente une description détaillée du besoin des utilisateurs à savoir :
• Le contexte général
• L'objectif du projet
• Les fonctionnalités attendues
• Les flux d'information et les processus métier
• Les règles de gestions des données
• Il existe deux types de cahier de charges :
Le cahier de charges technique (CDCT)
• Il contient les exigences et contraintes techniques, économiques, industrielles, environnementales et
matérielles d'un projet. Il sert à définir l'environnement technique : Architecture technique, les outils à utiliser,
PARTIE 1

les technologies..

Le cahier de charges fonctionnel (CDCF)

• Il décrit la structure, les besoins et les fonctionnalités attendues du maître d'ouvrage. Il contient les
informations qui permettent d'addresser les exigences liées au projet en précisant les conditions de réalisation.
Le CDCF doit comporter assez de détails pour être compréhensible par tous les acteurs du projet.
Copyright - Tout droit réservé - OFPPT 9
 01 - ANALYSER UN CAHIER DE CHARGES
Lecture d'un cahier de charges

La structure d'un cahier de charges

Un cahier de charge se compose de cinq éléments essentiels :

1- Contexte et présentation du projet

• On commence par présenter l'entreprise et l'importance du projet dans son plan stratégique. On définit
aussi les acteurs cibles, les objectifs et le périmètre du projet. Cette partie contient aussi la description de
l'existant (si d'autres implémentations existent déjà)
Exemples :

Présenter l'entreprise Présenter le projet Définir le périmètre

• Le groupe Hospitalier SantéPro se • Refonte d'un système d'information • La plateforme est utilisée par les
compose de 4 hopitaux. Sa mission est hospitalier dans le but de : différents hôpitaux du groupe, répartis
de fournir des services de santé pour les • Augmenter la productivité du sur la région. Il s'agit de plus de 2000
PARTIE 1

habitants de la région. personnel ; utilisateurs qui accèdent de manière

• Collecter plus d'informations depuis journalière.
les différents processus ;
• Minimiser le délais d'attente des
patients.

Copyright - Tout droit réservé - OFPPT 10

 01 - ANALYSER UN CAHIER DE CHARGES
Lecture d'un cahier de charges

La structure d'un cahier de charges : Suite

4- Définition des résultats 5- Budgétisation et fixation

2- Description graphique et 3- Description fonctionnelle et attendus des délais
ergonomique technique
• On présente dans ce stade • Cette phase concerne
• On y décrit la charte • Cette étape décrit les toutes les prestations l'estimation du budget
graphique ainsi que tous les spécifications techniques et attendues à la fin du projet global permettant d'aiguiller
éléments graphiques et fonctionnelles des livrables. les potentiels prestataires
ainsi que les délais de
ergonomiques exigés relatifs • Exemples : livraison. pour la réalisation de leurs
au nouveau projet. • Plateforme technique devis.
• Exemples :
• Exemples : • Technologies de • Exemples :
• Exécutables /packages.
• le logo développement • Un délais de réalisation de
• Serveur web configuré et
• la typographie • Sécurité 200 jours ouvrables
PARTIE 1

installé sur les lieux

• les couleurs • Données à collecter • Un budget global de 1M de
• les illustrations • Règles de gestion Dirhams.

Copyright - Tout droit réservé - OFPPT 11

 01 - ANALYSER UN CAHIER DE CHARGES
Lecture d'un cahier de charges

Exemple d'un cahier de charges

L'exemple ci-contre illustre les informations que l'on peut

indiquer sur la page de couverture ainsi que les
rubriques qui y figurent sur le sommaire.
PARTIE 1

Exemple de cahier de charges pour un développement logiciel

Copyright - Tout droit réservé - OFPPT 12

 CHAPITRE 1
ANALYSER UN CAHIER DE CHARGES

1. Lecture d'un cahier de charges

2. Description des limites du projet
3. Analyse des données et des traitements de la situation
présentée
 01 - ANALYSER UN CAHIER DE CHARGES
Description des limites du projet

Introduction

• Le cahier de charges représente les attentes et les besoins du client ainsi que les contraintes du client.
• En procédant à la lecture du cahier de charges, il faut définir le périmètre du projet :
• Le contexte du projet.
• L'ensemble des données que le système est supposé gérer et stocker.
• Les conditions et règles de gestion exprimées par le client.

Il faut aussi définir les limites du projet et répondre aux questions suivantes :
• Quels sont les éléments de données cités par le document ?
• Qui fait quoi ?
PARTIE 1

• Quelles sont les données qui peuvent/doivent être précisées ?

• Quand s'arrêter ? A-t-on tout pris en compte ?
• Quelles limitations présente la situation actuelle ?

Copyright - Tout droit réservé - OFPPT 14

 01 - ANALYSER UN CAHIER DE CHARGES
Description des limites du projet

Périmètre d'un projet

• Le périmètre du projet correspond à la délimitation précise du projet. Il s'agit de la liste des objectifs, des produits livrables, des affectations,
des dépenses et des délais qui doivent être respectés. Ces termes de références définissent aussi les limites du projet. Si le périmètre d'un
projet est efficacement tracé, la gestion des améliorations qui surviennent lors de la mise en œuvre et de la maintenance devient plus simple.
• Concernant un projet lié aux SI (mise en place d'un nouvel ERP, évolution d'un SI en fonction d'une nouvelle organisation, développement
d'une plateforme web, etc.), le périmètre total est l'identification et le recensement des applications/modules impactés par le projet.
• A partir du cahier de charges ainsi que des échanges avec les porteurs du projet, on peut définir le périmètre et dresser les limites du projet en
suivant les étapes suivantes :

1. Définir les buts 2. Définir les livrables

Il s'agit des objectifs à réaliser par le biais du projet. Il faut identifier les résultats attendus du projet : c'est-à-
dire l'ensemble des livrables. L'identification des
PARTIE 1

• Exemple : Dans le cas d'un projet d'informatisation des

activités d'un centre de formation, le but serait livrables permet de détecter les dérivées des objectifs
de créer des formulaires d'inscriptions pour les si celles-ci surviennent.
étudiants qui vont simplifier les processus d'inscription • Exemple : Quelles choses tangibles on doit créer pour
et leur prise en charge et de suite assurer l'accès aux le compte du client (Centre de formation) ? Dans ce
informations nécessaires à la bonne gestion du centre. cas, il s'agit du formulaire informatisé ainsi que la base
de données des inscriptions.
Copyright - Tout droit réservé - OFPPT 15
 01 - ANALYSER UN CAHIER DE CHARGES
Description des limites du projet

Périmètre d'un projet

3. Définir les tâches et les activités du projet 4. Définir les contraintes du projet
• Il s'agit des moyens qui vont permettre la création des • Les trois principales contraintes d'un projet sont le budget, le temps et la
livrables et la réalisation des buts du projet. Les livrables portée :
sont ainsi découpés en tâches et activités distinctes. Ceci • Contrainte de budget ou coût : L'ensemble des ressources financières (frais
permet de faciliter la gestion des projets surtout quand la des matériels, services et ressources humaines) nécessaires pour la
complexité est importante. réalisation du projet dans le respect des limites et délais prédéfinis.
• Exemple : Pour créer le formulaire d'inscription on doit : • Contrainte de temps : Le calendrier de livraison du projet en totalité ainsi
• Choisir et rédiger un exemplaire du formulaire que des différentes phases du projet.
• Développer une interface de saisie du formulaire • Contrainte de portée : La définition des objectifs, des livrables, des
• Créer une base de données pour stocker les informations fonctionnalités et des tâches à accomplir pour la finalisation du projet.
du formulaire • Exemple : Voici quelques contraintes applicables à votre questionnaire client :
PARTIE 1

• Le projet doit être bouclé en 6 mois

• Le budget total pour le projet ne doit pas dépasser 50 000 Dirhams
• L'équipe de développement ne pourra pas finaliser la conception dans 3 mois

Copyright - Tout droit réservé - OFPPT 16

 CHAPITRE 1
ANALYSER UN CAHIER DE CHARGES

1. Lecture d'un cahier de charges

2. Description des limites du projet
3. Analyse des données et des traitements de la situation
présentée
 01 - ANALYSER UN CAHIER DE CHARGES
Analyse des données et des traitements de la situation présentée

Introduction

• On inclut souvent dans un projet informatique : les bases de données et le système informatique comprenant les ressources et infrastructures
réseau, les applications et aussi les règles et dispositifs de sécurité.
• L'élaboration des bases de données afin de gérer l'accès aux données, le stockage et le traitement représentent un pilier du livrable d'un projet.
• Le cahier de charges relatif à un projet informatique indique les différents volets qui concernent ce projet, notamment la gestion des données.

Comment, à partir de ce cahier de charges, élaborer la solution souhaitée ?

• Il faut utiliser des méthodes de modélisation et de conception du système et de la base de données.

• Dans ce qui suit, on s'intéresse au volet du cahier de charges qui concerne la base de données.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 18

 01 - ANALYSER UN CAHIER DE CHARGES
Analyse des données et des traitements de la situation présentée

Définition
• Une des fonctions d'un système informatique dans une organisation est de stocker et gérer les données nécessaires à son bon fonctionnement,
d'où la nécessité du concept des bases de données.

Une base de données

est une structure permettant de stocker un grand
nombre d'informations afin d'en faciliter l'utilisation.

Objectifs
• Centraliser le stockage des informations
• Faciliter l'accès à l'information
PARTIE 1

• Assurer la justesse et la cohérence des informations stockées surtout lors des modifications
• Garantir l'intégrité et la confidentialité des données

Copyright - Tout droit réservé - OFPPT 19

 01 - ANALYSER UN CAHIER DE CHARGES
Analyse des données et des traitements de la situation présentée

Phases de conception d'une base de données

• La conception d'une base de données passe par quatre phases comme illustré par le schéma :
1. Analyse du cahier de charges et clarification du besoin du client.
2. Conception d'un modèle conceptuel qui représente tous les éléments nécessaires du projet.
3. Traduction du modèle conceptuel en modèle logique.
4. Implémentation de la base de données proposée.

• Dans le schéma ci-contre, la première étape de la conception d'une base de données se base sur l'analyse pertinente du cahier de charges et la
bonne compréhension des besoins exprimés par les utilisateurs. Elle est essentielle et délicate en même temps.

1. Analyse 2. Conception d'un MCD 3. Traduction en MLD 4. Implémentation

PARTIE 1

Représenter Traduire Implémenter

Cahier de charges Modèle conceptuel Modèle logique BD – Solution proposée

Les phases de conception d'une base de données

Copyright - Tout droit réservé - OFPPT 20
 01 - ANALYSER UN CAHIER DE CHARGES
Analyse des données et des traitements de la situation présentée

Exemple : Cahier de charges du projet « Gestion d'un centre de formation »

• Un centre de formation désire stocker et gérer des données Définition de l'objectif de la base de données :
concernant les étudiants et les formations dans lesquelles ils • Gérer les données des étudiants, formations
sont inscrits. Le travail demandé est la modélisation des et inscriptions.
données persistantes et la représentation sous forme
tabulaire de ces données telles qu'elles seront stockées dans
la base de données.

Définition des processus métier :

• Les étudiants choisissent la formation et la session de cette • Gérer les données des étudiants, formations
formation dans laquelle ils veulent s'inscrire et payent le prix et inscriptions.
de la formation.

• Un étudiant est définit par son numéro de CIN. Il est, lors de

son inscription, amené à remplir une fiche contenant son
nom et prénom, sa date de naissance, son adresse, sa ville et Définition des données de la base de données :
PARTIE 1

son niveau scolaire. • Étudiants (CIN, nom, prénom, etc.).

• Depuis le catalogue des formations, il doit choisir la • Formations, sessions, type de cours, etc.
formation souhaitée, et la session relative à cette formation.
Il indique aussi le type de cours qu'il veut suivre (présentiel
ou à distance). Une fiche d'inscription est conservée par
l'administration.
Copyright - Tout droit réservé - OFPPT 21
 01 - ANALYSER UN CAHIER DE CHARGES
Analyse des données et des traitements de la situation présentée

Exemple : Cahier de charges du projet « Gestion d'un centre de formation »

Définition des données de la base de données :

• Pour chaque formation, le catalogue précise le code, le titre, la
durée, le prix et les spécialités (code et nom) qui concernent • Formation (code, titre, durée, prix, etc.)
cette formation ainsi que les sessions ouvertes avec leurs date • Spécialité, etc.
début et date fin. • Session (date début, date fin, etc.)

• Voici quelques règles de gestion mises en œuvre par la direction

du centre :
• Un étudiant peut être inscrit dans plusieurs sessions de
formations. Définition des règles de gestion :
• La formation peut se tenir en plusieurs sessions.
• Conditions et contraintes à respecter lors de
• Un étudiant ne peut pas être inscrit à plusieurs sessions de
PARTIE 1

la modélisation de la base de données.

la même formation.
• Une formation n'est ouverte que s'il y a plus de 10
étudiants inscrits.
• Une formation peut faire partie de plusieurs spécialités.

Copyright - Tout droit réservé - OFPPT 22

 01 - ANALYSER UN CAHIER DE CHARGES
Analyse des données et des traitements de la situation présentée

Exemple : Cahier de charges du projet « Gestion d'un centre de formation »

Les règles de gestion ainsi que les informations collectées permettent de définir les éléments de la base de données qu’on va construire, les
relations entre ces éléments et aussi d'assurer l'intégrité des données :
• Exhaustivité
• Exactitude
• Cohérence des données
Exemple :
• Si l'on veut modéliser les données de ce centre, on va créer un ensemble de tables liées entre elles par des relations :
• Une table (ou entité) ÉTUDIANT qui contiendra des attributs :
• Nom
• Prénom
• Adresse, etc.
• Une table (ou entité) FORMATION qui contiendra des attributs :
• Titre
PARTIE 1

• Durée, etc.
• Une table (ou entité) SESSION qui contiendra des attributs :
• Code Formation
• Date
• Lieu, etc.
Copyright - Tout droit réservé - OFPPT 23
 CHAPITRE 2
MODÉLISER LES DONNÉES

Ce que vous allez apprendre dans ce chapitre :

• L'élaboration des dictionnaires de données

• L'identification des dépendances fonctionnelles
• La construction du Modèle Conceptuel de Données (MCD)

20 heures
 CHAPITRE 2
MODÉLISER LES DONNÉES

1. Contraintes déduites des règles de gestion

2. Dictionnaire des données
3. Construction du graphe de dépendances fonctionnelles
4. Règles de passage du graphe au Modèle Conceptuel de
Données
5. Construction du Modèle Conceptuel de Données
 02 - Modélisation des données
Contraintes déduites des règles de gestion

• Les règles de gestion fournies par le cahier de

charges permettent d'identifier les éléments
de données de la base à concevoir. Ces règles
doivent être traduites en contraintes afin
d'assurer l'intégrité des données et la
validation des modèles à construire.

• Identification des éléments de données :

Exemple du cahier de charges du centre de
formation.

• Les attributs des entités «ÉTUDIANT»,

« FORMATION », « SESSION » et
« SPÉCIALITÉ » peuvent être déduits du texte
ainsi que des fiches de renseignement
PARTIE 1

données en annexe du cahier de charges.

Copyright - Tout droit réservé - OFPPT 26

 02 - Modélisation des données
Contraintes déduites des règles de gestion

• Toujours dans le même sillage de l'exemple du cahier de charges relatif au centre de formation, les contraintes ci-après ont été identifiées :
• Un étudiant peut être inscrit dans plusieurs sessions de formations.
• La formation peut se tenir en plusieurs sessions.
• Un étudiant ne peut pas être inscrit à plusieurs sessions de la même formation.
• Une formation n'est ouverte que s'il y a plus de 10 étudiants inscrits.
• Une formation peut faire partie de plusieurs spécialités.

• Le tableau ci-après récapitule les règles de gestion relatives toujours au même exemple :

Règle N° Énoncé de la règle

1 Un élément de l'entité ÉTUDIANT peut être associé à plusieurs éléments de l'entité SESSION.

2 Un élément de l'entité SESSION concerne un élément unique de l'entité FORMATION.

PARTIE 1

3 Un élément de l'entité FORMATION peut être associé à plusieurs éléments de l'entité SESSION.

4 Un élément de l'entité FORMATION peut être associé à un ou plusieurs éléments de l'entité SPÉCIALITÉ.

Copyright - Tout droit réservé - OFPPT 27

 CHAPITRE 2
MODÉLISER LES DONNÉES

1. Contraintes déduites des règles de gestion

2. Dictionnaire des données
3. Construction du graphe de dépendances fonctionnelles
4. Règles de passage du graphe au Modèle Conceptuel de
Données
5. Construction du Modèle Conceptuel de Données
 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Dictionnaire des données

Avant la phase de conception du modèle conceptuel des données,

il faut relever d'abord trois types d'informations à partir du cahier de charges.

Concept Donnée Valeur

Il s'agit d' « objets » qui vont par la C'est une information élémentaire, qui Il s'agit d'occurrences ou exemples des
suite être des entités du schéma entité- ne peut pas être décomposée. Elle se données d'un concept.
association. Ce sont des éléments trouve souvent liée à un concept.
complexes qui peuvent être Exemples : «G434568» valeur de la
décomposés en plusieurs informations Exemples : CIN, durée de la formation, donnée : numéro de la CIN, relative au
sous formes de « données ». nom de la session, salle, adresse concept : étudiant.
PARTIE 1

étudiant, etc. Contre-exemples : nom de la

Exemples : étudiant, formation, Contre-exemple : numéro (sans formation, numéro de la CIN, étudiant,
session, etc. préciser 'relatif' à qui ou à quoi). etc.
Contre-exemple : CIN, durée de la
formation, nom de la session, etc.

Copyright - Tout droit réservé - OFPPT 29

 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Dictionnaire des données

Afin de réaliser un bon relevé d'informations, il faut :
• Bien identifier un concept : identifier les noms des objets, personnes et types qui ont des données y afférentes.
• Relever uniquement les concepts et données qui concernent le système à concevoir (faire attention aux détails inutiles).

Exemple :
• Le centre de formation « CF excellence » offre des formations aux étudiants des villes suivantes : Tanger, Rabat et Casablanca.
• On constate que :
• « CF excellence » n'est pas une donnée, c'est le nom de l'organisation.
• Formation, étudiant, session, spécialité sont des concepts.
• Il y a une donnée rattachée au concept Étudiant qui est « Nom Ville ».
• Tanger, Rabat et Casablanca sont des valeurs de la donnée « Nom Ville ».
• Il y a un lien entre les concepts Formation et Étudiant.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 30

 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Dictionnaire des données : Définitions

• Le dictionnaire des données contient toutes les données nécessaires qui vont être conservées dans la base de données. Il est souvent présenté
sous forme d'un tableau qui indique pour chaque donnée les informations suivantes :

Le code La désignation Le type de données La taille Observations

• il s'agit d'un libellé • description de la • Alphabétique : lorsque les valeurs de la • elle exprime la • ils peuvent
désignant une donnée. donnée sont composées de caractères longueur des contenir des
donnée. alphabétiques. valeurs. informations
• Numérique : lorsque les valeurs de la complémentaires.
donnée sont composées de nombres.
• Alphanumérique : lorsque les valeurs
PARTIE 1

de la donnée sont composées de

caractères alphabétiques et
numériques.
• Date : quand il s'agit d'une date.
• Booléen : vrai ou faux.

Copyright - Tout droit réservé - OFPPT 31

 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Dictionnaire des données : Définitions

• Après le relevé des données, il faut préciser :

1 - LA NATURE DE 3 - LE CONCEPT AUQUEL

2 - SON RÔLE
CHAQUE DONNÉE ELLE APPARTIENT

Est-ce qu'il s'agit Est-ce qu'elle identifie

Est-ce qu'elle
d'un concept, d'une un concept? Est-ce
appartient à un ou
donnée ou d'une qu'elle est calculée ou
plusieurs concepts ?
valeur ? élémentaire ?
PARTIE 1

Copyright - Tout droit réservé - OFPPT 32

 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Dictionnaire des données : Définitions

• Ensuite, procéder aux contrôles suivants :

Les données calculées :

Polysémies : il s'agit de
Synonymes : il s'agit de elles ne doivent pas figurer
Imprécisions : s'assurer deux données portant le
deux descriptions dans le dictionnaire des
que les champs sont bien même nom mais qui
différentes qui désignent la données, mais il faut plutôt
nommés et décrits. désignent des choses
même donnée. préciser les éléments qui
différentes.
ont permis ce calcul.

Exemple : « Ville » Exemple : « Nom » Exemple : code CIN Par exemple : PRIX
tout court n'est pas relatif a l'étudiant et numéro CIN. TTC = PRIX_HT * TVA
précis. et « Nom » relatif à la • Il faut garder une • Les données à
session. seule description et retenir seront :
• On optera plutôt
pour « Ville • Il faut renommer les supprimer les PRIX_HT et TVA.
deux données en : autres synonymes.
PARTIE 1

étudiant ».
« Nom étudiant » et
« Nom Session ».

Copyright - Tout droit réservé - OFPPT 33

 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Bonnes pratiques
• Afin d'uniformiser la nomenclature des données, on adopte une formule se composant de : objet (en minuscules) + raccourcis de nom du
concept qu'elle représente (première lettre en majuscule).
Exemple :
• Numéro CIN de l'étudiant → numCINEtu
• Titre de la formation → titreForm
Le Dictionnaire des données relatif à notre exemple de la gestion du centre de formation est comme suit : (1/2)

Code donnée Désignation Type Taille Observation

numCINEtu Numéro CIN Alphanumérique 9 Identifiant de l'étudiant
nomEtu Nom de l'étudiant Alphabétique 30
prenomEtu Prénom de l'étudiant Alphabétique 30
PARTIE 1

dateNaissEtu Date de naissance Date

niveauEtu Niveau scolaire Alphanumérique 15
nomVilleEtu Nom de la ville Alphabétique 15
AdresseEtu Adresse de l'étudiant Alphanumérique 90

Copyright - Tout droit réservé - OFPPT 34

 02 - MODÉLISATION DES DONNÉES
Dictionnaire des données

Bonnes pratiques
• Le Dictionnaire des données relatif à notre exemple de la gestion du centre de formation : (2/2)
Code donnée Désignation Type Taille Observation
codeForm Code de la formation Alphanumérique 9 Identifiant de la formation
titreForm Titre de la formation Alphanumérique 30
dureeForm Durée de la formation Numérique 3
prixForm Prix de la formation Numérique 5
codeSess Code de la session Alphanumérique 9 Identifiant de la session
nomSess Nom de la session Alphanumérique 30
dateDebutSess Date du début de la session Date
dateFinSess Date de la fin de la session Date
codeSpec Code de la spécialité Alphanumérique 10
PARTIE 1

nomSpec Nom de la spécialité Alphanumérique 30

descSpec Description de la spécialité Alphanumérique 90
typeCours Type de cours Alphabétique Distanciel ou présentiel

Copyright - Tout droit réservé - OFPPT 35

 CHAPITRE 2
MODÉLISER LES DONNÉES

1. Contraintes déduites des règles de gestion

2. Dictionnaire des données
3. Construction du graphe de dépendances fonctionnelles
4. Règles de passage du graphe au Modèle Conceptuel de
Données
5. Construction du Modèle Conceptuel de Données
 02 - Modélisation des données
Construction du graphe de dépendances fonctionnelles

Dépendances fonctionnelles : Définition

• Après le recensement des données dans un dictionnaire des données, l'étape suivante est celle de découvrir les relations entre ces données.
• Soient deux groupes de données : A (source) et B (cible).
• On entend par « Dépendance Fonctionnelle (DF)», une relation entre les deux groupes de données A et B de telle façon que :
• Un élément du groupe A (source) permet de déterminer un et un seul élément du groupe B (cible).
PARTIE 1

Dépendances entre les éléments des deux ensembles A et B

Copyright - Tout droit réservé - OFPPT 37

 02 - Modélisation des données
Construction du graphe de dépendances fonctionnelles

Dépendances fonctionnelles : Exemple

Code de Formation Titre de Formation Durée Prix

ID01 Introduction au développement 3 mois 2500

CCP01 Python 1 mois 3000

ID02 Introduction au développement 3 mois 2700

BD001 Base de données 1 mois 2500

• Pour le concept Formation, la valeur du Code de Formation ID01 détermine que le titre de la formation est « Introduction au développement ».
• On peut déduire que :
• Le code de formation détermine une seule occurrence du titre de formation.
• Le titre de formation dépend du code de formation.
• Cette relation est symbolisée sous cette forme : codeForm -> titreForm.
PARTIE 1

• Cette relation n'est pas réversible : plusieurs formations peuvent avoir le même titre.

Copyright - Tout droit réservé - OFPPT 38

 02 - Modélisation des données
Construction du graphe de dépendances fonctionnelles

Dépendances fonctionnelles : Liste

• La liste des dépendances fonctionnelles est élaborée à partir du dictionnaire des données.
• Il ne faut retenir que les dépendances directes et donc éliminer les transitivités, c'est-à-dire que si D1 -> D2 et D2-> D3, alors D1->D3 est
obtenue par transitivité et n'est pas, par la suite, directe.

Exemple :
• Voici la liste des dépendances fonctionnelles construite à partir du dictionnaire des données de notre exemple du centre de formation :

SOURCE CIBLE SOURCE CIBLE SOURCE CIBLE

numCINEtu → nomEtu codeSpec → nomSpec codeForm → titreForm

numCINEtu → prenomEtu codeSpec → descSpec codeForm → dureeForm
numCINEtu → dateNaissEtu codeSpec → codeForm codeForm → prixForm
numCINEtu → niveauEtu
numCINEtu → nomVilleEtu codeSess → nomSess
SOURCE CIBLE
numCINEtu → codeSess → codeForm
PARTIE 1

AdresseEtu
codeSess → dateDebutSess numCINEtu + codeSess → typeCours
codeSess → dateFinSess

Copyright - Tout droit réservé - OFPPT 39

 02 - Modélisation des données
Construction du graphe de dépendances fonctionnelles

Dépendances fonctionnelles : Contre-exemple

SOURCE CIBLE REMARQUE

numCINEtu → nomForm 1) Un étudiant peut être inscrit dans plusieurs formation à la fois.

codeForm → nomSpec 2) Une formation peut concerner plusieurs spécialités.

3) NomEtu n'induit pas NomForm, aucune relation directe n'existe entre
NomEtu → NomForm
les deux.

1) Cette dépendance suggère que l'on peut déduire le nom de la formation à partir du code de CIN de l'étudiant. Ceci n'est pas correct dû à la
règle de gestion qui indique qu'un élève peut être inscrit dans plusieurs formations.

2) Cette dépendance suggère que l'on peut déduire le nom de la spécialité à partir du code de la formation. Ceci n'est pas correct dû à la règle
de gestion qui indique qu'une formation peut apparaitre dans le curriculum de plusieurs spécialités.
PARTIE 1

3) Cette dépendance suggère que l'on peut déduire le nom de la formation à partir du nom de l'étudiant. Ceci n'est pas correct car d'une part,
le nom de l'étudiant n'est pas un identifiant, et d'une autre part aucune relation directe n'existe entre ces deux attributs.

Copyright - Tout droit réservé - OFPPT 40

 02 - Modélisation des données
Construction du graphe de dépendances fonctionnelles

Graphe des dépendances fonctionnelles

• Le graphe des dépendances fonctionnelles est la représentation graphique des dépendances fonctionnelles entre les données. Dans le cas du
même exemple, le graphe des dépendances fonctionnelles est le suivant :

TypeCours
numCINEtu codeSess codeForm codeSpec

• On remarque que la donnée typeCours dépend de la combinaison de numCINEtu et codeSess.

Par exemple :
PARTIE 1

• Le prix d'un article varie d'un fournisseur à l'autre.

• La DF sera présentée ainsi : numFou + codeArt -> prixAchat
• Le signe + indique que les données numFou et codeArt doivent être groupées pour obtenir le prix de l'article.

Copyright - Tout droit réservé - OFPPT 41

 CHAPITRE 2
MODÉLISER LES DONNÉES

1. Contraintes déduites des règles de gestion

2. Dictionnaire des données
3. Construction du graphe de dépendances fonctionnelles
4. Règles de passage du graphe au Modèle Conceptuel de
Données
5. Construction du Modèle Conceptuel de Données
 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Définition
• La phase de conception des systèmes d'information, et bien évidemment les bases de données, nécessite le recours à des méthodes de
modélisation. C'est-à-dire la représentation virtuelle des processus et données de telle façon à bien comprendre l'existant et bien définir les
futures livrables.
Il existe plusieurs méthodes d'analyse et de conception. Une des méthodes les plus utilisées étant la méthode MERISE.
Qu'est-ce que la méthode Merise ?
• La méthode Merise (Méthode d'étude et de réalisation informatique pour les systèmes d'entreprise) date de la fin des années 1970 en
France. Il s'agit d'une méthode d'analyse et de conception de systèmes d'information qui se base sur le principe de la séparation des données
et des traitements.
• La méthode Merise propose une démarche basée sur trois niveaux (ou cycles) : la conception, l'organisation et la technique. En effet, modéliser
un système revient à produire une analyse globale de sa fonction : Décrire ce qu'il fait avant de se focaliser sur comment il le fait. Les données
étant séparées des traitements, il faut vérifier la concordance entre données et traitements afin de vérifier que toutes les données nécessaires
aux traitements sont présentes et qu'il n'y a pas de données superflues. Les trois niveaux de représentation des données, sont détaillés ci-
dessous.
Niveau conceptuel : Niveau logique : Niveau physique :
le modèle conceptuel des données le modèle logique des données le modèle physique des données
PARTIE 1

(MCD) décrit les entités du monde (MLD) adapte le modèle conceptuel (MPD) permet d'établir la manière
réel, en terme d'objets, de au contexte organisationnel. Il s'agit concrète dont la base de données
propriétés et de relations, d'une transcription du MCD dans un sera construite.
indépendamment de toute formalisme adapté à une
technique d'organisation et implémentation ultérieure sous
d'implantation des données. forme de base de données.
Copyright - Tout droit réservé - OFPPT 43
 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Le Modèle Conceptuel de Données MCD

• Le modèle conceptuel des données (MCD) formalise les données qui vont être stockées dans la base de données.
• Il s'agit donc d'une représentation des données, facile à comprendre, et qui permet de décrire la base de données à l'aide d'entités. La
description par la méthode des entités association (MERISE) utilise les concepts suivants :
• Entité Utilise Pilote
Parapente
• Association 0,n - Date d'utilisation 0,n - Numéro de licence
- Numéro
• Identifiants - Date de mise en service - Nombre de vols - Nom
- Prénom
• Attributs - Couleur principale
- Date de naissance
• Cardinalité 1,1 - Niveau
Vol - Poids
Est de type
- Date de brevet
- Date du vol - …
- Durée
0,n - Distance
Modèle parapente
PARTIE 1

- Nom Site de décolage

- Niveau Site d'atterrissage
- Nom
- Surface
- Nom - Niveau requis
- Poids Maximum
- Situation - Orientation
- Poids Minimum
- Approche usuelle - …
- …
Exemple d'un MCD
Copyright - Tout droit réservé - OFPPT 44
 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Le Modèle Conceptuel de Données MCD : Entité et attributs

• À partir du Dictionnaire des données, on regroupe les données élémentaires par concept appelé entité. Une entité est un élément unique
décrit par un ensemble de propriétés (aussi appelées attributs). Une de ces propriétés est la source des dépendances fonctionnelles avec le
reste des propriétés. Elle joue le rôle d'un identifiant unique de l'entité.
• Le nom d'une entité est souvent un nom représentant un « objet de gestion ».
• Exemple : Étudiant, Formation, Article, Fournisseur, etc.
• Une entité est formalisée comme suit : Nom de l'entité
- Identifiant
- Propriété 1
- Propriété 2
- …
• Une entité est aussi l'ensemble des occurrences.
Exemple d'occurrences de l'entité FORMATION :
PARTIE 1

ID01 CCP01
Introduction au développement Python
3 30
2500 3000

Copyright - Tout droit réservé - OFPPT 45

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Le Modèle Conceptuel de Données MCD : Association

• Une association est un lien entre deux ou plusieurs entités. Ce lien est défini par des règles de gestion non traduites sous forme d'entité simple.
Une association porteuse peut avoir des propriétés aussi.
• Une association est formalisée comme suit :
Nom de l'association

Liste des données portées

• Les données du dictionnaire des données qui dépendent de plusieurs entités sont mises dans l'association (porteuse) qui relie ces entités. Une
association est dite binaire si elle relie entre deux entités, et tertiaire si elle relie entre trois entités.

Exemple :
• La règle de gestion : « Un étudiant est inscrit dans une session d'une formation » ainsi que « lors de l'inscription l'étudiant choisit le type de
cours » sera représentée par l'association « est inscrit ». Cette association porte la donnée : typeCours.
PARTIE 1

Session
Étudiant
Est inscrit - CodeSess
- numCINEtu
typeCours - nomSess
- nomEtu
- dateDebut
- prenomEtu
- ….

Copyright - Tout droit réservé - OFPPT 46

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Le Modèle Conceptuel de Données MCD : Les cardinalités d'une association

• Les cardinalités indiquent le nombre de fois où une entité est concernée par une association. Elles sont déduites des règles de gestion. Il y a
trois valeurs typiques : 0, 1 et N (plusieurs).

• Les entités liées par une association possèdent chacune deux cardinalités : minimum et maximum. En effet, pour une association entre deux
entités, il y aura quatre cardinalités à définir.

• Les cardinalités sont déduites à partir des règles de gestion. Ces règles sont propres à l'organisation étudiée et expriment des contraintes sur le
modèle.

Remarque :

• Il se peut qu'il y ait des règles de gestion qui imposent un nombre précis de cardinalités. Ceci devra être géré par des traitements
PARTIE 1

supplémentaires.

Copyright - Tout droit réservé - OFPPT 47

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Le Modèle Conceptuel de Données MCD : Les cardinalités d'une association

• Comment définir les cardinalités ?

La cardinalité minimum : La cardinalité maximum :

➢ = 0 si la participation des occurrences d'une entité ➢ = 1 si la participation des occurrences d'une entité
dans l'association est facultative. dans l'association est exclusive.
Exemple : on peut avoir des formations qui n'ont aucune Exemple : une session concerne une et une seule formation.
session programmée.
➢ = N si la participation des occurrences d'une entité
➢ = 1 si la participation des occurrences d'une entité dans l'association est multiple.
dans l'association est obligatoire. Exemple : une formation peut avoir plusieurs sessions.
Exemple : On ne peut pas avoir une session sans qu'elle soit liée
à une formation.
PARTIE 1

Session
Étudiant
1,1 Est inscrit 0,n - CodeSess
- numCINEtu
typeCours - nomSess
- nomEtu
- dateDebut
- prenomEtu
- ….

Copyright - Tout droit réservé - OFPPT 48

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Les cardinalités d'une association

• Voici un tableau récapitulatif des types de cardinalités les plus répandues :

Cardinalité Signification

Au plus un : chaque occurrence de l'entité n'est pas obligatoirement concernée par l'association et si elle l'est, c'est au plus une
0, 1
seule fois.

1, 1 Un et un seul : chaque occurrence de l'entité est concernée par l'association exactement une fois.

Zéro, un ou plusieurs : chaque occurrence de l'entité n'est pas obligatoirement concernée par l'association et si elle l'est, elle
0, N
peut l'être plusieurs fois.

1, N Au moins un : chaque occurrence est concernée par l'association et peut l'être plusieurs fois.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 49

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Règles de passage du graphe DF au MCD

• Le passage du graphe des dépendances fonctionnelles au MCD se fait en respectant les règles suivantes :

RÈGLES OBSERVATIONS

N°1 Toute donnée du graphe devient une propriété. ----------

Chacune des données sources de dépendance
N°2 ----------
fonctionnelle devient l'identifiant d'une entité.
Une dépendance fonctionnelle entre deux données sources
N°3 ----------
se traduit en association non porteuse de propriétés.
Une donnée source de DF qui est relevée de l'association Il s'agit d'une association hiérarchique appelée aussi
N°4 de plusieurs données élémentaires se traduit par une association fonctionnelle ou CIF (Contrainte d'Intégrité
association porteuse de propriétés. Fonctionnelle).
Des associations (issues de dépendances non Il s'agit d'une association non hiérarchique appelée aussi
PARTIE 1

N°5 fonctionnelles) peuvent exister dans un MCD sans pour association non fonctionnelle ou CIM (Contrainte
autant faire partie du graphe des DF. d'Intégrité Multiple).

Copyright - Tout droit réservé - OFPPT 50

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Règles de passage du graphe DF au MCD

• Le graphe des DF permet de construire un Modèle Conceptuel de Données (MCD) fiable. Même si les dépendances fonctionnelles sont la
plupart du temps évidentes et ne nécessitent pas une représentation graphique, le graphe des DF aide toutefois à distinguer entre les futures
éléments du MCD

Type de cours
numCINEtu codeSess codeForm codeSpec
PARTIE 1

Graphe des DF de l'exemple du centre de formation

Copyright - Tout droit réservé - OFPPT 51

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Règles de passage du graphe DF au MCD : Exemple

RÈGLES

N°1 Toute donnée du graphe devient une propriété.

Chacune des données sources de dépendance
N°2
fonctionnelle devient l'identifiant d'une entité.
Une dépendance fonctionnelle entre deux données sources
• En appliquant les règles N°1 et N°2, on peut déduire la liste des N°3
se traduit en association non porteuse de propriétés.
entités avec leurs propriétés et identifiants : Une donnée source de DF qui est relevée de l'association
N°4 de plusieurs données élémentaires se traduit par une
• ÉTUDIANT (numCINEtu, nomEtu, prenomEtu, adresseEtu, etc.) association porteuse de propriétés.
• FORMATION (codeForm, titreForm, dureeForm, prixForm) Des associations (issues de dépendances non
PARTIE 1

N°5 fonctionnelles) peuvent exister dans un MCD sans pour

• SESSION (codeSess, nomSess, dateDebutSess, dateFinSess) autant faire partie du graphe des DF.

• SPÉCIALITÉ (codeSpec, nomSpec, descSpec)

Copyright - Tout droit réservé - OFPPT 52

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Règles de passage du graphe DF au MCD : Exemple

RÈGLES

N°1 Toute donnée du graphe devient une propriété.

Chacune des données sources de dépendance
N°2
fonctionnelle devient l'identifiant d'une entité.
Une dépendance fonctionnelle entre deux données sources
N°3
se traduit en association non porteuse de propriétés.
Une donnée source de DF qui est relevée de l'association
N°4 de plusieurs données élémentaires se traduit par une
association porteuse de propriétés.
• Les règles N°3 et N°4 permettent de déduire les deux Des associations (issues de dépendances non
N°5 fonctionnelles) peuvent exister dans un MCD sans pour
associations fonctionnelles : Concerne (règle N°3 ) - est Inscrit
autant faire partie du graphe des DF.
(règle N°4)
PARTIE 1

Session Formation
Étudiant
0,n Est inscrit 0,n - CodeSess 1,1 Concerne 0,n - CodeForm
- numCINEtu
typeCours - nomSess - titreForm
- nomEtu
- dateDebut - dureeForm
- prenomEtu
- …. - ….

Copyright - Tout droit réservé - OFPPT 53

 02 - Modélisation des données
Règles de passage du graphe DF au MCD

Règles de passage du graphe DF au MCD : Exemple

RÈGLES

N°1 Toute donnée du graphe devient une propriété.

Chacune des données sources de dépendance
N°2
fonctionnelle devient l'identifiant d'une entité.
Une dépendance fonctionnelle entre deux données
N°3 sources se traduit en association non porteuse de
propriétés.
Une donnée source de DF qui est relevée de l'association
N°4 de plusieurs données élémentaires se traduit par une
association porteuse de propriétés.
• En appliquant la règle N°5, on définit l'association non fonctionnelle entre Des associations (issues de dépendances non
l'entité FORMATION et SPECIALITE : N°5 fonctionnelles) peuvent exister dans un MCD sans pour
• Une formation peut appartenir à plusieurs specialités, chaque specialité autant faire partie du graphe des DF.
PARTIE 1

peut contenir plusieurs formations

Formation
Spécialité
- CodeForm 1,n Appartient 0,n
- CodeSpec
- titreForm
- nomSpec
- dureeForm
- ….
- ….
Copyright - Tout droit réservé - OFPPT 54
 CHAPITRE 2
MODÉLISER LES DONNÉES

1. Contraintes déduites des règles de gestion

2. Dictionnaire des données
3. Construction du graphe de dépendances fonctionnelles
4. Règles de passage du graphe au Modèle Conceptuel de
Données
5. Construction du Modèle Conceptuel de Données
 02 - Modélisation des données
Construction du modèle conceptuel des données

Démarche pour la construction du MCD

• À ce stade, il est possible d'élaborer le MCD complet à partir des entités et associations ainsi que les données du dictionnaire des données.

• Afin d'assurer la validité du modèle, il faut observer les points suivants :

• Toutes les entités du MCD doivent être reliées à, au moins, une association.

• S'assurer de la conformité du modèle aux contraintes et règles de gestion.

• Évaluer le modèle contre ce que les utilisateurs comprennent.

• En cas de modèle complexe, il faut commencer par les entités et associations élémentaires puis itérer en complétant progressivement
jusqu'à ce que le modèle semble raisonnablement complet.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 56

 02 - Modélisation des données
Construction du modèle conceptuel des données

Démarche pour la construction du MCD : Exemple

• Le MCD correspondant au projet du «Centre de formation» :

Session Formation
Étudiant
0,n Est inscrit 0,n - codeSess 1,1 Concerne 0,n - codeForm
- numCINEtu
typeCours - nomSess - titreForm
- nomEtu
- dateDebut - dureeForm
- prenomEtu
- …. - ….

1,n

Appartient

0,n
Spécialité
PARTIE 1

- codeSpec
- nomSpec
- ….

Copyright - Tout droit réservé - OFPPT 57

 CHAPITRE 3
NORMALISER LES DONNÉES

Ce que vous allez apprendre dans ce chapitre :

• L'identification des différentes formes normales

• La construction du modèle logique des données (MLD)
normalisé

10 heures
 CHAPITRE 3
NORMALISER LES DONNÉES

1. Formes normales
2. Règles de passage du MCD au MLD normalisé
 03 - Normaliser les données
Formes normales

Formes normales

• Les formes normales permettent la décomposition des entités en des relations, sans perdre d'informations, en se basant sur les dépendances
fonctionnelles, dans le but de construire un schéma conceptuel représentant de manière correcte les associations canoniques du monde réel.

• Au niveau de la base de données, ce travail permet d'éviter les redondances et facilite la maintenance des données.

• Il existe différents niveaux de formes normales :

• Première forme normale (1FN ou 1NF)

• Deuxième forme normale (2FN ou 2NF)

• Troisième forme normale (3FN ou 3NF)

• Forme normale de Boyce-Codd (FNBC ou BCNF)

PARTIE 1

• Quatrième forme normale (4FN – NF4)

Copyright - Tout droit réservé - OFPPT 60

 03 - Normaliser les données
Formes normales

Première forme normale (1FN ou 1NF) : Attribut élémentaire

• Une relation est en première forme normale si, et seulement si, tous ses attributs sont atomiques et sont en dépendance fonctionnelle avec
l'identifiant de cette relation. Ceci dit, dans un attribut, on ne peut avoir qu'une seule valeur. Un attribut est atomique s'il ne contient qu'une
seule valeur pour un tuple (une ligne de données), c'est a dire qu'il ne regroupe pas un ensemble de plusieurs valeurs.

Exemple :

• La relation : Formation (codeForm, titreForm, codeSpec, etc.) : Cette relation n'est pas en 1FN car l'attribut « codeSpec » n'est pas en
dépendance fonctionnelle avec l'identifiant codeFom

• La relation : Étudiant (numCINEtu, nomEtu, prenoms, etc.) : Cette relation n'est pas en 1FN si on stocke plusieurs valeurs dans l'attribut
« prenoms ». La forme correcte serait : Étudiant (numCINEtu, nomEtu, prenom1, prenom2, prenom3...).

numCINEtu nomEtu prenom1 prenom2

PARTIE 1

numCINEtu nomEtu prenoms

G683909 Alaoui Mohammed, Amine G683909 Alaoui Mohammed Amine

AB123456 Hilali Nour, laila AB123456 Hilali Nour Laila

.. ..

Copyright - Tout droit réservé - OFPPT 61

 03 - Normaliser les données
Formes normales

Deuxième forme normale (2FN ou 2NF) : Dépendance fonctionnelle élémentaire

• Une relation est en deuxième forme normale si elle vérifie les deux conditions suivantes :

• Être en 1FN.

• Les attributs non clé dépendent de la totalité de la clé, et non d'une partie de la clé.

• Dans le cas échéant, il faut diviser la relation en plusieurs relations regroupant un groupe d'attributs qui vérifieront la dépendance entre chaque
partie de la clé et la clé entière.

Exemple :

• La relation : Inscription (numCINEtu, codeSess, nomEtu, villeEtu...) n'est pas en 2FN.

• Cette relation doit être divisée en deux :

PARTIE 1

• Étudiant (numCINEtu, nomEtu, villeEtu...)

• Inscription (numCINEtu, codeSess)

Copyright - Tout droit réservé - OFPPT 62

 03 - Normaliser les données
Formes normales

Troisième forme normale (3FN ou 3NF) : Dépendance fonctionnelle élémentaire directe

• Une relation est en troisième forme normale si elle vérifie les deux conditions suivantes :

• Être en 2FN.

• Chacun des attributs de la relation ne dépend que de la clé et non pas d'un autre attribut de la relation.

• C'est-à-dire que toutes les dépendances fonctionnelles entre la clé primaire et les autres attributs doivent être directes, et ce pour éliminer les
transitivités et les dépendances entres les attributs non clé.

• Dans le cas échéant, diviser la relation en autant de relations que de dépendances entre attributs non clés.

Exemple :

• La relation : Formation (codeForm, titreForm, codeSpec, nomSpec) n'est pas en 3FN.

PARTIE 1

• Cette relation doit être décomposée en deux :

• Formation (codeForm, titreForm, codeSpec#)

• Spécialité (codeSpec, nomSpec)

Copyright - Tout droit réservé - OFPPT 63

 03 - Normaliser les données
Formes normales

Forme normale de Boyce-Codd (FNBC ou BCNF)

• Une relation est en FNBC si elle vérifie les deux conditions suivantes :

• Être en 3FN.

• Les seules dépendances fonctionnelles élémentaires existantes dans les relations sont celles de la clé vers les attributs non clés.

• Cette règle permet d'éliminer les redondances créées par des dépendances entre parties de clés ainsi que celles déjà éliminées par la 3FN.

Exemple :

• La relation : Commune (commune, ville, région, population) n'est pas en FNBC.

• Si "commune + ville" déterminent la région et la population, on a aussi ville qui détermine région.

• Donc, on doit décomposer cette relation en :

PARTIE 1

• Commune (commune, ville, population)

• Ville (ville, région)

Copyright - Tout droit réservé - OFPPT 64

 03 - Normaliser les données
Formes normales

Quatrième forme normale (4FN – NF4)

• Les trois premières formes normales se focalisent sur des aspects très conceptuels et évidents, mais ne permettent pas d'éliminer toutes les
redondances.

• On fait alors recours aux formes 4FN et NF4 pour ajouter une dimension de traitement de l'information et faciliter la mise à jour des données de
la base.

• Une relation est en quatrième forme normale lorsqu' elle vérifie les deux conditions suivantes :

• Être en 3FN.

• Si, et seulement si, les dépendances multi-valuées élémentaires sont celles dans lesquelles une clé détermine la valeur d'une colonne.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 65

 03 - Normaliser les données
Formes normales

Quatrième forme normale (4FN – NF4) : Exemple

• Dépendances multi-valuées : Voici la liste des modèles, couleurs et versions disponibles d'une voiture :
Modèle Couleur Version
715 Gris Enjoy
715 Gris Excellence
715 blanc Enjoy
620 noir Enjoy
620 blanc Enjoy
620 blanc Excellence
• Pour le même modèle d'une voiture, il peut exister plusieurs couleurs et plusieurs versions. La table ci-dessus illustre toute les combinaisons
possibles pour chaque modèle en termes de couleur et version. En effet, la dépendance entre Modèle et Couleur d'une part, et Modèle et
Version d'autre part est dite dépendance multi-valuée.
• La table ainsi modélisée "Disponibilité(Modèle, Couleur, Version)" présente un inconvénient majeur : si pour un Modèle X, on veut supprimer
PARTIE 1

une valeur de la colonne Version V, il faudra parcourir et supprimer toutes les combinaisons ou Modèle = X et Version = V. Le recours à la forme
4FN permet d'éviter ce genre de problèmes et ainsi on doit décomposer la table en deux relations :
• DispoCouleur : (Modèle, Couleur)
• DispoVersion : (Modèle, Version)

Copyright - Tout droit réservé - OFPPT 66

 CHAPITRE 3
Normaliser les données

1. Formes normales
2. Règles de passage du MCD au MLD normalisé
 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Modèle Logique de Données (MLD)

• Le modèle logique de données est une représentation du modèle de données en tables logiques reliées entre elles par des flèches. Il permet de
modéliser la structure de la base de données à partir du MCD. Il est adapté au Systèmes de Gestion de Bases de Données Relationnelles
(SGBDR).

Du MCD AU MLD :
Ensemble des règles MCD
Niveau conceptuel En formalisme Entité-Relation

MLD
Niveau logique
(Relationnel)

Niveau physique Création des tables de la base

SGBD Relationnelle
de données en langage SQL
PARTIE 1

Copyright - Tout droit réservé - OFPPT 68

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Règles de passage du MCD au MLD normalisé

• Règle N°1 : transformation des entités.

• Règle N°2 : transformation d'une association sans propriété du type (*,n)-(1,1).
• Règle N°3 : transformation d'une association (1,n) -(*,n).
• Règle N°4 : associations ternaires (n-aires).

Règle N°1 : transformation des entités

• Une entité du MCD devient une table portant le même nom.

• Chaque ligne de la table correspond à un enregistrement.
• Chaque colonne correspond à un attribut.
Étudiant
• L'identifiant devient la clé primaire de la table.
Exemple : numCINEtu
PARTIE 1

nomEtu
• ÉTUDIANT (numCINEtu,prenomEtu, dateNaissEtu, niveauEtu, nomVilleEtu, AdresseEtu) prenomEtu
dateNaissEtu
niveauEtu
nomVilleEtu
AdresseEtu

Copyright - Tout droit réservé - OFPPT 69

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Règle N°2 : Transformation d'une association sans propriété du type (*,n)-(1,1)

• La clé primaire de la table, ayant la cardinalité (*,n), est dupliquée dans la table ayant la cardinalité (1,1).
Exemple : Session Formation
- codeSess 1,1 Concerne 0,n - codeForm
- nomSess - titreForm
- dateDebut - dureeForm
- …. - ….

• Une session concerne une seule formation.

• Une formation peut n'avoir aucune session.
• Formation est dite entité forte et Session est dite entité faible.
• La clé primaire codeForm de la table Formation doit être dupliquée dans la table Session.
• Formation (codeForm)
Session
• Session (codeSess) Formation
PARTIE 1

- codeSess
- codeForm
- nomSess
- titreForm
- dateDebut
- dureeForm
- codeForm
- ….
- ….

Copyright - Tout droit réservé - OFPPT 70

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Règle N°3 : Transformation d'une association (1, n) - (*, n)

• Concerne les relations ou les cardinalités max des deux côtés de l'association = N.
• La relation est transformée en une entité.
• La clé primaire de cette entité est la combinaison des clés des relations correspondantes aux entités.
• Les propriétés de l'association deviennent des attributs de l'entité.
Exemple : Session
Étudiant
0,n Est inscrit 0,n - codeSess
- numCINEtu
typeCours - nomSess
- nomEtu
- dateDebut
- prenomEtu
- ….

• Un étudiant peut n'être inscrit à aucune session de formations comme il peut être inscrit à plusieurs.
• Une session peut n'avoir aucun étudiant inscrit, comme elle peut avoir plusieurs étudiants inscrits.
• L'association « est inscrit » est transformée en une nouvelle entité « Inscription ».
PARTIE 1

• Inscription ( codeSess, numCINEtu, typeCours) Étudiant Inscription

Session
- CodeSess
- numCINEtu - CodeSess
- nomSess
- nomEtu - numCINEtu
- dateDebut
- prenomEtu - typeCour
- ….

Copyright - Tout droit réservé - OFPPT 71

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Règle N°3 : transformation d'une association (1, n) - (*, n)

Cas particuliers :
• Associations 1,1
Exemple : course à la voile (3 solutions)

Marin Voilier
1,1 Pilote 1,1
- numMarin - numVoilier
- nomMarin - nomVoilier

Marin Voilier Marin Voilier

- numMarin - numVoilier OU - numMarin - numVoilier
- nomMarin OU - nomVoilier - nomMarin - nomVoilier
- numVoilier - numMarin - numVoilier# - numMarin#
PARTIE 1

- nomVoilier - nomMarin

• numVoilier# : clé étrangère de Voilier

• numMarin# : clé étrangère de Marin

Copyright - Tout droit réservé - OFPPT 72

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Règle N°3 : transformation d'une association (1, n) - (*, n)

Cas particuliers :
• Associations binaires 01,01

Micro CD-ROM
1,1 Equiper 1,1
- numMicro - numCD
- marqueMicro - marqueCD

Micro Equiper CD-ROM

- numMicro - numMicro# - numCD
- marqueMicro - numCD# - marqueCD

• Micro (numMicro, marqueMicro)

• CD-ROM (numCD, marqueCD)
PARTIE 1

• Equiper (numMicro#, numCD#)

• Clé étrangère de “Equiper” :
• numMicro
• numCD

Copyright - Tout droit réservé - OFPPT 73

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Règle N°4 : Associations ternaires (n-aires)

• L'association gère une table, qui reçoit en clé étrangère, les clés primaires des tables associées.
• La composition des clés étrangères devient la clé primaire de la table association.
• Les données éventuelles de l'association deviennent les attributs de la table association.
Exemple :
• L'association suivante : Est transformée comme suit :

Engager
Écurie 0,n 0,n Saison Écurie Saison
Engager - nomEcurie#
- nomEcurie salaire - idSaison - nomEcurie - idSaison# - idSaison
- numPilote#
- salaire
0,n
Pilote Pilote
- numPilote - numPilote
PARTIE 1

- nomPilote - nomPilote
• Saison (idSaison)
• Pilote(numPilote, nomPilote)
• Écurie (nomEcurie)
• Engager (idSaison#, numPilote#, nomEcurie#, salaire)

Copyright - Tout droit réservé - OFPPT 74

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Le MCD de la base de données du centre de formation

Session Formation
Étudiant
0,n Est inscrit 0,n - CodeSess 1,1 Concerne 0,n - CodeForm
- numCINEtu
typeCours - nomSess - titreForm
- nomEtu
- dateDebut - dureeForm
- prenomEtu
- …. - ….

1,n

Appartient

0,n
Spécialité
PARTIE 1

- CodeSpec
- nomSpec
- ….

Copyright - Tout droit réservé - OFPPT 75

 03 - Normaliser les données
Règles de passage du MCD au MLD normalisé

Le MLD correspondant au MCD du centre de formation

Session
Formation
Étudiant Inscription
- codeSess
- codeForm
- numCINEtu - codeSess - nomSess
- titreForm
- nomEtu - numCINEtu - dateDebut
- dureeForm
- prenomEtu - typeCours - codeForm
- ….
- ….

Combinaison
- codeForm
- codeSpec

Spécialité
- codeSpec
PARTIE 1

- nomSpec
- ….
Remarques :
• Deux nouvelles entités ajoutées : Inscription et Combinaison identifiées respectivement pas les clés (codeSess,numCINEtu) et
(codeSpec,codeForm).
• L'association entre Session et Formation est traduite par l'ajout de la clé : codeForm dans l'entité Session.

Copyright - Tout droit réservé - OFPPT 76

 CHAPITRE 4
PRÉPARER L'ENVIRONNEMENT

Ce que vous allez apprendre dans ce chapitre :

• Apprendre la procédure d'installation d'un outil de

modélisation
• Maîtriser son utilisation et son exploitation
• Préparer le serveur MySQL

05 heures
 CHAPITRE 4
PRÉPARER L'ENVIRONNEMENT

1. Exploitation d'un outil de modélisation

2. Préparation du serveur MySQL
 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Introduction

• Dans la première partie de ce cours on a suivi les étapes de conception d'une base de données, depuis la lecture du cahier de charges jusqu'à
l'élaboration des modèles conceptuel et logique de données (MCD et MLD).

• La modélisation a donc pour but de convertir la conception complexe en diagrammes représentant les données de manière simple et facile à
comprendre. Ainsi, les outils de modélisation de données permettront de dessiner ces diagrammes et créer les structures logiques et physiques
de la base de données.

• Il existe différents outils de modélisation souvent adaptés aux besoins de l'utilisateur en termes de systèmes d'exploitation, architecture et
serveur de base de données. Parmi les fonctionnalités, on trouve : la création de structure de données à partir de diagrammes, l'ingénierie en
amont et en aval, la fonction d'importation et d'exportation, la documentation, la prise en charge de plusieurs bases de données, le reporting,
etc.

• Dans ce cours on va utiliser l'outil MySQL Workbench.

PARTIE 1

Copyright - Tout droit réservé - OFPPT 79

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Procédure d'installation d'un outil de modélisation : MySQL Workbench

• Il s'agit d'un outil utilisé par les administrateurs, les architectes et

les développeurs de bases de données pour la modélisation des
données, le développement SQL, la configuration du serveur,
l'administration des utilisateurs et la sauvegarde.

Fonctionnalités :
• Modélisation et reverse engineering.
• Création des modèles ER complexes.
• La gestion des bases de données.
• La documentation.
• La création, l'exécution et l'optimisation des requêtes SQL
via des outils visuels.
PARTIE 1

• Les outils visuels pour la configuration des serveurs, la

sauvegarde et la restauration, l'administration des Design des diagrammes sur Workbench
utilisateurs, l'inspection des données d'audit et la
visualisation de l'état de la base de données.

Copyright - Tout droit réservé - OFPPT 80

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Procédure d'installation d'un outil de modélisation : MySQL Workbench

Installation
• Étape 1 : télécharger l'outil sur le lien suivant : https ://dev.MySQL.com/downloads/workbench/
PARTIE 1

• Étape 2 : afin de commencer l'installation, lancer le fichier : MySQL-workbench-community-8.0.27-winx64.msi

Copyright - Tout droit réservé - OFPPT 81

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Procédure d'installation d'un outil de modélisation : MySQL Workbench

Installation
• Étape 3 : une fois l'assistant de configuration lancé, on Étape 4 : le système vous prompte à choisir le dossier où on veut
clique sur « Next » : installer Workbench.
Pour changer le dossier par défaut, cliquer sur « Change ». Puis,
valider en cliquant sur « Next ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 82

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Procédure d'installation d'un outil de modélisation : MySQL Workbench

Installation
• Étape 5 : choisir le type d'installation (complète ou personnalisée), puis cliquer sur « Next » :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 83

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Procédure d'installation d'un outil de modélisation : MySQL Workbench

Installation
• Étape 6 : pour terminer, lancer l'installation :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 84

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Procédure d'installation d'un outil de modélisation : MySQL Workbench

Installation
• Une fois l'installation terminée, accéder à la page d'accueil de Workbench :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 85

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation

• Une des plus intéressantes fonctionnalités de l'outil MySQL Workbench est la possibilité de créer et gérer des modèles de données. Dans ce
chapitre, on va suivre les étapes nécessaires pour concevoir un schéma simple à l'aide de MySQL Workbench. Ce schéma pourrait ensuite être
exploité pour générer un script SQL et le transmettre à un serveur de base de données pour créer la base de données physique correspondante.
• Rappelons les MCD et MLD relatifs au centre de formation qu’on a vu dans le chapitre précédent :

MCD
Session Formation
Étudiant Spécialité
0,n Est inscrit 0,n - codeSess 1,1 Concerne 0,n - codeForm 1,n Appartient 0,n
- numCINEtu - codeSpec
typeCours - nomSess - titreForm
- nomEtu - nomSpec
- dateDebut - dureeForm
- prenomEtu - ….
- …. - ….

MLD
PARTIE 1

Session Formation
Étudiant Inscription Spécialité
Combinaison
- codeSess - codeForm
- numCINEtu - codeSess - codeSpec
- nomSess - titreForm - codeForm
- nomEtu - numCINEtu - nomSpec
- dateDebut - dureeForm - codeSpec
- prenomEtu - typeCour - ….
- …. - ….

Copyright - Tout droit réservé - OFPPT 86

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer un nouveau modèle

• Pour créer un nouveau modèle, démarrer l'outil MySQL Workbench et cliquer sur l'option « New model » située dans la colonne
« Modélisation » des données de l'écran d'accueil.
PARTIE 1

• Un nouveau panneau sera ajouté à l'atelier intitulé Modèle.

Copyright - Tout droit réservé - OFPPT 87

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer un nouveau modèle

• On commence par définir les propriétés du schéma en double-cliquant sur l'onglet intitulé « mydb MySQL Schéma ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 88

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation

• On peut remplacer le nom du schéma, ici on utilise le nom : « centreFormation ».
• Fermer le panneau des propriétés du schéma en cliquant sur le petit « x » à côté de l'onglet « Schéma ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 89

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation

• Après avoir donné un nom au schéma, on peut maintenant ajouter une table au modèle. Ceci est réalisé en double-cliquant sur le bouton
« Ajouter une table » dans le panneau « Tables ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 90

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation

• Renommer la table « Etudiant », puis sélectionner l'onglet « Colonnes » pour commencer le processus d'ajout de colonnes à la table.
• Par défaut, le système va créer une clé primaire non nulle nommée idEtudiant. Renommer cette colonne en numCINEtu. La colonne Data Type
définit le type de données, on choisit VARCHAR(10). Cocher la case PK (Primary key) comme c'est la clé primaire de la table, et aussi la case NN
pour ne pas accepter des valeurs nulles dans ce champ.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 91

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation

• De la même manière, continuer à créer les autres colonnes de la table :

• Créer les autres tables du modèle en suivant les mêmes étapes :

PARTIE 1

Copyright - Tout droit réservé - OFPPT 92

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Générer le diagramme

• Après avoir créé toutes les tables, avant de continuer, enregistrer le modèle à partir du menu Fichier -> Enregistrer le modèle ou à l'aide du
bouton de barre d'outils approprié.
• Générer le modèle ER en naviguant vers le menu Model -> Create Model from catalogue objects :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 93

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Générer le diagramme

• Le diagramme illustré dans la figure suivante sera alors généré :

PARTIE 1

Copyright - Tout droit réservé - OFPPT 94

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• Passer à la création des relations entre les tables de notre modèle :

• Les associations (plusieurs à plusieurs)

• Les associations de un à plusieurs

• Pour ce, on peut utiliser l'interface graphique du design ou en définissant les clés étrangères.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 95

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

1 - Les associations (plusieurs à plusieurs)

• La relation entre « Etudiant » et « Session » , d'après le MCD :

Session
Etudiant
0,n Est inscrit 0,n - codeSess
- numCINEtu
typeCours - nomSess
- nomEtu
- dateDebut
- prenomEtu
- ….

• Pour établir l'association, cliquer sur l'icône « n :m Identifying

Relationship », puis successivement sur les objets « etudiant » et
PARTIE 1

« session ».

Copyright - Tout droit réservé - OFPPT 96

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• L'outil crée automatiquement une table d'association ETUDIANT_has_SESSION qu'on peut renommer : « Inscription ».
• On remarque que le système a déjà créé une clé primaire (numCINEtu, codeSession).
PARTIE 1

Copyright - Tout droit réservé - OFPPT 97

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• Afin de respecter les règles de gestions selon lesquelles un étudiant ne peut être inscrit en aucune session, et une session peut n'avoir aucun
étudiant y inscrit.
• On ajuste les cardinalités (0,n) pour les tables « etudiant » et « session », on double-clique sur la patte connectant « inscription » et
« etudiant », puis on décoche la case « Mandatory » associée à « inscription » (Referencing Table) :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 98

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• On refait le même exercice pour la table « session ». Et on ajoute la colonne : TypeCours qui est un attribut de la relation : inscription.
• Notre diagramme prend alors cette forme :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 99

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

2 - Les associations de un à plusieurs :
• La relation entre Formation et Session, d'après le MCD :

Session Formation
- codeSess 1,1 Concerne 0,n - codeForm
- nomSess - titreForm
- dateDebut - dureeForm
- …. - ….

• On sélectionne l'icône « 1 :n Non-Identifying

Relationship », puis on clique successivement sur les
objets « session » et « formation » (dans cet ordre, c'est-
à-dire le référençant d'abord, puis le référencé).
PARTIE 1

Copyright - Tout droit réservé - OFPPT 100

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• On remarque qu'une colonne : formation_codeForm a été ajoutée à la table « session ». Il s'agit de la clé étrangère qui référence la table
« formation » dans la table « session ».
• Aussi, la patte entre « session » et « formation » est en pointillés : l'outil indique ainsi que « session » n'est pas une propriété (multi-valuée)
de « formation ».
• Afin de respecter la cardinalité 0,n de la table « formation », il faut décocher la case « Mandatory » du côté « session » (Referencing Table) :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 101

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• Après avoir créé toutes les associations du MCD, voici le modèle final :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 102

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

Utilisation des clés étrangères
• On peut générer les relations automatiquement après avoir ajouté les clés étrangères sur toutes les tables concernées du schéma.
• Pour la table « inscription », en naviguant sur la tab Foreign Key, on définit 2 clés étrangères qui référencent les tables « etudiant » et
« session ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 103

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

• Si on génère le diagramme, on remarque que l'outil a

déjà créé cette relation :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 104

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les tables

Autres fonctionnalités :
• L'outil offre d'autres fonctionnalités graphiques qui facilitent la lecture du diagramme, par exemple :
1. Les notations des associations : afin de refléter la méthode de modélisation utilisée, les notations des associations personnalisables à
partir du menu Model -> Relationship Notation.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 105

 04 - PRÉPARER L'ENVIRONNEMENT
Exploitation d'un outil de modélisation

Utilisation de l'outil de modélisation : Créer les associations entre les table

2. Afficher les noms de relations entre les tables :
Depuis le menu, séléctionner Edit -> Preferences -> Diagram, puis cocher : Show caption.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 106

 CHAPITRE 4
PRÉPARER L'ENVIRONNEMENT

1. Exploitation d'un outil de modélisation

2. Préparation du serveur MySQL
 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• MySQL est un serveur de gestion de base de données relationnelle (SGBDR) open source et multiplateforme développé par la société suédoise
« MySQL AB » et acquis plus tard par Oracle Corporation.

• Dans le reste de cette partie du cours, on va installer et configurer l'édition du serveur MySQL Community.

• La version MySQL community server 8.0 peut être téléchargée sur le lien suivant :

• https ://dev.MySQL.com/get/Downloads/MySQLInstaller/MySQL-installer-community-8.0.19.0.msi

• Cette version comporte aussi l'outil Workbench qu’on a utilisé pour la modélisation des bases de données.
PARTIE 1

• Workbench sert aussi à assurer l'administration, la gestion, la maintenance, la sécurité et les différentes tâches de développement des bases de
données.

Copyright - Tout droit réservé - OFPPT 108

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Une fois le téléchargement terminé, lancer le fichier d'installation. Le programme prompte à choisir un type d'installation :

• Developer default : si on souhaite créer une machine de développement, on peut utiliser cette option. Elle comporte les composants
requis pour le développement d'applications, par exemple : MySQL Server, MySQL Shell, MySQL connectors et MySQL.

• Server only : si on souhaite créer un serveur de base de données autonome avec des composants spécifiques.

• Full : si on souhaite installer MySQL Server avec tous ses composants.

• Custom : si les besoins se limitent à quelques composants, on peut utiliser cette option.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 109

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Continuer avec l'installation « Full ».

PARTIE 1

Copyright - Tout droit réservé - OFPPT 110

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Le programme d'installation vérifie tous les prérequis

nécessaires pour le fonctionnement de tous les composants
du serveur de base de données MySQL.

• On peut consulter les détails des exigences défaillantes sur

la partie « Check Requirements ».

• Cliquer sur « Next ».

PARTIE 1

Copyright - Tout droit réservé - OFPPT 111

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur l'écran d'installation, le programme liste les composants

MySQL qui vont être installés sur le poste de travail.

• Consulter la liste et cliquer sur « Execute ».

PARTIE 1

Copyright - Tout droit réservé - OFPPT 112

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Le programme télécharge et installe tous les produits.

PARTIE 1

Copyright - Tout droit réservé - OFPPT 113

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur l'écran de configuration, on peut voir la liste des

produits qui doivent être configurés.

• Commencer par le serveur MySQL. Cliquer sur « Next ».

PARTIE 1

Copyright - Tout droit réservé - OFPPT 114

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur l'écran « High Availability », choisir d'effectuer une

installation autonome de MySQL Server, choisir donc
« Standalone MySQL Server / Classic MySQL Replication ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 115

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur le volet « Type and Networking », on peut choisir le
type de configuration MySQL.
• Il s'agit d'un ensemble prédéfini de paramètres de
configuration qui détermine la quantité de ressources à
allouer aux services MySQL. On peut choisir entre trois
options :
• Development Computer : cette configuration utilise
une quantité minimale de ressources.
• Server Computer : cette option convient lorsqu'on
installe des serveurs de base de données et des
serveurs Web sur la même machine. La configuration
alloue une quantité moyenne de ressources au service
MySQL.
• Dedicated Computer : cette option est utilisée lorsque
PARTIE 1

on veut créer un serveur MySQL dédié. La configuration

alloue une grande quantité de ressources au service
MySQL.
• On opte pour l'option « Development Computer ».

Copyright - Tout droit réservé - OFPPT 116

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server : Network Connectivity

• Dans cette section, on peut contrôler la façon dont les

clients peuvent se connecter aux bases de données MySQL.
On peut utiliser le protocole TCP/IP ou « Named Pipe »
ou « Shared Memory ». Si on souhaite configurer un Named
Pipe/Shared Memory, on doit fournir le nom du Pipe ou de
la Memory.

• On peut aussi spécifier le port par défaut pour se connecter

au serveur de base de données et choisir d'autoriser le
numéro de port spécifié dans la zone de texte « Port » dans
le pare-feu.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 117

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur l'écran « Authentification Method », choisir l'option

« Strong Password Encryption for Authentication » pour
installer les dernières versions des connectors.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 118

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur la partie « Accounts and Roles » on peut spécifier le

mot de passe du compte root. Le compte MySQL root est
un compte sysadmin par défaut et il doit être désactivé.

• On peut également créer d'autres utilisateurs en cliquant

sur « Add User ». Dans la boîte de dialogue du compte
d'utilisateur MySQL, fournir un nom d'utilisateur, un nom
d'hôte, le rôle de l'utilisateur, le type d'authentification et
un mot de passe.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 119

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur la partie « Windows Service », on peut configurer le

serveur MySQL pour qu'il s'exécute en tant que service
Windows.

• On peut fournir le nom souhaité et le configurer pour

démarrer automatiquement le service lorsque le système
redémarre.

• On peut aussi choisir le compte système standard ou

fournir un utilisateur spécifique.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 120

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Installation de MySQL Server

• Sur le volet « Apply Configuration », on peut consulter les

configurations choisies. Une fois tous les paramètres de
configuration vérifiés, exécuter.

• L'installation et la configuration sont donc réussies.

PARTIE 1

Copyright - Tout droit réservé - OFPPT 121

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Management des services MySQL

• Sur l'écran d'accueil de MySQL Workbench, on peut voir la liste

des connexions MySQL. On y trouve la liste des connexions
MySQL configurées.

• Dans l'exemple, on a configuré un service local : « local

instance MySQL80 ».
PARTIE 1

Copyright - Tout droit réservé - OFPPT 122

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Management des services MySQL

• On peut ajouter de nouvelles connexions à d'autres serveurs de bases de données en cliquant sur le bouton
• Puis entrer les détails nécessaires.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 123

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Management des services MySQL : Manager les services à partir de Workbench

• Afin de démarrer, arrêter ou redémarrer un service, cliquer sur la connexion relative pour avoir accès au menu de gestion de ce service. Sur le
volet « INSTANCE », on trouve « Startup/Shutdown ».
• Cette page permet de manager les services.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 124

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Management des services MySQL : Manager les services sur Windows

• Comme on a vu sur le volet configuration,

MySQL server est configuré comme un
service Windows.
• Afin de démarrer, arrêter ou redémarrer ce
service, on doit suivre les étapes suivantes :
• Ouvrir « Exécuter » depuis le menu
Windows ou en utilisant le raccourcis
bouton Windows + R.

•
PARTIE 1

• Taper « services.msc ».
• Chercher le service MySQL sur la liste
des services Windows.
• Cliquer sur « stop », « start » or
« restart ».

Copyright - Tout droit réservé - OFPPT 125

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Management des services MySQL : Manager les services sur Windows

• Il est aussi possible de faire de même à partir du command prompt.

• Démarrer le service

C :\> "C :\Program Files\MySQL\MySQL Server 8.0\bin\MySQLd"

• Arrêter le service

C :\> "C :\Program Files\MySQL\MySQL Server 8.0\bin\MySQLadmin" −u root shutdown

PARTIE 1

Copyright - Tout droit réservé - OFPPT 126

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Management des services MySQL : Manager les services sur Linux

• Afin de démarrer, arrêter ou redémarrer les services, on peut utiliser les commandes suivantes :

/etc/init.d/MySQLd start

/etc/init.d/MySQLd stop

/etc/init.d/MySQLd restart

• Sur les versions Linux qui utilisent le « service command » :

service MySQLd start

PARTIE 1

service MySQLd stop

service MySQLd restart

Copyright - Tout droit réservé - OFPPT 127

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Configuration des ports MySQL

Ports des connexions Client - Serveur MySQL

• Le port 3306 est le port par défaut du protocole classique MySQL. Il est utilisé par le client MySQL, les connecteurs MySQL et d'autres utilitaires.

• Le port du X Protocol (MySQLx_port), utilisé par des composants clients comme MySQL Shell, MySQL Connectors et MySQL Router, est calculé
en multipliant le port utilisé pour le protocole MySQL classique par 10.

Port/Protocole SSL ou autres

Description Obligatoire Direction
par défaut cryptages
Clients MySQL vers le serveur
Oui, sauf si on utilise uniquement le Du client au serveur
3306/TCP MySQL (protocole MySQL Oui
protocole X MySQL
classique)
PARTIE 1

Clients MySQL vers le serveur Oui, sauf si on utilise uniquement le port Du serveur au client
33060/TCP Oui
MySQL (protocole X) 3306 MySQL

Copyright - Tout droit réservé - OFPPT 128

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Configuration des ports MySQL

• On peut vérifier les valeurs des ports en utilisant les commandes suivantes sur la ligne de commande MySQL :

• MySQL> SHOW VARIABLES LIKE 'port';

• MySQL> SHOW VARIABLES LIKE 'MySQLx_port'

• Exemple :
PARTIE 1

Copyright - Tout droit réservé - OFPPT 129

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Configuration des ports MySQL

Afin de changer le port par défaut :

• Sur Workbench : cliquer sur l'icône pour avoir accès au gestionnaire de la connexion et modifier le port.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 130

 04 - PRÉPARER L'ENVIRONNEMENT
Préparation du serveur MySQL

Configuration des ports MySQL

• Sur Windows :
• Trouver le fichier de configuration « my.ini » dans le répertoire suivant :
C :\ProgramData\MySQL\MySQL Server 8.0\ (ProgramData est un dossier caché)

• Parcourir le fichier jusqu'à trouver l'expression suivante :

# The TCP/IP Port the MySQL Server will listen on

port=3306

• Modifier le port et enregistrer le fichier.

PARTIE 1

Noter qu'il faut redémarrer les services après le changement des ports.

Copyright - Tout droit réservé - OFPPT 131

 PARTIE 2
Manipuler un SGBD relationnel

Dans ce module, vous allez :

• S'initier à créer des Bases de Données

• Réaliser les différentes requêtes SQL
• Administrer une BDD

45 heures
 CHAPITRE 1
Créer une Base de Données

Ce que vous allez apprendre dans ce chapitre :

• Créer une base de données

• Créer des tables et des colonnes
• Intégrer des contraintes d'intégrité sur les colonnes
• Manipuler des objets d'une Base de données

17 heures
 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Création des Bases De Données

Rappel : Définition d'une base de données

Une base de données est une structure permettant de stocker un grand nombre d'informations afin d'en faciliter l'utilisation.

• Le fait de structurer les données a pour but d'assurer les fonctions fondamentales suivantes :

La fiabilité du Le partage des

stockage de La qualité des
données entre
l'information : La massification : L'optimisation : données :
plusieurs acteurs
la possibilité de le traitement de en terme de temps La sécurisation des la vérification des
(utilisateurs,
restituer grands volumes de de traitement et accès aux données ; règles de gestion et
applications...) et la
l'information stockée données ; espace de stockage ; la conformité avec
gestion des
dans la base de les modèles de
concurrences
données ; conception ;
d'accès.

• Ces fonctions sont assurées au moyen des Système de Gestion de Bases de Données SGBD
PARTIE 2

Copyright - Tout droit réservé - OFPPT 135

 01 - Créer une Base de Données
Création des Bases De Données

Rappel : Les objets d'une base de données

Les tables • contenant des données;

Les index • servant à retrouver, trier et regrouper rapidement les données ;

• permettant d'exécuter des opérations particulières lors de l'insertion, la

Les déclencheurs (triggers)
modification ou la suppression de données ;
Les types de données définis par l'utilisateur
• servant de référentiel à plusieurs tables ;
(UDDT)
• autorisant le système à insérer des valeurs dans les colonnes non
Les valeurs par défaut (Defaults)
renseignées par l'utilisateur ;

Les vues, ou pseudo-tables (Views) • offrant une vue particulière des données aux utilisateurs ;

Les fonctions définies par l'utilisateur (UDF) • permettant de renvoyer soit une valeur, soit une table ;
PARTIE 2

Les procédures stockées • exécutées par l'utilisateur pour produire un résultat donné ;

Les diagrammes (Diagrams) • visualisant les relations entre les tables.

Copyright - Tout droit réservé - OFPPT 136

 01 - Créer une Base de Données
Création des Bases De Données

Rappel : SGBD (Système de Gestion de Bases de Données)

Un SGBD est un ensemble logiciel qui permet la structuration, le

stockage, et la manipulation d'une base de données.
En termes d'architecture, et selon le modèle ANSI/SPARC, un SGBD
comporte 3 niveaux :
• Niveau conceptuel :
• Le niveau central d'un SGBD. Il correspond à une vue générale
de toutes les données qui existent dans l'entreprise.
• Niveau interne ou physique :
• Spécification du stockage physique des données (fichiers,
disques, etc.) et des méthodes d'accès (index, etc.).
• Niveau externe :
• Il s'agit d'une vue externe pour chaque groupe d'utilisateurs
sur un sous ensemble de la base ;
PARTIE 2

• Chaque schéma externe est généralement un sous schéma du

schéma conceptuel mais peut contenir parfois des
informations supplémentaires.

Copyright - Tout droit réservé - OFPPT 137

 01 - Créer une Base de Données
Création des Bases De Données

Types de SGBD

SGBD hiérarchique : SGBD relationnel : SGBD objet :

• Les données sont représentées • Les SGBDR Dominent le marché • Les SGBDOO enregistrent les
dans la base sous la forme d'un des SGBD. La théorie derrière ce données sous forme d'objets ; les
arbre dont le parcours se fait du type de systèmes est fondée sur données sont enregistrées avec
père vers le fils à l'aide de la théorie mathématique des les procédures et les fonctions
pointeurs. relations. Il s'agit d'une qui permettent de les manipuler.
représentation simple des
données sous forme de tables
PARTIE 2

constituées de lignes et de
colonnes.

Copyright - Tout droit réservé - OFPPT 138

 01 - Créer une Base de Données
Création des Bases De Données

Exemples de SGBD :

Oracle PostgreSQL Access MongoDb MySQL

est un SGBD relationnel et est un SGBD relationnel est un SGBD relationnel est un SGBD non- est un SGBDR d'oracle
relationnel-objet très puissant qui offre une Microsoft, qui offre une relationnel libre (licence open source. Il est basé
utilisé pour les alternative libre (licence interface graphique Apache) orienté sur le langage SQL
applications BSD) aux solutions permettant de concevoir document. Il permet de (Structured Query
professionnelles. commerciales comme rapidement des gérer facilement de très Language) et fonctionne
Oracle ou IBM. applications de petite grandes quantités de sur pratiquement toutes
envergure ou de réaliser données - dans un format les plates-formes comme
des prototypes. arborescent JSON - Linux, UNIX et Windows.
réparties sur de nombreux
ordinateurs.
PARTIE 2

Remarque : On utilisera pour les TD/TP le SGBD MySQL.

Copyright - Tout droit réservé - OFPPT 139

 01 - Créer une Base de Données
Création des Bases De Données

Le langage de requêtes SQL (Structured Query Language)

• Il s'agit d'un langage d'interrogation des bases de données relationnelles qui permet d'effectuer les tâches suivantes :
• Définition et modification de la structure de la base de données
• Interrogation et modification non procédurale (c'est à dire interactive) de la base de données
• Contrôle de sécurité et d'intégrité de la base
• Sauvegarde et restauration des bases
• Le langage SQL n'est pas sensible à la casse, cela signifie que l'on peut aussi bien écrire les instructions en minuscules qu'en majuscule.
• Le langage de commandes SQL peut être réparti en quatre catégories :

LDD (Langage de définition LMD (Langage de LQD (Langage des queries LCD (Langage de contrôle
des données) manipulation des données) des données) des données)
• langage de manipulation • il permet de consulter et • langage de requêtes sur les • il permet de gérer les
des structures de la base. de modifier le contenu de données. privilèges et les différents
la base de données. droits d'accès à la base de
PARTIE 2

données.

Copyright - Tout droit réservé - OFPPT 140

 01 - Créer une Base de Données
Création des Bases De Données

Création d'une base de données

Les interfaces des SGBD offrent la possibilité de créer des bases de données. Cette opération est aussi possible à partir de la ligne de commande.

• Syntaxe :

• Pour créer une base de données nommée « nom_base », il suffit d'utiliser la requête suivante : 'CREATE DATABASE nom_base'

• Options :

• La syntaxe utilisée pour chacune des options qui peuvent accompagner la commande CREATE DATABASE dépendent du SGBD utilisé. Il
convient alors de vérifier la documentation du SGBD pour avoir une idée sur les différentes options possibles : définition des jeux de
caractères, le propriétaire de la base, les limites de connexion, etc.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 141

 01 - Créer une Base de Données
Création des Bases De Données

Exemple : Création d'une base de données sur MySQL

Pour créer une nouvelle base de données sur MySQL, on utilise la commande CREATE DATABASE avec la syntaxe suivante :

Il faut noter que :

• Le nom de la base de données doit être unique. Si une autre base existe avec le même nom, MySQL retourne une erreur.

• Utiliser l'option IF NOT EXISTS pour créer conditionnellement une base de données si elle n'existe pas.

• On peut spécifier les options CHARACTER SET et COLLATE ainsi que le classement de la nouvelle base de données. Sinon MySQL utilisera les
valeurs par défaut pour la nouvelle base de données.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 142

 01 - Créer une Base de Données
Création des Bases De Données

Sur MySQL command line Client

• Se connecter au serveur MySQL avec un compte utilisateur disposant du privilège CREATE DATABASE.

• Exécuter la commande « CREATE DATABASE dbTest ; » et appuyer sur Entrée :

• MySQL retourne le résultat suivant :

PARTIE 2

• On peut utiliser la commande SHOW CREATE DATABASE pour examiner la base de données créée :

Copyright - Tout droit réservé - OFPPT 143

 01 - Créer une Base de Données
Création des Bases De Données

Sur MySQL command line Client

• Voici le résultat de la commande précédente :

• Enfin, sélectionner la base de données nouvellement créée avec laquelle on veut travailler en utilisant la commande USE :

• Le système confirme par le message suivant :

PARTIE 2

• Utiliser la commande EXIT pour quitter le programme.

Copyright - Tout droit réservé - OFPPT 144

 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Choix du moteur

Moteurs de stockage

• Le moteur de stockage d'un SGBD, aussi appelé moteur de table, est l'ensemble d'algorithmes qui permettent de stocker et d'accéder aux
données. En général, les SGBD utilisent un seul moteur de stockage qui est optimisé au mieux pour la lecture, l'écriture et la suppression de
données.

• MySQL se distingue des autres SGBD par le fait de donner à l'utilisateur le libre choix d'utiliser un moteur de table parmi plusieurs moteurs
différents. Ces moteurs de stockage peuvent être transactionnels ou non-transactionnels.

• On se retrouve ainsi avec des bases où plusieurs moteurs peuvent coexister. C'est un choix de conception qui a ses avantages et ses
inconvénients.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 146

 01 - Créer une Base de Données
Choix du moteur

Moteurs de stockage

• Afin de consulter la liste des engins mis à notre disposition par MySQL, on peut exécuter la commande « SHOW ENGINES ; » sur la ligne de
commande MySQL comme suit :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 147

 01 - Créer une Base de Données
Choix du moteur

Moteurs de stockage

• Chacun de ces moteurs de table possèdent des caractéristiques propres qui peuvent représenter des atouts ou des inconvénients selon le type
d'application qui aura besoin d'une base de données.

• A partir de la version MySQL 5.5, le moteur par défaut est InnoDB.

• On peut choisir le moteur de stockage au moment de la création d'une table.

• La syntaxe est la suivante :

CREATE TABLE <nomTable> (...) ENGINE <nomMoteur>
PARTIE 2

Copyright - Tout droit réservé - OFPPT 148

 01 - Créer une Base de Données
Choix du moteur

Quel moteur choisir ?

Les moteurs MySQL les plus utilisés sont MyISAM, InnoDb et Memory. Le tableau suivant présente une comparaison entre ces moteurs :
Moteur Avantages Inconvénients
MyISAM : un moteur non transactionnel assez rapide en écriture • Très rapide en lecture • Pas de gestion des relations
et très rapide en lecture. • Extrêmement rapide pour les • Pas de gestion des transactions
opérations COUNT() sur une • Bloque une table entière lors d'opérations
table entière d'insertions, suppressions ou mise à jour
• Les index FULLTEXT pour la des données
recherche sur des textes
InnoDB : un moteur relationnel performant faisant partie de la • Gestion des relations • Plus lent que MyISAM
famille des moteurs transactionnels. • Gestion des transactions • Occupe plus de place sur le disque dur
• Verrouillage à la ligne et non à la • Occupe plus de place en mémoire vive
table
Memory : un moteur de stockage permettant de créer des tables • Le moteur le plus rapide • Les données sont volatiles : un arrêt du
PARTIE 2

directement dans la mémoire vive, sans passer par le disque dur • Ne consomme pas de place sur serveur et elles disparaissent
pour stocker les données. Ceci est en fait le moteur de stockage le disque dur • Pas de champs BLOB ou TEXT
le plus rapide que propose MySQL, mais aussi le plus dangereux.

Afin de faciliter le choix, pour une petite base, sans liens entre les tables, et dont la cohérence des données n'est pas vitale, on peut opter pour
MyISAM. Dans les autres cas, choisir InnoDB.
Copyright - Tout droit réservé - OFPPT 149
 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Création des tables

La création des tables est possible à partir de la ligne de commande.

• Syntaxe : Pour créer une table « nom_table », il suffit d'utiliser la requête suivante :

CREATE TABLE nom_table

• Exemple : Création d'une table dans une base de données MySQL

• Pour créer une nouvelle base de données sur MySQL, on utilise la commande CREATE DATABASE avec la syntaxe suivante :

Sur cette syntaxe, on définit :

PARTIE 2

• Le nom de la table, « IF NOT EXISTS » est une commande optionnelle et permet de vérifier si une table avec le même nom existe déjà.
• La liste des colonnes de cette table, séparées par des virgules.
• La liste des contraintes comme UNIQUE, CHECK, PRIMARY KEY et FOREIGN KEY.
• Le moteur de stockage avec la clause : ENGINE. Si on n'utilise pas cette option, MySQL va utiliser le moteur définit par default : InnoDB.

Copyright - Tout droit réservé - OFPPT 151

 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Définition des colonnes

La syntaxe de définition d'une colonne est la suivante :

nom_colonne type_donnee Liste_contraintes;

Avec :

• Nom_colonne : le nom qu'on va donner à cette colonne.

• Type_donnee : le type et la taille des données qui vont être stockées dans cette colonne ( numériques, caractères, date, etc.).

• Liste_contraintes : la liste des contraintes sur cette colonne.

PARTIE 2

Copyright - Tout droit réservé - OFPPT 153

 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Typage des colonnes

Les types de données, qui représentent la nature des valeurs stockées dans une colonne, appartiennent à trois catégories : Numérique,
date/heure et chaîne (caractères). Voici les listes des types supportés par MySQL.
1 - Type de données numériques

Type Taille (octets) Utilisation

TINYINT 1 Petites valeurs entières/ Booléennes

SMALLINT 2 Valeur entière

MEDIUMINT 3 Valeur entière

INT ou INTEGER 4 Valeur entière

BIGINT 8 Valeur maximale entière

FLOAT 4 Simple précision valeurs à virgule flottante

PARTIE 2

DOUBLE 8 Double-précision valeurs à virgule flottante

DECIMAL De DECIMAL (M, D), si M> D, M Valeur décimale

+ 2 est par ailleurs D + 2

Copyright - Tout droit réservé - OFPPT 155

 01 - Créer une Base de Données
Typage des colonnes

2 - Type de données caractères

Type Taille (octets) Utilisation

CHAR 0-255 Chaîne longueur fixe

VARCHAR 0-65535 Chaînes de longueur variable

TINYBLOB 0-255 Pas plus de 255 caractères dans une chaîne binaire

TINYTEXT 0-255 Courtes chaînes de texte

BLOB 0-65535 Données textuelles longues sous forme binaire

TEXTE 0-65535 Longues données de texte

MEDIUMBLOB 0-16777215 Forme binaire de longueur moyenne des données de texte

PARTIE 2

MEDIUMTEXT 0-16777215 Longueur moyenne des données de texte

LONGBLOB 0-4294967295 Grandes données de texte sous forme binaire

LONGTEXT 0-4294967295 Grandes données de texte

Copyright - Tout droit réservé - OFPPT 156

 01 - Créer une Base de Données
Typage des colonnes

3 - Type de données date/heure

Type Taille (octets) Format Utilisation

DATE 3 AAAA-MM-JJ Valeur de date

TIME 3 HH : MM : SS Valeur du temps ou de la durée

ANNEE 1 AAAA Valeur de l'année

DATETIME 8 AAAA-MM-JJ HH : MM : SS Mixage des valeurs date et heure

TIMESTAMP 4 AAAAMMJJ HHMMSS Mixage des valeurs date et temps : un horodatage

PARTIE 2

Copyright - Tout droit réservé - OFPPT 157

 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Contraintes d'intégrité

Définition

• Une contrainte d'intégrité est une règle qui définit la cohérence d'une donnée ou d'un ensemble de données de la BD. Les contraintes peuvent
avoir une portée sur une colonne ou sur une table lorsque la contrainte porte sur plusieurs colonnes.

• Il existe trois types de contraintes d'intégrité :

Intégrité de domaine : (NOT NULL, DEFAULT, UNIQUE, etc.)

• Spécifier un ensemble de valeurs pour une colonne
• Déterminer si les valeurs nulles sont autorisées
• Implémenter par le contrôle de validité
Intégrité des entités : (PRIMARY KEY)
• Préciser la clé primaire de chaque table
PARTIE 2

Intégrité référentielle : (FOREIGN KEY/REFERENCES)

• Assurer l'intégrité des relations entre les clés primaires et les clés étrangères.

Copyright - Tout droit réservé - OFPPT 159

 01 - Créer une Base de Données
Contraintes d'intégrité

1 - PRIMARY KEY

• Une clé primaire est une colonne ou un ensemble de colonnes qui identifie de manière unique chacune des lignes de la table. Cette colonne
doit vérifier les conditions suivantes :

• Une clé primaire doit contenir des valeurs uniques. Si la clé primaire se compose de plusieurs colonnes, la combinaison de valeurs dans
ces colonnes doit être unique.

• Une colonne de clé primaire ne peut pas avoir de valeurs NULL. Toute tentative d'insertion ou de mise à jour de NULL dans les colonnes
de clé primaire entraînera une erreur. Noter que MySQL ajoute implicitement une contrainte NOT NULL aux colonnes de clé primaire.

• Une table ne peut avoir qu'une et une seule clé primaire.

• Lorsqu'on définit une clé primaire pour une table, MySQL crée automatiquement un index appelé PRIMARY.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 160

 01 - Créer une Base de Données
Contraintes d'intégrité

1 - PRIMARY KEY : suite

En général, la clé primaire d'une table est définie dans l'instruction CREATE TABLE.
Si la clé primaire est une seule colonne, PRIMARY KEY (contrainte de colonne) est utilisée avec la syntaxe suivante :

CREATE TABLE nom_table(

primary_key_colonne Type_donnee PRIMARY KEY,

...

);
PARTIE 2

Copyright - Tout droit réservé - OFPPT 161

 01 - Créer une Base de Données
Contraintes d'intégrité

1 - PRIMARY KEY : suite

Si la clé primaire est composée de plusieurs colonnes, PRIMARY KEY (contrainte de table) est utilisée avec la syntaxe suivante :

CREATE TABLE nom_table(

primary_key_colonne1 type_donnee,

primary_key_colonne2 type_donnee,

...,

PRIMARY KEY(primary_key_colonne1, primary_key_colonne2, … )

);

On sépare les colonnes par des virgules (,).

PARTIE 2

Copyright - Tout droit réservé - OFPPT 162

 01 - Créer une Base de Données
Contraintes d'intégrité

2 - FOREIGN KEY

• Une clé étrangère est une colonne ou un groupe de colonnes d'une table qui fait référence à une colonne ou à un groupe de colonnes d'une
autre table.

• La clé étrangère impose des contraintes sur les données des tables associées, ce qui permet à MySQL de maintenir l'intégrité référentielle.

Exemple : Rappelons la relation entres les tables etudiant et inscription de notre base de données CentreFormation.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 163

 01 - Créer une Base de Données
Contraintes d'intégrité

2 - FOREIGN KEY : suite

• Chaque étudiant peut avoir aucune ou plusieurs inscriptions, et chaque inscription appartient à un étudiant unique.

• Cette relation est traduite au niveau du MLD par une clé étrangère dans la table inscription (référencée) qui renvoie à la colonne
identifiante de la table etudiant : numCINEtu.

• La table etudiant est appelée table parent ou table référencée, et la table inscription est appelée table enfant ou table de référencement.

• Afin de créer une contrainte FOREIGN KEY pour la table inscription :

CONSTRAINT fk1_inscription
FOREIGN KEY (numCINEtu)
REFERENCES etudiant(numCINEtu)
PARTIE 2

Copyright - Tout droit réservé - OFPPT 164

 01 - Créer une Base de Données
Contraintes d'intégrité

2 - FOREIGN KEY : suite

Voici la syntaxe générale qu'on utilise pour la définition d'une contrainte de clé étrangère

CONSTRAINT nom_contrainte
FOREIGN KEY (nom_colonne)
REFERENCES table_parent(nom_colonne_p)
[ON DELETE reference_option]
[ON UPDATE reference_option]

Avec :

• nom_contrainte : le nom qu'on va donner à la contrainte FOREIGN KEY

• nom_colonne : la colonne dans la table de référencement

• table_parent : la table référencée

PARTIE 2

• nom_colonne_p : la clé primaire de la table référencée

• reference_option : détermine l'action que MySQL effectuera lorsque les valeurs des colonnes de clé parent seront supprimées (ON DELETE) ou
mises à jour (ON UPDATE).

Copyright - Tout droit réservé - OFPPT 165

 01 - Créer une Base de Données
Contraintes d'intégrité

3 - NOT NULL

• La contrainte NOT NULL est une contrainte de colonne qui garantit que les valeurs stockées dans une colonne ne sont pas NULL.

• La syntaxe d'une contrainte NOT NULL est la suivante :

nom_colonne type_donnee NOT NULL;

4 - DEFAULT
• La contrainte DEFAULT permet de spécifier une valeur par défaut pour une colonne :

nom_colonne type_donnee DEFAULT valeur_par_defaut;

• Dans cette syntaxe, le mot clé DEFAULT est suivi par la valeur par défaut de la colonne. Le type de la valeur par défaut doit correspondre au type
PARTIE 2

de données de la colonne.

Copyright - Tout droit réservé - OFPPT 166

 01 - Créer une Base de Données
Contraintes d'intégrité

5- UNIQUE
• UNIQUE est une contrainte d'intégrité qui garantit que les valeurs d'une colonne ou d'un groupe de colonnes sont uniques. Ainsi elle peut être
une contrainte de colonne ou une contrainte de table.
• Afin de définir la contrainte UNIQUE pour une colonne lors de la création de la table, on utilise cette syntaxe :

nom_colonne type_donnee UNIQUE;

• Pour définir une contrainte UNIQUE pour plusieurs colonnes (contrainte de table), on utilise la syntaxe suivante :
CREATE TABLE nom_table(
nom_colonne1 definition_colonne,
nom_colonne2 definition_colonne,
...,
UNIQUE(nom_colonne1, nom_colonne2,..)
PARTIE 2

);
• Si on définit la contrainte UNIQUE sans la nommer, MySQL génère automatiquement un nom pour celle-ci.
[CONSTRAINT nom_contrainte]
UNIQUE(liste_colonne)

Copyright - Tout droit réservé - OFPPT 167

 01 - Créer une Base de Données
Contraintes d'intégrité

6 - CHECK
• La contrainte CHECK assure que les valeurs stockées dans une colonne ou un groupe de colonnes satisfont à une expression booléenne. Elle est
ainsi considérée comme une contrainte de colonne et une contrainte de table.
• A partir de la version MySQL 8.0.16, la contrainte CHECK de table et de colonne est prise en charge lors de la création de la table, et ce pour
tous les moteurs de stockage.
• Voici la syntaxe à utiliser :

[CONSTRAINT [nom_contrainte]] CHECK(expression) [[NOT] ENFORCED]

• Où il faudra :
• Spécifier le nom de la contrainte à créer. Si le nom de la contrainte est omis, MySQL génère automatiquement un nom avec la convention
suivante : nom_table_chk_n (n étant un entier).
• Spécifier une expression booléenne qui doit être évaluée à TRUE ou FALSE pour chaque ligne de la table. Si l'expression est évaluée à
FALSE, les valeurs entrées violent la contrainte.
• En option, spécifier la clause d'application pour indiquer si la contrainte de vérification est appliquée.
PARTIE 2

• Utiliser ENFORCED ou omettre simplement la clause ENFORCED pour créer et appliquer la contrainte.
• Utiliser NOT ENFORCED pour créer la contrainte mais ne pas l'appliquer.

Copyright - Tout droit réservé - OFPPT 168

 01 - Créer une Base de Données
Contraintes d'intégrité

6 - CHECK : suite
Exemple 1 : Contrainte de colonne
• Contrainte CHECK

CREATE TABLE Produits (

Num_Produit VARCHAR(18) PRIMARY KEY,

description VARCHAR(40),

cout DECIMAL(10,2 ) NOT NULL CHECK (cout >= 0),

prix DECIMAL(10,2) NOT NULL CHECK (prix >= 0)

);
PARTIE 2

• La contrainte check assure que lors de l'insertion, la valeur de chacune des colonnes cout et prix sera >= 0.

Copyright - Tout droit réservé - OFPPT 169

 01 - Créer une Base de Données
Contraintes d'intégrité

6 - CHECK : suite
Exemple 2 : Contrainte de table
• Contrainte CHECK

CREATE TABLE Produits (

Num_Produit VARCHAR(18) PRIMARY KEY,

description VARCHAR(40),

cout DECIMAL(10,2 ) NOT NULL CHECK (cout >= 0),

prix DECIMAL(10,2) NOT NULL CHECK (prix >= 0),

CONSTRAINT produits_chk_prix_et_cout
CHECK(price >= cost)

);
PARTIE 2

• Dans cette requête, on a ajouté une contrainte CHECK nommée produits_chk_prix_et_cout comme contrainte de table, qui assure que la
valeur du prix doit toujours être supérieure à la valeur du coût pour chaque élément de la table produit.

Copyright - Tout droit réservé - OFPPT 170

 CHAPITRE 1
Créer une Base de Données

1. Création des Bases de Données

2. Choix de moteur
3. Création des tables
4. Définition des colonnes
5. Typage des colonnes
6. Contraintes d'intégrité
7. Manipulation d'objet table
 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande DROP TABLE

• La commande DROP TABLE supprime définitivement une table et ses données de la base de données. Dans MySQL, on peut également
supprimer plusieurs tables à la fois en suivant la syntaxe de base :

DROP [TEMPORARY] TABLE [IF EXISTS] nom_table1, nom_table2

...

• L'option TEMPORARY permet de supprimer uniquement les tables temporaires. Ceci prévient que l'utilisateur supprime accidentellement des
tables non temporaires.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 172

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande DROP TABLE

• L'option IF EXISTS fait que MySQL supprime une table uniquement si elle existe. Si on supprime une table inexistante avec l'option IF EXISTS,
MySQL génère une NOTE, qui peut être récupérée à l'aide de l'instruction SHOW WARNINGS.

• Noter que l'instruction DROP TABLE supprime uniquement les tables. Il ne supprime pas les privilèges utilisateur spécifiques associés aux tables.
Par conséquent, si on crée une table avec le même nom que celle supprimée, MySQL appliquera les privilèges existants à la nouvelle table, ce
qui peut poser un risque de sécurité.

• Enfin, pour exécuter DROP TABLE, l'utilisateur doit disposer des privilèges DROP pour la table qu'il va supprimer.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 173

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

• ALTER TABLE est la commande utilisée pour changer la structure d'une table :

• Ajouter, modifier, renommer et supprimer une colonne

• Renommer une table

• Ajouter et supprimer une contrainte d'intégrité

Ajouter une ou plusieurs colonnes à une table :

ALTER TABLE nom_table

ADD nouvelle_colonne1 [definition1],

PARTIE 2

ADD nouvelle_colonne2 [definition2],

...;

Copyright - Tout droit réservé - OFPPT 174

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Ajouter une ou plusieurs colonnes à une table : Exemples

• Ajouter une colonne « num_produit » à la table Produits :

ALTER TABLE Produits (

ADD num_Produit VARCHAR(18)

);
• Ajouter plusieurs colonnes à la table Produits :

ALTER TABLE Produits (

ADD Num_Produit VARCHAR(18),

cout DECIMAL(10,2 ) NOT NULL CHECK (cout >= 0)

PARTIE 2

);

Copyright - Tout droit réservé - OFPPT 175

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Modifier une ou plusieurs colonnes d'une table

• Cette modification peut porter sur le type ainsi que les contraintes associées à cette colonne en utilisant la syntaxe suivante :

ALTER TABLE nom_table

MODIFY nouvelle_colonne1 [definition1],

MODIFY nouvelle_colonne2 [definition2],

...;
PARTIE 2

Copyright - Tout droit réservé - OFPPT 176

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Modifier une ou plusieurs colonnes d'une table : Exemple 1

• Modifier une colonne de la table « Produits » :

• La colonne « num_produit » est déjà définit comme suit : num_Produit VARCHAR(18).

ALTER TABLE Produits (

MODIFY num_Produit VARCHAR(20) PRIMARY KEY );

• La commande permet de modifier cette colonne de telle façon à :

• Augmenter la taille du varchar(18) à varchar (20).

PARTIE 2

• Définir cette colonne comme clé primaire de la table « Produits » via l'ajout de la contrainte PRIMARY KEY.

Copyright - Tout droit réservé - OFPPT 177

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Modifier une ou plusieurs colonnes d'une table : Exemple 2

• Afin de modifier deux colonnes de la table « Produits » :

ALTER TABLE Produits (

MODIFY num_Produit VARCHAR(20) PRIMARY KEY,

MODIFY cout DECIMAL(10,2 ) CHECK (cout >= 0)

);

• Cette commande a permis de :

PARTIE 2

• Changer le type de la colonne num_Produit et la définir comme clé primaire.

• Ajouter la contrainte CHECK sur la colonne cout.

Copyright - Tout droit réservé - OFPPT 178

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Renommer une ou plusieurs colonnes d'une table
• Afin de renommer une colonne d'une table, utiliser la syntaxe suivante :

ALTER TABLE nom_table

CHANGE COLUMN nom_original nouveau_nom [definition] ;

Exemple :
• Pour renommer la colonne « cout » en « cout_produit » on exécute la commande suivante :

ALTER TABLE Produits (

PARTIE 2

CHANGE COLUMN cout cout_produit DECIMAL(10,2 ) CHECK (cout >= 0)

);

Copyright - Tout droit réservé - OFPPT 179

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Supprimer une colonne d'une table
• Afin de supprimer une colonne d'une table, utiliser la syntaxe suivante :

ALTER TABLE nom_table

DROP COLUMN nom_colonne ;

Exemple :
• Supprimer la colonne « description » de la table « Produits» :

ALTER TABLE Produits

DROP COLUMN description ;

PARTIE 2

Copyright - Tout droit réservé - OFPPT 180

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Renommer une table
• Pour renommer une table, utiliser la syntaxe suivante :

ALTER TABLE nom_table

RENAME TO nouveau_nom_table ;

Exemple :
• Renommer la table « Produits » en « Articles » :

ALTER TABLE Produits

PARTIE 2

RENAME TO Articles ;

Copyright - Tout droit réservé - OFPPT 181

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Ajouter et supprimer une contrainte de table : PRIMARY KEY
• Ajouter une clé primaire :

ALTER TABLE nom_table

ADD PRIMARY KEY(column_list);

• Supprimer une clé primaire :

ALTER TABLE nom_table

DROP PRIMARY KEY;

PARTIE 2

Copyright - Tout droit réservé - OFPPT 182

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE : Ajouter et supprimer une contrainte de table

Ajouter et supprimer une contrainte de table : FOREIGN KEY
• Ajouter une clé étrangère :

ALTER TABLE nom_table

ADD CONSTRAINT constraint_name

FOREIGN KEY (column_name, ...)

REFERENCES parent_table (colunm_name,...);

• Supprimer une clé étrangère :

PARTIE 2

ALTER TABLE nom_table

DROP FOREIGN KEY nom_contrainte;

Copyright - Tout droit réservé - OFPPT 183

 01 - Créer une Base de Données
Manipulation d'objet table (DROP, ALTER)

Commande ALTER TABLE

Ajouter et supprimer une contrainte de table : UNIQUE
• Ajouter la contrainte UNIQUE :

ALTER TABLE nom_table

ADD CONSTRAINT nom_contrainte

UNIQUE (liste_colonnes);

• Supprimer la contrainte UNIQUE : Il faut supprimer l'index que MySQL crée pour cette contrainte et qui porte le même nom.

ALTER TABLE nom_table

PARTIE 2

DROP INDEX nom_contrainte;

Copyright - Tout droit réservé - OFPPT 184

 CHAPITRE 2
Réaliser des requêtes SQL

Ce que vous allez apprendre dans ce chapitre :

• Maîtriser les principales requêtes SQL

• Gérer les fonctions du SGBD

23 heures
 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Vues
4. Tables temporaires
5. Expression du SGBD
6. Fonctions d'agrégation du SGBD
7. Sous requêtes
8. Requêtes de l'union
9. Jointures
 02 - Réaliser des requêtes SQL
Requêtes LMD

Dans ce qui suit, on va utiliser les requêtes SQL –LMD : Langage de Manipulation de Données (LMD).

INSERT :
• La commande INSERT permet d'insérer une ou plusieurs lignes de données dans une table selon la syntaxe suivante :

INSERT INTO nom_table(colonne1,colonne2,...)

VALUES (valeur1,valeur2,...);

On doit spécifier :
• Le nom de la table ainsi qu'une liste de ses colonnes séparées par des virgules entre parenthèses.
• La liste de valeurs à insérer dans ces colonnes, séparées par des virgules.

Il faut noter que :

PARTIE 2

• Le nombre de colonnes et de valeurs doit être le même. De plus, les positions des colonnes doivent correspondre aux positions de leurs valeurs.
• Pour les colonnes, qui ne figurent pas dans la liste, spécifiées, MySQL insère les valeurs par défaut ou alors NULL si elles ne sont pas spécifiées.
• Si une colonne est définie comme une colonne AUTO_INCREMENT, MySQL génère un entier séquentiel à chaque fois qu'une ligne est insérée
dans la table.

Copyright - Tout droit réservé - OFPPT 187

 02 - Réaliser des requêtes SQL
Requêtes LMD

INSERT : Insérer plusieurs lignes

• Pour insérer plusieurs lignes dans une table à l'aide d'une seule instruction INSERT, on utilise la syntaxe suivante :

INSERT INTO nom_table(c1,c2,...)

VALUES (v01,v02,...),
(v11,v22,...),
..
(vn1,vn2,...)
;

• Les lignes à ajouter dans la table sont séparées par des virgules dans la clause VALUES.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 188

 02 - Réaliser des requêtes SQL
Requêtes LMD

INSERT : Exemples
• Soit la table « Produit » créée à partir de la requête suivante :

CREATE TABLE Produits (

Num_Produit VARCHAR(18) PRIMARY KEY,
description VARCHAR(40) DEFAULT 'Non specifie',
cout DECIMAL(10,2 ) NOT NULL CHECK (cout >= 0),
prix DECIMAL(10,2) NOT NULL CHECK (prix >= cout),
Date_ajout DATE
);

• On ajoute le produit suivant : Numéro du Produit est P12, son prix est 14 et son cout égale 12.
• Voici la commande à exécuter :
PARTIE 2

INSERT INTO Produits(num_produit,cout,prix)

VALUES ('P12',12,14);

Copyright - Tout droit réservé - OFPPT 189

 02 - Réaliser des requêtes SQL
Requêtes LMD

INSERT : Exemples

• On veut ajouter plusieurs produits :

1. Numéro du Produit : P13, cout : 120, prix : 140, ajouté le : 01/01/2022.

2. Numéro du Produit : P100, description : Laptop, cout : 120, prix : 140, ajouté : aujourd'hui.

• Voici la commande à exécuter :

INSERT INTO Produits(num_produit,description,cout,prix,date_ajout)

VALUES ('P13','',120,140,'2022-01-01'),
('P100','Laptop',5000,6000,CURRENT_DATE)
;
PARTIE 2

Copyright - Tout droit réservé - OFPPT 190

 02 - Réaliser des requêtes SQL
Requêtes LMD

INSERT : Exemples
• Voici le contenu de la table après l'exécution de ces requêtes :

• On remarque que :
• La description non spécifiée dans le premier INSERT a été remplacée par la valeur par défaut : 'Non spécifié'
• Pour insérer une valeur de date dans une colonne, il faut utiliser le format 'YYYY-MM-DD' ou :
• YYYY représente une année à quatre chiffres.
PARTIE 2

• MM représente un mois à deux chiffres.

• DD représente un jour à deux chiffres.
• On va utiliser la fonction CURRENT_DATE() qui retourne la date système.

Copyright - Tout droit réservé - OFPPT 191

 02 - Réaliser des requêtes SQL
Requêtes LMD

UPDATE
• L'instruction UPDATE permet de mettre à jour les données d'une table. Elle sert à modifier les valeurs dans une ou plusieurs colonnes d'une
seule ligne ou de plusieurs lignes, selon la syntaxe suivante :

UPDATE nom_table
SET
nom_colonne1 = expr1,
nom_colonne2 = expr2,
...
[WHERE
condition];

• On doit spécifier :
• Le nom de la table dont on souhaite mettre à jour les données.
• La colonne qui va être mise à jour et la nouvelle valeur dans la clause SET.
PARTIE 2

• Pour mettre à jour les valeurs dans plusieurs colonnes, on utilise une liste d'affectations séparées par des virgules.
• En option, définir une condition dans la clause WHERE. Si cette étape est omise, l'instruction UPDATE modifiera toutes les lignes de la
table.

Copyright - Tout droit réservé - OFPPT 192

 02 - Réaliser des requêtes SQL
Requêtes LMD

UPDATE : Exemples
• Rappel de la table Produits :

• Modifier la date d'ajout du produit P12 en 31/12/2021 :

UPDATE Produits
SET
PARTIE 2

Date_ajout = '2021-12-31'
WHERE Num_Produit='P12';

Copyright - Tout droit réservé - OFPPT 193

 02 - Réaliser des requêtes SQL
Requêtes LMD

UPDATE : Exemples
• Modifier les données de telle façon que Description ='Non specifie' et Prix = 1.5*cout :

UPDATE Produits
SET
description = 'Non specifie',
Prix = 1.5*cout ;

• Résultat suivant les deux modifications :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 194

 02 - Réaliser des requêtes SQL
Requêtes LMD

DELETE

• L'instruction DELETE permet de supprimer une ou plusieurs lignes d'une table en utilisant la syntaxe suivante :

DELETE FROM nom_table

WHERE Conditions;

• Cette instruction permet d'utiliser,en option, une condition pour spécifier les lignes à supprimer dans la clause WHERE, si cette dernière est
omise, l'instruction DELETE supprimera toutes les lignes de la table.

• Pour une table qui a une contrainte de clé étrangère, lorsqu’on supprime des lignes de la table parent, les lignes de la table enfant peuvent
aussi être supprimées automatiquement à l'aide de l'option ON DELETE CASCADE.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 195

 02 - Réaliser des requêtes SQL
Requêtes LMD

DELETE : Exemples
• De la table Produit :

• On veut supprimer les Produits dont le cout <=12 .

DELETE FROM Produits

WHERE cout<=12;

• Voici la table après l'exécution de cette requête

PARTIE 2

Copyright - Tout droit réservé - OFPPT 196

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Vues
4. Tables temporaires
5. Expression du SGBD
6. Fonctions d'agrégation du SGBD
7. Sous requêtes
8. Requêtes de l'union
9. Jointures
 02 - Réaliser des requêtes SQL
Requêtes de sélection

SELECT

• L'instruction SELECT permet de consulter les données et de les présenter triées et/ou regroupées suivant certains critères.

• L'instruction SELECT basique est comme suit :

SELECT [Liste_select]
FROM nom_table;

• Dans cette syntaxe, il faut :

• Spécifier une ou plusieurs colonnes à partir desquelles on veut sélectionner des données après le mot-clé SELECT : Pour sélectionner
toute les colonnes de la table, on utilise « SELECT * » , sinon on spécifie les noms des colonnes séparés par une virgule (,).

• Spécifier le nom de la table à partir de laquelle on veut sélectionner des données après le mot-clé FROM.

• N.B : Lors de l'exécution de l'instruction SELECT, MySQL évalue la clause FROM avant la clause SELECT
PARTIE 2

FROM SELECT

Copyright - Tout droit réservé - OFPPT 198

 02 - Réaliser des requêtes SQL
Requêtes de sélection

SELECT
Exemples de requêtes SELECT simples
PARTIE 2

Copyright - Tout droit réservé - OFPPT 199

 02 - Réaliser des requêtes SQL
Requêtes de sélection

SELECT
Options de la Requête SELECT
• La requête SQL plus avancée prend la forme suivante :

SELECT [DISTINCT] Liste_Select

FROM Liste_Tables
WHERE Liste_Conditions_Recherche
GROUP BY Liste_regroupement
HAVING Liste_Conditions_regroupement
ORDER BY liste_Tri

• MySQL exécute cette requête dans cet ordre :

GROUP ORDER
FROM WHERE HAVING SELECT DISTINCT
PARTIE 2

BY BY

Copyright - Tout droit réservé - OFPPT 200

 02 - Réaliser des requêtes SQL
Requêtes de sélection

DISTINCT

DISTINCT est une option qui permet de supprimer les lignes en double.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 201

 02 - Réaliser des requêtes SQL
Requêtes de sélection

WHERE

• WHERE définit la liste des conditions que les données recherchées doivent vérifier. La condition de recherche est une combinaison d'une ou
plusieurs expressions utilisant les opérateurs logiques AND, OR et NOT. L'instruction SELECT inclura toute ligne qui satisfait la condition de
recherche dans le jeu de résultats.

• WHERE est aussi utilisé dans UPDATE ou DELETE pour spécifier les lignes à mettre à jour ou à supprimer.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 202

 02 - Réaliser des requêtes SQL
Requêtes de sélection

GROUP BY

• La clause GROUP BY regroupe un ensemble de lignes dans un ensemble de lignes récapitulatives par valeurs de colonnes ou d'expressions. La
clause GROUP BY renvoie une ligne pour chaque groupe, ceci réduit le nombre de lignes dans le jeu de résultats.

• En pratique, on utilise souvent la clause GROUP BY avec des fonctions d'agrégation telles que SUM, AVG, MAX, MIN et COUNT. La fonction
d'agrégation qui apparaît dans la clause SELECT fournit les informations de chaque groupe.

• Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 203

 02 - Réaliser des requêtes SQL
Requêtes de sélection

HAVING

La clause HAVING est utilisée dans l'instruction SELECT pour spécifier des conditions de filtre pour un groupe de lignes ou d'agrégats. Elle est
souvent utilisée avec GROUP BY pour filtrer les groupes en fonction d'une condition spécifiée. Si la clause GROUP BY est omise, HAVING se
comporte comme la clause WHERE.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 204

 02 - Réaliser des requêtes SQL
Requêtes de sélection

ORDER BY

• La clause ORDER BY est utilisée pour trier les lignes du jeu de résultats. Elle peut porter sur plusieurs colonnes, chacune suivie, en option, de
l'ordre de tri utilisé : ascendant ASC ou descendant DESC. L'ordre de tri par défaut étant ASC.

• Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 205

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Vues
4. Tables temporaires
5. Expression du SGBD
6. Fonctions d'agrégation du SGBD
7. Sous requêtes
8. Requêtes de l'union
9. Jointures
 02 - Réaliser des requêtes SQL
Les vues

Les vues

▪ Les vues sont des objets de base de données qui permettent d'enregistrer une requête particulière sous forme de table. Cela permet
d'enregistrer les résultats afin de pouvoir les utiliser ultérieurement.

▪ CREATE VIEW `nom_view` AS ‘requête_select’

CREATE VIEW vw_clients_rabat AS

SELECT * FROM clients
WHERE UPPER(adresse)=UPPER("rabat")

▪ Remarques

• Les vues dans MySQL sont interrogeables, ce qui signifie qu’on peut les inclure dans une autre requête, un peu comme une table
dans MySQL.
PARTIE 2

• Les vues peuvent également être mises à jour car on peut insérer, mettre à jour et supprimer des lignes dans la table sous-jacente
si la vue ne peut pas contenir les éléments suivants : MIN, MAX, SUM, AVG, COUNT, DISTINCT, GROUP BY, HAVING, UNION, LEFT
JOIN ou OUTER JOIN.

Copyright - Tout droit réservé - OFPPT 207

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Vues
4. Tables temporaires
5. Expression du SGBD
6. Fonctions d'agrégation du SGBD
7. Sous requêtes
8. Requêtes de l'union
9. Jointures
 02 - Réaliser des requêtes SQL
Les tables temporaires

Les tables temporaires

• Une table temporaire est un type spécial de table qui permet de stocker un ensemble de résultats temporaires, qu’on peut
réutiliser plusieurs fois au cours d'une même session.

• Une table temporaire est très pratique lorsqu'il est impossible ou coûteux d'interroger des données nécessitant une seule
instruction SELECT avec les clauses JOIN. Dans ce cas, on peut utiliser une table temporaire pour stocker le résultat immédiat et
utiliser une autre requête pour le traiter.

• Une table temporaire possède les fonctionnalités spécialisées suivantes :

• MySQL supprime automatiquement la table temporaire lorsque la session se termine ou que la connexion est interrompue.

• On peut utiliser l'instruction DROP TABLE pour supprimer explicitement une table temporaire lorsqu’elle n’est plus utilisée.

• Une table temporaire n'est disponible et accessible qu'au client qui la crée.
PARTIE 2

• Une table temporaire peut avoir le même nom qu'une table normale dans une base de données, dans ce cas, cette
dernière est masquée par la table temporaire dans les requêtes.

Copyright - Tout droit réservé - OFPPT 209

 02 - Réaliser des requêtes SQL
Les tables temporaires

Création d'une table temporaire

CREATE TEMPORARY TABLE nom_table(
column_1_definition, column_2_definition, ...,
table_constraints );

▪ Ou bien à partir du résultat d'un SELECT :

CREATE TEMPORARY TABLE temp_nom_table

SELECT * FROM original_table
LIMIT 0;

Suppression d'une table temporaire

▪ DROP TEMPORARY TABLE nom_table;

PARTIE 2

Copyright - Tout droit réservé - OFPPT 210

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Vues
4. Tables temporaires
5. Expression du SGBD
6. Fonctions d'agrégation du SGBD
7. Sous requêtes
8. Requêtes de l'union
9. Jointures
 02 - Réaliser des requêtes SQL
Expression du SGBD

Expression du SGBD
• Une expression se compose d'ensemble de colonnes, constantes et fonctions combinées au moyen d'opérateurs. On trouve les expressions
dans les différentes parties du SELECT : en tant que colonne résultat, dans les clauses WHERE, HAVING, et ORDER BY.

• Il existe trois types d'expressions selon le type de données de SQL :

Expressions de chaînes de
Expressions arithmétiques Expressions de dates
caractères

• A chaque type correspondent des opérateurs et des fonctions spécifiques.

• Afin d'utiliser des données de types différents dans la même expression, on peut utiliser les fonctions de conversion dont dispose le langage
SQL. Celles-ci permettent de convertir des chaînes de caractères en date ou en nombre selon le besoin.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 212

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL

• Un opérateur est un symbole spécifiant une action exécutée sur une ou plusieurs expressions. On trouve en SQL, différentes catégories
d'opérateurs.

• Voici les principaux opérateurs utilisables dans les requêtes de sélection.

Opérateurs arithmétiques Opérateurs de comparaison Opérateurs logiques

PARTIE 2

Copyright - Tout droit réservé - OFPPT 213

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs arithmétiques

• Les opérateurs arithmétiques présents dans SQL sont les suivants :

• + addition

• - soustraction

• * multiplication

• / division

• Remarque : la division par 0 provoque une fin avec code d'erreur.

PARTIE 2

Copyright - Tout droit réservé - OFPPT 214

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs arithmétiques

Priorité des opérateurs

• Une expression arithmétique peut comporter plusieurs opérateurs. Dans ce cas, le résultat de l'expression peut varier selon l'ordre dans lequel
sont effectuées les opérations. Les opérateurs de multiplication et de division sont prioritaires par rapport aux opérateurs d'addition et de
soustraction. Des parenthèses peuvent être utilisées pour forcer l'évaluation de l'expression dans un ordre différent de celui découlant de la
priorité des opérateurs.

• Au moyen des opérateurs arithmétiques + et -, il est possible de construire les expressions suivantes :

• date +/- nombre : le résultat est une date obtenue en ajoutant/soustrayant le nombre de jours à la date.

• date2 - date1 : le résultat est le nombre de jours entre les deux dates.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 215

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs arithmétiques

Exemple : Calcul du gain = Prix-Cout pour chacun des produits :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 216

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs de comparaison

• Les opérateurs de comparaison testent si deux expressions sont vraies.

• Ils peuvent s'utiliser sur toutes les expressions composées de données structurées.

• Ces opérateurs sont :

= (Égal à) > (Supérieur à) < (Inférieur à)

>= (Supérieur ou <= (Inférieur ou

<> (Différent de)
égal à) égal à)
PARTIE 2

Copyright - Tout droit réservé - OFPPT 217

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs logiques

• Les opérateurs logiques testent la valeur logique d'une condition.
• Les opérateurs logiques, comme les opérateurs de comparaison, retournent un type de données booléen de valeur TRUE ou FALSE.
• Un certain nombre d'entre eux (signalés par un * dans le tableau) sont utilisés pour comparer une valeur scalaire (unique) avec une sous
requête.
Opérateur Description
ALL (*) TRUE si tous les éléments d'un jeu de comparaison sont TRUE
AND TRUE si les deux expressions booléennes sont TRUE
ANY (*) TRUE si n'importe quel élément d'un jeu de comparaison est TRUE
BETWEEN TRUE si l'opérande est situé dans une certaine plage
EXISTS(*) TRUE si une sous-requête contient des lignes
IN(*) TRUE si l'opérande est égal à un élément dans la liste d'expressions
LIKE TRUE si l'opérande correspond à un modèle
PARTIE 2

NOT Inverse la valeur de tout autre opérateur booléen

OR TRUE si l'une ou l'autre expression booléenne est TRUE
SOME(*) TRUE si certains éléments d'un jeu de comparaison sont TRUE
* Utilisé avec des sous-requêtes

Copyright - Tout droit réservé - OFPPT 218

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs logiques

Opérateur BETWEEN

• On utilise BETWEEN pour tester si une valeur est comprise entre une valeur minimale et une autre maximale. La syntaxe de l'opérateur
BETWEEN : valeur BETWEEN Minimum AND Maximum
Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 219

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les opérateurs MySQL : Opérateurs logiques

Opérateur LIKE/NOT LIKE
• On utilise l'opérateur LIKE pour tester si une valeur correspond à un modèle spécifique. L'opérateur NOT sert à annuler l'opérateur LIKE.
• Le modèle est définit en utilisant les caractères génériques suivants :
Caractère générique Description
% Toute chaîne de 0 caractère ou plus
- N'importe quel caractère à cet emplacement
[] Tout caractère de l'intervalle ([a-f]) ou de l'ensemble spécifié ([abef])
[^] Tout caractère en dehors de l'intervalle ([^a-f]) ou de l'ensemble (^adef). Le ^représente le NOT
Exemples :
PARTIE 2

La liste des produits dont la description commence par 'L' La liste des produits qui ont '2' dans la case avant dernière de leur Num_Produit
Copyright - Tout droit réservé - OFPPT 220
 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL

• MySQL, comme les autres SGBD, propose de nombreuses fonctions intégrées qui permettent de manipuler les données utilisateurs ou les
données du système.

• Il existe plusieurs catégories de fonctions :

• Fonctions mathématiques

• Fonctions de traitement de chaînes

• Fonctions de manipulation de dates

• Fonctions de conversion

• La liste exhaustive des fonctions MySQL est disponible sur le lien : https ://dev.MySQL.com/doc/refman/8.0/en/functions.html
PARTIE 2

Copyright - Tout droit réservé - OFPPT 221

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL : Fonctions mathématiques

Ce sont des fonctions ayant un ou plusieurs nombres comme arguments, et qui renvoient une valeur numérique.

Voici quelques exemples

Fonction Description

ABS() Retourne la valeur absolue d'un nombre

CEIL() Renvoie la plus petite valeur entière supérieure ou égale au nombre d'entrée

FLOOR() Renvoie la plus grande valeur entière non supérieure au nombre d'entrée

MOD() Renvoie le reste d'un nombre divisé par un autre

ROUND() Arrondit un nombre à un nombre spécifié de places décimales

PARTIE 2

TRUNCATE() Tronque un nombre à un nombre spécifié de places décimales

Copyright - Tout droit réservé - OFPPT 222

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL : Fonctions de traitement de chaînes

Voici une liste contenant les fonctions de chaîne MySQL les plus utilisées qui permettent de manipuler efficacement les données de chaîne de
caractères.
Name Description
CONCAT Concaténer deux ou plusieurs chaînes en une seule
INSTR Renvoyer la position de la première occurrence d'une sous-chaîne dans une chaîne
LENGTH Obtenir la longueur d'une chaîne en octets et en caractères
LEFT Obtenir un nombre spécifié de caractères les plus à gauche d'une chaîne
LOWER Convertir une chaîne en minuscule
LTRIM Supprimer tous les espaces du début d'une chaîne
REPLACE Rechercher et remplacer une sous-chaîne dans une chaîne
RIGHT Retourner un nombre spécifié de caractères les plus à droite d'une chaîne
RTRIM Supprimer tous les espaces de la fin d'une chaîne
SUBSTRING Extraire une sous-chaîne à partir d'une position avec une longueur spécifique
PARTIE 2

SUBSTRING_INDEX Renvoyer une sous-chaîne à partir d'une chaîne avant un nombre spécifié d'occurrences d'un délimiteur
TRIM Supprimer les caractères indésirables d'une chaîne
FIND_IN_SET Rechercher une chaîne dans une liste de chaînes séparées par des virgules
FORMAT Mettre en forme une chaîne avec le format spécifié
UPPER Convertir une chaîne en majuscule
Copyright - Tout droit réservé - OFPPT 223
 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL : Fonctions de traitement de chaînes

Exemple :
• Pour chaque produit, retourner les valeurs suivantes :
• Num_Produit
• Une colonne « resultat » qui contient : Num_Produit ‘ajoute le' Date_ajout
• Une colonne « nouveau » qui contient : Remplacer 'P' dans Num_Produit par 'NP'.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 224

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL : Fonctions de manipulation de dates

Name Description
CURDATE Renvoie la date actuelle.
DATEDIFF Calcule le nombre de jours entre deux valeurs DATE.
DAY Obtient le jour du mois d'une date spécifiée.
DATE_ADD Ajoute une valeur de temps à la valeur de date.
DATE_SUB Soustrait une valeur d'heure d'une valeur de date.
DATE_FORMAT Formate une valeur de date en fonction d'un format de date spécifié.
DAYNAME Obtient le nom d'un jour de la semaine pour une date spécifiée.
DAYOFWEEK Renvoie l’indice des jours de la semaine pour une date.
EXTRACT Extrait une partie d'une date.
LAST_DAY Renvoie le dernier jour du mois d'une date spécifiée.
NOW Renvoie la date et l'heure actuelles d'exécution de l'instruction.
MONTH Renvoie un entier qui représente un mois d'une date spécifiée.
STR_TO_DATE Convertit une chaîne en une valeur de date et d'heure basée sur un format spécifié.
PARTIE 2

SYSDATE Renvoie la date actuelle.

TIMEDIFF Calcule la différence entre deux valeurs TIME ou DATETIME.
TIMESTAMPDIFF Calcule la différence entre deux valeurs DATE ou DATETIME.
WEEK Renvoie le numéro de semaine d'une date.
WEEKDAY Renvoie un indice des jours de la semaine pour une date.
YEAR Renvoie l'année pour une date spécifiée.
Copyright - Tout droit réservé - OFPPT 225
 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL : Fonctions de manipulation de dates

Exemple :
• La liste des produits, leur date d'ajout, l'année d'ajout, le jour de la semaine, et depuis combien de jours ils ont été ajoutés.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 226

 02 - Réaliser des requêtes SQL
Expression du SGBD

Les fonctions intégrées MySQL : Fonctions de conversion

• TO_CHAR(nombre,format) : Renvoie la chaîne de caractères en convertissant le nombre en fonction du format.

• TO_CHAR(date,format) : Renvoie la conversion d'une date en chaîne de caractères. Le format indique quelle partie de la date doit apparaître.

• TO_DATE(chaîne,format) : Permet de convertir une chaîne de caractères en donnée de type date. Le format est identique à celui de la
fonction TO_CHAR().

• TO_NUMBER(chaine) : Convertit ‘chaine’ en sa valeur numérique.

PARTIE 2

Copyright - Tout droit réservé - OFPPT 227

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Expression du SGBD
4. Fonctions d'agrégation du SGBD
5. Sous requêtes
6. Requêtes de l'union
7. Jointures
 02 - Réaliser des requêtes SQL
Fonctions d'agrégation du SGBD

• Une fonction d'agrégation effectue un calcul sur plusieurs valeurs et renvoie une seule valeur.
• Les fonctions d'agrégation les plus utilisés : SUM, AVG, COUNT, MAX et MIN.
• Les fonctions d'agrégation sont souvent utilisées avec la clause GROUP BY pour calculer une valeur agrégée pour chaque groupe, par exemple la
valeur moyenne par groupe ou la somme des valeurs dans chaque groupe.

La fonction SUM()
• La fonction SUM() retourne la somme des valeurs d'une colonne.
• L’exemple suivant illustre l'utilisation de la fonction d'agrégation SUM() avec une clause GROUP BY :

Nom Valeur ∑
A 10 SELECT
Nom SUM (Valeur)
Nom,
A 20 SUM(Valeur) A 30
FROM
PARTIE 2

B 40
Sample_table B 40
C 20 GROUP BY
Nom; C 70
C 50

Copyright - Tout droit réservé - OFPPT 229

 02 - Réaliser des requêtes SQL
Fonctions d'agrégation du SGBD

La fonction SUM()
Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 230

 02 - Réaliser des requêtes SQL
Fonctions d'agrégation du SGBD

La fonction AVG()

La fonction AVG() retourne la moyenne des valeurs d'une colonne.

Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 231

 02 - Réaliser des requêtes SQL
Fonctions d'agrégation du SGBD

La fonction COUNT()
La fonction COUNT() retourne le nombre d'enregistrements sélectionnés.
Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 232

 02 - Réaliser des requêtes SQL
Fonctions d'agrégation du SGBD

La fonction MAX/MIN

Les fonctions MAX() et MIN() retournent respectivement le maximum et le minimum des valeurs des enregistrements sélectionnés.
Exemple :

MAX (Prix) MIN (Prix)

PARTIE 2

Copyright - Tout droit réservé - OFPPT 233

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Expression du SGBD
4. Fonctions d'agrégation du SGBD
5. Sous requêtes
6. Requêtes de l'union
7. Jointures
 02 - Réaliser des requêtes SQL
Sous requêtes

• Une sous-requête est une requête imbriquée dans une autre requête telle que SELECT, INSERT, UPDATE ou DELETE.

• Une sous-requête est appelée « requête interne » tandis que la requête qui contient la sous-requête est appelée une requête externe. La
requête dite interne est évaluée pour chaque ligne de la requête externe.

• Une sous-requête peut être utilisée partout où une expression est utilisée et doit être fermée entre parenthèses.

Exemple

• La table Produits :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 235

 02 - Réaliser des requêtes SQL
Sous requêtes

Sous requête Mono-ligne

• On utilise des opérateurs de comparaison, par exemple =, >, < pour comparer une seule valeur renvoyée par la sous-requête avec l'expression
dans la clause WHERE.

• Afficher le produit ayant le prix minimal :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 236

 02 - Réaliser des requêtes SQL
Sous requêtes

Sous requête avec IN/NOT IN

• Si une sous-requête renvoie plusieurs valeurs, on peut utiliser d'autres opérateurs tels que l'opérateur IN ou NOT IN dans la clause WHERE.

• Afficher la liste des produits de la table Produits qui existent dans la table Sales :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 237

 02 - Réaliser des requêtes SQL
Sous requêtes

Sous requête avec ALL/ANY

• On peut utiliser les opérateurs ANY, ou ALL pour comparer la valeur d'une expression avec les valeurs d'un attribut d'une requête interne.

• Le mot-clé ALL spécifie tous les éléments retournés tandis que le mot-clé ANY spécifie l'un d'entre eux.

• Afficher les produits dont le cout est Inferieur à tous les prix des produits.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 238

 02 - Réaliser des requêtes SQL
Sous requêtes

Sous requête avec EXISTS/NOT EXISTS

• Lorsqu'une sous-requête est utilisée avec l'opérateur EXISTS ou NOT EXISTS, une sous-requête renvoie une valeur booléenne TRUE ou FALSE.

• Afficher les produits avec une colonne supplémentaire indiquant si le produit est présent dans la table Sales :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 239

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Expression du SGBD
4. Fonctions d'agrégation du SGBD
5. Sous requêtes
6. Requêtes de l'union
7. Jointures
 02 - Réaliser des requêtes SQL
Requêtes de l'union

• La combinaison des résultats des requêtes consiste à transformer deux ou plusieurs jeux de résultat en un seul.

• Les jeux de résultats combinés doivent tous avoir la même structure : même nombre de colonnes et même types de données.

• Il existe trois types de combinaison : UNION (UNION All), INTERSECT, MINUS.

Opérateur UNION
• L'opérateur UNION permet de combiner deux ou plusieurs ensembles de résultats de requêtes en un seul ensemble de résultats. Voici la
syntaxe d'utilisation de l'opérateur UNION :

SELECT column_list1
UNION [DISTINCT | ALL]
SELECT column_list2
UNION [DISTINCT | ALL]
PARTIE 2

SELECT column_list3
...

Copyright - Tout droit réservé - OFPPT 241

 02 - Réaliser des requêtes SQL
Requêtes de l'union

Opérateur UNION
• Par défaut, l'opérateur UNION supprime les lignes en double même si on ne spécifie pas l'opérateur DISTINCT.
• Soient les deux tables :
Group1 Group2
ID ID
1 2
2 3
3 4

• La requête UNION :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 242

 02 - Réaliser des requêtes SQL
Requêtes de l'union

Opérateur UNION ALL

• Si on utilise UNION ALL explicitement, les lignes dupliquées, sont affichées dans le résultat. Du fait que UNION ALL ne gère pas les doublons, il
s'exécute plus rapidement que UNION DISTINCT.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 243

 02 - Réaliser des requêtes SQL
Requêtes de l'union

Opérateur UNION
• Le diagramme de Venn suivant illustre l'union de deux ensembles de résultats provenant des tables Group1 et Group2 :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 244

 02 - Réaliser des requêtes SQL
Requêtes de l'union

Opérateur INTERSECT

• L'opérateur INTERSECT compare les ensembles de résultats de deux requêtes ou plus et renvoie les lignes distinctes générées par les deux
requêtes.

• MySQL ne prend pas en charge l'opérateur INTERSECT. Cependant, on peut l'émuler en utilisant les jointures.

• Le schéma suivant illustre l'opérateur INTERSECT :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 245

 02 - Réaliser des requêtes SQL
Requêtes de l'union

Opérateur INTERSECT

Contrairement à l'opérateur UNION, l'opérateur INTERSECT renvoie l'intersection entre deux cercles.

• Donc la requête :

(SELECT ID FROM Group1)

INTERSECT
(SELECT ID FROM Group2)

• Renvoie :
ID
2
3
PARTIE 2

Copyright - Tout droit réservé - OFPPT 246

 02 - Réaliser des requêtes SQL
Requêtes de l'union

Opérateur MINUS (EXCEPT)

• L'opérateur MINUS compare les résultats de deux requêtes et renvoie des lignes distinctes du jeu de résultats de la première requête qui
n'apparaissent pas dans le jeu de résultats de la deuxième requête. MySQL ne prend pas en charge l'opérateur MINUS. Cependant, On peut
l'émuler en utilisant les jointures.

• Le schéma suivant illustre l'opérateur MINUS :

• Donc la requête :
•
(SELECT ID FROM Group1)
MINUS
PARTIE 2

(SELECT ID FROM Group2)

• Revoie : ID
1

Copyright - Tout droit réservé - OFPPT 247

 CHAPITRE 2
Réaliser des requêtes SQL

1. Requêtes LMD
2. Requêtes de sélection
3. Expression du SGBD
4. Fonctions d'agrégation du SGBD
5. Sous requêtes
6. Requêtes de l'union
7. Jointures
 02 - Réaliser des requêtes SQL
Jointures

• Une base de données relationnelle se compose de plusieurs tables liées entre elles à l'aide de colonnes communes, appelées clés étrangères.

• Dans l'exemple « centre de formation » déjà présenté dans ce cours on trouve que les deux tables « etudiant » et « inscription » sont liées a
l'aide de la colonne : numCINEtu
PARTIE 2

Copyright - Tout droit réservé - OFPPT 249

 02 - Réaliser des requêtes SQL
Jointures

• Afin d'avoir plus d'information sur les étudiants ou les inscriptions, on a besoin de chercher dans les deux tables à la fois. D'où la nécessité des
jointures.

• La jointure consiste à rechercher entre deux tables ayant un attribut commun (même type et même domaine de définition) tous les tuples (les
lignes) pour lesquels ces attributs ont la même valeur.

• MySQL prend en charge les types de jointures suivants :

• INNER JOIN (Jointure interne)

• LEFT JOIN (Jointure gauche)

• RIGHT JOIN (Jointure à droite)

• CROSS JOIN (Jointure croisée)

• Pour joindre des tables, on utilise la clause de jointure dans l'instruction SELECT après la clause FROM.
PARTIE 2

• Noter que MySQL ne prend pas en charge la jointure FULL OUTER JOIN.

Copyright - Tout droit réservé - OFPPT 250

 02 - Réaliser des requêtes SQL
Jointures

INNER JOIN

• INNER JOIN joint deux tables en fonction d'une condition connue sous le nom de prédicat de jointure.

• Elle spécifie ainsi toutes les paires correspondantes de lignes renvoyées et ignore les lignes n'ayant pas de correspondance entre les deux
tables.

• La clause INNER JOIN ne retient que les lignes des deux tables pour lesquelles l'expression exprimée au niveau de ON se vérifie.

Exemple :
PARTIE 2

Produits Sales

Copyright - Tout droit réservé - OFPPT 251

 02 - Réaliser des requêtes SQL
Jointures

INNER JOIN
• Afin d’afficher, pour chaque produit vendu, son prix, sa description et la quantité qui a été vendue, on a besoin de joindre les deux tables
comme suit :
SELECT
P.Num_Produit,
P.Description,
P.prix,
S.quantite

FROM
Produits P
INNER JOIN Sales S ON P.Num_Produit=S.Num_Produit;

• Dans l’exemple, INNER JOIN utilise les valeurs des colonnes de « Num_Produit » dans les tables « Produits » et « Sales » pour faire la
correspondance.
PARTIE 2

• Le diagramme de Venn suivant illustre la jointure interne :

Copyright - Tout droit réservé - OFPPT 252

 02 - Réaliser des requêtes SQL
Jointures

INNER JOIN
• Si la colonne de jointure a le même nom dans les deux tables objets de la jointure, on peut utiliser la syntaxe suivante :

SELECT
P.Num_Produit,
P.Description,
P.prix,
S.quantite

FROM
Produits P
INNER JOIN Sales S USING (Num_Produit);
PARTIE 2

Copyright - Tout droit réservé - OFPPT 253

 02 - Réaliser des requêtes SQL
Jointures

LEFT JOIN

• Lors de la jointure de deux tables à l'aide d'une jointure gauche, les concepts de tables gauche et droite sont introduits.

• La jointure gauche sélectionne les données à partir de la table de gauche. Pour chaque ligne de la table de gauche, la jointure de gauche est
comparée à chaque ligne de la table de droite.

• Si les valeurs des deux lignes satisfont la condition de jointure, la clause de jointure gauche crée une nouvelle ligne, dont les colonnes sont les
colonnes des lignes des deux tables, et inclut cette ligne dans le jeu de résultats.

• Si les valeurs des deux lignes ne correspondent pas, la clause de jointure gauche crée toujours une nouvelle ligne dont les colonnes contiennent
les colonnes de la ligne de la table de gauche et NULL pour les colonnes de la ligne de la table de droite.

• En d'autres termes, la jointure gauche sélectionne toutes les données de la table de gauche, qu'il existe ou non des lignes correspondantes dans
la table de droite.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 254

 02 - Réaliser des requêtes SQL
Jointures

LEFT JOIN
Exemple :
SELECT
P.Num_Produit,
P.Description,
P.prix,
S.quantite

FROM
Produits P
LEFT JOIN Sales S ON P.Num_Produit=S.Num_Produit;

LEFT JOIN Sales S USING(Num_Produit) ;

Ou alors :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 255

 02 - Réaliser des requêtes SQL
Jointures

LEFT JOIN
• Le résultat de l’exemple précédent est :

• Le diagramme de Venn suivant illustre la jointure gauche :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 256

 02 - Réaliser des requêtes SQL
Jointures

RIGHT JOIN
• La clause de jointure droite est similaire à la clause de jointure gauche sauf que le traitement des tables gauche et droite est inversé.
• La jointure droite commence à sélectionner les données de la table de droite au lieu de la table de gauche : RIGHT JOIN sélectionne toutes les
lignes de la table de droite et fait correspondre les lignes de la table de gauche.
• Si une ligne de la table de droite n'a pas correspondances dans la table de gauche, la colonne de la table de gauche aura NULL dans le jeu de
résultats final.

Exemple :

SELECT
S.Num_Produit,
S.Quantite,
P.prix
FROM
Sales S
PARTIE 2

Right JOIN Produits P ON P.Num_Produit=S.Num_Produit;

• Ou alors
RIGHT JOIN Produits S USING(Num_Produit) ;

Copyright - Tout droit réservé - OFPPT 257

 02 - Réaliser des requêtes SQL
Jointures

RIGHT JOIN
• Le résultat de l’exemple précédent est :

• Le diagramme de Venn suivant illustre la jointure droite :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 258

 02 - Réaliser des requêtes SQL
Jointures

CROSS JOIN
Contrairement à autres types de jointures, la jointure croisée n'a pas de condition de jointure. Elle crée le produit cartésien des lignes des tables
jointes. La jointure croisée combine chaque ligne de la première table avec chaque ligne de la table de droite pour créer le jeu de résultats.

Exemple :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 259

 02 - Réaliser des requêtes SQL
Jointures

SELF JOIN

• L'auto-jointure est souvent utilisée pour interroger des données hiérarchiques ou pour comparer une ligne avec d'autres lignes dans la même
table.

• Pour effectuer une auto-jointure, on utilise des alias de la table pour ne pas répéter deux fois le même nom de table dans la même requête.

N.B : Référencer une table deux fois ou plus dans une requête sans utiliser d'alias de table provoquera une erreur.

Exemple :

• La table « People » est définie ainsi :

PARTIE 2

• La colonne IdParent définit le père de chaque personne, qui est aussi un élément de la table « People ».

Copyright - Tout droit réservé - OFPPT 260

 02 - Réaliser des requêtes SQL
Jointures

SELF JOIN

• Afin d'avoir la liste des personnes et leurs parents, il faut utiliser une auto-jointure.

• Au moyen de INNER JOIN :

SELECT c.Nom as Fils, p.nom as Pere
FROM people c
INNER JOIN people p on c.idParent = p.idPersonne;

• Le résultat donne uniquement les personnes ayant un parent définit :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 261

 CHAPITRE 3
Administrer une base de données

Ce que vous allez apprendre dans ce chapitre :

• Maîtriser les différentes fonctions liées à l'administration des

BD
• S’initier avec les commandes de gestion des comptes et de
privilèges de base

05 heures
 CHAPITRE 3
Administrer une base de données

1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes utilisateurs
5. Commandes de gestion des privilèges de base
 03 - Administrer une base de données
Backup/Restore

Backup

L'outil MySQLdump permet de sauvegarder une ou plusieurs bases de données. Il génère un fichier texte contenant les instructions SQL qui
peuvent recréer les bases de données. MySQLdump est situé dans le répertoire root/bin du répertoire d'installation de MySQL.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 264

 03 - Administrer une base de données
Backup/Restore

Backup d'une ou plusieurs bases de données

• La commande pour faire un backup avec MySQLdump :

MySQLdump --user=<username>
--password=<password>
--result-file=<Lien_Fichier_Backup>
--databases <Liste_des_databases>

• Dans cette syntaxe on doit définir :

• Username et password : le nom et le mot de passe de l'utilisateur qui est connecté sur MySQL
• Lien du fichier du backup
• Le/les noms des bases de données qu'on veut sauvegarder.
• Pour faire un backup de plusieurs bases de données à la fois on suit la syntaxe suivante : --databases Nom_Base1, Nom_Base2,, etc.
PARTIE 2

• Si on veut faire un backup de toutes les bases de données d'une instance MySQL, on remplace l'option : --databases <Liste_des_databases>
par : --all-databases

Copyright - Tout droit réservé - OFPPT 265

 03 - Administrer une base de données
Backup/Restore

Backup d'une ou plusieurs bases de données

Exemple : Backup de la base de données « dbtest »

PARTIE 2

Copyright - Tout droit réservé - OFPPT 266

 03 - Administrer une base de données
Backup/Restore

Backup d'une ou plusieurs bases de données

Exemple : Backup de la base de données « dbtest »
Le contenu du fichier dbtest_backup.sql après exécution :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 267

 03 - Administrer une base de données
Backup/Restore

Backup d'une ou plusieurs tables d'une base de données

Afin de faire un backup de tables spécifiques d'une base de données, on exécute :

MySQLdump --user=<username> --password=<password>

--result-file=<path_to_backup_file> <Nom_Base>
<table1> <table2> <table3>..

Exemple :
Réaliser le backup des tables « Produits » et « Sales » de la base de données « dbtest »

MySQLdump --user=root --password=Mypassword4@

--result-file=c :\backup\backup_tables.sql dbtest

produits sales
PARTIE 2

Copyright - Tout droit réservé - OFPPT 268

 03 - Administrer une base de données
Backup/Restore

Backup de la structure ou les données d'une bases de données

• L'outil MySQLdump permet aussi de sauvegarder juste la structure ou juste les données d'une base de données en utilisant respectivement les
options : --no-data et –-no-create-info
• Structure seulement :
MySQLdump --user=<username>
--password=<password>
--result-file=<Lien_Fichier_Backup>
--no-data
--databases <Liste_des_databases>

• Données seulement :
MySQLdump --user=<username>
PARTIE 2

--password=<password>
--result-file=<Lien_Fichier_Backup>
--no-create-info
--databases <Liste_des_databases>

Copyright - Tout droit réservé - OFPPT 269

 CHAPITRE 3
Administrer une base de données

1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes utilisateurs
5. Commandes de gestion des privilèges de base
 03 - Administrer une base de données
Importation

Afin d'importer des données sous forme de fichier sql sur MySQL à partir de la ligne de commande, on peut utiliser la commande source comme
dans le cas du restore.

Exemple :

MySQL>source c :\backup\fichier_backup.sql

Il est recommandé d'utiliser la commande source pour restaurer une base de données car elle renvoie des informations très détaillées sur le
processus, notamment des avertissements et des erreurs.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 271

 03 - Administrer une base de données
Importation

L'utilitaire Import data dans Workbench permet aussi de réaliser cette tâche en suivant ces étapes :

1. Ouvrir MySQL Workbench

2. Dans la liste des MySQL Connexions, choisir la base de données

3. Cliquer sur Data Import à partir de l'élément Server dans le menu de navigation
PARTIE 2

Copyright - Tout droit réservé - OFPPT 272

 03 - Administrer une base de données
Importation

4. Dans le volet « Data Import from Disk », Section « Import Options », choisir « Import from Self-Contained File », et sélectionner le fichier
SQL qui contient les données à importer.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 273

 03 - Administrer une base de données
Importation

5. Choisir le schéma cible par défaut (Default Target Schema) où les données seront importées. On peut également créer une nouvelle base
de données en choisissant New (Nouveau)

6. Passer sur le volet : Import Progress et choisir Start Import (Démarrer l'importation) pour lancer l'importation
PARTIE 2

Copyright - Tout droit réservé - OFPPT 274

 03 - Administrer une base de données
Importation

7. L’importation peut prendre quelques minutes ou plus en fonction de la taille du fichier « .SQL ». Une fois l'importation terminée, un
message semblable à ce qui suit s’affiche :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 275

 03 - Administrer une base de données
Importation

Importer des données vers une table

• MySQL Workbench fournit un outil pour importer des données dans une table. Il permet de modifier les données avant de les charger.

• Voici les étapes à suivre pour importer des données dans une table :
1. Ouvrir la table dans laquelle on veut importer des données
2. Cliquer sur l'icône « Import records from an external file »
PARTIE 2

Copyright - Tout droit réservé - OFPPT 276

 03 - Administrer une base de données
Importation

Importer des données vers une table

3. Naviguer vers le fichier qui contient les données.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 277

 03 - Administrer une base de données
Importation

Importer des données vers une table

4. Choisir d'importer les données vers une table qui existe déjà, ou en créer une nouvelle.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 278

 03 - Administrer une base de données
Importation

Importer des données vers une table

5. Ensuite on peut vérifier que le type du ficher choisi a été bien détecté et réaliser le mapping des colonnes du fichier avec celles de la
table.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 279

 03 - Administrer une base de données
Importation

Importer des données vers une table

6. Cliquer sur Next pour l’importation.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 280

 CHAPITRE 3
Administrer une base de données

1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes utilisateurs
5. Commandes de gestion des privilèges de base
 03 - Administrer une base de données
Exportation

Exporter la base de données

L'utilitaire Export data dans Workbench permet d'exporter les données d'une base de données en suivant ces étapes :

1. Ouvrir MySQL Workbench. Dans la liste des MySQL Connexions, choisir la base de données, puis naviguer dans le menu Server et cliquer
sur « Export Data »
PARTIE 2

Copyright - Tout droit réservé - OFPPT 282

 03 - Administrer une base de données
Exportation

Exporter la base de données

• Sur le volet « Objet Selection »

2. Choisir le schéma qui contient les données à exporter

3. Choisir les objets à exporter (Tables, Vues, etc.)

4. La liste déroulante permet de préciser s'il s'agit d'exporter

la structure des objets sélectionnés, les données qu'ils
contiennent ou les deux. On peut aussi choisir d'exporter
d'autres objets comme les procédures stockées, les
Triggers, etc.

5. Sélectionner le fichier cible qui va contenir le script des

PARTIE 2

données exportées

Copyright - Tout droit réservé - OFPPT 283

 03 - Administrer une base de données
Exportation

Exporter la base de données

Sur le volet « Export Progress »

6. Cliquer sur « Start Export » pour commencer le process

PARTIE 2

Copyright - Tout droit réservé - OFPPT 284

 03 - Administrer une base de données
Exportation

Exporter la base de données

7. Le système confirme l'exportation :
PARTIE 2

Copyright - Tout droit réservé - OFPPT 285

 03 - Administrer une base de données
Exportation

Exporter les données d'une table

MySQL Workbench fournit un outil pour exporter les données d'une table. Voici les étapes à suivre :
1. Ouvrir la table de laquelle on veut exporter des données
2. Cliquer sur l'icône « Export recordset to an external file »
PARTIE 2

Copyright - Tout droit réservé - OFPPT 286

 03 - Administrer une base de données
Exportation

Exporter les données d'une table

3. Choisir le type et l'emplacement du fichier data à créer, et cliquer sur Ok
PARTIE 2

Copyright - Tout droit réservé - OFPPT 287

 CHAPITRE 3
Administrer une base de données

1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes utilisateurs
5. Commandes de gestion des privilèges de base
 03 - Administrer une base de données
Commandes de création des comptes utilisateurs

• On utilise la commande CREATE USER pour créer de nouveaux utilisateurs dans le serveur de base de données MySQL.

• Voici la syntaxe de base de CREATE USER :

CREATE USER [IF NOT EXISTS] nom_compte

IDENTIFIED BY 'mot_de_passe';

• Dans cette expression il faut spécifier :

• nom_compte : Il s'agit du nom du compte à créer et se compose en général de deux parties sous cette forme :
nom_utilisateur@nom_host.

• nom_utilisateur : est le nom de l'utilisateur.

• nom_host : est le nom de l'hôte à partir duquel l'utilisateur se connecte au serveur MySQL. La partie nom d'hôte du nom de compte est
PARTIE 2

optionnelle. Si elle est omise, l'utilisateur peut se connecter depuis n'importe quel hôte.

• mot_de_passe : Le mot de passe relatif au nouveau compte.

• CREATE USER crée un nouvel utilisateur sans aucun privilège.

Copyright - Tout droit réservé - OFPPT 289

 03 - Administrer une base de données
Commandes de création des comptes utilisateurs

Exemple :

• Sur la ligne de commande MySQL, on liste les utilisateurs existants :

SELECT USER from MySQL.user

• On peut vérifier sur la table MySQL.user la création du nouveau compte :

PARTIE 2

CREATE USER Ahmad@localhost identified by 'Monmot2p@ss'

Copyright - Tout droit réservé - OFPPT 290

 03 - Administrer une base de données
Commandes de création des comptes utilisateurs

Exemple :

• Afin de tester le login, on ouvre une autre session MySQL avec le compte « Ahmad » , en utilisant la commande suivante : MySQL – u Ahmad –p

• Puis on saisit le mot de passe : Monmot2p@ss

• On peut vérifier les bases de données auxquelles Ahmad peut accéder :

PARTIE 2

Copyright - Tout droit réservé - OFPPT 291

 CHAPITRE 3
Administrer une base de données

1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes utilisateurs
5. Commandes de gestion des privilèges de base
 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT)

• La commande CREATE USER crée un ou plusieurs comptes d'utilisateurs sans privilèges.

• Pour qu'un utilisateur puisse accéder aux objets de base de données, il faut d'abord lui accorder des privilèges. Ceci se fait à l'aide de la
commande GRANT.

• Voici la syntaxe générique de GRANT : GRANT privilege [,privilege],..

ON privilege_level

TO nom_utilisateur;

• Pour donner des privilèges au compte « nom_utilisateur », il faut spécifier le/les privilèges à donner ( SELECT, DELETE, UPDATE, ALL, etc.) et sur
quel niveau les appliquer.
PARTIE 2

Copyright - Tout droit réservé - OFPPT 293

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Il existe différents niveaux de privilèges dans MySQL :

Niveau de privilèges

Global Base de données Proxy

Table Routine
PARTIE 2

Colonne

Copyright - Tout droit réservé - OFPPT 294

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Niveau de privilèges

Global Base de données Proxy

Table Routine

Colonne

1. Niveau global :

• Les privilèges globaux s'appliquent à toutes les bases de données d'un serveur MySQL. Pour attribuer des privilèges globaux, utiliser la
syntaxe *.*.

Exemple :
GRANT UPDATE
PARTIE 2

ON *.*

TO Ahmad@localhost;

Copyright - Tout droit réservé - OFPPT 295

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Niveau de privilèges

Global Base de données Proxy

Table Routine

Colonne

2. Niveau base de données :

• Ces privilèges s'appliquent à tous les objets d'une base de données. Pour attribuer des privilèges au niveau de la base de données, on
utilise la syntaxe : ON nom_base_de_données.*

Exemple :
GRANT INSERT
PARTIE 2

ON dbtest.*

TO Ahmad@localhost;

Copyright - Tout droit réservé - OFPPT 296

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Niveau de privilèges

Global Base de données Proxy

Table Routine

Colonne

3. Niveau Table :

• Les privilèges de table s'appliquent à toutes les colonnes d'une table. Pour attribuer des privilèges au niveau de la table, on utilise la
syntaxe ON nom_base_de_données.nom_table.

Exemple :
GRANT INSERT,DELETE
PARTIE 2

ON dbtest.Produits

TO Ahmad@localhost;

Copyright - Tout droit réservé - OFPPT 297

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Niveau de privilèges

Global Base de données Proxy

Table Routine

Colonne
4. Niveau Colonne :

• Les privilèges de colonne s'appliquent à des colonnes uniques dans une table. Spécifier la/les colonnes pour chaque privilège.

Exemple : GRANT

SELECT (Num_Produit,Description, Date_ajout),

PARTIE 2

UPDATE(Prix)

ON dbtest.Produits

TO Ahmad@localhost;

Copyright - Tout droit réservé - OFPPT 298

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Niveau de privilèges

Global Base de données Proxy

Table Routine

Colonne
5. Niveau Routine :

• Les privilèges de routine s'appliquent aux procédures et fonctions stockées.

Exemple : GRANT EXECUTE

ON PROCEDURE CalculPrix
PARTIE 2

TO Ahmad@localhost;

Copyright - Tout droit réservé - OFPPT 299

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Niveau de privilèges

Global Base de données Proxy

Table Routine

Colonne

6. Niveau Proxy :
• Les privilèges d'utilisateur proxy permettent à un utilisateur externe d'être un proxy pour un autre, c'est-à-dire, d'avoir les privilèges du
deuxième utilisateur. En d'autres termes, l'utilisateur externe est un "utilisateur proxy" (un utilisateur qui peut usurper l'identité ou
devenir un autre utilisateur) et le deuxième utilisateur est un "utilisateur mandaté" (un utilisateur dont l'identité peut être reprise par un
utilisateur proxy).
Exemple :
GRANT PROXY
PARTIE 2

ON root

TO Ahmad@localhost;

Copyright - Tout droit réservé - OFPPT 300

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Attribution des privilèges (GRANT) : Les niveaux de privilèges

Le tableau suivant illustre les privilèges autorisés les plus utilisés pour les instructions GRANT et REVOKE. La liste exhaustive des privilèges MySQL
est consultable sur le lien suivant : https ://dev.MySQL.com/doc/refman/8.0/en/privileges-provided.html

Niveau
Privilège Description
Global BD Table Colonne Routine Proxy
ALL [PRIVILEGES] Accorde tous les privilèges au niveau spécifié sauf GRANT OPTION
ALTER Autorise l'utilisation de ALTER TABLE X X X
ALTER ROUTINE Autorise ALTER et DROP des procédures et fonctions stockées X X X
CREATE Autorise la création des bases de données et tables X X X
CREATE ROUTINE Autorise la création des procédures et fonctions stockées X X
CREATE TEMPORARY TABLES Autorise la création des tables temporaires : CREATE TEMPORARY TABLE X X
CREATE USER Autorise l'utilisation de : CREATE USER, DROP USER, RENAME USER et REVOKE ALL PRIVILEGES X
CREATE VIEW Autorise la création et la modification des vues X X X
DELETE Autorise l'utilisation de DELETE X X X
DROP Autorise la suppression des bases de données et des tables X X X
EXECUTE Autorise l'exécution des routines X X X
GRANT OPTION Autorise l'utilisateur à accorder ou révoquer des privilèges d'autres comptes X X X X X
INDEX Autorise la création et la suppression des indexes X X X
INSERT Autorise l'utilisation de INSERT X X X X
PARTIE 2

PROCESS Autorise l'exécution de SHOW PROCESSLIST X

PROXY Autorise l'utilisation de PROXY
REFERENCES Autorise la création des clés étrangères X X X X
SELECT Autorise l'utilisation SELECT X X X X
SHUTDOWN Autorise l'utilisation de la commande MySQLadmin shutdown X
UPDATE Autorise l'utilisation de UPDATE X X X X

Copyright - Tout droit réservé - OFPPT 301

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Révocation des privilèges (REVOKE)

• Afin de supprimer un ou plusieurs privilèges donnés à des utilisateurs, on utilise la commande REVOKE suivant cette syntaxe :
REVOKE

Privilege1 [,privilege2]..

ON [type_objet] privilege_level

FROM utilisateur1 [, utilisateur2] ..;

• Il faut spécifier :

• Une liste de privilèges séparés par des virgules qu'on veut révoquer d'un compte d'utilisateur après le mot-clé REVOKE ;

• Le type d'objet et le niveau de privilège après le mot-clé ON ;

PARTIE 2

• Un ou plusieurs comptes d'utilisateur dont on souhaite révoquer les privilèges dans la clause FROM.

Copyright - Tout droit réservé - OFPPT 302

 03 - Administrer une base de données
Commandes de gestion des privilèges de base

Révocation des privilèges (REVOKE)

Exemples :
REVOKE

ALL, GRANT OPTION

FROM Ahmad@localhost;

REVOKE SELECT,UPDATE,DELETE

ON dbtest.*

FROM Ahmad@localhost, str@localhost;

• L'instruction REVOKE prend effet selon le niveau de privilège :

• Niveau global : Les modifications prennent effet lorsque le compte utilisateur se connecte au serveur MySQL dans les sessions
PARTIE 2

prochaines. Les modifications ne sont pas appliquées à tous les utilisateurs actuellement connectés.

• Niveau base de données : Les modifications prennent effet après la prochaine instruction USE.

• Niveaux table et colonne : Les modifications prennent effet sur toutes les requêtes suivantes.

Copyright - Tout droit réservé - OFPPT 303

 PARTIE 3
Exploiter les fonctionnalités avancées d'un SGBD
relationnel

Dans ce module, vous allez :

• Maîtriser le langage de programmation procédurale sous
MySQL
• Optimiser une base de données MySQL

30 heures
 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

Ce que vous allez apprendre dans ce chapitre :

• Présenter le langage de programmation procédural ;

• Distinguer les différents types des programmes;
• Maîtriser les instructions de base ;
• Maîtriser les structures de contrôle ;
• Gérer les transactions ;
• Gérer les exceptions ;
• Manipuler les curseurs ;
• Créer les procédures stockées et les fonctions ;
• Mettre en place les déclencheurs.

25 heures
 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Langage de programmation procédural

Introduction

• Les structures de contrôle habituelles d'un langage de programmation (IF, WHILE, etc.) ne font pas partie intégrante de la norme SQL.

• Elles apparaissent dans une sous-partie optionnelle de la norme (ISO/IEC 9075-5 :1996. Flow-control statements).

• Le Langage Procédural de MySQL (PL+SQL) permet de faire cohabiter les habituelles structures de contrôle
(IF,CASE,WHILE,REPEAT,LOOP) avec des instructions SQL (principalement SELECT, INSERT, UPDATE et DELETE).

• Le langage de programmation procédural / SQL est un langage fondé sur les paradigmes de la programmation procédurale. Il est
structuré pour appliquer et manipuler des requêtes SQL.

Avantages
PARTIE 3

Amélioration des Développement

Intégration Portabilité
performances modulaire

Copyright - Tout droit réservé - OFPPT 307

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes ;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Types de programmes

Types de programmes
• PL/SQL est un langage structuré sous forme de blocs.

• Chaque bloc est constitué d'un ensemble d'instructions.

[DECLARE]
- Variables, constantes, curseurs, exceptions utilisateurs
BEGIN
- Ordres SQL
- Instructions de Contrôle PL/SQL
[ EXCEPTION ]
- Traitements à effectuer lors d'erreurs
END ;
Structure d'un bloc

• Un bloc PL/SQL peut être « externe » ou anonyme, ou alors stocké dans la base de données sous forme de :
PARTIE 3

Procédure stockée •
Fonction Déclencheur

• Un bloc PL/SQL est intégralement envoyé au moteur PL/SQL, qui traite chaque instruction PL/SQL et sous-traite les instructions purement SQL
au moteur SQL, afin de réduire le trafic réseau.
Copyright - Tout droit réservé - OFPPT 309
 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Instructions de base

Déclaration des variables scalaires

• DECLARE nomVariable1[,nomVariable2...] typeMySQL [DEFAULT expression] ;

• Lors des conflits potentiels de noms (variables ou colonnes) dans des instructions SQL (principalement INSERT, UPDATE, DELETE et
SELECT), le nom de la variable est prioritairement interprété au détriment de la colonne de la table (de même nom).

• Le nom d'une variable doit répondre à ces exigences :

• Il ne peut pas contenir plus de 30 caractères

• Il ne peut pas contenir des mots réservés, à moins qu'ils soient encadrés par des guillemets.

• Il doit commencer par une lettre.

DECLARE v_dateNaissance DATE;
• Il doit avoir un nom distinct de celui d'une table de la base ou d'une colonne
DECLARE v_trouve BOOLEAN DEFAULT TRUE;
PARTIE 3

DECLARE v_Dans2jours DATE DEFAULT

ADDDATE(SYSDATE(),2);
DECLARE i, j, k INT;

Copyright - Tout droit réservé - OFPPT 311

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Instructions de base

Déclaration des variables scalaires : Types classiques

Type Valeurs

BINARY-INTEGER Entiers allant de -2**31 à 2**31

POSITIVE / NATURAL Entiers positifs allant jusqu'à 2**31-1

NUMBER Numériques (entre -2**418 à 2**418)

INTEGER Entiers stockés en binaire (entre -2**126 à 2**126)

CHAR (n) Chaînes fixes de 1 à 32757 caractères (différent pour une colonne de table)

VARCHAR2(n) Chaînes variables (1 à 32767 caractères)

LONG Idem que VARCHAR2 (maximum 2 gigaoctets)

DATE Dates (ex. 01/01/1996 ou 01-01-1996 ou 01-JAN-96, etc.)

PARTIE 3

RAW Permet de stocker des types de données binaire relativement faibles (<=32767 octets) idem que VARCHAR2.
Les données RAW ne subissent jamais de conversion de caractères lors de leur transfert entre le programme
et la base de données
LONG RAW Idem que LONG mais avec du binaire

Copyright - Tout droit réservé - OFPPT 312

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Instructions de base

Déclaration des variables de session : externes

• La variable dite de session (user-defined variables) est déclarée à l'aide du symbole « @ ».

• C'est une variable définie par un client qui n'est pas visible par les autres clients. En d'autres termes, une variable définie par l'utilisateur
qui est spécifique à la session.

• SET @nom_variable := valeur Ou SELECT @nom_variable := valeur

SELECT
@max_prix := MAX (pu) -- initialiser la variable de session @max_prix par le maximum des prix
FROM
Produits;

SELECT
id, designation, qtstock, pu
PARTIE 3

FROM
produit
WHERE
pu = @max_prix; -- utiliser la variable @max_prix pour lister les produits dont le prix est le max

Copyright - Tout droit réservé - OFPPT 313

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Instructions de base

Règles des instructions

• Une instruction doit terminer par un point virgule (;)

• Les chaînes de caractères et les dates doivent être entourées de simples quotes ( ' ' )

• Les commentaires peuvent être

• sur une seule ligne avec : « - commentaire»

• Sur plusieurs lignes avec

/* début et
fin de commentaire*/
PARTIE 3

Copyright - Tout droit réservé - OFPPT 314

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Instructions de base

Portée des objets

• La portée d'un objet (variable, curseur ou exception) est limitée au bloc dans lequel il est déclaré.

• Un objet déclaré dans un bloc est accessible dans les sous-blocs.

• En revanche, un objet déclaré dans un sous-bloc n'est pas visible du bloc conteur.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 315

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Instructions de base

Verrouillage des tables

• Un verrou est un drapeau associé à une table. MySQL permet à une session
client d'acquérir explicitement un verrou de table pour empêcher d'autres
sessions d'accéder à la même table pendant une période spécifique.

• Une session client peut acquérir ou libérer des verrous de table uniquement
pour elle-même.

• Une session client ne peut pas acquérir ou libérer des verrous de table pour
d'autres sessions client.

Syntaxe

• Pour verrouiller les tables : LOCK TABLES nom_table [READ | WRITE]

PARTIE 3

• Pour déverrouiller les tables : UNLOCK TABLES;

Copyright - Tout droit réservé - OFPPT 316

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle

• Les blocs PL/SQL traités permettent d'exécuter une suite d'actions les unes à la suite des autres

• Les problèmes réels sont plus complexes. La résolution de certains problèmes se fait sous un ensemble de conditions

• Pour chaque condition, un traitement spécifique sera déclenché. Il est donc possible de contrôler ces instructions dans un bloc, grâce à des
structures de contrôle.

• Parmi les instructions de contrôle, on distingue :

Les structures Les structures

conditionnelles itératives
PARTIE 3

L'instruction IF L'instruction CASE La boucle LOOP La boucle REPEAT La boucle WHILE

Copyright - Tout droit réservé - OFPPT 318

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle conditionnelles

IF
IF condition THEN
instructions;
END IF;

IF-ELSE

IF condition THEN IF SUBSTR (v_telephone,1,2) = '06' THEN

instructions; SELECT ''C'est un portable' ;
ELSE ELSE
SELECT ''C'est un fixe … '' ;
instructions;
END IF ;
PARTIE 3

END IF;

Copyright - Tout droit réservé - OFPPT 319

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle conditionnelles : IF-ELSEIF-ELSE

IF condition1 THEN CREATE FUNCTION IncomeLevel (monthly_value INT)

- Les fonctions seront traitées par la suite
instructions; RETURNS VARCHAR(20)
ELSEIF condition2 THEN BEGIN
DECLARE income_level VARCHAR(20);
Instructions2; IF monthly_value <= 4000 THEN
ELSE SET income_level = 'Low Income' ;
ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
instructions3; SET income_level = 'Avg Income' ;
ELSE
END IF;
SET income_level = 'High Income' ;
END IF ;
RETURN income_level ;
END ; //
PARTIE 3

DELIMITER ;

Copyright - Tout droit réservé - OFPPT 320

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle conditionnelles : CASE

Avec sélecteur CREATE PROCEDURE GetCustomerShipping (IN pCustomerNumber INT,
OUT pShipping VARCHAR(50))
- même chose pour les procédures
CASE variable_selecteur
BEGIN
WHEN expr1 THEN instructions1; DECLARE customerCountry VARCHAR(100);
WHEN expr2 THEN instructions2; SELECT country INTO customerCountry FROM customers
WHERE customerNumber = pCustomerNumber ;
…
CASE customerCountry
WHEN exprN THEN instructionsN; WHEN ‘Maroc' THEN
[ELSE instructionsN+1;] SET pShipping = '2-day Shipping' ;
END CASE WHEN 'Canada' THEN
SET pShipping = '3-day Shipping' ;
ELSE
PARTIE 3

SET pShipping = '5-day Shipping' ;

END CASE ;
END$$
DELIMITER ;

Copyright - Tout droit réservé - OFPPT 321

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle conditionnelles : CASE

Sans sélecteur SELECT OrderID, Quantity,

CASE
WHEN Quantity > 30 THEN ''The quantity is greater than 30''
CASE
WHEN Quantity=30 THEN ''The quantity is 30''
WHEN condition1 THEN instructions1; ELSE ''The quantity is under 30''
WHEN condition2 THEN instructions2; END
FROM OrderDetails;
…
WHEN condition¨N THEN instructionsN;
DECLARE v_mention CHAR(2) ;
[ELSE instructionsN+1;] DECLARE v_note DECIMAL(4,2) DEFAULT 9.8 ;
END CASE CASE
WHEN v_note >= 16 THEN SET v_mention := 'TB';
WHEN v_note >= 14 THEN SET v_mention := 'B';
PARTIE 3

WHEN v_note >= 12 THEN SET v_mention := 'AB';

WHEN v_note >= 10 THEN SET v_mention := 'P';
ELSE SET v_mention := 'R';
END CASE ;

Copyright - Tout droit réservé - OFPPT 322

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle conditionnelles : LOOP

L'instruction LOOP permet d'exécuter une ou plusieurs CREATE TABLE test (VALUE INT NULL DEFAULT NULL) ;
DECLARE a INT DEFAULT 1 ;
instructions à plusieurs reprises.
simple_loop : LOOP
INSERT INTO test VALUES (a) ;
[labele_debut :] LOOP SET a = a + 1 ;
IF a = 11 THEN
statement_list
LEAVE simple_loop ;
END LOOP [label_fin] END IF ;
END LOOP simple_loop;

SELECT value FROM test;

PARTIE 3

Copyright - Tout droit réservé - OFPPT 323

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle itératives : ITERATE/LEAVE

• L'instruction LEAVE sort immédiatement de la boucle. Cela BEGIN

fonctionne comme l'instruction break dans d'autres langages DECLARE x INT ;
DECLARE str VARCHAR (255) ;
de programmation comme PHP, C/C++ et Java. SET str = '' ;
loop_label : LOOP
IF x > 10 THEN
• L'instruction ITERATE est utilisée pour ignorer l'itération de la LEAVE loop_label;
boucle en cours et démarrer une nouvelle itération. ITERATE END IF ;
SET x = x + 1 ;
est similaire à l'instruction continue en PHP, C/C++ et Java. IF (x mod 2) THEN
ITERATE loop_label ;
ELSE
PARTIE 3

SET str = CONCAT (str,x,',') ;

END IF ;
END LOOP ;
SELECT str ;
END

Copyright - Tout droit réservé - OFPPT 324

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle itératives : REPEAT

• La liste d'instructions dans une instruction REPEAT est répétée

BEGIN
jusqu'à (UNTIL) ce que l'expression condition soit vraie.
SET @x = 0 ;
• Ainsi, un REPEAT entre toujours dans la boucle au moins une REPEAT
SET @x = @x + 1 ;
fois.
UNTIL @x > 10
• liste_instructions se compose d'une ou plusieurs instructions, END ;
chacune terminée par un délimiteur d'instruction point-
virgule (;).
REPEAT
liste_instructions ;
PARTIE 3

UNTIL condition

Copyright - Tout droit réservé - OFPPT 325

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Structures de contrôle

Structures de contrôle itératives : WHILE

• La boucle WHILE est une instruction de boucle qui exécute un bloc

CREATE PROCEDURE dowhile () ;
de code de manière répétée tant qu'une condition est vraie.
BEGIN
DECLARE v1 INT DEFAULT 5 ;
• Pour l'utilisation de la boucle WHILE, on utilise la syntaxe : WHILE v1 > 0 DO
...
[labele_debut :] WHILE condition DO SET v1 = v1 – 1 ;
END WHILE ;
liste des instructions
END ;
END WHILE [labele_fin]
PARTIE 3

Copyright - Tout droit réservé - OFPPT 326

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des transactions

Gestion des transactions

• Par défaut, le client MySQL est configuré pour utiliser la validation

automatique. BEGIN ;

• On n'a donc pas à le faire. Si on souhaite avoir la possibilité - - Ceci indique le début de la transaction

d'annuler l'instruction INSERT, on doit utiliser une transaction. INSERT INTO mytable VALUES (1, 'foo','bar','bas') ;
SELECT * FROM mytable ;
• Cela peut être fait avec une instruction BEGIN ou une instruction
COMMIT;
START TRANSACTION.
- - Utiliser ROLLBACK au lieu de COMMIT si on veut annuler l'opération
• Une fois qu'on a exécuté une ou plusieurs instructions pour
modifier les données, on doit utiliser COMMIT ou ROLLBACK.
PARTIE 3

Remarque : Pour désactiver le commit automatique utiliser : SET autocommit = 0;

Ou
SET autocommit = OFF

Copyright - Tout droit réservé - OFPPT 328

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des transactions

Contrôle des transactions

• Les commandes suivantes sont utilisées pour contrôler les transactions :

COMMIT
Pour enregistrer les modifications

ROLLBACK
Pour annuler les modifications

• Ces commandes ne sont utilisées qu'avec les commandes INSERT, UPDATE et DELETE uniquement.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 329

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des transactions

Exemple

• Le meilleur exemple pour comprendre une Débit -200 DH

TRANSACTION MySQL est un transfert d'argent entre Compte émetteur Compte récepteur
2 comptes d'une même banque. Crédit +200 DH

• On suppose que le débit ait été un succès, mais que le crédit n'ait pas eu lieu, peut-être à cause de problèmes de base de données.
Dans ce cas, la base de données serait dans un état incohérent.
• Idéalement, on veut que cette transaction (à la fois de crédit et de débit) se produise, sinon aucune d'entre elles ne se produira,
c'est-à-dire que dans ce cas, s'il s'agissait d'une transaction, un échec de crédit aurait entraîné une annulation de l'opération de
débit et il n'y aurait eu aucun changement dans l'état de la base de données.

START TRANSACTION ;
UPDATE compte SET solde = solde – 200 WHERE accountno = 'ACC1' ; - - Débiter le compte ACC1
PARTIE 3

UPDATE compte SET solde = solde + 200 WHERE accountno = 'ACC2' ; - - Créditer le compte ACC2
COMMIT ; - - Valider

Copyright - Tout droit réservé - OFPPT 330

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Évènements programmées (Events)

Évènements programmées (Events)

• Les événements MySQL sont des tâches qui s'exécutent selon un calendrier spécifié. Par conséquent, les événements MySQL sont parfois
appelés événements planifiés.

• MySQL utilise un thread spécial appelé thread du planificateur d'événements pour exécuter tous les événements planifiés.

• On peut afficher l'état du thread du planificateur d'événements en exécutant la commande SHOW PROCESSLIST :

CREATE EVENT test_event_03

ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO messages (message, created_at
VALUES ('Test MySQL recurring Event', NOW());
PARTIE 3

• D'après l'exemple, un message sera enregistré dans la table « messages », chaque minute, pendant une heure.

Copyright - Tout droit réservé - OFPPT 332

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Introduction
• Afin d'éviter qu'un programme ne s'arrête dès la première erreur suite à une instruction SQL, il est indispensable de prévoir les cas
potentiels d'erreurs et d'associer à chacun de ces cas la programmation d'une exception (handler).

• Les exceptions peuvent être gérées dans un sous-programme (fonction ou procédure) ou un déclencheur.

• Une exception MySQL correspond à une condition d'erreur et peut être associée à un identificateur (exception nommée).

• Une exception est détectée (aussi dite « levée ») si elle est prévue dans un handler au cours de l'exécution d'un bloc (entre BEGIN et END ).

• Une fois levée, elle fait continuer (ou sortir du bloc) le programme après avoir réalisé une ou plusieurs instructions que le programmeur aura
explicitement spécifiées.

• Deux mécanismes qui peuvent être mis en œuvre pour gérer une exception en MySQL : CONTINUE et EXIT.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 334

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Syntaxe

DECLARE handler_action HANDLER • CONTINUE : (appelée handler) force à poursuivre l'exécution de programme lorsqu'il se
FOR condition_value [, condition_value] …
passe un événement prévu dans la clause FOR
statement
Handler_action : { • EXIT fait sortir l'exécution du bloc courant entre BEGIN et END
CONTINUE • SQLSTATE : code d'erreur qui permet de couvrir toutes les erreurs d'un état donné.
| EXIT Pour plus de détails consulter (https ://dev.MySQL.com/doc/connector-
| UNDO
j/5.1/en/connector-j-reference-error-sqlstates.html)
}
• SQLWARNING :permet de couvrir toutes les erreurs d'état SQLSTATE débutant par 01.
Condition_value : {
MySQL_error_code • NOT FOUND : permet de couvrir toutes les erreurs d'état SQLSTATE débutant par 02
| SQLSTATE [VALUE] sqlstate_value (relatives à la gestion des curseurs).
| condition_name • SQLEXCEPTION : gère toutes les erreurs qui ne sont ni gérées par SQLWARNING ni par
PARTIE 3

| SQLWARNING
NOT FOUND
| NOT FOUND
| SQL EXCEPTION • statement : une ou plusieurs instructions du langage de MySQL (bloc, appel possibles
} par CALL d'une fonction ou d'une procédure stockée).

Copyright - Tout droit réservé - OFPPT 335

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions avec EXIT

▪ Essayer d'insérer un client sans spécifier le champs obligatoire (NOT
NULL) nom. Une exception et déclenchée ayant le code 1040.

▪ Gérer cette exception avec le handler Exit

DELEMITER//;
CREATE PROCEDURE ps_ajouter_client_Exception1(IN p_nom VARCHAR (200),
IN p_prenom VARCHAR(200),
IN p_adresse VARCHAR(200))
BEGIN
DECLARE flagNOTNULL BOOLEAN DEFAULT 0 ;
BEGIN - - début bloc de déclaration des deux exceptions
DECLARE EXIT HANDLER FOR 1048
SET flagNOTNULL := -1;
INSERT INTO clients (nom, prenom, adresse) VALUES (p_nom, p_prenom, p_adresse);
SELECT 'le client est ajouté avec succès';
PARTIE 3

END; - - fin bloc de déclaration des deux exceptions

IF flagNOTNULL THEN
SELECT CONCAT ('Le chmp nom doit être non null') AS 'Resultat ps_ajouter_client_Exception1';
END IF;
END;//
CALL ps_ajouter_client_Exception1(null, 'Saloua', 'Safi');

Copyright - Tout droit réservé - OFPPT 336

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions avec EXIT

CREATE PROCEDURE ps_exc_not_found_Exemple (IN p_nom VARCHAR (200))

• L' exemple suivant décrit une procédure BEGIN
DECLARE flagNOTFOUND BOOLEAN DEFAULT 0 ;
qui gère une erreur : 'aucun client n'est DECLARE var1 VARCHAR(20) ;
associé à ID passé en paramètre (NOT BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND
FOUND)'. SET flagNOTFOUND := -1;
SELECT nom INTO var1 FROM clients
• La procédure ne se termine pas WHERE nom = p_nom ;
correctement si plusieurs lignes sont SELECT CONCAT ('Le seul client avec le nom ', p_nom, ' est ', var1)
AS 'Resultat ps_exc_not_found_Exemple1';
retournées (erreur Result consisted of END
more than one row). IF flagNOTFOUND THEN
SELECT CONCAT ('Il n`'y a pas de client aven le nom ', p_nom)
PARTIE 3

AS 'Resultat ps_exc_not_found_Exemple1' ;
END IF;
END;
CALL ps_exc_not_found_Exemple1('Dalaj');

Copyright - Tout droit réservé - OFPPT 337

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exception FOR SQLEXCEPTION

DELIMITER // ;
• L'exemple suivant décrit une procédure CREATE PROCEDURE autreErreur ()
qui gère une erreur non spécifique. BEGIN
SELECT 'Une autre erreur est survenue' ;
• Remarque : MySQL ne permet pas, pour END ; //
DELIMITER // ;
l'instant, de récupérer dynamiquement,
CREATE PROCEDURE handlerdemoSQLEXCEPTION ()
au sein d'un sous-programme, le code et BEGIN
le message de l'erreur associée à une DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL autreErreur();
INSERT INTO table_inexistante VALUES (1);
exception levée suite à une instruction SET @x = 99;
SQL, et qui n'a pas été prévue dans un END;//
CALL handlerdemoSQLEXCEPTION();
handler. SELECT @x;
PARTIE 3

Copyright - Tout droit réservé - OFPPT 338

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions avec Continue

• L'exemple suivant décrit une procédure qui gère CREATE TABLE test (code INT, PRIMARY KEY (code));
DELIMITER // ;
une erreur :
CREATE PROCEDURE handlerdemo ()
Sqlstate : ER_DUP_KEY '23000’ BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
• Même si une exception de duplication de clé
SET @x2 = 1;
primaire ER_DUP_KEY est causée par l'instruction SET @x = 1;
INSERT INTO test VALUES (1);
insert, l'exécution des instructions après ‘insert’
SET @x = 2;
continue et la variable de session @x est initialisée. INSERT INTO test VALUES (1);
SET @x = 3;
END;//
PARTIE 3

CALL handlerdemo();
SELECT @x;

Copyright - Tout droit réservé - OFPPT 339

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions nommées : condition

• Pour intercepter une erreur MySQL et lui attribuer au passage un identificateur, il faut utiliser la clause DECLARE CONDITION.

• Pour la déclaration, on utilise la syntaxe suivante :

DECLARE nomException CONDITION FOR

{SQLSTATE [VALUE] 'valeur_sqlstate' | code_erreur_MySQL}
PARTIE 3

Copyright - Tout droit réservé - OFPPT 340

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

L'instruction SIGNAL

• On utilise l'instruction SIGNAL pour renvoyer une condition CREATE PROCEDURE ajouter_commande (IN commandeNo,
d'erreur ou d'avertissement à l'appelant à partir d'un sous IN produitCode varchar(45),
IN qte int,
programme : une procédure stockée, une fonction stockée, un
IN prix double,
déclencheur ou un événement. IN ligneNO int )
• L'instruction SIGNAL permet de contrôler les informations à BEGIN
DECLARE C INT ;
renvoyer, telles que la valeur et le message SQLSTATE.
SELECT COUNT (commandeNumber) INTO C
• Pour l'utilisation de l'instruction, on utilise la syntaxe suivante : FROM commandes
WHERE commandeNumber = commandeNo;
SIGNAL SQLSTATE | nom_exception_nomee; - - teste si le numéro de la commande existe
SET info_1 = valeur_1, IF (C != 1) THEN
info_2= valeur_2, … ; SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Commande
PARTIE 3

• Info_i peut prendre : introuvable';

MESSAGE_TEXT, MySQL_ERRNO, SCHEMA_NAME, NOM_TABLE, END IF ;
COLUMN_NAME, - - suite de code a exécuter si pas d'erreur
--…
END

Copyright - Tout droit réservé - OFPPT 341

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

L'instruction RESIGNAL
• l'instruction RESIGNAL est similaire à l'instruction SIGNAL en termes de fonctionnalité et de syntaxe, sauf que :
• On doit utiliser l'instruction RESIGNAL dans un gestionnaire d'erreurs ou d'avertissements, sinon on obtient un message d'erreur «
RESIGNAL lorsque le gestionnaire n'est pas actif ». Noter qu'on peut utiliser l'instruction SIGNAL n'importe où dans une procédure
stockée.
• On peut omettre tous les attributs de l'instruction RESIGNAL, même la valeur SQLSTATE.

DELIMITER $$
CREATE PROCEDURE Divide (IN numerator INT, IN denominator INT, OUT result double)
BEGIN
DECLARE division_by_zero CONDISTION FOR SQLSTATE '22012' ;
DECLARE CONTINUE HANDLER FOR division_by_zero
RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
--
IF denominator = 0 THEN
PARTIE 3

SIGNAL division_by_zero;
ELSE
SET result := numerator / denominator;
END IF ;
END

Copyright - Tout droit réservé - OFPPT 342

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les curseurs

Définition
▪ Un curseur est une zone mémoire qui est générée côté serveur (mise en cache) et qui permet de traiter individuellement chaque ligne
renvoyée par un SELECT.

▪ Un sous-programme peut travailler avec plusieurs curseurs en même temps. Un curseur, durant son existence (de l'ouverture à la fermeture),
contient en permanence l'adresse de la ligne courante.

▪ Tout curseur MySQL dispose des propriétés suivantes :

Read-only (lecture seule)

▪ Aucune modification dans la base n'est possible à travers ce dernier (sauf si on ajoute la clause FOR
UPDATE) ;

Non-scrollable (non navigable)

▪ Une fois ouvert, le curseur est parcouru du début à la fin sans pouvoir revenir à l'enregistrement
PARTIE 3

précédent ;

Asensitive (insensible )
▪ Toute mise à jour opérée dans la base de données n'est pas répercutée dans le curseur une fois ouvert
(il utilise une copie temporaire des données et ne pointe pas sur les données réelles).

Copyright - Tout droit réservé - OFPPT 344

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les curseurs

Syntaxe

Pour l'utilisation des curseurs on suit les étapes suivantes :

1. Déclaration du curseur avant la déclaration des variables

2. Utilisation de l'instruction OPEN pour initialiser le jeu de résultats pour le curseur

3. Utilisation de l'instruction FETCH pour récupérer la ligne suivante pointée par le curseur et déplacer le curseur vers la ligne suivante
dans le jeu de résultats.

4. Fermeture du curseur.

DECLARE nom_curseur CURSOR FOR instruction_SELECT

OPEN nom_curseur;
PARTIE 3

FETCH nom_curseur INTO liste_variables;

CLOSE nom_curseur;

Copyright - Tout droit réservé - OFPPT 345

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les curseurs

DELIMITER $$
Exemple
CREATE PROCEDURE lister_clients (INOUT resultat_txt VARCHAR(4000))
• Ce curseur permet de construire une chaîne contenant BEGIN
les informations des employés. DECLARE finished INTEGER DEFAULT 0 ;
• Remarques : DECLARE v_id INT ;
DECLARE v_nom VARCHAR (100) ;
• Un curseur doit toujours être associé à une DECLARE v_prenom VARCHAR (100) ;
instruction SELECT. DECLARE info VARVHAR (400) DEFAULT '''' ;
• Lorsque on travaille avec le curseur MySQL, on doit DECLARE cur_info_client CURSOR FOR SELECT id,nom,prenom FROM clients;
également déclarer un gestionnaire NOT FOUND DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
pour gérer la situation où le curseur ne trouve OPEN cur_info_client ;
boucle_parcours_clients : LOOP
aucune ligne.
FETCH cur_info_client INTO v_id, v_nomn v_prenom ;
IF finished = 1 THEN
- - Appel de la ps
LEAVE boucle_parcours_clients ;
SET @resultat_txt = '''';
END IF ;
PARTIE 3

CALL lister_clients(@resltat_txt) ;
SET info = CONCAT (v_id, ''-'',v_nom,''-',v_prenom);
SELECT @resultat_txt ; SET resultat_txt = CONCAT (info, '';'',resultat_txt) ;
END LOOP boucle_parcours_clients ;
CLOSE cur_info_client;
END $$
Copyright - Tout droit réservé - OFPPT 346
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les curseurs

Curseur pour modification

• Si on veut verrouiller les lignes d'une table interrogée par un curseur dans le but de mettre à jour la table, sans qu'un autre utilisateur ne la
modifie en même temps, il faut utiliser la clause FOR UPDATE. Elle s'emploie lors de la déclaration du curseur et verrouille les lignes
concernées dès l'ouverture du curseur. boucle _parcours_clients : LOOP
• Les verrous sont libérés à la fin de la transaction. FETCH cur_info_client INTO
v_id,v_nom,v_prenom,v_adresse;
DELIMITER $$ IF finished = 1 THEN
CREATE PROCEDURE lister_clients_avec_MAJ_adresse ( INOUT resultat_txt LEAVE boucle _parcours_clients ;
VARCHAR (4000)) END IF;
BEGIN IF UPPER(v_adresse)='AGADIR' THEN
DECLARE finished INTEGER DEFAULT 0 ; UPDATE clients
DECLARE v_id INT ; SET adresse = 'GRANDE AGADAIR'
DECLARE v_nom VARCHAR(100) ; WHERE id = v_id;
DECLARE v_prenom VARCHAR(100) ; END IF;
DECLARE v_adresse VARCHAR(100) ; SET info = CONCAT (v_id, ''-'',v_nom,''-',v_prenom);
PARTIE 3

DECLARE info VARCHAR(400) DEFAULT '''' ; SET resultat_txt = CONCAT (info, '';'',resultat_txt) ;
DECLARE cur_info_client CURSOR FOR SELECT id,nom,prenom,adresse END LOOP boucle_parcours_clients ;
FROM clients FOR UPDATE ; CLOSE cur_info_client;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET set finished = 1 ; END $$
OPEN cur_info_client ; DELIMITER ;

Copyright - Tout droit réservé - OFPPT 347

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les curseurs

Restrictions d'utilisation

Il n'est pas possible de déclarer un curseur FOR UPDATE, si on utilise dans la requête les directives :
▪ DISTINCT ;
▪ GROUP BY;
▪ Un opérateur ensembliste ;
▪ Une fonction d'agrégat.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 348

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Sous programme

▪ Un sous programme est un bloc d'instructions qui est BEGIN

SELECT 'Bloc d''instructions principal';
composé de : BEGIN
SELECT 'Bloc d''instructions 2, imbriqué dans le bloc principal';
▪ BIGIN : pour indiquer le début du bloc

▪ DECLARE (directive optionnelle) : pour déclarer une BEGIN

SELECT 'Bloc d''instructions 3, imbriqué dans le bloc d''instructions 2';
variable, un curseur, une exception, etc. ; END;
END;
▪ END : ferme le bloc.
BEGIN
SELECT 'Bloc d''instructions 4, imbriqué dans le bloc principal';
END;
▪ Un bloc peut être imbriqué dans un autre bloc
END;
PARTIE 3

Copyright - Tout droit réservé - OFPPT 350

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Fonctions : Définition

• Les fonctions peuvent être utilisées pour créer une logique de programmation personnalisée pour la base de données. Ceci est utile dans les
situations où on a du code répété dans plusieurs zones et qu’on souhaite éviter de copier le code plusieurs fois.

• MySQL possède de nombreuses fonctions intégrées qu’on peut appeler pour renvoyer des valeurs ou effectuer des tâches sur des données,
notamment :

• CURRENT_DATE()

• AVG()

• SUM()

• ABS()

• CONCAT()
PARTIE 3

• Ces fonctions peuvent être utilisées dans des instructions SQL, des vues et des procédures stockées.

• MySQL a également un autre type de fonction, connue sous le nom de Fonction Définie par l'Utilisateur (UDF).

Copyright - Tout droit réservé - OFPPT 351

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Fonctions : Syntaxe USE eshop_app_db;

DROP FUNCTION IF EXISTS uf_nombre_clients_par_ville;
USE nom_bd; DELIMITER $$
DROP FUNCTION IF EXISTS nom_fonction; CREATE FUNCTION uf_nombre_clients_par_ville ( ville
VARCHAR(50))
CREATE FUNCTION nom_fonction ([parameter(s)])
RETURNS INT
RETURNS type_retour READS SQL DATA
déclaration informative BEGIN
Instructions DECLARE nombre INT;
SET nombre = (SELECT COUNT (*)
FROM clients
WHERE adresse = ville );
RETURN nombre;
END $$
La déclaration informative peut prendre l'une des valeurs : DELIMITER ;
• DETERMINISTIC : la fonction renverra les mêmes valeurs si les mêmes arguments lui sont fournis, ce qui signifie qu'on connaisse toujours la
sortie, compte tenu de l'entrée ;
PARTIE 3

• READS SQL DATA : spécifie que la fonction va lire les données de la base de données mais ne modifiera pas les données ;
• MODIFIES SQL DATA : spécifie que la fonction modifiera les données dans la base de données ;
• CONTAINS SQL : spécifie que la fonction aura des instructions SQL. Ces instructions ni lisent ni modifient les données, telles que : SELECT
CURRENT_DATE();
Copyright - Tout droit réservé - OFPPT 352
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Fonctions : Syntaxe

DLIMITER $$

• Le délimiteur par défaut est le point-virgule ;

• Cependant, lorsque on définit des fonctions, des procédures stockées et des déclencheurs, on exécutera souvent plusieurs instructions.

• Définir un délimiteur différent permet d'exécuter toutes les instructions comme une seule unité plutôt qu'individuellement.

Remarques :

• On ne peutpas renvoyer une table à partir d'une fonction MySQL.

• Une fonction peut renvoyer une chaîne, un entier, un caractère, etc.

• Pour renvoyer une table à partir de MySQL, on utilise une procédure stockée, et non pas une fonction.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 353

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées
• Les procédures stockées permettent de stocker un ensemble de requêtes SQL, à exécuter en cas de besoin.
• En règle générale, on doit utiliser des procédures stockées lorsqu'une requête ou un ensemble de requêtes doivent être répété régulièrement.
• La procédure stockée peut contenir une instruction conditionnelle telle que IF ou CASE ou les boucles.
• La procédure stockée peut également exécuter une autre procédure stockée ou une fonction qui modularise le code.
• Les procédures stockées sont idéales pour déplacer des tâches de traitement lourdes vers le serveur MySQL.
• Si on imagine qu’on travaille sur une application de vente connectée à une base de données MySQL et qu’on doit enregistrer une vente.
L'application enregistrera une vente en procédant comme suit :
• Détermination du montant total du paiement pour confirmer le paiement
• Calcul de la valeur de vente (coût des ventes * valeur de l'article)
• Calcul de la taxe de vente qui s'applique à la vente
PARTIE 3

• Soustraction de l'article du tableau d'inventaire

• Vérification de la valeur de stock minimum de l'article
• Génération d'un reçu
• Toutes ces tâches peuvent être placées dans une seule procédure stockée.
Copyright - Tout droit réservé - OFPPT 354
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Avantages

Réduire le trafic réseau

• Plusieurs instructions SQL sont encapsulées dans une procédure stockée.
• Lorsque on l'exécute, au lieu d'envoyer plusieurs requêtes, on envoie uniquement le nom et les
paramètres de la procédure stockée.

Facile à maintenir
• Les procédures stockées sont réutilisables. On peut implémenter la logique métier dans un serveur qui
peut être utilisée par des applications plusieurs fois, ou par différents modules d'une application.
• Une procédure stockée rend la base de données plus cohérente.
• Si une modification est nécessaire, on doit uniquement apporter une modification à la procédure
stockée.

Sécurité
PARTIE 3

• Les procédures stockées sont plus sécurisées que les requêtes AdHoc.
• L'autorisation peut être accordée à l'utilisateur pour exécuter la procédure stockée sans donner
l'autorisation aux tables utilisées dans la procédure stockée.
• La procédure stockée aide à protéger la base de données de SQL Injection.

Copyright - Tout droit réservé - OFPPT 355

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Syntaxe

DELIMITER $$ DELIMITER $$
CREATE PROCEDURE nom_procedure([paramètres]) CREATE PROCEDURE sp_client_par_ville(IN ville VARCHAR(50))
BEGIN BEGIN
le code SQL SELECT *
END $$ FROM clients
WHERE UPPER(adresse) = UPPER(ville);
Syntaxe pour exécuter une procédures stockée
END $$
call nom_procedure([paramètres]);
DELIMITER
- - Appel
call sp_client_par_ville ('rabat');
PARTIE 3

Copyright - Tout droit réservé - OFPPT 356

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Syntaxe

Les procédures stockées MySQL ont trois directions dans lesquelles un paramètre peut être défini :

IN
• La valeur est uniquement transmise à la procédure stockée. Elle est utilisée dans le cadre
de la procédure. Cela revient à fournir une entrée à la procédure stockée.

OUT
• La valeur est uniquement transmise hors de la procédure stockée ; toutes les variables
externes qui ont été affectées à cette position prendront la valeur transmise. Cela revient à
renvoyer des valeurs à partir d'une procédure stockée.

INOUT
• Une variable et sa valeur (ExtVal) sont transmises à la procédure stockée (IntVal) et
PARTIE 3

peuvent y être modifiées. Lorsque la procédure stockée est terminée, la valeur externe
(ExtVal) sera égale à la valeur modifiée (IntVal)

Copyright - Tout droit réservé - OFPPT 357

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Mode de passage de paramètres IN

IN est le mode par défaut. Lorsqu’on définit un paramètre IN DELIMITER $$

dans une procédure stockée, le programme appelant doit CREATE PROCEDURE sp_client_par_ville(IN ville VARCHAR(50))
passer un argument à la procédure stockée. Cette argument BEGIN
est passé par valeur. SELECT *
FROM clients
WHERE UPPER(adresse) = UPPER(ville);
END $$
DELIMITER ;
- - Appel
call sp_client_par_ville (''agadir'');
PARTIE 3

Copyright - Tout droit réservé - OFPPT 358

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Mode de passage de paramètres OUT

• Il s'agit d'un passage de paramètre en sortie (par DELIMITER $$

référence). On passe à la procédure stockée une CREATE PROCEDURE sp_client_par_ville(IN ville VARCHAR(50),
OUT nombre_clients INT)
variable de session dont la valeur peut être modifiée à
l'intérieur de la procédure stockée. BEGIN
SELECT COUNT(*) INTO nombre_clients
• Noter que la procédure stockée ne peut pas accéder à la
FROM clients
valeur initiale du paramètre OUT lorsqu'elle démarre. WHERE UPPER(adresse) = UPPER(ville);
END $$
DELIMITER ;
- - Appel
PARTIE 3

CALL sp_client_par_ville (''agadir'', @total); - - @total est une variable de

session
SELECT @total;

Copyright - Tout droit réservé - OFPPT 359

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Mode de passage de paramètres INOUT

• Un paramètre INOUT est une combinaison de DELIMITER $$

paramètres IN et OUT. CREATE PROCEDURE SetCounter (INOUT counter INT,

IN inc INT
• Cela signifie que le programme appelant peut )
transmettre l'argument et que la procédure stockée
BEGIN
peut modifier le paramètre INOUT et retransmettre la
SET counter = counter + inc ;
nouvelle valeur au programme appelant. END $$
DELIMITER ;
- - Appel
SET @counter = 1;
PARTIE 3

CALL SetCounter (@counter', 1); - - 2

CALL SetCounter (@counter', 1); - - 3
CALL SetCounter (@counter', 5); - - 8
SELECT @counter; - - 8

Copyright - Tout droit réservé - OFPPT 360

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Suppression

• DROP PROCEDURE [IF EXISTS] nom_procedure_stockée ;

• Pour modifier une procédure stockée on doit supprimer et recréer la procédure stockée à l'aide des instructions DROP PROCEDURE
et CREATE PROCEDURE.

• MySQL Workbench fournit un bon outil qui permet de modifier rapidement une procédure stockée.

DROP PROCEDURE IF EXISTS SetCounter ;

PARTIE 3

Copyright - Tout droit réservé - OFPPT 361

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Création des procédures stockées et des fonctions

Procédures stockées : Liste

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

SHOW PROCEDURE STATUS ; - - affiche toutes les procédures stockées

SHOW PROCEDURE STATUS WHERE db = 'eshop_app_db' ; - - affiche toutes les procédures stockées de la base de données eshop_app_db
SHOW PROCEDURE STATUS LIKE '%clients%' ; - - affiche toutes les procédures stockées dont le nom respecte le pattern

Liste des procédures stockées en utilisant la table routine

SELECT SELECT
routine_name routine_name
FROM FROM
information_schema.routines information_schema.routines
WHERE WHERE
routine_type = 'PROCEDURE' routine_type = 'PROCEDURE'
AND routine_schema = 'nom_base_donnees'; AND routine_schema = 'eshop_app_db';
PARTIE 3

Copyright - Tout droit réservé - OFPPT 362

 CHAPITRE 1
Maîtriser le langage de programmation
procédurale sous MySQL

1. Présentation du langage de programmation procédural ;

2. Types des programmes;
3. Instructions de base ;
4. Structures de contrôle ;
5. Gestion des transactions ;
6. Evènements programmés ;
7. Gestion des exceptions ;
8. Manipulation des curseurs ;
9. Création des procédures stockées et des fonctions ;
10. Mise en place des déclencheurs.
 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Définition

• Un déclencheur est une procédure stockée dans la base de données, qui s'appelle automatiquement à chaque fois qu'un événement
spécial se produit sur la table à laquelle le déclencheur est attaché.
• Un déclencheur peut être utilisé pour valider les données, enregistrer les anciennes et les nouvelles valeurs dans une table d'audit (log)
ou s'assurer que les règles métier sont respectées.
• Par exemple, un déclencheur peut être invoqué lorsqu'une ligne est insérée dans une table spécifique ou lorsque certaines colonnes de
la table sont mises à jour.
Syntaxe
CREATE TRIGGER [nom_declencheur] • [before | after] ( [avant | apres]) : spécifie quand le déclencheur sera exécuté.
[before | after] • {insert | update | delete} ({insérer | mise à jour | supprimer}) : spécifie l'opération
{insert | update | delete} • ON [nom_table] : spécifie le nom de la table associée au déclencheur.
ON [nom_table] • [for each row] : spécifie un déclencheur au niveau de la ligne, c'est-à-dire que le
PARTIE 3

[for each row] déclencheur sera exécuté pour chaque ligne affectée.
[corps_declencheur] • [corps_declencheur] : fournit les opérations à effectuer lorsque le déclencheur est
déclenché.

Copyright - Tout droit réservé - OFPPT 364

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Types

• Les déclencheurs BEFORE exécutent l'action du déclencheur avant

l'exécution de l'instruction du déclenchement (ÉVÉNEMENT).

• Les déclencheurs AFTER exécutent l'action du déclencheur après

l'exécution de l'instruction du déclenchement.

• Il y a trois événements possibles auxquels un déclencheur peut être

assigné : insérer, mettre à jour et supprimer.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 365

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple
PARTIE 3

Copyright - Tout droit réservé - OFPPT 366

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Avantages
Les déclencheurs contribuent :
À renforcer l'intégrité des données

À la détection des erreurs

Aux tâches qui peuvent être exécutées automatiquement lorsque le déclencheur se déclenche plutôt que d'être planifiées

À auditer les changements de données, enregistrer les événements et prévenir les transactions invalides

Restrictions avec les déclencheurs

On ne peut avoir qu'un seul déclencheur par événement ; On ne peut avoir qu'un seul déclencheur BEFORE UPDATE sur une table donnée, mais
on peut y exécuter plusieurs instructions ;

Les déclencheurs ne renvoient pas de valeurs ;

PARTIE 3

Ils ne peuvent pas utiliser l'instruction CALL et ils ne peuvent pas créer de tables ou de vues temporaires ;

Les déclencheurs peuvent entraîner des résultats incohérents en fonction de plusieurs facteurs, notamment le type de moteur de base de
données (InnoDB, MyISAM,..) utilisé avec la table à laquelle le déclencheur a été attribué.

Copyright - Tout droit réservé - OFPPT 367

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

OLD/NEW

• le mot-clé OLD est utilisé pour accéder aux valeurs précédentes des données qui sont remplacées par la mise à jour.

• Le mot-clé NEW permet d'accéder aux nouvelles valeurs des données entrantes qui remplaceront l'ancienne ligne, en cas de succès.

• Selon le type de déclencheur créé, les lignes OLD et NEW peuvent ne pas être disponibles :

• INSERT TRIGGER : Accès possible a NEW uniquement.

• UPDATE TRIGGER : Accès aux deux pseudo-lignes NEW et OLD.

• DELETE TRIGGER : Accès uniquement a OLD pseudo-lignes.

• C'est-à-dire qu'il n'y a pas de ligne OLD sur un déclencheur INSERT et pas de ligne NEW sur un déclencheur DELETE.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 368

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple : BEFORE INSERT

DELIMITER //
CREATE TRIGGER tr_contrainte_age BEFORE INSERT
FOR clients
BEGIN
DECLARE MinAge INT ;
SET MinAge := 18;
IF NEW.date_naissance >(SELECT DATE_SUB (curdate(), interval MinAge year)) THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'La personne doit etre agee de plus de 18 ans. ' ;
END IF ;
END //
DELIMITER ;
- - tester le déclencheur par une opération insert sur la table clients
INSERT INTO clients (nom, prenom, date_naissance, adresse)
VALUES (''Slami'', ''Saida'',''2010-5-22'', ''casa'')
PARTIE 3

Copyright - Tout droit réservé - OFPPT 369

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple : AFTER INSERT

- - Initialisation de l age moyenne age
CREATE TABLE moyenne_age (moyenne double);
INSERT INTO moyenne_age SELECT (AVG (TIMESTAMPDIFF(YEAR, date_naissance, CURDATE())) FROM clients ;
SET SQL_SAFE_UPDATES = 0;

- - Creation du trigger
DROP TRIGGER IF EXISTS mise_ajour_moyenne_age ;
DELIMITER //
CREATE TRIGGER mise_ajour_moyenne_age AFTER INSERT
ON clients
FOR EACH ROW
UPDATE moyenne_age SET moyenne = age SELECT (AVG (TIMESTAMPDIFF(YEAR, date_naissance, CURDATE())) FROM clients ;
DELIMITER ;
- - déclencher le triger
INSERT INTO clients (nom, prenom, date_naissance, adresse)
PARTIE 3

VALUES (''Dadi'',''Hamza'',''2000-5-22'',''casa'');
- - afficher la nouvelle valeur de l'age moyen
SELECT * FROM moyenne_age;

Copyright - Tout droit réservé - OFPPT 370

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple : BEFORE UPDATE

DELIMITER //
CREATE TRIGGER tr_modification_age_controle BEFORE UPDATE
ON clients
FOR EACH ROW
BEGIN
DECLARE MinAge INT ;
SET MinAge := 18 ;
IF NEW.date_naissace > (SELECT DATE_SUB(curdate(), interval MinAge year )) THEN
SIGNAL SGLSTATE '50002' SET MESSAGE_TEXT = 'La personne doit etre agee de plus de 18 ans.';
END IF;
END;
DELIMITER ;

- - tester le trigger
UPDATE Clients SET date_naissance = '2010-9-23'
PARTIE 3

WHERE id = 6;
UPDATE Clients SET date_naissance = '1999-9-23'
WHERE id = 7;

Copyright - Tout droit réservé - OFPPT 371

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple : AFTER UPDATE

• Ce déclencheur after_sales_update est

DELIMITER //
automatiquement déclenché avant qu'un
événement de mise à jour ne se produise CREATE TRIGGER after_sales_update AFTER UPDATE
pour chaque ligne de la table des ventes ON sales
FOR EACH ROW
(sales). BEGIN
IF OLD.quantity <> NEW.quantity THEN
• Si on met à jour la valeur dans la colonne de INSERT INTO SalesChanges (salesId, beforeQuantity, afterQuantity)
quantité avec une nouvelle valeur, le VALUES (OLD.id, OLD.quantity, NEW.quantity) ;
END IF ;
déclencheur insère une nouvelle ligne pour END $$
consigner les modifications dans la table
DELIMITER ;
PARTIE 3

SalesChanges.

Copyright - Tout droit réservé - OFPPT 372

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple : BEFORE/AFTER DELETE

• Ce déclencheur permet de sauvegarder un DELIMITER //

salaire dans une table d'archivage
CREATE TRIGGER before_salaries_delete BEFORE DELETE
SalaryArchives avant de le supprimer.
ON salaries
FOR EACH ROW
BEGIN
INSERT INTO SalaryArchives (employeeNumber, validFrom, amount)
VALUES (OLD.employeeNumber, OLD.validFrom, OLD.amount) ;
END $$

DELIMITER ;

CREATE TRIGGER after_salaries_delete AFTER DELETE

• Ce déclencheur permet de mettre à jour le
PARTIE 3

ON salaries
budget des salaires stockée dans la table FOR EACH ROW
SalaryBudgets après la suppression d'un UPDATE SalaryBudgets
salaire. SET total = total - OLD.salary;

Copyright - Tout droit réservé - OFPPT 373

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

LISTE des déclencheurs

• Pour lister les déclencheur de la base de données, on utilise : SHOW TRIGGERS;

Suppression d'un déclencheur

• Pour supprimer un trigger, on utilise : DROP TRIGGER [IF EXISTS] [nom_bd.nom_declencheur];
• Remarque : si on supprime une table, MySQL supprimera automatiquement tous les déclencheurs associés à la table.
PARTIE 3

Copyright - Tout droit réservé - OFPPT 374

 01 – Maîtriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Modification d'un déclencheur

▪ Il n'existe pas une instruction pour modifier un trigger, on doit le supprimer et le LOCK TABLES t1 WRITE ;
DROP TRIGGER t1_bi ;
recréer.
DELIMITER $$
▪ LOCK permet aux sessions client d'acquérir explicitement des verrous de table CREATE TRIGGER t1_bi BEFORE INSERT
dans le but de coopérer avec d'autres sessions pour accéder aux tables, ou ON t1
FOR EACH ROW
d'empêcher d'autres sessions de modifier les tables pendant les périodes où une
BEGIN
session nécessite un accès exclusif à celles-ci. …
▪ NB : MariaDB, dans la version 10.1.4, a ajouté la prise en charge de CREATE OR END $$
DELIMITER ;
REPLACE TRIGGER.
UNLOCK TABLES ;
PARTIE 3

Copyright - Tout droit réservé - OFPPT 375

 CHAPITRE 2
Optimiser une base de données MySQL

Ce que vous allez apprendre dans ce chapitre :

• Optimiser les requêtes SQL ;

• Optimiser la structure de la base de données ;
• Optimiser la configuration de serveur MySQL.

05 heures
 CHAPITRE 2
Optimiser une base de données MySQL

1. Optimisation des requêtes SQL ;

2. Optimisation de la structure de la base de données ;
3. Optimisation de la configuration de serveur MySQL.
 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Quelque conseils pour optimiser les requêtes SQL :

Éviter si possible les SELECT * et réduire le nombre de champs, afin de réduire les données chargées en mémoire ;

Remplacer les clauses WHERE … IN par WHERE EXISTS ;

Éviter d'utiliser des fonctions dans les prédicats : exemple SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC' ;

Éviter d'utiliser un caractère générique (%) au début d'un prédicat LIKE ;

Utiliser la jointure interne (inner join), au lieu de la jointure externe (outer join) si possible ;

N'utiliser DISTINCT et UNION que si nécessaire ;

N'utiliser ORDER BY que si nécessaire ;

Compter les requêtes sur chaque page. Un grand nombre de requêtes peut signifier un « problème N+1 », c'est à dire une requête SELECT placée dans
PARTIE 3

une boucle ;
Utiliser les requêtes préparées ou les procédures stockées facilite la mise en câche des requêtes en interne par MySQL et assure un bon niveau de
sécurité ;

Utiliser la clause EXPLAIN pour comprendre le fonctionnement d'une requête et quelles sont les clauses qui impactent sa performance.

Copyright - Tout droit réservé - OFPPT 378

 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Instruction préparée (Preparedstatement)

• Avant, MySQL utilise le protocole textuel pour communiquer avec le client. Ce protocole a de sérieuses implications sur les performances.

• Pour résoudre ce problème, MySQL a ajouté une nouvelle fonctionnalité appelée instruction préparée depuis la version 4.1.

• Les instances de PreparedStatement contiennent une instruction SQL déjà compilée. D'où le terme prepared. Cela améliore notamment les
performances si cette instruction doit être appelée de nombreuses fois.

• Les instructions SQL des instances de PreparedStatement contiennent un ou plusieurs paramètres d'entrée, non spécifiés lors de la création
de l'instruction. Ces paramètres sont représentés par des points d'interrogation (?). Ces paramètres doivent être spécifiés avant l'exécution.

PREPARE stmt1 FROM

'SELECT id, designation FROM produit
WHERE id = ? ' ;
PARTIE 3

- - execution
SET @id = '999' ; DEALLOCAT
PREPARE EXECUTE
EXECUTE stm1 USING @id ; E PREPARE
- - libération
DEALLOCATE PREPARE stmt1 ;

Copyright - Tout droit réservé - OFPPT 379

 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Index

• Comme l'index d'un ouvrage aide à atteindre les pages concernées par un mot recherché, un index MySQL permet d'accélérer l'accès
aux données d'une table.
• La plupart des index de MySQL (PRIMARY KEY, UNIQUE, INDEX, et FULLTEXT) sont stockés dans des arbres équilibrés (balanced trees :
B-trees).
• D'autres types d'index existent. On trouve ceux qui portent sur des colonnes SPATIAL (reverse key : R-trees), et ceux appliqués aux
tables MEMORY (tables de hachage : hash).
• L'optimiseur de requête peut utiliser des index pour localiser rapidement les données sans avoir à analyser chaque ligne d'une table
pour une requête donnée.
• Lorsqu'on crée une table avec une clé primaire ou une clé unique, MySQL crée automatiquement un index spécial nommé PRIMARY.
Cet index est appelé index clusterisé.
• L'index PRIMARY est spécial car l'index lui-même est stocké avec les données dans la même table. L'index clusterisé applique l'ordre des
PARTIE 3

lignes dans la table.

• Les autres index autres que l'index PRIMARY sont appelés index secondaires ou index non clusterisés.

Copyright - Tout droit réservé - OFPPT 380

 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Création d'index

1 - En utilisant ALTER TABLE • UNIQUE : permet de créer un index qui n'accepte

ALTER TABLE nom_table pas les doublons.
ADD INDEX nom_index(liste_colonne)
• FULLTEXT permet de bénéficier des fonctions de
2 - Lors de la création de la table recherche dans des textes (flot de caractères).
CREATE TABLE nom_table( • SPATIAL permet de profiter des fonctions des
……….
INDEX (liste_colonne) données géographiques.
); • ASC et DESC précisent l'ordre (croissant ou
3 - Après la création de la table décroissant).

CREATE [UNIQUE | FULLTEXT | SPATIAL]

INDEX nomIndex [USING BTREE | HASH]
ON [nomBase.]nomTable (colonne1 [(taille1)] [ASC | DESC],...) ;
PARTIE 3

USE eshop_app_db;
CREATE UNIQUE INDEX idx_clients_adresse USING BTREE
ON clients (adresse DESC);
Copyright - Tout droit réservé - OFPPT 381
 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Index : Avantages

Avant la définition de l'index

Après la définition de l'index

PARTIE 3

Comme on peut le voir, MySQL n'a eu qu'à localiser 1 ligne à partir de l'index nom_index comme indiqué dans la colonne clé sans parcourir
toute la table.

Copyright - Tout droit réservé - OFPPT 382

 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Suppression d'un index

DROP INDEX nom_index ON nom_table;

DROP INDEX idx_clients ON clients;

PARTIE 3

Copyright - Tout droit réservé - OFPPT 383

 02 - Optimiser une base de données MySQL
Optimisation des requêtes SQL

Stratégie d'indexation

Les points importants qu'on doit prendre en compte dans la stratégie d'indexation sont les suivants :

• Créer une clé primaire (généralement, la colonne se terminera par id) ;

• Prédire les colonnes qui seront souvent interrogées dans l’application avec WHERE, GROUP BY, HAVING et Clauses ORDER BY ;

• Avoir un index sur les colonnes qui seront utilisées avec des fonctions, telles que SUM (), COUNT (), MIN (), MAX () et AVG () ; pour
en bénéficier en termes de performances ;

• Ne surcharger pas la base de données avec trop d'index, car cela aura un impact sur les performances des clés secondaires MySQL ;

• Prédire avec des index uniques pour accélérer les requêtes de jointure (généralement, les colonnes qui se terminent par _id) ;

• En règle générale, les noms d'utilisateur ou les e-mails comme colonnes sont de bons candidats pour l'indexation ; certaines
PARTIE 3

colonnes comme URL.

Copyright - Tout droit réservé - OFPPT 384

 CHAPITRE 2
Optimiser une base de données MySQL

1. Optimisation des requêtes SQL ;

2. Optimisation de la structure de la base de données ;
3. Optimisation de la configuration de serveur MySQL.
 02 - Optimiser une base de données MySQL
Optimisation de la structure de la base de données

Optimisation de l'installation et de la configuration

Utiliser un serveur avec suffisamment de RAM ;

Surveiller les performances du serveur afin de préconiser les adaptations nécessaires : augmenter la RAM, allouer plus d'espace disque, etc. ;

Installer le SGBD sur le même serveur que l'application ;

Utiliser des connexions stables pour éviter trop d'ouvertures et de fermetures de connexion, car c'est très coûteux en terme de performance ;

Toujours fermer les connexions correctement ;

PARTIE 3

Activer le log des “slow queries” sur le serveur afin de le vérifier régulièrement ;

Utiliser le système de mise en cache disponible par le SGBD. Sinon, utiliser un système de mise en cache externe (memcached par exemple).

Copyright - Tout droit réservé - OFPPT 386

 02 - Optimiser une base de données MySQL
Optimisation de la structure de la base de données

Optimisation de la structure de la base de données

Utilisation d'une clé primaire de type numérique avec une auto-incrémentation (AUTO_INCREMENT) ;

Stocker dans une nouvelle table les données textuelles qui sont trop répétées, et les lier avec une jointure ;

Indexer les données qui sont utilisées dans WHERE, JOIN, ORDER BY et GROUP BY ;

Eviter les index pour les colonnes de type BLOB ou les champs de texte libre ;

Le moteur de données MyISAM est à privilégier par rapport InnoDB pour les requêtes SELECT ;
PARTIE 3

Utiliser un index de type UNIQUE pour les colonnes qui doivent contenir des données uniques et qu’on souhaite être sûr qu'il n'y aura pas
de doublon superflu.

Copyright - Tout droit réservé - OFPPT 387

 02 - Optimiser une base de données MySQL
Optimisation de la structure de la base de données

Optimisation de l'usage des requêtes SQL dans l'application

Minimiser le nombre de requêtes SQL utilisés au sein d'une application ;

Eviter d'appeler des requêtes SQL dans une boucle ;

Pour un système de pagination, privilégier l'usage de SQL_CALC_FOUND_ROWS et éviter de faire 2 requêtes, celle contenant les résultats
et l'autre pour compter le nombre de résultats total ;

Eviter d'utiliser la requête “WHERE IN” si on peut utiliser “WHERE EXISTS” ;

Eviter les sous-requêtes si une jointure est possible ;

PARTIE 3

Eviter de compter une colonne lorsqu'il faut compter le nombre d'enregistrement.

Copyright - Tout droit réservé - OFPPT 388

 02 - Optimiser une base de données MySQL
Optimisation de la structure de la base de données

Optimisation à long terme

Nettoyer les données non utiles pour alléger le poids d'une base de données et optimiser le temps d'exécution des requêtes ;

Utiliser une requête OPTIMIZE afin de réorganiser le stockage physique des données et améliorer l'efficacité lors de l'accès aux données ;

Veiller les informations recueillies par le fichier “slow queries” pour améliorer les requêtes ;
PARTIE 3

Copyright - Tout droit réservé - OFPPT 389

 CHAPITRE 2
Optimiser une base de données MySQL

1. Optimiser les requêtes SQL ;

2. Optimiser la structure de la base de données ;
3. Optimisation de la configuration de serveur MySQL.
 02 - Optimiser une base de données MySQL
Optimisation de la configuration de serveur

Quelque conseils pour l'optimisation des performances d'un serveur MySQL

• Si on utilise des disques durs traditionnels (HDD), on peut effectuer une mise à niveau vers des disques à semi-conducteurs (SSD) pour une
amélioration des performances.

• On peut ajuster la cache mémoire pour améliorer les performances. Si on n'a pas assez de mémoire ou si la mémoire existante n'est pas
optimisée, on peut finir par nuire à nos performances au lieu de les améliorer.

• MyISAM est un ancien style de base de données utilisé pour certaines bases de données MySQL. C'est une conception de base de données
moins efficace. Le plus récent InnoDB prend en charge des fonctionnalités plus avancées et dispose de mécanismes d'optimisation intégrés.

• Essayer d'utiliser la dernière version de MySQL.

• Envisager d'utiliser un outil d'amélioration automatique des performances( tuning-primer, MySQLTuner, etc.).
PARTIE 3

Copyright - Tout droit réservé - OFPPT 391

 PARTIE 4
S'INITIER AUX BASES DE DONNÉES NOSQL

Dans ce module, vous allez :

• Définir le concept de bases de données NoSQL,
• Comparer les bases de données traditionnelles et NoSQL,
• Identifier les avantages et les inconvénients des bases de
données NoSQL,
• Recenser les caractéristiques des NoSQL,
• Identifier les bases de données NoSQL,
• Recenser les types de bases de données NoSQL,
• Comparer les différents types de bases de données NoSQL.

05 heures
 CHAPITRE 1
Définir les bases de données NoSQL

Ce que vous allez apprendre dans ce chapitre :

• Définir le concept de base de données NoSQL,

• Comparer les bases de données traditionnelles et NoSQL,
• Identifier les avantages et les inconvénients des bases de
données NoSQL.

1,5 heures
 CHAPITRE 1
Découvrir les bases de données NoSQL

1. Définition du concept de base de données NoSQL

2. Comparaison des bases de données traditionnelles et NoSQL
3. Avantages et inconvénients des bases de données NoSQL
 01 – Introduction aux Bases de données NoSQL
Définition du concept de bases de données NoSQL

Des SGBD Relationnels .. au NoSQL

• Les défis majeurs des SGBD étaient toujours le stockage des données et la recherche des données ;

• Les SGBDR sont adaptés à gérer des données bien structurées de types simples (chaines de caractères, entier, etc.) et représentables sous
forme de tables (colonnes => propriétés et lignes => données) ;

• Ils reposent sur le modèle relationnelle d'Edgard Codd et ont prouvé leur efficacité pour des décennies grâce à :

Un ensemble de
Une forte
contraintes
Une séparation structuration des Une représentation Un langage Une forte cohérence
permettant
logique et physique données et un fort tabulaire déclaratif (SQL) transactionnelle
d'assurer l'intégrité
typage
des données

• Mais, les SGBDR ont montré leur limite face aux 3V (Volume, Velocity, Veracity) que caractérisent l’ère actuelle des données (Big Data)
PARTIE 4

Le relationnel, étant fortement

transactionnel, ne peut pas lutter
Les données sont de types,
efficacement contre les volumes
éventuellement, non compatibles avec
très importants des données
la rigidité du schéma relationnel et ses
contraintes

Copyright - Tout droit réservé - OFPPT 395

 01 – Introduction aux Bases de données NoSQL
Définition du concept de bases de données NoSQL

Des SGBD Relationnels .. au NoSQL

• Le NoSQL (Not Only SQL) propose une nouvelle manière de gérer les données, sans respecter forcement le paradigme relationnel ;

• Le NoSQL supporte de nouveaux types de données (xml, collections d'objets, triplets, etc. ) ;

• Ce formalisme propose de relâcher certaines contraintes lourdes du relationnel (structure des données, langage d'interrogation ou la
cohérence) pour favoriser la distribution ;

• Le NoSQL ne remplace pas les bases SQL, il les complète en apportant des avantages en terme de stockage réparti par exemple.
PARTIE 4

Copyright - Tout droit réservé - OFPPT 396

 01 – Introduction aux Bases de données NoSQL
Définition du concept de bases de données NoSQL

Définition

• Le NoSQL est un ensemble de technologies de BD reposant sur un modèle diffèrent du modèle relationnel,

• Les Bases NoSQL sont le fruit du mouvement NoSQL apparu au milieu des années 2000,

• Le mouvement a initialement piloté les besoins Big Data des principaux acteurs du web GAFA ( Google, Amazone, Facebook, Apple, etc.) :

Google avec sa base Apple avec sa base Facebook avec sa base Amazone avec sa base
Hbase FoundationDB Cassandra DynamoDB

• Les serveurs de données NoSQL se caractérisent par des architectures distribuées ce qui leur permettent de mieux répondre aux
PARTIE 4

problématiques du big data.

Copyright - Tout droit réservé - OFPPT 397

 CHAPITRE 1
Découvrir les bases de données NoSQL

1. Définition du concept de base de données NoSQL

2. Comparaison des bases de données traditionnelles et
NoSQL
3. Avantages et inconvénients des bases de données NoSQL
 01 – Introduction aux Bases de données NoSQL
Comparaison des bases de données traditionnelles et NoSQL

Comparaison entre les bases de données traditionnelles et NoSQL

Base de données SQL Base de données NoSQL

Les données
Les données sont représentées
sont représentées sous
sous forme formecomposées
de tables de tables d’un les
les données sontreprésentées
données sont représentées
soussous forme
forme de collections
de collections de
de paires
composées de n nombre
ensemble de lignesdedelignes de données,
données. pairesclé-valeur,
clé-valeur,dede documents,
documents, de graphes,
de graphes, etc. etc.
Elles
Elles respectentun
respectent unschéma
schéma stricte
stricteetetstandard.
standard. EllesElles
ne ne possèdentpas
possèdent pasde
de définitions
définitions de
deschéma standard.
schéma standard.

L'augmentation dedelalacharge
L'augmentation charge est géréepar
est gérée parl'augmentation
l'augmentation
du du L'augmentation de la charge est gérée plutôt par l'ajout de
L'augmentation de la charge est gérée plutôt par l'ajout de serveurs
processeur,
processeur, de de la RAM,
la RAM, du etc.
du SSD, SSD,sur
etc.
un sur
seulun seul serveur
serveur :
: Scalabilité serveurs supplémentaires : Scalabilité (mise à l'échelle)
supplémentaires : Scalabilité (mise à l'échelle) horizontale.
(mise
Scalabilité à l'échelle)
(mise verticale.
à l'échelle) verticale. horizontale.
PARTIE 4

L'augmentation de la charge n'est pas pris en compte L'augmentation

L'augmentation decharge
de la la charge est automatique,
est automatique, si un tombe
si un serveur serveur
en
L'augmentation de la charge n'est pas pris en compte nativement, elle
nativement, elle risque de compromettre l'intégrité tombeilen
panne, panne, ilautomatiquement
se remplace se remplace automatiquement parsans
par un autre serveur un
risque de compromettre l'intégrité transactionnelle de la BD.
transactionnelle de la BD. interruption
autre serveur du service. du service.
sans interruption

Copyright - Tout droit réservé - OFPPT 399

 01 – Introduction aux Bases de données NoSQL
Comparaison des bases de données traditionnelles et NoSQL

Comparaison entre les bases de données traditionnelles et NoSQL

Base de données SQL Base de données NoSQL

Repose sur les propriétés BASE (Basically Available, Soft state,

Assure l'intégrité
Assure des données
l'intégrité des données en
en assurant
assurant la
la conformité
conformité ACID
ACID
Eventualy Consistent) (voir la partie « caractéristiques générales des
(Atomicité,
(Atomicité,Cohérence,
Cohérence,Isolation et Durabilité).
Isolation et Durabilité)
bases NoSQL »).

Recommandée
Recommandéepar
pardedenombreuses
nombreusesentreprises en en
entreprises raison de de
raison sa
sa structure Recommandée pour les données semi structurées ou même non
structure et deetses
deschémas
ses schémas prédéfinis.
prédéfinis.
Mais, ne convient pas au stockage de données hiérarchiques structurées.
Mais, ne convient pas au stockage de données hiérarchiques.
PARTIE 4

La plus appropriée pour les applications transactionnelles à usage

Hautement préférée pour les ensembles de données volumineux et
intensif étant plus stable et assurant l'atomicité, l'intégrité et la
hiérarchiques.
cohérence des données.

Copyright - Tout droit réservé - OFPPT 400

 01 – Introduction aux Bases de données NoSQL
Comparaison des bases de données traditionnelles et NoSQL

Résumons

NoSQL SQL

Basé sur le langage de requête unifié

Offre des meilleures performances que
(SQL) qui apporte une certaine
SQL vu qu'il ne gère aucune règle de
uniformité entre les différentes bases
cohérence
SQL,

Offre une meilleure fiabilité et

Optimisé pour gérer d'énormes volumes cohérence des données au détriment de
de données avec performance la performance si les données
deviennent volumineuses
PARTIE 4

Certes NoSQL et SQL permettent de stocker/rechercher de l'information, mais ils ne servent pas pour les mêmes objectifs ce qui rend toute
comparaison subjective voir non justifiée.

Copyright - Tout droit réservé - OFPPT 401

 CHAPITRE 1
Découvrir les bases de données NoSQL

1. Définition du concept de base de données NoSQL

2. Comparaison des bases de données traditionnelles et NoSQL
3. Avantages et inconvénients des bases de données NoSQL
 01 – Introduction aux Bases de données NoSQL
Définition du concept de bases de données NoSQL

Avantages du NoSQL
• Le format de la BD NoSQL est basée essentiellement sur des pairs clé-valeur (par exemple) beaucoup plus simple à mettre en œuvre

• Il est possible de stocker directement des objets manipulés dans des langages de programmation comme des listes, des collections d'objets,
des tableaux de valeurs, etc.

• Les bases de données NoSQL sont pour la plupart Open-source et ne possèdent pas de droits de licence

• Il est très facile d'étendre une base de données NoSQL en rajoutant, tout simplement des serveurs

• Les données sont regroupées par unités logiques et non pas dans des tables ce qui facilite la manipulation. Par exemple, pour avoir les
informations d'un client qui a passé une commande donnée, on n’aura pas besoin de passer par des jointures entre les tables client et
commande

Inconvénients du NoSQL
PARTIE 4

• Absence du concept de clé étrangère, ce qui veut dire qu'il n'y a pas de mécanisme pour vérifier la cohérence des données

• NoSQL n'est pas adaptable aux applications basées sur des transactions sécurisées et fiables (Gestion bancaire par exemple)

• Les requêtes SQL et NoSQL ne sont pas compatibles

Copyright - Tout droit réservé - OFPPT 403

 CHAPITRE 2
Identifier les caractéristiques des bases de
données NoSQL

Ce que vous allez apprendre dans ce chapitre :

• Recenser les caractéristiques des NoSQL,

• Identifier les bases de données NoSQL,
• Recenser les types de bases de données NoSQL (document,
clé / valeur, colonne, graphe),
• Comparer les différents types de bases de données NoSQL.

3,5 heures
 CHAPITRE 2
Identifier les caractéristiques des bases de
données NoSQL

1. Caractéristiques des NoSQL

2. Types de bases de données NoSQL
3. Comparaison des différents types de bases de données
NoSQL
 02 - Identifier les caractéristiques des bases de
données NoSQL
Caractéristiques

Caractéristiques générales des bases NoSQL

Caractéristiques générales
des bases NoSQL

Manipulation avec
BASE (Basically Manipulation des
performances de
Modèle de données Available, Soft State, Utilisation des données de
Architecture volumes de données
sans schéma Eventually langages qui ne sont structures
distribuée exponentiels (cas
(Schema Free) consistent) au lieu pas uniquement SQL complexes ou
des applications
de ACID imbriquées
Web)
PARTIE 4

Copyright - Tout droit réservé - OFPPT 406

 02 - Identifier les caractéristiques des bases de
données NoSQL
Caractéristiques

1- Modèle Sans schéma (Schema Free)

• Dans un contexte relationnel, la création d'une base de données commence par la modélisation des entités et associations puis d'en déduire
un schéma de la base

• Cette démarche crée une rigidité dans la phase d'implémentation, puisqu'elle implique d'avoir une vision assez claire des évolutions de
l'application dès le départ et au fil du temps, ce qui n'est pas souvent le cas de nos jours

• Les bases de données NoSQL s'appuient sur des données dénormalisées, non modélisées par des relations, mais plutôt par des
enregistrements (ou documents) intégrés. Il est donc possible d'interagir sans utiliser de langages de requêtes complexe
PARTIE 4

Copyright - Tout droit réservé - OFPPT 407

 02 - Identifier les caractéristiques des bases de
données NoSQL
Caractéristiques

1- Modèle Sans schéma (Schema Free)

• Exemple

SQL NoSQL

Posts(id,titre)
Posts(id,titre,commentaires)
Commentaires(id, #idPosts,texte)

Posts Commentaires
Posts
Id titre Id idPosts texte
P1 Titre1 Comment1
P1 Titre1 C1 P1 comment1
P2 Titre2 C2 P2 comment2 Comment3

C3 P1 comment3
PARTIE 4

P2 Titre2 Comment2

Copyright - Tout droit réservé - OFPPT 408

 02 - Identifier les caractéristiques des bases de
données NoSQL
Caractéristiques

2- Architecture distribuée

• Le volume de données à stocker ainsi que les traitements demandées par les organismes modernes, ne peuvent plus être satisfaits sur une
seule machine quelque soit sa performance. Même en utilisant un réseau de machine, l'interconnexion entre machines rendent les
traitements très lents ;

• Solution : un patron d'architecture propose de distribuer les traitements (le travail/la charge) sur plusieurs machine puis regrouper les
résultats de chaque machine et les agrèger dans un résultat final → Apparition de MapReduce en 2003 ;

• Les bases de données traditionnelles ne permettent pas l'implantation d'un tel patron d'architecture ;

• Les BD NoSQL sont conçues pour distribuer les données et les traitements associés sur de multiple nœuds(serveurs) →partitionnement
horizontal ;

• Problème : impossible d'avoir en même temps une disponibilité des données satisfaisante, une tolérance au partitionnement et une
PARTIE 4

meilleure cohérence des données ;

• Il faut toujours condamner un aspect en faveur des autres.

Copyright - Tout droit réservé - OFPPT 409

 02 - Identifier les caractéristiques des bases de
données NoSQL
Caractéristiques

2- Architecture distribuée

Théorème de CAP (Consistency, Aviability, Partition tolerance)

Dans toute base de données, on ne peut respecter au plus que deux propriétés parmi les trois propriétés suivantes :
la cohérence, la disponibilité et la distribution

A savoir :

• Consistency (cohérence) : tous les nœuds(serveurs) sont à jour sur les données au même
Partition
moment ;
tolerance CP
Consistency
• Avaibility (disponibilité) : la perte d'un nœud(serveur) n'empêche pas le système de
AP CA
fonctionner et de servir l'intégralité des données ;
PARTIE 4

• Partition tolerance (résistance au partitionnement) : chaque nœud(serveur) doit pouvoir Aviability

fonctionner de manière autonome.

Copyright - Tout droit réservé - OFPPT 410

 02 - Identifier les caractéristiques des bases de
données NoSQL
Caractéristiques

3- BASE vs ACID

• Les propriétés ACID ne sont pas partiellement ou totalement applicables dans un contexte NoSQL

• Les bases NoSQL reposent, par contre, sur les propriétés BASE :

Basically Available Soft State Eventually consistent

• la base garantie la • La base NoSQL n'a pas à • Plus besoin d'avoir
disponibilité des être cohérente tout le exactement les mêmes
données quelle que soit temps, son état peut données sur tous les
la charge, changer lors des mises à nœuds (serveurs) de la
jour ou lors base, mais on peut
d'ajout/suppression de synchroniser quand on
serveurs sans que cela peut
ne ??? Sa performance
PARTIE 4

• Les bases NoSQL privilégient la disponibilité à la cohérence : AP (Avaibility + Partition tolerance) plutôt que CP (Consistency + Partition
tolerance).

Copyright - Tout droit réservé - OFPPT 411

 CHAPITRE 2
Identifier les caractéristiques des bases de
données NoSQL

1. Caractéristiques des NoSQL

2. Types de bases de données NoSQL
3. Comparaison des différents types de bases de données
NoSQL
 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 1 - Bases de données Clé-Valeur

Le type le plus simple

Bases de données Clé-Valeur

Les données sont représentées par des couples (clé/valeur)

Une valeur peut être une valeur de type simple, un objet

sérialisé, etc.
Vu sa simplicité, ce type de BD NoSQL est capable de prendre en
charge efficacement un grand nombre de requêtes

IL est très commode pour stocker les profils des utilisateurs.

Exemple :
Clé Valeur
PARTIE 4

Ahmed type :Formateur; spec : Dev digital; modules :M102, M104, M106, M203

Sanaa type : Stagiaire; filière : Dev digital; groupe :DD203; niveau : 2A

Kamal type : Stagiaire; filière : Int artificielle; niveau : 1A

Copyright - Tout droit réservé - OFPPT 413
 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 1 - Bases de données Clé-Valeur

Exemples de bases de données Clé/valeur

Dynamo DB Berkeley DB ou BDB Voldemort de Linkedin Riak DB

Amazone solution d'oracle (et pas le sorcier de Apache
GMAIL, RPM,SVN, etc. Harry Potter ☺)
PARTIE 4

Copyright - Tout droit réservé - OFPPT 414

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 2 - Bases de données orientées Document

Evolution du type clé/valeur

Bases de données orientées

La valeur ici est un document dont la structure reste libre

document
Les documents sont stockés sous forme de fichiers JSON ou XML

Ce type possède l'avantage d'éviter les jointures pour reconstruire une

information puisque tout est compris dans la structure des documents

Exemples
Clé Ahmed Sanaa Ghizlane
PARTIE 4

{"type" :"Formateur", {"type" :"Stagiaire",

"spec" : "Dev digital", "filiere" : "Dev digital", {"type" :"Stagiaire",
Valeur "modules" :["M102", "groupe" :"DD203", "filiere" : "Int Artificielle",
"M104", "M106", "niveau" :"2A", "niveau" :"1A"}
"M203"]} "option" :"Mobile"}

Copyright - Tout droit réservé - OFPPT 415

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 2 - Bases de données orientées Document

Exemples de bases de données orientées Document

Mongo DB de CouchDB d'Apache RavenDB Cassandra de FaceBook

SourceForge Disney, PayPal, Ryanair, Plateformes NY Times, eBay, Sky,
Adobe, Bosch, Cisco, etc.. .Net/Windows Pearson Education
eBay, etc.
PARTIE 4

Copyright - Tout droit réservé - OFPPT 416

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 3 - Bases de données orientées Colonne

Bases de données orientées Colonne

Ce type change le paradigme traditionnel de la représentation des données en lignes

Il rend possible de focaliser les requêtes sur les colonnes importantes sans avoir à traiter les
données des autres colonnes (jugées alors inutiles pour la requête)

Ce type est adapté aux systèmes avec de gros calculs analytiques ( comptage, moyenne,
somme, etc.)
PARTIE 4

Copyright - Tout droit réservé - OFPPT 417

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 3 - Bases de données orientées Colonne

Exemple : représentation traditionnelle

Id Type Spécialité Niveau Filière Groupe Option Module

Ahmed Formateur Dev Digital M102, M104, M106, M202

Sanaa Stagiaire 2A Dev Digital DD203 Mobile

Ghizlane Stagiaire 1A Int Artificielle

Laila Formateur Int Artificielle M105,M107,M201

PARTIE 4

Copyright - Tout droit réservé - OFPPT 418

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 3 - Bases de données orientées Colonne

Exemple : représentations par colonnes

Id Type Spécialité Niveau Filière Groupe Option Module
Ahmed Formateur Dev Digital M102, M104, M106, M202
Sanaa Stagiaire 2A Dev Digital DD203 Mobile
Ghizlane Stagiaire 1A Int Artificielle
Laila Formateur Int Artificielle M105,M107,M201

Id Type Id Filière Id Module Id Option

Ahmed Formateur Sanaa Stagiaire Ahmed M102 Sanaa Mobile
Sanaa Stagiaire Ghizlane Stagiaire Ahmed M104
Ghizlane Stagiaire Ahmed M106
PARTIE 4

Laila Formateur Ahmed M202

Laila M105
Laila M107
Laila M201
Copyright - Tout droit réservé - OFPPT 419
 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 3 - Bases de données orientées Colonne

Exemples de bases de données orientées Colonne

BigTable DB de Google HBase d'Apache SparkSQL d'Apache Elasticsearch db

PARTIE 4

Copyright - Tout droit réservé - OFPPT 420

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 4 - Bases de données orientées Graphe

Bases de données orientées Graphe

Il est basé sur la théorie des graphes et ses fondements mathématiques

Ce type est conçu principalement pour les données fortement interconnectées (comme les
données des réseaux sociaux) avec un nombre indéterminé de relations entre elles

En d'autres termes, c'est le type le mieux approprié pour modéliser le monde réel
PARTIE 4

Exemple : la principale solution est Neo4j

Copyright - Tout droit réservé - OFPPT 421

 02 - Identifier les caractéristiques des bases de
données NoSQL
Types des bases NoSQL

Les quatre types des bases NoSQL : 4 - Bases de données orientées Graphe

Exemple

Mobile

Dev Int
Digital Artificielle

Ahmed
Type : Sanaa Laila Ghizlane
Formateur Type : Type : Type :
Stagiaire Formateur Stagiaire
PARTIE 4

M105 M201
M102 M202

Copyright - Tout droit réservé - OFPPT 422

 CHAPITRE 2
Identifier les caractéristiques des bases de
données NoSQL

1. Caractéristiques des NoSQL

2. Types de bases de données NoSQL
3. Comparaison des différents types de bases de données
NoSQL
 02 - Identifier les caractéristiques des bases de
données NoSQL
Comparaison des types de BD NoSQL

Riak (Clé/Valeur) MongoDB Cassandra HBase

(Document) (Document) (Colonne)

Coût + ++ ++ ++

Cohérence + ++ + +

Disponibilité ++ + ++ ++

Langages ++ ++ + ++
d'Interrogation
Fonctionnalités Solution hautement La solution la plus Solution mature, populaire. Destinée aux données
disponible avec un populaire. Une Excellente solution pour volumineuses.
langage de requêtes structure souple et grands volumes de données Privilégie le langage et la
performant bonnes besoins de bases distribuées. disponibilité à la
Approprié au performances. Mais langage trop réduit cohérence des données
PARTIE 4

stockage dans le Favorise la

cloud cohérence à la
disponibilité

Copyright - Tout droit réservé - OFPPT 424