Kaip parašyti „Microsoft Access“ SQL užklausas nuo nulio

Kaip parašyti „Microsoft Access“ SQL užklausas nuo nulio

„Microsoft Access“ yra neabejotinai galingiausias įrankis visame „Microsoft Office“ rinkinyje, tačiau jis mistifikuoja (o kartais ir gąsdina) „Office“ vartotojus. Su staigesniu mokymosi kreiviu nei „Word“ ar „Excel“, kaip kas nors turėtų apsukti galvą apie šio įrankio naudojimą? Šią savaitę Bruce'as Epperis apžvelgs kai kurias problemas, kurias paskatino vienas iš mūsų skaitytojų.





Skaitytojas klausia:

Turiu problemų rašydamas užklausą „Microsoft Access“. Turiu duomenų bazę su dviem produktų lentelėmis, kuriose yra bendras stulpelis su skaitiniu produkto kodu ir susijusiu produkto pavadinimu. Noriu sužinoti, kuriuos A lentelės produktus galima rasti Noriu pridėti stulpelį „Rezultatai“, kuriame yra produkto pavadinimas iš A lentelės, jei toks yra, ir produkto pavadinimas iš B lentelės, kai jo nėra A lentelėje. Ar turite patarimų?



Bruce'o atsakymas:

„Microsoft Access“ yra duomenų bazių valdymo sistema (DBVS), skirta naudoti tiek „Windows“, tiek „Mac“ kompiuteriuose. Jis naudoja „Microsoft“ „Jet“ duomenų bazės variklį duomenų apdorojimui ir saugojimui. Tai taip pat suteikia grafinę sąsają vartotojams, kuri beveik pašalina poreikį suprasti struktūrizuotų užklausų kalbą (SQL).





SQL yra komandų kalba, naudojama norint pridėti, ištrinti, atnaujinti ir grąžinti duomenų bazėje saugomą informaciją, taip pat keisti pagrindinius duomenų bazės komponentus, pvz., Pridėti, ištrinti ar keisti lenteles ar indeksus.

Atspirties taškas

Jei dar nesate gerai susipažinę su „Access“ ar kita RDBMS, prieš tęsdami siūlau pradėti nuo šių išteklių:



Jei iš esmės suprasite šiuose straipsniuose pateiktas sąvokas, tai bus šiek tiek lengviau virškinama.

Duomenų bazių santykiai ir normalizavimas

Įsivaizduokite, kad vadovaujate įmonei, parduodančiai 50 skirtingų tipų valdiklių visame pasaulyje. Turite 1250 klientų bazę ir vidutiniškai per mėnesį parduodate šiems klientams 10 000 valdiklių. Šiuo metu naudojate vieną skaičiuoklę, kad galėtumėte stebėti visus šiuos pardavimus - iš tikrųjų vieną duomenų bazės lentelę. Ir kiekvienais metais jūsų skaičiuoklė papildo tūkstančius eilučių.



Aukščiau pateikti vaizdai yra jūsų naudojamos užsakymų stebėjimo skaičiuoklės dalis. Dabar pasakykite, kad abu šie klientai perka valdiklius iš jūsų kelis kartus per metus, taigi jūs turite kur kas daugiau eilučių abiem.



Jei Joan Smith ištekės už Tedo Baineso ir paims jo pavardę, kiekvieną eilutę, kurioje yra jos vardas, reikia pakeisti. Problema dar labiau apsunkina, jei turite du skirtingus klientus, vardu „Joan Smith“. Ką tik tapo daug sunkiau išlaikyti nuoseklius pardavimo duomenis dėl gana dažno įvykio.

Naudodami duomenų bazę ir normalizuodami duomenis, galime atskirti prekes į kelias lenteles, pvz., Atsargas, klientus ir užsakymus.

Tik pažvelgę ​​į mūsų pavyzdžio kliento dalį, pašalinome stulpelius pagal kliento pavadinimą ir kliento adresą ir sudedame juos į naują lentelę. Aukščiau esančiame paveikslėlyje aš taip pat geriau išsprendžiau dalykus, kad galėčiau gauti išsamesnę prieigą prie duomenų. Naujoje lentelėje taip pat yra pirminio rakto (kliento ID) stulpelis - skaičius, kuris bus naudojamas norint pasiekti kiekvieną šios lentelės eilutę.

Pradinėje lentelėje, kurioje pašalinome šiuos duomenis, pridėtume svetimo rakto (ClientID) stulpelį, kuris nukreipia į tinkamą eilutę, kurioje yra informacija apie šį konkretų klientą.

