Учебники

16) Функции

Какие функции?

MySQL может сделать гораздо больше, чем просто хранить и извлекать данные . Мы также можем выполнять манипуляции с данными перед их извлечением или сохранением. Вот тут-то и появляются функции MySQL. Функции — это просто фрагменты кода, которые выполняют некоторые операции и затем возвращают результат. Некоторые функции принимают параметры, в то время как другие функции не принимают параметры.

 Давайте кратко рассмотрим пример функции MySQL. По умолчанию MySQL сохраняет типы данных даты в формате «ГГГГ-ММ-ДД». Предположим, что мы создали приложение, и наши пользователи хотят, чтобы дата возвращалась в формате «DD-MM-YYYY», для этого мы можем использовать встроенную функцию MySQL DATE_FORMAT. DATE_FORMAT — одна из наиболее часто используемых функций в MySQL. Мы рассмотрим это более подробно, когда будем разворачивать урок.

 

Зачем использовать функции?

Функции 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_idtitleUCASE('title')
1667% Guilty67% GUILTY
6Angels and DemonsANGELS AND DEMONS
4Code Name BlackCODE NAME BLACK
5Daddy's Little GirlsDADDY'S LITTLE GIRLS
7Davinci CodeDAVINCI CODE
2Forgetting Sarah MarshalFORGETTING SARAH MARSHAL
9Honey moonersHONEY MOONERS
19movie 3MOVIE 3
1Pirates of the Caribean 4PIRATES OF THE CARIBEAN 4
18sample movieSAMPLE MOVIE
17The Great DictatorTHE GREAT DICTATOR
3X-MenX-MEN

MySQL поддерживает ряд строковых функций. Полный список всех встроенных строковых функций см. По этой ссылке http://dev.mysql.com/doc/refman/5.0/en/string-functions.html на веб-сайте 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`;

Выполнение вышеуказанного скрипта дает нам следующие результаты.

multiplication_result

138

 

Оператор по модулю (-)

Оператор по модулю делит N на M и дает нам остаток. Давайте теперь посмотрим на пример оператора по модулю. Мы будем использовать те же значения, что и в предыдущих примерах.

SELECT 23 % 6 ;

 

 
ИЛИ
 
 
SELECT 23 MOD 6 ;

 

Выполнение вышеуказанного скрипта дает нам 5

 

Давайте теперь посмотрим на некоторые из распространенных числовых функций в MySQL.

Этаж — эта функция удаляет десятичные разряды из числа и округляет до ближайшего наименьшего числа. Сценарий, показанный ниже, демонстрирует его использование.

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

 

Выполнение вышеуказанного скрипта дает нам следующие результаты.

Floor_result

3

 

Round — эта функция округляет число с десятичными разрядами до ближайшего целого числа. Сценарий, показанный ниже, демонстрирует его использование.

SELECT ROUND(23 / 6) AS `round_result`;

Выполнение вышеуказанного скрипта дает нам следующие результаты.

Round_result

4

 

Rand — эта функция используется для генерации случайного числа, значение которого изменяется при каждом вызове функции. Сценарий, показанный ниже, демонстрирует его использование.

SELECT RAND() AS `random_result`;

 

Хранимые функции

Хранимые функции аналогичны встроенным функциям, за исключением того, что вы должны определить хранимую функцию самостоятельно. После создания хранимой функции ее можно использовать в операторах SQL, как и любую другую функцию. Основной синтаксис для создания хранимой функции, как показано ниже

CREATE FUNCTION sf_name ([parameter(s)])
   RETURNS data type
   DETERMINISTIC
   STATEMENTS

 

ВОТ

  • «CREATE FUNCTION sf_name ([параметр (ы)])» является обязательным и указывает серверу MySQL создать функцию с именем «sf_name» с необязательными параметрами, определенными в скобках.
  • «RETURNS data type» является обязательным и указывает тип данных, который должна возвращать функция.
  • «ДЕТЕРМИНИСТИЧЕСКИЙ» означает, что функция будет возвращать одинаковые значения, если ей предоставлены одинаковые аргументы.
  • «ЗАЯВЛЕНИЯ» — это процедурный код, который выполняет функция.

 

Давайте теперь посмотрим на практический пример, который реализует встроенную функцию. Предположим, мы хотим знать, какие арендованные фильмы просрочены. Мы можем создать хранимую функцию, которая принимает дату возврата в качестве параметра и затем сравнивает ее с текущей датой на сервере 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_idmembership_numberreturn_dateCURDATE()sf_past_movie_return_date('return_date')
11NULL04-08-2012NULL
2125-06-201204-08-2012yes
2325-06-201204-08-2012yes
2225-06-201204-08-2012yes
33NULL04-08-2012NULL

 

Пользовательские функции

MySQL также поддерживает пользовательские функции, расширяющие MySQL. Пользовательские функции — это функции, которые можно создавать с помощью языка программирования, например C, C ++ и т. Д., А затем добавлять их на сервер MySQL. После добавления они могут использоваться как любая другая функция.

 

Резюме

  • Функции позволяют нам расширить возможности MySQL.
  • Функции всегда возвращают значение и могут по выбору принимать параметры.
  • Встроенные функции — это функции, которые поставляются с MySQL. Их можно распределить по категориям в соответствии с типами данных, с которыми они работают, т.е. строками, датой и числовыми встроенными функциями.
  • Хранимые функции создаются пользователем на сервере MySQL и могут использоваться в инструкциях SQL.
  • Пользовательские функции создаются вне MySQL и могут быть включены в сервер MySQL.