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

+ — сложение:

SELECT 8 + 5;

- — вычитание:

SELECT 10 - 5;

* — умножение:

SELECT 10 * 5;

/ — деление:

SELECT 10 / 5;

/* Выведет: 2.0000 */

DIV — целочисленное деление:

SELECT 10 DIV 5;

/* Выведет: 2 */

SELECT 10 DIV 3;

/* Выведет: 3 */

% — остаток от деления:

SELECT 10 % 2;

/* Выведет: 0 */

SELECT 9 % 2;

/* Выведет: 1 */

MOD — остаток от деления:

SELECT 10 MOD 2;

/* Выведет: 0 */

Вместо операторов % и MOD можно использовать функцию MOD().

SELECT MOD(10, 2);

/* Выведет: 0 */

Следует отметить, что если один из операндов равен NULL, то результат операции также будет равен NULL. В отличие от языков программирования деление на ноль не приводит к генерации сообщения об ошибке. Результатом операции деления на ноль является значение NULL.

Если необходимо сменить знак, то перед операндом следует указать знак – (минус):

SELECT -(-5);

/* Выведет: 5 */

Двоичные операторы:

1. ~ — двоичная инверсия;

2. & — двоичное И;

3. | — двоичное ИЛИ;

4. ^ — двоичное исключающее ИЛИ;

5. << — сдвиг влево — сдвиг влево на один или более разрядов с заполнением младших разрядов нулями;

6. >> — сдвиг вправо — сдвиг вправо на один или более разрядов с заполнением старших разрядов содержимым самого старшего разряда.

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

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

= — равно;

<=> — эквивалентно;

!= — не равно;

<> — не равно;

< — меньше;

> — больше;

<= — меньше или равно;

>= — больше или равно;

IS NOT NULL — проверка на наличие значения;

IS NULL — проверка поля на отсутствие значения;

BETWEEN <Начало> AND <Конец> — проверяет, является ли значение большим или равным <Начало> и меньшим или равным <Конец>, например, pole BETWEEN 0 AND 100;

IN — содержится в определенном наборе, например, pole IN ('HDD', 'Монитор');

NOT IN — не содержится в определенном наборе, например, pole NOT IN ('HDD','Монитор');

LIKE — соответствие шаблону SQL;

NOT LIKE — несоответствие шаблону SQL;

RLIKE — соответствие регулярному выражению;

REGEXP — соответствие регулярному выражению (синоним RLIKE);

NOT RLIKE — несоответствие регулярному выражению;

NOT REGEXP — несоответствие регулярному выражению (синоним NOT RLIKE).

В шаблоне SQL могут использоваться следующие символы:

% — любое количество символов;

_ — любой одиночный символ.

Можно проверять сразу несколько условий, указав логические операции:

AND — логическое И;

OR — логическое ИЛИ;

XOR — исключающее логическое ИЛИ.

Результатом операции сравнения являются:

0 — ложь;

1 — истина;

NULL.

Исключением является оператор эквивалентности <=>. Он возвращает только два значения: 0 (ложь) и 1 (истина). Этот оператор введен специально дл сравнения значения NULL.

Следует отметить, что по умолчанию сравнение строк происходит без учета регистра. Если указать ключевое слово BINARY, то регистр символов будет учитываться:

SELECT 'TEXT'='text';

/* Выведет: 1 (истина) */

SELECT BINARY 'TEXT'='text';

/* Выведет: 0 (ложь) */

Результат сравнения можно изменить на противоположный с помощью операторов ! и NOT.

SELECT 'TEXT'='text';

/* Выведет: 1 (истина) */

SELECT !('TEXT'='text');

/* Выведет: 0 (ложь) */

SELECT NOT ('TEXT'='text');

/* Выведет: 0 (ложь) */

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

При составлении выражений следует учитывать приоритет выполнения операторов.

Перечислим операторы в порядке убывания приоритета:

1. BINARY, COLLATE.

2. NOT, !.

3. - (унарный минус), ~.

4. *, /, %, MOD, DIV.

5. +, - — сложение, вычитание.

6. <<, >> — двоичные сдвиги.

7. & — двоичное И.

8. | — двоичное ИЛИ.

9. =, <=>, >=, <=, >, <, <>, !=, IS, LIKE, REGEXP, IN.

10. BETWEEN.

11. &&, AND.

12. ||, OR, XOR.

Стандартные тригонометрические функции (аргументы должны задаваться в радианах):

SIN() — синус;

COS() — косинус;

TAN() — тангенс;

COT() — котангенс.

Обратные тригонометрические функции (возвращают значение в радианах):

ASIN() — арксинус;

ACOS() — арккосинус;

ATAN() — арктангенс.

Округление чисел:

CEILING() — значение, округленное до ближайшего большего целого:

SELECT CEILING(4.3);

/* Выведет: 5 */

CEIL() — значение, округленное до ближайшего большего целого:

SELECT CEIL(4.3);

/* Выведет: 5 */

FLOOR() — значение, округленное до ближайшего меньшего целого:

SELECT FLOOR(4.6);

/* Выведет: 4 */

ROUND() — значение, округленное до ближайшего меньшего целого для чисел с дробной частью меньше 0.5, или значение, округленное до ближайшего большего целого для чисел с дробной частью больше 0.5. Если дробная часть числа равна 0.5, то округление зависит от версии MySQL.

Начиная с версии 5.0.3, округление производится в большую сторону:

SELECT ROUND(4.49);

/* Выведет: 4 */

SELECT ROUND(4.5);

/* В зависимости от версии MySQL, выведет 4 или 5. */

SELECT ROUND(4.501);

/* Выведет: 5 */

SELECT ROUND(4.51);

/* Выведет: 5 */

TRUNCATE(X, Y) возвращает дробное число X с Y количеством знаков после запятой. Если в качестве значения аргумента Y передать значение 0, то функция вернет число, округленное до меньшего целого:

SELECT TRUNCATE(4.55, 0);

/* Выведет: 4 */

SELECT TRUNCATE(4.55, 1);

/* Выведет: 4.5 */

SELECT TRUNCATE(4.55, 3);

/* Выведет: 4.550 */

Функции для преобразования чисел:

CONV(<Число>, <Исходная система>, <Нужная система>) преобразует число из одной системы счисления в другую:

SELECT CONV(255, 10, 16);

/* Выведет: FF */

SELECT CONV('FF', 16, 10);

/* Выведет: 255 */

BIN(<Число>) преобразует число из десятичной системы счисления в двоичную:

SELECT BIN(17);

/* Выведет: 10001 */

HEX() возвращает значение аргумента в виде шестнадцатеричного числа:

SELECT HEX(255);

/* Выведет: FF */

OCT(<Число>) преобразует число из десятичной системы счисления в восьмеричную:

SELECT OCT(10);

/* Выведет: 12 */

Прочие функции:

ABS() — абсолютное значение:

SELECT ABS(-4.55);

/* Выведет: 4.55 */

EXP() — экспонента;

LOG(X) — натуральный логарифм;

LOG2(X) — логарифм числа по основанию 2:

SELECT LOG2(128);

/* Выведет: 7 */

LOG10(X) — логарифм числа по основанию 10:

SELECT LOG10(100);

/* Выведет: 2 */

LOG(<Основание>, X) — логарифм числа X по основанию <Основание>:

SELECT LOG(2, 128);

/* Выведет: 7 */

SELECT LOG(10, 100);

/* Выведет: 2 */

POW(<Число>, <Степень>) возводит <Число> в <Степень>:

SELECT POW(5, 2);

/* Выведет: 25 */

SQRT() извлекает квадратный корень:

SELECT SQRT(25);

/* Выведет: 5 */

PI() возвращает число π:

SELECT PI();

/* Выведет: 3.141593 */

MOD(X, Y) определяет остаток от деления X на Y:

SELECT MOD(10, 2);

/* Выведет: 0 */

DEGREES() преобразует значение угла из радиан в градусы:

SELECT DEGREES(PI());

/* Выведет: 180 */

RADIANS() преобразует значение угла из градусов в радианы:

SELECT RADIANS(180);

/* Выведет 3.141592653589793 */

SIGN() возвращает -1, если число отрицательное, 1, если число положительное, и 0, если число равно нулю:

SELECT SIGN(-80);

/* Выведет: -1 */

SELECT SIGN(80);

/* Выведет: 1 */

LEAST() служит для определения минимального значения из списка:

SELECT LEAST(2, 1, 3);

/* Выведет: 1 */

GREATEST() позволяет определить максимальное значение из списка:

SELECT GREATEST(2, 1, 3);

/* Выведет: 3 */

FORMAT(<Число>, <Количество знаков после запятой>) форматирует число в строку с заданным количеством знаков после запятой. Позиция запятой отмечается точкой, а каждые три разряда отделяются запятой (это американский стандарт записи чисел):

SELECT FORMAT(56873.8732, 2);

/* Выведет: 56,873.87 */

RAND() возвращает случайное число в диапазоне от 0 до 1. Если в функцию передать параметр, то это настроит генератор на новую последовательность. Следует учитывать, что при передаче одного и того же параметра функция выдает одну и ту же последовательность:

SELECT RAND();

/* Выведет: 0.35286363153985106 */

SELECT RAND();

/* Выведет: 0.7805252687824195 */

SELECT RAND(10);

/* Выведет: 0.6570515219653505 */

SELECT RAND(10);

/* Выведет: 0.6570515219653505 */

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

NOW(), LOCALTIME() и LOCALTIMESTAMP() возвращают текущие дату и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС;

SELECT NOW();

/* Выведет: 2009-09-21 22:35:04 */

SELECT LOCALTIME();

/* Выведет: 2009-09-21 22:35:04 */

SELECT LOCALTIMESTAMP();

/* Выведет: 2009-09-21 22:35:04 */

UTC_TIMESTAMP() выводит текущие дату и время по Гринвичу в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС;

SELECT UTC_TIMESTAMP();

/* Выведет: 2009-09-21 18:36:21 */

SYSDATE() позволяет определить текущие дату и время в формате ГГГГ- ММ-ДД ЧЧ:ММ:СС:

SELECT SYSDATE();

/* Выведет: 2009-09-21 22:36:43 */

В отличие от функции NOW() и ее синонимов SYSDATE() возвращает время, в которое она была вызвана, тогда как NOW() возвращает время начала выполнения запроса;

CURDATE() и CURRENT_DATE() возвращают текущую дату в формате ГГГГ- ММ-ДД:

SELECT CURDATE();

/* Выведет: 2009-09-21 */

SELECT CURRENT_DATE();

/* Выведет: 2009-09-21 */

UTC_DATE() позволяет определить текущую дату по Гринвичу в формате ГГГГ-ММ-ДД:

SELECT UTC_DATE();

/* Выведет: 2009-09-21 */

CURTIME() и CURRENT_TIME() возвращают текущее время в формате ЧЧ:ММ:СС;

SELECT CURTIME();

/* Выведет: 22:38:01 */

SELECT CURRENT_TIME();

/* Выведет: 22:38:01 */

UTC_TIME() сообщает текущее время по Гринвичу в формате ЧЧ:ММ:СС:

SELECT UTC_TIME();

/* Выведет: 18:38:01 */

UNIX_TIMESTAMP() подсчитывает число секунд, прошедших с полуночи

1 января 1970 г.:

SELECT UNIX_TIMESTAMP();

/* Выведет: 1253558203 */

Ряд функций позволяют получить следующие фрагменты даты и времени:

DATE() — дата:

SELECT DATE('2009-09-21 22:36:43');

/* Выведет: 2009-09-21 */

YEAR() — год:

SELECT YEAR('2009-09-21 22:36:43');

/* Выведет: 2009 */

MONTH() — месяц:

SELECT MONTH('2009-09-21 22:36:43');

/* Выведет: 9 */

MONTHNAME() — английское название месяца в виде строки:

SELECT MONTHNAME('2009-09-21 22:36:43');

/* Выведет: September */

DAY() и DAYOFMONTH() — номер дня в месяце:

SELECT DAY('2009-09-21 22:36:43');

/* Выведет: 21 */

SELECT DAYOFMONTH('2009-09-21 22:36:43');

/* Выведет: 21 */

TIME() — время:

SELECT TIME('2009-09-21 22:36:43');

/* Выведет: 22:36:43 */

HOUR() — час:

SELECT HOUR('2009-09-21 22:36:43');

/* Выведет: 22 */

MINUTE() — минуты:

SELECT MINUTE('2009-09-21 22:36:43');

/* Выведет: 36 */

SECOND() — секунды:

SELECT SECOND('2009-09-21 22:36:43');

/* Выведет: 43 */

MICROSECOND() — микросекунды:

SELECT MICROSECOND('2009-09-21 22:36:43.123456');

/* Выведет: 123456 */

Вместо перечисленных функций можно использовать функцию EXTRACT().

Функция имеет следующий формат:

EXTRACT(<Тип> FROM <Дата и время>)

Параметр <Тип> может принимать такие значения:

YEAR — год:

SELECT EXTRACT(YEAR FROM '2009-09-21 22:36:43');

/* Выведет: 2009 */

YEAR_MONTH — год и месяц:

SELECT EXTRACT(YEAR_MONTH FROM '2009-09-21 22:36:43');

/* Выведет: 200909 */

MONTH — месяц:

SELECT EXTRACT(MONTH FROM '2009-09-21 22:36:43');

/* Выведет: 9 */

DAY — день:

SELECT EXTRACT(DAY FROM '2009-09-21 22:36:43');

/* Выведет: 21 */

DAY_HOUR — день и час:

SELECT EXTRACT(DAY_HOUR FROM '2009-09-21 22:36:43');

/* Выведет: 2122 */

DAY_MINUTE — день, час и минуты:

SELECT EXTRACT(DAY_MINUTE FROM '2009-09-21 22:36:43');

/* Выведет: 212236 */

DAY_SECOND — день, час, минуты и секунды:

SELECT EXTRACT(DAY_SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 21223643 */

DAY_MICROSECOND — день, час, минуты, секунды и микросекунды:

SELECT EXTRACT(DAY_MICROSECOND FROM '2009-09-21

22:36:43.111111');

/* Выведет: 21223643111111 */

HOUR — час:

SELECT EXTRACT(HOUR FROM '2009-09-21 22:36:43');

/* Выведет: 22 */

HOUR_MINUTE — час и минуты:

SELECT EXTRACT(HOUR_MINUTE FROM '2009-09-21 22:36:43');

/* Выведет: 2236 */

HOUR_SECOND — час, минуты и секунды:

SELECT EXTRACT(HOUR_SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 223643 */

HOUR_MICROSECOND — час, минуты, секунды и микросекунды:

SELECT EXTRACT(HOUR_MICROSECOND FROM '2009-09-21 22:36:43.111111');

/* Выведет: 223643111111 */

MINUTE — минуты:

SELECT EXTRACT(MINUTE FROM '2009-09-21 22:36:43');

/* Выведет: 36 */

MINUTE_SECOND — минуты и секунды:

SELECT EXTRACT(MINUTE_SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 3643 */

MINUTE_MICROSECOND — минуты, секунды и микросекунды:

SELECT EXTRACT(MINUTE_MICROSECOND FROM '2009-09-21

22:36:43.111111');

/* Выведет: 3643111111 */

SECOND — секунды:

SELECT EXTRACT(SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 43 */

SECOND_MICROSECOND — секунды и микросекунды:

SELECT EXTRACT(SECOND_MICROSECOND

FROM '2009-09-21 22:36:43.111111');

/* Выведет: 43111111 */

MICROSECOND — микросекунды:

SELECT EXTRACT(MICROSECOND FROM '2009-09-21 22:36:43.111111');

/* Выведет: 111111 */

С помощью следующих функций можно получить дополнительные сведения о дате:

QUARTER() — порядковый номер квартала в году (от 1 до 4):

SELECT QUARTER('2009-09-21');

/* Выведет: 3 */

WEEKOFYEAR() — порядковый номер недели в году (от 1 до 53):

SELECT WEEKOFYEAR('2009-09-21');

/* Выведет: 39 */

WEEK() — порядковый номер недели в году (от 0 до 53). Неделя начинается с воскресенья:

SELECT WEEK('2009-09-21');

/* Выведет: 38 */

YEARWEEK() — число в формате ГГГГНН, где ГГГГ — год, а НН — порядковый номер недели в году (от 0 до 53). Неделя начинается с воскресенья:

SELECT YEARWEEK('2009-09-21');

/* Выведет: 200938 */

DAYOFYEAR() — порядковый номер дня в году (от 1 до 366):

SELECT DAYOFYEAR('2009-09-21');

/* Выведет: 264 */

MAKEDATE(<Год>, <Номер дня в году>) — дата в формате ГГГГ-ММ-ДД по номеру дня в году:

SELECT MAKEDATE(2009, 264);

/* Выведет: 2009-09-21 */

DAYOFWEEK() — порядковый номер дня недели (1 — для воскресенья, 2 — для понедельника, ..., 7 — для субботы):

SELECT DAYOFWEEK('2009-09-21');

/* Выведет: 2 */

WEEKDAY() — порядковый номер дня недели (0 — для понедельника, 1 — для вторника, ..., 6 — для воскресенья):

SELECT WEEKDAY('2009-09-21');

/* Выведет: 0 */

DAYNAME() — название дня недели на английском языке:

SELECT DAYNAME('2009-09-21');

/* Выведет: Monday */

TO_DAYS(<Дата>) — количество дней, прошедших с нулевого года:

SELECT TO_DAYS('2009-09-21');

/* Выведет: 734036 */

FROM_DAYS(<Количество дней>) — дата в формате ГГГГ-ММ-ДД по количеству дней, прошедших с нулевого года:

SELECT FROM_DAYS(734036);

/* Выведет: 2009-09-21 */

TIME_TO_SEC(<Время>) — количество секунд, прошедших с начала суток:

SELECT TIME_TO_SEC('12:52:35');

/* Выведет: 46355 */

SEC_TO_TIME(<Количество секунд>) — время в формате ЧЧ:ММ:СС по количеству секунд, прошедших с начала суток:

SELECT SEC_TO_TIME(46355);

/* Выведет: 12:52:35 */

Для манипуляции датой и временем можно использовать следующие функции:

ADDDATE(<Дата>, INTERVAL <Интервал> <Тип>) и DATE_ADD(<Дата>, INTERVAL <Интервал> <Тип>) прибавляют к параметру <Дата> временной интервал;

SUBDATE(<Дата>, INTERVAL <Интервал> <Тип>) и DATE_SUB(<Дата>,INTERVAL <Интервал> <Тип>) вычитают из параметра <Дата> временной интервал.

Параметр <Тип> в функциях ADDDATE(), DATE_ADD(), SUBDATE() и DATE_SUB() может принимать следующие значения:

• YEAR — год:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 2 YEAR);

/* Выведет: 2011-09-21 22:36:43 */

• YEAR_MONTH — год и месяц (формат 'ГГ-ММ'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '2-2' YEAR_MONTH);

/* Выведет: 2011-11-21 22:36:43 */

• MONTH — месяц:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 3 MONTH);

/* Выведет: 2009-12-21 22:36:43 */

• DAY — день:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 6 DAY);

/* Выведет: 2009-09-27 22:36:43 */

• DAY_HOUR — день и час (формат 'ДД ЧЧ'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '6 3' DAY_HOUR);

/* Выведет: 2009-09-28 01:36:43 */

• DAY_MINUTE — день, час и минуты (формат 'ДД ЧЧ:ММ'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '6 3:5' DAY_MINUTE);

/* Выведет: 2009-09-28 01:41:43 */

• DAY_SECOND — день, час, минуты и секунды (формат 'ДД ЧЧ:ММ:СС'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '6 3:5:15' DAY_SECOND);

/* Выведет: 2009-09-28 01:41:58 */

• DAY_MICROSECOND — день, час, минуты, секунды и микросекунды (формат 'ДД ЧЧ:ММ:СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '6 3:5:15.10' DAY_MICROSECOND);

/* Выведет: 2009-09-28 01:41:58.100000 */

• HOUR — час:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 3 HOUR); /* Выведет: 2009-09-22 01:36:43 */

• HOUR_MINUTE — час и минуты (формат 'ЧЧ:ММ'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '3:7' HOUR_MINUTE);

/* Выведет: 2009-09-22 01:43:43 */

• HOUR_SECOND — час, минуты и секунды (формат 'ЧЧ:ММ:СС'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '3:7:15' HOUR_SECOND);

/* Выведет: 2009-09-22 01:43:58 */

• HOUR_MICROSECOND — час, минуты, секунды и микросекунды (формат 'ЧЧ:ММ:СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '3:7:15.10' HOUR_MICROSECOND);

/* Выведет: 2009-09-22 01:43:58.100000 */

• MINUTE — минуты:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 8 MINUTE);

/* Выведет: 2009-09-21 22:44:43 */

• MINUTE_SECOND — минуты и секунды (формат 'ММ:СС'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '3:7' MINUTE_SECOND);

/* Выведет: 2009-09-21 22:39:50 */

• MINUTE_MICROSECOND — минуты, секунды и микросекунды (формат 'ММ:СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '3:7.11' MINUTE_MICROSECOND);

/* Выведет: 2009-09-21 22:39:50.110000 */

• SECOND — секунды:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 15 SECOND);

/* Выведет: 2009-09-21 22:36:58 */

• SECOND_MICROSECOND — секунды и микросекунды (формат 'СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '15.123456' SECOND_MICROSECOND);

/* Выведет: 2009-09-21 22:36:58.123456 */

• MICROSECOND — микросекунды:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 123456 MICROSECOND);

/* Выведет: 2009-09-21 22:36:43.123456 */

Кроме того, функции ADDDATE() и SUBDATE() можно применять в сокращенном формате, описанном далее;

□ ADDDATE(<Дата>, <Интервал в днях>) прибавляет к параметру <Дата> временной интервал в днях. Если указать перед интервалом знак -, то интервал вычитается из даты:

SELECT ADDDATE('2009-09-21', 10);

/* Выведет: 2009-10-01 */

SELECT ADDDATE('2009-09-21', -10);

/* Выведет: 2009-09-11 */

□ SUBDATE(<Дата>, <Интервал в днях>) вычитает из параметра <Дата> временной интервал в днях.

Если указать перед интервалом знак -, то интервал прибавляется к дате:

SELECT SUBDATE('2009-09-21', 10);

/* Выведет: 2009-09-11 */

SELECT SUBDATE('2009-09-21', -10);

/* Выведет: 2009-10-01 */

□ ADDTIME(<Дата>, <Время>) прибавляет к параметру <Дата> временной интервал:

SELECT ADDTIME('2009-09-21 22:36:43', '12:52:35');

/* Выведет: 2009-09-22 11:29:18 */

□ SUBTIME(<Дата>, <Время>) вычитает из параметра <Дата> временной интервал:

SELECT SUBTIME('2009-09-21 22:36:43', '12:52:35');

/* Выведет: 2009-09-21 09:44:08 */

□ DATEDIFF(<Конечная дата>, <Начальная дата>) вычисляет количество дней между двумя датами:

SELECT DATEDIFF('2009-09-27', '2009-09-21');

/* Выведет: 6 */

□ TIMEDIFF(<Конечная дата>, <Начальная дата>) вычисляет разницу между двумя временными значениями:

SELECT TIMEDIFF('2009-09-21 22:36:43', '2009-09-21 15:36:43');

/* Выведет: 07:00:00 */

□ PERIOD_ADD(<Дата>, <Количество месяцев>) добавляет заданное <Количество месяцев> к дате, заданной в формате ГГГГММ или ГГММ:

SELECT PERIOD_ADD(200904, 4);

/* Выведет: 200908 */

□ PERIOD_DIFF(<Конечная дата>, <Начальная дата>) вычисляет разницу в

месяцах между двумя временными значениями, заданными в формате

ГГГГММ или ГГММ:

SELECT PERIOD_DIFF(200908, 200904);

/* Выведет: 4 */

□ CONVERT_TZ(<Дата>, <Часовой пояс1>, <Часовой пояс2>) переводит дату из одного часового пояса в другой:

SELECT CONVERT_TZ('2009-09-21 22:36:43', '+00:00', '+4:00');

/* Выведет: 2009-09-22 02:36:43 */

□ LAST_DAY(<Дата>) возвращает дату в формате ГГГГ-ММ-ДД, в которой день выставлен на последний день текущего месяца:

SELECT LAST_DAY('2009-09-21 22:36:43');

/* Выведет: 2009-09-30 */

□ MAKETIME(<Часы>, <Минуты>, <Секунды>) возвращает время в формате ЧЧ:ММ:СС:

SELECT MAKETIME(12, 52, 35);

/* Выведет: 12:52:35 */

□ TIMESTAMP(<Дата>, [<Время>]) возвращает дату в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС:

SELECT TIMESTAMP('2009-09-21');

/* Выведет: 2009-09-21 00:00:00 */

SELECT TIMESTAMP('2009-09-21', '12:52:35');

/* Выведет: 2009-09-21 12:52:35 */

Помимо описанных функций добавить или вычесть интервал времени можно с помощью операторов + и -, за которыми следует ключевое слово INTERVAL, значение и тип интервала. Применимы те же типы интервалов, что и в функциях ADDDATE(), DATE_ADD(), SUBDATE() и DATE_SUB():

SELECT '2009-09-21 22:36:43' + INTERVAL '3:7:15' HOUR_SECOND;

/* Выведет: 2009-09-22 01:43:58 */

SELECT '2009-09-21 22:36:43' - INTERVAL '3:7:15' HOUR_SECOND;

/* Выведет: 2009-09-21 19:29:28 */

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

□ DATE_FORMAT(<Дата>, <Формат>) форматирует дату в соответствии со строкой <Формат>:

SELECT DATE_FORMAT('2009-09-21 22:36:43', '%d.%m.%Y');

/* Выведет: 21.09.2009 */

□ STR_TO_DATE(<Дата>, <Формат>) возвращает дату в форматах ГГГГ-ММ-ДД ЧЧ:ММ:СС или ГГГГ-ММ-ДД по дате, соответствующей строке <Формат>:

SELECT STR_TO_DATE('21.09.2009 12:52:35', '%d.%m.%Y %H:%i:%s');

/* Выведет: 2009-09-21 12:52:35 */

□ TIME_FORMAT(<Время>, <Формат>) форматирует время в соответствии со строкой <Формат>:

SELECT TIME_FORMAT('12:52:35', '%H %i %s');

/* Выведет: 12 52 35 */

□ FROM_UNIXTIME(<Дата>, [<Формат>]) возвращает дату в формате ГГГГ- ММ-ДД ЧЧ:ММ:СС или соответствующую строке <Формат> по количеству секунд, прошедших с полуночи 1 января 1970 г.:

SELECT FROM_UNIXTIME(1239671919);

/* Выведет: 2009-04-14 05:18:39 */

SELECT FROM_UNIXTIME(1239671919, '%d.%m.%Y');

/* Выведет: 14.04.2009 */

□ GET_FORMAT(<Тип времени>, '<Стандарт>') возвращает строку форматирования для пяти стандартов отображения даты и времени. Параметр

<Тип времени> может принимать следующие значения:

• DATETIME — дата и время;

• DATE — дата;

• TIME — время.

Параметр <Стандарт> может принимать такие значения:

• ISO — стандарт ISO;

• EUR — европейский стандарт;

• USA — американский стандарт;

• JIS — японский стандарт;

• INTERNAL — внутренний формат MySQL.

Например:

SELECT GET_FORMAT(DATE, 'EUR');

/* Выведет: %d.%m.%Y */

SELECT DATE_FORMAT('2009-09-21 22:36:43',

GET_FORMAT(DATE, 'EUR'));

/* Выведет: 21.09.2009 */

Параметр <Формат> в функциях форматирования может содержать следующие комбинации символов:

□ %Y — год из 4-х цифр;

□ %y — год из 2-х цифр;

□ %m — номер месяца с предваряющим нулем (от 01 до 12);

□ %c — номер месяца без предваряющего нуля (от 1 до 12);

□ %b — аббревиатура месяца из 3-х букв по-английски;

□ %M — полное название месяца по-английски;

□ %d — номер дня с предваряющим нулем (от 01 до 31);

□ %e — номер дня без предваряющего нуля (от 1 до 31);

□ %w — номер дня недели (0 — для воскресенья и 6 — для субботы);

□ %a — аббревиатура дня недели из 3-х букв по-английски;

□ %W — полное название дня недели по-английски;

□ %H — часы в 24-часовом формате (от 00 до 23);

□ %h — часы в 12-часовом формате (от 01 до 12);

□ %i — минуты (от 00 до 59);

□ %s — секунды (от 00 до 59);

□ %f — микросекунды;

□ %% — знак процента.Для получения текущей даты и времени используются следующие функции:

□ NOW(), LOCALTIME() и LOCALTIMESTAMP() возвращают текущие дату и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС;

SELECT NOW();

/* Выведет: 2009-09-21 22:35:04 */

SELECT LOCALTIME();

/* Выведет: 2009-09-21 22:35:04 */

SELECT LOCALTIMESTAMP();

/* Выведет: 2009-09-21 22:35:04 */

□ UTC_TIMESTAMP() выводит текущие дату и время по Гринвичу в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС;

SELECT UTC_TIMESTAMP();

/* Выведет: 2009-09-21 18:36:21 */

□ SYSDATE() позволяет определить текущие дату и время в формате ГГГГ- ММ-ДД ЧЧ:ММ:СС:

SELECT SYSDATE();

/* Выведет: 2009-09-21 22:36:43 */

В отличие от функции NOW() и ее синонимов SYSDATE() возвращает время, в которое она была вызвана, тогда как NOW() возвращает время начала выполнения запроса;

□ CURDATE() и CURRENT_DATE() возвращают текущую дату в формате ГГГГ- ММ-ДД:

SELECT CURDATE();

/* Выведет: 2009-09-21 */

SELECT CURRENT_DATE();

/* Выведет: 2009-09-21 */

□ UTC_DATE() позволяет определить текущую дату по Гринвичу в формате ГГГГ-ММ-ДД:

SELECT UTC_DATE();

/* Выведет: 2009-09-21 */

□ CURTIME() и CURRENT_TIME() возвращают текущее время в формате ЧЧ:ММ:СС;

SELECT CURTIME();

/* Выведет: 22:38:01 */

SELECT CURRENT_TIME();

/* Выведет: 22:38:01 */

□ UTC_TIME() сообщает текущее время по Гринвичу в формате ЧЧ:ММ:СС:

SELECT UTC_TIME();

/* Выведет: 18:38:01 */

□ UNIX_TIMESTAMP() подсчитывает число секунд, прошедших с полуночи

1 января 1970 г.:

SELECT UNIX_TIMESTAMP();

/* Выведет: 1253558203 */

Ряд функций позволяют получить следующие фрагменты даты и времени:

□ DATE() — дата:

SELECT DATE('2009-09-21 22:36:43');

/* Выведет: 2009-09-21 */

□ YEAR() — год:

SELECT YEAR('2009-09-21 22:36:43');

/* Выведет: 2009 */

□ MONTH() — месяц:

SELECT MONTH('2009-09-21 22:36:43');

/* Выведет: 9 */

□ MONTHNAME() — английское название месяца в виде строки:

SELECT MONTHNAME('2009-09-21 22:36:43');

/* Выведет: September */

□ DAY() и DAYOFMONTH() — номер дня в месяце:

SELECT DAY('2009-09-21 22:36:43');

/* Выведет: 21 */

SELECT DAYOFMONTH('2009-09-21 22:36:43');

/* Выведет: 21 */

□ TIME() — время:

SELECT TIME('2009-09-21 22:36:43');

/* Выведет: 22:36:43 */

□ HOUR() — час:

SELECT HOUR('2009-09-21 22:36:43');

/* Выведет: 22 */

□ MINUTE() — минуты:

SELECT MINUTE('2009-09-21 22:36:43');

/* Выведет: 36 */

□ SECOND() — секунды:

SELECT SECOND('2009-09-21 22:36:43');

/* Выведет: 43 */

□ MICROSECOND() — микросекунды:

SELECT MICROSECOND('2009-09-21 22:36:43.123456');

/* Выведет: 123456 */

Вместо перечисленных функций можно использовать функцию EXTRACT().

Функция имеет следующий формат:

EXTRACT(<Тип> FROM <Дата и время>)

Параметр <Тип> может принимать такие значения:

□ YEAR — год:

SELECT EXTRACT(YEAR FROM '2009-09-21 22:36:43');

/* Выведет: 2009 */

□ YEAR_MONTH — год и месяц:

SELECT EXTRACT(YEAR_MONTH FROM '2009-09-21 22:36:43');

/* Выведет: 200909 */

□ MONTH — месяц:

SELECT EXTRACT(MONTH FROM '2009-09-21 22:36:43');

/* Выведет: 9 */

□ DAY — день:

SELECT EXTRACT(DAY FROM '2009-09-21 22:36:43');

/* Выведет: 21 */

□ DAY_HOUR — день и час:

SELECT EXTRACT(DAY_HOUR FROM '2009-09-21 22:36:43');

/* Выведет: 2122 */

□ DAY_MINUTE — день, час и минуты:

SELECT EXTRACT(DAY_MINUTE FROM '2009-09-21 22:36:43');

/* Выведет: 212236 */

□ DAY_SECOND — день, час, минуты и секунды:

SELECT EXTRACT(DAY_SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 21223643 */

□ DAY_MICROSECOND — день, час, минуты, секунды и микросекунды:

SELECT EXTRACT(DAY_MICROSECOND FROM '2009-09-21

22:36:43.111111');

/* Выведет: 21223643111111 */

□ HOUR — час:

SELECT EXTRACT(HOUR FROM '2009-09-21 22:36:43');

/* Выведет: 22 */

□ HOUR_MINUTE — час и минуты:

SELECT EXTRACT(HOUR_MINUTE FROM '2009-09-21 22:36:43');

/* Выведет: 2236 */

□ HOUR_SECOND — час, минуты и секунды:

SELECT EXTRACT(HOUR_SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 223643 */

□ HOUR_MICROSECOND — час, минуты, секунды и микросекунды:

SELECT EXTRACT(HOUR_MICROSECOND FROM '2009-09-21 22:36:43.111111');

/* Выведет: 223643111111 */

□ MINUTE — минуты:

SELECT EXTRACT(MINUTE FROM '2009-09-21 22:36:43');

/* Выведет: 36 */

□ MINUTE_SECOND — минуты и секунды:

SELECT EXTRACT(MINUTE_SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 3643 */

□ MINUTE_MICROSECOND — минуты, секунды и микросекунды:

SELECT EXTRACT(MINUTE_MICROSECOND FROM '2009-09-21

22:36:43.111111');

/* Выведет: 3643111111 */

□ SECOND — секунды:

SELECT EXTRACT(SECOND FROM '2009-09-21 22:36:43');

/* Выведет: 43 */

□ SECOND_MICROSECOND — секунды и микросекунды:

SELECT EXTRACT(SECOND_MICROSECOND

FROM '2009-09-21 22:36:43.111111');

/* Выведет: 43111111 */

□ MICROSECOND — микросекунды:

SELECT EXTRACT(MICROSECOND FROM '2009-09-21 22:36:43.111111');

/* Выведет: 111111 */

С помощью следующих функций можно получить дополнительные сведения о дате:

□ QUARTER() — порядковый номер квартала в году (от 1 до 4):

SELECT QUARTER('2009-09-21');

/* Выведет: 3 */

□ WEEKOFYEAR() — порядковый номер недели в году (от 1 до 53):

SELECT WEEKOFYEAR('2009-09-21');

/* Выведет: 39 */

□ WEEK() — порядковый номер недели в году (от 0 до 53). Неделя начинается с воскресенья:

SELECT WEEK('2009-09-21');

/* Выведет: 38 */

□ YEARWEEK() — число в формате ГГГГНН, где ГГГГ — год, а НН — порядковый номер недели в году (от 0 до 53). Неделя начинается с воскресенья:

SELECT YEARWEEK('2009-09-21');

/* Выведет: 200938 */

□ DAYOFYEAR() — порядковый номер дня в году (от 1 до 366):

SELECT DAYOFYEAR('2009-09-21');

/* Выведет: 264 */

□ MAKEDATE(<Год>, <Номер дня в году>) — дата в формате ГГГГ-ММ-ДД по номеру дня в году:

SELECT MAKEDATE(2009, 264);

/* Выведет: 2009-09-21 */

□ DAYOFWEEK() — порядковый номер дня недели (1 — для воскресенья, 2 — для понедельника, ..., 7 — для субботы):

SELECT DAYOFWEEK('2009-09-21');

/* Выведет: 2 */

□ WEEKDAY() — порядковый номер дня недели (0 — для понедельника, 1 — для вторника, ..., 6 — для воскресенья):

SELECT WEEKDAY('2009-09-21');

/* Выведет: 0 */

□ DAYNAME() — название дня недели на английском языке:

SELECT DAYNAME('2009-09-21');

/* Выведет: Monday */

□ TO_DAYS(<Дата>) — количество дней, прошедших с нулевого года:

SELECT TO_DAYS('2009-09-21');

/* Выведет: 734036 */

□ FROM_DAYS(<Количество дней>) — дата в формате ГГГГ-ММ-ДД по количеству дней, прошедших с нулевого года:

SELECT FROM_DAYS(734036);

/* Выведет: 2009-09-21 */

□ TIME_TO_SEC(<Время>) — количество секунд, прошедших с начала суток:

SELECT TIME_TO_SEC('12:52:35');

/* Выведет: 46355 */

□ SEC_TO_TIME(<Количество секунд>) — время в формате ЧЧ:ММ:СС по количеству секунд, прошедших с начала суток:

SELECT SEC_TO_TIME(46355);

/* Выведет: 12:52:35 */

Для манипуляции датой и временем можно использовать следующие функции:

□ ADDDATE(<Дата>, INTERVAL <Интервал> <Тип>) и DATE_ADD(<Дата>,

INTERVAL <Интервал> <Тип>) прибавляют к параметру <Дата> временной интервал;

□ SUBDATE(<Дата>, INTERVAL <Интервал> <Тип>) и DATE_SUB(<Дата>,

INTERVAL <Интервал> <Тип>) вычитают из параметра <Дата> временной интервал.

Параметр <Тип> в функциях ADDDATE(), DATE_ADD(), SUBDATE() и

DATE_SUB() может принимать следующие значения:

• YEAR — год:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 2 YEAR);

/* Выведет: 2011-09-21 22:36:43 */

• YEAR_MONTH — год и месяц (формат 'ГГ-ММ'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '2-2' YEAR_MONTH);

/* Выведет: 2011-11-21 22:36:43 */

• MONTH — месяц:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 3 MONTH);

/* Выведет: 2009-12-21 22:36:43 */

• DAY — день:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 6 DAY);

/* Выведет: 2009-09-27 22:36:43 */

• DAY_HOUR — день и час (формат 'ДД ЧЧ'):

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL '6 3'

DAY_HOUR);

/* Выведет: 2009-09-28 01:36:43 */

• DAY_MINUTE — день, час и минуты (формат 'ДД ЧЧ:ММ'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '6 3:5' DAY_MINUTE);

/* Выведет: 2009-09-28 01:41:43 */

• DAY_SECOND — день, час, минуты и секунды (формат 'ДД ЧЧ:ММ:СС'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '6 3:5:15' DAY_SECOND);

/* Выведет: 2009-09-28 01:41:58 */

• DAY_MICROSECOND — день, час, минуты, секунды и микросекунды (формат 'ДД ЧЧ:ММ:СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '6 3:5:15.10' DAY_MICROSECOND);

/* Выведет: 2009-09-28 01:41:58.100000 */

• HOUR — час:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 3 HOUR);

/* Выведет: 2009-09-22 01:36:43 */

• HOUR_MINUTE — час и минуты (формат 'ЧЧ:ММ'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '3:7' HOUR_MINUTE);

/* Выведет: 2009-09-22 01:43:43 */

• HOUR_SECOND — час, минуты и секунды (формат 'ЧЧ:ММ:СС'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '3:7:15' HOUR_SECOND);

/* Выведет: 2009-09-22 01:43:58 */

• HOUR_MICROSECOND — час, минуты, секунды и микросекунды (формат 'ЧЧ:ММ:СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '3:7:15.10' HOUR_MICROSECOND);

/* Выведет: 2009-09-22 01:43:58.100000 */

• MINUTE — минуты:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 8 MINUTE);

/* Выведет: 2009-09-21 22:44:43 */

• MINUTE_SECOND — минуты и секунды (формат 'ММ:СС'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '3:7' MINUTE_SECOND);

/* Выведет: 2009-09-21 22:39:50 */

• MINUTE_MICROSECOND — минуты, секунды и микросекунды (формат 'ММ:СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '3:7.11' MINUTE_MICROSECOND);

/* Выведет: 2009-09-21 22:39:50.110000 */

• SECOND — секунды:

SELECT ADDDATE('2009-09-21 22:36:43', INTERVAL 15 SECOND);

/* Выведет: 2009-09-21 22:36:58 */

• SECOND_MICROSECOND — секунды и микросекунды (формат

'СС.XXXXXX'):

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL '15.123456' SECOND_MICROSECOND);

/* Выведет: 2009-09-21 22:36:58.123456 */

• MICROSECOND — микросекунды:

SELECT ADDDATE('2009-09-21 22:36:43',

INTERVAL 123456 MICROSECOND);

/* Выведет: 2009-09-21 22:36:43.123456 */

Кроме того, функции ADDDATE() и SUBDATE() можно применять в сокращенном формате, описанном далее;

ADDDATE(<Дата>, <Интервал в днях>) прибавляет к параметру <Дата> временной интервал в днях. Если указать перед интервалом знак -, то интервал вычитается из даты:

SELECT ADDDATE('2009-09-21', 10);

/* Выведет: 2009-10-01 */

SELECT ADDDATE('2009-09-21', -10);

/* Выведет: 2009-09-11 */

SUBDATE(<Дата>, <Интервал в днях>) вычитает из параметра <Дата> временной интервал в днях.

Если указать перед интервалом знак -, то интервал прибавляется к дате:

SELECT SUBDATE('2009-09-21', 10);

/* Выведет: 2009-09-11 */

SELECT SUBDATE('2009-09-21', -10);

/* Выведет: 2009-10-01 */

ADDTIME(<Дата>, <Время>) прибавляет к параметру <Дата> временной интервал:

SELECT ADDTIME('2009-09-21 22:36:43', '12:52:35');

/* Выведет: 2009-09-22 11:29:18 */

SUBTIME(<Дата>, <Время>) вычитает из параметра <Дата> временной интервал:

SELECT SUBTIME('2009-09-21 22:36:43', '12:52:35');

/* Выведет: 2009-09-21 09:44:08 */

DATEDIFF(<Конечная дата>, <Начальная дата>) вычисляет количество дней между двумя датами:

SELECT DATEDIFF('2009-09-27', '2009-09-21');

/* Выведет: 6 */

TIMEDIFF(<Конечная дата>, <Начальная дата>) вычисляет разницу между двумя временными значениями:

SELECT TIMEDIFF('2009-09-21 22:36:43', '2009-09-21 15:36:43');

/* Выведет: 07:00:00 */

PERIOD_ADD(<Дата>, <Количество месяцев>) добавляет заданное <Количество месяцев> к дате, заданной в формате ГГГГММ или ГГММ:

SELECT PERIOD_ADD(200904, 4);

/* Выведет: 200908 */

PERIOD_DIFF(<Конечная дата>, <Начальная дата>) вычисляет разницу в месяцах между двумя временными значениями, заданными в формате ГГГГММ или ГГММ:

SELECT PERIOD_DIFF(200908, 200904);

/* Выведет: 4 */

CONVERT_TZ(<Дата>, <Часовой пояс1>, <Часовой пояс2>) переводит дату из одного часового пояса в другой:

SELECT CONVERT_TZ('2009-09-21 22:36:43', '+00:00', '+4:00');

/* Выведет: 2009-09-22 02:36:43 */

LAST_DAY(<Дата>) возвращает дату в формате ГГГГ-ММ-ДД, в которой день выставлен на последний день текущего месяца:

SELECT LAST_DAY('2009-09-21 22:36:43');

/* Выведет: 2009-09-30 */

MAKETIME(<Часы>, <Минуты>, <Секунды>) возвращает время в формате ЧЧ:ММ:СС:

SELECT MAKETIME(12, 52, 35);

/* Выведет: 12:52:35 */

TIMESTAMP(<Дата>, [<Время>]) возвращает дату в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС:

SELECT TIMESTAMP('2009-09-21');

/* Выведет: 2009-09-21 00:00:00 */

SELECT TIMESTAMP('2009-09-21', '12:52:35');

/* Выведет: 2009-09-21 12:52:35 */

Помимо описанных функций добавить или вычесть интервал времени можно с помощью операторов + и -, за которыми следует ключевое слово INTERVAL, значение и тип интервала. Применимы те же типы интервалов, что и в функциях ADDDATE(), DATE_ADD(), SUBDATE() и DATE_SUB():

SELECT '2009-09-21 22:36:43' + INTERVAL '3:7:15' HOUR_SECOND;

/* Выведет: 2009-09-22 01:43:58 */

SELECT '2009-09-21 22:36:43' - INTERVAL '3:7:15' HOUR_SECOND;

/* Выведет: 2009-09-21 19:29:28 */

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

DATE_FORMAT(<Дата>, <Формат>) форматирует дату в соответствии со строкой <Формат>:

SELECT DATE_FORMAT('2009-09-21 22:36:43', '%d.%m.%Y');

/* Выведет: 21.09.2009 */

STR_TO_DATE(<Дата>, <Формат>) возвращает дату в форматах ГГГГ-ММ-ДД ЧЧ:ММ:СС или ГГГГ-ММ-ДД по дате, соответствующей строке <Формат>:

SELECT STR_TO_DATE('21.09.2009 12:52:35', '%d.%m.%Y %H:%i:%s');

/* Выведет: 2009-09-21 12:52:35 */

TIME_FORMAT(<Время>, <Формат>) форматирует время в соответствии со строкой <Формат>:

SELECT TIME_FORMAT('12:52:35', '%H %i %s');

/* Выведет: 12 52 35 */

FROM_UNIXTIME(<Дата>, [<Формат>]) возвращает дату в формате ГГГГ- ММ-ДД ЧЧ:ММ:СС или соответствующую строке <Формат> по количеству секунд, прошедших с полуночи 1 января 1970 г.:

SELECT FROM_UNIXTIME(1239671919);

/* Выведет: 2009-04-14 05:18:39 */

SELECT FROM_UNIXTIME(1239671919, '%d.%m.%Y');

/* Выведет: 14.04.2009 */

GET_FORMAT(<Тип времени>, '<Стандарт>') возвращает строку форматирования для пяти стандартов отображения даты и времени. Параметр

<Тип времени> может принимать следующие значения:

• DATETIME — дата и время;

• DATE — дата;

• TIME — время.

Параметр <Стандарт> может принимать такие значения:

• ISO — стандарт ISO;

• EUR — европейский стандарт;

• USA — американский стандарт;

• JIS — японский стандарт;

• INTERNAL — внутренний формат MySQL.

Например:

SELECT GET_FORMAT(DATE, 'EUR');

/* Выведет: %d.%m.%Y */

SELECT DATE_FORMAT('2009-09-21 22:36:43',

GET_FORMAT(DATE, 'EUR'));

/* Выведет: 21.09.2009 */

Параметр <Формат> в функциях форматирования может содержать следующие комбинации символов:

%Y — год из 4-х цифр;

%y — год из 2-х цифр;

%m — номер месяца с предваряющим нулем (от 01 до 12);

%c — номер месяца без предваряющего нуля (от 1 до 12);

%b — аббревиатура месяца из 3-х букв по-английски;

%M — полное название месяца по-английски;

%d — номер дня с предваряющим нулем (от 01 до 31);

%e — номер дня без предваряющего нуля (от 1 до 31);

%w — номер дня недели (0 — для воскресенья и 6 — для субботы);

%a — аббревиатура дня недели из 3-х букв по-английски;

%W — полное название дня недели по-английски;

%H — часы в 24-часовом формате (от 00 до 23);

%h — часы в 12-часовом формате (от 01 до 12);

%i — минуты (от 00 до 59);

%s — секунды (от 00 до 59);

%f — микросекунды;

%% — знак процента.

Основные функции для обработки строк:

CHAR_LENGTH(<Строка>) и CHARACTER_LENGTH(<Строка>) возвращают количество символов в строке. Функции корректно работают с многобайтными кодировками:

SELECT CHAR_LENGTH('String');

/* Выведет: 6 */

SELECT CHARACTER_LENGTH('String');

/* Выведет: 6 */

LENGTH(<Строка>) также позволяет определить количество символов в строке. Однако эта функция некорректно работает с многобайтными кодировками, так как возвращает количество байтов:

SELECT LENGTH('String');

/* Выведет: 6 */

BIT_LENGTH(<Строка>) возвращает длину строки в битах:

SELECT BIT_LENGTH('String');

/* Выведет: 48 */

CONCAT(<Строка1>, <Строка2>, ..., <СтрокаN>) объединяет все параметры в одну строку:

SELECT CONCAT('string1', 'string2', 'string3');

/* Выведет: string1string2string3 */

CONCAT_WS(<Разделитель>, <Строка1>, ..., <СтрокаN>) объединяет все параметры в одну строку через разделитель, заданный в параметре <Разделитель>:

SELECT CONCAT_WS(' - ', 'string1', 'string2', 'string3');

/* Выведет: string1 - string2 - string3 */

TRIM([[<Откуда>] [<Символы для удаления>] FROM] <Строка>) удаляет из начала (и/или конца) строки символы, указанные в параметре <Символы для удаления>. Если параметр не указан, то удаляемыми символами являются пробелы. Необязательный параметр <Откуда> может принимать значения:

• BOTH — символы удаляются из начала и конца строки (по умолчанию);

• LEADING — только из начала строки;

• TRAILING — только из конца строки.

Например:

SELECT CONCAT("'", TRIM(' String '), "'");

/* Выведет: 'String' */

SELECT CONCAT("'", TRIM(LEADING FROM ' String '), "'");

/* Выведет: 'String ' */

SELECT CONCAT("'", TRIM(TRAILING FROM ' String '), "'");

/* Выведет: ' String' */

SELECT CONCAT("'", TRIM(BOTH 'm' FROM 'mmmmStringmmmm'), "'");

/* Выведет: 'String' */

SELECT CONCAT("'", TRIM(TRAILING 'ing' FROM 'Stringing'), "'");

/* Выведет: 'Str' */

SELECT CONCAT("'", TRIM(TRAILING 'gn' FROM 'String'), "'");

/* Выведет: 'String' */

LTRIM(<Строка>) удаляет пробелы в начале строки:

SELECT CONCAT("'", LTRIM(' String '), "'");

/* Выведет: 'String ' */

RTRIM(<Строка>) удаляет пробелы в конце строки:

SELECT CONCAT("'", RTRIM(' String '), "'");

/* Выведет: ' String' */

LOWER(<Строка>) и LCASE(<Строка>) переводят все символы в нижний регистр:

SELECT LOWER('STRING');

/* Выведет: string */

SELECT LCASE('String');

/* Выведет: string */

UPPER(<Строка>) и UCASE(<Строка>) переводят все символы в верхний регистр:

SELECT UPPER('string');

/* Выведет: STRING */

SELECT UCASE('String');

/* Выведет: STRING */

REVERSE(<Строка>) возвращает строку в обратном порядке:

SELECT REVERSE('string');

/* Выведет: gnirts */

LEFT(<Строка>, <Количество символов>) возвращает заданное количество крайних символов слева:

SELECT LEFT('string', 2);

/* Выведет: st */

RIGHT(<Строка>, <Количество символов>) возвращает заданное количество крайних символов справа:

SELECT RIGHT('string', 2);

/* Выведет: ng */

SUBSTRING(<Строка>, <Начальная позиция>, [<Длина>]),

SUBSTR(<Строка>, <Начальная позиция>, [<Длина>]) и MID(<Строка>, <Начальная позиция>, [<Длина>]) позволяют получить подстроку заданной длины, начиная с позиции <Начальная позиция>. Если параметр <Длина> не задан, то возвращаются все символы до конца строки:

SELECT SUBSTRING('string', 2, 2);

/* Выведет: tr */

SELECT SUBSTR('string', 2, 2);

/* Выведет: tr */

SELECT MID('string', 2);

/* Выведет: tring */

Первые две функции имеют альтернативный синтаксис:

SELECT SUBSTRING('string' FROM 2 FOR 3);

/* Выведет: tri */

SELECT SUBSTRING('string' FROM 2);

/* Выведет: tring */

LPAD(<Строка>, <Длина>, <Подстрока>) добавляет подстроку к исходной строке слева, доводя общую длину строки до величины <Длина>:

SELECT LPAD('string', 11, 'mp');

/* Выведет: mpmpmstring */

RPAD(<Строка>, <Длина>, <Подстрока>) добавляет подстроку к исходной строке справа, доводя общую длину строки до величины <Длина>:

SELECT RPAD('string', 10, 'mp');

/* Выведет: stringmpmp */

REPEAT(<Строка>, <Количество повторений>) возвращает строку, содержащую заданное количество повторений исходной строки:

SELECT REPEAT('str', 3);

/* Выведет: strstrstr */

SPACE(<Количество пробелов>) возвращает строку, состоящую из заданного количества пробелов:

SELECT CONCAT("'", SPACE(3), 'String', "'");

/* Выведет: ' String' */

ELT(<Номер из списка>, <Строка1>, ..., <СтрокаN>) позволяет получить одну строку из списка параметров, номер которой задается первым параметром:

SELECT ELT(2, 'string1', 'string2', 'string3');

/* Выведет: string2 */

ASCII(<Строка>) возвращает код ASCII первого символа строки:

SELECT ASCII('String');

/* Выведет: 83 */

ORD(<Строка>) дает возможность узнать код первого символа строки.

Корректно работает с многобайтными кодировками. Если первый символ — однобайтный, вернет то же значение, что и ASCII():

SELECT ORD('String');

/* Выведет: 83 */

CHAR(<ASCII-код1>, <ASCII-код2>, ..., <ASCII-кодN>) возвращает строку, состоящую из последовательности символов, соответствующих ASCII-кодам:

SELECT CHAR(83, 116, 114, 105, 110, 103);

/* Выведет: String */

INSTR(<Строка>, <Подстрока>) или POSITION(<Подстрока> IN <Строка>) ищут подстроку в строке и возвращают позицию ее первого вхождения. Если вхождение не найдено, то возвращается 0:

SELECT INSTR('string', 'st');

/* Выведет: 1 */

SELECT POSITION('st' IN 'string');

/* Выведет: 1 */

SELECT POSITION('pt' IN 'string');

/* Выведет: 0 */

LOCATE(<Подстрока>, <Строка>, [<Начальная позиция>]) возвращает позицию первого вхождения подстроки в строку, начиная с указанной начальной позиции. Если подстрока не найдена, то возвращается 0. Если начальная позиция не указана, то поиск производится с начала строки:

SELECT LOCATE('st', 'string_st');

/* Выведет: 1 */

SELECT LOCATE('st', 'string_st', 3);

/* Выведет: 8 */

FIELD(<Исходная строка>, <Строка1>, ..., <СтрокаN>) позволяет определить номер строки из списка <Строка1>, ..., <СтрокаN>, которая совпадает с исходной строкой:

SELECT FIELD('st', 'string', 'st', 'st2');

/* Выведет: 2 */

FIND_IN_SET(<Исходная строка>, <Список строк через запятую>) возвращает номер строки из списка <Список строк через запятую>, которая совпадает с исходной строкой:

SELECT FIND_IN_SET('st', 'string,st,st2');

/* Выведет: 2 */

REPLACE(<Строка>, <Подстрока для замены>, <Новая подстрока>) производит замену всех вхождений подстроки для замены на новую подстроку и возвращает результат:

SELECT REPLACE('Привет, Петя', 'Петя', 'Вася');

/* Выведет: Привет, Вася */

SUBSTRING_INDEX(<Строка>, <Подстрока>, <Номер вхождения>) находит N-е вхождение подстроки в строку, где N задается параметром <Номер вхождения>, и возвращает часть строки, расположенную слева от подстроки:

SELECT SUBSTRING_INDEX('синий, красный, зеленый', ',', 1);

/* Выведет: синий */

SELECT SUBSTRING_INDEX('синий, красный, зеленый', ',', 2);

/* Выведет: синий, красный */

Если параметр <Номер вхождения> имеет отрицательное значение, то ищется N-е вхождение подстроки с конца строки и возвращается часть строки, расположенная справа от найденной подстроки:

SELECT CONCAT('"', SUBSTRING_INDEX('синий, красный, зеленый',',', -1), '"');

/* Выведет: " зеленый" */

SELECT CONCAT('"', SUBSTRING_INDEX('синий, красный, зеленый', ',', -2), '"');

/* Выведет: " красный, зеленый" */

INSERT(<Строка>, <Начальная позиция>, <Длина>, <Подстрока>) заменяет фрагмент в строке с начальной позиции длиной <Длина> на значение

параметра <Подстрока>:

SELECT INSERT('красный', 6, 2, 'ое');/* Выведет: красное */

SELECT INSERT('красный', 6, 1, 'ое');/* Выведет: красноей */

QUOTE(<Строка>) экранирует все специальные символы в строке:

SELECT QUOTE("Д'Артаньян и три мушкетера");

/* Выведет: Д\'Артаньян и три мушкетера */

UNHEX(<Строка>) переводит строку из шестнадцатеричных цифр в обычную строку. Каждая пара символов в исходной строке воспринимается как шестнадцатеричное число, которое преобразуется в символ:

SELECT UNHEX('537472696E67');

/* Выведет: String */

COMPRESS(<Строка>) архивирует строку. Сжатую строку следует хранить в полях, имеющих бинарный тип данных;

UNCOMPRESS(<Строка>) разархивирует строку, сжатую функцией COMPRESS();

UNCOMPRESSED_LENGTH(<Строка>) позволяет узнать длину строки, которую она будет иметь после разархивирования:

SELECT UNCOMPRESSED_LENGTH(COMPRESS('Строка'));

/* Выведет: 6 */

CHARSET(<Строка>) возвращает название кодировки для строки:

SET NAMES 'cp866';

SELECT CHARSET('Строка');

/* Выведет: cp866 */

COLLATION(<Строка>) возвращает порядок сортировки для строки:

SET NAMES 'cp866';

SELECT COLLATION('Строка');

/* Выведет: cp866_general_ci */

STRCMP(<Строка1>, <Строка2>) сравнивает две строки и возвращает:

• 0 — если строки идентичны;

• -1 — если <Строка1> больше <Строка2>;

• 1 — если <Строка1> меньше <Строка2>.

Например,

SELECT STRCMP('Строка', 'Строка');

/* Выведет: 0 */

SELECT STRCMP('Строка1', 'Строка2');

/* Выведет: -1 */

SELECT STRCMP('Строка2', 'Строка1');

/* Выведет: 1 */

Сравнение строк чувствительно к регистру;

LOAD_FILE(<Путь к файлу>) — возвращает содержимое файла в виде строки. Часто используется для заполнения бинарных полей. В качестве примера создадим текстовый файл с названием test.txt в папке C:\Apache2. Затем запишем в файл строку "Content". Теперь получим содержимое файла с помощью функции LOAD_FILE():

SELECT LOAD_FILE('C:/Apache2/test.txt');

/* Выведет: Content */

Также в SQL-запросах можно использовать следующие функции:

IF(<Условие>, <Если Истина>, <Если Ложь>) — функция для логиче- ского выбора. Если <Условие> истинно, то возвращается значение выражения <Если Истина>, в противном случае возвращается значение выражения <Если Ложь>;

SELECT IF(5>6, 'Больше', 'Меньше');

/* Выведет: Меньше */

CASE() — функция для логического выбора. Имеет две формы записи.

Первая форма:

CASE <Переменная или выражение>

WHEN <Значение 1> THEN <Выражение 1>

[WHEN <Значение 2> THEN <Выражение 2>]

…

[ELSE <Выражение>] END

В зависимости от значения переменной (или выражения) выполняется один из блоков WHEN, в котором указано это значение.

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

SELECT CASE 3 + 5 WHEN 8 THEN 'Равно 8'

WHEN 7 THEN 'Равно 7' ELSE 'Не смогли определить' END;

/* Выведет: Равно 8 */

Вторая форма:

CASE WHEN <Условие 1> THEN <Выражение 1>

[WHEN <Условие 2> THEN <Выражение 2>]

…

[ELSE <Выражение>] END

Например:

SELECT CASE WHEN 5>6 THEN 'Больше' ELSE 'Меньше' END;

/* Выведет: Меньше */

IFNULL(<Выражение1>, <Выражение2>) позволяет заменить значения NULL другими значениями. Если <Выражение1> не равно NULL, то функция возвращает <Выражение1>. В противном случае функция возвращает <Выражение2>:

SELECT IFNULL(5, 3);

/* Выведет: 5 */

SELECT IFNULL(NULL, 3);

/* Выведет: 3 */

NULLIF(<Выражение1>, <Выражение2>) — функция для логического выбора. Если <Выражение1> равно <Выражение2>, возвращается значение NULL, в противном случае возвращается <Выражение1>:

SELECT NULLIF(5, 5);

/* Выведет: NULL */

SELECT NULLIF(5, 3);

/* Выведет: 5 */

INET_ATON(<IP-адрес>) представляет IP-адрес в виде целого числа:

SELECT INET_ATON('127.0.0.1');

/* Выведет: 2130706433 */

INET_NTOA(<IP-адрес в виде числа>) принимает IP-адрес в виде целого числа и возвращает IP-адрес в виде строки, состоящей из четырех цифр, разделенных точкой:

SELECT INET_NTOA(2130706433);

/* Выведет: 127.0.0.1 */

GET_LOCK(<Имя>, <Время ожидания ответа сервера>) устанавливает блокировку с указанным именем. Функция возвращает 1 в случае успешной блокировки и 0, если время ожидания ответа сервера превысило величину, заданную в секундах параметром <Время ожидания ответа сервера>. Если произошла ошибка, то функция возвращает NULL. Блокировка снимается тремя способами:

• с помощью функции RELEASE_LOCK();

• при повторном вызове функции GET_LOCK();

• при разрыве соединения с сервером.

Например:

SELECT GET_LOCK('mylock', 5);

/* Выведет: 1 */

IS_FREE_LOCK(<Имя блокировки>) проверяет, свободна ли блокировка с указанным именем. Функция возвращает 1, если блокировка свободна, и 0, если она занята:

SELECT IS_FREE_LOCK('mylock');

/* Выведет: 0 */

IS_USED_LOCK(<Имя блокировки>) проверяет, установлена ли блокировка с указанным именем. Если блокировка установлена, то возвращается идентификатор соединения клиента, который установил блокировку:

SELECT IS_USED_LOCK('mylock');

/* Выведет: 1 */

SELECT CONNECTION_ID();

/* Выведет: 1 */

Если блокировка не установлена, то возвращается значение NULL;

RELEASE_LOCK(<Имя блокировки>) снимает блокировку с указанным именем. Если блокировка успешно снята, то функция возвращает 1. Если блокировка не может быть снята, то возвращается 0. Если блокировка с указанным именем не существует, то функция возвращает NULL:

SELECT RELEASE_LOCK('mylock');

/* Выведет: 1 */

SELECT IS_USED_LOCK('mylock');

/* Выведет: NULL */

UUID() возвращает универсальный уникальный идентификатор — 128-разрядное уникальное число в виде строки, состоящее из пяти шестнадцатеричных чисел, разделенных символом "-";

SELECT UUID();

/* Выведет: 9884721ded-2010-2ba9-71be-e337690000 */

SELECT UUID();

/* Выведет: a413be1fed-2010-2ba9-71be-e337690000 */

Используемый алгоритм гарантирует глобальную уникальность возвращенного идентификатора;

GROUP_CONCAT() объединяет отдельные значения в одну строку. Функция имеет следующий формат:

GROUP_CONCAT([DISTINCT] <Поле1> [, <ПолеN>]

[ORDER BY <Поле> [ASC | DESC]]

[SEPARATOR <Разделитель>])