Sprawozdanie

„Wbudowane funkcje serwera MySQL”

Tak jak we wszystkich innych językach programowania, w języku SQL funkcje stanowią potężne narzędzie w pracy programisty — zamiast samodzielnie pisać skomplikowane programy, wystarczy wywołać odpowiednią funkcję. W tym odcinku nauczysz się wywoływać funkcje systemowe i poznasz niektóre funkcje serwera MySQL.

Wbudowane funkcje serwerów baz danych można podzielić na trzy kategorie:

• Funkcje skalarne — zwracają pojedynczą wartość obliczoną na podstawie zera lub większej liczby prostych argumentów.

• Funkcje grupujące — zwracają pojedynczą wartość dla zbioru argumentów wywołania.

• Funkcje typu RowSet — zwracające dane w postaci tabelarycznej, do których odwołujemy się tak jak do tabel.

Funkcje Skalarne

Najpierw zajmę się funkcjami skalarnymi, ich rodzajami oraz zastosowaniem.

Baza, na której będę wykonywał poszczególne ćwiczenia została zaimplementowana do serwera MySql przy pomocy Query Browser. (Jest to przykładowa baza danych ze strony webmaster.helion.pl ) Dodatkowe wszystkie tabele znajdujące się w tej bazie zaopatrzyłem w początek mojego nazwiska mar.

Na podstawie typu parametrów wywołania funkcje skalarne można podzielić na:

• Funkcje tekstowe operujące na ciągach znaków.

• Funkcje liczbowe operujące na liczbach.

• Funkcje daty i czasu operujące na danych typu data i godzina.

• Funkcje konwersji służące do zmiany typu danych.

• Na specjalne wyróżnienie zasługują funkcje kryptologiczne, które pozwalają zaszyfrować, odszyfrować i podpisać wiadomość oraz sprawdzić jej autentyczność.

Funkcje tekstowe

Argumentem funkcji tekstowych są ciągi znaków (dane typów char, varchar lub text). Typ danych zwracanych przez funkcje tekstowe jest podstawą do ich dalszego podziału: wyróżniamy funkcje tekstowe zwracające wartość znakową i funkcje tekstowe zwracające liczbę.

Funkcje tekstowe zwracające tekst

CONCAT()

Funkcja łączy (konkatenuje) przekazane jako parametr i oddzielone przecinakami ciągi znaków. Używaliśmy już tej funkcji, a w następnych listingach użyjemy jej jeszcze kilka razy.

CONCAT_WS()

Funkcja łączy (konkatenuje) przekazane jako parametr i oddzielone przecinakami ciągi znaków, rozdzielając je przekazanym jako pierwszy parametr ciągiem znaków.

LOWER()

Wynikiem działania funkcji LOWER() jest ciąg znaków podany jako argument, ale składający się wyłącznie z małych liter. Za pomocą tej funkcji wszystkie wielkie litery argumentu zostaną zamienione na małe. Aby na przykład wyświetlić nazwiska wszystkich współpracowników za pomocą małych liter, wykorzystamy następującą funkcje:

UPPER()

Wynikiem działania funkcji UPPER() jest ciąg znaków podany jako argument, ale składający się wyłącznie z wielkich liter. Za pomocą tej funkcji wszystkie małe litery argumentu zostaną zamienione na wielkie. Aby na przykład uszeregować nazwy wszystkich towarów alfabetycznie według ich nazw, bez względu na wielkość użytych w nazwie liter co pokazuje rysunek poniżej

LEFT()

Za pomocą funkcji LEFT() z podanego jako argument ciągu znaków zostanie wycięta określona liczba znaków, począwszy od lewej strony. Aby odczytać inicjały klientów, wykonamy instrukcję pokazaną na rysunku poniżej:

RIGHT()

Za pomocą funkcji RIGHT() z podanego jako argument ciągu znaków zostanie wycięta określona liczba znaków, począwszy od prawej strony. Wynik działania funkcji poniżej.

TRIM(), LTRIM() I RTRIM()

Funkcja LTRIM() z podanego ciągu znaków usuwa wiodące spacje, funkcja RTIM() usuwa kończące (wolne) spacje, a funkcja TRIM() — zarówno wiodące, jak i wolne spacje

REPLACE()

