Comment écrire des requêtes sql microsoft access à partir de zéro

Microsoft Access est sans doute l`outil le plus puissant dans l`ensemble de la suite Microsoft Office, mais il mystifie (et parfois fait peur) les utilisateurs de puissance de bureau. Avec une courbe d`apprentissage plus raide que Word ou Excel, comment quelqu`un censé envelopper la tête autour de l`utilisation de cet outil? Cette semaine, Bruce Epper se penchera sur certaines des questions sous l`impulsion de cette question d`un de nos lecteurs.

Un lecteur demande:

Je vais avoir du mal à écrire une requête dans Microsoft Access.

J`ai une base de données avec deux tables de produits contenant une colonne commune avec un code de produit numérique et un nom de produit associé.

Je veux savoir quels produits du tableau A se trouvent dans le tableau B. Je veux ajouter une colonne nommée Résultats qui contient le nom du produit du tableau A, si elle existe, et le nom du produit du tableau B quand il n`existe pas dans le tableau A.

Avez-vous des conseils?

Réponse de Bruce:

Microsoft Access est un système de gestion de base de données (SGBD) conçu pour être utilisé sur les deux machines Windows et Mac. Il utilise le moteur de base de données Jet de Microsoft pour le traitement et le stockage des données. Il fournit également une interface graphique pour les utilisateurs, ce qui élimine presque la nécessité de comprendre Structured Query Language (SQL).

SQL est le langage de commande utilisé pour ajouter, supprimer, mettre à jour, et le retour des informations stockées dans la base de données ainsi que de modifier les composants de base de données de base telles que l`ajout, la suppression ou la modification des tables ou des indices.

Point de départ

Si vous ne possédez pas déjà une certaine familiarité avec Access ou autre SGBDR, je vous suggère de commencer avec ces ressources avant de poursuivre:

• Alors, quelle est une base de données? où Ryan Dube utilise Excel pour montrer les bases de données relationnelles.
• Un guide rapide pour commencer à utiliser Microsoft Access 2007 qui est une vue d`ensemble de haut niveau de l`accès et les composants qui comprennent une base de données d`accès.
• Un tutoriel rapide aux tables dans Microsoft Access 2007 jette un regard sur la création de votre première base de données et des tables pour stocker vos données structurées.
• Un tutoriel rapide sur les requêtes dans Microsoft Access 2007 examine les moyens pour retourner des parties spécifiques des données stockées dans les tables de base de données.

Avoir une compréhension de base des concepts présentés dans ces articles fera ce qui suit un peu plus facile à digérer.

Relations avec les bases de données et Normalization

Imaginez que vous exécutez une entreprise de vente de 50 différents types de widgets partout dans le monde. Vous avez une base de clients de 1,250 et un mois moyen de vendre 10.000 widgets à ces clients. Vous utilisez actuellement un tableur pour suivre l`ensemble de ces ventes - effectivement une table de base de données unique. Et chaque année, ajoute des milliers de lignes à votre feuille de calcul.

Les images ci-dessus font partie de la feuille de suivi des commandes que vous utilisez. Maintenant, supposons que ces deux clients acheter des widgets de vous plusieurs fois par an afin que vous avez beaucoup plus de lignes pour les deux.

Si Joan Smith se marie avec Ted Baines et prend son nom de famille, chaque ligne unique qui contient son nom doit maintenant être changé. Le problème est aggravé si vous arrive d`avoir deux clients différents avec le nom « Joan Smith ». Il est juste devenu beaucoup plus difficile de garder vos données de ventes cohérentes en raison d`un événement assez courant.

En utilisant une base de données et la normalisation des données, nous pouvons séparer les éléments en plusieurs tableaux tels que les stocks, les clients et les commandes.

Il suffit de regarder la partie client de notre exemple, nous supprimerions les colonnes pour le nom du client et adresse du client et de les mettre dans une nouvelle table. Dans l`image ci-dessus, j`ai aussi des choses mieux éclaté pour un accès plus granulaire aux données. La nouvelle table contient également une colonne pour une clé primaire (ClientID) - un nombre qui sera utilisé pour accéder à chaque ligne de ce tableau.

