4、（常练习）单行函数（包含数值函数、字符串函数、日期和时间函数） -mysql

小鱼儿 2024-03-31 08:26 84阅读 0赞

#### 单行函数 ####

*  单行函数
 *   *  一、函数的理解
     *  二、数值函数
     *   *   *  1、基本函数
             *  2、角度与弧度互换函数
             *  3、三角函数
             *  4、指数与对数
             *  5、进制间的转换
     *  三、字符串函数
     *  四、日期和时间函数
     *   *   *  1、获取日期、时间
             *  2、日期与时间戳的转换
             *  3、获取月份、星期、星期数、天数等函数
             *  4、日期的操作函数
             *  5、时间和秒钟转换的函数
             *  6、计算日期和时间的函数
             *  7、日期的格式化与解析
     *  五、流程控制函数
     *  六、加密与解密函数
     *  七. MySQL信息函数
     *  八、其他函数
     *  九、Mysql的truncate、format、convert和round保留小数（四舍五入）的区别
     *  十、课后练习

## 单行函数 ##

————————————————————————————————————————————————————

### 一、函数的理解 ###

可以把我们经常使用的代码 **封装** 起来，需要的时候直接调用即可。

这样既 `提高了代码效率` ，又 `提高了可维护性` 。

在 `SQL` 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对`数据库的管理效率` 。

![在这里插入图片描述][da88a485ad674f6daa2dfdb7f9e3f541.png]  
从函数定义的角度出发，我们可以将函数分成 `内置函数` 和 `自定义函数` 。

*  **MySQL的内置函数及分类**
    
     *  单行函数
     *  聚合函数（或分组函数）

![在这里插入图片描述][fca3ec3a47764de3bc508f0aa6e93b49.png]

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

### 二、数值函数 ###

##### 1、基本函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>ABS(x)</strong></td> 
   <td>返回<code>x的绝对值</code></td> 
  </tr> 
  <tr> 
   <td><strong>SIGN(X)</strong></td> 
   <td>返回X的符号。正数返回1，负数返回-1，0返回0</td> 
  </tr> 
  <tr> 
   <td><strong>PI()</strong></td> 
   <td>返回<code>圆周率</code>的值</td> 
  </tr> 
  <tr> 
   <td><strong>CEIL(x)，CEILING(x)</strong> 天花板函数</td> 
   <td>返回大于或等于某个值的最小整数</td> 
  </tr> 
  <tr> 
   <td><strong>FLOOR(x)</strong> 地板函数</td> 
   <td>返回小于或等于某个值的最大整数</td> 
  </tr> 
  <tr> 
   <td>LEAST(e1,e2,e3…)</td> 
   <td>返回列表中的最小值</td> 
  </tr> 
  <tr> 
   <td>GREATEST(e1,e2,e3…)</td> 
   <td>返回列表中的最大值</td> 
  </tr> 
  <tr> 
   <td>MOD(x,y)</td> 
   <td>返回X除以Y后的余数</td> 
  </tr> 
  <tr> 
   <td><strong>RAND()</strong></td> 
   <td>返回0~1的随机值</td> 
  </tr> 
  <tr> 
   <td><strong>RAND(x)</strong></td> 
   <td>返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数</td> 
  </tr> 
  <tr> 
   <td><code>ROUND(x)</code></td> 
   <td>返回一个对x的值进行四舍五入后，最接近于X的整数</td> 
  </tr> 
  <tr> 
   <td><code>ROUND(x,y)</code></td> 
   <td>返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE(x,y)</td> 
   <td>返回数字x截断为y位小数的结果</td> 
  </tr> 
  <tr> 
   <td>SQRT(x)</td> 
   <td>返回x的平方根。当X的值为负数时，返回NULL</td> 
  </tr> 
 </tbody> 
</table>

mysql> SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
        ->  FLOOR(-43.23),MOD(12,5),12 MOD 5, 12 % 5
        ->  FROM DUAL;
    +-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+----------+--------+
    | ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI()     | CEIL(32.32) | CEILING(-43.23) | FLOOR(32.32) | FLOOR(-43.23) | MOD(12,5) | 12 MOD 5 | 12 % 5 |
    +-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+----------+--------+
    |       123 |      32 |        -1 |        1 | 3.141593 |          33 |             -43 |           32 |           -44 |         2 |        2 |      2 |
    +-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+----------+--------+
    1 row in set (0.00 sec)

