Kako napisati poizvedbe Microsoft Access SQL iz nič

Kako napisati poizvedbe Microsoft Access SQL iz nič

Microsoft Access je verjetno najmočnejše orodje v celotnem paketu Microsoft Office, vendar skriva (in včasih prestraši) uporabnike Officea. Kako naj bi kdo s strmejšo krivuljo učenja kot Word ali Excel zavil z glavo okoli uporabe tega orodja? Ta teden bo Bruce Epper obravnaval nekatera vprašanja, ki jih je to vprašanje sprožilo pri enem od naših bralcev.





Bralka vpraša:

Imam težave pri pisanju poizvedbe v programu Microsoft Access. Imam bazo podatkov z dvema tabelama izdelkov, ki vsebujeta skupen stolpec s številsko kodo izdelka in s tem povezanim imenom izdelka. Želim izvedeti, katere izdelke iz tabele A je mogoče najti v tabelo B. Želim dodati stolpec z imenom Rezultati, ki vsebuje ime izdelka iz tabele A, če obstaja, in ime izdelka iz tabele B, če ne obstaja v tabeli A. Ali imate kakšen nasvet?



Bruceov odgovor:

Microsoft Access je sistem za upravljanje baz podatkov (DBMS), zasnovan za uporabo v računalnikih Windows in Mac. Za obdelavo in shranjevanje podatkov uporablja Microsoftov mehanizem zbirk podatkov Jet. Zagotavlja tudi grafični vmesnik za uporabnike, ki skoraj odpravlja potrebo po razumevanju jezika strukturiranih poizvedb (SQL).





SQL je ukazni jezik, ki se uporablja za dodajanje, brisanje, posodabljanje in vračanje informacij, shranjenih v bazi podatkov, ter spreminjanje osnovnih komponent baze podatkov, kot so dodajanje, brisanje ali spreminjanje tabel ali indeksov.

Izhodišče

Če še niste seznanjeni z Accessom ali drugim sistemom RDBMS, vam predlagam, da pred nadaljevanjem začnete s temi viri:



Osnovno razumevanje konceptov v teh člankih bo olajšalo prebavo naslednjega.

Odnosi z bazami podatkov in normalizacija

Predstavljajte si, da vodite podjetje, ki prodaja 50 različnih vrst pripomočkov po vsem svetu. Imate bazo strank 1.250 in tem strankam v povprečju prodate 10.000 pripomočkov. Trenutno za spremljanje vse te prodaje uporabljate eno samo preglednico - dejansko eno samo tabelo zbirke podatkov. Vsako leto vaši preglednici doda na tisoče vrstic.



Zgornje slike so del preglednice za sledenje naročilom, ki jo uporabljate. Zdaj recimo, da obe stranki večkrat na leto od vas kupujeta pripomočke, tako da imate za oba veliko več vrstic.



Če se Joan Smith poroči s Tedom Bainesom in vzame njegov priimek, je treba vsako vrstico, ki vsebuje njeno ime, spremeniti. Težava je še večja, če imate dve različni stranki z imenom 'Joan Smith'. Zaradi precej pogostega dogodka je bilo ohraniti dosledne podatke o prodaji veliko težje.

Z uporabo zbirke podatkov in normalizacijo podatkov lahko ločimo postavke v več tabel, kot so inventar, stranke in naročila.

Če pogledamo del odjemalca v našem primeru, bi odstranili stolpca za ime odjemalca in naslov odjemalca in jih postavili v novo tabelo. Na zgornji sliki sem tudi razčlenil stvari za bolj natančen dostop do podatkov. Nova tabela vsebuje tudi stolpec za primarni ključ (ClientID) - številko, ki bo uporabljena za dostop do vsake vrstice v tej tabeli.

V prvotni tabeli, kjer smo odstranili te podatke, bi dodali stolpec za tuji ključ (ClientID), ki povezuje ustrezno vrstico z informacijami za to stranko.

