Funcții MySQL: șir, numeric, definit de utilizator, stocat

MySQL poate face mult mai mult decât doar stocarea și preluarea datelor. De asemenea, putem efectua manipulări asupra datelor înainte de a le prelua sau salva. Aici intervin Funcțiile MySQL. Funcțiile sunt pur și simplu bucăți de cod care efectuează unele operații și apoi returnează un rezultat. Unele funcții acceptă parametri, în timp ce alte funcții nu acceptă parametri.

Să ne uităm pe scurt la un exemplu de funcție MySQL. În mod implicit, MySQL salvează tipurile de date de date în formatul „AAAA-LL-ZZ”. Să presupunem că am construit o aplicație și utilizatorii noștri doresc ca data să fie returnată în formatul „ZZ-LL-AAAA”, putem folosi MySQL încorporat în funcția DATE_FORMAT pentru a realiza acest lucru. DATE_FORMAT este una dintre cele mai utilizate funcții din MySQL. O vom analiza în mai multe detalii pe măsură ce desfășurăm lecția.

Pe baza exemplului dat în introducere, oamenii cu experiență în programarea computerelor se pot gândi „De ce să ne deranjezi Funcțiile MySQL? Același efect poate fi obținut cu limbajul de scripting/programare?” Este adevărat că putem realiza asta prin scrierea unor proceduri/funcție în programul aplicației.

Revenind la exemplul nostru DATE din introducere, pentru ca utilizatorii noștri să obțină datele în formatul dorit, stratul de afaceri va trebui să facă procesarea necesară.

Aceasta devine o problemă atunci când aplicația trebuie să se integreze cu alte sisteme. Când folosim funcții MySQL, cum ar fi DATE_FORMAT, atunci putem avea acea funcționalitate încorporată în baza de date și orice aplicație care are nevoie de date le primește în formatul necesar. Acest lucru reduce reluarea în logica de afaceri și reduce inconsecvențele datelor.

Un alt motiv pentru care ar trebui să luăm în considerare utilizarea funcțiilor MySQL este faptul că poate ajuta la reducerea traficului de rețea în aplicațiile client/server . Business Layer va trebui doar să apeleze la funcțiile stocate fără a fi nevoie să manipuleze datele. În medie, utilizarea funcțiilor poate ajuta la îmbunătățirea semnificativă a performanței generale a sistemului.

Tipuri de funcții

Funcții încorporate

MySQL vine la pachet cu o serie de funcții încorporate. Funcțiile încorporate sunt pur și simplu funcții care sunt deja implementate în serverul MySQL. Aceste funcții ne permit să efectuăm diferite tipuri de manipulări asupra datelor. Funcțiile încorporate pot fi, practic, clasificate în următoarele categorii cele mai utilizate.

• Funcții șiruri – operează pe tipuri de date șiruri
• Funcții numerice – operează pe tipuri de date numerice
• Funcții de dată – operează pe tipuri de date de date
• Funcții agregate – operează pe toate tipurile de date de mai sus și produc seturi de rezultate rezumate.
• Alte funcții – MySQL acceptă și alte tipuri de funcții încorporate, dar ne vom limita lecția doar la funcțiile menționate mai sus.

Să ne uităm acum la fiecare dintre funcțiile menționate mai sus în detaliu. Vom explica cele mai utilizate funcții folosind „Myflixdb”.

Funcții șiruri

Ne-am uitat deja la ce fac funcțiile șir. Ne vom uita la un exemplu practic care le folosește. În tabelul nostru de filme, titlurile filmelor sunt stocate folosind combinații de litere mici și mari. Să presupunem că vrem să obținem o listă de interogări care returnează titlurile filmelor cu litere mari. Putem folosi funcția „UCASE” pentru a face asta. Ia un șir ca parametru și convertește toate literele în majuscule. Scriptul prezentat mai jos demonstrează utilizarea funcției „UCASE”.

SELECTAȚI `movie_id`,`title`, UCASE(`title`) FROM `movies`;

• UCASE(`title`) este funcția încorporată care ia titlul ca parametru și îl returnează cu litere mari cu numele de alias `upper_case_title`.

Executarea scriptului de mai sus în MySQL workbench împotriva Myflixdb ne oferă următoarele rezultate prezentate mai jos.

MySQL acceptă o serie de funcții șir. Pentru o listă completă a tuturor funcțiilor string încorporate, consultați acest link http://dev.mysql.com/doc/refman/5.0/en/string-functions.html pe site-ul MySQL.

Funcții numerice

După cum am menționat mai devreme, aceste funcții operează pe tipuri de date numerice. Putem efectua calcule matematice pe date numerice din instrucțiunile SQL.

Operatori aritmetici

MySQL acceptă următorii operatori aritmetici care pot fi utilizați pentru a efectua calcule în instrucțiunile SQL.

Să ne uităm acum la exemple ale fiecărui operator de mai sus

Diviziune întreagă (DIV)

SELECT 23 DIV 6 ;

Executarea scriptului de mai sus ne oferă următoarele rezultate.

