MySQL学习 - 单行函数（一）

数值函数

基本函数

函数	说明
`ABS(X)`	返回 `X` 的绝对值
`SIGN(X)`	返回 `X` 的符号值，负数返回 `-1`、`0` 返回 `0`、正数返回 `1`
`PI()`	返回常数 `π`
`CEIL(X)`、`CEILING(X)`	向上取整（天花板取整）
`FLOOR(X)`	向下取整（地取整）
`LEAST(X0,X1,...)`	在 `X0,X1...` 中取返回最小值
`GREATEST(X0,X1,...)`	在 `X0,X1...` 中取返回最大值
`MOD(X,Y)`	返回 `X` 除以 `Y` 的余数，符号与 `X` 相同
`RAND()`	返回 `0~1` 的随机数
`RAND(X)`	返回 `0~1` 的随机数，`X` 为种子数，相同时返回的随机数相同
`TRUNCATE(X,Y)`	直接返回截断指定位数 `Y` 的值
`ROUND(X)`	返回四舍五入的整数
`ROUND(X,Y)`	返回对 `X` 指定位数 `Y` 的四舍五入
`SQRT(X)`	返回平方根，负数返回 `NULL`

`ABS(X)`

mysql> SELECT
    -> ABS(-5) ABS_0,ABS(99) ABS_1
    -> FROM DUAL;
+-------+-------+
| ABS_0 | ABS_1 |
+-------+-------+
|     5 |    99 |
+-------+-------+
1 row in set (0.00 sec)

`SIGN(X)`

mysql> SELECT
    -> SIGN(-1) SIGN_0,SIGN(0) SIGN_1,SIGN(1) SIGN_2,SIGN(2) SIGN_3
    -> FROM DUAL;
+--------+--------+--------+--------+
| SIGN_0 | SIGN_1 | SIGN_2 | SIGN_3 |
+--------+--------+--------+--------+
|     -1 |      0 |      1 |      1 |
+--------+--------+--------+--------+
1 row in set (0.00 sec)

`PI()`

mysql> SELECT
    -> PI() PI
    -> FROM DUAL;
+----------+
| PI       |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

`CEIL(X)`、`CEILING(X)`

mysql> SELECT
    -> CEIL(123.789) CEIL_0,CEILING(-789.666) CEIL_1
    -> FROM DUAL;
+--------+--------+
| CEIL_0 | CEIL_1 |
+--------+--------+
|    124 |   -789 |
+--------+--------+
1 row in set (0.00 sec)

`FLOOR(X)`

mysql> SELECT
    -> FLOOR(123.789) FLOOR_0,FLOOR(-789.666) FLOOR_1
    -> FROM DUAL;
+---------+---------+
| FLOOR_0 | FLOOR_1 |
+---------+---------+
|     123 |    -790 |
+---------+---------+
1 row in set (0.00 sec)

`LEAST(X0,X1,...)`

mysql> SELECT
    -> LEAST(1,1.2,2,4,5) LEAST
    -> FROM DUAL;
+-------+
| LEAST |
+-------+
|   1.0 |
+-------+
1 row in set (0.00 sec)

`GREATEST(X0,X1,...)`

mysql> SELECT
    -> GREATEST(1,1.2,2,4,5) GREATEST
    -> FROM DUAL;
+----------+
| GREATEST |
+----------+
|      5.0 |
+----------+
1 row in set (0.00 sec)

`MOD(X,Y)`

mysql> SELECT
    -> MOD(12,5) MOD_0,MOD(-5,3) MOD_1,MOD(5,-3) MOD_2
    -> FROM DUAL;
+-------+-------+-------+
| MOD_0 | MOD_1 | MOD_2 |
+-------+-------+-------+
|     2 |    -2 |     2 |
+-------+-------+-------+
1 row in set (0.00 sec)

`RAND()`

mysql> SELECT
    -> RAND() RAND_0,RAND() RAND_1,RAND() RAND_2
    -> FROM DUAL;
+---------------------+--------------------+--------------------+
| RAND_0              | RAND_1             | RAND_2             |
+---------------------+--------------------+--------------------+
| 0.20196235478107105 | 0.7029358145808203 | 0.9087920392945334 |
+---------------------+--------------------+--------------------+
1 row in set (0.00 sec)

`RAND(X)`

mysql> SELECT
    -> RAND(111) RAND_0,RAND(111) RAND_1,RAND(.2) RAND_2 ,RAND(.2) RAND_3
    -> FROM DUAL;
+--------------------+--------------------+---------------------+---------------------+
| RAND_0             | RAND_1             | RAND_2              | RAND_3              |
+--------------------+--------------------+---------------------+---------------------+
| 0.9255455517447978 | 0.9255455517447978 | 0.15522042769493574 | 0.15522042769493574 |
+--------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

`TRUNCATE(X,Y)`

mysql> SELECT
    -> TRUNCATE(123.456,1) TRUNCATE_0,TRUNCATE(123.456,0) TRUNCATE_1,TRUNCATE(123.456,-1) TRUNCATE_2,TRUNCATE(125.456,-1) TRUNCATE_3
    -> FROM DUAL;