Za pomocą funkcji REPLACE() w ciągu znaków podanym jako pierwszy parametr zostanie wyszukany ciąg podany jako jej drugi parametr, a następnie w miejsce znalezionego ciągu będzie podstawiony ciąg podany jako trzeci parametr wywołania. Jeżeli trzeci parametr nie zostanie podany, z ciągu podstawowego będzie wycięty wyszukany ciąg znaków.

SUBSTRING()

W wyniku działania funkcji SUBSTRING() zostanie zwrócona określona liczba znaków z łańcucha tekstowego, począwszy od podanej pozycji. Jeżeli nie podamy liczby zwracanych znaków, zwrócone będą wszystkie znaki występujące po pozycji określonej przez drugi parametr. Podanie ujemnej wartości drugiego parametru spowoduje, że znaki będą liczone od prawej do lewej.

SPACE()

Działanie funkcji SPACE() powoduje zwrócenie liczby spacji określonej jako parametr.

REVERSE ()

Funkcja REVERSE () zwraca ciąg znaków będący ciągiem znaków o odwróconej kolejności liter.

Funkcje tekstowe zwracające liczby

LENGTH ()

Funkcja LENGTH() jako wynik zwraca długość ciągu znaków podanego jako parametr jej wywołania. Jeżeli wywołamy funkcję LENGTH() z wartością Null, funkcja zwróci wartość Null. Za pomocą poniższej instrukcji wyświetlimy tylko te opisy towarów, które mają długie (dłuższe niż 10-znakowe) nazwy.

INSTR()

W wyniku działania funkcji INSTR() będzie zwrócona pozycja, na której w ciągu znaków podanym jako pierwszy parametr został znaleziony ciąg znaków podany jako drugi parametr. Jeżeli szukany ciąg znaków nie będzie znaleziony, funkcja zwróci 0.

Funkcje liczbowe

ROUND()

Działanie funkcji ROUND() polega na zaokrągleniu liczby do określonej liczby cyfr po przecinku. Pierwszy parametr jest liczbą do zaokrąglenia, drugi wskazuje, do ilu pozycji chcemy ją zaokrąglić. Ujemna wartość powoduje zaokrąglenie liczby z lewej strony przecinka; 0 spowoduje zaokrąglenie do najbliższej liczby całkowitej. Jeżeli drugi parametr nie jest podany, serwer baz danych przyjmuje domyślnie jego wartość jako równą 0.

TRUNCATE()

Funkcja TRUNCATE() powoduje obcięcie liczby do określonej liczby cyfr po przecinku. Pierwszy parametr jest liczbą do obcięcia, drugi wskazuje, do ilu pozycji chcemy liczbę skrócić. Ujemna wartość powoduje dodanie określonej liczby zer z lewej strony przecinka. Jeżeli drugi parametr nie jest podany, MySQL przyjmuje domyślnie jego wartość jako równą 0.

ABS()

Wynikiem działania funkcji ABS() jest wartość bezwzględna liczby (liczba bez znaku). Jako parametr podaje się liczbę, której wartość bezwzględną należy obliczyć.

CEILING(), FLOOR()

Za pomocą funkcji CEILING() zwrócona zostanie najmniejsza liczba całkowita równa liczbie podanej jako argument funkcji lub większa. Funkcja FLOOR() zwraca największą liczbę całkowitą równą liczbie podanej jako argument funkcji lub mniejszą.

POWER(), POW()

Funkcja POWER() sprawia, że liczba podana jako pierwszy parametr zostanie podniesiona do potęgi podanej jako drugi parametr. Wartości drugiego parametru mogą być mniejsze niż zero.

SQRT()

W wyniku działania funkcji SQRT() zwrócony będzie pierwiastek kwadratowy z liczby podanej jako parametr wywołania.

MOD()

Funkcja MOD() zwraca jako wynik wartość reszty po podzieleniu liczby podanej jako pierwszy parametr przez dzielnik podany jako drugi parametr wywołania funkcji. Jeżeli wartość drugiego parametru wynosi 0, zwracana jest liczba podana jako pierwszy parametr.

FUNKCJE TRYGONOMETRYCZNE — SIN(), COS(), TAN() I COT()

