mysql常用函数
1、format(col,2)格式化浮点数,保留两位小数点
mysql> SELECT FORMAT(12562.6655,2); 结果:12,562.67
mysql> SELECT FORMAT(12332.2,0); 结果:12,332
2、truncate(col,2) 直接截取不四舍五入,保留两位小数点
mysql> SELECT truncate(12562.6655,2); 结果:12562.66
3、CAST(value as type)和CONVERT(value, type) 可用来获取一个类型的值,并产生另一个类型的值
就是CAST(xxx AS 类型), CONVERT(xxx,类型)
mysql> SELECT CAST('1266e342.83' AS signed); 结果:1266
mysql> SELECT CONVERT('1266e342.83', signed); 结果:1266
mysql> SELECT CAST('3.83' AS signed); 结果:3
mysql> SELECT CONVERT('3.83', signed); 结果:3
4、DAYOFWEEK(date) 返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
mysql> select DAYOFWEEK('1988-11-17'); 结果:5(星期四)
mysql> select DAYOFWEEK(CURDATE()); 结果:?
5、WEEKDAY(date) 返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
mysql> select WEEKDAY('1988-11-17'); 结果:3(星期四)
mysql> select WEEKDAY(CURDATE()); 结果:?
6、DAYOFMONTH(date) 返回date是一月中的第几日(在1到31范围内)
mysql> select DAYOFMONTH('1988-11-17'); 结果:17
mysql> select DAYOFMONTH(CURDATE()); 结果:?
7、DAYOFYEAR(date) 返回date是一年中的第几日(在1到366范围内)
mysql> select DAYOFYEAR('1988-11-17'); 结果:322
mysql> select DAYOFYEAR(CURDATE()); 结果:?
8、MONTH(date) 返回date中的月份数值
mysql> select MONTH('1988-11-17'); 结果:11
mysql> select MONTH(CURDATE()); 结果:?
9、DAYNAME(date) 返回date是星期几(按英文名返回)
mysql> select DAYNAME('1988-11-17'); 结果:Thursday(星期四)
mysql> select DAYNAME(CURDATE()); 结果:?
10、MONTHNAME(date) 返回date是几月(按英文名返回)
mysql> select MONTHNAME('1988-11-17'); 结果:November(十一月)
mysql> select MONTHNAME(CURDATE()); 结果:?
11、QUARTER(date) 返回date是一年的第几个季度
mysql> select QUARTER('1988-11-17'); 结果:4 (第4个季度)
mysql> select QUARTER(CURDATE()); 结果:?
12、WEEK(date,first) 返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
mysql> select WEEK('1988-11-17', 0); 结果:46 (第46周)
mysql> select WEEK('1988-11-17', 1); 结果:46 (第46周)
mysql> select WEEK(CURDATE()); 结果:?
13、YEAR(date) 返回date的年份(范围在1000到9999)
mysql> select YEAR('1988-11-17'); 结果:1988
mysql> select YEAR(CURDATE()); 结果:?
14、HOUR(time) 返回time的小时数(范围是0到23)
mysql> select HOUR('10:05:03'); 结果:10
mysql> select HOUR(now()); 结果:?
15、MINUTE(time) 返回time的分钟数(范围是0到59)
mysql> select MINUTE('98-02-03 10:05:03'); 结果:5
mysql> select MINUTE(now()); 结果:?
16、SECOND(time) 返回time的秒数(范围是0到59)
mysql> select SECOND('10:05:03'); 结果:3
mysql> select MINUTE(now()); 结果:?
17、PERIOD_ADD(P,N) 增加N个月到时期P并返回(P的格式YYMM或YYYYMM)
mysql> select PERIOD_ADD(9801,2); 结果:199803
mysql> select PERIOD_ADD(199801,2); 结果:199803
mysql>SELECT PERIOD_ADD(DATE_FORMAT(CURDATE(),'%Y%m'),5); 结果:?
18、UNIX_TIMESTAMP(date) 返回date的时间戳
mysql>SELECT UNIX_TIMESTAMP('1988-11-17 18:20:06'); 结果:595765206
mysql>SELECT UNIX_TIMESTAMP(); 结果:当前时间点的时间戳
19、DATE(date) 返回date的日期(yyyy-MM-dd)
mysql>SELECT CURDATE(); 结果:当天日期(yyyy-MM-dd)
mysql>SELECT CURRENT_DATE(); 结果:当天日期(yyyy-MM-dd)
mysql>SELECT CURRENT_TIME() 结果:当前时间(HH:mm:ss)
mysql>SELECT CURTIME(); 结果:当前时间(HH:mm:ss)
mysql>SELECT NOW(); 结果:当前时间(yyy-MM-dd HH:mm:ss)
mysql>SELECT DATE(now()); 结果:当天日期(yyyy-MM-dd)
mysql>SELECT CURRENT_TIMESTAMP(); 结果:当前时间(yyy-MM-dd HH:mm:ss)
20、FROM_UNIXTIME(unix_timestamp)和FROM_UNIXTIME(unix_timestamp,format) 将时间戳转为日期时间
mysql> FROM_UNIXTIME(1459267635); 结果:2016-03-30 00:07:15
mysql>SELECT FROM_UNIXTIME(1459267635,'%Y-%m-%d %H:%i:%s') 结果:2016-03-30 00:07:15
21、DATE_FORMAT(date,format) 格式化date,返回format部分
mysql>SELECT DATE_FORMAT('1988-11-17 18:06:03', '%H:%i') 结果:18:06
22、DATE_ADD(date,INTERVAL expr unit) 对date日期进行增加(年YEAR,月MONTH,日DAY,时HOUR,分MINUTE,秒SECOND)
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 3 YEAR); 结果: 1991-11-17
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 2 MONTH); 结果: 1989-01-17
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 1 DAY); 结果:1988-11-18
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 3 HOUR); 结果: 1988-11-17 03:00:00
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 3 MINUTE); 结果: 1988-11-17 00:03:00
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 60 SECOND); 结果: 1988-11-17 00:01:00
23、DATE_SUB(date,INTERVAL expr unit) 对date日期进行减少(年YEAR,月MONTH,日DAY,时HOUR,分MINUTE,秒SECOND)
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 3 YEAR); 结果: 1985-11-17
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 2 MONTH); 结果: 1988-09-17
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 1 DAY); 结果:1988-11-16
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 3 HOUR); 结果: 1988-11-16 21:00:00
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 3 MINUTE); 结果: 1988-11-16 23:57:00
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 60 SECOND); 结果: 1988-11-16 23:59:00
24、DATEDIFF(expr1,expr2) 将expr1日期与expr2日期做差集。
mysql>SELECT DATEDIFF('2015-12-05','2015-12-03'); 结果:2
25、SEC_TO_TIME(seconds) 以'HH:MM:SS'或HHMMSS格式返回秒数转成的TIME值(根据返回值所处上下文是字符串或数字)
mysql> select SEC_TO_TIME(2378); 结果:00:39:38
26、TIME_TO_SEC(time) 返回time值有多少秒
mysql> select TIME_TO_SEC('22:23:00'); 结果:80580
27、GREATEST(value1,value2,...); 返回输入参数(value1, value2, value3, 等)组的最大值
SELECT GREATEST(1,2,3); 结果:3
SELECT GREATEST('2012-11-28 23:02:03','2015-01-01','2013-06-19 10:59:00'); 结果:2015-01-01
28、IFNULL(expr1,expr2) 如果expr1不是null,则返回expr1,否则返回expr2
mysql>select ifnull(1,0); 结果:1
29、IF(expr1,expr2,expr3) 如果expr1为true,则返回expr2,否则返回expr3
mysql>select if(1=4,2,3); 结果:3
30、GROUP_CONCAT()是MySQL数据库提供的一个函数,通常跟GROUP BY一起用,作用:将多条记录的字段连接在一起组成一个字符串
语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
1、format(col,2)格式化浮点数,保留两位小数点
mysql> SELECT FORMAT(12562.6655,2); 结果:12,562.67
mysql> SELECT FORMAT(12332.2,0); 结果:12,332
2、truncate(col,2) 直接截取不四舍五入,保留两位小数点
mysql> SELECT truncate(12562.6655,2); 结果:12562.66
3、CAST(value as type)和CONVERT(value, type) 可用来获取一个类型的值,并产生另一个类型的值
就是CAST(xxx AS 类型), CONVERT(xxx,类型)
mysql> SELECT CAST('1266e342.83' AS signed); 结果:1266
mysql> SELECT CONVERT('1266e342.83', signed); 结果:1266
mysql> SELECT CAST('3.83' AS signed); 结果:3
mysql> SELECT CONVERT('3.83', signed); 结果:3
4、DAYOFWEEK(date) 返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
mysql> select DAYOFWEEK('1988-11-17'); 结果:5(星期四)
mysql> select DAYOFWEEK(CURDATE()); 结果:?
5、WEEKDAY(date) 返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
mysql> select WEEKDAY('1988-11-17'); 结果:3(星期四)
mysql> select WEEKDAY(CURDATE()); 结果:?
6、DAYOFMONTH(date) 返回date是一月中的第几日(在1到31范围内)
mysql> select DAYOFMONTH('1988-11-17'); 结果:17
mysql> select DAYOFMONTH(CURDATE()); 结果:?
7、DAYOFYEAR(date) 返回date是一年中的第几日(在1到366范围内)
mysql> select DAYOFYEAR('1988-11-17'); 结果:322
mysql> select DAYOFYEAR(CURDATE()); 结果:?
8、MONTH(date) 返回date中的月份数值
mysql> select MONTH('1988-11-17'); 结果:11
mysql> select MONTH(CURDATE()); 结果:?
9、DAYNAME(date) 返回date是星期几(按英文名返回)
mysql> select DAYNAME('1988-11-17'); 结果:Thursday(星期四)
mysql> select DAYNAME(CURDATE()); 结果:?
10、MONTHNAME(date) 返回date是几月(按英文名返回)
mysql> select MONTHNAME('1988-11-17'); 结果:November(十一月)
mysql> select MONTHNAME(CURDATE()); 结果:?
11、QUARTER(date) 返回date是一年的第几个季度
mysql> select QUARTER('1988-11-17'); 结果:4 (第4个季度)
mysql> select QUARTER(CURDATE()); 结果:?
12、WEEK(date,first) 返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
mysql> select WEEK('1988-11-17', 0); 结果:46 (第46周)
mysql> select WEEK('1988-11-17', 1); 结果:46 (第46周)
mysql> select WEEK(CURDATE()); 结果:?
13、YEAR(date) 返回date的年份(范围在1000到9999)
mysql> select YEAR('1988-11-17'); 结果:1988
mysql> select YEAR(CURDATE()); 结果:?
14、HOUR(time) 返回time的小时数(范围是0到23)
mysql> select HOUR('10:05:03'); 结果:10
mysql> select HOUR(now()); 结果:?
15、MINUTE(time) 返回time的分钟数(范围是0到59)
mysql> select MINUTE('98-02-03 10:05:03'); 结果:5
mysql> select MINUTE(now()); 结果:?
16、SECOND(time) 返回time的秒数(范围是0到59)
mysql> select SECOND('10:05:03'); 结果:3
mysql> select MINUTE(now()); 结果:?
17、PERIOD_ADD(P,N) 增加N个月到时期P并返回(P的格式YYMM或YYYYMM)
mysql> select PERIOD_ADD(9801,2); 结果:199803
mysql> select PERIOD_ADD(199801,2); 结果:199803
mysql>SELECT PERIOD_ADD(DATE_FORMAT(CURDATE(),'%Y%m'),5); 结果:?
18、UNIX_TIMESTAMP(date) 返回date的时间戳
mysql>SELECT UNIX_TIMESTAMP('1988-11-17 18:20:06'); 结果:595765206
mysql>SELECT UNIX_TIMESTAMP(); 结果:当前时间点的时间戳
19、DATE(date) 返回date的日期(yyyy-MM-dd)
mysql>SELECT CURDATE(); 结果:当天日期(yyyy-MM-dd)
mysql>SELECT CURRENT_DATE(); 结果:当天日期(yyyy-MM-dd)
mysql>SELECT CURRENT_TIME() 结果:当前时间(HH:mm:ss)
mysql>SELECT CURTIME(); 结果:当前时间(HH:mm:ss)
mysql>SELECT NOW(); 结果:当前时间(yyy-MM-dd HH:mm:ss)
mysql>SELECT DATE(now()); 结果:当天日期(yyyy-MM-dd)
mysql>SELECT CURRENT_TIMESTAMP(); 结果:当前时间(yyy-MM-dd HH:mm:ss)
20、FROM_UNIXTIME(unix_timestamp)和FROM_UNIXTIME(unix_timestamp,format) 将时间戳转为日期时间
mysql> FROM_UNIXTIME(1459267635); 结果:2016-03-30 00:07:15
mysql>SELECT FROM_UNIXTIME(1459267635,'%Y-%m-%d %H:%i:%s') 结果:2016-03-30 00:07:15
21、DATE_FORMAT(date,format) 格式化date,返回format部分
mysql>SELECT DATE_FORMAT('1988-11-17 18:06:03', '%H:%i') 结果:18:06
22、DATE_ADD(date,INTERVAL expr unit) 对date日期进行增加(年YEAR,月MONTH,日DAY,时HOUR,分MINUTE,秒SECOND)
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 3 YEAR); 结果: 1991-11-17
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 2 MONTH); 结果: 1989-01-17
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 1 DAY); 结果:1988-11-18
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 3 HOUR); 结果: 1988-11-17 03:00:00
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 3 MINUTE); 结果: 1988-11-17 00:03:00
mysql>SELECT DATE_ADD('1988-11-17',INTERVAL 60 SECOND); 结果: 1988-11-17 00:01:00
23、DATE_SUB(date,INTERVAL expr unit) 对date日期进行减少(年YEAR,月MONTH,日DAY,时HOUR,分MINUTE,秒SECOND)
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 3 YEAR); 结果: 1985-11-17
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 2 MONTH); 结果: 1988-09-17
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 1 DAY); 结果:1988-11-16
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 3 HOUR); 结果: 1988-11-16 21:00:00
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 3 MINUTE); 结果: 1988-11-16 23:57:00
mysql>SELECT DATE_SUB('1988-11-17',INTERVAL 60 SECOND); 结果: 1988-11-16 23:59:00
24、DATEDIFF(expr1,expr2) 将expr1日期与expr2日期做差集。
mysql>SELECT DATEDIFF('2015-12-05','2015-12-03'); 结果:2
25、SEC_TO_TIME(seconds) 以'HH:MM:SS'或HHMMSS格式返回秒数转成的TIME值(根据返回值所处上下文是字符串或数字)
mysql> select SEC_TO_TIME(2378); 结果:00:39:38
26、TIME_TO_SEC(time) 返回time值有多少秒
mysql> select TIME_TO_SEC('22:23:00'); 结果:80580
27、GREATEST(value1,value2,...); 返回输入参数(value1, value2, value3, 等)组的最大值
SELECT GREATEST(1,2,3); 结果:3
SELECT GREATEST('2012-11-28 23:02:03','2015-01-01','2013-06-19 10:59:00'); 结果:2015-01-01
28、IFNULL(expr1,expr2) 如果expr1不是null,则返回expr1,否则返回expr2
mysql>select ifnull(1,0); 结果:1
29、IF(expr1,expr2,expr3) 如果expr1为true,则返回expr2,否则返回expr3
mysql>select if(1=4,2,3); 结果:3
30、GROUP_CONCAT()是MySQL数据库提供的一个函数,通常跟GROUP BY一起用,作用:将多条记录的字段连接在一起组成一个字符串
语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])