数据库内置函数学习

MySQL内置函数

MySQL 字符串函数

ASCII(s):返回字符串 s 的第一个字符的 ASCII 码。

MariaDB [test3]> select ASCII(title) from test5;
+--------------+
| ASCII(title) |
+--------------+
|           98 |
|           87 |
|           67 |
|           83 |
+--------------+
4 rows in set (0.00 sec)

MariaDB [test3]> select title from test5;
+-------------+
| title       |
+-------------+
| binary star |
| WISH        |
| CRY         |
| Seven       |
+-------------+
4 rows in set (0.00 sec)

CHAR_LENGTH(s):返回字符串 s 的字符数

MariaDB [test3]> select CHAR_LENGTH("author");
+-----------------------+
| CHAR_LENGTH("author") |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)

CHARACTER_LENGTH(s)
返回字符串 s 的字符数，等同于 CHAR_LENGTH(s)


MariaDB [test3]> select CHARACTER_LENGTH("author");
+----------------------------+
| CHARACTER_LENGTH("author") |
+----------------------------+
|                          6 |
+----------------------------+
1 row in set (0.00 sec)

CONCAT(s1,s2...sn)
字符串 s1,s2 等多个字符串合并为一个字符串

MariaDB [test3]> SELECT CONCAT("ID","AUTHOR","TITLE");
+-------------------------------+
| CONCAT("ID","AUTHOR","TITLE") |
+-------------------------------+
| IDAUTHORTITLE                 |
+-------------------------------+
1 row in set (0.00 sec)

CONCAT_WS(x, s1,s2...sn)
同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符

MariaDB [test3]> select concat_ws("-","SQL","Tutorial","is","fun!")AS ConcatenatedString;
+----------------------+
| ConcatenatedString   |
+----------------------+
| SQL-Tutorial-is-fun! |
+----------------------+
1 row in set (0.00 sec)

FIELD(s,s1,s2...)
返回第一个字符串 s 在字符串列表(s1,s2...)中的位置

MariaDB [test3]> SELECT FIELD("ID","TITLE");
+---------------------+
| FIELD("ID","TITLE") |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

FIND_IN_SET(s1,s2)
返回在字符串s2中与s1匹配的字符串的位置

MariaDB [test3]> SELECT FIND_IN_SET("C","A,B,C,D,E");
+------------------------------+
| FIND_IN_SET("C","A,B,C,D,E") |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.01 sec)

FORMAT(x,n)
函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。

MariaDB [test3]> SELECT FORMAT(250500.5634,2);
+-----------------------+
| FORMAT(250500.5634,2) |
+-----------------------+
| 250,500.56            |
+-----------------------+
1 row in set (0.00 sec)

INSERT(s1,x,len,s2)
字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

MariaDB [test3]> SELECT INSERT("google.com",1,6,"runoob");
+-----------------------------------+
| INSERT("google.com",1,6,"runoob") |
+-----------------------------------+
| runoob.com                        |
+-----------------------------------+
1 row in set (0.00 sec)

LOCATE(s1,s)
从字符串 s 中获取 s1 的开始位置

MariaDB [test3]> select locate('st','myteststring');
+-----------------------------+
| locate('st','myteststring') |
+-----------------------------+
|                           5 |
+-----------------------------+
1 row in set (0.00 sec)

LCASE(s)
将字符串 s 的所有字母变成小写字母

MariaDB [test3]> SELECT LCASE('RUNOOB');
+-----------------+
| LCASE('RUNOOB') |
+-----------------+
| runoob          |
+-----------------+
1 row in set (0.00 sec)

LEFT(s,n)
返回字符串 s 的前 n 个字符

MariaDB [(none)]> select LEFT('runoob',2);
+------------------+
| LEFT('runoob',2) |
+------------------+
| ru               |
+------------------+
1 row in set (0.00 sec)

LOWER(s)
将字符串 s 的所有字母变成小写字母

MariaDB [test5]> select lower('RUNOOB');
+-----------------+
| lower('RUNOOB') |
+-----------------+
| runoob          |
+-----------------+
1 row in set (0.00 sec)

LPAD(s1,len,s2)
在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len

MariaDB [test5]> select lpad('abc',5,'xx')
    -> ;
+--------------------+
| lpad('abc',5,'xx') |
+--------------------+
| xxabc              |
+--------------------+
1 row in set (0.00 sec)

LTRIM(s)
去掉字符串 s 开始处的空格

MariaDB [test5]> select LTRIM("    RUNOOB")AS leftTrimmedString;
+-------------------+
| leftTrimmedString |
+-------------------+
| RUNOOB            |
+-------------------+
1 row in set (0.00 sec)

MID(s,n,len)
从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)

MariaDB [test5]> select MID("RUNOOB",2,3)AS ExtracString;
+--------------+
| ExtracString |
+--------------+
| UNO          |
+--------------+
1 row in set (0.00 sec)

POSITION(s1 IN s)
从字符串 s 中获取 s1 的开始位置

MariaDB [test5]> SELECT POSITION('b' in 'abc');
+------------------------+
| POSITION('b' in 'abc') |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

REPEAT(s,n)
将字符串 s 重复 n 次

