Bases de données et outils bioinformatiques utiles en génétique

Les bases de données relationnelles

Chapitre 10 : Introduction aux requêtes

1 - Préambule

Nous savons désormais stocker des informations structurées dans les tables d'une base de données relationnelle. Cette étape franchie, il nous faut maintenant apprendre à gérer ces

  informations, et à retrouver celles dont nous avons besoin quand cela s'avère nécessaire.

Une base de données a besoin de maintenance. Il faut pouvoir supprimer les informations obsolètes après les avoir archivées. Il est, par exemple, inutile de laisser traîner dans une

  BDD des données relatives à des factures qui ont été réglées, et qui sont relatives à un exercice clos.

Une base de données est souvent une mine d'informations, en particulier dans le domaine économique et financier. Il est très important pour le bon fonctionnement d'une   entreprise que ces informations puissent être retrouvées rapidement et simplement par les personnes qui en ont besoin et qui sauront en faire bon usage.

Pour ce faire, la requête constitue l'outil adéquat. La requête est, par ordre d'importance   décroissante , le deuxième "objet" des BDD après la table.

Comme pour les autres chapitres de ce cours, nous nous servirons du SGBD Access pour   les développements pratiques.

2 - Les trois fonctions des requêtes

L'outil requête a trois fonctions principales :

la réalisation de vues présentant tout ou partie de l'information contenue dans la BDD. Dans une base de données relationnelle, les données sont éparpillées dans de multiple tables, liées par des relations, et contenant souvent des codes non explicites. Pour appréhender, en partie ou en totalité, le contenu de la base, il faut rassembler les données

utiles dans une seule table, que l'utilisateur peut consulter directement ou via un formulaire. Pour ce faire, on sélectionne des colonnes dans différentes tables, et on met les lignes en correspondance grâce aux relations ; la maintenance de la BDD. Cette opération consiste à archiver et / ou supprimer des

  enregistrements obsolètes, mettre à jour des données révisables, rechercher et supprimer

 les doublons indésirables, etc. Elle concerne des lignes particulières, mais le nombre de

colonnes n'est pas modifié ; la recherche d'information dans la BDD. Cette opération consiste à créer une soustable contenant les enregistrements répondant à certains critères et appartenant à certains

 champs. Elle porte à la fois sur les lignes et les colonnes d'une table, ou de plusieurs  tables liées par des relations.

3 - Les différents types de requêtes

Pour assurer les trois fonctions précitées, différents types de requêtes ont été créés, que   l'on retrouve dans presque tous les SGBD. On peut les classer ainsi :

La sélection simple ou projection permet de réaliser les vues précitées ;

La sélection est l'outil de recherche d'information par excellence, même si ce n'est pas le seul qui soit utilisé. Cette requête est dotée de deux perfectionnements importants (la

jointure et le regroupement) ;

