数值函数

基本函数

函数 说明
ABS(X) 返回 X 的绝对值
SIGN(X) 返回 X 的符号值,负数返回 -10 返回 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 相互影响,旋转一周,角度从 0360,弧度从 0

弧度 = 弧长 / 半径

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) 返回 XY 次方
EXP(X) 返回 eY 次方
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. 获取日期

  1. CURDATE
  2. CURRENT_DATE
  3. 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. 获取时间

  1. CURTIME
  2. CURRENT_TIME
  3. 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. 获取日期 + 时间 / 时间戳 的函数

  1. NOW
  2. CURRENT_TIMESTAMP
  3. 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)

时间戳转换的函数

  1. UNIX_TIMESTAMP()

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

  1. UNIX_TIMESTAMP(date)

    获取指定时间 UNIX 形式的时间戳

  2. 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 指定返回的值

image-20230728194241936

image-20230728194253235

SELECT
EXTRACT(YEAR FROM NOW()) "year",
EXTRACT(HOUR FROM NOW()) "hour"
FROM DUAL;
+------+------+
| year | hour |
+------+------+
| 2023 |   19 |
+------+------+
1 row in set (0.0006 sec)

时间和秒的转换的函数

  1. SEC_TO_TIME(sec)
  2. 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 取值类型

image-20230728200359876

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)

日期和时间的格式化

  1. DATE_FORMAT(date,fmt)

    根据 fmt 输出指定格式日期字符串

  2. TIME_FORMAT(date,fmt)

    根据 fmt 输出指定格式时间字符串

  3. GET_FORMAT(date_type,format_type)

    获取格式标准化格式

    image-20230728203306200

  4. 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)

参考链接

文章目录