Previous Topic

Next Topic

Book Contents

Book Index

Słowa - Klucze

Znając już bazy danych oraz ich zawartość możemy przystąpić do tworzenia skryptów. Treść skryptu to szereg poleceń wskazujących komputerowi, “co” ma przenieś, skąd “to” wziąć i ewentualnie, z “czym” i “jak” połączyć. Nic prostszego.

Na początek opis podstawowych słów-kluczy wykorzystywanych w skryptach. Nie ma znaczenia sposób ich wpisywania, możemy pisać dużymi lub małymi literami. Program kluczowe słowa wyróżni automatycznie.

Polecenia i klauzule:

SELECT

Lista pól do obróbki; jeśli wybieramy pola z kilku baz wtedy przed każdym z nich należy umieścić nazwę bazy oraz kropkę. Np.: obroty.kod, salda.konto.

FRO

Lista baz danych.

WHERE

Wskazanie warunków (np.: =,>,<,=>,<=,<>, itd.)

OR

Łączenie warunków.

BETWEEN

Wskazywanie zakresu.

AND

Łączenie warunków.

LEFT OUTER JOIN

Lewostronne złączenie zewnętrzne.

ON

Wskazanie połączenia.

GROUP BY

Grupowanie.

HAVING

Posiadanie, zawieranie – warunek dla zbioru wynikowego.

ORDER BY

Porządkowanie tabeli wynikowej.

Funkcje:

UPPER

Zamiana małych liter na duże.

LIKE

Wyszukiwanie według wskazanego klucza: ‘%xxx%’ – gdzie % oznacza xxx gdziekolwiek w polu.

SUBSTRING

Wyszukiwanie według wskazanego klucza: (nazwapola,x,y)=’z’ – gdzie x- oznacz numer znaku, od którego należy rozpocząć wyszukiwanie; y - oznacza liczbę wyszukiwanych znaków; z - oznacza wyszukiwane znaki.

SUM

Sumowanie.

COUNT (*)

Wyliczanie liczby rekordów np. ilość wystąpień takiej samej wartości pola w całej kolumnie.

AVG

Wyliczanie średniej wartości.

MAX

Maksymalna wartość.

MIN

Minimalna wartość.

Znaki wykorzystywane w treści skryptu.

‘ ‘

W “pojedynczych uszach” podajemy treść, wartość pola tekstowego.

( )

Wartości podawane dla funkcji sumujących wpisujemy w nawiasach.

,

Przecinkiem oddzielamy nazwy pól w poleceniu SELECT, a także nazwy baz danych po słowie FROM.

.

Kropka służy do łącznia nazwy bazy danych z nazwą jego pola np.: obroty.kod.

“ ”

W cudzysłowie podajemy nazwy kolumn wynikowych, jeśli składają się z więcej niż jednego członu. Przykład “Płaca BRUTTO”.

Zastosowanie słów-kluczy najlepiej przedstawią przykłady.

SELECT

ang. Wybierz– komendę zapisujemy w postaci: SELECT *, gdzie gwiazdka oznacza nazwę pola. Jeśli chcemy wyselekcjonować kilka pól, wtedy ich nazwy podajemy po przecinku. Nie ma potrzeby ręcznego wpisywania wartości do komendy SELECT, ponieważ program może zrobić to automatycznie.

Możemy w treści skryptu wpisać SELECT*, wtedy w wynikowej tabeli wyświetlone zostaną wszystkie kolumny ze wskazanej bazy danych.

Przypomnienie

Aby przenieś nazwy pól, należy najpierw zaznaczyć żądane wartości w oknie POLA BAZY DANYCH, (po wcześniejszym wybraniu – kliknięciem odpowiedniej bazy). Następnie przy pomocy Ikony IKONArekaLewo przenosimy informacje do okna skryptu.

PRZYKŁAD:

Krok

Opis postępowania

Pierwszy

Kliknięcie ikonyIKONAkartkaPioro pozwoli nam na edycje skryptu.

Drugi

Wybieramy odpowiednia bazę danych – na przykład klikamy OBR_1999.ADT. W oknie obok wyświetlone zostaną wszystkie pola z tej bazy.

Trzeci

Wybieramy interesujące nas pola – klikając okienka przy nazwach: Miesiąc, Osoba, Kod, Wartość.

Czwarty

