Întrebați experții

Cum să scrieți interogări SQL Microsoft Access de la zero

Cum să scrieți interogări SQL Microsoft Access de la zero / Întrebați experții

Microsoft Access este, fără îndoială, cel mai puternic instrument din întreaga suită Microsoft Office, dar mistifică (și, uneori, sperie) utilizatorii de putere Office. Cu o curbă mai accentuată de învățare decât Word sau Excel, cum ar trebui cineva să-și înfășoare capul în jurul utilizării acestui instrument? În această săptămână, Bruce Epper va analiza unele dintre problemele provocate de această întrebare de la unul dintre cititorii noștri.

Un cititor întreabă:

Am probleme cu scrierea unei interogări în Microsoft Access.

Am o bază de date cu două tabele de produse care conțin o coloană obișnuită cu un cod de produs numeric și un nume de produs asociat.

Vreau să aflu care produse din Tabelul A pot fi găsite în Tabelul B. Vreau să adaug o coloană numită Rezultate care conține numele produsului din Tabelul A dacă există și numele produsului din Tabelul B atunci când acesta nu există în tabelul A.

Aveți vreo sfat?

Răspunsul lui Bruce:

Microsoft Access este un sistem de gestionare a bazelor de date (DBMS) conceput pentru utilizarea pe ambele mașini Windows și Mac. Acesta utilizează motorul bazei de date Microsoft Jet pentru prelucrarea și stocarea datelor. De asemenea, oferă o interfață grafică pentru utilizatori, care aproape elimină necesitatea de a înțelege limba structurată a interogărilor (SQL).

SQL este limba de comandă folosită pentru a adăuga, șterge, actualiza și returna informațiile stocate în baza de date, precum și pentru a modifica componentele bază de bază, cum ar fi adăugarea, ștergerea sau modificarea tabelelor sau a indicilor.

Punct de start

Dacă nu aveți deja o anumită familiaritate cu Access sau cu o altă RDBMS, vă sugerăm să începeți cu aceste resurse înainte de a continua:

  • Ce este o bază de date? Deci, ce este o bază de date, oricum? [Explicarea MakeUseOf] Deci ce este o bază de date, oricum? [Explică-i pe MakeUseOf] Pentru un programator sau un entuziast al tehnologiei, conceptul de bază de date este ceva care poate fi cu adevărat de acordat. Cu toate acestea, pentru mulți oameni conceptul de bază de date în sine este un pic străin ... Citește mai mult în cazul în care Ryan Dube utilizează Excel pentru a arăta elementele de bază ale bazelor de date relaționale.
  • Un ghid rapid pentru a începe cu Microsoft Access 2007 Un ghid rapid pentru a începe cu Microsoft Access 2007 Un ghid rapid pentru a începe cu Microsoft Access 2007 Read More, care este o prezentare generală la nivel înalt a accesului și a componentelor care cuprind o bază de date Access.
  • Un tutorial rapid pentru tabelele din Microsoft Access 2007 Un tutorial rapid pentru tabelele din Microsoft Access 2007 Un tutorial rapid pentru tabelele din Microsoft Access 2007 Citiți mai multe aruncă o privire la crearea primei baze de date și a tabelelor pentru a stoca datele structurate.
  • Un tutorial rapid privind interogările din Microsoft Access 2007 Un tutorial rapid privind interogările din Microsoft Access 2007 Un tutorial rapid privind interogările În Microsoft Access 2007 Citiți mai mult se uită la mijloacele de returnare a anumitor porțiuni ale datelor stocate în tabelele bazei de date.

Având o înțelegere de bază a conceptelor furnizate în aceste articole va face din urmă un pic mai ușor de digerat.

Relații de bază și normalizare

Imaginați-vă că rulați o companie care vinde 50 de tipuri diferite de widgeturi din întreaga lume. Aveți o bază de clienți de 1.250 și într-o lună medie vindeți 10.000 de widget-uri pentru acești clienți. În prezent, utilizați o singură foaie de calcul pentru a urmări toate aceste vânzări - în mod eficient o singură tabelă de baze de date. Și în fiecare an adaugă mii de rânduri în foaia de calcul.

Imaginile de mai sus fac parte din foaia de calcul pentru urmărirea comenzilor pe care o utilizați. Acum spuneți că ambii clienți cumpără widget-uri de la dvs. de mai multe ori pe an, astfel încât să aveți mult mai multe rânduri pentru ambele.

