Kako napisati Microsoft Access SQL upite od nule

Kako napisati Microsoft Access SQL upite od nule

Microsoft Access vjerojatno je najmoćniji alat u cijelom paketu Microsoft Office, ali ipak mistificira (a ponekad i plaši) korisnike Officea. Uz strmiju krivulju učenja od Worda ili Excela, kako bi netko trebao zaokupiti glavu korištenjem ovog alata? Ovog će tjedna Bruce Epper razmotriti neka pitanja koja je ovo pitanje potaknulo jednog od naših čitatelja.





Čitatelj pita:

Imam problema s pisanjem upita u Microsoft Accessu. Imam bazu podataka s dvije tablice proizvoda koje sadrže zajednički stupac s numeričkim kodom proizvoda i pridruženim nazivom proizvoda. Želim saznati koji se proizvodi iz tablice A mogu pronaći u tablicu B. Želim dodati stupac pod nazivom Rezultati koji sadrži naziv proizvoda iz tablice A ako postoji, i naziv proizvoda iz tablice B ako ne postoji u tablici A. Imate li savjet?



Bruceov odgovor:

Microsoft Access je sustav za upravljanje bazom podataka (DBMS) dizajniran za upotrebu na Windows i Mac računalima. Koristi Microsoftov mehanizam baze podataka Jet za obradu i pohranu podataka. Također pruža grafičko sučelje za korisnike koje gotovo eliminira potrebu za razumijevanjem jezika strukturiranih upita (SQL).





SQL je jezik naredbe 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 još niste upoznati s Accessom ili nekim drugim RDBMS -om, predlažem vam da prije nastavka počnete s ovim resursima:



Osnovno razumijevanje pojmova navedenih u ovim člancima učinit će sljedeće pomalo lakšim za probaviti.

Odnosi i normalizacija baze podataka

Zamislite da vodite tvrtku koja prodaje 50 različitih vrsta widgeta po cijelom svijetu. Imate bazu klijenata od 1.250 i u prosjeku mjesec dana prodate tim klijentima 10.000 widgeta. Trenutno koristite jednu proračunsku tablicu za praćenje svih ovih prodaja - zapravo jednu tablicu baze podataka. Svake godine u vašu proračunsku tablicu dodaju se tisuće redaka.



Gore navedene slike dio su proračunske tablice za praćenje narudžbi. Sada recite da oba klijenta kupuju widgete od vas nekoliko puta godišnje, tako da imate daleko više redaka za oboje.



Ako se Joan Smith uda za Teda Bainesa i uzme njegovo prezime, svaki red koji sadrži njezino ime sada je potrebno promijeniti. Problem se pojačava ako slučajno imate dvije različite klijentice s imenom 'Joan Smith'. Upravo je postalo mnogo teže održavati dosljedne podatke o prodaji zbog prilično uobičajenog događaja.

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

Samo gledajući dio klijenta 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 za detaljniji pristup podacima. Nova tablica također sadrži stupac za primarni ključ (ClientID) - broj koji će se koristiti za pristup svakom retku u ovoj tablici.

U izvornu tablicu u kojoj smo uklonili te podatke dodali bismo stupac za strani ključ (ClientID) koji povezuje odgovarajući redak koji sadrži podatke za ovog klijenta.

Sada, kad Joan Smith promijeni ime u Joan Baines, promjenu je potrebno izvršiti samo jednom u tablici klijenta. Svaka druga referenca iz spojenih tablica povući će odgovarajuće ime klijenta, a izvješće koje pregledava ono što je Joan kupila u posljednjih 5 godina dobit će sve narudžbe i pod njezinim djevojačkim i vjenčanim imenom, bez potrebe za promjenom načina generiranja izvješća .

Kao dodatna prednost, ovo također smanjuje ukupnu količinu utrošenog prostora za pohranu.

Vrste pridruživanja

SQL definira pet različitih vrsta pridruživanja: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER i CROSS. Ključna riječ OUTER nije obavezna u SQL izrazu.

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

Izlaz CROSS spoja sadrži svaki redak lijeve tablice uparen sa svakim retkom desne tablice. Jedini put kada sam vidio da se koristi CROSS pridruživanje je tijekom testiranja opterećenja poslužitelja baze podataka.

Pogledajmo kako funkcioniraju osnovni spojevi, a zatim ćemo ih izmijeniti tako da odgovaraju našim potrebama.

Počnimo stvaranjem dvije tablice, ProdA i ProdB, sa sljedećim svojstvima dizajna.

