Mysql 函数

最新推荐文章于 2023-02-15 10:56:07 发布

 清欢渡.

最新推荐文章于 2023-02-15 10:56:07 发布

阅读量511

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/shm19990131/article/details/120096918

版权

Mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MySQL 开发常用函数

一、数学类函数

ABS(x) 绝对值函数
PI() 圆周率函数
SQRT(x) 平方根函数
FLOOR(x) CEIL(x) CEILING(x) 取整函数
RAND() \ RAND(x) 随机数函数(0~1),(x) 取固定值
ROUND(x,y) \ ROUND(x) 四舍五入函数，(x) 保留整数
TRUNCATE(x,y) 截取数值函数
SIGN(x) 符号函数 x>0=1 x<0=-1
POW(x,y) POWER(x,y) EXP(y) 幂运算函数
LOG(x) LOG10(x) 对数函数

1、绝对值函数 ABS

ABS(x)  用于给 x 求绝对值  
一个负数的绝对值等于它的相反数，一个正数的绝对值等于他本身

mysql> SELECT ABS(10),ABS(-10);
+---------+----------+
| ABS(10) | ABS(-10) |
+---------+----------+
|      10 |       10 |
+---------+----------+

2、圆周率函数 PI

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

mysql> SELECT PI()+0.000000002;
+------------------+
| PI()+0.000000002 |
+------------------+
|      3.141592656 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(PI()-123*3/2+123);
+-----------------------+
| ABS(PI()-123*3/2+123) |
+-----------------------+
|             58.358407 |
+-----------------------+
1 row in set (0.00 sec)

3、平方根函数 SQRT(x)

SQRT(x) 

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

mysql> SELECT SQRT(ABS(-9));
+---------------+
| SQRT(ABS(-9)) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

4、取整函数 CEIL、CEILING、FLOOR

CEIL(x)		取最接近x且不小于x的整数
CEILING(X)   同上
FLOOR(X)    取最接近x且不大于x的整数

5、随机数函数 RAND

RAND()   返回随机数，范围在0~1之间，大于0 小于1
RAND(x)    x 作为种子值，用于获取固定的随机数列

6、四舍五入函数 ROUND

ROUND(x) 	对 x 进行四舍五入，结果保留整数
ROUND(x,y)  对 x 进行四舍五入，结果保留 y 位小数

mysql> SELECT ROUND(10.123),ROUND(10.567);
+---------------+---------------+
| ROUND(10.123) | ROUND(10.567) |
+---------------+---------------+
|            10 |            11 |
+---------------+---------------+

mysql> SELECT ROUND(10.123,2),ROUND(10.567,6);
+-----------------+-----------------+
| ROUND(10.123,2) | ROUND(10.567,6) |
+-----------------+-----------------+
|           10.12 |       10.567000 |
+-----------------+-----------------+

7、截取数值的函数 TRUNCATE(x，y)

TRUNCATE (x,y)   只截取，不约分不进位

mysql> SELECT TRUNCATE(10.12,0);
+-------------------+
| TRUNCATE(10.12,0) |
+-------------------+
|                10 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(10.123456,3);
+-----------------------+
| TRUNCATE(10.123456,3) |
+-----------------------+
|                10.123 |
+-----------------------+
1 row in set (0.00 sec)

8、符号函数 SIGN

SIGN (x) 如果 x>0，返回1，如果 x<0 返回 -1。=0，返回0

9、幂运算函数 POW、POWER、EXP

POW (x,y)   对于 x 进行幂运算，x 的 y次方
POWER (x,y)  同上
EXP(y)  计算 e 的 y 次幂  ( e 自然常数 )

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

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

10、对数函数 LOG LOG10 与 POW 联用

LOG(x)  表示以 e 为底 x 的对数
LOG10(X)  表示以 10 为底 x 的对数

mysql> SELECT LOG(7.38905609893065);
+-----------------------+
| LOG(7.38905609893065) |
+-----------------------+
|                     2 |
+-----------------------+

mysql> SELECT LOG10(10000);
+--------------+
| LOG10(10000) |
+--------------+
|            4 |
+--------------+

二、字符串函数

1、计算字符串长度的函数 LENGTH

CHAR_LENGTH (S)    计算字符个数
LENGTH (S)		  计算字符串字节长度
mysql> SELECT CHAR_LENGTH('abc');
+--------------------+
| CHAR_LENGTH('abc') |
+--------------------+
|                  3 |
+--------------------+

mysql> SELECT CHAR_LENGTH('你好');
+-----------------------+
| CHAR_LENGTH('你好')   |
+-----------------------+
|                     2 |
+-----------------------+

