MySQL数据库函数

忘是亡心i 2023-10-01 15:44 10阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>类型</th> 
   <th align="left">地址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96165881" rel="nofollow">MySQL操作之概念、SQL约束（一）</a></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96339284" rel="nofollow">MySQL操作之数据定义语言（DDL)（二）</a></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96445064" rel="nofollow">MySQL操作之数据操作语言（DML)（三）</a></td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96726772" rel="nofollow">MySQL操作之数据查询语言：(DQL)（四-1）（单表操作）</a></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96729083" rel="nofollow">MySQL操作之数据查询语言：(DQL)（四-2）（多表查询）</a></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120254916" rel="nofollow">MySQL操作之数据控制语言：(DC)（五）</a></td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/122593057" rel="nofollow">MySQL操作之数据库函数</a></td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383272" rel="nofollow">MySQL管理之数据类型</a></td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120393164" rel="nofollow">MySQL管理之索引</a></td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120240970" rel="nofollow">MySQL管理之事务管理</a></td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383054" rel="nofollow">MySQL管理之存储过程</a></td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120251668" rel="nofollow">MySQL管理之视图</a></td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383282" rel="nofollow">MySQL管理之数据备份与还原</a></td> 
  </tr> 
  <tr> 
   <td>14</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/95240161" rel="nofollow">Linux（centos 7.5）服务器安装MySQL</a></td> 
  </tr> 
  <tr> 
   <td>15</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/119246809" rel="nofollow">MyBatis从入门到多表关联</a></td> 
  </tr> 
  <tr> 
   <td>16</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120823825" rel="nofollow">MyBatis常用方法</a></td> 
  </tr> 
  <tr> 
   <td>17</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/98309342" rel="nofollow">Mybatis逆向工程的使用（附文件地址）</a></td> 
  </tr> 
  <tr> 
   <td>18</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/106430172" rel="nofollow">spring boot连接Mybatis数据库的配置文件(MySql、SQLserver、Oracle)</a></td> 
  </tr> 
  <tr> 
   <td>19</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/119060130" rel="nofollow">Mybatis-Plus使用案例(包括初始化以及常用插件)</a></td> 
  </tr> 
  <tr> 
   <td>20</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124972178" rel="nofollow">Mybatis-Plus(Service CRUD 接口)</a></td> 
  </tr> 
  <tr> 
   <td>21</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="http://t.csdn.cn/3mdPc" rel="nofollow">Mybatis-plus 4种条件构造器方式</a></td> 
  </tr> 
  <tr> 
   <td>22</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124936692" rel="nofollow">Mybatis-Plus 执行自定义SQL</a></td> 
  </tr> 
  <tr> 
   <td>23</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124971821" rel="nofollow">MyBatis-plus配置自定义SQL(执行用户传入SQL)</a></td> 
  </tr> 
  <tr> 
   <td>24</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124973383" rel="nofollow">Mybatis-Plus(连接Hive)</a></td> 
  </tr> 
  <tr> 
   <td>25</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/122095848" rel="nofollow">Mybatis-Plus 代码生成器</a></td> 
  </tr> 
 </tbody> 
</table>

--------------------

### 文章目录 ###

*   *  一、数学函数
     *  二、字符串函数
     *  三、日期和时间函数
     *   *  3.1 概念
         *  3.2 函数使用：
         *   *  3.2.1 时间格式转换
     *  四、条件判断函数
     *  五、加密函数
     *  六、参考位置(菜鸟教程)

--------------------

*  提供的函数包括：数学函数、字符串函数、日期和时间函数、条件判断函数、加密函数等。
 *  CONCAT(str1,str2,…)：如果任何一个参数为NULL，则返回值为NULL 。

## 一、数学函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ABS(x)</td> 
   <td>返回 x 的绝对值</td> 
   <td>SELECT ABS(-1) – 返回1</td> 
  </tr> 
  <tr> 
   <td>SQRT(x)</td> 
   <td>返回x的平方根</td> 
   <td>SELECT SQRT(25) – 5</td> 
  </tr> 
  <tr> 
   <td>SIGN(x)</td> 
   <td>返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1</td> 
   <td>SELECT SIGN(-10) – (-1)</td> 
  </tr> 
  <tr> 
   <td>ROUND(x)</td> 
   <td>返回离 x 最近的整数</td> 
   <td>SELECT ROUND(1.23456) --1</td> 
  </tr> 
  <tr> 
   <td>RAND()</td> 
   <td>返回 0 到 1 的随机数</td> 
   <td>SELECT RAND() --0.93099315644334</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE(x,y)</td> 
   <td>返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）</td> 
   <td>SELECT TRUNCATE(1.23456,3) – 1.234</td> 
  </tr> 
 </tbody> 
</table>