MariaDB [test5]> select REPEAT('runoob',3);
+--------------------+
| REPEAT('runoob',3) |
+--------------------+
| runoobrunoobrunoob |
+--------------------+
1 row in set (0.00 sec)

REPLACE(s,s1,s2)
将字符串 s2 替代字符串 s 中的字符串 s1

MariaDB [test5]> select REPLACE('abc','a','x');
+------------------------+
| REPLACE('abc','a','x') |
+------------------------+
| xbc                    |
+------------------------+
1 row in set (0.00 sec)

REVERSE(s)
将字符串s的顺序反过来

MariaDB [test5]> select REVERSE('abc');
+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

RIGHT(s,n)
返回字符串 s 的后 n 个字符

MariaDB [test5]> select RIGHT('runoob',2);
+-------------------+
| RIGHT('runoob',2) |
+-------------------+
| ob                |
+-------------------+
1 row in set (0.00 sec)

RPAD(s1,len,s2)
在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

MariaDB [test5]> select RPAD('abc',5,'xx');
+--------------------+
| RPAD('abc',5,'xx') |
+--------------------+
| abcxx              |
+--------------------+
1 row in set (0.00 sec)

RTRIM(s)
去掉字符串 s 结尾处的空格

MariaDB [test5]> select RTRIM("RUNOOB  ")AS RightTrimmedString;
+--------------------+
| RightTrimmedString |
+--------------------+
| RUNOOB             |
+--------------------+
1 row in set (0.00 sec)

SPACE(n)
返回 n 个空格

MariaDB [test5]> select SPACE(10);
+------------+
| SPACE(10)  |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

STRCMP(s1,s2)
比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

MariaDB [test5]> select STRCMP("runoob","runoob");
+---------------------------+
| STRCMP("runoob","runoob") |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [test5]> select STRCMP("Runooc","runoob");
+---------------------------+
| STRCMP("Runooc","runoob") |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [test5]> select STRCMP("runnoob","runoac");
+----------------------------+
| STRCMP("runnoob","runoac") |
+----------------------------+
|                         -1 |
+----------------------------+
1 row in set (0.00 sec)

SUBSTR(s, start, length)
从字符串 s 的 start 位置截取长度为 length 的子字符串

MariaDB [test5]> select SUBSTR('RUNOOB',2,3)AS ExtractString;
+---------------+
| ExtractString |
+---------------+
| UNO           |
+---------------+
1 row in set (0.00 sec)

SUBSTRING(s, start, length)
从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length)

MariaDB [test5]> select SUBSTRING('RUNOOB',2,3)AS ExtracString;
+--------------+
| ExtracString |
+--------------+
| UNO          |
+--------------+
1 row in set (0.00 sec)

SUBSTRING_INDEX(s, delimiter, number)
返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。

MariaDB [test5]> select SUBSTRING_INDEX('a*b','*',1);
+------------------------------+
| SUBSTRING_INDEX('a*b','*',1) |
+------------------------------+
| a                            |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [test5]> select SUBSTRING_INDEX('a*b','*',-1);
+-------------------------------+
| SUBSTRING_INDEX('a*b','*',-1) |
+-------------------------------+
| b                             |
+-------------------------------+
1 row in set (0.00 sec)

MariaDB [test5]> select SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1);
+------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) |
+------------------------------------------------------------+
| c                                                          |
+------------------------------------------------------------+
1 row in set (0.00 sec)

TRIM(s)
去掉字符串 s 开始和结尾处的空格

MariaDB [test5]> select TRIM('   RUNOOB   ')AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| RUNOOB        |
+---------------+
1 row in set (0.00 sec)

UCASE(s)
将字符串转换为大写

MariaDB [test5]> select UCASE('runoob');
+-----------------+
| UCASE('runoob') |
+-----------------+
| RUNOOB          |
+-----------------+
1 row in set (0.00 sec)

UPPER(s)
将字符串转换为大写

MariaDB [test5]> select UPPER("runoob");
+-----------------+
| UPPER("runoob") |
+-----------------+
| RUNOOB          |
+-----------------+
1 row in set (0.00 sec)

MySQL 数字函数

ABS(x)
返回 x 的绝对值

MariaDB [test5]> select ABS(-1);
+---------+
| ABS(-1) |
+---------+
|       1 |
+---------+
1 row in set (0.01 sec)

ACOS(x)
求 x 的反余弦值（单位为弧度），x 为一个数值

MariaDB [test5]> SELECT ACOS(0.25);
+-------------------+
| ACOS(0.25)        |
+-------------------+
| 1.318116071652818 |
+-------------------+
1 row in set (0.00 sec)

ASIN(x)
求反正弦值（单位为弧度），x 为一个数值

MariaDB [test5]> SELECT ASIN(0.25);
+---------------------+
| ASIN(0.25)          |
+---------------------+
| 0.25268025514207865 |
+---------------------+
1 row in set (0.00 sec)

ATAN(x)
求反正切值（单位为弧度），x 为一个数值

MariaDB [test5]> SELECT ATAN(2.5);
+--------------------+
| ATAN(2.5)          |
+--------------------+
| 1.1902899496825317 |
+--------------------+
1 row in set (0.00 sec)

ATAN2(n, m)
求反正切值（单位为弧度）

