【⑤MySQL函数】：让你的数据库操作更高效（二）

小咪咪 2023-10-12 14:48 41阅读 0赞

#### 前言 ####

> **✨欢迎来到[小K][K]的[MySQL专栏][MySQL]，本节将为大家带来MySQL日期时间、条件判断、系统信息、加密、进制转换和IP地址转换函数的讲解**✨

#### 目录 ####

*   *   *  前言
         *  一、日期和时间函数
         *  二、条件判断函数
         *  三、系统信息函数
         *  四、加密函数
         *  五、进制转换函数
         *  六、IP地址转换函数
         *  七、总结

#### 一、日期和时间函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CURDATE()，CURRENT_DATE()</td> 
   <td>返回当前日期</td> 
  </tr> 
  <tr> 
   <td>CURTIME()，CURRENT_TIME()</td> 
   <td>返回当前时间</td> 
  </tr> 
  <tr> 
   <td>NOW()，CURRENT_TIMESTAMP()，LOCALTIME()，<br>SYSDATE()，LOCALTIMESTAMP()</td> 
   <td>返回会当前日期和事件</td> 
  </tr> 
  <tr> 
   <td>YEAR(日期)</td> 
   <td>返回日期中的年份</td> 
  </tr> 
  <tr> 
   <td>MONTH(日期)</td> 
   <td>返回日期中的月份[1,12]</td> 
  </tr> 
  <tr> 
   <td>DAY(日期)</td> 
   <td>返回日期中的天[1,31]</td> 
  </tr> 
  <tr> 
   <td>HOUR(时间)</td> 
   <td>返回时间中的小时</td> 
  </tr> 
  <tr> 
   <td>MINUTE(时间)</td> 
   <td>返回时间中的分钟</td> 
  </tr> 
  <tr> 
   <td>SECOND(时间)</td> 
   <td>返回时间中的秒</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK(日期)</td> 
   <td>返回星期几，1星期日，2星期一</td> 
  </tr> 
  <tr> 
   <td>WEEKDAY(日期)</td> 
   <td>返回星期几，0星期一，1星期二</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR(日期)</td> 
   <td>计算指定日期是本年第几天</td> 
  </tr> 
  <tr> 
   <td>DAYOFMONTH(日期)</td> 
   <td>计算指定日期是本月第几天</td> 
  </tr> 
  <tr> 
   <td>QUARTER(日期)</td> 
   <td>计算指定日期是第几季度</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(time)</td> 
   <td>将指定时间转换为秒</td> 
  </tr> 
  <tr> 
   <td>SEC_TO_TIME(sec)</td> 
   <td>将以秒为单位的时间转换为时分秒的格式</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF(d1,d2)</td> 
   <td>计算两个日期相隔的天数</td> 
  </tr> 
  <tr> 
   <td>ADDDATE(date,n)</td> 
   <td>计算指定日期加上n天后的日期</td> 
  </tr> 
  <tr> 
   <td>SUBDATE(date,n)</td> 
   <td>计算指定日期减去n天后的日期</td> 
  </tr> 
  <tr> 
   <td>ADDTIME(time,n)</td> 
   <td>计算指定时间加上n秒后的时间</td> 
  </tr> 
  <tr> 
   <td>SUBTIME(time,n)</td> 
   <td>计算指定时间减去n秒后的时间</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT(date,f)</td> 
   <td>按格式返回日期</td> 
  </tr> 
  <tr> 
   <td>TIME_FORMAT(time,f)</td> 
   <td>按格式返回时间</td> 
  </tr> 
 </tbody> 
</table>

✨日期和时间函数练习：

