Bases de données

MySQL
Pr. Safae SMIRI
ENSAO
2023/2024
 Plan

• Le SGBD "MySQL"

• Types de données MySQL

• SQL

• Le LDD MySQL

• Le LMD MySQL

• Utilisateurs et privilèges

2
Le SGBD MySQL
 Présentation de MySQL

• SGBD relationnel

• Basé sur le modèle client/serveur

• Multi-thread et multi-utilisateur

• Développé dans un souci de performances élevées en lecture

• Il est davantage orienté vers le service de données déjà en place que

vers celui de mises à jour fréquentes et fortement sécurisées

• Distribué sous une double licence

◦ GPL: la version gratuite est MySQL community
◦ Propriétaire: la version commerciale payante existe également

4
 MySQL: Historique
• Dates importantes
◦ 1994: début du développement
◦ 1995: la société MySQLAB fondée et sortie de la version officielle de MySQl
◦ 2008: MySQLAB rachetée par Sun Microsystems
◦ 2010: Sun Microsystems rachetée par Oracle Coorporation

• versions
◦ Version 4.0 : octobre 2001, stable depuis mars 2003
◦ Version 4.1 : avril 2003, stable depuis octobre 2004
◦ Version 5.0 : décembre 2003, stable depuis octobre 2005
◦ Version 5.1 : novembre 2005, Release Candidate distribuée depuis septembre 2007
◦ Version 5.2 : distribuée en avant-première (ajout du nouveau moteur de stockage Falcon) en février 2007,
ensuite renommée 6.0
◦ Version 5.5 : Version stable depuis octobre 2010
◦ Version 5.6 : Version stable depuis février 2013
◦ Version 6.0 : première version alpha en avril 2007, abandonnée depuis le rachat de MySQL par oracle en
décembre 2010 5
 MySQL: Les concurrents

• Oracle database
◦ SGBDR payant, à coût élevé, utilisé principalement par les sociétés
◦ Disponible sous Linux, Windows, Unix et MacOS
◦ Performant pour la gestion de grands volumes de données (plus de 200 GO)
et de grand nombre d’utilisateurs (plus de 300)
• PostgreSQL
◦ SGBDR open source et gratuit
◦ Disponible sous Linux, Unix, MacOS et Windows
◦ A été, pour longtemps, disponible uniquement sous unix. 1ère version
windows en 2005
◦ Riche en fonctionnalités, héritage, multitude de modules
◦ Simple d’usage

6
 MySQL: Les concurrents