W wyniku działania funkcji SIN() (sinus), COS() (cosinus), TAN() (tangens) i COT() (cotangens) zwrócona zostanie wartość odpowiednich funkcji trygonometrycznych — dla parametru będącego kątem w radianach będzie obliczony odpowiednio: sinus, cosinus, tangens lub cotangens. Aby przeliczyć kąty z radianów na stopnie, należy posłużyć się wzorem: stopnie · PI()/180 = radiany.

RAND()

Funkcja RAND() zwraca pseudolosową liczbę z przedziału od 0 do 1. Serwer MySQL oblicza tę wartość dla każdego wiersza wyniku, co pozwala użyć funkcji RAND() do losowego wybierania danych z tabeli.

SIGN()

Funkcja SIGN() zwraca 1, jeżeli argumentem jej wywołania była liczba większa od 0, lub 0, jeżeli argumentem jej wywołania było 0, albo –1, jeżeli została wywołana dla liczby mniejszej od zera. W przykładzie użyłem jej do wyróżniania informacji o tym, czy cena sprzedaży towaru była wyższa od ceny jego zakupu.

Funkcje daty i czasu

CURDATE(), CURTIME()

Wynikiem działania funkcji CURDATE() jest bieżąca data, a funkcji CURTIME() — bieżący czas. Częstym zastosowaniem funkcji jest automatyczne wstawianie informacji o czasie utworzenia lub zmodyfikowania danych.

DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR()

Funkcje DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR() zwracają numer dnia odpowiednio: miesiąca (liczba z zakresu od 1 do 31), tygodnia (liczba z zakresu od 1 do 7, gdzie 1 oznacza niedzielę — pierwszy dzień tygodnia, a 7 sobotę — siódmy dzień tygodnia) i roku (liczba z zakresu od 1 do 365).

DAY(), MONTH(), YEAR()

Funkcje DAY(), MONTH(), YEAR() zwracają odpowiednio dzień, miesiąc i rok z daty przekazanej jako parametr wywołania.

Funkcje konwersji

ASCII()

W wyniku działania funkcji ASCII() będzie zwrócony kod ASCII znaku podanego jako parametr wywołania. Jeżeli jako parametr podamy ciąg znaków, za pomocą tej funkcji zostanie obliczony i zwrócony kod ASCII pierwszego znaku w ciągu.

CHR()

Działanie funkcji CHR() jest przeciwieństwem działania funkcji ASCII() — zamiast zamiany tekstu na liczbę przeprowadza konwersję liczby na odpowiadające jej znaki kodu ASCII.

BIN()

Funkcja BIN() zwraca binarną reprezentację podanej liczby dziesiętnej.

CAST()

Funkcja CAST() pozwala przekonwertować (rzutować) dane przekazane jako pierwszy parametr wywołania na typ podany jako drugi parametr funkcji.

Funkcje serwera MySQL

ENCODE(), DECODE()

Funkcja ENCODE() zwraca szyfrogram podanego ciągu znaków. Do szyfrowania używane jest hasło podane jako drugi parametr wywołania. Funkcja DECODE() deszyfruje zaszyfrowane funkcją ENCODE() szyfrogramy.

SHA1()

Funkcja SHA1() wylicza sygnaturę podanego ciągu znaków.

PASSWORD()

Hasła użytkowników muszą być chronione, a to oznacza, że nigdy nie należy zapisywać ich w bazie w jawnej postaci. Prawie zawsze hasło wykorzystywane jest jedynie do potwierdzenia tożsamości użytkownika, a do tego celu serwer baz danych nie musi odszyfrować hasła, czyli hasła powinny być zapisane nieodwracalnie zaszyfrowane. Wiemy już, że tak działają funkcje mieszania — hasła do serwera MySQL też są tak przechowywane, z tym że do ich wyliczenia i późniejszego porównania wykorzystywana jest funkcja PASSWORD()

Funkcje grupujące

Funkcje, które zwracają jedną wartość obliczoną na podstawie przekazanego zbioru parametrów, nazywamy funkcjami grupującymi. W każdym serwerze baz danych, w tym w MySQL-u, zaimplementowano najważniejsze i najczęściej używane funkcje tego typu. Charakterystyczną cechą funkcji grupujących jest operowanie na zbiorach, a nie pojedynczych wartościach. Dzięki temu otrzymane w wyniku grupowania dane mogą być użyte jako argumenty ich wywołania. Jeżeli wszystkie wiersze tabeli są połączone w jedną grupę, funkcja grupująca będzie wywołana tylko raz, w innym przypadku zostanie wywołana dla każdej grupy. Funkcje grupujące zwracają pojedyncze (skalarne) wartości, więc wywołuje się je w klauzuli SELECT, tak jak wcześniej poznane funkcje systemowe.u — minimum, maksimum, średnią, sumę itd.