+------------+------------+------------+------------+
| TRUNCATE_0 | TRUNCATE_1 | TRUNCATE_2 | TRUNCATE_3 |
+------------+------------+------------+------------+
|      123.4 |        123 |        120 |        120 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)

`ROUND(X)`

mysql> SELECT
    -> ROUND(123.456) ROUND_0,ROUND(123.556) ROUND_1
    -> FROM DUAL;
+---------+---------+
| ROUND_0 | ROUND_1 |
+---------+---------+
|     123 |     124 |
+---------+---------+
1 row in set (0.00 sec)

`ROUND(X,Y)`

mysql> SELECT
    -> ROUND(123.456,1) ROUND_0,ROUND(123.456,0) ROUND_1,ROUND(123.456,-1) ROUND_2,ROUND(125.456,-1) ROUND_3
    -> FROM DUAL;
+---------+---------+---------+---------+
| ROUND_0 | ROUND_1 | ROUND_2 | ROUND_3 |
+---------+---------+---------+---------+
|   123.5 |     123 |     120 |     130 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

`SQRT(X)`

mysql> SELECT
    -> SQRT(4) SQRT_0,SQRT(3) SQRT_1,SQRT(-4) SQRT_2
    -> FROM DUAL;
+--------+--------------------+--------+
| SQRT_0 | SQRT_1             | SQRT_2 |
+--------+--------------------+--------+
|      2 | 1.7320508075688772 |   NULL |
+--------+--------------------+--------+
1 row in set (0.00 sec)

三角函数

前置知识：角度（`degrees`）和弧度（`radians`）

角度（degrees） 和 弧度（radians） 相互影响，旋转一周，角度从 0 到 360，弧度从 0 到 2π。

弧度 = 弧长 / 半径

1 Radian is about 57.2958

前置知识：正弦（`sin`）、余弦（`cos`）和正切（`tan`）

adjacent-opposite-hypotenuse

函数列表

在练习使用中，计算 SIN(PI()) 时，结果一直是大于 1 的一个值，按道理应该是 0，害我一直以为是我自己哪里理解错了，前前后后浪费了我半个小时。结论：浮点数时存在精度问题造成的。

函数	说明
`RADIANS(X)`	角度转弧度
`DEGREES(X)`	弧度转角度
`SIN(X)`	`X`为弧度值，返回正弦值，返回值范围在 `-1~1`
`ASIN(X)`	`X` 取值范围在 `-1~1`，返回反正弦值，超出返回 `NULL`
`COS(X)`	`X`为弧度值，返回余弦值，返回值范围在 `-1~1`
`ACOS(X)`	`X` 取值范围在 `-1~1`，返回反余弦值，超出返回 `NULL`
`TAN(X)`	`X`为弧度值，返回正切值
`ATAN(X)`	返回反正切值
`COT(X)`	返回反余切值
`TAN2(X,Y)`	返回两个参数的正切值

`RADIANS(X)`

mysql> SELECT
    -> RADIANS(360) RADIANS_0,RADIANS(360)/PI() RADIANS_1
    -> FROM DUAL;
+-------------------+-----------+
| RADIANS_0         | RADIANS_1 |
+-------------------+-----------+
| 6.283185307179586 |         2 |
+-------------------+-----------+
1 row in set (0.00 sec)

`DEGREES(X)`

mysql> SELECT
    -> DEGREES(2*PI()) DEGREES_0,DEGREES(1) DEGREES_1
    -> FROM DUAL;
+-----------+-------------------+
| DEGREES_0 | DEGREES_1         |
+-----------+-------------------+
|       360 | 57.29577951308232 |
+-----------+-------------------+
1 row in set (0.00 sec)

`SIN(X)`

mysql> SELECT
    -> SIN(1/2*PI()) SIN_0,SIN(0) SIN_1,SIN(PI()) SIN_2
    -> FROM DUAL;
+-------+-------+------------------------+
| SIN_0 | SIN_1 | SIN_2                  |
+-------+-------+------------------------+
|     1 |     0 | 1.2246467991473532e-16 |
+-------+-------+------------------------+
1 row in set (0.00 sec)

`ASIN(X)`

mysql> SELECT
    -> DEGREES(ASIN(-1)) ASIN_0,DEGREES(ASIN(0)) ASIN_1,DEGREES(ASIN(1)) ASIN_2,ASIN(2) ASIN_3
    -> FROM DUAL;
+--------+--------+--------+--------+
| ASIN_0 | ASIN_1 | ASIN_2 | ASIN_3 |
+--------+--------+--------+--------+
|    -90 |      0 |     90 |   NULL |
+--------+--------+--------+--------+
1 row in set (0.00 sec)

`COS(X)`

mysql> SELECT
    -> COS(RADIANS(0)) COS_0,
    -> COS(RADIANS(60)) COS_1,
    -> COS(RADIANS(90)) COS_2,
    -> COS(RADIANS(180)) COS_3
    -> FROM DUAL;
+-------+--------------------+-----------------------+-------+
| COS_0 | COS_1              | COS_2                 | COS_3 |
+-------+--------------------+-----------------------+-------+
|     1 | 0.5000000000000001 | 6.123233995736766e-17 |    -1 |
+-------+--------------------+-----------------------+-------+
1 row in set (0.00 sec)

