C_CoursDB2016

COURS DE
©
HOFMANS Pierre
Novembre 2016
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
PARTIE 1 : INTRODUCTION GENERALE ........................ 1
1.
2.
Introduction ............................................................. 1
Importance de la base de données ......................................... 1
PARTIE 2 : CONCEPTION D'UNE BASE DE DONNEES ............. 1
1.
2.
3.
Qu'est ce qu'une base de données ? .......................................
Structure d'une base de données ..........................................
A. Notion de table .......................................................
B. Notion d'enregistrement et de champ ...................................
C. Notion de liens entre les tables ......................................
D. Notion de clé primaire ................................................
E. Notion d'indexation des données .......................................
F. Les opérations de base ................................................
Méthodologie à suivre pour la conception d'une base de données ...........
A. Analyse des souhaits, besoins et situation actuelle du client .........
B. Identifier les entités ................................................
C. Les attributs des entités .............................................
D. Les identifiants des entités ..........................................
E. Les liens entre les entités, le schéma relationnel ....................
F. Les optimisations possibles ...........................................
1
1
1
2
2
3
3
4
5
5
5
6
7
7
8
PARTIE 3 : PhpMyAdmin ................................... 1
1.
2.
PhpMyAdmin ............................................................... 1
A. Installation / Préparation du PC ...................................... 1
B. Introduction .......................................................... 1
C. Créer une base de données ............................................. 1
D. Définir une table ..................................................... 2
E. La clé primaire et l'indexation ....................................... 5
F. Ajouter un enregistrement ............................................. 6
G. Consulter le contenu d'une table ...................................... 7
H. Modifier un enregistrement ............................................ 8
I. Supprimer un enregistrement ........................................... 8
J. Modifier la structure d'une table ..................................... 9
Autres fonctions importantes de phpMyAdmin .............................. 10
A. Ecran SQL ............................................................ 10
B. Ecran Rechercher ..................................................... 11
C. Exportation .......................................................... 12
D. Importation .......................................................... 12
PARTIE 4 : LES COMMANDES SQL ............................ 1
1.
2.
Introduction ............................................................. 1
Lecture d'une table ...................................................... 1
A. La commande SQL SELECT de base ........................................ 1
19.04.17
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
3.
4.
5.
6.
7.
B. La clause DISTINCT ....................................................
C. La clause WHERE .......................................................
D. La clause ORDER BY ....................................................
E. La clause LIMIT .......................................................
Ajout d'un nouvel enregistrement dans une table de la DB .................
Suppression d'enregistrements d'une table de la DB .......................
Modification d'enregistrements d'une table de la DB ......................
Lien entre plusieurs tables ..............................................
A. L'équi-jointure .......................................................
B. Les jointures externes LEFT ...........................................
C. Les jointures externes RIGHT ..........................................
Diverses fonctions .......................................................
A. L'opérateur IN ........................................................
B. La fonction COUNT() ...................................................
C. La fonction SUM() .....................................................
D. La clause GROUP BY ....................................................
E. La fonction AVG() .....................................................
F. Les fonctions MIN() et MAX() ..........................................
2
2
3
4
4
4
5
5
5
7
7
8
8
8
8
9
9
9
PARTIE 5 : GESTION D'UNE DB D'UN SITE INTERNET .......... 1
1.
Accéder à la base de données à partir de PHP .............................
A. Introduction ..........................................................
B. Connexion à la base de données ........................................
C. Lecture et traitement de données d'une table de la DB .................
D. Utilisation de variables dans les requêtes ............................
19.04.17
1
1
1
2
3
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
1. INTRODUCTION
Le but de ce cours est d'apprendre dans un premier temps ce qu'est une base de
données. Mais, savoir ce que c'est ne suffit bien évidemment pas, nous allons
donc également apprendre à l'utiliser et plus important encore, nous allons
apprendre à la concevoir. Nous allons donc étudier toutes les phases de la vie
d'une DB (Data Base).
Pour cela, nous allons apprendre à :

Analyser les besoins en données actuels et futurs des applications

Schématiser l'ensemble des besoins

Transformer le schéma des besoins en base de données

