Apprendre le langage SQL pour extraire des données

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Présentation des requêtes SQL sous Access

Dans cette formation Access, nous proposons de démystifier la syntaxe des requêtes Sql. Ces dernières sont précieuses. Nous l'avons constaté au travers de nombreuses formations. Elles permettent de manipuler dynamiquement les données, avec une grande puissance. SQL signifie Structured Query Language.

Source et présentation de la problématique
Pour réaliser les manipulations, nous avons besoin de données à torturer. Cette source est proposée au téléchargement.

• Télécharger le fichier requetes-sql.accdb en cliquant sur son lien,

Le téléchargement conduit à la base de données Access, nommée : requetes-sql.accdb.

• Double cliquer sur le fichier pour ouvrir la base de données dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Comme l'illustre le volet des objets Access sur la gauche, cette base de données propose deux objets. Le premier est la table societes. C'est elle qui offre les informations à manipuler. Le second est le formulaire destiné à recevoir nos essais.

• Dans le volet de gauche, double cliquer sur le formulaire Selection_Sql pour l'exécuter,

Ce formulaire est doté d'un sous-formulaire. Ce dernier est lié à la table societes. Par défaut, il restitue donc l'ensemble de ses enregistrements. Ils sont au nombre de 1246 comme l'indique la petite barre de navigation, en bas de la feuille de données. L'objectif consiste à dompter ces informations en les filtrant, en les classant, en leur appliquant des contraintes sélectives par la syntaxe Sql.



C'est pourquoi, une zone de saisie est proposée juste au-dessus du sous-formulaire. Un clic sur le bouton Valider doit exécuter la requête sur la base de données. Les résultats filtrés doivent automatiquement se mettre à jour dans le sous formulaire. Il s'agit d'un bon moyen de comprendre la syntaxe Sql en consultant instantanément son impact sur la source de données.

Source de données dynamique d'un sous formulaire
Pour que cette application soit fonctionnelle, nous devons ordonner au sous formulaire de réagir en fonction de la syntaxe SQL commandée par l'utilisateur. Ces requêtes émanant de la zone de saisie, doivent lui servir de source de données.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Puis, dans la liste, choisir Mode Création,

Pour les manipulations à venir, la feuille de Propriétés doit être affichée. Elle est généralement placée sur la droite de l'espace de travail. Si elle n'est pas visible :

• Cliquer sur l'onglet Création en haut de la fenêtre Access pour activer son ruban,
• Dans la section Outils, cliquer sur le bouton bascule nommé Feuille de Propriétés.

Cette feuille de propriétés est contextuelle. Elle synthétise les réglages opérés pour le contrôle sélectionné sur le formulaire.

• Cliquer sur la zone de texte pour la sélectionner,

Comme le mentionne la feuille de Propriétés, ce contrôle est nommé requete. Ce nom d'objet est important. Nous devrons y faire référence par le code VBA pour prélever la syntaxe SQL saisie par l'utilisateur. Puis, nous établirons le lien entre le sous formulaire et le résultat de cette extraction commandée.

• Cliquer sur le sous-formulaire pour le sélectionner,

Ce contrôle est nommé Resultats. C'est par ce nom d'objet que nous pourrons lui attribuer cette source de données dynamique, soit la syntaxe de la requête SQL.

• Cliquer enfin sur le bouton Valider pour le sélectionner,
• Activer l'onglet Evènement de sa feuille de propriétés,
• Cliquer sur le petit bouton de son évènement Au clic,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur de code VBA Access, entre les bornes de la procédure Valider_Click. Les instructions que nous y ajouterons se déclencheront donc au clic sur le bouton Valider.

Nous souhaitons que les données se réorganisent sur cet évènement, mais aussi à validation de la saisie dans la zone nommée requete. De fait, deux évènements doivent déclencher le même code. C'est pourquoi, nous proposons de créer une procédure indépendante, qui sera appelée par les deux procédures évènementielles.

• Sous la procédure évènementielle Valider_Click, créer la procédure generer comme suit :

Private Sub generer()
On Error Resume Next
Dim la_requete As String

la_requete = requete.Value
Me.Resultats.Form.RecordSource = la_requete

