Функции и операторы

Обзор функций и операторов

Логические операторы:

AND, OR, NOT

Операторы сравнения:

=, <, >, >=, <=, <>, BETWEEN
IS NULL, IS NOT NULL
LIKE

Математические операции:

+, -, *, / и тд...

Операторы для символьных типов:

Математические функции:

ABS(n), ROUND(n) и тд...

Функция для символьных типов:

LOWER(str), UPPER(str)
CHARACTER_LENGTH(str)
SUBSTRING(), POSITION()

Функции для дат:

TO_DATE(str), TO_CHAR(dt)
EXTRACT(field FROM dt)

Для числовых типов данных

ABS

ABS - модуль числа

SELECT ABS(10), ABS(-10)

ROUND

ROUND - округление дробного числа до ближайшего целого.

SELECT ROUND(2.8), ROUND(3.5189), ROUND(0.001)

SELECT 2.8 as original_num, ROUND(2.8) as round_num
UNION ALL
SELECT 5.8951475 as original_num, ROUND(5.8951475) as round_num
UNION ALL
SELECT 2.5 as original_num, ROUND(2.5) as round_num
UNION ALL
SELECT 2.00001 as original_num, ROUND(2.00001) as round_num

TRUNC

TRUNC - отбрасывает дробную часть.

SELECT 2.8 as original_num, TRUNC(2.8) as trunc_num
UNION
SELECT 2.001, TRUNC(2.001)
UNION
SELECT 2.5, TRUNC(2.5)
UNION
SELECT -2.8, TRUNC(-2.8)

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

SELECT 2.8411254 as original_num, TRUNC(2.8411254, 2) as trunc_num

FLOOR

FLOOR - возвращает ближайшее целое число, которое меньше аргумента (искомого числа).

SELECT FLOOR (2.8), FLOOR(-2.8)

CEILING

CEILING - возвращает ближайшее целое число, которое больше аргумента (искомого числа).

SELECT CEILING (2.8), CEILING(-2.8)

Примеры работы этих функций

Пример 1: Возьмем album и вычислим среднее значение альбомов на одну музыкальную группу

SELECT n_albums, n_bands, n_albums / n_bands,
       cast(n_albums as numeric) / n_bands,
	   ROUND(cast(n_albums as numeric) / n_bands, 3),
	   ROUND(cast(n_albums as numeric) / n_bands),
	   FLOOR(cast(n_albums as numeric) / n_bands),
	   CEILING(cast(n_albums as numeric) / n_bands)
FROM (
SELECT COUNT(*) as n_albums,
       COUNT(DISTINCT band_id) as n_bands
  FROM album
	) as table_1

Пример 2: Ошибка деления на ноль

SELECT n_albums, n_bands, n_albums / n_bands,
       cast(n_albums as numeric) / NULLIF (n_bands, 0)
FROM (
SELECT COUNT(*) as n_albums,
       COUNT(DISTINCT band_id) as n_bands
  FROM album
	) as table_1

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

Пишется так: NULLIF(n_bands, 0) - в первом параметре колонка, а во втором ноль. Это означает следующее: если колонка n_bands когда-нибудь примет значение 0, то функция NULLIF заменить значение 0 на значение NULL.

LIKE для поиска шаблонов

Оператор LIKE

LIKE сравнивает выражение, в котором содержится текст на совпадение с шаблоном.

В шаблоне знак % - любое количество символов. А знак _ - один символ.

SELECT * FROM album WHERE name LIKE '% Music %'

Экранирование в операторе LIKE

Если нужно найти какую-либо запись, где есть знак %:

В этой ситуации используется символ экранирования. Синтаксис такой:

LIKE '%f%%' ESCAPE 'f'

Первый знак % - спецсимвол, означающий любое количество символов до строки, которую ищем.
f% - поиск процента как отдельного символа в строке, а f - объект экранирования.
Последний знак % - спецсимвол, означающий любое количество символов до строки, которую ищем.

SELECT * FROM band WHERE name LIKE '%f%%' ESCAPE 'f'

По такому же принципу, через экранирование, работает и поиск по знаку _

SELECT * FROM band WHERE name LIKE '_f_%' ESCAPE 'f'

Пример использования LIKE

SELECT * FROM band WHERE name LIKE '%Good%Bad%'

