Как использовать функции в SQL

Введение

Работая с реляционными базами данных и языком структурированных запросов (SQL), вы можете хранить, управлять и извлекать данные из системы управления реляционными базами данных. SQL позволяет извлекать данные в том же виде, в котором они были сохранены в базе данных.

SQL также может выполнять вычисления и манипулировать данными с помощью функций. Например, с помощью функций можно получить цены товаров, округлённые до ближайшего доллара, рассчитать среднее количество покупок товаров или определить количество дней, оставшихся до истечения срока гарантии на покупку.

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

Предпосылки

Для работы с этим руководством вам понадобится компьютер с установленной системой управления реляционными базами данных (СУРБД) на базе SQL. Инструкции и примеры в этом руководстве были проверены в следующей среде:

• Сервер под управлением Ubuntu 20.04 с пользователем без прав root, обладающим правами администратора, и брандмауэром, настроенным с помощью UFW, как описано в Руководстве по начальной настройке сервера для Ubuntu 20.04.
• MySQL установлен и защищён на сервере, как описано в руководстве “Установка MySQL в Ubuntu 20.04”. В данном руководстве предполагается использование пользователя MySQL без прав root, созданного в процессе, описанном в шаге 3.
• Базовое введение в выполнение запросов SELECT для выборки данных из базы данных, как описано в руководстве “Как выбирать строки из таблиц в SQL”.

Примечание: Многие СУБД используют собственную реализацию SQL. Хотя команды, описанные в этом руководстве, работают в большинстве СУБД, стандартный синтаксис SQL определяет лишь ограниченное количество функций. Кроме того, поддержка стандартного синтаксиса различается в зависимости от СУБД. Точный синтаксис или выходные данные могут отличаться при тестировании в системе, отличной от MySQL.

Вам также понадобится база данных с таблицами, загруженными демонстрационными данными, чтобы вы могли использовать эти функции. Рекомендуем вам ознакомиться с разделом “Подключение к MySQL и настройка демонстрационной базы данных”, чтобы узнать больше о подключении к серверу MySQL и создании тестовой базы данных, используемой в примерах этого руководства.

Подключение к MySQL и настройка образца базы данных

В этом разделе вы подключитесь к серверу MySQL и создадите образец базы данных, чтобы иметь возможность следовать примерам из этого руководства.

Если ваша система базы данных SQL работает на удаленном сервере, войдите на свой сервер через SSH:

ssh sammy@your_server_ip

Затем откройте среду сервера MySQL вместо Сэмми Введите ваше имя пользователя MySQL:

mysql -u sammy -p

Создайте базу данных под названием «книжный магазин»:

CREATE DATABASE bookstore;

Если база данных была создана успешно, вы получите вывод, подобный следующему:

Output
Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных книжного магазина, выполните команду USE:

USE bookstore;

Вы увидите следующий вывод:

Output
Database changed

Выбрав базу данных, вы можете создать в ней примеры таблиц. В этом руководстве мы будем использовать вымышленную библиотеку, в которой продаётся множество книг разных авторов.

Таблица «Инвентарь» содержит информацию о книгах, имеющихся в наличии в книжном магазине. Она состоит из следующих столбцов:

• book_id: Этот столбец содержит идентификатор каждой книги, представленный типом данных int. Этот столбец становится первичным ключом таблицы, а каждое значение — уникальным идентификатором соответствующей строки.
• Автор: В этом столбце содержится имя автора книги, выраженное с использованием типа данных varchar длиной не более 50 символов.
• Название: В этом столбце содержится название купленной книги, выраженное с использованием типа данных varchar и длиной не более 200 символов.
• Introduction_date: В этом столбце используется тип данных «дата» для записи даты, когда каждая книга была представлена книжным магазином.
• Запасы: в этом столбце с использованием целочисленного типа данных указывается количество книг, имеющихся в наличии в книжном магазине.
• Цена: В этом столбце хранится розничная цена книги с использованием десятичного типа данных с максимум 5 значениями до десятичной точки и 2 значениями после нее.