• SQLite
◦ Licence BSD, open source et gratuit
◦ Disponible sous Linux, MacOS, Windows, Unix, BSD
◦ Bibliothèque écrite en C qui propose un SGBDR accessible par le langage
SQL (porté sur C#)
◦ N’utilise pas le modèle client/serveur
◦ Très performant pour de très petits volumes de données
• MS access
◦ SGBDR propriétaire: édité par Microsoft et ne fonctionne que sous
windows
◦ Couplé à un moteur de base de données "Fichier"
◦ Outils de reporting, conversion des données
◦ Possibilité de s'en servir comme interface sur une base SQLServer

7
Types de données
 Choix des types de données

• Choisir un mauvais type des données peut entrainer:

◦ un gaspillage de mémoire (si on stocke de toutes petites données dans
une colonne faite pour stocker de grosses quantités de données)
◦ des problèmes de performance (il est plus rapide de faire une recherche
sur un nombre que sur une chaîne de caractères)
◦ un comportement contraire à celui attendu (trier sur un nombre stocké
comme tel, ou sur un nombre stocké comme une chaîne de caractères ne
donnera pas le même résultat)
◦ l'impossibilité d'utiliser des fonctionnalités propres à un type de données
(stocker une date comme une chaîne de caractères vous prive des
nombreuses fonctions temporelles disponibles)
=> comprendre les usages et particularités de chaque type de données, afin
de choisir le meilleur type possible

9
 Les types de données MySQL

• Types numériques
◦ Nombres entiers
◦ Nombres décimaux

• Types alphanumériques
◦ Chaines de type texte
◦ Chaines de type binaire

• Types temporels
◦ DATE, TIME et DATEETIME
◦ YEAR
◦ TIMESTAMP
◦ Date par défaut
• SET et ENUM

10
Types numériques

11
 Nombres entiers

Type Nb d’octets Minimum Maximum

TINYINT 1 -128 127

SMALLINT 2 -32768 32767

MEDIUMINT 3 -8388608 8388607

INT 4 -2147483648 2147483647

BIGINT 8 -9223372036854775808 9223372036854775807

• si nous essayons de stocker 12457 dans un TINYINT, MySQL stockera la

valeur la plus proche
◦ la valeur stockée sera 127

12
 Nombres entiers

• L’attribut UNSIGNED
◦ La valeur stockée est toujours positive
◦ Le minimum vaut 0
◦ Exemple: l’intervalle des TINYINT va de 0 à 255

• INT(X): limite le nombre de chiffres minimum à l'affichage à X sans changer

la capacité de stockage
◦ Si un nombre contient un nombre de chiffres inférieur au nombre défini, un
espace (le caractère par défaut) sera ajouté à gauche du chiffre pour qu'il
prenne la bonne taille
◦ l'attribut ZEROFILL change le caractère par défaut par '0‘
◦ exemple de déclaration d’une colonne INT(4) ZEROFILL

13
 Nombres décimaux

• DECIMAL
• NUMERIC
• FLOAT
• REAL
• DOUBLE

14
 NUMERIC & DECIMAL
• NUMERIC et DECIMAL sont équivalents
• DECIMAL (5,3) ou NUMERIC(5,3)
◦ 5 est la précision: définit le nombre de chiffres significatifs stockés
◦ 3 est l'échelle: définit le nombre de chiffres après la virgule
◦ Exemple: 75,452
• Dans un champ DECIMAL(5,3)
◦ En SQL pur, on peut stocker des nombres allant jusqu’à 99,999
◦ MySQL permet de stocker des nombres allant jusqu'à 999.999
En effet,
◦ Pour les nombres positifs, MySQL utilise l'octet qui sert à stocker le signe ‘-’
pour stocker un chiffre supplémentaire
• Règles
◦ Si le nb saisi est trop loin dans les positifs 999.999 sera stocké
◦ Si le nb saisi est trop loin dans les négatifs -99.999 sera stocké
◦ S'il y a trop de chiffres après la virgule, MySQL arrondira à l'échelle définie

15
 FLOAT, DOUBLE et REAL

Type Description Nombre d’octets

FLOAT Décimal simple précision 4 octets

REAL Décimal double précision 8 octets

DOUBLE Décimal double précision 8 octets

16
 FLOAT, DOUBLE et REAL

• FLOAT peut s'utiliser sans paramètres

◦ Dans ce cas, quatre octets sont utilisés pour stocker les valeurs
◦ Il est aussi possible de spécifier une précision et une échelle comme pour
DECIMAL et NUMERIC
• REAL et DOUBLE ne supportent pas de paramètres
◦ MySQL utilise 8 octets pour stocker les valeurs dans REAL et DOUBLE
◦ SQL utilise 4 octets pour REAL et 8 octets pour DOUBLE
◦ DOUBLE est conseillé pour la compatibilité en cas de changement de SGBD

17
 Valeurs exactes / valeurs
approchées

• NUMERIC et DECIMAL sont des types numériques à valeur exacte

◦ Les nombres sont stockés sous forme de chaînes de caractères
• FLOAT, REAL et DOUBLE sont des types à valeur approchée
◦ Une valeur approchée de 56,6789 (par exemple, 56,678900000000000001)
sera stockée
◦ Cela pose problème lors des comparaisons: 56,678900000000000001 n‘est
pas égal à 56,6789
• Il est conseillé d'utiliser un type numérique à valeur exacte lorsque la
précision des données est crucial
◦ Données bancaires par exemple

18
Types alphanumériques
 CHAR et VARCHAR

• CHAR(x) et VARCHAR(x) sont utilisés pour stocker un texte de longueur x

(x < 255 octets)
• Un CHAR(x) stocke x octets
◦ en remplissant des espaces vides si nécessaire
• Un VARCHAR(x) stocke entre 0 et x octets plus la taille du texte stocké
◦ Si le texte saisi est plus long que la taille maximale définie pour le champ il
sera tronqué
• La longueur du texte est comptée en octets car des caractères peuvent se
coder sur deux octets
◦ Exemple des caractères accentués en UTF-8

20
 Le type TEXT

• Les types TEXT, TINYTEXT, MEDIUMTEXT et LONGTEXT servent à

stocker des textes de plus de 255 octets

Type Longueur maximale Mémoire occupée

TINYTEXT 2^8 octets Longueur de la chaine + 1 octet

TEXT 2^16 octets Longueur de la chaine + 2 octets

MEDIUMTEXT 2^24 octets Longueur de la chaine + 3 octets

LONGTEXT 2^32 octets Longueur de la chaine + 4 octets

21
 Chainesbinaires

• Une chaîne binaire est une suite d’octets sans aucun encodage ni
interprétation
• Une chaine binaire traite l’octet et non le caractère représenté par cet octet
◦ ‘a’ est différent de ‘A’
• Tous les caractères sont utilisables, y compris les caractères de contrôle non-
affichables définis dans la table ASCII
• Les types binaires sont parfaits pour stocker des données "brutes" comme
des images par exemple
• Les types TEXT sont parfaits pour stocker du texte

22
 Types binaires

• BINARY(x) et VARBINARY(x)
◦ Permettent de stocker des chaînes binaires de x caractères maximum
◦ Avec une gestion mémoire identique à CHAR(x) et VARCHAR(x)
• TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB
◦ Permettent le stockage de chaines binaires plus longues (> 255 octets)
◦ Avec gestion mémoire identique aux champs de type TEXT

23
SET et ENUM

24
 ENUM

• Type d’énumération spécifique de MySQL

• Les valeurs sont de type chaine de caractères parmi un certain nombre de
valeurs autorisées
• Exemple:

semaine ENUM(‘lundi', ‘mardi', ‘mercredi‘, ’jeudi’, ’vendredi’, ’samedi’,

’dimanche’)

• Si une chaîne non-autorisée est introduite MySQL stockera une chaîne vide ‘ ’
dans le champ

25
 Remplir un champ de type ENUM

• Directement avec la valeur choisie

• Utiliser l’index de la valeur
◦ L'index est attribué selon l'ordre dans lequel les valeurs ont été données lors de la
création du champ
◦ La chaîne vide (stockée en cas de valeur non-autorisée) correspond à l'index 0
• S’il faut stocker ‘dimanche’ dans un champ
◦ Insérer la valeur ‘dimanche’
◦ ou insérer 7 (il s'agit d'un nombre, pas d'un caractère)
Valeur Index
NULL NULL
‘‘ 0
‘lundi’ 1
‘mardi’ 2
… …
‘dimanche’ 7
26
 SET

• Le type d’ensemble de MYSQL

• Un champ de type SET permet de stocker un ensemble de chaînes de

caractères dont les valeurs possibles sont prédéfinies par l'utilisateur

• Différence avec ENUM:

◦ On peut stocker dans la colonne entre 0 et x valeur(s),
◦ x étant le nombre de valeurs autorisées

• Un champ de type SET peut avoir jusqu’à 64 valeurs définies

27
 SET

créneau SET (‘8h-10h’, ’10h-12h’, ’14h-16h’, ’16h-18h’)

On peut stocker dans un champ de type créneau :

◦ ‘ ' (chaîne vide ) ;
◦ ‘8h-10h'
◦ ‘8h-10h,10h-12h'
◦ ‘8h-10h,10h-12h,14h-16h,16h-18h’
◦ ’10h-12h,14h-16h’, etc
• A noter que:
◦ Il faut séparer par virgule sans espace et entourer toutes les valeur
par des guillemets (non pas chaque valeur séparément)
◦ Les valeurs autorisées pour les champs de type SET ne peuvent pas
contenir des virgules elles mêmes
◦ On ne peut pas stocker la même valeur 2 fois: ‘8h-10h,8h-10h’ n’est
pas une valeur valable
28
 Avertissement

• SET et ENUM sont des types propres à MySQL

• Il faut les utiliser avec prudence !

29
Types temporels

30
 Les types temporels de MySQL

• DATE, DATETIME, TIME, TIMESTAMP et YEAR

• MySQL effectue quelques vérifications de base sur la validité de la date
entrée
◦ le jour doit être compris entre 1 et 31 et le mois entre 1 et 12
◦ Mais il est possible d'entrer une date telle que le 31 février 2023

31
 DATE

• Une date peut être entrée sous forme de nombre ou de chaîne de

caractères mais l’ordre est important
◦ 'AAAA-MM-JJ'
◦ c'est sous ce format-ci qu'une DATE est stockée dans MySQL
◦ 'AAMMJJ'
◦ 'AAAA/MM/JJ'
◦ 'AA+MM+JJ'
◦ 'AAAA%MM%JJ'
◦ AAAAMMJJ (nombre)
◦ AAMMJJ (nombre)

32
 DATE

◦ le siècle n'est pas précisé, MySQL décide selon les critères suivants :
◦ si l'année donnée est entre 00 et 69, le 21e siècle est utilisé (intervalle de
2000 à 2069)
◦ si l'année est entre 70 et 99, le 20e siècle est utilisé (intervalle entre
1970 et 1999)
◦ Comment MySQL comprends les dates suivantes:
◦ 12-12-12 => 2012-12-12
◦ 75-11-06 => 1975-11-06
◦ 70-08-25 => 1970-08-25
◦ 69-11-17 => 2069-11-17
◦ MySQL supporte des DATE allant de '1001-01-01' à '9999-12-31'