## 二、字符串函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT(s1,s2…sn)</td> 
   <td>字符串 s1,s2 等多个字符串合并为一个字符串</td> 
   <td>SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", “Facebook”) AS ConcatenatedString;</td> 
  </tr> 
  <tr> 
   <td>CHAR_LENGTH(s)</td> 
   <td>返回字符串 s 的字符数</td> 
   <td>SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;</td> 
  </tr> 
  <tr> 
   <td>TRIM(s)</td> 
   <td>去掉字符串 s 开始和结尾处的空格</td> 
   <td>SELECT TRIM(’ RUNOOB ') AS TrimmedString;</td> 
  </tr> 
  <tr> 
   <td>RTRIM(s)</td> 
   <td>去掉字符串 s 结尾处的空格</td> 
   <td>SELECT RTRIM("RUNOOB ") AS RightTrimmedString; – RUNOOB</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(s, start, length)</td> 
   <td>从字符串 s 的 start 位置截取长度为 length 的子字符串</td> 
   <td>SELECT SUBSTRING(“RUNOOB”, 2, 3) AS ExtractString; – UNO</td> 
  </tr> 
  <tr> 
   <td>LOCATE(s1,s)</td> 
   <td>从字符串 s 中获取 s1 的开始位置</td> 
   <td>获取 b 在字符串 abc 中的位置：<br>SELECT LOCATE(‘st’,‘myteststring’); – 5<br>返回字符串 abc 中 b 的位置：<br>SELECT LOCATE(‘b’, ‘abc’) – 2</td> 
  </tr> 
 </tbody> 
</table>

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

### 3.1 概念 ###

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CURDATE()</td> 
   <td>返回当前日期</td> 
   <td>SELECT CURDATE(); <br>-&gt; 2018-09-19</td> 
  </tr> 
  <tr> 
   <td>CURTIME()</td> 
   <td>返回当前时间</td> 
   <td>SELECT CURTIME(); <br>-&gt; 19:59:02</td> 
  </tr> 
  <tr> 
   <td>SYSDATE()</td> 
   <td>返回当前日期和时间</td> 
   <td>SELECT SYSDATE() <br>-&gt; 2018-09-19 20:57:43</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(t)</td> 
   <td>将时间 t 转换为秒</td> 
   <td>SELECT TIME_TO_SEC(‘1:12:00’) <br>-&gt; 4320</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(t)</td> 
   <td>将时间 t 转换为秒</td> 
   <td>SELECT TIME_TO_SEC(‘1:12:00’) <br>-&gt; 4320</td> 
  </tr> 
  <tr> 
   <td>ADDDATE( )</td> 
   <td>指定日期的加运算</td> 
   <td>SELECT ADDDATE(CURRENT_DATE,10);<br>-&gt; 2022-01-23(当前日期+3天)</td> 
  </tr> 
  <tr> 
   <td>SBUDATE( )</td> 
   <td>指定日期的减运算</td> 
   <td>SELECT SUBDATE(CURRENT_DATE,10); <br>-&gt; 2022-01-17 (当前日期-3天)</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT( )</td> 
   <td>格式化输出日期和时间值</td> 
   <td>SELECT DATE_FORMAT(create_time,“%Y-%m-%d”) FROM student <br>-&gt; 2022-01-17</td> 
  </tr> 
 </tbody> 
</table>

### 3.2 函数使用： ###

#### 3.2.1 时间格式转换 ####

将create\_time中时间(yyyy-MM-dd HH-mm-ss)格式化为只有时间的(yyyy-MM-dd)  
**使用DATE\_FORMAT( )函数**

UPDATE student 
    SET create_date=DATE_FORMAT(create_time,"%Y-%m-%d");

**使用FROM\_UNIXTIME( )函数**

timestamp格式化为指定格式时间。

> FROM\_UNIXTIME(timestamp, “%Y-%m-%d”)

时间格式化为timestamp

> UNIX\_TIMESTAMP( )

UPDATE student
    SET create_date = FROM_UNIXTIME( UNIX_TIMESTAMP( create_time ), "%Y-%m-%d" );

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

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IF(expr,v1,v2)</td> 
   <td>如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。</td> 
   <td>SELECT IF(1 &gt; 0,‘正确’,‘错误’) <br> -&gt;正确</td> 
  </tr> 
  <tr> 
   <td>IFNULL(v1,v2)</td> 
   <td>如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。</td> 
   <td>SELECT IFNULL(null,‘Hello Word’) <br>-&gt;Hello Word</td> 
  </tr> 
 </tbody> 
</table>

## 五、加密函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MD5(str)</td> 
   <td>对字符串str进行MD5加密</td> 
  </tr> 
  <tr> 
   <td>ENCODE(str,pwd_str)</td> 
   <td>使用pwd作为密码加密字符串str</td> 
  </tr> 
  <tr> 
   <td>DECODE(str,pwd_str)</td> 
   <td>使用pwd作为密码解密字符串str</td> 
  </tr> 
 </tbody> 
</table>

## 六、参考位置(菜鸟教程) ##

[聚合函数][Link 1]

https://www.runoob.com/mysql/mysql-functions.html

[Link 1]: https://www.runoob.com/mysql/mysql-functions.html