Создайте образец таблицы с помощью следующей команды:

CREATE TABLE inventory (
book_id int,
author varchar(50),
title varchar(200),
introduction_date date,
stock int,
price decimal(5, 2),
PRIMARY KEY (book_id)
);

Если напечатан следующий вывод, таблица создана:

Output
Query OK, 0 rows affected (0.00 sec)

Затем загрузите таблицу покупок некоторыми образцами данных, выполнив следующую операцию INSERT INTO:

INSERT INTO inventory
VALUES
(1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),
(2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),
(3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),
(4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),
(5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);

Операция INSERT INTO добавляет пять книг с указанными значениями в таблицу inventory. Следующий вывод показывает, что все пять строк были добавлены:

Output
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

Теперь вы готовы следовать оставшейся части руководства и начать использовать функции в SQL.

Понимание функций SQL

Функции — это именованные выражения, которые принимают одно или несколько значений, выполняют вычисления или преобразования данных и возвращают новое значение. Функции SQL можно рассматривать аналогично математическим функциям. Например, функция log(x) принимает значение x и возвращает логарифм x.

Например, если вы хотите получить все названия книг с их ценами, от самой дорогой к самой дешевой, вы можете выполнить следующую команду:

SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;

Вы получите вывод, подобный следующему:

Output
+------------------------------------+-------+-------------------+
| title | price | introduction_date |
+------------------------------------+-------+-------------------+
| Pride and Prejudice | 42.13 | 2022-10-04 |
| The Adventures of Huckleberry Finn | 23.15 | 2022-10-01 |
| The Time Machine | 21.99 | 2022-09-23 |
| The Picture of Dorian Gray | 20.83 | 2022-10-01 |
| Frankenstein | 17.43 | 2022-07-23 |
+------------------------------------+-------+-------------------+
5 rows in set (0.000 sec)

В этой команде заголовок, цена и введение_дата — это имена столбцов, а на выходе база данных показывает нетронутые значения, извлеченные из этих столбцов для каждой книги: полное название книги, цена и дата поступления книги в библиотеку.

Однако вам может потребоваться извлечь значения из базы данных после какой-либо обработки или манипуляции. Вас могут заинтересовать цены книг, округлённые до ближайшего доллара, названия книг, набранные заглавными буквами, или год выпуска, без учёта месяца и дня. В этом случае используется функция.

Функции SQL можно разделить на несколько групп в зависимости от типа данных, с которыми они работают. Вот наиболее часто используемые функции:

• Математические функции: функции, которые оперируют числовыми значениями и выполняют вычисления, такие как округление, логарифмирование, извлечение квадратных корней или возведение в степень.
• Функции обработки строк: функции, которые работают со строками и текстовыми полями и выполняют преобразования текста, такие как преобразование текста в верхний регистр, усечение или замена слов в значениях.
• Функции даты и времени: функции, работающие с полями даты. Эти функции выполняют вычисления и преобразования, например, добавляют количество дней к заданной дате или вычитают год из полной даты.
• Агрегатные функции: особый случай математических функций, которые оперируют значениями, полученными из нескольких строк, например, вычисляют среднюю цену для всех строк.

Примечание: Большинство реляционных баз данных, включая MySQL, расширяют стандартный набор функций, определённый стандартом SQL, добавляя дополнительные операции, специфичные для ядра СУБД. Многие функции, не входящие в стандартный набор функций SQL, работают одинаково во многих базах данных, в то время как другие специфичны для конкретной СУБД и её уникальных функций. Вы можете обратиться к документации по выбранной вами СУБД, чтобы узнать больше о функциях, предоставляемых этой СУБД. В случае MySQL вы можете узнать больше о встроенных функциях и ссылках на операторы.

В следующем примере показан общий синтаксис использования вымышленной, несуществующей функции с именем EXAMPLE для изменения результатов значений цен в базе данных инвентаря книжного магазина с помощью запроса SELECT:

SELECT EXAMPLE(price) AS new_price FROM inventory;

