Temat 9 : Wybrane funkcje skalarne 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 nauczymy się wywoływać funkcje systemowe i poznamy niektóre
funkcje serwera MySQL.

Wprowadzenie

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

1. Funkcje skalarne, które zwracają pojedynczą wartość obliczoną na podstawie zera lub większej liczby

prostych argumentów.

2. Funkcje grupujące, które zwracają pojedynczą wartość dla zbioru argumentów wywołania.
3. Funkcje typu RowSet, zwracające dane w postaci tabelarycznej, do których odwołujemy się tak jak do

tabel.

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

1. Funkcje tekstowe operujące na ciągach znaków.
2. Funkcje liczbowe operujące na liczbach.
3. Funkcje daty i czasu operujące na danych typu data/godzina.
4. Funkcje konwersji służące do zmiany typu danych.

1. 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ę.

LOWER(wartosc) – zamienia WIELKIE litery na małe;

SELECT stanowisko, LOWER(stanowisko) FROM pracownik WHERE id<3;

UPPER(wartosc) – zamienia małe litery na WIELKIE;

INITCAP(wartosc) – zamienia pierwsze litery w słowie na duże;

LPAD(wartosc, n ,’ciag’), RPAD(wartosc, n, ’ciag’) – uzupełnia kolumny z lewej (prawej) strony podanym
ciągiem aż do długości n znaków. Jeśli ciąg nie został podany to wypełnia spacjami;

select LOWER(nazwa), UPPER(‘miesci sie na’), INITCAP(adres)
from zespoly;

select LPAD(nazwa,25,’*’), RPAD(nazwa,25,’.’)from zespoly;

SUBSTR(wartość, n ,m) – z podanego łańcucha znaków wycina m znaków począwszy od pozycji n-tej;

INSTR(wartość, ‘ciąg’, [m, n]) – wskazuje miejsce pierwszego (n-tego) wystąpienia ciągu w łańcuchu
znaków począwszy od pozycji m-tej;

LTRIM(wartość ,’znaki’), RTRIM(wartość ,’znaki’) – usuwa z lewej strony (prawej) podane znaki
(spacje);

LENGTH(wartość) – zwraca długość łańcucha znaków;

select SUBSTR(nazwisko,3,4), INSTR(etat,’PROF.’), LENGTH(etat)
from pracownicy;

select nazwa, LTRIM(nazwa,’ABCD’)from zespoly;

REPLACE(źródło, wzór, nowy) – każde wystąpienie w źródle ciągu wzorzec zostanie zastąpione przez
ciąg nowy;

select etat, REPLACE(etat, ‘AS’, ‘**’)from pracownicy;

2. Funkcje liczbowe

ROUND(wartość, n) – zaokrągla wartość do n- tego dziesiętnego miejsca po przecinku. Pierwszy parametr
jest liczbą do zaokrąglenia, drugi wskazuje, do ilu pozycji chcemy zaokrąglić. Ujemna liczba 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, SZBD przyjmuje domyślnie jego wartość jako równą 0.

SELECT Round(3.1415926535897,4) , Round(3.1415926535897,0),
Round(3.1415926535897) from dual;

TRUNC(wartosc, n) – obcina wartość do n-tego dziesiętnego miejsca po przecinku. Pierwszy parametr jest
liczbą do obcięcia, drugi wskazuje, do ilu pozycji chcemy liczbę skrócić. Ujemna liczba 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.

SELECT Truncate(3.1415926535897,4) from dual;

select ROUND(123.456,1), ROUND(123.456), ROUND(123.456,-1),
TRUNC(123.456,1), TRUNC(123.456), TRUNC(123.456,-1)from dual;

CEIL(wartosc), FLOOR(wartosc) – najmniejsza (największą) liczba całkowita większa lub równa
(mniejsza lub równa) podanej wartości.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ą.

select FLOOR(1.5), CEIL(1.5)from dual;

POWER(wartosc, n) – podnosi wartość do podanej potęgi;

SQRT(wartosc) – oblicza pierwiastek kwadratowy z podanej wartości;