`ACOS(X)`

mysql> SELECT
    -> DEGREES(ACOS(-1)) ACOS_1,
    -> DEGREES(ACOS(0)) ACOS_2,
    -> DEGREES(ACOS(.5)) ACOS_3,
    -> DEGREES(ACOS(1)) ACOS_4,
    -> ACOS(2) ACOS_5
    -> FROM DUAL;
+--------+--------+-------------------+--------+--------+
| ACOS_1 | ACOS_2 | ACOS_3            | ACOS_4 | ACOS_5 |
+--------+--------+-------------------+--------+--------+
|    180 |     90 | 60.00000000000001 |      0 |   NULL |
+--------+--------+-------------------+--------+--------+
1 row in set (0.00 sec)

`TAN(X)`

mysql> SELECT
    -> TAN(RADIANS(0)) TAN_0,
    -> TAN(RADIANS(45)) TAN_1,
    -> TAN(RADIANS(90)) TAN_2,
    -> TAN(RADIANS(180)) TAN_3
    -> FROM DUAL;
+-------+--------------------+----------------------+-------------------------+
| TAN_0 | TAN_1              | TAN_2                | TAN_3                   |
+-------+--------------------+----------------------+-------------------------+
|     0 | 0.9999999999999999 | 1.633123935319537e16 | -1.2246467991473532e-16 |
+-------+--------------------+----------------------+-------------------------+
1 row in set (0.00 sec)

`ATAN(X)`

SELECT
ATAN(1) ATAN_0,
ATAN(2) ATAN_1,
ATAN(0) ATAN_2,
ATAN(111) ATAN_3
FROM DUAL;

`COT(X)`

mysql> SELECT
    -> -- COT(RADIANS(0)) COT_0,
    -> COT(RADIANS(45)) COT_1,
    -> COT(RADIANS(90)) COT_2,
    -> COT(RADIANS(180)) COT_3
    -> FROM DUAL;
+--------------------+-----------------------+-----------------------+
| COT_1              | COT_2                 | COT_3                 |
+--------------------+-----------------------+-----------------------+
| 1.0000000000000002 | 6.123233995736766e-17 | -8.165619676597685e15 |
+--------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

指数和对数函数

函数	说明
`POW(X)` 和 `POWER(X)`	返回 `X` 的 `Y` 次方
`EXP(X)`	返回 `e` 的 `Y` 次方
`LN(X)` 和 `LOG(X)`	返回以 `e` 为底的 `X` 的对数，`X` <= 0 时，返回 `NULL`
`LOG(X,Y)`	返回以 `X` 为底的 `Y` 的对数，`Y` <= 0 时，返回 `NULL`
`LOG2(X)`	返回以 2 为底的 X 的对数，X <= 0 时，返回 `NULL`
`LOG10(X)`	返回以 2 为底的 X 的对数，X <= 0 时，返回 `NULL`

`POW(X,Y)` 和 `POWER(X,Y)`

mysql> SELECT
    -> POW(2,3) POW_0,
    -> POW(2,-1) POW_1,
    -> POWER(2,-3) POW_2,
    -> POWER(2,0) POW_3
    -> FROM DUAL;
+-------+-------+-------+-------+
| POW_0 | POW_1 | POW_2 | POW_3 |
+-------+-------+-------+-------+
|     8 |   0.5 | 0.125 |     1 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)

`EXP(X)`

mysql> SELECT
    -> EXP(1) EXP_0,
    -> EXP(2) EXP_1,
    -> EXP(0) EXP_2
    -> FROM DUAL;
+-------------------+------------------+-------+
| EXP_0             | EXP_1            | EXP_2 |
+-------------------+------------------+-------+
| 2.718281828459045 | 7.38905609893065 |     1 |
+-------------------+------------------+-------+
1 row in set (0.00 sec)

`LN(X)` 和 `LOG(X)`

mysql> SELECT
    -> LN(EXP(1)) LN_0,
    -> LN(0) LN_1,
    -> LN(10) LN_2,
    -> LN(-1) LN_3
    -> FROM DUAL;
+------+------+-------------------+------+
| LN_0 | LN_1 | LN_2              | LN_3 |
+------+------+-------------------+------+
|    1 | NULL | 2.302585092994046 | NULL |
+------+------+-------------------+------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT
    -> LOG(EXP(1)) LOG_0,
    -> LOG(0) LOG_1,
    -> LOG(10) LOG_2,
    -> LOG(-1) LOG_3
    -> FROM DUAL;
+-------+-------+-------------------+-------+
| LOG_0 | LOG_1 | LOG_2             | LOG_3 |
+-------+-------+-------------------+-------+
|     1 |  NULL | 2.302585092994046 |  NULL |
+-------+-------+-------------------+-------+
1 row in set, 2 warnings (0.00 sec)

`LOG(X,Y)`

mysql> SELECT
    -> LOG(1,1) LOG_0,
    -> LOG(2,4) LOG_1,
    -> LOG(2,1) LOG_2,
    -> LOG(4,-2) LOG_3
    -> FROM DUAL;