Utiliser les commandes de gestion d'une base de données à partir de
programmes
2. IMPORTANCE
DE LA BASE DE DONNÉES
On ne soulignera jamais assez l'importance d'une bonne base de données dans la
gestion des entreprises. Au contraire me direz-vous, tout le monde sait qu'avoir
une liste bien faite de ses clients, fournisseurs, produits, factures est très
important. Oui, effectivement tout le monde le sait, mais je ne parle pas de
cela. Quand je parle de l'importance d'une base de données, je ne parle pas du
contenu des informations qui est bien évidemment très important, je parle de la
structure des informations. Comment toutes ces données sont-elles sauvées dans
nos classeurs, armoires, systèmes informatiques, etc. ? Avoir toutes les
informations est bien évidemment capital, mais ce qui est aussi capital est de
bien les ranger et de les structurer de manière telle que l'on puisse les
retrouver et les manipuler rapidement et efficacement.
C'est là tout le défi à relever lors
Si vos données sont mal rangées, mal
difficultés à les manipuler. Et dans
traiter du tout. Il est donc crucial
actuelles et futures, d'analyser les
on a besoin, de structurer les liens
rédaction des applications.
de l'informatisation d'une entreprise,...
structurées, vous aurez beaucoup de
le pire des cas, vous ne saurez pas les
d'analyser les besoins des applications
informations dont on dispose et celles dont
entre celles-ci avant de se lancer dans la
Souvenez-vous donc que la structure de votre base de données est toute aussi
importante que son contenu... et il est plus facile d'améliorer son contenu que
de changer sa structure. Donc, n'hésitez pas à passer du temps sur l'analyse des
besoins et sur la conception de votre base de données.
19.04.17
Introduction - 1
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
1. QU'EST
CE QU'UNE BASE DE DONNÉES
?
Avant de voir comment concevoir une base de données, voyons d'abord ce que
c'est.
Auparavant, l'information était conservée sur papier dans des dossiers qui
étaient rangés dans des classeurs. Ces derniers contenaient l'ensemble des
dossiers des livres et des lecteurs d'une bibliothèque par exemple. Ils
correspondaient à ce qu'on appelle aujourd'hui une base de données.
On pouvait extraire de l'information de ces classeurs. Pour connaître le nombre
d'emprunts de l'année de chaque lecteur, il fallait sortir un par un les
dossiers de chaque lecteur et faire le total de tous les emprunts de l'année.
En informatique, une base de données est une collection de renseignements ou de
données classés par sujet. Un classeur contenant tous les dossiers des clients
d'une entreprise constitue la base de données des clients.
Ils existent des logiciels de gestion de bases de données ainsi que des langages
spécifiques qui ont pour but de faciliter la manipulation des données. Ces
logiciels/langages permettent de trier, analyser, afficher, ajouter, supprimer,
modifier,... rapidement les données.
2. STRUCTURE D'UNE
BASE DE DONNÉES
Prenons pour exemple une base de données contenant l'ensemble des renseignements
sur la gestion d'une bibliothèque.
A. NOTION DE TABLE
L'organisation de cette base de données pourrait être telle qu'on aurait un
dossier "Lecteurs", un dossier "Livres" et un dossier "Emprunts". En
informatique, on dira que la base de données "Bibliothèque" contient les tables
"Lecteurs", "Livres" et "Emprunts".
En d'autres mots, une base de données (DB) est un grand fichier composé de
plusieurs tables.
Une table est une sorte de tableau à deux dimensions contenant des informations
sur un même sujet. Ainsi le fichier "Livres" contiendra les informations sur les
livres, mais pas sur les lecteurs :
Livres
Reference
Titre
Auteurs
TypeLivre
AnneeEdition
NbrExemplaires
19.04.17
Conception d'une DB - 1
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
B. NOTION D'ENREGISTREMENT ET DE CHAMP
Une table reprend donc les données relatives à un type d'informations que l'on
gère. Par exemple, la table "Lecteurs" contiendra les données des clients de la
bibliothèque. Chaque "fiche client" aura la même structure qui pourrait être :
Référence...
Nom.........
Prénom......
Naissance...
Adresse.....
Ville.......
Tél.........
Commentaires
:
:
:
:
:
:
:
:
Signalétique lecteur
____
_________________________
_________________________
__/__/____
_________________________
_________________________ Code postal : ____
____________
________________________________________
________________________________________
Un enregistrement correspond à une "fiche client". En d'autres mots, il reprend
toutes les informations relatives à une entité déterminée (= un client) de la
table.
Un enregistrement est lui-même décomposé en petites zones appelées champs. Un
champ désigne donc chacune des données stockées dans un enregistrement.
Remarques :
En informatique, il existe souvent deux manières de visualiser les données :
soit sur une fiche (cf. ci-avant), soit dans un tableau. On admettra que la
structure "tableau" est exactement la même que celle de la fiche si ce n'est que
les données sont écrites sur une seule ligne. Dans cette représentation, un
enregistrement correspond à une ligne et un champ à une colonne.
Référence
Nom
Naissance
Adresse
Ville
Code postal Tél.
Commentaires
___
..... .....
Prénom
__/__/____
.....
.....
____
.....
.....
___
..... .....
__/__/____
.....
.....
____
.....
.....
___
..... .....
__/__/____
.....
.....
____
.....
.....
___
..... .....
__/__/____
.....
.....
____
.....
.....
Lorsque l'on crée une base de données, il est très important de bien réfléchir
tant à sa structure générale, qu'à la structure de chacune de ses tables. En
effet, comme nous le verrons par la suite, toutes les opérations que l'on pourra
effectuer par la suite dépendront toujours de la manière dont les données ont
été structurées. Certaines opérations seront rendues très complexes, voire
impossibles, si les différentes structures ont été mal dessinées. La définition
des tables est donc un travail très important qui vaut la peine qu'on s'y
attarde. Tout le temps "perdu" à ce niveau se rattrapera largement par la suite.
C. NOTION DE LIENS ENTRE LES TABLES
Dans le cadre de l'informatisation de la gestion de la bibliothèque reprise dans
notre exemple, on établit la liste des listes de contrôle que le bibliothécaire
pourrait avoir besoin dans le futur. Un des souhaits possibles serait de pouvoir
vérifier tous les livres empruntés depuis le début de l'année. Nous, informati-
19.04.17
Conception d'une DB - 2
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
ciens, devons-nous poser la question suivante : "Que faut-il comme informations
dans la base de données pour pouvoir répondre à sa demande ?". La réponse est
évidemment celle-ci : il faut une table reprenant tous les emprunts effectués
depuis le début de l'année (voire depuis la création de la bibliothèque). La
question découlant de cette réponse est : "Quels champs devons-nous y définir ?"
Il faut, bien évidemment, retenir la date de l'emprunt, ainsi que les informations relatives au lecteur et au livre. Mais est-il nécessaire de sauver dans
cette table toutes les données du lecteur et du livre ? Non, il suffit d'y
retenir les références du lecteur et du livre ! En effet, toutes les autres
données se trouvent déjà dans les autres tables de la base de données. Il nous
suffit d'établir un lien entre les tables "Emprunts" et "Lecteurs", ainsi qu'un
lien entre les tables "Emprunts" et "Livres" pour avoir toutes les informations
nécessaires.
En d'autres mots, les liens entre les tables évitent d'avoir à répéter les
informations d'une table dans une autre. Et puisque l'on parle de répétition des
données, insistons sur le fait qu'il est très important d'éviter toute
répétition des données dans une base de données et ce pour diverses raisons :
 Eviter de consommer de la place mémoire inutilement
 Eviter une perte de temps lors des mises-à-jour des informations
