Jak pisać zapytania SQL programu Microsoft Access od zera

Microsoft Access jest prawdopodobnie najpotężniejszym narzędziem w całym pakiecie Microsoft Office, jednak mistyfikuje (a czasem przeraża) zaawansowanych użytkowników pakietu Office. W przypadku bardziej stromej krzywej uczenia się niż Word lub Excel, w jaki sposób ktokolwiek powinien owinąć głowę przy użyciu 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ę dowiedzieć się, 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, i 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 bazami danych (DBMS) przeznaczony do użytku zarówno na komputerach z systemem Windows, jak i Mac. Wykorzystuje silnik bazy danych Jet firmy Microsoft do przetwarzania i przechowywania danych. Zapewnia również interfejs graficzny dla użytkowników, co prawie eliminuje potrzebę zrozumienia języka zapytań strukturalnych (SQL).

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

Punkt początkowy

Jeśli jeszcze nie znasz się na Access lub innym systemie RDBMS, radzę zacząć od tych zasobów, zanim przejdziesz dalej:

• Czym jest baza danych? Czym zatem jest baza danych? [MakeUseOf wyjaśnia] Więc czym jest baza danych? [MakeUseOf wyjaśnia] Dla programisty lub entuzjasty technologii koncepcja bazy danych jest czymś, co naprawdę można uznać za coś oczywistego. Jednak dla wielu osób koncepcja samej bazy danych jest nieco obca… gdzie Ryan Dube używa programu Excel do przedstawienia podstaw relacyjnych baz danych.
• Szybki przewodnik na początek z Microsoft Access 2007 Szybki przewodnik na początek z Microsoft Access 2007 Szybki przewodnik na początek z Microsoft Access 2007, który jest ogólnym omówieniem programu Access i składników, które składają się na bazę danych Access.
• Szybki samouczek na temat tabel w programie Microsoft Access 2007 Szybki samouczek na temat tabel w programie Microsoft Access 2007 Szybki samouczek na temat tabel w programie Microsoft Access 2007 zajmuje się tworzeniem pierwszej bazy danych i tabel do przechowywania danych strukturalnych.
• Szybki samouczek na temat zapytań w programie Microsoft Access 2007 Szybki samouczek na temat zapytań w programie Microsoft Access 2007 Szybki samouczek na temat zapytań w programie Microsoft Access 2007 analizuje sposoby zwracania określonych części danych przechowywanych w tabelach bazy danych.

Podstawowa znajomość pojęć zawartych w tych artykułach sprawi, że poniższe będą nieco łatwiejsze do strawienia.

Relacje z bazami danych i normalizacja

Wyobraź sobie, że prowadzisz firmę sprzedającą 50 różnych typów widżetów na całym świecie. Posiadasz bazę klientów wynoszącą 1250 i średnio sprzedajesz tym klientom 10 000 widżetów. Obecnie używasz jednego arkusza kalkulacyjnego do śledzenia całej sprzedaży - w efekcie pojedynczej tabeli bazy danych. I co roku dodaje tysiące wierszy do arkusza kalkulacyjnego.

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

Jeśli Joan Smith poślubi Teda Bainesa i przyjmie jego nazwisko, każdy wiersz zawierający jej imię musi teraz zostać zmieniony. Problem jest spotęgowany, jeśli zdarza się, że masz dwóch różnych klientów o nazwie „Joan Smith”. Właśnie stało się o wiele trudniej utrzymać spójność danych dotyczących sprzedaży ze względu na dość częste zdarzenie.

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

Patrząc na część klienta z naszego przykładu, usuwamy kolumny Nazwa klienta i Adres klienta i umieszczamy je w nowej tabeli. Na powyższym obrazku lepiej rozłożyłem rzeczy, aby uzyskać bardziej szczegółowy dostęp do danych. Nowa tabela zawiera także kolumnę dla klucza podstawowego (ClientID) - liczby, która będzie używana do uzyskania dostępu do każdego wiersza w tej tabeli.

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

Teraz, kiedy Joan Smith zmienia swoje imię na Joan Baines, zmiana musi być dokonana tylko raz w tabeli Klientów. Każde inne odniesienie z połączonych tabel pobierze odpowiednią nazwę klienta, a raport, który analizuje to, co Joan kupiła przez ostatnie 5 lat, otrzyma wszystkie zamówienia zarówno pod nazwami panieńskimi, jak i małżeńskimi, bez konieczności zmiany sposobu generowania raportu.

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

Połącz typy

SQL definiuje pięć różnych typów złączeń: WEWNĘTRZNY, LEWY ZEWNĘTRZNY, PRAWY ZEWNĘTRZNY, PEŁNY ZEWNĘTRZNY i KRZYŻ. Słowo kluczowe OUTER jest opcjonalne w instrukcji SQL.

