mysql 常用函数

作者: 缓慢移动的蜗牛 | 来源:发表于2017-03-15 17:53 被阅读0次

前言： 可以用这样的命令查看帮助

mysql> ? from_unixtime
mysql> ? select
//查看表信息的命令：
mysql>desc table_name
mysql>show create table table_name \G;   \G选项的使得记录能够按照字段竖向排列，以便更好地显示较长内容。

字符串函数

函数	功能
CONCAT(S1,S2,...Sn)	连接S1,S2...Sn为一个字符串
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str)	将字符串str中的所有字符变为小写
UPPER(str)	将字符串str中的所有字符变为大写
LEFT(str,x)	返回字符串str最左边的x字符
RIGHT(str,x)	返回字符串str最右边的x字符
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串str行尾的空格
REPEAT(str,x)	返回str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)	返回从字符串str x位置起y个字符长度的子串
REPEAT(str,count)	返回一个由字符串str重复count次数组成的字符串

CONCAT(S1,S2,...Sn) 函数：把传入的参数连接成一个字符串

mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);
//任何字符串与NULL进行连接的结果都将是NULL
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+

INSERT(str,x,y,instr) 函数：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr

mysql>select INSERT('beijing2017you',12,3,'me');
+------------------------------------+
 | INSERT('beijing2017you',12,3,'me') |
 +------------------------------------+
 | beijing2017me                      |
 +------------------------------------+

LOWER(str)和UPPER(str) 函数：将字符串转换为小写或大写

mysql>select LOWER('BEIJING2017'),UPPER('beijing2017');
+----------------------+----------------------+
| LOWER('BEIJING2017') | UPPER('beijing2017') |
+----------------------+----------------------+
| beijing2017          | BEIJING2017          |
+----------------------+----------------------+

LEFT(str,x)和RIGHT(str,x)函数：分别返回字符串最左边的x个字符和最右边的x字符。如果第二个参数为NULL，那么将不返回任何字符串

mysql> select LEFT('beijing2017',7),LEFT('beijing',null),RIGHT('beijing2017',4);
+-----------------------+----------------------+------------------------+
| LEFT('beijing2017',7) | LEFT('beijing',null) | RIGHT('beijing2017',4) |
+-----------------------+----------------------+------------------------+
| beijing               | NULL                 | 2017                   |
+-----------------------+----------------------+------------------------+

LPAD(str,n,pad)和RPAD(str,n,pad)函数：用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度

mysql> select LPAD('2017',20,'beijing'),RPAD('beijing',20,'2017');
+---------------------------+---------------------------+
| LPAD('2017',20,'beijing') | RPAD('beijing',20,'2017') |
+---------------------------+---------------------------+
| beijingbeijingbe2017      | beijing2017201720172      |
+---------------------------+---------------------------+

LTRIM(str)和RTRIM(str)函数：去掉字符串str左侧和右侧空格

mysql> select LTRIM('  |beijing'),RTRIM('beijing|   ');
+---------------------+----------------------+
| LTRIM('  |beijing') | RTRIM('beijing|   ') |
+---------------------+----------------------+
| |beijing            | beijing|             |
+---------------------+----------------------+

REPEAT(str,x)函数：返回str重复x次的结果

mysql> select REPEAT('mysql',3);
+-------------------+
| REPEAT('mysql',3) |
+-------------------+
| mysqlmysqlmysql   |
+-------------------+

REPLACE(str,a,b)函数：用字符串b替换字符串str中所有出现的字符串a

mysql> select REPLACE('beijing_2017','_2017','2008');
+----------------------------------------+
| REPLACE('beijing_2017','_2017','2008') |
+----------------------------------------+
| beijing2008                            |
+----------------------------------------+

STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值大小

mysql> select STRCMP('a','b'),STRCMP('b','b'),STRCMP('c','b');
+-----------------+-----------------+-----------------+
| STRCMP('a','b') | STRCMP('b','b') | STRCMP('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+

TRIM(str)函数：去掉目标字符串的开头和结尾的空格

mysql> select TRIM('  $ beijing2017  $   ');
+-------------------------------+
| TRIM('  $ beijing2017  $   ') |
+-------------------------------+
| $ beijing2017  $              |
+-------------------------------+

SUBSTRING(str,x,y)函数：返回字符串str中第x位置起y个字符长度的子串

mysql> select SUBSTRING('beijing2017',8,4),SUBSTRING('beijing2017',1,7);
+------------------------------+------------------------------+
| SUBSTRING('beijing2017',8,4) | SUBSTRING('beijing2017',1,7) |
+------------------------------+------------------------------+
| 2017                         | beijing                      |
+------------------------------+------------------------------+

数值函数

函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数
FLOOR(x)	返回小于x的最大整数
MOD(x,y)	返回x%y，求余
RAND()	返回0~1内的随机数
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)	返回数字x截断y位小数的结果