redondantes (ex.: adresse d'un lecteur,...)
 Eviter les incohérences entre les données si l'on oublie un des champs
dupliqués lors des mises-à-jour
Précisons que le lien, physique ou logique, se fait grâce à un champ commun qui
est une clé primaire dans l'une des deux tables.
D. NOTION DE CLÉ PRIMAIRE
Si nous reprenons notre exemple de table "Lecteurs" et que nous recherchons les
informations concernant une personne précise, cela peut prendre beaucoup de
temps si les données ne sont pas triées. En effet, si rien n'est précisé au
système, il classe les informations dans l'ordre suivant lequel on les a
introduites. Par contre, si on lui a demandé de trier les données sur le champ
Nom, on retrouvera les informations de la personne voulue très rapidement.
Un problème subsiste toutefois. En effet, comment faire si deux personnes ont le
même nom ? Pour contourner ce problème, les informaticiens ont défini la notion
de clé primaire.
Une clé primaire est une zone (ou un ensemble de zones) qui joue trois rôles :
- Le premier est d'assurer une identification unique de chaque enregistrement
de la table.
- Le deuxième est de préciser l'ordre suivant lequel les données seront triées.
- Le troisième consiste à établir des liens entre les diverses tables de la
base de données.
Dans notre exemple, le champ "Référence" sera utilisé comme clé primaire. Cette
référence sera définie comme étant unique et ne sera donc jamais attribuée à
deux lecteurs.
E. NOTION D'INDEXATION DES DONNÉES
Si une clé primaire est souvent indispensable pour toutes les tables, elle n'est
toutefois pas suffisante. En effet, il ne faut pas oublier que le rôle principal
19.04.17
Conception d'une DB - 3
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
d'une clé primaire est d'identifier de manière unique un enregistrement dans une
table. C'est pour cette raison que l'on attribue généralement une référence
(numérique ou autre) à chaque enregistrement ; référence qui sert de clé
primaire. Mais qu'arrive-t-il lorsque l'on recherche un enregistrement dont on
ne connaît pas cette référence ? Il suffit alors, simplement, de rechercher
l'enregistrement en comparant le contenu d'autres champs (ex.: le nom d'une
personne ou le titre d'un livre)... Le problème, c'est que dans ce cas, le
système doit lire tous les enregistrements de la table pour trouver celui que
l'on a demandé. Ce qui peut prendre beaucoup de temps. Pour éviter cette perte
de temps, on va créer un index sur les champs des tables que l'on utilisera lors
des recherches. On pourrait donc indexer un champ "Ville" pour retrouver
rapidement tous les clients de Bruxelles.
Attention, contrairement à la clé primaire, les index supplémentaires autorisent
les doublons (= valeur identique pour deux enregistrements).
F. LES OPÉRATIONS DE BASE
Attention, certaines opérations dépendent fortement du système de gestion de
base de données utilisé. Les informations données ci-après ne seront donc que
générales et devront être adaptées en fonction du système utilisé.
Les opérations de base sont :
- L'ouverture de la base de données :
Lorsque l'on veut travailler avec une DB complète, la première opération à
effectuer est de l'ouvrir.
- L'ouverture d'une table :
Lorsque l'on veut travailler avec une table, la première opération à
effectuer est de l'ouvrir.
Certains systèmes exigeront aussi que l'on indique l'index à utiliser ;
d'autres systèmes rechercheront par eux-mêmes l'index à utiliser lors des
commandes de lecture.
- La lecture directe d'un enregistrement :
Cette opération consiste à rechercher, via un index, l'enregistrement
correspondant à la demande et à le copier en mémoire.
Pour permettre cette lecture, il faut bien évidemment indiquer les critères
de recherche (ex.: Reference = "ABC123").
- La lecture globale de plusieurs enregistrements (via les commandes SQL) :
Cette opération consiste à rechercher et copier dans un tableau en mémoire
tous les enregistrements qui répondent aux critères de sélection.
Lors de cette opération, on peut également souvent indiquer l'ordre suivant
lequel on souhaite que le système trie les enregistrements avant de les
copier dans le tableau.
Cette commande permet généralement aussi de fusionner des informations venant
de plusieurs tables.
- L'écriture d'un enregistrement :
Cette opération consiste à écrire un enregistrement dans la table.
- La modification d'un enregistrement :
Cette opération consiste à modifier un enregistrement dans la table.
19.04.17
Conception d'une DB - 4
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
- La modification globale de plusieurs enregistrements (via SQL) :
Cette opération utilisant une requête SQL (ou query) consiste à rechercher et
modifier tous les enregistrements qui répondent aux critères de sélection.
- La suppression d'un enregistrement :
Cette opération consiste à supprimer définitivement un enregistrement de la
table.
- La suppression globale de plusieurs enregistrements (via SQL) :
Cette opération utilisant une requête SQL consiste à rechercher et supprimer
tous les enregistrements qui répondent aux critères de sélection.
- Le test de lecture erronée (enregistrement non trouvé) :
Lorsque l'on lit un enregistrement, il faut toujours vérifier si le système a
trouvé l'enregistrement demandé.
- La fermeture de la table :
Cette commande ferme simplement la table.
- La fermeture de la DB :
Cette commande ferme simplement la DB.
Attention, il faut toujours fermer soigneusement un fichier ouvert avant de
quitter le programme !
3. MÉTHODOLOGIE
À SUIVRE POUR LA CONCEPTION D'UNE BASE DE DONNÉES
Lorsque l'on doit créer une nouvelle base de données, ou même si l'on veut
adapter une base de données existante, il est conseillé de suivre une certaine
méthodologie. Il en existe plusieurs, en voici donc une qui reprend la base de
plusieurs de celles-ci.
Il est conseillé de travailler en suivant les étapes suivantes :
1. Analyser les souhaits, besoins et situation actuelle du client
2. Identifier les entités en présence
3. Lister leurs attributs
4. Définir leurs identifiants
5. Définir les liens entre les entités et ainsi établir le schéma relationnel
6. Optimiser au mieux la base de données
A. ANALYSE DES SOUHAITS, BESOINS ET SITUATION ACTUELLE DU CLIENT
La première étape consiste bien évidemment à rencontrer le client pour savoir ce
qu'il souhaite, quel est son environnement de travail, quels sont ses besoins
actuels mais aussi futurs,... Existe-t-il déjà une base de données ?
Attention, on ne doit pas se contenter de ce qu'il dit, il faut se mettre à sa
place, tenter de comprendre son métier et imaginer tout ce que l'informatique
peut lui apporter.
Il faut ensuite lui faire une présentation de ce que l'on compte réaliser au
niveau informatique afin de voir si l'on a bien compris ses besoins et souhaits.
B. IDENTIFIER LES ENTITÉS
La deuxième étape consiste à identifier les entités.
Une entité est un ensemble d’objets de même structure au sujet desquels on
conserve de l’information dans la base de données. Par même structure, on entend
19.04.17
Conception d'une DB - 5
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
les éléments pour lesquels on enregistre les mêmes informations. A chaque entité
correspondra au moins une table.
Dans notre exemple de gestion d'une bibliothèque, on aurait plusieurs entités
dont, par exemple, les livres, les lecteurs. Mais on pourrait en avoir d'autres
en fonction du niveau d'informatisation de la gestion tels que le personnel,
etc.
Dans un magasin, on pourrait avoir des entités telles que les clients, les
articles, les fournisseurs, etc.
C. LES ATTRIBUTS DES ENTITÉS
Par attribut, on entend les informations liées aux entités. En fait, les
attributs correspondent aux champs des tables.
Quelles sont les informations nécessaires et utiles pour les traitements
informatiques à développer ? Dans quelle table doit-on les stocker pour pouvoir
les retrouver facilement ?
C'est aussi lors de cette étape que l'on va éventuellement définir de nouvelles
entités auxquelles on n'avait pas pensé lors de l'étape précédente.
En effet, il faut veiller à ce que la base de données contienne toutes les
informations nécessaires, mais aussi évite toute redondance des informations qui
y sont contenues et reste cohérente.
Si nous prenons l'exemple de l'entité Livres de la base de données Bibliothèque,
il pourrait s'avérer nécessaire d'y ajouter de nouvelles entités qui
contiendraient :
 les Types des livres (ex.: Roman, biographie,...) afin de garder une
certaine cohérence des données pour tous les livres (toujours le même nom
avec la même orthographe afin de faciliter de futures recherches et/ou
sélections)
 les Auteurs avec leur biographie. Cette entité séparée permettra d'éviter
les redondances d'informations telles que les best-sellers de l'auteur au
niveau de chaque livre.
Petit conseil : Lors de la définition des champs, il faut veiller à faire des
champs ne contenant qu'une seule information. On ne créera pas le champ Adresse,
mais les champs Rue, Numéro, Boîte, Code Postal, Localité,... Cela facilitera
19.04.17
Conception d'une DB - 6
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
fortement les traitements ultérieurs de recherche,...
D. LES IDENTIFIANTS DES ENTITÉS
Chaque élément d'une entité doit pouvoir être identifiable de manière unique.
C'est pourquoi toutes les entités doivent posséder un attribut sans doublon
(c'est-à-dire sans qu'il soit possible d'avoir une deuxième occurrence dans la
même entité).
Cet identifiant correspond généralement à une référence numérique ou, plus
rarement, alphanumérique.
Dans notre exemple, on associera un numéro à chaque lecteur. En effet, nous ne
pouvons pas utiliser leurs noms et prénoms comme identifiant unique car il est
possible qu'il y ait plusieurs lecteurs avec les mêmes noms et prénoms.
Cet identifiant servira aussi de clé primaire.
Pour rappel, une clé primaire est une zone (ou un ensemble de zones) qui joue
trois rôles :
- Le premier est d'assurer une identification unique de chaque enregistrement
de la table.
- Le deuxième est de préciser l'ordre suivant lequel les données seront triées.
- Le troisième consiste à établir des liens entre les diverses tables de la
base de données.
Attention, lorsque l'on choisit l'identifiant d'une table, il faut éviter les
identifiants susceptibles de changer avec le temps tels que les numéros des
cartes d'identité, les âges, les adresses,...
E. LES LIENS ENTRE LES ENTITÉS, LE SCHÉMA RELATIONNEL
Il faut à présent définir les liens entre les différentes entités/tables. Dans
notre exemple, on créera différentes associations :
19.04.17
Conception d'une DB - 7
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
-
Un lecteur empruntera 0, un ou plusieurs livres
Un livre sera emprunté par 0, un ou plusieurs lecteurs
Un livre sera écrit par un ou plusieurs auteurs
Un auteur écrira un ou plusieurs livres
Lors d'un emprunt, plusieurs livres peuvent être emportés
On remarquera aussi que s'il n'y a pas de lien direct entre les lecteurs et les
livres, un lien indirect existe via les emprunts.
De plus, sur le schéma relationnel, on notera aussi la cardinalité des
relations. Celle-ci indique le nombre maximum de fois qu'un élément peut être
concerné par l'association.
Enfin, on remarquera aussi que les champs utilisés au niveau des liens sont
généralement la clé primaire d'une entité d'une part et son équivalent dans la
seconde entité d'autre part.
F. LES OPTIMISATIONS POSSIBLES
Pour les bases de données relationnelles, l'optimisation qui vise à accélérer
les requêtes peut passer par :

l'ajout d'index aux tables sur les champs régulièrement utilisés lors des
recherches ou jointures.

l'ajout de champs calculés ou de certaines redondances pour éviter des
jointures coûteuses. Mais, dans ce cas, il faudra veiller à ce que la
cohérence entre les données soit respectée.

la suppression des contraintes d'unicité au sein d'une table. Par exemple,
les tables Emprunts et LignesEmprunt seraient fusionnées et on ne
garderait que la table des lignes des emprunts.
19.04.17
Conception d'une DB - 8
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Exercice :
Faire l'exercice DB1.
19.04.17
Conception d'une DB - 9
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
1. PHPMYADMIN
A. INSTALLATION / PRÉPARATION DU PC
PhpMyAdmin est un gestionnaire de base de données qui fonctionne sur les
serveurs mais pas directement sur votre PC ; ce qui est embêtant lors de la
construction ou modification d'une base de données que l'on souhaite tester en
local sur son pc avant de l'héberger et la rendre ainsi accessible à tous les
internautes. C'est pourquoi on installera sur son pc un serveur local qui
permettra de la tester avant de l'héberger.
PhpMyAdmin étant intégré au sein de WampServer, installons et configurons ce
dernier tel qu'indiqué dans le document WampServerWindows10.docx en annexe.
B. INTRODUCTION
Un logiciel de gestion de DB a pour but de faciliter la manipulation des
données. Il permet de créer une table, ajouter, supprimer, modifier rapidement
des données, etc.
Dans ce cours, nous allons étudier phpMyAdmin qui est un des outils les plus
connus permettant de manipuler une base de données MySQL. Ce logiciel étant
fourni avec Wamp, nous allons pouvoir l'utiliser sans autre installation.
En local, vous pouvez le démarrer en cliquant sur l'icône de WampServer de la
barre de tâches, puis sur phpMyAdmin. Si tout va bien, il affichera l'écran :
La quasi-totalité des hébergeurs permettent d'utiliser phpMyAdmin, mais il est
possible que vous ayez besoin d'un login et d'un mot de passe pour y accéder.
Renseignez-vous auprès de votre hébergeur pour le savoir.
C. CRÉER UNE BASE DE DONNÉES
L'écran d'accueil de phpMyAdmin est composé de deux parties :
- Celle de gauche reprend la liste des bases de données.
19.04.17
PhpMyAdmin - 1
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Rem.: Certaines bases de données sont déjà présentes. Elles servent au
fonctionnement de MySQL, il est donc vivement conseillé de ne pas y toucher !
- La partie principale de l'écran qui permet notamment de créer une DB.
Pour créer une base de données, il suffit de :
- Cliquer sur le bouton Bases de données (de la barre de boutons/menu)
- Encoder son nom dans la zone Créer une base de données
- Préciser l'interclassement souhaité (ici : utf8_general_ci c'est-à-dire les
caractères internationaux - multilingue - insensible à la casse lors des
tris, etc.)
- Cliquer sur le bouton Créer.
→ Créons la base de données Bibliotheque.
→ Son nom est ajouté dans la partie gauche de l'écran et dans la liste des
bases de données de la partie principale de l'écran.
Notons dès à présent que pour supprimer une base de données, il faut la
sélectionner dans la liste des bases de l'écran Bases de données, puis cliquer
sur la commande Supprimer.
D. DÉFINIR UNE TABLE
Si l'étape précédente, à savoir la création de la base de données, est bien
évidemment une étape importante et indispensable, elle n'est que la toute
première étape d'un processus. En effet, à ce stade, nous n'avons défini que le
fichier qui contiendra les données. Mais nous n'avons pas encore encodé les
données elles-mêmes.
Mais avant de passer à cette étape d'encodage, il faut définir la
structure des tables qui contiendront les données.
Dans le cadre de notre exemple, nous allons créer dans un premier
temps la table des livres. Par la suite, vous créerez les autres
tables.
19.04.17
PhpMyAdmin - 2
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Pour ajouter une première table à une base de données, il faut :
- Cliquer sur son nom dans la partie gauche de l'écran
- Spécifier le Nom de la table, ainsi que son Nombre de colonnes (= champs)
- Cliquer sur le bouton Exécuter
phpMyAdmin affiche à présent l'écran de définition des différents champs de la
table (voir page suivante). Voyons d'un peu plus près les informations les plus
importantes à fournir :
- Colonne contient le nom de chaque champ de la table
- Type indique le type de données que contiendra le champ (numérique, etc.)
- Taille/Valeurs indique la taille à réserver pour ce champ
- Défaut permet d'introduire une valeur par défaut pour ce champ
- Null indique si le champ est mis à NULL lorsqu'il est vide
- Index indique le type d'index à définir, si nécessaire, sur le champ
- A_I (Auto_Increment) indique si le système doit auto-incrémenter ce champ
lors de chaque création d'un enregistrement
Dès que les informations ont été encodées, il suffit de cliquer sur le bouton
Sauvegarder pour créer la table.
Avant de poursuivre, revenons sur les notions : type des données et indexation.
Il existe un grand nombre de types de données dans MySQL. Nous n'en étudierons
toutefois que les plus souvent utilisés. Ces derniers sont regroupés en 3
catégories : les types numériques, de temps et textes.
Dans la catégorie "numérique", on trouve :
- BOOLEAN : Le champ aura deux valeurs (0 = false, une autre valeur = true)
- SMALLINT et l'attribut UNSIGNED : Nombres entiers de 0 à 65535 inclus
- SMALLINT et l'attribut SIGNED : Nombres entiers de -32768 à 32767 inclus
- INT (ou INTEGER) UNSIGNED : Nombres entiers de 0 à 4.294.967.295 inclus
- INT (ou INTEGER) SIGNED : Nombres entiers de -2.147.483.648 à 2.147.483.647
inclus
- DOUBLE (t,d) : Nombres décimaux où t représente le nombre total de chiffres
et d le nombre de décimales
→ Pour cette catégorie, on utilise couramment BOOLEAN, INT et DOUBLE.
19.04.17
PhpMyAdmin - 3
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
19.04.17
PhpMyAdmin - 4
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Dans la catégorie "temps", on trouve :
- DATE : Date sous le format AAAA-MM-JJ
- DATETIME : Date et heure sous le format AAAA-MM-JJ HH:MM:SS
Dans la catégorie "texte", on trouve :
- CHAR(n) : String où n représente le nombre de caractères (entre 1 et 255 ;
Par défaut 1)
- VARCHAR(n) : String où n représente le nombre de caractères (de 0 à 65535)
Remarques :
- La taille réelle de ce champ fluctue en fonction de son contenu.
- MySQL se réserve 1 byte si la longueur est inférieure à 256 ou 2 bytes dans
le cas contraire afin de mémoriser la taille du champ.
- Le nombre maximum de caractères sauvés peut-être nettement inférieur à
celui indiqué. Cela dépend du jeu de caractères associés au champ. Si l'on
prend le "latin1_general_ci", 1 caractère = 1 byte → Pas de problème. Par
contre, si l'on prend l'UTF-8, certains caractères nécessitent jusqu'à 3
bytes pour être sauvés !
- ENUM('val1','val2',...) : Champ dans lequel seules les valeurs énumérées sont
acceptées.
NB: On choisit une valeur dans une liste de maximum 65535 valeurs.
- SET('val1','val2',...) : Champ similaire à ENUM ; mais, dans ce type, on peut
choisir plusieurs valeurs de la liste qui en comprend au maximum 64.
→ Pour cette catégorie, on utilise couramment CHAR et VARCHAR.
Notons que pour ajouter une nouvelle table à une base de données en possédant
déjà, on cliquera d'abord sur le bouton Nouvelle table qui se trouve à la gauche
de l'écran, et ensuite on en spécifiera la structure.
E. LA CLÉ PRIMAIRE ET L'INDEXATION
Dans l'exemple "Livres", le champ "Ref-Livre" sera utilisé comme clé primaire.
Cette référence sera définie comme étant unique et ne sera donc jamais attribuée
à deux livres.
Afin de nous faciliter la vie, nous avons demandé à MySQL, en cochant la case
A_I (auto-increment), de gérer le contenu de ce champ lui-même. Ainsi, MySQL
initialisera cette zone automatiquement à chaque création d'un enregistrement
dans la table.
Pour éviter de grosses pertes de temps lors de recherches fréquentes, on va
demander à MySQL de créer un index sur les champs des tables que l'on utilisera
couramment. Pour ce faire, il suffit de demander à MySQL de définir un index du
type INDEX ou UNIQUE sur les champs souhaités.
Attention, l'index du type INDEX, contrairement à la clé primaire et au type
UNIQUE, autorise les doublons (= valeur identique pour deux enregistrements). On
pourrait donc indexer un champ Ville pour retrouver rapidement tous les clients
de Bruxelles.
La gestion d'index (ajout ou suppression) peut se faire lors de la définition de
la table ou, dans phpMyAdmin, sur l'écran d'édition Structure de la table.
19.04.17
PhpMyAdmin - 5
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Créons à présent les autres tables de notre base de données :
Ref-Lecteur
Nom
Prenom
DateNaiss
Rue
Numero
CodePostal
Localite
Lecteurs
INT - Clé primaire - AI
VARCHAR(50) - Index
VARCHAR(40)
DATE
VARCHAR(50)
CHAR(6)
INT(4)
VARCHAR(50)
LignesEmprunt
Ref-Emprunt
INT - Clé primaire
Ref-Livre
INT - Clé primaire
Ref-Lecteur
INT - Index
DateEmprunt
DATE
DateRetour
DATE
Ref-Auteur
Nom
Prenom
DateNaiss
DateDeces
BestSeller
InfoGen
Auteurs
INT - Clé primaire - AI
VARCHAR(50) - Index
VARCHAR(40)
DATE
DATE
VARCHAR(80) - Index
VARCHAR(150)
Ref-Auteur
Ref-Livre
AuteursLivre
INT - Clé primaire
INT - Clé primaire
F. AJOUTER UN ENREGISTREMENT
Pour ajouter un enregistrement dans la table, il suffit de :
- Activer la table en cliquant sur son nom dans la partie gauche de l'écran
- Cliquer sur la commande Insérer (en haut de l'écran)
- Introduire les informations dans les champs correspondants
- Cliquer sur le bouton Exécuter
19.04.17
PhpMyAdmin - 6
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Remarque :
Le champ Reference sera initialisé automatiquement à moins que l'on introduise
soi-même la valeur que l'on souhaite. Attention, une erreur sera générée si on
introduit une valeur déjà existante.
Introduisons les valeurs suivantes dans la table Lecteurs :
Introduisons les valeurs suivantes dans la table Auteurs :
Introduisons les valeurs suivantes dans la table Livres :
Introduisons les valeurs suivantes dans la table AuteursLivre :
G. CONSULTER LE CONTENU D'UNE TABLE
Pour consulter le contenu d'un table, il suffit de :
- Activer la table en cliquant sur son nom dans la partie gauche de l'écran
19.04.17
PhpMyAdmin - 7
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
- Cliquer sur la commande Afficher liée à la table (en haut de l'écran)
- Préciser les lignes à afficher
H. MODIFIER UN ENREGISTREMENT
Pour modifier un enregistrement, il suffit de :
- Cliquer sur la commande Afficher liée à la table
- Préciser les lignes à afficher
- Cliquer sur le bouton Modifier correspondant à l'enregistrement
- Encoder les modifications souhaitées
- Cliquer sur le bouton Exécuter
I. SUPPRIMER UN ENREGISTREMENT
Pour supprimer un enregistrement, il suffit de :
- Cliquer sur la commande Afficher liée à la table
- Préciser les lignes à afficher
- Cliquer sur le bouton Effacer correspondant à l'enregistrement
- Confirmer la demande en cliquant sur le bouton OK
Pour supprimer plusieurs enregistrements,
- Cliquer sur la commande Afficher liée à
- Préciser les lignes à afficher
- Sélectionner tous les enregistrements à
- Cliquer sur le bouton Pour la sélection
- Confirmer la demande en cliquant sur le
19.04.17
voire tous, il suffit de :
la table
effacer en cochant les cases liées
: Effacer
bouton Oui
PhpMyAdmin - 8
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Pour supprimer tous les enregistrements, il suffit de :
- Sélectionner la table
- Cliquer sur la commande Opérations / Vider la table
- Confirmer la demande en cliquant sur le bouton OK
J. MODIFIER LA STRUCTURE D'UNE TABLE
Il est possible de modifier la structure d'une table à tout moment (même s'il
existe déjà des enregistrements). Cette opération peut s'avérer nécessaire si
l'on souhaite ajouter des champs, créer des index supplémentaires,...
Ces opérations s'effectuent à partir de l'écran suivant de phpMyAdmin accessible
via la commande Structure liée à la table à modifier :
19.04.17
PhpMyAdmin - 9
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Si l'on souhaite modifier un champ, on peut le faire notamment en :
- Cochant la case liée afin de le sélectionner
- Cliquant sur le bouton Pour la sélection : ... correspondant à l'action
souhaitée (Afficher, Modifier, Supprimer, Primaire, Unique, Index)
Si l'on souhaite ajouter un champ, on utilisera les options de la commande
Ajouter ... colonne(s) se trouvant en dessous du tableau.
Si l'on souhaite créer un nouvel index, composé d'un ou de plusieurs champs, on
utilisera la commande Créer un index sur ... colonne(s).
2. AUTRES
FONCTIONS IMPORTANTES DE PHPMYADMIN
A. ECRAN SQL
La commande SQL liée à la table en cours affiche l'onglet correspondant. Ce
dernier nous permet d'encoder et exécuter des requêtes SQL.
SQL (sigle de Structured Query Language) est un langage informatique normalisé
qui sert à effectuer des opérations sur des bases de données. Grâce à ce
langage, nous pouvons créer, modifier,... la structure des tables ; mais nous
pouvons également effectuer les opérations liées à la gestion du contenu des
tables (ajout, modification, consultation, etc.).
En fait, chaque opération effectuée avec phpMyAdmin génère une commande SQL et
c'est celle-ci qui est exécutée. Ces commandes SQL sont d'ailleurs affichées à
l'écran. A vous de vous en inspirer pour améliorer vos connaissances en SQL.
Sur l'écran ci-dessus, la commande SQL SELECT * FROM 'livres' WHERE 1 signifie
qu'il faut afficher tous les enregistrements de la table livres.
Il est également possible d'accéder à une nouvelle fenêtre dédicacée au SQL en
cliquant sur le bouton Fenêtre de requête de la liste des bases de données.
19.04.17
PhpMyAdmin - 10
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Cette fenêtre donne également la possibilité de visualiser l'historique des
commandes SQL ce qui peut s'avérer très utile à certains moments.
B. ECRAN RECHERCHER
La commande Rechercher liée à la table en cours affiche l'écran sur lequel on
peut encoder les critères de recherche des enregistrements souhaités.
19.04.17
PhpMyAdmin - 11
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Attention, phpMyAdmin ne tiendra pas compte des zones Recherche «par valeur» si
quelque chose est encodé au niveau de la zone Ou Critères de recherche (pour
l'énoncé «where»).
C. EXPORTATION
La commande Exporter liée à la table en cours affiche l'onglet correspondant. Ce
dernier nous permet de créer un fichier contenant toutes les commandes SQL
nécessaires à la création de la table (structure et contenu).
Notons qu'une commande similaire existe au niveau de la base de données. Cette
dernière permet de créer un fichier contenant les mêmes commandes mais, dans ce
cas, pour l'ensemble des tables de la DB.
Ce fichier "export" peut servir dans plusieurs situations :
- Transférer la base de données sur internet.
Actuellement, la DB se trouve sur notre disque dur. Mais lorsque notre site
sera hébergé sur internet, il faudra utiliser la base de données en ligne de
l'hébergeur ! Le fichier .sql généré permettra de reconstruire la DB grâce à
l'outil d'importation de phpMyAdmin de l'hébergeur.
- Faire une copie de sauvegarde de la base de données.
On ne sait jamais, en cas d'erreur ou si un pirate parvient à détruire les
données du site (dont la DB), on pourra utiliser ce fichier stocké sur notre
disque pour reconstruire la DB.
Dans le cas présent, il est conseillé de laisser les valeurs par défaut et de
simplement cliquer sur Exécuter. phpMyAdmin vous demandera alors où il doit
sauver le fichier .sql généré.
D. IMPORTATION
La commande Importer liée à la table ou DB en cours a pour effet d'exécuter
toutes les commandes SQL contenues dans le fichier importé et, ainsi, créer
et/ou remplir les tables de la DB.
Pour importer un fichier, il suffit généralement d'indiquer l'Emplacement du
fichier texte à importer et, ensuite, de cliquer sur le bouton Exécuter.
Exercice :
Faire l'exercice DB2.
19.04.17
PhpMyAdmin - 12
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
1. INTRODUCTION
Les commandes SQL étant indispensables pour accéder aux données de la base de
données de votre site WEB, étudions-les.
Mais, avant toute chose, complétons quelque peu notre base de données afin
d'avoir un peu plus d'informations à traiter.
Grâce à phpMyAdmin, ajoutons les 2 colonnes suivantes à la fin de la table
Livres, puis encodons les données correspondantes :
2. LECTURE D'UNE
TABLE
A. LA COMMANDE SQL SELECT DE BASE
Analysons la commande du chapitre "PhpMyAdmin - Consulter le contenu d'une
table" : SELECT * FROM livres
- SELECT : En SQL, le premier mot indique le type d'opération à effectuer.
SELECT demande d'afficher ce que contient une table.
19.04.17
Commandes SQL - 1
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
- * : Après le SELECT, on doit indiquer les champs de la table que l'on désire
récupérer. Les noms des champs doivent être écrits les uns après les autres
et séparés par une virgule (ex.: SELECT titre, collection FROM livres). Si
l'on désire tous les champs de la table, on peut remplacer la liste des noms
par une étoile.
Attention, il faudra entourer les noms de ' ' le cas échéant (ex.: si
contient des -,...)
- FROM : C'est un mot réservé du SQL qui indique que le paramètre suivant sera
le nom de la table à utiliser pour la commande
- livres : Nom de la table à lire
Rem.: Pour tester le résultat de ces queries, il suffit d'exécuter la commande
SQL (onglet dans PhpMyAdmin).
On peut modifier un query que l'on vient d'exécuter grâce à l'hyperlien
En ligne.
Si la commande SELECT, telle qu'encodée ci-dessus, permet d'afficher rapidement
les données de la table, elle ne peut toutefois pas toujours être utilisée telle
qu'elle pour afficher des données aux utilisateurs finaux. En effet, il arrive
fréquemment que le nom des champs soient codés et n'ont donc pas un nom très
compréhensible... Les concepteurs du SQL ont donc ajouté la clause AS à la
commande. Celle-ci doit être encodée après chaque champ dont on souhaite changer
le libellé à l'écran.
Exemple :
De plus, il est possible d'afficher des champs calculés grâce aux opérateurs +,
-, * et /.
Exemple :
B. LA CLAUSE DISTINCT
Le bibliothécaire souhaite envoyer des toutes-boîtes dans les communes dans
lesquelles au moins un de ses lecteurs habite. Pour cela, il exécute la commande
SQL SELECT codepostal, localite FROM lecteurs. Celle-ci lui affiche bien toutes
les communes à l'écran, malheureusement elle affiche plusieurs fois les mêmes...
N'y a-t-il pas moyen d'éviter l'affichage des doublons ?
Les concepteurs du SQL ont solutionné ce problème en créant la clause DISTINCT
que l'on peut ajouter juste après le mot SELECT. Cette clause indique qu'il ne
faut pas afficher les enregistrements dont l'ensemble des champs à afficher est
identique à un autre enregistrement déjà affiché.
Exemple :
C. LA CLAUSE WHERE
Cette clause, ajoutée à la suite de la commande SELECT, permet de filtrer les
enregistrements en limitant la sélection en fonction de certaines conditions.
Pour ce faire, on utilisera les opérateurs de comparaisons <, >, =, <=, >= et <>
19.04.17
Commandes SQL - 2
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
(ou !=).
Supposons que je ne souhaite avoir que les livres "policier" :
Notons que les chaînes de caractères doivent être placées entre apostrophes ou
guillemets pour les délimiter (ex.: 'Policier') ; ce qui n'est pas nécessaire
pour les nombres.
Il est également possible de combiner plusieurs conditions grâce aux mots AND et
OR, voire des parenthèses, ajoutés entre les différentes conditions :
Le langage SQL étant assez développé, il permet de faire des sélections plus
complexes grâce notamment aux mots réservés NOT et LIKE.
NOT, comme nous l'avons déjà vu dans d'autres langages, permet d'inverser le
résultat d'une condition. Si le résultat était True, il vaudra False et
inversement.
LIKE permet d'utiliser des wildcards dans la zone de recherche : le caractère _
sera remplacé par 1 et 1 seul caractère et le caractère % sera remplacé par 0, 1
ou plusieurs caractères.
D. LA CLAUSE ORDER BY
Cette clause, ajoutée à la suite de la commande SELECT, permet de trier les
résultats sur le contenu de certains champs de manière croissante (ASC) ou
décroissante (DESC).
Supposons que je souhaite avoir tous les livres écrits après 1965, triés par
type (croissant) et nombre d'impressions (décroissant) :
Notons que dans ce cas-ci le résultat pourrait être surprenant. En effet, le
champ Type étant du type ENUM, MySQL triera de manière croissante les
enregistrements en tenant compte de l'ordre des valeurs dans la définition de
l'ENUM de la table qui ne correspond pas nécessairement à l'ordre alphabétique !
19.04.17
Commandes SQL - 3
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
E. LA CLAUSE LIMIT
Cette clause, ajoutée à la fin de la commande SELECT, permet de ne sélectionner
qu'une partie des résultats (par exemple les 30 premiers). C'est très utile
lorsqu'il y a beaucoup de résultats et que l'on souhaite les paginer.
Cette clause a deux paramètres numériques séparés par une virgule :
- Le premier paramètre indique à partir de quel enregistrement "résultat"
(≠ champ Référence) on commence à afficher les données. Le premier
enregistrement sélectionné ayant l'indice 0 dans le tableau des résultats.
- Le deuxième nombre indique le nombre d'enregistrements à sélectionner.
Supposons que je souhaite avoir les 3 premiers livres dont le tirage est
supérieur à 200000 exemplaires :
Attention, si l'on peut utiliser les 3 clauses indépendamment les unes des
autres, elles doivent (lorsqu'elles sont présentes) respecter l'ordre suivant :
WHERE, ORDER puis LIMIT.
3. AJOUT D'UN
NOUVEL ENREGISTREMENT DANS UNE TABLE DE LA
DB
L'ajout d'un nouvel enregistrement dans une table s'effectue au moyen de la
commande INSERT INTO table(champ 1, ...) VALUES (valeur1, ...)
4. SUPPRESSION D'ENREGISTREMENTS D'UNE
TABLE DE LA
DB
La suppression d'enregistrements se fait très, voire trop, facilement. En effet,
il suffit de taper la commande DELETE FROM table WHERE condition. Il faudra donc
veiller tout particulièrement à la condition. En cas d'erreur, on risque
d'effacer à tout jamais des enregistrements que l'on ne voulait pas effacer.
Si cette commande fonctionne très bien lorsqu'on l'encode dans la section SQL
en utilisant les noms des colonnes mis à disposition dans la boîte de dialogue,
19.04.17
Commandes SQL - 4
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
elle ne fonctionne pas correctement quand on l'encode directement en ligne.
Pourquoi ? A cause du nom du champ ! En effet, MySQL interprète mal le nom des
champs contenant un trait d'union. C'est pourquoi, je conseille de changer tous
les noms des champs contenant un trait d'union et de le retirer.
5. MODIFICATION D'ENREGISTREMENTS D'UNE
TABLE DE LA
DB
La modification des enregistrements d'une table se fait au moyen de la commande
UPDATE dont la syntaxe est la suivante :
UPDATE table
SET champ1 = valeur1, champ2 = valeur2, ...
WHERE condition
6. LIEN
ENTRE PLUSIEURS TABLES
Tout cela, c'est très bien... Mais, nous, nous avons besoin de données qui se
trouvent dans plusieurs tables. Comment pouvons-nous regrouper les tables lors
d'un SELECT ? Et bien, c'est relativement simple. Nous allons utiliser les
clauses JOIN !
Mais, avant toute chose, ajoutons ces données dans la table LignesEmprunt :
Il existe plusieurs types de jointures, nous allons en étudier 3 dans ce cours.
A. L'ÉQUI-JOINTURE
La première, l'équi-jointure, consiste à opérer une jointure entre les tables
avec une condition d'égalité.
Cette jointure se faisait auparavant au moyen de la commande SELECT telle qu'on
l'a déjà étudiée. Il suffisait de préciser les différentes tables souhaitées au
niveau du FROM et les critères de jointure au niveau du WHERE.
Attention, le nom des champs commun à plusieurs tables doivent être préfixés du
nom de la table liée (ex.: Livres.RefLivre).
Exemple 1 :
19.04.17
Commandes SQL - 5
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Le résultat affiche 7 lignes de données. En effet, cette jointure recherche pour
chaque enregistrement de la table Livres, les données correspondantes dans les
autres tables. S'il ne trouve pas de données, cet enregistrement ne sera pas
affiché. Par contre, dans le cas où il en trouve plusieurs, l'enregistrement
sera affiché en plusieurs exemplaires (ex.: Le livre "Ce soir, je veillerai sur
toi").
Exemple 2 :
Dans ce cas-ci, seule 2 lignes sont affichées car les autres livres n'ont jamais
été empruntés. Aucun enregistrement lié n'est donc présent dans la table des
emprunts.
Précisons encore qu'il est possible d'ajouter des critères de sélection
supplémentaires afin de limiter le nombre d'enregistrements affichés.
Exemple 3 :
Dans ce cas, seuls les livres empruntés non rendus seront affichés.
Les jointures réalisées avec la clause WHERE étant quelque peu limitées, une
nouvelle clause est apparue : la clause JOIN. Etudions-la.
Exemple 4 :
Comme pour l'exemple 3, seuls les livres empruntés non rendus seront affichés.
Les jointures se font au moyen des clauses JOIN table ON critère où table
indique la table liée et critère indique les critères de jointure entre deux
tables.
Notons qu'afin de faciliter la lecture des commandes, on peut utiliser le
surnommage qui consiste à remplacer dans la commande le nom d'une table par un
alias (ex.: LignesEmprunt est remplacé par E, Livres par LI, Lecteurs par LE).
19.04.17
Commandes SQL - 6
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
B. LES JOINTURES EXTERNES LEFT
Si l'équi-jointure est la plus utilisée, il se peut qu'elle ne réponde pas aux
besoins dans certaines situations. En effet, elle ne sélectionne que les
enregistrements dont des données sont présentes dans chaque table de la
jointure. Or, il se pourrait que l'on ait besoin d'afficher les enregistrements
dès que des données sont présentes dans au moins une des tables. Prenons
l'exemple de deux tables : la première contient les données signalétiques des
clients et la deuxième les numéros des téléphones. Si un client ne possède pas
de téléphone, une équi-jointure entre ces deux tables n'affichera pas le client
en question. C'est pourquoi, on a mis au point les jointures externes.
Etudions la première : la jointure externe LEFT.
Lorsque l'on ajoute le mot LEFT à la gauche de JOIN, cela signifie que la table
principale est la première table citée et que tous les enregistrements qui y
sont présents et qui répondent à la condition WHERE seront affichés et ce même
s'ils n'ont pas de correspondance dans la seconde table. Ainsi, on pourrait
afficher tous les clients qu'ils aient ou non un numéro de téléphone.
Exemple :
Dans cet exemple, on affiche tous les emprunts (en cours ou non).
C. LES JOINTURES EXTERNES RIGHT
La seconde jointure externe étudiée est la jointure externe RIGHT.
Dans ce cas-ci, on ajoute le mot RIGHT à la gauche de JOIN, cela signifie que la
table principale est la seconde table citée et que tous les enregistrements qui
y sont présents et qui répondent à la condition WHERE seront affichés et ce même
s'ils n'ont pas de correspondance dans la première table.
Exemple :
Dans cet exemple, on affiche tous les emprunts (en cours ou non), ainsi que tous
les livres jamais empruntés.
19.04.17
Commandes SQL - 7
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
7. DIVERSES
FONCTIONS
A. L'OPÉRATEUR IN
L'opérateur IN peut être placé dans une clause WHERE afin de préciser une liste
de valeurs acceptées.
Exemple :
Dans cet exemple, on sélectionne les auteurs Lenteric et Christie.
B. LA FONCTION COUNT()
La fonction COUNT() permet de savoir le nombre de lignes sélectionnées par la
requête.
La commande SELECT COUNT(*) FROM Livres permet d'afficher le nombre de livres de
la table.
En fait, il existe deux façons d'utiliser cette fonction :
 COUNT(*) renvoie le nombre de lignes sélectionnées par la requête
 COUNT(champ) renvoie le nombre de lignes sélectionnées par la requête dont
la valeur du champ spécifié n'est pas égal à NULL
Exemple :
C. LA FONCTION SUM()
La fonction SUM() permet de faire la somme d'un champ pour les lignes
sélectionnées par la requête.
Exemple :
Cette commande affiche le nombre total d'impressions pour l'ensemble des livres
de la table.
19.04.17
Commandes SQL - 8
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
D. LA CLAUSE GROUP BY
Comme vous avez pu le constater, les fonctions COUNT() et SUM() groupent toutes
les lignes sélectionnées et affichent un seul résultat. Néanmoins, il est
souvent intéressant d'avoir des résultats pour certains groupes. Par exemple,
serait-il possible d'avoir le nombre d'impressions par type de livre ? Le patron
de la maison d'édition aimerait savoir le type de livre qui se vend le plus. On
pourrait bien évidemment exécuter une commande SELECT COUNT(*) avec une clause
WHERE qui sélectionnerait qu'un seul type à la fois. En espérant qu'il n'y ait
pas trop de types différents sans quoi on devra exécuter un grand nombre de
commandes...
C'est notamment pour ce genre de demandes que la clause GROUP BY a été créée.
Celle-ci permet de regrouper les données en fonction des valeurs d'un champ.
Dans l'exemple suivant, on demande au système d'afficher le nombre d'impressions
par type en ajoutant la clause GROUP BY champ après l'éventuelle clause WHERE
(ou après FROM).
E. LA FONCTION AVG()
La fonction AVG() permet de faire la moyenne d'un champ pour les lignes
sélectionnées par la requête.
F. LES FONCTIONS MIN() ET MAX()
Ces fonctions permettent d'afficher respectivement le minimum et le maximum des
lignes sélectionnées par la requête.
Exercice :
Faire l'exercice DB3.
19.04.17
Commandes SQL - 9
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
1. ACCÉDER
À LA BASE DE DONNÉES À PARTIR DE
PHP
A. INTRODUCTION
Pourquoi ce chapitre ? Tout simplement car si les sites dynamiques utilisent
souvent une base de données contenant les informations à afficher sur les pages
web en fonction des souhaits des internautes, il y a un problème important lié
au langage SQL : il ne permet pas de générer de l'HTML. Et comme l'HTML est
incapable de lancer une commande SQL ou de traiter les résultats d'une telle
commande, il a fallu utiliser un langage intermédiaire : le PHP.
B. CONNEXION À LA BASE DE DONNÉES
La première étape pour pouvoir travailler avec une DB est l'établissement de la
connexion avec MySQL. Le but de cette connexion est de remplir l'obligation qu'a
PHP de s'identifier auprès de MySQL. En effet, MySQL demande un nom
d'utilisateur et un mot de passe afin d'empêcher l'accès à la DB aux personnes
non autorisées.
PHP propose plusieurs moyens de se connecter à une base de données MySQL. Nous
étudierons les commandes faisant partie de l'extension mysqli_. Ce sont des
fonctions améliorées d'accès à MySQL. Elles proposent plus de fonctionnalités et
sont plus à jour que celles de l'extension mysql_.
Pour établir la connexion, nous utiliserons la fonction mysqli_connect(hôte,
utilisateur, motDePasse) où :
- hôte est l'adresse de l'ordinateur où MySQL est installé. Si MySQL est
installé sur le même ordinateur que PHP, comme c'est souvent le cas, on
mettra la valeur localhost (= "sur le même ordinateur"). Il est néanmoins
possible que l'hébergeur du site indique une autre valeur à renseigner. Il
faudra alors modifier cette valeur lorsque le site sera placé sur le web.
- utilisateur permet de vous identifier. Renseignez-vous auprès de votre
hébergeur pour le connaître. Le plus souvent, il correspond au nom utilisé
pour le FTP.
- motDePasse. Celui-ci correspond souvent à celui utilisé pour accéder au FTP.
Renseignez-vous auprès de votre hébergeur.
Dans notre exemple, nous faisons des tests sur un poste local ; par conséquent,
le nom de l'hôte sera localhost. Quant au login et au mot de passe, par défaut
le login est root et il n'y a pas de mot de passe.
Cette fonction renvoie un identifiant de lien si la connexion a pu s'établir ou
false dans le cas contraire. Attention, il faudra bien évidemment retenir cet
identifiant pour les opérations suivantes liées à la DB. De même, il faudra
toujours vérifier si l'opération s'est déroulée sans erreur avant de continuer.
En cas d'erreur, on traitera celle-ci soit en associant directement la commande
die() à la commande mysqli_, soit en vérifiant la valeur renvoyée et en
19.04.17
DB d'un site Internet - 1
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
effectuant un traitement spécifique en cas d'erreur (afficher un message, etc.).
La seconde étape consiste à indiquer à MySQL le mode d'encodage des caractères
choisi. Par défaut, MySQL utilise l'encodage ISO-8859-1. Si un autre mode a été
activé, il faudra l'indiquer via la commande mysqli_set_charset($ptr, charset).
Et, enfin, la troisième étape consiste à sélectionner la base de données
désirée. Pour ce faire, on utilisera la fonction mysqli_select_db($ptr, nomDB).
C. LECTURE ET TRAITEMENT DE DONNÉES D'UNE TABLE DE LA DB
Ce chapitre a pour but de :
- Vous apprendre à lancer une requête (= query) sur une table
- A traiter les résultats d’une requête
Etapes "Exécution d’une requête" :
Cet exemple vous permettra d’apprendre à exécuter un query et à en traiter les
résultats.
19.04.17
DB d'un site Internet - 2
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Détaillons un peu cet exemple :
- La première partie du code consiste à se connecter à la DB et à sélectionner
la table
- Ensuite, nous créons notre query. La requête utilisée dans cet exemple
(SELECT * FROM livres) demande à MySQL de rechercher tous les champs de tous
les enregistrements de la table livres.
- Puis, nous l’exécutons en utilisant la commande mysqli_query($lien,$requete).
Cette commande renvoie soit false en cas d’erreur, soit un objet avec les
résultats.
- Nous traitons l’éventuelle erreur en testant si le résultat vaut false. Si
c’est le cas, nous affichons l’erreur via la fonction mysqli_error($lien) et
nous arrêtons le traitement.
- Enfin, nous affichons les enregistrements renvoyés par MySQL. La commande
mysqli_fetch_array($resultat) parcourt la totalité de l'objet $resultat et
renvoie soit false à la fin du tableau, soit un enregistrement sous la forme
d’un tableau associatif.
D. UTILISATION DE VARIABLES DANS LES REQUÊTES
Utilisation simple de variables dans les requêtes :
Les requêtes données en exemple jusqu'à présent étaient relativement figées :
les paramètres étaient définis dès l'encodage de la commande. Vous verrez plus
tard, lorsque vous créerez un site lié à une DB, qu'il est souvent nécessaire
d'utiliser des variables dans les requêtes si l'on veut tenir compte des choix
de l'internaute.
La première manière de procéder consiste à construire sa requête dans une
variable avant de l'exécuter en n'oubliant pas de mettre des apostrophes ou
guillemets autour des chaînes de caractères utilisées dans la clause WHERE :
$requete = "SELECT * FROM livres WHERE auteurs='" . $_GET['auteurs'] . "'";
Bien que cette commande fonctionne très bien, elle peut s'avérer dangereuse. En
effet, si la variable $_GET['auteurs'] a été modifiée par l'internaute, il y a
un gros risque de faille de sécurité qu'on appelle Injection SQL. Un internaute
pourrait insérer une requête SQL au milieu de la vôtre et pourrait donc accéder
au contenu complet de la base de données comme, par exemple, une liste des mots
de passe des visiteurs du site !
19.04.17
DB d'un site Internet - 3
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Exercice :
Faire l'exercice DB4.
Utilisation sécurisée de variables dans les requêtes :
Nous allons utiliser un autre moyen plus sûr d'adapter nos requêtes en fonction
de variables : les requêtes préparées. On va dans un premier temps "préparer" la
requête sans sa partie variable, que l'on représentera avec un marqueur sous
forme de point d'interrogation :
Au lieu d'exécuter la requête avec mysqli_query() comme précédemment, on appelle
ici mysqli_prepare() :
La requête est alors prête, sans sa partie variable. Maintenant, nous allons
exécuter la requête en appelant mysqli_stmt_execute() et en lui transmettant la
liste des paramètres via mysqli_stmt_bind_param() (S'il y a plusieurs marqueurs,
il faut indiquer les paramètres dans le bon ordre) :
Le traitement des données reçues peut alors se faire en liant les variables avec
mysqli_stmt_bind_result() et en traitant chaque enregistrement via
mysqli_stmt_fetch() :
Lorsque l'on n'aura plus besoin d'exécuter cette requête (y compris avec
d'autres valeurs pour les paramètres), on veillera à la clôturer via
mysqli_stmt_close() afin de regagner de la place mémoire :
19.04.17
DB d'un site Internet - 4
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Dans cet exemple complet, on voit qu'il est possible d'exécuter la même requête
avec des valeurs différentes pour les mêmes paramètres sans tout redéfinir.
19.04.17
DB d'un site Internet - 5
GESTION D'UNE BASE DE DONNEES
© Pierre Hofmans
Cet exemple affichera le résultat suivant :
Exercice :
Faire l'exercice DB5.
19.04.17
DB d'un site Internet - 6