Dabar, kai Joan Smith pakeičia savo vardą į Joan Baines, pakeitimus reikia atlikti tik vieną kartą klientų lentelėje. Kiekviena kita nuoroda iš sujungtų lentelių surinks tinkamą kliento vardą, o ataskaitoje, kurioje nagrinėjama, ką Joan įsigijo per pastaruosius 5 metus, bus gauti visi užsakymai mergautine ir vedusia pavardėmis, nekeičiant ataskaitos sudarymo. .

Tai taip pat sumažina bendrą sunaudotos saugyklos kiekį.

Prisijungimo tipai

SQL apibrėžia penkis skirtingus sujungimo tipus: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER ir CROSS. Išorinis raktinis žodis SQL sakinyje yra neprivalomas.

„Microsoft Access“ leidžia naudoti INNER (numatytasis), LEFT OUTER, RIGHT OUTER ir CROSS. „FULL OUTER“ nėra palaikomas, tačiau naudojant LEFT OUTER, UNION ALL ir RIGHT OUTER, jis gali būti suklastotas už daugiau procesoriaus ciklų ir įvesties/išvesties operacijų kainą.

CROSS sujungimo išvestyje yra kiekviena kairės lentelės eilutė, suporuota su kiekviena dešinės lentelės eilute. Vienintelis kartas, kai kada nors mačiau naudojamą CROSS jungtį, yra duomenų bazės serverių apkrovos testavimas.

Pažiūrėkime, kaip veikia pagrindinės jungtys, tada pakeisime jas pagal savo poreikius.

Pradėkime nuo dviejų lentelių „ProdA“ ir „ProdB“ sukūrimo su šiomis dizaino savybėmis.

„AutoNumber“ yra automatiškai didėjantis ilgas sveikasis skaičius, priskiriamas įrašams, kai jie pridedami prie lentelės. Teksto parinktis nebuvo pakeista, todėl ji priims iki 255 simbolių ilgio teksto eilutę.

Dabar užpildykite juos tam tikrais duomenimis.

Norėdami parodyti 3 sujungimo tipų veikimo skirtumus, iš ProdA ištryniau 1, 5 ir 8 įrašus.

Kitas, sukurti naują užklausą eidamas į Sukurti> Užklausos dizainas . Dialogo lange Rodyti lentelę pasirinkite abi lenteles ir spustelėkite Pridėti , tada Uždaryti .

Lentelėje „ProdA“ spustelėkite „ProductID“, vilkite jį į „ProductID“ lentelėje „ProdB“ ir atleiskite pelės mygtuką, kad sukurtumėte ryšį tarp lentelių.

Dešiniuoju pelės mygtuku spustelėkite liniją tarp lentelių, vaizduojančių elementų ir pasirinkite Prisijungti prie ypatybių .

Pagal numatytuosius nustatymus pasirinktas 1 prisijungimo tipas (INNER). 2 variantas yra kairysis išorinis sujungimas, o 3 - dešinysis išorinis sujungimas.

Pirmiausia pažvelgsime į vidinį prisijungimą, todėl spustelėkite Gerai, kad atsisakytumėte dialogo.

Užklausų dizainerio išskleidžiamuosiuose sąrašuose pasirinkite laukus, kuriuos norime matyti.

Kai vykdome užklausą (raudonas šauktukas juostelėje), ji parodys lauką „ProductName“ iš abiejų lentelių su reikšme iš lentelės „ProdA“ pirmajame stulpelyje ir „ProdB“ antrame stulpelyje.

Atkreipkite dėmesį, kad rezultatai rodo tik tas vertes, kuriose „ProductID“ abiejose lentelėse yra lygus. Nors lentelėje „ProdB“ yra įrašas „ProductID = 1“, jis nerodomas rezultatuose, nes „ProductID = 1“ lentelėje „ProdA“ nėra. Tas pats pasakytina apie „ProductID = 11.“. Jis yra lentelėje „ProdA“, bet ne lentelėje „ProdB“.

Naudodami juostelės mygtuką Rodinys ir pereidami prie SQL rodinio, galite pamatyti SQL užklausą, kurią sukūrė dizaineris, naudotas šiems rezultatams gauti.

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

Grįždami į dizaino rodinį, pakeiskite sujungimo tipą į 2 (LEFT OUTER). Norėdami pamatyti rezultatus, paleiskite užklausą.