Funkcja COUNT()

Pierwszą funkcją agregującą, którą chcę dokładnie omówić, jest funkcja COUNT(). Funkcja ta zlicza w przekazanym zbiorze wartości wystąpienia różne od NULL, chyba że jako argumentu użyto znaku * (gwiazdka) — takie wywołanie funkcji spowoduje zliczenie wszystkich wierszy, łącznie z duplikatami i wartościami NULL. Argumentem funkcji mogą być liczby, daty, znaki i ciągi znaków.

Jeżeli chcemy policzyć unikatowe wystąpienia wartości, wystarczy wykorzystać wiedzę z wcześniejszych odcinków kursu i właściwie użyć słowa kluczowego DISTINCT.

Funkcja SUM()

Za pomocą funkcji SUM() dodawane są wszystkie wartości rekordów wybranych w zapytaniu i zwracany jest pojedynczy wynik. W przeciwieństwie do funkcji COUNT(), która działa dla wszystkich typów danych, argumentami funkcji SUM() mogą być wyłącznie liczby. Funkcja SUM(), tak jak inne funkcje grupujące, nie uwzględnia wartości Null.

Oczywiście, zbiór argumentów wywołania funkcji grupujących możemy ograniczać za pomocą wcześniej omówionej klauzuli WHERE.

Funkcja AVG()

W wyniku działania funkcji AVG() zwracana jest wartość średnia dla podanych wyrażeń. Wiersze przechowujące wartość Null nie są uwzględniane. Argumentami funkcji AVG() muszą być dane liczbowe. Aby na przykład obliczyć średnie ceny sprzedaży i zakupu towarów.

Wynikiem funkcji grupujących są pojedyncze liczby. Język SQL jako język przeznaczony do operacji na zbiorach danych początkowo nie umożliwiał definiowania zmiennych. Ostatnio prawie wszystkie serwery baz danych obsługują zmienne, ale wiele operacji, które w proceduralnych językach programowania wymagały ich użycia, w SQL możemy wykonać bez zadeklarowania choćby jednej zmiennej.

Funkcje MIN() i MAX()

Funkcja MIN() służy do znajdowania wartości najmniejszej w zbiorze wartości, a funkcja MAX() — największej. Obie funkcje, podobnie jak funkcja COUNT(), mogą być użyte dla różnych typów danych.

Za pomocą funkcji MAX() można znaleźć największy łańcuch danych, najnowszą datę (lub najodleglejszą w przyszłości) oraz największą liczbę w zestawieniu. W wyniku działania funkcji MIN() można znaleźć odpowiednio wartości najmniejsze. Aby znaleźć datę pierwszej operacji naszej firmy.

Funkcja STDDEV_POP()

Funkcja STDDEV_POP() wyznacza odchylenie standardowe zbioru. Wiersze przechowujące wartość Null nie są uwzględniane. Argumentami funkcji muszą być dane liczbowe. Aby wyznaczyć odchylenie standardowe dla cen zakupu wszystkich towarów.

Funkcja VARIANCE()

Funkcja VARIANCE() wyznacza wariancje zbioru. Wiersze przechowujące wartość Null nie są uwzględniane. Argumentami funkcji muszą być dane liczbowe. Aby obliczyć wariancje ceny sprzedaży.

Funkcja GROUP_CONCAT()

Funkcja GROUP_CONCAT() w przeciwieństwie do pozostałych funkcji grupujących zwraca dane tekstowe, a nie liczbowe. Jej działanie jest bardzo proste — łączy w jeden ciąg znaków przekazane argumenty wywołania. Poniższy przykład pokazuje, jak przygotować listę imion klientów.

Grupowanie danych

