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?

Dlaczego warto 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.