33
 DATETIME

◦ Formats acceptés
◦ 'AAAA-MM-JJ HH:MM:SS'
◦ c'est sous ce format-ci qu'un DATETIME est stocké dans MySQL
◦ 'AA*MM*JJ HH+MM+SS'
◦ AAAAMMJJHHMMSS (nombre)
◦ MySQL supporte des DATETIME allant de '1001-01-01 00:00:00' à
'9999-12-31 23:59:59'

34
 TIME

• Formats acceptés
◦ 'HH:MM:SS'
◦ 'HHH:MM:SS'
◦ 'MM:SS'
◦ 'J HH:MM:SS'
◦ 'HHMMSS'
◦ HHMMSS (nombre)
• Possibilité de stocker un intervalle de temps
◦ Le nb d’heures n’est pas limité à 24h
◦ Le nombre de jours ou l’intervalle peut être négatif
• MySQL supporte des TIME allant de '-838:59:59' à '838:59:59'

35
 YEAR

• Le type YEAR ne prend qu'un seul octet en mémoire

• On ne peut y stocker que des années entre 1901 et 2155
• Une donnée de type YEAR peut être entrée sous forme de chaîne de
caractères ou d'entiers, avec 2 ou 4 chiffres
• Si on ne précise que deux chiffres, le siècle est ajouté par MySQL selon les
mêmes critères que pour DATE et DATETIME
◦ Exception :
◦ la valeur entière 00 sera interprétée comme la valeur par défaut de YEAR
0000
◦ La chaine de caractères '00‘ sera interprétée comme l'année 2000

36
 TIMESTAMP

• le timestamp d'une date est le nombre de secondes écoulées depuis le 1er

janvier 1970, 0h0min0s (UTC) et la date en question
• Les timestamps sont stockés sur 4 octets
◦ limite supérieure : le 19 janvier 2038 à 3h14min7s
• Le type TIMESTAMP de MySQL
◦ Ne stocke pas un nombre de secondes, mais une date sous format
numérique AAAAMMJJHHMMSS
◦ Exemple: le timestamp du 4 octobre 2011, à 21h05min51s
n’est pas stocké comme 1317755151 mais 20111004210551
◦ C’est-à-dire: sous format numérique du DATETIME correspondant: ‘2011-
10-04-21-05-51’
◦ A les même limites qu'un vrai timestamp
◦ Il n'accepte que des date entre le 1e janvier 1970 à 00h00min00s et le 19
janvier 2038 à 3h14min7s
37
 Date par défaut

• Lorsque MySQL rencontre une date/heure incorrecte, ou qui n'est pas dans
l'intervalle de validité du champ, la valeur par défaut est stockée à la place

Type Date par défaut (zéro)

DATE ‘0000-00-00’
DATETIME ‘0000-00-00 00:00:00’
TIME ‘00:00:00’
YEAR 0000
TIMESTAMP 00000000000000

• Exception
• si une valeur TIME dépasse l'intervalle de validité, MySQL ne la remplacera
pas par le "zéro", mais par la plus proche valeur appartenant à l'intervalle
de validité (-838:59:59 ou 838:59:59)

38
SQL (Structured Query
Language)
 Norme SQL

• A été normalisé depuis 1986, mais les premières normes, incomplètes, ont
été ignorées par les SGBD
• La norme SQL2 (appelée aussi SQL92) date de 1992
• SQL-2 définit trois niveaux :
◦ Full SQL (ensemble de la norme)
◦ Intermediate SQL
◦ Entry Level (ensemble minimum à respecter pour se dire à la norme SQL-2)
• SQL3 est une extension introduisant les concepts orientés objet
• Malgré ces normes, il existe des différences non négligeables entre
les syntaxes et fonctionnalités des différents SGBDs

40
 Présentation de SQL

• SQL «Structured Query Language»: Langage de requêtes structuré

• Langage complet de gestion de BDs relationnelles
◦ un Langage de Définition des Données (LDD ; ordres CREATE, ALTER, DROP),
◦ un langage d'interrogation de la base (ordre SELECT)
◦ un Langage de Manipulation des Données (LMD; ordres UPDATE, INSERT,
DELETE)
◦ un Langage de Contrôle d'accès aux Données (LCD ; ordres GRANT, REVOKE).
• Utilisé par les principaux SGBDR: DB2, oracle, informix, ingrès…

41
LDD (Langage de Définition des
Données)
CREATE – ALTER – DROP
 Création de la base de donnée

• Syntaxe générale

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

• Il faut avoir le privilège ‘CREATE SCHEMA’ synonyme de ‘CREATE DATABASE’

• Erreur si la BD existe et on n’a pas spécifié ‘IF NOT EXISTS’
• Les options de ‘create specification’ spécifient les caractéristiques de la BD
stockées dans le fichier ‘[Link]’
• Une BD MySQL est implémenté comme un répertoire contenant:
◦ les fichiers correspondant aux tables de la BD
◦ Le fichier [Link] 43
 Création de table

• Syntaxe générale

CREATE TABLE [IF NOT EXISTS] Nom_table

( colonne1 description_colonne1, [colonne2
description_colonne2, colonne3 description_colonne3, ...,]
[PRIMARY KEY (colonne_clé_primaire)]
)
[ENGINE=moteur];

• Il faut avoir le privilège de création pour les tables

• 2 moteurs de stockage (engine) les plus utilisés
◦ MyISAM
◦ InnoDB

44
 Moteurs de stockage

• MyISAM
◦ Moteur de stockage par défaut
◦ Insertion et sélection des données rapides
◦ Ne gère pas les clés étrangères et les transactions
• InnoDB
◦ Supporte les clés étrangères et les transactions
◦ Plus lent et plus gourmand en ressources que MyISAM

• MySQL représente chaque table dans le répertoire de la BD par un fichier

de données .frm

45
 Définition des colonnes

• Au minimum spécifier le type de la colonne

NomClient VARCHAR(30);
• Not NULL
NomClient VARCHAR(30) NOT NULL;
◦ Par défaut NULL est autorisé
• AUTOINCREMENT
NumeroClient SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT;
• DEFAULT
Ville VARCHAR(30) NOT NULL DEFAULT ‘Oujda’;
◦ Lorsqu’aucune valeur n’est précisée au moment de l’insertion, le champ
prend la valeur par défaut
◦ La valeur par défaut doit être une constante et non pas une fonction

46
 Exemple de création de table

CREATE TABLE Client(

NumClient SMALLINT UNSIGNED NOT NULL
AUTO_INCREMENT,
NomClient VARCHAR(30) NOT NULL,
date_naissance DATETIME NOT NULL,
ville VARCHAR(30) NOT NULL DEFAULT
‘Oujda’, PRIMARY KEY (id)
)
ENGINE=INNODB;

Vérification de la structure des tables

