Opis użycia sprzężeń w programie Microsoft Query

Co to jest sprzężenie?

Sprzężenie to połączenie między dwiema tabelami, w których dwie tabele są scalane zgodnie z polami, które są wspólne, tworząc nową tabelę wirtualną (którą można zapisać jako tabelę rzeczywistą). Na przykład z dwiema poniższymi tabelami: Color_Table: Join_Field Color_Field 1 Red 2 Blue 3 Green Pattern_Table: Join_Field Pattern_Field 2 Striped 3 Checkered 4 Polka-Dot proste sprzężenie wygląda podobnie do następujących: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered Tabela wyników zawiera tylko rekordy 2 i 3 w polu Join_Field, ponieważ są one jedynymi rekordami, które istnieją zarówno w Color_Table, jak i w Pattern_Table. Praktycznym przykładem połączenia jest wykaz produktów i producentów detalisty; tabela Produkty i tabela dostawcy mogą być sprzężone w polu Identyfikator produktu.

Sprzężenie wewnętrzne

Sprzężenie w poprzednim przykładzie, nazywane sprzężeniem wewnętrznym, to najprostszy typ sprzężenia. Zwykle w tabelach należy użyć tylko ułamka pól. Na przykład w przypadku rzeczywistych sprzężeń wewnętrznych, takich jak powyższe, warto wykluczyć Join_Field, aby sprzężenie wyglądało następująco: Color_Field Pattern_Field Blue Striped Green Checkered W programie Microsoft Query sprzężenia wewnętrzne są domyślnym typem sprzężenia (Aby uzyskać więcej informacji, zobacz stronę 105 w podręczniku użytkownika programu Microsoft Query, wersja 1,0).

Instrukcja SQL

Instrukcja SELECT języka Structured Query Language (SQL) jest typem makra, którego można używać podczas tworzenia sprzężenia. Zauważ, że język SQL jest bardzo różny od innych języków makr programu Microsoft Excel (Visual Basic for Applications i języka makr programu Excel 4,0). NIE jest konieczne zrozumienie języka SQL w celu łatwego tworzenia sprzężeń w programie Microsoft Query. Z każdym sprzężeniem jest skojarzona instrukcja SELECT. Instrukcję SELECT można wyświetlić w przypadku dołączania do programu Microsoft Query, klikając pozycję "SQL" na pasku narzędzi. Podobnie jak w przypadku rejestratora makr programu Microsoft Excel, możesz użyć kwerendy, aby nagrać instrukcję SELECT. W przeciwieństwie do rejestratora makr programu Microsoft Excel Rejestrator wybierania instrukcji jest zawsze włączony i nie można go wyłączyć. Oto sposób, w jaki instrukcja SELECT może wyglądać w programie Microsoft Query w przypadku powyższego sprzężenia wewnętrznego: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table WHERE Color_Table.Join_Field = Pattern_Table.Join_Field Pamiętaj, że używamy nazwy bazy danych "C:\database", której plik mdb to rozszerzenie nazwy pliku dla bazy danych programu Microsoft Access, która może zawierać wiele tabel w jednym pliku. W przypadku innych baz danych, takich jak dBASE, Paradox i FoxPro, każda tabela musi mieć własny plik. W takich przypadkach składnia SQL może być wyświetlana nadmiarowa, ponieważ nazwa tabeli jest zawsze taka sama jak nazwa pliku bez rozszerzenia. Składnia SQL jest różna między aparatami zapytań; na przykład w programie Microsoft Access kwerenda z powyższego przykładu jest podobna do następującej: SELECT Color_Table.[Color_Field], Pattern_Table.Pattern_Field FROM Pattern_Table INNER JOIN Color_Table ON Pattern_Table.[Join_Field] = Color_Table.[Join_Field]; Ścieżka do tabeli nie jest używana w programie Microsoft Access, ponieważ jest ona w pliku mdb programu Microsoft Access. Nawet jeśli tabela zewnętrzna jest dołączona i używana w kwerendzie, instrukcja SQL programu Microsoft Access nie wyświetla ścieżki do tabeli zewnętrznej.

Sprzężenie zewnętrzne