Dacă Joan Smith se căsătorește cu Ted Baines și își ia numele de familie, fiecare rând care conține numele ei trebuie schimbat acum. Problema este complicată dacă întâmpinați doi clienți diferiți cu numele "Joan Smith". A devenit mai greu să păstrați datele dvs. de vânzări consecvente, datorită unui eveniment destul de comun.

Prin utilizarea unei baze de date și a normalizării datelor, putem separa elementele în mai multe tabele, cum ar fi inventarul, clienții și comenzile.

Privind doar la partea client a exemplului nostru, am elimina coloanele pentru numele clientului și adresa clientului și le-am plasat într-un nou tabel. În imaginea de mai sus, am mai spart lucrurile mai bine pentru un acces mai granular la date. Noul tabel conține, de asemenea, o coloană pentru o cheie primară (ClientID) - un număr care va fi utilizat pentru a accesa fiecare rând din acest tabel.

În tabelul original în care am eliminat aceste date, am adăuga o coloană pentru o cheie străină (ClientID) care este legată de rândul corespunzător care conține informațiile pentru acest anumit client.

Acum, când Joan Smith își schimbă numele în Joan Baines, schimbarea trebuie făcută doar o singură dată în tabelul Client. Orice altă referință din tabele integrate va atrage numele corect al clientului și un raport care examinează ceea ce a cumpărat Joan în ultimii 5 ani va primi toate ordinele atât sub numele ei, cât și în numele căsătoriei și al căsătoriei, fără a schimba modul în care este generat raportul.

Ca un avantaj suplimentar, aceasta reduce și cantitatea totală de stocare consumată.

Alăturați-vă Tipurile

SQL definește cinci tipuri diferite de îmbinări: INNER, STÂNGA ÎNTREGUL, EXTERIORUL DREAPTA, EXTERIORUL FULL și CROSS. Cuvântul OUTER este opțional în instrucțiunea SQL.

Microsoft Access permite utilizarea INNER (implicit), LEFT OUTER, RIGHT OUTER și CROSS. FULL OUTER nu este acceptat ca atare, dar folosind LEFT OUTER, UNION ALL și RIGHT OUTER, poate fi falsificat cu costul mai multor cicluri de procesor și operațiuni I / O.

Ieșirea unei legături CROSS conține fiecare rând din tabelul stâng asociat cu fiecare rând din tabelul din dreapta. Singura dată în care am văzut vreodată o conexiune CROSS utilizată este în timpul testării încărcării serverelor de baze de date.

Să aruncăm o privire asupra modului în care funcționează punctele de bază, apoi le vom modifica pentru a ne satisface nevoile.

Să începem prin crearea a două mese, ProdA și ProdB, cu următoarele proprietăți de design.

AutoNumber este un număr întreg lung, care crește automat, alocat intrărilor în momentul în care acestea sunt adăugate în tabel. Opțiunea Text nu a fost modificată, deci va accepta un șir de text de până la 255 de caractere.

Acum, populează-le cu unele date.

Pentru a arăta diferențele în modul de funcționare a celor 3 tipuri de conexiuni, am șters intrările 1, 5 și 8 de la ProdA.

Apoi, creați o nouă interogare accesând Creați> Design interogare. Selectați ambele tabele din dialogul Afișați tabelul și faceți clic pe Adăugați, atunci Închide.

Faceți clic pe ProductID în tabelul ProdA, trageți-l în ProductID în tabelul ProdB și eliberați butonul mouse-ului pentru a crea relația dintre tabele.

Faceți clic dreapta pe linia dintre tabelele reprezentând relația dintre elementele și selectați Join Properties.

În mod implicit, este selectat tipul de intrare 1 (INNER). Opțiunea 2 este o îmbinare la ieșire din stânga și 3 este o îmbinare dreaptă.

Vom privi mai întâi INNER-ul, deci faceți clic pe OK pentru a renunța la dialog.

În designerul interogării, selectați câmpurile pe care doriți să le vedeți din listele derulante.

Când executăm interogarea (punctul de exclamare roșu din panglică), va afișa câmpul ProductName din ambele tabele cu valoarea din tabelul ProdA din prima coloană și ProdB în cea de-a doua.

Observați că rezultatele arată doar valori în care ProductID este egal în ambele tabele. Chiar dacă există o intrare pentru ProductID = 1 în tabelul ProdB, acesta nu apare în rezultate, deoarece ProductID = 1 nu există în tabelul ProdA. Același lucru este valabil și pentru ProductID = 11. Există în tabelul ProdA, dar nu în tabelul ProdB.

