Chapitre 3 LES OBJETS MySQL - Free
15.5.1 Exemples d'opérations de l'AR. 15.6 TP modèle relationnel PARISCOPE.
15.6.1 Enoncé. 15.6.2 Corrigé. 15.7 TP algèbre relationnelle PARISCOPE.
part of the document
MYSQL 5 - SQL
Sommaire
TOC \o "1-9" \t "Titre 9;9;Titre 8;8;Titre 7;7;Titre 6;6;Titre 5;5;Titre 4;4;Titre 3;3;Titre 2;2;Titre 1;1" \h HYPERLINK \l "_Toc258332274" Chapitre 1 INTRODUCTION PAGEREF _Toc258332274 \h 7
HYPERLINK \l "_Toc258332275" 1.1 Bases utilisées PAGEREF _Toc258332275 \h 7
HYPERLINK \l "_Toc258332276" 1.2 Définition d'un SGBDR PAGEREF _Toc258332276 \h 7
HYPERLINK \l "_Toc258332277" Chapitre 2 INSTALLATION et CONFIGURATION PAGEREF _Toc258332277 \h 8
HYPERLINK \l "_Toc258332278" 2.1 Fichiers à télécharger PAGEREF _Toc258332278 \h 8
HYPERLINK \l "_Toc258332279" 2.1.1 WAMP5 PAGEREF _Toc258332279 \h 8
HYPERLINK \l "_Toc258332280" 2.1.2 EasyPHP PAGEREF _Toc258332280 \h 9
HYPERLINK \l "_Toc258332281" 2.1.3 Xampp PAGEREF _Toc258332281 \h 9
HYPERLINK \l "_Toc258332282" 2.1.4 Autres PAGEREF _Toc258332282 \h 9
HYPERLINK \l "_Toc258332283" 2.1.5 MySQL PAGEREF _Toc258332283 \h 10
HYPERLINK \l "_Toc258332284" 2.2 Installations PAGEREF _Toc258332284 \h 11
HYPERLINK \l "_Toc258332285" 2.2.1 Installation du SGBDR PAGEREF _Toc258332285 \h 11
HYPERLINK \l "_Toc258332286" 2.2.2 Installation des outils PAGEREF _Toc258332286 \h 11
HYPERLINK \l "_Toc258332287" 2.2.2.1 PhpMyAdmin PAGEREF _Toc258332287 \h 11
HYPERLINK \l "_Toc258332288" 2.2.2.2 MysqlQueryBrowser de MySQL AB PAGEREF _Toc258332288 \h 11
HYPERLINK \l "_Toc258332289" 2.2.2.3 DBDesignor de FabForge.net PAGEREF _Toc258332289 \h 11
HYPERLINK \l "_Toc258332290" 2.2.2.4 MySQL Workbench de Mysql AB PAGEREF _Toc258332290 \h 11
HYPERLINK \l "_Toc258332291" 2.2.2.5 MysqlAministrator de Mysql AB PAGEREF _Toc258332291 \h 11
HYPERLINK \l "_Toc258332292" 2.3 Le MySQL Query Browser PAGEREF _Toc258332292 \h 12
HYPERLINK \l "_Toc258332293" 2.3.1 Connexion PAGEREF _Toc258332293 \h 12
HYPERLINK \l "_Toc258332294" 2.3.2 Interface PAGEREF _Toc258332294 \h 13
HYPERLINK \l "_Toc258332295" 2.3.3 Création d'une base PAGEREF _Toc258332295 \h 14
HYPERLINK \l "_Toc258332296" 2.3.4 Suppression d'une base de données. PAGEREF _Toc258332296 \h 15
HYPERLINK \l "_Toc258332297" 2.3.5 Création d'une table PAGEREF _Toc258332297 \h 16
HYPERLINK \l "_Toc258332298" 2.3.6 Création d'une clé étrangère PAGEREF _Toc258332298 \h 18
HYPERLINK \l "_Toc258332299" 2.3.7 Modification, suppression d'une table PAGEREF _Toc258332299 \h 20
HYPERLINK \l "_Toc258332300" 2.3.8 Travailler en mode ligne de commandes PAGEREF _Toc258332300 \h 21
HYPERLINK \l "_Toc258332301" 2.3.8.1 Avec MySQL Query Browser PAGEREF _Toc258332301 \h 21
HYPERLINK \l "_Toc258332302" 2.4 Travailler avec l'utilitaire mysql PAGEREF _Toc258332302 \h 22
HYPERLINK \l "_Toc258332303" 2.5 Travailler Avec PHPMyADMIN PAGEREF _Toc258332303 \h 24
HYPERLINK \l "_Toc258332304" Chapitre 3 LES OBJETS MySQL PAGEREF _Toc258332304 \h 25
HYPERLINK \l "_Toc258332305" 3.1 Le LDD PAGEREF _Toc258332305 \h 25
HYPERLINK \l "_Toc258332306" 3.2 Les bases de données PAGEREF _Toc258332306 \h 26
HYPERLINK \l "_Toc258332307" 3.2.1 Création PAGEREF _Toc258332307 \h 26
HYPERLINK \l "_Toc258332308" 3.2.2 Modification d'une base de données PAGEREF _Toc258332308 \h 27
HYPERLINK \l "_Toc258332309" 3.2.3 Supression d'une base de données PAGEREF _Toc258332309 \h 27
HYPERLINK \l "_Toc258332310" 3.3 Les tables PAGEREF _Toc258332310 \h 28
HYPERLINK \l "_Toc258332311" 3.3.1 Les types de données. PAGEREF _Toc258332311 \h 28
HYPERLINK \l "_Toc258332312" 3.3.2 Création d'une table PAGEREF _Toc258332312 \h 30
HYPERLINK \l "_Toc258332313" 3.3.3 Création d'une table à partir d'une autre table PAGEREF _Toc258332313 \h 31
HYPERLINK \l "_Toc258332314" 3.3.4 Le schéma de la base COURS PAGEREF _Toc258332314 \h 31
HYPERLINK \l "_Toc258332315" 3.3.5 Renommage d'une table PAGEREF _Toc258332315 \h 32
HYPERLINK \l "_Toc258332316" 3.3.6 Suppression d'une table PAGEREF _Toc258332316 \h 32
HYPERLINK \l "_Toc258332317" 3.3.7 Modification de la structure d'une table PAGEREF _Toc258332317 \h 33
HYPERLINK \l "_Toc258332318" 3.4 Les contraintes (Constraint) PAGEREF _Toc258332318 \h 35
HYPERLINK \l "_Toc258332319" 3.4.1 Création d'une contrainte PAGEREF _Toc258332319 \h 35
HYPERLINK \l "_Toc258332320" 3.4.2 Suppression d'une contrainte PAGEREF _Toc258332320 \h 36
HYPERLINK \l "_Toc258332321" 3.5 Les index (Index) PAGEREF _Toc258332321 \h 37
HYPERLINK \l "_Toc258332322" 3.5.1 Création d'un index PAGEREF _Toc258332322 \h 37
HYPERLINK \l "_Toc258332323" 3.5.2 Suppression d'un index PAGEREF _Toc258332323 \h 38
HYPERLINK \l "_Toc258332324" 3.5.3 Visualisation des Index PAGEREF _Toc258332324 \h 39
HYPERLINK \l "_Toc258332325" 3.5.4 Utilisation des index PAGEREF _Toc258332325 \h 41
HYPERLINK \l "_Toc258332326" 3.6 TP PARISCOPE : Création et Manipulation des objets de la base PAGEREF _Toc258332326 \h 42
HYPERLINK \l "_Toc258332327" 3.6.1 Enoncés PAGEREF _Toc258332327 \h 42
HYPERLINK \l "_Toc258332328" Chapitre 4 MISE A JOUR DES DONNEES PAGEREF _Toc258332328 \h 43
HYPERLINK \l "_Toc258332329" 4.1 Présentation PAGEREF _Toc258332329 \h 44
HYPERLINK \l "_Toc258332330" 4.2 Insertion de données (INSERT) PAGEREF _Toc258332330 \h 45
HYPERLINK \l "_Toc258332331" 4.2.1.1 Première syntaxe PAGEREF _Toc258332331 \h 45
HYPERLINK \l "_Toc258332332" 4.2.1.2 Deuxième syntaxe PAGEREF _Toc258332332 \h 46
HYPERLINK \l "_Toc258332333" 4.2.1.3 Troisième syntaxe PAGEREF _Toc258332333 \h 46
HYPERLINK \l "_Toc258332334" 4.3 Insertion de données (REPLACE) PAGEREF _Toc258332334 \h 47
HYPERLINK \l "_Toc258332335" 4.4 Suppression de données (DELETE) PAGEREF _Toc258332335 \h 48
HYPERLINK \l "_Toc258332336" 4.5 Suppression des données (TRUNCATE) PAGEREF _Toc258332336 \h 49
HYPERLINK \l "_Toc258332337" 4.6 Modification de données (UPDATE) PAGEREF _Toc258332337 \h 50
HYPERLINK \l "_Toc258332338" 4.7 TP PARISCOPE : Mise à jour des données PAGEREF _Toc258332338 \h 51
HYPERLINK \l "_Toc258332339" 4.7.1 Enoncés MAJ PARISCOPE PAGEREF _Toc258332339 \h 51
HYPERLINK \l "_Toc258332340" Chapitre 5 L'EXTRACTION DES DONNEES (L'ORDRE SELECT) PAGEREF _Toc258332340 \h 52
HYPERLINK \l "_Toc258332341" 5.1.1 SELECT mono table sans condition PAGEREF _Toc258332341 \h 52
HYPERLINK \l "_Toc258332342" 5.1.2 Extraire un certain nombre d'enregistrements (Restriction) PAGEREF _Toc258332342 \h 52
HYPERLINK \l "_Toc258332343" 5.1.3 Le tri PAGEREF _Toc258332343 \h 53
HYPERLINK \l "_Toc258332344" 5.1.4 Quelques fonctions propriétaires PAGEREF _Toc258332344 \h 54
HYPERLINK \l "_Toc258332345" 5.1.4.1 Sur les chaînes PAGEREF _Toc258332345 \h 54
HYPERLINK \l "_Toc258332346" 5.1.4.2 Sur les numériques PAGEREF _Toc258332346 \h 55
HYPERLINK \l "_Toc258332347" 5.1.4.3 Sur les dates PAGEREF _Toc258332347 \h 56
HYPERLINK \l "_Toc258332348" 5.1.5 Les opérateurs de comparaison PAGEREF _Toc258332348 \h 57
HYPERLINK \l "_Toc258332349" 5.1.6 Les opérateurs logiques PAGEREF _Toc258332349 \h 58
HYPERLINK \l "_Toc258332350" 5.1.7 Les opérateurs ensemblistes PAGEREF _Toc258332350 \h 59
HYPERLINK \l "_Toc258332351" 5.1.7.1 IN PAGEREF _Toc258332351 \h 59
HYPERLINK \l "_Toc258332352" 5.1.7.2 BETWEEN PAGEREF _Toc258332352 \h 59
HYPERLINK \l "_Toc258332353" 5.1.7.3 LIKE PAGEREF _Toc258332353 \h 60
HYPERLINK \l "_Toc258332354" 5.1.7.4 IS NULL IS NOT NULL PAGEREF _Toc258332354 \h 60
HYPERLINK \l "_Toc258332355" 5.1.8 Les requêtes calculées PAGEREF _Toc258332355 \h 61
HYPERLINK \l "_Toc258332356" 5.1.8.1 Numériques PAGEREF _Toc258332356 \h 61
HYPERLINK \l "_Toc258332357" 5.1.8.2 Chaînes PAGEREF _Toc258332357 \h 61
HYPERLINK \l "_Toc258332358" 5.1.9 TP PARISCOPE : les requêtes simples PAGEREF _Toc258332358 \h 62
HYPERLINK \l "_Toc258332359" Chapitre 6 LES JOINTURES PAGEREF _Toc258332359 \h 63
HYPERLINK \l "_Toc258332360" 6.1 Principes PAGEREF _Toc258332360 \h 63
HYPERLINK \l "_Toc258332361" 6.2 L'equi-jointure PAGEREF _Toc258332361 \h 64
HYPERLINK \l "_Toc258332362" 6.2.1 Syntaxe simplifiée PAGEREF _Toc258332362 \h 64
HYPERLINK \l "_Toc258332363" 6.2.2 Syntaxe ANSI PAGEREF _Toc258332363 \h 65
HYPERLINK \l "_Toc258332364" 6.3 Auto-jointure PAGEREF _Toc258332364 \h 66
HYPERLINK \l "_Toc258332365" 6.4 Les jointures externes PAGEREF _Toc258332365 \h 67
HYPERLINK \l "_Toc258332366" 6.5 Plus loin avec la syntaxe ANSI PAGEREF _Toc258332366 \h 68
HYPERLINK \l "_Toc258332367" 6.6 Le produit cartésien PAGEREF _Toc258332367 \h 69
HYPERLINK \l "_Toc258332368" 6.6.1 Premier exemple PAGEREF _Toc258332368 \h 69
HYPERLINK \l "_Toc258332369" 6.6.2 Deuxième exemple PAGEREF _Toc258332369 \h 70
HYPERLINK \l "_Toc258332370" 6.6.3 TP PARISCOPE sur les jointures PAGEREF _Toc258332370 \h 71
HYPERLINK \l "_Toc258332371" Chapitre 7 LES REQUETES AGREGATS PAGEREF _Toc258332371 \h 72
HYPERLINK \l "_Toc258332372" 7.1 Les fonctions agrégats PAGEREF _Toc258332372 \h 72
HYPERLINK \l "_Toc258332373" 7.2 La clause GROUP BY PAGEREF _Toc258332373 \h 74
HYPERLINK \l "_Toc258332374" 7.3 La clause HAVING PAGEREF _Toc258332374 \h 76
HYPERLINK \l "_Toc258332375" 7.3.1 TP PARISCOPE sur les Agrégats PAGEREF _Toc258332375 \h 77
HYPERLINK \l "_Toc258332376" Chapitre 8 LES REQUETES ENSEMBLISTES PAGEREF _Toc258332376 \h 78
HYPERLINK \l "_Toc258332377" 8.1 Principes PAGEREF _Toc258332377 \h 78
HYPERLINK \l "_Toc258332378" 8.2 Union PAGEREF _Toc258332378 \h 79
HYPERLINK \l "_Toc258332379" 8.3 Intersection (N'existe pas en MySQL) PAGEREF _Toc258332379 \h 80
HYPERLINK \l "_Toc258332380" 8.4 Différence (N'existe pas en MySQL) PAGEREF _Toc258332380 \h 81
HYPERLINK \l "_Toc258332381" 8.4.1 TP PARISCOPE sur les requêtes ensemblistes PAGEREF _Toc258332381 \h 82
HYPERLINK \l "_Toc258332382" Chapitre 9 LES REQUETES IMBRIQUEES PAGEREF _Toc258332382 \h 83
HYPERLINK \l "_Toc258332383" 9.1 Principes PAGEREF _Toc258332383 \h 83
HYPERLINK \l "_Toc258332384" 9.2 Format 1 : la sous-requête renvoie un seul résultat PAGEREF _Toc258332384 \h 84
HYPERLINK \l "_Toc258332385" 9.3 Format 2 : la sous-requête renvoie plusieurs résultats PAGEREF _Toc258332385 \h 86
HYPERLINK \l "_Toc258332386" 9.4 La requête renvoie vrai ou faux PAGEREF _Toc258332386 \h 87
HYPERLINK \l "_Toc258332387" 9.5 La sous requête renvoie un agrégat PAGEREF _Toc258332387 \h 89
HYPERLINK \l "_Toc258332388" 9.6 Les opérateurs ANY, ALL PAGEREF _Toc258332388 \h 90
HYPERLINK \l "_Toc258332389" 9.7 Mise à jour en fonction d'une sous-requête PAGEREF _Toc258332389 \h 95
HYPERLINK \l "_Toc258332390" 9.8 Insert contrôlé PAGEREF _Toc258332390 \h 96
HYPERLINK \l "_Toc258332391" 9.8.1 TP PARISCOPE sur les requêtes imbriquées PAGEREF _Toc258332391 \h 97
HYPERLINK \l "_Toc258332392" Chapitre 10 LES TABLEAUX CROISES DYNAMIQUES PAGEREF _Toc258332392 \h 98
HYPERLINK \l "_Toc258332393" 10.1 Sur une table PAGEREF _Toc258332393 \h 98
HYPERLINK \l "_Toc258332394" 10.2 Sur une jointure statique PAGEREF _Toc258332394 \h 100
HYPERLINK \l "_Toc258332395" 10.3 Sur une jointure dynamique PAGEREF _Toc258332395 \h 101
HYPERLINK \l "_Toc258332396" Chapitre 11 LES TRANSACTIONS PAGEREF _Toc258332396 \h 102
HYPERLINK \l "_Toc258332397" 11.1 Principes PAGEREF _Toc258332397 \h 102
HYPERLINK \l "_Toc258332398" 11.2 Validation PAGEREF _Toc258332398 \h 103
HYPERLINK \l "_Toc258332399" 11.3 Annulation PAGEREF _Toc258332399 \h 103
HYPERLINK \l "_Toc258332400" 11.4 Les savepoints PAGEREF _Toc258332400 \h 104
HYPERLINK \l "_Toc258332401" 11.5 Le verrouillage de table PAGEREF _Toc258332401 \h 105
HYPERLINK \l "_Toc258332402" 11.6 Le verrouillage de ligne PAGEREF _Toc258332402 \h 106
HYPERLINK \l "_Toc258332403" Chapitre 12 LES VUES (VIEWS) PAGEREF _Toc258332403 \h 107
HYPERLINK \l "_Toc258332404" 12.1 Création, suppression, modification PAGEREF _Toc258332404 \h 108
HYPERLINK \l "_Toc258332405" 12.1.1 Création PAGEREF _Toc258332405 \h 108
HYPERLINK \l "_Toc258332406" 12.1.2 Suppression PAGEREF _Toc258332406 \h 108
HYPERLINK \l "_Toc258332407" 12.1.3 Modification PAGEREF _Toc258332407 \h 108
HYPERLINK \l "_Toc258332408" 12.2 Les vues et les mises à jour PAGEREF _Toc258332408 \h 109
HYPERLINK \l "_Toc258332409" 12.3 TP sur les views PAGEREF _Toc258332409 \h 111
HYPERLINK \l "_Toc258332410" Chapitre 13 OPTIMISATION PAGEREF _Toc258332410 \h 112
HYPERLINK \l "_Toc258332411" 13.1 Indexation PAGEREF _Toc258332411 \h 113
HYPERLINK \l "_Toc258332412" 13.2 Explain PAGEREF _Toc258332412 \h 114
HYPERLINK \l "_Toc258332413" 13.2.1 La commande PAGEREF _Toc258332413 \h 114
HYPERLINK \l "_Toc258332414" 13.2.2 Un select PAGEREF _Toc258332414 \h 114
HYPERLINK \l "_Toc258332415" 13.2.3 Explain et les index PAGEREF _Toc258332415 \h 115
HYPERLINK \l "_Toc258332416" 13.2.4 Explain et les jointures PAGEREF _Toc258332416 \h 117
HYPERLINK \l "_Toc258332417" 13.2.5 Explain et les requêtes imbriquées PAGEREF _Toc258332417 \h 124
HYPERLINK \l "_Toc258332418" 13.3 ANALYZE TABLE PAGEREF _Toc258332418 \h 125
HYPERLINK \l "_Toc258332419" Chapitre 14 DIVERS PAGEREF _Toc258332419 \h 126
HYPERLINK \l "_Toc258332420" 14.1 Les événements (> 5.1) PAGEREF _Toc258332420 \h 126
HYPERLINK \l "_Toc258332421" 14.2 Quelques éléments meta-basiques PAGEREF _Toc258332421 \h 127
HYPERLINK \l "_Toc258332422" 14.2.1 La commande SHOW PAGEREF _Toc258332422 \h 127
HYPERLINK \l "_Toc258332423" Chapitre 15 LE MODELE RELATIONNEL DE CODD PAGEREF _Toc258332423 \h 128
HYPERLINK \l "_Toc258332424" 15.1 Les domaines, les relations et les attributs. PAGEREF _Toc258332424 \h 128
HYPERLINK \l "_Toc258332425" 15.1.1 Domaine PAGEREF _Toc258332425 \h 128
HYPERLINK \l "_Toc258332426" 15.1.2 Attribut PAGEREF _Toc258332426 \h 128
HYPERLINK \l "_Toc258332427" 15.1.3 Relation PAGEREF _Toc258332427 \h 129
HYPERLINK \l "_Toc258332428" 15.1.4 BD R PAGEREF _Toc258332428 \h 129
HYPERLINK \l "_Toc258332429" 15.2 Les dépendances fonctionnelles élémentaires, clés candidates, clé primaire. PAGEREF _Toc258332429 \h 130
HYPERLINK \l "_Toc258332430" 15.2.1 Les dépendances fonctionnelles (DF) PAGEREF _Toc258332430 \h 130
HYPERLINK \l "_Toc258332431" 15.2.2 Clés candidates PAGEREF _Toc258332431 \h 130
HYPERLINK \l "_Toc258332432" 15.2.3 Clé primaire PAGEREF _Toc258332432 \h 131
HYPERLINK \l "_Toc258332433" 15.2.4 Clé étrangère PAGEREF _Toc258332433 \h 131
HYPERLINK \l "_Toc258332434" 15.3 La valeur NULL, l'intégrité d'entité, l'intégrité référentielle. PAGEREF _Toc258332434 \h 132
HYPERLINK \l "_Toc258332435" 15.3.1 La valeur NULL PAGEREF _Toc258332435 \h 132
HYPERLINK \l "_Toc258332436" 15.3.2 L'intégrité d'entité PAGEREF _Toc258332436 \h 132
HYPERLINK \l "_Toc258332437" 15.3.3 L'intégrité référentielle PAGEREF _Toc258332437 \h 132
HYPERLINK \l "_Toc258332438" 15.4 La normalisation des données PAGEREF _Toc258332438 \h 133
HYPERLINK \l "_Toc258332439" 15.4.1 Première forme normale (1ère FN) PAGEREF _Toc258332439 \h 133
HYPERLINK \l "_Toc258332440" 15.4.2 Deuxième forme normale (2ème FN) PAGEREF _Toc258332440 \h 134
HYPERLINK \l "_Toc258332441" 15.4.3 Troisième forme normale (3ème FN) PAGEREF _Toc258332441 \h 135
HYPERLINK \l "_Toc258332442" 15.5 Les opérateurs de l'algèbre relationnelle PAGEREF _Toc258332442 \h 136
HYPERLINK \l "_Toc258332443" 15.5.1 Exemples d'opérations de l'AR PAGEREF _Toc258332443 \h 137
HYPERLINK \l "_Toc258332444" 15.6 TP modèle relationnel PARISCOPE PAGEREF _Toc258332444 \h 138
HYPERLINK \l "_Toc258332445" 15.6.1 Enoncé PAGEREF _Toc258332445 \h 138
HYPERLINK \l "_Toc258332446" 15.6.2 Corrigé PAGEREF _Toc258332446 \h 139
HYPERLINK \l "_Toc258332447" 15.7 TP algèbre relationnelle PARISCOPE PAGEREF _Toc258332447 \h 140
HYPERLINK \l "_Toc258332448" 15.7.1 Enoncés PAGEREF _Toc258332448 \h 140
HYPERLINK \l "_Toc258332449" 15.7.2 Corrigés PAGEREF _Toc258332449 \h 141
HYPERLINK \l "_Toc258332450" Chapitre 16 ANNEXES PAGEREF _Toc258332450 \h 142
HYPERLINK \l "_Toc258332451" 16.1 La BD COURS PAGEREF _Toc258332451 \h 142
HYPERLINK \l "_Toc258332452" 16.1.1 Schéma PAGEREF _Toc258332452 \h 142
HYPERLINK \l "_Toc258332453" 16.1.2 Script de création des tables PAGEREF _Toc258332453 \h 143
HYPERLINK \l "_Toc258332454" 16.1.3 Script d'insertion des données PAGEREF _Toc258332454 \h 146
HYPERLINK \l "_Toc258332455" 16.2 La BD PARISCOPE PAGEREF _Toc258332455 \h 148
HYPERLINK \l "_Toc258332456" 16.2.1 Schéma PAGEREF _Toc258332456 \h 148
HYPERLINK \l "_Toc258332457" 16.2.2 Corrigés PAGEREF _Toc258332457 \h 149
HYPERLINK \l "_Toc258332458" 16.2.2.1 TP Pariscope sur les objets tables PAGEREF _Toc258332458 \h 149
HYPERLINK \l "_Toc258332459" 16.2.2.2 TP Pariscope sur les clés étrangères PAGEREF _Toc258332459 \h 153
HYPERLINK \l "_Toc258332460" 16.2.2.3 TP Pariscope sur les index PAGEREF _Toc258332460 \h 155
HYPERLINK \l "_Toc258332461" 16.2.2.4 TP Pariscope sur les modifications de table PAGEREF _Toc258332461 \h 155
HYPERLINK \l "_Toc258332462" 16.2.3 SQL Insertion des données dans Pariscope PAGEREF _Toc258332462 \h 156
HYPERLINK \l "_Toc258332463" 16.2.4 Corrigés MAJ PARISCOPE PAGEREF _Toc258332463 \h 158
HYPERLINK \l "_Toc258332464" 16.3 TP Extraction des données PAGEREF _Toc258332464 \h 159
HYPERLINK \l "_Toc258332465" 16.3.1 Corrigés PAGEREF _Toc258332465 \h 159
HYPERLINK \l "_Toc258332466" 16.4 La BD Ingénieurs PAGEREF _Toc258332466 \h 163
HYPERLINK \l "_Toc258332467" 16.4.1 Le schéma PAGEREF _Toc258332467 \h 163
HYPERLINK \l "_Toc258332468" 16.4.2 La création du schéma et l'insertion des données PAGEREF _Toc258332468 \h 163
HYPERLINK \l "_Toc258332469" 16.5 La BD Ingénieurs Light PAGEREF _Toc258332469 \h 164
HYPERLINK \l "_Toc258332470" 16.5.1 Schéma PAGEREF _Toc258332470 \h 164
HYPERLINK \l "_Toc258332471" 16.5.2 Script de création et d'insertions dans BD_INGENIEURS_LIGHT PAGEREF _Toc258332471 \h 165
HYPERLINK \l "_Toc258332472" 16.6 Bibliographie PAGEREF _Toc258332472 \h 167
HYPERLINK \l "_Toc258332473" 16.7 Quelques corrigés PAGEREF _Toc258332473 \h 168
INTRODUCTION
Pour supprimer le service Mysql : sc delete MySQL dans une VDM DOS.
Bases utilisées
Cours : cours.
TP : pariscope, bd_light_ingenieurs, bd_ingenieurs.
Les schémas et scripts de création des bases et d'insertion des données sont en fin de support o u sous forme de fichiers externes.
Définition d'un SGBDR
Un SGBDR est un Système de Gestion de bases de Données Relationnelles.
C'est-à-dire un ensemble de logiciels capable de gérer une base de données relationnelles.
Une base de données est un ensemble de tables bien souvent reliées entre elles (il peut exister des tables paramètre qui sont complètement indépendantes) qui modélisent un domaine du SI d'une organisation.
Les tables sont composées de colonnes (les champs) et de lignes (les enregistrements).
Une table comprend une clé primaire, composée d'un ou plusieurs champs; celle-ci permet d'identifier chaque enregistrement; chaque valeur est unique. La clé primaire doit être renseignée (Elle est NOT NULL). Elle est indexée.
Une table peut comprendre zéro, une ou plusieurs clé(s) étrangère(s) qui sont des champs correspondant à une clé primaire dans une autre table. Une clé étrangère dans une table (enfant) permet de faire un lien vers une autre table (une table parent). Mais c'est aussi une contrainte, dans la mesure où les valeurs de la clé primaire de la table parent sont références pour la table enfant. On parle de contrainte d'intégrité référentielle.
Le R signifie relationnel parce que l'implémentation des SGBDR dépend du modèle relationnel de CODD qui a aussi créé une algèbre relationnelle dont est issu le langage standard d'interrogation des BDR, le SQL (Structured Query Language).
MySQL est OpenSource et Propriétaire. Il existe donc deux licences.
INSTALLATION et CONFIGURATION
Fichiers à télécharger
Il existe deux possibilités :
Soit installer un LAMP (Linux, Apache, MySQL, PHP), WAMP ou MAMP. Un LAMP install automatiquement une serveur HHTPd (Apache), module PHP, un SGBDR (MySQL) et une interface d'amininistration et de requêtage (PHPMyAdmin).
Soit installer des produits séparément (MySQL, MySQL Query Browser, MySQL Administrator).
WAMP5
HYPERLINK "http://www.wampserver.com/" http://www.wampserver.com/
EasyPHP
HYPERLINK "http://www.easyphp.org/" http://www.easyphp.org/
Xampp
HYPERLINK "http://www.apachefriends.org/fr/xampp-windows.html" http://www.apachefriends.org/fr/xampp-windows.html
Autres
LAMP sous Linux.
MAMP sous Mac.
MySQL
L'installation séparée de MySQL consiste à n'installer que le SGBDR MySQL.
Il est possible de ne travailler qu'avec l'utilitaire mysql en mode commande, mais vous pouvez dans le cadre de cette formation initiale installer MySQL Qurey Browser qui est une interface client lourd multi-plateforme.
D'autres outils existent AGL (Atelier de Génie Logiciel), Outils de migration,
, pilotes pour des connections applicatives, références hors-ligne.
La liste correspond aux versions à une certaine date. Les versions évoluent rapidement.
Sites :
HYPERLINK "http://www-fr.mysql.com/"http://www-fr.mysql.com/
HYPERLINK "http://dev.mysql.com/downloads/" http://dev.mysql.com/downloads/
Tableau des téléchargements possibles (non exhaustifs et versions non définitives).
FonctionFichierMYSQLmysql-5.1.40-win32.msiInterface d'administration (MySQL Administrator),
Interface d'interrogation (MySQL Query Browser),
Interface de migration (MySQL Migration Toolkit).mysql-gui-tools-noinstall-5.0-r15-win32.zipMySQL WorkBenchConcepteur graphique de BD.
Nécessite sous Windows le framework .NET 2.0 minimum.DBDesignerConcepteur graphique de BD (Obsolète mais intéressant dans la mesure où il possède un QBE).Connecteur ODBC 5.1mysql-connector-odbc-5.1.5-win32.msiConnecteur JDBC mysql-connector-java-5.1.7.zipConnecteur PHP php_mysqli.dll for PHP 5.2.1Connecteur .NETmysql-connector-net-5.2.5.zipLa référence en français (> 1500 pages)mysql_5_reference_fr.pdfLe fichier d'aide (format Winhelp) HYPERLINK "http://downloads.mysql.com/docs/refman-5.0-fr.chm" http://downloads.mysql.com/docs/refman-5.0-fr.chm
ou
HYPERLINK "http://www.placeoweb.com/chm/" http://www.placeoweb.com/chm/
Installations
Installation du SGBDR
Au moins quatre possibilités avec Windows :
MYSQL : installation rapide et assistée (Cf support Administration).
EasyPHP : installation rapide et assistée (WAMP).
WAMP5 : installation rapide et assistée (WAMP).
XAMPP : installation rapide et assistée (WAMP).
Installation des outils
PhpMyAdmin
Avec EasyPHP, WAMP et XAMPP l'outil PHPMyAdmin est fourni.
Pour l'installer de façon indépendante, phpMyAdmin-3.2.3-all-languages.zip est téléchargeable à HYPERLINK "http://www.phpmyadmin.net/home_page/downloads.php" http://www.phpmyadmin.net/home_page/downloads.php
MysqlQueryBrowser de MySQL AB
Mais il existe un outil plus convivial : MySQL Query Browser (pour Windows, Mac et Linux). C'est un utilitaire gratuit édité par MySQL.
DBDesignor de FabForge.net
AGL pour la conception de la BD (AGL de conception, reverse engineering, QBE).
MySQL Workbench de Mysql AB
AGL pour la conception de la BD (AGL de conception, reverse engineering, QBE).
MysqlAministrator de Mysql AB
Outil d'administration des BD.
Le MySQL Query Browser
MySQL Query Browser est un client lourd graphique (à la différence du client léger PHPMyADMIN) qui permet de :
Créer, modifier et supprimer des bases de données,
Créer, modifier et supprimer des tables.
Créer, visualiser, modifier et supprimer des enregistrements.
Créer et modifier des procédures et fonctions stockées.
Pour de plus amples détails cf le support MySQL Query Browser.
Connexion
EMBED PBrush
Interface
Ligne de commande Shémas Signets Histoique des commandes
Rafraîchissement Exécution
Espace Résultats Syntaxe et aide Fonctions
Création d'une base
Cliquez droit dans le volet de droite nommé Schemata.
Et validez sur Create New Schéma.
Nommez la nouvelle base.
Le jeu de caractères et la collation sont définis par défaut. Sa modification s'effectue en mode commande. Cf plus loin.
Suppression d'une base de données.
Pour supprimer une base de données il faut cliquer droit dans l'espace shemata sur l'objet et sélectionner soit Drop Schema.
C'est irréversible.
Création d'une table
Permet de créer une table avec des champs typés.
La clé primaire est automatiquement le premier champ. Cela est modifiable par clic.
La table Pays par exemple
pays (id_pays, nom_pays)
Champs de la table
Type de table (Moteur) et jeu de caractères.
Le moteur InnoDB permet la gestion des clés étrangères, la gestion des transactions et le verrouillage au niveau des lignes de tables.
Ce moteur est plutôt utilisé pour l'insertion et la mise à jour.
Le moteur MyISAM ne pas permet ces gestions.
Ce moteur est plutôt utilisé pour la consultation de données, pour DataWarehouse.
C'est un moteur plus rapide.
Les jeux de caractères permettent de préciser les "alphabets" que l'on va utiliser (Les caractères accentués
).
Pour les jeux de caractères soyez attentif aussi à ceux des colonnes.
La collation détermine l'algorthime de comparaison donc de tri ou de comparaison pour les jointures.
Création d'une clé étrangère
Il faut au préalable créer la table parent pays id_pays, nom_pays).
Ensuite créer la table enfant villes cp, nom_ville, id_pays).
Les tables doivent être de moteur InnoDB.
Il est préférable que la colonne clé étrangère soit NOT NULL (id_pays de villes).
Il est préférable d'indexer la future colonne étrangère (id_pays de villes).
Pour modifier la strucrure d'une table appuyez sur la touche F2.
La table Villes (et son parent la table Pays)
Dans l'onglet Foreign Keys, cliquez sur +, nommez la clé étrangère, sélectionnez la table parent, reliez les colonnes parent et enfant.
Les options de DELETE et de UPDATE sont au nombre de 4 :
Cascade : supprime ou modifie en cascade,
No Action : même effet que restrict,
Set Null : affecte la valeur NULL; possible si la clé étrangère est positionnée à NULL,
Restrict : interdit la suppression du parent si au mois un enfant existe.
Modification, suppression d'une table
Pour modifier ou visualiser la structure d'une table il faut la sélectionner, cliquer droit et sélectionner Edit Table.
Ou la sélectionnez et F2.
EMBED MSPhotoEd.3
Pour supprimer une table il faut cliquer droit sur l'objet et sélectionner Drop table.
C'est irréversible.
Travailler en mode ligne de commandes
Avec MySQL Query Browser
Vous sélectionnez une base de données. Vous cliquez droit et vous sélectionnez "Make Default Schema".
Vous saisissez la commande SQL dans le bandeau du haut : SELECT cp, nom_ville FROM villes;
Vous vous assurez que vous êtes dans la ligne de commande.
Vous cliquez sur le bouton Execute.
EMBED MSPhotoEd.3
Les résultats s'affichent en-dessous.
EMBED MSPhotoEd.3
Note : vous pouvez aussi cliquer/glisser la table vers le volet "Résultats"
Travailler avec l'utilitaire mysql
Se connecter au serveur
Mysql h serveur u utilisateur p
Exemples
C:\
\mysql\bin>mysql --host=localhost --user=root --password= --database=cours
ou
C:\
\mysql\bin>mysql -h localhost -u root -D cours p
ou
C:\
\mysql\bin>mysql h localhost u root -p
Note : le mode raccourci ne fonctionne pas avec toutes les versions.
Ensuite il vous sera demandé de saisir le mot de passe.
Enfin vous êtes dans l'interface MySQL avec son prompt.
Lister les bases de données du serveur
Mysql>show databases;
Pour sélectionner une base
Mysql>use nom_de_la_bd;
Exemple
MySQL>use mysql;
Puis les commandes SQL
Mysql>SELECT * FROM user;
Lister les tables
Mysql>show tables;
Lister la structure d'une table
Desc nom_de_table;
Exemple
dans la base MySQL
Desc user;
Pour sortir
Exit;
Travailler Avec PHPMyADMIN
Au niveau de la base de données cliquez sur EMBED MSPhotoEd.3
LES OBJETS MySQL
Le LDD
Le LDD (Data Definition Language ou Langage de Définition de Données) permet de créer, modifier ou supprimer les objets.
Les objets MySQL sont les bases de données, les users, les tables, les contraintes, les index, les views,
Il comprend 3 verbes :
CREATE pour créer un objet,
ALTER pour modifier un objet,
DROP pour supprimer un objet.
Les bases de données
Création
Syntaxe
CREATE DATABASE [IF NOT EXISTS] nom_de_base
[DEFAULT CHARACTER SET jeu_de_caractères
COLLATE collation];
Exemples
CREATE DATABASE IF NOT EXISTS base_exos;
CREATE DATABASE IF NOT EXISTS base_exos
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;
USE base_exos;
Autres jeux de caractères :
CREATE DATABASE bd1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE bd2 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Notes :
Un jeu de caractères est un ensemble de symboles et de codes.
Une collation est un ensemble de règles permettant la comparaison de caractères dans un jeu.
utf8 : un encodage UTF-8 encode un ensemble de caractères Unicode sur 1, 2 ou octets.
Il permet le stockage de textes de 650 langages.
La collation general_ci est basée sur un algorithme simple et rapide de comparaison.
La collation unicode_ci sur un algoritme plus complexe.
Cf : HYPERLINK "http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html" http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html
Utf8 : un encodage Utf8, correspond à la norme ISO 8859-1 (Alphabet de l'Europe occidentale). C'est une table de 191 caractères. Elle comprend les caractères accentués du français.
La collation pour le français est utf8_general_ci ou utf8_general_cs.
Attention, par défaut de nombreuses plateformes MySQL sont en utf8 collation utf8_swedish_ci.
Modification d'une base de données
Syntaxe
ALTER DATABASE nom_de_base DEFAULT CHARACTER SET jeu COLLATE collation;
Exemple
Modification de la collation du jeu de caractères.
ALTER DATABASE base_exos DEFAULT CHARACTER SET utf8 COLLATE utf8_general_cs;
Supression d'une base de données
DROP DATABASE nom_de_base;
DROP DATABASE base_exos;
Les tables
Les types de données.
Les grandes catégories de types de données sont :
Numériques
Caractères
Dates
Binaires
Ensemble (Enumeration ou Set)
La taille de l'affichage est paramétrable avec l'attribut (M).
Les types numériques peuvent être signés ou non (UNSIGNED).
Ils peuvent aussi être complétés par des 0 (ZEROFILL).
TypeSous-typesOctetsExtensionsNumériquesTinyInt10 à 255 ou 127 à 128Smallint2-32 768 à 32 767 ou 0 à 65 535Mediumint3-8 388 608 à 8 388 607 ou 0 à 16 777 215Int[(M)]4-2 147 483 648 à 2 147 483 647
Bigint80 à 18 446 744 073 709 551 615Float,
Float(M,D)4Ce type de données permet de stocker des nombres flottants à précision simple. Va de -1.175494351E-38 à 3.402823466E+38
Float est à éviter (Les calculs renvoyés sont incertains)
Float(M,D); M pour le nombre de chiffres à afficher, D pour le nombre de chiffres décimaux.Double ,
Double(M,D) 8Stocke des nombres flottants à double précision de -1.7976931348623157E+308 à -2.2250738585072014E-308, 0, et de 2.2250738585072014E-308 à 1.7976931348623157E+308.DateDate3'AAAA-MM-JJ'Time3'HH:MM:SS'Datetime8'AAAA-MM-JJ HH:MM:SS'Timestamp4Year1CaractèresChar(N)N255 caractères maximumVarchar(N)N255 caractères maximum (MySQL convertit en TEXT ou MEDUIMTEXT si vous dépassez 255)BinaireTinyBlob255Blob65 535Mediumblob16 MoOu 16 777 215 d'octetsLongblob4 GoOu 4 294 967 295 d'octetsTextesTinyText255Text65 535MediumText16 MoOu 16 777 215 d'octetsLongText4 GoOu 4 294 967 295 d'octetsEnumérationEnum('valeur1','valeur2',...)Une chaîne qui peut prendre une valeur, sélectionnée parmi une liste 'valeur1', 'valeur2', ..., NULL ou la valeur spéciale d'erreur "". 65535 valeurs distinctes sont autorisées. Donc un choix unique et obligatoire dans la liste autrement une erreur survient et une chaîne vide est affectée.
L'interface sera une liste déroulante ou des boutons radio.EnsembleSet('valeur1' , 'valeur2',...)Une chaîne, qui peut prendre zéro, une ou plusieurs valeurs, choisies parmi une liste de valeurs 'valeur1', 'valeur2', ... Une valeur SET peut avoir un maximum de 64 membres. Donc choix multiple facultatif.
L'interface sera une liste à choix multiples ou des cases à cocher.
Notes :
Pour les chaînes :
L'attribut BINARY signifie que les valeurs sont classées et triées en tenant compte de la casse, suivant l'ordre des caractères ASCII de la machine.
L'attribut ASCII peut être spécifiée avec, pour assigner le jeu de caractère utf8 à une colonne de type CHAR.
L'attribut UNICODE peut être spécifié pour assigner le jeu de caractères ucs2 à une colonne CHAR.
Pour les numériques :
UNSIGNED signifie non signé,
ZEROFILL signifie complété par des 0 et est de fait UNSIGNED.
Création d'une table
Syntaxe
CREATE TABLE nom_de_table(
nom_de_colonne TYPE CONTRAINTE
[, col2
,
[Contrainte de table]])
[ENGINE moteur_de_table]
[DEFAULT CHARSET=jeu_de_caractères COLLATE=collation];
Exemple de base (sans contrainte)
CREATE TABLE villes (cp CHAR(5), nom_ville VARCHAR(50));
Avec une clé primaire, un auto_increment et un moteur spécifique
CREATE TABLE clients (
id_client INT(5) NOT NULL AUTO_INCREMENT ,
nom VARCHAR(50) NOT NULL ,
prenom VARCHAR(50) NULL ,
adresse VARCHAR(100) NULL ,
date_naissance DATE NULL ,
cp CHAR(5) NOT NULL ,
PRIMARY KEY ( id_client )
) ENGINE = InnoDB;
Avec en plus un test d'existence et un jeu de caractères spécifique
CREATE TABLE IF NOT EXISTS clients (
id_client INT(5) NOT NULL AUTO_INCREMENT ,
nom VARCHAR(50) NOT NULL ,
prenom VARCHAR(50) NULL ,
adresse VARCHAR(100) NULL ,
date_naissance DATE NULL ,
cp CHAR(5) NOT NULL ,
PRIMARY KEY (id_client)
) ENGINE = InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Avec une énumération (Choix obligatoire parmi la liste de valeurs)
connu_par ENUM('Internet','Bouche à oreille','Presse') default NULL,
Avec un ensemble (Choix facultatif dans la liste, le choix peut être multiple)
lectures SET('Romans','Essais','Scolaires','Art','Techniques') default NULL,
Les contraintes
Les contraintes prises en charge par MySQL 5 sont (D'autres sont acceptées mais non validées : palges,
) :
NOT NULL,
PRIMARY KEY,
INDEX.
Exemple avec des colonnes NOT NULL
CREATE TABLE villes (cp CHAR(5) NOT NULL, nom_ville VARCHAR(50) NOT NULL, site VARCHAR(50) NULL , photo VARCHAR(50) NULL , id_pays CHAR(3) NOT NULL);
Exemple avec la création d'une clé primaire.
CREATE TABLE villes
(
cp CHAR(5) NOT NULL ,
nom_ville VARCHAR(50) NOT NULL ,
site VARCHAR(50) NULL ,
photo VARCHAR(50) NULL ,
id_pays CHAR(3) NOT NULL ,
PRIMARY KEY (cp)
)
ENGINE = InnoDB;
Exemple de création d'une table avec une clé primaire et une colonne indexée
CREATE TABLE villes (
cp CHAR(5) NOT NULL ,
nom_ville VARCHAR(50) NOT NULL ,
site VARCHAR(50) NULL ,
photo VARCHAR(50) NULL ,
PRIMARY KEY (cp) ,
INDEX (nom_ville)
) ENGINE = InnoDB;
Création d'une table à partir d'une autre table
CREATE TABLE table_a_creer
AS SELECT * | colonnes FROM table_source | jointure [WHERE condition];
CREATE TABLE villes_bis AS SELECT * FROM villes;
EMBED MSPhotoEd.3 Seule la structure de base est copiée. Les clés
sont omises dans la copie.
Le schéma de la base COURS
Cf les annexes
Renommage d'une table
Syntaxe
ALTER TABLE ancien_nom RENAME TO nouveau_nom;
Exemple
ALTER TABLE villes_bis RENAME TO villes_2;
Suppression d'une table
Syntaxe
DROP TABLE [IF EXISTS] nom_de_table;
Exemple
DROP TABLE villes_2;
Modification de la structure d'une table
Syntaxes
Pour ajouter une colonne ou une contrainte.
ALTER TABLE nom_de_table ADD
[, ADD
]
Pour supprimer une colonne.
ALTER TABLE nom_de_table DROP nom_de_colonne [, DROP nom_de_colonne];
Pour modifier une colonne ou une contrainte.
ALTER TABLE nom_de_table CHANGE
Exemple : ajout d'une clé primaire
CREATE TABLE villes (
cp CHAR(5) NOT NULL ,
nom_ville VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
ALTER TABLE villes ADD PRIMARY KEY (cp);
Exemple : ajout d'une colonne
ALTER TABLE villes ADD id_pays CHAR(3) NOT NULL;
Exemple : suppression d'une colonne
ALTER TABLE villes DROP id_pays;
Exemple : ajout d'un index
Cf plus loin
Exemple : modification d'un type
ALTER TABLE villes CHANGE id_pays id_pays INT(3) NOT NULL;
Exemple : modification d'une contrainte (NOT NULL -> NULL)
ALTER TABLE villes CHANGE id_pays id_pays CHAR(3) NULL;
Et inversement (Si aucune données n'est dans la table)
ALTER TABLE villes CHANGE id_pays id_pays CHAR(3) NOT NULL;
Autre syntaxe pour la modification d'une colonne
ALTER TABLE nomDeTable MODIFY COLUMN nomDeColonne Type Contrainte;
Exemple
ALTER TABLE villes MODIFY COLUMN id_pays CHAR(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
Ou
ALTER TABLE villes MODIFY COLUMN id_pays CHAR(3) NOT NULL;
Les contraintes (Constraint)
Création d'une contrainte
Syntaxe
ALTER TABLE nom_de_table
ADD CONSTRAINT nom_contrainte Contrainte [, ADD CONSTRAINT
];
Note : bien entendu une contrainte peut être créée lors de la création de la table comme nous l'avons vu précédemment.
Admettons la table suivante STAGIAIRES(#id_stagiaire, nom, statut, #cp).
CREATE TABLE stagiaires(id_stagiaire INT(5), nom VARCHAR(50), age INT, cp CHAR(5)) ENGINE = innoDB;
Clé primaire
ALTER TABLE stagiaires
ADD CONSTRAINT pk_stagiaires
PRIMARY KEY (id_stagiaire);
Clé étrangère
ALTER TABLE stagiaires
ADD CONSTRAINT fk_stagiaires_cp
FOREIGN KEY (cp)
REFERENCES villes(cp)
ON DELETE CASCADE ON UPDATE CASCADE;
Valeur unique
ALTER TABLE stagiaires
ADD CONSTRAINT stagiaires_nom_u
UNIQUE (nom);
Validité (Plage,
) MySQL ne supporte pas encore ce type de contrainte.
ALTER TABLE stagiaires ADD CONSTRAINT cst_age CHECK age BETWEEN 18 AND 25;
Cf le type ENUM.
Clé étrangère réflexive
ALTER TABLE vendeurs ADD CONSTRAINT FK_vendeurs_id_vendeur FOREIGN KEY FK_vendeurs_id_vendeur (chef)
REFERENCES vendeurs (id_vendeur)
ON DELETE RESTRICT
ON UPDATE RESTRICT
, ROW_FORMAT = DYNAMIC;
Suppression d'une contrainte
ALTER TABLE nom_de_table DROP TYPE_DE_CONTRAINTE [nom_de_contrainte];
ALTER TABLE stagiaires DROP PRIMARY KEY;
ALTER TABLE stagiaires DROP FOREIGN KEY fk_stagiaires_cp;
ALTER TABLE stagiaires DROP INDEX stagiaires_nom_u;
Les index (Index)
Création d'un index
Un index est un accélérateur.
Un index peut être créé dans l'instruction de création de table (cela permet d'en créer plusieurs) ou via Create Index (Un seul à la fois).
Un index peut s'appliquer à un champ ou plusieurs.
Un index peut être appliqué à un champ scalaire.
Un index peut être UNIQUE (Mais accepte les valeurs NULL, à la différence des Primary Key) ou NOT UNIQUE.
Un index peut-être appliqué à un préfixe de colonne (colonne(n)).
Un index peut être appliqué à un texte long (FULL TEXT).
Syntaxe de CREATE INDEX.
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX nom_d_index ON nom_de_table (nom_de_colonne[(n)],...)
FULLTEXT précise que l'index, même sur les textes longs, doit être créé sur toute la valeur du texte.
SPATIAL indexe les colonnes dont les types sont géométriques.
Exemple
CREATE INDEX i_villes_ville ON villes(nom_ville);
CREATE INDEX i_clients_nom_prenom ON clients(nom, prenom);
CREATE INDEX i_nom_ville ON villes(nom_ville(5)); -- Préfixe de colonne
Syntaxe de ALTER TABLE
ADD INDEX
ALTER TABLE nom_de_table ADD INDEX [nom_d_index] (nom_de_colonne [(n)] [, nom_de_colonne]);
Exemples
ALTER TABLE villes ADD INDEX i_villes_nom_ville (nom_ville); -- Un index
ALTER TABLE villes ADD INDEX i_villes_nom_ville (nom_ville), add index i_villes_id_pays(id_pays); -- Deux index
ALTER TABLE villes ADD INDEX i_villes_nom_ville_id_pays (nom_ville,id_pays); -- Un index sur 2 colonnes
Suppression d'un index
Syntaxe
DROP INDEX nom_d_index ON nom_de_table;
Exemple
DROP INDEX ind_villes_ville ON villes;
Syntaxe
ALTER TABLE nom_de_table DROP INDEX nom_d_index;
Exemple
ALTER TABLE villes DROP INDEX ind_villes_nom_ville;
Visualisation des Index
Via la structure de la table
Mysql>SHOW CREATE TABLE nom_de_table \G
mysql> SHOW CREATE TABLE villes \G
Table: villes
Create Table: CREATE TABLE villes (
cp varchar(5) NOT NULL,
nom_ville varchar(50) NOT NULL,
site varchar(50) DEFAULT NULL,
photo varchar(50) DEFAULT NULL,
id_pays char(3) DEFAULT NULL,
PRIMARY KEY (cp),
KEY Index_id_pays (id_pays)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
Via la structure des index d'une table
SHOW INDEX FROM nom_de_table \G
mysql> SHOW INDEX FROM villes \G
*************************** 1. row ***************************
Table: villes
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: cp
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
*************************** 2. row ***************************
Table: villes
Non_unique: 1
Key_name: Index_id_pays
Seq_in_index: 1
Column_name: id_pays
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Index sur préfixe
Les index sur préfixe sont plus rapides.
CREATE INDEX i_nom_ville ON villes(nom_ville(5));
Table: villes
Non_unique: 1
Key_name: i_nom_ville
Seq_in_index: 1
Column_name: nom_ville
Collation: A
Cardinality: NULL
Sub_part: 5
Packed: NULL
Null:
Index_type: BTREE
Notes :
Les index par défaut sont de type B-TREE (arbre balancé).
Les index FULLTEXT ne peuvent indexer que des colonnes VARCHAR ou TEXT, et seulement dans les tables MyISAM
Les index SPATIAL peuvent indexer les colonnes spatiales, et uniquement avec les tables MyISAM.
Si une base de données est suffisamment petite pour tenir en mémoire, alors le plus rapide pour faire des requêtes est d'utiliser les index hash.
R-trees are tree data structures that are similar to B-trees, but are used for spatial access methods i.e., for indexing multi-dimensional information
Utilisation des index
Par défaut les index sont utilisés avec un SELECT comportant une clause WHERE sur une colonne indexée.
Pour les désactiver vous pouvez utiliser une fonction ou une opération neutre (+0, CONCAT '',
).
Ou utiliser une clause d'usage d'index :
USE INDEX, IGNORE INDEX et FORCE INDEX affectent les index qui sont utilisés lors du choix de la méthode de sélection des lignes dans la table lors d'une jointure.
Elles n'affectent pas l'utilisation de l'index dans les clauses ORDER BY ou GROUP BY.
SELECT * FROM villes USE INDEX (i_villes_nom_ville) where nom_ville = 'Lyon';
Ou
SELECT * FROM villes IGNORE INDEX (i_villes_nom_ville) where nom_ville = 'Lyon';
Cf explain.
TP PARISCOPE : Création et Manipulation des objets de la base
Enoncés
Les corrigés sont dans les annexes.
Créez les tables de la BD Pariscope.
Toutes les tables du modèle relationnel PARISCOPE (cf le modèle au chapitre au 15.6).
Créez les clés étrangères.
Toutes les clés étrangères.
Créez les index.
Titre du film dans Films.
Genre dans Genres.
Nom de l'acteur dans Acteurs.
Modifiez les tables.
Ajouter une colonne à la table cinemas : acces_handicapes.
Modifier le type de l'année du film (INT(4) vers CHAR(4)) et rétablissez ensuite.
Passez la colonne annee du film de NOT NULL à NULL.
Ajouter une contrainte sur l'année du film (entre 1900 et 2030) [KO].
MISE A JOUR DES DONNEES
Présentation
Il existe 4 verbes (CRUD : Create, Read, Update, Delete) du LMD (Langage de manipulation de données) dont 3 permettant la mise à jour des données :
INSERT pour ajouter un ou des enregistrements.
DELETE pour supprimer un ou des enregistrements.
UPDATE pour modifier un ou des enregistrements.
Pour visualiser les résultats des opérations de MAJ il faut savoir extraire les données d'une table; l'ordre SELECT le permet.
Exemple : SELECT * FROM villes;
qui visualisera l'ensemble des champs et des enregistrements de la table Villes.
Insertion de données (INSERT)
Première syntaxe
Insérer un enregistrement.
INSERT INTO nom_de_table [(col1, col2,
)]
VALUES (valeur1, valeur2,
);
Exemples
-- Insère le 21ème arrondissement
INSERT INTO villes(cp, nom_ville) VALUES('75021' , 'Paris 21');
-- Insertion dans une table ayant un champ de type ENUM.
-- Une interface graphique présentera des boutons radio.
INSERT INTO personnels (nom ,categorie)
VALUES ('Tintin', 'M');
INSERT INTO personnels (nom ,categorie)
VALUES ('Casta', 'C');
-- Insertion dans une table ayant un champ de type SET.
-- On insère la valeur de type SET entre ' (Quote simple).
-- Si plusieurs valeurs doivent être insérées on les sépare par une virgule.
-- Une interface graphique présentera une liste à choix multiples.
INSERT INTO personnes (nom ,hobbies ,cp)
VALUES ('Tintin', 'S', '75011');
INSERT INTO personnes (nom ,hobbies ,cp)
VALUES ('Haddock', 'C,T', '75011');
-- Insertion dans une table avec un auto_increment
-- Et récupération de la nouvelle valeur.
INSERT INTO clients(nom, prenom, cp)
VALUES('Tournesol','Bruno', '75011');
SELECT LAST_INSERT_ID();
Note : ne fonctionne pas avec MySQL Query Browser mais fonctionne sous le client Mysql.
Deuxième syntaxe
Insérer plusieurs enregistrements via un seul ordre INSERT (Syntaxe non standard propre à MySQL).
INSERT INTO nom_de_table(col1, col2,
)
VALUES (valeur1, valeur2,
) , (valeur3, valeur4)
;
Exemple
-- Insère Caen et Lille
INSERT INTO villes (cp , nom_ville)
VALUES
('14000', 'Caen'),
('59000', 'Lille');
Troisième syntaxe
Insérer un ou plusieurs enregistrements à partir d'un SELECT (Donc d'une autre table).
INSERT INTO table1 [(col1, col2,
)]
SELECT col1, col2,
FROM table2
;
Exemple
-- Insère dans la table villes_bis l'ensemble des enregistrements de la table Villes
INSERT INTO villes_bis
SELECT *
FROM villes;
Insertion de données (REPLACE)
Objectif
Insérer des données en remplaçant les données qui créent des confilts d'unicité.
C'est un équivalent d'un INSERT si la ligne n'existe pas et d'un DELETE + INSERT si la ligne existe. D'ailleurs le message de MySQL est soit "1 row affected"
soit "2 rows affected".
Syntaxes
REPLACE INTO nom_de_table(colonne1 [, colonne2])
VALUES(valeur1 [, valeur2]);
REPLACE INTO nom_de_table [(colonne1 [, colonne2])]
SELECT colonne1 [, colonne2]
FROM nom_de_table
[WHERE condition];
Exemple
REPLACE INTO villes(cp, nom_ville)
VALUES('75031','Paris XXXI');
Suppression de données (DELETE)
Syntaxe
DELETE FROM nom_de_table [WHERE condition];
Exemples
-- Supprime toutes les villes
DELETE FROM villes;
-- Supprime les clients qui habitent le 12ème à Paris
DELETE FROM clients WHERE cp = '75012';
Autre Syntaxe
Supprimer dans une table en fonction d'un SELECT (cf les requêtes imbriquées)
DELETE FROM nom_de_table WHERE colonne Opérateur (SELECT
);
Exemples
-- Supprime les lignes de commandes de l'an 2000
DELETE FROM ligcdes WHERE id_cde IN
(SELECT id_cde FROM cdes WHERE date_cde LIKE '%2000%');
ou
DELETE FROM ligcdes WHERE id_cde IN
(SELECT id_cde FROM cdes WHERE YEAR(date_cde) = 2000);
-- Supprime les lillois
DELETE FROM clients WHERE cp IN
(SELECT cp FROM villes WHERE nom_ville LIKE 'Lille%');
Suppression des données (TRUNCATE)
TRUNCATE supprime toutes les données de la table. Le résultat est le même que DELETE FROM nom_de_table.
L'exécution est plus rapide.
En fait TRUNCATE droppe la table et la recrée.
Syntaxe
TRUNCATE TABLE nom_de_table;
Exemple
TRUNCATE TABLE villes_bis;
Si la table villes_bis n'existe pas lancez les commandes suivantes :
CREATE TABLE villes_bis AS SELECT * FROM villes;
SELECT * FROM villes_bis;
TRUNCATE villes_bis;
SELECT * FROM villes_bis;
Modification de données (UPDATE)
Syntaxe
UPDATE nom_de_table SET col1 = valeur1 [, col2 = valeur2] [WHERE condition];
Exemples
-- Met en majuscule les noms des villes
UPDATE villes SET nom_ville = UPPER(nom_ville);
-- Modifie le nom de la ville en question
UPDATE villes SET nom_ville = 'Paris' WHERE nom_ville = 'Paris 12';
Autre syntaxe
Modifier dans une table en fonction d'un SELECT (cf les requêtes imbriquées)
UPDATE nom_de_table SET col1 = valeur1 [, col2 = valeur2]
WHERE colonne Opérateur (SELECT
);
Exemple
Mettre en majuscules les noms des clients qui habitent Lille.
UPDATE clients SET nom = UPPER(nom)
WHERE cp IN
(SELECT cp FROM villes WHERE UPPER(nom_ville) LIKE UPPER('lille%'));
TP PARISCOPE : Mise à jour des données
Enoncés MAJ PARISCOPE
Les corrigés sont dans les annexes.
EnoncéAjoutez le genre "Divers"Insérez un film de ce nouveau genrePassez ce film dans les policiersSupprimez le genre "divers"Archivez les films d'avant 2000 (Sans les supprimer)Mettez les titres des films en majusculesPuis mettez-les en nom propre
L'EXTRACTION DES DONNEES (L'ORDRE SELECT)
SELECT mono table sans condition
Syntaxe
SELECT [DISTINCT] *| col1, col2,
FROM nom_de_table;
Exemples
Tout sur les villes
SELECT * FROM villes;
Les noms et cp des clients (Projection)
SELECT nom, cp FROM clients;
SELECT DISTINCT cp FROM clients;
Extraire un certain nombre d'enregistrements (Restriction)
Syntaxe
SELECT *| col1, col2,
FROM nom_de_table
LIMIT début, nombre;
Note : syntaxe spécifique à MySQL. Cf plus loin pour le WHERE.
Exemples
Les 3 premiers : SELECT * FROM clients LIMIT 0,3;
Les trois suivants : SELECT * FROM clients LIMIT 3,3;
Le tri
Syntaxe
SELECT *| col1, col2,
FROM nom_de_table
ORDER BY col1 [ASC | DESC] [, col2
];
Exemples
SELECT * FROM clients ORDER BY nom;
SELECT * FROM clients ORDER BY nom DESC;
SELECT * FROM clients ORDER BY cp, nom;
Note : la clause ORDER BY est toujours la dernière clause d'un ordre SELECT.
Quelques fonctions propriétaires
Sur les chaînes
SyntaxeActionExempleType résultatCHAR(n)Retourne le caractère dont la valeur ASCII= nCHR(65) donne AcharASCII(char)Retourne le code ASCIIASCII('A') donne 65intCONCAT(c1, c2 )Retourne une seule chaîne (La concaténation)CONCAT('PA','RIS') donne PARISChaîneUPPER(ch)Transforme ch en majusculeUPPER('paris') retourne PARISChaîneLOWER(ch)Retourne la chaîne en minusculeLOWER('ORACLE') donne oracleChaîneREPLACE(ch1, ch2, ch3)Cherche dans ch1 la ch2 puis la remplace par ch3REPLACE('JACK et JUE','J','BL') donne BLACK et BLUEChaîneTRIM(ch) et RTRIM(ch) et LTRIM(ch)Supprime des caractères espace devant et derrière TRIM(' PARIS ') donne PARISChaîneSOUNDEX(ch1)Retourne la représentation phonétique de ch1SELECT nom FROM clients WHERE SOUNDEX(nom)=SOUNDEX('Dupaunt')
trouvera Dupont, Dupond, ...ChaîneLEFT(ch, n)Extrait de la chaîne ch n caractères au débutSELECT LEFT(CP,2) FROM villes retourne le code du départementChaîneRIGHT(ch, n)Extrait de la chaîne ch n caractères à la finSELECT RIGHT(CP,3) FROM villes retourne le code de la communeChaîneSUBSTR(ch1,n[,m])
SUBSTRING(ch1,n[,m])Extrait de la chaîne ch1 à partir de la position n, le nombre de caractères mSUBSTR('PARIS LA DEFENSE',7,2) retourne LAChaîneLENGTHCalcule la longueur d'une chaîneLENGTH('Tintin') retourne 6int
Sur les numériques
SyntaxeActionExempleType de résultatsABS(n)Extrait la valeur absolue de nSELECT ABS(-125) [FROM dual];
affiche 125NumériqueROUND(m, n)Arrondit m à n décimalesSELECT ROUND(15.2358,2) [FROM dual]; donne 15.24NumériqueCEIL(n)Calcule l'entier >= nSELECT CEIL(15.4) [FROM dual]; donne 16NumériqueFLOOR(n)La partie entière de nSELECT FLOOR(15.8) [FROM dual]; donne 15NumériqueMOD(m, n)Renvoie le reste de la division de m par nSELECT MOD(25,7) [FROM dual]; donne 4NumériquePOWER(m, n)Calcule m à la puissance nSELECT POWER(3,2) "Puissance" [FROM dual]; donne 9NumériqueSIN, COS, TANCalcule le sinus, cosinus tangente, etc. Numérique
Sur les dates
SyntaxeActionExempleType de résultatsYEAR(d)Extrait l'annéeSELECT YEAR(date_cde), date_cde FROM cdes;NumériqueMONTH(d)Extrait le moisSELECT MONTH(date_cde), date_cde FROM cdes;NumériqueDAY(d)Extrait le quantième du moisSELECT DAY(date_cde), date_cde FROM cdes;NumériqueMONTHNAME(d)Extrait le nom du moisSELECT MONTHNAME(date_cde), date_cde FROM cdes;NumériqueDAYNAME(d)Extrait le nom du jourSELECT DAYNAME(date_cde), date_cde FROM cdes;NumériqueDAYOFWEEK(d)Extrait le jour de la semaineSELECT DAYOFWEEK(date_cde), date_cde FROM cdes;NumériqueDAYOFYEAR(d)Extrait le jour de l'annéeSELECT DAYOFYEAR(date_cde), date_cde FROM cdes;NumériqueWEEKOFYEAR(d)Extrait la semaine de l'annéeSELECT WEEKOFYEAR(date_cde), date_cde FROM cdes;Numérique
Fonctions système
Curdate()Date du serveurNow()Date et heure du serveurSysDate()Date et heure du serveur
Afficher une date au format français
EMBED MSPhotoEd.3
SELECT nom "Nom", date_naissance "Date de naissance USF",
DATE_FORMAT(date_naissance, '%d/%m/%Y') 'Date de naissance FRF'
FROM clients;
Autres formats :
%D : quantième avec suffixe anglais.
%W : Nom du jour.
%M : Mois en toutes lettres.
Cf la documentation officielle pour le reste.
Exercices :
Affichage long.
L'âge du capitaine.
Les opérateurs de comparaison
Les opérateurs de comparaison
OPERATEURDESCRIPTION=Egal!= , Différent de>Supérieur à>=Supérieur ou égal à= valeur = 5;
Note : si l'on veut une seule fois le même produit il faut cette requête-ci :
SELECT DISTINCT designation "Désignation"
FROM produits p , ligcdes l
WHERE p.id_produit = l.id_produit
AND qte >= 5;
Syntaxe ANSI
SELECT table1.col1, table1.col2, table2.col3
FROM table1 [INNER] JOIN table2
ON table1.col1 = table2.col2;
Exemples
Les villes et les clients (Sans alias de tables)
SELECT villes.nom_ville, clients.nom
FROM villes JOIN clients
ON villes.cp = clients.cp;
Les pays et les villes (Avec des alias de tables)
SELECT p.nom_pays, v.nom_ville
FROM pays p JOIN villes v
ON p.id_pays = v.id_pays;
Auto-jointure
C'est une jointure sur la même table.
Championnat (Theta auto-jointure).
SELECT c1.id_client, c1.nom, c2.id_client, c2.nom
FROM clients c1, clients c2
WHERE c1.id_client > c2.id_client;
Ou
SELECT c1.id_client, c1.nom, c2.id_client, c2.nom
FROM clients c1 JOIN clients c2
ON c1.id_client > c2.id_client;
Rapport hiérarchique : les vendeurs et leur chef
SELECT v1.id_vendeur, v1.nom, v1.chef, v2.nom "Nom du chef"
FROM vendeurs v1, vendeurs v2
WHERE v1.chef = v2.id_vendeur;
Cf plus loin pour voir le chef !
Les jointures externes
Objectif
Une jointure externe permet d'extraire les enregistrements d'une table ainsi que ceux de la deuxième table de jointure mais aussi ceux de la première table qui n'ont pas de correspondants dans la deuxième table.
Dans la table Villes il y a des villes qui n'ont pas de clients; ainsi une équi-jointure n'affiche que les villes où il y a des clients.
La jointure externe permettra d'afficher ainsi toutes les villes même celles sans clients.
Une jointure externe permettra avec une condition supplémentaire d'afficher les enregistrements sans correspondances.
Une jointure externe peut l'être à gauche ou à droite.
Une jointure externe à gauche ajoute un enregistrement NULL à droite (Deuxième table) pour le concaténer à l'enregistrement de la table de gauche (Première table) qui n'a pas de correspondants.
Syntaxe (ANSI)
SELECT table1.col1, table1.col2, table2.col3
FROM table1 RIGHT | LEFT OUTER JOIN table2
ON table1.col1 = table2.col2;
Exemples
Toutes les villes avec leurs clients ainsi que celles qui n'ont pas de clients
SELECT nom_ville, nom
FROM villes v LEFT OUTER JOIN clients c
ON v.cp = c.cp
ORDER BY nom;
Toutes les villes qui n'ont pas de clients
SELECT nom_ville, nom
FROM villes v LEFT OUTER JOIN clients c
ON v.cp = c.cp
WHERE nom IS NULL;
Plus loin avec la syntaxe ANSI
Trois tables : Equi jointure
SELECT pays.nom_pays, villes.nom_ville, clients.nom
FROM pays JOIN villes JOIN clients
ON pays.id_pays = villes.id_pays
AND villes.cp = clients.cp;
ou
SELECT pays.nom_pays, villes.nom_ville, clients.nom
FROM (pays JOIN villes ON pays.id_pays = villes.id_pays) JOIN clients
ON villes.cp = clients.cp;
Trois tables : jointures externes
SELECT pays.nom_pays, villes.nom_ville, clients.nom
FROM
(pays LEFT OUTER JOIN villes ON pays.id_pays = villes.id_pays)
LEFT OUTER JOIN clients ON villes.cp = clients.cp;
Les vendeurs et leur chef (Auto-jointure externe)
SELECT v1.id_vendeur, v1.nom, v1.chef, v2.nom "Nom du chef"
FROM vendeurs v1 LEFT OUTER JOIN vendeurs v2
ON v1.chef = v2.id_vendeur;
Le produit cartésien
Le produit cartésien est la concaténation de chaque ligne d'une table avec toutes les autres lignes d'une autre table.
D'emploi rare il peut être très utile pour des "saisies" de masse.
Premier exemple
Admettons deux tables Listes (Listes candidates) et Bureaux (Bureaux de votes)
CREATE TABLE listes (
id_liste int(10) unsigned NOT NULL default '0',
nom_liste varchar(50) NOT NULL default '',
PRIMARY KEY (id_liste)
) ENGINE=InnoDB;
INSERT INTO listes (id_liste, nom_liste) VALUES
(1, 'MODEM'),
(2, 'UMP'),
(3, 'PS'),
(4, 'PCF'),
(5, 'VERTS');
CREATE TABLE bureaux (
id_bureau int(10) unsigned NOT NULL default '0',
nom_bureau varchar(50) NOT NULL default '',
adresse varchar(100) NOT NULL default '',
inscrits int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id_bureau)
) ENGINE=InnoDB;
INSERT INTO bureaux (id_bureau, nom_bureau, adresse, inscrits) VALUES
(1, 'Mairie', 'Place Léon Blum', 1101),
(2, 'Ecole Roquette', 'Rue de la Roquette', 1200),
(3, 'Ecole Folie Régnault', 'Rue de la Folie Régnault', 1001),
(4, 'Ecole Faidherbe', 'Rue Faidherbe', 1000),
(11, 'Ecole élémentaire Pihet', 'Rue Pihet', 800),
(12, 'Ecole Popincourt', 'Rue Popincourt', 1300),
(52, 'Ecole Roubo', 'Rue Roubo', 800);
Le produit cartésien sera
SELECT id_bureau, nom_bureau, adresse, inscrits, id_liste, nom_liste
FROM bureaux, listes;
Vous créez une table nommée 'resultats' ainsi
CREATE TABLE resultats
AS
SELECT id_bureau, nom_bureau, adresse, inscrits, id_liste, nom_liste, 0 "Voix"
FROM bureaux, listes;
Il ne restera plus qu'à saisir les résultats dans la colonne voix.
Deuxième exemple
Admettons la table 'Fourchettes' de prix
CREATE TABLE fourchettes (
plancher int(10) unsigned NOT NULL default '0',
plafond int(10) unsigned NOT NULL default '0',
libelle VARCHAR(45) NOT NULL default '',
PRIMARY KEY (plancher,plafond)
) ENGINE=InnoDB;
INSERT INTO fourchettes (plancher, plafond, libelle) VALUES
(0, 10, 'Economique'),
(11, 100, 'Moyen'),
(101, 1000, 'Cher');
La requête qui permet d'afficher les produits et leur libellé de fourchette.
SELECT designation, prix, libelle
FROM produits, fourchettes
WHERE prix BETWEEN plancher AND plafond
ORDER BY plancher, prix;
EMBED PBrush
TP PARISCOPE sur les jointures
JOINTURESListez les films et leur genre (libelle)Listez les films de Julia RobertsListez les films de CoppolaListez les films projetés actuellementListez les films de Julia Roberts projetés actuellementListez les films de Coppola projetés actuellement
LES REQUETES AGREGATS
Les fonctions agrégats
Objectif
Faire des calculs sur des ensembles ou sous-ensembles d'enregistrements.
Syntaxe
SELECT fonction_agregat(col1 | *),
FROM nom_de_table;
Fonctions
FONCTIONDESCRIPTIONCOUNT(*)Nombre de lignes satisfaisant une requête.COUNT(DISTINCT | ALL COLONNE)Nombre des valeurs de colonnes vérifiant la requête.
SUM(DISTINCT | ALL COLONNE)Somme des valeurs de colonnes vérifiant la requête.
Avec DISTINCT somme les valeurs uniques (sans les doublons).AVG(DISTINCT | ALL COLONNE)Moyenne des valeurs de colonnes vérifiant la requête.
Avec DISTINCT moyenne des valeurs uniques (sans les doublons).MAX(DISTINCT | ALL COLONNE)Maximum des valeurs de la colonne.MIN(DISTINCT | ALL COLONNE)Minimum des valeurs de la colonne.STDDEV(DISTINCT | ALL COLONNE)Ecart type des valeurs de la colonne.VARIANCE(DISTINCT | ALL COLONNE)La variance des valeurs de la colonne.
Exemples
Nombre de villes
EMBED MSPhotoEd.3
SELECT COUNT(*) "Nombre de villes" FROM villes;
Nombre clients
EMBED MSPhotoEd.3
SELECT COUNT(*) FROM clients;
Nombre clients dont on connaît la date de naissance
EMBED MSPhotoEd.3
SELECT COUNT(date_naissance) FROM clients;
Nombre de noms de client différents
SELECT COUNT(DISTINCT nom) "Nombre de noms de client différents" FROM clients;
Moyenne des prix
SELECT AVG(prix) "Moyenne des prix" FROM produits;
Prix le plus élevé
SELECT MAX(prix) "Prix le plus élevé" FROM produits;
Nombre de clients résidant dans une ville
SELECT COUNT(*) "Nombre de clients"
FROM clients c, villes v
WHERE c.cp = v.cp
AND UPPER(nom_ville) = ('LILLE');
La clause GROUP BY
Regrouper les enregistrements lorsque l'on applique une fonction agrégat.
Syntaxe
SELECT fonction_agregat(col1 | *) [, colonne | fonction_agregat() ]
FROM nom_de_table
GROUP BY col1,
;
Il n'est possible d'avoir dans le SELECT que des fonctions agrégats ou des colonnes présentent dans la clause GROUP BY.
Exemples
Nombre de clients par cp.
EMBED MSPhotoEd.3
SELECT cp, COUNT(*) "Nombre de clients"
FROM clients
GROUP BY cp;
Nombre de clients par ville.
EMBED MSPhotoEd.3
SELECT nom_ville "Ville", COUNT(*) "Nombre de clients"
FROM clients c, villes v
WHERE c.cp = v.cp
GROUP BY nom_ville;
Nombre de commandes par année par client
EMBED MSPhotoEd.3
SELECT YEAR(date_cde) "Année", nom "Nom", COUNT(*) "Nombre de commandes"
FROM clients c, cdes cd
WHERE c.id_client = cd.id_client
GROUP BY c.id_client, nom, YEAR(date_cde)
ORDER BY YEAR(date_cde);
La clause HAVING
Appliquer une condition à un groupe.
Syntaxe
SELECT fonction_agregat(col1 | *) [, colonne | fonction_agregat() ]
FROM nom_de_table
GROUP BY col1,
HAVING fonction_agregat(col1 | *) > 1;
Exemples
Nombre de clients en fonction du CP quand ce nombre est supérieur à 1
EMBED MSPhotoEd.3
SELECT cp, COUNT(*) "Nombre de clients"
FROM clients
GROUP BY cp
HAVING COUNT(cp) > 1;
Clients ayant passé commande plus d'une fois en 2005
EMBED MSPhotoEd.3
SELECT YEAR(date_cde) "Année", c.id_client "Code client", nom "Nom", COUNT(*) "Nombre de commandes"
FROM clients c, cdes cd
WHERE c.id_client = cd.id_client
AND YEAR(date_cde) = '2005'
GROUP BY c.id_client, nom, YEAR(date_cde)
HAVING COUNT(*) > 1;
TP PARISCOPE sur les Agrégats
AGREGATSAffichez le nombre de filmsAffichez le nombre de projectionsAffichez le nombre de films sortis par annéeAffichez le nombre de films sortis en 2000Affichez le nombre de films sortis en 2005JOINTURES ET AGREGATSAffichez le nombre de films de CoppolaAffichez le nombre de films projetés actuellementAffichez le nombre de films projetés actuellement avec Julia RobertsAffichez le nombre d'acteurs par filmAffichez la liste des cinémas ouvertsAffichez la liste des cinémas fermés
LES REQUETES ENSEMBLISTES
Principes
Les opérations ensemblistes de SQL sont : l'Union, l'Intersection et la Différence.
Les opérateurs standards sont :
UNION qui renvoie l'ensemble des enregistrements des tables de l'union,
INTERSECT qui renvoie l'ensemble des enregistrements communs des tables de l'intersection,
MINUS ou EXCEPT qui renvoie l'ensemble des enregistrements qui appartiennent à une table et seulement à cette table.
Ces opérateurs doivent avoir comme opérandes des schémas identiques.
Les SGBDR n'implémentent pas nécessairement tous ces opérateurs.
C'est le cas de MySQL qui n'implémente que l'UNION.
Union : T1 Union T2
T1T2
Intersection : T1 Intersect T2
T1T2
Différence : T1 T2
T1T2
Différence : T2 T1
T1T2
Union
Syntaxe
SELECT schéma FROM table1
UNION
SELECT schéma FROM table2;
Exemples
"Cumul" des CP des clients et des villes
SELECT cp FROM clients
UNION
SELECT cp FROM villes;
Les clients et les clients étrangers
Admettons que la table Villes contienne des noms de villes étrangères dont le CP commence par 99.
Et quelques clients habitant ces villes.
Admettons la table clients_etrangers créée avec une requête du style :
CREATE TABLE clients_etrangers
AS SELECT * FROM clients
WHERE cp LIKE '99%';
Ce sont des clients qui résident aussi bien en France qu'à l'étranger.
Ajoutons dans la table clients_etrangers un nouvel enregistrement (ce sera un client qui ne réside qu'à l'étranger).
INSERT INTO clients_etrangers(id_client, nom, prenom, cp)
VALUES (22,'Loren','Sofia','99391');
SELECT * FROM clients
UNION
SELECT * FROM clients_etrangers;
Intersection (N'existe pas en MySQL)
Syntaxe SQL Standard
SELECT schéma FROM table1
INTERSECT
SELECT schéma FROM table2;
Exemples
Les CP communs des clients et des villes
SELECT cp FROM clients
INTERSECT
SELECT cp FROM villes;
D'où en MySQL
SELECT distinct clients.cp
FROM clients, villes
WHERE clients.cp = villes.cp;
Ou (cf requêtes imbriquées)
SELECT DISTINCT cp
FROM clients
WHERE cp
IN (SELECT cp FROM villes);
Les clients communs à Clients et clients_etrangers
SELECT * FROM clients
WHERE id_client
IN (SELECT id_client FROM clients_etrangers);
Différence (N'existe pas en MySQL)
Syntaxe SQL Standard
SELECT schéma FROM table1
MINUS | EXCEPT
SELECT schéma FROM table2
;
Exemple
Les villes où il n'y a pas de clients
SELECT cp FROM villes
EXCEPT
SELECT cp FROM clients;
D'où en MySQL
SELECT DISTINCT nom_ville
FROM villes LEFT JOIN clients ON villes.cp = clients.cp
WHERE clients.cp Is Null;
Ou (cf requêtes imbriquées)
SELECT DISTINCT nom_ville
FROM villes
WHERE cp
NOT IN (SELECT cp FROM clients);
Les clients strictement français
SELECT *
FROM clients
WHERE id_client
NOT IN (SELECT id_client FROM clients_etrangers);
Les clients strictement étrangers
SELECT *
FROM clients_etrangers
WHERE id_client
NOT IN (SELECT id_client FROM clients);
TP PARISCOPE sur les requêtes ensemblistes
REQUETES ENSEMBLISTESLa liste des acteurs et des réalisateursIntersectDifférence
LES REQUETES IMBRIQUEES
Principes
Une requête imbriquée est une requête qui utilise dans sa clause WHERE le résultat d'une autre requête.
Les requêtes imbriquées sont proches des jointures à la différence près que l'on ne peut afficher que les colonnes de la première table.
Une requête peut renvoyer :
Une seule ligne et une seule colonne (une seule valeur),
Une seule colonne (plusieurs valeurs),
Une seule ligne (plusieurs valeurs),
Plusieurs lignes et plusieurs colonnes (plusieurs valeurs).
Format 1 : la sous-requête renvoie un seul résultat
Syntaxe
SELECT col1, col2,
FROM table1
WHERE colonne =
(SELECT colonne FROM table2 WHERE condition);
L'opérateur doit être =.
Exemple 1
Les villes de France (On est sûr que France renvoie un seul ID; jointure possible)
SELECT * FROM villes
WHERE id_pays =
(SELECT id_pays FROM pays WHERE UPPER(nom_pays) = 'FRANCE');
Exemple 2 (Il faut être sûr qu'il n'y a qu'une seule Annabelle Fassiola née le 10 mai 1985)
Les clients qui habitent la même ville qu'Annabelle Fassiola née le 10 mai 1985.
SELECT nom, cp
FROM clients
WHERE cp =
(SELECT cp
FROM clients
WHERE UPPER(nom) = 'FASSIOLA'
AND UPPER(prenom) = 'ANNABELLE'
AND date_naissance = '1985-05-10'
);
La requête renvoie un seul résultat (une seule ligne mais plusieurs colonnes)
Villes2 (Avec une clé double)
EMBED MSPhotoEd.3
CREATE TABLE IF NOT EXISTS villes2 (
cp char(5) collate utf8_general_ci NOT NULL default '',
id_pays char(3) collate utf8_general_ci NOT NULL default '',
nom_ville varchar(45) collate utf8_general_ci NOT NULL default '',
PRIMARY KEY (cp,id_pays)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO villes2 (cp, id_pays, nom_ville) VALUES
('75001', '033', 'Paris'),
('75001', '039', 'Rome');
Clients2 (Avec une clé étrangère double)
EMBED MSPhotoEd.3
CREATE TABLE IF NOT EXISTS clients2 (
id_client int(10) unsigned NOT NULL auto_increment,
nom varchar(45) character set utf8 collate utf8_general_ci NOT NULL,
cp char(5) character set utf8 collate utf8_general_ci NOT NULL,
id_pays char(3) character set utf8 collate utf8_general_ci NOT NULL,
PRIMARY KEY (id_client)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO clients2 (id_client, nom, cp, id_pays) VALUES
(1, 'Tintin', '75001', '033'),
(2, 'Casta', '75001', '033'),
(3, 'Gassman', '75001', '039');
La requête imbriquée
SELECT * FROM clients2 c
WHERE (cp, id_pays) =
(SELECT cp, id_pays
FROM villes2
WHERE UPPER(nom_ville) = 'ROME');
Format 2 : la sous-requête renvoie plusieurs résultats
Syntaxe
SELECT col1, col2,
FROM table1
WHERE colonne IN
(SELECT colonne FROM table2 WHERE condition);
Exemples
Les clients de Paris (2 niveaux sur 2 tables; une jointure est possible)
SELECT nom, cp
FROM clients
WHERE cp IN
(SELECT cp FROM villes WHERE nom_ville LIKE 'Paris%');
SELECT nom, v.cp
FROM clients c , villes v
WHERE v.cp = c.cp
AND nom_ville LIKE 'Paris%';
Les clients qui habitent la même ville que les Buguet (2 niveaux sur la même table).
SELECT * FROM clients
WHERE cp IN
(SELECT cp FROM clients
WHERE UPPER(nom) = 'BUGUET');
Infos complètes sur les commandes d'Evian (3 niveaux; jointure possible).
Note : EVIAN renvoie un seul enregistrement dans la table Produits.
SELECT * FROM cdes WHERE id_cde IN
(SELECT id_cde FROM ligcdes
WHERE id_produit =
(SELECT id_produit FROM produits
WHERE UPPER(designation) = 'EVIAN'));
La requête renvoie vrai ou faux
C'est la clause EXISTS qui teste la présence ou l'absence de résultats de la requête imbriquée pour chaque enregistrement du premier SELECT. Elle renvoie True ou False.
Syntaxe
SELECT colonnes FROM table(s)
WHERE [NOT] EXISTS
(SELECT colonne(s)
FROM table(s)
[WHERE (conditions)] );
Exemples
On s'appuie sur une table nommée 'bidon' qui possède une seule colonne et un seul enregistrement.
La table BIDON
CREATE TABLE bidon (C1 INTEGER);
INSERT INTO bidon VALUES(1);
Tester l'existence d'une valeur
SELECT 'Trouvé' FROM bidon WHERE EXISTS (SELECT * FROM cdes WHERE YEAR(date_cde) = 2000);
Renverra Trouvé si une commande de cette année existe.
SELECT 'Trouvé' FROM bidon WHERE EXISTS (SELECT * FROM cdes WHERE YEAR(date_cde) = 1999 );
Ne renverra aucun résultat si aucune commande de cette année n'existe.
Tester l'inexistence d'une valeur
SELECT 'Non Trouvé' FROM bidon WHERE NOT EXISTS (SELECT * FROM cdes WHERE YEAR(date_cde) = 1999);
Renverra 'Non trouvé' si aucune commande de cette année n'existe.
EXISTS et NOT EXISTS en substitution d'une jointure externe
EXISTS
Renverra la liste des villes où il y a au moins un client.
SELECT nom_ville
FROM villes v
WHERE EXISTS(SELECT * FROM clients WHERE cp = v.cp);
C'est la même chose que ceci
SELECT DISTINCT nom_ville
FROM villes v , clients c
WHERE v.cp = c.cp;
NOT EXISTS
Renverra la liste des villes où il n'y a pas de clients.
SELECT nom_ville
FROM villes v
WHERE NOT EXISTS
(SELECT * FROM clients WHERE cp = v.cp);
C'est la même chose que ceci
SELECT DISTINCT nom_ville FROM villes v
LEFT OUTER JOIN clients c
ON v.cp = c.cp
WHERE c.cp IS NULL;
La sous requête renvoie un agrégat
Si l'agrégat est mono-valué on emploie l'opérateur = ou > ou
.
Autrement l'opérateur IN.
Exemples mono-valués
Caractéristiques du produit le plus cher.
SELECT *
FROM produits
WHERE prix =
(SELECT MAX(prix) FROM produits);
Produits dont le prix est supérieur à la moyenne des prix
SELECT designation "Désignation"
FROM produits
WHERE prix >
(SELECT AVG(prix) FROM produits);
Exemples multi-valués
Produits qui ont été commandés plus d'une fois
SELECT designation "Désignation"
FROM produits
WHERE id_produit IN
(SELECT p.id_produit
FROM produits p , ligcdes l
WHERE p.id_produit = l.id_produit
GROUP BY p.id_produit
HAVING COUNT(*) > 1
);
Les opérateurs ANY, ALL
Fonctionalités
Les opérateurs ANY et ALL sont combinés aux opérateurs =, >, >=, < et >==MAX sur la même table ANY
(SELECT prix FROM produits);SELECT * FROM produits
WHERE prix >
(SELECT MIN(prix) FROM produits);
L'année où il y a eu le plus grand nombre de commandes
Là MAX(COUNT(*)) est impossible; il faut passer par ce type de requête.
On calcule le nombre de commandes par année (requête de niveau 2).
Ensuite on sélectionne l'année où il y a le plus de commandes (requête de niveau 1).
SELECT YEAR(date_cde) "Année", COUNT(*) "Nombre de commandes"
FROM cdes
GROUP BY YEAR(date_cde)
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM cdes
GROUP BY YEAR(date_cde));
La commande dont le total est le plus élévé.
On calcule le total de chaque commande (requête de niveau 2).
Ensuite on sélectionne la commande dont le total est le plus élévé (requête de niveau 1).
SELECT l.id_cde "Code commande", SUM(prix * qte) "Total de la commande"
FROM ligcdes l, produits p
WHERE l.id_produit = p.id_produit
GROUP BY l.id_cde
HAVING SUM(prix*qte) >= ALL
(SELECT SUM(prix * qte)
FROM ligcdes l, produits p
WHERE l.id_produit = p.id_produit
GROUP BY l.id_cde);
Exercices
L'année où il y a eu le plus petit nombre de commandes.
La commande dont le total est le plus bas.
Exemples avec les trois tables Vendeurs, Villes, vendeurs_villes : requêtes corrélées (la sous-requête fait appel à la requête principale dans les 3 derniers cas).
VendeursVendeurs_villes EMBED PBrush EMBED PBrush
Donc :
Lucky, Dalton et Mickey ont travaillé (1,2,3),
Lucky a toujours travaillé là où il habite (75011),
Dalton a parfois travaillé là où il habite (75012),
Mickey n'a jamais travaillé là où il habite (75012),
Donald n'a jamais travaillé !!!
Les vendeurs qui ont travaillé au moins une fois (Ils sont dans Vendeurs_villes)
SELECT * FROM vendeurs v
WHERE id_vendeur IN
(SELECT id_vendeur FROM vendeurs_villes);
EMBED PBrush
Les vendeurs qui n'ont jamais travaillé (Ils ne sont pas dans Vendeurs_villes)
SELECT * FROM vendeurs v
WHERE id_vendeur NOT IN
(SELECT id_vendeur FROM vendeurs_villes);
EMBED PBrush
Les vendeurs qui ont travaillé (au moins une fois) dans la ville où ils habitent.
EMBED PBrush
SELECT * FROM vendeurs v
WHERE cp IN
(SELECT cp FROM vendeurs_villes
WHERE id_vendeur = v.id_vendeur);
OU
SELECT * FROM vendeurs v
WHERE cp = ANY
(SELECT cp FROM vendeurs_villes
WHERE id_vendeur = v.id_vendeur);
Les vendeurs qui ont toujours travaillé dans la ville où ils habitent.
EMBED PBrush
SELECT DISTINCT v.id_vendeur, v.nom, v.cp
FROM vendeurs v, vendeurs_villes vv
WHERE v.id_vendeur =vv.id_vendeur
AND v.cp = ALL
(SELECT cp FROM vendeurs_villes
WHERE id_vendeur = v.id_vendeur);
Les vendeurs qui n'ont jamais travaillé dans la ville où ils habitent.
EMBED PBrush
SELECT DISTINCT v.id_vendeur, v.nom, v.cp
FROM vendeurs v, vendeurs_villes vv
WHERE v.id_vendeur=vv.id_vendeur
AND v.cp ALL
(SELECT cp FROM vendeurs_villes
WHERE id_vendeur = v.id_vendeur);
Mise à jour en fonction d'une sous-requête
Mettre en majuscule les noms des villes italiennes.
UPDATE villes
SET nom_ville = UPPER(nom_ville)
WHERE id_pays =
(SELECT id_pays
FROM pays
WHERE UPPER(nom_pays) = 'ITALIE');
Supprimer les villes anglaises.
DELETE FROM villes
WHERE id_pays =
(SELECT id_pays
FROM pays
WHERE UPPER(nom_pays) = 'ANGLETERRE');
Insert contrôlé
Objectif
Contrôler qu'une valeur existe dans une table avant de faire quelque chose.
Démarche
On utilise une clause WHERE NOT EXISTS sur une table 'bidon' pour contrôler l'existence d'un enregistrement de référence.
Exemple 1 : table sans clé primaire
Cette requête ajoutera 75011, Paris 11 si l'enregistrement n'existe pas et ne l'ajoutera pas s'il existe.
INSERT INTO villes(cp, nom_ville)
SELECT '75011', 'Paris 11'
FROM bidon
WHERE NOT EXISTS
(SELECT * FROM villes
WHERE cp = '75011');
Ce type de requête peut aussi servir pour mettre en place une contrainte d'intégrité référentielle quand les clés étrangères ne peuvent pas être mises en place (Tables MyISAM).
Exemple 2
Insérera l'enregistrement Clients puisque '75011' existe dans la table villes.
INSERT INTO clients(nom, prenom, cp)
SELECT 'Milou', 'Le chien', '75011'
FROM bidon
WHERE EXISTS
(SELECT * FROM villes
WHERE cp = '75011');
N'insérera pas l'enregistrement Clients puisque '75016' n'existe pas dans la table villes.
INSERT INTO clients(nom, prenom, cp)
SELECT 'Milou', 'Le chien', '75016'
FROM bidon
WHERE EXISTS
(SELECT * FROM villes
WHERE cp = '75016');
TP PARISCOPE sur les requêtes imbriquées
REQUETES IMBRIQUEESLes policiersLes acteurs-réalisateursLes acteurs seulement acteursLes films projetésLes cinémas ouvertsLes cinémas fermésL'année où le plus grand nombre de films est sorti
LES TABLEAUX CROISES DYNAMIQUES
Sur une table
Définition
Un TCD (Tableau croisé dynamique) permet de passer d'une représentation 1D à une représentation 2D.
EMBED PBrush EMBED PBrush
Exemple
La table 'ventes_croisees'
CREATE TABLE ventes_croisees (
nom varchar(50) NOT NULL default '',
designation varchar(50) NOT NULL default '',
vente int(10) unsigned NOT NULL default '0',
PRIMARY KEY (nom, designation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO ventes_croisees (nom, designation, vente) VALUES
('Casta', 'Evian', 20),
('Casta', 'Graves', 5),
('Haddock', 'Badoit', 1),
('Haddock', 'Evian', 1),
('Haddock', 'Graves', 10),
('Tintin', 'Badoit', 5),
('Tintin', 'Evian', 10),
('Tintin', 'Graves', 10);
Le TCD
SELECT nom,
SUM(IF( designation = 'Evian' , vente, 0)) AS 'Evian',
SUM(IF( designation = 'Graves' , vente, 0)) AS 'Graves',
SUM(IF( designation = 'Badoit' , vente, 0)) AS 'Badoit'
FROM ventes_croisees
GROUP BY nom;
Note sur le IF en MySQL
Il permet de faire un test sur une valeur d'une colonne, d'un calcul,
IF(condition, vrai, faux)
SELECT * , IF(prix > 10, prix, 0) FROM produits;
EMBED PBrush
Syntaxe du TCD
SELECT colonne à afficher en ligne,
SUM(IF(colonne à afficher en colonne= 'valeur', colonne à afficher à l'intersection, 0)) AS alias,
SUM(IF(colonne à afficher en colonne = 'valeur', colonne à afficher à l'intersection, 0)) AS alias,
GROUP BY colonne à afficher en ligne
Sur une jointure statique
Avec les tables Vendeurs, Ventes et Produits
EMBED PBrush
Même syntaxe mais sur une jointure.
SELECT nom,
SUM(IF(designation = 'Evian' , vente, 0)) AS 'Evian',
SUM(IF(designation = 'Graves' , vente, 0)) AS 'Graves',
SUM(IF(designation = 'Badoit' , vente, 0)) AS 'Badoit'
FROM vendeurs , ventes, produits
WHERE vendeurs.id_vendeur = ventes.id_vendeur
AND ventes.id_produit = produits.id_produit
GROUP BY nom;
Avec une jointure externe
EMBED PBrush
SELECT nom,
SUM(IF(designation = 'Evian' , vente, 0)) AS 'Evian',
SUM(IF(designation = 'Graves' , vente, 0)) AS 'Graves',
SUM(IF(designation = 'Badoit' , vente, 0)) AS 'Badoit'
FROM (vendeurs LEFT JOIN ventes ON vendeurs.id_vendeur = ventes.id_vendeur)
LEFT JOIN produits ON ventes.id_produit = produits.id_produit
GROUP BY nom;
Sur une jointure dynamique
Il faut travailler avec un langage de programmation.
Soit avec une procédure stockée,
Soit avec un langage serveur dynamique de type PHP, ASP, JSP,
En créant d'abord la liste des désignations de la table produits,
Puis le SELECT dynamique.
LES TRANSACTIONS
Principes
Une transaction est l'espace-temps qui s'écoule entre deux états stables de la base de données.
Les transactions doivent garantir les propriétés ACID (Atomicité, Consistence, Isolation, Durabilité).
Atomicité : garantit le fait que toutes les actions élémentaires sont effectuées ou aucune.
Consistence : garantit le fait que l'on passe d'un état cohérent à un autre état cohérent.
Isolation : garantit le fait que d'autres actions ne peuvent accéder aux données pendant la transaction.
Durabilité : garantit le fait qu'une fois la transaction validée elle ne peut être défaite.
Lors des MAJ la BD est en état instable. Ce n'est qu'à la fin de toutes les MAJ d'une transaction (Ajout d'une commande avec ses lignes de commandes, opération de transfert de débit-crédit- d'un compte vers un autre) que la BD retrouve un état stable.
Par défaut mysql (et MySQL Query Browser) est lancé avec l'option AutoCommit=true.
C'est-à-dire qu'à chaque instruction de MAJ la BD est actualisée. Ce n'est pas un bon paramétrage.
Ce n'est pas modifiable dans un fichier de configuration (my.ini).
MySQL Query Browser ne permet pas de passer en autocommit=0.
Seules les tables InnoDB, BDB, Berkeley supportent les transactions.
Pour activer le support des transactions vous devez lancer la commande suivante :
SET AUTOCOMMIT = 0;
Dans ce cas les MAJ seront validées (commit) par une instruction spécifique de validation définitive ou bien elles seront invalidées (rollback).
Les tests ne peuvent être faits qu'avec le client mysql ou un script MysqlQueryBrowser.
Validation
Valide toutes les mises à jour depuis le dernier commit ou l'ouverture de la session.
Commit;
Annulation
Annule toutes les mises à jour depuis le dernier commit ou le dernier rollback ou l'ouverture de la session.
Rollback;
NB : basculez en SET AUTOCOMMIT = 0; si par défaut c'est à 1.
Il faut être dans l'utilitaire mysql pour modifier la variable Autocommit.
Pour connaître l'état de l'autocommit tapez mysql>SELECT @@AUTOCOMMIT;
La gestion des transactions peut aussi être réalisée grâce à la commande START TRANSACTION depuis MySQL 4.0.11.
(Avec l'utilitaire mysql et pas avec MYSQL QUERY BROWSER)
SET AUTOCOMMIT = 0;
START TRANSACTION;
INSERT INTO villes (cp, nom_ville) VALUES ('24300','AILLAC');
SELECT * FROM VILLES;
ROLLBACK;
SELECT * FROM villes;
Note : depuis la version 4.0.14 MySQL supporte (avec les tables InnoDB) les commandes SAVEPOINT point_de_sauvegarde; et ROLLBACK TO point_de_sauvegarde;
Les savepoints
Objectif
Diviser une transaction en plusieurs sous-parties.
Syntaxes
Création d'un point de sauvegarde
SAVEPOINT point_de_sauvegarde;
Création d'un point de sauvegarde
ROLLBACK TO SAVEPOINT point_de_sauvegarde;
Suppression d'un point de sauvegarde
RELEASE SAVEPOINT point_de_sauvegarde;
Exemple
Au final vous n'aurez que 75031.
SELECT * FROM villes;
SET AUTOCOMMIT=0;
START TRANSACTION;
SAVEPOINT sp1;
INSERT INTO villes(cp, nom_ville) VALUES('75031','Paris 31');
SAVEPOINT sp2;
INSERT INTO villes(cp, nom_ville) VALUES('75032','Paris 32');
ROLLBACK TO SAVEPOINT sp2;
COMMIT;
SELECT * FROM villes;
Le verrouillage de table
Les commandes LOCK et UNLOCK permettent de verrouiller et de déverrouiller une ou plusieurs tables en lecture ou en lecture/écriture.
Syntaxes
LOCK TABLES nom_de_table verrouillage [, nom_de_table verrouillage];
Verrouillage prend les valeurs READ ou WRITE.
Un verrouillage de type READ autorise les lectures mais pas les écritures de la part des autres utilisateurs.
Un verrouillage de type WRITE n'autorise ni les lectures ni les écritures.
UNLOCK TABLES;
Exemple
Ouvrez deux sessions clients (mysql et MySQL Query Browser par exemple).
UtilisateurAutre utilisateurSELECT * FROM villes;
SET AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLES villes READ;
UPDATE villes SET nom_ville = 'Marsiglia'
WHERE cp = '13000';
UNLOCK TABLES;
COMMIT;
SELECT * FROM villes; -- OK
UPDATE villes SET nom_ville = 'Marsilia' WHERE cp = '13000'; -- KO
UtilisateurAutre utilisateurSELECT * FROM villes;
SET AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLES villes WRITE;
UPDATE villes SET nom_ville = 'Marsiglia'
WHERE cp = '13000';
UNLOCK TABLES;
COMMIT;
SELECT * FROM villes; -- KO
UPDATE villes SET nom_ville = 'Marsilia' WHERE cp = '13000'; -- KO
Le verrouillage de ligne
La clause FOR UPDATE appliquée à un SELECT permet de verrouiller un ou plusieurs lignes en écriture.
Syntaxe
SELECT * FROM nomDeTable WHERE condition FOR UPDATE;
Exemple
Ouvrez deux sessions clients (mysql et MySQL Query Browser par exemple).
UtilisateurAutre utilisateurSET AUTOCOMMIT=0;
START TRANSACTION;
SELECT * FROM pays WHERE id_pays = '033' FOR UPDATE;
UPDATE pays SET nom_pays = 'FR' WHERE id_pays = '033';
COMMIT;
SET AUTOCOMMIT=0;
START TRANSACTION;
UPDATE pays SET nom_pays = 'fr' WHERE id_pays = '033'; -- Attente bloquante (*)
COMMIT;
(*) UPDATE sur un autre pays c'est OK.
LES VUES (VIEWS)
Création, suppression, modification
Une vue est une requête stockée.
Une vue permet de ne pas avoir à ressaisir les requêtes statiques, mais aussi de garantir l'intégrité des données et d'assurer la confidentialité. Elles correspondent aux vues des Modèles Organisationnels des données.
Elles en facilitent l'implémentation.
Les autorisations seront données sur des vues plutôt que sur des tables.
On utilise une vue comme une table; SELECT, jointures, vue à partir d'une autre vue, insertions, suppressions, modifications,
.
Création
Syntaxe
CREATE OR REPLACE VIEW nom_de_vue
AS SELECT * | colonnes FROM nom_de_table [WHERE condition]
[WITH CHECK OPTION];
Check Option permet de contrôler les MAJ à partir des vues.
Il est impossible d'insérer ou de modifier un enregistrement via la view si l'enregistrement ne correspond pas au prédicat de celle-ci. Cf plus loin.
Exemple
CREATE VIEW clients_parisiens AS SELECT * FROM clients WHERE cp LIKE '75%';
Suppression
Syntaxe
DROP VIEW nom_de_vue;
Modification
Syntaxe
ALTER VIEW nom_de_la_vue AS SELECT
;
Les vues et les mises à jour
Les views permettent, éventuellement, les mises à jour.
Pour cela il faut qu'elles soient mono-table, que toutes les colonnes NOT NULL soient présentes dans le SELECT et qu'aucun calcul n'ait été effectué dans le SELECT.
A contrario les requêtes suivantes dans la création d'une View ne seront pas susceptibles de mises à jour :
Jointures,
Requêtes calculées,
Requêtes agrégats,
Etc
Exemples
-- Les clients parisiens avec jointure Clients X Villes
-- Aucun ajout possible
CREATE OR REPLACE VIEW clients_parisiens
AS SELECT nom, prenom, c.cp, nom_ville
FROM clients c, villes v
WHERE c.cp = v.cp
AND nom_ville LIKE 'PARIS%';
SELECT * FROM clients_parisiens;
-- Les clients parisiens mono-table avec toutes les colonnes NOT NULL
-- Tous les clients peuvent être ajoutés
CREATE OR REPLACE VIEW clients_parisiens_ajout
AS SELECT nom, prenom, cp
FROM clients
WHERE cp LIKE '75%';
SELECT * FROM clients_parisiens_ajout;
-- L'enregistrement sera inséré, toutes les colonnes NOT NULL sont renseignées
INSERT
INTO clients_parisiens_ajout(nom, prenom, cp)
VALUES ('Casta', 'Laetitia','75012');
-- L'enregistrement sera inséré, toutes les colonnes NOT NULL sont renseignées
INSERT
INTO clients_parisiens_ajout(nom, prenom, cp)
VALUES ('Jolie', 'Laetitia','94100');
-- Les clients parisiens mono-table avec toutes les colonnes NOT NULL mais CHECK OPTION
-- Seuls les clients parisiens peuvent être ajoutés
CREATE OR REPLACE VIEW clients_parisiens_ajout
AS SELECT nom, prenom, cp
FROM clients
WHERE cp LIKE '75%'
WITH CHECK OPTION;
SELECT * FROM clients_parisiens_ajout c, villes v
WHERE c.cp = v.cp;
-- L'enregistrement ne sera pas inséré, le cp ne correspond pas au prédicat
INSERT INTO clients_parisiens_ls(nom, prenom, cp)
VALUES ('Bullock', 'Sandra','69000');
-- Les clients parisiens avec blocage
-- Aucun client ne peut être ajouté, il y a un calcul dans la requête
CREATE OR REPLACE VIEW clients_parisiens_ls
AS SELECT id_client + 0 "id_client", nom, prenom, cp
FROM clients
WHERE cp LIKE '75%';
-- L'enregistrement ne sera pas inséré, la View est en Lecture Seule
INSERT INTO clients_parisiens_ls(nom, prenom, cp)
VALUES ('Roberts', 'Julia','75012');
TP sur les views
Créez une vue sur les cinémas parisiens et une autre sur les cinémas de banlieue.
Les cinémas parisiens
CREATE VIEW cinemas_parisiens AS SELECT * FROM cinemas WHERE cp LIKE '75%';
Les cinémas de banlieue
CREATE VIEW cinemas_de_banlieue AS SELECT * FROM cinemas WHERE cp NOT LIKE '75%';
OPTIMISATION
Indexation
Cf le chapitre sur les Index.
Explain
La commande
La commande EXPLAIN permet de visualiser la structure d'une table (Comme la commande DESC) mais aussi de visualiser le traitement d'un ordre SELECT (Choix des tables, des index). C'est ce deuxième point que nous allons examiner ici.
Syntaxes
EXPLAIN nom_de_table;
EXPLAIN EXTENDED SELECT
\G
Utilsez l'utilitaire mysql pour les exemples :
C:\
\mysql\bin>mysql --host=localhost --user=root --password= --database=cours
Un select
mysql> EXPLAIN EXTENDED SELECT * FROM villes\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: villes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra:
Explain et les index
Utilisation d'un index
mysql> EXPLAIN EXTENDED SELECT * FROM villes WHERE nom_ville LIKE 'par%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: villes
type: range
possible_keys: i_villes_nom_ville
key: i_villes_nom_ville
key_len: 152
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
Désactivation d'un index
mysql> EXPLAIN EXTENDED SELECT * FROM villes WHERE UPPER(nom_ville) LIKE 'PAR%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: villes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra: Using where
Autre exemple :
Avec l'index
mysql> EXPLAIN EXTENDED SELECT * FROM villes USE INDEX (i_villes_nom_ville) where nom_ville = 'Sarlat'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: villes
type: ref
possible_keys: i_villes_nom_ville
key: i_villes_nom_ville
key_len: 152
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Avec la désactivation de l'index
mysql> EXPLAIN EXTENDED SELECT * FROM villes IGNORE INDEX (i_villes_nom_ville) where nom_ville = 'Lyon'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: villes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Explain et les jointures
mysql> EXPLAIN EXTENDED SELECT clients.nom, clients.cp, villes.nom_ville FROM clients, villes WHERE clients.cp = villes.cp\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: clients
type: ALL
possible_keys: Index_cp
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: villes
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 17
ref: cours.clients.cp
rows: 1
filtered: 100.00
Extra: Using where
Sans index dans la table communes.
mysql> EXPLAIN EXTENDED SELECT clients.nom, clients.cp, communes.nom_commune_majus FROM clients, communes WHERE clients.cp = communes.code_insee\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: communes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 35925
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: clients
type: ALL
possible_keys: Index_cp
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 81.82
Extra: Using where; Using join buffer
Après avoir indexé le code_insee
mysql> EXPLAIN EXTENDED SELECT clients.nom, clients.cp, communes.nom_commune_majus FROM clients, communes WHERE clients.cp = communes.code_insee\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: clients
type: ALL
possible_keys: Index_cp
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: communes
type: ref
possible_keys: code_insee
key: code_insee
key_len: 17
ref: cours.clients.cp
rows: 1
filtered: 100.00
Extra: Using where
Jointure 6 tables
Son ordre : cdes, clients, ligcdes, produits, villes et pays
mysql> EXPLAIN EXTENDED SELECT p.nom_pays, v.nom_ville, c.nom, pr.designation FROM pays p, villes v, clients c, cdes cd, ligcdes l, produits pr WHERE p.id_pays = v.id_pays AND c.cp = c.cp AND c.id_client = cd.id_client AND cd.id_cde = l.id_cde AND l.id_produit = pr.id_produit\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cd
type: ALL
possible_keys: PRIMARY,FK_cdes_client
key: NULL
key_len: NULL
ref: NULL
rows: 9
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: cours.cd.id_client
rows: 1
filtered: 100.00
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: l
type: ref
possible_keys: PRIMARY,FK_ligcdes_id_produit
key: PRIMARY
key_len: 4
ref: cours.cd.id_cde
rows: 1
filtered: 100.00
Extra: Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: pr
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: cours.l.id_produit
rows: 1
filtered: 100.00
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: v
type: ALL
possible_keys: i_villes_id_pays
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra: Using join buffer
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: p
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 9
ref: cours.v.id_pays
rows: 1
filtered: 100.00
Extra:
La même avec STRAIGHT_JOIN
mysql> EXPLAIN EXTENDED SELECT p.nom_pays, v.nom_ville, c.nom, pr.designation FROM pays p STRAIGHT_JOIN villes v STRAIGHT_JOIN clients c STRAIGHT_JOIN cdes cd STRAIGHT_JOIN ligcdes l STRAIGHT_JOIN produits pr WHERE p.id_pays = v.id_pays AND c.cp = c.cp AND c.id_client = cd.id_client AND cd.id_cde = l.id_cde AND l.id_produit = pr.id_produit\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: v
type: ALL
possible_keys: i_villes_id_pays
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 75.00
Extra: Using where; Using join buffer
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: l
type: index
possible_keys: PRIMARY,FK_ligcdes_id_produit
key: PRIMARY
key_len: 8
ref: NULL
rows: 15
filtered: 100.00
Extra: Using index; Using join buffer
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 100.00
Extra: Using join buffer
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: cd
type: eq_ref
possible_keys: PRIMARY,FK_cdes_client
key: PRIMARY
key_len: 4
ref: cours.l.id_cde
rows: 1
filtered: 100.00
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: pr
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: cours.l.id_produit
rows: 1
filtered: 100.00
Extra:
6 rows in set, 1 warning (0.00 sec)
TableLignes avec JOINLignes avec STRAIGHT_JOINPays13Villes1212Clients115Cdes911Ligcdes11Produits11Total2543
Ordre Join : cdes, clients, ligcdes, produits, villes et pays
Explain et les requêtes imbriquées
mysql> EXPLAIN EXTENDED SELECT * FROM clients WHERE cp IN(SELECT cp FROM villes)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: clients
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: villes
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 17
ref: func
rows: 1
filtered: 100.00
Extra: Using index; Using where
2 rows in set, 1 warning (0.05 sec)
ANALYZE TABLE
La commande ANALYZE TABLE met à jour les statistiques de cardinalité des tables, qui affectent les choix de l'optimiseur.
Syntaxe
ANALYZE TABLE nom_de_table[,nom_de_table...];
DIVERS
Les événements (> 5.1)
Objectif
Créer un événement pour effectuer une action, une commande SQL.
L'événement peut gérer une action récurrente (un archivage tous les mois) ou unique (une suppression de données dans 1 heure).
Syntaxes
CREATE EVENT nom_d_evement
ON SCHEDULE horaire
DO commandeSQL;
Horaire peut être défini de deux façons : absolu ou relatif.
ON SCHEDULE AT heure [+ INTERVAL intervalle]
ON SCHEDULE intervalle
ALTER EVENT nom_d_evenement
;
DROP EVENT [IF EXISTS] nom_d_evenement;
Exemples
CREATE EVENT dans_2_minutes
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO DELETE FROM villes_bis;
CREATE EVENT tous_les_mois
ON SCHEDULE EVERY 1 MONTH
DO DELETE FROM villes_bis;
Quelques éléments meta-basiques
La commande SHOW
Objectif
Permet de récupérer les caractéristiques d'un objet.
Syntaxe
SHOW objet [FROM objet_parent [ LIKE '%
']];
Exemples
-- Liste des bases
SHOW DATABASES;
-- Liste des tables d'une base
SHOW TABLES FROM cours;
SHOW TABLES FROM cours LIKE 'V%';
-- Liste des colonnes d'une table
SHOW FIELDS FROM villes;
SHOW COLUMNS FROM villes;
-- Liste des clés d'une table
SHOW KEYS FROM villes;
-- Liste des index d'une table
SHOW INDEX FROM villes;
-- Instruction de création d'une table
SHOW CREATE TABLE villes;
LE MODELE RELATIONNEL DE CODD
Les domaines, les relations et les attributs.
Domaine
Un domaine est un ensemble de valeurs typées
Les types de base sont les suivants :
Numérique : entier ou réel (Int, Float, etc.).
Chaîne de caractères (Char, VarChar, etc.).
Date (DATE, TIME, YEAR, etc.).
Type énuméré.
Binaire.
Exemple
Les CP des clients.
Les noms des clients.
Les codes des produits.
Attribut
Un attribut est une information atomique
Le nom du client.
Le nom d'une ville.
Relation
Une relation (dans le modèle de Codd) est représentée de deux façons :
Un Schéma : La structure de la relation. Le schéma est fixé.
Une Extension : Le contenu, qui est un ensemble de n-uplets dont l'ordre n'a pas d'importance.
Exemple
Schéma : Villes(cp, nom_ville)
Extension :
75011Paris 1175012Paris 1275020Paris 20
BD R
Une Base de données Relationnelle est un ensemble de relations représentant un système d'informations. Les relations sont reliées entre elles et forment un tout.
Les dépendances fonctionnelles élémentaires, clés candidates, clé primaire.
Les dépendances fonctionnelles (DF)
Il y a dépendance fonctionnelle entre deux propriétés lorsque la connaissance d'une valeur d'une propriété permet de déterminer une et une seule valeur d'une autre propriété.
Une dépendance fonctionnelle est notée P1 --> P2
Exemple :id_client ---> nom
En revanche nom ne détermine pas l'id client.
Clés candidates
Lorsque plusieurs attributs déterminent le même sous ensemble d'attributs, ces attributs sont clés (primaires) candidates.
Admettons que l'on ait les attributs suivants :
Id_salarie, numero_insee, nom_salarie, prenom_salarie,
.
Id_salarie et numero_insee déterminent nom_salarie et prenom_salarie. Ils sont clés candidates.
Clé primaire
Une clé primaire permet d'identifier tous les tuples d'une relation.
Exemple
Clients(#id_client, nom, cp)
Villes(#cp, nom_ville)
La clé primaire est diésée, en gras et soulignée.
Clé étrangère
Une clé étrangère est un attribut clé primaire dans une autre table.
Elle représente une contrainte d'intégrité référentielle.
La clé étrangère est diésée et en gras.
Exemple
Villes(#cp, nom_ville)
Clients(#id_client, nom, #cp)
La valeur NULL, l'intégrité d'entité, l'intégrité référentielle.
La valeur NULL
Les attributs peuvent dans certains cas ne pas être renseignés.
On leur affecte la valeur NULL qui est différente de 0 ou de chaîne vide.
L'intégrité d'entité
Chaque clé primaire doit être unique et non nulle (NOT NULL).
L'intégrité référentielle
Une contrainte d'intégrité référentielle signifie qu'un tuple d'une relation dépend d'un autre tuple dans une autre rélation.
Exemple : la ville d'un client doit exister dans la relation villes.
La normalisation des données
La normalisation a pour objectif d'éliminer les redondances dans la base ainsi que les anomalies de mise à jour.
Première forme normale (1ère FN)
Toutes les propriétés sont élémentaires et il existe un identifiant.
Sinon on décompose une propriété en plusieurs propriétés et/ou on crée une propriété identifiante.
Exemple de 1ère FN
Cet individu n'est pas en 1ère FN. L'adresse n'est pas atomique.
Cet individu est en 1ère FN
Deuxième forme normale (2ème FN)
Toute propriété dépend de l'identifiant par une dépendance fonctionnelle (DF) élémentaire.
Donc chaque propriété dépend de tout l'identifiant et non pas d'une partie.
Sinon on décompose en plusieurs entités.
Exemple de 2ème FN
Modélisation qui n'est pas en 2ème FN
EMBED Image Microsoft Word
Modélisation en 2ème FN
Troisième forme normale (3ème FN)
Toute propriété doit dépendre de l'identifiant par une DF directe.
Donc tous les attributs non identifiants sont indépendants entre eux.
Sinon on décompose en deux entités.
Exemple de 3 FN
Modélisation qui n'est pas en 3FN
Modélisation en 3FN
EMBED Image Microsoft Word
Note :
Pour se souvenir de l'ordre et des caractéristiques des trois premières formes normales, il suffit de se rappeler le serment que tous les témoins doivent prêter devant la justice : Je jure de dire la vérité, toute la vérité, rien d'autre que la vérité.
Ce qui donne : 1FN = La clé. 2FN = Toute la clé. 3FN = Rien que la clé.
Les opérateurs de l'algèbre relationnelle
L'algèbre relationnelle est l'ensemble des opérations effectuées avec les opérateurs de l'AR et dont les opérandes sont des relations.
Tout résultat d'une opération relationnelle est une relation.
Les opérations de l'algèbre relationnelle
- La Projection : la projection d'une relation R1 de schéma R(a1,a2,.....,ap) selon les attributs a1,..,an est une relation R' de schéma R'(a1,...,an) dont les tuples sont obtenus par élimination des attributs ai+1,...,ap et par élimination des tuples en double.
La projection est notée R' = project(R/a1,....an)
- La Restriction : la restriction d'une relation R de schéma quelconque par une qualification Q est la relation R' de même schéma contenant l'ensemble des tuples satisfaisant la condition Q.
La restriction est notée R' = restrict(R/Q)
- L'Union : L'union de deux relations de même schéma R1 et R2 est une relation R de même schéma contenant les tuples de R1 ou de R2 ou aux deux relations.
L'union est notée R = union(R1,R2)
- L'Intersection : l'intersection de deux relations R1 et R2 de même schéma est une relation R de même schéma contenant les tuples appartenant à la fois à R1 et à R2.
L'intersection est notée R = intersect (R1,R2)
- La Différence : la différence de deux relations de même schéma R1 et R2 est la relation R de même schéma contenant les tuples qui appartiennent à R1 mais n'appartiennent pas à R2.
La différence est notée R = minus(R1,R2)
- Le Produit cartésien : le produit cartésien de deux relations R1 et R2 de schémas quelconques est une relation R dont le schéma est la concaténation des attributs des relations R1 et R2 et dont les tuples sont la concaténation de chaque tuple de R1 concaténé à tous les tuples de R2.
Le produit cartésien est noté R = product (R1,R2)
- La Jointure : la jointure est un sous-produit du produit cartésien. La jointure de deux relations R1 et R2 de schémas quelconques selon une qualification multi-attributs est l'ensemble des tuples du produit cartésien satisfaisant la qualification Q.
La jointure est notée R = join(R1, R2 /Qma)
Exemples d'opérations de l'AR
EnoncéOpérationLister les désignations des produitsR' = project(produits/designation)Lister les clients du 75R' = restrict(clients / cp comme '75*')Lister les noms des clients et leur villeR' = Join(clients, villes / clients.cp = villes.cp)Lister les clients de ClamartR' = Join(clients, villes / clients.cp = villes.cp ET villes.nom_ville = 'Clamart')Lister les cp des villes et des clientsR' = union(project(villes / cp), project(villes / cp))Lister les codes des produits vendusR' = difference(project(produits / id_produit), project(ligcdes / id_produit))Lister les cp communs aux villes et aux clientsR' = intersect(project(villes / cp), project(clients / cp))
TP modèle relationnel PARISCOPE
Enoncé
Créez le modèle PARISCOPE.
SI sur la rubrique cinémas de Pariscope.
Un film est caractérisé par un titre, un titre original, une année de sortie, un genre.
Un film est d'un genre et d'un seul.
Un acteur est caractérisé par un nom.
Deux acteurs peuvent avoir le même nom.
Un acteur joue dans 1 ou plusieurs films.
Un réalisateur est caractérisé par un nom.
Deux réalisateurs peuvent avoir le même nom.
Un réalisateur a réalisé 1 ou plusieurs films.
Un artiste peut être soit acteur, soit réalisateur, soit les deux.
Un film est éventuellement projeté dans un ou plusieurs cinémas.
Un cinéma est caractérisé par un nom, une adresse, un arrondissement pour les cinémas parisiens, un cp pour les cinémas de banlieue, un réseau.
Un cinéma se trouve en un lieu et un seul.
Un arrondissement possède plusieurs cinémas, une ville aussi.
Une ville est située dans un département.
Un film est projeté dans aucun, un ou plusieurs cinémas.
Corrigé
departements(#id_departement, nom_departement)
villes(#cp , nom_ville, #id_departement)
arrondissements(#id_arrondissement, nom_arrondissement)
cinemas(#id_cinema, nom_cinema, #id_arrondissement, #cp, adresse, reseau)
genres(#id_genre, libelle_genre)
films(#id_film, titre, titre_original, annee, #id_genre)
projeter(#id_cinema, #id_film, version, semaine, annee)
acteurs(#id_acteur, nom_acteur)
realisateurs(#id_realisateur, nom_realisateur)
realiser(#id_realisateur, #id_film)
jouer(#id_acteur, #id_film)
TP algèbre relationnelle PARISCOPE
Enoncés
EnoncéCorrigéLister les titres des filmsLister les titres et les titres originaux des filmsLister les policiersLister les films de 2002Lister les titres des films et leur genreLister les films de Julia RobertsLister les films de CoppolaLister les titres des films projetés actuellementLister les titres des films NON projetés actuellementLister les noms des acteurs et des réalisateursLister les noms des acteurs qui sont seulement acteursLister les noms de ceux qui sont acteur et aussi réalisteur
Corrigés
EnoncéCorrigéLister les titres des filmsR' = project(films/titre)Lister les titres et les titres originaux des filmsR' = project(films/titre, titre_original)Lister les policiersR' = restrict(films / id_genre='PO')Lister les films de 2002R' = restrict(films / year(annee) = 2002)Lister les titres des films et leur genreR' = Join(films, genres / films.id_genre = genres.id_genre)Lister les films de Julia RobertsR' = Join (films, jouer, acteurs / films.id_film = jouer.id_film ET jouer.id_acteur = acteurs.id_acteur ET nom_acteur = 'Julia Roberts') Lister les films de CoppolaR' = Join (films, jouer, realisateurs / films.id_film = realiser.id_film ET realiser.id_realisateur = realisateurs.id_ realisateur ET nom_ realisateur = 'Coppola')Lister les titres des films projetés actuellementR' = Join (films, projeter / films.id_film = projeter.id_film)Lister les titres des films NON projetés actuellementR' = Left Join (films, projeter / films.id_film = projeter.id_film ET projeter.id EST NULL)Lister les noms des acteurs et des réalisateursR' = union(project(acteurs / nom_acteur), project(realisateurs / nom_realisateur))Lister les noms des acteurs qui sont seulement acteursR' = difference(project(acteurs / nom_acteur), project(realisateurs / nom_realisateur))Lister les noms de ceux qui sont acteur et aussi réalisteurR' = intersect(project(acteurs / nom_acteur), project(realisateurs / nom_realisateur))
ANNEXES
La BD COURS
Schéma
EMBED MSPhotoEd.3
Script de création des tables
DROP DATABASE IF EXISTS cours;
CREATE DATABASE cours;
USE cours;
SET FOREIGN_KEY_CHECKS = 0;
-- Structure de la table 'utilisateurs'
DROP TABLE IF EXISTS utilisateurs;
CREATE TABLE IF NOT EXISTS utilisateurs (
ut varchar(50) NOT NULL,
mdp varchar(50) NOT NULL,
e_mail varchar(50) NOT NULL,
qualite varchar(50) NOT NULL,
PRIMARY KEY (ut),
UNIQUE KEY e_mail (e_mail)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Structure de la table 'pays'
DROP TABLE IF EXISTS pays;
CREATE TABLE IF NOT EXISTS pays (
id_pays char(3) NOT NULL,
nom_pays varchar(50) NOT NULL,
PRIMARY KEY (id_pays)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'villes'
DROP TABLE IF EXISTS villes;
CREATE TABLE IF NOT EXISTS villes (
cp varchar(5) NOT NULL,
nom_ville varchar(50) NOT NULL,
site varchar(50) default NULL,
photo varchar(50) default NULL,
id_pays char(3) default NULL,
PRIMARY KEY (cp),
KEY Index_id_pays (id_pays)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'clients'
DROP TABLE IF EXISTS clients;
CREATE TABLE IF NOT EXISTS clients (
id_client int(5) NOT NULL auto_increment,
nom varchar(50) NOT NULL,
prenom varchar(50) NOT NULL,
adresse varchar(100) default NULL,
date_naissance date default NULL,
cp char(5) default NULL,
PRIMARY KEY (id_client),
KEY Index_cp (cp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'cdes'
DROP TABLE IF EXISTS cdes;
CREATE TABLE IF NOT EXISTS cdes (
id_cde int(5) NOT NULL auto_increment,
date_cde date NOT NULL,
id_client int(5) NOT NULL,
PRIMARY KEY (id_cde),
KEY FK_cdes_client (id_client)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Structure de la table 'produits'
DROP TABLE IF EXISTS produits;
CREATE TABLE IF NOT EXISTS produits (
id_produit int(5) NOT NULL auto_increment,
designation varchar(50) NOT NULL,
prix double(7,2) default NULL,
qte_stockee int(5) default NULL,
photo varchar(50) default NULL,
PRIMARY KEY (id_produit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Structure de la table 'ligcdes'
DROP TABLE IF EXISTS ligcdes;
CREATE TABLE IF NOT EXISTS ligcdes (
id_cde int(5) NOT NULL,
id_produit int(5) NOT NULL,
qte int(5) NOT NULL,
PRIMARY KEY (id_cde,id_produit),
KEY FK_ligcdes_id_produit (id_produit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'vendeurs'
DROP TABLE IF EXISTS vendeurs;
CREATE TABLE IF NOT EXISTS vendeurs (
id_vendeur int(10) unsigned NOT NULL auto_increment,
nom varchar(45) NOT NULL,
chef int(10) unsigned NOT NULL default '0',
cp char(5) NOT NULL,
PRIMARY KEY (id_vendeur),
KEY FK_vendeurs_cp (cp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'vendeurs_villes'
DROP TABLE IF EXISTS vendeurs_villes;
CREATE TABLE IF NOT EXISTS vendeurs_villes (
id_vendeur int(10) unsigned NOT NULL auto_increment,
cp varchar(5) NOT NULL,
Date_debut date NOT NULL default '0000-00-00',
date_fin date NOT NULL default '0000-00-00',
PRIMARY KEY (id_vendeur,cp,Date_debut),
KEY cp (cp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'ventes'
DROP TABLE IF EXISTS ventes;
CREATE TABLE IF NOT EXISTS ventes (
id_vendeur int(10) unsigned NOT NULL default '0',
id_produit int(10) unsigned NOT NULL default '0',
vente int(10) unsigned NOT NULL default '0',
date_vente date default NULL,
PRIMARY KEY (id_vendeur,id_produit,vente)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Structure de la table 'ventes_croisees'
DROP TABLE IF EXISTS ventes_croisees;
CREATE TABLE IF NOT EXISTS ventes_croisees (
nom_vendeur varchar(50) NOT NULL,
designation varchar(50) NOT NULL,
vente int(10) unsigned NOT NULL default '0',
PRIMARY KEY (nom_vendeur,designation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Contraintes pour les tables exportées
-- Contraintes pour la table cdes
ALTER TABLE cdes
ADD CONSTRAINT cdes_ibfk_1 FOREIGN KEY (id_client) REFERENCES clients (id_client) ON DELETE CASCADE ON UPDATE CASCADE;
-- Contraintes pour la table clients
ALTER TABLE clients
ADD CONSTRAINT clients_ibfk_1 FOREIGN KEY (cp) REFERENCES villes (cp) ON DELETE CASCADE ON UPDATE CASCADE;
-- Contraintes pour la table ligcdes
ALTER TABLE ligcdes
ADD CONSTRAINT ligcdes_ibfk_2 FOREIGN KEY (id_produit) REFERENCES produits (id_produit) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT ligcdes_ibfk_1 FOREIGN KEY (id_cde) REFERENCES cdes (id_cde) ON DELETE CASCADE ON UPDATE CASCADE;
-- Contraintes pour la table vendeurs
ALTER TABLE vendeurs
ADD CONSTRAINT vendeurs_ibfk_1 FOREIGN KEY (cp) REFERENCES villes (cp) ON DELETE CASCADE ON UPDATE CASCADE;
-- Contraintes pour la table vendeurs_villes
ALTER TABLE vendeurs_villes
ADD CONSTRAINT vendeurs_villes_ibfk_2 FOREIGN KEY (id_vendeur) REFERENCES vendeurs (id_vendeur) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT vendeurs_villes_ibfk_1 FOREIGN KEY (cp) REFERENCES villes (cp) ON DELETE CASCADE ON UPDATE CASCADE;
-- Contraintes pour la table ventes
ALTER TABLE ventes
ADD CONSTRAINT ventes_ibfk_1 FOREIGN KEY (id_vendeur) REFERENCES vendeurs (id_vendeur) ON DELETE CASCADE ON UPDATE CASCADE;
-- Contraintes pour la table villes
ALTER TABLE villes
ADD CONSTRAINT villes_ibfk_1 FOREIGN KEY (id_pays) REFERENCES pays (id_pays) ON DELETE CASCADE ON UPDATE CASCADE;
Script d'insertion des données
-- Contenu de la table pays
INSERT INTO pays (id_pays, nom_pays) VALUES
('033', 'France'),
('035', 'Angleterre'),
('039', 'Italie');
-- Contenu de la table villes
INSERT INTO villes (cp, nom_ville, site, photo, id_pays) VALUES
('13000', 'Marseille', 'www.ma.net', NULL, '033'),
('24200', 'Sarlat', NULL, NULL, NULL),
('24300', 'Carsac', NULL, NULL, NULL),
('24400', 'Aillac', NULL, NULL, NULL),
('59000', 'Lille', 'www.lille.fr', 'lille.jpg', '033'),
('69000', 'Lyon', 'www.lyon.fr', 'lyon.jpg', '033'),
('75011', 'Paris 11', 'www.paris.fr', 'paris.jpg', '033'),
('75012', 'Paris 12', 'www.paris.fr', 'paris.jpg', '033'),
('75019', 'Paris XIX', 'www.paris.fr', 'paris.jpg', '033'),
('78000', 'Versailles', NULL, NULL, '033'),
('94100', 'Vincennes', NULL, NULL, NULL),
('94200', 'St Mandé', NULL, NULL, NULL),
('99391', 'ROME', NULL, NULL, '039'),
('99392', 'MILAN', NULL, NULL, '039');
-- Contenu de la table clients
INSERT INTO clients (id_client, nom, prenom, adresse, date_naissance, cp) VALUES
(1, 'Buguet', 'Pascal', NULL, '1955-10-03', '75011'),
(2, 'Serra', 'MH', NULL, '1958-11-27', '75019'),
(4, 'Buguet', 'MJ', NULL, '1948-08-22', '75011'),
(5, 'Fassiola', 'Annabelle', NULL, '1985-05-10', '75011'),
(6, 'Roux', 'Françoise', NULL, '1950-10-10', '59000'),
(7, 'Tintin', 'Albert', NULL, NULL, '75011'),
(8, 'Sordi', 'Alberto', NULL, NULL, '99391'),
(9, 'Muti', 'Ornella', NULL, NULL, '99392'),
(10, 'Milou', 'Le chien', NULL, NULL, '75019'),
(11, 'Tournesol', 'Bruno', NULL, NULL, '75011'),
(17, 'Roberts', 'Julia', NULL, '1965-10-03', '75011');
-- Contenu de la table cdes
INSERT INTO cdes (id_cde, date_cde, id_client) VALUES
(1, '2005-10-03', 1),
(2, '2005-10-10', 2),
(3, '2005-11-01', 1),
(4, '2000-11-01', 1),
(5, '2000-12-10', 2),
(6, '2008-12-13', 1),
(7, '2008-12-13', 2),
(8, '2008-12-13', 5),
(9, '2008-12-13', 4);
-- Contenu de la table produits
INSERT INTO produits (id_produit, designation, prix, qte_stockee, photo) VALUES
(1, 'Evian', 1.81, 10, 'evian.jpg'),
(2, 'Badoit', 1.93, 10, 'badoit.jpg'),
(3, 'Graves', 13.20, 10, 'graves.jpg'),
(4, 'Ruinard', 110.00, 10, 'ruinard.jpg'),
(5, 'Dom Pérignon', 165.00, 10, 'dom.jpg'),
(7, 'Picpoul', 5.00, 500, NULL),
(8, 'Picmal', 5.00, 10, NULL);
-- Contenu de la table ligcdes
INSERT INTO ligcdes (id_cde, id_produit, qte) VALUES
(1, 1, 2),
(1, 2, 3),
(2, 1, 2),
(3, 1, 6),
(3, 2, 2),
(3, 3, 1),
(4, 1, 5),
(5, 4, 10),
(6, 1, 1),
(6, 2, 1),
(6, 3, 1),
(6, 4, 1),
(7, 4, 100),
(8, 1, 10),
(9, 1, 10);
-- Contenu de la table vendeurs
INSERT INTO vendeurs (id_vendeur, nom, chef, cp) VALUES
(1, 'Lucky', 0, '75011'),
(2, 'Dalton', 1, '75012'),
(3, 'Mickey', 1, '75012'),
(4, 'Donald', 2, '75011');
-- Contenu de la table vendeurs_villes
INSERT INTO vendeurs_villes (id_vendeur, cp, Date_debut, date_fin) VALUES
(1, '75011', '2006-01-01', '2006-12-31'),
(1, '75011', '2007-01-01', '2007-12-31'),
(2, '75011', '2006-01-01', '2006-12-31'),
(2, '75012', '2007-01-01', '2007-12-31'),
(3, '75011', '2007-01-01', '2007-12-31'),
(3, '75012', '2006-01-01', '2006-12-31');
-- Contenu de la table ventes
INSERT INTO ventes (id_vendeur, id_produit, vente, date_vente) VALUES
(1, 1, 20, '2007-04-16'),
(1, 2, 100, '2007-04-16'),
(2, 1, 1, '2007-04-16'),
(2, 2, 10, '2008-04-16'),
(2, 3, 5, '2008-04-16');
-- Contenu de la table ventes_croisees
INSERT INTO ventes_croisees (nom_vendeur, designation, vente) VALUES
('Casta', 'Evian', 20),
('Casta', 'Graves', 5),
('Haddock', 'Badoit', 1),
('Haddock', 'Evian', 1),
('Haddock', 'Graves', 10),
('Tintin', 'Badoit', 5),
('Tintin', 'Evian', 10),
('Tintin', 'Graves', 10);
-- Contenu de la table utilisateurs
INSERT INTO utilisateurs (ut, mdp, e_mail, qualite) VALUES
('a', 'f', 'af@free.fr', ''),
('p', 'b', 'pb@free.fr', '');
La BD PARISCOPE
Schéma
EMBED PBrush
EMBED MSPhotoEd.3
Corrigés
TP Pariscope sur les objets tables
--
-- Base de données: `pariscope`
--
CREATE DATABASE IF NOT EXISTS pariscope
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;
USE pariscope;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- --------------------------------------------------------
--
-- Structure de la table `acteurs`
--
DROP TABLE IF EXISTS `acteurs`;
CREATE TABLE IF NOT EXISTS `acteurs` (
`id_acteur` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nom_acteur` varchar(50) NOT NULL,
PRIMARY KEY (`id_acteur`),
KEY `nom_acteur` (`nom_acteur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- --------------------------------------------------------
--
-- Structure de la table `arrondissements`
--
DROP TABLE IF EXISTS `arrondissements`;
CREATE TABLE IF NOT EXISTS `arrondissements` (
`id_arrondissement` varchar(5) NOT NULL,
`nom_arrondissement` varchar(50) NOT NULL,
PRIMARY KEY (`id_arrondissement`),
UNIQUE KEY `nom_arrondissement` (`nom_arrondissement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Structure de la table `cinemas`
--
DROP TABLE IF EXISTS `cinemas`;
CREATE TABLE IF NOT EXISTS `cinemas` (
`id_cinema` char(5) NOT NULL,
`nom_cinema` varchar(45) NOT NULL,
`id_arrondissement` varchar(5) DEFAULT NULL,
`cp` char(5) NOT NULL,
`adresse` varchar(100) DEFAULT NULL,
`reseau` varchar(45) NOT NULL,
`acces_handicapes` int(10) unsigned NOT NULL,
PRIMARY KEY (`id_cinema`),
KEY `index_nom_cinema` (`nom_cinema`),
KEY `index_cp` (`cp`),
KEY `index_id_arrondissement` (`id_arrondissement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Structure de la table `departements`
--
DROP TABLE IF EXISTS `departements`;
CREATE TABLE IF NOT EXISTS `departements` (
`id_departement` int(10) unsigned NOT NULL,
`nom_departement` varchar(50) NOT NULL,
PRIMARY KEY (`id_departement`),
UNIQUE KEY `nom_departement` (`nom_departement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Structure de la table `films`
--
DROP TABLE IF EXISTS `films`;
CREATE TABLE IF NOT EXISTS `films` (
`id_film` int(10) unsigned NOT NULL AUTO_INCREMENT,
`titre` varchar(50) NOT NULL,
`titre_original` varchar(50) NOT NULL,
`annee` char(4) NOT NULL,
`id_genre` char(2) NOT NULL,
PRIMARY KEY (`id_film`),
KEY `titre` (`titre`,`id_genre`),
KEY `id_genre` (`id_genre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- --------------------------------------------------------
--
-- Structure de la table `genres`
--
DROP TABLE IF EXISTS `genres`;
CREATE TABLE IF NOT EXISTS `genres` (
`id_genre` char(2) NOT NULL,
`libelle_genre` varchar(50) NOT NULL,
PRIMARY KEY (`id_genre`),
KEY `libelle_genre` (`libelle_genre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Structure de la table `jouer`
--
DROP TABLE IF EXISTS `jouer`;
CREATE TABLE IF NOT EXISTS `jouer` (
`id_film` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_acteur` int(10) unsigned NOT NULL,
`voix` char(1) DEFAULT NULL,
`rang` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id_film`,`id_acteur`) USING BTREE,
KEY `FK_jouer_id_acteur` (`id_acteur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- --------------------------------------------------------
--
-- Structure de la table `projeter`
--
DROP TABLE IF EXISTS `projeter`;
CREATE TABLE IF NOT EXISTS `projeter` (
`id_film` int(10) unsigned NOT NULL,
`id_cinema` char(5) NOT NULL,
`version` varchar(45) NOT NULL,
`semaine` int(10) unsigned NOT NULL,
`annee` char(4) NOT NULL,
PRIMARY KEY (`id_film`,`id_cinema`,`version`) USING BTREE,
KEY `FK_projeter_id_cinema` (`id_cinema`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Structure de la table `realisateurs`
--
DROP TABLE IF EXISTS `realisateurs`;
CREATE TABLE IF NOT EXISTS `realisateurs` (
`id_realisateur` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nom_realisateur` varchar(50) NOT NULL,
PRIMARY KEY (`id_realisateur`),
KEY `nom_realisateur` (`nom_realisateur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- --------------------------------------------------------
--
-- Structure de la table `realiser`
--
DROP TABLE IF EXISTS `realiser`;
CREATE TABLE IF NOT EXISTS `realiser` (
`id_film` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_realisateur` int(10) unsigned NOT NULL,
PRIMARY KEY (`id_film`,`id_realisateur`) USING BTREE,
KEY `id_realisateur` (`id_realisateur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- --------------------------------------------------------
--
-- Structure de la table `villes`
--
DROP TABLE IF EXISTS `villes`;
CREATE TABLE IF NOT EXISTS `villes` (
`cp` char(5) NOT NULL,
`nom_ville` varchar(45) NOT NULL,
`id_departement` int(10) unsigned NOT NULL,
PRIMARY KEY (`cp`) USING BTREE,
KEY `index_nom_ville` (`nom_ville`),
KEY `FK_villes_id_departement` (`id_departement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Contraintes pour les tables exportées
--
--
-- Contraintes pour la table `cinemas`
--
ALTER TABLE `cinemas`
ADD CONSTRAINT `cinemas_ibfk_1` FOREIGN KEY (`cp`) REFERENCES `villes` (`cp`),
ADD CONSTRAINT `cinemas_ibfk_2` FOREIGN KEY (`id_arrondissement`) REFERENCES `arrondissements` (`id_arrondissement`);
--
-- Contraintes pour la table `films`
--
ALTER TABLE `films`
ADD CONSTRAINT `films_ibfk_1` FOREIGN KEY (`id_genre`) REFERENCES `genres` (`id_genre`);
--
-- Contraintes pour la table `jouer`
--
ALTER TABLE `jouer`
ADD CONSTRAINT `FK_jouer_id_acteur` FOREIGN KEY (`id_acteur`) REFERENCES `acteurs` (`id_acteur`),
ADD CONSTRAINT `FK_jouer_id_film` FOREIGN KEY (`id_film`) REFERENCES `films` (`id_film`);
--
-- Contraintes pour la table `projeter`
--
ALTER TABLE `projeter`
ADD CONSTRAINT `FK_projeter_id_cinema` FOREIGN KEY (`id_cinema`) REFERENCES `cinemas` (`id_cinema`),
ADD CONSTRAINT `FK_projeter_id_film` FOREIGN KEY (`id_film`) REFERENCES `films` (`id_film`);
--
-- Contraintes pour la table `realiser`
--
ALTER TABLE `realiser`
ADD CONSTRAINT `FK_realiser_id_film` FOREIGN KEY (`id_film`) REFERENCES `films` (`id_film`),
ADD CONSTRAINT `realiser_ibfk_1` FOREIGN KEY (`id_realisateur`) REFERENCES `realisateurs` (`id_realisateur`);
--
-- Contraintes pour la table `villes`
--
ALTER TABLE `villes`
ADD CONSTRAINT `FK_villes_id_departement` FOREIGN KEY (`id_departement`) REFERENCES `departements` (`id_departement`);
TP Pariscope sur les clés étrangères
ALTER TABLE pariscope.villes
ADD CONSTRAINT FK_villes_departements
FOREIGN KEY FK_villes_departements (id_departement)
REFERENCES departements (id_departement)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.cinemas
ADD CONSTRAINT FK_cinemas_villes
FOREIGN KEY FK_cinemas_villes (cp)
REFERENCES villes (cp)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.cinemas
ADD CONSTRAINT FK_cinemas_arrondissements
FOREIGN KEY FK_cinemas_arrondissements (id_arrondissement)
REFERENCES arrondissements (id_arrondissement)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.films
ADD CONSTRAINT FK_films_genres
FOREIGN KEY FK_films_genres (id_genre)
REFERENCES genres (id_genre)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.projeter
ADD CONSTRAINT FK_projeter_cinemas
FOREIGN KEY FK_projeter_cinemas (id_cinema)
REFERENCES cinemas (id_cinema)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.projeter
ADD CONSTRAINT FK_projeter_films
FOREIGN KEY FK_projeter_films (id_film)
REFERENCES films (id_film)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.jouer
ADD CONSTRAINT FK_jouer_acteurs
FOREIGN KEY FK_jouer_acteurs (id_acteur, id_film)
REFERENCES jouer (id_acteur, id_film)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.jouer
ADD CONSTRAINT FK_jouer_films
FOREIGN KEY FK_jouer_films (id_film)
REFERENCES films (id_film)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.realiser
ADD CONSTRAINT FK_realiser_realisateurs
FOREIGN KEY FK_realiser_realisateurs (id_realisateur)
REFERENCES realisateurs (id_realisateur)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE pariscope.realiser
ADD CONSTRAINT FK_realiser_films
FOREIGN KEY FK_realiser_films (id_film)
REFERENCES films (id_film)
ON DELETE CASCADE
ON UPDATE CASCADE;
TP Pariscope sur les index
Index sur le titre du film
ALTER TABLE films ADD INDEX (titre);
Ou
CREATE INDEX i_films_titre ON films(titre);
Index sur le nom de l'acteur
CREATE INDEX i_acteurs_nom_acteur ON acteurs(nom_acteur);
Index unique sur le genre
CREATE UNIQUE INDEX i_genres_libelle_genre ON films(libelle_genre);
TP Pariscope sur les modifications de table
Ajouter une colonne à la table cinemas : acces_handicapes.
ALTER TABLE cinemas ADD acces_handicapes BOOL NOT NULL;
Modifier le type de l'année du film (INT(4) vers CHAR(4)) et rétablissez ensuite.
ALTER TABLE films CHANGE annee annee CHAR( 4 );
ALTER TABLE films CHANGE annee annee INT( 4 );
Passez la colonne annee du film de NOT NULL à NULL.
ALTER TABLE films CHANGE annee annee INT( 4 ) NULL;
ALTER TABLE films CHANGE annee annee INT( 4 ) NOT NULL DEFAULT 0;
Ajouter une contrainte sur l'année du film (entre 1900 et 2030).
ALTER TABLE films ADD CONSTRAINT films_annee_plage CHECK annee BETWEEN 1900 AND 2030;
Remarque : syntaxe acceptée mais sans aucun effet de contrainte avec MySQL !!! MySQL ne le supporte pas encore (Cf la Documentation officielle).
SQL Insertion des données dans Pariscope
USE pariscope;
-- Contenu de la table departements
INSERT INTO departements (id_departement, nom_departement) VALUES ('75', 'Paris'),
('77', 'Seine et Marne'),
('91', 'Essone'),
('92', 'Hauts-de-Seine'),
('93', 'Seine Saint Denis'),
('94', 'Val de Marne'),
('95', 'Val d''Oise');
-- Contenu de la table villes
INSERT INTO villes (cp, nom_ville, id_departement) VALUES ('75001', 'Paris 1', '75'),
('75011', 'Paris 11', '75'),
('75012', 'Paris 12', '75'),
('94100', 'St Mandé', '94'),
('94200', 'Vincennes', '94');
-- Contenu de la table arrondissements
INSERT INTO arrondissements (id_arrondissement, nom_arrondissement) VALUES ('0', 'Banlieue'),
('1', 'Châtelet Les Halles'),
('11', 'Bastille'),
('12', 'Nation'),
('19', 'Buttes Chaumont'),
('2', 'Opéra');
-- Contenu de la table cinemas
INSERT INTO cinemas (id_cinema, nom_cinema, id_arrondissement, cp, adresse, reseau, acces_handicapes) VALUES ('1', 'Victoria', '1', '75001', '', '', 0),
('111', 'MK 2 Nation', '12', '75012', '', '', 0),
('112', 'Oberkampf', '11', '75011', '', '', 0),
('1244', 'Socrate', '0', '94200', '', '', 0),
('1245', 'Sorano', '0', '94200', '', '', 0);
-- Contenu de la table acteurs
INSERT INTO acteurs (id_acteur, nom_acteur) VALUES (1, 'Robert De Niro'),
(2, 'Julia Roberts'),
(3, 'Hugh Grant'),
(4, 'Al Pacino'),
(5, 'Marlone Brando'),
(6, 'Woody Allen');
-- Contenu de la table realisateurs
INSERT INTO realisateurs (id_realisateur, nom_realisateur) VALUES (1, 'Coppola'),
(2, 'Woody Allen'),
(3, 'Martin Scorsese');
-- Contenu de la table genres
INSERT INTO genres (id_genre, libelle_genre) VALUES ('CD', 'Comédie Dramatique'),
('CM', 'Comédie Musicale'),
('CO', 'Comédie'),
('DR', 'Drame'),
('HI', 'Historique'),
('PO', 'Policier'),
('TH', 'Thriller');
-- Contenu de la table films
INSERT INTO films (id_film, titre, titre_original, annee, id_genre) VALUES (1, 'COUP DE FOUDRE A N', '', 2000, 'CM'),
(2, 'Tout le monde dit I Love You', 'Everyone says I love you', 2002, 'CO'),
(3, 'Guerre et paix', '', 1990, 'CO'),
(4, 'Le parrain', '', 1990, 'PO'),
(5, 'Le parrain 2', '', 2001, 'PO'),
(6, 'Taxi Driver', '', 2000, 'TH');
-- Contenu de la table jouer
INSERT INTO jouer (id_acteur, id_film) VALUES (2, 1),
(3, 1),
(2, 2),
(4, 4),
(5, 4),
(4, 5),
(1, 6);
-- Contenu de la table realiser
INSERT INTO realiser (id_realisateur, id_film) VALUES (2, 2),
(2, 3),
(1, 4),
(1, 5),
(3, 6);
-- Contenu de la table projeter
INSERT INTO projeter (id_cinema, id_film, version, semaine, annee) VALUES ('1', 1, 'VO', 0, 0),
('111', 1, 'VF', 0, 0),
('111', 4, '', 0, 0),
('112', 5, '', 0, 0),
('1245', 6, '', 0, 0);
Corrigés MAJ PARISCOPE
EnoncéCorrigéAjoutez le genre "Divers"INSERT INTO genres (id_genre , libelle_genre) VALUES (
'DI', 'Divers'
);Insérez un film de ce nouveau genreINSERT INTO films(titre,titre_original, annee,id_genre)
VALUES('Joueuse d échecs','','2009','DI');Passez ce film dans les policiersUPDATE films SET id_genre = 'PO' WHERE id_genre = 'DI';Supprimez le genre "divers"DELETE FROM genres WHERE id_genre 'DI';Archivez les films d'avant 2000
Créez la table films_archCREATE TABLE films_arch AS SELECT * FROM films WHERE annee < 2000;
Mettez les titres des films en majusculesUPDATE films SET titre = UPPER(titre);
TP Extraction des données
Corrigés
EnoncéCorrigéREQUETES SIMPLESListez les filmsSELECT * FROM films;Listez les titres et genres des filmsSELECT titre, id_genre FROM films;Listez les titres triésSELECT titre FROM films ORDER BY titre;Listez les policiersSELECT titre FROM films WHERE id_genre = 'PO';Listez les films dont le titre contient "parrain"SELECT * FROM films WHERE titre LIKE '%parrain%';Listez les départements qui contiennent "seine"SELECT * FROM departements WHERE nom_departement LIKE '%seine%';Listez les films sortis entre 2000 et 2002SELECT * FROM films WHERE annee BETWEEN 2000 AND 2002;Listez les films sortis depuis 2000SELECT * FROM films WHERE annee >= 2000;Listez les films ayant un titre originalSELECT * FROM films WHERE titre_original IS NOT NULL;Listez les films n'ayant pas de titre originalSELECT * FROM films WHERE titre_original IS NULL;Listez les films policiers et thrillerSELECT * FROM films WHERE id_genre IN('PO','TH');AGREGATSAffichez le nombre de filmsSELECT COUNT(*) FROM films; Affichez le nombre de projectionsSELECT COUNT(*) FROM projeter;Affichez le nombre de films sortis par annéeSELECT annee, COUNT(*) FROM films GROUP BY annee;Affichez le nombre de films sortis en 2000SELECT COUNT(*) FROM films WHERE annee = 2000 GROUP BY annee;Affichez le nombre de films sortis en 2000SELECT COUNT(*) FROM films GROUP BY annee HAVING annee = 2000;JOINTURESListez les films et leur genre (libelle)SELECT films.titre, genres.libelle_genre
FROM films INNER JOIN genres ON films.id_genre = genres.id_genre;Listez les films de Julia RobertsSELECT films.titre, acteurs.nom_acteur
FROM (films INNER JOIN jouer ON films.id_film = jouer.id_film) INNER JOIN acteurs ON jouer.id_acteur = acteurs.id_acteur
WHERE (((acteurs.nom_acteur)="julia roberts"));Listez les films de CoppolaSELECT films.titre, realisateurs.nom_realisateur
FROM (films INNER JOIN realiser ON films.id_film = realiser.id_film) INNER JOIN realisateurs ON realiser.id_realisateur = realisateurs.id_realisateur
WHERE (((realisateurs.nom_realisateur)="coppola"));Listez les films projetés actuellementSELECT DISTINCT films.titre
FROM films INNER JOIN projeter ON films.id_film = projeter.id_film;Listez les films de Julia Roberts projetés actuellementSELECT films.titre, acteurs.nom_acteur
FROM ((films INNER JOIN projeter ON films.id_film = projeter.id_film) INNER JOIN jouer ON films.id_film = jouer.id_film) INNER JOIN acteurs ON jouer.id_acteur = acteurs.id_acteur
WHERE (((acteurs.nom_acteur)="julia roberts"));Listez les films de Coppola projetés actuellementSELECT films.titre, realisateurs.nom_realisateur
FROM ((films INNER JOIN realiser ON films.id_film = realiser.id_film) INNER JOIN realisateurs ON realiser.id_realisateur = realisateurs.id_realisateur) INNER JOIN projeter ON films.id_film = projeter.id_film
WHERE (((realisateurs.nom_realisateur)="coppola"));JOINTURES ET AGREGATSAffichez le nombre de films de CoppolaSELECT DISTINCT COUNT(films.titre) AS CompteDetitre, realisateurs.nom_realisateur
FROM films INNER JOIN (realisateurs INNER JOIN realiser ON realisateurs.id_realisateur = realiser.id_realisateur) ON films.id_film = realiser.id_film
GROUP BY realisateurs.nom_realisateur
HAVING (((realisateurs.nom_realisateur)="coppola"));Affichez le nombre de films projetés actuellementSELECT DISTINCT COUNT(films.titre) AS CompteDetitre
FROM films INNER JOIN projeter ON films.id_film = projeter.id_film;Affichez le nombre de films projetés actuellement avec Julia RobertsSELECT DISTINCT COUNT(films.titre) AS CompteDetitre, acteurs.nom_acteur
FROM ((films INNER JOIN projeter ON films.id_film = projeter.id_film) INNER JOIN jouer ON films.id_film = jouer.id_film) INNER JOIN acteurs ON jouer.id_acteur = acteurs.id_acteur
GROUP BY acteurs.nom_acteur
HAVING (((acteurs.nom_acteur)="julia roberts"));Affichez le nombre d'acteurs par filmSELECT DISTINCT films.titre, COUNT(jouer.id_acteur) AS CompteDeid_acteur
FROM films INNER JOIN jouer ON films.id_film = jouer.id_film
GROUP BY films.titre;Affichez la liste des cinémas ouvertsSELECT DISTINCT cinemas.nom_cinema
FROM cinemas INNER JOIN projeter ON cinemas.id_cinema = projeter.id_cinema;Affichez la liste des cinémas fermésSELECT DISTINCT cinemas.nom_cinema, projeter.id_film
FROM cinemas LEFT JOIN projeter ON cinemas.id_cinema = projeter.id_cinema
WHERE (((projeter.id_film) Is Null));REQUETES ENSEMBLISTESLa liste des acteurs et des réalisateursSELECT nom_acteur FROM acteurs
UNION
SELECT nom_realisateur FROM realisateurs;IntersectSELECT nom_acteur FROM acteurs
INTERSECT
SELECT nom_realisateur FROM realisateurs;DifférenceSELECT nom_acteur FROM acteurs
EXCEPT
SELECT nom_realisateur FROM realisateurs;REQUETES IMBRIQUEESLes policiersSELECT * FROM films WHERE id_genre = (SELECT id_genre FROM genres WHERE libelle_genre = 'Policier');Les acteurs-réalisateursSELECT * FROM acteurs WHERE nom_acteur IN (SELECT nom_realisateur FROM realisateurs);Les acteurs seulement acteursSELECT * FROM acteurs WHERE nom_acteur NOT IN (SELECT nom_realisateur FROM realisateurs);Les films projetésSELECT * FROM FILMS WHERE id_film IN (SELECT id_film FROM projeter);Les cinémas ouvertsSELECT * FROM cinemas WHERE id_cinema IN (SELECT id_cinema FROM projeter);Les cinémas fermésSELECT * FROM cinemas WHERE id_cinema NOT IN (SELECT id_cinema FROM projeter);L'année où le plus grand nombre de films est sortiSELECT annee, count(*) FROM films GROUP BY annee HAVING COUNT(*) >= ALL
(SELECT COUNT(*) FROM films GROUP BY annee);
La BD Ingénieurs
Le schéma
La création du schéma et l'insertion des données
Cf les scripts :
bd_ingenieurs_innodb.sql,
bd_ingenieurs_myisam.sql,
bd_ingenieurs_inserts.sql.
La BD Ingénieurs Light
Schéma
EMBED MSPhotoEd.3
EMBED MSPhotoEd.3
Script de création et d'insertions dans BD_INGENIEURS_LIGHT
-- Base de données: bd_ingenieurs_light
DROP DATABASE IF EXISTS bd_ingenieurs_light;
CREATE DATABASE bd_ingenieurs_light DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE bd_ingenieurs_light;
-- --------------------------------------------------------
-- Structure de la table competences
DROP TABLE IF EXISTS competences;
CREATE TABLE IF NOT EXISTS competences (
id_competence int(10) unsigned NOT NULL auto_increment,
libelle varchar(45) NOT NULL default '',
PRIMARY KEY (id_competence)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Contenu de la table competences
INSERT INTO competences (id_competence, libelle) VALUES
(1, 'ASP'),
(2, 'JSP'),
(3, 'PHP');
-- --------------------------------------------------------
-- Structure de la table ingenieurs
DROP TABLE IF EXISTS ingenieurs;
CREATE TABLE IF NOT EXISTS ingenieurs (
id_ingenieur int(10) unsigned NOT NULL auto_increment,
nom varchar(45) NOT NULL default '',
PRIMARY KEY (id_ingenieur)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Contenu de la table ingenieurs
INSERT INTO ingenieurs (id_ingenieur, nom) VALUES
(1, 'CASTA'),
(2, 'DUPOND'),
(3, 'TINTIN');
-- --------------------------------------------------------
-- Structure de la table ingenieurs_competences
DROP TABLE IF EXISTS ingenieurs_competences;
CREATE TABLE IF NOT EXISTS ingenieurs_competences (
id_ingenieur int(10) unsigned NOT NULL ,
id_competence int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id_ingenieur,id_competence)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Contenu de la table ingenieurs_competences
INSERT INTO ingenieurs_competences (id_ingenieur, id_competence) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 3);
-- --------------------------------------------------------
-- Structure de la table ingenieurs_projets
DROP TABLE IF EXISTS ingenieurs_projets;
CREATE TABLE IF NOT EXISTS ingenieurs_projets (
id_ingenieur int(10) unsigned NOT NULL default '0',
id_projet int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id_ingenieur,id_projet)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Contenu de la table ingenieurs_projets
INSERT INTO ingenieurs_projets (id_ingenieur, id_projet) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 3);
-- --------------------------------------------------------
-- Structure de la table projets
DROP TABLE IF EXISTS projets;
CREATE TABLE IF NOT EXISTS projets (
id_projet int(10) unsigned NOT NULL auto_increment,
libelle varchar(45) character set utf8 NOT NULL default '',
date_debut date NOT NULL default '0000-00-00',
date_fin date NOT NULL default '0000-00-00',
id_competence int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id_projet)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-- Contenu de la table projets
INSERT INTO projets (id_projet, libelle, date_debut, date_fin, id_competence)
VALUES
(1, 'Web PHP', '2008-02-10', '2008-02-20', 3),
(2, 'Web JSP', '2008-02-10', '2008-02-20', 2),
(3, 'Web ASP', '2008-02-15', '2008-03-10', 1);
Bibliographie
MySQL 5 - Guide officiel
De Paul DuBois , Stefan Hinz , Carsten Pedersen
Editions MySQL Press, juillet 2006
700 pages, 1285 g
La référenceMySQL 5
Installation, mise en uvre, administration et programmation
de Cyril THIBAUD
Editions ENI, Février 2006
468 pages
ISBN: 2-7460-3004-7.
Apprendre SQL avec MySQL
de Christian Soutou
Editions Eyrolles, Mars 2006,
398 pages, 835 g, . Format : 19 x 23
ISBN: 2-212-11915-1
Quelques corrigés
Les subalternes du Boss
SELECT v1.id_vendeur, v1.nom, v1.chef, v2.nom "Nom du chef"
FROM vendeurs v1 JOIN vendeurs v2
ON v1.chef = v2.id_vendeur
AND v2.chef = 0;
Buveurs d'évian
SELECT DISTINCT c.id_client, nom
FROM clients c, cdes cd, ligcdes l, produits p
WHERE c.id_client = cd.id_client
AND cd.id_cde = l.id_cde
AND l.id_produit = p.id_produit
AND designation = 'Evian';
MYSQL 5 - SQL
© Pascal Buguet Imprimé le DATE \@"'d 'MMMM' yyyy'" d avril yyyy Page PAGE 114
Advanded Toolbar