Ko Joan Smith spremeni ime v Joan Baines, je treba spremembo narediti le enkrat v tabeli odjemalca. Vsaka druga referenca iz združenih tabel bo potegnila ustrezno ime odjemalca, poročilo o tem, kaj je Joan kupila v zadnjih 5 letih, pa bo prejelo vsa naročila pod njenim dekliškim in poročenim imenom, ne da bi bilo treba spremeniti način ustvarjanja poročila .

Kot dodatna prednost to zmanjšuje tudi celotno porabo prostora za shranjevanje.

Pridruži se vrstam

SQL definira pet različnih vrst spojev: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER in CROSS. Ključna beseda OUTER je v stavku SQL neobvezna.

Microsoft Access dovoljuje uporabo INNER (privzeto), LEFT OUTER, RIGHT OUTER in CROSS. FULL OUTER ni podprt kot tak, vendar ga lahko z uporabo LEFT OUTER, UNION ALL in RIGHT OUTER ponaredite na ceno več ciklov procesorja in V/I operacij.

Izhod združevanja CROSS vsebuje vsako vrstico leve tabele, seznanjeno z vsako vrstico desne tabele. Edini čas, ko sem videl uporabljen spoj CROSS, je med testiranjem obremenitve strežnikov baz podatkov.

Poglejmo, kako delujejo osnovni spoji, nato pa jih bomo prilagodili svojim potrebam.

Začnimo z ustvarjanjem dveh tabel, ProdA in ProdB, z naslednjimi lastnostmi oblikovanja.

Samodejno oštevilčevanje je samodejno naraščajoče dolgo celo število, dodeljeno vnosom, ko so dodani v tabelo. Možnost Besedilo ni bila spremenjena, zato bo sprejela besedilni niz dolžine do 255 znakov.

Zdaj jih napolnite z nekaterimi podatki.

Za prikaz razlik v delovanju treh vrst združevanja sem izbrisal vnose 1, 5 in 8 iz ProdA.

Naslednji, ustvarite novo poizvedbo z odhodom na Ustvari> Oblikovanje poizvedb . V pogovornem oknu Pokaži tabelo izberite obe tabeli in kliknite Dodaj , potem Zapri .

Kliknite ProductID v tabeli ProdA, povlecite do ProductID v tabeli ProdB in spustite gumb miške, da ustvarite odnos med tabelami.

Z desno miškino tipko kliknite vrstico med tabelami, ki predstavlja razmerje med postavkami in izberite Pridružite se lastnostim .

Privzeto je izbrana vrsta pridružitve 1 (INNER). Možnost 2 je pridružitev LEVI ZUNANJI in 3 je DESNI ZUNAJ.

Najprej bomo pogledali pridružitev INNER, zato kliknite V redu, da opustite pogovorno okno.

V oblikovalcu poizvedb izberite polja, ki jih želimo videti s spustnih seznamov.

Ko izvedemo poizvedbo (rdeči klicaj na traku), bo iz obeh tabel prikazano polje ProductName z vrednostjo iz tabele ProdA v prvem stolpcu in ProdB v drugem.

Upoštevajte, da rezultati prikazujejo samo vrednosti, pri katerih je ProductID enak v obeh tabelah. Čeprav v tabeli ProdB obstaja vnos za ProductID = 1, se v rezultatih ne prikaže, ker ProductID = 1 ne obstaja v tabeli ProdA. Enako velja za ProductID = 11. Obstaja v tabeli ProdA, ne pa v tabeli ProdB.

Z uporabo gumba Pogled na traku in preklopom na pogled SQL si lahko ogledate poizvedbo SQL, ki jo je ustvaril oblikovalec, ki je uporabila te rezultate.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Če se vrnete v pogled za oblikovanje, spremenite vrsto spoja na 2 (LEVA ZUNANJA). Zaženite poizvedbo, da vidite rezultate.

Kot lahko vidite, je vsak rezultat v tabeli ProdA predstavljen v rezultatih, medtem ko so v rezultatih prikazani le tisti v ProdB, ki imajo ujemajoč se vnos ProductID v tabeli ProdB.

