Projekt kwerendy

Widok projektu kwerendy umożliwia tworzenie i edytowanie kwerendy bazy danych.

Większość baz danych używa kwerend do filtrowania lub sortowania tabel bazy danych w celu wyświetlenia rekordów na komputerze użytkownika. Widoki oferują taką samą funkcjonalność jak kwerendy, ale po stronie serwera. Jeśli baza danych znajduje się na serwerze obsługującym widoki, możesz użyć widoków do filtrowania rekordów na serwerze, aby przyspieszyć czas wyświetlania.

Wybierając polecenie Utwórz widok na karcie Tabele dokumentu bazy danych, zobaczysz okno Projekt widoku, które przypomina okno Projektu kwerendy opisane tutaj.

Układ okna projektu kwerendy jest przechowywany wraz z utworzonym kwerendy, ale nie można go przechowywać wraz z utworzonym widokiem.

Widok projektu

Aby utworzyć kwerendę, kliknij ikonę Kwerendy w dokumencie bazy danych, a następnie kliknij Utwórz projekt kwerendy.

Dolne okienko Widoku projektu to miejsce, w którym definiujesz kwerendę. Aby zdefiniować kwerendę, określ nazwy pól bazy danych, które mają zostać uwzględnione, oraz kryteria wyświetlania pól. Aby zmienić układ kolumn w dolnym okienku Widoku projektu, przeciągnij główkę kolumny w nowe miejsce lub zaznacz kolumnę i naciśnij +klawisz strzałki.

W górnej części okna Widoku projektu kwerendy ikony paska Projekt kwerendy i paska Projekt są wyświetlane.

Jeśli chcesz przetestować kwerendę, kliknij dwukrotnie nazwę kwerendy w dokumencie bazy danych. Wynik kwerendy jest wyświetlany w tabeli podobnej do widoku źródła danych. Uwaga: wyświetlana tabela jest tylko tymczasowa.

Skróty klawiaturowe w widoku projektu kwerendy

Przeglądaj

Po otwarciu projektu kwerendy po raz pierwszy pojawi się okno dialogowe, w którym należy najpierw wybrać tabelę lub kwerendę, która będzie podstawą dla nowej kwerendy.

Aby dodać pole do kwerendy, kliknij je dwukrotnie. Relacje można definiować metodą „przeciągnij i upuść”.

Podczas projektowania kwerendy nie można modyfikować wybranych tabel.

Usuń tabele

Aby usunąć tabelę z Widoku projektu, kliknij górną krawędź okna tabeli i wyświetl menu kontekstowe. Możesz użyć polecenia Usuń, aby usunąć tabelę z Widoku projektu. Inną opcją jest naciśnięcie klawisza Delete.

Przenoszenie i zmiana wielkości tabeli

Rozmiar tabeli można zmienić; tabele można także układać odpowiednio do wymagań. Aby przenieść tabelę, przeciągnij górną jej krawędź w żądane miejsce. Aby zwiększyć lub zmniejszyć rozmiar w jakim jest wyświetlana tabela, umieść kursor myszy na krawędzi lub rogu tabeli i przeciągnij do żądanego rozmiaru.

Relacje między tabelami

Jeśli między nazwą pola w jednej tabeli a nazwą pola w drugiej występują relacje danych, można je wykorzystać w kwerendzie.

Jeśli na przykład masz arkusz kalkulacyjny dla artykułów identyfikowanych przez numer artykułu i arkusz kalkulacyjny dla klientów, w którym rejestrujesz wszystkie artykuły zamawiane przez klienta przy użyciu odpowiednich numerów artykułów, to istnieje związek między tymi dwoma „numerami artykułów” pola danych. Jeśli chcesz teraz utworzyć kwerendę zwracającą wszystkie artykuły zamówione przez klienta, musisz pobrać dane z dwóch arkuszy kalkulacyjnych. Aby to zrobić, musisz poinformować LibreOffice o relacji, jaka istnieje między danymi w dwóch arkuszach kalkulacyjnych.

Aby to zrobić, kliknij nazwę pola w tabeli (na przykład nazwę pola „Numer pozycji” z tabeli Klient), przytrzymaj przycisk myszy, a następnie przeciągnij nazwę pola do nazwy pola drugiej tabeli („ Numer pozycji” z tabeli pozycji). Po zwolnieniu przycisku myszy pojawi się linia łącząca dwa pola między dwoma oknami tabeli. Odpowiedni warunek, że zawartość obu nazw pól musi być identyczna, jest wprowadzany w wynikowym zapytaniu SQL.