◦ SHOW TABLES;

◦ DESCRIBE Client;

47
 Suppression d’une table

DROP TABLE Client;

• Commande à utiliser avec prudence car elle est irréversible

48
Modification du schéma

49
 Modification des tables

• La création du schéma est la première étape dans la vie d’une BD

• Plusieurs modifications peuvent avoir lieu après:
◦ Ajouter des colonnes aux tables, en modifier la définition, etc.
• En plus des tables, d’autres éléments de la BD peuvent faire l’objet de
modifications comme les contraintes ou les indexes
• Syntaxe générale:
ALTER TABLE nomTable ACTION description

◦ ACTION = ADD, DROP, CHANGE, MODIFY

◦ Description = la commande de modification associée à ACTION

50
 Ajout d’une colonne

• Syntaxe
ALTER TABLE nom_table
ADD [COLUMN] nom_colonne description_colonne;

◦ [COLUMN] est facultatif: s’il n’est pas précisé, MySQL

considère qu’il s’agit d’une colonne

ALTER TABLE Client

ADD COLUMN nationnalité VARCHAR(30) NOT NULL DEFAULT
‘MAROCAINE’;

51
 Suppression d’une colonne

• Syntaxe
ALTER TABLE nom_table
DROP [COLUMN] nom_colonne description_colonne;

◦ [COLUMN] est facultatif: s’il n’est pas précisé, MySQL

considère qu’il s’agit d’une colonne

ALTER TABLE Client

DROP COLUMN nationnalité VARCHAR(30) NOT NULL DEFAULT
‘MAROCAINE’;

52
 Modification d’une colonne

• CHANGE et MODIFY permettent de changer le type des données de la

colonne, de changer la valeur par défaut et d’ajouter/supprimer une
propriété AUTO_INCREMENT
• Changement du nom d’une colonne
ALTER TABLE nom_table
CHANGE ancien_nom nouveau_nom description_colonne;
ALTER TABLE Client
CHANGE NomClient PrenomClient VARCHAR(30) NOT NULL;

• Changement du type d’une colonne

ALTER TABLE nom_table
CHANGE ancien_nom nouveau_nom nouvelle_description;

ALTER TABLE nom_table

MODIFY nom_colonne nouvelle_description;

53
 Exemples

• La table d’origine

CREATE TABLE Client(

NumClient SMALLINT UNSIGNED NOT NULL
AUTO_INCREMENT,
NomClient VARCHAR(30) NOT NULL,
date_naissance DATETIME NOT NULL,
ville VARCHAR(30) NOT NULL DEFAULT ‘Oujda’,
PRIMARY KEY (id)
) ENGINE=INNODB;

54
 Exemples

ALTER TABLE Client

CHANGE NomClient PrenomClient VARCHAR(40);
• Changement du type + changement du nom

ALTER TABLE Client

CHANGE NomClient NomClient VARCHAR(30);
• Changement du type sans renommer
ALTER TABLE Client
MODIFY NumClient SMALLINT NOT NULL;
• Suppression de l'auto-incrémentation

ALTER TABLE Client

