SGBD - de la documentation du langage SPIP

BES WEBDEVELOPER (5XBDR) / Bachelier en informatique de Gestion (5IBD1)
Initiation aux bases de données / Système de gestion de bases de données
1
BES WEBDEVELOPER
(5XBDR)
Système de gestion de bases de
données
BACHELIER EN
INFORMATIQUE DE
GESTION (5IBD1)
Initiation aux bases de données
L’étudiant sera capable :
dans le cadre de mise en situations simples et de
l'observation de cas réels, en disposant d’une station
informatique opérationnelle équipée d’un logiciel "Bases
de données" et en développant des compétences de
communication:
 d’identifier une base de données ;
 de présenter les éléments essentiels d’un système de
gestion de bases de données (SGBD) ;
 d’expliciter les mécanismes relationnels et le schéma
relationnel dans une base de données ;
 de schématiser une base de données à partir d'un
problème pratique en justifiant les choix effectués ;
à l’aide d’un outil approprié et d’un langage de requête
tel que SQL (Structured Query Language) :
 de créer une base de données par :
o l’identification et la création de tables,
o l’identification et la création d'index (clé
primaire, clé étrangère,…),
o l’identification des champs et la définition à
bon escient du type de données,
o l’identification et la création des relations entre
les tables ;
 de mettre à jour une base de données par:
o la modification de la structure d'une table,
o la modification des index,
o la modification des relations entre les tables ;
 d'intervenir sur le contenu de la base de données par
:
o l’ajout, la modification et la suppression de
données,
 d'interroger une base de données par ;
o des requêtes de sélection (simple, multiple,
avec tri, avec filtre, avec jointure, avec
regroupement, calculée, ...),
o des requêtes ensemblistes (union, intersection,
différence,…) ;
 d'importer et d'exporter des données ;
de recourir à bon escient à la documentation disponible.
L’étudiant sera capable :
dans le cadre d’applications issues des environnements
informatique et technique, en disposant d’une station
informatique opérationnelle équipée d’un logiciel « Bases
de données » et en développant des compétences de
communication,
 de définir une base de données ;
 de présenter les éléments essentiels d’un système de
gestion de bases de données (SGBD) ;
 de créer une table, un index en utilisant différents
types de données et de formats d’affichage de ces
données sur un système de gestion de bases de
données relationnelles ;
 d’expliciter les mécanismes relationnels et le schéma
relationnel dans une base de données ;
 d’implémenter sur des exemples pratiques le schéma
relationnel ;
 d’utiliser une clé primaire et les vues ;
 d’introduire et d’utiliser des tables à jonctions
(jointure) ;
 d’utiliser les éléments essentiels d’un langage tel que
SQL ;
 de créer des tables à l’aide du langage choisi ;
 d’effectuer des sélections à l’aide du langage de
requête : requêtes, tri simple, tri multiple,
élimination des doublons, requêtes avec création de
champs, jointure, regroupement,… applications
pratiques sur un système de gestion de bases de
données relationnelles ;
 d’appliquer les opérations ensemblistes en SQL,… ;
 d’importer et d’exporter des données.

BES WEBDEVELOPER (5XBDR) / Bachelier en informatique de Gestion (5IBD1)
Initiation aux bases de données / Système de gestion de bases de données
2
Table des matières
1. Introduction – Théorie des bases de données relationnelles ....................................................................................... 6
1.1 Définitions ............................................................................................................................................................... 6
2 Administration avec l’outil phpMyAdmin ...................................................................................................................... 7
2.1 Présentation ........................................................................................................................................................... 7
2.2 phpMyAdmin : Création/sélection d’une base de données ................................................................................... 8
2.3 phpMyAdmin : Gestion de la base de données ...................................................................................................... 9
2.4 phpMyAdmin : Affichage d’une table ..................................................................................................................... 9
2.5 phpMyAdmin : Insertion/modification d’un enregistrement................................................................................. 9
2.6 phpMyAdmin : Gestion d’un table ....................................................................................................................... 10
2.7 phpMyAdmin : Création d’une clé ........................................................................................................................ 11
3. Création d’une base de données ................................................................................................................................ 12
3.1 Identification et création de tables ...................................................................................................................... 12
3.2 Identification des champs et leurs types de données .......................................................................................... 14
3.2.1 Types des attributs ........................................................................................................................................ 14
3.2.2 Entiers ............................................................................................................................................................ 14
3.2.3 Flottants ........................................................................................................................................................ 14
3.2.4 Chaînes .......................................................................................................................................................... 15
3.2.5 Dates et heures ............................................................................................................................................. 15
3.2.6 Ensembles ..................................................................................................................................................... 16
3.3 Création des relations entre les tables ................................................................................................................. 16
3.3.1 Clé primaire ................................................................................................................................................... 16
3.3.2 Attribut non nul ............................................................................................................................................. 16
3.3.3 Valeur par défaut .......................................................................................................................................... 16
3.3.4 Attribut sans doublon .................................................................................................................................... 16
3.3.5 Index .............................................................................................................................................................. 17
3.3.6 Exercices – création de table ............................................................................................................................. 17
4. Gestion du contenu de la base de données ................................................................................................................ 17
4.1 Ajout de données .................................................................................................................................................. 17
4.2 Modification de données ...................................................................................................................................... 19
4.3 Suppression de données ....................................................................................................................................... 19
4.4 Exercices – insertion de données ......................................................................................................................... 20
5. Intégrité référentielle .................................................................................................................................................. 20
5.1 Exercices – Analyse/ Intégrité référentielle .......................................................................................................... 22
6. Mise à jour d’une base de données ............................................................................................................................ 23
6.1 Modification d’une table ...................................................................................................................................... 23
6.1.1 Ajouter un attribut ........................................................................................................................................ 23
6.1.2 Supprimer un attribut ................................................................................................................................... 23

