Mysql常用函数总结

不念不忘少年蓝@ 2022-04-14 04:44 434阅读 0赞

### 文章目录 ###

*  前言：
 *  1、日期相关函数
 *   *  1.1、mysql获取未来、现在、过去的时间：DATE\_SUB（）、DATE\_ADD()
     *  1.2、格式化日期：date\_format（）
     *  1.3、MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2), TIMESTAMPDIFF(interval，datetime1，datetime2)
     *  1.4、Mysql当前时间的几个函数
     *  1.5、获取子值，语法如下
     *  1.6、日期计算，使用±运算符，数字后面的关键字为year、month、day、hour、minute、second
 *  2、字符串相关函数
 *   *  2.1、拼接字符串concat(str1,str2…)
     *  2.2、包含字符个数length(str)
     *  2.3、截取字符串
     *  2.4、去除空格
     *  2.5、返回由n个空格字符组成的一个字符串space(n)
     *  2.6、换字符串replace(str,from\_str,to\_str)
     *  2.7、大小写转换，函数如下
 *  3、数学函数
 *   *  3.1、求绝对值abs(n)
     *  3.2、求m除以n的余数mod(m,n)，同运算符%
     *  3.3、求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0
     *  3.4、随机数rand()，值为0-1.0的浮点数
 *  4、其他函数
 *   *  4. 1、SQL中CASE的用法
     *  4.2、SQL的nvl用法
     *  4.3、Mysql删除表数据drop，truncate，delete区别
     *  4.4、改变数据类型的函数CAST（x，AS type）和CONVERT（x，type）

# 前言： #

工作中总会和mysql打交道，这里总结下一些常用的函数功能及需求实现  
。后续会不断更新。

# 1、日期相关函数 #

## 1.1、mysql获取未来、现在、过去的时间：DATE\_SUB（）、DATE\_ADD() ##

#当前日期
    select DATE_SUB(curdate(),INTERVAL 0 DAY) ;#或者 NOW()
    #明天日期
    select DATE_SUB(curdate(),INTERVAL -1 DAY) ;
    #昨天日期
    select DATE_SUB(curdate(),INTERVAL 1 DAY) ;
    #前一个小时时间
    select date_sub(now(), interval 1 hour);
    #后一个小时时间
    select date_sub(now(), interval -1 hour);
    #前30分钟时间
    select date_add(now(),interval -30 minute)
    #后30分钟时间
    select date_add(now(),interval 30 minute)
    #对于Date_add（）同样
    set @dt = now();
    select date_add(@dt, interval 1 day);           - 加1天
    select date_add(@dt, interval 1 hour);                -加1小时
    select date_add(@dt, interval 1 minute);    - 加1分钟
    select date_add(@dt, interval 1 second);             -加1秒
    select date_add(@dt, interval 1 microsecond);     -加1毫秒
    select date_add(@dt, interval 1 week);         -加1周
    select date_add(@dt, interval 1 month);        -加1月
    select date_add(@dt, interval 1 quarter);             -加1季
    select date_add(@dt, interval 1 year);           -加1年

## 1.2、格式化日期：date\_format（） ##

获取年%Y，返回4位的整数
    *　获取年%y，返回2位的整数
    *　获取月%m，值为1-12的整数
    获取日%d，返回整数
    *　获取时%H，值为0-23的整数
    *　获取时%h，值为1-12的整数
    *　获取分%i，值为0-59的整数
    *　获取秒%s，值为0-59的整数
    select date_format('2016-12-21','%Y %m %d');    
    
    %S, %s 两位数字形式的秒（ 00,01, ..., 59）
    %I, %i 两位数字形式的分（ 00,01, ..., 59）
    %H 两位数字形式的小时，24 小时（00,01, ..., 23）
    %h 两位数字形式的小时，12 小时（01,02, ..., 12）
    %k 数字形式的小时，24 小时（0,1, ..., 23）
    %l 数字形式的小时，12 小时（1, 2, ..., 12）
    %T 24 小时的时间形式（hh:mm:ss）
    %r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
    %p AM或PM
    %W 一周中每一天的名称（Sunday, Monday, ..., Saturday）
    %a 一周中每一天名称的缩写（Sun, Mon, ..., Sat）
    %d 两位数字表示月中的天数（00, 01,..., 31）
    %e 数字形式表示月中的天数（1, 2， ..., 31）
    %D 英文后缀表示月中的天数（1st, 2nd, 3rd,...）
    %w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）
    %j 以三位数字表示年中的天数（ 001, 002, ..., 366）
    %U 周（0, 1, 52），其中Sunday 为周中的第一天
    %u 周（0, 1, 52），其中Monday 为周中的第一天
    %M 月名（January, February, ..., December）
    %b 缩写的月名（ January, February,...., December）
    %m 两位数字表示的月份（01, 02, ..., 12）
    %c 数字表示的月份（1, 2, ...., 12）
    %Y 四位数字表示的年份
    %y 两位数字表示的年份
    %% 直接值“%”

## 1.3、MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2), TIMESTAMPDIFF(interval，datetime1，datetime2) ##

MySQL datediff(date1,date2)：两个日期相减 date1 date2，返回天数。

