单行函数

# 数值函数

# 基本函数

函数	说明
`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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `PI()`

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

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# 三角函数

# 前置知识：角度（`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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9

# `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)

1
2
3
4
5
6
7
8
9
10
11
12

# `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)

1
2
3
4
5
6
7
8
9
10
11
12
13

# `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)

1
2
3
4
5
6
7
8
9
10
11
12

# `ATAN(X)`

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

1
2
3
4
5
6

# `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)

1
2
3
4
5
6
7
8
9
10
11
12

# 指数和对数函数

函数	说明
`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)

1
2
3
4
5
6
7
8
9
10
11
12

# `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)

1
2
3
4
5
6
7
8
9
10
11

# `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)

1
2
3
4
5
6
7
8
9
10
11
12

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)

1
2
3
4
5
6
7
8
9
10
11
12

# `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)

1
2
3
4
5
6
7
8
9
10
11
12

# `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)

1
2
3
4
5
6
7
8
9
10
11

# `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)

1
2
3
4
5
6
7
8
9
10
11

# `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)

1
2
3
4
5
6
7
8
9
10
11

# `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)

1
2
3
4
5
6
7
8
9
10
11

# 字符串函数

# `ASCII(str)`

获取第一个字节的 ASCII 码

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

1
2

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

1
2
3
4
5
6

# `LENGTH(str)`

计算字符串的字节长度

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

1
2

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

1
2
3
4
5
6

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

# `CHAR_LENGTH(str)`

求字符串的字符长度

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

1
2

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

1
2
3
4
5
6

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

拼接字符串

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

1
2

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

1
2
3
4
5
6

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

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

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

1
2

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

1
2
3
4
5
6

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

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

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

1
2

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

1
2
3
4
5
6

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

1
2

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

1
2
3
4
5
6

# `REPLACE(str,oldStr,newStr)`

替换字符串

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

1
2

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

1
2
3
4
5
6

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

转大写

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

1
2

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

1
2
3
4
5
6

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

转小写

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

1
2

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

1
2
3
4
5
6

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

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

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

1
2

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

1
2
3
4
5
6

# `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;

1
2
3
4
5
6

+------------+------------+-------+-------+
| 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)

1
2
3
4
5
6
7
8
9
10
11
12

# `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;

1
2
3
4
5
6
7
8

+------------------+-------------------+--------------------+------------+------------+------------+
| 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)

1
2
3
4
5
6

# `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;

1
2
3
4
5

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

1
2
3
4
5
6

# `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;

1
2
3
4
5
6
7

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

1
2
3
4
5
6

# `SUBSTR(str,index,len)`

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

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

1
2
3

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

1
2
3
4
5
6

# `LOCATE(findStr,str)`

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

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

1
2
3
4

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

1
2
3
4
5
6

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

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

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

1
2
3

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

1
2
3
4
5
6

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

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

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

1
2
3
4

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

1
2
3
4
5
6

# `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;

1
2
3
4

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

1
2
3
4
5
6

# `REVERSE(str)`

反转字符串

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

1
2
3

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

1
2
3
4
5
6

# `NULLIF(expr1,expr2)`

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

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

1
2
3
4

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

1
2
3
4
5
6

# 日期和时间函数

# 获取日期和时间

函数	用法
`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;

1
2
3
4
5
6
7
8

+------------+------------+------------+----------+----------+----------+
| 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)

1
2
3
4
5
6

# 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;

1
2
3
4
5
6
7
8

+----------+----------+----------+--------+--------+--------+
| 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)

1
2
3
4
5
6

# 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;

1
2
3
4
5
6
7
8

+---------------------+---------------------+---------------------+----------------+----------------+----------------+
| 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)

1
2
3
4
5
6

# 时间戳转换的函数

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;

1
2
3
4
5
6

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

1
2
3
4
5
6

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

函数	用法
`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;

1
2
3
4
5
6
7
8
9
10

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

1
2
3
4
5
6

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

1
2
3
4
5
6
7
8
9

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

1
2
3
4
5
6

# 提取函数

EXTRACT(type FROM date)

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

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

1
2
3
4

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

1
2
3
4
5
6