mysql> SELECT LENGTH('你好');
+------------------+
| LENGTH('你好')   |
+------------------+
|                6 |
+------------------+

2、链接函数 CONCAT

CONCAT (s1,s2...)			将连接函数中的字符串合并显示
CONCAT_WS (x,s1,s2...)		将 x 作为分隔符把 s1,s2.. 合并进行显示

mysql> SELECT CONCAT('My','SQL','5.5.22');
+-----------------------------+
| CONCAT('My','SQL','5.5.22') |
+-----------------------------+
| MySQL5.5.22                 |
+-----------------------------+

mysql> SELECT CONCAT_WS('-','My','SQL','5.5.22');
+------------------------------------+
| CONCAT_WS('-','My','SQL','5.5.22') |
+------------------------------------+
| My-SQL-5.5.22                      |
+------------------------------------+

--------
mysql> CREATE TABLE c_t (name VARCHAR(30),version VARCHAR(30));

mysql> INSERT INTO c_t VALUES  ('MySQL','5.5.22');

mysql> ALTER TABLE c_t ADD test_ct VARCHAR(30);

mysql> UPDATE c_t SET test_ct=CONCAT(name,version) WHERE name='Mysql';

mysql> select * from c_t;
+-------+---------+-------------+
| name  | version | test_ct     |
+-------+---------+-------------+
| MySQL | 5.5.22  | MySQL5.5.22 |
+-------+---------+-------------+
----