Kaip matote, kiekvienas „ProdA“ lentelės įrašas pateikiamas rezultatuose, o tik tie „ProdB“ įrašai, kurių lentelėje „ProdB“ yra atitinkamas „ProductID“ įrašas, rodomi rezultatuose.

Tuščia vieta stulpelyje „ProdB.ProductName“ yra ypatinga reikšmė (NULL), nes lentelėje „ProdB“ nėra atitinkančios vertės. Tai vėliau pasirodys svarbu.

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

Išbandykite tą patį su trečiuoju sujungimo tipu (RIGHT OUTER).

Rezultatai rodo viską iš lentelės „ProdB“, kol rodomos tuščios (žinomos kaip NULL) vertės, kai „ProdA“ lentelė neturi atitinkančios vertės. Iki šiol tai priartina mus prie skaitytojo klausimo norimų rezultatų.

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

Funkcijų naudojimas užklausoje

Funkcijos rezultatai taip pat gali būti grąžinti kaip užklausos dalis. Norime, kad mūsų rezultatų rinkinyje būtų rodomas naujas stulpelis pavadinimu „Rezultatai“. Jos vertė bus lentelės „ProdA“ stulpelio „ProductName“ turinys, jei „ProdA“ turi reikšmę (ji nėra NULL), kitaip ji turėtų būti paimta iš lentelės „ProdB“.

Šiam rezultatui gauti galima naudoti funkciją Immediate IF (IIF). Funkcija turi tris parametrus. Pirmoji yra sąlyga, kuri turi būti įvertinta kaip teisinga ar klaidinga. Antrasis parametras yra vertė, kurią reikia grąžinti, jei sąlyga yra teisinga, o trečiasis parametras - vertė, kurią reikia grąžinti, jei sąlyga yra klaidinga.

Visa mūsų situacijos funkcijų struktūra atrodo taip:

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

Atkreipkite dėmesį, kad sąlygos parametras netikrina lygybės. Nulinė reikšmė duomenų bazėje neturi vertės, kurią būtų galima palyginti su bet kuria kita verte, įskaitant kitą nulinę. Kitaip tariant, Null nėra lygus nuliui. Kada nors. Norėdami to išvengti, vietoj to patikriname vertę naudodami raktinį žodį „Yra“.

Mes taip pat galėjome naudoti „Is Not Null“ ir pakeisti teisingų ir klaidingų parametrų tvarką, kad gautume tą patį rezultatą.

Įdėdami tai į užklausų dizainerį, turite įvesti visą funkciją lauke::. Norėdami sukurti stulpelį „Rezultatai“, turite naudoti slapyvardį. Norėdami tai padaryti, iš anksto nurodykite funkciją „Rezultatai:“, kaip parodyta šioje ekrano kopijoje.

Atitinkamas SQL kodas tam būtų toks:

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;

Dabar, kai vykdysime šią užklausą, ji duos šiuos rezultatus.

kaip sukurti tekstūrą „Photoshop“

Čia matome kiekvieną įrašą, kuriame lentelė „ProdA“ turi reikšmę, ta vertė atsispindi stulpelyje „Rezultatai“. Jei lentelėje „ProdA“ nėra įrašo, „ProdB“ įrašas rodomas rezultatuose, būtent to klausė mūsų skaitytojas.

Norėdami gauti daugiau išteklių mokytis „Microsoft Access“, peržiūrėkite Joelio Lee knygą „Kaip išmokti„ Microsoft Access “: 5 nemokami internetiniai ištekliai.

Dalintis Dalintis „Tweet“ Paštu Ar verta atnaujinti į „Windows 11“?

„Windows“ buvo pertvarkytas. Bet ar to pakanka norint įtikinti jus pereiti nuo „Windows 10“ prie „Windows 11“?

Skaityti toliau Susijusios temos
  • Produktyvumas
  • Klauskite ekspertų
Apie autorių Bruce'as Epperis(Paskelbta 13 straipsnių)

Bruce'as žaidė su elektronika nuo 70 -ųjų, kompiuteriais nuo 80 -ųjų pradžios ir tiksliai atsakinėjo į klausimus apie technologijas, kurių jis nenaudojo ir nematė visą laiką. Jis taip pat erzina save bandydamas groti gitara.

Daugiau iš Bruce'o Epperio

Prenumeruokite mūsų naujienlaiškį

Prisijunkite prie mūsų naujienlaiškio, kad gautumėte techninių patarimų, apžvalgų, nemokamų el. Knygų ir išskirtinių pasiūlymų!

Norėdami užsiprenumeruoti, spustelėkite čia