Базовый курс SQL. Функции() для обработки данных. TRIM, DATE_PART, SQRT
Primary tabs
Forums:
Функции в SQL, как и в других языках программирования, помогают выполнять часто реализуемые операции. Например, с помощью функции RTRIM() можно "обрезать" лишние пробелы, справа от значения столбца. Как это сделать, мы рассмотрели на предыдущем уроке.
Очень удобно то, что типовые задачи уже реализованы, и нет необходимости повторять их на стороне клиентского приложения, однако используя функции в SQL, можно столкнуться с ощутимой проблемой. В отличии от универсальных операторов (SELECT, WHERE, FROM), функции в различных СУБД имеют разные названия для аналогичных действий.
Например,
- Получить часть строки.
- В PostgreSQL, Oracle, SQLite - SUBSTR()
- В MySQL, MariaDB, SQL Server - SUBSTRING()
- Преобразовать тип данных.
- В PostgreSQL, DB2 - CAST()
- В MySQL, MariaDB, SQL Server - CONVERT()
- Получить текущую дату.
- В PostgreSQL - CURRENT_DATE
- В MySQL, MariaDB - CURDATE()
- В Oracle - SYSDATE
- В SQLite - DATE()
Что это значит?
- Во-первых, то, что следует каждый раз уточнять синтаксис для своей СУБД
- Во-вторых, то, что код становится менее переносимым
Переносимый код - код, который продолжит корректно работать при изменении СУБД. Это необходимо при переносе проекта в новое окружение, который повлечёт принудительную смену СУБД.
Многие программисты стараются не использовать функции по причине снижения переносимости, но это всегда менее удачно с точки зрения производительности. Решение зависит от вас: тут нет правильного или неправильного выбора. Если вы используете функции в своём коде, просто добавляйте комментарии о том, что должен делать данный код, чтобы сэкономить время и нервы разработчика, его поддерживающего.
Типы функций
- Текстовые. Изменение регистра, отсечение или добавление пробелов
- Числовые. Простые и более сложные математические операции: возведение в степень, извлечение корня
- Даты и времени. Определение разницы во времени, корректности даты
- Системные. Получение информации о пользователе БД и др
Обычно функции применяются после операторов SELECT или WHERE, но и не только. Вы поймёте это, рассматривая примеры данного и следующего урока.
Текстовые функции
В предыдущем уроке мы с вами уже познакомились с функциями удаления лишних пробелов, TRIM(). Следующая функция - UPPER().
SELECT teacher_surname, UPPER(teacher_surname) AS upped_teacher_surname FROM Teachers ORDER BY teacher_surname;
teacher_surname | upped_teacher_surname ------------------------------------------------------- Донских | ДОНСКИХ Игнатьева | ИГНАТЬЕВА Колесникова | КОЛЕСНИКОВА Потапова | ПОТАПОВА Ростовская | РОСТОВСКАЯ Ростовский | РОСТОВСКИЙ Смирнов | СМИРНОВ Спрут | СПРУТ
Как несложно догадаться по названию, она преобразует символы в верхний регистр. Результат выполнения функции представлен в виде вычисляемого поля.
Добавим в нашу копилку ещё несколько часто используемых функций:
- LTRIM() и RTRIM() -- Удаляют пробелы в левой / правой части строки
- LEFT() и RIGTH() -- Возвращают символы из левой / правой части строки
- LENGTH() или DATALENGTH() или LEN() -- Возвращает длину строки
- LOWER() или LCASE() -- Преобразует строку в нижний регистр
- UPPER() или UCASE() -- Преобразует строку в верхний регистр
- SOUNDEX() -- Возвращает значение SOUNDEX строки
SOUNDEX - это алгоритм, преобразующий текст в буквенно-цифровой шаблон, который описывает его фонетическое представление. Иными словами, функция SOUNDEX() найдёт значения, созвучные предложенному. К сожалению, она не поддерживается в PostgreSQL, но, например, в MySQL запрос бы выглядел так:
SELECT teacher_id, teacher_surname FROM Teachers WHERE SOUNDEX(teacher_surname) = SOUNDEX('Игнатева');
teacher_id | teacher_surname ----------------------------------------- 778 | Игнатьева
Использование данной функции помогает найти строки несмотря на неточное написание: ошибку или попросту незнание, как точно пишется искомый предмет. В данном случае в фамилии преподавателя был пропущен мягкий знак, но поиск всё равно дал результаты благодаря сходному звучанию.
Функции для даты и времени
Дата и время хранятся в таблицах в соответствующем типе данных. Это позволяет эффективно сортировать, фильтровать а также хранить значения. Но в таком виде их невозможно использовать в приложениях, поэтому для преобразования приходится использовать специальные функции.
Функции даты и времени одни из наиболее важных в SQL, но и наименее согласованные между СУБД, а значит, снижают совместимость кода.
Рассмотрим на примерах разных СУБД, как можно извлечь часть даты.
PostgreSQL. Получим список конкурсов, проводившихся в 2020 году:
SELECT contest_name, contest_date FROM Contests WHERE DATE_PART('year', contest_date) = '2020';
Вывод:
contest_name | contest_date ------------------------------------------------------------- Фотовыставка Технологии будущего | 2020-10-25
Данная функция принимает 2 аргумента: первый указывает, какой именно фрагмент даты необходимо извлечь, а второй - поле, в котором производится поиск значения. Т.е. константа 'year' говорит СУБД о том, что нам требуется только год из столбца contest_date.
В MySQL и MariaDB это же можно сделать ещё проще, т.к. у них есть более узкая функция YEAR(), позволяющая получить год, а не всю дату:
SELECT contest_name, contest_date FROM Contests WHERE YEAR(contest_date) = '2020';
В Oracle придётся, напротив, скомбинировать 2 функции:
SELECT contest_name, contest_date FROM Contests WHERE to_number(to_char(contest_date, 'YYYY')) = 2020;
Здесь to_char() используется чтобы извлечь лишь часть даты, а to_number() - чтобы преобразовать полученное значение в число, которое можно будет сравнить с 2020.
Или можно воспользоваться смекалкой и оператором BETWEEN, который мы рассматривали в главе 5:
SELECT contest_name, contest_date FROM Contests WHERE contest_date BETWEEN to_date('01-01-2020') AND to_date('12-31-2020');
Также в примере выше была использована функция to_date(), преобразующая строку в формат даты и времени, чтобы сравнение производилось между одинаковыми типами данных.
SQLite предполагает такой формат:
SELECT contest_name, contest_date FROM Contests WHERE strftime('%Y', contest_date) = 2020;
---
Функций для работы с датами гораздо больше, чем описано в этом уроке. Можно выполнять и сравнение дат, и арифметические операции, форматировать даты и др. Но, как упоминалось выше, их синтаксис может существенно различаться. За подробностями обращайтесь к документации вашей СУБД.
Числовые функции
В основном это функции для выполнения сложных алгебраических и тригонометрических вычислений. Вот список наиболее популярных:
- ABS() -- Модуль числа
- SQRT() -- Корень квадратный
- SIN() -- Синус угла
- COS() -- Косинус угла
- TAN() -- Тангенс угла
- EXP() -- Экспонента числа
- PI() -- Число π
Итак, мы познакомились с некоторыми функциями, используемыми SQL. Далее мы рассмотрим, какие функции применяются к результатам запроса.
Следующая страница. Функции() для обработки результатов запроса.
Оглавление.
- Log in to post comments
- 1712 reads