MODIFY NomClient VARCHAR(30) DEFAULT ‘Mohamed';
• Changement de la description (même type mais ajout d'une valeur par défaut)

55
LMD (Langage de
Manipulation des Données)
INSERT - DELETE - UPDATE

56
 Insertion des lignes dans une
table

• Par la requête INSERT INTO

• Par l’exécution d’un script SQL
• Par importation des données d’un fichier formaté

57
 INSERT INTO

• Avec précision des noms des colonnes

INSERT INTO Client (NumClient, NomClient,
date_naissance, ville) VALUES (1, ‘Ahmed Ali', ‘1991-
08-03 05:12:00‘, ‘Rabat’);
• Sans précision des noms de colonnes
INSERT INTO Client
VALUES (2, ‘Khalid Fathi', ‘1990-11-13 15:24:00‘, ‘Kénitra’);

• Insertion multiple
INSERT INTO Client (NumClient, NomClient, date_naissance,
ville)
VALUES (3, ‘Kacim Achkar', ‘1995-12-27 12:34:00‘, ‘Tanger’),
(4, ‘Karima Abbadi', ‘1995-12-27 12:34:00‘, ‘Meknès’),
(5, ‘Fatiha Mokhtar’, ‘1995-12-27 12:34:00‘,
‘Marrakech’);
58
 Syntaxe alternative de MySQL

INSERT INTO Client

SET NomClient=‘Hamza Chakir', Datetime=‘1994-02-03 09:30:00',
ville=‘Mohammadia’;

• Avantages
◦ Syntaxe plus lisible et plus facile à manipuler surtout pour un grand
nombre de colonnes
◦ En effet, on ne se rappelle pas facilement de l’ordre de déclaration des
colonnes pour le respecter lors de l’insertion
• Inconvénients
◦ Syntaxe propre à MySQL
◦ Ne permet pas l’insertion multiple

59
 Insertion par exécution de script
SQL

• Insérer les données dans la console peut devenir une tâche pénible lorsqu’il
s’agit d’écrire des requêtes longues ou beaucoup de requêtes à la fois
=> MySQL permet d’exécuter des scripts SQL
• Syntaxe:
SOURCE [Link]
Ou
\. [Link]

◦ Permet d’exécuter les commandes du script [Link]

◦ L’extension «.sql» n’est pas obligatoire
◦ Il faut donner le chemin complet vers le fichier script
◦ Sinon, MySQL le cherche dans le répertoire de connexion

60
 Insertion des données à partir
d’un fichier formaté

• Chargement des données depuis un fichier texte vers une base de

données MySQL sans l’intervention d’un langage de programmation.

• La commande SQL utilisée est: LOAD DATA INFILE

• Le fichier contenant les données doit respecter les règles suivantes :

• le séparateur des champs doit être fixe
• le séparateur des lignes doit être fixe

• Si ces séparateurs se retrouvent dans les valeurs contenues dans le fichier,

ils doivent être échappés par un caractère spécifique (par exemple le
caractère ‘\’)

61
 Fichier .csv

• Exemple de fichier formaté: le fichier .CSV

◦ Le caractère de séparation des champs est ‘;’
◦ Le caractère de séparation de lignes est le retour à la ligne (‘\n’)
=> c’est un fichier valide pour un transfert avec l’instruction LOAD DATA INFILE
• Fichier facilement produit et lu par un tableur de type Excel

62
 LOAD DATA INFILE

• Syntaxe:
LOAD DATA [LOCAL] INFILE 'nom_fichier'
INTO TABLE nom_table
[FIELDS
[TERMINATED BY ' ;']
[ENCLOSED BY '"']
[ESCAPED BY '\\' ] ]
[LINES
[STARTING BY '']
[TERMINATED BY '\r\n'] ]
[IGNORE nombre LINES]
[(nom_colonne,...)];

◦ LOCAL: spécifie que le fichier existe du côté client.

◦ Si le fichier est du côté serveur, il doit être stocké dans le répertoire de
la base de données
◦ S’il est du côté client, il faut spécifier le chemin du fichier
◦ FIELDS: se rapporte aux colonnes
◦ LINES: se rapporte aux lignes
63
 LOAD DATA INFILE

• Au moins une des 3 causes suivantes doit être spécifiée si la clause FIELDS est spécifiée:
◦ TERMINATED BY: définit le caractère séparateur des colonnes (; dans notre exemple)
◦ ENCLOSED BY: définit le caractère qui entoure la valeur de chaque colonne (vide par
défaut) (")
◦ ESCAPED BY: définit le caractère d’échappement pour les caractères spéciaux (par
défaut \ qu’il faut aussi échapper dans la clause)
• LINES
◦ STARTING BY: définit le caractère de début de la ligne (vide par défaut)
◦ TERMINATED BY: définit le caractère de fin de ligne (‘\n’ par défaut ou ‘\r\n’ pour les
fichier générés sous windows)
• IGNORE nombre LINES
◦ Permet d’ignorer nombre lignes
◦ Par exemple, ignorer la première ligne du fichier si elle contient les noms des colonnes
• On peut spécifier les noms des colonnes s’ils sont présents dans le fichier csv
◦ Les colonnes absentes doivent pouvoir être à NULL ou auto-incrémentées
64
 Suppression des lignes d’une
table

• Syntaxe:
DELETE FROM Client
WHERE NumClient = 1;

• Permet de supprimer le client qui a le numéro 1

DELETE FROM Client;

• Permet de supprimer tous les clients de la table

• Attention! La suppression des lignes est irreversible

65
 Modification des données

• Syntaxe
UPDATE nom_table
SET col1 = val1
[, col2 = val2, ...]
[WHERE ...];

• Exemple
UPDATE Client
SET NomClient = ‘Farouk’
WHERE NumClient = 5;

• Si la clause WHERE n’est pas spécifiée, la modification va porter sur toutes les lignes
de la table

66
Index
 Index

• Un index est une structure qui reprend la liste ordonnée des valeurs auxquelles il se
rapporte.
◦ Si un index est créé sur la colonne prénom de la table personne,
◦ MySQL stocke cet index sous forme d'une structure contenant les valeurs des
prénoms triées
◦ Ce qui permet d'accéder à chacune de ces valeurs de manière efficace et rapide

• Les index
◦ sont utilisés pour accélérer les requêtes (requêtes de recherche ou impliquant
plusieurs tables)
◦ sont indispensables à la création de clés primaires et étrangères
• Inconvénient
◦ Les index prennent de la place en mémoire
◦ Ils ralentissent les requêtes d'insertion, modification et suppression
=> Ne créer des index que lorsque c’est nécessaire 68
 Comment MySQL utilise les
index?

• MySQL utilise les index pour trouver des lignes de résultat avec une valeur
spécifique, très rapidement
• Sans index, MySQL doit lire successivement toutes les lignes, et à chaque fois,
faire les comparaisons nécessaires pour extraire un résultat pertinent
• Plus la table est grosse, plus c'est coûteux
• Si la table dispose d'un index pour les colonnes utilisées, MySQL peut alors trouver
rapidement les positions des lignes dans le fichier de données, sans parcourir toute la
table
• Si une table a 1000 lignes, l'opération sera alors 100 fois plus rapide qu'une lecture
séquentielle
• Il est à noter que si l’on doit lire la presque totalité des 1000 lignes, la lecture
séquentielle se révélera alors plus rapide

69
 Création / suppression d’index

• Lors de la création de la CREATE TABLE [IF NOT EXISTS] Nom_table

( colonne1 description_colonne1, [colonne2
table description_colonne2, ...,] [PRIMARY KEY
(colonne_clé_primaire)]
[INDEX [nom_index] (colonne1_index [, colonne2_index, ...]]
)
[ENGINE=moteur];
• Après création de la table

ALTER TABLE nom_table

ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]);
CREATE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);

• Suppression d’un index

ALTER TABLE
nom_table DROP
INDEX nom_index;

70
 Exemple

CREATE TABLE Personne ( Numero int(5),

Prenom VARCHAR(50),
Adresse VARCHAR(50),
Age int(3),
PRIMARY KEY (Numero),
INDEX indx_prenom (Prenom)
)
ENGINE=InnoDB;

CREATE INDEX indx_Prenom ON Personne (Prenom);

ALTER TABLE Personne
ADD INDEX indx_Prenom (Prenom);
ALTER TABLE Personne
DROP INDEX indx_Prenom;

71
 Utilisation des index

• Trouver rapidement des lignes qui satisfont une clause WHERE

• Lire des lignes dans d’autres tables lors des jointures

• Trouver les valeurs MAX() et MIN() pour une colonne indexée (opération
optimisée par le préprocesseur)

• Trier ou grouper des lignes dans une table

72
 Index sur plusieurs colonnes

• MySQL peut créer des index sur plusieurs colonnes => jusqu'à 15 colonnes
• Un index sur plusieurs colonnes peut être compris comme un tableau trié contenant
des valeurs créées par concaténation des valeurs des colonnes indexées
• La table client contient les informations des clients d’une société

• En supposant qu’on a besoin de faire beaucoup de recherches par nom, prénom et

initial du 2ème prénom => 2 solutions
• 3 index: un index par colonne
• 1 index sur les 3 colonnes: L'index contient les valeurs des trois colonnes et sera trié
par nom, ensuite par prénom, et enfin par initial (l'ordre des colonnes a donc de
l’importance)
73
Index sur plusieurs colonnes

74
 Index par la gauche

• Index sur le prénom / nom / nom, prénom / nom, prénom, init_2e_prenom

75
 Index sur des colonnes de type
alphanumérique
• Un index sur un CHAR ou un VARCHAR est décomposé caractère par caractère:

Carac1 Carac2 Carac3 Carac4 Carac5 Carac6 Carac7 Carac8 Carac9 Carac10
B o u l i a n
C a r a m o u

• MySQL permet de spécifier le nombre de caractères à considérer pour l’index sur

une colonne de type alphanumérique
• Très utile dans le cas de chaines très longues VARCHAR(100) ou VARCHAR(150)
CREATE TABLE Livre
( Titre_livre VARCHAR(150), …
INDEX indx_titre (Titre_livre25)
);

• MySQL exige de spécifier un nombre de caractères à prendre en compte

76
 Types d’index

• L’index UNIQUE
◦ Sur une colonne (ou plusieurs) permet de s'assurer de ne jamais insérer la même
valeur (ou combinaison de valeurs) deux fois dans la table
◦ Un index UNIQUE est considéré comme contrainte sur la table
◦ Surtout utilisé avec les colonnes représentant des clés secondaires
• L’index FULLTEXT
◦ Permet de faire des recherches de manière puissante et rapide sur un texte
◦ Utilisé sur les colonnes de type CHAR, VARCHAR et TEXT
◦ On ne peut plus utiliser les index par la gauche avec les index FULLTEXT
• L’index SPATIAL
◦ Utilisé dans des BD manipulant des données spatiales (point, ligne, polygone, etc.)

77
 Index automatiques

• Un index est créé automatiquement par MySQL dans les cas suivants:
◦ Sur une colonne définie clé primaire
◦ Sur une colonne définie clé étrangère
◦ Sur une colonne définie avec la contrainte UNIQUE

78
Clé primaire
 Clé primaire

• La clé primaire d'une table est une contrainte d'unicité, composée d'une ou
plusieurs colonnes, et qui permet d'identifier de manière unique chaque ligne de la
table

• Clé primaire
◦ Contrainte d'unicité: UNIQUE
◦ Composée d'une ou plusieurs colonnes: les clés primaires peuvent être
composites.
◦ Permet d'identifier chaque ligne de manière unique : une clé primaire ne peut pas
être NULL.

80
 Création / suppression de clé
primaire
• Lors de la création de la table
CREATE TABLE [IF NOT EXISTS] Nom_table
( colonne1 description_colonne1 PRIMARY
KEY, [colonne2 description_colonne2, ...,]
[PRIMARY KEY (colonne_clé_primaire)]
[CONSTRAINT [nom_contrainte]] PRIMARY KEY (colonne_pk1 [, colonne_pk2,
...])
) [ENGINE=moteur];

• Après création de la table

ALTER TABLE nom_table
ADD [CONSTRAINT [nom_contrainte]] PRIMARY KEY (colonne_pk1 [,
colonne_pk2, ...]);

• Suppression d’une clé

ALTER TABLE nom_table

DROP PRIMARY KEY

81
 Exemple

CREATE TABLE Personne

( Numero int(5)
PRIMARY KEY,
Prenom
VARCHAR(50),
Adresse VARCHAR(50),
Age int(3),
PRIMARY KEY (Numero),
CONSTRAINT Numero_pk PRIMARY KEY (Numero)
)
ENGINE=InnoDB;

ALTER TABLE Personne

ADD CONSTRAINT Numero_pk PRIMARY KEY (Numero);

ALTER TABLE Personne

DROP PRIMARY KEY

82
Clé étrangère
Clé étrangère

84
 Création de clé étrangère

Créer clé étrangère = spécifier 2 éléments

◦ la ou les colonnes sur laquelle (lesquelles) nous voulons créer la clé étrangère
◦ on utilise FOREIGN KEY ;
◦ la ou les colonnes référencée (s)
◦ on utilise REFERENCES.
• lors de la création de la table
CREATE TABLE [IF NOT EXISTS] Nom_table
( colonne1 description_colonne1, [colonne2
description_colonne2, ...,] [PRIMARY KEY
(colonne_clé_primaire)]
[CONSTRAINT [nom_contrainte]] FOREIGN KEY (colonne(s)_clé_étrangère)
REFERENCES table_référence (colonne(s)_référencées)]
)
[ENGINE=moteur];

85
 Création / suppression de clé
étrangère

• Après création de la table

ALTER TABLE nom_table

ADD CONSTRAINT [nom_contrainte]] FOREIGN KEY
(colonne(s)_clé_étrangère)
REFERENCES table_référence (colonne(s)_référencées)]