Tworzenie kwerend opartych na kilku powiązanych arkuszach jest możliwe tylko wtedy, gdy pakiet LibreOffice pełni rolę interfejsu relacyjnej bazy danych.

W ramach kwerendy nie można uzyskać dostępu do tabel innej bazy danych. Kwerendy obejmujące wiele tabel można tworzyć tylko w ramach jednej bazy danych.

Określenie typu relacji

Jeśli dwukrotnie klikniesz linię łączącą dwa połączone pola lub wywołasz polecenie menu Wstaw - Nowa relacja, możesz określić typ relacji w oknie dialogowym Relacje.

Ewentualnie naciskaj Tab, aż linia zostanie zaznaczona, a następnie naciśnij Shift+F10, aby wyświetlić menu kontekstowe i tam wybierz polecenie Edytuj. Niektóre bazy danych obsługują tylko podzbiór możliwych typów łączenia.

Usuwanie relacji

Aby usunąć relację między dwiema tabelami, kliknij linię połączenia i naciśnij klawisz Delete.

Ewentualnie usuń odpowiednie wpisy w Złączonych polach okna dialogowego Relacje. Lub naciskaj Tab, aż wektor łączący zostanie podświetlony, a następnie naciśnij Shift+F10, aby otworzyć menu kontekstowe i wybierz polecenie Usuń.

Definiowanie kwerendy

Wybierz warunki do zdefiniowania kwerendy. Każda kolumna tabeli konfiguracji akceptuje pole danych dla kwerendy. Warunki w jednym wierszu są połączone operatorem logicznym I.

Określanie nazw pól

Najpierw wybierz wszystkie nazwy pól z tabel, które chcesz dodać do kwerendy. Można to zrobić metodą „przeciągnij i upuść” lub klikając dwukrotnie nazwę pola w oknie tabeli. Korzystając z metody „przeciągnij i upuść”, użyj myszy, aby przeciągnąć nazwę pola z okna tabeli do dolnego obszaru okna projektu kwerendy. W trakcie tej czynności możesz zdecydować, w której kolumnie w oknie projektu kwerendy zostanie umieszczone wybrane pole. Nazwę pola można również wybrać, klikając dwukrotnie. Zostanie on następnie dodany do następnej wolnej kolumny w oknie projektu kwerendy.

Usuwanie nazw pól

Aby usunąć nazwę pola z kwerendy, kliknij główkę kolumny pola i wybierz polecenie Usuń z menu kontekstowego kolumny.

Zapisywanie kwerendy

Użyj ikony Zapisz na standardowym pasku narzędzi, aby zapisać kwerendę. Zobaczysz okno dialogowe z prośbą o wprowadzenie nazwy kwerendy. Jeśli baza danych obsługuje schematy, można również wprowadzić nazwę schematu.

Schemat

Wpisz nazwę schematu przypisanego do kwerendy lub widoku tabeli.

Nazwa kwerendy lub nazwa widoku tabeli

Wpisz nazwę kwerendy lub widoku tabeli.

Filtrowanie danych

Aby filtrować dane dla kwerendy, ustaw żądane kryteria w dolnym obszarze okna projektu kwerendy. Dostępne są następujące opcje:

Pole

Wprowadź nazwę pola danych, do którego odnosi się kwerenda. Wszystkie ustawienia dokonane w wierszach opcji filtrowania odnoszą się do tego pola. Jeśli aktywujesz tutaj komórkę kliknięciem myszy, zobaczysz przycisk ze strzałką, który umożliwia wybranie pola. Opcja "Nazwa tabeli.*" powoduje wybranie szystkich pól danych, co powoduje, że określone kryteria zostaną zastosowane do wszystkich pól tabeli.

Alias

Określa alias. Ten alias zostanie wymieniony w kwerendzie zamiast nazwy pola. Umożliwia to użycie etykiet kolumn zdefiniowanych przez użytkownika. Na przykład, jeśli pole danych nosi nazwę PtNo i zamiast tej nazwy chcesz, aby w zapytaniu pojawiła się nazwa PartNum, wprowadź PartNum jako alias.

W instrukcji SQL aliasy definiuje się w następujący sposób:

SELECT kolumna AS alias FROM tabela.

Na przykład:

SELECT "PtNo" AS "PartNum" FROM "Parts"

Tabela

Odpowiadająca tabela bazy danych wybranego pola danych jest wymieniona tutaj. Jeśli aktywujesz tę komórkę kliknięciem myszy, pojawi się strzałka, która umożliwia wybranie innej tabeli dla bieżącej kwerendy.