+-------+-------+-------+-------+
| LOG_0 | LOG_1 | LOG_2 | LOG_3 |
+-------+-------+-------+-------+
|  NULL |     2 |     0 |  NULL |
+-------+-------+-------+-------+
1 row in set, 2 warnings (0.00 sec)

`LOG2(X)`

同上。

`LOG10(X)`

同上。

进制转换函数

函数	说明
`BIN(X)`	十进制数 `X` 转为二进制
`HEX(X)`	十进制数 `X` 转为十六进制
`OCT(X)`	十进制数 `X` 转为八进制
`CONV(X,F1,F2)`	F1 进制数 `X` 转为 F2 进制

`BIN(X)`

mysql> SELECT
    -> BIN(10) BIN_0,
    -> BIN('C') BIN_1,
    -> BIN(12) BIN_2
    -> FROM DUAL;
+-------+-------+-------+
| BIN_0 | BIN_1 | BIN_2 |
+-------+-------+-------+
| 1010  | 0     | 1100  |
+-------+-------+-------+
1 row in set, 1 warning (0.00 sec)

`HEX(X)`

mysql> SELECT
    -> HEX(10) HEX_0,
    -> HEX('C') HEX_1,
    -> HEX(12) HEX_2
    -> FROM DUAL;
+-------+-------+-------+
| HEX_0 | HEX_1 | HEX_2 |
+-------+-------+-------+
| A     | 43    | C     |
+-------+-------+-------+
1 row in set (0.00 sec)

`OCT(X)`

mysql> SELECT
    -> OCT(10) OCT_0,
    -> OCT('C') OCT_1,
    -> OCT(12) OCT_2
    -> FROM DUAL;
+-------+-------+-------+
| OCT_0 | OCT_1 | OCT_2 |
+-------+-------+-------+
| 12    | 0     | 14    |
+-------+-------+-------+
1 row in set, 1 warning (0.00 sec)

`CONV(X,F1,F2)`

mysql> SELECT
    -> CONV(11,2,8) CONV_0,
    -> CONV(11,8,2) CONV_1,
    -> CONV(21,8,16) CONV_2
    -> FROM DUAL;
+--------+--------+--------+
| CONV_0 | CONV_1 | CONV_2 |
+--------+--------+--------+
| 3      | 1001   | 11     |
+--------+--------+--------+
1 row in set (0.00 sec)

字符串函数

`ASCII(str)`

获取第一个字节的 ASCII 码

SELECT ASCII('123'),ASCII('abc')
FROM DUAL;

+--------------+--------------+
| ASCII('123') | ASCII('abc') |
+--------------+--------------+
|           49 |           97 |
+--------------+--------------+
1 row in set (0.0006 sec)

`LENGTH(str)`

计算字符串的字节长度

SELECT LENGTH('ABC'),LENGTH('乌云ABC')
FROM DUAL;

+---------------+-------------------+
| LENGTH('ABC') | LENGTH('乌云ABC') |
+---------------+-------------------+
|             3 |                 9 |
+---------------+-------------------+
1 row in set (0.0007 sec)

中文字符使用 UTF-8 占三个字节，3*2+3=9

`CHAR_LENGTH(str)`

求字符串的字符长度

SELECT CHAR_LENGTH('ABC'),CHAR_LENGTH('乌云ABC')
FROM DUAL;

+--------------------+------------------------+
| CHAR_LENGTH('ABC') | CHAR_LENGTH('乌云ABC') |
+--------------------+------------------------+
|                  3 |                      5 |
+--------------------+------------------------+
1 row in set (0.0008 sec)

`CONCAT(str1,str2,...)`

拼接字符串

SELECT CONCAT('ABC','XYZ','HGNU')
FROM DUAL;

+----------------------------+
| CONCAT('ABC','XYZ','HGNU') |
+----------------------------+
| ABCXYZHGNU                 |
+----------------------------+
1 row in set (0.0005 sec)

`CONCAT_WS(sep,str1,str2,...)`

拼接字符串，并且使用 sep 分割

SELECT CONCAT_ws('-','ABC','XYZ','HGNU')
FROM DUAL;

+-----------------------------------+
| CONCAT_ws('-','ABC','XYZ','HGNU') |
+-----------------------------------+
| ABC-XYZ-HGNU                      |
+-----------------------------------+
1 row in set (0.0006 sec)

`INSERT(stt,start,len,insertStr)`

在第 start 个文字删除 len 个字符，并且插入字符串 insertStr，字符串的索引是从 1 开始的。

SELECT INSERT('aaawww.xxcheng.cn',1,6,'www')
FROM DUAL;

+---------------------------------------+
| INSERT('aaawww.xxcheng.cn',1,6,'www') |
+---------------------------------------+
| www.xxcheng.cn                        |
+---------------------------------------+
1 row in set (0.0006 sec)

SELECT INSERT('不再联系',2,2,'喜羊羊')
FROM DUAL;

+---------------------------------+
| INSERT('不再联系',2,2,'喜羊羊') |
+---------------------------------+
| 不喜羊羊系                      |
+---------------------------------+
1 row in set (0.0006 sec)

`REPLACE(str,oldStr,newStr)`

替换字符串

SELECT REPLACE('sss.xxcheng.cn sss.xxcheng.top','sss','www')
FROM DUAL;