Функция (EXAMPLE) принимает имя столбца (price) в качестве аргумента в скобках. Эта часть запроса указывает базе данных выполнить функцию EXAMPLE для значений цены в столбце и вернуть результаты этой операции. AS new_price указывает базе данных назначить временное имя (new_price) значениям, вычисленным в ходе запроса. С его помощью можно идентифицировать результаты функции в выходных данных и ссылаться на вычисленные значения с помощью предложений WHERE и ORDER BY.

В следующем разделе вы будете использовать математические функции для выполнения распространенных вычислений.

Использование математических функций

Математические функции оперируют числовыми значениями, такими как цена книги или количество книг на складе в базе данных-образце. Их можно использовать для выполнения вычислений в базе данных, чтобы адаптировать результаты к вашим потребностям.

Округление — одно из наиболее распространённых применений математических функций в SQL. Предположим, вам нужно получить цены всех книг, округлив их только до ближайшего доллара. Для этого можно использовать функцию КРУГЛЫЙ Используйте , который выполняет операцию округления.

Выполните следующую команду:

SELECT title, price, ROUND(price) AS rounded_price FROM inventory;

Вы увидите вывод, подобный следующему:

Output
+------------------------------------+-------+---------------+
| title | price | rounded_price |
+------------------------------------+-------+---------------+
| The Picture of Dorian Gray | 20.83 | 21 |
| Pride and Prejudice | 42.13 | 42 |
| The Time Machine | 21.99 | 22 |
| Frankenstein | 17.43 | 17 |
| The Adventures of Huckleberry Finn | 23.15 | 23 |
+------------------------------------+-------+---------------+
5 rows in set (0.000 sec)

Приведенная выше команда возвращает значения столбцов. заголовок и цена с временной колонной округленная_цена Что получается из функции РАУНД(цена) Эта функция принимает один аргумент — имя столбца (здесь, цена) и округляет значения в этом столбце таблицы до ближайшего целого числа.

Функция ROUND также может принимать дополнительные аргументы, указывающие количество знаков после запятой, до которых следует округлить значение, а также арифметические операции вместо имени столбца. Например, выполните следующий запрос:

SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;

Вы получите следующий результат:

Output
+------------------------------------+-------+-------+-------------+
| title | stock | price | stock_price |
+------------------------------------+-------+-------+-------------+
| The Picture of Dorian Gray | 4 | 20.83 | 83.3 |
| Pride and Prejudice | 12 | 42.13 | 505.6 |
| The Time Machine | 7 | 21.99 | 153.9 |
| Frankenstein | 9 | 17.43 | 156.9 |
| The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 |
+------------------------------------+-------+-------+-------------+
5 rows in set (0.000 sec)

Исполнение ОКРУГЛЫЙ(Цена * Запас, 1) Сначала цена одной книги умножается на количество доступных книг, а затем полученная цена округляется до первой десятой. Результат будет представлен во временном столбце Stock_Price.

Другие математические функции, встроенные в MySQL, включают тригонометрические функции, квадратные корни, степени, логарифмы и экспоненты. Подробнее об использовании математических функций в SQL можно узнать в руководстве «Использование математических выражений и агрегатных функций в SQL».

В следующем разделе вы будете манипулировать текстом из базы данных с помощью функций SQL.

Использование текстовых функций

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

Для этого вы можете использовать функцию ПОДРЕЗАТЬ Используйте эту функцию для удаления всех пробелов и лишних символов в начале и конце строки.

Рассмотрим следующий пример:

SELECT LOWER(title) AS title_lowercase FROM inventory;

На экран выводится следующий вывод:

Output
+------------------------------------+
| title_lowercase |
+------------------------------------+
| the picture of dorian gray |
| pride and prejudice |
| the time machine |
| frankenstein |
| the adventures of huckleberry finn |
+------------------------------------+
5 rows in set (0.001 sec)

Функция SQL LOWER принимает один аргумент и преобразует его содержимое в строчные буквы. Благодаря псевдониму столбца AS title_downcase результирующие данные представляются во временном столбце с именем title_downcase.