Sortowanie

Klikając tę komórkę, możesz wybrać opcję sortowania: rosnąco, malejąco i nieposortowane. Pola tekstowe zostaną posortowane alfabetycznie, a pola liczbowe – liczbowo. W przypadku większości baz danych administratorzy mogą ustawić opcje sortowania na poziomie bazy danych.

Widoczne

Jeśli zaznaczysz właściwość Widoczne dla pola danych, to pole będzie widoczne w wynikowym zapytaniu. Jeśli używasz pola danych tylko do sformułowania warunku lub wykonania obliczeń, nie musisz koniecznie go wyświetlać.

Kryteria

Określa pierwsze kryterium, według którego ma być filtrowana zawartość pola danych.

lub

Tutaj możesz wprowadzić jedno dodatkowe kryterium filtrowania dla każdej linii. Wiele kryteriów w jednej kolumnie zostanie zinterpretowanych jako logiczne LUB.

Możesz także użyć menu kontekstowego główek wierszy w dolnym obszarze okna projektu zapytania, aby wstawić filtr oparty na funkcji:

Funkcje

Dostępne tutaj funkcje zależą od funkcji udostępnianych przez silnik bazy danych.

Jeśli pracujesz z wbudowaną bazą danych HSQL, pole listy w wierszu Funkcja oferuje następujące opcje:

Wywołania funkcji można wprowadzać także bezpośrednio w instrukcji SQL. Należy wtedy zastosować następującą składnię:

SELECT FUNKCJA(kolumna) FROM tabela.

Na przykład wywołanie funkcji w języku SQL do obliczenia sumy to:

SELECT SUM("Cena") FROM "Artykuł".

Z wyjątkiem funkcji Grupuj, powyższe funkcje nazywane są funkcjami agregującymi. Są to funkcje, które obliczają dane w celu tworzenia podsumowań z wyników. Dodatkowe funkcje, które nie są wymienione w polu listy, mogą być również możliwe. Zależą one od konkretnego używanego silnika bazy danych i bieżącej funkcjonalności zapewnianej przez sterownik podstawowy używany do łączenia się z tym silnikiem bazy danych.

Aby użyć innych funkcji niewymienionych w polu listy, musisz wprowadzić je ręcznie w Pole.

Możesz także przypisać aliasy do wywołań funkcji. Jeśli nie chcesz wyświetlać ciągu zapytania w główce kolumny, wprowadź żądaną nazwę zastępczą w polu Alias.

Odpowiednia funkcja w instrukcji SQL ma postać:

SELECT FUNKCJA() AS alias FROM tabela

Przykład:

SELECT COUNT(*) AS liczba FROM "Artykuł"

Jeśli uruchomisz taką funkcję, nie możesz wstawić żadnych dodatkowych kolumn do kwerendy inaczej niż jako argument w funkcji "Grupuj".

Przykłady

W następującym przykładzie kwerenda obejmuje dwie tabele: tabelę "Artykuł" z polem "Nr_artykułu" oraz tabelę "Dostawcy" z polem "Nazwa_dostawcy". Ponadto obie tabele mają pola o wspólnej nazwie "Nr_dostawcy".

Aby utworzyć kwerendę wyświetlającą wszystkich dostawców, którzy dostarczyli więcej niż trzy artykuły, należy wykonać następujące kroki.

1. Wstaw tabele "Artykuł" i "Dostawcy" do projektu kwerendy.

2. Powiąż pola "Nr_dostawcy" obu tabel, o ile wcześniej nie zdefiniowano takiej relacji.

3. Kliknij dwukrotnie pole "Nr_artykułu" w tabeli "Artykuł". Wyświetl wiersz Funkcja za pomocą menu kontekstowego i wybierz funkcję Liczba.

4. Jako kryterium wprowadź wartość >3 i usuń zaznaczenie pola Widoczne.

5. W tabeli "Dostawcy" kliknij dwukrotnie pole "Nazwa_dostawcy" i wybierz funkcję Grupuj.

6. Uruchom kwerendę.

Jeśli w tabeli "Artykuł" istnieją pola "cena" (cena danego artykułu) oraz "Nr_dostawcy" (dostawca artykułu), następująca kwerenda umożliwia pobranie średniej ceny artykułu dostawcy:

1. Wstaw tabelę "Artykuł" do projektu kwerendy.

2. Kliknij dwukrotnie pola "Cena" i "Nr_dostawcy".

3. Włącz linię Funkcja i wybierz funkcję Średnia z pola "Cena".

4. Można również wpisać alias "Średnia" (bez cudzysłowu).