requete.SetFocus

End Sub

Nous déclarons la variable la_requete comme une chaîne de caractères (As String). Nous pouvons ainsi l'affecter au contenu de la zone de texte du formulaire, grâce à sa propriété Value.

L'instruction qui suit est particulière. Le sous formulaire est considéré comme un sous objet du formulaire. Il faut donc descendre jusqu'à lui en partant du formulaire. Nous pouvons ainsi le désigner et piloter ses propriétés. L'objet Me que nous avons déjà abordé, désigne l'objet actif, soit le formulaire. Resultats est le sous formulaire qui lui appartient. Form est sa propriété qui désigne l'objet dans sa globalité. Ainsi, nous pouvons appeler la propriété RecordSource pour le charger de la nouvelle requête, sous forme de chaîne de caractères. Nous l'affectons donc à la saisie de l'utilisateur, mémorisée dans la variable la_requete. La première instruction (On Error Resume Next) est destinée à ignorer les erreurs si d'aventure nous commettions des fautes de syntaxe dans nos essais.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + F11),
• Sélectionner la zone de saisie nommée requete,
• Cliquer sur le bouton associé à son évènement Après MAJ,
• Dans la boîte de dialogue, choisir Générateur de code et valider par Ok,

Nous basculons de nouveau dans l'éditeur de code VBA Access, mais cette fois, entre les bornes de la procédure évènementielle requete_AfterUpdate. Cette dernière ainsi que la procédure Valider_Click, doivent déclencher l'exécution du code de la procédure generer.

• Entre les bornes de chacune, ajouter l'appel de la procédure generer, comme suit :

Private Sub requete_AfterUpdate()

generer

End Sub

Private Sub Valider_Click()

generer

End Sub

• Enregistrer les modifications et basculer sur le formulaire Access,

Nous en avons terminé avec le code VBA. Toute notre attention doit désormais se focaliser sur la syntaxe des requêtes Sql. Nous proposons l'apprentissage au travers de requêtes simples.

Requêtes Sql de Sélection
C'est le mot clé SELECT qui permet d'initier une requête sélection. Il est toujours de bon ton d'inscrire les instructions SQL en majuscules. C'est la norme. De plus, cette méthode simplifie le déchiffrage d'une syntaxe. Ce mot clé doit être suivi des noms des champs énumérés. Si une faute est commise dans l'un des noms, une erreur est générée et la requête ne produit aucun résultat. De la rigueur et de la minutie sont donc de mise. Cette énumération se réalise en séparant les champs les uns des autres par une virgule. Puis, doit suivre le nom de la table précédé de la clause FROM. Elle signifie littéralement : A partir de la table. Une requête peut agir sur n'importe quelle table en effet et même sur plusieurs tables à la fois. Enfin, il est toujours préférable de ponctuer l'expression globale par un point-virgule. La syntaxe SQL d'une requête sélection classique est donc la suivante :

SELECT nom_champ1, nom_champ2, ..., nom_champN FROM nom_table;

• Enfoncer la touche F5 du clavier pour exécuter le formulaire,
• Dans la zone de saisie, taper la requête suivante :

SELECT societes_nom, societes_departement FROM societes;

• Puis, cliquer sur le bouton Valider pour déclencher le code VBA actualisant les données,

Dans l'énumération, nous ne listons que deux champs. Nous sélectionnons donc tous les enregistrements (SELECT) à partir de la table societes (FROM) et ne restituons l'information que sur ces deux colonnes. Dans la barre de navigation, le résultat retourné par la requête est toujours de 1246 enregistrements. Nous avons restreint le niveau de détail en colonne, mais n'avons en effet pas émis de contrainte pour un résultat sélectif.

Bien sûr, nous pourrions poursuivre l'énumération des noms pour obtenir le niveau d'information sur chacun des champs. Mais lorsque les colonnes d'une table sont nombreuses, cette méthode est fastidieuse, risquée en termes d'erreurs de saisie et alourdit la syntaxe. Le symbole étoile (*) remplace l'énumération pour désigner tous les champs d'une table.

