SQL

Конспект застосування функцій у запитах MySQL та MariaDB

Leave a comment

//

  • офіційний MySQL 8.0 Reference Manual > Data Manipulation Statements //dev.mysql.com
  • MariaDB: Complete list of SQL statements for data definition, data manipulation, etc. //mariadb.com
  • METANIT.COM MySQL Операции с данными //metanit.com

ГРАНДИОЗНЫЙ РЕФАКТОРИНГ!

Справочник !!! VPN //www.mysql.ru/docs/mysql-man-4.0-ru/reference.html

!!! MySQL — Использование переменных в запросе https://habr.com/ru/post/442706/

DESCRIBE, EXPLAIN операторы используются либо для получения информации о структуре таблицы, либо для планов выполнения запроса

Інформаційні функції (и ниже!!!)

//dev.mysql.com/doc/refman/8.0/en/information-functions.html

  • VERSION() функция возвращает строку в кодировке utf8mb3 с версией сервера MySQL
  • ROW_COUNT() возвращает количество строк, затронутых последним оператором, как UPDATE, INSERT или DELETE,  кроме SELECT
    • для чистого SELECT возвращает -1
    • если в INSERT...ON DUPLICATE KEY UPDATE был выполнен UPDATE, то операция засчитывается как две строки
  • FOUND_ROWS() в операторе SELECT...LIMIT в сочетании с опцией SQL_CALC_FOUND_ROWS возвращает общее количество выбранных строк до применения LIMIT
  • LAST_INSERT_ID() возвращает значение BIGINT UNSIGNED (x64) первого автоматически сгенерированного значения в столбце AUTO_INCREMENT при успешной вставке INSERT, или остается неизменным, если вставке не выполнилась
    • LAST_INSERT_ID(expr)  с аргументом возвращает значение с типом INTEGER UNSIGNED
  • USER() возвращает строку с именем пользователя и хостом вида admin@188.163.47.24
  • DATABASE(), SCHEMA() возвращает имя DB

Информационные функции (и выше!!!)

Information Functions //dev.mysql.com

  • ROW_COUNT() возвращает количество записей обработанных выражениями UPDATE, INSERT, DELETE и ALTER TABLE, LOAD DATA для MySQL 8.0
    • выражения INSERT...ON DUPLICATE KEY UPDATE и REPLACE для каждой записи в, которой выполнено обновление, увеличивают счетчик +2
  • FOUND_ROWS() (устарела с 8.0.17, но присутствует) в сочетании с параметром SQL_CALC_FOUND_ROWS  возвращает число записей выбранных оператором SELECT до применения оператора LIMIT

Функции

MySQL 8.0 Reference Manual > Functions and Operators > Date and Time Functions //dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

  • Константа даты имеет полный строковый формат 'yyyy-mm-dd hh:nn:ss.sss'
  • SET time_zone = '+2:00'; - устанавливает временную зону на время действия соединения
  • NOW() - текущее время сервера с учетом временной зоны
  • CURDATE() - функция возвращает текущую дату
  • UTC_TIMESTAMP() - текущее время сервера по Гринвичу (UTC - Coordinated Universal Time)
  • DATE(expr) извлекает DATE (без времени) из DATETIME, VARCHAR и т.п.
  • DATE_ADD(date, INTERVAL expr unit) -добавляет временной интервал к значению датавремени (вычитает, при отрицательном значение интервала)
    • DATE_SUB(date,INTERVAL expr unit) - вычитает временной интервал
    • простые юниты интервала MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND
    • комбинированные юниты интервала MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH
  • DATEDIFF(expr1,expr2) returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation
  • TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function
  • TIMEDIFF(expr1,expr2) returns expr1 − expr2 expressed as a time value. expr1 and expr2 are strings which are converted to TIME or DATETIME expressions; these must be of the same type following conversion. Returns NULL if expr1 or expr2 is NULL.
    • 838:59:59
  • Управление потоком
    • IFNULL(expression, alt_value) если произвольное sql-выражение expression принимает значение NULL, будет возвращено значение alt_value; например, выражение может быть подселектом, который не нашел значения
    • IF(condition, value_if_true, value_if_false) эквивалентна выражению условному оператору для вычисления значения
    • NULLIF(expr1, expr2) возвращает NULL, если выражения имеют равное значение
  • LAST_INSERT_ID() возвращает идентификатор AUTO_INCREMENT последней строки, которая была вставлена ​​или обновлена ​​в таблице.
  • ROW_COUNT() (v8.0) нумерует строки
    • стала доступна в версии 8.0, в версии 5.7 реализуется искусственным приемом:

Управление потоком

//dev.mysql.com/doc/refman/5.7/en/flow-control-functions.html

В документации группа функций управления потоком содержит 4 описания: оператора CASE и трех функций, названных конструктами:

  • IF(condition, value_if_true, value_if_false) эквивалентна выражению условному оператору для вычисления значения
  • IFNULL(expression, alt_value) если произвольное sql-выражение expression принимает значение NULL, будет возвращено значение alt_value; например, выражение может быть подселектом, который не нашел значения
  • NULLIF(expr1, expr2) возвращает NULL, если выражения имеют равное значение

Преобразование типов

//

  • INET_ATON(expr) Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument, or if expr is NULL.
  • INET_NTOA(expr) Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.

Функции поддержки JSON

Работа с JSON поддерживается
//dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

  • JSON_EXTRACT(json_doc, path[, path] ...) извлекает значение или json-массив значений из json-документа
  • JSON_UNQUOTE(json_val) извлекает исходные данные из json-представления - удаляет двойные кавычки на границах и заменят спецсимволы json-значения (кавычка \", забой \b, \f, новая строка \n, возврат каретки \r, табуляция \t, обратный слеш \\, юникод \uXXXX)

Пример обработки json-документа:

Пример использования значения из json-документа в выражении sql-запроса:

Функції

MySQL 8.0 Reference Manual > Functions and Operators > String Functions and Operators //dev.mysql.com/doc/refman/8.0/en/string-functions.html

Універсальна обробка значень

 //dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

  • IF(condition,onTrue,onFalse) если выражение condition принимает значение True, то функция возвратит выражение onTrue, иначе выражение onFalse
  • IFNULL(expression,onNull) если выражение expression имеет значение NULL, то функция возвратит выражение onNull
  • NULLIF(expr1,expr2) функция возвратит NULL при равенстве значений expr1 и expr2
  • COALESCE(value,...) из двух или более аргументов возвращает первый отличный от NULL
  • LEAST(value1,value2,...) из двух или более аргументов возвращает наименьший
    • если любой из аргументов Null, результат будет Null
    • /
  • GREATEST(value1,value2,...) из двух или более аргументов возвращает наибольший или Null, если все аргументы были Null

Числові

Numeric Functions and Operators //dev.mysql.com/doc/refman/8.0/en/numeric-functions.html

  • Оператори
    • DIV Целочисленное деление. Из результата деления выбрасывается любая дробная часть справа от десятичной точки. Если любой из операндов имеет нецелый тип, операнды преобразуются в DECIMAL и делятся с использованием арифметики DECIMAL перед преобразованием результата в BIGINT. Если результат превышает диапазон BIGINT, возникает ошибка.
  • Функции
    • ROUND(value[,precision]) возвращает математическое округление числового value
      • precision необязательное целое значение числа знаков после десятичного разделителя, по умолчанию 0
        • отрицательно значение округляет младшие разряды целой части
    • RAND([N]) Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). For example, to obtain a random integer in the range the range 7 <= R < 12, use the following statement:
    • FLOOR(X) Returns the largest integer value not greater than X. Returns NULL if X is NULL.
    • CEIL(), CEILING() Return the smallest integer value not less than the argument

Кодування

//

  • CAST(expr AS type [ARRAY]) приводит выражение любого типа к указанному типу
    • CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)]) для timezone_specifier допустимы только значения '+00:00' или 'UTC'
  • CONVERT(expr USING transcoding_name)  функция
    • CONVERT(expr, type) синтаксис без USING  эквивалентен CAST
  • CONV(num_str, base, to_base) преобразует строковое представление числа num_str в системе счисления с основанием base в строковое в системе счисления с основанием to_base
    • num_str строковое представление числового значения
      • при получении значения или выражения числового типа, оно будет сначала приведено к строковому как десятичное (независимо от base)
    • base числовое основание системы счисления от 2 до 36 включительно, в которой принимается строковое num_str
      • если в num_str допущен символ за пределами системы счисления base, оставшиеся символы игнорируются
    • to_base числовое основание системы счисления результата
    • преобразование выполняется над значениями до 64 двоичных бит
      • при переполнении результирующее значением будет эквивалентно X'FFFFFFFFFFFFFFFF'
  • CRC32(expr) Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.
    mysql> SELECT CRC32('MySQL')
  • MD5(str) возвращает значение 128-битной контрольной суммы MD5 для строки  в виде строки из 32 шестнадцатеричных цифр или NULL, если аргумент был NULL
  • RANDOM_BYTES(len) возвращает двоичную строку из len случайных байтов, сгенерированную с помощью генератора случайных чисел библиотеки SSL
    • len от 1 до 1024