W oknie skryptu wpisujemy słowo SELECT - a następnie klikamy ikonkęIKONArekaLewo.

Wartości zostały przeniesione do treści skryptu. Mamy już wybrane pola. Teraz musimy podać komputerowi skąd ma te dane wziąć. Posłużymy się następnym słowem-kluczem.

FROM

ang. Z – wskazanie zapisujemy w postaci FROM * - gdzie gwiazdka oznacza nazwę bazy danych. Jeśli korzystamy z kilku baz wpisujemy je po przecinku. Pamiętać należy, że nie wpisujemy “kropki” oraz rozszerzenia pliku, ponieważ program nie potrzebuje tych informacji.

Teraz sformułowane polecenie jest już gotowe do wykonania skryptu, czyli wygenerowania tabeli. Pamiętamy, że służy do tego ikona BUTTONoblicz lub klawisz F12.

OKNOsql2

Utworzoną tabelę możemy wydrukować, przesłać do Excela lub do RAVE-a, albo też poddać dalszej “obróbce”. (Na przykład zmniejszyć kolumny lub pozmieniać kolejność ich występowania.)

Przypomnienie

W programie są dostępne dwie ikony dotyczące EXCEL-a. Obie zajmują się transmisją danych, ale przeniesione raporty różnią się między sobą.

IKONAexcel

Przenosi do EXCEL-a wygenerowana tabelę jako nowy odrębny plik. Sami podajemy jego nazwę oraz ścieżkę dostępu. Utworzony przez program arkusz zachowuje format nagłówków z tabeli SQL.

Zobacz także opis opcji Menu Główne - Zbiory - Export danych do EXCEL-a.

IKONAexcel1lub F7

Ikona ta również przenosi dane do Excela. Jednak nie tworzy za każdym razem nowego pliku, tylko pozwala zapisywać kolejne wygenerowane tabele jako kolejne arkusze w jednym zbiorze.

Przedstawiony wyżej przykład jest najprostszym skryptem SQL. Składa się jedynie z dwóch podstawowych kluczy. Aby wzbogacić nasze raporty wprowadzimy warunki do naszych skryptów.

WHERE

