Bases de données en Access
Comment débuter
1ère année informatique
Najib Tounsi
(Lien permanent: http://www.mescours.ma/DB/Polys/Access/bdAccess.html
Sommaire
Une base de données (BD) est un ensemble de fichiers contenant les données nécessaires aux opérations et à la gestion d'une organisation telle qu'un entreprise, une administration etc. Par exemple les données sur les clients d'une société, les ventes et les fournisseurs. Les données sur les étudiants, les cours et les professeurs d'une université, etc.
Les actions (*) qu'on peut effectuer sur une base de données sont principalement :
Pour cela, une base de données doit être gérée par un logiciel adaptée appelé Système de Gestion de Bases de Données ou SGBD. Pour une bonne expérience utilisateur, un SGBD présente les données sous forme de tables. Access est un tel logiciel.
(*) Ces actions peuvent être faites à travers
un écran interactif adapté ou par des programmes d'applications prévus à
cet effet. Nous négligerons ce dernier aspect ici.
Une base de données est donc constituée d'un ensemble de tables, qu'on peut exploiter facilement.
Exemple de table : données pour les livres d'une bibliothèque
| numinv | titre | auteur | qte |
|---|---|---|---|
| 124 | Algorithmes | D. Knuth | 2 |
| 216 | Systèmes Opératoires | Crocus | 1 |
| 241 | Langage C | B. Kernighan | 5 |
| 312 | Langage Java | J. Gosling | 8 |
| 321 | Unix | K. Thompson | 2 |
| 323 | Bases de données | C. J. Date | 3 |
| 1110 | Programmation | B. Meyer | 6 |
Le but ici est de :
Créer une table c'est lui donner un nom de table (Livres)
et décrire sa structure, c'est-à-dire le liste des noms de champs
et leur type, numérique, texte, date etc.
(on dit aussi format). Ici, on a le champ numinv de type
numérique, les champs titre et auteur de
type texte, et le champ quantité (nombre d'exemplaires) qte de
type numérique aussi.
Remplir une table saisir les valeurs associées aux différentes lignes. Cette opération peut se faire par saisie directe au clavier ou par importation à partir d'une source déjà existante (autre fichier par exemple).
Interroger une table c'est formuler des questions pour savoir une information. Par exemple « qui est l'auteur du livre 'Langage C' » ?
En langage d'interrogation SQL, cette requête s'écrit :
SELECT auteur FROM Livres WHERE titre = 'langage C'
et qui signifie (en paraphrasant) :
Chercher le champs auteur
dans la table Livres
sachant que le champ titre vaut 'langage
C'
SQL (Structured Query Language) est le langage d'interrogation formel de notre type de bases de données.
On peut interroger la base de données en soumettant directement la
requête sous forme SQL. On dit mode SQL. Cela suppose une bonne
connaissance du langage. Il y a d'autres moyens d'interroger la base de
données et de soumettre la requête. Par exemple, le mode création
de Access. On utilise une grille préparée déjà qui permet de
sélectionner les champs à interroger (auteur) et de rentrer
les critères de recherche (titre = 'langage C').
Dans la première ligne on indique les champs concernés (auteur et
titre) par la requête et dans la deuxième ligne la
table. Ensuite, ligne "Afficher" on coche ou non si le champ
est a visualiser (ici on veut auteur), et enfin à la ligne "critère"
on met la valeur du critère (="Langage C") dans la
colonne correspondante (titre).
Lancement Access et choix de menu création table. Sélectionner le menu Créer et ensuite Création de table
Il s'agit ensuite de rentrer le nom des champs et leur type. Une ligne par champ. (NB. la table a un nom par défaut tablei qu'on changera plus tard.
On remplit les cases Nom de champ ensuite Type de données. Sélectionner le type de données en déroulant le menu.
On peut constater en bas une liste d'options de format possibles, pour des raffinements de format ou de propriété de donnée. On ne s'en préoccupera pas ici.
N.B. On pourra spécifier que la valeur de
champ numinv est unique par livre et en faire alors
une clé. On peut le faire en cliquant sur le bouton droit (ou Ctrl-Clique) au
niveau du champ pour faire apparaître un menu déroulant. Sélectionner
ensuite clé primaire. Une icône
clé apparaîtra à gauche du nom de champ.
On rentre ensuite les autres champs. Par exemple titre qui
est un champ de type texte. Noter qu'on peut choisit la
taille du texte, 20 caractères ici, dans la liste des options justement.
Après avoir défini tous les champs et leur type (qu'on appelle aussi le schéma
de la table), il faut sauvegarder le schéma de la table. Faire ^S
par exemple. C'est alors que Access vous demande de changer le
nom de la table (ne pas garder table1, ou table2
etc. Choisir un nom significatif).
Ici, on va rentrer Livres comme nom de table. On voit ce
nom de table apparaître dans l'onglet Tables
à gauche.
Remarque: Il se peut que Access vous suggère de définir une clé
primaire. On le fait on cliquant sur le bouton droit dans la case grise à
gauche du champ numinv qu'on a choisi comme valeur clé
unique.
En cliquant sur le nom de la table Livres dans l'onglet
des Objets Access (onglet de gauche), on accède à une grille qui
permet rentrer les données de la table. Les données à rentrer doivent
correspondre au type de schéma donné en entête de la grille.
On tape ses données champ par champ comme dans la figure en dessus, et on sauvegarde. Faire attention à ne pas rentrer des valeurs doubles pour la clé primaire ou des données non conforme au type spécifié à la création d'un champs.
Pour interroger la table, on a la notion de requête. Il faut créer une requête Access et la sauvegarder.
Aller vers le menu Créer et sélectionner Création de requête.
On a alors un nouvel onglet requête. Cette fonctionnalité demande de sélectionner une table qu'on désire interroger.
Le schéma de la table s'affiche dans une zone en haut de l'onglet et, en bas, il y a une grille pour exprimer sa requête.
On peut procéder comme ci-dessus et exprimer sa requête. (Voir plus haut, §Interroger une table)
Une fois exprimée la requête, on peut l'exécuter avec le menu "!",
(ou bien en sauvegardant la requête et en la sélectionnant ensuite dans l'onglet des objets Access actifs.)
où on voit le résultat de l'exécution de la requête.
Nous avons déjà dit qu'on peut interroger une base de donnée avec SQL On peut travailler directement en langage SQL et taper sa requête.
Après avoir choisi de créer une nouvelle requête, pointer sur l'onglet de la requête, cliquer bouton droit et choisir Mode SQL.
On a alors la main pour taper et exécuter des requêtes SQL.
où on voit le code SQL correspondant à notre requête initiale.
N.B. On peut d'ailleurs passer du mode SQL au mode Création (la grille précédente) en cliquant sur le bouton droit et voir comment une requête exprimée par le mode assisté est traduite en SQL par Access.
1) « Quels sont les titres des livres écrits par "D. Knuth" ?»
| Requête SQL | Résultat | En Mode Création |
|---|---|---|
 |
 |
 |
N.B. On a illustré côte à côte la requête en mode SQL, son résultat et le mode création correspondant.
2) « Quel est le numéro inventaire et le titre de tous les livres ?»
| Requête SQL |
Résultat |
En Mode Création |
|---|---|---|
 |
 |
 |
On voit donc que le résultat d'une requête SQL est aussi une table (en milieu de tableau ci-dessus) .
En fait, une règle de base en SQL c'est : une requête SELECT
prend une table de base en entrée (Livres ici) et produit
comme résultat une table à afficher. (SELECT peut aussi
interroger deux ou plusieurs tables, voir plus
bas §Interrogation de plusieurs tables.)
3) « Toutes les informations sur les livres disponibles en quantité égale à 2.»
| Requête SQL |
Résultat |
En Mode Création |
|---|---|---|
 |
 |
 Seul le critère "2" est spécifié. Aucun champ à afficher n'est précise, donc tous. |
