MySQL Функції: рядкові, числові, визначені користувачем, збережені
Маркус Аллен
Що таке функції?
MySQL може робити набагато більше, ніж просто зберігати та отримувати дані. Ми також можемо виконувати маніпуляції з даними перш ніж отримати або зберегти його. Ось де MySQL З’являються функції. Функції — це просто фрагменти коду, які виконують деякі операції, а потім повертають результат. Деякі функції приймають параметри, тоді як інші не приймають параметри.
Давайте коротко розглянемо приклад MySQL функція. За замовчуванням, MySQL зберігає типи даних дати у форматі «РРРР-ММ-ДД». Припустімо, ми створили додаток і наші користувачі хочуть, щоб дата поверталася у форматі «ДД-ММ-РРРР», ми можемо використовувати MySQL для цього вбудовано функцію DATE_FORMAT. DATE_FORMAT є однією з найбільш використовуваних функцій у MySQL. Ми розглянемо це більш детально під час розгортання уроку.
Навіщо використовувати функції?
Грунтуючись на прикладі, наведеному у вступі, люди з досвідом комп’ютерного програмування можуть подумати: «Навіщо турбуватися MySQL Функції? Такого ж ефекту можна досягти за допомогою мови сценаріїв/програмування?» Це правда, ми можемо досягти цього, написавши деякі процедури/функції в прикладній програмі.
Повертаючись до нашого прикладу DATE у вступі, щоб наші користувачі отримували дані в бажаному форматі, бізнес-рівень повинен буде виконати необхідну обробку.
Це стає проблемою, коли програму потрібно інтегрувати з іншими системами. Коли ми використовуємо MySQL такі функції, як DATE_FORMAT, тоді ми можемо вбудувати ці функції в базу даних, і будь-яка програма, якій потрібні дані, отримає їх у потрібному форматі. Це зменшує повторну роботу в бізнес-логіці та зменшує невідповідності даних.
Ще одна причина, чому ми повинні розглянути можливість використання MySQL функцій полягає в тому, що це може допомогти зменшити мережевий трафік у програмах клієнт/сервер. Бізнес-рівню потрібно буде лише викликати збережені функції без необхідності маніпулювати даними. У середньому використання функцій може допомогти значно підвищити загальну продуктивність системи.
Види функцій
Вбудовані функції
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.
| ІМ'Я | Опис |
|---|---|
| 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 ([parameter(s)]) “ є обов'язковим і розповідає MySQL сервер для створення функції під назвою `sf_name' з необов'язковими параметрами, визначеними в дужках.
- «Тип даних RETURNS» є обов’язковим і вказує тип даних, який має повернути функція.
- «ДЕТЕРМІНІСТИЧНИЙ» означає, що функція поверне однакові значення, якщо їй надано однакові аргументи.
- «ЗАЯВИ» це процедурний код, який виконує функція.
Давайте тепер розглянемо практичний приклад, який реалізує вбудовану функцію. Припустімо, ми хочемо знати, дата повернення яких із взятих напрокат фільмів минула. Ми можемо створити збережену функцію, яка приймає дату повернення як параметр, а потім порівнює її з поточною датою в 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 сервер. Після додавання їх можна використовувати як будь-яку іншу функцію.
Підсумки
- Функції дозволяють нам розширити можливості MySQL.
- Функції завжди повертають значення та, за бажанням, можуть приймати параметри.
- Вбудовані функції – це функції, які постачаються разом MySQL. Їх можна класифікувати відповідно до типів даних, з якими вони працюють, тобто рядків, дат і числових вбудованих функцій.
- Збережені функції створюються користувачем MySQL сервер і може використовуватися в операторах SQL.
- Визначені користувачем функції створюються ззовні MySQL і можуть бути включені в MySQL сервера.