Kako napisati Microsoft Access SQL upite iz nule

  • Owen Little
  • 0
  • 5341
  • 235
Oglas

Microsoft Access je zasigurno najmoćniji alat u čitavom paketu Microsoft Officea, ali mistificira (a ponekad i zastrašuje) korisnike Office Officea. S strmijom krivuljom učenja od Worda ili Excela, kako bi se netko trebao omotati oko glave pomoću ovog alata? Bruce Epper će ovog tjedna pogledati dio koji je ovo pitanje pokrenuo jedan od naših čitatelja.

Čitatelj pita:

Imam problema s pisanjem upita u programu Microsoft Access.

Imam bazu podataka s dvije tablice proizvoda koja sadrži zajednički stupac s numeričkim šifrom proizvoda i pridruženim nazivom proizvoda.

Želim saznati koji se proizvodi iz tablice A mogu naći u tablici B. Želim dodati stupac s nazivom Rezultati koji sadrži naziv proizvoda iz tablice A ako postoji, te naziv proizvoda iz tablice B ako ga ne postoji u tablici A.

Imate li kakav savjet?

Bruceov odgovor:

Microsoft Access je sustav za upravljanje bazama podataka (DBMS) dizajniran za upotrebu na Windows i Mac računalima. Koristi Microsoftov motor Jet baze podataka za obradu i pohranu podataka. Također nudi grafičko sučelje za korisnike što gotovo eliminira potrebu za razumijevanjem strukturiranog jezika upita (SQL).

SQL je naredbeni jezik koji se koristi za dodavanje, brisanje, ažuriranje i vraćanje podataka pohranjenih u bazi podataka, kao i za izmjenu osnovnih komponenti baze podataka poput dodavanja, brisanja ili izmjene tablica ili indeksa.

Polazna točka

Ako već nemate poznanstvo s Accessom ili nekim drugim RDBMS-om, predlažem vam da započnete s ovim resursima prije nego što nastavite:

  • Pa što je baza podataka? Pa što je uopće baza podataka? [MakeUseOf objašnjava] Dakle, što je uopće baza podataka? [MakeUseOf Explains] Za programera ili tehnološkog entuzijasta, koncept baze podataka nešto je što se doista može shvatiti zdravo za gotovo. Međutim, mnogima je pojam baze podataka sam po sebi pomalo tuđi ... gdje Ryan Dube koristi Excel kako bi pokazao osnove relacijskih baza podataka.
  • Kratki vodič za početak rada s Microsoftovim pristupom 2007 Kratki vodič za početak rada s Microsoft Accessom 2007 Kratki vodič za početak rada s Microsoft Accessom 2007, koji je pregled pristupa na visokoj razini i komponenti koje sadrže Access bazu podataka.
  • Brzi vodič za tablice u programu Microsoft Access 2007 Brzi vodič za tablice u programu Microsoft Access 2007 Kratki vodič za tablice u programu Microsoft Access 2007 pregledava stvaranje prve baze podataka i tablica za pohranu vaših strukturiranih podataka.
  • Brzi vodič o upitima u programu Microsoft Access 2007 Brzi vodič o upitima u programu Microsoft Access 2007 Brzi vodič o upitima u programu Microsoft Access 2007 traži način vraćanja određenih dijelova podataka pohranjenih u tablicama baza podataka.

Osnovno razumijevanje koncepata u ovim člancima olakšaće probavu sljedećeg.

Odnosi i normalizacija baza podataka

Zamislite da imate tvrtku koja prodaje 50 različitih vrsta widgeta širom svijeta. Imate klijentsku bazu od 1.250 i prosječno mjesečno prodate 10.000 widgeta tim klijentima. Trenutno koristite jednu proračunsku tablicu za praćenje svih tih prodaja - učinkovito jednu tablicu baze podataka. Svake godine u vašu proračunsku tablicu doda tisuće redaka.

Gore navedene slike dio su proračunske tablice za praćenje narudžbe koju koristite. Sada recite da obojica ovih klijenata kupuju widgete od vas nekoliko puta godišnje, tako da imate puno više redaka za oba.

Ako se Joan Smith udala za Teda Bainesa i uzela mu prezime, svaki red koji sadrži njeno ime sada treba promijeniti. Problem je složen ako imate dva različita klijenta s imenom "Joan Smith". Upravo je postalo mnogo teže održavati podatke o prodaji dosljednim zbog prilično uobičajenog događaja.

