sql函数简记

sql函数优先级

操作符	优先级
:=	1
｜｜, OR, XOR	2
&&, AND	3
NOT	4
BETWEEN, CASE, WHEN, THEN, ELSE	5
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN	6
\		7
&	8
<<, >>	9
-, +	10
*, /, DIV, %, MOD	11
^	12
- (一元减号), ~ (一元比特反转)	12
!	13
BINARY, COLLATE	14

注释：假如 HIGH_NOT_PRECEDENCE SQL 模式被激活，则 NOT 的优先级同 the ! 操作符相同。

比较运算

比较运算产生的结果为1(TRUE)、0 (FALSE)或 NULL。这些运算可用于数字和字符串。根据需要，字符串可自动转换为数字，而数字也可自动转换为字符串。

控制流程函数

CASE 1 WHEN 1 THEN 2

mysql>  SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| two                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

IF(expr1,expr2,expr3)
expr1 是TRUE,返回值为expr2; 否则返回expr3。

mysql> SELECT IF(1,2,3);
+-----------+
| IF(1,2,3) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT IF(0,2,3);
+-----------+
| IF(0,2,3) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

IFNULL(expr1,expr2)
expr1 不为 NULL，返回 expr1; 否则返回 expr2。

mysql> SELECT IFNULL(2,3);
+-------------+
| IFNULL(1,2) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(0,3);
+-------------+
| IFNULL(0,2) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

NULLIF(expr1,expr2)
expr1 = expr2 ,返回NULL，否则返回 expr1。

mysql> SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

ASCII(str)
返回值为字符串str 的最左字符的数值。
假如str为空字符串，则返回值为 0 。
假如str 为NULL，则返回值为 NULL。
ASCII()用于带有从 0到255的数值的字符。

mysql> SELECT ASCII("a");
+------------+
| ASCII("a") |
+------------+
|         97 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ASCII("ab");
+-------------+
| ASCII("ab") |
+-------------+
|          97 |
+-------------+
1 row in set (0.00 sec)

ORD(str) 同上

BIN(N)
返回值为N的二进制值的字符串表示。

mysql> SELECT BIN(2);
+--------+
| BIN(2) |
+--------+
| 10     |
+--------+
1 row in set (0.00 sec)

BIT_LENGTH(str)
返回值为二进制的字符串str 长度。

mysql> SELECT BIT_LENGTH(2);
+---------------+
| BIT_LENGTH(2) |
+---------------+
|             8 |
+---------------+
1 row in set (0.00 sec)

CHAR(N,… [USING charset])
CHAR()将每个参数N理解为一个整数，其返回值为一个包含这些整数的代码值所给出的字符的字符串。

mysql> SELECT CHAR(77,121,83,81,'76');
+-------------------------+
| CHAR(77,121,83,81,'76') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

大于 255的CHAR()参数被转换为多结果字符。

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100           | 0100           |
+----------------+----------------+
1 row in set (0.00 sec)

CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

CHARACTER_LENGTH(str)
CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。

COMPRESS(string_to_compress)
压缩一个字符串。这个函数要求 MySQL已经用一个诸如zlib的压缩库压缩过。否则，返回值始终是NULL。UNCOMPRESS() 可将压缩过的字符串进行解压缩。

CONCAT(str1,str2,…)返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

mysql> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

CONCAT_WS(separator,str1,str2,…)
第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。

mysql> SELECT CONCAT_WS(',','a','b','c');
+----------------------------+
| CONCAT_WS(',','a','b','c') |
+----------------------------+
| a,b,c                      |
+----------------------------+
1 row in set (0.00 sec)

CONV(N,from_base,to_base)
不同数基间转换数字。

mysql> SELECT CONV(2,10,2);
+--------------+
| CONV(2,10,2) |
+--------------+
| 10           |
+--------------+
1 row in set (0.00 sec)

ELT(N,str1,str2,str3,…)若N = 1，则返回值为 str1 ，若N = 2，则返回值为 str2。

mysql> SELECT ELT(3, '1', '2', '3', '4');
+----------------------------+
| ELT(3, '1', '2', '3', '4') |
+----------------------------+
| 3                          |
+----------------------------+
1 row in set (0.00 sec)

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
比特操作字符串

FIELD(str,str1,str2,str3,…)
返回值为str1, str2, str3,……列表中的str的位置。

HEX(N_or_S)如果N_OR_S 是一个数字，则返回一个十六进制值 N 的字符串表示，在这里， N 是一个longlong (BIGINT)数。这相当于 CONV(N,10,16)。如果N_OR_S 是一个字符串，则返回值为一个N_OR_S的十六进制字符串表示