mysql> INSERT INTO c_t VALUES ('Redis','4.0.6',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from c_t;
+-------+---------+-------------+
| name  | version | test_ct     |
+-------+---------+-------------+
| MySQL | 5.5.22  | MySQL5.5.22 |
| Redis | 4.0.6   | NULL        |
+-------+---------+-------------+
2 rows in set (0.00 sec)

mysql> UPDATE c_t SET test_ct=CONCAT(name,version);

mysql> select * from c_t;
+-------+---------+-------------+
| name  | version | test_ct     |
+-------+---------+-------------+
| MySQL | 5.5.22  | MySQL5.5.22 |
| Redis | 4.0.6   | Redis4.0.6  |
+-------+---------+-------------+
---
mysql> UPDATE c_t SET test_ct=CONCAT_WS('-',name,version);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from c_t;
+-------+---------+--------------+
| name  | version | test_ct      |
+-------+---------+--------------+
| MySQL | 5.5.22  | MySQL-5.5.22 |
| Redis | 4.0.6   | Redis-4.0.6  |
+-------+---------+--------------+
2 rows in set (0.00 sec)

/* NULL 如果元素写入CONCAT_WS mysql会把NULL 忽略，如果作为分隔符，返回NULL */
mysql> SELECT CONCAT_WS('-','A','B',NULL,'C')
    -> ;
+---------------------------------+
| CONCAT_WS('-','A','B',NULL,'C') |
+---------------------------------+
| A-B-C                           |
+---------------------------------+
1 row in set (0.00 sec)

3、空格函数 SPACE

SPACE（n） 生成指定数量的空格
mysql> SELECT SPACE(30);
+--------------------------------+
| SPACE(30)                      |
+--------------------------------+
|                                |
+--------------------------------+

mysql> SELECT LENGTH(SPACE(30));
+-------------------+
| LENGTH(SPACE(30)) |
+-------------------+
|                30 |
+-------------------+

4、替换字符串函数 INSERT

INSERT (s1,x,length,s2) 返回字符串 s1从x的位置进行替换，使用s2替换length长度

/* 如果 x 超过了字符串长度，返回原始字符串 
   如果 length 长度大于字符串长度，从x开始替换一直到结尾，如果参数中有NULL，返回NULL*/

mysql> SELECT INSERT('tfhzds',2,4,'yjssjk') AS a,
    -> INSERT ('tfhzds',100,4,'yjssjk') AS b,
    -> INSERT ('tfhzds',3,100,'yjssjk') AS c;
    
+----------+--------+----------+
| a        | b      | c        |
+----------+--------+----------+
| tyjssjks | tfhzds | tfyjssjk |
+----------+--------+----------+
1 row in set (0.00 sec)

5、获取指定长度的字符串函数 LEFT,RIGHT

LEFT(s,n)  获取字符串 s 左侧的 n 个字符
RIGHT(s,n)  获取字符串 s 右侧的 n个字符

mysql> SELECT LEFT('yjssjk',3),RIGHT('yjssjk',3);
+------------------+-------------------+
| LEFT('yjssjk',3) | RIGHT('yjssjk',3) |
+------------------+-------------------+
| yjs              | sjk               |
+------------------+-------------------+
1 row in set (0.00 sec)

mysql> insert INTO user VALUES (100000001,'a'),(100000002,'b'),(101000001,'c')
    -> ,(101000002,'d'),(100000003,'e');
Query OK, 5 

mysql> SELECT RIGHT(id,6),name FROM user WHERE id LIKE '100%';
+-------------+------+
| RIGHT(id,6) | name |
+-------------+------+
| 000001      | a    |
| 000002      | b    |
| 000003      | e    |
+-------------+------+
3 rows in set (0.00 sec)

6、填充字符串的函数

LPAD(s1,length,s2) 返回字符串s1,在s1的左侧使用s2填充到len长度
RPAD(s1,length,s2) 返回字符串s1,在s1的右侧使用s2填充到len长度

mysql> SELECT LPAD('hello',4,'?'),LPAD('hello',10,'?');
+---------------------+----------------------+
| LPAD('hello',4,'?') | LPAD('hello',10,'?') |
+---------------------+----------------------+
| hell                | ?????hello           |
+---------------------+----------------------+

/* 用到识别其他分公司 ，添加标签 */
mysql> UPDATE user SET id=LPAD(id,12,500) WHERE  id LIKE '100%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM user;
+--------------+------+
| id           | name |
+--------------+------+
| 500100000001 | a    |
| 500100000002 | b    |
|    101000001 | c    |
|    101000002 | d    |
| 500100000003 | e    |
+--------------+------+
5 rows in set (0.00 sec)

7、删除空格的函数

LTRIM(S) 删除字符串 s 左边空格
RTRIM(S) 删除字符串 s 右边空格
TRIM(S) 删除字符串 s 两边空格

mysql> SELECT LTRIM('  A=abc  ');
+--------------------+
| LTRIM('  A=abc  ') |
+--------------------+
| A=abc              |
+--------------------+


mysql> SELECT RTRIM('  A=abc  ') as a;
+---------+
| a       |
+---------+
|   A=abc |
+---------+
1 row in set (0.00 sec)


mysql> SELECT RTRIM('  A=abc  ') as a;
+---------+
| a       |
+---------+
|   A=abc |
+---------+
1 row in set (0.00 sec)

8、删除指定字符串的函数

TRIM(s1 FROM s) 删除字符串s两侧的S1

mysql> SELECT TRIM('xy' FROM 'xyzabczyx');
+-----------------------------+
| TRIM('xy' FROM 'xyzabczyx') |
+-----------------------------+
| zabczyx                     |
+-----------------------------+
1 row in set (0.00 sec)
SE
mysql> SELECT TRIM('xy' FROM 'xyzxyabczyx');
+-------------------------------+
| TRIM('xy' FROM 'xyzxyabczyx') |
+-------------------------------+
| zxyabczyx                     |
+-------------------------------+
1 row in set (0.00 sec)

9、重复生成字符串的函数

将字符串 REPEAT(s,n) 。将字符串s重复生成 n 遍

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

mysql> INSERT INTO user VALUES (REPEAT(1,10),'z');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user;
+---------------------+------+
| id                  | name |
+---------------------+------+
|        500100000001 | a    |
|        500100000002 | b    |
|           101000001 | c    |
|           101000002 | d    |
|        500100000003 | e    |
| 1111111111111111111 | z    |
|          1111111111 | z    |
+---------------------+------+
7 rows in set (0.00 sec)

10、替换函数 (员工离职后，交换工作使用)

REPLACE (s1,s2,s3)  将字符串s1中的所有 s2 使用s3 进行替换

mysql> SELECT REPLACE ('www.mysql.com','v','w');
+-----------------------------------+
| REPLACE ('www.mysql.com','v','w') |
+-----------------------------------+
| www.mysql.com                     |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE yeji SET name=REPLACE(name,'zhangsan','wangwu');
mysql> UPDATE yeji SET name=REPLACE(name,'lisi','zhangsan') WHERE id=2;

10、比较字符串大小的函数

STRCMP(s1,s2) 比较s1和s2的大小，如果s1大---'1';如果s2大---'-1',如果相等----'0'
/* 对位比较 ，比较到第一个不相等字符，立马返回 */

mysql> SELECT STRCMP('yjs','YJS'),STRCMP('yjs2','yjs1'),STRCMP('yjs10','yjs2');
+---------------------+-----------------------+------------------------+
| STRCMP('yjs','YJS') | STRCMP('yjs2','yjs1') | STRCMP('yjs10','yjs2') |
+---------------------+-----------------------+------------------------+
|                   0 |                     1 |                     -1 |
+---------------------+-----------------------+------------------------+

11、获取子字符串函数

SUBSTRING (s,n.length)  用于获取指定位置的子字符串，n 获取多少位
MID(s,n,length)  同上
/* 如果，省略length ，将会从n位获取直到末尾，如果n为负数，将倒叙进行获取 */

mysql> SELECT SUBSTRING('yjssjk',4) AS a,
    -> SUBSTRING('yjssjk',2,3) AS b,
    -> SUBSTRING('yjssjk',-3) AS c,
    -> SUBSTRING('yjssjk',-3,2) AS d;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| sjk  | jss  | sjk  | sj   |
+------+------+------+------+

12、匹配子字符串开始位置的函数（返回位置、数值 )