Dans le tableau d`origine où nous avons supprimé ces données, nous ajouterons une colonne pour une clé étrangère (ClientID) qui est ce qui lie à la ligne appropriée contenant les informations pour ce client particulier.

Maintenant, quand Joan Smith change son nom à Joan Baines, le changement ne doit être fait une fois dans la table des clients. Toutes les autres références de tables jointes va tirer le nom du client approprié et un rapport qui se penche sur ce que Joan a acheté pour les 5 dernières années permettront à tous les ordres relevant à la fois la jeune fille et les mariés sans avoir à changer la façon dont le rapport est généré .

Comme un avantage supplémentaire, ce qui réduit également la quantité totale de stockage consommée.

Video: 4 - Nos premières requêtes SQL

join

SQL définit cinq différents types de jointures: INNER, LEFT OUTER, RIGHT OUTER FULL OUTER et CROSS. Le mot-clé OUTER est facultatif dans l`instruction SQL.

Microsoft Access permet l`utilisation de INNER (par défaut), LEFT OUTER, RIGHT OUTER et CROSS. FULL OUTER est pas pris en charge en tant que telle, mais en utilisant LEFT OUTER, UNION ALL et OUTER, il peut être falsifiée au coût de plus de cycles CPU et les opérations d`E / S.

La sortie d`une jointure croisée contient toutes les lignes de la table de gauche associé à chaque ligne de la table de droite. La seule fois que je l`ai jamais vu une CROSS JOIN est utilisé lors des tests de charge des serveurs de base de données.

Jetons un coup d`oeil à la façon dont la base rejoint le travail, nous allons les modifier en fonction de nos besoins.

Commençons par la création de deux tables, prodĂ et ProdB, avec les propriétés de conception suivantes.

Le NuméroAuto est un entier long automatiquement affecté à incrémenter entrées comme ils sont ajoutés à la table. L`option de texte n`a pas été modifié, il acceptera une chaîne de texte jusqu`à 255 caractères.

Maintenant, les remplir avec des données.

Pour montrer les différences dans la façon dont les 3 types de jointure de travail, j`ai supprimé les entrées 1, 5 et 8 de prodĂ.

Ensuite, créez une nouvelle requête en allant Créer gt; Création de requête. Sélectionnez les deux tables de la boîte de dialogue Afficher la table et cliquez sur Ajouter, puis Fermer.

Cliquez sur ProductID dans le tableau prodĂ, faites-le glisser ProductID dans le tableau ProdB et relâchez le bouton de la souris pour créer la relation entre les tables.

Faites un clic droit sur la ligne entre les tables représentant les relations entre les éléments et Inscrivez-vous sélectionnez Propriétés.

Par défaut, type de jointure 1 (INNER) est sélectionné. L`option 2 est une LEFT OUTER JOIN et 3 est un RIGHT OUTER JOIN.

Nous examinerons la jointure d`abord, donc cliquez sur OK pour fermer la boîte de dialogue.

Dans le concepteur de requêtes, sélectionnez les champs que nous voulons voir dans les listes déroulantes.

Lorsque nous courons la requête (le point d`exclamation rouge dans le ruban), il affichera le champ ProductName des deux tables avec la valeur de la table prodĂ dans la première colonne et ProdB dans le second.