*  `CURDATE()，CURRENT_DATE()` ，**返回当前日期`CURTIME()，CURRENT_TIME()` ，返回当前时间`NOW()，CURRENT_TIMESTAMP()，LOCALTIME()，SYSDATE()，LOCALTIMESTAMP()`，返回会当前日期和事件**
    
        SELECT CURDATE(),CURTIME(),NOW(),LOCALTIME(),SYSDATE();
 *  **`MONTH`(日期) : 返回日期中的月份\[1,12\],`DAY`(日期) : 返回日期中的天\[1,31\],`HOUR`(时间) : 返回时间中的小时,`MINUTE`(时间) : 返回时间中的分钟,`SECOND`(时间) : 返回时间中的秒**
    
        SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
 *  `DAYOFWEEK`**:(日期) 返回星期几，1星期日，2星期一；`WEEKDAY`(日期)：返回星期几，0星期一，1星期二**
    
        SELECT DAYOFWEEK(NOW()),WEEKDAY(NOW());
 *  **`DAYOFYEAR`(日期)：计算指定日期是本年第几天；`DAYOFMONTH(日期)`： 计算指定日期是本月第几天；`QUARTER`(日期) ：计算指定日期是第几季度**
    
        SELECT DAYOFYEAR(NOW()),DAYOFMONTH(NOW()),QUARTER(NOW());
 *  **`TIME_TO_SEC(time)` ：将指定时间转换为秒；`SEC_TO_TIME(sec)` ： 将以秒为单位的时间转换为时分秒的格式**
    
        SELECT TIME_TO_SEC('01:00:00');
        SELECT SEC_TO_TIME('3600');
 *  **`ADDDATE(date,n)` ： 计算指定日期加上n天后的日期；`SUBDATE(date,n)` ： 计算指定日期减去n天后的日期；`ADDTIME(time,n)`： 计算指定时间加上n秒后的时间；`SUBTIME(time,n)` ： 计算指定时间减去n秒后的时间**
    
        SELECT DATEDIFF(CURDATE(),'2023/6/5');
        SELECT ADDDATE(CURDATE(),'2'),SUBDATE(CURDATE(),'2');
        SELECT ADDTIME(CURTIME(),'2'),SUBTIME(CURTIME(),'2');
 *  **`DATE_FORMAT(date,f)`： 按格式返回日期；`TIME_FORMAT(time,f)` ：按格式返回时间**，注意：%Y表示年份为4位，%y表示两位，分钟的格式化字符要用%i
    
        SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d');
        SELECT DATE_FORMAT(CURDATE(),'%y-%m-%d');
        SELECT TIME_FORMAT(CURTIME(),'%h-%i-%s');

✨**效果如下：**

![在这里插入图片描述][606e8b456bc84934ad933e973d3d1cce.png_pic_center]  
![在这里插入图片描述][dc1c5f475e574a7d9fae76995bd4a00d.png_pic_center]  
![在这里插入图片描述][23c062fd828e417a95b01b985db5f3b4.png_pic_center]

#### 二、条件判断函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IF(expr,v1,v2)</td> 
   <td>如果表达式expr成立，返回结果v1；否则，返回结果v2。</td> 
  </tr> 
  <tr> 
   <td>IFNULL(v1,v2)</td> 
   <td>如果v1的值不为NULL，则返回v1，否则返回v2</td> 
  </tr> 
  <tr> 
   <td>CASE</td> 
   <td>分支(详见下面)</td> 
  </tr> 
 </tbody> 
</table>

*  `IF(expr,v1,v2)`

SELECT IF(1>0,'true','false');
    ->true

*  `IFNULL(v1,v2)`

SELECT IFNULL(NULL,'king');
    ->king

*  `CASE`

✨ **语法1：**

CASE
    	WHEN expr1 THEN V1
    	WHEN expr2 THEN V2
    	...
    	ELSE vn
    END

SELECT CASE 
    　　WHEN 1 > 0
    　　THEN '1 > 0'
    　　WHEN 2 > 0
    　　THEN '2 > 0'
    　　ELSE '3 > 0'
    　　END
    ->1 > 0

✨**语法2：**

CASE expr 
    　　WHEN e1 THEN v1
    　　WHEN e1 THEN v1
    　　...
    　　ELSE vn
    END

SELECT CASE 1 
    　　WHEN 1 THEN '我是1'
    　　WHEN 2 THEN '我是2'
    ELSE '你是谁'

#### 三、系统信息函数 ####

> ✨ **系统信息函数用来查询MySQL数据库的系统信息。**

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>VERSION()</td> 
   <td>返回数据库的版本号</td> 
  </tr> 
  <tr> 
   <td>CONNECTION_ID()</td> 
   <td>返回服务器的连接数</td> 
  </tr> 
  <tr> 
   <td>DATABASE()、SCHEMA</td> 
   <td>返回当前数据库名</td> 
  </tr> 
  <tr> 
   <td>USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER</td> 
   <td>返回当前用户</td> 
  </tr> 
  <tr> 
   <td>CHARSET(str)</td> 
   <td>返回字符串str的字符集</td> 
  </tr> 
  <tr> 
   <td>COLLATION(str)</td> 
   <td>返回字符串str的字符排列方式</td> 
  </tr> 
 </tbody> 