LOCATE(str1,str)  str1 匹配 str，str1在str的第几位
POSITION(str1 IN str)  str1 匹配 str，str1在str的第几位
INSTR(str,str1)  str1匹配 str ，str1在str的第几位
mysql> SELECT LOCATE('yjs','yjssjk'),POSITION('yjs' IN 'yjssjk'),INSTR('yssjk','sjk');
+------------------------+-----------------------------+----------------------+
| LOCATE('yjs','yjssjk') | POSITION('yjs' IN 'yjssjk') | INSTR('yssjk','sjk') |
+------------------------+-----------------------------+----------------------+
|                      1 |                           1 |                    3 |
+------------------------+-----------------------------+----------------------+

13、反转字符串函数

REVERSE(s) 将字符串s进行反转，即头变尾，尾变头

mysql> SELECT REVERSE('abc');
+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+

14、返回指定位置的字符串的函数

ELT(n,s1,s2,....)  用于返回第n个字符串

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

15、返回指定字符串位置的函数

FIELD(s,s1,s2...)  
/* FIELD 作为单次运算，检测到当时返回结果，不能重复运算 */

mysql> SELECT FIELD('hi','hello','world','mysql','hi','hi');
+------------------------------------------+
| FIELD('hi','hello','world','mysql','hi','hi') |
+------------------------------------------+
|                                        4 |
+------------------------------------------+
1 row in set (0.00 sec)

16、返回子字符串位置的函数

FIND_IN_SET(s1,s2)  返回s1 在指定范围s2内 寻找，也是单次运算
mysql> SELECT FIND_IN_SET ('chang','tiao,chang,lanqiu,rap');
+-----------------------------------------------+
| FIND_IN_SET ('chang','tiao,chang,lanqiu,rap') |
+-----------------------------------------------+
|                                             2 |
+-----------------------------------------------+

三、日期时间函数

1、获取当前日期的函数


NOW()
CURDATE()
CURRENT_DATE()

2、获取当前时间的函数

CURTIME()
CURRENT_TIME()

3、显示当前日期或者时间的函数

DATE(date)
TIME(time)
mysql> SELECT DATE(now()),time(now());
+-------------+-------------+
| DATE(now()) | time(now()) |
+-------------+-------------+
| 2019-12-09  | 15:06:44    |
+-------------+-------------+

4、获取当前日期和时间的函数

CURRENT_TIMESTAMP()  当前时区时间
LOCALTIME()	 	 	 本地时间
NOW()	   			当前时间
SYSDATE()  			系统时间
mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2019-12-09 15:08:48 | 2019-12-09 15:08:48 | 2019-12-09 15:08:48 | 2019-12-09 15:08:48 |
+---------------------+---------------------+---------------------+---------------------+

5、获取时间戳的函数

UNIX_TIMESTAMP()
/* 返回 时区范围内的多少秒数 */

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1575875638 |
+------------------+

mysql> SELECT UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP('1999-01-31 21:30:00');
+-------------------------------------------------------------+
| UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP('1999-01-31 21:30:00') |
+-------------------------------------------------------------+
|                                                   658088185 |
+-------------------------------------------------------------+

6、转换时间戳的函数

FROM_UNIXTIME()
/* 精确到秒数，将时间戳转换成日期时间 */

mysql> SELECT FROM_UNIXTIME(1575877828);
+---------------------------+
| FROM_UNIXTIME(1575877828) |
+---------------------------+
| 2019-12-09 15:50:28       |
+---------------------------+

7、获取UTC日期和时间的函数（UTC世界标准时间）

UTC_DATE()
UTC_TIME()

mysql> select UTC_DATE(),UTC_TIME();
+------------+------------+
| UTC_DATE() | UTC_TIME() |
+------------+------------+
| 2019-12-09 | 07:52:24   |
+------------+------------+

