MySQL常见函数

        函数：将某些功能封装到一起，对外提供到一个接口(函数名)，通过函数调用的方式可以重复的执行函数里的功能代码从而提高我们的代码的复用性。

MySql自带了很多已经封装好了的函数，可以帮我们实现很多功能，

MySql里调用函数使用函数名()，完整的写法：

select 函数名(实参) from 表名；

一、字符函数

    1、length(str)：获取参数的字节数           select length('abcd'); // 返回值:4           select length('数据库abc'); // 返回值:与字符编码类型有关           show variables like '%char%'; //查看数据可客服端字符集编码           GBK:一个汉字占两个字节;UTF-8:一个汉字是占三个字节.

    2、concat(str1,str2,...)：连接字符串

             也可以这样写：concat(str1,concat(str2,str3))：

    3、upper(str)：小写字母变大写; 

    4、lower(str)：大写字母变小写;

    5、substr 和 substring           案例:截取字符串,从第4个字符(包含)开始到最后                   select substr('目标字符串',4) ;      //注意:Mysql中索引值是从1开始的,java索引是从0开始的.           案例:截取字符串,从第二个开始接到第三个                   select substr('目标字符串',2,1);    //注意:第一个数字是开始截取的索引值,第二个数数截取的长度

     6、.instr('源字符串','子字符串')：作用返回子字符串子源字符串里的起始索引.            select instr('目标字符串','字符串'); //返回结果3, 如果找不到返回0

     7、trim(' 有空格的字符串 ')：作用是去掉字符串前后的空格,中间空格去不掉            扩展用法:去掉前后两端的其他字符:select trim('a' from 'aaaaa字符aaaaa去掉aaaaa');

     8、replace('目标字符串','被替换子串','用于替换的新串')替换            select replace('目标字符串','字符','zifu');

二、数学函数

     1、round() ： 四舍五入            select round(1.45);//不管正负数,先将绝对值round,然后加正负号            select round(1.567,2); //表示小数点保留2位

     2、ceil()： 向上取整            select ceil(-1.3);

     3、floor()： 向下取整

     4、truncate()： 截断            select truncate(1.65,1); // 结果保留小数1位:1.6

三、日期函数

     1、获取系统时间

SELECT now(); -- 返回当前日期时间
SELECT curdate(); -- 返回当前系统日期,没有时间部分
SELECT curtime() ; -- 返回当前系统时间,没有日期部分
SELECT unix_timestamp(now()); -- 返回日期时间转时间戳

     2、单独获取年/月/日 比较

YEAR(NOW()); -- 返回年份
month(now()); -- 返回日期d中的月份值，1 到 12
day(now()); -- 返回日期值 d 的日期部分(几号)
hour(now()); -- 返回小时值
minute(now()); -- 返回分钟值
second(now()); -- 返回秒数
monthname(now()); -- 返回日期当中的月份名称，如 Janyary

WEEKDAY(now()); -- 日期 d 是星期几，0 表示星期一，1 表示星期二
WEEKOFYEAR(now()); -- 计算日期 d 是本年的第几个星期，范围是 0 到 53

QUARTER(date) -- 返回日期d是第几季节，返回 1 到 4

TO_DAYS(date) -- 计算日期 d 距离 0000 年 1 月 1 日的天数

3、STR_TO_DATE(str,format) ：将字符串转换为指定格式的日期         

格式字符串可以包含以字母开头的文字字符和格式说明符%。

• 文字字符 format必须符合字面意思 str。
• 格式说明符 format必须与日期或时间部分匹配str。

比如：str_to_date('2018-1-12','%y-%m-%d');

4、DATE_FORMAT(date,format) ：格式化日期，将日期转换成字符串

• date：根据format字符串 格式化值。
• format：字符串中可以使用以下说明符。%格式说明符字符前需要该字符

比如：  date_format(now(),'%Y-%m-%d');

常用的一个 farmat：'%Y-%m-%d  %H:%i:%s'