BES WEBDEVELOPER (5XBDR) / Bachelier en informatique de Gestion (5IBD1)
Initiation aux bases de données / Système de gestion de bases de données
3
6.1.3 Créer une clé primaire ................................................................................................................................... 23
6.1.4 Supprimer une clé primaire ........................................................................................................................... 24
6.1.5 Ajout d’une contrainte d’unicité ................................................................................................................... 24
6.1.6 Changer la valeur par défaut d’un attribut ................................................................................................... 24
6.1.7 Changer la définition d’un attribut ................................................................................................................ 24
6.1.8 Changer le nom d’une relation ...................................................................................................................... 24
6.1.9 Ajouter un index ............................................................................................................................................ 25
6.1.10 Supprimer un index ..................................................................................................................................... 25
6.2 Suppression d’une table ....................................................................................................................................... 25
6.3 Exercices – mise à jour d’une base de données ................................................................................................... 25
7 Interrogation de base de données ............................................................................................................................... 27
7.1 Requêtes de sélection........................................................................................................................................... 27
7.1.1 Simple ............................................................................................................................................................ 27
7.1.2 Avec filtre ...................................................................................................................................................... 28
7.1.3 Avec tri .......................................................................................................................................................... 28
7.1.4 Exercices – Sélection simple/filtre/tri ................................................................................................................ 29
7.1.5 Avec jointure ................................................................................................................................................. 29
7.1.6 Multiple ......................................................................................................................................................... 29
7.1.6.1 Requêtes imbriquées : ............................................................................................................................ 31
7.1.6.2 Requêtes sélection multi-tables: ............................................................................................................ 31
7.1.7 Avec regroupement ....................................................................................................................................... 32
7.1.7.1 MySQL gère-t-il le GROUP BY comme les autres SGBD ? ........................................................................ 33
7.1.7.2 Regroupement sur plusieurs critères : .................................................................................................... 33
7.1.8 Champs calculés ............................................................................................................................................ 34
7.1.9 Fonctions de MySQL ...................................................................................................................................... 34
7.1.9.1 Quelques exemples ................................................................................................................................. 34
7.1.9.2 Fonctions de comparaison de chaînes .................................................................................................... 35
7.1.9.3 Fonctions mathématiques ...................................................................................................................... 35
7.1.9.4 Fonctions de chaînes ............................................................................................................................... 35
7.1.9.5 Fonctions de dates et heures .................................................................................................................. 36
7.1.9.6 Fonctions à utiliser dans les GROUP BY .................................................................................................. 36
7.1.10 Les jointures internes .................................................................................................................................. 37
7.1.11 Les jointures externes ................................................................................................................................. 37
7.1.11.1 left outer ............................................................................................................................................... 37
7.1.11.2 right outer ............................................................................................................................................. 38
7.1.12 Exercices - jointures internes/externes/requêtes jointures/multiples/avec regroupement/champ calculée 39
7.2 Requêtes ensemblistes ......................................................................................................................................... 41
7.2.1 Union ............................................................................................................................................................. 41
7.2.2 Intersection ................................................................................................................................................... 41