8、获取月份的函数

MONTH(date) 获取月份对应的序号
MONTHNAME(date)	 获取月份对应的英文

mysql> SELECT MONTH(now());
+--------------+
| MONTH(now()) |
+--------------+
|           12 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME(now());
+------------------+
| MONTHNAME(now()) |
+------------------+
| December         |
+------------------+

9、获取星期的函数

DAYNAME(date)  		返回日期对应的工作日的英文名
DAYOFWEEK(date)		返回日期对应的一周中的位置，周日为1，依次类推
WEEKDAY(date)		返回日期对应的一周中的位置，周一为0，依次类推
WEEK(date)			返回日期在一年中是第多少周，以0记为第一周
WEEKOFYEAR(date)	返回日期在一年中是第多少周，以1记为第一周

mysql> SELECT DAYNAME(NOW()),dayofweek(NOW()),WEEKDAY(NOW()),WEEK(NOW()),WEEKOFYEAR(NOW());
+----------------+------------------+----------------+-------------+-------------------+
| DAYNAME(NOW()) | dayofweek(NOW()) | WEEKDAY(NOW()) | WEEK(NOW()) | WEEKOFYEAR(NOW()) |
+----------------+------------------+----------------+-------------+-------------------+
| Monday         |                2 |              0 |          49 |                50 |
+----------------+------------------+----------------+-------------+-------------------+

10、获取天数的函数

DAYOFYEAR(date)  返回指定日期在一年中是第几天
DAYOFMONTH(date)  返回指定日期在一月中是第几天
mysql> SELECT DAYOFYEAR(NOW()),DAYOFMONTH(NOW());
+------------------+-------------------+
| DAYOFYEAR(NOW()) | DAYOFMONTH(NOW()) |
+------------------+-------------------+
|              343 |                 9 |
+------------------+-------------------+
1 row in set (0.00 sec)

11、获取年份的函数

YEAR(date)

12、获取季度的函数

QUARTER(date)
mysql> SELECT QUARTER(NOW());
+----------------+
| QUARTER(NOW()) |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

13、获取小时、分钟、秒钟的函数

HOUR(time)
MINUTE(time)
SECOND(time) 

mysql> SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
+-------------+---------------+---------------+
| HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+-------------+---------------+---------------+
|          16 |            17 |            55 |
+-------------+---------------+---------------+

14、获取日期指定值函数

EXTRACT(type FROM date)

mysql> SELECT EXTRACT(MONTH FROM NOW()) AS A,
    -> EXTRACT(DAY FROM NOW()) AS B,
    -> EXTRACT(YEAR_MONTH FROM NOW()) AS C;
+------+------+--------+
| A    | B    | C      |
+------+------+--------+
|   12 |   10 | 201912 |
+------+------+--------+

15、时间和秒钟互相转换的函数

TIME_TO_SEC(time)  将时间转换为秒数
SEC_TO_TIME(time)  将秒数转换为时间
mysql> SELECT SEC_TO_TIME(48894),TIME_TO_SEC('24:00:00');
+--------------------+-------------------------+
| SEC_TO_TIME(48894) | TIME_TO_SEC('24:00:00') |
+--------------------+-------------------------+
| 13:34:54           |                   86400 |
+--------------------+-------------------------+

16、对日期和时间进行操作的函数

DATE_ADD(date,INTERVAL expr type) 对日期进行加运算  INTERVAL(间隔)
ADDDATE(date,INTERVAL expr type) 同上
DATE_SUB(date,INTERVAL expr type) 对日期进行减运算
SUBDATE(date,INTERVAL expr type) 同上
ADDTIME(date,expr) 对时间进行加运算
SUBTIME(date,expr) 对时间进行减运算
DATEDIFF(date1，date2) 计算两个日期之间的间隔,DATE1大返回正数，DATE2大返回负数

/* DATE_ADD */
mysql> SELECT DATE_ADD('2019-12-31 23:59:59',INTERVAL 1 SECOND);
+---------------------------------------------------+
| DATE_ADD('2019-12-31 23:59:59',INTERVAL 1 SECOND) |
+---------------------------------------------------+
| 2020-01-01 00:00:00                               |
+---------------------------------------------------+

mysql> SELECT DATE_ADD('2019-12-31 23:59:59',INTERVAL '1:1:1' HOUR_SECOND);
+--------------------------------------------------------------+
| DATE_ADD('2019-12-31 23:59:59',INTERVAL '1:1:1' HOUR_SECOND) |
+--------------------------------------------------------------+
| 2020-01-01 01:01:00                                          |
+--------------------------------------------------------------+