• Dans la requête, remplacer l'énumération (societes_nom, societes_departement) par le symbole de l'étoile,
• Puis, cliquer sur le bouton Valider,

SELECT * FROM societes;

Nous sélectionnons ainsi tous les enregistrements et pour chacun d'eux, tous les champs. Nous obtenons le résultat de départ. En effet, cette requête récupère tous les enregistrements de la table societes. Or cette table est la source par défaut du sous formulaire.

Les clauses Sql sont nombreuses. L'une d'entre elles offre la possibilité d'éliminer les doublons. Ces idées de sorties sont référencées par activité, ville et département. Plusieurs proposent la même activité et sont situées dans le même département et la même ville, impliquant des répétitions. Pour chacun, nous souhaiterions dresser la liste des valeurs uniques. Un cas pratique concret consisterait à charger des listes déroulantes, offrant à l'utilisateur la possibilité d'effectuer des choix stricts. Cette clause se nomme DISTINCT. Elle doit précéder le nom du champ à purger de ses doublons.

• Tester désormais la requête suivante :

SELECT DISTINCT societes_departement FROM societes;

12 enregistrements seulement résultent de cette requête. Nous obtenons bien l'énumération des valeurs uniques pour le champ societes_departement.



De la même façon, il suffit de changer le nom du champ, derrière la clause DISTINCT pour obtenir la liste de ses valeurs uniques :

SELECT DISTINCT societes_activite FROM societes;

Cette requête conduit à 24 enregistrements soit 24 activités différentes.

SELECT DISTINCT societes_ville FROM societes;

Cette requête produit 475 enregistrements soit 475 villes différentes.

Toujours dans la recherche de ces syntaxes simplifiées pour produire des extractions puissantes, SQL propose la clause TOP. Cette clause permet de sélectionner un nombre défini d'enregistrements, en partant du premier (TOP), pour les champs souhaités, à énumérer. Ses applications sont nombreuses. Sur un site Web qui propose un système de pagination, il convient de limiter le nombre d'enregistrements restitués.

• Tester la requête SQL suivante :

SELECT TOP 10 * FROM societes;



Pour tous les champs de la table (*), nous sélectionnons seulement les 10 premiers (TOP) enregistrements. Et comme vous le constatez, l'extraction des données est fidèle. Bien sûr, nous pourrions être plus sélectif dans le détail des informations à restituer, en précisant les champs dans l'énumération :

SELECT TOP 10 societes_nom, societes_activite FROM societes;

Pour les autres gestionnaires de bases de données comme SQL Server et MySql, il faut utiliser la clause LIMIT et non la clause TOP. En revanche, cette clause intervient en toute fin de syntaxe.

Trier les données sélectionnées
Question d'organisation, il est important de pouvoir restituer les données triées dans un ordre maîtrisé. Pour établir des classements par exemple, nous réaliserons un tri décroissant sur le score. En SQL, c'est la clause ORDER BY qui enclenche les tris de données. Elle doit intervenir en toute fin de syntaxe. Mais c'est aussi une manière de regrouper et d'ordonner l'information. Il est en effet possible d'organiser plusieurs niveaux de tris hiérarchiques. Il suffit pour cela, d'énumérer les champs par ordre de priorité. L'organisation est croissante par défaut. Mais avec l'attribut DESC, nous pouvons définir un tri décroissant.

Nous proposons par exemple d'afficher toutes les idées de sorties regroupées par activité, par département et par ville. En d'autres termes, nous sélectionnons tous les enregistrements que nous trions dans l'ordre sur l'activité, puis le département et la ville.

• Tester la requête suivante :

SELECT * FROM societes ORDER BY societes_activite, societes_departement, societes_ville;

Les 1246 enregistrements sont effectivement restitués, mais ils sont regroupés comme nous l'avons défini. Et d'ailleurs, cette organisation peut ne pas sembler très cohérente. Des idées de sorties de différents départements sont mélangées. Pour pallier ce défaut, il suffit de changer le niveau de tri, en passant le département devant l'activité.

Nous pourrions donc tester la requête suivante :

SELECT * FROM societes ORDER BY societes_departement, societes_activite, societes_ville;