Для символьных типов данных

SUBSTRING

SUBSTRING - поиск подстроки в строке.

SELECT SUBSTRING('This is a book', 11, 4)
-- выводит 4 символа, начиная с 11-ого в строке

SELECT SUBSTRING('This is a book', 22, 4)
-- выводит 3 символа, начиная с 22-ого в строке

POSITION

POSITION - эта функция ищет подстроку в строке, и если она нашлась, возвращает номер символа, начиная с которого, нашлась подстрока.

SELECT POSITION('book' IN 'This is book')

SELECT POSITION('red' IN 'This is book')

CHARACTER_LENGTH

CHARACTER_LENGTH - возвращает количество символов строке

SELECT CHARACTER_LENGTH('This a is book')

TRIM

TRIM - отбрасывает символы слева и справа строки.

SELECT TRIM(' BOOK '),
       --если ничего не задавать, убирает пробелы
       TRIM('x' FROM 'xxBOOKxx'),
	   --если задать только символ, который нужно убрать
	   --убирает его с двух сторон
	   TRIM(leading 'x' FROM 'xxBOOKxx'),
	   --leading убирает заданные символы слева
	   TRIM(trailing 'x' FROM 'xxBOOKxx')
	   --trailing убирает заданные символы справа

LOWER

LOWER - переводит все символы строки в нижний регистр.

SELECT LOWER('This Is a Book')

UPPER

UPPER - переводит все символы строки в верхний регистр.

SELECT UPPER('This Is a Book')

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

Пример 1: Надо найти название в таблице, но мы не знаем, в каком регистре она записана.

SELECT * FROM band WHERE lower(name) = 'metallica'

Либо же с использованием функции UPPER

SELECT * FROM band WHERE upper(name) = 'METALLICA'

Пример 2: узнать длину строки в колонке

SELECT name, character_length(name) as len FROM person LIMIT 10

Пример 3: Найдем всех исполнителей, чьи имена или названия групп поставляют более двух слов

SELECT *
FROM person
WHERE POSITION (' ' in name ) > 0

и тд

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

Так же этот запрос можно записать с помощью LIKE

SELECT * FROM person WHERE name LIKE '% %'

Пример 4: Отделить имя от фамилии музыканта

SELECT name, 
       character_length(name) as len_name,
       --
	   substring(name, 1, POSITION (' ' in name )-1) as first_name, 
	   character_length(substring(name, 1, POSITION (' ' in name )-1)) as len_first_name,
	   --
	   substring(name, POSITION (' ' in name ) +1) as last_name,
	   character_length(substring(name, POSITION (' ' in name ) +1)) as len_last_name   
	   --
	   FROM person as table_1
WHERE POSITION (' ' in name ) > 0

и тд

Для типов данных даты и времени

TO_DATE

TO_DATE - конвертация из символьного типа в тип даты.

SELECT TO_DATE('2018-12-31', 'yyyy-mm-dd'),
       TO_DATE('31-12-2018', 'dd-mm-yyyy')

TO_TIMESTAMP

TO_TIMESTAMP - конвертация из символьного типа в тип даты-времени.

SELECT TO_TIMESTAMP('18.11.1990 05.00.24', 'dd.mm.yyyy hh24.mi.ss'),
       TO_TIMESTAMP('1990-11-18 05:00:24', 'yyyy-mm-dd hh24.mi.ss')

И в TO_DATE, и в TO_TIMESTAMP первый аргумент - это текстовое поле, второй - формат даты, записанный в первом аргументе.

TO_CHAR

TO_CHAR - конвертация из типа дата в символьный тип данных.

SELECT TO_CHAR(date '2018-12-31', 'yyyy-mm-dd'),
       TO_CHAR(date '1990-11-18', 'dd.mm.yyyy')

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - выводит текущую дату, текущее время и текущие дату и время соответственно.

SELECT current_date,
       current_time,
	   current_timestamp

EXTRACT

EXTRACT - извлечение отдельных составляющих из типов данных даты и времени

SELECT extract(day FROM date '2018-12-31'),
	   extract(month FROM date '2018-12-31'),
	   extract(year FROM date '2018-12-31')

SELECT current_timestamp,
       extract(hour FROM current_timestamp),
	   extract(minute FROM current_timestamp),
	   extract(second FROM current_timestamp)