Korištenjem baze podataka i normalizacijom podataka možemo izdvojiti stavke u više tablica, kao što su inventar, klijenti i narudžbe.

Samo gledanjem dijela klijenta iz našeg primjera, uklonili bismo stupce za ime klijenta i adresu klijenta i stavili ih u novu tablicu. Na gornjoj slici također sam bolje razbio stvari radi detaljnijeg pristupa podacima. Nova tablica sadrži i stupac za primarni ključ (ClientID) - broj koji će se koristiti za pristup svakom retku u ovoj tablici.

U originalnoj tablici iz koje smo uklonili ove podatke dodali bismo stupac za strani ključ (ClientID) koji povezuje u odgovarajući redak koji sadrži podatke za konkretnog klijenta.

Sada, kad Joan Smith promijeni ime u Joan Baines, promjenu je potrebno izvršiti samo jednom u tablici klijenta. Svaka druga referenca iz pridruženih tablica povući će odgovarajuće ime klijenta, a izvještaj u kojem se promatra ono što je Joan kupila u posljednjih 5 godina dobit će sve narudžbe i pod djevojačkim i u bračnim imenima, a da ne mora mijenjati način na koji se izvješće generira.

Kao dodatnu korist, ovo također smanjuje i ukupnu količinu potrošene zalihe.

Vrste pridruživanja

SQL definira pet različitih vrsta spajanja: UNUTRAŠNJA, LIJEVA VANJSKA, DESNA VANJSKA, PUNA OUTER i CROSS. OUTER ključna riječ nije obavezna u SQL izrazu.

Microsoft Access dopušta upotrebu INNER-a (zadano), LIJEVO OUTER, DESNO OUTER i CROSS. PUNI OUTER nije podržan kao takav, ali upotrebom LIJEVO OUTER, UNION ALL i DESNO OUTER može se krivotvoriti po cijeni više CPU ciklusa i I / O operacija.

Izlaz CROSS pridruživanja sadrži svaki red lijeve tablice uparen sa svakim redom desne tablice. Jedini put kada sam vidio CROSS pridruživanje se koristi tijekom testiranja opterećenja poslužitelja baza podataka.

Pogledajmo kako djeluju osnovne spojeve, a zatim ćemo ih prilagoditi našim potrebama.

Započnimo stvaranjem dvije tablice, ProdA i ProdB, sa sljedećim dizajnerskim svojstvima.

Automatski broj automatski se povećava cijelim brojevima dodijeljenim unosima jer su dodani u tablicu. Opcija Text nije izmijenjena, pa će prihvatiti tekstualni niz do 255 znakova.

Sada ih popunite s nekim podacima.

Kako bih pokazao razlike u funkcioniranju 3 vrste pridruživanja, iz ProdA sam izbrisao unose 1, 5 i 8.

Zatim kreirajte novi upit Stvori> Dizajn upita. Odaberite obje tablice u dijaloškom okviru Prikaži tablicu i kliknite Dodaj, zatim Zatvoriti.

Kliknite ProductID u tablici ProdA, povucite ga na ProductID u tablici ProdB i pustite gumb miša za stvaranje odnosa između tablica.

Desnom tipkom miša kliknite liniju između tablica koje predstavljaju odnos između stavki i odaberite Pridružite svojstva.

Prema zadanim postavkama odabran je tip pridruživanja 1 (INNER). Opcija 2 je pridruživanje LEVO OUTER, a 3 je pridruživanje DESNO OUTER.

Prvo ćemo pogledati pridruživanje UNUTRA, pa kliknite U redu da biste isključili dijalog.

U dizajneru upita odaberite polja koja želimo vidjeti s padajućih popisa.

Kada pokrenemo upit (crveni uskličnik na vrpci), prikazat će se polje ProductName iz obje tablice sa vrijednošću iz tablice ProdA u prvom stupcu i ProdB u drugom..

Primjetite da rezultati pokazuju vrijednosti samo ako je ProductID jednak u obje tablice. Iako u tablici ProdB postoji unos za ProductID = 1, u rezultatima se ne prikazuje jer ProductID = 1 ne postoji u tablici ProdA. Isto vrijedi i za ProductID = 11. Postoji u tablici ProdA, ali nije u tablici ProdB.

Upotrebom gumba View na vrpci i prebacivanjem na SQL View možete vidjeti SQL upit generiran od dizajnera koji se koristio za dobivanje tih rezultata.

ODABIR ProdA.ProductName, ProdB.ProductName IZ PRODA UNUTARNJA PRIDRUŽITE se ProdB ON ProdA.ProductID = ProdB.ProductID;