</table>

**系统信息函数练习：**

*  `VERSION()` : **返回数据库的版本号**
    
        SELECT VERSION();
 *  `CONNECTION_ID()`: **返回服务器的连接数**
    
        SELECT CONNECTION_ID();
 *  `DATABASE()、SCHEMA`: **返回当前数据库名**
    
        SELECT DATABASE();
 *  `USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER`: **返回当前用户**
    
        SELECT user();
 *  `CHARSET(str)` :**返回字符串str的字符集**
    
        SELECT CHARSET('123564');
 *  `COLLATION(str)`: **返回字符串str的字符排列方式**
    
        SELECT COLLATION('king');

✨效果如下：

![在这里插入图片描述][a67bc086cc8a466a9805115fe10542f5.png_pic_center]

#### 四、加密函数 ####

> ✨**加密函数是MySQL用来对数据进行加密的函数.**

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MD5(str)</td> 
   <td>对字符串str进行散列，可以用于一些普通的不需要解密的数据加密，如密码</td> 
  </tr> 
  <tr> 
   <td>sha1</td> 
   <td>对字符串str进行散列，可以用于一些普通的不需要解密的数据加密，如密码，比MD5更安全</td> 
  </tr> 
 </tbody> 
</table>

SELECT MD5('12345679'),SHA1('12345679');
    -->defac44447b57f152d14f30cea7a73cb   -->c4b93599b61b85b1f109064294c5bd7adf73ca66

✨**我们会发现`sha1`产生的密码更长**

#### 五、进制转换函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BIN(x)</td> 
   <td>返回x的二进制</td> 
  </tr> 
  <tr> 
   <td>HEX(x)</td> 
   <td>返回x的十六进制</td> 
  </tr> 
  <tr> 
   <td>OCT(x)</td> 
   <td>返回x的八进制</td> 
  </tr> 
  <tr> 
   <td>CONV(x,from_base,to_base)</td> 
   <td>将from_base进制的x，转成to_base进制</td> 
  </tr> 
 </tbody> 
</table>

SELECT BIN(10),HEX(10),OCT(10),CONV(16,16,10);
    ->1010 A 12 22

#### 六、IP地址转换函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>INET_ATON(IP)</td> 
   <td>将点分十进制的IP地址转为数字</td> 
  </tr> 
  <tr> 
   <td>INET_NTOA(number)</td> 
   <td>将数字形式的IP转为点分十进制</td> 
  </tr> 
 </tbody> 
</table>

SELECT INET_ATON('192.168.0.1')
        ->3232235521
    SELECT INET_NTOA(3232235521)
        ->192.168.0.1

#### 七、总结 ####

> **✨~MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。MySQL 包含了大量并且丰富的函数，咱们讲解几十个常用的，剩下的比较罕见的函数我们可以到「✨[MySQL 参考手册][MySQL 1]」查询。**

[K]: https://blog.csdn.net/qq_72157449?spm=1000.2115.3001.5343
[MySQL]: https://blog.csdn.net/qq_72157449/category_12344760.html?spm=1001.2014.3001.5482
[606e8b456bc84934ad933e973d3d1cce.png_pic_center]: https://img-blog.csdnimg.cn/606e8b456bc84934ad933e973d3d1cce.png#pic_center
[dc1c5f475e574a7d9fae76995bd4a00d.png_pic_center]: https://img-blog.csdnimg.cn/dc1c5f475e574a7d9fae76995bd4a00d.png#pic_center
[23c062fd828e417a95b01b985db5f3b4.png_pic_center]: https://img-blog.csdnimg.cn/23c062fd828e417a95b01b985db5f3b4.png#pic_center
[a67bc086cc8a466a9805115fe10542f5.png_pic_center]: https://img-blog.csdnimg.cn/a67bc086cc8a466a9805115fe10542f5.png#pic_center
[MySQL 1]: https://dev.mysql.com/doc/refman/8.0/en/functions.html