Mysql函数和运算符（看一点写一点）

CONCAT 连接函数

SELECT CONCAT(2,' test');        -> '2 test'

还能隐式把字符类型改成字符串

SELECT 38.8, CONCAT(38.8);        -> 38.8, '38.8'

CAST字符类型转换

SELECT 38.8, CAST(38.8 AS CHAR);        -> 38.8, '38.8'

运算符

NameDescriptionAND, &&Logical AND=Assign(分配) a value (as part of a SET statement(声明), or as part of the SET clause in an UPDATE statement):=Assign a valueBETWEEN ... AND ...Check whether a value is within a range of valuesBINARYCast a string to a binary(二进制的) string&Bitwise AND~Bitwise(按位) inversion(倒置)|Bitwise OR^Bitwise XORCASECase operatorDIVInteger(整数) division/Division operator=Equal operator<=>NULL-safe equal to operator>Greater than operator>=Greater than or equal operatorISTest a value against a booleanIS NOTTest a value against a booleanIS NOT NULLNOT NULL value testIS NULLNULL value test->Return value from JSON column after evaluating(评价) path; equivalent(等价的) to JSON_EXTRACT().->>Return value from JSON column after evaluating path and unquoting(结束) the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).<<Left shift<Less than operator<=Less than or equal operatorLIKESimple pattern matching-Minus operator%, MODModulo(以…为模) operatorNOT, !Negates valueNOT BETWEEN ... AND ...Check whether a value is not within a range of values!=, <>Not equal operatorNOT LIKENegation(否定) of simple pattern matchingNOT REGEXPNegation of REGEXP||, ORLogical OR+Addition operatorREGEXPPattern matching using regular expressions>>Right shiftRLIKESynonym(同义词) for REGEXPSOUNDS LIKECompare sounds*Multiplication(乘法) operator-Change the sign of the argumentXORLogical XOR

运算符优先级，同一行有相同的优先级

INTERVALBINARY, COLLATE!- (unary minus), ~ (unary bit inversion)^*, /, DIV, %, MOD-, +<<, >>&|= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, INBETWEEN, CASE, WHEN, THEN, ELSENOTAND, &&XOROR, ||= (assignment), :=

比较运算符

NameDescriptionBETWEEN ... AND ...Check whether a value is within a range of valuesCOALESCE()Return the first non-NULL argument=Equal operator<=>NULL-safe equal to operator>Greater than operator>=Greater than or equal operatorGREATEST()Return the largest argumentIN()Check whether a value is within a set of valuesINTERVAL()Return the index of the argument that is less than the first argumentISTest a value against a booleanIS NOTTest a value against a booleanIS NOT NULLNOT NULL value testIS NULLNULL value testISNULL()Test whether the argument is NULLLEAST()Return the smallest argument<Less than operator<=Less than or equal operatorLIKESimple pattern matchingNOT BETWEEN ... AND ...Check whether a value is not within a range of values!=, <>Not equal operatorNOT IN()Check whether a value is not within a set of valuesNOT LIKENegation(否定) of simple pattern matchingSTRCMP()Compare two strings

COALESCE 返回第一个非空值，

使用情景：如果列为空值，不想返回null可以使用函数返回想要的值

SELECT COALESCE(NULL,1);        -> 1

  GREATEST(value1,value2,...) 返回最大值

mysql> SELECT GREATEST(2,0);        -> 2mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);        -> 767.0mysql> SELECT GREATEST('B','A','C');        -> 'C'

INTERVAL(N,N1,N2,N3,...)   表示N值在列表中的位置

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);        -> 3mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);        -> 2mysql> SELECT INTERVAL(22, 23, 30, 44, 200);        -> 0

如果N值为NULL，返回-1

LEAST(value1,value2,...)  返回列表最小值

mysql> SELECT LEAST(2,0);        -> 0mysql> SELECT LEAST(34.0,3.0,5.0,767.0);        -> 3.0mysql> SELECT LEAST('B','A','C');        -> 'A'

XOR OR的反义

字符串函数

NameDescriptionASCII()Return numeric(数) value of left-most characterBIN()Return a string containing binary(二进制的) representation(代表) of a numberBIT_LENGTH()Return length of argument in bitsCHAR()Return the character for each integer(整数) passedCHAR_LENGTH()Return number of characters in argumentCHARACTER_LENGTH()Synonym(同义词) for CHAR_LENGTH()CONCAT()Return concatenated(连结) stringCONCAT_WS()Return concatenate with separator(分离器)ELT()Return string at index numberEXPORT_SET()Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off stringFIELD()Return the index (position) of the first argument in the subsequent(后来的) argumentsFIND_IN_SET()Return the index position of the first argument within the second argumentFORMAT()Return a number formatted(格式化) to specified(规定的) number of decimal(小数) placesFROM_BASE64()Decode(译码) to a base-64 string and return resultHEX()Return a hexadecimal(十六进制的) representation(代表) of a decimal or string valueINSERT()Insert a substring(子串) at the specified position up to the specified number of charactersINSTR()Return the index of the first occurrence(发生) of substring(子串)LCASE()Synonym(同义词) for LOWER()LEFT()Return the leftmost(最左边的) number of characters as specified(指定)LENGTH()Return the length of a string in bytesLIKESimple pattern matchingLOAD_FILE()Load the named fileLOCATE()Return the position of the first occurrence of substringLOWER()Return the argument in lowercase(小写字母)LPAD()Return the string argument, left-padded with the specified stringLTRIM()Remove leading spacesMAKE_SET()Return a set of comma-separated strings that have the corresponding bit in bits setMATCHPerform full-text searchMID()Return a substring(子串) starting from the specified(规定的) positionNOT LIKENegation(否定) of simple pattern matchingNOT REGEXPNegation of REGEXPOCT()Return a string containing octal(八进制的) representation(代表) of a numberOCTET_LENGTH()Synonym(同义词) for LENGTH()ORD()Return character code for leftmost(最左边的) character of the argumentPOSITION()Synonym for LOCATE()QUOTE()Escape the argument for use in an SQL statement(声明)REGEXPPattern matching using regular expressionsREPEAT()Repeat a string the specified(规定的) number of timesREPLACE()Replace occurrences(发生) of a specified stringREVERSE()Reverse(相反) the characters in a stringRIGHT()Return the specified rightmost number of charactersRLIKESynonym(同义词) for REGEXPRPAD()Append(附加) string the specified number of timesRTRIM()Remove trailing(后面的) spacesSOUNDEX()Return a soundex(探测法) stringSOUNDS LIKECompare soundsSPACE()Return a string of the specified(规定的) number of spacesSTRCMP()Compare two stringsSUBSTR()Return the substring(子串) as specifiedSUBSTRING()Return the substring as specifiedSUBSTRING_INDEX()Return a substring from a string before the specified number of occurrences(发生) of the delimiter(划界)TO_BASE64()Return the argument converted(转变) to a base-64 stringTRIM()Remove leading and trailing(追踪) spacesUCASE()Synonym(同义词) for UPPER()UNHEX()Return a string containing hex representation(代表) of a numberUPPER()Convert to uppercase(以大写字母印刷)WEIGHT_STRING()Return the weight string for a string