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'

运算符

Name	Description
AND, &&	Logical AND
=	Assign(分配) a value (as part of a SET statement(声明), or as part of the SET clause in an UPDATE statement)
:=	Assign a value
BETWEEN ... AND ...	Check whether a value is within a range of values
BINARY	Cast a string to a binary(二进制的) string
&	Bitwise AND
~	Bitwise(按位) inversion(倒置)
|	Bitwise OR
^	Bitwise XOR
CASE	Case operator
DIV	Integer(整数) division
/	Division operator
=	Equal operator
<=>	NULL-safe equal to operator
>	Greater than operator
>=	Greater than or equal operator
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL 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 operator
LIKE	Simple pattern matching
-	Minus operator
%, MOD	Modulo(以…为模) operator
NOT, !	Negates value
NOT BETWEEN ... AND ...	Check whether a value is not within a range of values
!=, <>	Not equal operator
NOT LIKE	Negation(否定) of simple pattern matching
NOT REGEXP	Negation of REGEXP
||, OR	Logical OR
+	Addition operator
REGEXP	Pattern matching using regular expressions
>>	Right shift
RLIKE	Synonym(同义词) for REGEXP
SOUNDS LIKE	Compare sounds
*	Multiplication(乘法) operator
-	Change the sign of the argument
XOR	Logical XOR

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

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

比较运算符

Name	Description
BETWEEN ... AND ...	Check whether a value is within a range of values
COALESCE()	Return the first non-NULL argument
=	Equal operator
<=>	NULL-safe equal to operator
>	Greater than operator
>=	Greater than or equal operator
GREATEST()	Return the largest argument
IN()	Check whether a value is within a set of values
INTERVAL()	Return the index of the argument that is less than the first argument
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL value test
ISNULL()	Test whether the argument is NULL
LEAST()	Return the smallest argument
<	Less than operator
<=	Less than or equal operator
LIKE	Simple pattern matching
NOT BETWEEN ... AND ...	Check whether a value is not within a range of values
!=, <>	Not equal operator
NOT IN()	Check whether a value is not within a set of values
NOT LIKE	Negation(否定) of simple pattern matching
STRCMP()	Compare two strings

COALESCE 返回第一个非空值，

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

SELECT COALESCE(NULL,1);
        -> 1

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

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

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

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

如果N值为NULL，返回-1

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

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

XOR OR的反义

字符串函数

Name	Description
ASCII()	Return numeric(数) value of left-most character
BIN()	Return a string containing binary(二进制的) representation(代表) of a number
BIT_LENGTH()	Return length of argument in bits
CHAR()	Return the character for each integer(整数) passed
CHAR_LENGTH()	Return number of characters in argument
CHARACTER_LENGTH()	Synonym(同义词) for CHAR_LENGTH()
CONCAT()	Return concatenated(连结) string
CONCAT_WS()	Return concatenate with separator(分离器)
ELT()	Return string at index number
EXPORT_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 string
FIELD()	Return the index (position) of the first argument in the subsequent(后来的) arguments
FIND_IN_SET()	Return the index position of the first argument within the second argument
FORMAT()	Return a number formatted(格式化) to specified(规定的) number of decimal(小数) places
FROM_BASE64()	Decode(译码) to a base-64 string and return result
HEX()	Return a hexadecimal(十六进制的) representation(代表) of a decimal or string value
INSERT()	Insert a substring(子串) at the specified position up to the specified number of characters
INSTR()	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 bytes
LIKE	Simple pattern matching
LOAD_FILE()	Load the named file
LOCATE()	Return the position of the first occurrence of substring
LOWER()	Return the argument in lowercase(小写字母)
LPAD()	Return the string argument, left-padded with the specified string
LTRIM()	Remove leading spaces
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH	Perform full-text search
MID()	Return a substring(子串) starting from the specified(规定的) position
NOT LIKE	Negation(否定) of simple pattern matching
NOT REGEXP	Negation of REGEXP
OCT()	Return a string containing octal(八进制的) representation(代表) of a number
OCTET_LENGTH()	Synonym(同义词) for LENGTH()
ORD()	Return character code for leftmost(最左边的) character of the argument
POSITION()	Synonym for LOCATE()
QUOTE()	Escape the argument for use in an SQL statement(声明)
REGEXP	Pattern matching using regular expressions
REPEAT()	Repeat a string the specified(规定的) number of times
REPLACE()	Replace occurrences(发生) of a specified string
REVERSE()	Reverse(相反) the characters in a string
RIGHT()	Return the specified rightmost number of characters
RLIKE	Synonym(同义词) for REGEXP
RPAD()	Append(附加) string the specified number of times
RTRIM()	Remove trailing(后面的) spaces
SOUNDEX()	Return a soundex(探测法) string
SOUNDS LIKE	Compare sounds
SPACE()	Return a string of the specified(规定的) number of spaces
STRCMP()	Compare two strings
SUBSTR()	Return the substring(子串) as specified
SUBSTRING()	Return the substring as specified
SUBSTRING_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 string
TRIM()	Remove leading and trailing(追踪) spaces
UCASE()	Synonym(同义词) for UPPER()
UNHEX()	Return a string containing hex representation(代表) of a number
UPPER()	Convert to uppercase(以大写字母印刷)
WEIGHT_STRING()	Return the weight string for a string