Ako písať dotazy Microsoft Access SQL od začiatku

Ako písať dotazy Microsoft Access SQL od začiatku

Microsoft Access je pravdepodobne najsilnejším nástrojom v celom balíku Microsoft Office, napriek tomu mystifikuje (a niekedy desí) náročných používateľov balíka Office. Ako by si mal niekto pri výraznejšej krivke učenia ako Word alebo Excel obaliť hlavu nad používaním tohto nástroja? Tento týždeň sa Bruce Epper pozrie na niektoré problémy vyvolané touto otázkou od jedného z našich čitateľov.





Čitateľ sa pýta:

Mám problémy s napísaním dotazu v programe Microsoft Access. Mám databázu s dvoma tabuľkami produktov, ktoré obsahujú spoločný stĺpec s číselným kódom produktu a priradený názov produktu. Chcem zistiť, ktoré produkty z tabuľky A je možné nájsť do tabuľky B. Chcem pridať stĺpec s názvom Výsledky, ktorý obsahuje názov produktu z tabuľky A, ak existuje, a názov produktu z tabuľky B, ak neexistuje v tabuľke A. Máte nejakú radu?



Bruceova odpoveď:

Microsoft Access je systém správy databáz (DBMS) navrhnutý na použitie na počítačoch so systémom Windows aj Mac. Na spracovanie a ukladanie údajov používa databázový stroj Jet spoločnosti Microsoft. Poskytuje tiež grafické rozhranie pre používateľov, ktoré takmer eliminuje potrebu porozumieť jazyku Structured Query Language (SQL).





SQL je príkazový jazyk používaný na pridávanie, odstraňovanie, aktualizáciu a vracanie informácií uložených v databáze, ako aj na úpravu základných komponentov databázy, ako je pridávanie, odstraňovanie alebo úprava tabuliek alebo indexov.

Štartovací bod

Ak ešte nepoznáte Access alebo iný RDBMS, navrhujem, aby ste pred pokračovaním začali s týmito zdrojmi:



Základné porozumenie konceptov uvedených v týchto článkoch uľahčí strávenie nasledujúceho.

Vzťahy s databázou a normalizácia

Predstavte si, že prevádzkujete spoločnosť predávajúcu 50 rôznych typov miniaplikácií po celom svete. Máte klientsku základňu 1 250 a v priemere za mesiac predáte týmto klientom 10 000 widgetov. Aktuálne používate na sledovanie všetkých týchto predajov jednu tabuľku - v skutočnosti jednu databázovú tabuľku. A každý rok pridá do vašej tabuľky tisíce riadkov.



Vyššie uvedené obrázky sú súčasťou používanej tabuľky sledovania objednávok. Teraz povedzte, že obaja títo klienti od vás kupujú miniaplikácie niekoľkokrát za rok, takže pre oboch máte oveľa viac riadkov.



Ak sa Joan Smith ožení s Tedom Bainesom a vezme si jeho priezvisko, teraz je potrebné zmeniť každý jeden riadok, ktorý obsahuje jej meno. Problém je znásobený, ak máte náhodou dvoch rôznych klientov s menom „Joan Smith“. Vďaka pomerne bežnej udalosti je teraz oveľa ťažšie udržať konzistentné údaje o vašich tržbách.

Použitím databázy a normalizáciou údajov môžeme položky rozdeliť do viacerých tabuliek, ako sú zásoby, klienti a objednávky.

Ak sa pozrieme na klientsku časť nášho príkladu, odstránime stĺpce pre meno klienta a adresu klienta a vložíme ich do novej tabuľky. Na obrázku vyššie som tiež lepšie rozložil veci pre granulárnejší prístup k údajom. Nová tabuľka tiež obsahuje stĺpec pre primárny kľúč (ClientID) - číslo, ktoré sa použije na prístup ku každému riadku v tejto tabuľke.

V pôvodnej tabuľke, kde sme tieto údaje odstránili, by sme pridali stĺpec pre cudzí kľúč (ClientID), ktorý odkazuje na príslušný riadok obsahujúci informácie o tomto konkrétnom klientovi.