Automatsko numeriranje je automatski rastući dugi cijeli broj dodijeljen unosima koji se dodaju u tablicu. Opcija Text nije izmijenjena pa će prihvatiti tekstualni niz do 255 znakova.

Sada ih napunite nekim podacima.

Kako bih pokazao razlike u načinu rada 3 vrste spajanja, izbrisao sam unose 1, 5 i 8 iz ProdA -e.

Sljedeći, stvoriti novi upit odlaskom na Kreiraj> Dizajn upita . Odaberite obje tablice u dijalogu Prikaži tablicu i kliknite Dodaj , tada Zatvoriti .

Pritisnite ProductID u tablici ProdA, povucite je do ProductID u tablici ProdB i otpustite tipku miša za stvaranje odnosa između tablica.

Desnom tipkom miša kliknite liniju između tablica koja predstavlja odnos između stavki i odaberite Pridruživanje svojstava .

Prema zadanim postavkama odabrana je vrsta pridruživanja 1 (INNER). Opcija 2 je pridruživanje LIJEVI OUTER, a 3 DESNO OUTER pridruživanje.

Prvo ćemo pogledati INNER pridruživanje pa kliknite U redu da biste odbacili 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 s vrijednošću iz tablice ProdA u prvom stupcu i ProdB u drugom.

Uočite da rezultati prikazuju samo vrijednosti gdje je ProductID jednak u obje tablice. Iako postoji unos za ProductID = 1 u tablici ProdB, on se ne prikazuje u rezultatima jer ProductID = 1 ne postoji u tablici ProdA. Isto vrijedi i za ProductID = 11. Postoji u tablici ProdA, ali ne i u tablici ProdB.

Korištenjem gumba Pogled na vrpci i prebacivanjem na SQL prikaz možete vidjeti SQL upit koji je generirao dizajner i koji je koristio za dobivanje ovih rezultata.

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

Vraćajući se na prikaz dizajna, promijenite vrstu spajanja na 2 (LIJEVO VANJSKO). Pokrenite upit da vidite rezultate.

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

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

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

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

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

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

Korištenje funkcija u upitu

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

Za generiranje ovog rezultata može se koristiti funkcija neposrednog IF (IIF). Funkcija ima tri parametra. Prvi je uvjet koji se mora procijeniti na True ili False vrijednost. Drugi parametar je vrijednost koju treba vratiti ako je uvjet True, a treći parametar je vrijednost koju treba vratiti ako je uvjet False.

Konstrukcija potpune funkcije za našu situaciju izgleda ovako:

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

Uočite da parametar uvjeta ne provjerava jednakost. Nulta vrijednost u bazi podataka nema vrijednost koja se može usporediti s bilo kojom drugom vrijednošću, uključujući drugu null. Drugim riječima, Null nije jednako Null. Ikad. Kako bismo to prebrodili, umjesto toga provjeravamo vrijednost pomoću ključne riječi 'Je'.

Mogli smo koristiti i 'Nije null' i promijeniti redoslijed parametara True i False kako bismo dobili isti rezultat.

Kada ovo stavljate u Dizajner upita, morate unijeti cijelu funkciju u polje Field:. Da biste postigli da stvori stupac 'Rezultati', morate upotrijebiti pseudonim. Da biste to učinili, predgovorite funkciju s 'Rezultati:' kao što se vidi na sljedećoj snimci zaslona.

Ekvivalentni SQL kod za to bi bio:

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;

Sada, kada pokrenemo ovaj upit, on će proizvesti ove rezultate.

instalirajte google play na fire tabletu

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

Dodatne izvore za učenje Microsoft Accessa potražite u članku Joela Leea Kako naučiti Microsoft Access: 5 besplatnih internetskih resursa.

Udio Udio Cvrkut E -pošta Isplati li se nadogradnja na Windows 11?

Windows je redizajniran. No, je li to dovoljno da vas uvjeri da prijeđete s Windows 10 na Windows 11?

Pročitajte Dalje Povezane teme
  • Produktivnost
  • Pitajte stručnjake
O autoru Bruce Epper(Objavljeno 13 članaka)

Bruce se s elektronikom igra od 70 -ih, računala od ranih 80 -ih i točno odgovara na pitanja o tehnologiji koju nije koristio niti vidio cijelo vrijeme. I sam sebe živcira pokušavajući svirati gitaru.

Više od Brucea Eppera

Pretplatite se na naše obavijesti

Pridružite se našem biltenu za tehničke savjete, recenzije, besplatne e -knjige i ekskluzivne ponude!

Kliknite ovdje za pretplatu