MySQL常见函数

怼烎@ 2022-04-21 15:24 269阅读 0赞

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

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'

<table> 
 <thead> 
  <tr> 
   <th style="vertical-align:baseline;">符</th> 
   <th style="vertical-align:baseline;">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%a</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">缩写的工作日名称（<code>Sun</code>..&nbsp;<code>Sat</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%b</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">缩写的月份名称（<code>Jan</code>..&nbsp;<code>Dec</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%c</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">月，数字（<code>0</code>..&nbsp;<code>12</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%D</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">这个月的一天，英语后缀（<code>0th</code>，&nbsp;<code>1st</code>，<code>2nd</code>，&nbsp;<code>3rd</code>，...）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><span style="color:#f33b45;"><code>%d</code></span></td> 
   <td style="border-color:#808080;vertical-align:baseline;">每月的某天，数字（<code>00</code>..&nbsp;<code>31</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%e</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">每月的某天，数字（<code>0</code>..&nbsp;<code>31</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%f</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">微秒（<code>000000</code>...&nbsp;<code>999999</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><span style="color:#f33b45;"><code>%H</code></span></td> 
   <td style="border-color:#808080;vertical-align:baseline;">小时（<code>00</code>..&nbsp;<code>23</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%h</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">小时（<code>01</code>..&nbsp;<code>12</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%I</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">小时（<code>01</code>..&nbsp;<code>12</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><span style="color:#f33b45;"><code>%i</code></span></td> 
   <td style="border-color:#808080;vertical-align:baseline;">分钟，数字（<code>00</code>..&nbsp;<code>59</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%j</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">一年中的某一天（<code>001</code>..&nbsp;<code>366</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%k</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">小时（<code>0</code>..&nbsp;<code>23</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%l</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">小时（<code>1</code>..&nbsp;<code>12</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%M</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">月份名称（<code>January</code>..&nbsp;<code>December</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><span style="color:#f33b45;"><code>%m</code></span></td> 
   <td style="border-color:#808080;vertical-align:baseline;">月，数字（<code>00</code>..&nbsp;<code>12</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%p</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>AM</code>&nbsp;要么&nbsp;<code>PM</code></td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%r</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">时间，12小时（<code>hh:mm:ss</code>其次是&nbsp;<code>AM</code>或<code>PM</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%S</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">秒（<code>00</code>...&nbsp;<code>59</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><span style="color:#f33b45;"><code>%s</code></span></td> 
   <td style="border-color:#808080;vertical-align:baseline;">秒（<code>00</code>...&nbsp;<code>59</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%T</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">时间，24小时（<code>hh:mm:ss</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%U</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">周（<code>00</code>..&nbsp;<code>53</code>），周日是一周的第一天;&nbsp;<a href="https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week" title="WEEK()" rel="nofollow">WEEK()</a>模式0</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%u</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">周（<code>00</code>..&nbsp;<code>53</code>），周一是一周的第一天;&nbsp;<a href="https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week" title="WEEK()" rel="nofollow">WEEK()</a>模式1</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%V</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">周（<code>01</code>..&nbsp;<code>53</code>），周日是一周的第一天;&nbsp;<a href="https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week" title="WEEK()" rel="nofollow">WEEK()</a>模式2;&nbsp;用于&nbsp;<code>%X</code></td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%v</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">周（<code>01</code>..&nbsp;<code>53</code>），周一是一周的第一天;&nbsp;<a href="https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week" title="WEEK()" rel="nofollow">WEEK()</a>模式3;&nbsp;用于&nbsp;<code>%x</code></td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%W</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">平日名称（<code>Sunday</code>..&nbsp;<code>Saturday</code>）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%w</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">星期几（<code>0</code>=星期日..&nbsp;<code>6</code>=星期六）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%X</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">星期日是星期的第一天的星期，数字，四位数;&nbsp;用于<code>%V</code></td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%x</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">一周的年份，星期一是一周的第一天，数字，四位数;&nbsp;用于<code>%v</code></td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><span style="color:#f33b45;"><code>%Y</code></span></td> 
   <td style="border-color:#808080;vertical-align:baseline;">年份，数字，四位数</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%y</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">年份，数字（两位数）</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%%</code></td> 
   <td style="border-color:#808080;vertical-align:baseline;">文字<code>%</code>字符</td> 
  </tr> 
  <tr> 
   <td style="border-color:#808080;vertical-align:baseline;"><code>%<em><code>x</code></em></code></td> 
   <td style="border-color:#808080;vertical-align:baseline;"><em><code>x</code></em>，对于上面未列出的任何&nbsp;“&nbsp;<em><code>x</code></em>”</td> 
  </tr> 
 </tbody> 