Keď teraz Joan Smith zmení svoje meno na Joan Baines, zmenu je potrebné vykonať iba raz v klientskej tabuľke. Každá ďalšia referencia zo spojených tabuliek vytiahne správne meno klienta a správa, ktorá sa zameriava na to, čo si Joan kúpila za posledných 5 rokov, dostane všetky objednávky pod jej rodným aj ženatým menom bez toho, aby ste museli meniť spôsob generovania prehľadu. .

Ďalšou výhodou je, že sa tým zníži aj celkové množstvo spotrebovaného úložiska.

Pripojte sa k typom

SQL definuje päť rôznych typov spojení: VNÚTORNÝ, ĽAVÝ VONKAJŠÍ, PRAVÝ VONKAJŠÍ, PLNÝ VONKAJŠÍ a KRÍŽOVÝ. Kľúčové slovo OUTER je v príkaze SQL voliteľné.

Program Microsoft Access umožňuje používanie VNÚTORNÝCH (predvolené), ĽAVÉHO VONKAJŠÍCH, PRAVÝCH VONKAJŠÍCH a CROSS. FULL OUTER ako taký nie je podporovaný, ale pomocou LEFT OUTER, UNION ALL a RIGHT OUTER je možné ho falšovať za cenu väčšieho počtu cyklov CPU a vstupno -výstupných operácií.

Výstup spojenia CROSS obsahuje každý riadok ľavej tabuľky spárovaný s každým riadkom pravej tabuľky. Jediný prípad, kedy som kedy použil spojenie CROSS, je počas testovania záťaže databázových serverov.

Pozrime sa, ako fungujú základné spoje, potom ich upravíme tak, aby vyhovovali našim potrebám.

Začnime vytvorením dvoch tabuliek ProdA a ProdB s nasledujúcimi vlastnosťami návrhu.

Automatické číslo je automaticky sa zvyšujúce dlhé celé číslo priradené k položkám pri ich pridávaní do tabuľky. Možnosť Text nebola upravená, takže bude akceptovať textový reťazec až 255 znakov.

Teraz ich naplňte niekoľkými údajmi.

Aby som ukázal rozdiely v fungovaní troch typov spojenia, odstránil som položky 1, 5 a 8 z ProdA.

Ďalšie, vytvorte nový dotaz tým, že pôjdete do Vytvoriť> Návrh dotazu . Vyberte obe tabuľky v dialógovom okne Zobraziť tabuľku a kliknite na Pridať potom Zavrieť .

Kliknite na ProductID v tabuľke ProdA, presuňte ju na ProductID v tabuľke ProdB a uvoľnite tlačidlo myši, aby ste vytvorili vzťah medzi tabuľkami.

Pravým tlačidlom myši kliknite na riadok medzi tabuľkami, ktorý predstavuje vzťah medzi položkami a vyberte položku Pripojiť vlastnosti .

Štandardne je vybratý typ spojenia 1 (VNÚTORNÝ). Možnosť 2 je ĽAVÉ VONKAJŠIE spojenie a 3 je SPRÁVNE VONKAJŠIE spojenie.

Najprv sa pozrieme na VNÚTORNÉ spojenie, takže kliknutím na tlačidlo OK dialógové okno zavriete.

V návrhárovi dotazov vyberte z rozbaľovacích zoznamov polia, ktoré chceme vidieť.

Keď spustíme dotaz (červený výkričník na páse s nástrojmi), zobrazí pole ProductName z oboch tabuliek s hodnotou z tabuľky ProdA v prvom stĺpci a ProdB v druhom.

Všimnite si, že výsledky zobrazujú iba hodnoty, kde je ID produktu v oboch tabuľkách rovnaké. Aj keď existuje záznam pre ProductID = 1 v tabuľke ProdB, vo výsledkoch sa nezobrazuje, pretože ProductID = 1 v tabuľke ProdA neexistuje. To isté platí pre ProductID = 11. Existuje v tabuľke ProdA, ale nie v tabuľke ProdB.

Použitím tlačidla Zobraziť na páse s nástrojmi a prepnutím na zobrazenie SQL môžete vidieť dotaz SQL vygenerovaný dizajnérom, ktorý použil na získanie týchto výsledkov.

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