Les idées de sorties sont effectivement triées dans l'ordre croissant en priorité sur le département. Et pour chaque département, sont alors listées toutes les activités croissantes dans un ordre établi lui-même croissant sur la ville. Cette clause ORDER BY est très souple et précieuse. En modifiant l'énumération ou l'attribut de tri, elle peut livrer des organisations complètement différentes.

Nous souhaiterions par exemple débuter l'énumération par les départements les plus grands. Il suffit donc d'organiser un tri décroissant sur le champ societes_departement.

• Tester la requête SQL suivante :

SELECT * FROM societes ORDER BY societes_departement DESC, societes_activite, societes_ville;

Les enregistrements sont en effet restitués dans l'ordre inverse des départements, tout en conservant le tri croissant sur l'activité puis la ville.

A titre de mise en application, nous pourrions d'ores et déjà exploiter ce que nous avons appris. L'enjeu consisterait par exemple à sélectionner seulement les 5 premiers départements, purgés des doublons, triés dans l'ordre décroissant. Il s'agit donc d'exploiter les clauses DISTINCT, TOP et ORDER BY réunies. En d'autres termes, nous proposons de sélectionner les 5 derniers départements uniques.

• Tester la requête SQL suivante :

SELECT DISTINCT TOP 5 societes_departement FROM societes ORDER BY societes_departement DESC;



Le résultat est intéressant. Nous obtenons bien la liste des 5 derniers départements référencés, sans doublons.

Le langage SQL offre aussi la possibilité d'extraire des données aléatoirement. Là encore les applicatifs sont nombreux. Nous avions exploité cette technique pour l'application d'évaluation en proposant des questions générées aléatoirement. Mais cette fois, il s'agit d'une fonction et non d'une clause. Et qui dit fonction, dit qu'elle doit être appliquée sur un champ à lui passer en paramètre. Cette fonction se nomme Rnd. Il s'agit de l'abréviation de Random. Elle doit être incluse dans un tri, soit dans la clause ORDER BY. Finalement, ce principe est tout à fait logique puisqu'il s'agit d'une question d'organisation. Nous proposons donc de restituer toutes les idées de sorties organisées aléatoirement.

• Tester la requête SQL suivante :

SELECT * FROM societes ORDER BY RND(societes_id);

Fort logiquement, cette requête ne produit jamais le même résultat. A chaque clic sur le bouton Valider, les enregistrements sont complétement réorganisés.



Bien entendu, tout ce que nous avons appris jusque-là reste vrai. Par exemple, nous pourrions choisir de restituer seulement les cent premiers noms des idées de sorties, triés dans un ordre aléatoire. Pour cela, nous devons exécuter la requête SQL suivante :

SELECT TOP 100 societes_nom FROM societes ORDER BY RND(societes_id);

Sélections selon des critères
Nous abordons désormais une clause incontournable dans la syntaxe. Il s'agit de la clause WHERE. C'est elle qui permet d'extraire des données en émettant des conditions. Ces dernières peuvent d'ailleurs être recoupées dans l'énumération grâce au mot clé AND.

La clause WHERE intervient nécessairement après la clause FROM et avant la clause ORDER BY :

SELECT liste_des_champs FROM nom_table WHERE conditions ORDER BY champs_pour_tris;

Nous souhaitons par exemple extraire uniquement les idées de sorties pour le département de la Drôme. Le critère doit donc être posé sur le champ societes_departement.

• Tester la requête SQL suivante :

SELECT * FROM societes WHERE societes_departement = '26-Drome' ORDER BY societes_nom;



Grâce à l'égalité ordonnée sur le champ societes_departement, nous filtrons les idées de sorties pour la Drôme uniquement. Nous restituons tous les enregistrements concordants, triés dans l'ordre croissant sur le nom. Le critère est mentionné entre guillemets car il s'agit d'un champ textuel.

Nous souhaitons désormais affiner les résultats en ne récoltant que les activités Loisir/Sport pour ce même département. Cela signifie que nous devons recouper deux critères. Et comme nous l'avons évoqué, c'est le mot clé AND qui permet de croiser des conditions. Grâce à lui, seuls les enregistrements qui satisfont à la fois les deux contraintes, sont extraits.