ABS(wartosc) – oblicza wartość bezwzględną wyrażenia. 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ć.

Funkcja ABS() użyta do sprawdzenia, czy dana liczba jest dodatnia:

SELECT * FROM towar WHERE cena != ABS(cena);

MOD(wartosc1, wartosc2) – zwraca resztę z dzielenia;

select POWER(2,16), SQRT(64),ABS(-100), MOD(123456789,10)from dual;

3. Funkcje daty i czasu

Kolejna często używana grupa funkcji, to funkcje operujące na argumentach będących zapisem daty lub czasu.
W prawie każdej bazie danych część przechowywanych w niej informacji musi mieć jakiś znacznik czasu —
atrybut pozwalający na sprawdzenie, kiedy rekord został dodany lub zmodyfikowany. Ponadto część
informacji, na przykład dane o poszczególnych transakcjach finansowych, po wyeliminowaniu dat ich
zawarcia, staje się bezwartościowa. Dlatego MySQL posiada predefiniowane funkcje pozwalające wykonywać
podstawowe operacje na danych tego typu.

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.

Odczytanie bieżącej daty i czasu systemowego

SELECT CURDATE(),CURTIME();

NOW()
Funkcja NOW() zwraca zarówno datę, jak i czas systemowy.

Przykład wywołania funkcji NOW(). Należy zwrócić uwagę, że nawet jeżeli funkcja wywoływana jest bez
parametrów, trzeba użyć nawiasów.

SELECT NOW();

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).

Lista zamówień złożonych w piątek.

SELECT * FROM zamowienia WHERE DAYOFWEEK(data) = 6;

4. Funkcje konwersji

Piąta wersja serwera MySQL umożliwia definiowanie kolumn lub deklarowanie zmiennych różnych typów:

1. Dane tekstowe (typy char, varchar, text) — mogą zawierać tekst lub kombinacje tekstu i liczb, na

przykład adres; mogą zawierać również liczby, na których nie są przeprowadzane obliczenia, takie jak
numery telefonów, numery katalogowe i kody pocztowe.

2. Dane binarne (typy binary, varbinary, BLOB) — mogą zawierać dowolne dane. Mogą to być zarówno

długie teksty, jak i grafika czy pliki multimedialne.

3. Dane liczbowe (typy tinyint, smallint, mediumint, int, bigint, decimal, float, double) — zawierają dane

liczbowe, na których są przeprowadzane obliczenia, przy czym dwa ostatnie typy są
zmiennoprzecinkowe, czyli takie dane przechowywane są z określoną dokładnością.

4. Daty (typy datetime, date, timestamp, time i year) — przechowują dane dotyczące daty i czasu.
5. Dane logiczne (typy bool, boolean) — w mySQL-u są to synonimy typu tinyint, przy czym 0 oznacza

fałsz a 1 — prawdę.

ASCII()
W wyniku działania funkcji ASCII() zostaje 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 .

Konwersja znaków na liczby

SELECT ASCII(nazwisko), nazwisko FROM pracownik;
+--++
| ASCII(lname) | lname |
+--++
| 83 | Stones |
| 83 | Stones |
| 77 | Matthew |
| 77 | Matthew |
| 67 | Cozens |
| 77 | Matthew |
| 83 | Stones |
| 83 | Stones |
| 72 | Hickman |
| 72 | Howard |
| 74 | Jones |
| 78 | Neill |
| 72 | Hendy |
| 78 | Neill |
| 72 | Hudson |
| 87 | Wolski |
+--++

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 .

Funkcję CHR() często stosuje się w celu oszukania prostych zabezpieczeń przed iniekcją kodu.

Po wpisaniu: SELECT CHAR(77,121,83,81,76);

Mamy:

+-+
| CHAR(77,121,83,81,76) |
+-+
| MySQL |
+-+

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

Zmiana podstawy liczb na z dziesiętnej binarną.

SELECT id, BIN(id) FROM towary;
++--+
| id | BIN(id) |
++--+
| 1 | 1 |
| 2 | 10 |
| 3 | 11 |
| 4 | 100 |
| 5 | 101 |
| 6 | 110 |

