Поиск по сайту:

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


Автор выбрал программу Write for DOnations.

Введение

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

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

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

Предпосылки

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

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

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

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

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

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

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

  1. ssh sammy@your_server_ip

Затем откройте приглашение сервера MySQL, заменив sammy именем вашей учетной записи пользователя MySQL:

  1. mysql -u sammy -p

Создайте базу данных с именем bookstore:

  1. CREATE DATABASE bookstore;

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

Output
Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных bookstore, выполните следующую инструкцию USE:

  1. USE bookstore;

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

Output
Database changed

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

Таблица inventory будет содержать данные о книгах в книжном магазине. Он будет содержать следующие столбцы:

  • book_id: этот столбец содержит идентификатор каждой книги, представленный типом данных int. Этот столбец станет первичным ключом таблицы, а каждое значение станет уникальным идентификатором соответствующей строки.
  • author: в этом столбце содержится имя автора книги, выраженное с использованием типа данных varchar, не более 50 символов.
  • title: в этом столбце содержится название купленной книги, выраженное с использованием типа данных varchar, не более 200 символов.
  • introduction_date. При использовании типа данных date в этом столбце содержится дата, когда каждая книга была представлена книжным магазином.
  • stock: в этом столбце содержится количество книг, имеющихся в книжном магазине, с использованием целочисленного типа данных int.
  • price: в этом столбце хранится розничная цена книги с использованием типа данных decimal с максимальным значением 5 до десятичной точки и 2 значения после него.

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

  1. CREATE TABLE inventory (
  2. book_id int,
  3. author varchar(50),
  4. title varchar(200),
  5. introduction_date date,
  6. stock int,
  7. price decimal(5, 2),
  8. PRIMARY KEY (book_id)
  9. );

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

Output
Query OK, 0 rows affected (0.00 sec)

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

  1. INSERT INTO inventory
  2. VALUES
  3. (1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),
  4. (2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),
  5. (3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),
  6. (4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),
  7. (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, запрашивая у базы данных значения для отдельных интересующих вас столбцов, указывая имена столбцов в операторе. .

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

  1. 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)

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

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

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

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

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

В следующем примере показан общий синтаксис использования воображаемой несуществующей функции с именем EXAMPLE для изменения результатов значений price в базе данных инвентаризации книжного магазина с помощью ВЫБЕРИТЕ запрос:

  1. SELECT EXAMPLE(price) AS new_price FROM inventory;

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

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

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

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

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

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

  1. 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)

Запрос выбирает значения из столбцов title и price без изменений, а также временный столбец rounded_price с результатами ROUND(price ) функция. Эта функция принимает один аргумент — имя столбца (в данном случае это price) и возвращает значения из этого столбца в таблице, округленные до ближайшего целого числа.

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

  1. 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)

Выполнение ROUND(price * stock, 1) сначала умножит цену одной книги на количество книг в наличии, а затем округлит полученную цену до первого знака после запятой. Результат будет представлен во временном столбце stock_price.

Другие математические функции, встроенные в MySQL, включают тригонометрические функции, квадратные корни, степени, логарифмы и экспоненты. Вы можете узнать больше об использовании математических функций в SQL в учебном пособии How To Use Mathematical Expressions and Aggregate Functions in SQL.

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

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

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

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

  1. 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_lowercase результирующие данные представлены во временном столбце с именем title_lowercase.

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

  1. 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)

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

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

  1. 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, относится к столбцу author, содержащему имена авторов. Второй, : , представляет собой произвольное строковое значение для разделения авторов и названий книг двоеточием. Последний, title, относится к столбцу с названиями книг.

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

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

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

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

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

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

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

  1. 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, которая позволяет получить количество дней между двумя датами. Теперь попробуйте проверить, сколько дней прошло между датой введения каждой книги и текущей датой.

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

  1. 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 вычисляет количество дней, разделяющих эти два момента времени. Результат может быть отрицательным числом, если дата окончания наступает раньше. В этом примере первым аргументом является имя столбца introduction_date, содержащего даты в таблице inventory. Второй аргумент — это другая функция, CURRENT_DATE, представляющая текущую системную дату. Выполнение этого запроса извлекает количество дней между этими двумя моментами времени и помещает результаты во временный столбец days_since.

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

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

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

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

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

Основные агрегатные функции в SQL включают следующее:

  • AVG для среднего значения, на котором выполняются вычисления.
  • COUNT для количества значений, над которыми выполняются вычисления.
  • MAX для максимального значения.
  • MIN для минимального значения.
  • SUM для суммы всех значений.

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

  1. 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 подсчитывает строки, которые ищет запрос. В этом примере в качестве аргумента передается title, но поскольку количество строк будет одинаковым для каждого проверенного столбца, вы также можете использовать любое другое имя столбца в качестве аргумента функции. Функция MAX вычисляет максимальное значение из столбца price: здесь важно имя столбца, поскольку вычисления выполняются на основе значений этого столбца. Последняя функция — это функция AVG, которая вычисляет среднее значение по всем ценам из столбца price.

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

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

Заключение

Следуя этому руководству, вы узнали, что такое функции SQL и как их использовать для работы с числами, строками и датами с помощью функций. Вы использовали ROUND для округления числовых значений, CONCAT для объединения нескольких столбцов в один и DATEDIFF для вычисления количества дней между двумя точками в время. Наконец, вы также использовали агрегатные функции, такие как COUNT, SUM или AVG, для создания сводок по нескольким строкам.

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

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

Если вы хотите узнать больше о различных концепциях языка SQL и работе с ним, мы рекомендуем вам ознакомиться с другими руководствами из серии «Как использовать SQL».