Функції рядкові +

String Functions and Operators //dev.mysql.com/doc/refman/8.0/en/string-functions.html

  • CHAR_LENGTH(str) Return number of characters in argument
    • LENGTH(str) Return the length of a string in bytes
    • BIT_LENGTH(str) Return length of argument in bits
    • функции синонимы CHARACTER_LENGTH() и OCTET_LENGTH()
  • CONCAT(str1,str2,...) Return concatenated string
    • CONCAT_WS(separator,str1,str2,...) Return concatenate with separator
  • LPAD(str,len,padstr) Возвращает строку str, дополненную слева строкой padstr до длины len символов. Если str длиннее len, возвращаемое значение сокращается до len символов. Если str, padstr или len имеют значение NULL, функция возвращает значение NULL
    • RPAD(str,len,padstr) *
  • INSERT(str,pos,len,newstr) Insert substring at specified position up to specified number of characters
  • CHAR(N,... [USING charset_name]) Return the character for each integer passed
  • ASCII(str) Return numeric value of left-most character
    • ORD() Return character code for leftmost character of the argument
  • UUID() Returns a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace”
  • BIN() Return a string containing binary representation of a number
  • BIN_TO_UUID(binary_uuid), BIN_TO_UUID(binary_uuid, swap_flag) преобразует двоичный UUID в строковый UUID и возвращает результат. Двоичное значение должно быть UUID как значение VARBINARY(16)..
  • UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag) Converts a string UUID to a binary UUID and returns the result. (The IS_UUID() function description lists the permitted string UUID formats.) The return binary UUID is a VARBINARY(16) value. If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.
  • STRCMP(expr1,expr2) сравнивает строковые значения и возвращает один из индексов отношения {-1; 0; 1}
  • FORMAT(X,D[,locale]) Return a number formatted to specified number of decimal places
  • INSTR(str,substr) Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.
  • REPEAT(str,count) Returns a string consisting of the string str repeated count times
  • REVERSE(str) Returns the string str with the order of the characters reversed, or NULL if str is NULL
  • SUBSTRING_INDEX(str, delim, count) находит в строке str разделители delim  и возвращает фрагмент
    • count числовой порядковый номер найденного разделителя (отсчитывается от 1)
      • положительный отсчитывается разделители от начала строки и возвращает фрагмент от начала str до этого разделителя
      • отрицательный отсчитывается от конца строки и возвращает фрагмент от этого разделителя до конца str
  • SUBSTRING(str,pos),
    SUBSTRING(str FROM pos),
    SUBSTRING(str, pos,len),
    SUBSTRING(str FROM pos FOR len) возвращает фрагмент строки str

    • pos  начальная позиция фрагмента в str с нумерацией от 1
      • значение приводится к целому с математическим округлением
      • отрицательное pos отсчитывается от конца str
    • len устанавливает длину фрагмента, иначе до конца str
    • SUBSTR() полный синоним функции SUBSTRING()
  • FROM_BASE64(str) Decode base64 encoded string and return result
    • TO_BASE64() Return the argument converted to a base-64 string
  • HEX(str), HEX(N) Hexadecimal representation of decimal or string value
    Для строкового аргумента str функция HEX() возвращает шестнадцатеричное строковое представление str, где каждый байт каждого символа в str преобразуется в две шестнадцатеричные цифры. (Поэтому многобайтовые символы становятся более чем двумя цифрами.) Обратное этой операции выполняется функцией UNHEX(). Для числового аргумента N функция HEX() возвращает шестнадцатеричное строковое представление значения N, рассматриваемого как длинное (BIGINT) число. Это эквивалентно CONV(N,10,16). Обратное этой операции выполняется с помощью CONV(HEX(N),16,10).
  • UNHEX() Return a string containing hex representation of a number
  • TRIM(str) Remove leading and trailing spaces
    • RTRIM(str) Remove trailing spaces
  • SOUNDEX(str) формирует soundex-строку для поиска похожих слов
  • LOAD_FILE(file_name) Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege.