# RAND() 不带参数的随机数会一直变化 
    mysql> SELECT RAND(),RAND(),RAND(10),RAND(10), RAND(-1),RAND(-1)
        -> FROM DUAL;
    +--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
    | RAND()             | RAND()             | RAND(10)           | RAND(10)           | RAND(-1)           | RAND(-1)           |
    +--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
    | 0.7666654240029542 | 0.4990492272181895 | 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
    +--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
    1 row in set (0.00 sec)

*  **round 四舍五入**
    
        mysql> select round(123.556),round(123.456,0),round(123.456, 1),round(123.456,2),round(123.456,-1),round(123.456, -2)
            -> from dual;
        +----------------+------------------+-------------------+------------------+-------------------+--------------------+
        | round(123.556) | round(123.456,0) | round(123.456, 1) | round(123.456,2) | round(123.456,-1) | round(123.456, -2) |
        +----------------+------------------+-------------------+------------------+-------------------+--------------------+
        |            124 |              123 |             123.5 |           123.46 |               120 |                100 |
        +----------------+------------------+-------------------+------------------+-------------------+--------------------+
        1 row in set (1.65 sec)
 *  **truncate 截断**
    
        mysql> select truncate(123.456, 0),truncate(123.496, 1),truncate(129.45, -1) from dual;
        +----------------------+----------------------+----------------------+
        | truncate(123.456, 0) | truncate(123.496, 1) | truncate(129.45, -1) |
        +----------------------+----------------------+----------------------+
        |                  123 |                123.4 |                  120 |
        +----------------------+----------------------+----------------------+
 *  **单行函数可以嵌套**
    
        mysql> select truncate(round(123.456, 2), 0) from dual;
        +--------------------------------+
        | truncate(round(123.456, 2), 0) |
        +--------------------------------+
        |                            123 |
        +--------------------------------+

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

##### 2、角度与弧度互换函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>RADIANS(x)</td> 
   <td>将角度转化为弧度，其中，参数x为角度值</td> 
  </tr> 
  <tr> 
   <td>DEGREES(x)</td> 
   <td>将弧度转化为角度，其中，参数x为弧度值</td> 
  </tr> 
 </tbody> 
</table>

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

##### 3、三角函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIN(x)</td> 
   <td>返回x的<code>正弦值</code>，其中，参数x为弧度值</td> 
  </tr> 
  <tr> 
   <td>ASIN(x)</td> 
   <td>返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL</td> 
  </tr> 
  <tr> 
   <td>COS(x)</td> 
   <td>返回x的余弦值，其中，参数x为弧度值</td> 
  </tr> 
  <tr> 
   <td>ACOS(x)</td> 
   <td>返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL</td> 
  </tr> 
  <tr> 
   <td>TAN(x)</td> 
   <td>返回x的正切值，其中，参数x为弧度值</td> 
  </tr> 
  <tr> 
   <td>ATAN(x)</td> 
   <td>返回x的反正切值，即返回正切值为x的值</td> 
  </tr> 
  <tr> 
   <td>ATAN2(m,n)</td> 
   <td>返回两个参数的反正切值</td> 
  </tr> 
  <tr> 
   <td>COT(x)</td> 
   <td>返回x的余切值，其中，X为弧度值</td> 
  </tr> 
 </tbody> 
</table>

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

##### 4、指数与对数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>POW(x,y)，POWER(X,Y)</td> 
   <td>返回x的y次方</td> 
  </tr> 
  <tr> 
   <td>EXP(X)</td> 
   <td>返回e的X次方，其中e是一个常数，2.718281828459045</td> 
  </tr> 
  <tr> 
   <td>LN(X)，LOG(X)</td> 
   <td>返回以e为底的X的对数，当X &lt;= 0 时，返回的结果为NULL</td> 
  </tr> 
  <tr> 
   <td>LOG10(X)</td> 
   <td>返回以10为底的X的对数，当X &lt;= 0 时，返回的结果为NULL</td> 
  </tr> 
  <tr> 
   <td>LOG2(X)</td> 
   <td>返回以2为底的X的对数，当X &lt;= 0 时，返回NULL</td> 
  </tr> 
 </tbody> 
</table>

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

##### 5、进制间的转换 #####

<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,f1,f2)</td> 
   <td>返回f1进制数变成f2进制数</td> 
  </tr> 
 </tbody> 
</table>

mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
        -> FROM DUAL;
    +---------+---------+---------+--------------+
    | BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
    +---------+---------+---------+--------------+
    | 1010    | A       | 12      | 2            |
    +---------+---------+---------+--------------+
    1 row in set (0.00 sec)

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

### 三、字符串函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>ASCII(S)</strong></td> 
   <td>返回字符串S中的第一个字符的<code>ASCII</code>码值</td> 
  </tr> 
  <tr> 
   <td><code>CHAR_LENGTH(s)</code></td> 
   <td>返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同</td> 
  </tr> 
  <tr> 
   <td><code>LENGTH(s)</code></td> 
   <td>返回字符串s的字节数，和字符集有关</td> 
  </tr> 
  <tr> 
   <td><strong>CONCAT(s1,s2,…,sn)</strong></td> 
   <td>连接s1,s2,…,sn为一个字符串</td> 
  </tr> 
  <tr> 
   <td><strong>CONCAT_WS(x, s1,s2,…,sn)</strong></td> 
   <td>同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x</td> 
  </tr> 
  <tr> 
   <td><code>INSERT(str, idx, len, replacestr)</code></td> 
   <td>将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr</td> 
  </tr> 
  <tr> 
   <td><code>REPLACE(str, a, b)</code></td> 
   <td>用字符串b替换字符串str中所有出现的字符串a</td> 
  </tr> 
  <tr> 
   <td>UPPER(s) 或 UCASE(s)</td> 
   <td>将字符串s的所有字母转成大写字母</td> 
  </tr> 
  <tr> 
   <td>LOWER(s) 或LCASE(s)</td> 
   <td>将字符串s的所有字母转成小写字母</td> 
  </tr> 
  <tr> 
   <td><strong>LEFT(str,n)</strong></td> 
   <td>返回字符串str最左边的n个字符</td> 
  </tr> 
  <tr> 
   <td><strong>RIGHT(str,n)</strong></td> 
   <td>返回字符串str最右边的n个字符</td> 
  </tr> 
  <tr> 
   <td><code>LPAD(str, len, pad)</code></td> 
   <td>用字符串pad对str最左边进行填充，直到str的长度为len个字符</td> 
  </tr> 
  <tr> 
   <td><code>RPAD(str ,len, pad)</code></td> 
   <td>用字符串pad对str最右边进行填充，直到str的长度为len个字符</td> 
  </tr> 
  <tr> 
   <td><strong>LTRIM(s)</strong></td> 
   <td>去掉字符串s<code>左侧的空格</code></td> 
  </tr> 
  <tr> 
   <td><strong>RTRIM(s)</strong></td> 
   <td>去掉字符串s<code>右侧的空格</code></td> 
  </tr> 
  <tr> 
   <td><strong>TRIM(s)</strong></td> 
   <td>去掉字符串s<code>开始与结尾的空格</code></td> 
  </tr> 
  <tr> 
   <td><strong>TRIM(s1 FROM s)</strong></td> 
   <td>去掉字符串s<code>开始与结尾的s1</code></td> 
  </tr> 
  <tr> 
   <td><strong>TRIM(LEADING s1 FROM s)</strong></td> 
   <td>去掉<code>字符串s开始处的s1</code></td> 
  </tr> 
  <tr> 
   <td><strong>TRIM(TRAILING s1 FROM s)</strong></td> 
   <td>去掉字符串s结尾处的s1</td> 
  </tr> 
  <tr> 
   <td>REPEAT(str, n)</td> 
   <td>返回str重复n次的结果</td> 
  </tr> 
  <tr> 
   <td>SPACE(n)</td> 
   <td>返回n个空格</td> 
  </tr> 
  <tr> 
   <td>STRCMP(s1,s2)</td> 
   <td>比较字符串s1,s2的ASCII码值的大小</td> 
  </tr> 
  <tr> 
   <td>SUBSTR(s,index,len)</td> 
   <td>返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同</td> 
  </tr> 
  <tr> 
   <td>LOCATE(substr,str)</td> 
   <td>返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0</td> 
  </tr> 
  <tr> 
   <td>ELT(m,s1,s2,…,sn)</td> 
   <td>返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn</td> 
  </tr> 
  <tr> 
   <td>FIELD(s,s1,s2,…,sn)</td> 
   <td>返回字符串s在字符串列表中第一次出现的位置</td> 
  </tr> 
  <tr> 
   <td>FIND_IN_SET(s1,s2)</td> 
   <td>返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串</td> 
  </tr> 
  <tr> 
   <td>REVERSE(s)</td> 
   <td>返回s反转后的字符串</td> 
  </tr> 
  <tr> 
   <td>NULLIF(value1,value2)</td> 
   <td>比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1</td> 
  </tr> 
 </tbody> 