Vráťte sa do návrhového zobrazenia a zmeňte typ spojenia na 2 (Vľavo VONKA). Spustite dotaz, aby ste videli výsledky.

Ako vidíte, vo výsledkoch je reprezentovaný každý záznam v tabuľke ProdA, zatiaľ čo vo výsledkoch sa zobrazujú iba tie položky v ProdB, ktoré majú zodpovedajúci záznam ProductID v tabuľke ProdB.

Prázdne miesto v stĺpci ProdB.ProductName je špeciálna hodnota (NULL), pretože v tabuľke ProdB neexistuje zodpovedajúca hodnota. To sa neskôr ukáže ako dôležité.

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

Skúste to isté s tretím typom spojenia (SPRÁVNE VONKAJŠÍ).

Výsledky ukazujú všetko z tabuľky ProdB, zatiaľ čo ukazuje prázdne (známe ako NULL) hodnoty, kde tabuľka ProdA nemá zodpovedajúcu hodnotu. Zatiaľ sa tým dostávame najbližšie k výsledkom požadovaným v otázke nášho čitateľa.

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

Použitie funkcií v dopyte

Výsledky funkcie je možné tiež vrátiť ako súčasť dotazu. Chceme, aby sa v našej skupine výsledkov objavil nový stĺpček s názvom „Výsledky“. Jeho hodnota bude obsahom stĺpca ProductName tabuľky ProdA, ak má ProdA hodnotu (nie je NULL), v opačnom prípade by mala byť prevzatá z tabuľky ProdB.

Na vygenerovanie tohto výsledku je možné použiť funkciu Immediate IF (IIF). Funkcia má tri parametre. Prvá je podmienka, ktorá sa musí vyhodnotiť na hodnotu True alebo False. Druhý parameter je hodnota, ktorá sa má vrátiť, ak je podmienka True, a tretí parameter je hodnota, ktorá sa má vrátiť, ak je podmienka nepravdivá.

Plne funkčný konštrukt pre našu situáciu vyzerá takto:

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

Všimnite si, že parameter podmienky nekontroluje rovnosť. Hodnota Null v databáze nemá hodnotu, ktorú je možné porovnať s akoukoľvek inou hodnotou vrátane inej hodnoty Null. Inými slovami, Null sa nerovná Null. Niekedy. Aby sme to prekonali, namiesto toho skontrolujeme hodnotu pomocou kľúčového slova „Je“.

Na získanie rovnakého výsledku sme mohli použiť aj „Is Not Null“ a zmeniť poradie parametrov True a False.

Keď to zadávate do Query Designer, musíte zadať celú funkciu do položky Field :. Na vytvorenie stĺpca „Výsledky“ musíte použiť alias. Za týmto účelom predhovorte funkcii „Výsledky:“, ako je vidieť na nasledujúcom obrázku.

Ekvivalentný kód SQL, ktorý to má urobiť, by bol:

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;

Teraz, keď spustíme tento dotaz, prinesie tieto výsledky.

názov súboru je príliš dlhý na odstránenie

Tu vidíme pre každý záznam, kde tabuľka ProdA má hodnotu, je táto hodnota vyjadrená v stĺpci Výsledky. Ak v tabuľke ProdA neexistuje záznam, záznam z ProdB sa zobrazí vo výsledkoch, na čo sa presne náš čitateľ pýtal.

Ak chcete získať ďalšie zdroje informácií o programe Microsoft Access, prečítajte si článok Ako sa naučiť program Microsoft Access od Joela Leeho: 5 bezplatných online zdrojov.

zdieľam zdieľam Tweet E -mail Oplatí sa upgradovať na Windows 11?

Windows bol prepracovaný. Stačí to však na to, aby ste sa presvedčili o prechode z Windows 10 na Windows 11?

Čítajte ďalej Súvisiace témy
  • Produktivita
  • Opýtajte sa odborníkov
O autorovi Bruce Epper(13 článkov uverejnených)

Bruce sa hrá s elektronikou od 70. rokov, s počítačmi od začiatku 80. rokov a presne odpovedal na otázky o technológiách, ktoré celý čas nepoužíval ani nevidel. Otravuje sa aj tým, že sa pokúša hrať na gitare.

Viac od Brucea Eppera

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber