Jak pisać zapytania SQL programu Microsoft Access od podstaw

Jak pisać zapytania SQL programu Microsoft Access od podstaw

Microsoft Access jest prawdopodobnie najpotężniejszym narzędziem w całym pakiecie Microsoft Office, ale zadziwia (a czasem przeraża) zaawansowanych użytkowników pakietu Office. Mając bardziej stromą krzywą uczenia się niż Word lub Excel, jak ktoś ma owinąć głowę wokół korzystania z tego narzędzia? W tym tygodniu Bruce Epper przyjrzy się niektórym problemom wywołanym tym pytaniem jednego z naszych czytelników.





Czytelnik pyta:

Mam problem z napisaniem zapytania w programie Microsoft Access.Mam bazę danych z dwiema tabelami produktów zawierającymi wspólną kolumnę z numerycznym kodem produktu i powiązaną nazwą produktu.Chcę się dowiedzieć, które produkty z tabeli A można znaleźć w Tabeli B. Chcę dodać kolumnę o nazwie Wyniki, która zawiera nazwę produktu z Tabeli A, jeśli istnieje, oraz nazwę produktu z Tabeli B, jeśli nie istnieje w Tabeli A. Czy masz jakieś rady?



Odpowiedź Bruce'a:

Microsoft Access to system zarządzania bazą danych (DBMS) przeznaczony do użytku na komputerach z systemem Windows i Mac. Wykorzystuje silnik bazy danych Jet firmy Microsoft do przetwarzania i przechowywania danych. Zapewnia również graficzny interfejs dla użytkowników, który prawie eliminuje potrzebę zrozumienia strukturalnego języka zapytań (SQL).





SQL to język poleceń używany do dodawania, usuwania, aktualizowania i zwracania informacji przechowywanych w bazie danych, a także do modyfikowania podstawowych komponentów bazy danych, takich jak dodawanie, usuwanie lub modyfikowanie tabel lub indeksów.

Punkt początkowy

Jeśli nie znasz jeszcze programu Access lub innego RDBMS, sugeruję, abyś zaczął od tych zasobów przed kontynuowaniem:



Podstawowe zrozumienie pojęć zawartych w tych artykułach sprawi, że poniższe informacje będą nieco łatwiejsze do przyswojenia.

Relacje i normalizacja bazy danych

Wyobraź sobie, że prowadzisz firmę sprzedającą 50 różnych rodzajów widżetów na całym świecie. Masz bazę klientów liczącą 1250 osób i średnio w miesiącu sprzedajesz tym klientom 10 000 widżetów. Obecnie do śledzenia wszystkich tych sprzedaży używasz jednego arkusza kalkulacyjnego — w rzeczywistości pojedynczej tabeli bazy danych. Co roku dodaje do arkusza kalkulacyjnego tysiące wierszy.



Powyższe obrazy są częścią używanego arkusza kalkulacyjnego śledzenia zamówień. Teraz powiedzmy, że obaj ci klienci kupują od Ciebie widżety kilka razy w roku, więc masz znacznie więcej wierszy dla nich obu.



Jeśli Joan Smith poślubi Teda Bainesa i przyjmie jego nazwisko, każdy wiersz zawierający jej imię musi zostać zmieniony. Problem komplikuje się, jeśli masz dwóch różnych klientów o nazwisku „Joan Smith”. Utrzymanie spójności danych sprzedażowych stało się o wiele trudniejsze z powodu dość częstego zdarzenia.

Korzystając z bazy danych i normalizując dane, możemy rozdzielić pozycje na wiele tabel, takich jak zapasy, klienci i zamówienia.

Po prostu patrząc na część naszego przykładu dotyczącą klienta, usuniemy kolumny dla nazwy klienta i adresu klienta i umieścimy je w nowej tabeli. Na powyższym obrazku lepiej podzieliłem również rzeczy, aby uzyskać bardziej szczegółowy dostęp do danych. Nowa tabela zawiera również kolumnę klucza podstawowego (identyfikator klienta) — numer, który będzie używany do uzyskiwania dostępu do każdego wiersza w tej tabeli.

