MySQL Funkcje: łańcuchowe, numeryczne, definiowane przez użytkownika, przechowywane
Co to są funkcje?
MySQL może zrobić znacznie więcej niż tylko przechowywanie i pobieranie danych. Możemy także wykonywać manipulacje na danych przed pobraniem lub zapisaniem. To tam gdzie MySQL Funkcje wchodzą. Funkcje są po prostu fragmentami kodu, które wykonują pewne operacje, a następnie zwracają wynik. Niektóre funkcje akceptują parametry, podczas gdy inne nie akceptują parametrów.
Przyjrzyjmy się pokrótce przykładowi MySQL funkcjonować. Domyślnie, MySQL zapisuje typy danych daty w formacie „RRRR-MM-DD”. Załóżmy, że zbudowaliśmy aplikację i nasi użytkownicy chcą, aby data była zwracana w formacie „DD-MM-RRRR”, możemy użyć MySQL wbudowaną funkcję DATE_FORMAT, aby to osiągnąć. DATE_FORMAT to jedna z najczęściej używanych funkcji w MySQLPrzyjrzymy się temu bardziej szczegółowo w trakcie lekcji.
Po co używać funkcji?
Bazując na przykładzie podanym we wstępie, osoby z doświadczeniem w programowaniu komputerowym mogą pomyśleć: „Po co się tym przejmować MySQL Funkcje? Ten sam efekt można osiągnąć za pomocą języka skryptowego/programowania?” To prawda, że możemy to osiągnąć pisząc pewne procedury/funkcje w programie użytkowym.
Wracając do naszego przykładu DATE we wstępie, aby nasi użytkownicy mogli uzyskać dane w pożądanym formacie, warstwa biznesowa będzie musiała dokonać niezbędnego przetwarzania.
Staje się to problemem, gdy aplikacja musi integrować się z innymi systemami. Kiedy używamy MySQL funkcje, takie jak DATE_FORMAT, wówczas możemy umieścić tę funkcjonalność w bazie danych, a każda aplikacja potrzebująca danych otrzyma je w wymaganym formacie. Ten ogranicza przeróbki logiki biznesowej i zmniejsza niespójności danych.
Kolejny powód, dla którego powinniśmy rozważyć użycie MySQL funkcji jest fakt, że może pomóc w zmniejszeniu ruchu sieciowego w aplikacjach klient/serwer. Warstwa biznesowa będzie musiała jedynie wywoływać zapisane funkcje bez konieczności manipulowania danymi. Średnio użycie funkcji może znacznie poprawić ogólną wydajność systemu.
Rodzaje funkcji
Wbudowane funkcje
MySQL jest dostarczany z wieloma wbudowanymi funkcjami. Wbudowane funkcje to po prostu funkcje już zaimplementowane w programie MySQL serwer. Funkcje te pozwalają nam wykonywać różne rodzaje manipulacji na danych. Wbudowane funkcje można zasadniczo podzielić na następujące najczęściej używane kategorie.
- Funkcje ciągów – operuj na typach danych typu string
- Funkcje numeryczne – operować na typach danych numerycznych
- Funkcje daty – operuj na typach danych dat
- Funkcje agregujące – przeprowadzaj operacje na wszystkich powyższych typach danych i generuj podsumowane zestawy wyników.
- Inne funkcje - MySQL obsługuje również inne typy wbudowanych funkcji, ale ograniczymy naszą lekcję tylko do wyżej wymienionych funkcji.
Przyjrzyjmy się teraz szczegółowo każdej z wyżej wymienionych funkcji. Będziemy wyjaśniać najczęściej używane funkcje za pomocą naszego „Myflixdb”.
Funkcje ciągów
Przyjrzeliśmy się już, do czego służą funkcje łańcuchowe. Przyjrzymy się praktycznemu przykładowi, który je wykorzystuje. W naszej tabeli filmów tytuły filmów są zapisywane przy użyciu kombinacji małych i wielkich liter. Załóżmy, że chcemy uzyskać listę zapytań, która zwraca tytuły filmów pisane wielkimi literami. Możemy do tego użyć funkcji „UCASE”. Przyjmuje ciąg znaków jako parametr i konwertuje wszystkie litery na wielkie. Poniższy skrypt demonstruje użycie funkcji „UCASE”.
SELECT `movie_id`,`title`, UCASE(`title`) FROM `movies`;
TUTAJ
- UCASE(`title`) to wbudowana funkcja, która przyjmuje tytuł jako parametr i zwraca go wielkimi literami z nazwą aliasu `upper_case_title`.
Wykonanie powyższego skryptu w MySQL workbench z Myflixdb daje nam następujące wyniki, pokazane poniżej.
movie_id | title | UCASE('title') |
---|---|---|
16 | 67% Guilty | 67% GUILTY |
6 | Angels and Demons | ANGELS AND DEMONS |
4 | Code Name Black | CODE NAME BLACK |
5 | Daddy's Little Girls | DADDY'S LITTLE GIRLS |
7 | Davinci Code | DAVINCI CODE |
2 | Forgetting Sarah Marshal | FORGETTING SARAH MARSHAL |
9 | Honey mooners | HONEY MOONERS |
19 | movie 3 | MOVIE 3 |
1 | Pirates of the Caribean 4 | PIRATES OF THE CARIBEAN 4 |
18 | sample movie | SAMPLE MOVIE |
17 | The Great Dictator | THE GREAT DICTATOR |
3 | X-Men | X-MEN |
MySQL obsługuje wiele funkcji łańcuchowych. Pełną listę wszystkich wbudowanych funkcji łańcuchowych znajdziesz pod tym linkiem http://dev.mysql.com/doc/refman/5.0/en/string-functions.html on MySQL stronie internetowej.
Funkcje numeryczne
Jak wspomniano wcześniej, funkcje te działają na typach danych numerycznych. Możemy wykonywać obliczenia matematyczne na danych numerycznych w poleceniach SQL.
Operatorzy arytematyczni
MySQL obsługuje następujące operatory arytmetyczne, których można używać do wykonywania obliczeń w poleceniach SQL.
Imię | Opis |
---|---|
DIV | Dzielenie liczb całkowitych |
/ | podział |
- | Odejmowanie |
+ | Dodatek |
* | Mnożenie |
% lub MOD | Moduł |
Przyjrzyjmy się teraz przykładom każdego z powyższych operatorów
Dzielenie liczb całkowitych (DIV)
SELECT 23 DIV 6 ;
Wykonanie powyższego skryptu daje nam następujące wyniki.
3
Operator dzielenia (/)
Przyjrzyjmy się teraz przykładowi operatora dzielenia. Zmodyfikujemy przykład DIV.
SELECT 23 / 6 ;
Wykonanie powyższego skryptu daje nam następujące wyniki.
3.8333
Operator odejmowania (-)
Przyjrzyjmy się teraz przykładowi operatora odejmowania. Użyjemy tych samych wartości, co w dwóch poprzednich przykładach
SELECT 23 - 6 ;
Wykonanie powyższego skryptu daje nam 17
Operator dodawania (+)
Przyjrzyjmy się teraz przykładowi operatora dodawania. Zmodyfikujemy poprzedni przykład.
SELECT 23 + 6 ;
Wykonanie powyższego skryptu daje nam 29
Operator mnożenia (*)
Przyjrzyjmy się teraz przykładowi operatora mnożenia. Użyjemy tych samych wartości, co w poprzednich przykładach.
SELECT 23 * 6 AS `multiplication_result`;
Wykonanie powyższego skryptu daje nam następujące wyniki.
wynik_mnożenia |
---|
138 |
Operator modulo (-)
Operator modulo dzieli N przez M i daje nam resztę. Przyjrzyjmy się teraz przykładowi operatora modulo. Użyjemy tych samych wartości, co w poprzednich przykładach.
SELECT 23 % 6 ;
OR
SELECT 23 MOD 6 ;
Wykonanie powyższego skryptu daje nam 5
Przyjrzyjmy się teraz niektórym typowym funkcjom numerycznym w MySQL.
piętro – ta funkcja usuwa miejsca dziesiętne z liczby i zaokrągla ją do najbliższej najniższej liczby. Skrypt pokazany poniżej demonstruje jej użycie.
SELECT FLOOR(23 / 6) AS `floor_result`;
Wykonanie powyższego skryptu daje nam następujące wyniki.
Wynik_piętra |
---|
3 |
Okrągły – ta funkcja zaokrągla liczbę z miejscami dziesiętnymi do najbliższej liczby całkowitej. Skrypt pokazany poniżej demonstruje jej użycie.
SELECT ROUND(23 / 6) AS `round_result`;
Wykonanie powyższego skryptu daje nam następujące wyniki.
Wynik_rundy |
---|
4 |
Rand – funkcja ta służy do generowania liczby losowej, jej wartość zmienia się przy każdym wywołaniu funkcji. Skrypt pokazany poniżej demonstruje jego użycie.
SELECT RAND() AS `random_result`;
Przechowywane funkcje
Funkcje przechowywane działają tak samo jak funkcje wbudowane, z tą różnicą, że musisz samodzielnie zdefiniować funkcję przechowywaną. Po utworzeniu funkcji składowanej można jej używać w instrukcjach SQL, tak jak każdej innej funkcji. Podstawowa składnia tworzenia funkcji składowanej jest pokazana poniżej
CREATE FUNCTION sf_name ([parameter(s)]) RETURNS data type DETERMINISTIC STATEMENTS
TUTAJ
- „UTWÓRZ FUNKCJĘ nazwa_sf ([parametr(y)]) “ jest obowiązkowe i mówi MySQL serwerze, aby utworzył funkcję o nazwie `sf_name' z opcjonalnymi parametrami zdefiniowanymi w nawiasach.
- „Typ danych ZWRACA” jest obowiązkowe i określa typ danych, które funkcja powinna zwrócić.
- „DETERMINISTYCZNY” oznacza, że funkcja zwróci te same wartości, jeśli zostaną jej podane te same argumenty.
- "SPRAWOZDANIA" to kod proceduralny, który wykonuje funkcja.
Przyjrzyjmy się teraz praktycznemu przykładowi, który implementuje wbudowaną funkcję. Załóżmy, że chcemy wiedzieć, które wypożyczone filmy mają datę zwrotu. Możemy utworzyć funkcję składowaną, która akceptuje datę zwrotu jako parametr, a następnie porównuje ją z bieżącą datą w MySQL serwer. Jeśli bieżąca data jest wcześniejsza niż data zwrotu filmu, wówczas zwracamy „Nie”, w przeciwnym razie zwracamy „Tak”. Skrypt pokazany poniżej pomaga nam to osiągnąć.
DELIMITER | CREATE FUNCTION sf_past_movie_return_date (return_date DATE) RETURNS VARCHAR(3) DETERMINISTIC BEGIN DECLARE sf_value VARCHAR(3); IF curdate() > return_date THEN SET sf_value = 'Yes'; ELSEIF curdate() <= return_date THEN SET sf_value = 'No'; END IF; RETURN sf_value; END|
Wykonanie powyższego skryptu utworzyło zapisaną funkcję `sf_past_movie_return_date`.
Przetestujmy teraz naszą zapisaną funkcję.
SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;
Wykonanie powyższego skryptu w MySQL workbench z myflixdb daje nam następujące wyniki.
movie_id | membership_number | return_date | CURDATE() | sf_past_movie_return_date('return_date') |
---|---|---|---|---|
1 | 1 | NULL | 04-08-2012 | NULL |
2 | 1 | 25-06-2012 | 04-08-2012 | yes |
2 | 3 | 25-06-2012 | 04-08-2012 | yes |
2 | 2 | 25-06-2012 | 04-08-2012 | yes |
3 | 3 | NULL | 04-08-2012 | NULL |
Funkcje zdefiniowane przez użytkownika
MySQL obsługuje również funkcje zdefiniowane przez użytkownika, które rozszerzają MySQL. Funkcje zdefiniowane przez użytkownika to funkcje, które można utworzyć za pomocą języka programowania, np C, C++ itp., a następnie dodaj je do MySQL serwer. Po dodaniu można ich używać tak samo, jak każdej innej funkcji.
Podsumowanie
- Funkcje pozwalają nam zwiększać możliwości MySQL.
- Funkcje zawsze zwracają wartość i opcjonalnie mogą przyjmować parametry.
- Funkcje wbudowane to funkcje dostarczane z urządzeniem MySQL. Można je kategoryzować według typów danych, na których operują, tj. ciągów znaków, dat i wbudowanych funkcji numerycznych.
- Przechowywane funkcje są tworzone przez użytkownika wewnątrz MySQL serwera i można go używać w instrukcjach SQL.
- Funkcje zdefiniowane przez użytkownika są tworzone na zewnątrz MySQL i można je włączyć do MySQL serwer.