MariaDB [test5]> SELECT ATAN2(-0.8,2);
+---------------------+
| ATAN2(-0.8,2)       |
+---------------------+
| -0.3805063771123649 |
+---------------------+
1 row in set (0.00 sec)

AVG(expression)
返回一个表达式的平均值，expression 是一个字段

MariaDB [test3]> select AVG(Title) AS AveragePrice FROM test5; 
+--------------+
| AveragePrice |
+--------------+
|            0 |
+--------------+
1 row in set, 3 warnings (0.00 sec)

CEIL(x)
返回大于或等于 x 的最小整数

MariaDB [test3]> select CEIL(1.5);
+-----------+
| CEIL(1.5) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

CEILING(x)
返回大于或等于 x 的最小整数

MariaDB [test3]> SELECT CEILING(1.5);
+--------------+
| CEILING(1.5) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

COS(x)
求余弦值(参数是弧度)

MariaDB [test3]> SELECT COS(2);
+---------------------+
| COS(2)              |
+---------------------+
| -0.4161468365471424 |
+---------------------+
1 row in set (0.00 sec)

COT(x)
求余切值(参数是弧度)

MariaDB [test3]> SELECT COT(6);
+--------------------+
| COT(6)             |
+--------------------+
| -3.436353004180128 |
+--------------------+
1 row in set (0.00 sec)

COUNT(expression)
返回查询的记录总数，expression 参数是一个字段或者 * 号

MariaDB [test3]> SELECT COUNT(ID) AS NumberOfProducts FROM test5;
+------------------+
| NumberOfProducts |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

DEGREES(x)
将弧度转换为角度

MariaDB [test3]> select DEGREES(3.1415926535898);
+--------------------------+
| DEGREES(3.1415926535898) |
+--------------------------+
|        180.0000000000004 |
+--------------------------+
1 row in set (0.00 sec)

n DIV m
整除，n 为被除数，m 为除数

MariaDB [test3]> SELECT 10 DIV 5;
+----------+
| 10 DIV 5 |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

EXP(x)
返回 e 的 x 次方

MariaDB [test3]> SELECT EXP(3)
    -> ;
+--------------------+
| EXP(3)             |
+--------------------+
| 20.085536923187668 |
+--------------------+
1 row in set (0.00 sec)

FLOOR(x)
返回小于或等于 x 的最大整数

MariaDB [test3]> SELECT FLOOR(1.5);
+------------+
| FLOOR(1.5) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

GREATEST(expr1, expr2, expr3, ...)
返回列表中的最大值

MariaDB [test3]> SELECT GREATEST(3,12,34,8,25);
+------------------------+
| GREATEST(3,12,34,8,25) |
+------------------------+
|                     34 |
+------------------------+
1 row in set (0.00 sec)

MariaDB [test3]> SELECT GREATEST('Google','Runoob','Apple');
+-------------------------------------+
| GREATEST('Google','Runoob','Apple') |
+-------------------------------------+
| Runoob                              |
+-------------------------------------+
1 row in set (0.00 sec)

LEAST(expr1, expr2, expr3, ...)
返回列表中的最小值

MariaDB [test3]> select least(3,12,24,5,35);
+---------------------+
| least(3,12,24,5,35) |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test3]> select least('google','runnoob','apple');
+-----------------------------------+
| least('google','runnoob','apple') |
+-----------------------------------+
| apple                             |
+-----------------------------------+
1 row in set (0.00 sec)

LN
返回数字的自然对数，以 e 为底。

MariaDB [test3]> select ln(2);
+--------------------+
| ln(2)              |
+--------------------+
| 0.6931471805599453 |
+--------------------+
1 row in set (0.00 sec)

LOG(x) 或 LOG(base, x)

返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。

MariaDB [test3]> select log(20.085536923188);
+----------------------+
| log(20.085536923188) |
+----------------------+
|   3.0000000000000164 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [test3]> select LOG(2,4);
+----------+
| LOG(2,4) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

LOG10(x)

返回以 10 为底的对数

MariaDB [test3]> select LOG10(100);
+------------+
| LOG10(100) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

LOG2(x)

返回以 2 为底的对数

MariaDB [test3]> select LOG2(6);
+-------------------+
| LOG2(6)           |
+-------------------+
| 2.584962500721156 |
+-------------------+
1 row in set (0.00 sec)

MAX(expression)

返回字段 expression 中的最大值

MariaDB [test3]> SELECT MAX(AUTHOR) AS LargestAuthor FROM test5;
+---------------+
| LargestAuthor |
+---------------+
| wish          |
+---------------+
1 row in set (0.00 sec)

MIN(expression)

返回字段 expression 中的最小值

MariaDB [test3]> select MIN(author) AS Minauthor From test5;
+-----------+
| Minauthor |
+-----------+
| Aimer     |
+-----------+
1 row in set (0.00 sec)

MOD(x,y)
返回 x 除以 y 以后的余数