+-------------------------------------------------------+
| REPLACE('sss.xxcheng.cn sss.xxcheng.top','sss','www') |
+-------------------------------------------------------+
| www.xxcheng.cn www.xxcheng.top                        |
+-------------------------------------------------------+
1 row in set (0.0006 sec)

`UPPER(str)`、`UCASE(str)`

转大写

SELECT UPPER('www.XXCHENG.cn'),UCASE('WWW.xxcheng.cn')
FROM DUAL;

+-------------------------+-------------------------+
| UPPER('www.XXCHENG.cn') | UCASE('WWW.xxcheng.cn') |
+-------------------------+-------------------------+
| WWW.XXCHENG.CN          | WWW.XXCHENG.CN          |
+-------------------------+-------------------------+
1 row in set (0.0006 sec)

`LOWER(str)`、`UCASE(str)`

转小写

SELECT LOWER('www.XXCHENG.cn'),LCASE('WWW.xxcheng.cn')
FROM DUAL;

+-------------------------+-------------------------+
| LOWER('www.XXCHENG.cn') | LCASE('WWW.xxcheng.cn') |
+-------------------------+-------------------------+
| www.xxcheng.cn          | www.xxcheng.cn          |
+-------------------------+-------------------------+
1 row in set (0.0006 sec)

`LEFT(str)`、`RIGHT(str)`

取字符串最左边、最右边的子字符串

SELECT LEFT('www.xxcheng.cn',3),RIGHT('www.xxcheng.cn',2)
FROM DUAL;

+--------------------------+---------------------------+
| LEFT('www.xxcheng.cn',3) | RIGHT('www.xxcheng.cn',2) |
+--------------------------+---------------------------+
| www                      | cn                        |
+--------------------------+---------------------------+
1 row in set (0.0007 sec)

`LPAD(str,len,padStr)`、`RPAD(str,len,padStr)`

字符串左对齐、右对齐，使用 padStr 字符串对齐符，字符串长度大于 lne 会被裁剪

SELECT
LPAD(salary,10,'*') pad_1,
RPAD(salary,10,'*') pad_2,
LPAD(salary,3,'*') pad_3,
RPAD(salary,3,'*') pad_4
FROM employees;

+------------+------------+-------+-------+
| pad_1      | pad_2      | pad_3 | pad_4 |
+------------+------------+-------+-------+
| **24000.00 | 24000.00** | 240   | 240   |
| **17000.00 | 17000.00** | 170   | 170   |
| **17000.00 | 17000.00** | 170   | 170   |
-- ... ... ... ...
| **10000.00 | 10000.00** | 100   | 100   |
| **12000.00 | 12000.00** | 120   | 120   |
| ***8300.00 | 8300.00*** | 830   | 830   |
+------------+------------+-------+-------+
107 rows in set (0.0013 sec)

`TRIM(str)`、`LTRIM(str)`、`RTRIM(str)`

去除字符串两边空格、左边空格、右边空格

SELECT
CONCAT('*',TRIM('  www.xxcheng.cn '),'*') trim_1,
CONCAT('*',LTRIM('  www.xxcheng.cn '),'*') trim_2,
CONCAT('*',RTRIM('  www.xxcheng.cn  '),'*') trim_3,
LENGTH(TRIM('  www.xxcheng.cn  ')) len_trim_1,
LENGTH(LTRIM('  www.xxcheng.cn  ')) len_trim_2,
LENGTH(RTRIM('  www.xxcheng.cn  ')) len_trim_3
FROM DUAL;

+------------------+-------------------+--------------------+------------+------------+------------+
| trim_1           | trim_2            | trim_3             | len_trim_1 | len_trim_2 | len_trim_3 |
+------------------+-------------------+--------------------+------------+------------+------------+
| *www.xxcheng.cn* | *www.xxcheng.cn * | *  www.xxcheng.cn* |         14 |         16 |         16 |
+------------------+-------------------+--------------------+------------+------------+------------+
1 row in set (0.0008 sec)

`TRIM(s1 FROM str)`、`TRIM(LEADING s1 FROM str)`、`TRIM(TRAILING s1 FROM str)`

去除两边、左边、右边指定的字符串

SELECT
TRIM('www' FROM 'www.xxcheng.www.cn.www') trim_1,
TRIM(LEADING 'www' FROM 'www.xxcheng.cn.www') trim_2,
TRIM(TRAILING 'www' FROM 'www.xxcheng.cn.www') trim_3
FROM DUAL;

+------------------+-----------------+-----------------+
| trim_1           | trim_2          | trim_3          |
+------------------+-----------------+-----------------+
| .xxcheng.www.cn. | .xxcheng.cn.www | www.xxcheng.cn. |
+------------------+-----------------+-----------------+
1 row in set (0.0008 sec)

`REPEAT(str,count)`

字符串重复 count 次返回

示例见下

`SPACE(count)`

返回指定个数空格

示例见下

`STRCMP(s1,s2)`

依次比较字符串每个字符的 ASCII，前者更大返回 1，后者更大返回 -1，一样大返回 0