Notez que les résultats montrent que les valeurs où ProductID est égale dans les deux tables. Même si il y a une entrée pour ProductID = 1 dans le tableau ProdB, il ne se présente pas dans les résultats depuis ProductID = 1 n`existe pas dans le tableau prodĂ. De même pour ProductID = 11. Il existe dans le tableau prodĂ mais pas dans le tableau ProdB.

En utilisant le bouton Affichage sur le ruban et le passage à la vue SQL, vous pouvez voir la requête SQL généré par le concepteur utilisé pour obtenir ces résultats.

SÉLECTIONNER prodĂ.ProductName, ProdB.ProductName DE prodĂ INTERNE JOINDRE ProdB SUR prodĂ.ProductID = ProdB.ProductID-

Pour en revenir à la vue de conception, changer le type de jointure à 2 (LEFT OUTER). Exécutez la requête pour voir les résultats.

Video: Formation base de données #09 Requêtes SQL avancées 2

Comme vous pouvez le voir, chaque entrée dans le tableau prodĂ est représenté dans les résultats alors que seuls ceux de ProdB qui ont une entrée ProductID correspondant dans le tableau ProdB apparaissent dans les résultats.

L`espace vide dans la colonne ProdB.ProductName est une valeur spéciale (NULL) car il n`y a pas une valeur correspondante dans le tableau ProdB. Cela prouvera important plus tard.

SÉLECTIONNER prodĂ.ProductName, ProdB.ProductName DE prodĂ LA GAUCHE JOINDRE ProdB SUR prodĂ.ProductID = ProdB.ProductID-

Essayez la même chose avec le troisième type de jointure (OUTER).

Les résultats montrent tout de la table ProdB alors qu`il montre des valeurs vides (connu sous le nom NULL) où la table prodĂ ne dispose pas d`une valeur correspondante. Jusqu`à présent, cela nous rapproche le plus les résultats escomptés dans la question de notre lecteur.

SÉLECTIONNER prodĂ.ProductName, ProdB.ProductName DE prodĂ DROITE JOINDRE ProdB SUR prodĂ.ProductID = ProdB.ProductID-

Utilisation des fonctions dans une requête

Les résultats d`une fonction peuvent également être retournés dans le cadre d`une requête. Nous voulons une nouvelle colonne intitulée « Résultats à apparaître dans notre jeu de résultats. Sa valeur sera le contenu de la colonne ProductName de la table prodĂ si prodĂ a une valeur (il est pas NULL), sinon il doit être pris de la table ProdB.

La fonction immédiate IF (IIF) peut être utilisé pour générer ce résultat. La fonction prend trois paramètres. La première est une condition qui doit évaluer à une valeur Vrai ou faux. Le deuxième paramètre est la valeur à renvoyer si la condition est vraie, et le troisième paramètre est la valeur à renvoyer si la condition est fausse.

La construction pleine fonction pour la situation ressemble à ceci:

IIF(prodĂ.ProductID Est Nul, ProdB.ProductName,prodĂ.ProductName)

Notez que le paramètre de condition ne vérifie pas l`égalité. Une valeur nulle dans une base de données ne dispose pas d`une valeur qui peut être comparée à une autre valeur, y compris une autre Null. En d`autres termes, nul ne correspond pas à nul. Déjà. Pour passer, nous vérifions plutôt la valeur à l`aide du mot-clé « Is`.

Nous aurions pu aussi utiliser « est pas nul » et changé l`ordre des paramètres Vrai et Faux pour obtenir le même résultat.

Lorsque vous mettez cela dans le concepteur de requêtes, vous devez saisir l`ensemble de la fonction dans le champ: entrée. Pour l`obtenir pour créer la colonne « Résultats, vous devez utiliser un alias. Pour ce faire, préface la fonction avec « Résultats: » comme on le voit dans la capture d`écran suivante.

L`équivalent code SQL pour ce faire serait la suivante:

SÉLECTIONNER prodĂ.ProductName, ProdB.ProductName, IIF(prodĂ.ProductID Est Nul,ProdB.ProductName,prodĂ.ProductName) COMME Résultats DE prodĂ DROITE JOINDRE ProdB SUR prodĂ.ProductID = ProdB.ProductID-

Maintenant, quand nous courons cette requête, il produira ces résultats.

Ici, nous voyons pour chaque entrée où la table prodĂ a une valeur, cette valeur se reflète dans la colonne des résultats. S`il n`y a pas une entrée dans la table prodĂ, l`entrée de ProdB apparaît dans les résultats qui est exactement ce que notre lecteur a demandé.

Pour plus de ressources pour l`apprentissage de Microsoft Access, consultez Joel Lee Comment savoir Microsoft Access: 5 Ressources en ligne gratuites.Comment savoir Microsoft Access: 5 Ressources en ligne gratuitesComment savoir Microsoft Access: 5 Ressources en ligne gratuitesAvez-vous de gérer une grande quantité de données? Vous devriez regarder dans Microsoft Access. Nos ressources d`étude gratuits peuvent vous aider à démarrer et d`apprendre les compétences pour des projets plus complexes.Lire la suite