MariaDB [test3]> select MOD(5,2);
+----------+
| MOD(5,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

PI()

返回圆周率(3.141593）

MariaDB [test3]> SELECT PI()
    -> ;
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

POW(x,y)

返回 x 的 y 次方

MariaDB [test3]> SELECT POW(2,3);
+----------+
| POW(2,3) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

POWER(x,y)

返回 x 的 y 次方

MariaDB [test3]> SELECT POWER(2,3);
+------------+
| POWER(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)

RADIANS(x)

将角度转换为弧度

MariaDB [test3]> SELECT RADIANS(180);
+-------------------+
| RADIANS(180)      |
+-------------------+
| 3.141592653589793 |
+-------------------+
1 row in set (0.00 sec)

RAND()

返回 0 到 1 的随机数

MariaDB [test5]> select RAND();
+---------------------+
| RAND()              |
+---------------------+
| 0.32665407594913065 |
+---------------------+
1 row in set (0.00 sec)

ROUND(x)

返回离 x 最近的整数

MariaDB [test5]> SELECT ROUND(1.22);
+-------------+
| ROUND(1.22) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

SIGN(x)

返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1

MariaDB [test5]> SELECT SIGN(-10);
+-----------+
| SIGN(-10) |
+-----------+
|        -1 |
+-----------+
1 row in set (0.00 sec)

MariaDB [test5]> SELECT SIGN(10);
+----------+
| SIGN(10) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

SIN(x)

求正弦值(参数是弧度)

MariaDB [test5]> SELECT SIN(RADIANS(30));
+---------------------+
| SIN(RADIANS(30))    |
+---------------------+
| 0.49999999999999994 |
+---------------------+
1 row in set (0.00 sec)

SQRT(x)

返回x的平方根

MariaDB [test5]> SELECT SQRT(25);
+----------+
| SQRT(25) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

SUM(expression)

返回指定字段的总和

MariaDB [test3]> select SUM(id) as Totalauthor FROM test5;
+-------------+
| Totalauthor |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

TAN(x)

求正切值(参数是弧度)

MariaDB [test3]> select TAN(1.75);
+-------------------+
| TAN(1.75)         |
+-------------------+
| -5.52037992250933 |
+-------------------+
1 row in set (0.00 sec)

TRUNCATE(x,y)

返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

MariaDB [test3]> SELECT TRUNCATE(1.23456,3);
+---------------------+
| TRUNCATE(1.23456,3) |
+---------------------+
|               1.234 |
+---------------------+
1 row in set (0.00 sec)

MySQL 日期函数

ADDDATE(d,n)

计算起始日期 d 加上 n 天的日期

MariaDB [test3]> SELECT ADDDATE("2022-05-05",INTERVAL 10 DAY);
+---------------------------------------+
| ADDDATE("2022-05-05",INTERVAL 10 DAY) |
+---------------------------------------+
| 2022-05-15                            |
+---------------------------------------+
1 row in set (0.00 sec)

ADDTIME(t,n)
n 是一个时间表达式，时间 t 加上时间表达式 n

MariaDB [test3]> SELECT ADDTIME("2022-05-05 11:11:11",5);
+----------------------------------+
| ADDTIME("2022-05-05 11:11:11",5) |
+----------------------------------+
| 2022-05-05 11:11:16              |
+----------------------------------+
1 row in set (0.00 sec)

CURDATE()

返回当前日期

MariaDB [test3]> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2022-05-05 |
+------------+
1 row in set (0.00 sec)

CURRENT_DATE()

返回当前日期

MariaDB [test3]> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2022-05-05     |
+----------------+
1 row in set (0.00 sec)

CURRENT_TIME

返回当前时间

MariaDB [test3]> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 21:28:04       |
+----------------+
1 row in set (0.00 sec)

CURRENT_TIMESTAMP()

返回当前日期和时间

MariaDB [test3]> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2022-05-05 21:28:47 |
+---------------------+
1 row in set (0.00 sec)

CURTIME()

返回当前时间

MariaDB [test3]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 21:29:28  |
+-----------+
1 row in set (0.00 sec)

DATE()

从日期或日期时间表达式中提取日期值

MariaDB [test3]> SELECT DATE("2022-05-15");
+--------------------+
| DATE("2022-05-15") |
+--------------------+
| 2022-05-15         |
+--------------------+
1 row in set (0.00 sec)

DATEDIFF(d1,d2)

计算日期 d1->d2 之间相隔的天数

MariaDB [test3]> SELECT DATEDIFF('2001-01-01','2001-02-02')
    -> ;
+-------------------------------------+
| DATEDIFF('2001-01-01','2001-02-02') |
+-------------------------------------+
|                                 -32 |
+-------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(d，INTERVAL expr type)

计算起始日期 d 加上一个时间段后的日期，type 值可以是：

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

MariaDB [test3]> SELECT DATE_ADD('2022-05-05',INTERVAL 10 DAY);
+----------------------------------------+
| DATE_ADD('2022-05-05',INTERVAL 10 DAY) |
+----------------------------------------+
| 2022-05-15                             |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [test3]> SELECT DATE_ADD('2017-06-15 09:34:21',INTERVAL 15 MINUTE);
+----------------------------------------------------+
| DATE_ADD('2017-06-15 09:34:21',INTERVAL 15 MINUTE) |
+----------------------------------------------------+
| 2017-06-15 09:49:21                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test3]> SELECT DATE_ADD('2017-05-15 09:34:21',INTERVAL -3 HOUR);
+--------------------------------------------------+
| DATE_ADD('2017-05-15 09:34:21',INTERVAL -3 HOUR) |
+--------------------------------------------------+
| 2017-05-15 06:34:21                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test3]> SELECT DATE_ADD('2017-05-015 09:34:21',INTERVAL -3 MONTH);
+----------------------------------------------------+
| DATE_ADD('2017-05-015 09:34:21',INTERVAL -3 MONTH) |
+----------------------------------------------------+
| 2017-02-15 09:34:21                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT(d,f)