W oryginalnej tabeli, z której usunęliśmy te dane, dodamy kolumnę dla klucza obcego (ClientID), która jest linkiem do odpowiedniego wiersza zawierającego informacje dla tego konkretnego klienta.

Teraz, gdy Joan Smith zmienia imię na Joan Baines, zmiana musi być dokonana tylko raz w tabeli Klient. Każde inne odniesienie z połączonych tabel spowoduje wyświetlenie prawidłowej nazwy klienta, a raport, który analizuje, co Joanna kupiła przez ostatnie 5 lat, otrzyma wszystkie zamówienia pod jej nazwiskami panieńskimi i żonatymi bez konieczności zmiany sposobu generowania raportu .

Dodatkową korzyścią jest to, że zmniejsza to również całkowitą ilość zużywanej pamięci.

Dołącz typy

SQL definiuje pięć różnych typów złączeń: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER i CROSS. Słowo kluczowe OUTER jest opcjonalne w instrukcji SQL.

Microsoft Access pozwala na użycie INNER (domyślnie) , LEFT OUTER, RIGHT OUTER i CROSS. FULL OUTER jako taki nie jest obsługiwany, ale używając LEFT OUTER, UNION ALL i RIGHT OUTER, można go sfałszować kosztem większej liczby cykli procesora i operacji we/wy.

Wyjście złączenia CROSS zawiera każdy wiersz lewej tabeli sparowany z każdym wierszem prawej tabeli. Jedyny raz, kiedy widziałem używane złącze CROSS, to podczas testowania obciążenia serwerów bazodanowych.

Przyjrzyjmy się, jak działają podstawowe złączenia, a następnie zmodyfikujemy je do własnych potrzeb.

Zacznijmy od utworzenia dwóch tabel, ProdA i ProdB, o następujących właściwościach projektowych.

Autonumerowanie to automatycznie zwiększająca się długa liczba całkowita przypisywana do wpisów podczas ich dodawania do tabeli. Opcja Tekst nie została zmodyfikowana, więc akceptuje ciąg tekstowy o długości do 255 znaków.

Teraz wypełnij je danymi.

Aby pokazać różnice w działaniu 3 typów złączeń, usunąłem wpisy 1, 5 i 8 z programu ProdA.

Następny, utwórz nowe zapytanie idąc do Utwórz > Projekt zapytania . Wybierz obie tabele w oknie dialogowym Pokaż tabelę i kliknij Dodaj , następnie Blisko .

Kliknij ProductID w tabeli ProdA, przeciągnij go do ProductID w tabeli ProdB i zwolnij przycisk myszy, aby utworzyć relację między tabelami.

Kliknij prawym przyciskiem myszy linię między tabelami reprezentującą relację między pozycjami i wybierz opcję Dołącz do właściwości .

Domyślnie wybrany jest typ łączenia 1 (INNER). Opcja 2 to złącze LEFT OUTER, a 3 to złącze RIGHT OUTER.

Najpierw przyjrzymy się połączeniu INNER, więc kliknij OK, aby zamknąć okno dialogowe.

W projektancie zapytań wybierz z list rozwijanych pola, które chcemy zobaczyć.

Po uruchomieniu zapytania (czerwony wykrzyknik na wstążce) zostanie wyświetlone pole ProductName z obu tabel z wartością z tabeli ProdA w pierwszej kolumnie i ProdB w drugiej.

Zwróć uwagę, że wyniki pokazują tylko wartości, w których ProductID jest równy w obu tabelach. Mimo że istnieje wpis dla ProductID = 1 w tabeli ProdB, nie pojawia się on w wynikach, ponieważ ProductID = 1 nie istnieje w tabeli ProdA. To samo dotyczy ProductID = 11. Istnieje w tabeli ProdA, ale nie w tabeli ProdB.

Używając przycisku Widok na wstążce i przełączając się do widoku SQL, możesz zobaczyć zapytanie SQL wygenerowane przez projektanta użytego do uzyskania tych wyników.

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

Wracając do widoku projektu, zmień typ połączenia na 2 (LEWO ZEWNĘTRZNE). Uruchom zapytanie, aby zobaczyć wyniki.