符描述%a缩写的工作日名称（Sun.. Sat）%b缩写的月份名称（Jan.. Dec）%c月，数字（0.. 12）%D这个月的一天，英语后缀（0th， 1st，2nd， 3rd，...）%d每月的某天，数字（00.. 31）%e每月的某天，数字（0.. 31）%f微秒（000000... 999999）%H小时（00.. 23）%h小时（01.. 12）%I小时（01.. 12）%i分钟，数字（00.. 59）%j一年中的某一天（001.. 366）%k小时（0.. 23）%l小时（1.. 12）%M月份名称（January.. December）%m月，数字（00.. 12）%pAM 要么 PM%r时间，12小时（hh:mm:ss其次是 AM或PM）%S秒（00... 59）%s秒（00... 59）%T时间，24小时（hh:mm:ss）%U周（00.. 53），周日是一周的第一天; WEEK()模式0%u周（00.. 53），周一是一周的第一天; WEEK()模式1%V周（01.. 53），周日是一周的第一天; WEEK()模式2; 用于 %X%v周（01.. 53），周一是一周的第一天; WEEK()模式3; 用于 %x%W平日名称（Sunday.. Saturday）%w星期几（0=星期日.. 6=星期六）%X星期日是星期的第一天的星期，数字，四位数; 用于%V%x一周的年份，星期一是一周的第一天，数字，四位数; 用于%v%Y年份，数字，四位数%y年份，数字（两位数）%%文字%字符%xx，对于上面未列出的任何 “ x” 查询当天、本周、最近7天、本月、最近30天的语句

上年
SELECT * FROM  表名 WHERE YEAR(create_date) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

上季度
SELECT * FROM  表名 WHERE QUARTER(create_date) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));

上个月
SELECT * FROM  表名 WHERE DATE_FORMAT(submittime,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')
 
SELECT * FROM  表名 WHERE DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 
SELECT * FROM  表名 WHERE WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(NOW()) 
 
SELECT * FROM  表名 WHERE MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(NOW()) 
 
SELECT * FROM  表名 WHERE YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(NOW()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(NOW()) 
 
SELECT * FROM  表名 WHERE pudate BETWEEN  上月最后一天 AND 下月第一天

上周
SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(submittime,'%Y-%m-%d')) = YEARWEEK(NOW())-1;

昨日
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段,'%Y-%m-%d') = DATE_FORMAT(CURDATE()-1,'%Y-%m-%d');

SELECT * FROM 表名 WHERE DATE(时间字段) = DATE(CURDATE()-1);

SELECT * FROM 表名 WHERE DATEDIFF(时间字段,NOW())=-1

-----------------------
当天
SELECT * FROM 表名 WHERE TO_DAYS(时间字段名) = TO_DAYS(NOW());

本周
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d')) = YEARWEEK(NOW());

本月
SELECT * FROM 表名 where DATE_FORMAT(时间字段, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

本季度
SELECT * FROM 表名 WHERE QUARTER(create_date) = QUARTER(NOW()); 

本年
SELECT * FROM 表名 WHERE YEAR(create_date) = YEAR(NOW());

最近7天
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) 20000 THEN 'A级工资'
        WHEN salary>10000 THEN 'B级工资'
    ELSE 'C级工资'
    END AS 工资等级
FROM employees;

五、IFNULL()函数用法

        语法： IFNULL(expr1,expr2)  ：返回一个数字或字符串值

        如果expr1不为NULL，返回expr1的值，否则它返回expr2的值。

其他

-- 修改主键的初始值
alter table t_student AUTO_INCREMENT = 114;

-- 中文排序
SELECT DISTINCT name  FROM t_student ORDER BY CONVERT(name USING gbk);

-- MySql分组后随机获取每组一条数据的操作
SELECT * FROM
 (SELECT * FROM t_student ORDER BY RAND()) a
GROUP BY a.name

 参考文章：

• 官方手册包含详细的函数及其他知识点：MySQL 官方5.7参考手册
•  mySql 查询当天、本周、最近7天、本月、最近30天的语句_SunFlowerXT的博客-CSDN博客
• 在线 MySQL在线手册 - 函数：MySQL 函数
• 在线 MySQL 手册2：MySQL DATE_ADD() 函数

ends~