按表达式 f的要求显示日期 d

MariaDB [test3]> SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');
+--------------------------------------------------+
| DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') |
+--------------------------------------------------+
| 2011-11-11 11:11:11 AM                           |
+--------------------------------------------------+
1 row in set (0.00 sec)

DATE_SUB(date,INTERVAL expr type)

函数从日期减去指定的时间间隔。


MariaDB [test3]> select * from test5;
+----+-------------+--------+-----------------+
| id | title       | author | submission_date |
+----+-------------+--------+-----------------+
|  1 | binary star | ss     | 2022-04-19      |
|  2 | WISH        | Aimer  | 2022-04-19      |
|  3 | CRY         | wish   | 2022-04-19      |
|  4 | Seven       | Aimer  | 2022-04-19      |
+----+-------------+--------+-----------------+
4 rows in set (0.00 sec)

MariaDB [test3]> SELECT id ,DATE_SUB(submission_date,INTERVAL 2 DAY) AS OrderDate from test5;
+----+------------+
| id | OrderDate  |
+----+------------+
|  1 | 2022-04-17 |
|  2 | 2022-04-17 |
|  3 | 2022-04-17 |
|  4 | 2022-04-17 |
+----+------------+
4 rows in set (0.00 sec)

DAY(d)

返回日期值 d 的日期部分

MariaDB [test3]> select DAY("2022-05-15");
+-------------------+
| DAY("2022-05-15") |
+-------------------+
|                15 |
+-------------------+
1 row in set (0.00 sec)

DAYNAME(d)

返回日期 d 是星期几，如 Monday,Tuesday

MariaDB [test3]> SELECT DAYOFMONTH('2022-11-11 11:11:11');
+-----------------------------------+
| DAYOFMONTH('2022-11-11 11:11:11') |
+-----------------------------------+
|                                11 |
+-----------------------------------+
1 row in set (0.00 sec)

DAYOFWEEK(d)

日期 d 今天是星期几，1 星期日，2 星期一，以此类推

MariaDB [test3]> SELECT DAYOFWEEK('2022-11-11 11:11:11');
+----------------------------------+
| DAYOFWEEK('2022-11-11 11:11:11') |
+----------------------------------+
|                                6 |
+----------------------------------+
1 row in set (0.00 sec)

DAYOFYEAR(d)

计算日期 d 是本年的第几天

MariaDB [test5]> select DAYOFYEAR('2022-11-11 11:11:11');
+----------------------------------+
| DAYOFYEAR('2022-11-11 11:11:11') |
+----------------------------------+
|                              315 |
+----------------------------------+
1 row in set (0.00 sec)

EXTRACT(type FROM d)

从日期 d 中获取指定的值，type 指定返回的值。
type可取值为：

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

MariaDB [test5]> SELECT EXTRACT(MINUTE FROM '2022-10-24 10:24:11');
+--------------------------------------------+
| EXTRACT(MINUTE FROM '2022-10-24 10:24:11') |
+--------------------------------------------+
|                                         24 |
+--------------------------------------------+
1 row in set (0.00 sec)

FROM_DAYS(n)

计算从 0000 年 1 月 1 日开始 n 天后的日期

MariaDB [test5]> SELECT FROM_DAYS(1111)
    -> ;
+-----------------+
| FROM_DAYS(1111) |
+-----------------+
| 0003-01-16      |
+-----------------+
1 row in set (0.00 sec)

HOUR(t)

返回 t 中的小时值

MariaDB [test5]> SELECT HOUR('1:2:3');
+---------------+
| HOUR('1:2:3') |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

LAST_DAY(d)

返回给给定日期的那一月份的最后一天

MariaDB [test5]> SELECT LAST_DAY('2022-06-22');
+------------------------+
| LAST_DAY('2022-06-22') |
+------------------------+
| 2022-06-30             |
+------------------------+
1 row in set (0.00 sec)

LOCALTIME()

返回当前日期和时间

MariaDB [test5]> SELECT LOCALTIME();
+---------------------+
| LOCALTIME()         |
+---------------------+
| 2022-05-06 09:57:02 |
+---------------------+
1 row in set (0.00 sec)

LOCALTIMESTAMP()

返回当前日期和时间

MariaDB [test5]> SELECT LOCALTIMESTAMP();
+---------------------+
| LOCALTIMESTAMP()    |
+---------------------+
| 2022-05-06 09:57:45 |
+---------------------+
1 row in set (0.00 sec)

MAKEDATE(year, day-of-year)

基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期

MariaDB [test5]> SELECT MAKEDATE(2022,3);
+------------------+
| MAKEDATE(2022,3) |
+------------------+
| 2022-01-03       |
+------------------+
1 row in set (0.00 sec)

MICROSECOND(date)

返回日期参数所对应的微秒数