4) « Le titre et l'auteur des livres en nombre d'exemplaires supérieur à 2, affichés par ordre croissant de titre.»
| Requête SQL |
Résultat |
En Mode Création |
|---|---|---|
 |
 |
 |
| numAb | Nom | prenom |
|---|---|---|
| 10 | BenSaid | Said |
| 12 | Benali | Ali |
| 14 | Berrada | Rajaa |
| 32 | Karim | Karima |
| 15 | BelKacem | Kacem |
| numinv | numab | datepret |
|---|---|---|
| 124 | 12 | 10/4/2017 |
| 241 | 32 | 10/5/2017 |
| 312 | 10 | 10/10/2017 |
| 321 | 14 | 8/31/2017 |
| 323 | 10 | 10/1/2017 |
| 323 | 12 | 10/12/2017 |
| 323 | 14 | 10/9/2017 |
| 323 | 32 | 9/27/2017 |
N.B. D'autres types de requêtes et l'interrogation de deux ou plusieurs tables en même temps seront présentés au § suivant.
Dans sa forme simple, une requête SELECT s'écrit :
SELECT champs à afficher
FROM tables
WHERE condition de recherche
comme dans le requête 4 ci-dessus (sans tri).
SELECT titre, auteur FROM Livres WHERE qte>2;
qu'il est parfois utile d'écrire
SELECT Livres.titre, Livres.auteur FROM Livres WHERE Livres.qte>2;
en préfixant les champs avec le noms des tables. En effet, il y a parfois des champs qui ont un même nom mais dans deux tables différentes.
Voici d'autres exemples de requêtes SQL :
1) « Tous les livres disponibles en quantité comprise entre 6 et 2 »
SELECT * FROM Livres WHERE qte < 6 AND qte > 2
2 ) « Tous les livres de titre "Unix" ou disponibles en quantité comprise entre 6 et 2 »
SELECT *
FROM Livres
WHERE (qte < 6 AND qte > 2)
OR titre = "Unix"
3) Usage d'une expression dans SELECT « Les titres
des livres et la nouvelle quantité après augmentation de 5 »
SELECT titre, qte+5 AS newQte FROM Livres
4) « Quels sont les abonnés ayant emprunté au moins un livre ? »
SELECT numab FROM prets;
Comparer avec
SELECT DISTINCT numab FROM prets;
En effet, il n'y a pas besoin d'afficher une même réponse plusieurs fois.
5) « Quelle est jusqu'à aujourd'hui la durée en jours des prêts
accordés ? »
Usage de la fonction date() qui donne la date du jours.
SELECT *, (date() - datepret) AS durée FROM Prets;
où on voit (en 1ère colonne) le résultat du calcule de la durée entre la date du jour et la date de prêt.
6) « Quels sont les prêts ayant dépassé la durée du prêt qui est de 30 jours ?»
SELECT * FROM Prets WHERE ( date() - datepret) > 30;
7) « Quels sont les titres et auteurs des livres dont le titre
commence par 'L' ? »
Usage des motifs. '*' pour toute chaîne de caractères et '?'
pour un caractère quelconque.
SELECT titre, auteur FROM livres WHERE titre like "L*";
« Idem pour les livres dont la 2èm lettre du titre est un ‘a’ »
SELECT titre, auteur FROM livres WHERE titre like "?a*";
8) « Quel est le nombre des abonnés ?».
SELECT count(*) FROM Abonnes;
9) « Quel est le nombre moyen d'exemplaires des livres ?»
SELECT avg(qte) AS [Nombre moyen] FROM Livres;
10) « Quel est, par livre, le nombre d'abonnés l'ayant emprunté ?»
SELECT numinv,
Count (numab) AS [Nombre Abonnés] FROM prets GROUP BY numinv
11) « Quel est, par livre emprunté par plus d'une personne, le nombre d'abonnés l'ayant emprunté ?»
SELECT numinv,
Count(numab) AS [Nombre Abonnés] FROM prets GROUP BY numinv
HAVING count(numab)>1
12) Le numéro de livre emprunté par l'abonné "12" est
cherché uniquement dans la table Prets.
SELECT numinv FROM prets WHERE numab=12;
Si on ne donne comme critère que le nom "Benali" de cet
abonné, il faut consulter aussi la table Abonne et dans la
même requête.On dit jointure de deux tables.
« Quel est le numéro du livre emprunté par "Benali"
?»
SELECT numinv
FROM prets
WHERE numab = (SELECT numab
FROM abonnes
WHERE nom = "Benali");
Le SELECT interne entre parenthèses est dit requête
imbriquée (ou sous-requête)
12') On peut aussi l'écrire comme suit avec un seul SELECT (Exercice
: vérifiez-le)
SELECT numinv
FROM Prets, Abonnes
WHERE Prets.numab = Abonnes.numab
AND Abonnes.nom = "Benali";
En rajoutant la condition de jointure Prets.numab =
Abonnes.numab dans la clause WHERE.
13) « Quels sont les titres des livres empruntés par l'abonné "12"
? »
SELECT titre
FROM Livres
WHERE numinv IN (SELECT numinv
FROM Prets
WHERE numab = 12);
Exercice : Réécrire cette requête sous la forme avec 1 seul SELECT
(cf. 12').
13') Un forme plus intéressante où on exprime la jointure est :
SELECT titre FROM Livres INNER JOIN Prets
ON Livres.numinv=Prets.numinv WHERE numab = 12;
On n'a pas à exprimer la condition de jointure Livres.numinv =
Prets.numinv dans la clause WHERE.
« Quels sont le titre et le nombre d'exemplaires pour les livres
empruntés par l'abonné "12" ? ». On ne peut pas faire
comme dans 13)
SELECT titre, qte
FROM Livres, Prets
...
(Pourquoi ?)
La bonne requête est obligatoirement :
SELECT titre, qte FROM Livres INNER JOIN Prets
ON Livres.numinv = Prets.numinv WHERE numab = 12
15) « Quel sont les noms des abonnés et les titres des livres empruntés ?»
SELECT titre , nom FROM Livres, Prets, Abonnes WHERE Livres.numinv = Prets.numinv
AND Abonnes.numab = Prets.numab;
Exercices :
On peut modifier les données en affichant la table en "mode création" et en intervenant directement sur les lignes et les valeurs affichées. Un nouvel enregistrement est à ajouter en fin de table affichée. Pour supprimer une ligne on la sélectionne et on agit sur le bouton droit pour dérouler un menu.
Mais quand on programme on a besoin d'écrire des instructions pour faire ces opérations. Pour cela on a en SQL :
INSERT INTO INSERT INTO Livres
VALUES (345, "Weaving the Web", "T. Berners-Lee", 2);
« Augmenter de 1 la quantité du livre 345»
UPDATE Livres SET qte = qte + 1 WHERE numinv = 345
La clause WHERE sert à indiquer la (ou les) lignes
concernée(s) par la mise à jour.
« Supprimer tous les prêts faits par l'abonné 12»
DELETE FROM Prets WHERE numab = 12
La clause WHERE sert à indiquer la (ou les) lignes
concernée(s) par la suppression.
Sans cette clause, ce seront toutes les lignes qui seront supprimées (ou
modifiées si UPDATE).
Ces commandes sont à utiliser quand on écrit des programmes d'exploitation d'une base de données. Ils sont intéressantes aussi quand on a plusieurs lignes à modifier pareillement en mode-création. Une seul instruction SQL suffira dans ce cas.