Les opérations ensemblistes (dont la plus importante est l'union), auxquelles on peut associer l'ajout. Elles permettent de regrouper dans une même table des enregistrements

       provenant de deux tables différentes ;

Les requêtes de maintenance sont principalement la mise à jour et la suppression. La première permet de modifier le contenu de certains champs, la seconde de supprimer

certains enregistrements ;

L'analyse croisée est une spécificité d'Access. Comme son nom l'indique, c'est un outil  d'analyse qui permet, sous certaines conditions, de réorganiser complètement une table.

Le SGBD Access permet de créer des requêtes en utilisant soit une interface graphique,   soit le langage SQL. Nous étudions tour à tour ces deux possibilités :

Interface graphique. La sélection simple (ou projection) fait l'objet du chapitre 11. La sélection est étudiée dans le chapitre 12, et ses perfectionnements dans les trois chapitres

 suivants. L'ajout et l'analyse croisée sont regroupées dans le chapitre 16. La mise à jour

       et la suppression sont étudiées dans le chapitre 17.

Langage SQL. Quatre chapitres (18-21) sont consacrés à la création des divers types de requêtes.

Dans le SGBD Access, la création d'une requête union n'est possible qu'en SQL. Nous évoquerons ce point au chapitre 20. On notera par ailleurs que deux opérations

ensemblistes (intersection et différence) ne sont pas implémentées dans Access.

4 - Conclusion

La recherche d'information est , à notre humble avis, l'aspect le plus intéressant -- pour ne pas dire le plus passionnant -- de l'étude des bases de données. En enchaînant astucieusement un petit nombre de requêtes bien choisies, on peut souvent faire des

  merveilles dans la recherche d'information, sans avoir besoin de recourir aux outils plus compliqués (et combien plus onéreux !) du "data mining", ou autres techniques à la mode.

Nous voyons que, comme une table, une requête présente un double aspect :

l'aspect structure, lequel est défini en mode création ; l'aspect résultat, qui est représentée par une table à l'existence volatile, laquelle s'appelle

 "feuille de données" dans la terminologie de Microsoft.

Comme pour une table également, on peut passer rapidement d'un aspect à l'autre en cliquant dans la barre d'outils sur le bouton (en mode feuille de données), ou le bouton

(en mode création).

Pour conserver la structure de la requête, il suffit de cliquer sur l'icône "Enregistrer", de donner un nom (par exemple, "Sélection des personnes") à la requête dans la boite de dialogue qui s'ouvre, et de confirmer. Ce nom figurera désormais dans la fenêtre "Base de données" (l'objet "Requêtes" étant sélectionné), précédé de l'icône , pour rappeler qu'il   s'agit d'une requête de sélection. Si nous fermons la fenêtre de définition de la requête sans avoir préalablement enregistré la structure, le SGBD nous demande si nous voulons conserver la requête. Dans l'affirmative, la boite de dialogue s'ouvre, et nous procédons comme précédemment.

Mais le résultat de la requête a disparu ! Pour retrouver cette "feuille de données" volatile, il faut relancer la requête, soit en double-cliquant sur son nom, soit en la sélectionnant et

      en cliquant sur le bouton      "Ouvrir" (lequel devrait plutôt s'appeler "Exécuter").

3 - La requête avec création de table

Le résultat d'une requête est une table, et il peut être enregistré comme tel. Pour ce faire, nous sélectionnons la requête précédente, et nous cliquons sur le bouton "Modifier". La requête s'ouvre en mode création. Nous cliquons sur le bouton de la barre d'outils et, dans la liste déroulante qui s'affiche, nous sélectionnons "Requête Création de table ".

  Une boite de dialogue s'ouvre, dans laquelle nous renseignons le nom de la table ("Liste de personnes", par exemple). Dans la liste des requêtes, "Sélection des personnes" apparaît maintenant avec l'icône , qui rappelle que le résultat de la requête est enregistré dans la base sous forme d'une table.

Exécutons la requête : deux boites d'alerte s'ouvrent successivement. Pas de panique, répondons "oui" dans les deux cas. Si la table existe déjà, une troisième boite d'alerte prévient de son écrasement. Que de précautions ! (Si ces alertes vous agacent, vous

  pouvez les supprimer en utilisant la rubrique "Outils" du menu. Cliquez sur "Options ", puis sur l'onglet "Modifier/Rechercher, et décochez les cases de la zone "Confirmer").

Nous pouvons maintenant vérifier, dans la fenêtre "Base de donnée" (l'objet "Tables" étant sélectionné), que la table "Liste de personnes" a bien été créée. Si nous l'ouvrons,   nous constatons que son contenu correspond bien à la structure de la requête "Sélection des personnes".

Comment faire pour qu'une requête ne crée plus de table ? Il semble que l'éditeur Microsoft n'ait pas prévu la chose en mode graphique, si bien qu'il faut passer en mode SQL. La fenêtre de création (ou modification) de la requête étant ouverte, nous cliquons sur la petite flèche adjacente à l'icône "Affichage". Dans la liste déroulante, nous   choisissons "Mode SQL", et la traduction de notre requête en langage SQL s'affiche. Dans la première ligne du code, nous repérons le terme "INTO" suivi du nom de la table (éventuellement écrit entre crochets). Nous les supprimons tous les deux, nous refermons la fenêtre, et nous confirmons la modification de la requête.

4 - Le tri simple et le tri multiple

On ne peut retrouver rapidement des informations dans une liste que si elle est triée (par ordre alphabétique). Or la liste des personnes que crée notre requête présente le défaut d'être présentée dans l'ordre où les informations ont été saisies. Une table, en effet, se remplit toujours par la ligne la plus basse. Pour trier la table, nous pouvons utiliser le bouton , mais il est plus pratique de rendre l'opération automatique. Sélectionnons la requête, et cliquons sur . Dans la grille, cliquons à l'intersection de la colonne  

"nom_personne" et de la ligne "Tri :". Une liste s'affiche, qui nous propose les trois options possibles : croissant, décroissant et non trié. Choisissons "croissant", refermons la fenêtre, confirmons la modification, et relançons la requête : la table "Liste de personnes" s'affiche désormais par ordre alphabétique des noms, comme le montre la feuille de données ci-dessous.

Nous pouvons également demander le tri croissant dans le champ "prénom". Si deux personnes portent le même nom, elles apparaîtront dans l'ordre croissant de leurs prénoms respectifs. Attention ! ce tri multiple s'exécute de gauche à droite : par les noms d'abord, par les prénoms ensuite. Si nous voulons obtenir le résultat inverse, il faut que nous

  placions la colonne nom à droite de la colonne prénom dans la grille de création de la requête. Pour ce faire, il faut sélectionner (par le haut) la colonne à déplacer, puis la tirer (toujours par le haut) jusqu'à sa nouvelle position.

5 - L'élimination des doublons

Dans la table "Liste de personnes", Paul Lechant apparaît à deux reprises : nous avons affaire à un doublon, une information répétée deux fois ou plus. Dans la table

"Personnes" de départ, cette double apparition de Paul Lechant était justifiée par deux   affiliations distinctes. La sélection a fait disparaître les informations correspondant à l'affiliation et créé le doublon. Nous pouvons faire en sorte que les doublons soient éliminés du résultat :

grâce à une modification des propriétés de la requête ;

     grâce à une opération de regroupement.

Première méthode. Ouvrons la requête "Requête1" en mode création. Effectuons un clic droit dans la fenêtre de définition de la requête et sélectionnons "Propriétés" dans la liste déroulante, ou cliquons sur l'icône "Propriétés". La boite de dialogue "Propriétés de la

  requête" s'ouvre. Modifions la propriété "Valeurs distinctes" de "Non" à "Oui". Fermons la boite de dialogue, et basculons en mode feuille de données : les doublons ont disparu,

La signification du contenu de la ligne "Champ :" de la grille ci-dessus est la suivante :

la requête crée une feuille de données contenant une colonne intitulée "personne" ; chaque ligne contiendra le nom, puis un espace, puis le prénom. Ces données

   proviendront de la table située au-dessus de la grille.

Le signe & désigne, comme en Visual Basic, l'opérateur de concaténation de chaînes. Les crochets [ ..] signifient que l'on évoque le contenu des champs correspondants.

  L'espace qui sépare le nom du prénom est mis entre guillemets pour rappeler qu'il s'agit d'une chaîne de caractères. Le résultat de la requête est le suivant :

De la même manière, on peut concaténer le code postal avec un tiret suivi du nom de la commune, reconstituer une adresse complète, etc. Cette technique de reconstitution de   chaînes est intéressante parce que, au nom du principe d'atomisation, les informations situées dans une BDD sont divisées le plus possible en petits morceaux.

De manière plus générale, une requête avec création de champ permet d'effectuer des opérations (numériques ou sur chaînes de caractères) sur le contenu des champs d'un même enregistrement, c'est à dire horizontalement. On peut effectuer des opérations   verticalement dans une table (en utilisant ou non la notion de regroupement), mais on obtient une meilleure présentation en se servant des états, que nous étudierons dans un chapitre ultérieur.

7 - Les requêtes emboîtées

Une requête peut prendre comme point de départ la feuille de données résultant de l'exécution d'une autre requête. Il suffit de lancer la seconde requête pour que la première s'exécute en premier lieu. On peut généraliser, et créer une chaîne de requêtes qui   s'exécutent dans l'ordre par simple lancement de la dernière. Il faut simplement veiller à ce que chaque requête (à l'exclusion de la dernière) ne crée pas de table. Sinon, le logiciel proposera de partir de cette table, et la chaîne sera rompue.

A titre d'exemple, créons les requêtes suivantes :

la requête n° 1 extrait les colonnes nom et prénom de la table "Personnes", et trie par    ordre croissant des noms ;

la requête n° 2 part du résultat de la requête n° 1 et élimine les doublons (par

 modification de propriété, ou par regroupement) ;  la requête n° 3 part du résultat de la requête n° 2 et concatène nom et prénom.

Il suffit de lancer la troisième requête pour que l'ensemble s'exécute et fournisse le résultat obtenu au paragraphe précédent. Nous avons ainsi créé un automatisme élémentaire. Nous

  verrons dans un chapitre ultérieur que l'on peut obtenir le même résultat avec une macro.

Il ne faut pas abuser de l'emboîtement, et les professionnels conseillent généralement de   ne pas emboîter plus de 3 requêtes à la file. Il y a plusieurs raisons à cela :

si une requête est utilisée plusieurs fois dans une application, toutes les requêtes emboîtées qui la précédent seront re-exécutées. On allonge ainsi le temps machine requis

pour l'application ; si une requête faisant partie d'un emboîtement contient une erreur, cette erreur sera signalée par le système (du moins par le SGBD Access) comme faisant partie de la

dernière requête de l'emboîtement. L'emboîtement rend donc la correction des erreurs plus difficile ; l'emboîtement se programme malaisément lorsqu'on utilise le langage SQL, et le risque

 d'erreur croit avec le nombre de requêtes emboîtées.

8 - La requête multifonctionnelle

Pour des raisons didactiques, nous avons créé une nouvelle requête pour chaque opération que nous voulions réaliser. Dans la pratique, nous éviterons de multiplier les requêtes, en

  regroupant le plus possible les opérations à effectuer dans une même requête.

Ainsi, la requête représentée par la figure ci-dessous permet d'obtenir le résultat final (la liste des noms concaténés avec les prénoms, dans l'ordre alphabétique, et sans doublons)

en une seule étape :

et on peut lui demander en plus de créer une table si on le désire. On notera qu'il n'est pas nécessaire que le nom de la table figure dans la grille (mais la table doit être présente au-

  dessus de la grille), et qu'il est inutile de spécifier un tri car ce dernier est implicite en cas de regroupement.

9- La requête multi-table

Dans une BDD relationnelle, les informations sont généralement dispersées dans plusieurs tables (une dizaine couramment, voire plus) liées par un nombre similaire de relations, ce qui fait qu'il est impossible d'avoir une vue globale du contenu de la base. Une requête

  multi-table permet de rassembler dans une même table les informations désirées, et d'obtenir au premier coup d'oeil une idée de ce contenu.

Revenons à la table "Personnes" que nous avons utilisée au début de ce chapitre. Une personne pouvant travailler pour plusieurs organismes, et un organisme pouvant employer les services de plusieurs personnes, la table "Personnes" doit être séparée en trois tables  

(dont une table de jonction), liées par des relations. Le schéma relationnel correspondant apparaît sur la figure ci-dessous.

Mais cette séparation en trois tables fragmente les données, et nous empêche de voir simplement qui travaille pour qui. Si nous nous plaçons dans la table "Personnes", nous voyons aussi (grâce à la sous-table) les données de la table "Affiliation", mais pas celles de la table "Organismes". Si nous nous plaçons dans la table "Organismes", nous voyons   aussi (grâce à la sous-table) les données de la table "Affiliation", mais pas celles de la table "Personnes". La solution consiste à rassembler pour examen, dans une même table, les données que nous voulons examiner simultanément. Bref, il faut que nous exécutions une requête de sélection simple multi-table.

Dans la fenêtre "Base de données", l'objet "Requêtes" étant sélectionné, nous cliquons sur

"Créer une requête en mode Création". Dans la boite de dialogue "Afficher la table", nous sélectionnons les trois tables nécessaires (l'une après l'autre, ou simultanément grâce à la touche CTRL), et nousconstruisons la requête représentée ci-dessous.

Nous obtenons ainsi une vue claire du contenu de la base, vue que nous n'avons   absolument pas lorsque nous examinons les trois tables de départ.

Attention ! Si nous effectuons une sélection sur les colonnes de deux tables qui ne sont pas liées par une relation, le logiciel associe chaque ligne de la première table à toutes les lignes de la seconde (cela s'appelle faire leur produit vectoriel). Le résultat est

  généralement sans intérêt et, si les deux tables sont conséquentes, l'opération risque d'être fort longue.

Dans le même ordre d'idée, il ne faut jamais introduire dans la fenêtre de création de requête une table dont la présence n'est pas nécessaire. Le résultat de la requête risque

  d'être tout à fait aberrant.

Dans la fenêtre de création d'une requête, nous pouvons supprimer cette relation. La procédure est identique à celle pratiquée dans la fenêtre "Relations" : nous effectuons un   clic droit sur la relation, et nous choisissons "Supprimer". Nous fermons la fenêtre de création de la requête, et nous enregistrons cette dernière.

Si nous ouvrons la fenêtre "Relations", nous constatons que la relation qui lie les deux tables existe toujours. Cette relation est en quelque sorte une propriété des deux tables.

La suppression que nous avons effectuée est liée à une requête particulière. Elle n'a d'effet que lors de l'exécution de la requête. Ce n'est pas une propriété des deux tables,

  mais une propriété de la requête.

En conclusion, les opérations que nous effectuons sur les relations (création, suppression,   modification des propriétés) ont un effet :

permanent lorsqu'elles sont effectuées dans la fenêtre "Relations" ; éphémère lorsqu'elles sont effectuées dans la fenêtre de création d'une requête

   particulière.

Remarque : même s'il n'existe pas de relation entre deux tables, le SGBD Access en crée une automatiquement lorsque vous ajoutez ces deux tables à la fenêtre de création d'une   requête, à condition que ces tables aient chacune un champ du même nom et du même type de données, et qu'un des deux champs possède une clé primaire.

3 - Le produit vectoriel

Nous rouvrons la requête précédente en mode "Modification". Nous vérifions qu'aucune relation n'apparaît entre les deux tables. Dans la grille, nous introduisons les champs

  "Nom" et "Prénom" de la première table, et les champs "Commune" et "Code postal" de la seconde. La feuille de données résultante contient 20 lignes ! Que s'est-il passé ?

Le SGBD a associé chaque ligne de la première table (il y en a 4) à chaque ligne de la seconde (il y en a 5). On dit qu'il a effectué le produit vectoriel des deux tables.

  L'absence de relation fait que le SGBD ne sait pas comment il doit associer les lignes des deux tables ; de ce fait, il réalise toutes les combinaisons possibles.

Il faut faire attention au fait que le produit vectoriel peut nous conduire à créer des tables gigantesques : le produit de deux tables contenant chacune 1.000 enregistrements est une

  table possédant 1 million de lignes !

En pratique, on n'utilise pas le produit vectoriel, sauf dans des cas très rares, comme par exemple pour réunir dans une seule table des comptages isolés. Ces derniers se présentent   en effet sous forme de tables à une seule ligne, et l'on peut en faire le produit vectoriel sans risque, car le résultat est alors une table à une seule ligne.

4 - La jointure interne

Dans la fenêtre de création de la requête, nous rétablissons la relation entre les deux tables. Cette fois, la feuille de données résultante ne contient plus que 3 lignes, comme le montre la figure ci-dessous.

Pour juger les performances de l'entreprise, ces données brutes sont malcommodes. Un décideur a besoin du chiffre d'affaires non seulement au jour le jour, mais aussi à la semaine, au mois et pour l'exercice annuel. Il le veut toutes agences confondues pour juger des performances de l'entreprise. Il le veut aussi agence par agence, pour juger des performances de ces dernières (le responsable de l'agence Est va prendre un savon). Et il ne veut pas être obligé de sortir sa calculette pour regrouper les chiffres qui l'intéressent ; le regroupement doit être effectué par le SGBD.

Pour l'exemple très simple que nous avons choisi, deux regroupements du chiffre d'affaires sont possibles :

•    par date, en sommant les CA des trois agences, de manière à obtenir le CA quotidien de l'entreprise. Dans ce cas, la notion d'agence s'efface ; 

•    par agence, en sommant les CA de chaque agence sur l'ensemble des dates mentionnées dans la table. Dans ce cas, la notion de date s'efface. 

Quand peut-on envisager d'effectuer un regroupement dans une table ?

•    Quand il existe un champ possédant des doublons. Dans l'exemple ci-dessus, il serait impossible de regrouper par date si chaque valeur de la date n'apparaissait qu'une seule fois. De même, il serait impossible d'envisager le regroupement par agence, si le nom de chaque agence n'apparaissait pas de manière répétée. 

Quelle opération peut-on envisager quand on effectue un regroupement ? La nature de cette opération dépend du type des données à regrouper :

•    des données numériques ou monétaires se prêtent à des opérations arithmétiques (somme, moyenne, minimum, maximum), statistiques (variance et écart-type), voire mathématiques. Tout dépend des possibilités offertes par le SGBD ; 

•    des données de type texte se prêtent au classement et au comptage (la concaténation n'est pas prévue). 

Nous voyons tout de suite qu'une requête de regroupement met en jeu le plus souvent deux colonnes :

•    une colonne sur laquelle s'effectue le regroupement (elle doit contenir des doublons). On peut effectuer le regroupement sur plusieurs colonnes lorsqu'il existe des doublons s'étendant sur plusieurs colonnes ; 

•    une colonne sur laquelle s'effectue une opération (somme, ou moyenne, ou etc.). 

La mise au point d'une requête de regroupement peut s'avérer délicate, et il faut garder en mémoire les observations suivantes :

•    Regroupement. Le SGBD permet d'effectuer le regroupement sur plusieurs colonnes, mais la probabilité pour qu'il existe des doublons (sur plusieurs colonnes) diminue très vite avec le nombre de ces dernières. Dans beaucoup de cas rencontrés en pratique, on effectue le regroupement sur une colonne seulement ; 

•    Opérations. On peut envisager d'effectuer des opérations sur plusieurs colonnes, si elles s'y prêtent. Dans l'exemple ci-dessus, le CA pourrait être ventilé sur deux colonnes (l'une pour les biens, l'autre sur les services, par exemple), que nous pourrions sommer séparément ;

•    Requête multi-table. Une requête de regroupement peut impliquer plusieurs tables liées par des relations, mais il est alors beaucoup plus facile de commettre des erreurs de conception. Il est donc prudent d'utiliser d'abord une requête de sélection pour regrouper dans une même table les données dont on a besoin, avant d'appliquer la requête de regroupement, même si cela risque d'augmenter un peu le temps d'exécution. 

Il résulte de ces considérations qu'une requête de regroupement met généralement en jeu un nombre très restreint de champs. En fait, il est fortement conseillé de commencer la mise au point d'une requête de regroupement sur deux colonnes seulement, et que ces deux colonnes appartiennent à la même table (ou à la même feuille de données).

3 - La création de la requête

Nous allons créer le premier regroupement envisagé au paragraphe précédent (calcul du CA quotidien de l'entreprise). Pour ne pas nous tromper, nous allons opérer de manière méthodique.

Première étape. Elle consiste à ouvrir la fenêtre de définition d'une requête, et à y introduire la table sur laquelle on veut effectuer l'opération de regroupement (ici "Résultats").

Seconde étape. Elle consiste à introduire dans la grille le champ sur lequel s'effectue le regroupement. Comme nous cherchons à calculer des CA quotidiens, ce champ ne peut être que la date. Nous introduisons donc le champ "Date" dans la grille de création de la requête.

Troisième étape. Il faut signifier au SGBD que la requête implique un regroupement sur le champ "Date". Pour ce faire, nous cliquons sur l'icône

"Totaux". Une nouvelle ligne, baptisée "Opération :", apparaît dans la grille de définition de la requête, entre "Table :" et "Tri :" (figures ci-dessous). La valeur par défaut, pour le champ "Date", est justement "Regroupement" (si cette valeur n'apparaît pas, nous cliquons sur la ligne et nous sélectionnons "Regroupement" dans la liste déroulante). Ainsi, le regroupement sera effectué sur la date.

Quatrième étape. Il faut maintenant introduire le champ sur lequel s'effectue l'opération liée au regroupement. Dans le présent exemple, l'opération consiste à sommer les CA de chaque agence. Nous introduisons donc le champ "CA" dans la grille.

Cinquième étape. Il faut indiquer au SGBD à quelle opération il doit procéder sur le champ "CA". Nous cliquons sur la ligne "Opération :", nous utilisons la liste déroulante pour remplacer "Regroupement" (qui s'est inscrit par défaut) par "Somme".

La requête se présente ainsi comme le montre la figure ci-dessous à gauche.

Nous pouvons ainsi déterminer le nombre d'enregistrements d'une table, à condition que nous soyons sûrs que la colonne utilisée pour l'opération ne contient pas de case vide. Nous pouvons songer à utiliser une colonne dans laquelle le Null est interdit, mais il n'en

  n'existe pas toujours. Pour nous affranchir du comptage sur une colonne particulière, nous procédons comme suit :

nous ajoutons la table "Résultats" dans la fenêtre de création de la requête, mais nous

 n'introduisons aucun champ dans la grille ;

sur la ligne "Champ :", nous inscrivons le nom de la colonne dans laquelle figurera le

 résultat du comptage, soit par exemple "Comptage" ;

nous faisons suivre ce nom de deux points et d'un espace. Le SGBD sait alors que la

 suite définit le contenu du champ ;

    à la suite, nous écrivons la fonction Compte(*). L'astérisque indique au SGBD qu'il doit  compter les lignes de la table, sans utiliser de colonne particulière ;

sur la ligne "Table :", nous n'inscrivons rien. En effet, la colonne que nous allons créer n'appartient à aucune table. Par contre, le SGBD sait que la fonction "Compte(*)"

 s'applique à la table "Résultats", qu'il était donc indispensable d'introduire dans la  fenêtre.

Le principe de l'opération est simple. Nous créons une requête sélection sans introduire aucun des champs de la table sélectionnée. Nous demandons à cette requête de créer un nouveau champ, auquel nous donnons un nom (indispensable pour l'affichage). Nous définissons le contenu de ce nouveau champ à l'aide la fonction "Compte()" qui compte   des lignes. Nous utilisons l'astérisque comme argument, de telle sorte que la fonction compte les lignes de la table. La requête affiche une feuille de données possédant une seule colonne (puisque nous l'avons définie ainsi), et une seule ligne (puisque la fonction fournit une valeur unique).

Les figures ci-dessous représentent la requête (à gauche) et la feuille de données   résultante (à droite).

Cette méthode compte effectivement les lignes d'une table, même si elles sont toutes vides. Vous pouvez faire l'expérience en effaçant tout ce que contient la table "Résultats"

(après en avoir gardé copie).

Les bases de données relationnelles

Chapitre 16 : les requêtes ajout et analyse croisée

1 - Introduction

Nous avons consacré 5 chapitres à la requête de sélection et à ses divers développements. Nous avons d'abord étudiée sa forme élémentaire (la sélection simple), puis sa forme générale (la sélection avec critères). Nous avons ensuite découvert la notion de jointure, qui s'introduit naturellement lorsque la sélection porte sur plusieurs tables, et de là nous sommes passés à la correspondance et

  à la non-correspondance. Nous avons enfin perfectionné la sélection grâce à la notion de regroupement, ce qui nous a permis d'effectuer des synthèse, et de manipuler les doublons. Bref, comme nous pouvons le constater, la sélection est la reine des requêtes !

Cependant, la sélection ne peut pas tout faire, et sous la pression des besoins, d'autres types de requête ont été créés. Nous en avons rassemblé deux dans ce chapitre (l'ajout et l'analyse croisée), et deux dans le chapitre suivant (la suppression et la mise à jour). La requête analyse croisée est une spécificité   d'Access, et on ne la retrouve généralement pas dans les autres SGBD. Pour les afficionados du SQL, la requête ajout n'est pas connue sous ce nom ; elle est simplement considérée comme un cas particulier d'utilisation de la commande INSERT.

Il reste une grande absente, la requête union, qu'on ne peut pas créer dans la fenêtre graphique d'Access, mais que nous traiterons lorsque nous étudierons le

  langage SQL.

Comme pour les autres chapitres de ce cours, nous utilisons le SGBD Access   comme support pratique.

2 - Le fonctionnement de la requête ajout

La requête ajout permet d'insérer les enregistrements d'une table n° 2 dans une table n° 1. L'opération ne peut se faire que si les deux tables ont au moins un champ commun (même nom, même type de données ou conversion de type possible -- cela dépend du SGBD). Comme le montre la figure ci-dessous, les champs de la table n° 2 qui ne sont pas communs avec ceux de la table n° 1 sont

  ignorés ou refusés (ex : le champ "T"). Les champs de la table n° 1 qui n'existent pas dans la table n° 2 ne sont pas renseignés (ex : le champ "W") -- à moins que le champ ne soit du type NuméroAuto, auquel cas le système le remplira luimême, comme nous le constaterons dans un prochain exemple.

UVW

1

UVW

U2

1 53

2

64

3

75

 4

                                                                                                                                                                                            6

7

                                                            Table n°2      Table n°1      Résultat

Attention ! la requête ajout modifie irréversiblement la table à laquelle on ajoute des données (la table n° 1 dans la terminologie du paragraphe ci-dessus).

L'opération une fois effectuée, il n'est plus possible de revenir en arrière. Il est   donc très fortement recommandé de créer une copie de la table n° 1 avant de procéder à l'ajout. La table que l'on ajoute (la table n° 2) n'est ni modifiée, ni supprimée, au cours de l'opération.

Pour créer une requête ajout dans le SGBD Access, nous introduisons la table à ajouter (la table n° 2 dans notre terminologie) dans la fenêtre de création/modification d'une requête, et nous sélectionnons les champs que nous voulons -- ou que nous pouvons -- ajouter. Puis nous cliquons sur la petite flèche qui borde l'icône "Type de requête" et, dans la liste déroulante qui s'affiche,   nous choisissons "Requête Ajout ". Dans la boite de dialogue "Ajout" qui s'ouvre, nous précisons quelle est la table dans laquelle doit s'effectuer l'ajout (la table n° 1 dans notre terminologie). La grille de définition de la requête acquiert alors une ligne supplémentaire intitulée "Ajouter à :", comme le montre la figure ci-dessous.

Les données de la table n° 2 seront effectivement ajoutées à la table n° 1 lorsque nous exécuterons la requête. Des messages nous avertiront de ce qui se passera

  -- à moins que nous n'en ayons décidé autrement dans les options (onglet "Modifier/Rechercher", cadre "Confirmer").

Diverses sophistications sont possibles. Nous pouvons :

sélectionner une partie seulement des champs de la table n° 2 ; sélectionner à l'aide de critères les enregistrements de la table n° 2 qui doivent

 être ajoutés à la table n° 1 ;

  remplacer la table n° 2 par une requête ; faire en sorte qu'une requête (mono ou multi-table) effectue également un ajout

 dans une autre table.

Il nous faut cependant bien veiller à ce que les colonnes qui sont utilisées pour définir les opérations de sélection, mais qui ne sont pas concernées par l'ajout, ne contiennent aucune information sur la ligne "Ajouter à :", sinon le SGBD Access nous gratifiera d'un message d'erreur qui nous plongera dans des abîmes de réflexion (exemple à méditer : "Destination de sortie 'requête' répliquée").

Voici une liste non limitative des diverses utilisations de la requête ajout :

rassembler dans une même table des enregistrements provenant de tables séparées. Dans cette application, la requête ajout entre en concurrence avec la requête union, que nous étudierons dans l'un des chapitres consacrés au SQL

 (chapitre 22). Attention : les deux requêtes n'imposent pas les mêmes contraintes, et ne fournissent pas forcément le même résultat (problème des

doublons) ;

  imposer à une table des propriétés particulières, en l'ajoutant à une table modèle, initialement vide et dont les propriétés sont soigneusement définies

(largeur et visibilité des colonnes, tri, police de caractères, etc.) ; garder trace d'un classement dans une table ;

 etc.

Dans le paragraphe suivant, nous examinerons quelques exemples d'utilisation   de la requête ajout.

3 - L'utilisation de la requête ajout (exemples)

Notre premier exemple illustre simplement la procédure exposée ci-dessus. La   figure suivante représente le contenu des deux tables avant et après l'ajout.

                            Table n° 2                                                    Table n° 1 (après ajout

Table n° 1 (avant)

                      (avant & après)                                                      de la table n° 2)

Attention ! Le résultat de l'ajout dépend de l'ordre dans lequel on effectue les opérations. Si nous permutons les rôles des tables 1 et 2, nous redéfinissons

notre requête ajout comme suit :

car le SGBD Access n'acceptera pas que nous tentions d'introduire dans une

Par copier/coller (structure seulement) à partir de la table n° 1, nous obtenons une table "modèle" qui contient les mêmes champs (mais vides). Nous modifions le format du champ "Date", initialement "Date, abrégé", en "Date, complet" et nous enregistrons la modification. Grâce à une macro (cet objet est étudié dans   les chapitres 26 et suivant), nous créons une copie de la table "modèle" que nous appelons table n° 3, puis nous lui ajoutons tous les champs de la table n° 1. Nous constatons que, dans la table n° 3, la date s'affiche en format complet, comme le montre la figure ci-dessous.

                       Table n° 1                    Table n° 3                    Table n° 3 (après ajout

                 (avant & après)        (copie du modèle)                    de la table n° 1)

Il existe en principe une méthode beaucoup plus simple pour modifier les propriétés d'un champ de manière automatisable. Elle consiste à écrire une requête SQL utilisant la commande ALTER TABLE avec la clause MODIFY

(chapitre 18). Malheureusement, la clause MODIFY ne fonctionne pas dans Access et on utilise la requête ajout pour pallier cette déficience.

Notre quatrième exemple montre comment on peut garder la trace du classement d'une table. Pour ce faire, nous traitons l'exemple d'une entreprise qui veut établir la liste de ses produits classés par ordre décroissant de chiffre d'affaires (CA) au cours de l'année écoulée. Le point de départ est une table contenant la liste des produits (classés par ordre alphabétique) avec leur CA. La

  méthode la plus simple consiste à trier la table par ordre de CA décroissant, à l'enregistrer, puis à la doter (en mode création) d'une colonne supplémentaire du type de données NuméroAuto. Mais si nous avons besoin d'automatiser l'opération, il nous faut recourir à une autre solution.

La figure ci-dessous montre la méthode utilisée. Nous trions la table de départ (Table1) par ordre de CA décroissant. Nous l'ajoutons à une table vide contenant les mêmes champs, plus un champ de type NuméroAuto (Table2). Puis, à l'aide d'une requête de sélection simple, nous trions la table Table2 par ordre   alphabétique du premier champ. Le résultat final est une table des produits classés par ordre alphabétique, avec une colonne indiquant le rang de classement par ordre de CA décroissant. On notera que le champ "Classement" a été rempli par le SGBD (l'opérateur ne peut pas écrire dans ce champ).

                                            Table1              Table1 (triée)       Table2 (avant ajout)

                               Table2 (après ajout de Table1)             Table2 (triée)

Une autre solution à ce problème consiste à utiliser la commande ALTER TABLE en SQL, avec la clause ADD COLUMN et le type de données COUNTER, qui

correspond à NuméroAuto (voir le chapitre 18).

4 - L'ajout sans doublons

La requête ajout crée des doublons si la seconde table contient des enregistrements identiques à ceux de la première. Lecas le plus flagrant résulte de l'ajout d'une table à elle-même, opération qui est tout à fait licite dans Access,

  même si son intérêt parait à peu près nul. On notera que la plupart des SGBD interdisent cette opération.

Le premier correctif auquel nous songions consiste à basculer de "Non" à "Oui" la propriété "Valeurs distinctes" de la requête ajout. Ainsi modifiée, la requête n'élimine pas les doublons qui résultent de l'ajout, mais évite de transporter dans

  la première table des enregistrements qui constituent des doublons dans la seconde. C'est mieux que rien, mais ce n'est pas suffisant.

Le second correctif auquel nous songions consiste à créer un index sans doublons sur les champs de la première table communs avec ceux de la seconde table. Lorsque nous lançons la requête ajout, nous recevons l'alerte suivante, qui   constitue un morceau d'anthologie en matière de message informatique. Mais si nous admettons que par "violation de clé" il faut entendre "violation d'indexation sans doublons", tout s'éclaire :

Cliquons sur "Oui" et le tour est joué : le SGBD n'ajoute que les enregistrements qui ne créent pas de doublon. Si nous cliquons sur "Non", la requête est annulée.  

Si nous cliquons sur "Aide", nous obtenons une aide qui n'a rien à voir avec le

Dans la fenêtre "Base de données", l'objet "Requêtes" étant sélectionné, nous cliquons sur le bouton nouveau, nous choisissons "Assistant Requête analyse   croisée" dans la liste qui s'affiche, et nous cliquons sur "OK". Le dialogue suivant s'établit avec l'assistant :

nous indiquons d'abord sur quelle table nous voulons opérer. Dans le cas

 présent, il s'agit de la table "Table1" ;

nous choisissons le champ "Société" comme "en-tête de ligne". Dans le jargon

 de l'éditeur, cela signifie que ce champ sera le premier de la nouvelle table ;

nous choisissons le champ "Rang" comme "en-tête de colonne". Cela signifie

   que le SGBD va créer les colonnes "1", "2", etc. ; nous choisissons "premier" et nous décochons la case "Oui, inclure les sommes

 des lignes" car les données sont du type texte et non du type numérique ; nous cliquons sur le bouton "Terminer" et nous basculons en mode feuille de

 données pour examiner le résultat (figure ci-dessous).

La requête analyse croisée est surtout utilisée dans le domaine financier, où elle sert à créer des bilans à partir de données comptables. Les nouvelles colonnes   qui sont crées correspondent alors à des périodes de temps données (jours, semaines, mois, etc.).

Attention ! Si nous demandons à cette requête de créer une table, le résultat obtenu est erroné. Il y a là un bug que nous pouvons contourner en créant une   requête sélection simple (avec création de table et conservation de tous les champs), opérant sur le résultat de la requête analyse croisée.

5 - Conclusion

Comme vous avez pu le constater, nous avons rassemblé dans ce chapitre deux requêtes qui n'ont pas de points communs, nous ferons mieux dans le chapitre   suivant, où les requêtes de suppression et de mise à jour peuvent être

regroupées sous la bannière unique de la maintenance des BDD.

Les bases de données relationnelles

Chapitre 17 : les requêtes de maintenance

1 - Introduction

Une base de données évolue sans arrêt : de nouveaux enregistrements sont introduits, d'autres sont archivés, d'autres sont modifiés, d'autres enfin sont

  supprimés. Des contrôles, suivis éventuellement de corrections, sont effectués.

Deux types de requête sont particulièrement utilisés pour ces opérations de   maintenance :

la suppression, qui permet de faire disparaître des enregistrements jugés  obsolètes, erronés ou inutiles ;

   la mise à jour, qui permet de modifier le contenu de certains enregistrements.

Sont supprimés ou modifiés les enregistrements qui répondent à certains critères. Ces opérations sont généralement effectuées sur une seule table à la fois. Cependant, par le jeu des relations, suppressions et modifications peuvent   se répercuter en cascade dans d'autres tables, si l'option correspondante a été choisie lors de la création de la relation, après que l'intégrité référentielle eût été requise (Cf. le chapitre 5).

La suppression est une opération qui s'effectue au niveau de l'enregistrement. La mise à jour intervient souvent à un niveau plus fin : seuls certains champs, dans

  certains enregistrements, sont concernés.

Attention ! Une fois effectuées, suppressions et mises à jour sont irréversibles. Avant d'effectuer une requête de ce type, il est indispensable d'effectuer une

copie des tables concernées, voire de la BDD toute entière.

Bien entendu, une opération de modification ou de suppression concernant un tout petit nombre d'enregistrements ne justifie pas la création d'une requête ; il suffit d'intervenir ponctuellement dans la table considérée. Par contre, si le

  nombre d'opérations à effectuer croit, la création d'une requête fait gagner du temps et diminuer le risque d'erreurs.

Comme il est d'usage dans tout ce cours, nous utiliserons le SGBD Access   comme support pratique.

2 - La suppression

La requête de suppression opère sur une table. Elle supprime les enregistrements (ou lignes) répondant à un ou plusieurs critères. Dans la figure   ci-dessous, les enregistrements répondant à ces critères sont colorés en rouge ; la requête les fait disparaitre irrémédiablement.

A titre d'exemple, l'opération de suppression peut être utile dans les cas suivants   :

retirer de la BDD d'une entreprise toutes les données économiques relatives à

 un exercice clos ;

retirer de la table des prêts (d'une bibliothèque) toutes les opérations terminées

 (le livre emprunté a été rendu) ; retirer du fichier journal d'un système informatique tous les enregistrements

 âgés de plus d'un mois ; retirer de la liste des clients tous ceux qui n'ont rien commandé depuis deux ans  

 ; éliminer d'une table tous les enregistrements incomplets (un champ donné n'a

 pas été renseigné) ; retirer de la table du stock tous les articles qui se sont mal vendus au cours des

 douze derniers mois ;  etc.

Supposons, à titre d'exemple, que nous voulions éliminer d'une table "Factures" toutes les factures soldées avant le 1er janvier de cette année (2002). Dans la fenêtre "Base de données" nous sélectionnons l'objet "Requêtes", puis nous double-cliquons sur "Créer une requête en mode Création". Dans la boite

"Afficher la table", nous sélectionnons la table "Factures", nous l'ajoutons et nous

  fermons. Dans la fenêtre "Microsoft Access", nous cliquons sur l'icône de la barre d'outils et, dans la liste déroulante qui s'affiche, nous choisissons "Requête Suppression". Nous introduisons le champ "Date_de_réglement" dans la grille et, sur la ligne "Critères :", nous écrivons :

<#01/01/2002#

l'opérateur < signifiant "antérieur à", et les signes # rappelant que nous opérons

  sur une date.

Attention ! Si nous basculons en mode feuille de données en cliquant sur l'icône

, le logiciel affiche la liste des enregistrements qui seront supprimés, et non l'aspect de la table après suppression comme nous pourrions nous y attendre.

Si nous exécutons la requête (icône ), tous les enregistrements antérieurs au

1er janvier 2002 sont éliminés. Si la table "Factures" est ouverte pendant la   suppression, le mot "Supprimé" apparaît dans chaque champ de chaque enregistrement supprimé. Dès que l'on referme la table, les enregistrements supprimés disparaissent sans laisser de traces.

L'écriture d'un critère de suppression doit tenir compte du type de données du   champ auquel il s'applique. Nous associerons sous peu à ce chapitre des annexes détaillant les règles d'écriture des critères pour les différents cas.

3 - La mise à jour

Les informations contenues dans une BDD peuvent avoir besoin d'une mise à jour. Ainsi, un taux de TVA peut varier, de même que le prix de vente des produits et services de l'entreprise, etc. Mais la mise à jour peut être soumise à un ou plusieurs critères, s'appliquant ou non au champ susceptible d'être modifié.

Par exemple, une prime peut être versée à l'ancienneté, et donc dépendre de la

  date d'embauche du salarié (laquelle figure dans la table du personnel de l'entreprise). Par conséquent, la mise à jour s'applique à une table, et concerne soit une colonne complète, soit seulement certains enregistrements de la colonne (colorés en rose dans la figure ci-dessous).

L'actualité proche nous fournit un excellent exemple mise à jour simple : lors du passage à l'euro, tous les prix contenus dans les base de données durent être convertis de francs en euros à l'aide du fameux coefficient 6,55957. Pour traiter ce cas, créons une table "Produits" contenant trois champs : le code, le nom, et le   prix unitaire HT de chaque produit. Les types de données correspondants sont : NuméroAuto, texte, et numérique (réel, deux décimales, pas de format particulier). Saisissons quelques enregistrements, et créons une copie de la table ainsi remplie.

Créons une requête en mode création. Introduisons la table "Produits", cliquons sur l'icône , et sélectionnons "Requête Mise à jour" dans la liste déroulante.

Introduisons le champ "Prix_unitaire" dans la grille et, sur la ligne "Mise à jour :",   saisissons l'expression représentée dans la figure ci-dessous. Laissons la ligne "Critères :" vide, puisque le passage des francs aux euros concerne tous les enregistrements de la colonne sans exception.

Les bases de données relationnelles

Chapitre 18 : les tables en SQL

1 - Introduction à SQL

Le sigle SQL signifie "Structured Query Language", soit en français "Langage de recherche structuré". SQL est un langage de gestion des bases de données relationnelles que presque tous les SGBD comprennent. Il a été développé par  

IBM dans le courant des années 70, et son nom actuel (il s'appelait initialement SEQUEL) date du début des années 80.

SQL a été normalisé par l'ANSI (American National Standards Institute) et par l'ISO (International Organization for Standardization). Voici les principales étapes

  de ce processus :

première norme ANSI en 1986 ;

première norme ISO (SQL1) en 1987, révisée en 1989 ; deuxième norme ISO (SQL2) en 1992 ;

  troisième norme (SQL3) en cours de rédaction depuis 1999 par l'ANSI et l'ISO,

 après une très longue gestation, et avec beaucoup de retard sur l'événement.

Malgré la normalisation ISO, l'implémentation du SQL par les différents éditeurs   de SGBD comporte des différences plus ou moins marquées concernant :

les détails de la syntaxe ; l'écriture des commandes ; le fonctionnement exact des commandes ;

  l'implémentation de nouveaux types de données (images, animations, vidéos,

 liens hypertexte, etc.).

Bref, il n'y a qu'un seul langage SQL, mais chaque éditeur de SGBD implémente son propre dialecte. Le "dictionnaire" qui permet de passer d'un dialecte à l'autre s'appelle ODBC (Open Data Base Connectivity). Il a été imaginé par Microsoft, et mis sur le marché en 1993.

Contrairement à ce que son nom indique, SQL ne sert pas qu'à écrire des requêtes. C'est un langage complet, qui permet de créer des BDD, des tables, de saisir des données et de les corriger, de créer des vues, des index et des états

(parfois baptisés "rapports", par francisation de l'anglais "reports"). Sauf erreur de

  notre part, il ne permet pas de créer des formulaires, parce qu'il a été conçu à une époque ou l'interface graphique n'existait pas sur ordinateur, et parce qu'un formulaire sans interface graphique n'a guère d'intérêt.

Par contre, dans les SGBD sans interface graphique, le recours à SQL est obligatoire pour toutes les opérations, y compris la création de la BDD, celle des tables, et la saisie des données. La tentation est donc forte, pour un professeur   qui ignore à quels SGBD ses étudiants seront confrontés lorsqu'ils entreront dans la vie active, de faire dans son enseignement une large part à SQL, qui représente l'outil universel de manipulation des données. Ceci dit, l'interface graphique est tellement entrée dans les moeurs, qu'il parait difficile qu'à terme tous les SGBD n'en soient pas dotés. En attendant, nous avons adopté une position mixte, en commençant par l'interface graphique, plus facile à appréhender, et en rajoutant à ce cours quatre chapitres consacrés au langage SQL.

Selon notre habitude, nous utiliserons le SGBD Access comme support pratique de ce cours. A l'occasion, nous comparerons l'implémentation du SQL d'Access à celle d'Oracle. Comme nous ne disposons pas d'une base Oracle (nos moyens

  ne nous le permettent pas ), nous nous inspirerons d'un manuel de formation à la version 8 (l'avant-dernière).

2 - Le langage SQL dans Access

Dans Access, le langage SQL est utilisé par le moteur du SGBD pour traduire en commandes exécutables les instructions que donne l'utilisateur à travers l'interface graphique. Mais l'utilisateur n'a pas accès à ce code, sauf pour laconception des requêtes, où il peut passer facilement du mode graphique au mode SQL et vice versa. Nous utiliserons largement cette possibilité dans les trois chapitres suivants.

Pour les tables, la situation est nettement moins satisfaisante. L'utilisateur qui se sert de l'interface graphique pour créer une table n'a pas accès au code SQL correspondant. Par contre, il dispose d'un éditeur de SQL qui reconnaît les

  principales commandes concernant la création et la modification des tables, et la saisie des données.

Cet éditeur, cependant, ne doit pas faire illusion, car il est loin d'être complet. Il ne permet pas de régler dans le détail les propriétés des champs, comme on peut le faire dans l'interface graphique. Il ne permet pas non plus de créer des listes. Il

  rend donc des services limités. Il présente cependant de l'intérêt dans les deux cas suivants :

l'apprentissage initial du SQL, pour lequel il n'est pas utile d'entrer

 immédiatement dans les moindres détails ; l'automatisation (via les macros) de certaines opérations relatives aux tables. En

  effet, le code SQL que nous allons écrire sera enregistré sous forme de requête

 par le SGBD, et il est très facile de lancer une requête à partir d'une macro.

Évidemment, il est beaucoup plus facile de créer, remplir, modifier, et supprimer une table dans l'interface graphique d'Access qu'en utilisant des commandes

  SQL. Mais tous les SGBD ne sont pas dotés d'une interface graphique, et il est bon de savoir se débrouiller sans elle le cas échéant.

3 - La procédure

Pour gérer les tables en langage SQL dans Access, il nous faut opérer de la manière suivante. Dans la fenêtre "Base de données", nous sélectionnons l'objet

"Requêtes". Nous effectuons un double clic sur "Créer une requête en mode

  création", nous refermons la fenêtre "Afficher la table" sans introduire de table et, dans le menu, nous suivons le chemin suivant :