Функції рядкових наборів

//

  • ELT(N,str1,str2,str3,...) возвращает значение N-параметра при отсчете с параметра следующего за N, начиная с 1, или NULL
  • FIELD(str,str1,str2,str3,...) возвращает индекс параметра со значением str при отсчете параметров сдедующих за str начиная с 1, или 0, если значение str не найдено; значения сопоставляются как числовые, если среди них нет строковых, иначе все значения приводятся к строковым
  • FIND_IN_SET(str,strset) возвращает индекс от 1 до N, если строковое str находится в списке strset из N подстрок, или 0 (NULL, если любой из аргументов равен NULL

Функции регулярных выражений

//

  • REGEXP_LIKE()
  • REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
  • REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
  • REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

Дата та час

//dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
//

Текущее время

Получение текущего времени не так очевидно, как кажется, особенно в системах работающих в различных временных зонах. Верный способ - использовать время UTC.

  • UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP() глобальные дата и время UTC (Coordinated Universal Time)
  • CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, NOW() метка текущего времени в часовом поясе текущего сеанса (UTC + timezone)
  • CURRENT_DATE(), CURRENT_DATE дата в текущем сеансе
  • CURRENT_TIME(), CURRENT_TIME время в текущем сеансе

Другие функции

  • CONVERT_TZ(dt,from_tz,to_tz) для значения dt типа DATETIME в часовом поясе from_tz возвращает измененное значение для часового пояса to_tz
  • UNIX_TIMESTAMP([timestamp]) возвращает числовую метку времени для метки timestamp или текущей метки времени сеанса или Null
  • FROM_UNIXTIME(uts[,format]) для заданной unix-метки времени uts возвращает соответствующее значение даты для часового пояса сеанса, тип значения DATETIME или VARCHAR определяется вторым необязательным параметром
    • format строковое значение
      • при опущенном параметре функция возвращает значение DATETIME
  • DATEDIFF(expr1,expr2) возвращает календарную разность  expr1-expr2 между двумя датами в календарных днях, время при этом полностью игнорируется

Обробка даних типу JSON

Тип JSON похож на LONGBLOB или LONGTEXT, но значения типа JSON рассматриваются как документ, который во-первых, при записи проходит проверку валидности с генерацией ошибки, и во-вторых, хранится во внутреннем формате, обеспечивающем быстрый доступ к элементам документа средствами SQL. Если ни валидация, ни обработка json-документа не требуются, использование типа JSON не будет целесообразно.

  • SON_UNQUOTE(json_val)  преобразует строковое представление json-документа в json-значение
  • JSON_EXTRACT(json_doc, path[, path] ...) Возвращает данные из документа JSON, выбранные из частей документа, соответствующих аргументам пути. Возвращает NULL, если какой-либо аргумент имеет значение NULL или нет путей, определяющих местонахождение значения в документе. Ошибка возникает, если аргумент json_doc не является допустимым документом JSON или любой аргумент пути не является допустимым выражением пути. Возвращаемое значение состоит из всех значений, соответствующих аргументам пути. Если возможно, что эти аргументы могут возвращать несколько значений, совпадающие значения автоматически упаковываются в массив в порядке, соответствующем путям, которые их произвели. В противном случае возвращаемое значение является единственным совпавшим значением
  • JSON_STORAGE_SIZE(json_val)  возвращает количество байтов, используемых для хранения двоичного представления значения JSON или строки представления валидного json-документа
  • JSON_SET(json_doc, path, val[, path, val] ...)  вставляет или замещает значение по указанному пути
  • JSON_INSERT(json_doc, path, val[, path, val] ...)  вставляет значение, если по указанному пути значения не существует
  • JSON_REPLACE(json_doc, path, val[, path, val] ...) замещает значение новым, если в указанном пути оно уже существует
  • JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) вставляет значение в конце массива по указанному пути
  • JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) вставляет значение по указанному пути массива в указанный индекс
  • JSON_REMOVE(json_doc, path[, path] ...)  удаляет значение в указанном пути

//

  • JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)  объединяет два или более json-документов в один, при совпадении пути значение замещает предыдущее
  • JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)  объединяет два или более json-документов в один, при совпадении пути значение добавляется в массив значений
    • JSON_MERGE(...)  устаревший синоним

Leave a Reply