</table>

*  **insert / replace 替换**

mysql> select insert('helloworld', 2, 3, 'aaaaa'),replace('hello','ll','mmm') from dual;
    +-------------------------------------+-----------------------------+
    | insert('helloworld', 2, 3, 'aaaaa') | replace('hello','ll','mmm') |
    +-------------------------------------+-----------------------------+
    | haaaaaoworld                        | hemmmo                      |
    +-------------------------------------+-----------------------------+

> 注意：MySQL中，字符串的位置是从1开始的。

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

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

##### 1、获取日期、时间 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CURDATE() ，<br>CURRENT_DATE()</td> 
   <td>返回当前日期，只包含年、月、日</td> 
  </tr> 
  <tr> 
   <td>CURTIME() ， <br>CURRENT_TIME()</td> 
   <td>返回当前时间，只包含时、分、秒</td> 
  </tr> 
  <tr> 
   <td>NOW() <br> SYSDATE() <br> CURRENT_TIMESTAMP() <br> LOCALTIME() <br> LOCALTIMESTAMP()</td> 
   <td>返回当前系统日期和时间</td> 
  </tr> 
  <tr> 
   <td>UTC_DATE()</td> 
   <td>返回UTC（世界标准时间）日期</td> 
  </tr> 
  <tr> 
   <td>UTC_TIME()</td> 
   <td>返回UTC（世界标准时间）时间</td> 
  </tr> 
 </tbody> 
</table>

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

##### 2、日期与时间戳的转换 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>UNIX_TIMESTAMP()</td> 
   <td>以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() -&gt;1634348884</td> 
  </tr> 
  <tr> 
   <td>UNIX_TIMESTAMP(date)</td> 
   <td>将时间date以UNIX时间戳的形式返回。</td> 
  </tr> 
  <tr> 
   <td>FROM_UNIXTIME(timestamp)</td> 
   <td>将UNIX时间戳的时间转换为普通格式的时间</td> 
  </tr> 
 </tbody> 
</table>

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

##### 3、获取月份、星期、星期数、天数等函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>YEAR(date) / MONTH(date) / DAY(date)</td> 
   <td>返回具体的日期值</td> 
  </tr> 
  <tr> 
   <td>HOUR(time) / MINUTE(time) / SECOND(time)</td> 
   <td>返回具体的时间值</td> 
  </tr> 
  <tr> 
   <td>MONTHNAME(date)</td> 
   <td>返回月份：January，…</td> 
  </tr> 
  <tr> 
   <td>DAYNAME(date)</td> 
   <td>返回星期几：MONDAY，TUESDAY…SUNDAY</td> 
  </tr> 
  <tr> 
   <td>WEEKDAY(date)</td> 
   <td>返回周几，注意，周1是0，周2是1，。。。周日是6</td> 
  </tr> 
  <tr> 
   <td>QUARTER(date)</td> 
   <td>返回日期对应的季度，范围为1～4</td> 
  </tr> 
  <tr> 
   <td>WEEK(date) ， WEEKOFYEAR(date)</td> 
   <td>返回一年中的第几周</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR(date)</td> 
   <td>返回日期是一年中的第几天</td> 
  </tr> 
  <tr> 
   <td>DAYOFMONTH(date)</td> 
   <td>返回日期位于所在月份的第几天</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK(date)</td> 
   <td>返回周几，注意：周日是1，周一是2，。。。周六是7</td> 
  </tr> 
 </tbody> 
</table>

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

##### 4、日期的操作函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>EXTRACT(type FROM date)</td> 
   <td>返回指定日期中特定的部分，type指定返回的值</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][5a7c82e36a894713a34115a3c2c0740a.png]

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
    EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
    FROM DUAL;

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

##### 5、时间和秒钟转换的函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TIME_TO_SEC(time)</td> 
   <td>将 time 转化为秒并返回结果值。转化的公式为：小时<em>3600+分钟</em>60+秒</td> 
  </tr> 
  <tr> 
   <td>SEC_TO_TIME(seconds)</td> 
   <td>将 seconds 描述转化为包含小时、分钟和秒的时间</td> 
  </tr> 
 </tbody> 