SELECT
REPEAT('www',3) repeat_1,
CONCAT('*',SPACE(5),'*') space_1,
STRCMP('abc','abs') strcmp_1,
STRCMP('abc','abb') strcmp_2,
STRCMP('abc','abc') strcmp_3
FROM DUAL;

+-----------+---------+----------+----------+----------+
| repeat_1  | space_1 | strcmp_1 | strcmp_2 | strcmp_3 |
+-----------+---------+----------+----------+----------+
| wwwwwwwww | *     * |       -1 |        1 |        0 |
+-----------+---------+----------+----------+----------+
1 row in set (0.0009 sec)

`SUBSTR(str,index,len)`

获取指定 index 开始 len 的字符串字串

SELECT
SUBSTR('www.xxcheng.cn',5,7)
FROM DUAL;

+------------------------------+
| SUBSTR('www.xxcheng.cn',5,7) |
+------------------------------+
| xxcheng                      |
+------------------------------+
1 row in set (0.0005 sec)

`LOCATE(findStr,str)`

获取子串首次出现的序号，没有返回 0

SELECT
LOCATE('xxcheng','www.xxcheng.cn') locate_1,
LOCATE('aaa','www.xxcheng.cn') locate_2
FROM DUAL;

+----------+----------+
| locate_1 | locate_2 |
+----------+----------+
|        5 |        0 |
+----------+----------+
1 row in set (0.0006 sec)

`ELT(N,str1,str2,str3,...)`

返回字符串列表第 N 个位置的字符串

SELECT
ELT(3,'aa','bb','cc','dd')
FROM DUAL;

+----------------------------+
| ELT(3,'aa','bb','cc','dd') |
+----------------------------+
| cc                         |
+----------------------------+
1 row in set (0.0006 sec)

`FIELD(s,s1,s2,s3,...)`

获取字符串 s 在字符串列表首次出现的位置，未找到返回 0

SELECT
FIELD('cc','aa','bb','cc','dd') f1,
FIELD('zz','aa','bb','cc','dd') f2
FROM DUAL;

+----+----+
| f1 | f2 |
+----+----+
|  3 |  0 |
+----+----+
1 row in set (0.0006 sec)

`FIND_IN_SET(str,strlist)`

获取字符串 str 在字符串列表首次出现的位置，这里的字符串列表是一个字符串然后使用逗号 , 分割组成的列表

找不到返回0

SELECT
FIND_IN_SET('cc','aa,bb,cc,dd,') f1,
FIND_IN_SET('z','aa,bb,cc,dd,') f2
FROM DUAL;

+----+----+
| f1 | f2 |
+----+----+
|  3 |  0 |
+----+----+
1 row in set (0.0005 sec)

`REVERSE(str)`

反转字符串

SELECT
REVERSE('ABC哈哈哈')
FROM DUAL;

+----------------------+
| REVERSE('ABC哈哈哈') |
+----------------------+
| 哈哈哈CBA            |
+----------------------+
1 row in set (0.0007 sec)

`NULLIF(expr1,expr2)`

比较两个字符串是否相等，相等返回 NULL，不相等返回第一个字符串

SELECT
NULLIF('AA','AA') n1,
NULLIF('AA','BB') n2
FROM DUAL;

+------+----+
| n1   | n2 |
+------+----+
| NULL | AA |
+------+----+
1 row in set (0.0006 sec)

日期和时间函数

获取日期和时间

函数	用法
`CURDATE()`、`CURRENT_DATE()`	返回当前日期，只包含年、月、日
`CURTIME()`、`CURRENT_TIME()`	返回当前时间，只包含时、分、秒
`NOW()` /`SYSDATE()`/`CURRENT_TIMESTAMP()` / `LOCALTIME()` / `LOCALTIMESTAMP()`	返回当前系统日期和时间
`UTC_DATE()`	返回 UTC（世界标准时间）日期
`UTC_TIME()`	返回 UTC（世界标准时间）时间

+0 操作会按照对应格式，转为数值类型

1. 获取日期

CURDATE
CURRENT_DATE
UTF_DATE

SELECT
CURDATE() d1,
CURRENT_DATE() d2,
UTC_DATE() d3,
CURDATE()+0 d4,
CURRENT_DATE()+0 d5,
UTC_DATE()+0 d6
FROM DUAL;

+------------+------------+------------+----------+----------+----------+
| d1         | d2         | d3         | d4       | d5       | d6       |
+------------+------------+------------+----------+----------+----------+
| 2023-07-28 | 2023-07-28 | 2023-07-28 | 20230728 | 20230728 | 20230728 |
+------------+------------+------------+----------+----------+----------+
1 row in set (0.0006 sec)

2. 获取时间

CURTIME
CURRENT_TIME
UTC_TIME

SELECT
CURTIME() T1,
CURRENT_TIME() T2,
UTC_TIME() T3,
CURTIME()+0 T4,
CURRENT_TIME()+0 T5,
UTC_TIME()+0 T6
FROM DUAL;

+----------+----------+----------+--------+--------+--------+
| T1       | T2       | T3       | T4     | T5     | T6     |
+----------+----------+----------+--------+--------+--------+
| 20:15:26 | 20:15:26 | 12:15:26 | 201526 | 201526 | 121526 |
+----------+----------+----------+--------+--------+--------+
1 row in set (0.0007 sec)