INSERT(str,pos,len,newstr)返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr取代的len 字符。如果pos 超过字符串长度，则返回值为原始字符串。假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

mysql> SELECT INSERT('123456789', 3, 4, '00');
+---------------------------------+
| INSERT('123456789', 3, 4, '00') |
+---------------------------------+
| 1200789                         |
+---------------------------------+
1 row in set (0.00 sec)

INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置。

mysql> SELECT INSTR('123456', '56');
+-----------------------+
| INSTR('123456', '56') |
+-----------------------+
|                     5 |
+-----------------------+
1 row in set (0.00 sec)

LCASE(str)LCASE() 是 LOWER()的同义词。

LEFT(str,len)返回从字符串str 开始的len 最左字符。

mysql> SELECT LEFT('123456789', 5);
+----------------------+
| LEFT('123456789', 5) |
+----------------------+
| 12345                |
+----------------------+
1 row in set (0.00 sec)

LENGTH(str)返回值为字符串str 的长度，单位为字节。一个多字节字符算作多字节。

mysql> SELECT LENGTH('12345');
+-----------------+
| LENGTH('12345') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('你好');
+----------------+
| LENGTH('你好') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

LOAD_FILE(file_name)读取文件并将这一文件按照字符串的格式返回。

文件的位置必须在服务器上,
你必须为文件制定路径全名，
而且你还必须拥有FILE 特许权。
文件必须可读取，
文件容量必须小于 max_allowed_packet字节。
若文件不存在，或因不满足上述条件而不能被读取，则函数返回值为 NULL。

LOCATE(substr,str) , LOCATE(substr,str,pos)第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如若substr 不在str中，则返回值为0。

mysql> SELECT LOCATE('456', '123456456');
+----------------------------+
| LOCATE('456', '123456456') |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT LOCATE('456', '123456456', 5);
+-------------------------------+
| LOCATE('456', '123456456', 5) |
+-------------------------------+
|                             7 |
+-------------------------------+
1 row in set (0.00 sec)

LOWER(str)返回字符串 str 以及所有根据最新的字符集映射表变为小写字母的字符

LPAD(str,len,padstr)返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

mysql> SELECT LPAD('123',4,'0');
+-------------------+
| LPAD('123',4,'0') |
+-------------------+
| 0123              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT LPAD('123',2,'0');
+-------------------+
| LPAD('123',2,'0') |
+-------------------+
| 12                |
+-------------------+
1 row in set (0.00 sec)

LTRIM(str)返回字符串 str ，其引导空格字符被删除。

mysql> SELECT LTRIM('  0 0  ');
+------------------+
| LTRIM('  0 0  ') |
+------------------+
| 0 0              |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(LTRIM('  0 0  '));
+--------------------------+
| LENGTH(LTRIM('  0 0  ')) |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)

MAKE_SET(bits,str1,str2,…)返回一个设定值 (一个包含被‘,’号分开的字字符串的字符串) ，由在bits 组中具有相应的比特的字符串组成。str1 对应比特 0, str2 对应比特1,以此类推。str1, str2, …中的 NULL值不会被添加到结果中。

MID(str,pos,len)MID(str,pos,len) 是 SUBSTRING(str,pos,len)的同义词。

OCT(N)返回一个 N的八进制值的字符串表示，其中 N 是一个longlong (BIGINT)数。这等同于CONV(N,10,8)。若N 为 NULL ，则返回值为NULL。

mysql> SELECT OCT(8);
+--------+
| OCT(8) |
+--------+
| 10     |
+--------+
1 row in set (0.00 sec)

OCTET_LENGTH(str)OCTET_LENGTH() 是 LENGTH()的同义词。

ORD(str)若字符串str 的最左字符是一个多字节字符，则返回该字符的代码，代码的计算通过使用以下公式计算其组成字节的数值而得出:(1st byte code)(2nd byte code × 256)(3rd byte code × 2562) …假如最左字符不是一个多字节字符，那么 ORD()和函数ASCII()返回相同的值。

POSITION(substr IN str)POSITION(substr IN str)是 LOCATE(substr,str)同义词。

QUOTE(str)引证一个字符串，由此产生一个在SQL语句中可用作完全转义数据值的结果。返回的字符串由单引号标注，每例都带有单引号 (‘’’)、反斜线符号 (‘\’)、 ASCII NUL以及前面有反斜线符号的Control-Z 。如果自变量的值为NULL, 则返回不带单引号的单词 “NULL”。

REPEAT(str,count)返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。

