COURS DE · Web viewDe 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é

COURS DE

© HOFMANS PierreNovembre 2016

GESTION D'UNE BASE DE DONNEES© Pierre Hofmans

PARTIE 1 : INTRODUCTION GENERALE........................11. Introduction..............................................................12. Importance de la base de données..........................................1

PARTIE 2 : CONCEPTION D'UNE BASE DE DONNEES.............11. Qu'est ce qu'une base de données ?........................................12. Structure d'une base de données...........................................1

A. Notion de table.......................................................1B. Notion d'enregistrement et de champ...................................2C. Notion de liens entre les tables......................................2D. Notion de clé primaire................................................3E. Notion d'indexation des données.......................................3F. Les opérations de base................................................4

3. Méthodologie à suivre pour la conception d'une base de données............5A. Analyse des souhaits, besoins et situation actuelle du client.........5B. Identifier les entités................................................5C. Les attributs des entités.............................................6D. Les identifiants des entités..........................................7E. Les liens entre les entités, le schéma relationnel....................7F. Les optimisations possibles...........................................8

PARTIE 3 : PhpMyAdmin...................................11. PhpMyAdmin................................................................1

A. Installation / Préparation du PC......................................1B. Introduction..........................................................1C. Créer une base de données.............................................1D. Définir une table.....................................................2E. La clé primaire et l'indexation.......................................5F. Ajouter un enregistrement.............................................6G. Consulter le contenu d'une table......................................7H. Modifier un enregistrement............................................8I. Supprimer un enregistrement...........................................8J. Modifier la structure d'une table.....................................9

2. Autres fonctions importantes de phpMyAdmin...............................10A. Ecran SQL............................................................10B. Ecran Rechercher.....................................................11C. Exportation..........................................................12D. Importation..........................................................12

PARTIE 4 : LES COMMANDES SQL............................11. Introduction..............................................................12. Lecture d'une table.......................................................1

A. La commande SQL SELECT de base........................................1

11.05.23


B. La clause DISTINCT....................................................2C. La clause WHERE.......................................................2D. La clause ORDER BY....................................................3E. La clause LIMIT.......................................................4

3. Ajout d'un nouvel enregistrement dans une table de la DB..................44. Suppression d'enregistrements d'une table de la DB........................45. Modification d'enregistrements d'une table de la DB.......................56. Lien entre plusieurs tables...............................................5

A. L'équi-jointure.......................................................5B. Les jointures externes LEFT...........................................7C. Les jointures externes RIGHT..........................................7

7. Diverses fonctions........................................................8A. L'opérateur IN........................................................8B. La fonction COUNT()...................................................8C. La fonction SUM().....................................................8D. La clause GROUP BY....................................................9E. La fonction AVG().....................................................9F. Les fonctions MIN() et MAX()..........................................9

PARTIE 5 : GESTION D'UNE DB D'UN SITE INTERNET..........11. Accéder à la base de données à partir de PHP..............................1

A. Introduction..........................................................1B. Connexion à la base de données........................................1C. Lecture et traitement de données d'une table de la DB.................2D. Utilisation de variables dans les requêtes............................3

11.05.23


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 de l'informatisation d'une entreprise,... Si vos données sont mal rangées, mal structurées, vous aurez beaucoup de difficultés à les manipuler. Et dans le pire des cas, vous ne saurez pas les traiter du tout. Il est donc crucial d'analyser les besoins des applications actuelles et futures, d'analyser les informations dont on dispose et celles dont on a besoin, de structurer les liens entre celles-ci avant de se lancer dans la rédaction des applications.

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.

11.05.23 Introduction - 1


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

ReferenceTitreAuteursTypeLivreAnneeEditionNbrExemplaires

11.05.23 Conception d'une DB - 1


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 :

Signalétique lecteurRéférence... : ____Nom......... : _________________________Prénom...... : _________________________Naissance... : __/__/____Adresse..... : _________________________Ville....... : _________________________ Code postal : ____Tél......... : ____________Commentaires : ________________________________________ ________________________________________

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 Prénom Naissance Adresse Ville Code postal Tél. Commentaires

___ ..... ..... __/__/____ ..... ..... ____ ..... .....

___ ..... ..... __/__/____ ..... ..... ____ ..... .....

___ ..... ..... __/__/____ ..... ..... ____ ..... .....

___ ..... ..... __/__/____ ..... ..... ____ ..... .....

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-



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 informa-tions 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



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.



- 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 client2. Identifier les entités en présence3. Lister leurs attributs4. Définir leurs identifiants5. Définir les liens entre les entités et ainsi établir le schéma relationnel6. 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



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



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 :



- 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.



Exercice :

Faire l'exercice DB1.



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.

11.05.23 PhpMyAdmin - 1


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.



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.





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.



Créons à présent les autres tables de notre base de données :

Lecteurs AuteursRef-Lecteur INT - Clé primaire - AI Ref-Auteur INT - Clé primaire - AINom VARCHAR(50) - Index Nom VARCHAR(50) - IndexPrenom VARCHAR(40) Prenom VARCHAR(40)DateNaiss DATE DateNaiss DATERue VARCHAR(50) DateDeces DATENumero CHAR(6) BestSeller VARCHAR(80) - IndexCodePostal INT(4) InfoGen VARCHAR(150)Localite VARCHAR(50)

LignesEmprunt AuteursLivreRef-Emprunt INT - Clé primaire Ref-Auteur INT - Clé primaireRef-Livre INT - Clé primaire Ref-Livre INT - Clé primaireRef-Lecteur INT - IndexDateEmprunt DATEDateRetour DATE

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



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



- 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, voire tous, il suffit de :- Cliquer sur la commande Afficher liée à la table- Préciser les lignes à afficher- Sélectionner tous les enregistrements à effacer en cochant les cases liées- Cliquer sur le bouton Pour la sélection : Effacer- Confirmer la demande en cliquant sur le bouton Oui



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 :



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.



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.



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 :




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.

11.05.23 Commandes SQL - 1


- * : 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 <>



(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 !



E. L A 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,



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 tableSET 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 :



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).



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.



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.



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 :




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

11.05.23 DB d'un site Internet - 1


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.



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 !



Exercice :


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 :



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.



Cet exemple affichera le résultat suivant :

Exercice :



Documents

COURS DE · Web viewDe 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é