MariaDB [test5]> SELECT MICROSECOND("2022-05-20 09:34:00.000023");
+-------------------------------------------+
| MICROSECOND("2022-05-20 09:34:00.000023") |
+-------------------------------------------+
|                                        23 |
+-------------------------------------------+
1 row in set (0.00 sec)

MINUTE(t)

返回 t 中的分钟值

MariaDB [test5]> SELECT MINUTE('1:2:3');
+-----------------+
| MINUTE('1:2:3') |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

MONTHNAME(d)

返回日期当中的月份名称，如 November

MariaDB [test5]> SELECT MONTHNAME('2022-06-16 15:11:14');
+----------------------------------+
| MONTHNAME('2022-06-16 15:11:14') |
+----------------------------------+
| June                             |
+----------------------------------+
1 row in set (0.00 sec)

MONTH(d)

返回日期d中的月份值，1 到 12

MariaDB [test5]> SELECT MONTH('2022-11-11 11:11:11');
+------------------------------+
| MONTH('2022-11-11 11:11:11') |
+------------------------------+
|                           11 |
+------------------------------+
1 row in set (0.00 sec)

NOW()

返回当前日期和时间

MariaDB [test5]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-05-06 10:02:40 |
+---------------------+
1 row in set (0.00 sec)

PERIOD_ADD(period, number)

为年-月组合日期添加一个时段

MariaDB [test5]> SELECT PERIOD_ADD(202206,6);
+----------------------+
| PERIOD_ADD(202206,6) |
+----------------------+
|               202212 |
+----------------------+
1 row in set (0.00 sec)

PERIOD_DIFF(period1, period2)

返回两个时段之间的月份差值

MariaDB [test5]> SELECT PERIOD_DIFF(202206,202209);
+----------------------------+
| PERIOD_DIFF(202206,202209) |
+----------------------------+
|                         -3 |
+----------------------------+
1 row in set (0.00 sec)

QUARTER(d)

返回日期d是第几季节，返回 1 到 4

MariaDB [test5]> SELECT QUARTER("2022-11-11 11:11:11");
+--------------------------------+
| QUARTER("2022-11-11 11:11:11") |
+--------------------------------+
|                              4 |
+--------------------------------+
1 row in set (0.00 sec)

SECOND(t)

返回 t 中的秒钟值

MariaDB [test5]> SELECT SECOND('1:2:3');
+-----------------+
| SECOND('1:2:3') |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

SEC_TO_TIME(s)

将以秒为单位的时间 s 转换为时分秒的格式

MariaDB [test5]> select second(3345);
+--------------+
| second(3345) |
+--------------+
|           45 |
+--------------+
1 row in set (0.00 sec)

STR_TO_DATE(string, format_mask)

将字符串转变为日期

MariaDB [test5]> SELECT STR_TO_DATE("August 10 2022","%M %d %Y");
+------------------------------------------+
| STR_TO_DATE("August 10 2022","%M %d %Y") |
+------------------------------------------+
| 2022-08-10                               |
+------------------------------------------+
1 row in set (0.00 sec)

SUBDATE(d,n)

日期 d 减去 n 天后的日期

MariaDB [test5]> select subdate('2022-11-11 11:11:11',1);
+----------------------------------+
| subdate('2022-11-11 11:11:11',1) |
+----------------------------------+
| 2022-11-10 11:11:11              |
+----------------------------------+
1 row in set (0.01 sec)

SUBTIME(t,n)

时间 t 减去 n 秒的时间

MariaDB [test5]> select subtime('2022-11-11 11:11:11',5);
+----------------------------------+
| subtime('2022-11-11 11:11:11',5) |
+----------------------------------+
| 2022-11-11 11:11:06              |
+----------------------------------+
1 row in set (0.00 sec)

SYSDATE()

返回当前日期和时间

MariaDB [test5]> select SYSDATE();
+---------------------+
| SYSDATE()           |
+---------------------+
| 2022-05-06 10:11:20 |
+---------------------+
1 row in set (0.00 sec)

TIME(expression)

提取传入表达式的时间部分

MariaDB [test5]> SELECT TIME('20:22:10');
+------------------+
| TIME('20:22:10') |
+------------------+
| 20:22:10         |
+------------------+
1 row in set (0.00 sec)

TIME_FORMAT(t,f)

按表达式 f 的要求显示时间 t

MariaDB [test5]> SELECT TIME_FORMAT('11:11:11','%r');
+------------------------------+
| TIME_FORMAT('11:11:11','%r') |
+------------------------------+
| 11:11:11 AM                  |
+------------------------------+
1 row in set (0.01 sec)

TIME_TO_SEC(t)

将时间 t 转换为秒

MariaDB [test5]> select TIME_TO_SEC('1:12:00');
+------------------------+
| TIME_TO_SEC('1:12:00') |
+------------------------+
|                   4320 |
+------------------------+
1 row in set (0.00 sec)

TIMEDIFF(time1, time2)

计算时间差值

MariaDB [test5]> SELECT TIMEDIFF('13:10:11','13:11:12');
+---------------------------------+
| TIMEDIFF('13:10:11','13:11:12') |
+---------------------------------+
| -00:01:01                       |
+---------------------------------+
1 row in set (0.00 sec)

TIMESTAMP(expression, interval)