• Suppression d’une table

ALTER TABLE nom_table
DROP FOREIGN KEY nom_contrainte

86
 Exemple

CREATE TABLE Departement

( NumDep int(5) PRIMARY KEY,
NomDep VARCHAR(50),
Directeur int(5),
CONSTRAINT fk_Departement_Personne FOREIGN KEY
(Directeur) REFRENCES Personne (Numero)
)
ENGINE=InnoDB;

ALTER TABLE Departement

ADD CONSTRAINT fk_Departement_Personne FOREIGN KEY
(Directeur)
REFRENCES Personne (Numero);

ALTER TABLE Departement

DROP FOREIGN KEY fk_Departement_Personne;

87
 Options sur les clés étrangères

• Que se passe-t-il si on supprime le directeur d’un département de la table Personne?

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

• Les options possibles

ALTER TABLE nom_table
ADD [CONSTRAINT fk_col_ref] FOREIGN KEY (colonne)
REFERENCES table_ref(col_ref)
ON DELETE {
RESTRICT | NO ACTION | SET NULL | CASCADE};

• RESTRICT ou NO ACTION: suppression impossible de la valeur référencée

(comportement par défaut)
• SET NULL: Met à NULL les références avant de supprimer la valeur référencée
• CASCADE: Supprime les lignes des références avant de supprimer la valeur
référencée
88
 Options sur les clés étrangères

• Que se passe-t-il si on modifie le directeur d’un département de la table Personne?

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

• Les options possibles

ALTER TABLE nom_table
ADD [CONSTRAINT fk_col_ref] FOREIGN KEY (colonne) REFERENCES
table_ref(col_ref)
ON UPDATE {RESTRICT | NO ACTION | SET NULL | CASCADE};

• RESTRICT et NO ACTION : empêche la modification si elle casse la contrainte

(comportement par défaut)
• SET NULL : met NULL partout où la valeur modifiée était référencée
• CASCADE : modifie également la valeur là où elle est référencée

89
 Violation des contraintes
d’unicité

• Que se passe-t-il lors de l’insertion/modification des données dans une table qui
causent la violation d’une contrainte d’unicité (clé primaire ou secondaire ou index
UNIQUE)?
◦ Une erreur est déclenchée

• Trois possibilités pour changer ce comportement par défaut:

◦ Laisser l’insertion/modification échouer mais sans déclencher d’erreur
◦ Remplacer la ligne qui existe par la nouvelle ligne dans le cas d’une insertion
◦ Modifier la ligne qui existe au lieu d’insérer une nouvelle dans le cas d’une
insertion

90
 Ignorer l’insertion

• L’option IGNORE de la commande d’insertion

◦ Permet d’ignorer l’insertion lorsqu’une contrainte d’unicité n’est pas
respectée
• Exemple

91
 Ignorer la modification

• L’option IGNORE de la commande UPDATE permet de:

◦ Ignorer la modification lorsqu’une contrainte d’unicité n’est pas respectée
◦ Ne pas déclencher l’erreur

92
 Ignorer l’insertion à travers des
fichiers

• L’option IGNORE appliquée à LOAD DATA INFILE

◦ Permet d’ignorer l’insertion lorsqu’une ligne du fichier ne respecte pas
la contrainte d’unicité

LOAD DATA [LOCAL] INFILE 'nom_fichier' IGNORE

INTO TABLE
nom_table [FIELDS
[TERMINATED BY ' ;']
[ENCLOSED BY '"']
[ESCAPED BY '\\' ]
] [LINES
[STARTING BY '']
[TERMINATED BY '\r\n'] ]
[IGNORE nombre
LINES]
[(nom_colonne,...)];