Jak widać, każdy wpis w tabeli ProdA jest reprezentowany w wynikach, podczas gdy w wynikach pojawiają się tylko te w ProdB, które mają pasujący wpis ProductID w tabeli ProdB.

Puste miejsce w kolumnie ProdB.ProductName jest wartością specjalną (NULL), ponieważ nie ma pasującej wartości w tabeli ProdB. To okaże się ważne później.

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

Wypróbuj to samo z trzecim typem sprzężenia (RIGHT OUTER).

Wyniki pokazują wszystko z tabeli ProdB, podczas gdy pokazuje puste (znane jako NULL) wartości, w których tabela ProdA nie ma pasującej wartości. Jak dotąd zbliża nas to do rezultatów pożądanych w pytaniu naszego czytelnika.

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

Używanie funkcji w zapytaniu

Wyniki funkcji mogą być również zwracane jako część zapytania. Chcemy, aby w naszym zestawie wyników pojawiła się nowa kolumna o nazwie „Wyniki”. Jego wartością będzie zawartość kolumny ProductName tabeli ProdA, jeśli ProdA ma wartość (nie ma wartości NULL), w przeciwnym razie należy ją pobrać z tabeli ProdB.

Do wygenerowania tego wyniku można użyć funkcji Natychmiastowe JEŻELI (IIF). Funkcja przyjmuje trzy parametry. Pierwszy to warunek, którego wartością musi być wartość True lub False. Drugi parametr to wartość, która zostanie zwrócona, jeśli warunek jest prawdziwy, a trzeci parametr to wartość, która zostanie zwrócona, jeśli warunek jest fałszywy.

Pełna konstrukcja funkcji dla naszej sytuacji wygląda tak:

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

Zauważ, że parametr warunku nie sprawdza równości. Wartość Null w bazie danych nie ma wartości, którą można porównać z żadną inną wartością, w tym z inną wartością Null. Innymi słowy, Null nie równa się Null. Kiedykolwiek. Aby ominąć ten problem, zamiast tego sprawdzamy wartość za pomocą słowa kluczowego „Is”.

Mogliśmy również użyć „Is Not Null” i zmienić kolejność parametrów Prawda i Fałsz, aby uzyskać ten sam wynik.

Umieszczając to w Projektancie zapytań, musisz wpisać całą funkcję w polu Pole: wpis. Aby zmusić go do utworzenia kolumny „Wyniki”, musisz użyć aliasu. Aby to zrobić, poprzedź funkcję słowem „Wyniki:”, jak pokazano na poniższym zrzucie ekranu.

Równoważny kod SQL, który to zrobi, to:

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, gdy uruchomimy to zapytanie, przyniesie te wyniki.

jak uzyskać dostęp do poczty e-mail z programu Outlook?

Tutaj widzimy dla każdego wpisu, w którym tabela ProdA ma wartość, ta wartość jest odzwierciedlona w kolumnie Wyniki. Jeśli nie ma wpisu w tabeli ProdA, wpis z ProdB pojawia się w wynikach, o co dokładnie pytał nasz czytelnik.

Aby uzyskać więcej zasobów dotyczących nauki Microsoft Access, zapoznaj się z artykułem Joela Lee How to Learn Microsoft Access: 5 bezpłatnych zasobów online .

Udział Udział Ćwierkać E-mail Czy warto uaktualnić do systemu Windows 11?

Windows został przeprojektowany. Ale czy to wystarczy, aby przekonać Cię do przejścia z Windows 10 na Windows 11?

Czytaj dalej Powiązane tematy
  • Wydajność
  • Zapytaj ekspertów
O autorze Bruce Epper(13 opublikowanych artykułów)

Bruce bawi się elektroniką od lat 70., komputerami od wczesnych lat 80. i trafnie odpowiada na pytania dotyczące technologii, których przez cały czas nie używał i nie widział. On też denerwuje się, próbując grać na gitarze.

Więcej od Bruce'a Eppera

Zapisz się do naszego newslettera

Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!

Kliknij tutaj, aby zasubskrybować