Jak psát Microsoft Access SQL dotazy od nuly

Jak psát Microsoft Access SQL dotazy od nuly

Microsoft Access je pravděpodobně nejmocnějším nástrojem v celé sadě Microsoft Office, přesto mystifikuje (a někdy děsí) uživatele Office. Jak by si měl někdo s použitím strmější křivky učení než Word nebo Excel omotat hlavu nad používáním tohoto nástroje? Tento týden se Bruce Epper podívá na některá z problémů vyvolaných touto otázkou od jednoho z našich čtenářů.





Čtenář se ptá:

Mám potíže při psaní dotazu v aplikaci Microsoft Access. Mám databázi se dvěma tabulkami produktů, které obsahují společný sloupec s číselným kódem produktu a přidruženým názvem produktu. Chci zjistit, které produkty z tabulky A lze nalézt do tabulky B. Chci přidat sloupec s názvem Results, který obsahuje název produktu z tabulky A, pokud existuje, a název produktu z tabulky B, pokud neexistuje v tabulce A. Máte nějakou radu?



Bruceova odpověď:

Microsoft Access je systém pro správu databází (DBMS) navržený pro použití na počítačích se systémem Windows i Mac. Ke zpracování a ukládání dat využívá databázový stroj Jet společnosti Microsoft. Poskytuje také grafické rozhraní pro uživatele, které téměř eliminuje potřebu porozumět strukturovanému dotazovacímu jazyku (SQL).





SQL je příkazový jazyk používaný k přidávání, odstraňování, aktualizaci a vracení informací uložených v databázi a také k úpravě základních databázových komponent, jako je přidávání, odstraňování nebo úpravy tabulek nebo indexů.

Výchozí bod

Pokud ještě nemáte nějaké znalosti o Accessu nebo jiném RDBMS, doporučuji vám začít s těmito prostředky, než budete pokračovat:



Základní porozumění pojmům uvedeným v těchto článcích usnadní následující přehled.

Vztahy s databází a normalizace

Představte si, že provozujete společnost prodávající 50 různých typů widgetů po celém světě. Máte klientskou základnu 1 250 a za průměrný měsíc prodáte 10 000 widgetů těmto klientům. Aktuálně používáte jedinou tabulku ke sledování všech těchto prodejů - efektivně jedinou databázovou tabulku. A každý rok přidá do vaší tabulky tisíce řádků.



Výše uvedené obrázky jsou součástí tabulky pro sledování objednávek, kterou používáte. Nyní řekněte, že oba tito klienti od vás nakupují widgety několikrát ročně, takže pro oba máte mnohem více řádků.



Pokud si Joan Smith vezme Teda Bainese a vezme si jeho příjmení, je nyní třeba změnit každý řádek, který obsahuje její jméno. Problém nastává, pokud máte náhodou dva různé klienty se jménem „Joan Smith“. Kvůli poměrně běžné události je nyní mnohem těžší udržet konzistentní údaje o vašich prodejích.

Použitím databáze a normalizací dat můžeme rozdělit položky do více tabulek, jako jsou zásoby, klienti a objednávky.

Při pohledu na klientskou část našeho příkladu bychom odstranili sloupce pro jméno klienta a adresu klienta a vložili je do nové tabulky. Na výše uvedeném obrázku jsem také lépe rozbil věci pro podrobnější přístup k datům. Nová tabulka také obsahuje sloupec pro primární klíč (ClientID) - číslo, které bude použito pro přístup ke každému řádku v této tabulce.

V původní tabulce, kde jsme tato data odstranili, bychom přidali sloupec pro cizí klíč (ClientID), který odkazuje na správný řádek obsahující informace o tomto konkrétním klientovi.

Když nyní Joan Smith změní své jméno na Joan Baines, změnu je třeba provést pouze jednou v tabulce klientů. Každá další reference ze spojených tabulek vytáhne správné jméno klienta a zpráva, která se zaměřuje na to, co si Joan koupila za posledních 5 let, získá všechny objednávky pod jejím rodným i vdaným jménem, ​​aniž by bylo nutné měnit způsob generování přehledu. .

Další výhodou je také snížení celkového množství spotřebovaného úložiště.

Připojte se k typům

SQL definuje pět různých typů spojení: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER a CROSS. Klíčové slovo OUTER je v příkazu SQL volitelné.

Microsoft Access umožňuje použití VNITŘNÍ (výchozí), VLEVO VENKU, VPRAVO VNĚJŠÍ a KŘÍŽE. FULL OUTER jako takový není podporován, ale pomocí LEFT OUTER, UNION ALL a RIGHT OUTER může být padělaný za cenu více cyklů CPU a I/O operací.

Výstup CROSS join obsahuje každý řádek levé tabulky spárovaný s každým řádkem pravé tabulky. Jediný případ, kdy jsem CROSS join použil, je během zátěžového testování databázových serverů.

Podívejme se na to, jak fungují základní spoje, poté je upravíme tak, aby vyhovovaly našim potřebám.

Začněme vytvořením dvou tabulek ProdA a ProdB s následujícími vlastnostmi návrhu.

Automatické číslo je automaticky přírůstkové dlouhé celé číslo přiřazené k položkám při jejich přidání do tabulky. Možnost Text nebyla změněna, takže bude přijímat textový řetězec dlouhý až 255 znaků.

Nyní je naplňte některými daty.

Abych ukázal rozdíly ve fungování tří typů spojení, odstranil jsem položky 1, 5 a 8 z ProdA.