ang. Gdzie – klauzula ta określa, które rekordy mają być wybrane przez komendę SELECT. Wartości pola tekstowego podajemy w “pojedynczych uszach” ‘ ‘ i ściśle w taki sposób jak zostały wprowadzone w programie, wartości pozostałych pól (np. logicznych (TAK/NIE) wprowadzamy bez ograniczników. Oznacza to, że jeśli chcemy uzyskać wykaz pracowników, których stawka zaszeregowania jest większa niż 1000 musimy podać klauzulę w sposób przedstawiony poniżej. Aby lepiej poznać zastosowanie klauzuli WHERE prześledźmy klika przykładów:

Wskazówka

Pamiętajmy, aby podając w klauzuli WHERE 0 (zero), nie wpisać literki O. Wpisanie litery O spowoduje, że skrypt zostanie wykonany, ale wygenerowana tabela nie będzie zawierała żadnych rekordów, ponieważ program nie znajdzie odpowiednich pozycji.

Przykład 1:

SQLwhere

Przykład 2:

SQLwhere

W przykładzie tym, wyraźnie widać, że nie trzeba wybierać danego pola ( w tym przypadku Czynny) w poleceniu SELECT, aby móc się nim posłużyć stosując klauzulę WHERE. Program wykona skrypt prawidłowo, bez potrzeby wyświetlania kolumny Czynny.

Kolejny przykład przedstawia jak można zastosować przedział wartości.

Przykład 3:

SQLwhere2

Stosując zakresy korzystamy z kolejnych słów kluczy są to:

AND

ang. I – słowo klucz służące do łączenia komend. W tym przykładzie 3, scalone zostały dwa warunki, ponieważ chcieliśmy uzyskać listę osób, których stawka jest większa od 1000,- ale mniejsza od 1200,- (generowane pozycje muszą spełniać obydwa warunki).

Innym słowem używanym przy podawaniu warunków jest:

OR

ang. Lub – słowo, które pozwala na wyszukiwanie rekordów według jednego lub drugiego warunku.

Przykład 4:

W przykładzie tym poznamy sposób wybierania tych osób, dla których w nazwie miejscowości występuje słowo No. Użycie słowa-klucza LIKE spowoduje, że nie musimy podawać całego wyrazu. Program przeszuka pozycje pola Az_Miejscowosc i utworzy tabelę z tych rekordów, które w swojej nazwie zawierają gdziekolwiek ‘No’ Należy zwrócić uwagę na szczególny sposób wprowadzania tej komendy: wartość, która ma być odszukana znajduje się w dwóch ogranicznikach. Istotnym składnikiem polecenia jest zastosowanie następnego słowa-klucza UPPER. Jego użycie jest szczególnie przydatne, gdy rekordy w kolumnie, według której chcemy wyszukiwać, mogą zawierać zarówno małe jak i duże litery. Komenda UPPER zamienia wszystkie znaki znajdujące się we wskazanym polu, (w tym przypadku Az_Miejscowosc) na duże litery. Teraz nie ma już znaczenia, jakiego formatu operator wprowadził litery, ponieważ wszystkie zostały “roboczo” zamienione na duże i my w dalszej części komendy też musimy stosować duże znaki. Słowo “roboczo” oznacza tylko dla prawidłowej, tymczasowej pracy komputera. Ani na ekranie, ani w zbiorach zmiany nie będą dokonane na stałe. Poza tym po wykonaniu skryptu program zwróci tabelę z danymi wprowadzonymi w ich pierwotnej formie, to znaczy z małymi i dużymi literami. Oto prosty przykład:

SQLupperLike

UWAGA

Bardzo ważna jest kolejność wprowadzanych słów-kluczy oraz używanie symboli, które są do nich przypisane. Należy pamiętać, że najpierw zamieniamy litery, a dopiero później podajemy kod (oczywiście składający się z dużych liter) do wyszukiwania. Tak jak w przedstawionym wyżej przykładzie.

W przedstawionym przykładzie wyraźnie widać, że znalezione zostały wszystkie rekordy, które w polu Az_Miejscowosc miały gdziekolwiek ciąg znaków NO. Dlatego w wygenerowanej tabeli znalazł się rekord Mazurska Zuzanna z miejscowości Legionowo. Aby wyszukać jedynie pozycje, których nazwa miejscowości zaczyna się od literki N, należy posłużyć się innym słowem-kluczem. Następny przykład pokaże, co należy zrobić, aby wyszukiwanie komendą WHERE ograniczyć tylko do jednego znaku w dodatku w wybranym przez nas miejscu. Przerabiamy ten sam skrypt.

Przykład 5:

Chcemy uzyskać listę osób z miejscowości o nazwach zaczynających się na N. W tym wypadku nie ma potrzeby stosowania UPPER, ponieważ nazwy, program zawsze wpisuje do baz z pierwszą dużą literą. Funkcja, której użyjemy to SUBSTRING. Po jej wpisaniu, w nawiasie podajemy pole, numer znaku (licząc od lewej) oraz liczę znaków (pobranych do dalszej obróbki). Następnie podajemy, co z tego wybranego miejsca chcemy wydobyć. W naszym przypadku jest to litera N.

SQLsubstring

Kolejny przykład wskaże nam jak wybierać zapisy pochodzące z podanego przedziału czasowego. W tekście skryptu wykorzystujemy następny słowny klucz:

Przykład 6:

BETWEEN

ang. Pomiędzy - do określenia zakresu dat stosujemy następujący format:

‘RRRR-MM-DD’ AND ‘RRRR-MM-DD’. Bez względu na to jak data jest wyświetlana na ekranie (np. 01.02.1999 lub 03/01/99) przy tworzeniu skryptu musimy zachować podany wyżej, prawidłowy format daty.

SQLbetween

Oprócz podanych wyżej zastosowań, klauzula WHERE bywa także używana do łączenia dwóch tabel i generowania wynikowego zbioru danych. Aby uzyskać złączenie tabel należy zmodyfikować nasz skrypt. Przede wszystkim musimy wzbogacić polecenie SELECT o nazwy baz.

Prześledźmy uważnie poniższy wzór, aby lepiej zrozumieć istotę łączenia baz.

BD1

Nazwa pierwszej bazy danych.

BD2

Nazwa drugiej bazy danych.

P1a

Nazwa pola z pierwszej bazy danych.

P2a

Nazwa pola z drugiej bazy danych.

P1b

Nazwa innego pola z pierwszej bazy danych.

P2b

Nazwa innego pola z drugiej bazy danych.

Itd...

 

Itd...

 

select BD1.P1a, BD1.P1b, BD2.P2a, BD2.P2b

from BD1, BD2

where ............ – musimy podać warunek, który scali obie bazy.

Sposób łączenia baz najlepiej prześledzić na prostym przykładzie.

Przykład 7:

Przygotujemy listę osób (z bazy Rodzina) wraz z imionami i nazwiskami ich rodzica lub małżonka, pracującego w naszej firmie (z bazy osoby).Aby połączyć bazy, musimy znaleźć jakąś część wspólną. W naszym przypadku jest to pole kod.

SQLlaczenie

UWAGA

Łączenie baz danych wymaga znajomości baz, z których składa się program, a także zawartych w nich pól. Jeśli nie połączymy bazy w odpowiedni - przemyślany sposób, system wygeneruje tabelę połączoną przypadkowo, co spowoduje, że informacje nie będą prawidłowe!!!!!!!

Treść naszego skryptu jest prawidłowa. Dodatkowo w klauzuli Select przerzucono do następnego wiersza pola z drugiej bazy. Wróćmy teraz do ostatniej linii zastosowanego w powyższym przykładzie skryptu:

WHERE osoby.kod=rodzina.kod

Tabelę, wymienioną po lewej stronie znaku równości, nazywamy tabelą zewnętrzną, natomiast po prawej stronie, tabelą wewnętrzna. Możemy też nazywać je tabelą lewą i prawą.

W opisywanym przed chwilą przykładzie zastosowane zostało łączenie wewnętrzne. Wynikiem są tylko te wiersze, dla których spełniony został warunek złączenia. W tym wypadku wygenerowane zostały te rekordy, dla których pod tym samym kodem program znalazł rekordy i w bazie obroty i w bazie rodzina.

W przypadku złączenia zewnętrznego rekordy zwracane są bez względu na to czy spełniony jest warunek. Jeśli nie jest, wtedy w miejsce wartości pól z tabeli wewnętrznej podstawiana jest wartość NULL i pole jest puste.

Przykład 8:

SQLlaczenieJoin

Przedstawiony przykład, pomoże dokładniej zrozumieć to zagadnienie. Polecenie złączenia zewnętrznego różni się nieco od komendy złączenia wewnętrznego. Składa się z kilku dodatkowych słów-kluczy:

LEFT OUTER JOINT

ang. Lewostronne złączenie zewnętrzne.

ON

ang. Na - wskazanie pola(kolumny) połączenia.

Wygenerowana tabela jest inna od wcześniejszego wyniku złączenia wewnętrznego. Możemy zauważyć, że pojawiły się rekordy mające puste pola w ostatniej kolumnie. Wyjaśnienie jest proste. Program połączywszy dwie bazy, wyświetlił wszystkie rekordy z bazy obroty i dopasował (kierując się polem - kod) do niektórych z nich rekordy z bazy rodzina. Wynik prawie taki jak w połączeniu wewnętrznym, jednak dodatkowo pokazane zostały rekordy z bazy wymienionej po lewej stronie złączenia, bez "pary" w drugiej bazie. Nie znajdując dla nich odpowiedników w bazie rodzina, program pokazał puste pola, które oznaczają, że Pan Adamczyk oraz Pani Mazurska nie posiadają osób na utrzymaniu.

UWAGA

Informacja dla wytrawnych informatyków: nie można wykonać połączeń: FULL i RIGHT OUTER JOINS.

Na tym zakończymy wyjaśnianie możliwości zastosowania klauzuli WHERE. Na tym jednak nie koniec. Wzbogacimy nasze skrypty o dodatkowe warunki.

GROUP BY

ang. Grupa, grupować – klauzula służąca do grupowania danych. Dzięki temu poleceniu stosowanemu z funkcjami sumującymi, możemy uzyskiwać odpowiedzi na złożone pytania dotyczące baz danych. Poniższe przykłady pozwolą nam lepiej poznać tą klauzulę.

Przykład 1:

Utworzymy tabelę, która oprócz kolumn przez nas wskazanych, utworzy nową. Wybieramy pola Kod, nazwisko oraz Imie_Pierwsze z bazy Osoby. Chcemy też otrzymać sumę składnika 450 z wszystkich miesięcy dla poszczególnych pracowników.

W programie, w bazie obr_1999 taka kolumna nie istnieje, dlatego chcemy, aby utworzył ją SQL. Po funkcji SUM -ang. Sumować- otrzymamy łączną kwotę obrotów danego konta. Nazwa kolumny, w której wynik ten zostanie zapisany musi znaleźć się za funkcją sumowania w cudzysłowie. Aby móc sumować musimy posłużyć się klauzulą grupującą rekordy -GROUP BY.

WZÓR: SUM (*.*) “nazwa pola” - funkcją tą należy posługiwać się w tej właśnie formie. W nawiasie pierwsza * oznacza nazwę bazy danych, druga natomiast pole z tej bazy, które ma być sumowane. “To”, co znajduje się za nawiasem będzie przez program potraktowane jako nazwa dla pola wynikowego. Jeśli składa się z jednego wyrazu możemy wpisać go bez cudzysłowu. Brak nazwy spowoduje, że program wpisze swoją i skrypt zostanie utworzony poprawnie. Chcąc otrzymać nazwisko odpowiednie dla sumowanych wartości musimy skorzystać z dwóch baz danych, ponieważ w bazie Obr_1999 jest zapisany kod pracownika (pole: osoba), ale nie ma jego nazwiska ani imienia. Łączymy więc dwie bazy, w sposób, jakiego nauczyliśmy się śledząc przykłady dla klauzuli WHERE. Dodatkowo jednak musimy użyć komendy grupującej, aby móc posłużyć się funkcją sumującą.

SQLgroup

UWAGA

W klauzuli GROUP BY zawsze podajemy wszystkie te pola, które wpisaliśmy w poleceniu SELECT tego samego skryptu, z wyjątkiem funkcji sumujących oraz wartości.

W przedstawionym wyżej przykładzie widzimy, że po wykonaniu skryptu otrzymaliśmy tabelę z dodatkową kolumną, w której znajduje się suma składnika 450 z każdego miesiąca 1999 roku (oczywiście z list zaksięgowanych).

WSKAZÓWKA

Zawsze warto sprawdzić (wyrywkowo lub sumy końcowe) wygenerowanej tabele z danymi, aby być pewnym, że nie pokręciło się czegoś w skrypcie.

UWAGI

Użytkownicy, którzy nie mają dobrego rozeznania w zawartości baz danych tego programu, nie powinni wykonywać skomplikowanych skryptów, wykorzystujących formuły obliczeniowe (jak przykład powyżej). Nieprawidłowe zastosowanie formuł, lub niedopisanie odpowiednich połączeń, spowoduje fatalne skutki – UZYSKANIE BŁĘDNYCH WYNIKÓW.

Autor programu nie bierze odpowiedzialności za źle wykonane skrypty, a tym samym za uzyskiwanie nieprawidłowych informacji. Opcja SQL została dołączona do programu na prośbę osób znających zagadnienia informatyczne i “uwielbiających” wykonywanie głębszych analiz. W tym miejscu należy przypomnieć, że wszystkie wydruki potrzebne do prawidłowego i sprawnego funkcjonowania działu kadrowo-płacowego ( a także inne analizujące sprawozdania) są łatwo dostępne w poszczególnych opcjach programu.

Ponad to należy pamiętać, że nieprawidłowo podany skrypt przy dużych bazach danych spowoduje zablokowanie komputera, ponieważ program może się zapętlić. Tego typu komplikacje mogą spowodować awarię systemu, a tym samym uszkodzenie zbiorów.

Następną funkcją sumującą, którą używamy wraz z klauzulą GROUP BY jest:

COUNT(*) “nazwa kolumny”

ang. Obliczać. Służy do wyświetlenia w danej kolumnie listy występujących wartości oraz w utworzonej wynikowej kolumnie podania ilość wystąpień tych wartości (w wybranej kolumnie-polu) w całej bazie. Prześledźmy podany niżej, prosty przykład.

Przykład 2:

W poleceniu SELECT podajemy bazę danych i pole, które chcemy poddać obliczaniu. Bezpośrednio za nim znajduje się zmieniona nazwa kolumny.

Pamiętajmy, że jeśli chcemy by nowy tytuł składał się z kilku wyrazów, musimy umieścić je w cudzysłowie. Po przecinku podajemy funkcję COUNT (*) oraz nazwę dla wynikowej kolumny. Pamiętajmy o zastosowaniu klauzuli GROUP BY.

Wynikiem tego skryptu jest tabela z listą imion oraz liczba ich wystąpień w naszej bazie.

SQLcount

Wyliczaniem średniej wartości zajmiemy się w:

Przykładzie 3:

AVG

Skrót od average - ang. Pośredni, przeciętny. Funkcja ta działa w następującym formacie: AVG (baza danych.pole). Oczywiście pamiętamy, że używana jest z klauzulą GROUP BY. Bez niej nie będzie działać. W naszym przykładzie wyliczymy średnie wartości składnika BRUTTO (w naszej liście składników odpowiada mu kod 700) z wypłat z całego 1999 roku, dla poszczególnych osób. Korzystamy z bazy obr_1999. Wybieramy (select) pole osoba. Następnie wpisujemy funkcję AVG, a bezpośrednio po niej nazwę pola, z którego ma być wyliczona średnia. Przy tego rodzaju funkcji, gdzie korzystamy tylko z jednej bazy danych, nasz skrypt składa się z prostej formy.

SQLavg

Przyglądając się wygenerowanej tabeli, naszą uwagę zwracają wyniki wypełniające całą komórkę. Czyżby komputer coś poplątał? Nie, po prostu wynik nie zmieścił się w polu i dlatego wyświetlana jest tylko jego część. Wystarczy “chwycić” myszą prawą krawędź pola nagłówka, wynikowej kolumny i przesunąć je o pożądaną wielkość. Zdanie być może zawiłe, ale wykonanie to doprawdy błahostka. Efekt:

SQLavg1

Teraz wiadomo, dlaczego liczba była “inna”. Uzyskane wyniki możemy porównać z wyrywkowo wybraną kartoteką..

Przykładzie 4,

W przykładzie tym prześledzimy sposób wyliczania maksymalnych oraz minimalnych wartości. Funkcji tych używamy w taki sam sposób jak AVG (zastępując w skrypcie AVG funkcją MAX lub MIN).

MAX

Maksymalny. Funkcja wybierająca z podanego pola największą wartość.

MIN

Minimalny. Funkcja wybierająca z podanego pola najmniejszą wartość.

Następne słowo-klucz, z którym warto się zapoznać to:

HAVING

ang. Posiadanie, – klauzula służąca do ograniczania zbioru rekordów zwróconych po zastosowaniu klauzuli GROUP BY. Pomiędzy tymi dwoma kluczowymi słowami istnieje zależność, podobna do tej, jaka wiąże WHERE z poleceniem SELECT. Różnica polega na tym, że WHERE działa na wszystkich zbiorach wyliczonych w poleceniu SELECT, natomiast HAVING operuje tylko na zbiorach wynikowych. Przeważnie możemy w inny sposób dokonać podobnego ograniczenia, stosując np. komendę WHERE. Przyjrzyjmy się jednak przykładowi, który zapozna nas lepiej z tą klauzulą.

Przykład:

Dokładnie taki sam wynik uzyskamy stosując poniższy skrypt:

Dokładnie taki sam wynik uzyskamy stosując poniższy skrypt:

SQLhaving1

Ostatnie słowo-klucz, które poznamy jest:

ORDER BY

ang. Porządkować – klauzula służąca do określenia kolejności występowania wierszy w zbiorze wynikowym. Forma skryptu jest bardzo prosta. Wynikiem jest lista w kolejności alfabetycznej.

SQLorder

Jak widać w przedstawionym wyżej przykładzie z lewej strony, klauzula ta segreguje rekordy nie tylko według alfabetu. Równie dobrze możemy poukładać pozycje na przykład według dat.

UWAGA

Pamiętajmy, że czasami dodatkowy znak (przecinek, litera, nawias, kropka itd...) w treści skryptu, sprawią, że komputer nie będzie w stanie wykonać zadania i zgłosi błąd. Ta sama zasada działa również w drugą stronę, jeśli w skrypcie braknie jednego znaku, niemożliwe będzie jego wykonanie.

Zobacz także

TGSoft

SQL

Tworzenie skryptu

Wykaz Baz danych programu GMF

Dodatkowe informacje o SQL