</table>

mysql> SELECT TIME_TO_SEC(NOW());
    +--------------------+
    | TIME_TO_SEC(NOW()) |
    +--------------------+
    |               78774 |
    +--------------------+
    1 row in set (0.00 sec)
    
    #----------------
    
    mysql> SELECT SEC_TO_TIME(78774);
    +--------------------+
    | SEC_TO_TIME(78774) |
    +--------------------+
    | 21:52:54            |
    +--------------------+
    1 row in set (0.12 sec)

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

##### 6、计算日期和时间的函数 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)</td> 
   <td>返回与给定日期时间相差INTERVAL时间段的日期时间</td> 
  </tr> 
  <tr> 
   <td>DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)</td> 
   <td>返回与date相差INTERVAL时间间隔的日期</td> 
  </tr> 
 </tbody> 
</table>

上述函数中`type`的取值：  
![在这里插入图片描述][2bc2432fc987491cb81a63cb6139262b.png]

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
    ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
    DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
    DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
    DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
    FROM DUAL;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
    SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
    DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
    FROM DUAL;

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ADDTIME(time1,time2)</td> 
   <td>返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数</td> 
  </tr> 
  <tr> 
   <td>SUBTIME(time1,time2)</td> 
   <td>返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF(date1,date2)</td> 
   <td>返回date1 - date2的日期间隔天数</td> 
  </tr> 
  <tr> 
   <td>TIMEDIFF(time1, time2)</td> 
   <td>返回time1 - time2的时间间隔</td> 
  </tr> 
  <tr> 
   <td>FROM_DAYS(N)</td> 
   <td>返回从0000年1月1日起，N天以后的日期</td> 
  </tr> 
  <tr> 
   <td>TO_DAYS(date)</td> 
   <td>返回日期date距离0000年1月1日的天数</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY(date)</td> 
   <td>返回date所在月份的最后一天的日期</td> 
  </tr> 
  <tr> 
   <td>MAKEDATE(year,n)</td> 
   <td>针对给定年份与所在年份中的天数返回一个日期</td> 
  </tr> 
  <tr> 
   <td>MAKETIME(hour,minute,second)</td> 
   <td>将给定的小时、分钟和秒组合成时间并返回</td> 
  </tr> 
  <tr> 
   <td>PERIOD_ADD(time,n)</td> 
   <td>返回time加上n后的时间</td> 
  </tr> 
 </tbody> 
</table>

#查询 7 天内的新增用户数有多少？
    
    SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

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

##### 7、日期的格式化与解析 #####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATE_FORMAT(date,fmt)</td> 
   <td>按照字符串fmt格式化日期date值</td> 
  </tr> 
  <tr> 
   <td>TIME_FORMAT(time,fmt)</td> 
   <td>按照字符串fmt格式化时间time值</td> 
  </tr> 
  <tr> 
   <td>GET_FORMAT(date_type,format_type)</td> 
   <td>返回日期字符串的显示格式</td> 
  </tr> 
  <tr> 
   <td>STR_TO_DATE(str, fmt)</td> 
   <td>按照字符串fmt对str进行解析，解析为一个日期</td> 
  </tr> 
 </tbody> 
</table>

上述`非GET_FORMAT函数`中fmt参数常用的格式符：

<table> 
 <thead> 
  <tr> 
   <th>格式符</th> 
   <th>说明</th> 
   <th>格式符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%Y</td> 
   <td>4位数字表示年份</td> 
   <td>%y</td> 
   <td>表示两位数字表示年份</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td>月名表示月份（January,…）</td> 
   <td>%m</td> 
   <td>两位数字表示月份（01,02,03。。。）</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td>缩写的月名（Jan.，Feb.，…）</td> 
   <td>%c</td> 
   <td>数字表示月份（1,2,3,…）</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>英文后缀表示月中的天数（1st,2nd,3rd,…）</td> 
   <td>%d</td> 
   <td>两位数字表示月中的天数(01,02…)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>数字形式表示月中的天数（1,2,3,4,5…）</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td>两位数字表示小数，24小时制（01,02…）</td> 
   <td>%h和%I</td> 
   <td>两位数字表示小时，12小时制（01,02…）</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>数字形式的小时，24小时制(1,2,3)</td> 
   <td>%l</td> 
   <td>数字形式表示小时，12小时制（1,2,3,4…）</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td>两位数字表示分钟（00,01,02）</td> 
   <td>%S和%s</td> 
   <td>两位数字表示秒(00,01,02…)</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>一周中的星期名称（Sunday…）</td> 
   <td>%a</td> 
   <td>一周中的星期缩写（Sun.，Mon.,Tues.，…）</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>以数字表示周中的天数(0=Sunday,1=Monday…)</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>以3位数字表示年中的天数(001,002…)</td> 
   <td>%U</td> 
   <td>以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第</td> 
  </tr> 
  <tr> 
   <td>%u</td> 
   <td>以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td>24小时制</td> 
   <td>%r</td> 
   <td>12小时制</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>AM或PM</td> 
   <td>%%</td> 
   <td>表示%</td> 
  </tr> 
 </tbody> 
