Descargue el libro avec MySQL bases de données Administrez vos ahora PDF
Preview Descargue el libro avec MySQL bases de données Administrez vos ahora
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
The list of books you might like