select datediff('2008-08-08', '2008-08-01'); - 7
    
    select datediff('2008-08-01', '2008-08-08'); -7
    
    MySQL timediff(time1,time2)：两个日期相减 time1 time2，返回 time 差值。
    
    select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00');- 08:08:08
    
    select timediff('08:08:08', '00:00:00');                      - 08:08:08
    
    注意：timediff(time1,time2) 函数的两个参数类型必须相同。
    
    TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值
    FRAC_SECOND。表示间隔是毫秒
    SECOND。秒
    MINUTE。分钟
    HOUR。小时
    DAY。天
    WEEK。星期
    MONTH。月
    QUARTER。季度
    YEAR。年
    select TIMESTAMPDIFF(DAY,'2018-07-01 09:00:00','2018-07-04 12:00:00');

## 1.4、Mysql当前时间的几个函数 ##

1. NOW (); 当前具体的日期和时间，
     2. CURDATE (); 当前日期
     3. CURTIME(); 当前时间
     4. DATE ( date ); 提取日期或日期/时间表达式的日期部分
     5. DATE_ADD ( date, INTERVAL  expr  type); 给日期添加指定的时间间隔，
     6. DATE_SUB (date, INTERVAL  expr  type);  从日期送去指定的时间间隔。#expr 参数是您希望添加的时间间隔， type参数同DATE_ADD()。
     7. DATEDIFF ( date1, date2 ); 返回两个日期之间的天数，只有值的日期部分参与计算。
     8. DATE_FORMAT (date, format); 用不同的格式显示日期/时间。

## 1.5、获取子值，语法如下 ##

year(date)返回date的年份(范围在1000到9999)
    month(date)返回date中的月份数值
    day(date)返回date中的日期数值
    hour(time)返回time的小时数(范围是0到23)
    minute(time)返回time的分钟数(范围是0到59)
    second(time)返回time的秒数(范围是0到59)
    select year('2016-12-21');

## 1.6、日期计算，使用±运算符，数字后面的关键字为year、month、day、hour、minute、second ##

select '2016-12-21'+interval 1 day;

# 2、字符串相关函数 #

## 2.1、拼接字符串concat(str1,str2…) ##

select concat(12,34,'ab');

## 2.2、包含字符个数length(str) ##

select length(‘abc’);

## 2.3、截取字符串 ##

left(str,len)返回字符串str的左端len个字符
    right(str,len)返回字符串str的右端len个字符
    substring(str,pos,len)返回字符串str的位置pos起len个字符
    #select substring('abc123',2,3);

## 2.4、去除空格 ##

ltrim(str)返回删除了左空格的字符串str
    rtrim(str)返回删除了右空格的字符串str
    trim([方向 remstr from str)返回从某侧删除remstr后的字符串str，方向词包括both、leading、trailing，表示两侧、左、右
    select trim('  bar   ');
    select trim(leading 'x' FROM 'xxxbarxxx');
    select trim(both 'x' FROM 'xxxbarxxx');
    select trim(trailing 'x' FROM 'xxxbarxxx');

## 2.5、返回由n个空格字符组成的一个字符串space(n) ##

select space(10);

## 2.6、换字符串replace(str,from\_str,to\_str) ##

select replace('abc123','123','def');

## 2.7、大小写转换，函数如下 ##

lower(str)
    upper(str)
    select lower('aBcD');

# 3、数学函数 #

## 3.1、求绝对值abs(n) ##

select abs(-32);

## 3.2、求m除以n的余数mod(m,n)，同运算符% ##

select mod(10,3);
    select 10%3;

## 3.3、求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0 ##

select round(1.6);

## 3.4、随机数rand()，值为0-1.0的浮点数 ##

select rand();

还有其它很多三角函数，使用时可以查询文档

# 4、其他函数 #

## 4. 1、SQL中CASE的用法 ##

sql语言中的CASE类似C语言中的switch case

#case语法
    SELECT <myColumnSpec> =
    CASE
    WHEN <A> THEN <somethingA>
    WHEN <B> THEN <somethingB>
    ELSE <somethingE>
    END 
    #例子
    SELECT
         Title,
        'Price Range' =
        CASE
            WHEN price IS NULL THEN 'Unpriced'
            WHEN price < 10 THEN 'Bargain'
            WHEN price BETWEEN 10 and 20 THEN 'Average'
            ELSE 'Gift to impress relatives'
        END
    FROM titles
    ORDER BY price

## 4.2、SQL的nvl用法 ##

**NVL函数是一个空值转换函数**

**NVL（表达式1，表达式2）**

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。 该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

## 4.3、Mysql删除表数据drop，truncate，delete区别 ##

1、drop (删除表)：**删除内容和定义，释放空间**。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。**与drop不同的是,只是清空表数据而已**。

注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

以便进行进行回滚操作。
           truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

## 4.4、改变数据类型的函数CAST（x，AS type）和CONVERT（x，type） ##

函数将一个类型的值转换为另一个类型的值，可转换的type有：BINARY、CHAR（n）、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

参考：  
https://www.cnblogs.com/maanshancss/p/4036608.html  
https://blog.csdn.net/anneqiqi/article/details/52061384  
https://blog.csdn.net/liujava621/article/details/26599035  
https://www.cnblogs.com/fjl0418/p/7929420.html  
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html\#function\_date-format