</table>

`GET_FORMAT`函数中`date_type`和`format_type`参数取值如下：  
![在这里插入图片描述][973e552c8adc43b8ad3251992bebb5cf.png]

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
    +--------------------------------+
    | DATE_FORMAT(NOW(), '%H:%i:%s') |
    +--------------------------------+
    | 22:57:34                        |
    +--------------------------------+
    1 row in set (0.00 sec)

SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
    FROM DUAL;
    
    SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
    FROM DUAL;
    
    SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
    FROM DUAL;

mysql> SELECT GET_FORMAT(DATE, 'USA');
    +-------------------------+
    | GET_FORMAT(DATE, 'USA') |
    +-------------------------+
    | %m.%d.%Y                |
    +-------------------------+
    1 row in set (0.00 sec)
    
    SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
    FROM DUAL;

mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d'); 
    +-----------------------------------------------+
    | STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
    +-----------------------------------------------+
    | 2020-01-01                                    |
    +-----------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

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

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

`MySQL`中的流程处理函数主要包括`IF()`、`IFNULL()`和`CASE()`函数。

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>IF(value,value1,value2)</code></td> 
   <td>如果value的值为TRUE，返回value1，否则返回value2<br>类似于三元运算符：<a href="https://blog.csdn.net/feizuiku0116/article/details/121797866" rel="nofollow">https://blog.csdn.net/feizuiku0116/article/details/121797866</a></td> 
  </tr> 
  <tr> 
   <td><code>IFNULL(value1, value2)</code></td> 
   <td>如果value1不为NULL，返回value1，否则返回value2</td> 
  </tr> 
  <tr> 
   <td>CASE WHEN 条件1 THEN 结果1 <br>WHEN 条件2 THEN 结果2 … <br>[ELSE resultn] END</td> 
   <td>相当于Java的if…else if…else…</td> 
  </tr> 
  <tr> 
   <td>CASE expr WHEN 常量值1 THEN 值1 <br>WHEN 常量值1 THEN 值1 … <br>[ELSE 值n] END</td> 
   <td>相当于Java的switch…case…</td> 
  </tr> 
 </tbody> 
</table>

SELECT IF(1 > 0,'正确','错误')    
    ->正确

SELECT IFNULL(null,'Hello Word')
    ->Hello Word

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

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

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
    				  WHEN salary>=10000 THEN '潜力股'  
    				  WHEN salary>=8000 THEN '屌丝' 
    				  ELSE '草根' END  "描述"
    FROM employees;

SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款' 
    								   WHEN 2 THEN '已付款' 
    								   WHEN 3 THEN '已发货'  
    								   WHEN 4 THEN '确认收货'  
    								   ELSE '无效订单' END 
    FROM t_order;

mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
    +---------------------------------------------------------------------+
    | CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
    +---------------------------------------------------------------------+
    | yes                                                                  |
    +---------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;  
    +--------------------------------------------------------------------+
    | CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
    +--------------------------------------------------------------------+
    | unknown                                                             |
    +--------------------------------------------------------------------+
    1 row in set (0.00 sec)

mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
    +------------------------------------------------+
    | CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
    +------------------------------------------------+
    |                                               1 |
    +------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
    +-------------------------------------------------+
    | CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
    +-------------------------------------------------+
    |                                               -1 |
    +-------------------------------------------------+
    1 row in set (0.00 sec)

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
    FROM employees;

SELECT last_name, job_id, salary,
           CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                       WHEN 'ST_CLERK' THEN  1.15*salary
                       WHEN 'SA_REP'   THEN  1.20*salary
           			   ELSE  salary END "REVISED_SALARY"
    FROM   employees;

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