Inny rodzaj sprzężenia nosi nazwę sprzężenia zewnętrznego. Sprzężenie zewnętrzne umożliwia uzyskiwanie wszystkich rekordów z jednej tabeli i tylko tych rekordów z drugiej tabeli, które mają pasujące wartości z pierwszej tabeli. Może to spowodować pozostawienie pustego pola lub wartości "null". W przypadku dołączenia dwóch tabel, które można dołączyć, są dwa możliwe sprzężenia zewnętrzne, "lewe sprzężenie zewnętrzne" oraz "prawe sprzężenie zewnętrzne" (tak samo, że tabele są zwykle wyświetlane obok siebie). Używając dwóch poprzednich tabel w przykładzie, poniżej przedstawiono jedno z dwóch możliwych sprzężeń zewnętrznych: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Oto inne możliwe dołączenie: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Uwaga: w sprzężeniu nie widzisz słowa "NULL" podczas wyświetlania danych; Użyj słowa kluczowego "NULL" podczas pracy z sprzężeniami. W programie Microsoft Query można łatwo utworzyć oba rodzaje sprzężenia zewnętrznego, korzystając z myszy (Aby uzyskać więcej informacji na temat tej procedury, zobacz stronę 112 "podręcznika użytkownika programu Microsoft Query", wersja 1,0). Poniżej przedstawiono, jak instrukcja SQL może szukać drugiego przykładu sprzężenia zewnętrznego: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} Aby utworzyć praktyczny przykład sprzężenia zewnętrznego, należy utworzyć listę produktów firmy z wartościami sprzedaży dla produktów, które zostały sprzedane, ale nie z wyjątkiem produktów, które nie zostały sprzedane. Aby to zrobić, użyj pola Identyfikator produktu w celu dołączenia do tabeli Products and w tabeli Sales.

Sprzężenie odejmowania

Trzecim rodzajem sprzężenia jest sprzężenie odejmowania. Sprzężenie odejmowania jest odwrotne do sprzężenia zewnętrznego; zawiera tylko te rekordy z jednej tabeli, które nie pasują do żadnego rekordu w drugiej tabeli. Podobnie jak sprzężenia zewnętrzne, istnieją dwie możliwe sprzężenia odejmowania dla dowolnych dwóch tabel, do których chcesz dołączyć. nie są jednak zwykle określane jako "sprzężenie lewostronne" lub "przyłączenie do odejmowania". Sprzężenie odejmowania zwykle zwraca pola pochodzące tylko z jednej z tabel, ponieważ według definicji pola drugiej tabeli zwracają tylko wartości NULL. Poniżej przedstawiono jedno możliwe sprzężenie odejmowania: join_Field Color_Field 1 Red Oto inny sposób: Join_Field Pattern_Field 4 Polka-Dot W programie Microsoft Query sprzężenie odejmowania jest tworzone najpierw przez utworzenie sprzężenia zewnętrznego, a następnie użycie kryteriów "jest NULL" na odpowiednim polu (Pattern_Field w pierwszym przykładzie powyżej; W drugim przykładzie Color_Field, aby wykluczyć rekordy, które pasują do tabel. Poniżej przedstawiono sposób, w jaki instrukcja SQL może wyszukać pierwsze sprzężenie odejmowania: SELECT Color_Table.Join_Field, Color_Table.Color_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Pattern_Table.Pattern_Field Is Null) Aby utworzyć praktyczny przykład sprzężenia odejmowania, listę klientów, którzy nie zamówili się ostatnio. Aby to zrobić, użyj pola Identyfikator zamówienia w celu dołączenia do tabeli Klienci i tabeli Orders (zamówienia).

Pełne sprzężenie zewnętrzne

W przypadku pełnego sprzężenia zewnętrznego jest to sprzężenie czwarte. Pełne sprzężenie zewnętrzne to połączenie sprzężenia zewnętrznego ze swoim dołączaniem do odejmowania. Pełne sprzężenie zewnętrzne zawiera wszystkie rekordy z obu tabel i scala te rekordy, które są wspólne dla tych dwóch tabel. Poniżej przedstawiono pełne sprzężenie zewnętrzne: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot W programie Microsoft Query powstaje pełne sprzężenie zewnętrzne, wstawiając Operator UNION w osobnym wierszu między instrukcją SELECT sprzężenia zewnętrznego a instrukcją SELECT sprzężenia odejmowanego (Zobacz powyżej). Aby utworzyć pełne sprzężenie zewnętrzne w programie Microsoft Query, wykonaj poniższe czynności, aby utworzyć odpowiednie zdanie SQL:

1. Utwórz sprzężenie zewnętrzne, a następnie w menu plik kliknij polecenie Nowy i Utwórz sprzężenie różnicowe.

2. Skopiuj instrukcję SQL dołączanie do pola Odejmij.

3. Przełącz do sprzężenia zewnętrznego, wpisz tekst UNION w osobnym wierszu poniżej instrukcji SQL sprzężenia zewnętrznego, wklej kod SQL dołączania w polu Odejmij pod wyrażeniem UNION, a następnie zamknij okno sprzężenia.

Uwaga W wynikach poniższej tabeli nie znaleziono wartości Join_Field "4", ponieważ rekord, którego Join_Field wartość jest równa 4, znajduje się w tabeli Pattern_Table. Poniższe zdanie SQL dones't zaznacz pole Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Oto sposób, w jaki SQL może szukać powyższego pełnego sprzężenia zewnętrznego (polecenia poniżej operatora UNION są w trakcie wklejenia): SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Pattern_Table LEFT OUTER JOIN `C:\database`.Color_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} UNION SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Color_Table LEFT OUTER JOIN `C:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Color_Table.Color_Field Is Null) or(Pattern_Table.Pattern_Field Is Null) Aby utworzyć praktyczny przykład pełnego sprzężenia zewnętrznego, Scal nakładające się listy klientów, które są używane przez różne działy, w tym numery faksów, które znajdowały się na pierwszej liście, oraz nazwy internetowych poczty e-mail (które są tylko na drugiej liście). Każdy dział może nadal korzystać z jego częściowej listy, gdy będzie dostępna pełna, przyłączona lista. Można je dołączyć do pola Identyfikator klienta.

Samosprzężenie

Do samodzielenia jest przyłączony piąty. Autosprzężenie to połączenia, w których pole tabeli jest dopasowane do innego pola w kopii tej samej tabeli. W poniższej tabeli przedstawiono następujące przykładowe czynności: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 i kopię tej funkcji: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 Możesz użyć samosprzężenia, aby utworzyć listę nazw pracowników z nazwiskami kierownika. Employee_ID w Table_Three będzie sprzężony z Reports_To w Table_Three_01. Poniżej opisano, jak to może wyglądać na pierwszy sposób: Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Ponieważ jednak nie ma takiej samej nazwy pola dla obu pól, Zmień nazwę jednej z nazw pól w następujący sposób: Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim Poniżej przedstawiono, jak w języku SQL można szukać powyższych funkcji autosprzężenia: SELECT table_three.Employee_Name, table_three_01.Employee_Name 'Supervisor' FROM `c:\database`.table_three, `c:\database`.table_three_01 WHERE table_three.Employee_ID = table_three_01.Reports_To Po zwróceniu danych do programu Microsoft Excel nie jest ona używana do zmiany nazwy pola w programie Microsoft Query. Jest to prawdziwe, ponieważ program Microsoft Excel używa pierwotnej nazwy pola. Aby uzyskać więcej informacji na temat tego problemu, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base: 121551 : XL5: pole zamiast nazwy kolumny w MSQuery zwrócone do programu Excel makro programu Microsoft Excel musi zmienić nazwę kolumny za każdym razem, gdy zwracane dane zostaną odświeżone (jeśli nie zostaną zwrócone dane w tabeli przestawnej, co może spowodować utworzenie i zachowanie nazwy pola niestandardowego).

Sprzężenie wewnętrzne i naturalne