Prazen prostor v stolpcu ProdB.ProductName je posebna vrednost (NULL), ker v tabeli ProdB ni ujemajoče se vrednosti. To se bo kasneje izkazalo za pomembno.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Poskusite isto s tretjo vrsto združevanja (DESNI ZUNANJI).

Rezultati prikazujejo vse iz tabele ProdB, medtem ko prikazuje prazne (znane kot NULL) vrednosti, kjer tabela ProdA nima ustrezne vrednosti. Doslej se nam to najbolj približa rezultatom, ki jih želimo v vprašanju bralca.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Uporaba funkcij v poizvedbi

Rezultati funkcije se lahko vrnejo tudi kot del poizvedbe. Želimo, da se v našem naboru rezultatov prikaže nov stolpec z imenom »Rezultati«. Njegova vrednost bo vsebina stolpca ProductName tabele ProdA, če ima ProdA vrednost (ni NULL), sicer pa je treba vzeti iz tabele ProdB.

Za ustvarjanje tega rezultata je mogoče uporabiti takojšnjo funkcijo IF (IIF). Funkcija ima tri parametre. Prvi je pogoj, ki ga je treba ovrednotiti na vrednost True ali False. Drugi parameter je vrednost, ki jo je treba vrniti, če je pogoj True, tretji parameter pa vrednost, ki jo je treba vrniti, če je pogoj False.

Konstrukcija celotne funkcije za našo situacijo izgleda tako:

IIF(ProdA.ProductID Is Null, ProdB.ProductName,ProdA.ProductName)

Upoštevajte, da parameter pogoja ne preverja enakosti. Ničelna vrednost v zbirki podatkov nima vrednosti, ki bi jo lahko primerjali s katero koli drugo vrednostjo, vključno z drugo vrednostjo Null. Z drugimi besedami, Null ni enak Null. Nikoli. Da bi to presegli, namesto tega preverimo vrednost s ključno besedo 'Is'.

Lahko bi uporabili tudi 'Is Not Null' in spremenili vrstni red parametrov True in False, da bi dobili enak rezultat.

Ko to vnesete v oblikovalnik poizvedb, morate vnesti celotno funkcijo v vnos Field:. Če želite ustvariti stolpec »Rezultati«, morate uporabiti vzdevek. Če želite to narediti, pred funkcijo predgovorite z »Rezultati:«, kot je prikazano na naslednjem posnetku zaslona.

Enakovredna koda SQL za to bi bila:

SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Ko bomo izvedli to poizvedbo, bo prinesla te rezultate.

ime datoteke predolgo za brisanje

Tu vidimo za vsak vnos, kjer ima tabela ProdA vrednost, to vrednost, ki se odraža v stolpcu Rezultati. Če v tabeli ProdA ni vnosa, se vnos iz ProdB prikaže v rezultatih, kar je vprašal naš bralec.

Za več virov za učenje Microsoftovega dostopa si oglejte Joel Lee's How to Learn Microsoft Access: 5 brezplačnih spletnih virov.

Deliti Deliti Cvrkutati E-naslov Ali je vredno nadgraditi na Windows 11?

Windows je bil preoblikovan. Toda ali je to dovolj, da vas prepriča, da preidete z operacijskega sistema Windows 10 na Windows 11?

Preberite Naprej Sorodne teme
  • Produktivnost
  • Vprašajte strokovnjake
O avtorju Bruce Epper(13 objavljenih člankov)

Bruce se z elektroniko igra že od 70. let, računalniki od zgodnjih 80. let in natančno odgovarja na vprašanja o tehnologiji, ki je ni uporabljal niti videl ves čas. Tudi sam se jezi, ko poskuša igrati kitaro.

Več od Brucea Epperja

Naročite se na naše novice

Pridružite se našemu glasilu za tehnične nasvete, ocene, brezplačne e -knjige in ekskluzivne ponudbe!

Kliknite tukaj, če se želite naročiti