单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和

MariaDB [test5]> SELECT TIMESTAMP('2022-06-22','13:11:11');
+------------------------------------+
| TIMESTAMP('2022-06-22','13:11:11') |
+------------------------------------+
| 2022-06-22 13:11:11                |
+------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差

MariaDB [test5]> SELECT TIMESTAMPDIFF(DAY,'2022-06-01','2022-05-01');
+----------------------------------------------+
| TIMESTAMPDIFF(DAY,'2022-06-01','2022-05-01') |
+----------------------------------------------+
|                                          -31 |
+----------------------------------------------+
1 row in set (0.00 sec)

TO_DAYS(d)

计算日期 d 距离 0000 年 1 月 1 日的天数

MariaDB [test5]> SELECT TO_DAYS('0001-01-01 01;01:01');
+--------------------------------+
| TO_DAYS('0001-01-01 01;01:01') |
+--------------------------------+
|                            366 |
+--------------------------------+
1 row in set (0.00 sec)

WEEK(d)

计算日期 d 是本年的第几个星期，范围是 0 到 53

MariaDB [test5]> SELECT WEEK('2022-11-11 11:11:11');
+-----------------------------+
| WEEK('2022-11-11 11:11:11') |
+-----------------------------+
|                          45 |
+-----------------------------+
1 row in set (0.00 sec)

WEEKDAY(d)

日期 d 是星期几，0 表示星期一，1 表示星期二

MariaDB [test5]> SELECT WEEKDAY('2022-06-15');
+-----------------------+
| WEEKDAY('2022-06-15') |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

WEEKOFYEAR(d)

计算日期 d 是本年的第几个星期，范围是 0 到 53

MariaDB [test5]> SELECT WEEKOFYEAR('2022-11-11 11:11:11');
+-----------------------------------+
| WEEKOFYEAR('2022-11-11 11:11:11') |
+-----------------------------------+
|                                45 |
+-----------------------------------+
1 row in set (0.00 sec)

YEAR(d)

返回年份

MariaDB [test5]> SELECT YEAR('2022-06-11');
+--------------------+
| YEAR('2022-06-11') |
+--------------------+
|               2022 |
+--------------------+
1 row in set (0.00 sec)

YEARWEEK(date, mode)

返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推

MariaDB [test5]> SELECT YEARWEEK('2022-06-11');
+------------------------+
| YEARWEEK('2022-06-11') |
+------------------------+
|                 202223 |
+------------------------+
1 row in set (0.00 sec)

MySQL 高级函数

BIN(x)

返回 x 的二进制编码

MariaDB [test5]> SELECT BIN(16);
+---------+
| BIN(16) |
+---------+
| 10000   |
+---------+
1 row in set (0.00 sec)

BINARY(s)

将字符串 s 转换为二进制字符串

MariaDB [test5]> SELECT BINARY 'RUNOOB';
+-----------------+
| BINARY 'RUNOOB' |
+-----------------+
| RUNOOB          |
+-----------------+
1 row in set (0.00 sec)

CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

MariaDB [test5]> SELECT CASE
    -> WHEN 1>0
    -> THEN '1>0'
    -> WHEN 2>0
    -> THEN '2>0'
    -> ELSE '3>0'
    -> END
    -> ;
+-------------------------------------------------------------+
| CASE
WHEN 1>0
THEN '1>0'
WHEN 2>0
THEN '2>0'
ELSE '3>0'
END |
+-------------------------------------------------------------+
| 1>0                                                         |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

CAST(x AS type)

转换数据类型

MariaDB [test5]> SELECT CAST('2022-08-25' AS DATE);
+----------------------------+
| CAST('2022-08-25' AS DATE) |
+----------------------------+
| 2022-08-25                 |
+----------------------------+
1 row in set (0.00 sec)

COALESCE(expr1, expr2, ...., expr_n)

返回参数中的第一个非空表达式（从左向右）

MariaDB [test5]> SELECT COALESCE(NULL,NULL,NULL,'runoob.com',NULL,'google.com');
+---------------------------------------------------------+
| COALESCE(NULL,NULL,NULL,'runoob.com',NULL,'google.com') |
+---------------------------------------------------------+
| runoob.com                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONNECTION_ID()

返回唯一的连接 ID

MariaDB [test5]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

CONV(x,f1,f2)

返回 f1 进制数变成 f2 进制数

MariaDB [test5]> select CONV(15,10,2);
+---------------+
| CONV(15,10,2) |
+---------------+
| 1111          |
+---------------+
1 row in set (0.00 sec)

CONVERT(s USING cs)

函数将字符串 s 的字符集变成 cs

MariaDB [test5]> SELECT CHARSET('ABC')
    -> ;
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8           |
+----------------+
1 row in set (0.00 sec)

MariaDB [test5]> SELECT CHARSET(CONVERT('ABC' USING gbk));
+-----------------------------------+
| CHARSET(CONVERT('ABC' USING gbk)) |
+-----------------------------------+
| gbk                               |
+-----------------------------------+
1 row in set (0.01 sec)

CURRENT_USER()

返回当前用户

MariaDB [test5]> select CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

DATABASE()

返回当前数据库名

MariaDB [test5]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test5      |
+------------+
1 row in set (0.00 sec)