Vratite se na dizajn dizajna, promijenite vrstu spajanja u 2 (LIJEVI VANJSKI). Pokrenite upit da biste vidjeli rezultate.

Kao što možete vidjeti, svaki je unos u tablici ProdA predstavljen u rezultatima, dok se u rezultatima prikazuju samo oni u ProdB-u koji imaju odgovarajući ProductID unos u tablici ProdB..

Prazni prostor u stupcu ProdB.ProductName posebna je vrijednost (NULL) jer u tablici ProdB nema odgovarajuće vrijednosti. To će se kasnije pokazati važnim.

ODABJITE ProdA.ProductName, ProdB.ProductName OD ProdA LEVO PRIDRUŽITE se ProdB ON ProdA.ProductID = ProdB.ProductID;

Pokušajte isto s trećom vrstom spajanja (DESNO OUTER).

Rezultati pokazuju sve iz tablice ProdB dok prikazuje prazne (poznate kao NULL) vrijednosti gdje ProdA tablica nema odgovarajuću vrijednost. Za sada, to nam najviše približava željene rezultate u našem čitateljevom pitanju.

SELECT ProdA.ProductName, ProdB.ProductName OD ProdA DESNO PRIDRUŽITE se ProdB ON ProdA.ProductID = ProdB.ProductID;

Korištenje funkcija u upitu

Rezultati funkcije mogu se vratiti i kao dio upita. Želimo da se novi stupac pod nazivom "Rezultati" prikazuje u našem skupu rezultata. Njegova vrijednost bit će sadržaj stupca ProductName tablice ProdA ako ProdA ima vrijednost (nije NULL), u suprotnom treba uzeti iz tablice ProdB.

Da bi se postigao ovaj rezultat, može se koristiti funkcija IF (IIF). Funkcija ima tri parametra. Prvo je stanje koje se mora ocijeniti na vrijednost True ili False. Drugi parametar je vrijednost koju treba vratiti ako je uvjet True, a treći parametar je vrijednost koja se vraća ako je uvjet False.

Konstrukt pune funkcije za našu situaciju izgleda ovako:

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

Primijetite da parametar uvjeta ne provjerava jednakost. Nulta vrijednost u bazi podataka nema vrijednost koja se može uspoređivati ​​s bilo kojom drugom vrijednošću, uključujući drugu Null. Drugim riječima, Null ne odgovara Nullu. Ikad. Da bismo to prošli, umjesto toga provjeravamo vrijednost pomoću ključne riječi "Is".

Mogli smo također upotrijebiti 'Is Not Null' i promijeniti redoslijed parametara True i False da bismo dobili isti rezultat.

Kad ovo stavite u Dizajner upita, morate unijeti cijelu funkciju u polje Field:. Da biste ga kreirali u stupcu "Rezultati", morate upotrijebiti pseudonim. Da biste to učinili, predgovorite funkciju s "Rezultati:" kao što se vidi na sljedećoj snimci zaslona.

Ekvivalentni SQL kôd za to bi bio:

SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID je nula, ProdB.ProductName, ProdA.ProductName) KAO Rezultati IZ PRODA PRIDRUŽITE se ProdB ON ProdA.ProductID = ProdB.ProductID;

Kada pokrenemo ovaj upit, proizvest će ove rezultate.

Ovdje vidimo za svaki unos gdje tablica ProdA ima vrijednost, ta vrijednost se odražava u stupcu Rezultati. Ako u tablici ProdA nema unosa, unos iz ProdB-a pojavit će se u rezultatima, a to je upravo ono što je pitao naš čitatelj.

Za više resursa za učenje Microsoftovog pristupa, pogledajte Joel Lee kako naučiti Microsoftov pristup: 5 besplatnih mrežnih resursa Kako naučiti Microsoftov pristup: 5 besplatnih internetskih resursa Kako naučiti Microsoftov pristup: 5 besplatnih internetskih resursa Morate li upravljati velikim iznosom podataka? Trebali biste pogledati Microsoftov pristup. Naši besplatni resursi za učenje mogu vam pomoći da započnete i naučite vještine za složenije projekte. .




Još ne komentari

O modernoj tehnologiji, jednostavnoj i pristupačnoj.
Vaš vodič u svijetu moderne tehnologije. Naučite kako koristiti tehnologije i uređaje koji nas okružuju svaki dan i naučite kako otkriti zanimljivosti na Internetu.