Microsoft Access pozwala na użycie WEWNĘTRZNEJ (domyślnie), LEWEGO ZEWNĘTRZNEGO, PRAWEGO ZEWNĘTRZNEGO i KRZYŻOWEGO. PEŁNY ZEWNĘTRZNY 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 I / O.

Wynik połączenia krzyżowego zawiera każdy wiersz lewej tabeli sparowany z każdym rzędem prawej tabeli. Jedyny raz, kiedy widziałem połączenie CROSS, jest podczas testowania obciążenia serwerów baz danych.

Przyjrzyjmy się, jak działają podstawowe sprzężenia, a następnie zmodyfikujemy je zgodnie z naszymi potrzebami.

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

AutoNumber to automatycznie zwiększająca się długa liczba całkowita przypisywana do wpisów dodawanych do tabeli. Opcja Tekst nie została zmodyfikowana, więc będzie akceptować ciąg tekstowy o długości do 255 znaków.

Teraz zapełnij je pewnymi danymi.

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

Następnie utwórz nowe zapytanie, przechodząc do Utwórz> Projekt zapytania. Wybierz obie tabele z okna dialogowego 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ącymi relacje między elementami a wybierz Połącz właściwości.

Domyślnie wybrany jest typ łączenia 1 (WEWNĘTRZNY). Opcja 2 to lewe połączenie zewnętrzne, a 3 to prawe połączenie zewnętrzne.

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

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

Kiedy uruchomimy zapytanie (czerwony wykrzyknik na wstążce), pokaże pole ProductName z obu tabel z wartością z tabeli ProdA w pierwszej kolumnie i ProdB w drugiej.

Zauważ, że wyniki pokazują tylko wartości, w których identyfikator produktu jest równy w obu tabelach. Mimo że w tabeli ProdB znajduje się pozycja ProductID = 1, nie pojawia się ona w wynikach, ponieważ ProductID = 1 nie istnieje w tabeli ProdA. To samo dotyczy ProductID = 11. Istnieje w tabeli ProdA, ale nie w tabeli ProdB.

Korzystając z przycisku Widok na wstążce i przechodząc do widoku SQL, możesz zobaczyć zapytanie SQL wygenerowane przez projektanta użyte do uzyskania tych wyników.

WYBIERZ ProdA.ProductName, ProdB.ProductName OD PRODUA INNER DOŁĄCZ DO ProdB ON ProdA.ProductID = ProdB.ProductID;

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

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

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

WYBIERZ ProdA.ProductName, ProdB.ProductName OD PRODUA LEWE DOŁĄCZ ProdB ON ProdA.ProductID = ProdB.ProductID;

Spróbuj tego samego z trzecim rodzajem złączenia (PRAWY ZEWNĘTRZNY).

Wyniki pokazują wszystko z tabeli ProdB, gdy pokazuje puste (znane jako NULL) wartości, w przypadku 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.

WYBIERZ ProdA.ProductName, ProdB.ProductName FROM ProdA PRAWE DOŁĄCZ 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 jest NULL), w przeciwnym razie należy ją pobrać z tabeli ProdB.

Do wygenerowania tego wyniku można użyć funkcji Immediate IF (IIF). Funkcja przyjmuje trzy parametry. Pierwszy to warunek, który musi zostać oceniony na wartość Prawda lub Fałsz. Drugi parametr to wartość zwracana, jeśli warunek ma wartość True, a trzeci parametr to wartość zwracana, jeśli warunek ma wartość False.

Pełna konstrukcja funkcji dla naszej sytuacji wygląda następująco:

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 dowolną inną wartością, w tym inną Null. Innymi słowy, wartość Null nie jest równa Null. Zawsze. Aby temu zaradzić, zamiast tego sprawdzamy wartość za pomocą słowa kluczowego „Is”.

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

Umieszczając to w Query Designer, musisz wpisać całą funkcję w polu Field:. Aby utworzyć kolumnę „Wyniki”, musisz użyć aliasu. Aby to zrobić, należy poprzedzić tę funkcję słowem „Wyniki:”, jak pokazano na poniższym zrzucie ekranu.

Odpowiednikiem tego kodu SQL byłoby:

WYBIERZ ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) JAK WYNIKI Z PRAWEJ DOŁĄCZenia ProdA NA PRODUA.ProductID = ProdB.ProductID;

Teraz, gdy uruchomimy to zapytanie, wygeneruje te wyniki.

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

Aby uzyskać więcej zasobów do nauki Microsoft Access, sprawdź Joel Lee Jak nauczyć się Microsoft Access: 5 darmowych zasobów online Jak nauczyć się Microsoft Access: 5 bezpłatnych zasobów online Jak nauczyć się Microsoft Access: 5 bezpłatnych zasobów online Czy musisz zarządzać dużą ilością danych? Powinieneś zajrzeć do Microsoft Access. Nasze bezpłatne zasoby do nauki mogą pomóc w rozpoczęciu nauki i zdobyciu umiejętności w zakresie bardziej złożonych projektów. .