BES WEBDEVELOPER (5XBDR) / Bachelier en informatique de Gestion (5IBD1)
Initiation aux bases de données / Système de gestion de bases de données
4
7.2.3 Différence ...................................................................................................................................................... 42
7.2.4 Le produit cartésien ...................................................................................................................................... 42
7.2.5 Exercices -Requêtes ensemblistes .................................................................................................................... 43
8. Importation/Exportation des données ....................................................................................................................... 44
8.1 Réalisation d’un backup sous PHPMyAdmin ........................................................................................................ 44
8.2 Restorer une base de donnée sous PHPMyAdmin ............................................................................................... 45
8.2.1 Exemple d’importation sous OVH ................................................................................................................. 46
8.3 Exporter une base de données MySQL ................................................................................................................. 47
8.4 Sauvegarder une base de données MySQL avec mysqldump et PHP .................................................................. 48
8.4 Exercices – mysqldump ......................................................................................................................................... 48
9. Cas pratiques – Analyse .............................................................................................................................................. 49
9.1 Service achats ....................................................................................................................................................... 49
9.2 Élections en Syldavie............................................................................................................................................. 49
9.3 Région Bruxelloises – administration ................................................................................................................... 49
9.4 Région Bruxelloises – bâtiments ........................................................................................................................... 49
9.5 Région Bruxelloises – administration/bâtiments V1 ............................................................................................ 49
9.6 Région Bruxelloises – administration/bâtiments V2 ............................................................................................ 49
9.7 IFOSUP .................................................................................................................................................................. 49
9.8 Agriculteur ............................................................................................................................................................ 50
9.9 Forum .................................................................................................................................................................... 50
9.10 Bibliothèque ....................................................................................................................................................... 50
9.11 Camping .............................................................................................................................................................. 50
9.12 Fleuves ................................................................................................................................................................ 51
9.13 Gestion d’anciens ............................................................................................................................................... 51
9.14 Gestion des logements dans un agence immobilière ......................................................................................... 51
9.15 Pairi Daiza ........................................................................................................................................................... 51
9.16 Toutenbois .......................................................................................................................................................... 52
9.17 UGC ..................................................................................................................................................................... 52
10 Cas pratiques – Requêtes ........................................................................................................................................... 53
10.1 Gestion d’une école ............................................................................................................................................ 53
10.2 bibliothèque ........................................................................................................................................................ 56
10.3 Clinique ............................................................................................................................................................... 56
10.4 Montagnes .......................................................................................................................................................... 57
10.5 Spectacles ........................................................................................................................................................... 59
10.6 voyages ............................................................................................................................................................... 60
10.7 World .................................................................................................................................................................. 61
10.8 Comptoir ............................................................................................................................................................. 62
11. Interface avec PHP .................................................................................................................................................... 63
11.1 Choix de l’API ...................................................................................................................................................... 63

BES WEBDEVELOPER (5XBDR) / Bachelier en informatique de Gestion (5IBD1)
Initiation aux bases de données / Système de gestion de bases de données
5
11.2 PDO ..................................................................................................................................................................... 63
11.2.1 Activation - Php Windows ........................................................................................................................... 63
11.2.1 Connexion : ................................................................................................................................................. 64
11.2.2 Gestion d’erreur .......................................................................................................................................... 64
11.2.2.1 Erreur "could not find driver" ............................................................................................................... 64
11.2.2.2 Erreur "Unknown MySQL server host" ................................................................................................. 64
11.2.2.3 Erreur "Can't connect to MySQL server" .............................................................................................. 64
11.2.2.4 Erreur "Unknown database <db_name>" ............................................................................................. 64
11.2.2.5 Erreur "Access denied for user" ............................................................................................................ 64
11.2.2.5 Intercepter les erreurs de connexion avec un try { catch } ................................................................... 64
11.2.3 Méthodes : exec et query ........................................................................................................................... 65
11.2.3.1 SELECT - QUERY ..................................................................................................................................... 65
11.2.3.2 setFetchMode / fetch ........................................................................................................................... 65
11.2.3.3 UPDATE et DELETE - EXEC ..................................................................................................................... 65
11.2.4 Les requêtes préparées ............................................................................................................................... 66
11.2.4.1 Paramètres mystères ............................................................................................................................ 66
11.2.4.2 Paramètres nommés ............................................................................................................................. 66
11.2.4.3 Sécuriser sans passer par des requêtes préparées ............................................................................... 67
11.3 Ancienne extension mysql .................................................................................................................................. 67
11.3.1 mysql_connect – Ouvre une connexion à un serveur MySQL .................................................................... 67
11.3.2 mysql_close() - Ferme la connexion MySQL ............................................................................................... 67
11.3.3 Interrogation – mysql_query ....................................................................................................................... 68
11.3.4 Extraction des données ............................................................................................................................... 68
11.3.5 Fonctions sur le serveur .............................................................................................................................. 69
12 Sources ....................................................................................................................................................................... 69
13. Evaluation.................................................................................................................................................................. 69