Sådan skrives Microsoft Access SQL-forespørgsler fra bunden

  • Peter Holmes
  • 0
  • 2000
  • 172
Reklame

Microsoft Access er uden tvivl det mest kraftfulde værktøj i hele Microsoft Office-pakken, men alligevel mystificerer (og til tider skræmmer) Office-magtbrugere. Hvordan er det meningen, at nogen med en stejlere læringskurve end Word eller Excel skal vikle hovedet omkring brugen af ​​dette værktøj? Denne uge vil Bruce Epper se på nogle af de spørgsmål, der er ansporet til dette spørgsmål fra en af ​​vores læsere.

En læser spørger:

Jeg har problemer med at skrive en forespørgsel i Microsoft Access.

Jeg har en database med to produktborde, der indeholder en fælles kolonne med en numerisk produktkode og et tilknyttet produktnavn.

Jeg vil gerne finde ud af, hvilke produkter fra Tabel A der kan findes i Tabel B. Jeg vil tilføje en kolonne med navnet Resultater, der indeholder produktnavnet fra Tabel A, hvis det findes, og produktnavnet fra Tabel B, når det ikke findes i tabel A.

Har du noget råd?

Bruces svar:

Microsoft Access er et databasestyringssystem (DBMS) designet til brug på både Windows og Mac-maskiner. Den bruger Microsofts Jet-databasemotor til databehandling og opbevaring. Det giver også en grafisk grænseflade for brugere, som næsten eliminerer behovet for at forstå SQL (Structured Query Language).

SQL er det kommandosprog, der bruges til at tilføje, slette, opdatere og returnere oplysninger, der er gemt i databasen, samt til at ændre kernedatabasekomponenter såsom tilføjelse, sletning eller ændring af tabeller eller indekser.

Udgangspunktet

Hvis du ikke allerede har nogen fortrolighed med Access eller en anden RDBMS, vil jeg foreslå, at du starter med disse ressourcer, før du fortsætter:

  • Så hvad er en database? Så hvad er en database, alligevel? [MakeUseOf Explains] Så hvad er en database alligevel? [MakeUseOf Explains] For en programmør eller en teknologientusiast er begrebet en database noget, der virkelig kan tages for givet. For mange mennesker er konceptet med en database selv lidt fremmed ... hvor Ryan Dube bruger Excel til at vise det grundlæggende i relationelle databaser.
  • En hurtig guide til at komme i gang med Microsoft Access 2007 En hurtig guide til at komme i gang med Microsoft Access 2007 En hurtig guide til at komme i gang med Microsoft Access 2007, som er en oversigt på højt niveau af Access og de komponenter, der indeholder en Access-database.
  • En hurtig tutorial til tabeller i Microsoft Access 2007 En hurtig tutorial til tabeller i Microsoft Access 2007 En hurtig tutorial til tabeller i Microsoft Access 2007 tager et kig på oprettelse af din første database og tabeller til lagring af dine strukturerede data.
  • En hurtig tutorial om forespørgsler i Microsoft Access 2007 En hurtig tutorial om forespørgsler i Microsoft Access 2007 En hurtig tutorial om forespørgsler i Microsoft Access 2007 ser på midlerne til at returnere specifikke dele af de data, der er gemt i databasetabellerne.

At have en grundlæggende forståelse af de begreber, der er leveret i disse artikler, gør følgende lidt lettere at fordøje.

Databaseforhold og normalisering

Forestil dig, at du driver et firma, der sælger 50 forskellige typer widgets over hele verden. Du har en kundegrundlag på 1.250 og sælg i en gennemsnitlig 10.000 widgets til disse klienter. Du bruger i øjeblikket et enkelt regneark til at spore alle disse salg - effektivt en enkelt databasetabel. Og hvert år tilføjes tusindvis af rækker til dit regneark.

Ovenstående billeder er en del af det regneark-spreadsheet, du bruger. Sig nu, at begge disse klienter køber widgets af dig flere gange om året, så du har langt flere rækker til dem begge.

Hvis Joan Smith gifter sig med Ted Baines og tager sit efternavn, skal hver enkelt række, der indeholder hendes navn, nu ændres. Problemet forværres, hvis du tilfældigvis har to forskellige klienter med navnet 'Joan Smith'. Det er bare blevet meget sværere at holde dine salgsdata konsistente på grund af en ret almindelig begivenhed.