### 六、加密与解密函数 ###

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。  
**8.0已弃用，不推荐使用**

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>PASSWORD(str)</td> 
   <td>返回字符串str的加密版本，41位长的字符串。加密结果<code>不可逆</code>，常用于用户的密码加密</td> 
  </tr> 
  <tr> 
   <td>MD5(str)</td> 
   <td>返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL</td> 
  </tr> 
  <tr> 
   <td>SHA(str)</td> 
   <td>从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。</td> 
  </tr> 
  <tr> 
   <td>ENCODE(value,password_seed)</td> 
   <td>返回使用password_seed作为加密密码加密value</td> 
  </tr> 
  <tr> 
   <td>DECODE(value,password_seed)</td> 
   <td>返回使用password_seed作为加密密码解密value</td> 
  </tr> 
 </tbody> 
</table>

可以看到，`ENCODE(value,password_seed)`函数与`DECODE(value,password_seed)`函数互为反函数。

举例：

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
    +-------------------------------------------+----------------+
    | PASSWORD('mysql')                         | PASSWORD(NULL) |
    +-------------------------------------------+----------------+
    | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                |
    +-------------------------------------------+----------------+
    1 row in set, 1 warning (0.00 sec)

SELECT md5('123')
    ->202cb962ac59075b964b07152d234b70

SELECT SHA('Tom123')
    ->c7c506980abc31cc390a2438c90861d0f1216d50

mysql> SELECT ENCODE('mysql', 'mysql');
    +--------------------------+
    | ENCODE('mysql', 'mysql') |
    +--------------------------+
    | íg　¼　ìÉ                  |
    +--------------------------+
    1 row in set, 1 warning (0.01 sec)

mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
    +-----------------------------------------+
    | DECODE(ENCODE('mysql','mysql'),'mysql') |
    +-----------------------------------------+
    | mysql                                   |
    +-----------------------------------------+
    1 row in set, 2 warnings (0.00 sec)

### 七. MySQL信息函数 ###

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>VERSION()</td> 
   <td>返回当前MySQL的版本号</td> 
  </tr> 
  <tr> 
   <td>CONNECTION_ID()</td> 
   <td>返回当前MySQL服务器的连接数</td> 
  </tr> 
  <tr> 
   <td>DATABASE()，<br>SCHEMA()</td> 
   <td>返回MySQL命令行当前所在的数据库</td> 
  </tr> 
  <tr> 
   <td>USER()，<br>CURRENT_USER()、<br>SYSTEM_USER()，<br>SESSION_USER()</td> 
   <td>返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”</td> 
  </tr> 
  <tr> 
   <td>CHARSET(value)</td> 
   <td>返回字符串value自变量的字符集</td> 
  </tr> 
  <tr> 
   <td>COLLATION(value)</td> 
   <td>返回字符串value的比较规则</td> 
  </tr> 
 </tbody> 
</table>

### 八、其他函数 ###

`MySQL`中有些函数无法对其进行具体的分类，但是这些函数在`MySQL`的开发和运维过程中也是不容忽视的。

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>FORMAT(value,n)</td> 
   <td>返回对数字value进行格式化后的结果数据。n表示<code>四舍五入</code>后保留到小数点后n位</td> 
  </tr> 
  <tr> 
   <td>CONV(value,from,to)</td> 
   <td>将value的值<code>进行不同进制之间的转换</code></td> 
  </tr> 
  <tr> 
   <td><code>INET_ATON(ipvalue)</code></td> 
   <td>将以点分隔的IP地址转化为一个数字</td> 
  </tr> 
  <tr> 
   <td><code>INET_NTOA(value)</code></td> 
   <td>将数字形式的IP地址转化为以点分隔的IP地址</td> 
  </tr> 
  <tr> 
   <td>BENCHMARK(n,expr)</td> 
   <td>将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间</td> 
  </tr> 
  <tr> 
   <td>CONVERT(value USING char_code)</td> 
   <td>将value所使用的字符编码修改为char_code</td> 
  </tr> 
 </tbody> 
</table>

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

### 九、Mysql的truncate、format、convert和round保留小数（四舍五入）的区别 ###

