Administrez vos bases de données avec Mysql
Chantal Gribaumont
OpenClassrooms
Avant-propos
i
Qu'allez-vous apprendre en lisant ce livre ?ii
Comment lire ce livre ?iii
Suivez l'ordre des chapitresiii
Pratiquez en même tempsiii
Utilisez les codes web !iii
Remerciementsiv
I MySQL et les bases du langage SQL
1
1 Introduction
3
Concepts de base4
Présentation succincte de MySQL...6
... et de ses concurrents7
Organisation d'une base de données8
2 Installation de MySQL
9
Avant-propos10
Ligne de commande10
Interface graphique10
Pourquoi utiliser la ligne de commande ?11
Installation du logiciel11
Windows11
Mac OS14
Linux15
Connexion à MySQL15
Connexion au client16
Déconnexion17
Syntaxe SQL et premières commandes18
«Hello World !»18
Syntaxe18
Un peu de math20
Utilisateur20
Le raccourci «MySQL Command Line» de Windows21
Encodage, jeux de caractères et interclassement23
La table ASCII23
Jeux de caractères24
Interclassement25
3 Les types de données
27
Types numériques28
Nombres entiers28
Nombres décimaux29
Types alphanumériques31
Chaînes de type texte31
Chaînes de type binaire32
Les types SET et ENUM32
Types temporels36
Les types Date, Time et Datetime36
Le type Year38
Le type Timestamp38
La date par défaut38
4 Création d'une base de données
41
Avant-propos : conseils et conventions42
Conseils42
Conventions42
Mise en situation43
Création et suppression d'une base de données44
Création44
Suppression44
Utilisation d'une base de données45
5 Création de tables
47
Définition des colonnes48
Type de colonne48
Null or Not null ?48
Récapitulatif49
Introduction aux clés primaires49
Identité49
Clé primaire50
Auto-incrémentation50
Les moteurs de tables51
Préciser un moteur lors de la création de la table51
Syntaxe de Create table51
Syntaxe52
Application : création de Animal53
Vérifications53
Suppression d'une table54
6 Modification d'une table
55
Syntaxe de la requête56
Ajout et suppression d'une colonne56
Ajout56
Suppression57
Modification de colonne57
Changement du nom de la colonne57
Changement du type de données57
7 Insertion de données
59
Syntaxe de Insert60
Insertion sans préciser les colonnes60
Insertion en précisant les colonnes61
Insertion multiple61
Syntaxe alternative de MySQL61
Utilisation de fichiers externes62
Exécuter des commandes SQL à partir d'un fichier62
Insérer des données à partir d'un fichier formaté63
Remplissage de la base65
Exécution de commandes SQL65
La commande Load data infile65
8 Sélection de données
67
Syntaxe de Select68
Sélectionner toutes les colonnes68
La clause Where69
Les opérateurs de comparaison69
Combinaisons de critères69
Sélection complexe71
Le cas de Null72
Tri des données74
Tri ascendant ou descendant74
Trier sur plusieurs colonnes75
Éliminer les doublons75
Restreindre les résultats76
Syntaxe76
Syntaxe alternative77
9 Élargir les possibilités de la clause Where
79
Recherche approximative80
Sensibilité à la casse81
Recherche dans les numériques81
Recherche dans un intervalle82
Set de critères82
10 Suppression et modification de données
85
Sauvegarde d'une base de données86
Suppression87
Modification88
II Index, jointures et sous-requêtes
89
11 Index
91
État actuel de la base de données92
Qu'est-ce qu'un index ?92
Intérêt des index93
Désavantages94
Index sur plusieurs colonnes94
Index sur des colonnes de type alphanumérique96
Les différents types d'index97
Index Unique97
Index Fulltext98
Création et suppression des index98
Ajout des index lors de la création de la table98
Ajout des index après création de la table101
Création d'un index Unique : le cas des contraintes102
Suppression d'un index103
Recherches avec Fulltext103
Comment fonctionne la recherche Fulltext ?103
Les types de recherche104
12 Clés primaires et étrangères
111
Clés primaires, le retour112
Choix de la clé primaire112
Création d'une clé primaire113
Suppression de la clé primaire115
Clés étrangères115
Création116
Suppression d'une clé étrangère117
Modification de notre base118
La table Espece118
La table Animal119
13 Jointures
123
Principe des jointures et notion d'alias124
Principe des jointures124
Notion d'alias125
Jointure interne126
Syntaxe127
Pourquoi «interne» ?129
Jointure externe130
Jointures par la gauche131
Jointures par la droite132
Syntaxes alternatives133
Jointures avec Using133
Jointures naturelles134
Jointures sans Join134
Exemples d'application et exercices135
A/ Commençons par des choses faciles135
B/ Compliquons un peu les choses137
C/ Et maintenant, le test ultime !138
14 Sous-requêtes
141
Sous-requêtes dans le From142
Les règles à respecter143
Sous-requêtes dans les conditions144
Comparaisons145
Conditions avec In et Not in147
Conditions avec Any, Some et All148
Sous-requêtes corrélées150
15 Jointures et sous-requêtes : modification de données
153
Insertion154
Sous-requête pour l'insertion154
Modification156
Utilisation des sous-requêtes156
Modification avec jointure158
Suppression158
Suppression avec jointure159
16 Union de plusieurs requêtes
161
Syntaxe162
Les règles162
Requête avec Union all164
Limit et Order by165
Requête avec Limit165
Requête avec Order by166
17 Options des clés étrangères
169
Option sur suppression des clés étrangères170
Petits rappels170
Suppression d'une référence170
Option sur modification des clés étrangères173
Utilisation de ces options dans notre base174
Modifications174
Suppressions174
Les requêtes175
18 Violation de contrainte d'unicité
177
Ignorer les erreurs178
La commande Load data infile179
Remplacer l'ancienne ligne179
Remplacement de plusieurs lignes180
La commande Replace avec Load data infile181
Modifier l'ancienne ligne181
III Fonctions : nombres, chaînes et agrégats
183
19 Rappels et introduction
185
État actuel de la base de données186
Rappels et manipulation simple de nombres186
Rappels186
Combiner les données avec des opérations mathématiques187
Définition d'une fonction188
Fonctions scalaires versus fonctions d'agrégation190
Quelques fonctions générales191
Informations sur l'environnement actuel191
Informations sur la dernière requête192
Convertir le type de données194
20 Fonctions scalaires
197
Manipulation de nombres198
Arrondis198
Exposants et racines199
Hasard200
Divers201
Manipulation de chaînes de caractères201
Longueur et comparaison201
Retrait et ajout de caractères202
Recherche et remplacement204
Concaténation206
Field(), une fonction bien utile pour le tri206
Code ASCII207
Exemples d'application et exercices208
On commence par du facile208
Puis on corse un peu208
21 Fonctions d'agrégation
211
Fonctions statistiques212
Nombre de lignes212
Minimum et maximum213
Somme et moyenne213
Concaténation214
Principe214
Syntaxe214
Exemples215
22 Regroupement
217
Regroupement sur un critère218
Voir d'autres colonnes218
Colonnes sélectionnées219
Tri des données221
Et les autres espèces ?221
Regroupement sur plusieurs critères222
Super-agrégats224
Conditions sur les fonctions d'agrégation227
Optimisation228
23 Exercices sur les agrégats
231
Du simple...232
...Vers le complexe233
IV Fonctions : manipuler les dates
235
24 Obtenir la date/l'heure actuelle
237
État actuel de la base de données238
Rappels238
La date238
L'heure238
La date et l'heure238
Timestamp239
L'année239
Date actuelle239
Heure actuelle240
Date et heure actuelles240
Les fonctions240
Qui peut le plus, peut le moins241
Timestamp Unix241
25 Formater une donnée temporelle
243
Extraire une information précise244
Informations sur la date244
Informations sur l'heure247
Formater une date facilement247
Format248
Exemples248
Fonction supplémentaire pour l'heure249
Formats standards250
Créer une date à partir d'une chaîne de caractères251
26 Calculs sur les données temporelles
253
Différence entre deux dates/heures254
Ajout et retrait d'un intervalle de temps255
Ajout d'un intervalle de temps255
Soustraction d'un intervalle de temps258
Divers259
Créer une date/heure à partir d'autres informations259
Convertir un Time en secondes, et vice versa260
Dernier jour du mois261
27 Exercices
263
Commençons par le format264
Passons aux calculs265
Et pour finir, mélangeons le tout266
V Sécuriser et automatiser ses actions
269
28 Transactions
271
État actuel de la base de données272
Principe272
Support des transactions273
Syntaxe et utilisation274
Valider/annuler les changements274
Démarrer explicitement une transaction276
Jalon de transaction278
Validation implicite et commandes non-annulables279
ACID280
A pour Atomicité281
C pour cohérence281
I pour Isolation282
D pour Durabilité285
29 Verrous
287
Principe288
Verrous de table et verrous de ligne288
Avertissements288
Modification de notre base de données289
Syntaxe et utilisation : verrous de table289
Syntaxe et utilisation : verrous de ligne295
Requêtes de modification, insertion et suppression295
Requêtes de sélection296
Transactions et fin d'un verrou de ligne296
Exemples297
Rôle des index300
Lignes fantômes et index de clé suivante303
Pourquoi poser un verrou exclusif avec une requête Select ?305
Niveaux d'isolation307
Syntaxe307
Les différents niveaux308
30 Requêtes préparées
311
Variables utilisateur312
Définitions312
Créer et modifier une variable utilisateur312
Utilisation d'une variable utilisateur313
Portée des variables utilisateurs314
Principe et syntaxe des requêtes préparées315
Principe315
Syntaxe315
Usage et utilité318
Usage318
Utilité320
31 Procédures stockées
325
Création et utilisation d'une procédure326
Procédure avec une seule requête326
Procédure avec un bloc d'instructions326
Délimiteur327
Création d'une procédure stockée328
Utilisation d'une procédure stockée329
Les paramètres d'une procédure stockée329
Sens des paramètres329
Syntaxe329
Exemples330
Suppression d'une procédure334
Avantages, inconvénients et usage des procédures stockées334
Avantages334
Inconvénients335
Conclusion et usage335
32 Structurer ses instructions
337
Blocs d'instructions et variables locales338
Blocs d'instructions338
Variables locales338
Structures conditionnelles343
La structure IF343
La structure Case345
Utiliser une structure conditionnelle dans une requête349
Boucles350
La boucle While350
La boucle Repeat351
Donner un label à une boucle352
Les instructions Leave et Iterate353
La boucle Loop357
33 Gestionnaires d'erreurs, curseurs et utilisation avancée
359
Gestion des erreurs360
Création d'un gestionnaire d'erreur361
Définition de l'erreur gérée363
Déclarer plusieurs gestionnaires, gérer plusieurs erreurs par gestionnaire366
Curseurs368
Syntaxe368
Restrictions370
Parcourir intelligemment tous les résultats d'un curseur370
Utilisation avancée des blocs d'instructions374
Utiliser des variables utilisateur dans un bloc d'instructions375
Utiliser une procédure dans un bloc375
Transactions et gestion d'erreurs376
Préparer une requête dans un bloc d'instructions377
34 Triggers
379
Principe et usage380
Qu'est-ce qu'un trigger ?380
À quoi sert un trigger ?380
Création des triggers382
Syntaxe382
Règle et convention383
Les mots-clés Old et New383
Erreur déclenchée pendant un trigger384
Suppression des triggers384
Exemples385
Contraintes et vérification des données385
Mise à jour d'informations dépendant d'autres données390
Historisation392
Restrictions397
VI Au-delà des tables classiques : vues, tables temporaires
et vues matérialisées
401
35 Vues
403
État actuel de la base de données404
Création d'une vue404
Le principe404
Création404
Les colonnes de la vue405
Requête Select stockée dans la vue407
Sélection des données d'une vue410
Modification et suppression d'une vue411
Modification411
Suppression411
Utilité des vues412
Clarification et facilitation des requêtes412
Création d'une interface entre l'application et la base de données413
Restriction des données visibles par les utilisateurs414
Algorithmes415
Merge415
Temptable416
Algorithme par défaut et conditions417
Modification des données d'une vue417
Conditions pour qu'une vue permette de modifier des données418
Conditions pour qu'une vue permette d'insérer des données419
Conditions pour qu'une vue permette de supprimer des données421
Option de la vue pour la modification des données421
36 Tables temporaires
425
Principe, règles et comportement426
Création, modification, suppression d'une table temporaire426
Utilisation des tables temporaires427
Cache-cache table427
Restrictions des tables temporaires429
Interaction avec les transactions430
Méthodes alternatives de création des tables431
Créer une table à partir de la structure d'une autre431
Créer une table à partir de données sélectionnées433
Utilité des tables temporaires438
Gain de performance438
Tests439
Sets de résultats et procédures stockées439
37 Vues matérialisées
443
Principe444
Vues : rappels et performance444
Vues matérialisées444
Mise à jour des vues matérialisées446
Mise à jour sur demande446
Mise à jour automatique447
Gain de performance449
Tables vs vue vs vue matérialisée449
Les trois procédures450
Le test452
Conclusion452
VII Gestion des utilisateurs et configuration du serveur
455
38 Gestion des utilisateurs
457
État actuel de la base de données458
Introduction458
Les utilisateurs et leurs privilèges458
Création, modification et suppression des utilisateurs459
Mot de passe461
Les privilèges - introduction461
Les différents privilèges462
Les différents niveaux d'application des privilèges462
Ajout et révocation de privilèges463
Ajout de privilèges463
Révocation de privilèges464
Privilèges particuliers465
Les privilèges All, Usage et Grant option465
Particularité des triggers, vues et procédures stockées466
Options supplémentaires469
Limitation des ressources469
Connexion SSL470
39 Informations sur la BDD et les requêtes
471
Commandes de description472
Description d'objets472
Requête de création d'un objet473
La base de données information_schema474
Déroulement d'une requête477
40 Configuration et options
483
Variables système484
Niveau des variables système485
Modification des variables système avec SET487
Effet de la modification selon le niveau487
Les commandes Set spéciales488
Options au démarrage du client MySQL488
Options au démarrage du serveur mysqld490
Fichiers de configuration491
Emplacement du fichier492
Structure du fichier493