3

Operator de divizie (/)

Să ne uităm acum la exemplul de operator de diviziune. Vom modifica exemplul DIV.

SELECTARE 23 / 6 ;

Executarea scriptului de mai sus ne oferă următoarele rezultate.

3,8333

Operatorul de scădere (-)

Să ne uităm acum la exemplul de operator de scădere. Vom folosi aceleași valori ca în cele două exemple precedente

SELECTARE 23 - 6 ;

Executarea scriptului de mai sus ne dă 17

Operator de adăugare (+)

Să ne uităm acum la exemplul de operator de adăugare. Vom modifica exemplul anterior.

SELECTARE 23 + 6 ;

Executarea scriptului de mai sus ne dă 29

Operator de multiplicare (*)

Să ne uităm acum la exemplul de operator de multiplicare. Vom folosi aceleași valori ca în exemplele anterioare.

SELECT 23 * 6 AS `rezultat_multiplicare`;

Executarea scriptului de mai sus ne oferă următoarele rezultate.

Modul operator (-)

Operatorul modulo împarte N la M și ne dă restul. Să ne uităm acum la exemplul de operator modulo. Vom folosi aceleași valori ca în exemplele anterioare.

SELECTARE 23 % 6 ;

SAU

SELECT 23 MOD 6 ;

Executarea scriptului de mai sus ne oferă 5

Să ne uităm acum la unele dintre funcțiile numerice comune din MySQL.

Etaj – această funcție elimină zecimale dintr-un număr și îl rotunjește la cel mai apropiat număr. Scriptul prezentat mai jos demonstrează utilizarea acestuia.

SELECT FLOOR(23 / 6) AS `floor_result`;

Executarea scriptului de mai sus ne oferă următoarele rezultate.

Round – această funcție rotunjește un număr cu zecimale la cel mai apropiat număr întreg. Scriptul prezentat mai jos demonstrează utilizarea acestuia.

SELECTARE ROUND(23 / 6) AS `rundă_rezultat`;

Executarea scriptului de mai sus ne oferă următoarele rezultate.

Rand – această funcție este folosită pentru a genera un număr aleator, valoarea acestuia se modifică de fiecare dată când funcția este apelată. Scriptul prezentat mai jos demonstrează utilizarea acestuia.

SELECTAȚI RAND() CA `rezultat_aleatoriu`;

Funcții stocate

Funcțiile stocate sunt la fel ca și funcțiile încorporate, cu excepția faptului că trebuie să definiți singur funcția stocată. Odată ce o funcție stocată a fost creată, aceasta poate fi utilizată în instrucțiuni SQL la fel ca orice altă funcție. Sintaxa de bază pentru crearea unei funcții stocate este cea prezentată mai jos

CREATE FUNCTION sf_name ([parametru(i)])

   RETURNS tipul de date

   DETERMINAT

   DECLARAȚII

• „CREATE FUNCTION sf_name ([parameter(s)]) „ este obligatoriu și îi spune serverului MySQL să creeze o funcție numită `sf_name' cu parametri opționali definiți în paranteză.
• „RETURNS data type” este obligatoriu și specifică tipul de date pe care funcția ar trebui să-l returneze.
• „DETERMINISTIC” înseamnă că funcția va returna aceleași valori dacă îi sunt furnizate aceleași argumente.
• „DECLARAȚII” este codul procedural pe care îl execută funcția.

Să ne uităm acum la un exemplu practic care implementează o funcție încorporată. Să presupunem că vrem să știm ce filme închiriate au trecut de data de întoarcere. Putem crea o funcție stocată care acceptă data de returnare ca parametru și apoi o compară cu data curentă în serverul MySQL. Dacă data curentă este mai mică decât data de întoarcere a filmului, atunci revenim „Nu”, altfel revenim „Da”. Scriptul prezentat mai jos ne ajută să realizăm acest lucru.

DELIMITOR |

CREATE FUNCTION sf_past_movie_return_date (return_date DATE)

  RETURNĂ VARCHAR(3)

   DETERMINAT

    ÎNCEPE

     DECLARE sf_value VARCHAR(3);

        IF curdate() > return_date

            THEN SET sf_value = 'Da';

        ELSEIF curdate() <= return_date

            THEN SET sf_value = 'Nu';

        END IF;

     RETURN sf_value;

    SFÂRȘIT|

Executarea scriptului de mai sus a creat funcția stocată `sf_past_movie_return_date`.

Să testăm acum funcția noastră stocată.

SELECTează `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) DIN `movierentals`;

Executarea scriptului de mai sus în MySQL workbench împotriva myflixdb ne oferă următoarele rezultate.

Funcții definite de utilizator

MySQL acceptă și funcții definite de utilizator care extind MySQL. Funcțiile definite de utilizator sunt funcții pe care le puteți crea folosind un limbaj de programare precum C, C++ etc. și apoi le puteți adăuga la serverul MySQL. Odată adăugate, acestea pot fi folosite la fel ca orice altă funcție.