参考地址：[https://blog.csdn.net/qq\_37493556/article/details/98079305][https_blog.csdn.net_qq_37493556_article_details_98079305]

![在这里插入图片描述][442d2c68c4c340aa8c01ec5f37a451d1.png]  
![在这里插入图片描述][28f0678a2faf4eb698095f14a6ad6308.png]

### 十、课后练习 ###

# 1、显示系统时间（注：日期 + 时间）
    SELECT NOW(),SYSDATE(),CURRENT_TIME(),LOCALTIME(),LOCALTIMESTAMP() FROM DUAL;
    
    # 2、查询员工号，姓名，工资以及工资提高20%后的结果（new salary）
    SELECT employee_id,last_name,salary,salary * (1+0.2) AS "new salary" FROM employees;
    
    # 3、将员工的姓名按首字母排序，并写出姓名的长度（length）
    SELECT last_name,LENGTH(last_name) AS "name_length" FROM employees 
    # order by last_name ASC;
    ORDER BY name_length ASC;
    
    # 4、查询员工 id,last_name,salary 并作为一个列输出，别名为 out_put
    SELECT CONCAT(employee_id,'-',last_name,'-',salary) AS "out_put"
    FROM employees;
    
    # 5、查询公司各员工工作的年数、工作的天数、并按工作年数的降序排序
    SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 AS "worked_years",DATEDIFF(CURDATE(),hire_date) AS "worded_days",
    TO_DAYS(CURDATE()) - TO_DAYS(hire_date) AS "worked_days1"
    FROM employees
    ORDER BY worked_years DESC;
    
    # 6、查询员工姓名，hire_date,department_id,满足以下条件：
    # 雇用时间在1997年之后，department_id为80或90或110,commission_pct不为空
    SELECT last_name,hire_date,department_id
    FROM employees
    WHERE department_id IN (80,90,110) 
    AND commission_pct IS NOT NULL
    # and hire_date >= '1997-01-01' # 存在着隐式转换
    # and date_format(hire_date,'%Y-%m-%d') >= '1997-01-01' # 显式转换操作，格式化：日期--> 字符串
    # and date_format(hire_date,'%Y') >= '1997' # 显式转换操作，格式化
    # and hire_date >= str_to_date('1997-01-01','%Y-%m-%d')
    AND YEAR(hire_date) >= '1997' 
    ORDER BY hire_date;
    
    # 7、查询公司中入职超过10000天的员工姓名、入职时间
    SELECT last_name,hire_date, DATEDIFF(NOW(),hire_date) AS "hire_days"
    FROM employees
    WHERE DATEDIFF(NOW(),hire_date) >= 10000 
    ORDER BY hire_days DESC; 
    
    # 8、做一个查询，产生下面的结果
    # <last_name> earns <salary> monthly but wants <salary * 3>
    
    SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary,0), ' monthly but wants ', TRUNCATE((salary * 3),0)) AS "Dream Salary"
    FROM employees;
    
    # 9、使用case-when,按照下面的条件
    /* job 	  	grade
    AD_PRES		  A
    ST_MAN		  B
    IT_PROG		  C
    SA_PER		  D
    ST_CLERK	  E  	
    
    产生下面的结果：*/
    SELECT last_name,job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'
    			     WHEN 'ST_MAN'  THEN 'B'
    			     WHEN 'IT_PROG' THEN 'C'
    			     WHEN 'SA_PER'  THEN 'D'
    			     WHEN 'ST_CLERK'THEN 'E'
    			     ELSE 'undefined' END AS "grade"	
    FROM employees;

[da88a485ad674f6daa2dfdb7f9e3f541.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/301a9ca5597843e5850f4d777a3f9ce9.png
[fca3ec3a47764de3bc508f0aa6e93b49.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d2238bc67d5a4932a4ac9b787d415e4a.png
[5a7c82e36a894713a34115a3c2c0740a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4014285a8f134084a8ae60c8d2b0f462.png
[2bc2432fc987491cb81a63cb6139262b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a4735a10bc57410582e7301ac0590779.png
[973e552c8adc43b8ad3251992bebb5cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e3ebc928724540db95014dfe3bcec005.png
[https_blog.csdn.net_qq_37493556_article_details_98079305]: https://blog.csdn.net/qq_37493556/article/details/98079305
[442d2c68c4c340aa8c01ec5f37a451d1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ff1037a823dd42608dcfad88f7a2c0f7.png
[28f0678a2faf4eb698095f14a6ad6308.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/be648a2b864046bfa1487c968a953a7a.png