93
 Remplacer la ligne existante

• Dans le cas de l’insertion d’une ligne qui ne respecte pas une contrainte
d’unicité,
• REPLACE INTO permet de remplacer la ligne existante par la nouvelle ligne

94
 Remplacer une ligne

Plusieurs lignes affectées

95
 Remplacement de lignes à travers
un fichier

• L’option REPLACE est disponible aussi avec LOAD DATA INFILE

LOAD DATA [LOCAL] INFILE 'nom_fichier' REPLACE
INTO TABLE
nom_table [FIELDS
[TERMINATED BY
' ;'] [ENCLOSED
BY '"']
[ESCAPED BY
'\\' ] ] [LINES
[STARTING BY '']
[TERMINATED BY '\r\n'] ]
[IGNORE nombre
LINES]
[(nom_colonne,...)];
• Remarque: REPLACE et IGNORE ne peuvent pas être utilisées en même temps

96
 Modifier la ligne existante

• REPLACE supprime l’ancienne ligne et insert une nouvelle ligne

• La clause ON DUPLICATE KEY UPDATE de INSERT INTO permet de modifier la ligne
existante sans suppression
• Syntaxe:
INSERT INTO nom_table [(colonne1, colonne2,
colonne3)] VALUES (valeur1, valeur2, valeur3)
ON DUPLICATE KEY UPDATE colonne2 = valeur2 [, colonne3 = valeur3];

97
 Modifier une ligne existante

• La ligne a été bien modifiée et non pas remplacée (c’est juste l’adresse qui a été
modifiée)

98
 Plusieurs contraintes d’unicité

• REPLACE supprime le nombre de lignes nécessaires avant de faire l’insertion

• ON DUPLICATE KEY UPDATE
• Une des lignes sera modifié et on ne peut pas prédire laquelle
• ◦ => éviter d’utiliser cette clause lorsque plusieurs contraintes d’unicité
portent sur une table et peuvent être violées au moment de l’insertion

99
Utilisateurs et privilèges
 BDs par défaut

• information_schema
◦ Stocke les informations sur toutes les BDs
◦ tables, colonnes, types des colonnes, procédures stockées et leurs caractéristiques
• performance_schema
◦ stocke des informations sur les actions effectuées sur le serveur
◦ temps d'exécution, temps d'attente dus aux verrous, etc.
• test
◦ BD de test créée automatiquement créée
◦ Elle ne contient rien
• Mysql
◦ contient des informations sur le serveur
◦ Entre autres, les utilisateurs et leurs privilèges.

101
 Utilisateurs et privilèges
• Chaque utilisateur possède un ensemble de privilèges
◦ privilège de sélection des données, de modification, de création des objets, etc.
• Les privilèges peuvent exister à plusieurs niveaux : serveur, base de données, tables,
colonnes, procédures, etc.
• Ces informations sont stockées dans la BD MySQL dans plusieurs tables:
◦ Table user: stocke les utilisateurs et leurs privilèges globaux (valables au niveau
du serveur sur toutes les BDs)
◦ Table db : stocke les privilèges au niveau des BDs
◦ Table tables_priv : stocke les privilèges au niveau des tables
◦ Table columns_priv : stocke les privilèges au niveau des colonnes
◦ Table proc_priv : stocke les privilèges au niveau des routines (procédures et
fonctions stockées)

102
 Manipulation directe des
privilèges

• Il est possible d’ajouter, modifier ou supprimer des utilisateurs directement à travers

les tables de la BD mysql:
◦ [Link]: pour les utilisateurs
◦ [Link], mysql.tables_priv, mysql.columns_priv et mysql.procs_priv pour les
autres privilèges

• Des commandes dédiées à la gestion des utilisateurs et privilèges exstent

• Il est préférable pour minimiser le risque d’erreur et pour ne pas apprendre la

structure de ces table, d’utiliser les commandes dédiées

103
 Création / suppression
d’utilisateurs

-- Création
CREATE USER 'login'@'hote' [IDENTIFIED BY 'mot_de_passe'];
-- Suppression
DROP USER 'login'@'hote';
• Un utilisateur est défini par deux éléments :
◦ son login
◦ il n’est pas obligatoire d’entourer le login de guillemets sauf dans le cas où il
contient des caractères spéciaux comme – ou @
◦ l'hôte à partir duquel il se connecte à la BD
◦ Prut être le nom de l’hôte ou son adresse IP
◦ Si l’hôte n’est pas précisé, l’utilisateur est considéré comme ayant accès depuis
tous les hôtes
CREATE USER ‘fatiha'@'localhost' IDENTIFIED BY ‘123';
CREATE USER ‘ali'@'[Link]' IDENTIFIED BY ‘456';
CREATE USER ‘hamza'@'[Link]' IDENTIFIED BY ‘789';

104
 Modification des utilisateurs

• Permettre à un utilisateur de se connecter à partir de plusieurs hôtes

différents (sans avoir à créer un utilisateur par hôte)
◦ Utiliser le joker: %

CREATE USER ‘Adam'@'194.28.12.%' IDENTIFIED BY ‘101’;

• Renommer un utilisateur

RENAME USER ‘fatiha'@'localhost' TO ‘hafida'@'localhost';

105
 Mot de passe

• La clause IDENTIFIED BY n’est pas obligatoire

=> il est possible d’avoir des utilisateurs qui peuvent se connecter au serveur sans
mot de passe (ce qui est déconseillé)
• C’est la valeur hachée du mot de passe qui est stockées dans la table
[Link]
◦ La fonction PASSWORD() permet de hacher le mot de passe
• Modifier le mot de passe

SET PASSWORD FOR ‘ali'@'194.28.12.%' = PASSWORD('basketball');

106
 Les privilèges

• Un utilisateur sans aucun privilège ne peut que se connecter.

◦ Il n'a pas accès aux données, ne peut créer ni utiliser aucun objet
(base/table/procédure/autre)
• Privilèges de base
◦ Les privilèges SELECT, INSERT, UPDATE et DELETE
◦ permettent aux utilisateurs d'utiliser ces mêmes commandes.
• Privilèges particuliers
◦ ALL, USAGE, GRANT OPTION

107
 Privilèges des tables/vues/BDs

Privilège Action autorisée

CREATE TABLE Création de tables
CREATE TEMPORARY TABLE Création de tables temporaires
CREATE VIEW Création de vues (suppose avoir le privilège SELECT
sur les colonnes sélectionnées par la vue)

ALTER Modification de tables (avec ALTER TABLE)

DROP Suppression de tables, vues et BDs

108
 Autres privilèges

Privilège Action autorisée

