MySQL基础操作--函数

（一）数学函数

1、求余函数MOD(X,Y)

MOD(X,Y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，他返回除法运算后的精确余数

组函数:min(求最小值),max(求最大值),avg(求平均数),sum(求总和),counnt(统计记录数)min(求最小值),max(求最大值),avg(求平均数),sum(求总和),counnt(统计记录数)

注意：要统计一组数据中的条数要用count，sum是用来统计数据字段值加起来的总和。

（二）日期和时间函数

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

CURDATE()、CURRENT_DATE()、CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

以上函数都是返回当前日期和时间值，MYSQL的函数数量的确比SQLSERVER多很多，SQLSERVER获取当前时间和日期用的

2、返回UTC日期的函数和返回UTC时间的函数

SELECT UTC_DATE(),UTC_TIME();

3、获取月份的函数MONTHNAME(DATE)

MONTHNAME(DATE)函数返回日期date对应月份的英文全名

4、获取季度、分钟和秒钟的函数

QUARTER(DATE)返回date对应的一年中的季度值，范围是从1~4

使用QUARTER(DATE)函数返回指定日期对应的季度

5、MINUTE(TIME)返回time对应的分钟数，范围是从0~59

6、SECOND(time) 返回time对应的秒数，范围是从0~59

7、获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)这个函数跟SQLSERVER里的DATEPART()函数是一样的获取日期中的年份

8、时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转化为秒的time参数，转换公式为：小时*3600+分钟*60+秒

9、计算日期和时间的函数

增加日期：DATE_ADD(date,interval  expr type)，ADDDATE(date,interval  expr type)

例子：select DATE_ADD(NOW(),interval 2 DAY),返回当前日期加上2天.

减去日期：DATE_SUB(date,interval  expr type)，SUBDATE(date,interval  expr type)

增加时间：ADD_TIME(date,expr)

减去时间：SUBTIME(date,expr)

时间差：DATEDIFF()

最后一天：LAST_DAY(datetime)

日期和时间格式化：

DATE_FORMAT(date,format)

TIME_FORMAT(time,format)

拼接字符串：

concat(str1,str2,…)  

例子： concat('11','22','33') |
| 112233 |

例子：SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

返回：2018-06-01

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

返回：18-06-01

返回日期时间字符串的显示格式：GET_FORMAT(val_type,format_type)

10.截取某个字符串的长度

SELECT SUBSTR(str,pro);--pro表示从str字符串的第一位算起，第Pro位后面的字符串输出

例子：SELECT SUBSTR('我想说的是小姐姐你好漂亮',6)；

输出：小姐姐你好漂亮

10、IF(expr,v1,v2)函数

IF(expr,v1,v2)如果表达式expr是TRUE（expr<>0 and expr<>NULL），则IF()的返回值为v1；

否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定

例子：SELECT IF(1>2,1,2);--结果为false,输出2.

11、改变数据类型的函数

CAST：属于SQL2003标准，并且在mysql，sqlserver，oracle都已经被实现

CAST(x,AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有：

BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED

12.@：=自定义函数

1.@end_dt := if(DATE_FORMAT(NOW(),'%i') < '30',DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:30:00')); 注解：if(参数一，参数二，参数三)，如果现在的时间的分钟数小于30，则取参数二的值，否则取参数3的值；          @end_dt  := if( 分钟数小于30 , 当前时间精确到小时,当前时间精确到小时+30分钟)；          @end_dt  := if( 分钟数小于30 , 当前时间,当前时间+30分钟)--详解：第一个做判断，当前时间的分钟数是否小于30，是就设置成第二个参数的样子，不是就设置成第三个参数的样子，其中第二个参数就是只保留小时，后面分和秒为0，第三个参数就是保留小时，分钟数是30，秒为0； 2.@incr_s_dt := DATE_ADD(@end_dt,INTERVAL -30 minute); 注解：  @incr_s_dt := @end_dt添加时间间隔-30分钟； 3. @all_s_dt := DATE_FORMAT(@end_dt,'%Y-%m-%d 00:00:00'); @all_s_dt := @end_dt当天的0点；

13.查询本周时间

公式：当前日期-今天的星期序号

#整时
set @end_dt := if(DATE_FORMAT(NOW(),'%i') < '30',DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:30:00'));
#刻量
set @incr_s_dt := DATE_ADD(@end_dt,INTERVAL -30 MINUTE);
set @all_s_dt := DATE_FORMAT(@end_dt,'%Y-%m-%d 00:00:00');
set @month_s_dt:= DATE_FORMAT(@end_dt,'%Y-%m-01 00:00:00');
#今天
set @s_dt := DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00');
#本周
set @weekend_s_dt := DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),INTERVAL -DATE_FORMAT(NOW(),'%w') DAY);
#本月
set @thismonth_s_dt := DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00');
#本年
set @thisYear_s_dt := DATE_FORMAT(NOW(),'%Y-01-01 00:00:00');
#过去7天
set @7_day_s_dt := DATE_FORMAT(DATE_ADD(@s_dt,INTERVAL -7 day),'%Y-%m-%d');
#过去30天
set @30_day_s_dt := DATE_FORMAT(DATE_ADD(@s_dt,INTERVAL -30 day),'%Y-%m-%d');
#昨天
set @s_dt := DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d 00:00:00');
#上周
set @weekend_s_dt := DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),INTERVAL -DATE_FORMAT(NOW(),'%w')-7 DAY);
#上个月
set @month_l_s_dt := DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00'),INTERVAL -1 MONTH);
#去年
set @year_l_s_dt := DATE_ADD(DATE_FORMAT(NOW(),'%Y-01-01 00:00:00'),INTERVAL -1 YEAR);