Теперь извлеките имена всех авторов, на этот раз преобразованные в заглавные буквы. Попробуйте выполнить следующий SQL-запрос:

SELECT UPPER(author) AS author_uppercase FROM inventory;

Вы получите следующий результат:

Output
+----------------------+
| author_uppercase |
+----------------------+
| OSCAR WILDE |
| JANE AUSTEN |
| HERBERT GEORGE WELLS |
| MARY SHELLEY |
| MARK TWAIN |
+----------------------+
5 rows in set (0.000 sec)

Вместо функции СТРОЧНЫЕ вы использовали функцию ПРОПИСНЫЕ, которая работает аналогично, но преобразует текст в верхний регистр. Обе функции можно использовать, если вы хотите обеспечить единообразие регистра при извлечении данных.

Ещё одна полезная функция для работы со строками — CONCAT, которая принимает несколько аргументов, содержащих текстовые значения, и объединяет их. Попробуйте извлечь авторов и названия книг в одном столбце. Для этого выполните следующую команду:

SELECT CONCAT(author, ': ', title) AS full_title FROM inventory;

Эта команда возвращает следующий вывод:

Output
+------------------------------------------------+
| full_title |
+------------------------------------------------+
| Oscar Wilde: The Picture of Dorian Gray |
| Jane Austen: Pride and Prejudice |
| Herbert George Wells: The Time Machine |
| Mary Shelley: Frankenstein |
| Mark Twain: The Adventures of Huckleberry Finn |
+------------------------------------------------+
5 rows in set (0.001 sec)

Функция CONCAT объединяет несколько строк и выполняется с тремя аргументами. Первый аргумент, author, ссылается на столбец с именем автора. Второй, :, — это произвольное строковое значение, разделяющее авторов и названия книг двоеточием. Последний аргумент, title, ссылается на столбец с названиями книг.

В результате этого запроса авторы и заголовки возвращаются во временном столбце с именем full_title, который напрямую объединяется ядром базы данных.

Другие строковые функции, встроенные в MySQL, включают функции поиска и замены строк, извлечения подстрок, дополнения и обрезки строковых значений, применения регулярных выражений и т. д. Подробнее об использовании функций SQL для конкатенации нескольких значений см. в статье «Манипулирование данными с помощью функций CAST и конкатенационных выражений SQL». Вы также можете ознакомиться с разделом «Строковые функции и операторы» в документации MySQL.

В следующем разделе вы будете использовать функции SQL для манипулирования датами из базы данных.

Использование функций даты и времени

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

Предположим, вам нужно разделить дату публикации книги на год, месяц и день отдельно, а не выводить ее в виде одного столбца с датами.

Попробуйте выполнить следующую команду:

SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;

Вы увидите вывод, подобный следующему:

Output
+-------------------+------+-------+------+
| introduction_date | year | month | day |
+-------------------+------+-------+------+
| 2022-10-01 | 2022 | 10 | 1 |
| 2022-10-04 | 2022 | 10 | 4 |
| 2022-09-23 | 2022 | 9 | 23 |
| 2022-07-23 | 2022 | 7 | 23 |
| 2022-10-01 | 2022 | 10 | 1 |
+-------------------+------+-------+------+
5 rows in set (0.000 sec)

Этот оператор SQL использует три отдельные функции: YEAR, MONTH и DAY. Каждая функция принимает в качестве аргумента имя столбца, в котором хранятся даты, и извлекает только одну часть полной даты: год, месяц или день соответственно. С помощью этих функций можно получать доступ к отдельным частям даты в SQL-запросах.

Ещё одна полезная функция для работы с датами — DATEDIFF, которая позволяет узнать количество дней между двумя датами. Теперь проверьте, сколько дней прошло между датой выхода каждой книги и текущей датой.

Выполните следующий запрос:

SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;

На экран выводится следующий вывод:

Output
+-------------------+------------+
| introduction_date | days_since |
+-------------------+------------+
| 2022-10-01 | -30 |
| 2022-10-04 | -27 |
| 2022-09-23 | -38 |
| 2022-07-23 | -100 |
| 2022-10-01 | -30 |
+-------------------+------------+
5 rows in set (0.000 sec)

Функция DATEDIFF принимает два аргумента: начальную и конечную дату. Функция DATEDIFF вычисляет количество дней, разделяющих эти две точки. Если конечная дата раньше, результат может быть отрицательным. В этом примере первый аргумент — имя столбца introduce_date, который содержит даты в таблице inventory. Второй аргумент — другая функция, CURRENT_DATE, которая представляет текущую системную дату. Выполнение этого запроса извлекает количество дней между этими двумя точками времени и помещает результаты во временный столбец days_since.

Примечание: DATEDIFF не входит в официальный набор стандартных функций SQL. Хотя многие базы данных поддерживают эту функцию, её синтаксис часто различается в зависимости от СУБД. В этом примере используется собственный синтаксис MySQL.

Другие функции работы с датами, встроенные в MySQL, включают сложение и вычитание временных интервалов и дат, форматирование дат для различных языковых форматов, извлечение названий дней и месяцев, а также создание новых значений дат. Подробнее о работе с датами в SQL можно узнать в руководстве «Как работать с датами и временем в SQL». Вы также можете обратиться к разделу «Функции даты и времени» в документации MySQL.

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

Использование агрегатных функций

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

• Основные агрегатные функции в SQL включают:
• AVG — среднее значение значений, на основе которых производятся расчеты.
• COUNT — количество значений, над которыми производятся вычисления.
• МАКСИМУМ для максимальной ценности.
• MIN — минимальное значение.
• СУММА — сумма всех значений.

Вы можете включить несколько агрегатных функций в свой запрос SELECT. Представьте, что вы хотите проверить количество книг, представленных в книжном магазине, максимальную цену каждой книги и среднюю цену по всему каталогу. Для этого выполните следующий оператор:

SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;

Эта команда возвращает следующий вывод:

Output
+-------+-----------+-----------+
| count | max_price | avg_price |
+-------+-----------+-----------+
| 5 | 42.13 | 25.106000 |
+-------+-----------+-----------+
1 row in set (0.001 sec)

В приведенном выше запросе используются сразу три агрегатные функции. Функция COUNT подсчитывает количество строк, по которым выполняется поиск. В этом примере заголовок передаётся в качестве аргумента, но поскольку количество строк для каждого отмеченного столбца будет одинаковым, в качестве аргумента функции можно использовать любое другое имя столбца. Функция MAX вычисляет максимальное значение из столбца price: Здесь имя столбца важно, поскольку вычисление выполняется над значениями в этом столбце. Последняя функция — функция AVG, которая вычисляет среднее значение всех цен из столбца price.

Использование агрегатных функций таким образом приводит к возврату в базу данных строки с временными столбцами, представляющими значения агрегированных вычислений. Исходные строки используются для внутренних вычислений, но не возвращаются в запросе. В этом примере агрегатные функции использовались для вычисления статистических значений по всей таблице inventory одновременно, с учётом всех строк при составлении сводки.

С помощью SQL вы также можете разделить строки таблицы на группы, а затем вычислить сумму значений этих групп по отдельности. Например, можно рассчитать среднюю цену книг разных авторов, чтобы узнать, чьи произведения самые дорогие. Подробнее о группировке строк для таких вычислений можно узнать в руководстве «Как использовать GROUP BY и ORDER BY в SQL». Подробнее об использовании агрегатов можно узнать в руководстве «Как использовать математические выражения и агрегатные функции в SQL».

Результат

В этом руководстве вы узнали, как использовать функции SQL для выполнения вычислений с данными и создания более сложных запросов. Эти функции помогают обрабатывать данные различными способами и получать наилучшие результаты. Имейте в виду, что точный синтаксис SQL может различаться в зависимости от типа вашей системы управления реляционными базами данных (СУРБД).