Niemal wszystkie sprzężenia, łącznie ze wszystkimi przykładami, które zamieszczono dotąd, to wewnętrzne i sprzężenia naturalne. Znaczenie tych terminów ma niewielki znaczenie dla przeciętnego użytkownika programu Microsoft Query, ale kolejne dwa akapity próbują wyjaśnić warunki dla osób, które mogą być zastanawiasz się. Wewnętrzne-Join to sprzężenie, w którym są pobierane rekordy w zależności od tego, czy pola sprzężenia mają zgodne wartości w obu tabelach. Może się tak zdarzyć, jak tylko definicja sprzężenia, ale nie. Przykładem nierównego sprzężenia jest sprzężenie, w którym rekordy z pierwszej tabeli są dołączane do tych rekordów w drugiej tabeli, w której pole sprzężone w pierwszej tabeli jest większe niż pole sprzężone w drugiej tabeli (lub mniejsze niż równe). W naturalny sposób zwraca więcej rekordów niż wewnętrzne-Join. Sprzężenie naturalne to takie, w którym jest zwracana tylko jedna z dwóch sprzężonych pól tabel. Ponieważ te dwa pola są według definicji identycznej z wewnętrzne-join, warto je uwzględnić jednocześnie. W przypadku nierównego sprzężenia ważne jest, aby uwzględnić oba te pola. W związku z tym, wewnętrzne i sprzężenia naturalne przechodzą razem. Wewnętrzne (, w tym opis większości sprzężeń) ma być naturalnie sprzężona, zwracając tylko jedno sprzężone pole; Jeśli kiedykolwiek korzystasz z nierównego sprzężenia, możesz również nadać mu nienaturalne sprzężenie, zwracając oba sprzężone pola. Istnieją inne rodzaje sprzężeń. Pełny zakres sprzężeń został ostatnio zdefiniowany w 1992, a ten standard jest znany jako SQL-92. Niektóre sprzężenia nie są istotne dla użytkowników programu Microsoft Excel, ponieważ te sprzężenia są łatwiejsze do wykonania w programie Microsoft Excel.

Produkt iloczyn kartezjański

Próba zwrócenia danych z dwóch lub większej liczby tabel bez dołączania powoduje utworzenie "produktu iloczyn kartezjański". Produkt iloczyn kartezjański jest definiowany jako wszystkie możliwe kombinacje wierszy we wszystkich tabelach. Upewnij się, że masz sprzężenia przed próbą zwrócenia danych, ponieważ iloczyn kartezjański produkt w tabelach zawierających wiele rekordów i/lub w wielu tabelach może trwać kilka godzin. Poniżej przedstawiono produkt iloczyn kartezjański, który jest wykorzystywany w dwóch przykładowych tabelach; Pamiętaj, że ta tabela jest tylko 3 rekordami Times 3 rekordów, co daje łącznie 9 rekordów. Jednak zamiast tego Wyobraźmy sobie, że w tabeli wprowadzono 100 rekordów, a 1 000 rekordy Times 10 000 rekordy. tabela będzie zawierać 1 000 000 000 rekordów! Join_Field Join_Field Color_Field Pattern_Field 1 2 Red Striped 1 3 Red Checkered 1 4 Red Polka-Dot 2 2 Blue Striped 2 3 Blue Checkered 2 4 Blue Polka-Dot 3 2 Green Striped 3 3 Green Checkered 3 4 Green Polka-Dot Czasami użytkownicy chcą korzystać z produktu iloczyn kartezjański. Jednak większość użytkowników, którzy ich potrzebują, są na to przypadkowo pomylone. Ponieważ większość użytkowników wyłączy większość pól w sprzężeniu, rzeczywisty produkt iloczyn kartezjański może być łatwo widoczny jako perplexing: Color_Field Red Red Red Blue Blue Blue Green Green Green Jeśli do Pattern_Table zostaną dodane rekordy programu 100, to będzie miało on następującą liczbę rekordów: 309 (103 rekordy każdego z czerwonych, niebieskich i zielonych). Produkty iloczyn kartezjański mają stosunkowo proste instrukcje SELECT. Poniżej przedstawiono sposób, w jaki program SQL może szukać powyższego produktu iloczyn kartezjański: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Praktycznym przykładem produktu iloczyn kartezjański może być utworzenie listy wszystkich możliwych kombinacji opcji produktu dla towarów, z sumami cen dla każdej kombinacji.