Строковые функции, функции дат и чисел
Повседневный набор инструментов для преобразования значений в столбцах
Главная идея: скалярная функция берёт одно или несколько значений из одной строки и возвращает одно новое значение для этой строки. В отличие от агрегатов (
SUM, COUNT), сворачивающих много строк в одну, скалярные функции выполняются по одному разу на строку и никогда не меняют число строк. Освоив семейства строковых, датовых и числовых функций, вы сможете очищать, форматировать и вычислять прямо в SQL, не перетаскивая сырые данные в код приложения.
К концу темы вы сможете:
- резать и преобразовывать текст с помощью
SUBSTRING,TRIM,REPLACE,UPPER/LOWERиCONCAT; - выполнять арифметику над датами через
DATE_ADD,DATEDIFFи форматировать их черезDATE_FORMAT; - управлять точностью с помощью
ROUND,FLOOR,CEILиMOD; - осознанно преобразовывать типы через
CAST; - распознавать, где MySQL, PostgreSQL и SQLite расходятся.
1. Наша примерная схема
Каждый запрос ниже работает с одной небольшой базой магазина: таблица customers (много текста и дата) и таблица orders (даты и деньги). Создайте её один раз и держите в уме.
CREATE TABLE customers (
id INT PRIMARY KEY,
full_name VARCHAR(60),
email VARCHAR(80),
city VARCHAR(40),
signup_date DATE
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(9,3),
placed_at DATETIME
);
INSERT INTO customers VALUES
(1, ' Ada Lovelace', 'ADA@Example.com', 'London', '2023-11-05'),
(2, 'Alan Turing ', 'alan@Example.ORG', 'Manchester', '2024-01-20'),
(3, 'Grace Hopper', 'grace@navy.mil', 'New York', '2024-03-14'),
(4, 'Katherine Johnson', 'kj@nasa.gov', 'Hampton', '2024-06-11');
INSERT INTO orders VALUES
(101, 1, 42.507, '2024-07-01 09:30:00'),
(102, 1, 19.949, '2024-07-15 14:05:00'),
(103, 2, 128.250, '2024-06-20 11:00:00'),
(104, 3, 7.335, '2024-07-03 16:45:00'),
(105, 4, 256.000, '2024-05-28 08:15:00');Обратите внимание на намеренно "грязные" данные: у некоторых имён есть лишние пробелы в начале или конце, а адреса почты смешивают регистр. Реальные данные выглядят именно так, и скалярные функции — это то, чем мы их приводим в порядок.