# 时间和秒的转换的函数

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;

1
2
3
4
5

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

1
2
3
4
5
6

(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;

1
2
3
4
5
6
7

+------------+------------+------------+------------+---------------------+
| 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)

1
2
3
4
5
6

# 计算时间差距

函数	用法
`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;

1
2
3
4

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

1
2
3
4
5
6

# 日期和时间的格式化

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;

1
2
3
4

+----------------------------------------+----------------------------------------+
| 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)

1
2
3
4
5
6

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

1
2
3

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

1
2
3
4
5
6

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

1
2
3

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

1
2
3
4
5
6

常用时间格式符

格式符	作用
`%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)

# 流程控制函数

函数	用法
`IF(value,value1,value2)`	如果value的值为TRUE，返回value1，否则返回value2
`IFNULL(value1, value2)`	如果value1不为NULL，返回value1，否则返回value2
`CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END`	相当于Java的if...else if...else...
`CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 .... [ELSE 值n] END`	相当于Java的switch...case...

# `IF`

SELECT
IF('A'>'B','A大','B大');

1
2

+-------------------------+
| IF('A'>'B','A大','B大') |
+-------------------------+
| B大                     |
+-------------------------+
1 row in set (0.0006 sec)

1
2
3
4
5
6

# `IFNULL`

SELECT
IFNULL(NULL,'CCC'),
IFNULL('AAA','CCC');

1
2
3

+--------------------+---------------------+
| IFNULL(NULL,'CCC') | IFNULL('AAA','CCC') |
+--------------------+---------------------+
| CCC                | AAA                 |
+--------------------+---------------------+
1 row in set (0.0006 sec)

1
2
3
4
5
6

# `CASE WHEN expr1 THEN statment1 ... END`

SELECT
last_name,department_id,
CASE WHEN salary>10000 THEN '高工资'
WHEN salary >=8000 THEN '较高工资'
ELSE '其他'
END
"level"
FROM employees
WHERE department_id IN(60,90,100);

1
2
3
4
5
6
7
8
9