/* ADDTIME */
mysql> SELECT ADDTIME(NOW(),'1 1:1');
+------------------------+
| ADDTIME(NOW(),'1 1:1') |
+------------------------+
| 2019-12-11 14:52:23    |
+------------------------+

/* DATEDIFF */
mysql> SELECT DATEDIFF('2019-12-10','2020-01-01');
+-------------------------------------+
| DATEDIFF('2019-12-10','2020-01-01') |
+-------------------------------------+
|                                 -22 |
+-------------------------------------+

17、对日期或者时间进行格式化的函数

DATE_FORMAT(date,format)  将指定的日期进行格式化
TIME_FORMAT(date,format)  将指定的时间进行格式化
GET_FORMAT(value_type,format_type)  将数据格式化成想要的类型

/* DATE_FORMAT */
%W(大) 工作日对应星期的英文名
%w(小) 工作日对应星期的序号
%M(大) 工作月对应的英文名
%m(小) 工作月对应的序号
%Y(大) 年份（四位）
%y(小) 年份（两位）
语法:
SELECT  DATE_FORMAT(now(),'%Y-%m,%w');

/* TIME_FORMAT */
%H(大)  以两位数形式显示24小时制
%k(小)  以一位数（不满10）形式显示24小时制
%I(大)  以两位数形式显示12小时制
mysql> SELECT TIME_FORMAT('8:00:00','%H %k %I');
+-----------------------------------+
| TIME_FORMAT('8:00:00','%H %k %I') |
+-----------------------------------+
| 08 8 08                           |
+-----------------------------------+

/* GET_FORMAT */
'将现有日期显示为美国的时间格式'
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'EUR'));
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'EUR')) |
+-------------------------------------------+
| 10.12.2019                                |
+-------------------------------------------+'

'将现有日期显示为日本的时间格式'
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'JIS'));
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'JIS')) |
+-------------------------------------------+
| 2019-12-10                                |
+-------------------------------------------+

'将现有日期显示为国际化的显示格式'
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'INTERNAL'));
+------------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'INTERNAL')) |
+------------------------------------------------+
| 20191210                                       |
+------------------------------------------------+

/* 只获取年月 */

mysql> SELECT DATE_FORMAT(NOW(),'%Y.%m')

四、判断函数

1、IF 条件判断

/* 结果和v1，v2中写入的数据没有直接关系 */
IF (expr,v1,v2) 如果expr为true返回v1，否则返回v2

mysql> SELECT IF(1+1>2,'ok','no'),IF(1+1=2,'yes','rang');
+---------------------+------------------------+
| IF(1+1>2,'ok','no') | IF(1+1=2,'yes','rang') |
+---------------------+------------------------+
| no                  | yes                    |
+---------------------+------------------------+

2、IF NULL 判断是否为空

IFNULL(v1,v2) 如果v1不为null,返回v1，如果为空，返回v2
mysql> SELECT IFNULL(5,10),IFNULL(NULL,10);
+--------------+-----------------+
| IFNULL(5,10) | IFNULL(NULL,10) |
+--------------+-----------------+
|            5 |              10 |
+--------------+-----------------+

3、CASE 选择判断

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2...] [ELSE rn] END 
对于 expr 进行判断，如果符合v1返回r1;如果符合v2返回r2;如果都不满足返回ELSE中的 rn。

mysql> SELECT CASE 1+1 WHEN 1 THEN 'ERROR' WHEN 2 THEN 'RIGHT' ELSE 'REQUEST' END;
+----------------------------------------------------------------+
| CASE 1+1 WHEN 1 THEN 'ERROR' WHEN 2 THEN 'RIGHT' ELSE 'REQUEST' END |
+----------------------------------------------------------------+
| RIGHT                                                          |
+----------------------------------------------------------------+

/* 判断性别 */
mysql> SELECT CASE WHEN sex=1 THEN '男' WHEN sex=0 THEN '女' ELSE '其他' END FROM class;
+--------------------------------------------------------------------+
| CASE WHEN sex=1 THEN '男' WHEN sex=0 THEN '女' ELSE '其他' END     |
+--------------------------------------------------------------------+
| 其他                                                               |
| 女                                                                 |
| 男                                                                 |
+--------------------------------------------------------------------+
mysql> SELECT *,CASE WHEN sex=1 THEN '男' WHEN sex=0 THEN '女' ELSE '其他' END AS sex FROM class;
+----+------+------+--------+
| id | name | sex  | sex    |
+----+------+------+--------+
|  1 | ljj  | 3    | 其他   |
|  2 | ckx  | 0    | 女     |
|  3 | bg   | 1    | 男     |
+----+------+------+--------+