• Tester la requête SQL suivante :

SELECT * FROM societes WHERE societes_departement='26-Drome' AND societes_activite='Loisir/sport' ORDER BY societes_nom;

Seules 10 idées de sorties subsistent. Voilà comment sur un site Web notamment, il est possible de répondre rapidement à une demande spécifique de l'internaute.

Le AND est exclusif. Tous les enregistrements qui ne vérifient pas les deux conditions à la fois sont exclus. Bien sûr, autant de critères que souhaités peuvent être énoncés et recoupés. Il suffit de poursuivre l'énumération sur les différents champs, séparés du mot clé AND. Mais il existe aussi le mot clé OR. Il permet dans l'énumération d'extraire les enregistrements qui correspondent à l'une ou l'autre condition.

Par exemple, nous souhaiterions extraire toutes les idées de sorties de Loisir/Sport pour les départements de la Drôme et de l'Ardèche. Le critère commun est l'activité. Il doit être recoupé (AND). Le département en revanche peut être soit l'un, soit l'autre.

• Modifier la syntaxe de la requête SQL comme suit :

SELECT * FROM societes WHERE societes_activite='Loisir/sport' AND (societes_departement='26-Drome' OR societes_departement='07-Ardèche') ORDER BY societes_nom;



Seuls 19 enregistrements subsistent. Les parenthèses sont très importantes dans la syntaxe, comme la factorisation en mathématiques. Si nous ne les avions pas posées, nous aurions obtenus uniquement les loisir/sport pour la Drôme et toutes les idées pour l'Ardèche, quelle que soit l'activité.

Comme vous le remarquez donc au fil de l'apprentissage, il est assez aisé d'imbriquer les différentes clauses, afin de produire des résultats d'extraction affinés et maîtrisés. Et pourtant, dans cette formation, nous n'abordons que la couche superficielle du langage SQL. Les possibilités sont immenses et la puissance redoutable.

Bien sûr les opérateurs sont multiples et les possibilités sans fin. Sur les champs numériques, nous pouvons exploiter les symboles supérieur (>) et inférieur (<), pour extraire les données correspondant à des critères numériques. L'opération Compris entre consistera en une combinaison de ces opérateurs et de la clause AND. Nous pourrions ainsi extraire tous les enregistrements pour lesquels la valeur est à la fois supérieure à tel nombre et inférieure à tel autre nombre. Notre base de données n'est pas dédiée. Le seul champ numérique est celui de la clé primaire, nommé societes_id. Nous proposons néanmoins de démontrer cette syntaxe. Nous souhaitons extraire tous les enregistrements pour lesquels la clé primaire est située entre la valeur 400 et la valeur 420.

• Tester la requête SQL suivante :

SELECT * FROM societes WHERE societes_id>=400 AND societes_id<=420;

Nous extrayons en effet toutes les idées de sorties pour lesquelles la clé primaire est située entre les bornes imposées par les opérateurs numériques et la clause AND.

Pour terminer, il est possible de réaliser des extractions approximatives grâce à l'opérateur LIKE. Sa désignation est explicite. Il permet d'extraire les enregistrements dont le contenu se rapproche ou ressemble au critère spécifié. Il doit le contenir par exemple. Il peut être combiné avec le symbole de l'étoile (*). C'est ainsi que nous produisons des extractions d'enregistrements contenant le mot clé (*mot-cle-*).



Tous départements confondus, nous souhaiterions par exemple extraire tous les musées. Cependant, aucune activité ne les catégorise explicitement. L'astuce consiste donc à effectuer la recherche dans le champ societes_nom, pour les enregistrements incluant ce terme.

• Tester la requête SQL suivante :

SELECT * FROM societes WHERE societes_nom LIKE '*Musée*';

Toutes les idées de sorties dont le nom contient le terme Musée sont effectivement extraites. L'étoile en préfixe et en suffixe permet d'exprimer l'opération : Contient.



Ces manipulations ont consisté à démystifier la syntaxe SQL pour les opérations les plus simples, mais aussi les plus courantes. Dans un prochain volet, nous aborderons les requêtes action ainsi que les requêtes multi-tables.