MySQL Функции: низови, числови, дефинирани от потребителя, съхранени
Маркъс Алън
Какво представляват функциите?
MySQL може да прави много повече от просто съхраняване и извличане на данни. Ние също можем извършват манипулации върху данните преди да го извлечете или запазите. Ето къде MySQL Влизат функциите. Функциите са просто парчета код, които изпълняват някои операции и след това връщат резултат. Някои функции приемат параметри, докато други функции не приемат параметри.
Нека разгледаме накратко един пример за MySQL функция. По подразбиране MySQL записва типове данни за дата във формат “ГГГГ-ММ-ДД”. Да предположим, че сме изградили приложение и нашите потребители искат датата да бъде върната във формат „ДД-ММ-ГГГГ“, можем да използваме MySQL вградена функция DATE_FORMAT за постигане на това. DATE_FORMAT е една от най-използваните функции в MySQL. Ще го разгледаме по-подробно, докато разгръщаме урока.
Защо да използвате функции?
Въз основа на примера, даден във въведението, хората с опит в компютърното програмиране може да си помислят „Защо да се притеснявам MySQL Функции? Същият ефект може да се постигне със скриптов/програмен език?“ Вярно е, че можем да постигнем това, като напишем някои процедури/функции в приложната програма.
Връщайки се към нашия пример DATE във въведението, за да могат нашите потребители да получат данните в желания формат, бизнес слоят ще трябва да извърши необходимата обработка.
Това се превръща в проблем, когато приложението трябва да се интегрира с други системи. Когато използваме MySQL функции като DATE_FORMAT, тогава можем да имаме тази функционалност, вградена в базата данни и всяко приложение, което се нуждае от данните, ги получава в необходимия формат. това намалява повторната работа в бизнес логиката и намалява несъответствията в данните.
Друга причина, поради която трябва да обмислим използването на MySQL функции е фактът, че може да помогне за намаляване на мрежовия трафик в клиент/сървър приложения. Business Layer ще трябва само да се обади на съхранените функции, без да е необходимо да манипулира данни. Средно използването на функции може да помогне значително да подобри цялостната производителност на системата.
Видове функции
Вградени функции
MySQL идва в комплект с редица вградени функции. Вградените функции са просто функции, които вече са внедрени в MySQL сървър. Тези функции ни позволяват да извършваме различни видове манипулации върху данните. Вградените функции могат основно да бъдат категоризирани в следните най-използвани категории.
- Стрингови функции – работят с низови типове данни
- Числови функции – работят с числови типове данни
- Функции за дата – работа с типове данни за дата
- Агрегатни функции – работят с всички горепосочени типове данни и произвеждат обобщени набори от резултати.
- Други функции - MySQL поддържа и други типове вградени функции, но ние ще ограничим нашия урок само до горепосочените функции.
Нека сега разгледаме подробно всяка от функциите, споменати по-горе. Ще обясним най-използваните функции с помощта на нашия “Myflixdb”.
Стрингови функции
Вече разгледахме какво правят низовите функции. Ще разгледаме практически пример, който ги използва. В нашата таблица с филми заглавията на филмите се съхраняват с помощта на комбинации от малки и главни букви. Да предположим, че искаме да получим списък със заявки, който връща заглавията на филмите с главни букви. Можем да използваме функцията „UCASE“, за да направим това. Той приема низ като параметър и преобразува всички букви в главни букви. Показаният по-долу скрипт демонстрира използването на функцията “UCASE”.
SELECT `movie_id`,`title`, UCASE(`title`) FROM `movies`;
ТУК
- UCASE(`title`) е вградената функция, която приема заглавието като параметър и го връща с главни букви с името на псевдонима `upper_case_title`.
Изпълнение на горния скрипт в MySQL workbench срещу Myflixdb ни дава следните резултати, показани по-долу.
| 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 поддържа редица низови функции. За пълен списък на всички вградени низови функции вижте тази връзка http://dev.mysql.com/doc/refman/5.0/en/string-functions.html on MySQL уебсайта.
Числови функции
Както бе споменато по-рано, тези функции работят с числови типове данни. Можем да извършваме математически изчисления върху числови данни в SQL изразите.
Аритметични оператори
MySQL поддържа следните аритматични оператори, които могат да се използват за извършване на изчисления в SQL изразите.
| Име | Descriptйон |
|---|---|
| DIV | Целочислено разделение |
| / | делене |
| - | Изваждане |
| + | Допълнение |
| * | Умножение |
| % или MOD | Модул |
Нека сега разгледаме примери за всеки от горните оператори
Целочислено деление (DIV)
SELECT 23 DIV 6 ;
Изпълнението на горния скрипт ни дава следните резултати.
3
Оператор на разделяне (/)
Нека сега да разгледаме примера с оператора за деление. Ще модифицираме примера на DIV.
SELECT 23 / 6 ;
Изпълнението на горния скрипт ни дава следните резултати.
3.8333
Оператор за изваждане (-)
Нека сега да разгледаме примера с оператора за изваждане. Ще използваме същите стойности като в предишните два примера
SELECT 23 - 6 ;
Изпълнението на горния скрипт ни дава 17
Оператор за добавяне (+)
Нека сега да разгледаме примера с оператора за добавяне. Ще модифицираме предишния пример.
SELECT 23 + 6 ;
Изпълнението на горния скрипт ни дава 29
Оператор за умножение (*)
Нека сега да разгледаме примера с оператора за умножение. Ще използваме същите стойности като в предишните примери.
SELECT 23 * 6 AS `multiplication_result`;
Изпълнението на горния скрипт ни дава следните резултати.
| резултат_умножение |
|---|
| 138 |
Модулен оператор (-)
Операторът модулно дели N на M и ни дава остатъка. Нека сега да разгледаме примера с модулен оператор. Ще използваме същите стойности като в предишните примери.
SELECT 23 % 6 ;
OR
SELECT 23 MOD 6 ;
Изпълнението на горния скрипт ни дава 5
Нека сега да разгледаме някои от често срещаните числови функции в MySQL.
Етаж – тази функция премахва десетичните знаци от число и го закръгля до най-близкото най-ниско число. Показаният по-долу скрипт демонстрира използването му.
SELECT FLOOR(23 / 6) AS `floor_result`;
Изпълнението на горния скрипт ни дава следните резултати.
| Етаж_резултат |
|---|
| 3 |
Кръгъл – тази функция закръгля число с десетични знаци до най-близкото цяло число. Показаният по-долу скрипт демонстрира използването му.
SELECT ROUND(23 / 6) AS `round_result`;
Изпълнението на горния скрипт ни дава следните резултати.
| Кръг_резултат |
|---|
| 4 |
Ранд – тази функция се използва за генериране на произволно число, стойността му се променя при всяко извикване на функцията. Показаният по-долу скрипт демонстрира използването му.
SELECT RAND() AS `random_result`;
Съхранени функции
Съхранените функции са точно като вградените функции, с изключение на това, че трябва сами да дефинирате съхранената функция. След като съхранената функция бъде създадена, тя може да се използва в SQL изрази точно както всяка друга функция. Основният синтаксис за създаване на съхранена функция е както е показано по-долу
CREATE FUNCTION sf_name ([parameter(s)]) RETURNS data type DETERMINISTIC STATEMENTS
ТУК
- „CREATE FUNCTION sf_name ([параметър(и)])“ е задължително и разказва MySQL сървър, за да създаде функция с име `sf_name' с незадължителни параметри, дефинирани в скобите.
- „ВРЪЩА тип данни“ е задължително и указва типа данни, които функцията трябва да върне.
- „ДЕТЕРМИНИСТИЧЕН“ означава, че функцията ще върне същите стойности, ако са й предоставени същите аргументи.
- „ИЗЯВЛЕНИЯ“ е процедурният код, който функцията изпълнява.
Нека сега да разгледаме практически пример, който прилага вградена функция. Да предположим, че искаме да знаем на кои наети филми е изтекла датата на връщане. Можем да създадем съхранена функция, която приема датата на връщане като параметър и след това я сравнява с текущата дата в MySQL сървър. Ако текущата дата е по-малка от датата на върнатия филм, тогава връщаме „Не“, в противен случай връщаме „Да“. Показаният по-долу скрипт ни помага да постигнем това.
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|
Изпълнението на горния скрипт създаде съхранената функция `sf_past_movie_return_date`.
Нека сега тестваме нашата съхранена функция.
SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;
Изпълнение на горния скрипт в MySQL workbench срещу myflixdb ни дава следните резултати.
| 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 |
Дефинирани от потребителя функции
MySQL също поддържа дефинирани от потребителя функции, които разширяват MySQL. Дефинираните от потребителя функции са функции, които можете да създадете с помощта на език за програмиране като C, C++ и след това ги добавете към MySQL сървър. Веднъж добавени, те могат да се използват както всяка друга функция.
Oбобщение
- Функциите ни позволяват да подобрим възможностите на MySQL.
- Функциите винаги връщат стойност и по желание могат да приемат параметри.
- Вградените функции са функции, които се доставят с MySQL. Те могат да бъдат категоризирани според типовете данни, с които работят, т.е. низове, дата и цифрови вградени функции.
- Съхранените функции се създават от потребителя вътре MySQL сървър и може да се използва в SQL изрази.
- Дефинираните от потребителя функции се създават отвън MySQL и може да се включи в MySQL сървър.