</table>

### 查询当天、本周、最近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) <= DATE(时间字段);
    
    最近30天
    SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(时间字段);
    
    查询距离当前现在6个月的数据
    SELECT * FROM 表名 WHERE submittime BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQ2hhcmdlOA_size_20_color_FFFFFF_t_70_g_se_x_16][]

**获取用户当天的总积分（TO\_DAYS）：**

SELECT SUM(integral) 
    FROM t_user
    WHERE enabled = 1 AND TO_DAYS(create_time) = TO_DAYS(NOW()) AND id = 1

### 四、流程控制函数 ###

**  1、if函数**  
         select if('10>1','大','小');

**2、case函数**

>  语法1：  
> CASE expr(字段或表达式)  
>     WHEN v1(case的结果是常量) THEN  
>          statement\_list1(要显示的值或语句)  
>     WHEN v2 THEN  
>          statement\_list2    
>     ELSE  
>          statement\_list3(都不是前面的时候显示)  
> END 别名
> 
> 语法2：  
> CASE   
>     WHEN v1(expr(字段表达式)) THEN  
>               statement\_list1  
>     WHEN v2 THEN  
>               statement\_list2    
>     ELSE  
>               statement\_list  
> END 别名

案例：员工表中, 部门号是30,显示的工资是1.1倍； 部门号是40,显示的工资是1.2倍； 其他显示原工资

SELECT 
        salary AS 原工资,
        department_id,
        CASE department_id
            WHEN 30 THEN salary*1.1
            WHEN 50 THEN salary*1.2
        ELSE salary
        END AS 新工资
    FROM employees;  
    
    SELECT
      id,
      CASE 
        WHEN age=8 THEN
          '等于10'
        WHEN age=99 THEN
          '等于99'
        ELSE
          '其他'
      END age2,
      age
    FROM t_test6 where id < 4;

**3、多重if**

语法：
        case
            when 条件1 then 要显示的值1(或语句1;)
            when 条件2 then 要显示的值2(或语句2;)
            ......
        else 前面的条件都不符合时候显示;
        end

案例：

SELECT 
        salary,
        CASE
            WHEN salary>20000 THEN 'A级工资'
            WHEN salary>10000 THEN 'B级工资'
        ELSE 'C级工资'
        END AS 工资等级
    FROM employees;

### 五、IFNULL()函数用法 ###

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

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

** 参考文章：**

*  官方手册包含详细的函数及其他知识点：[MySQL 官方5.7参考手册][MySQL _5.7]
 *   [mySql 查询当天、本周、最近7天、本月、最近30天的语句\_SunFlowerXT的博客-CSDN博客][mySql _7_30_SunFlowerXT_-CSDN]
 *  在线 MySQL在线手册 - 函数：[MySQL 函数][MySQL]
 *  在线 MySQL 手册2：[MySQL DATE\_ADD() 函数][MySQL DATE_ADD_]

ends~

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQ2hhcmdlOA_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220414/25653407906c4317b6d6050fca19ed10.png
[MySQL _5.7]: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
[mySql _7_30_SunFlowerXT_-CSDN]: https://blog.csdn.net/SunFlowerXT/article/details/70159456
[MySQL]: https://www.zixuephp.net/manual-mysql-1345.html
[MySQL DATE_ADD_]: http://booksky.99lb.net/sql/mydate_add.shtml