ABS(x)函数：返回x的绝对值

mysql> select ABS(-0.8),ABS(0.8);
+-----------+----------+
| ABS(-0.8) | ABS(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+

CEIL(x)函数：返回大于x的最小整数

mysql> select CEIL(-0.8),CEIL(0.8); 
+------------+-----------+
| CEIL(-0.8) | CEIL(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+

FLOOR(x)函数：返回小于x的最大整数

mysql> select FLOOR(-0.8),FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+

MOD(x,y)：返回x/y的模

mysql> select MOD(15,10),MOD(1,11),MOD(NULL,10);
+------------+-----------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+------------+-----------+--------------+
|          5 |         1 |         NULL |
+------------+-----------+--------------+

RAND()：返回0~1内的随机数

mysql> select RAND(),RAND();
+--------------------+--------------------+
| RAND()             | RAND()             |
+--------------------+--------------------+
| 0.6605393799585657 | 0.6978235959055122 |
+--------------------+--------------------+
mysql> select CEIL(100*RAND()),CEIL(100*RAND());
+------------------+------------------+
| CEIL(100*RAND()) | CEIL(100*RAND()) |
+------------------+------------------+
|               51 |               45 |
+------------------+------------------+

ROUND(x,y)：返回参数x的四舍五入的有y位小数的值

mysql> select ROUND(1.1),ROUND(1.1,2),ROUND(1,2);
+------------+--------------+------------+
| ROUND(1.1) | ROUND(1.1,2) | ROUND(1,2) |
+------------+--------------+------------+
|          1 |         1.10 |          1 |
+------------+--------------+------------+

TRUNCATE(x,y)：返回数字x截断y位小数的结果

mysql> select ROUND(1.235,2),TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
|           1.24 |              1.23 |
+----------------+-------------------+

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX的时间戳
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MMINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期的date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数

CURDATE()，CURTIME()，NOW()

mysql> select CURDATE(),CURTIME(),NOW();
+------------+-----------+---------------------+
| CURDATE()  | CURTIME() | NOW()               |
+------------+-----------+---------------------+
| 2017-03-15 | 17:35:44  | 2017-03-15 17:35:44 |
+------------+-----------+---------------------+

SYSDATE 与 NOW 比较

mysql> SELECT SYSDATE(), NOW();
+---------------------+---------------------+
| SYSDATE()           | NOW()               |
+---------------------+---------------------+
| 2017-08-10 20:44:38 | 2017-08-10 20:44:38 |
+---------------------+---------------------+
# 似乎`SYSDATE()`和[NOW()](http://www.yiibai.com/mysql/now.html)函数都返回一个相同的值，它是执行语句时当前日期和时间。

# 然而，`SYSDATE()`函数实际上返回执行时的时间，而`NOW()`函数返回一个常量时间，该语句开始执行

mysql> SELECT NOW(), SLEEP(5), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(5) | NOW()               |
+---------------------+----------+---------------------+
| 2017-08-10 20:46:51 |        0 | 2017-08-10 20:46:51 |
+---------------------+----------+---------------------+

mysql>  SELECT SYSDATE(), SLEEP(5), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(5) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2017-08-10 20:48:14 |        0 | 2017-08-10 20:48:19 |
+---------------------+----------+---------------------+

UNIX_TIMESTAMP(date)，FROM_UNIXTIME

mysql> select UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1489570679);
+-----------------------+---------------------------+
| UNIX_TIMESTAMP(NOW()) | FROM_UNIXTIME(1489570679) |
+-----------------------+---------------------------+
|            1489570846 | 2017-03-15 17:37:59       |
+-----------------------+---------------------------+

WEEK(date) ，MONTHNAME(date),YEAR(date)

mysql> select WEEK(NOW()),MONTHNAME(NOW()),YEAR(NOW());
+-------------+------------------+-------------+
| WEEK(NOW()) | MONTHNAME(NOW()) | YEAR(NOW()) |
+-------------+------------------+-------------+
|          11 | March            |        2017 |
+-------------+------------------+-------------+

DATE_FORMAT(date,fmt)

更详细的

mysql> select DATE_FORMAT(NOW(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%M,%D,%Y') |
+-------------------------------+
| March,15th,2017               |
+-------------------------------+
1 row in set (0.01 sec)
//接一个常用的转换例子
mysql>SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

DATE_ADD(date,INTERVAL expr type)

期中INTERVAL是间隔类型关键字，expr是一个表达式，这个表达式对应后面的类型，type是间隔类型，MYSQL提供了13中间隔类型，如表

表达式类型	描述	格式
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分和秒	mm:ss

示例：

第一列返回了当前日期时间，第二列返回距离当前日期31天后的日期时> 间，第三列返回距离当前日期一年两个月后的日期时间

mysql> select NOW() current,DATE_ADD(NOW(),INTERVAL 31 day) after31days,DATE_ADD(NOW(),INTERVAL '1_2' year_month) after_oneyear_twomonth; 
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2017-03-15 19:03:33 | 2017-04-15 19:03:33 | 2018-05-15 19:03:33    |
+---------------------+---------------------+------------------------+

第一列返回当期日期时间，第二列返回距离当前日期31天前的日期时间，第三列返回距离当前日期一年两个月前的日期时间

mysql> select NOW() current,DATE_ADD(NOW(),INTERVAL -31 day) before31days,DATE_ADD(NOW(),INTERVAL '-1_-2' year_month) before_oneyear_twomoth; 
+---------------------+---------------------+------------------------+
| current             | before31days        | before_oneyear_twomoth |
+---------------------+---------------------+------------------------+
| 2017-03-15 19:06:55 | 2017-02-12 19:06:55 | 2016-01-15 19:06:55    |
+---------------------+---------------------+------------------------+

DATEDIFF(expr,expr2)

mysql> select DATEDIFF('2017-01-01',NOW());
+------------------------------+
| DATEDIFF('2017-01-01',NOW()) |
+------------------------------+
|                          -73 |
+------------------------------+

流程函数

函数	功能
IF(value,t,f)	如果value是真，返回t；否则返回f
IFNULL(value1,value2)	如果value1 不为空，返回value1,否则返回value2
CASE WHEN [value1] THEN [result1]...ELSE [default] END	如果value1 是真，返回result1，否则返回default
CASE [expr] WHEN [value1] THEN [result1] ...ELSE[default] END	如果expr等于valule1，返回result1，否则返回default

create table salary(userid int,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);

IF(value,t,f)

mysql> select IF(salary>2000,'high','low') from salary;
+------------------------------+
| IF(salary>2000,'high','low') |
+------------------------------+
| low                          |
| low                          |
| high                         |
| high                         |
| high                         |
| low                          |
+------------------------------+

IFNULL(value1,value2)

mysql> select IFNULL(salary,0) from salary;
+------------------+
| IFNULL(salary,0) |
+------------------+
|          1000.00 |
|          2000.00 |
|          3000.00 |
|          4000.00 |
|          5000.00 |
|             0.00 |
+------------------+

case when函数

mysql> select CASE WHEN salary<=2000  THEN 'low' ELSE 'high' end from salary;
+----------------------------------------------------+
| CASE WHEN salary<=2000  THEN 'low' ELSE 'high' end |
+----------------------------------------------------+
| low                                                |
| low                                                |
| high                                               |
| high                                               |
| high                                               |
| high                                               |
+----------------------------------------------------+
mysql> select CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' end from salary;
+-----------------------------------------------------------------------+
| CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' end |
+-----------------------------------------------------------------------+
| low                                                                   |
| mid                                                                   |
| high                                                                  |
| high                                                                  |
| high                                                                  |
| high                                                                  |
+-----------------------------------------------------------------------+

case when 更多示例

其他常用函数

函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA(num)	返回当前数字代表的IP地址
PASSWORD(str)	返回字符串str的加密版本
MD5(str)	返回字符串str的MD5值

mysql> select DATABASE(),VERSION(),USER();
+------------+-----------+----------------+
| DATABASE() | VERSION() | USER()         |
+------------+-----------+----------------+
| test       | 5.7.17    | root@localhost |
+------------+-----------+----------------+

mysql> select INET_ATON('192.168.1.1'),INET_NTOA(3232235777);
+--------------------------+-----------------------+
| INET_ATON('192.168.1.1') | INET_NTOA(3232235777) |
+--------------------------+-----------------------+
|               3232235777 | 192.168.1.1           |
+--------------------------+-----------------------+

mysql> select PASSWORD('adf_2345DD'),MD5('123456');
+-------------------------------------------+----------------------------------+
| PASSWORD('adf_2345DD')                    | MD5('123456')                    |
+-------------------------------------------+----------------------------------+
| *BD1258F8130C30F4EA90715B5CE817C6D7773FE2 | e10adc3949ba59abbe56e057f20f883e |
+-------------------------------------------+----------------------------------+

mysql 常用函数

字符串函数

CONCAT(S1,S2,...Sn) 函数：把传入的参数连接成一个字符串

INSERT(str,x,y,instr) 函数：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr

LOWER(str)和UPPER(str) 函数：将字符串转换为小写或大写

LEFT(str,x)和RIGHT(str,x)函数：分别返回字符串最左边的x个字符和最右边的x字符。如果第二个参数为NULL，那么将不返回任何字符串

LPAD(str,n,pad)和RPAD(str,n,pad)函数：用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度

LTRIM(str)和RTRIM(str)函数：去掉字符串str左侧和右侧空格

REPEAT(str,x)函数：返回str重复x次的结果

REPLACE(str,a,b)函数：用字符串b替换字符串str中所有出现的字符串a

STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值大小

TRIM(str)函数：去掉目标字符串的开头和结尾的空格

SUBSTRING(str,x,y)函数：返回字符串str中第x位置起y个字符长度的子串

数值函数

ABS(x)函数：返回x的绝对值

CEIL(x)函数：返回大于x的最小整数

FLOOR(x)函数：返回小于x的最大整数

MOD(x,y)：返回x/y的模

RAND()：返回0~1内的随机数

ROUND(x,y)：返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y)：返回数字x截断y位小数的结果

日期函数

CURDATE()，CURTIME()，NOW()

SYSDATE 与 NOW 比较

UNIX_TIMESTAMP(date)，FROM_UNIXTIME

WEEK(date) ，MONTHNAME(date),YEAR(date)

DATE_FORMAT(date,fmt)

DATE_ADD(date,INTERVAL expr type)

DATEDIFF(expr,expr2)

流程函数

IF(value,t,f)

IFNULL(value1,value2)

case when函数

其他常用函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

mysql 常用函数

字符串函数

CONCAT(S1,S2,...Sn) 函数：把传入的参数连接成一个字符串

INSERT(str,x,y,instr) 函数：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr

LOWER(str)和UPPER(str) 函数：将字符串转换为小写或大写

LEFT(str,x)和RIGHT(str,x)函数：分别返回字符串最左边的x个字符和最右边的x字符。如果第二个参数为NULL，那么将不返回任何字符串

LPAD(str,n,pad)和RPAD(str,n,pad)函数：用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度

LTRIM(str)和RTRIM(str)函数：去掉字符串str左侧和右侧空格

REPEAT(str,x)函数：返回str重复x次的结果

REPLACE(str,a,b)函数：用字符串b替换字符串str中所有出现的字符串a

STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值大小

TRIM(str)函数：去掉目标字符串的开头和结尾的空格

SUBSTRING(str,x,y)函数：返回字符串str中第x位置起y个字符长度的子串

数值函数

ABS(x)函数：返回x的绝对值

CEIL(x)函数：返回大于x的最小整数

FLOOR(x)函数：返回小于x的最大整数

MOD(x,y)：返回x/y的模

RAND()：返回0~1内的随机数

ROUND(x,y)： 返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y)： 返回数字x截断y位小数的结果

日期函数

CURDATE()，CURTIME()，NOW()

SYSDATE 与 NOW 比较

UNIX_TIMESTAMP(date)，FROM_UNIXTIME

WEEK(date) ，MONTHNAME(date),YEAR(date)

DATE_FORMAT(date,fmt)

DATE_ADD(date,INTERVAL expr type)

DATEDIFF(expr,expr2)

流程函数

IF(value,t,f)

IFNULL(value1,value2)

case when函数

其他常用函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

ROUND(x,y)：返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y)：返回数字x截断y位小数的结果