MySQL: установка, настройка, описание - Функции для SELECT и WHERE

Written on . Posted in MySQL

ОГЛАВЛЕНИЕ

Страница 14 из 16

Функции для SELECT и WHERE

Между именем функции и скобкой не должно быть пробела. Операндами могут быть константы, имена колонок и переменные.

  • группировка функций с помощью круглых скобок
  • арифметические операции: +, -, *, /
  • битовые функции: |, &, <<, >>, ~, BIT_COUNT()
  • логические функции: !, ||, && (возвращают 1, 0, или NULL)
  • сравнение:
    • =, , !=, <=, <, >=, >
    • <=> (сравнение, позволяющее сравнивать NULL)
    • expr IS NULL, expr IS NOT NULL
    • expr BETWEEN min AND max
    • expr IN (значение1,...)
    • expr NOT IN (значение1,...)
    • ISNULL()
    • COALESCE(список) - возвращает первый ненулевой элемент списка
    • INTERVAL(N,N1,N2,N3,...) - возвращает номер интервала, т.е. 1, если N < N1; 2 если N < N2 и т.д.
  • сравнение строк (если выражение чувствительно к регистру, то и сравнение будет чувствительным к регистру)
    • expr LIKE шаблон [ESCAPE 'escape-символ'] (соответствие простому регулярному выражению SQL; "%" - любое количество любых символов; "_" - ровно один символ; escape-символ по умолчанию - "\"; чтобы найти '\n' надо задать в шаблоне '\\n'; чтобы найти '\' - '\\\\')
    • expr NOT LIKE шаблон [ESCAPE 'escape-символ']
    • expr REGEXP шаблон (расширенное регулярное выражение)
    • expr RLIKE шаблон (расширенное регулярное выражение)
    • expr NOT REGEXP шаблон
    • expr NOT RLIKE шаблон
    • STRCMP(expr1,expr2) (арифметическое сравнение строк: -1 если первая строка меньше, 0 если строки равны, 1 если первая строка больше)
    • MATCH (колонка1,колонка2,...) AGAINST (expr) (возвращает меру релевантности - положительное вещественное число - между содержимым колонок и выражением; должен быть создан FULLTEXT индекс)
  • преобразование типов
    • BINARY (преобразование строки в двоичную - т.е. чувствительную к регистру)
  • условные выражения
    • IFNULL(expr1,expr2) (если expr1 есть NULL, то возвращает expr2, иначе expr1)
    • NULLIF(expr1,expr2) (если выражения равны, то возвращает NULL, иначе - expr1; expr1 может вычисляться дважды)
    • IF(expr1,expr2,expr3) (если expr1, то возвращает expr2, иначе expr3)
    • CASE значение1 WHEN [значение2] THEN результат1 [WHEN [значение3] THEN результат2 ...] [ELSE результатn] END (если значение1 равно значение2, то вернуть результат1, иначе сравнить значение1 и значение3 и т.д.; в противном случае результатn; если его нет, то NULL)
    • CASE WHEN [условие1] THEN результат1 [WHEN [условие2] THEN результат2 ...] [ELSE результатn] END
  • математические функции (в случае ошибки возвращают NULL; подробнее см. библиотеку C)
    • унарный "-"
    • ABS(X), SIGN(X), MOD(N,M), FLOOR(X), CEILING(X), ROUND(X), ROUND(X,число-цифр-после-точки), EXP(X), LOG(X), LOG10(X), POWER(X,Y), SQRT(X), PI(), COS(радиан), SIN(радиан), TAN(радиан), ACOS(X), ASIN(X), ATAN(X), ATAN2(Y,X), COT(X), RAND(), RAND(seed), LEAST(X,Y,...), GREATEST(X,Y,...), DEGREES(радиан), RADIANS(градусов), TRUNCATE(X,число-цифр-после-точки)
  • обработка строк (возвращают NULL, если длина результата больше max_allowed_packet; позиции нумеруются с 1)
    • ASCII(str) - ASCII-код первого символа строки
    • ORD(str) - ASCII-код с учетом мультибайтных символов
    • CONV(N,основание1,основание2) - преобразование числа из представления с основанием1 в представление с основанием2
    • BIN(N) - синоним CONV(N,10,2)
    • OCT(N) - синоним CONV(N,10,8)
    • HEX(N) - синоним CONV(N,10,16)
    • CHAR(целое,...) - возвращает строку, составленную из символов с соответствующими кодами
    • CONCAT(str1,str2,...)
    • CONCAT_WS(разделитель,str1,str2,...) - добавляет разделитель между сливаемыми строками
    • LENGTH(str), OCTET_LENGTH(str)
    • CHAR_LENGTH(str), CHARACTER_LENGTH(str) - понимает многобайтные символы
    • LOCATE(подстрока,строка[,начальная-позиция]), POSITION(подстрока IN строка), INSTR(строка,подстрока) - понимает многобайтные символы
    • LPAD(строка,длина,строка-заполнитель) - дополнить строку слева до достижения указанной длины
    • RPAD(строка,длина,строка-заполнитель) - дополнить строку справа до достижения указанной длины
    • LEFT(строка,длина) - вернуть указанное число символов слева, понимает многобайтные символы
    • RIGHT(строка,длина) - вернуть указанное число символов справа, понимает многобайтные символы
    • SUBSTRING(строка,начальная-позиция[,длина]), SUBSTRING(строка FROM начальная-позиция[ FOR длина]), MID(строка,начальная-позиция,длина) - понимает многобайтные символы
    • SUBSTRING_INDEX(строка,разделитель,счетчик) - подстрока до указанного числа разделителей (если число отрицательное, то справа; понимает многобайтные символы)
    • LTRIM(строка) - удаление левых пробелов
    • RTRIM(строка) - удаление правых пробелов
    • TRIM([[BOTH | LEADING | TRAILING] [префикс] FROM] строка) - если префикс не указан, то удаляются пробелы)
    • SPACE(число) - указанное число пробелов
    • REPLACE(строка,что,на-что) - понимает многобайтные символы
    • REPEAT(str,count)
    • REVERSE(str) - понимает многобайтные символы
    • INSERT(строка,позиция,длина,новая-строка) - на самом деле не вставка, а замена; понимает многобайтные символы
    • ELT(N,str1,str2,...) - возвращает N-ю строку
    • FIELD(str,str1,str2,str3,...) - возвращает индекс строки str в списке str1,...
    • FIND_IN_SET(строка,список) - номер строки в списке (строки через запятую или колонка типа SET)
    • MAKE_SET(биты,строка1,строка2,...) - конкатенирует строки в список через запятую, используются только строки для которых взведен бит
    • EXPORT_BIT(биты,on-строка,off-строка[,разделитель,число-бит]) - EXPORT_SET(5,'Y','N',',',4) выдает 'Y,N,Y,N'
    • LCASE(строка) - переводит все буквы в нижний регистр (строчные)
    • LOWER(строка) - переводит все буквы в нижний регистр (строчные)
    • UCASE(строка) - переводит все буквы в верхний регистр (прописные)
    • UPPER(строка) - переводит все буквы в верхний регистр (прописные)
    • LOAD_FILE(полное-имя-файла) - содержимое файла возвращается как строка, необходимо иметь привилегии работы с файлами; размер файла ограничен max_allowed_packet
  • работа со временем и датами
    • DAYOFWEEK(дата) - воскресенье: 1, понедельник: 2 и т.д.
    • WEEKDAY(дата) - понедельник: 0, вторник: 1 и т.д.
    • DAYOFMONTH(дата)
    • DAYOFYEAR(дата)
    • MONTH(дата)
    • DAYNAME(дата) - в виде: 'Thursday'
    • MONTHNAME(дата) - в виде 'February'
    • QUARTER(дата)
    • WEEK(дата) - от 0 до 53; первый день недели - воскресенье
    • WEEK(дата,начало-недели) - если начало-недели равно 0, то неделя начинается с воскресенья, если равно 1, то с понедельника
    • YEAR(дата)
    • YEARWEEK(дата[,начало-недели]) - год и номер недели
    • HOUR(время)
    • MINUTE(время)
    • SECOND(время)
    • PERIOD_ADD(P,N) - добавить N месяцев к периоду P (P в формате YYYYMM)
    • PERIOD_DIFF(P1,P2) - число месяцев между периодами
    • DATE_ADD(дата,INTERVAL выражение единица-измерения) или ADDDATE(дата,INTERVAL выражение единица-измерения) или "время" + INTERVAL выражение единица-измерения: добавить интервал к дате/времени
        где единицы-измерения:
      • SECOND
      • MINUTE
      • HOUR
      • DAY
      • MONTH ('1998-01-30' + Interval 1 month => '1998-02-28')
      • YEAR
      • MINUTE_SECOND ("минут:секунд")
      • HOUR_MINUTE ("часов:минут")
      • DAY_HOUR ("дней часов")
      • YEAR_MONTH ("лет-месяцев")
      • HOUR_SECOND ("часов:минут:секунд")
      • DAY_MINUTE ("дней часов:минут")
      • DAY_SECUND ("дней часов:минут:секунд")
    • DATE_SUB(дата,INTERVAL выражение единица-измерения) или SUBDATE(дата,INTERVAL выражение единица-измерения) или "время" - INTERVAL выражение единица-измерения: вычесть интервал из даты/времени
    • EXTRACT(единица-измерения FROM дата) - возвращает интервал указанного типа
    • TO_DAYS(дата) - возвращает номер дня с 0-го года (не учитывает момент перехода на Грегорианский календарь)
    • FROM_DAYS(N) - обратная к TO_DAYS()
    • DATE_FORMAT(дата,формат) - преобразует дату в соответствии с форматом (см. strftime(3), кроме отсутствия i18n: %M - полное английское имя месяца; %W - полное английское имя дня недели; %D - день месяца с английским суффиксом; %c - номер месяца от 1 до 12; %h - час от 01 до 12; %i - минуты от 00 до 59)
    • TIME_FORMAT(время,формат) - аналогично DATE_FORMAT, но ограниченно часами, минутами и секундами
    • CURDATE() - '1997-12-15' или 19971215 в зависимости от контекста
    • CURRENT_DATE - синоним CURDATE()
    • CURTIME() - '23:50:26' или 235026 в зависимости от контекста
    • CURRENT_TIME - синоним CURTIME()
    • NOW() или SYSDATE() или CURRENT_TIMESTAMP - '1997-12-15 23:50:26' или 19971215235026 в зависимости от контекста
    • UNIX_TIMESTAMP([дата]) - число секунд с 1 января 1970 GMT, дата в локальном времени
    • FROM_UNIXTIME(timestamp[,формат]) - обратная к UNIX_TIMESTAMP()
    • SEC_TO_TIME(секунд) - преобразует секунды в часы:минуты:секунды
    • TIME_TO_SEC(время) - преобразует часы:минуты:секунды в секунды
  • разное
    • DATABASE() - имя текущей БД
    • USER() или SYSTEM_USER() или SESION_USER() - текущее имя пользователя и хост ('root@localhost')
    • PASSWORD(строка) - перевод пароля во внутренний вид (не UNIX!)
    • ENCRYPT(строка[,соль]) - UNIX-овый преобразование пароля
    • ENCODE(строка,пароль) - шифровка (какой алгоритм?)
    • DECODE(шифрованная-строка,пароль) - расшифровка
    • MD5(строка)
    • LAST_INSERT_ID([выражение])
    • FORMAT(X,D) - вывод числа в виде 'xxx,xxx.xx', где D - число цифр после точки
    • VERSION() - '3.23.37-log'
    • CONNECTION_ID()
    • GET_LOCK(имя-замка,timeout-секунд) - возвращает 1 при успехе, 0 - при timeout, NULL - при ошибке; освобождается при RELEASE_LOCK(), новом GET_LOCK() или завершении потока
    • RELEASE_LOCK(имя-замка) - возвращает 1 при успехе; 0 - если замок был закрыт не этим потоком; NULL - если замок не существует
    • INET_NTOA(выражение) - преобразование IP-адреса в сетевом формате в "точечный" формат
    • INET_ATON(выражение) - преобразование IP-адреса из "точечного" формата в целое с сетевым порядком байт
    • MASTER_POS_WAIT(имя-журнала,позиция) - синхроницация репликации
  • функции для group by (использование данных функций в операторе без group by влечет группирование всех строк)
    • COUNT(выражение) - число не-NULL значений в извлеченных строках
    • COUNT(*) - число извлеченных строк
    • COUNT(DISTINCT выражение,[выражение...]) - число различных не-NULL значений
    • AVG(выражение) - среднее
    • MIN(выражение), MAX(выражение)
    • SUM(выражение) - сумма (если не извлечено ни одной строки, то NULL)
    • STD(выражение) или STDDEV(выражение) - стандартное отклонение
    • BIT_OR(выражение) - побитовое или
    • BIT_AND(выражение) - побитовое И

форум mysql