五、系统函数

1、系统版本号函数

VERSION()

2、查看当前用户链接ID的函数

CONNECTION_ID() 连接mysql的次数

mysql> SHOW PROCESSLIST;
+----+-------+-----------+------+---------+------+-------+------------------+
| Id | User  | Host      | db   | Command | Time | State | Info             |
+----+-------+-----------+------+---------+------+-------+------------------+
|  1 | root  | localhost | NULL | Query   |    0 | init  | SHOW PROCESSLIST |
|  3 | user1 | localhost | NULL | Sleep   |   17 |       | NULL             |
+----+-------+-----------+------+---------+------+-------+------------------+

3、查看用户连接信息的函数

SHOW PROCESSLIST
KILL 用户ID  强制指定用户下线

mysql> SHOW PROCESSLIST;
+----+-------+-----------+------+---------+------+-------+------------------+
| Id | User  | Host      | db   | Command | Time | State | Info             |
+----+-------+-----------+------+---------+------+-------+------------------+
|  1 | root  | localhost | NULL | Query   |    0 | init  | SHOW PROCESSLIST |
|  3 | user1 | localhost | NULL | Sleep   |   17 |       | NULL             |
+----+-------+-----------+------+---------+------+-------+------------------+

mysql> KILL 3;
/* 根据ID 杀死用户登录进程 */

4、查看当前使用的数据库的函数

DATABASE()
SCHEMA()

5、查看当前用户名登录的函数

USER()
CURRENT_USER()
SYSTEM_USER()

mysql> SELECT USER(),CURRENT_USER(),system_user();
+-----------------+-----------------+-----------------+
| USER()          | CURRENT_USER()  | system_user()   |
+-----------------+-----------------+-----------------+
| user1@localhost | user1@localhost | user1@localhost |
+-----------------+-----------------+-----------------+

6、查看字符集的函数

CHARSET()

/* 查看Mysql所支持的字符集*/
SHOW CHARSET;

/* 创建库时指定字符集 */
CREATE DATABASE 库名 DEFAULT CHARACTER SET=字符集;

/* 创表时指定字符集 */
CREATE TABLE 表名(字段1属性,字段2属性...) DEFAULT CHARACTER SET=字符集;

6-1、改变字符集的函数

CHARSET(CONVERT(...USING...))  临时修改

mysql> SELECT CHARSET(CONVERT('abc' USING latin1));
+--------------------------------------+
| CHARSET(CONVERT('abc' USING latin1)) |
+--------------------------------------+
| latin1                               |
+--------------------------------------+

7、改变数据类型的函数

CAST(x AS type) 将 x 以指定的 type类型显示，不支持VARCHAR
CONVERT(x,type) 同上

mysql> SELECT CAST(50 AS CHAR(2));
+----------------------+
| CAST(500 AS CHAR(2)) |
+----------------------+
| 50                   |
+----------------------+

8、查看字符集排序规则的函数

COLLATION(tar)
mysql> SELECT CHARSET('abc'),COLLATION('abv');
+----------------+------------------+
| CHARSET('abc') | COLLATION('abv') |
+----------------+------------------+
| utf8           | utf8_general_ci  |
+----------------+------------------+
1 row in set (0.00 sec)

9、重复执行指定操作的函数

BENCHMARK(count,expr) 用于计算服务器性能的函数，检测速度，做的操作越短效率越高

mysql> SELECT BENCHMARK(500000,PASSWORD('123.COM'));
+---------------------------------------+
| BENCHMARK(500000,PASSWORD('123.COM')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.07 sec)

六、加密解密函数

1、HASH加密

PASSWORD(str) 将str 进行HASH 加密  （密码）
mysql> SELECT MD5('123.COM');
+----------------------------------+
| MD5('123.COM')                   |
+----------------------------------+
| b6bdeecca9b752d2759a279d538e03f9 |
+----------------------------------+

2、MD5 加密函数

MD5(str) 将 Str 进行md5加密，变成32位十六进制字符串  （软件）

mysql> SELECT MD5('123.COM');
+----------------------------------+
| MD5('123.COM')                   |
+----------------------------------+
| b6bdeecca9b752d2759a279d538e03f9 |
+----------------------------------+

3、ENCODE、DECODE函数

ENCODE(str,passwd_str) 
使用 passwd_str作为密码，加密str，变成二进制数据，可逆转  （二级密码）