| 7 | 111 |
| 8 | 1000 |
| 9 | 1001 |
| 10 | 1010 |
| 11 | 1011 |
| 12 | 1100 |
++--+

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

CAST() jest najbardziej uniwersalną funkcją konwersji, np. zmiana na datę:

SELECT CAST('2001-1-1' AS date);
+--+
| CAST('2001-1-1' AS date) |
+--+
| 2001-01-01 |
+--+

ZADANIA

Zadanie 1. Stworzyć nową bazę danych nazwisko_magazyn i uzyskać do niej dostęp.
Zadanie 2. Stworzyć tabelę TOWARY z polami:

•

Id – klucz podstawowy, identyfikator towaru, int

•

Nazwa – nazwa towaru, varchar (30)

•

Rodzaj – rodzaj towaru, varchar(20)

•

Ilość – ilość sztuk towaru, int

•

Cena – cena jednostkowa, decimal(6,2)

•

Data zakupu – data zakupu towaru, date

Zadanie 3. Wypełnić tabelę danymi:

TOWARY

id

nazwa

Rodzaj

ilosc

cena

Data_zakupu

1

Core 2 Duo 2.66

procesor

10

266.50 2008-04-05

2

Gigabyte 7600GS

karta grafiki

14

166.66 2008-03-15

3

Seagate 320GB sata II

dysk twardy

6

194.00 2008-04-15

4

Gigabyte MA790X-DS4 płyta główna 7

399.49 2008-03-11

5

Galaxy 8800GT

karta grafiki

8

599.00 2008-01-11

6

Athlon 3000+

procesor

9

319.99 2008-02-29

7

Samsung 19'' SM 940N+ monitor

13

749.00 2008-03-22

8

LITE ON x52

CD-ROM

14

20.90

2008-04-01

Zadanie 4. Wyświetl bieżącą datę i czas dwoma sposobami.

Zadanie 5. Wyświetl rodzaj towaru dużymi literami, a nazwę małymi literami.

Zadanie 6. Wyświetl nazwę towaru ozdobioną z lewej strony gwiazdkami, a rodzaj ozdobiony z prawej strony
kropkami. (proponowana ilość znaków : 20)

Zadanie 7. Wyświetl nazwę i rodzaj towaru wyrzucając z pola rodzaj 5 pierwszych liter.

Zadanie 8. Wyświetl długość łańcucha znaków dla pól nazwa i rodzaj.

Zadanie 9. Wyświetl nazwę i rodzaj towaru ze zmianą ciągu znaków ‘pro’ na ‘***’.

Zadanie 10. Wyświetl nazwę, rodzaj, cenę i cenę towaru zaokrągloną do jednego miejsca po przecinku.

Zadanie 11. Wyświetl nazwę, rodzaj, cenę i cenę towaru, w której obcięto miejsca po przecinku.

Zadanie 12. Chcemy zmienić cenę towaru i określić nową wartość. Wyświetl nazwę i cenę towaru oraz
najmniejszą i największą liczba całkowitą większa lub równą (mniejszą lub równą) podanej cenie.

Zadanie 13. Wyświetl ilość towaru i pierwiastek 2. stopnia z ilości towaru.

Zadanie 14. Wyświetl ilość towaru i ilość towaru do potęgi 3 zaokrągloną do 2 miejsc po przecinku.

Zadanie 15. Wyświetl nazwę, ilość, cenę towaru zakupionego w środę.

Zadanie 16. Wyświetl nazwę, ilość, cenę towaru, datę towaru zakupionego w połowie każdego miesiąca.

Zadanie 17. Wyświetl nazwę, ilość, cenę towaru zakupionego we wtorek 11 dnia miesiąca.

Zadanie 18. Wyświetl ilość towaru oraz ilość towaru zakodowaną w kodzie binarnym.

Zadanie 19. Wyświetl nazwę towaru oraz pierwszy znak nazwy towaru w kodzie ASCII.

Zadanie 20. Wyświetl posługując się znakami ASCII na ekranie swoje imię. (kody ASCII znajdź w Internecie )