Další, vytvořit nový dotaz tím, že jde do Vytvořit> Návrh dotazu . Vyberte obě tabulky v dialogu Zobrazit tabulku a klikněte na Přidat , pak Zavřít .

Klikněte na ProductID v tabulce ProdA, přetáhněte ji na ProductID v tabulce ProdB a uvolněním tlačítka myši vytvořte vztah mezi tabulkami.

Klikněte pravým tlačítkem na řádek mezi tabulkami představujícími vztah mezi položkami a vyberte Připojit vlastnosti .

Ve výchozím nastavení je vybrán typ spojení 1 (VNITŘNÍ). Možnost 2 je LEVÉ VNĚJŠÍ spojení a 3 je PRAVÉ VNĚJŠÍ spojení.

Nejprve se podíváme na VNITŘNÍ spojení, takže kliknutím na OK dialog zavřete.

V návrháři dotazů vyberte z rozevíracích seznamů pole, která chceme vidět.

Když spustíme dotaz (červený vykřičník na pásu karet), zobrazí pole ProductName z obou tabulek s hodnotou z tabulky ProdA v prvním sloupci a ProdB ve druhém.

Všimněte si, že výsledky zobrazují pouze hodnoty, kde ID produktu je v obou tabulkách stejné. I když je v tabulce ProdB záznam pro ProductID = 1, ve výsledcích se nezobrazuje, protože ProductID = 1 v tabulce ProdA neexistuje. Totéž platí pro ProductID = 11. Existuje v tabulce ProdA, ale ne v tabulce ProdB.

Pomocí tlačítka Zobrazit na pásu karet a přepnutí na zobrazení SQL můžete zobrazit dotaz SQL vygenerovaný návrhářem použitým k získání těchto výsledků.

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

Vraťte se zpět do návrhového zobrazení a změňte typ spojení na 2 (VLEVO VNĚJŠÍ). Výsledky zobrazíte spuštěním dotazu.

Jak vidíte, ve výsledcích je zastoupen každý záznam v tabulce ProdA, zatímco ve výsledcích se zobrazují pouze ty v ProdB, které mají odpovídající položku ProductID v tabulce ProdB.

Prázdné místo ve sloupci ProdB.ProductName je speciální hodnota (NULL), protože v tabulce ProdB neexistuje odpovídající hodnota. To se později ukáže jako důležité.

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

Zkuste to samé s třetím typem spojení (PRAVĚ VNĚJŠÍ).

Výsledky ukazují vše z tabulky ProdB, zatímco zobrazuje prázdné (známé jako NULL) hodnoty, kde tabulka ProdA nemá odpovídající hodnotu. Zatím se tím dostáváme nejblíže k výsledkům požadovaným v otázce našeho čtenáře.

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

Použití funkcí v dotazu

Výsledky funkce mohou být také vráceny jako součást dotazu. Chceme, aby se v naší sadě výsledků objevil nový sloupec s názvem „Výsledky“. Jeho hodnota bude obsahem sloupce ProductName tabulky ProdA, pokud ProdA má hodnotu (není NULL), jinak by měla být převzata z tabulky ProdB.

K vygenerování tohoto výsledku lze použít funkci Immediate IF (IIF). Funkce má tři parametry. První je podmínka, která musí být vyhodnocena na hodnotu True nebo False. Druhý parametr je hodnota, která má být vrácena, pokud je podmínka True, a třetí parametr je hodnota, která má být vrácena, pokud je podmínka False.

Konstrukce plné funkce pro naši situaci vypadá takto:

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

Všimněte si, že parametr podmínky nekontroluje rovnost. Hodnota Null v databázi nemá hodnotu, kterou lze porovnat s jakoukoli jinou hodnotou, včetně jiné hodnoty Null. Jinými slovy, Null nerovná se Null. Vůbec. Abychom toho dosáhli, místo toho zkontrolujeme hodnotu pomocí klíčového slova 'Is'.

Mohli jsme také použít 'Is Not Null' a změnit pořadí parametrů True a False, abychom získali stejný výsledek.

Když to zadáváte do Query Designer, musíte zadat celou funkci do položky Field :. Abyste mohli vytvořit sloupec „Výsledky“, musíte použít alias. Chcete -li to provést, předmluvte funkci „Výsledky:“, jak je vidět na následujícím snímku obrazovky.

Ekvivalentní kód SQL, který to má udělat, by byl:

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;

Nyní, když spustíme tento dotaz, vytvoří tyto výsledky.

jak odstranit profil na netflixu

Zde vidíme u každého záznamu, kde má tabulka ProdA hodnotu, se tato hodnota projeví ve sloupci Výsledky. Pokud v tabulce ProdA neexistuje položka, položka z ProdB se objeví ve výsledcích, což je přesně to, co se náš čtenář zeptal.

Další zdroje pro výuku aplikace Microsoft Access najdete v článku Jak se naučit Microsoft Access od Joela Lee: 5 bezplatných online zdrojů.

Podíl Podíl tweet E-mailem Vyplatí se upgradovat na Windows 11?

Windows byl přepracován. Ale stačí to k tomu, abyste vás přesvědčili o přechodu z Windows 10 na Windows 11?

Číst dále Související témata
  • Produktivita
  • Zeptejte se odborníků
O autorovi Bruce Epper(13 článků zveřejněno)

Bruce si hraje s elektronikou od 70. let, s počítači od počátku 80. let a přesně odpovídá na otázky o technologii, kterou po celou dobu nepoužíval ani neviděl. Také se obtěžuje pokusem hrát na kytaru.

Více od Bruce Eppera

Přihlaste se k odběru našeho zpravodaje

Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!

Kliknutím sem se přihlásíte k odběru