案例1：
mysql> SELECT ENCODE('yjs','123.com');
+-------------------------+
| ENCODE('yjs','123.com') |
+-------------------------+
| ә񞞠                    |
+-------------------------+

DECODE(crpty_str,passwd_str) 
使用 passwd-str 解密之前加密过的 crypt_str
mysql> SELECT DECODE(ENCODE('yjs','123.com'),'123.com');
+-------------------------------------------+
| DECODE(ENCODE('yjs','123.com'),'123.com') |
+-------------------------------------------+
| yjs                                       |
+-------------------------------------------+

案例2：
mysql> CREATE TABLE jiami( event1 VARCHAR(255), pswd BLOB);

mysql> INSERT INTO jiami VALUES (1,ENCODE('jintianhenkaxin','123.com'));

mysql> SELECT * FROM jiami;
+--------+-----------------+
| event1 | pswd            |
+--------+-----------------+
| 1      | ʼwA	¼ǂ񁵠        |
+--------+-----------------+
mysql> SELECT DECODE(pswd,'123.com') FROM jiami;
+------------------------+
| DECODE(pswd,'123.com') |
+------------------------+
| jintianhenkaxin        |
+------------------------+

4、加锁函数解锁函数（底层编程使用）

GET_LOCK(str,timeout) 使用 str得到一个锁，超时时间为 timeout 秒
	1、如果成功得到锁，返回1
	2、如果操作超时，返回0
	3、如果出现错误，返回NULL
RELEASE_LOCK(str) 解锁
	1、如果解锁成功，得到1
	2、如果'该线程尚未创建锁'，返回0
	3、如果锁不存在返回NULL 
注意:提前解除锁等同于锁不存在

IS_FREE_LOCK(str)
	1、如果可用返回1
	2、如果正被使用，返回0
	3、如果参数出错返回NULL
IS_USED_LOCK(str) 判断名为str的锁是否正被使用，如果被使用，返回该用户的标识符，如果没被使用返回NULL

/* 如果该用户断开和mysql的连接或者强制下线，加过的锁自动被释放 */

mysql> SELECT GET_LOCK('yjs',10);
+--------------------+
| GET_LOCK('yjs',10) |
+--------------------+
|                  1 |
+--------------------+
mysql> SELECT RELEASE_LOCK('yjs');
+---------------------+
| RELEASE_LOCK('yjs') |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT IS_USED_LOCK('yjs1');
+----------------------+
| IS_USED_LOCK('yjs1') |
+----------------------+
|                    2 |
+----------------------+

七、进制函数

1、格式化函数

FORMAT(x,n) 将x进行格式化，结果以四舍五入的形式保留小数点后面N位，结果以字符串形式返回

mysql> SELECT FORMAT(13.454553,4),FORMAT(1.3456,3),FORMAT(1.2,3);
+---------------------+------------------+---------------+
| FORMAT(13.454553,4) | FORMAT(1.3456,3) | FORMAT(1.2,3) |
+---------------------+------------------+---------------+
| 13.4546             | 1.346            | 1.200         |
+---------------------+------------------+---------------+

2、不同进制的数据进行互相转换的函数

CONV(要转换的数据，该数据的进制，转换成的数据)

mysql> SELECT CONV(16,16,10);
+----------------+
| CONV(16,16,10) |
+----------------+
| 22             |
+----------------+

3、IP地址和数值互换的函数

INET_ATON(expr) IP地址 转换 数值
INET_NTOA(expr)  数值 转换 IP地址
mysql> SELECT INET_ATON('255.255.255.255');
+------------------------------+
| INET_ATON('255.255.255.255') |
+------------------------------+
|                   4294967295 |
+------------------------------+


mysql> SELECT INET_NTOA('4294967295');
+-------------------------+
| INET_NTOA('4294967295') |
+-------------------------+
| 255.255.255.255         |
+-------------------------+

 清欢渡.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql 函数

MySQL 开发常用函数一、数学类函数二、字符串函数三、日期时间函数四、判断函数五、系统函数六、加密解密函数七、进制函数一、数学类函数ABS(x) 绝对值函数PI() 圆周率函数SQRT(x) 平方根函数FLOOR(x) CEIL(x) CEILING(x) 取整函数RAND() \ RAND(x) 随机数函数(0~1),(x) 取固定值ROUND(x,y) \ ROUND(x) 四舍五入函数，(x) 保留整数TRUNCATE(x,y) 截取数值函数SIGN(x) 符号函数 x
复制链接

扫一扫

专栏目录