CREATE ROUTINE Création de procédures et de fonctions stockées
ALTER ROUTINE Modification et suppression de procédures et
fonctions stockées
EXECUTE Exécution de procédures et fonctions stockées
INDEX Création et suppression d’index
TRIGGER Création et suppression de triggers
LOCK TABLES Verouillage de tables sur lesquelles on a le privilège SELECT
CREATE USER Gestion des utilisateurs (CREATE USER / DROP USER /
RENAME USER / SET PASSWORD)

• Liste de tous les privilèges dans la documentation officielle de MySQL:

◦ [Link]

109
 Niveaux d’application des
privilèges
Niveau Application du privilège
*.* Privilège global: s’applique à toutes les BDs et à tous
les objets. Un privilège de ce niveau sera stocké dans
la table [Link]
* Si aucune BD n’a été préalablement sélectionnée (avec USE
nom_BD), c’est l’équivalent de *.* (privilège stocké dans
[Link])
Sinon, le privilège s’applique à tous les objets de la BD en cours
d’utilisation (privilège stocké dans la table [Link])
Nom_BD.* Privilège de BD qui s’applique à tous les objets de la BD
nom_BD Il est stocké dans [Link]
Nom_BD.nom_table Privilège de table (stocké dans mysql.tables_priv)
Nom_table Privilège de table qui s’applique à la table nom_table de la BD dans
laquelle on se trouve, sélectionnée au préalable avec USE nom_BD
Stocké dans la table mysql.tables_priv
Nom_BD.nom_routine S’applique à la procédure ou fonction stockée:
nom_BD.nom_routine Privilège stocké dans mysql.procs_priv

Les privilèges peuvent être restreints aux colonnes => utilisation de la commande
GRANT
110
 Attribution de privilèges

GRANT privilege [(liste_colonnes)] [, privilege [(liste_colonnes)],

...] ON [type_objet] niveau_privilege
TO utilisateur [IDENTIFIED BY mot_de_passe];

◦ privilege : le privilège à accorder à l'utilisateur (SELECT, CREATE VIEW, EXECUTE, etc.) ;

◦ (liste_colonnes) : facultatif - liste des colonnes auxquelles le privilège s'applique ;
◦ niveau_privilege : niveau auquel le privilège s'applique (*.*, nom_bdd.nom_table,
etc.) ;
◦ type_objet : en cas de noms ambigus, il est possible de préciser à quoi se rapporte le
niveau : TABLE ou PROCEDURE.
◦ Pour accorder plusieurs privilèges en une fois il faut séparer les privilèges par une
virgule.
◦ Si l'utilisateur auquel on accorde les privilèges n'existe pas, il sera créé => Ne pas
oublier la clause IDENTIFIED BY.
◦ Si l’utilisateur existe et qu’on ajoute la clause IDENTIFIED BY, son mot de passe sera
modifié.

111
 Exemples

GRANT
SELECT,
UPDATE(nom,adrese),
DELETE,
INSERT
ON [Link]
TO ‘fatiha'@'localhost' IDENTIFIED BY ‘155';
GRANT SELECT
ON TABLE [Link] -- On précise que c'est une table (facultatif)
TO 'john'@'localhost' IDENTIFIED BY 'change2012';

GRANT CREATE ROUTINE, EXECUTE

ON departements.* TO
‘ali'@'localhost';

112
 Révocation de privilèges

REVOKE privilege [, privilege, ...]

ON niveau_privilege
FROM utilisateur;

• Exemple
REVOKE DELETE
ON [Link]
FROM ‘fatiha'@'localhost';

113
 Le privilège ALL

• ALL
◦ représente tous les privilèges
◦ Il faut préciser le niveau auquel tous les droits sont accordés (une table, une base
de données, etc.)
◦ Un privilège fait exception : GRANT OPTION n'est pas compris dans les privilèges
représentés par ALL

GRANT ALL
ON [Link] TO
‘hamza'@'localhost' IDENTIFIED BY ‘mdp';

114
 Le privilège USAGE

• USAGE
◦ À l'inverse de ALL, le privilège USAGE signifie "aucun privilège"

◦ USAGE permet de modifier les caractéristiques d'un compte avec la commande

GRANT, sans modifier les privilèges du compte
◦ USAGE est toujours utilisé comme un privilège global (donc
ON *.*). Exemple de modification du mot de passe

GRANT USAGE ON *.*

TO ‘ali'@'localhost' IDENTIFIED BY 'test2015';

115
 Le privilège GRANT OPTION

• GRANT OPTION
◦ Un utilisateur ayant ce privilège est autorisé à utiliser la commande GRANT pour
accorder des privilèges à d'autres utilisateurs.
◦ Ce privilège n'est pas compris dans le privilège ALL.
◦ Un utilisateur ne peut accorder que les privilèges qu'il possède lui-même.
• GRANT OPTION est accordé de deux manières :
◦ comme un privilège normal, après le mot GRANT ;
◦ à la fin de la commande GRANT, avec la clause WITH GRANT OPTION.

116
 Le privilège WITH GRANT
OPTION

GRANT SELECT, UPDATE, INSERT, DELETE, GRANT OPTION

ON departements.*
TO ‘khalid'@'localhost' IDENTIFIED BY ‘khalid'; OU
GRANT SELECT, UPDATE, INSERT, DELETE
ON elevage.*
TO ‘khalid'@'localhost' IDENTIFIED BY ‘khalid' WITH GRANT OPTION;

• Remarque
◦ Le privilège ALL doit s’utiliser tout seul
◦ L’écriture suivante n’est pas acceptée: GRANTALL, GRANT OPTION ….
◦ Il faut, dans ce cas, utiliser WITH GRANT OPTION

117
 Option de limitation des
ressources

• MAX_QUERIES_PER_HOUR
◦ Limiter le nombre de requêtes par heure
◦ Limitation de toutes les commandes exécutées par l’utilisateur
• MAX_UPDATES_PER_HOUR
◦ Limiter le nombre de modifications par heure
◦ Limitation des commandes entrainant la modification d’une table ou d’une BD
• MAX_CONNECTIONS_PER_HOUR
◦ Limiter le nombre de connexions au serveur par heure
• Utiliser GRANT avec la clause:
◦ WITH MAX_QUERIES_PER_HOUR nb1 |
MAX_UPDATES_PER_HOUR nb2 |
MAX_CONNECTIONS_PER_HOUR nb3

118
 Option de limitation des
ressources

• Création d’un compte ayant tous les droits et avec des ressources limitées

GRANT ALL ON departements.*

TO ‘said'@'localhost' IDENTIFIED BY 'limitation'
WITH MAX_QUERIES_PER_HOUR 50
MAX_CONNECTIONS_PER_HOUR 5;

• Limiter les ressources d’un utilisateur existant sans modifier ses privilèges
GRANT USAGE ON *.*
TO ‘hamza'@'localhost'
WITH MAX_UPDATES_PER_HOUR 15;
• Supprimer une limitation des ressources
GRANT USAGE ON *.*
TO ‘hamza'@'localhost'
WITH MAX_UPDATES_PER_HOUR 0;

119