3. 获取日期 + 时间 / 时间戳的函数

NOW
CURRENT_TIMESTAMP
SYSDATE

SELECT
NOW() N1,
SYSDATE() N2,
CURRENT_TIMESTAMP() N3,
NOW()+0 N4,
SYSDATE()+0 N5,
CURRENT_TIMESTAMP()+0 N6
FROM DUAL;

+---------------------+---------------------+---------------------+----------------+----------------+----------------+
| N1                  | N2                  | N3                  | N4             | N5             | N6             |
+---------------------+---------------------+---------------------+----------------+----------------+----------------+
| 2023-07-28 20:17:43 | 2023-07-28 20:17:43 | 2023-07-28 20:17:43 | 20230728201743 | 20230728201743 | 20230728201743 |
+---------------------+---------------------+---------------------+----------------+----------------+----------------+
1 row in set (0.0006 sec)

时间戳转换的函数

UNIX_TIMESTAMP()

获取当前时间 UNIX 形式的时间戳

UNIX_TIMESTAMP(date)

获取指定时间 UNIX 形式的时间戳
FROM_UNIXTIME(timestamp)

时间戳转时间

SELECT
UNIX_TIMESTAMP() T1,
UNIX_TIMESTAMP('2023-01-01 12:00:30') T2,
UNIX_TIMESTAMP('2023-01-01') T3,
FROM_UNIXTIME('1690542680') T4
FROM DUAL;

+------------+------------+------------+---------------------+
| T1         | T2         | T3         | T4                  |
+------------+------------+------------+---------------------+
| 1690543040 | 1672545630 | 1672502400 | 2023-07-28 19:11:20 |
+------------+------------+------------+---------------------+
1 row in set (0.0008 sec)

获取年、月、日、时、分、秒等具体某个值的函数

函数	用法
`YEAR(date)` / `MONTH(date)` / `DAY(date)`	返回具体的日期值
`HOUR(time)` / `MINUTE(time)` / `SECOND(time)`	返回具体的时间值
`MONTHNAME(date)`	返回月份：January，...
`DAYNAME(date)`	返回星期几：MONDAY，TUESDAY.....SUNDAY
`WEEKDAY(date)`	返回周几，注意，周1是0，周2是1，。。。周日是6
`QUARTER(date)`	返回日期对应的季度，范围为1～4
`WEEK(date)`， `WEEKOFYEAR(date)`	返回一年中的第几周
`DAYOFYEAR(date)`	返回日期是一年中的第几天
`DAYOFMONTH(date)`	返回日期位于所在月份的第几天
`DAYOFWEEK(date)`	返回周几，注意：周日是1，周一是2，。。。周六是7

SELECT
YEAR(CURDATE()) Y,
MONTH(CURDATE()) M1,
DAY(CURDATE()) D,
HOUR(CURTIME()) H,
MINUTE(CURTIME()) M2,
SECOND(CURTIME()) S,
WEEKDAY(CURDATE()) W,
QUARTER(CURDATE()) Q
FROM DUAL;

+------+----+----+----+----+----+---+---+
| Y    | M1 | D  | H  | M2 | S  | W | Q |
+------+----+----+----+----+----+---+---+
| 2023 |  7 | 28 | 19 | 38 | 35 | 4 | 3 |
+------+----+----+----+----+----+---+---+
1 row in set (0.0006 sec)

SELECT
MONTHNAME(CURDATE()) M1,
DAYNAME(CURDATE()) D1,
DAYOFYEAR(CURDATE()) D2,
DAYOFMONTH(CURDATE()) D3,
DAYOFWEEK(CURDATE()) D4,
WEEK(CURDATE()) W1,
WEEKOFYEAR(CURDATE()) W2
FROM DUAL;

+------+--------+-----+----+----+----+----+
| M1   | D1     | D2  | D3 | D4 | W1 | W2 |
+------+--------+-----+----+----+----+----+
| July | Friday | 209 | 28 |  6 | 30 | 30 |
+------+--------+-----+----+----+----+----+
1 row in set (0.0008 sec)

提取函数

EXTRACT(type FROM date)

返回指定日期中特定的部分，type 指定返回的值

SELECT
EXTRACT(YEAR FROM NOW()) "year",
EXTRACT(HOUR FROM NOW()) "hour"
FROM DUAL;

+------+------+
| year | hour |
+------+------+
| 2023 |   19 |
+------+------+
1 row in set (0.0006 sec)

时间和秒的转换的函数

SEC_TO_TIME(sec)
TIME_TO_SEC(time)

SELECT
TIME_TO_SEC('01:10:30') T1,
SEC_TO_TIME(1000) T2,
SEC_TO_TIME(100000)
FROM DUAL;

+------+----------+---------------------+
| T1   | T2       | SEC_TO_TIME(100000) |
+------+----------+---------------------+
| 4230 | 00:16:40 | 27:46:40            |
+------+----------+---------------------+
1 row in set (0.0006 sec)

(1*60+10)*60+30=4230

计算日期和时间的函数

获取指定时间间隔的日期