Ved at bruge en database og normalisere dataene, kan vi opdele emner i flere tabeller såsom inventar, klienter og ordrer.

Bare at se på klientdelen i vores eksempel, ville vi fjerne kolonnerne for klientnavn og klientadresse og placere dem i en ny tabel. På billedet ovenfor har jeg også brudt tingene bedre ud for mere granulær adgang til dataene. Den nye tabel indeholder også en kolonne for en primær nøgle (ClientID) - et nummer, der vil blive brugt til at få adgang til hver række i denne tabel.

I den originale tabel, hvor vi fjernede disse data, tilføjede vi en kolonne for en fremmed nøgle (ClientID), som er det, der linker til den rigtige række, der indeholder oplysningerne til netop denne klient.

Når Joan Smith nu skifter navn til Joan Baines, skal ændringen kun foretages en gang i klienttabellen. Hver anden henvisning fra sammenføjede tabeller vil trække det rigtige klientnavn, og en rapport, der ser på, hvad Joan har købt i de sidste 5 år, får alle ordrer under både hendes piger og gifte navne uden at skulle ændre, hvordan rapporten genereres.

Som en ekstra fordel reducerer dette også den samlede mængde forbrugt lager.

Deltag i typer

SQL definerer fem forskellige sammenføjningstyper: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER og CROSS. OUTER-nøgleordet er valgfrit i SQL-sætningen.

Microsoft Access tillader brug af INNER (standard), LEFT OUTER, RIGHT OUTER og CROSS. FULL OUTER understøttes ikke som sådan, men ved at bruge LEFT OUTER, UNION ALL og RIGHT OUTER kan det forfalskes på bekostning af flere CPU-cyklusser og I / O-operationer.

Outputet fra en CROSS-sammenføjning indeholder hver række i det venstre bord sammen med hver række i den højre tabel. Den eneste gang, jeg nogensinde har set en CROSS-sammenkobling, er under belastningstest af databaseservere.

Lad os se på, hvordan de grundlæggende sammenføjninger fungerer, så ændrer vi dem, så de passer til vores behov.

Lad os starte med at oprette to tabeller, ProdA og ProdB, med følgende designegenskaber.

AutoNumber er et automatisk stigende langt heltal, der er tildelt poster, når de føjes til tabellen. Indstillingen Tekst blev ikke ændret, så den accepterer en tekststreng op til 255 tegn lang.

Udfyld dem nu med nogle data.

For at vise forskellene i, hvordan de tre sammenkoblingstyper fungerer, har jeg slettet poster 1, 5 og 8 fra ProdA.

Opret derefter en ny forespørgsel ved at gå til Opret> Forespørgseldesign. Vælg begge tabeller i dialogboksen Vis tabel og Klik på Tilføj, derefter Tæt.

Klik på ProductID i tabel ProdA, træk det til ProductID i tabel ProdB og slip museknappen for at oprette forholdet mellem tabellerne.

Højreklik på linjen mellem tabellerne, der repræsenterer forholdet mellem elementerne og vælg Tilslut egenskaber.

Som standard er sammenføjningstype 1 (INNER) valgt. Valgmulighed 2 er en LEFT OUTER sammenføjning og 3 er en RIGHT OUTER sammenføjning.

Vi ser først på INNER-tilslutningen, så klik på OK for at afvise dialogen.

I forespørgselsdesigneren skal du vælge de felter, vi vil se, fra rullelisterne.

Når vi kører forespørgslen (det røde udråbstegn i båndet), viser det feltet Produktnavn fra begge tabeller med værdien fra tabel ProdA i den første kolonne og ProdB i den anden.

Bemærk, at resultaterne kun viser værdier, hvor ProductID er ens i begge tabeller. Selvom der er en post for ProductID = 1 i tabel ProdB, vises den ikke i resultaterne, da ProductID = 1 ikke findes i tabel ProdA. Det samme gælder ProductID = 11. Det findes i tabel ProdA, men ikke i tabel ProdB.

Ved at bruge knappen Vis på båndet og skifte til SQL View, kan du se SQL-forespørgslen genereret af designeren, der blev brugt til at få disse resultater.