Prin utilizarea butonului Vizualizare de pe panglică și trecerea la Vizualizare SQL, puteți vedea interogarea SQL generată de designerul folosit pentru obținerea acestor rezultate.

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

Revenind la Design View, schimbați tipul de conectare la 2 (LEFT OUTER). Rulați interogarea pentru a vedea rezultatele.

După cum puteți vedea, fiecare intrare din tabelul ProdA este reprezentată în rezultate, în timp ce numai cele din ProdB care au o intrare de ProductID potrivită în tabelul ProdB apar în rezultate.

Spațiul gol din coloana ProdB.ProductName este o valoare specială (NULL) deoarece nu există o valoare potrivită în tabelul ProdB. Acest lucru se va dovedi important mai târziu.

SELECT ProdA.ProductName, ProdB.ProductName DE PRODA LEFT JOIN PRODB ON ProdA.ProductID = ProdB.ProductID;

Încercați același lucru cu cel de-al treilea tip de conectare (DREAPTA DREAPTA).

Rezultatele arată totul din tabelul ProdB în timp ce se afișează valori goale (cunoscute ca NULL) unde tabela ProdA nu are o valoare potrivită. Până acum, acest lucru ne aduce cel mai aproape de rezultatele dorite în întrebarea cititorului nostru.

SELECT ProdA.ProductName, ProdB.ProductName DE PRODA RIGHT JOIN PRODB ON ProdA.ProductID = ProdB.ProductID;

Utilizarea funcțiilor într-o interogare

Rezultatele unei funcții pot fi, de asemenea, returnate ca parte a unei interogări. Vrem ca o nouă coloană numită "Rezultate" să apară în setul nostru de rezultate. Valoarea sa va fi conținutul coloanei ProductName din tabelul ProdA dacă ProdA are o valoare (nu este NULL), altfel ar trebui să fie luată din tabelul ProdB.

Funcția Immediate IF (IIF) poate fi utilizată pentru a genera acest rezultat. Funcția are trei parametri. Prima este o condiție care trebuie evaluată la o valoare adevărată sau falsă. Al doilea parametru este valoarea care trebuie returnată dacă condiția este True și al treilea parametru este valoarea care trebuie returnată dacă condiția este False.

Construcția completă a funcției pentru situația noastră arată astfel:

IIF (produsul ProdAid este Null, ProdB.ProductName, ProdA.ProductName)

Observați că parametrul condiției nu verifică egalitatea. O valoare nulă într-o bază de date nu are o valoare care poate fi comparată cu orice altă valoare, inclusiv o altă valoare Null. Cu alte cuvinte, Null nu este egal cu Null. Vreodată. Pentru a trece peste acest lucru, verificăm valoarea utilizând cuvântul cheie "Is".

Am fi putut folosi și "Is Not Null" și a schimbat ordinea parametrilor True și False pentru a obține același rezultat.

Atunci când puneți acest lucru în Designerul de interogări, trebuie să introduceți întreaga funcție în câmpul Field: entry. Pentru a crea coloana "Rezultate", trebuie să utilizați un pseudonim. Pentru a face acest lucru, prefața funcția cu "Rezultate:" așa cum se vede în următoarea imagine.

Codul SQL echivalent pentru a face acest lucru ar fi:

SELECT produsul ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID este Null, ProdB.ProductName, ProdA.ProductName) Rezultate ASUPRA PRODUSULUI DREPT DE PRODUS PRODB ON ProdA.ProductID = ProdB.ProductID;

Acum, când executam această interogare, aceasta va produce aceste rezultate.

Aici vedem pentru fiecare intrare în care tabelul ProdA are o valoare, această valoare este reflectată în coloana Rezultate. Dacă nu există o intrare în tabelul ProdA, intrarea de la ProdB apare în rezultate, ceea ce este exact ceea ce cititorul nostru a cerut.

Pentru mai multe resurse pentru învățarea Microsoft Access, consultați modul de învățare al lui Joel Lee: 5 resurse gratuite online Cum să învățați accesul la Microsoft: 5 resurse gratuite online Cum să învățați accesul la Microsoft: 5 resurse gratuite online Nu trebuie să gestionați o sumă mare de date? Ar trebui să te uiți în Microsoft Access. Resursele gratuite de studiu vă pot ajuta să începeți și să învățați abilitățile pentru proiecte mai complexe. Citeste mai mult .