mysql> SELECT REPEAT('123', 3);
+------------------+
| REPEAT('123', 3) |
+------------------+
| 123123123        |
+------------------+
1 row in set (0.00 sec)

REPLACE(str,from_str,to_str)返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

mysql> SELECT REPLACE('101010101', '0', '2');
+--------------------------------+
| REPLACE('101010101', '0', '2') |
+--------------------------------+
| 121212121                      |
+--------------------------------+
1 row in set (0.00 sec)

REVERSE(str)返回字符串 str ，顺序和字符顺序相反。

mysql> SELECT REVERSE('12345');
+------------------+
| REVERSE('12345') |
+------------------+
| 54321            |
+------------------+
1 row in set (0.00 sec)

RIGHT(str,len)从字符串str 开始，返回最右len 字符。

mysql> SELECT RIGHT('12345678', 4);
+----------------------+
| RIGHT('12345678', 4) |
+----------------------+
| 5678                 |
+----------------------+
1 row in set (0.00 sec)

RPAD(str,len,padstr)返回字符串str, 其右边被字符串 padstr填补至len 字符长度。假如字符串str 的长度大于 len,则返回值被缩短到与 len 字符相同长度。

mysql> SELECT RPAD('123',5,'0');
+-------------------+
| RPAD('123',5,'0') |
+-------------------+
| 12300             |
+-------------------+
1 row in set (0.00 sec)

RTRIM(str)返回字符串 str ，结尾空格字符被删去。

mysql> SELECT RTRIM('123   ');
+-----------------+
| RTRIM('123   ') |
+-----------------+
| 123             |
+-----------------+
1 row in set (0.00 sec)

SOUNDEX(str)从str返回一个soundex字符串。两个具有几乎同样探测的字符串应该具有同样的 soundex 字符串。一个标准的soundex 字符串的长度为4个字符，然而SOUNDEX() 函数会返回一个人以长度的字符串。可使用结果中的SUBSTRING() 来得到一个标准 soundex 字符串。在str中，会忽略所有未按照字母顺序排列的字符。所有不在A-Z范围之内的国际字母符号被视为元音字母。

mysql> SELECT SOUNDEX('Hello');
  -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
  -> 'Q36324'

expr1 SOUNDS LIKE expr2这相当于SOUNDEX(expr1) = SOUNDEX(expr2)。

SPACE(N)返回一个由N 间隔符号组成的字符串。
两个空格

mysql> SELECT SPACE(2);
+----------+
| SPACE(2) |
+----------+
|          |
+----------+
1 row in set (0.00 sec)

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

mysql> SELECT SUBSTRING('Quadratically',5);
  -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
  -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
  -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
  -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
  -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
  -> 'ki'

SUBSTR()是 SUBSTRING()的同义词。

SUBSTRING_INDEX(str,delim,count)在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边的一切内容。

mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', 2) |
+------------------------------------------+
| www.baidu                                |
+------------------------------------------+
1 row in set (0.00 sec)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)返回字符串 str ，其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

UCASE(str)UCASE()是UPPER()的同义词。

UNCOMPRESS(string_to_uncompress)对经COMPRESS()函数压缩后的字符串进行解压缩。若参数为压缩值，则结果为 NULL。这个函数要求 MySQL 已被诸如zlib 之类的压缩库编译过。否则, 返回值将始终是 NULL。

UNCOMPRESSED_LENGTH(compressed_string)返回压缩字符串压缩前的长度。

UNHEX(str)执行从HEX(str)的反向操作。就是说，它将参数中的每一对十六进制数字理解为一个数字，并将其转化为该数字代表的字符。结果字符以二进制字符串的形式返回。

mysql> select hex("asdf");
+-------------+
| hex("asdf") |
+-------------+
| 61736466    |
+-------------+
1 row in set (0.00 sec)

mysql> select unhex("61736466");
+-------------------+
| unhex("61736466") |
+-------------------+
| asdf              |
+-------------------+
1 row in set (0.00 sec)

UPPER(str)返回字符串str，以及根据最新字符集映射转化为大写字母的字符

STRCMP(expr1,expr2)若所有的字符串均相同，则返回STRCMP()，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。

mysql> SELECT STRCMP('123456', '12345');
+---------------------------+
| STRCMP('123456', '12345') |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT STRCMP('123456', '123456');
+----------------------------+
| STRCMP('123456', '123456') |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT STRCMP('12345', '123456');
+---------------------------+
| STRCMP('12345', '123456') |
+---------------------------+
|                        -1 |
+---------------------------+
1 row in set (0.00 sec)

被零除的结果为 NULL：

mysql> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)