函数	用法
`DATE_ADD(datetime, INTERVAL expr type)`， `ADDDATE(date,INTERVAL expr type)`	返回与给定日期时间相差INTERVAL时间段的日期时间
`DATE_SUB(date,INTERVAL expr type)`， `SUBDATE(date,INTERVAL expr type)`	返回与date相差INTERVAL时间间隔的日期

type 取值类型

SELECT
DATE_ADD(CURDATE(),INTERVAL 2 YEAR) COL_1,
ADDDATE(CURRENT_DATE(),INTERVAL -2 MONTH) COL_2,
DATE_SUB(CURDATE(),INTERVAL 2 YEAR) COL_3,
SUBDATE(CURRENT_DATE(),INTERVAL -2 MONTH) COL_4,
DATE_ADD(NOW(),INTERVAL '1_2' DAY_HOUR) COL_5
FROM DUAL;

+------------+------------+------------+------------+---------------------+
| COL_1      | COL_2      | COL_3      | COL_4      | COL_5               |
+------------+------------+------------+------------+---------------------+
| 2025-07-28 | 2023-05-28 | 2021-07-28 | 2023-09-28 | 2023-07-29 22:03:07 |
+------------+------------+------------+------------+---------------------+
1 row in set (0.0007 sec)

计算时间差距

函数	用法
`ADDTIME(time1,time2)`	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
`SUBTIME(time1,time2)`	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
`DATEDIFF(date1,date2)`	返回date1 - date2的日期间隔天数
`TIMEDIFF(time1, time2)`	返回time1 - time2的时间间隔
`FROM_DAYS(N)`	返回从0000年1月1日起，N天以后的日期
`TO_DAYS(date)`	返回日期date距离0000年1月1日的天数
`LAST_DAY(date)`	返回date所在月份的最后一天的日期
`MAKEDATE(year,n)`	针对给定年份与所在年份中的天数返回一个日期
`MAKETIME(hour,minute,second)`	将给定的小时、分钟和秒组合成时间并返回
`PERIOD_ADD(time,n)`	返回time加上n后的时间

SELECT
ADDTIME(NOW(),60*60) COL_1,
ADDTIME(NOW(),'12:30:50') COL_2
FROM DUAL;

+---------------------+---------------------+
| COL_1               | COL_2               |
+---------------------+---------------------+
| 2023-07-28 20:56:37 | 2023-07-29 08:51:27 |
+---------------------+---------------------+
1 row in set (0.0006 sec)

日期和时间的格式化

DATE_FORMAT(date,fmt)

根据 fmt 输出指定格式日期字符串
TIME_FORMAT(date,fmt)

根据 fmt 输出指定格式时间字符串
GET_FORMAT(date_type,format_type)

获取格式标准化格式
STR_TO_DATE(str,fmt)

字符串转时间

SELECT
DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),
TIME_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
FROM DUAL;

+----------------------------------------+----------------------------------------+
| DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') | TIME_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+----------------------------------------+
| 2023-07-28 20:40:30                    | 0000-00-00 20:40:30                    |
+----------------------------------------+----------------------------------------+
1 row in set (0.0008 sec)

SELECT
GET_FORMAT(DATETIME, 'USA')
FROM DUAL;

+-----------------------------+
| GET_FORMAT(DATETIME, 'USA') |
+-----------------------------+
| %Y-%m-%d %H.%i.%s           |
+-----------------------------+
1 row in set (0.0005 sec)

SELECT
STR_TO_DATE('2023-01-01 13:20:18','%Y-%m-%d %H:%i:%s') "time"
FROM DUAL;

+---------------------+
| time                |
+---------------------+
| 2023-01-01 13:20:18 |
+---------------------+
1 row in set (0.0007 sec)

常用时间格式符

格式符	作用
`%Y`	四位的年份
`%y`	两位的年份
`%m`	月份(01 - 12)
`%c`	月份(1 - 12)
`%d`	日(01, 02, …)
`%H`	小时(24小时制)
`%h`	小时(12小时制)
`%i`	分钟(00 - 59)
`%s`	秒(00 - 59)
`%U`	星期(0 - 52)，星期天为第一天
`%u`	星期(0 - 52)，星期一为第一天
`%W`	星期英文(Sunday - Saturday)
`%M`	月份英文(January - December)

参考链接

文章目录

MySQL学习 - 单行函数（一）

数值函数

基本函数

ABS(X)

SIGN(X)

PI()

CEIL(X)、CEILING(X)

FLOOR(X)

LEAST(X0,X1,...)

GREATEST(X0,X1,...)

MOD(X,Y)

RAND()

RAND(X)

TRUNCATE(X,Y)

ROUND(X)

ROUND(X,Y)

SQRT(X)

三角函数

前置知识：角度（degrees）和弧度（radians）

前置知识：正弦（sin）、余弦（cos）和正切（tan）

函数列表

RADIANS(X)

DEGREES(X)

SIN(X)

ASIN(X)

COS(X)

ACOS(X)

TAN(X)

ATAN(X)

COT(X)

指数和对数函数

POW(X,Y) 和 POWER(X,Y)

EXP(X)

LN(X) 和 LOG(X)

LOG(X,Y)

LOG2(X)

LOG10(X)