Klauzula GROUP BY umożliwia grupowanie wyników względem zawartości wybranych kolumn. W wyniku jej działania uzyskujemy podział wierszy tablicy na dowolne grupy. W pewnym sensie jej działanie jest podobne do działania operatora DISTINCT, ponieważ po jej zastosowaniu zwracany jest pojedynczy wynik dla każdej grupy.

Jeżeli jednak w zapytaniu użyjemy jednocześnie funkcji grupującej, to ta funkcja zostanie wywołana niezależnie dla każdej grupy zdefiniowanej w klauzuli GROUP BY. W bazie test informacje o zamówieniach przechowywane są w tabeli orderinfo, a poszczególne pozycje zamówienia — w tabeli orderline (dzięki temu w ramach każdego zamówienia klient może kupić dowolną liczbę najróżniejszych towarów). Pierwsze zapytanie zwraca liczbę wszystkich sprzedanych towarów, drugie rozbija tę liczbę na poszczególne zamówienia.

Klauzula HAVING

Język SQL dostarcza jeszcze jedną metodę filtrowania wyników zapytań — jeżeli grupujemy wyniki (a więc używamy klauzuli GROUP BY), możemy sprawdzić, czy te grupy wierszy spełniają jakiś warunek. Po zastosowaniu klauzuli WHERE wyniki zapytania najpierw są filtrowane, a potem grupowane. Klauzula HAVING, tak jak WHERE, umożliwia określenie testu logicznego, ale w jej przypadku będzie on zastosowany do grup, a nie pojedynczych wierszy. Testy logiczne zawarte w klauzuli HAVING wykonywane są na całych grupach, a nie na pojedynczych rekordach. Tak więc klauzula ta służy do wybierania interesujących nas grup, a klauzula WHERE — interesujących nas wierszy. Warunek umieszczony w klauzuli HAVING wyrażony jest za pomocą dowolnej funkcji grupowej.

Klauzule HAVING i WHERE mogą wystąpić w tym samym zapytaniu — w takim przypadku najpierw będzie zastosowany test z klauzuli WHERE, a następnie — z klauzuli HAVING.

Zapisanie w klauzuli HAVING warunku, który jest sprawdzany na poziomie wierszy, nie jest błędem składniowym (czyli MySQL prawidłowo zinterpretuje i wykona takie zapytanie), ale taka instrukcja jest nie tylko nieelegancka i nieczytelna, ale również może być dłużej wykonywana.

Kolejność wykonywania klauzuli zapytań

Logiczna kolejność wykonywania zapytania zawierającego omówione wyżej klauzule jest następująca:

1. Jako pierwsza wykonywana jest klauzula FROM. Jeżeli zapytanie odwołuje się do wielu tabel, są one kolejno ze sobą złączane.

2. Otrzymany w ten sposób zbiór pośredni jest filtrowany na podstawie warunku logicznego umieszczonego w klauzuli WHERE. Tylko te wiersze, dla których jest on prawdziwy, trafiają do kolejnego zbioru pośredniego.

3. Następnie wykonywana jest klauzula GROUP BY, czyli grupowane są tylko przefiltrowane wiersze.

4. Utworzone grupy są filtrowane poprzez porównanie ich z warunkiem umieszczonym w klauzuli HAVING.

5. Wybrane w poprzednim punkcie wiersze są zwracane, czyli wykonywana jest klauzula SELECT.

6. Następnie wiersze są sortowane, czyli wykonywana jest klauzula ORDER BY.

7. Na końcu liczba wierszy wyniku zapytania jest ograniczana podczas wykonywania klauzuli LIMIT.

WNIOSKI

Wykonując powyższe ćwiczenia poznałem nowe możliwości serwera bazy danych MySQL pod względem wyświetlania danych za pomocą funkcji SELECT i jej różnych klauzul i funkcji. Poznałem funkcje grupujące i skalarne. Skalarne dzielą się na funkcje tekstowe, funkcje liczbowe, funkcje daty i czasu oraz funkcje konwersji. Nauczyłem się również grupować dane podawane w funkcji SELECT przy użyciu komendy GROUP BY. Poznałem również kolejność wykonywania klauzyli zapytań. Moim zdaniem wykorzystując różnorodne funkcje języka SQL możemy poprawić nasza prace z bazą danych, ponieważ dają one bardzo dużo możliwości i ułatwiają przejżyste i rzeczowe wyświetlanie danych z tabel.