+-----------+---------------+----------+
| last_name | department_id | level    |
+-----------+---------------+----------+
| Hunold    |            60 | 较高工资 |
| Ernst     |            60 | 其他     |
| Austin    |            60 | 其他     |
| Pataballa |            60 | 其他     |
| Lorentz   |            60 | 其他     |
| King      |            90 | 高工资   |
| Kochhar   |            90 | 高工资   |
| De Haan   |            90 | 高工资   |
| Greenberg |           100 | 高工资   |
| Faviet    |           100 | 较高工资 |
| Chen      |           100 | 较高工资 |
| Sciarra   |           100 | 其他     |
| Urman     |           100 | 其他     |
| Popp      |           100 | 其他     |
+-----------+---------------+----------+
14 rows in set (0.0015 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# `CASE variable WHEN value1 THEN statment1 ... END`

SELECT
last_name,department_id,
CASE department_id WHEN 60 THEN '60号部门'
WHEN 90 THEN '90号部门'
ELSE '其他部门'
END
"department_name"
FROM employees
WHERE department_id IN(60,90,100);

1
2
3
4
5
6
7
8
9

+-----------+---------------+-----------------+
| last_name | department_id | department_name |
+-----------+---------------+-----------------+
| Hunold    |            60 | 60号部门        |
| Ernst     |            60 | 60号部门        |
| Austin    |            60 | 60号部门        |
| Pataballa |            60 | 60号部门        |
| Lorentz   |            60 | 60号部门        |
| King      |            90 | 90号部门        |
| Kochhar   |            90 | 90号部门        |
| De Haan   |            90 | 90号部门        |
| Greenberg |           100 | 其他部门        |
| Faviet    |           100 | 其他部门        |
| Chen      |           100 | 其他部门        |
| Sciarra   |           100 | 其他部门        |
| Urman     |           100 | 其他部门        |
| Popp      |           100 | 其他部门        |
+-----------+---------------+-----------------+
14 rows in set (0.0010 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 加密与解密函数

函数	用法
`PASSWORD(str)`	返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密，8.0被弃用
`MD5(str)`	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
`SHA(str)`	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全。
`ENCODE(value,password_seed)`	返回使用password_seed作为加密密码加密value，8.0被弃用
`DECODE(value,password_seed)`	返回使用password_seed作为加密密码解密value，8.0被弃用

下面 SQL 在 5.7 版本下运行

SELECT
PASSWORD('ABC'),
MD5('ABC'),
SHA('ABC');

1
2
3
4

+-------------------------------------------+----------------------------------+------------------------------------------+
| PASSWORD('ABC')                           | MD5('ABC')                       | SHA('ABC')                               |
+-------------------------------------------+----------------------------------+------------------------------------------+
| *71B101096C51D03995285042443F5C44D59C8A31 | 902fbdd2b1df0c4f70b4a5d23525e932 | 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8 |
+-------------------------------------------+----------------------------------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6

SELECT
ENCODE('www.xxcheng.cn','ABC') a,
ENCODE('www.xxcheng.cn','XYZ') b;

1
2
3

+----------------+----------------+
| a              | b              |
+----------------+----------------+
| E5^w蹏噓 | Q)漏龢?椅3雒 |
+----------------+----------------+
1 row in set, 2 warnings (0.00 sec)

1
2
3
4
5
6

这是在 8.0 Shell 下连接 5.7版本数据库的结果

+--------------------------------+--------------------------------+
| a                              | b                              |
+--------------------------------+--------------------------------+
| 0x1345355E771211181ADB8787758F | 0x512917C2A9FD98FEBFD2CE33F6C3 |
+--------------------------------+--------------------------------+
1 row in set, 2 warnings (0.00 sec)

1
2
3
4
5
6

SELECT
DECODE(ENCODE('www.xxcheng.cn','ABC'),'ABC') a,
DECODE(ENCODE('www.xxcheng.cn','XYZ'),'XYZ') b;

1
2
3

+----------------+----------------+
| a              | b              |
+----------------+----------------+
| www.xxcheng.cn | www.xxcheng.cn |
+----------------+----------------+
1 row in set, 4 warnings (0.00 sec)

1
2
3
4
5
6

在练习的时候有一个小插曲，因为有几个函数不支持 8.0 版本的，所以我就直接去虚拟机里面测试 5.7 版本，但是在测试 ENCODE 和 DECODE 时，一直不能相互转换，后面发现是使用 8.0版本的 Shell 连接 5.7 版本数据库导致的。

# 信息查询函数

函数	说明
`VERSION()`	返回当前MySQL的版本号
`CONNECTION_ID()`	返回当前MySQL服务器的连接数
`DATABASE()`，`SCHEMA()`	返回MySQL命令行当前所在的数据库
`USER()`、`CURRENT_USER()`、`SYSTEM_USER()`、`SESSION_USER()`	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
`CHARSET(value)`	返回字符串value自变量的字符集
`COLLATION(value)`	返回字符串value的比较规则

SELECT
VERSION(),
CONNECTION_ID(),
DATABASE(),
USER();

1
2
3
4
5

+-----------+-----------------+------------+----------------------+
| VERSION() | CONNECTION_ID() | DATABASE() | USER()               |
+-----------+-----------------+------------+----------------------+
| 8.0.33    |              55 | atguigudb  | root@DESKTOP-I4UTEH6 |
+-----------+-----------------+------------+----------------------+
1 row in set (0.0007 sec)

1
2
3
4
5
6

SELECT
CHARSET('ABC'),
COLLATION('ABC');

1
2
3

+----------------+--------------------+
| CHARSET('ABC') | COLLATION('ABC')   |
+----------------+--------------------+
| utf8mb4        | utf8mb4_0900_ai_ci |
+----------------+--------------------+
1 row in set (0.0005 sec)

1
2
3
4
5
6

# 其他函数

函数	用法
`FORMAT(value,n)`	返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
`CONV(value,from,to)`	将value的值进行不同进制之间的转换
`INET_ATON(ipvalue)`	将以点分隔的IP地址转化为一个数字
`INET_NTOA(value)`	将数字形式的IP地址转化为以点分隔的IP地址
`BENCHMARK(n,expr)`	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
`CONVERT(value USING char_code)`	将value所使用的字符编码修改为char_code

# `FORMAT`

SELECT
FORMAT(123.456,2),
FORMAT(123.456,-3),
FORMAT(123.456,0);

1
2
3
4

+-------------------+--------------------+-------------------+
| FORMAT(123.456,2) | FORMAT(123.456,-3) | FORMAT(123.456,0) |
+-------------------+--------------------+-------------------+
| 123.46            | 123                | 123               |
+-------------------+--------------------+-------------------+
1 row in set (0.0008 sec)

1
2
3
4
5
6

# `CONV`

见上 数值函数 - 进制转换函数

# `INET_ATON`、`INET_NTOA`

SELECT
INET_ATON('172.16.0.1'),
INET_NTOA(2886729730);

1
2
3

+-------------------------+-----------------------+
| INET_ATON('172.16.0.1') | INET_NTOA(2886729730) |
+-------------------------+-----------------------+
|              2886729729 | 172.16.0.2            |
+-------------------------+-----------------------+
1 row in set (0.0006 sec)

1
2
3
4
5
6

# `BENCHMARK`

SELECT
BENCHMARK(100000,LOG(2,16));
SELECT BENCHMARK(10000000,LOG(2,16));

1
2
3

# `CONVERT`

SELECT
CHARSET('ABC'),
CHARSET(CONVERT('ABC' USING 'utf8mb3'));

1
2
3

+----------------+-----------------------------------------+
| CHARSET('ABC') | CHARSET(CONVERT('ABC' USING 'utf8mb3')) |
+----------------+-----------------------------------------+
| utf8mb4        | utf8mb3                                 |
+----------------+-----------------------------------------+
1 row in set, 1 warning (0.0006 sec)
Warning (code 1287): 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead

1
2
3
4
5
6
7

# 参考链接

上次更新: 2023/07/29, 17:09:37

← 多表查询聚合函数→

单行函数

# 数值函数

# 基本函数

# 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)

# 进制转换函数

# BIN(X)

# HEX(X)

# OCT(X)

# CONV(X,F1,F2)

# 字符串函数

# ASCII(str)

# LENGTH(str)

# CHAR_LENGTH(str)

# CONCAT(str1,str2,...)

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

# INSERT(stt,start,len,insertStr)

# REPLACE(str,oldStr,newStr)

# UPPER(str)、UCASE(str)

# LOWER(str)、UCASE(str)

# LEFT(str)、RIGHT(str)

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

# TRIM(str)、LTRIM(str)、RTRIM(str)

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

# REPEAT(str,count)

# SPACE(count)

# STRCMP(s1,s2)

# SUBSTR(str,index,len)

# LOCATE(findStr,str)

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

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

# FIND_IN_SET(str,strlist)

# REVERSE(str)

# NULLIF(expr1,expr2)

# 日期和时间函数

# 获取日期和时间

# 1. 获取日期

# 2. 获取时间

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

# 时间戳转换的函数

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

# 提取函数

# 时间和秒的转换的函数

# 计算日期和时间的函数

# 获取指定时间间隔的日期

# 计算时间差距

# 日期和时间的格式化

# 流程控制函数

# `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)`

# `BIN(X)`

# `HEX(X)`

# `OCT(X)`

# `CONV(X,F1,F2)`

# `ASCII(str)`

# `LENGTH(str)`

# `CHAR_LENGTH(str)`

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

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

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

# `REPLACE(str,oldStr,newStr)`

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

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

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

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

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

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

# `REPEAT(str,count)`

# `SPACE(count)`

# `STRCMP(s1,s2)`

# `SUBSTR(str,index,len)`

# `LOCATE(findStr,str)`

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

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

# `FIND_IN_SET(str,strlist)`

# `REVERSE(str)`

# `NULLIF(expr1,expr2)`

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

# `IF`

# `IFNULL`

# `CASE WHEN expr1 THEN statment1 ... END`

# `CASE variable WHEN value1 THEN statment1 ... END`

# `FORMAT`

# `CONV`

# `INET_ATON`、`INET_NTOA`

# `BENCHMARK`

# `CONVERT`