5. Dla pola "Nr_dostawcy" wybierz opcję Grupuj.

6. Uruchom kwerendę.

Dostępne są następujące polecenia menu kontekstowego i symbole:

Funkcje

Pokazuje lub ukrywa wiersz do wyboru funkcji.

Nazwa tabeli

Pokazuje lub ukrywa wiersz nazwy tabeli.

Nazwa aliasu

Pokazuje lub ukrywa wiersz nazwy aliasu.

Wartości bez powtórzeń

Pobiera tylko odrębne wartości z kwerendy. Dotyczy to wielu rekordów, które mogą zawierać kilka powtarzających się wystąpień danych w wybranych polach. Jeśli aktywne jest polecenie Wartości odrębne, w kwerendzie powinien być widoczny tylko jeden rekord (DISTINCT). W przeciwnym razie zobaczysz wszystkie rekordy odpowiadające kryteriom zapytania (ALL).

Na przykład, jeśli nazwisko „Kowalski” występuje kilka razy w adresowej bazie danych, można wybrać polecenie Wartości bez powtórzeń, aby określić w kwerendzie, że nazwisko „Kowalski” wystąpi tylko raz.

W przypadku kwerendy obejmującej kilka pól kombinacja wartości ze wszystkich pól musi być unikalna, aby wynik mógł zostać utworzony na podstawie określonego rekordu. Na przykład masz raz „Kowalski z Warszawy” w swojej książce adresowej i dwa razy „Kowalski z Krakowa”. Za pomocą polecenia Wartości bez powtórzeń kwerenda użyje dwóch pól „nazwisko” i „miasto” i zwróci jeden raz wynik kwerendy „Kowalski z Warszawy” i raz „Kowalski z Krakowa”.

Odpowiednikiem tej opcji w języku SQL jest właściwość DISTINCT.

Granica

Pozwala ograniczyć maksymalną liczbę rekordów zwracanych przez kwerendę.

Jeśli dodana zostanie konstrukcja Granica, otrzymasz co najwyżej tyle wierszy, ile określisz. W przeciwnym razie zobaczysz wszystkie rekordy odpowiadające kryteriom kwerendy.

Formułowanie warunków filtrowania

Podczas formułowania warunków filtrowania dostępne są różne operatory i polecenia. Oprócz operatorów relacyjnych istnieją polecenia specyficzne dla języka SQL, które odpytują zawartość pól bazy danych. Jeśli używasz tych poleceń w składni LibreOffice, LibreOffice automatycznie konwertuje je na odpowiednią składnię SQL za pomocą wewnętrznego parsera. Możesz także wprowadzić polecenie SQL bezpośrednio i ominąć wewnętrzny parser. Poniższe tabele zawierają przegląd operatorów i poleceń:

Przykłady

Sekwencja anulowania Like: {escape 'znak-anulowania'}

SELECT * FROM Item WHERE ItemName LIKE 'The *%' {escape '*'}

W wyniku tej kwerendy przedstawione zostaną wszystkie artykuły o nazwach rozpoczynających się od słowa 'Zestaw *'. To znaczy, że można poszukiwać także znaków, które w innym kontekście byłyby zinterpretowane jako symbole zastępcze, np. *, ?, _, % lub kropka.

Sekwencja anulowania Outer Join: {oj outer-join}

SELECT Article.* FROM {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}

Kwerendy na polach tekstowych

Aby zapytać o zawartość pola tekstowego, należy umieścić wyrażenie w pojedynczych cudzysłowach. Rozróżnienie między wielkimi i małymi literami zależy od używanej bazy danych. LIKE z definicji uwzględnia wielkość liter (chociaż niektóre bazy danych nie interpretują tego ściśle).

Kwerendy na polach daty

Pola dat są oznaczane jako #Date#, co pozwala łatwo zidentyfikować je jako daty. Data, godzina i stałe data/godzina (literały) stosowane w warunkach mogą mieć zarówno składnię typu SQL Escape lub domyślną SQL2.

SELECT {d '1999-12-31'} FROM world.years

SELECT * FROM mytable WHERE years='1999-12-31'

Wszystkie wyrażenia daty (literały daty) muszą być ujęte w pojedyncze cudzysłowy. (Aby uzyskać więcej informacji, zapoznaj się z odwołaniem do konkretnej bazy danych i używanego łącznika).

Kwerendy na polach logicznych (Tak/Nie)

W tabelach dBASE kwerendy na polach Tak/Nie wymagają zastosowania następującej składni:

Składnia zależy od wykorzystywanego systemu bazy danych. Należy pamiętać, że pola Tak/Nie mogą być także zdefiniowane w inny sposób (dwa stany zamiast trzech).

Kwerendy parametryczne

Kwerendy parametryczne pozwalają użytkownikowi wprowadzić wartości w czasie wykonywania. Wartości te są stosowane w ramach kryteriów wyboru rekordów do wyświetlenia. Każda taka wartość ma nazwę parametru skojarzonego z nim, która jest używana do monitowania użytkownika, gdy kwerenda jest uruchomiona.

Nazwy parametrów są poprzedzone dwukropkiem w obu widokach zapytania, w widoku projektu oraz w widoku SQL. Mogą być stosowany wszędzie tam, gdzie może pojawić się wartość. Jeśli ta sama wartość ma pojawić się więcej niż jeden raz w zapytaniu, używana jest ta sama nazwa parametru.

W najprostszym przypadku, gdy użytkownik wprowadza wartość, która jest dopasowana pod kątem równości, nazwa parametru z poprzedzającym ją dwukropkiem jest po prostu wpisywana w wierszu Kryterium. W trybie SQL należy to wpisać jako WHERE "Pole" = :Nazwa_parametru

Nazwy parametrów nie mogą zawierać żadnego z następujących znaków: <space>`!"$%^*()+={}[]@'~#<>?/,. Nie mogą nazywać się tak samo jak nazwy pól lub jak słowa zastrzeżone dla składni SQL. Mogą mieć nazwy takie same jak aliasy.

Przydatną konstrukcją do wybierania rekordów na podstawie części zawartości pola tekstowego jest dodanie ukrytej kolumny z "LIKE '%' || :Czesc_pola || '%'" jako kryterium. Spowoduje to wybranie rekordów z dokładnym dopasowaniem. Jeśli potrzebny jest test bez rozróżniania wielkości liter, jednym z rozwiązań jest użycie LOWER (Nazwa_pola) jako pola i LIKE LOWER ( '%' || :Czesc_pola || '%' ) jako kryterium. Zwróć uwagę, że spacje w kryterium są ważne; jeśli zostaną pominięte, parser SQL zinterpretuje całe kryterium jako ciąg do dopasowania. W trybie SQL należy to wpisać jako LOWER ( "Nazwa_pola" ) LIKE LOWER ( '%' || :Czesc_pola || '%' ).

Kwerendy parametryczne mogą służyć jako źródło danych dla podformularzy, aby umożliwić użytkownikowi ograniczenie wyświetlanych rekordów.

Wprowadzanie parametrów

Okno dialogowe Wprowadzanie parametrów prosi użytkownika o wprowadzenie wartości parametrów. Wprowadź wartość dla każdego parametru kwerendy i potwierdź, klikając OK lub wpisując Enter.

Wartości wprowadzone przez użytkownika mogą składać się z dowolnych znaków dopuszczalnych dla SQL dla danego kryterium; może to zależeć od bazowego systemu bazy danych.

Użytkownik może korzystać ze znaków wieloznacznych SQL "%" (ciąg losowy) lub "_" (losowy, pojedynczy znak) jako część wartości do pobierania rekordów z bardziej złożonych kryteriów.

Tryb SQL

Skrót SQL (ang. Structured Query Language) oznacza strukturalny język zapytań. SQL jest zbiorem instrukcji służących do modyfikowania zawartości i administrowania relacyjnymi bazami danych.

W LibreOffice do większości kwerend nie jest wymagana znajomość języka SQL, ponieważ nie trzeba wprowadzać kodu SQL. Jeśli utworzysz kwerendę w projekcie kwerend, LibreOffice automatycznie przekonwertuje instrukcje na odpowiednią składnię SQL. Jeśli za pomocą przycisku Włącz/wyłącz widok projektu przełączysz się na widok SQL, możesz zobaczyć polecenia SQL dla kwerendy, które zostało już utworzone.

Możesz sformułować kwerendę bezpośrednio w kodzie SQL. Należy jednak pamiętać, że specjalna składnia zależy od używanego systemu bazy danych.

Jeśli ręcznie wpiszesz kod SQL, możesz tworzyć kwerendy specyficzne dla SQL, które nie są obsługiwane przez interfejs graficzny w Projekcie kwerend. Te kwerendy muszą być wykonywane w natywnym trybie SQL.

Klikając ikonę Wykonaj bezpośrednio polecenie SQL w widoku SQL, możesz sformułować kwerendę, która nie jest przetwarzana przez LibreOffice i wysyłans bezpośrednio do silnika bazy danych.