Descargue el libro avec MySQL bases de données Administrez vos ahora

413 Pages·2014·7.58 MB·French

Administrez vos
bases de données
avec MySQL
Par Chantal Gribaumont (Taguan)
www.openclassrooms.com
Licence Creative Commons 6 2.0
Dernière mise à jour le 5/10/2012
2/414
Sommaire
Sommaire ........................................................................................................................................... 2
Partager .............................................................................................................................................. 7
Administrez vos bases de données avec MySQL .............................................................................. 9
Quelques exemples d'applications .................................................................................................................................... 9
Points abordés dans ce tutoriel ......................................................................................................................................... 9
Partie 1 : MySQL et les bases du langage SQL ................................................................................ 11
Introduction ...................................................................................................................................................................... 11
Concepts de base ...................................................................................................................................................................................................... 11
Base de données ....................................................................................................................................................................................................... 11
SGBD ........................................................................................................................................................................................................................ 11
SGBDR ...................................................................................................................................................................................................................... 11
Le langage SQL ........................................................................................................................................................................................................ 12
Présentation succincte de MySQL... ......................................................................................................................................................................... 13
Un peu d'histoire ....................................................................................................................................................................................................... 13
Mise en garde ............................................................................................................................................................................................................ 13
... et de ses concurrents ............................................................................................................................................................................................ 13
Oracle database ........................................................................................................................................................................................................ 13
PostgreSQL ............................................................................................................................................................................................................... 13
MS Access ................................................................................................................................................................................................................. 14
SQLite ....................................................................................................................................................................................................................... 14
Organisation d'une base de données ........................................................................................................................................................................ 14
Installation de MySQL ..................................................................................................................................................... 15
Avant-propos ............................................................................................................................................................................................................. 16
Ligne de commande .................................................................................................................................................................................................. 16
Interface graphique ................................................................................................................................................................................................... 16
Pourquoi utiliser la ligne de commande ? ................................................................................................................................................................. 17
Installation du logiciel ................................................................................................................................................................................................ 17
Windows .................................................................................................................................................................................................................... 17
Mac OS ..................................................................................................................................................................................................................... 18
Linux .......................................................................................................................................................................................................................... 19
Connexion à MySQL ................................................................................................................................................................................................. 20
Connexion au client ................................................................................................................................................................................................... 20
Déconnexion ............................................................................................................................................................................................................. 22
Syntaxe SQL et premières commandes .................................................................................................................................................................... 22
"Hello World !" ........................................................................................................................................................................................................... 22
Syntaxe ..................................................................................................................................................................................................................... 22
Un peu de math ......................................................................................................................................................................................................... 24
Utilisateur .................................................................................................................................................................................................................. 24
Les types de données ..................................................................................................................................................... 26
Types numériques ..................................................................................................................................................................................................... 26
Nombres entiers ........................................................................................................................................................................................................ 26
Nombres décimaux ................................................................................................................................................................................................... 27
Types alphanumériques ............................................................................................................................................................................................ 27
Chaînes de type texte ............................................................................................................................................................................................... 27
Chaînes de type binaire ............................................................................................................................................................................................ 28
SET et ENUM ............................................................................................................................................................................................................ 29
Types temporels ........................................................................................................................................................................................................ 31
DATE, TIME et DATETIME ........................................................................................................................................................................................ 31
YEAR ......................................................................................................................................................................................................................... 33
TIMESTAMP .............................................................................................................................................................................................................. 33
La date par défaut ..................................................................................................................................................................................................... 33
Création d'une base de données .................................................................................................................................... 33
Avant-propos : conseils et conventions ..................................................................................................................................................................... 34
Conseils .................................................................................................................................................................................................................... 34
Conventions .............................................................................................................................................................................................................. 34
Mise en situation ....................................................................................................................................................................................................... 35
Création et suppression d'une base de données ...................................................................................................................................................... 35
Création ..................................................................................................................................................................................................................... 35
Suppression .............................................................................................................................................................................................................. 35
Utilisation d'une base de données ............................................................................................................................................................................ 36
Création de tables ........................................................................................................................................................... 37
Définition des colonnes ............................................................................................................................................................................................. 38
Type de colonne ........................................................................................................................................................................................................ 38
NULL or NOT NULL ? ................................................................................................................................................................................................ 38
Récapitulatif .............................................................................................................................................................................................................. 38
Introduction aux clés primaires ................................................................................................................................................................................. 39
Identité ....................................................................................................................................................................................................................... 39
Clé primaire ............................................................................................................................................................................................................... 39
Auto-incrémentation .................................................................................................................................................................................................. 39
Les moteurs de tables ............................................................................................................................................................................................... 40
Préciser un moteur lors de la création de la table .................................................................................................................................................... 40
Syntaxe de CREATE TABLE ..................................................................................................................................................................................... 40
Syntaxe ..................................................................................................................................................................................................................... 40
Application : création de Animal ................................................................................................................................................................................ 42
www.openclassrooms.com
Sommaire 3/414
Vérifications ............................................................................................................................................................................................................... 42
Suppression d'une table ............................................................................................................................................................................................ 42
Modification d'une table ................................................................................................................................................... 42
Syntaxe de la requête ............................................................................................................................................................................................... 43
Ajout et suppression d'une colonne .......................................................................................................................................................................... 43
Ajout .......................................................................................................................................................................................................................... 43
Suppression .............................................................................................................................................................................................................. 44
Modification de colonne ............................................................................................................................................................................................. 44
Changement du nom de la colonne .......................................................................................................................................................................... 44
Changement du type de données ............................................................................................................................................................................. 44
Insertion de données ....................................................................................................................................................... 45
Syntaxe de INSERT .................................................................................................................................................................................................. 46
Insertion sans préciser les colonnes ......................................................................................................................................................................... 46
Insertion en précisant les colonnes ........................................................................................................................................................................... 47
Insertion multiple ....................................................................................................................................................................................................... 47
Syntaxe alternative de MySQL .................................................................................................................................................................................. 47
Utilisation de fichiers externes .................................................................................................................................................................................. 48
Exécuter des commandes SQL à partir d'un fichier .................................................................................................................................................. 48
Insérer des données à partir d'un fichier formaté ...................................................................................................................................................... 49
Remplissage de la base ............................................................................................................................................................................................ 50
Exécution de commandes SQL ................................................................................................................................................................................. 50
LOAD DATA INFILE ................................................................................................................................................................................................... 51
Sélection de données ...................................................................................................................................................... 52
Syntaxe de SELECT ................................................................................................................................................................................................. 53
Sélectionner toutes les colonnes .............................................................................................................................................................................. 53
La clause WHERE .................................................................................................................................................................................................... 54
Les opérateurs de comparaison ................................................................................................................................................................................ 54
Combinaisons de critères .......................................................................................................................................................................................... 54
Sélection complexe ................................................................................................................................................................................................... 56
Le cas de NULL ......................................................................................................................................................................................................... 57
Tri des données ......................................................................................................................................................................................................... 58
Tri ascendant ou descendant .................................................................................................................................................................................... 58
Trier sur plusieurs colonnes ...................................................................................................................................................................................... 59
Éliminer les doublons ................................................................................................................................................................................................ 59
Restreindre les résultats ........................................................................................................................................................................................... 59
Syntaxe ..................................................................................................................................................................................................................... 59
Syntaxe alternative .................................................................................................................................................................................................... 61
Élargir les possibilités de la clause WHERE ................................................................................................................... 61
Recherche approximative ......................................................................................................................................................................................... 62
Sensibilité à la casse ................................................................................................................................................................................................. 63
Recherche dans les numériques ............................................................................................................................................................................... 63
Recherche dans un intervalle .................................................................................................................................................................................... 64
Set de critères ........................................................................................................................................................................................................... 64
Suppression et modification de données ........................................................................................................................ 65
Sauvegarde d'une base de données ......................................................................................................................................................................... 66
Suppression .............................................................................................................................................................................................................. 67
Modification ............................................................................................................................................................................................................... 67
Partie 2 : Index, jointures et sous-requêtes ....................................................................................... 68
Index ................................................................................................................................................................................ 69
Etat actuelle de la base de données ......................................................................................................................................................................... 69
Qu'est-ce qu'un index ? ............................................................................................................................................................................................. 70
Intérêt des index ........................................................................................................................................................................................................ 72
Désavantages ........................................................................................................................................................................................................... 72
Index sur plusieurs colonnes ..................................................................................................................................................................................... 72
Index sur des colonnes de type alphanumérique ...................................................................................................................................................... 74
Les différents types d'index ....................................................................................................................................................................................... 75
Index UNIQUE ........................................................................................................................................................................................................... 75
Index FULLTEXT ....................................................................................................................................................................................................... 76
Création et suppression des index ............................................................................................................................................................................ 76
Ajout des index lors de la création de la table ........................................................................................................................................................... 76
Ajout des index après création de la table ................................................................................................................................................................ 78
Complément pour la création d'un index UNIQUE - le cas des contraintes .............................................................................................................. 79
Suppression d'un index ............................................................................................................................................................................................. 79
Recherches avec FULLTEXT .................................................................................................................................................................................... 80
Comment fonctionne la recherche FULLTEXT ? ....................................................................................................................................................... 80
Les types de recherche ............................................................................................................................................................................................. 81
Clés primaires et étrangères ........................................................................................................................................... 89
Clés primaires, le retour ............................................................................................................................................................................................ 89
Choix de la clé primaire ............................................................................................................................................................................................. 89
Création d'une clé primaire ....................................................................................................................................................................................... 90
Suppression de la clé primaire .................................................................................................................................................................................. 91
Clés étrangères ......................................................................................................................................................................................................... 91
Création ..................................................................................................................................................................................................................... 92
Suppression d'une clé étrangère ............................................................................................................................................................................... 93
Modification de notre base ........................................................................................................................................................................................ 93
La table Espece ........................................................................................................................................................................................................ 94
La table Animal .......................................................................................................................................................................................................... 95
Jointures .......................................................................................................................................................................... 97
Principe des jointures et notion d'alias ...................................................................................................................................................................... 98
Principe des jointures ................................................................................................................................................................................................ 98
Notion d'alias ............................................................................................................................................................................................................. 99
www.openclassrooms.com
Sommaire 4/414
Jointure interne ........................................................................................................................................................................................................ 100
Syntaxe ................................................................................................................................................................................................................... 101
Pourquoi "interne" ? ................................................................................................................................................................................................ 103
Jointure externe ....................................................................................................................................................................................................... 104
Jointures par la gauche ........................................................................................................................................................................................... 104
Jointures par la droite .............................................................................................................................................................................................. 105
Syntaxes alternatives .............................................................................................................................................................................................. 106
Jointures avec USING ............................................................................................................................................................................................. 106
Jointures naturelles ................................................................................................................................................................................................. 106
Jointures sans JOIN ................................................................................................................................................................................................ 107
Exemples d'application et exercices ....................................................................................................................................................................... 108
A/ Commençons par des choses faciles ................................................................................................................................................................. 108
B/ Compliquons un peu les choses ......................................................................................................................................................................... 109
C/ Et maintenant, le test ultime ! .............................................................................................................................................................................. 110
Sous-requêtes ............................................................................................................................................................... 112
Sous-requêtes dans le FROM ................................................................................................................................................................................. 113
Les règles à respecter ............................................................................................................................................................................................. 114
Sous-requêtes dans les conditions .......................................................................................................................................................................... 115
Comparaisons ......................................................................................................................................................................................................... 115
Conditions avec IN et NOT IN ................................................................................................................................................................................. 118
Conditions avec ANY, SOME et ALL ........................................................................................................................................................................ 119
Sous-requêtes corrélées ......................................................................................................................................................................................... 120
Jointures et sous-requêtes : modification de données ................................................................................................. 122
Insertion ................................................................................................................................................................................................................... 123
Sous-requête pour l'insertion .................................................................................................................................................................................. 123
Modification ............................................................................................................................................................................................................. 125
Utilisation des sous-requêtes .................................................................................................................................................................................. 125
Modification avec jointure ........................................................................................................................................................................................ 127
Suppression ............................................................................................................................................................................................................ 127
Utilisation des sous-requêtes .................................................................................................................................................................................. 127
Suppression avec jointure ....................................................................................................................................................................................... 128
Union de plusieurs requêtes ......................................................................................................................................... 128
Syntaxe ................................................................................................................................................................................................................... 129
Les règles ................................................................................................................................................................................................................ 129
UNION ALL .............................................................................................................................................................................................................. 131
LIMIT et ORDER BY ................................................................................................................................................................................................ 132
LIMIT ....................................................................................................................................................................................................................... 132
ORDER BY .............................................................................................................................................................................................................. 134
Options des clés étrangères ......................................................................................................................................... 135
Option sur suppression des clés étrangères ........................................................................................................................................................... 136
Petits rappels .......................................................................................................................................................................................................... 136
Suppression d'une référence .................................................................................................................................................................................. 136
Option sur modification des clés étrangères ........................................................................................................................................................... 138
Utilisation de ces options dans notre base .............................................................................................................................................................. 139
Modifications ........................................................................................................................................................................................................... 139
Suppressions .......................................................................................................................................................................................................... 140
Les requêtes ............................................................................................................................................................................................................ 140
Violation de contrainte d'unicité ..................................................................................................................................... 141
Ignorer les erreurs ................................................................................................................................................................................................... 142
Insertion ................................................................................................................................................................................................................... 142
Modification ............................................................................................................................................................................................................. 142
LOAD DATA INFILE ................................................................................................................................................................................................. 143
Remplacer l'ancienne ligne ..................................................................................................................................................................................... 143
Remplacement de plusieurs lignes ......................................................................................................................................................................... 145
LOAD DATA INFILE ................................................................................................................................................................................................. 145
Modifier l'ancienne ligne .......................................................................................................................................................................................... 146
Syntaxe ................................................................................................................................................................................................................... 146
Attention : plusieurs contraintes d'unicité sur la même table .................................................................................................................................. 147
Partie 3 : Fonctions : nombres, chaînes et agrégats ....................................................................... 147
Rappels et introduction ................................................................................................................................................. 148
Etat actuel de la base de données .......................................................................................................................................................................... 148
Rappels et manipulation simple de nombres .......................................................................................................................................................... 150
Rappels ................................................................................................................................................................................................................... 150
Combiner les données avec des opérations mathématiques ................................................................................................................................. 151
Définition d'une fonction .......................................................................................................................................................................................... 153
Fonctions scalaires vs fonctions d'agrégation ......................................................................................................................................................... 155
Quelques fonctions générales ................................................................................................................................................................................. 155
Informations sur l'environnement actuel .................................................................................................................................................................. 156
Informations sur la dernière requête ....................................................................................................................................................................... 156
Convertir le type de données .................................................................................................................................................................................. 158
Fonctions scalaires ....................................................................................................................................................... 160
Manipulation de nombres ........................................................................................................................................................................................ 160
Arrondis ................................................................................................................................................................................................................... 160
Exposants et racines ............................................................................................................................................................................................... 161
Hasard ..................................................................................................................................................................................................................... 162
Divers ...................................................................................................................................................................................................................... 163
Manipulation de chaînes de caractères ................................................................................................................................................................... 163
Longueur et comparaison ....................................................................................................................................................................................... 163
Retrait et ajout de caractères .................................................................................................................................................................................. 164
Recherche et remplacement ................................................................................................................................................................................... 166
Concaténation ......................................................................................................................................................................................................... 168
www.openclassrooms.com
Sommaire 5/414
FIELD(), une fonction bien utile pour le tri ............................................................................................................................................................... 168
Code ASCII .............................................................................................................................................................................................................. 169
Exemples d'application et exercices ....................................................................................................................................................................... 169
On commence par du facile .................................................................................................................................................................................... 169
Puis on corse un peu .............................................................................................................................................................................................. 170
Fonctions d'agrégation .................................................................................................................................................. 172
Fonctions statistiques .............................................................................................................................................................................................. 172
Nombre de lignes .................................................................................................................................................................................................... 172
Minimum et maximum ............................................................................................................................................................................................. 173
Somme et moyenne ................................................................................................................................................................................................ 173
Concaténation ......................................................................................................................................................................................................... 174
Principe ................................................................................................................................................................................................................... 174
Syntaxe ................................................................................................................................................................................................................... 174
Exemples ................................................................................................................................................................................................................ 175
Regroupement ............................................................................................................................................................... 175
Regroupement sur un critère ................................................................................................................................................................................... 176
Voir d'autres colonnes ............................................................................................................................................................................................. 177
Colonnes sélectionnées .......................................................................................................................................................................................... 177
Tri des données ....................................................................................................................................................................................................... 179
Et les autres espèces ? ........................................................................................................................................................................................... 180
Regroupement sur plusieurs critères ...................................................................................................................................................................... 181
Super-agrégats ........................................................................................................................................................................................................ 182
Conditions sur les fonctions d'agrégation ................................................................................................................................................................ 185
Optimisation ............................................................................................................................................................................................................ 186
Exercices sur les agrégats ............................................................................................................................................ 188
Du simple… ............................................................................................................................................................................................................. 188
1. Combien de races avons-nous dans la table Race ? .......................................................................................................................................... 188
2. De combien de chiens connaissons-nous le père ? ............................................................................................................................................ 188
3. Quelle est la date de naissance de notre plus jeune femelle ? ........................................................................................................................... 188
4. En moyenne, quel est le prix d'un chien ou d'un chat de race, par espèce, et en général ? .............................................................................. 188
5. Combien avons-nous de perroquets mâles et femelles, et quels sont leurs noms (en une seule requête bien sûr) ? ....................................... 189
…Vers le complexe ................................................................................................................................................................................................. 189
1. Quelles sont les races dont nous ne possédons aucun individu ? ...................................................................................................................... 189
2. Quelles sont les espèces (triées par ordre alphabétique du nom latin) dont nous possédons moins de cinq mâles ? ...................................... 189
3. Combien de mâles et de femelles de chaque race avons-nous, avec un compte total intermédiaire pour les races (mâles et femelles confond 1u9e0s) et pour les espèces ? Afficher le nom de la race, et le nom courant de l'espèce.
4. Quel serait le coût, par espèce et au total, de l'adoption de Parlotte, Spoutnik, Caribou, Cartouche, Cali, Canaille, Yoda, Zambo et Lulla ? ... 190
Partie 4 : Fonctions : manipuler les dates ....................................................................................... 191
Obtenir la date/l'heure actuelle ..................................................................................................................................... 192
Etat actuel de la base de données .......................................................................................................................................................................... 192
Rappels ................................................................................................................................................................................................................... 194
Date ......................................................................................................................................................................................................................... 195
Heure ....................................................................................................................................................................................................................... 195
Date et heure ........................................................................................................................................................................................................... 195
Timestamp ............................................................................................................................................................................................................... 195
Année ...................................................................................................................................................................................................................... 195
Date actuelle ........................................................................................................................................................................................................... 195
Heure actuelle ......................................................................................................................................................................................................... 196
Date et heure actuelles ........................................................................................................................................................................................... 196
Les fonctions ........................................................................................................................................................................................................... 196
Qui peut le plus, peut le moins ................................................................................................................................................................................ 196
Timestamp Unix ...................................................................................................................................................................................................... 197
Formater une donnée temporelle .................................................................................................................................. 198
Extraire une information précise ............................................................................................................................................................................. 198
Informations sur la date ........................................................................................................................................................................................... 198
Informations sur l'heure ........................................................................................................................................................................................... 200
Formater une date facilement ................................................................................................................................................................................. 201
Format ..................................................................................................................................................................................................................... 201
Exemples ................................................................................................................................................................................................................ 202
Fonction supplémentaire pour l'heure ..................................................................................................................................................................... 203
Formats standards .................................................................................................................................................................................................. 204
Créer une date à partir d'une chaîne de caractères ................................................................................................................................................ 204
Calculs sur les données temporelles ............................................................................................................................ 205
Différence entre deux dates/heures ........................................................................................................................................................................ 206
Ajout et retrait d'un intervalle de temps ................................................................................................................................................................... 207
Ajout d'un intervalle de temps ................................................................................................................................................................................. 208
Soustraction d'un intervalle de temps ..................................................................................................................................................................... 210
Divers ...................................................................................................................................................................................................................... 211
Créer une date/heure à partir d'autres informations ................................................................................................................................................ 211
Convertir un TIME en secondes, et vice versa ........................................................................................................................................................ 212
Dernier jour du mois ................................................................................................................................................................................................ 212
Exercices ....................................................................................................................................................................... 212
Commençons par le format ..................................................................................................................................................................................... 213
Passons aux calculs ................................................................................................................................................................................................ 214
Et pour finir, mélangeons le tout .............................................................................................................................................................................. 215
Partie 5 : Sécuriser et automatiser ses actions ............................................................................... 217
Transactions .................................................................................................................................................................. 218
Etat actuel de la base de données .......................................................................................................................................................................... 218
Principe ................................................................................................................................................................................................................... 221
Support des transactions ........................................................................................................................................................................................ 222
Syntaxe et utilisation ............................................................................................................................................................................................... 222
Valider/annuler les changements ............................................................................................................................................................................ 222
www.openclassrooms.com
Sommaire 6/414
Démarrer explicitement une transaction ................................................................................................................................................................. 225
Jalon de transaction ................................................................................................................................................................................................ 226
Validation implicite et commandes non-annulables ................................................................................................................................................ 227
ACID ........................................................................................................................................................................................................................ 228
A pour Atomicité ...................................................................................................................................................................................................... 228
C pour cohérence .................................................................................................................................................................................................... 228
I pour Isolation ......................................................................................................................................................................................................... 229
D pour Durabilité ..................................................................................................................................................................................................... 231
Verrous .......................................................................................................................................................................... 232
Principe ................................................................................................................................................................................................................... 233
Verrous de table et verrous de ligne ........................................................................................................................................................................ 233
Avertissements ........................................................................................................................................................................................................ 233
Modification de notre base de données .................................................................................................................................................................. 233
Syntaxe et utilisation : verrous de table ................................................................................................................................................................... 236
Syntaxe et utilisation : verrous de ligne ................................................................................................................................................................... 242
Requêtes de modification, insertion et suppression ................................................................................................................................................ 242
Requêtes de sélection ............................................................................................................................................................................................. 242
Transactions et fin d'un verrou de ligne ................................................................................................................................................................... 243
Exemples ................................................................................................................................................................................................................ 243
En résumé ............................................................................................................................................................................................................... 246
Rôle des index ......................................................................................................................................................................................................... 247
Lignes fantômes et index de clé suivante ............................................................................................................................................................... 248
Pourquoi poser un verrou exclusif avec une requête SELECT ? ............................................................................................................................ 250
Niveaux d'isolation .................................................................................................................................................................................................. 252
Syntaxe ................................................................................................................................................................................................................... 252
Les différents niveaux ............................................................................................................................................................................................. 252
Requêtes préparées ...................................................................................................................................................... 255
Variables utilisateur ................................................................................................................................................................................................. 255
Définitions ............................................................................................................................................................................................................... 255
Créer et modifier une variable utilisateur ................................................................................................................................................................ 255
Utilisation d'une variable utilisateur ......................................................................................................................................................................... 256
Portée des variables utilisateurs ............................................................................................................................................................................. 257
Principe et syntaxe des requêtes préparées ........................................................................................................................................................... 258
Principe ................................................................................................................................................................................................................... 258
Syntaxe ................................................................................................................................................................................................................... 258
Usage et utilité ........................................................................................................................................................................................................ 260
Usage ...................................................................................................................................................................................................................... 260
Utilité ....................................................................................................................................................................................................................... 262
Procédures stockées ..................................................................................................................................................... 265
Création et utilisation d'une procédure .................................................................................................................................................................... 265
Procédure avec une seule requête ......................................................................................................................................................................... 265
Procédure avec un bloc d'instructions ..................................................................................................................................................................... 265
Délimiteur ................................................................................................................................................................................................................ 266
Création d'une procédure stockée .......................................................................................................................................................................... 267
Utilisation d'une procédure stockée ........................................................................................................................................................................ 267
Les paramètres d'une procédure stockée ............................................................................................................................................................... 268
Sens des paramètres .............................................................................................................................................................................................. 268
Syntaxe ................................................................................................................................................................................................................... 268
Exemples ................................................................................................................................................................................................................ 268
Suppression d'une procédure ................................................................................................................................................................................. 272
Avantages, inconvénients et usage des procédures stockées ............................................................................................................................... 273
Avantages ............................................................................................................................................................................................................... 273
Inconvénients .......................................................................................................................................................................................................... 273
Conclusion et usage ................................................................................................................................................................................................ 273
Structurer ses instructions ............................................................................................................................................. 274
Blocs d'instructions et variables locales .................................................................................................................................................................. 275
Blocs d'instructions .................................................................................................................................................................................................. 275
Variables locales ..................................................................................................................................................................................................... 275
Structures conditionnelles ....................................................................................................................................................................................... 279
La structure IF ......................................................................................................................................................................................................... 279
La structure CASE ................................................................................................................................................................................................... 281
Utiliser une structure conditionnelle directement dans une requête ....................................................................................................................... 285
Boucles ................................................................................................................................................................................................................... 286
La boucle WHILE .................................................................................................................................................................................................... 286
La boucle REPEAT .................................................................................................................................................................................................. 286
Donner un label à une boucle ................................................................................................................................................................................. 287
Les instructions LEAVE et ITERATE ....................................................................................................................................................................... 288
La boucle LOOP ...................................................................................................................................................................................................... 292
Gestionnaires d'erreurs, curseurs et utilisation avancée .............................................................................................. 293
Gestion des erreurs ................................................................................................................................................................................................. 294
Création d'un gestionnaire d'erreur ......................................................................................................................................................................... 295
Définition de l'erreur gérée ...................................................................................................................................................................................... 296
Déclarer plusieurs gestionnaires, gérer plusieurs erreurs par gestionnaire ............................................................................................................ 299
Curseurs .................................................................................................................................................................................................................. 300
Syntaxe ................................................................................................................................................................................................................... 301
Restrictions ............................................................................................................................................................................................................. 302
Parcourir intelligemment tous les résultats d'un curseur ......................................................................................................................................... 303
Utilisation avancée des blocs d'instructions ............................................................................................................................................................ 306
Utiliser des variables utilisateur dans un bloc d'instructions ................................................................................................................................... 306
Utiliser une procédure dans un bloc ........................................................................................................................................................................ 307
Transactions et gestion d'erreurs ............................................................................................................................................................................ 307
Préparer une requête dans un bloc d'instructions ................................................................................................................................................... 308
www.openclassrooms.com
Sommaire 7/414
Triggers ......................................................................................................................................................................... 309
Principe et usage .................................................................................................................................................................................................... 310
Qu'est-ce qu'un trigger ? ......................................................................................................................................................................................... 310
À quoi sert un trigger ? ............................................................................................................................................................................................ 310
Création des triggers ............................................................................................................................................................................................... 311
Syntaxe .................................................................................................................................................................................................................... 311
Règle et convention ................................................................................................................................................................................................ 312
OLD et NEW ............................................................................................................................................................................................................ 312
Erreur déclenchée pendant un trigger ..................................................................................................................................................................... 313
Suppression des triggers ........................................................................................................................................................................................ 313
Exemples ................................................................................................................................................................................................................ 313
Contraintes et vérification des données .................................................................................................................................................................. 313
Mise à jour d'informations dépendant d'autres données ......................................................................................................................................... 318
Historisation ............................................................................................................................................................................................................ 320
Restrictions ............................................................................................................................................................................................................. 324
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées ............. 326
Vues .............................................................................................................................................................................. 327
Etat actuel de la base de données .......................................................................................................................................................................... 327
Création d'une vue .................................................................................................................................................................................................. 332
Le principe ............................................................................................................................................................................................................... 332
Création ................................................................................................................................................................................................................... 332
Les colonnes de la vue ........................................................................................................................................................................................... 334
Requête SELECT stockée dans la vue ................................................................................................................................................................... 335
Sélection des données d'une vue ........................................................................................................................................................................... 337
Modification et suppression d'une vue .................................................................................................................................................................... 338
Modification ............................................................................................................................................................................................................. 338
Suppression ............................................................................................................................................................................................................ 339
Utilité des vues ........................................................................................................................................................................................................ 339
Clarification et facilitation des requêtes ................................................................................................................................................................... 339
Création d'une interface entre l'application et la base de données ......................................................................................................................... 341
Restriction des données visibles par les utilisateurs ............................................................................................................................................... 341
Algorithmes ............................................................................................................................................................................................................. 342
MERGE ................................................................................................................................................................................................................... 342
TEMPTABLE ........................................................................................................................................................................................................... 344
Algorithme par défaut et conditions ......................................................................................................................................................................... 344
Modification des données d'une vue ....................................................................................................................................................................... 345
Conditions pour qu'une vue permette de modifier des données (requêtes UPDATE) ............................................................................................ 345
Conditions pour qu'une vue permette d'insérer des données (requêtes INSERT) .................................................................................................. 346
Conditions pour qu'une vue permette de supprimer des données (requêtes DELETE) ......................................................................................... 348
Option de la vue pour la modification des données ................................................................................................................................................ 349
Tables temporaires ........................................................................................................................................................ 351
Principe, règles et comportement ........................................................................................................................................................................... 352
Création, modification, suppression d'une table temporaire ................................................................................................................................... 352
Utilisation des tables temporaires ........................................................................................................................................................................... 353
Cache-cache table .................................................................................................................................................................................................. 353
Restrictions des tables temporaires ........................................................................................................................................................................ 354
Interaction avec les transactions ............................................................................................................................................................................. 356
Méthodes alternatives de création des tables ......................................................................................................................................................... 357
Créer une table à partir de la structure d'une autre ................................................................................................................................................. 357
Créer une table à partir de données sélectionnées ................................................................................................................................................ 358
Utilité des tables temporaires .................................................................................................................................................................................. 363
Gain de performance ............................................................................................................................................................................................... 363
Tests ........................................................................................................................................................................................................................ 364
Sets de résultats et procédures stockées ............................................................................................................................................................... 364
Vues matérialisées ........................................................................................................................................................ 365
Principe ................................................................................................................................................................................................................... 366
Vues - rappels et performance ................................................................................................................................................................................ 366
Vues matérialisées .................................................................................................................................................................................................. 366
Mise à jour des vues matérialisées ......................................................................................................................................................................... 367
Mise à jour sur demande ......................................................................................................................................................................................... 367
Mise à jour automatique .......................................................................................................................................................................................... 368
Gain de performance ............................................................................................................................................................................................... 370
Tables vs vue vs vue matérialisée ........................................................................................................................................................................... 370
Les trois procédures ................................................................................................................................................................................................ 370
Le test ...................................................................................................................................................................................................................... 372
Conclusion .............................................................................................................................................................................................................. 372
Partie 7 : Gestion des utilisateurs et configuration du serveur ........................................................ 373
Gestion des utilisateurs ................................................................................................................................................. 374
Etat actuel de la base de données .......................................................................................................................................................................... 374
Introduction .............................................................................................................................................................................................................. 381
Les utilisateurs et leurs privilèges ........................................................................................................................................................................... 381
Création, modification et suppression des utilisateurs ............................................................................................................................................ 382
Création et suppression .......................................................................................................................................................................................... 382
Syntaxe ................................................................................................................................................................................................................... 382
Utilisateur ................................................................................................................................................................................................................ 382
Mot de passe ........................................................................................................................................................................................................... 383
Les privilèges - introduction .................................................................................................................................................................................... 384
Les différents privilèges ........................................................................................................................................................................................... 384
Les différents niveaux d'application des privilèges ................................................................................................................................................. 385
Ajout et révocation de privilèges ............................................................................................................................................................................. 385
Ajout de privilèges ................................................................................................................................................................................................... 385
www.openclassrooms.com
Partager 8/414
Révocation de privilèges ......................................................................................................................................................................................... 386
Privilèges particuliers .............................................................................................................................................................................................. 387
Les privilèges ALL, USAGE et GRANT OPTION .................................................................................................................................................... 387
Particularité des triggers, vues et procédures stockées .......................................................................................................................................... 388
Options supplémentaires ........................................................................................................................................................................................ 391
Limitation des ressources ....................................................................................................................................................................................... 391
Connexion SSL ....................................................................................................................................................................................................... 391
Informations sur la base de données et les requêtes ................................................................................................... 392
Commandes de description .................................................................................................................................................................................... 393
Description d'objets ................................................................................................................................................................................................. 393
Requête de création d'un objet ............................................................................................................................................................................... 395
La base de données information_schema .............................................................................................................................................................. 396
Déroulement d'une requête de sélection ................................................................................................................................................................. 399
Configuration et options ................................................................................................................................................ 402
Variables système ................................................................................................................................................................................................... 403
Niveau des variables système ................................................................................................................................................................................ 404
Modification des variables système avec SET ........................................................................................................................................................ 406
Effet de la modification selon le niveau ................................................................................................................................................................... 407
Les commandes SET spéciales .............................................................................................................................................................................. 407
Options au démarrage du client mysql .................................................................................................................................................................... 408
Options au démarrage du serveur mysqld .............................................................................................................................................................. 409
Fichiers de configuration ......................................................................................................................................................................................... 411
Emplacement du fichier ........................................................................................................................................................................................... 411
Structure du fichier .................................................................................................................................................................................................. 412
www.openclassrooms.com
Partager 9/414
Administrez vos bases de données avec MySQL
Par Chantal Gribaumont (Taguan)
Mise à jour : 05/10/2012
Difficulté : Intermédiaire Durée d'étude : 1 mois, 15 jours
Vous avez de nombreuses données à traiter et vous voulez les organiser correctement, avec un
outil adapté ?
Les bases de données ont été créées pour vous !
Ce tutoriel porte sur MySQL, qui est un Système de Gestion de Bases de Données Relationnelles (abrégé SGBDR). C'est-à-dire
un logiciel qui permet de gérer des bases de données, et donc de gérer de grosses quantités d'informations. Il utilise pour cela le
langage SQL.
Il s'agit d'un des SGBDR les plus connus et les plus utilisés (Wikipédia et Adobe utilisent par exemple MySQL). Et c'est
certainement le SGBDR le plus utilisé à ce jour pour réaliser des sites web dynamiques. C'est d'ailleurs MySQL qui est présenté
dans le tutoriel Concevez votre site web avec PHP et MySQL écrit par Mathieu Nebra, fondateur de ce site.
MySQL peut donc s'utiliser seul, mais est la plupart du temps combiné à un autre langage de programmation : PHP par exemple
pour de nombreux sites web, mais aussi Java, Python, C++, et beaucoup, beaucoup d'autres.
MySQL avec l'interface PHPMyAdmin MySQL avec une console
windows
Différentes façons d'utiliser MySQL
Quelques exemples d'applications
Vous gérez une boîte de location de matériel audiovisuel, et afin de toujours savoir où vous en êtes dans votre stock, vous
voudriez un système informatique vous permettant de gérer les entrées et sorties de matériel, mais aussi éventuellement les
données de vos clients. MySQL est une des solutions possibles pour gérer tout ça.
Vous voulez créer un site web dynamique en HTML/CSS/PHP avec un espace membre, un forum, un système de news ou même
un simple livre d'or. Une base de données vous sera presque indispensable.
Vous créez un super logiciel en Java qui va vous permettre de gérer vos dépenses afin de ne plus jamais être à découvert, ou
devoir vous affamer pendant trois semaines pour pouvoir payer le cadeau d'anniversaire du petit frère. Vous pouvez utiliser une
base de données pour stocker les dépenses déjà effectuées, les dépenses à venir, les rentrées régulières, ...
Votre tantine éleveuse d'animaux voudrait un logiciel simple pour gérer ses bestioles, vous savez programmer en python et lui
proposez vos services dans l'espoir d'avoir un top cadeau à Noël. Une base de données vous aidera à retenir que Poupouche le
Caniche est né le 13 décembre 2007, que Sami le Persan a des poils blancs et que Igor la tortue est le dernier représentant d'une
race super rare !
Points abordés dans ce tutoriel
La conception et l'utilisation de bases de données est un vaste sujet, il a fallu faire des choix sur les thèmes à aborder. Voici les
www.openclassrooms.com
Administrez vos bases de données avec MySQL 10/414
compétences que ce tutoriel vise à vous faire acquérir :
Création d'une base de données et des tables nécessaires à la gestion des données
Gestion des relations entre les différentes tables d'une base
Sélection des données selon de nombreux critères
Manipulation des données (modification, suppression, calculs divers)
Utilisation des triggers et des procédures stockées pour automatiser certaines actions
Utilisation des vues et des tables temporaires
Gestion des utilisateurs de la base de données
Et plus encore...
www.openclassrooms.com