IF(expr,v1,v2)

如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

MariaDB [test5]> SELECT IF(1 > 0,'TRUE','FALSE');
+--------------------------+
| IF(1 > 0,'TRUE','FALSE') |
+--------------------------+
| TRUE                     |
+--------------------------+
1 row in set (0.00 sec)

IFNULL(v1,v2)

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

MariaDB [test5]> SELECT IFNULL(null,'HELLO WORD')
    -> ;
+---------------------------+
| IFNULL(null,'HELLO WORD') |
+---------------------------+
| HELLO WORD                |
+---------------------------+
1 row in set (0.00 sec)

ISNULL(expression)

判断表达式是否为 NULL

MariaDB [test5]> select ISNULL(NULL);
+--------------+
| ISNULL(NULL) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

LAST_INSERT_ID()

返回最近生成的 AUTO_INCREMENT 值

MariaDB [test5]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

NULLIF(expr1, expr2)

比较两个字符串，如果字符串 expr1 与 expr2 相等返回 NULL，否则返回 expr1

MariaDB [test5]> SELECT NULLIF(25,25);
+---------------+
| NULLIF(25,25) |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

SESSION_USER()

返回当前用户

MariaDB [test5]> SELECT SESSION_USER();
+----------------+
| SESSION_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

SYSTEM_USER()

返回当前用户

MariaDB [test5]> SELECT SYSTEM_USER();
+----------------+
| SYSTEM_USER()  |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

USER()

返回当前用户

MariaDB [test5]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

VERSION()

返回数据库的版本号

MariaDB [test5]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 5.5.68-MariaDB |
+----------------+
1 row in set (0.00 sec)

小结

可以用来获取数据库信息，Mysql授权的用户名和主机名，还有版本号，以及当前的数据库，利用user()、system_user()获取用户名信息，version()获取版本号。

执行系统命令

使用system
在 MySQL 的命令行界面中可以使用 system shell-cmd 或者 ! shell-cmd 格式执行 shell 命令

# 查看当前目录
MariaDB [test5]> \! pwd 
/home/seven

# 开启新的shell
MariaDB [test5]> \! bash
[seven@centos7 ~]$

只需要在mysql命令行界面使用system + linux命令即可。

# 查看当前目录
MariaDB [test5]> system ls;
test1.txt  公共  模板  视频  图片  文档  下载  音乐  桌面

# 查看当前时间
MariaDB [test5]> system date;
2022年 05月 06日 星期五 11:17:55 CST

# 查看当前用户
MariaDB [test5]> system whoami;
seven

Mysql udf

UDF是mysql的一个拓展接口，UDF（User-defined function）可翻译为用户自定义函数，这个是用来拓展Mysql的技术手段。

安装环境

yum install mysql-server
yum install mysql-devel

1.编写c++代码并生成动态链接库

示例c++代码如下:

// g++ -shared -fPIC -I /usr/include/mysql -o udf.so udf.cc to compile

#include <mysql/mysql.h>

extern "C"{

long long myadd(UDF_INIT *initid, UDF_ARGS *args,

char *is_null, char *error);

my_bool myadd_init(UDF_INIT *initid, UDF_ARGS *args,

char *message);

}

long long myadd(UDF_INIT *initid, UDF_ARGS *args,

char *is_null, char *error) {

int a = *((long long *)args->args[0]);

int b = *((long long *)args->args[1]);

return a + b;

}

my_bool myadd_init(UDF_INIT *initid, UDF_ARGS *args,

char *message){

return 0;

}

完成以后, 将文件保存为udf.cpp 然后使用如下的命令编译动态链接库:

g++ -shared -fPIC -I /usr/include/mysql -o udf.so udf.cpp

Step2: 在MySQL中添加函数

完成上述的编写以后, 将udf.so文件拷贝到MySQL的plugin目录下:

/usr/lib/mysql/plugin/

然后使用如下的命令在MySQL中安装动态链接库


CREATE FUNCTION myadd RETURNS INTEGER SONAME 'udf.so'

Step3: 调用函数以及相关查询

调用函数

select myadd(1,2);

可以获得计算结果3

查询安装列表

select * from mysql.func;

可以查看数据库当前被安装的.so的库列表.

用drop function来删除函数:

DROP FUNCTION myadd;

小结

可以看到, MySQL的UDF可以用于处理MySQL表中的数据, 其对外提供了普通函数与Aggregate函数接口, 普通函数处理一行的数据, Aggregate函数处理一个group的数据. 其函数头是固定的, 对外提供了5种数据类型.需要注意的是, 我们编写的MySQL UDF必须保证是线程安全的。

参考

关于Mysql中UDF函数的思考（一）
MySQL的UDF
Sql注入-数据库系统功能函数]
MySQL 函数

posted @ 2022-05-06 15:49 JaydenHuan 阅读(38) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Jayden

Like a miracle to starving show.

数据库内置函数学习

MySQL内置函数

MySQL 字符串函数

MySQL 数字函数

MySQL 日期函数

MySQL 高级函数

小结

执行系统命令

Mysql udf

安装环境

1.编写c++代码并生成动态链接库

Step2: 在MySQL中添加函数

Step3: 调用函数以及相关查询

小结

参考

公告