VÆLG ProdA.ProductName, ProdB.ProductName FRA ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Når du vender tilbage til Designvisning, skal du ændre sammenkoblingstypen til 2 (VENSTRE UDRE). Kør forespørgslen for at se resultaterne.

Som du kan se, er hver post i tabel ProdA repræsenteret i resultaterne, mens kun de i ProdB, der har en matchende ProductID-post i tabel ProdB, vises i resultaterne.

Det tomme rum i kolonnen ProdB.ProductName er en særlig værdi (NULL), da der ikke er en matchende værdi i tabel ProdB. Dette vil vise sig vigtigt senere.

VÆLG ProdA.ProductName, ProdB.ProductName FRA ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Prøv det samme med den tredje type sammenføjning (RIGHT OUTER).

Resultaterne viser alt fra tabel ProdB, mens det viser tomme (kendt som NULL) -værdier, hvor ProdA-tabellen ikke har en matchende værdi. Indtil videre bringer dette os tættest på de ønskede resultater i vores læsers spørgsmål.

VÆLG ProdA.ProductName, ProdB.ProductName FRA ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Brug af funktioner i en forespørgsel

Resultaterne af en funktion kan også returneres som en del af en forespørgsel. Vi ønsker, at en ny kolonne med navnet 'Resultater' skal vises i vores resultatsæt. Dets værdi vil være indholdet i kolonnen Produktnavn i tabel ProdA, hvis ProdA har en værdi (den er ikke NULL), ellers skal den tages fra tabel ProdB.

Funktionen Immediate IF (IIF) kan bruges til at generere dette resultat. Funktionen tager tre parametre. Den første er en betingelse, der skal evalueres til en sand eller falsk værdi. Den anden parameter er den værdi, der skal returneres, hvis betingelsen er sand, og den tredje parameter er den værdi, der skal returneres, hvis betingelsen er falsk.

Den fulde funktionskonstruktion til vores situation ser sådan ud:

IIF (ProdA.ProductID er null, ProdB.ProductName, ProdA.ProductName)

Bemærk, at betingelsesparameteren ikke tjekker for lighed. En Null-værdi i en database har ikke en værdi, der kan sammenlignes med nogen anden værdi, inklusive en anden Null. Med andre ord er Null ikke lig med Null. Nogensinde. For at komme forbi dette, kontrollerer vi i stedet værdien ved hjælp af 'Is' nøgleordet.

Vi kunne også have brugt 'Is Not Null' og ændret rækkefølgen af ​​True and False parametre for at få det samme resultat.

Når du lægger dette i Query Designer, skal du indtaste hele funktionen i feltet Field:. For at få det til at oprette kolonnen 'Resultater', skal du bruge et alias. For at gøre dette, forord funktionen med 'Resultater:' som det ses på følgende skærmbillede.

Den ækvivalente SQL-kode til at gøre dette ville være:

VÆLG ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID er Nul, ProdB.ProductName, ProdA.ProductName) AS resultater FRA ProdA RETTIGE DET BLIVE MED ProdB PÅ ProdA.ProductID = ProdB.ProductID;

Når vi kører denne forespørgsel, giver den disse resultater.

Her ser vi for hver post, hvor tabel ProdA har en værdi, denne værdi afspejles i kolonnen Resultater. Hvis der ikke er en post i ProdA-tabellen, vises posten fra ProdB i Resultater, hvilket er nøjagtigt, hvad vores læser bad om.

For flere ressourcer til at lære Microsoft Access, se Joel Lee's Sådan lærer du Microsoft Access: 5 gratis online ressourcer Sådan lærer du Microsoft Access: 5 gratis online ressourcer Sådan lærer du Microsoft Access: 5 gratis online ressourcer Skal du administrere et stort beløb af data? Du skal undersøge Microsoft Access. Vores gratis studieressourcer kan hjælpe dig med at komme i gang og lære evnerne til mere komplekse projekter. .




Endnu ingen kommentarer

Om moderne teknologi, enkel og overkommelig.
Din guide i en verden af moderne teknologi. Lær hvordan du bruger de teknologier og gadgets, der omgiver os hver dag, og lær, hvordan du finder interessante ting på Internettet.