25.4 MySQL 函数

开局签到Python基础

已于 2024-03-08 01:10:23 修改

阅读量483

点赞数 5

分类专栏： Python 从入门到全栈开发文章标签： mysql 数据库

于 2024-03-08 01:10:12 首次发布

风

本文链接：https://blog.csdn.net/qq_46137324/article/details/136549872

版权

Python 从入门到全栈开发专栏收录该内容

79 篇文章 12 订阅

订阅专栏

2024-03-08_010523

1. 函数的介绍

1.1 函数简介

在编程中, 函数是一种组织代码的方式, 用于执行特定任务.
它是一段可以被重复使用的代码块, 通常接受一些输入(参数)然后返回一个输出.
函数可以帮助开发者将大型程序分解为更小的, 更易于管理的部分, 提高代码的可读性和可维护性.

函数在编程语言和SQL中都扮演着非常重要的角色.
以下是对函数的一些详细解释:
* 1. 代码重用和模块化: 函数允许我们将一段常用的代码块封装起来, 并在需要时多次调用.
     这避免了代码的重复, 并使得代码结构更清晰, 更易于阅读和维护.

* 2. 提高开发效率: 使用函数, 可以更快地开发应用程序.
     由于函数是预先编写和测试过的代码块, 因此在使用它们时, 不必从零开始编写代码, 只需调用现有的函数即可.

* 3. 提高代码可维护性: 如果某个功能需要修改或更新, 只需要修改相应的函数, 而无需查找并修改代码中所有相关的部分.
     这使得代码更易于维护, 减少了出错的可能性.

在SQL中, 函数的作用同样强大:
* 1. 数据转换和处理: SQL函数可以用来转换和处理检索到的数据.
     例如, 可以用函数来处理字符串, 数值, 或者执行一些复杂的计算.

* 2. 提高查询效率: 通过使用SQL函数, 可以在数据库查询中包含复杂的逻辑和操作, 而无需在应用程序代码中执行这些操作.
     这可以大大提高查询效率, 并减少网络传输的数据量.

* 3. 增强数据管理能力: SQL函数的使用可以让数据库管理员更有效地管理数据库.
     例如, 可以编写函数来执行常规任务, 如备份, 恢复或清理数据, 从而提高管理效率.

从函数定义的角度来看, 函数可以分为"内置函数"和"自定义函数"两类:
* 1. 内置函数: 是由编程语言或数据库系统预先定义好的函数.
     它们通常是通用功能, 例如字: 符串处理, 数学计算, 日期和时间操作等.
     
* 2. 自定义函数: 是开发人员根据自己的需求编写的函数.
     它们通常用于实现特定业务逻辑或特定任务.

无论是内置函数还是自定义函数, 它们的目的都是为了提高代码的可重用性, 可读性和维护性;
正确, 合理地使用函数, 可以使代码结构更加清晰, 提高开发效率, 减少错误, 并方便后续的修改和扩展.

1.2 不同DBMS函数的差异

不同的数据库管理系统(DBMS)在函数支持方面存在很大的差异性.
尽管SQL语言本身是标准化的, 但各个DBMS厂商在实现时, 为了满足特定的需求或者提供更高的性能, 都会增加一些特定的函数和功能.

这些差异可能包括以下几个方面:
* 1. 函数名称和语法: 不同的DBMS可能使用不同的函数名称和语法来完成相同的操作.
     例如, 字符串拼接操作, 一些DBMS使用(||)或者(+)作为拼接符, 而MySQL使用concat()函数.
  
* 2. 函数参数和返回值: 即使不同DBMS中存在相同名称的函数, 它们的参数和返回值也可能有所不同.
     一些函数可能在某些DBMS中接受更多的参数, 或者在返回结果方面有所不同.
  
* 3. 函数性能和优化: 不同的DBMS可能对相同的函数有不同的实现方式, 导致性能有所不同.
     一些DBMS可能针对特定函数进行了优化, 以提供更好的性能.
  
由于这些差异的存在, 开发人员在使用SQL函数时需要特别注意. 
为了提高代码的可移植性, 建议遵循以下几点最佳实践:
* 1. 尽量使用通用的函数和语法: 尽量使用那些被多个DBMS支持的通用函数和语法, 以减少差异.
* 2. 编写DBMS特定的代码: 如果需要使用某个DBMS特定的函数或功能, 可以在代码中明确标注, 并确保该代码仅在目标DBMS上运行.
* 3. 进行充分的测试: 在不同的DBMS上对代码进行充分的测试, 确保函数的行为和性能符合预期.

总之, 了解并应对不同DBMS之间在函数支持方面的差异, 对于开发人员来说是非常重要的.
这有助于编写出更具可移植性和适应性的代码, 提高应用程序的稳定性和性能.

1.3 MySQL的内置函数及分类

MySQL内置函数按功能可划分为以下几大类(某些函数可能跨越多个类别, 需要根据实际需求和场景来决定):
* 1. 数值函数: 用于数学运算和数值处理, 比如求和, 求平均值, 求绝对值等.
* 2. 字符串函数: 用于处理字符串数据, 包括字符串的合并, 截取, 查找, 替换等.
* 3. 日期和时间函数: 用于处理日期和时间数据, 例如获取当前日期, 计算日期差, 格式化日期等.
* 4. 流程控制函数: 用于实现条件判断和流程控制, 如IF函数, CASE函数等.
* 5. 加密和解密函数: 提供数据的加密和解密功能, 保证数据的安全性. 如AES加密函数等.
* 6. 信息函数: 用于获取MySQL数据库或服务器的信息, 如数据库版本, 当前连接数等.
* 7. 聚合(或分组)函数: 这类函数主要用于对多行数据进行聚合计算, 常常与GROUP BY子句一起使用, 如COUNT, SUM, AVG, MAX, MIN等.

根据函数的操作和特性, 可以将其区分为单行函数和多行函数,
这种区分帮助我们更好地理解和应用这些函数:
* 1. 单行函数: 通常针对单个数据行进行操作, 它们接受一个或多个参数, 然后返回一个结果.
     这些函数通常执行简单的计算或转换任务, 处理单一的数据行.
     它们一般较简单, 方便在查询语句中使用, 并且能够快速返回结果.

* 2. 多行函数(或称为聚合函数)则针对多个数据行进行操作, 并对这些行进行聚合计算.
     它们通常用于对数据进行统计, 汇总或计算平均值等操作.
     多行函数会对一组数据行进行处理, 并返回一个综合的结果.
     它们通常用于处理包含多个数据行的数据集, 并在这些数据行上进行计算和分析.

需要注意的是, 这种区分是基于函数的操作和特性进行的逻辑划分, 而不是根据函数实际占用的行数来划分的.
无论是单行函数还是多行函数, 它们的具体实现可能会占用一行或多行代码, 这取决于函数的复杂性, 语法要求以及编程风格等因素.

2. 数值类型函数

2.1 基本函数

函数	功能	示例
`ABS(x)`	`返回数值x的绝对值.`	`SELECT ABS(-10); 返回10.`
`SIGN(X)`	`返回X的符号. 正数返回1, 负数返回-1, 0返回0.`	`SELECT SIGN(-10); 返回-1.`
`PI()`	`返回圆周率的值.`	`SELECT PI(); 返回3.14159… .`
`CEIL(x), CEILING(x)`	`返回大于或等于x的最小整数(向上取整).`	`SELECT CEIL(3.14); 返回4.`
`FLOOR(x)`	`返回小于或等于x的最大整数(向下取整).`	`SELECT FLOOR(3.14); 返回3.`
`LEAST(e1, e2,e3…)`	`返回参数列表中的最小值.`	`SELECT LEAST(10, 20, 30); 返回10.`
`GREATEST(e1,e2,e3…)`	`返回参数列表中的最大值.`	`SELECT GREATEST(10, 20, 30); 返回30.`
`MOD(x,y)`	`返回x除以y的余数.`	`SELECT MOD(10, 3); 返回1.`
`RAND()`	`返回0~1之间的随机浮点数(包含0但是不包含1).`	`SELECT RAND(); 返回类似于0.12345的随机数.`
`RAND(x)`	`使用x作为种子值, 返回一个0~1之间的随机数.相同的x会产生相同的随机数.`	`SELECT RAND(10); 对于相同的种子值, 总是返回相同的随机数, 这可以用于生成可重复的随机序列.`
`ROUND(x)`	`对x进行四舍五入, 返回最接近的整数.`	`SELECT ROUND(3.5); 返回4.`
`ROUND(x,y)`	`对x进行四舍五入, 并保留小数点后y位.`	`SELECT ROUND(3.567, 2); 返回3.57.`
`TRUNCATE(x,y)`	`截断数字x到y位小数.`	`SELECT TRUNCATE(3.567, 2); 返回3.56.`
`SQRT(x)`	`返回x的平方根. 如果x为负数, 则返回NULL.`	`SELECT SQRT(9); 返回3.`

-- 返回绝对值:
mysql> SELECT ABS(-123), ABS(32) FROM DUAL;
+-----------+---------+
| ABS(-123) | ABS(32) |
+-----------+---------+
|       123 |      32 |
+-----------+---------+
1 row in set (0.00 sec)

-- 返回x的符号, 正数返回1, 负数返回-1, 0返回0:
mysql> SELECT SIGN(43), SIGN(-23), SIGN(0) FROM DUAL;
+----------+-----------+---------+
| SIGN(43) | SIGN(-23) | SIGN(0) |
+----------+-----------+---------+
|        1 |        -1 |       0 |
+----------+-----------+---------+
1 row in set (0.00 sec)

-- 返回圆周率的值:
mysql>  SELECT PI() FROM DUAL;
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

-- 向上取整:
mysql> SELECT CEIL(32.32), CEILING(-43.23) FROM DUAL;
+-------------+-----------------+
| CEIL(32.32) | CEILING(-43.23) |
+-------------+-----------------+
|          33 |             -43 |  -- 负数的向上取整是舍弃小数部分
+-------------+-----------------+
1 row in set (0.00 sec)

-- 向下取整:
mysql> SELECT FLOOR(32.32), FLOOR(-43.23) FROM DUAL;
+--------------+---------------+
| FLOOR(32.32) | FLOOR(-43.23) |
+--------------+---------------+
|           32 |           -44 |  -- 负数向下取整是直接进1位
+--------------+---------------+
1 row in set (0.00 sec)

-- 求余数:
mysql> SELECT MOD(12,5) FROM DUAL;
+-----------+
| MOD(12,5) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

-- 返回一个0~1之间的随机数:
mysql>  SELECT RAND(), RAND() FROM DUAL;
+--------------------+--------------------+
| RAND()             | RAND()             |
+--------------------+--------------------+
| 0.6542968737467163 | 0.5435604877244313 |
+--------------------+--------------------+
1 row in set (0.00 sec)

-- 输入种子值, 相同的种子值会产生相同的随机数:
mysql> SELECT RAND(10), RAND(10), RAND(-1), RAND(-1) FROM DUAL;
+--------------------+--------------------+--------------------+--------------------+
| RAND(10)           | RAND(10)           | RAND(-1)           | RAND(-1)           |
+--------------------+--------------------+--------------------+--------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

-- 四舍五入:
mysql> SELECT ROUND(12.43), ROUND(12.53) FROM DUAL;
+--------------+--------------+
| ROUND(12.43) | ROUND(12.53) |
+--------------+--------------+
|           12 |           13 |
+--------------+--------------+
1 row in set (0.01 sec)

-- 保留n位小数, 位数不够不会在末尾填充0:
mysql> SELECT ROUND(12.43, 2), ROUND(12.53, 3) FROM DUAL;
+-----------------+-----------------+
| ROUND(12.43, 2) | ROUND(12.53, 3) |
+-----------------+-----------------+
|           12.43 |           12.53 |  
+-----------------+-----------------+
1 row in set (0.00 sec)

-- 当第二个参数(即要保留的小数位数)为负数时, 它表示将数字四舍五入到最接近的10的幂的倍数:
mysql> SELECT ROUND(12.343, -1)  FROM DUAL;
+-------------------+
| ROUND(12.343, -1) |  -- -1表示四舍五入到十位数
+-------------------+
|                10 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(122.343, -2)  FROM DUAL;
+--------------------+
| ROUND(122.343, -2) | -- -2表示四舍五入到百位数
+--------------------+
|                100 |
+--------------------+
1 row in set (0.00 sec)

-- 截取数字:
mysql> SELECT TRUNCATE(12.66,1) FROM DUAL;
+-------------------+
| TRUNCATE(12.66,1) |
+-------------------+
|              12.6 |
+-------------------+
1 row in set (0.00 sec)

-- 当第二个参数为负数时, 它表示截断到最接近的10的幂的倍数:
mysql> SELECT TRUNCATE(12.66,-1) FROM DUAL;
+--------------------+
| TRUNCATE(12.66,-1) |  -- -1表示: 截断到十位
+--------------------+
|                 10 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(122.343, -2)  FROM DUAL;
+-----------------------+
| TRUNCATE(122.343, -2) |  -- -2表示: 截断到百位
+-----------------------+
|                   100 |
+-----------------------+
1 row in set (0.00 sec)

2.2 角度与弧度互换函数

函数	函数
`RADIANS(x)`	`将角度转化为弧度. 其中, 参数x为角度值.`
`DEGREES(x)`	`将弧度转化为角度, 其中, 参数x为弧度值.`

-- 将角度转化为弧度:
mysql> SELECT RADIANS(30), RADIANS(60), RADIANS(90) FROM DUAL;
+--------------------+--------------------+--------------------+
| RADIANS(30)        | RADIANS(60)        | RADIANS(90)        |
+--------------------+--------------------+--------------------+
| 0.5235987755982988 | 1.0471975511965976 | 1.5707963267948966 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

-- 将弧度转化为角度:
mysql> SELECT DEGREES(RADIANS(30)), DEGREES(2*PI()) FROM DUAL;
+----------------------+-----------------+
| DEGREES(RADIANS(30)) | DEGREES(2*PI()) |
+----------------------+-----------------+
|   29.999999999999996 |             360 |
+----------------------+-----------------+
1 row in set (0.00 sec)

2.3 三角函数

函数	用法	描述
`SIN(x)`	`返回x的正弦值`.	`参数x为弧度值.`
`ASIN(x)`	`返回x的反正弦值`.	`获取正弦为x的值. 如果x的值不在-1到1之间, 则返回NULL.`
`COS(x)`	`返回x的余弦值`.	`参数x为弧度值.`
`ACOS(x)`	`返回x的反余弦值.`	`获取余弦为x的值. 如果x的值不在-1到1之间, 则返回NULL.`
`TAN(x)`	`返回x的正切值.`	`参数x为弧度值.`
`ATAN(x)`	`返回x的反正切值.`	`返回正切值为x的值.`
`ATAN2(m,n)`	`返回两个参数的反正切值.`	`返回m和n的反正切值.`
`COT(x)`	`返回x的余切值.`	`X为弧度值.`

SIN(), COS(), TAN(), COT()函数期望的输入值弧度, 一般通过RADIANS()行将将角度转化为弧度.

ATAN2(M,N)函数返回两个参数的反正切值.
与ATAN(X)函数相比, ATAN2(M,N)需要两个参数, 例如有两个点point(x1,y1)和point(x2,y2),
使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1)), 使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1).
由使用方式可以看出, 当x2-x1等于0时, ATAN(X)函数会报错, 而ATAN2(M,N)函数则仍然可以计算.

-- 返回一个弧度的正弦值:
-- 将角度30度转换为弧度是π/6,
-- sin(π/6) 的值是 1/2 (或 0.5):
mysql> SELECT SIN(RADIANS(30)) FROM DUAL;
+---------------------+
| SIN(RADIANS(30))    |
+---------------------+
| 0.49999999999999994 |
+---------------------+
1 row in set (0.00 sec)

-- 返回一个数值的反正弦值:
-- ASIN函数返回的是反正弦值, 这是一个在 -π/2 到 π/2 范围内的角度值, 但是这个角度值是以弧度为单位的.
-- ASIN(1) 返回 π/2 弧度.
-- DEGREES(π/2) 将 π/2 弧度转换为角度, 结果是 90 度.
mysql> SELECT DEGREES(ASIN(1)) FROM DUAL;
+------------------+
| DEGREES(ASIN(1)) | 
+------------------+
|               90 | 
+------------------+
1 row in set (0.00 sec)


-- 返回弧度值的正切值:
-- 将角度值45转换为弧度值(π/4弧度).
-- 计算算π/4弧度的正切值.
mysql> SELECT TAN(RADIANS(45)) FROM DUAL;
+--------------------+
| TAN(RADIANS(45))   |
+--------------------+
| 0.9999999999999999 |
+--------------------+
1 row in set (0.01 sec)

-- 返回一个角度的反正切值:
-- ATAN(1), 得到45度的反正切值, 用弧度表示. 这个值大约是0.7854(π/4 弧度).
-- 使用DEGREES函数将这个弧度值转换为角度值.
mysql> SELECT DEGREES(ATAN(1)) FROM DUAL;
+------------------+
| DEGREES(ATAN(1)) |
+------------------+
|               45 |
+------------------+
1 row in set (0.00 sec)


-- 返回从原点到点(x, y)的线的角度:
-- ATAN2(1, 1) 返回的是 π/4.
-- 将弧度值π/4转换为角度值45度.
mysql> SELECT DEGREES(ATAN2(1,1)) FROM DUAL;
+---------------------+
| DEGREES(ATAN2(1,1)) |
+---------------------+
|                  45 |
+---------------------+
1 row in set (0.00 sec)

-- 返回弧度余切值:
-- 函数将角度值30转换为弧度值, 30度转换为弧度是 π/6.
-- 计算π/6弧度的余切值.
mysql> SELECT COT(RADIANS(30)) FROM DUAL;
+--------------------+
| COT(RADIANS(30))   |
+--------------------+
| 1.7320508075688774 |
+--------------------+
1 row in set (0.01 sec)

2.4 指数与对数

函数	用法	描述
`POW(x,y), POWER(X,Y)`	`返回x的y次方.`	`接受两个参数x和y, 返回x的y次方.`
`EXP(X)`	`返回e的X次方.`	`接受一个参数X, 返回以常数e (2.718281828459045) 为底的X次方.`
`LN(X), LOG(X)`	`返回以e为底的X的对数.`	`接受一个参数X, 返回以e为底的X的对数.当X <= 0 时, 返回的结果为NULL.`
`LOG10(X)`	`返回以10为底的X的对数.`	`接受一个参数X, 返回以10为底的X的对数.当X <= 0 时, 返回的结果为NULL.`
`LOG2(X)`	`返回以2为底的X的对数.`	`接受一个参数X, 返回以2为底的X的对数.当X <= 0 时, 返回NULL.`

-- 返回x的y次方
mysql> SELECT POW(2,5), POWER(2,4) FROM DUAL;
+----------+------------+
| POW(2,5) | POWER(2,4) |
+----------+------------+
|       32 |         16 |
+----------+------------+
1 row in set (0.00 sec)

-- 返回常数e为底的x次方:
mysql> SELECT EXP(2) FROM DUAL;
+------------------+
| EXP(2)           |
+------------------+
| 7.38905609893065 |
+------------------+
1 row in set (0.00 sec)

-- 返回以e为底的X的对数:
mysql> SELECT LN(10) FROM DUAL;
+-------------------+
| LN(10)            |
+-------------------+
| 2.302585092994046 |
+-------------------+
1 row in set (0.01 sec)

-- 返回以10为底的X的对数:
mysql> SELECT LOG10(10) FROM DUAL;
+-----------+
| LOG10(10) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

-- 返回以2为底的X的对数:
mysql> SELECT LOG2(4) FROM DUAL;
+---------+
| LOG2(4) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

2.5 进制转换

函数	用法	示例输入	示例输出
`BIN(x)`	`返回x的二进制编码`	`BIN(10)`	`'1010'`
`OCT(x)`	`返回x的八进制编码`	`OCT(64)`	`'100'`
`HEX(x)`	`返回x的十六进制编码`	`HEX(255)`	`'FF'`
`CONV(x, f1, f2)`	`返回f1进制数变成f2进制数`	`CONV('1010', 2, 16)`	`'A'`

-- 十进制转二进制:
mysql> SELECT BIN(10) FROM DUAL;
+---------+
| BIN(10) |
+---------+
| 1010    |
+---------+
1 row in set (0.00 sec)
-- 二进制转十进制:
mysql> SELECT CONV('1010', 2, 10) FROM DUAL;
+---------------------+
| CONV('1010', 2, 10) |
+---------------------+
| 10                  |
+---------------------+
1 row in set (0.00 sec)

-- 十进制转八进制:
mysql> SELECT OCT(10) FROM DUAL;
+---------+
| OCT(10) |
+---------+
| 12      |
+---------+
1 row in set (0.00 sec)

-- 八进制转十进制:
mysql> SELECT CONV('12', 8, 10) FROM DUAL;
+-------------------+
| CONV('12', 8, 10) |
+-------------------+
| 10                |
+-------------------+
1 row in set (0.00 sec)

-- 十进制转十六进制:
mysql> SELECT HEX(10) FROM DUAL;
+---------+
| HEX(10) |
+---------+
| A       |
+---------+
1 row in set (0.00 sec)

-- 十六进制转十进制:
mysql> SELECT CONV('A', 16, 10) FROM DUAL;
+-------------------+
| CONV('A', 16, 10) |
+-------------------+
| 10                |
+-------------------+
1 row in set (0.00 sec)

-- 二进制转八进制:
mysql> SELECT CONV('1010', 2, 8) FROM DUAL;
+--------------------+
| CONV('1010', 2, 8) |
+--------------------+
| 12                 |
+--------------------+
1 row in set (0.00 sec)

3. 字符串函数

* MySQL中, 字符串的索引是从1开始的.

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

默认情况下, 大多数字符串函数不区分大小写, 比较函数和排序函数通常区分大小写.

-- 返回字符串S中的第一个字符的ASCII码值: 
mysql> SELECT ASCII('hell0') FROM DUAL;
+----------------+
| ASCII('hell0') |
+----------------+
|            104 |
+----------------+
1 row in set (0.00 sec)

-- 返回字符串s的字符数(与CHARACTER_LENGTH(s)相同):
mysql> SELECT CHAR_LENGTH('你好') FROM DUAL;
+---------------------+
| CHAR_LENGTH('你好') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT CHARACTER_LENGTH('你好') FROM DUAL;
+--------------------------+
| CHARACTER_LENGTH('你好') |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set (0.00 sec)

-- 返回字符串s的字节数(和字符集有关):
mysql> SELECT  LENGTH('你好') FROM DUAL;
+----------------+
| LENGTH('你好') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT  LENGTH('AB') FROM DUAL;
+--------------+
| LENGTH('AB') |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

-- 字符连接:
mysql> SELECT CONCAT('H', 'E', 'L', 'L', 'O') FROM DUAL;
+---------------------------------+
| CONCAT('H', 'E', 'L', 'L', 'O') |
+---------------------------------+
| HELLO                           |
+---------------------------------+
1 row in set (0.03 sec)

-- 使用字符连接字符串:
mysql> SELECT CONCAT_WS('-', 'H', 'E', 'L', 'L', 'O') FROM DUAL;
+-----------------------------------------+
| CONCAT_WS('-', 'H', 'E', 'L', 'L', 'O') |
+-----------------------------------------+
| H-E-L-L-O                               |
+-----------------------------------------+
1 row in set (0.00 sec)

-- 字符串替换(将第一个字符与第二个字符串删除替换成新字符串):
mysql> SELECT INSERT('hello', 1, 2, 'yyyy') FROM DUAL;
+-------------------------------+
| INSERT('hello', 1, 2, 'yyyy') |
+-------------------------------+
| yyyyllo                       |
+-------------------------------+
1 row in set (0.00 sec)

-- 字符替换(将所有指定字符替换):
mysql> SELECT REPLACE('hello', 'l', 'o') FROM DUAL;
+----------------------------+
| REPLACE('hello', 'l', 'o') |
+----------------------------+
| heooo                      |
+----------------------------+
1 row in set (0.00 sec)

-- 将所有字母转成大写字母:
mysql> SELECT UPPER('hello') FROM DUAL;
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UCASE('hello') FROM DUAL;
+----------------+
| UCASE('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

-- 将所有字母转成小写字母:
mysql> SELECT LOWER('HELLO') FROM DUAL;
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LCASE('HELLO') FROM DUAL;
+----------------+
| LCASE('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

-- 返回字符串最左边的n个字符:
mysql> SELECT LEFT('hello', 2) FROM DUAL;
+------------------+
| LEFT('hello', 2) |
+------------------+
| he               |
+------------------+
1 row in set (0.00 sec)

-- 返回字符串最右边的n个字符:
mysql> SELECT RIGHT('hello', 2) FROM DUAL;
+-------------------+
| RIGHT('hello', 2) |
+-------------------+
| lo                |
+-------------------+
1 row in set (0.00 sec)

-- 用字符串pad对str最左边进行填充, 直到str的长度为len个字符:
mysql> SELECT LPAD('hello', 10, 'world!') FROM DUAL;
+-----------------------------+
| LPAD('hello', 10, 'world!') |
+-----------------------------+
| worldhello                  |
+-----------------------------+
1 row in set (0.00 sec)

-- 用字符串pad对str最右边进行填充, 直到str的长度为len个字符:
mysql> SELECT RPAD('hello', 10, 'world!') FROM DUAL;
+-----------------------------+
| RPAD('hello', 10, 'world!') |
+-----------------------------+
| helloworld                  |
+-----------------------------+
1 row in set (0.00 sec)

-- 移除字符串左侧空格:
mysql> SELECT LTRIM('  HELLO  ') FROM DUAL;
+--------------------+
| LTRIM('  HELLO  ') |
+--------------------+
| HELLO              |
+--------------------+
1 row in set (0.00 sec)

-- 移除字符串右侧空格:
mysql> SELECT RTRIM('  HELLO  ') FROM DUAL;
+--------------------+
| RTRIM('  HELLO  ') |
+--------------------+
|   HELLO            |  -- 看不出效果
+--------------------+
1 row in set (0.00 sec)

-- 移除字符串两侧空格:
mysql> SELECT TRIM('  HELLO  ') FROM DUAL;
+-------------------+
| TRIM('  HELLO  ') |
+-------------------+
| HELLO             |
+-------------------+
1 row in set (0.00 sec)

-- 移除两侧指定字符:
mysql> SELECT TRIM('-' FROM '--HELLO--') FROM DUAL;
+----------------------------+
| TRIM('-' FROM '--HELLO--') |
+----------------------------+
| HELLO                      |
+----------------------------+
1 row in set (0.00 sec)

-- 移除左侧指定字符:
mysql> SELECT TRIM(LEADING '-' FROM '--HELLO--') FROM DUAL;
+------------------------------------+
| TRIM(LEADING '-' FROM '--HELLO--') |
+------------------------------------+
| HELLO--                            |
+------------------------------------+
1 row in set (0.00 sec)

-- 移除右侧指定字符:
mysql> SELECT TRIM(TRAILING '-' FROM '--HELLO--') FROM DUAL;
+-------------------------------------+
| TRIM(TRAILING '-' FROM '--HELLO--') |
+-------------------------------------+
| --HELLO                             |
+-------------------------------------+
1 row in set (0.00 sec)

-- 字符串重复n次:
mysql> SELECT REPEAT('HELLO-', 3) FROM DUAL;
+---------------------+
| REPEAT('HELLO-', 3) |
+---------------------+
| HELLO-HELLO-HELLO-  |
+---------------------+
1 row in set (0.00 sec)

-- 返回n个空格:
mysql> SELECT SPACE(3), LENGTH(SPACE(3)) FROM DUAL;
+----------+------------------+
| SPACE(3) | LENGTH(SPACE(3)) |
+----------+------------------+
|          |                3 |
+----------+------------------+
1 row in set (0.00 sec)

-- 依次比较两个字符串的ASCII码值的大小(s1比s2大返回1, 相等返回0, s1比s2小返回-1):
mysql>  SELECT STRCMP('abd', 'abc'), STRCMP('abc', 'abc'),  STRCMP('abc', 'abd') FROM DUAL;
+----------------------+----------------------+----------------------+
| STRCMP('abd', 'abc') | STRCMP('abc', 'abc') | STRCMP('abc', 'abd') |
+----------------------+----------------------+----------------------+
|                    1 |                    0 |                   -1 |
+----------------------+----------------------+----------------------+
1 row in set (0.00 sec)

-- 返回从字符串s的index位置开始的len个字符:
mysql> SELECT SUBSTR('hello', 1, 5) FROM DUAL;
+-----------------------+
| SUBSTR('hello', 1, 5) |
+-----------------------+
| hello                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('hello', 1, 5) FROM DUAL;
+--------------------------+
| SUBSTRING('hello', 1, 5) |
+--------------------------+
| hello                    |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MID('hello', 1, 5) FROM DUAL;
+--------------------+
| MID('hello', 1, 5) |
+--------------------+
| hello              |
+--------------------+
1 row in set (0.00 sec)

-- 返回子字符串在字符串中首次出现的位置(未找到则返回0):
mysql> SELECT LOCATE('ll', 'hello') FROM DUAL;
+-----------------------+
| LOCATE('ll', 'hello') |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT POSITION('ll' IN 'hello') FROM DUAL;
+---------------------------+
| POSITION('ll' IN 'hello') |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT INSTR('hello', 'll') FROM DUAL;
+----------------------+
| INSTR('hello', 'll') |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.01 sec)

-- 返回指定位置的字符串:
mysql> SELECT ELT(1,'m1','m2','m3','m4') FROM DUAL;
+----------------------------+
| ELT(1,'m1','m2','m3','m4') |
+----------------------------+
| m1                         |
+----------------------------+
1 row in set (0.00 sec)

-- 查询字符串'mm'在字符序列中的位置(不区分大小写, 找不到返回0):
mysql> SELECT FIELD('mm', 'hello', 'msm', 'amma', 'MM') FROM DUAL;
+-------------------------------------------+
| FIELD('mm', 'hello', 'msm', 'amma', 'MM') |
+-------------------------------------------+
|                                         4 |
+-------------------------------------------+
1 row in set (0.00 sec)

-- 查询字符串'mm'在字符集合的位置(使用逗号分割字符不能出现多余的空格):
mysql> SELECT  FIND_IN_SET('mm', 'hello,mm,amma') FROM DUAL;
+------------------------------------+
| FIND_IN_SET('mm', 'hello,mm,amma') |
+------------------------------------+
|                                  2 |
+------------------------------------+
1 row in set (0.00 sec)

-- 字符串反转:
mysql> SELECT REVERSE('HELLO') FROM DUAL;
+------------------+
| REVERSE('HELLO') |
+------------------+
| OLLEH            |
+------------------+
1 row in set (0.00 sec)

-- 比较两个值是否相等(如果value1与value2相等, 则返回NULL, 否则返回value1):
mysql> SELECT NULLIF('mysql', 'mysql'), NULLIF('mysql', '') FROM DUAL;
+--------------------------+---------------------+
| NULLIF('mysql', 'mysql') | NULLIF('mysql', '') |
+--------------------------+---------------------+
| NULL                     | mysql               |
+--------------------------+---------------------+
1 row in set (0.00 sec)

4. 日期和时间函数

4.1 获取日期, 时间

函数	用法
`CURDATE(), CURRENT_DATE()`	`返回当前日期, 只包含年, 月, 日.`
`CURTIME() , CURRENT_TIME()`	`返回当前时间, 只包含时, 分, 秒.`
`NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()`	`返回当前系统日期和时间, 包含年, 月, 日, 时, 分, 秒.`
`UTC_DATE()`	`返回UTC(世界标准时间)日期, 只包含年, 月, 日.`
`UTC_TIME()`	`返回UTC(世界标准时间)时间, 只包含时, 分, 秒.`

上类函数返回的是一个日期时间类型的值, 在这个值后面加上"+0", 将日期时间值转换为纯数字表示方式.

-- 返回当前日期:
mysql> SELECT CURDATE(), CURRENT_DATE() FROM DUAL;
+------------+----------------+
| CURDATE()  | CURRENT_DATE() |
+------------+----------------+
| 2024-03-05 | 2024-03-05     |
+------------+----------------+
1 row in set (0.00 sec)

-- 返回当前时间:
mysql> SELECT CURTIME(), CURRENT_TIME() FROM DUAL;
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 15:23:45  | 15:23:45       |
+-----------+----------------+
1 row in set (0.00 sec)

-- 返回当前系统时间:
mysql> SELECT NOW() FROM DUAL;
+---------------------+
| NOW()               |
+---------------------+
| 2024-03-05 15:24:41 |
+---------------------+
1 row in set (0.00 sec)

-- 返回UTC日期, UTC时间:
mysql> SELECT UTC_DATE(), UTC_TIME() FROM DUAL;
+------------+------------+
| UTC_DATE() | UTC_TIME() |
+------------+------------+
| 2024-03-05 | 07:26:06   |
+------------+------------+
1 row in set (0.00 sec)

-- 在函数后面加上'+0'将时间格式转换为纯数字:
mysql> SELECT NOW(), NOW()+0 FROM DUAL;
+---------------------+----------------+
| NOW()               | NOW()+0        |
+---------------------+----------------+
| 2024-03-05 16:51:50 | 20240305165150 |
+---------------------+----------------+
1 row in set (0.00 sec)

4.2 日期与时间戳的转换

函数	用法	示例
`UNIX_TIMESTAMP()`	`以UNIX时间戳的形式返回当前时间.`	`SELECT UNIX_TIMESTAMP(); -> 1634348884`
`UNIX_TIMESTAMP(date)`	`将时间date以UNIX时间戳的形式返回.`	`SELECT UNIX_TIMESTAMP('2023-09-25 12:00:00'); -> 对应的UNIX时间戳.`
`FROM_UNIXTIME(timestamp)`	`将UNIX时间戳的时间转换为普通格式的时间.`	`SELECT FROM_UNIXTIME(1634348884); -> '2021-10-16 08:28:04'`

-- 返回当前的时间戳:
mysql>  SELECT NOW(), UNIX_TIMESTAMP() AS `unit_time` FROM DUAL;
+---------------------+------------+
| NOW()               | unit_time  |
+---------------------+------------+
| 2024-03-05 16:57:36 | 1709629056 |
+---------------------+------------+
1 row in set (0.00 sec)
              
-- 将时间格式转换为时间戳:
mysql> SELECT NOW(), UNIX_TIMESTAMP(NOW()) AS `unit_time` FROM DUAL;
+---------------------+------------+
| NOW()               | unit_time  |
+---------------------+------------+
| 2024-03-05 16:57:47 | 1709629067 |
+---------------------+------------+
1 row in set (0.00 sec)


-- 将指定时间转为时间戳:
mysql> SELECT UNIX_TIMESTAMP('2023-09-25 12:00:00') AS `unit_time` FROM DUAL;
+------------+
| unit_time  |
+------------+
| 1695614400 |
+------------+
1 row in set (0.00 sec)

-- 将时间戳转为时间格式:
mysql> SELECT FROM_UNIXTIME(1695614400) AS `date_time` FROM DUAL;
+---------------------+
| date_time           |
+---------------------+
| 2023-09-25 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

4.3 获取月份, 星期, 星期数, 天数等函数

函数	用法	示例
`YEAR(date)`	`返回日期中的年份.`	`SELECT YEAR('2023-09-25'); 返回2023.`
`MONTH(date)`	`返回日期中的月份.`	`SELECT MONTH('2023-09-25'); 返回9.`
`DAY(date)`	`返回日期中的天数.`	`SELECT DAY('2023-09-25'); 返回25.`
`HOUR(time)`	`返回时间中的小时数.`	`SELECT HOUR('14:30:00'); 返回14.`
`MINUTE(time)`	`返回时间中的分钟数.`	`SELECT MINUTE('14:30:00'); 返回30.`
`SECOND(time)`	`返回时间中的秒数.`	`SELECT SECOND('14:30:45'); 返回45.`
`MONTHNAME(date)`	`返回日期的月份名称.`	`SELECT MONTHNAME('2023-09-25'); 返回"September".`
`DAYNAME(date)`	`返回日期是星期几的单词名称.`	`SELECT DAYNAME('2023-09-25'); 返回"Monday".`
`WEEKDAY(date)`	`返回日期是星期几, 周一是0, 周日是6.`	`SELECT WEEKDAY('2023-09-25'); 返回对应的数字.`
`QUARTER(date)`	`返回日期所在的季度.`	`SELECT QUARTER('2023-09-25'); 返回3.`
`WEEK(date), WEEKOFYEAR(date)`	`返回日期是一年中的第几周.`	`SELECT WEEK('2023-09-25'); 返回对应的周数.`
`DAYOFYEAR(date)`	`返回日期是一年中的第几天.`	`SELECT DAYOFYEAR('2023-09-25'); 返回268.`
`DAYOFMONTH(date)`	`返回日期是所在月份的第几天.`	`SELECT DAYOFMONTH('2023-09-25'); 返回25.`
`DAYOFWEEK(date)`	`返回日期是星期几, 周日是1, 周六是7.`	`SELECT DAYOFWEEK('2023-09-25'); 返回对应的数字.`

-- 获取当前时间的年; 月; 日:
mysql> SELECT YEAR(CURDATE()) AS `year`, MONTH(CURDATE()) AS `month`, DAY(CURDATE()) AS `day` FROM DUAL;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2024 |     3 |    5 |
+------+-------+------+
1 row in set (0.00 sec)

-- 获取当前时间的时; 分; 秒:
SELECT HOUR(CURTIME()) AS `hour`, MINUTE(NOW()) AS `minute`, SECOND(SYSDATE()) AS `second` FROM DUAL;
+------+--------+--------+
| hour | minute | second |
+------+--------+--------+
|   17 |      2 |     18 |
+------+--------+--------+
1 row in set (0.00 sec)

-- 月份名称; 星期几; 周几:
mysql> SELECT
MONTHNAME('2021-10-26') AS `month_name`, 
DAYNAME('2021-10-26') AS `day_name`, 
WEEKDAY('2021-10-26') AS `week_name`
FROM DUAL;
+------------+----------+-----------+
| month_name | day_name | week_name |
+------------+----------+-----------+
| October    | Tuesday  |         1 |
+------------+----------+-----------+
1 row in set (0.00 sec)

-- 第几季度; 今年的第几周; 今年的第几天; 这个月的第几天; 星期几的对应数字:
mysql> SELECT 
QUARTER(NOW()) AS `quarter`, 
WEEK(NOW()) AS `week_of_year`,
DAYOFYEAR(NOW()) AS `day_of_year`,
DAYOFMONTH(NOW()) AS `day_of_month`, 
DAYOFWEEK(NOW()) AS `day_of_week`
FROM DUAL;
| quarter | week_of_year | day_of_year | day_of_month | day_of_week |
+---------+--------------+-------------+--------------+-------------+
|       1 |            9 |          65 |            5 |           3 |
+---------+--------------+-------------+--------------+-------------+
1 row in set (0.00 sec)

4.4 日期的操作函数

函数	用法
`EXTRACT(type FROM date)`	`返回指定日期中特定的部分, type指定返回的值.`

type 取值	返回值
`MICROSECOND`	`毫秒数`
`SECOND`	`秒数`
`MINUTE`	`分钟数`
`HOUR`	`小时数`
`DAY`	`天数`
`WEEK`	`日期在一年中的第几个星期`
`MONTH`	`日期在一年中的第几个月`
`QUARTER`	`日期在一年中的第几个季度`
`YEAR`	`日期的年份`
`SECOND_MICROSECOND`	`秒和毫秒值`
`MINUTE_MICROSECOND`	`分钟和毫秒值`
`MINUTE_SECOND`	`分钟和秒值`
`HOUR_MICROSECOND`	`小时和毫秒值`
`HOUR_SECOND`	`小时和秒值`
`HOUR_MINUTE`	`小时和分钟值`
`DAY_MICROSECOND`	`天和毫秒值`
`DAY_SECOND`	`天和秒值`
`DAY_MINUTE`	`天和分钟值`
`DAY_HOUR`	`天和小时`
`YEAR_MONTH`	`年和月`

-- 分钟数; 一年的第几周:
mysql> SELECT
EXTRACT(MINUTE FROM NOW()) AS `minute`, 
EXTRACT( WEEK FROM NOW()) AS `week_of_year`
FROM DUAL;
+--------+--------------+
| minute | week_of_year |
+--------+--------------+
|     24 |            9 |
+--------+--------------+
1 row in set (0.00 sec)

-- 第几个季度; 分秒:
mysql> SELECT 
EXTRACT(QUARTER FROM NOW()) AS `quarter`, 
EXTRACT(MINUTE_SECOND FROM NOW()) AS `minute_second`
FROM DUAL;
+---------+---------------+
| quarter | minute_second |
+---------+---------------+
|       1 |          2640 |
+---------+---------------+
1 row in set (0.00 sec)

4.5 时间和秒钟转换的函数

函数	用法	示例
`TIME_TO_SEC(time)`	`将时间time转化为秒, 并返回结果值. 转化公式为: 小时3600 + 分钟60 + 秒.`	`SELECT TIME_TO_SEC('02:30:45'); 返回结果为9045, 即2小时30分钟45秒转换为9045秒.`
`SEC_TO_TIME(seconds)`	`将秒数 seconds 转化为包含小时, 分钟和秒的时间格式, 并返回结果.`	`SELECT SEC_TO_TIME(9045); 返回结果为 '02:30:45', 即9045秒转换为2小时30分钟45秒的时间格式.`

-- 时间转为秒:
mysql> SELECT 
CURTIME() AS `time`, 
TIME_TO_SEC(CURTIME()) AS `second`
FROM DUAL;
+----------+--------+
| time     | second |
+----------+--------+
| 17:27:52 |  62872 |
+----------+--------+
1 row in set (0.00 sec)


-- 秒转换为时分秒:
mysql> SELECT SEC_TO_TIME(56158) AS `time` FROM DUAL;
+----------+
| time     |
+----------+
| 15:35:58 |
+----------+
1 row in set (0.00 sec)

4.6 计算日期和时间的函数

4.6.1 方式1

函数	用法	示例
`DATE_ADD(datetime, INTERVAL expr type)`	`在给定的日期时间datetime 上添加指定的时间间隔 INTERVAL expr type, 并返回结果日期时间.`	`SELECT DATE_ADD('2023-09-25 10:30:00', INTERVAL 5 DAY); 返回'2023-10-01 10:30:00'`
`ADDDATE(date, INTERVAL expr type)`	`与 DATE_ADD 函数相同, 可以在日期date上添加指定的时间间隔.`	`SELECT ADDDATE('2023-09-25', INTERVAL 1 MONTH); 返回'2023-10-25'`
`DATE_SUB(date, INTERVAL expr type)`	`在给定的日期时间date上减去指定的时间间隔 INTERVAL expr type, 并返回结果日期时间.`	`SELECT DATE_SUB('2023-09-25', INTERVAL 3 YEAR); 返回'2020-09-25'`
`SUBDATE(date, INTERVAL expr type)`	`与DATE_SUB函数相同, 可以在日期date上减去指定的时间间隔.`	`SELECT SUBDATE('2023-09-25', INTERVAL 1 WEEK); 返回'2023-09-18'`

间隔类型	含义
`HOUR`	`小时`
`MINUTE`	`分钟`
`SECOND`	`秒`
`YEAR`	`年`
`MONTH`	`月`
`DAY`	`日`
`YEAR_MONTH`	`年和月`
`DAY_HOUR`	`日和小时`
`DAY_MINUTE`	`日和分钟`
`DAY_SECOND`	`日和秒`
`HOUR_MINUTE`	`小时和分钟`
`HOUR_SECOND`	`小时和秒`
`MINUTE_SECOND`	`分钟和秒`

-- 加1天:
mysql> SELECT
NOW(),
DATE_ADD(NOW(), INTERVAL 1 DAY) AS `date_add`
FROM DUAL;
+---------------------+---------------------+
| NOW()               | date_add            |
+---------------------+---------------------+
| 2024-03-05 17:29:13 | 2024-03-06 17:29:13 |
+---------------------+---------------------+
1 row in set (0.00 sec)

-- 加1秒:
mysql> SELECT
DATE_ADD('2021-10-21 23:32:12', INTERVAL 1 SECOND) AS `date_add` 
FROM DUAL;
+---------------------+
| date_add            |
+---------------------+
| 2021-10-21 23:32:13 |
+---------------------+
1 row in set (0.00 sec)

-- 加1年1分, 可以使用空格或下划线间隔, 使用单引号包裹:
mysql> SELECT 
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS `date_add`
FROM DUAL;
+---------------------+
| date_add            |
+---------------------+
| 2021-10-21 23:33:13 |
+---------------------+
1 row in set (0.00 sec)

-- 加1年一月:
mysql> SELECT 
DATE_ADD(NOW(), INTERVAL '1 1' YEAR_MONTH) AS `date_add` 
FROM DUAL;
+---------------------+
| date_add            |
+---------------------+
| 2025-04-05 17:35:43 |
+---------------------+
1 row in set (0.00 sec)

-- 使用负数减去n年:
mysql> SELECT 
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS `date_add` 
FROM DUAL;
+---------------------+
| date_add            |
+---------------------+
| 2023-03-05 17:36:45 |
+---------------------+
1 row in set (0.00 sec)

-- 减31天:
mysql> SELECT 
DATE_SUB('2021-01-21', INTERVAL 31 DAY) AS `date_sub`
FROM DUAL;
+------------+
| date_sub   |
+------------+
| 2020-12-21 |
+------------+
1 row in set (0.00 sec)

-- 减1天1小时:
mysql> SELECT 
DATE_SUB('2021-01-21 02:01:01', INTERVAL '1 1' DAY_HOUR) AS `date_sub`
FROM DUAL;
+---------------------+
| date_sub            |
+---------------------+
| 2021-01-20 01:01:01 |
+---------------------+
1 row in set (0.00 sec)

4.6.2 方式2

函数名	用法	描述
`ADDTIME(time1, time2)`	`返回time1加上time2的时间.`	`当time2为一个数字时, 代表的是秒, 可以为负数.`
`SUBTIME(time1, time2)`	`返回time1减去time2后的时间.`	`当time2为一个数字时, 代表的是秒, 可以为负数.`
`DATEDIFF(date1, date2)`	`返回date1 - date2的日期间隔天数.`	`返回两个日期之间的天数差.`
`TIMEDIFF(time1, time2)`	`返回time1 - time2的时间间隔.`	`返回两个时间之间的时间差.`
`FROM_DAYS(N)`	`返回从0000年1月1日起, N天以后的日期.`	`根据给定的天数返回相应的日期.`
`TO_DAYS(date)`	`返回日期date距离0000年1月1日的天数.`	`返回给定日期与0000年1月1日之间的天数.`
`LAST_DAY(date)`	`返回date所在月份的最后一天的日期.`	`返回给定日期所在月份的最后一天.`
`MAKEDATE(year, n)`	`针对给定年份与所在年份中的天数返回一个日期.`	`根据给定的年份和天数返回一个日期.`
`MAKETIME(hour, minute, second)`	`将给定的小时, 分钟和秒组合成时间并返回.`	`将给定的小时, 分钟和秒组合成一个时间.`
`PERIOD_ADD(time, n)`	`返回time加上n后的时间.`	`在给定的时间上添加指定的时间间隔.`

-- 加, 减20秒:
mysql> SELECT
NOW(), 
ADDTIME(NOW(), 20) AS `add_time`,
ADDTIME(NOW(), -20) AS `sub_time`
FROM DUAL;
+---------------------+---------------------+---------------------+
| NOW()               | add_time            | sub_time            |
+---------------------+---------------------+---------------------+
| 2024-03-05 17:39:18 | 2024-03-05 17:39:38 | 2024-03-05 17:38:58 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)


mysql> SELECT
NOW(),
SUBTIME(NOW(), 20) AS `sub_time`, 
SUBTIME(NOW(), -20) AS `add_time`
FROM DUAL;
+---------------------+---------------------+---------------------+
| NOW()               | sub_time            | add_time            |
+---------------------+---------------------+---------------------+
| 2024-03-05 17:40:04 | 2024-03-05 17:39:44 | 2024-03-05 17:40:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

-- 现在的时间加上1小时1分钟1秒:
mysql> SELECT 
NOW(), 
ADDTIME(NOW(), '1:1:1') AS `add_time`
FROM DUAL;
+---------------------+---------------------+
| NOW()               | add_time            |
+---------------------+---------------------+
| 2024-03-05 17:42:38 | 2024-03-05 18:43:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

-- 现在的时间减去1小时1分钟1秒:
mysql> SELECT
NOW(),
SUBTIME(NOW(), '1:1:1') AS `sub_time`
FROM DUAL;
+---------------------+---------------------+
| NOW()               | sub_time            |
+---------------------+---------------------+
| 2024-03-05 17:43:23 | 2024-03-05 16:42:22 |
+---------------------+---------------------+
1 row in set (0.00 sec)

-- 负数在字符串里面不会负负得正, 而是保持不变:
mysql> SELECT NOW(), SUBTIME(NOW(), '-1:-1:-1') FROM DUAL;
+---------------------+----------------------------+
| NOW()               | SUBTIME(NOW(), '-1:-1:-1') |
+---------------------+----------------------------+
| 2024-03-05 16:25:55 | 2024-03-05 16:25:56        |
+---------------------+----------------------------+
1 row in set, 1 warning (0.00 sec) -- 一个警告

-- 数字格式时间增加10秒:
mysql> SELECT 
PERIOD_ADD(20200101010101, 10) AS `period_add` 
FROM DUAL;
+----------------+
| period_add     |
+----------------+
| 20200101010111 |
+----------------+
1 row in set (0.00 sec)

-- 数字格式时间增加100秒时间:
mysql> SELECT 
PERIOD_ADD(20200101010101, 100) AS `period_add` 
FROM DUAL;
+----------------+
| period_add     |
+----------------+
| 20200101010905 |
+----------------+
1 row in set (0.00 sec)

-- 计算天数差:
mysql> SELECT 
NOW(), 
DATEDIFF(NOW(), '2021-10-01') AS `date_diff`
FROM DUAL;
+---------------------+-----------+
| NOW()               | date_diff |
+---------------------+-----------+
| 2024-03-05 17:49:26 |       886 |
+---------------------+-----------+
1 row in set (0.00 sec)

-- 计算时间差, 天:时:分 :
mysql> SELECT 
TIMEDIFF(NOW(), '2021-10-25 22:10:10') AS `date_time_diff`
FROM DUAL;
+----------------+
| date_time_diff |
+----------------+
| 838:59:59      |
+----------------+
1 row in set, 1 warning (0.00 sec)

-- 0000年加366的日期; 0000年到0000-12-25的天数:
mysql> SELECT
FROM_DAYS(366) AS `from_days`,
TO_DAYS('0000-12-25') AS `to_days`
FROM DUAL;
+------------+---------+
| from_days  | to_days |
+------------+---------+
| 0001-01-01 |     359 |
+------------+---------+
1 row in set (0.00 sec)

-- 当前时间月份的最后一天:
mysql> SELECT
LAST_DAY(NOW()) AS `last_day`
FROM DUAL;
+------------+
| last_day   |
+------------+
| 2024-03-31 |
+------------+
1 row in set (0.00 sec)

-- 制作时间:
mysql> SELECT 
MAKETIME(10, 21, 23) AS `make_time`
FROM DUAL;
+-----------+
| make_time |
+-----------+
| 10:21:23  |
+-----------+
1 row in set (0.00 sec)

-- 制作日期, 提供的参数为年, 天:
mysql> SELECT
MAKEDATE(2020, 1) AS `make_date`
FROM DUAL;
+------------+
| make_date  |
+------------+
| 2020-01-01 |
+------------+
1 row in set (0.00 sec)

-- 制作日期,年份 + 36天:
mysql> SELECT
YEAR(NOW()) AS `year`, 
MAKEDATE(YEAR(NOW()), 36) AS `make_date`
FROM DUAL;
+------+------------+
| year | make_date  |
+------+------------+
| 2024 | 2024-02-05 |
+------+------------+
1 row in set (0.00 sec)

-- 一年的第32天转为日期:
mysql> SELECT 
MAKEDATE(2020, 32) AS `make_date`
FROM DUAL;
+------------+
| make_date  |
+------------+
| 2020-02-01 |
+------------+
1 row in set (0.00 sec)

-- 制作时间:
mysql> SELECT 
MAKETIME(1, 1, 1) AS `make_time`
FROM DUAL;
+-----------+
| make_time |
+-----------+
| 01:01:01  |
+-----------+
1 row in set (0.00 sec)

-- 0000年+366天的时间:
mysql> SELECT 
FROM_DAYS(366) AS `from_days`
FROM DUAL;
+------------+
| from_days  |
+------------+
| 0001-01-01 |
+------------+
1 row in set (0.00 sec)

-- 与0000年1月1日到现在的天数:
mysql> SELECT 
NOW(), 
TO_DAYS(NOW()) AS `to_days`
FROM DUAL;
+---------------------+---------+
| NOW()               | to_days |
+---------------------+---------+
| 2024-03-05 18:12:55 |  739315 |
+---------------------+---------+
1 row in set (0.00 sec)

-- 时间的使用场景举例, 查询7天内的新增用户数有多少?
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW()) - TO_DAYS(regist_time) <= 7;

4.7 日期的格式化与解析

函数	用法
`DATE_FORMAT(date, fmt)`	`按照字符串fmt格式化日期date值. 返回格式化后的日期字符串.`
`TIME_FORMAT(time, fmt)`	`按照字符串fmt格式化时间time值. 返回格式化后的时间字符串.`
`GET_FORMAT(date_type, format_type)`	`返回日期字符串的显示格式. date_type指定日期类型('ISO', 'EUR'或其他), format_type指定格式类型('DATE', 'TIME'或'DATETIME'). 返回指定日期类型和格式类型的日期字符串显示格式.`
`STR_TO_DATE(str, fmt)`	`按照字符串fmt对str进行解析, 解析为一个日期. 返回解析后的日期值. 如果解析失败, 则返回NULL.`

非GET_FORMAT函数中fmt参数常用的格式符:

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

GET_FORMAT函数中date_type和format_type参数取值如下:

日期类型	格式化类型	返回的格式化字符串
`DATE`	`USA`	`%m.%d.%Y`
`DATE`	`JIS`	`%Y-%m-%d`
`DATE`	`ISO`	`%Y-%m-%d`
`DATE`	`EUR`	`%d.%m.%Y`
`DATE`	`INTERNAL`	`%Y%m%d`
`TIME`	`USA`	`%h:%i:%s %p`
`TIME`	`JIS`	`%H:%i:%s`
`TIME`	`ISO`	`%H:%i:%s`
`TIME`	`EUR`	`%H.%i.%s`
`TIME`	`INTERNAL`	`%H%i%s`
`DATETIME`	`USA`	`%Y-%m-%d %h:%i:%s %p`
`DATETIME`	`JIS`	`%Y-%m-%d%H:%i:%s`
`DATETIME`	`ISO`	`%Y-%m-%dT%H:%i:%s`
`DATETIME`	`EUR`	`%d.%m.%Y %H.%i.%s`
`DATETIME`	`INTERNAL`	`%Y%m%d%H%i%s`

-- 解析时分秒:
mysql> SELECT 
NOW(), 
DATE_FORMAT(NOW(), '%H:%i:%s') AS `get_time` 
FROM DUAL;
+---------------------+----------+
| NOW()               | get_time |
+---------------------+----------+
| 2024-03-05 18:21:27 | 18:21:27 |
+---------------------+----------+
1 row in set (0.00 sec)

-- 解析年月日:
mysql> SELECT 
STR_TO_DATE('09/01/2009','%m/%d/%Y') AS `get_date` 
FROM DUAL;
+------------+
| get_date   |
+------------+
| 2009-09-01 |
+------------+
1 row in set (0.00 sec)

-- 字符串时间转为时间格式:
mysql> SELECT 
STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') AS `get_date_time`
FROM DUAL;
+---------------------+
| get_date_time       |
+---------------------+
| 2014-04-22 15:47:06 |
+---------------------+
1 row in set (0.00 sec)


-- 格式符之间可以设置连接符:
mysql> SELECT 
STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s') AS `get_date_time` 
FROM DUAL;
+---------------------+
| get_date_time       |
+---------------------+
| 2014-04-22 15:47:06 |
+---------------------+
1 row in set (0.00 sec)

-- 返回USA时间的格式化字符串:
mysql> SELECT
GET_FORMAT(DATE, 'USA') AS `get_format`
FROM DUAL;
+------------+
| get_format |
+------------+
| %m.%d.%Y   |
+------------+
1 row in set (0.00 sec)

-- 将当前时间使用USA格式(%m.%d.%Y )显示:
mysql> SELECT
DATE_FORMAT(NOW(), GET_FORMAT(DATE,'USA')) AS `usa_date_fromat`
FROM DUAL;
+-----------------+
| usa_date_fromat |
+-----------------+
| 03.05.2024      |
+-----------------+
1 row in set (0.00 sec)

-- 指定显示年月日:
mysql> SELECT 
STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') AS `get_date`
FROM DUAL;
+------------+
| get_date   |
+------------+
| 2020-01-01 |
+------------+
1 row in set, 1 warning (0.00 sec)

5. 流程控制函数

流程处理函数可以根据不同的条件, 执行不同的处理流程, 可以在SQL语句中实现不同的条件选择.
MySQL中的流程处理函数主要包括IF(), IFNULL()和CASE()函数.

函数	用法	示例
`IF(condition, value1, value2);`	`condition为评估条件, 值为TRUE, 返回value1, 否则返回value2.`	`IF(5 > 3, 'True', 'False') 返回 'True'`
`IFNULL(value1, value2);`	`如果value1不为NULL, 返回value1, 否则返回value2.`	`IFNULL(NULL, 'Fallback') 返回 'Fallback'`
`CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE resultn] END;`	`相当于C语言的if...else if...else...`	`CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END`
`CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 ... [ELSE 值n] END;`	`相当于C语言的switch...case...`	`CASE grade WHEN 'A' THEN '优秀' WHEN 'B' THEN '良好' ELSE '一般' END`

-- IF判断:
mysql> SELECT IF(1 > 0, 'true', 'false') FROM DUAL;
+----------------------------+
| IF(1 > 0, 'true', 'false') |
+----------------------------+
| true                       |
+----------------------------+
1 row in set (0.01 sec)

-- IFNULL语句:
mysql> SELECT IFNULL(null, 'Hello World') FROM DUAL;
+-----------------------------+
| IFNULL(null, 'Hello World') |
+-----------------------------+
| Hello World                 |
+-----------------------------+
1 row in set (0.00 sec)

-- 设置一个别名, 否则字段明就太长了(字段名是整个CASE语句).
-- 不设置评估的表达式, 从上往下遇到符合条件表达式执行子句并结束CASE语句:
SELECT
	CASE  -- 不设置评估的表达式
		WHEN 1 > 0 
			THEN '1 > 0'  
		WHEN 2 > 0 
			THEN '2 > 0'  
   		ELSE '没有匹配'  
    END AS `结果`;
+-------+
| 结果  |
+-------+
| 1 > 0 |
+-------+
1 row in set (0.00 sec)


SELECT 
	CASE  -- 不设置评估的表达式
		WHEN 1 = 0 
			THEN '1 = 0'
		WHEN 0 = 0
        	THEN '0 = 0'
		ELSE '没有匹配' 
	END AS `结果`;
+-------+
| 结果  |
+-------+
| 0 = 0 |
+-------+
1 row in set (0.00 sec)


SELECT
	CASE
		WHEN 1 = 0 
			THEN '1 = 0'
		WHEN 0 > 0
        	THEN '0 > 0'
		ELSE '没有匹配'
    END AS `结果`;
+----------+
| 结果     |
+----------+
| 没有匹配 |
+----------+
1 row in set (0.00 sec)


-- 设置常量值, 依据常量值选择, 遇到符合条件表达式执行子句并结束CASE语句:
SELECT  
    CASE 1 -- 设置常量
        WHEN 1 
        	THEN '我是1'  
        WHEN 2 
        	THEN '我是2'  
    	ELSE '没有匹配'  
    END AS `结果`;
+-------+
| 结果  |
+-------+
| 我是1 |
+-------+
1 row in set (0.00 sec)

SELECT  
    CASE 2 -- 设置常量
        WHEN 1 
        	THEN '我是1'  
        WHEN 2 
        	THEN '我是2'  
    	ELSE '没有匹配'  
    END AS `结果`;
+-------+
| 结果  |
+-------+
| 我是2 |
+-------+
1 row in set (0.00 sec)


SELECT  
    CASE 3 -- 设置常量
        WHEN 1 
        	THEN '我是1'  
        WHEN 2 
        	THEN '我是2'  
    	ELSE '没有匹配'  
    END AS `结果`;
+----------+
| 结果     |
+----------+
| 没有匹配 |
+----------+
1 row in set (0.00 sec)

-- 按照薪资打标签(结尾字段需要带,):
SELECT employee_id, salary,
	CASE 
        WHEN salary>=15000  -- 顺序不能出错!!!
            THEN '高薪'
        WHEN salary>=10000
            THEN '潜力股' 
        WHEN salary>=8000
            THEN '一般'
        ELSE '加油'
    END AS `描述`
FROM employees;
+-------------+----------+--------+
| employee_id | salary   | 描述   |
+-------------+----------+--------+
|         100 | 24000.00 | 高薪   |
|         101 | 17000.00 | 高薪   |
|         102 | 17000.00 | 高薪   |
|         103 |  9000.00 | 一般   |
|         104 |  6000.00 | 加油   |
|         105 |  4800.00 | 加油   |
|         ... |     ...  | ...    | -- 省略
|         198 |  2600.00 | 加油   |
|         199 |  2600.00 | 加油   |
|         200 |  4400.00 | 加油   |
|         201 | 13000.00 | 潜力股 |
|         202 |  6000.00 | 加油   |
|         203 |  6500.00 | 加油   |
|         204 | 10000.00 | 潜力股 |
|         205 | 12000.00 | 潜力股 |
|         206 |  8300.00 | 一般   |
+-------------+----------+--------+
107 rows in set (0.05 sec)

-- status字段作为判断的依据:
SELECT oid, `status`,
    CASE `status` 
        WHEN 1
            THEN '未付款'
        WHEN 2 
            THEN '已付款'
        WHEN 3 
            THEN '已发货' 
        WHEN 4 
            THEN '确认收货' 
        ELSE '无效订单'
    END AS `订单状态`
FROM t_order;

-- 计算年薪带佣金提成, 其中有些人有提成有些人没有提成, 可以使用IFNULL语句区分:
mysql> SELECT employee_id, 12 * salary * (1 + IFNULL(commission_pct, 0)) AS '年薪' FROM employees;
+-------------+-----------+
| employee_id | 年薪      |
+-------------+-----------+
|         100 | 288000.00 |
|         101 | 204000.00 |
|         102 | 204000.00 |
|         103 | 108000.00 |
|         ... | ...       |
|         202 |  72000.00 |
|         203 |  78000.00 |
|         204 | 120000.00 |
|         205 | 144000.00 |
|         206 |  99600.00 |
+-------------+-----------+
107 rows in set (0.00 sec)

-- 为IT_PROG, ST_CLERK, SA_REP三个部门的员工加薪:
SELECT first_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  '加油' 
	END AS `REVISED_SALARY`
FROM  employees;
+-------------+------------+----------+----------------+
| first_name  | job_id     | salary   | REVISED_SALARY |
+-------------+------------+----------+----------------+
| Steven      | AD_PRES    | 24000.00 | 加油           |
| Neena       | AD_VP      | 17000.00 | 加油           |
| Lex         | AD_VP      | 17000.00 | 加油           |
| Alexander   | IT_PROG    |  9000.00 | 9900.00        |
| Bruce       | IT_PROG    |  6000.00 | 6600.00        |
| David       | IT_PROG    |  4800.00 | 5280.00        |
| Valli       | IT_PROG    |  4800.00 | 5280.00        |
| ...         | ...        |  ...     |           ...  | -- 省略
| Alana       | SH_CLERK   |  3100.00 | 加油           |
| Kevin       | SH_CLERK   |  3000.00 | 加油           |
| Donald      | SH_CLERK   |  2600.00 | 加油           |
| Douglas     | SH_CLERK   |  2600.00 | 加油           |
| Jennifer    | AD_ASST    |  4400.00 | 加油           |
| Michael     | MK_MAN     | 13000.00 | 加油           |
| Pat         | MK_REP     |  6000.00 | 加油           |
| Susan       | HR_REP     |  6500.00 | 加油           |
| Hermann     | PR_REP     | 10000.00 | 加油           |
| Shelley     | AC_MGR     | 12000.00 | 加油           |
| William     | AC_ACCOUNT |  8300.00 | 加油           |
+-------------+------------+----------+----------------+
107 rows in set (0.00 sec)

-- 练习: 查询部门号为10, 20, 30的员工信息, 
-- 若部门号为10, 则打印其工资的1.1倍, 20号部门, 则打印其工资的1.2倍, 30号部门打印其工资的1.3倍数.
SELECT department_id, first_name, salary,
	CASE department_id
		WHEN 10
			THEN salary * 1.1
		WHEN 20
			THEN salary * 1.2
		WHEN 30
			THEN salary * 1.3
		ELSE '加油'
	END AS "REVISED_SALARY"
FROM  employees
WHERE department_id IN (10, 20, 30);
+---------------+------------+----------+----------------+
| department_id | first_name | salary   | REVISED_SALARY |
+---------------+------------+----------+----------------+
|            10 | Jennifer   |  4400.00 | 4840.00        |
|            20 | Michael    | 13000.00 | 15600.00       |
|            20 | Pat        |  6000.00 | 7200.00        |
|            30 | Den        | 11000.00 | 14300.00       |
|            30 | Alexander  |  3100.00 | 4030.00        |
|            30 | Shelli     |  2900.00 | 3770.00        |
|            30 | Sigal      |  2800.00 | 3640.00        |
|            30 | Guy        |  2600.00 | 3380.00        |
|            30 | Karen      |  2500.00 | 3250.00        |
+---------------+------------+----------+----------------+
9 rows in set (0.01 sec)

6. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理, 以防止数据被他人窃取.
这些函数在保证数据库安全时非常有用.

函数名	参数	返回值	描述
`PASSWORD(str)`	`str: 要加密的字符串`	`加密后的字符串, 41位长`	`返回字符串str的加密版本, 常用于用户密码加密. 加密结果不可逆. 在MySQL 8.0中, PASSWORD函数已被弃用.`
`MD5(str)`	`str: 要加密的字符串`	`加密后的字符串`	`返回字符串str的MD5加密后的值. 若参数为NULL, 返回NULL. MD5严格来说并不能算是加密.`
`SHA(str)`	`str: 要加密的字符串`	`加密后的字符串`	`从原明文密码str计算并返回加密后的字符串. 当参数为NULL时, 返回NULL. SHA加密算法比MD5更安全.`
`AES_ENCRYPT(value, password_seed)`	`value: 要加密的值，password_seed: 加密密码`	`加密后的字符串`	`返回使用password_seed作为加密密码加密value后的结果.`
`AES_DECRYPT(value, password_seed)`	`value: 要解密的值，password_seed: 加密密码`	`解密后的字符串`	`返回使用password_seed作为加密密码解密value的十六进制结果.`

-- MD5:
mysql> SELECT md5('123') FROM DUAL;
+----------------------------------+
| md5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.01 sec)

-- sha加密:
mysql> SELECT SHA('123') FROM DUAL;
+------------------------------------------+
| SHA('123')                               |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+
1 row in set (0.01 sec)

-- 对称加密:
mysql> SELECT AES_ENCRYPT('mysql', 'sql') AS `aes_encrypt` FROM DUAL;
+------------------------------------+
| aes_encrypt                        |
+------------------------------------+
| 0x526D32028DF8A1721BE85D33F1FABD8C |
+------------------------------------+
1 row in set (0.01 sec)

-- 解密(要确保加密后的值被正确保存到数据库后, 才能进行解密):
mysql> SELECT AES_DECRYPT('0x526D32028DF8A1721BE85D33F1FABD8C', 'sql') AS `password` FROM DUAL;
+--------------------+
| password           |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

-- 创建表格:
CREATE TABLE secure_data (  
    id INT PRIMARY KEY AUTO_INCREMENT,  -- id, 主键, 自增
    encrypted_data VARBINARY(255)       -- 长度的二进制数据, 最长255
);  

-- 写入数据
INSERT INTO secure_data (encrypted_data)   
VALUES (AES_ENCRYPT('mysql', 'sql'));

-- 查看数据:
mysql> SELECT * FROM secure_data;
+----+-------------------------------------+
| id | encrypted_data                      |
+----+-------------------------------------+
|  1 | 0x526D32028DF8A1721BE85D33F1FABD8C  |
+----+-------------------------------------+
1 row in set (0.00 sec)

-- 解密得到其字符串的16进制:
mysql> SELECT AES_DECRYPT(encrypted_data, 'sql') AS `decrypted_data` FROM secure_data WHERE id = 1;
+--------------------------------+
| decrypted_data                 |
+--------------------------------+
| 0x6D7973716C                   |
+--------------------------------+
1 row in set (0.00 sec)

-- 手动去除0x前缀:
-- UNHEX(): 将十六进制字符串转换为其二进制形式.
-- CAST(... AS CHAR): 将二进制值转换为字符类型.
mysql> SELECT CAST(UNHEX('6D7973716C') AS CHAR) AS `decrypted_data` FROM DUAL;
+----------------+
| decrypted_data |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

-- 舍弃前缀:
-- SUBSTRING(str, pos, len): 从一个字符串中提取子字符串.
mysql> SELECT CAST(UNHEX(SUBSTRING(AES_DECRYPT(encrypted_data, 'sql'), 3)) AS CHAR) FROM secure_data;
+---------------------------------------------------+
| CAST(UNHEX(SUBSTRING('0x6D7973716C', 3)) AS CHAR) |
+---------------------------------------------------+
| mysql                                             |
+---------------------------------------------------+
1 row in set (0.00 sec)

7. 信息函数

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

函数	用法	描述
`VERSION()`	`SELECT VERSION();`	`返回当前MySQL的版本号`
`CONNECTION_ID()`	`SELECT CONNECTION_ID();`	`返回当前MySQL服务器的连接数`
`DATABASE()`	`SELECT DATABASE();`	`返回当前MySQL命令行所在的数据库名`
`SCHEMA()`	`SELECT SCHEMA();`	`与DATABASE()函数功能相同，返回当前数据库名`
`USER()`	`SELECT USER();`	`返回当前连接MySQL的用户名, 格式为"用户名"@"主机名"`
`CURRENT_USER()`	`SELECT CURRENT_USER();`	`返回当前连接MySQL的用户名和主机名，与USER()函数相同`
`SYSTEM_USER()`	`SELECT SYSTEM_USER();`	`返回当前连接MySQL的用户名，与USER()函数相同`
`SESSION_USER()`	`SELECT SESSION_USER();`	`返回当前会话连接MySQL的用户名，与USER()函数相同`
`CHARSET(value)`	`SELECT CHARSET(value);`	`返回字符串value的字符集`
`COLLATION(value)`	`SELECT COLLATION(value);`	`返回字符串value的比较规则`

-- 当前MySQL命令行所在的数据库名:
mysql> SELECT DATABASE() FROM DUAL;
+------------+
| DATABASE() |
+------------+
| atguigudb  |
+------------+
1 row in set (0.00 sec)

-- 当前连接MySQL的用户名:
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER() FROM DUAL;
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
1 row in set (0.01 sec)

-- 字符串使用的字符集(国内终端使用的gbk, 字面常量也就是gbk):
mysql> SELECT CHARSET('ABC') FROM DUAL;
+----------------+
| CHARSET('ABC') |
+----------------+
| gbk            |
+----------------+
1 row in set (0.00 sec)

-- 字符串比较规则:
mysql> SELECT COLLATION('ABC') FROM DUAL;
+------------------+
| COLLATION('ABC') |
+------------------+
| gbk_chinese_ci   |
+------------------+
1 row in set (0.00 sec)

8. 其他函数

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

函数	用法	描述
`FORMAT(value, n)`	`返回格式化后的数字`	`将数字value进行格式化, 保留小数点后n位, 并四舍五入.`
`CONV(value, from, to)`	`返回进制转换后的值`	`将value从from进制转换为to进制.`
`INET_ATON(ipvalue)`	`返回IP地址的数字表示`	`将以点分隔的IP地址ipvalue转化为一个数字.`
`INET_NTOA(value)`	`返回数字形式的IP地址`	`将数字形式的IP地址value转化为以点分隔的IP地址.`
`BENCHMARK(n, expr)`	`返回执行表达式的结果`	`将表达式expr重复执行n次, 用于测试MySQL处理expr表达式所耗费的时间.`
`CONVERT(value USING char_code)`	`返回字符编码转换后的值`	`将value所使用的字符编码修改为char_code.`

-- 如果n的值小于或者等于0, 则只保留整数部分:
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2) FROM DUAL;
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12             | 124                | 123                 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)

-- 进制转换:
mysql>  SELECT CONV(16, 10, 2), CONV(8888, 10, 16), CONV(NULL, 10, 2) FROM DUAL;
+-----------------+--------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888, 10, 16) | CONV(NULL, 10, 2) |
+-----------------+--------------------+-------------------+
| 10000           | 22B8               | NULL              |
+-----------------+--------------------+-------------------+
1 row in set (0.00 sec)

-- 计算方式为: 192乘以256的3次方, 加上168乘以256的2次方, 加上1乘以256, 再加上100.
mysql> SELECT INET_ATON('192.168.1.100') FROM DUAL;
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
|                 3232235876 |
+----------------------------+
1 row in set (0.01 sec)

-- 数字转ip地址:
mysql> SELECT INET_NTOA(3232235876) FROM DUAL;
+-----------------------+
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100         |
+-----------------------+
1 row in set (0.01 sec)

-- 检测程序运行时间:
mysql> SELECT BENCHMARK(1, MD5('mysql')) FROM DUAL;
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.01 sec)

-- 检测程序运行时间(100w次):
mysql>  SELECT BENCHMARK(1000000, MD5('mysql')) FROM DUAL;
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (2.82 sec)

-- CONVERT函数使用'USING'关键字来指定目标字符集为'utf8'. 将字gbk符串'mysql'转换为'utf8'字符集:
mysql> SELECT CHARSET('mysql') AS `CHARSET`, CHARSET(CONVERT('mysql' USING 'utf8')) AS `CONVERT` FROM DUAL;
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| gbk              | utf8mb3                                |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

9.练习

-- 1. 显示系统时间(注:日期+时间):
mysql> SELECT NOW() FROM DUAL;
+---------------------+
| NOW()               |
+---------------------+
| 2024-03-07 23:17:28 |
+---------------------+
1 row in set (0.01 sec)

-- 2. 查询员工号, 姓名, 工资, 以及工资提高百分之20%后的结果(new salary):
mysql> SELECT employee_id, first_name, salary, salary * 1.2 AS `new salary` FROM employees;
+-------------+-------------+----------+------------+
| employee_id | first_name  | salary   | new salary |
+-------------+-------------+----------+------------+
|         100 | Steven      | 24000.00 |   28800.00 |
|         101 | Neena       | 17000.00 |   20400.00 |
|         102 | Lex         | 17000.00 |   20400.00 |
|         ... | ...         | ...      |    ...     | -- 省略
+-------------+-------------+----------+------------+
107 rows in set (0.00 sec)

-- 将员工的姓名按首字母排序, 并显示姓名的长度(length):
mysql> SELECT first_name, LENGTH(first_name) AS `length` FROM employees ORDER BY first_name;
+-------------+--------+
| first_name  | length |
+-------------+--------+
| Adam        |      4 |
| Alana       |      5 |
| Alberto     |      7 |
| Alexander   |      9 |
| Alexander   |      9 |
| Alexis      |      6 |
| Allan       |      5 |
| Alyssa      |      6 |
| Amit        |      4 |
| Anthony     |      7 |
| Britney     |      7 |
|         ... |    ... | -- 省略
+-------------+--------+
107 rows in set (0.00 sec)

-- 4. 查询员工id, 名字, 工资，并作为一个列输出(使用'-'分隔), 别名为OUT_PUT
mysql> SELECT CONCAT_WS('-', employee_id, first_name, salary) AS `OUT_PUT` FROM employees;
+-------------------------+
| OUT_PUT                 |
+-------------------------+
| 100-Steven-24000.00     |
| 101-Neena-17000.00      |
| 102-Lex-17000.00        |
| ...                     | -- 省略
+-------------------------+
107 rows in set (0.00 sec)

-- 5. 查询公司各员工信息(部门id, 姓名, 工作的年数, 工作的天数, 并按工作年数的降序排序):
-- hire_date字段是入职时间, 使用当前的时间-去入职时间为工作的时间.

TIMESTAMPDIFF():　用于计算两个日期或时间戳之间差异的函数．
它接受三个参数: 时间间隔的单位, 开始日期或时间戳以及结束日期或时间戳.
然后, 它返回这两个日期或时间戳之间的间隔数量.
TIMESTAMPDIFF 函数的基本语法: TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2);
其中:
unit是想要计算的时间间隔的单位, 可以是以下值之一:
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, 或 YEAR.
datetime_expr1: 是开始日期或时间戳.
datetime_expr2: 是结束日期或时间戳.

mysql> SELECT employee_id, first_name, 
TIMESTAMPDIFF(YEAR, hire_date, NOW()) AS `years_worked`,
DATEDIFF(NOW(), hire_date) AS`worked_days`
FROM employees
ORDER BY years_worked DESC;
+-------------+-------------+--------------+-------------+
| employee_id | first_name  | years_worked | worked_days |
+-------------+-------------+--------------+-------------+
|         100 | Steven      |           36 |       13413 |
|         200 | Jennifer    |           36 |       13321 |
|         101 | Neena       |           34 |       12586 |
|         103 | Alexander   |           34 |       12482 |
|         104 | Bruce       |           32 |       11979 |
|         102 | Lex         |           31 |       11376 |
|         ... | ...         |          ... |         ... | -- 省略
+-------------+-------------+--------------+-------------+
107 rows in set (0.00 sec)

-- 6.查询员工姓名, 部门id, 入职时间, 满足后面的条件
-- (雇用时间在1997年之后, department_id为80 或 90 或110, commission_pct不为空):
-- 当比较日期时间值时. 确保你使用的格式与数据库中的格式相匹配.
-- 通常, 使用 'YYYY-MM-DD' 或 'YYYY-MM-DD HH:MM:SS' 格式是安全的, 
-- 因为这些格式是ISO 8601标准格式, 并且MySQL能够很好地识别它们(MySQL会按照字典顺序进行比较, 这恰好与日期的大小顺序一致.).
SELECT first_name, department_id, hire_date 
FROM employees
WHERE department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL
AND hire_date >= '1997-1-1'; -- 存在着隐式转换
-- 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'); -- 显式转换操作(解析: 字符串 --> 日期)
+-------------+---------------+------------+
| first_name  | department_id | hire_date  |
+-------------+---------------+------------+
| Karen       |            80 | 1997-01-05 |
| Alberto     |            80 | 1997-03-10 |
| Gerald      |            80 | 1999-10-15 |
| ...         |           ... | ...        | -- 省略
| William     |            80 | 1999-02-23 |
| Elizabeth   |            80 | 1999-03-24 |
| Sundita     |            80 | 2000-04-21 |
| Alyssa      |            80 | 1997-03-19 |
| Jonathon    |            80 | 1998-03-24 |
| Jack        |            80 | 1998-04-23 |
| Charles     |            80 | 2000-01-04 |
+-------------+---------------+------------+
29 rows in set (0.00 sec)

-- 7. 查询公司中入职超过10000天的员工姓名, 入职时间, 入职天数:
SELECT first_name, hire_date, 
DATEDIFF(NOW(), hire_date) AS `worked_days` 
FROM employees 
WHERE DATEDIFF(NOW(), hire_date)  > 10000;
+------------+------------+-------------+
| first_name | hire_date  | worked_days |
+------------+------------+-------------+
| Steven     | 1987-06-17 |       13414 |
| Neena      | 1989-09-21 |       12587 |
| Lex        | 1993-01-13 |       11377 |
| ...        | ...        |       ...   | -- 省略
| Patrick    | 1996-03-04 |       10231 |
| Allan      | 1996-08-01 |       10081 |
| Ellen      | 1996-05-11 |       10163 |
| Nandita    | 1996-01-27 |       10268 |
| Sarah      | 1996-02-04 |       10260 |
| Jennifer   | 1987-09-17 |       13322 |
| Michael    | 1996-02-17 |       10247 |
| Susan      | 1994-06-07 |       10867 |
| Hermann    | 1994-06-07 |       10867 |
| Shelley    | 1994-06-07 |       10867 |
| William    | 1994-06-07 |       10867 |
+------------+------------+-------------+
27 rows in set (0.00 sec)

-- 8.做一个查询, 产生下面的结果:
+----------------------------------------------------------------+
| Dream Salary                                                   |
+----------------------------------------------------------------+
| <first_name> earns <salary>  monthly but wants <salary*3>      | -- <>不需要显示.
+----------------------------------------------------------------+

SELECT CONCAT(first_name,' earns ',
TRUNCATE(salary,0),
' monthly but wants ',
TRUNCATE(salary * 3,0)) AS `Dream Salary`
FROM employees;
+------------------------------------------------+
| Dream Salary                                   |
+------------------------------------------------+
| Steven earns 24000 monthly but wants 72000     |
| Neena earns 17000 monthly but wants 51000      |
| Lex earns 17000 monthly but wants 51000        |
| Alexander earns 9000 monthly but wants 27000   |
| Bruce earns 6000 monthly but wants 18000       |
| David earns 4800 monthly but wants 14400       |
| Valli earns 4800 monthly but wants 14400       |
| Diana earns 4200 monthly but wants 12600       |
| Nancy earns 12000 monthly but wants 36000      |
| Daniel earns 9000 monthly but wants 27000      |
| John earns 8200 monthly but wants 24600        |
| Ismael earns 7700 monthly but wants 23100      |
| Jose Manuel earns 7800 monthly but wants 23400 |
| ...                                            | -- 省略
+------------------------------------------------+
107 rows in set (0.00 sec)

-- 9.使用case-when, 按照下面的条件产生下面的结果:

/*
  first_name    job                    grade
  xxx           AD_PRES              	A
  xxx           ST_MAN               	B
  xxx           IT_PROG              	C
  xxx           SA_REP               	D
  xxx           ST_CLERK             	E
*/

SELECT first_name, job_id AS `job`,
CASE job_id
    WHEN 'AD_PRES' THEN 'A'
    WHEN 'ST_MAN'  THEN 'B'
    WHEN 'IT_PROG'  THEN 'C'
    WHEN 'SA_REP'   THEN 'D'
    WHEN 'ST_CLERK' THEN 'E'
    ELSE '加油'
END AS `grade`
from employees;

+-------------+------------+-------+
| first_name  | job        | grade |
+-------------+------------+-------+
| Steven      | AD_PRES    | A     |
| Neena       | AD_VP      | 加油  |
| Lex         | AD_VP      | 加油  |
| Alexander   | IT_PROG    | C     |
| Bruce       | IT_PROG    | C     |
| David       | IT_PROG    | C     |
| Valli       | IT_PROG    | C     |
| Diana       | IT_PROG    | C     |
| Nancy       | FI_MGR     | 加油  |
| Daniel      | FI_ACCOUNT | 加油  |
| John        | FI_ACCOUNT | 加油  |
| Ismael      | FI_ACCOUNT | 加油  |
| Jose Manuel | FI_ACCOUNT | 加油  |
| Luis        | FI_ACCOUNT | 加油  |
| Den         | PU_MAN     | 加油  |
| Alexander   | PU_CLERK   | 加油  |
| Shelli      | PU_CLERK   | 加油  |
| ...         | ...        | ...   | -- 省略
+-------------+------------+-------+
107 rows in set (0.00 sec)

开局签到Python基础

关注

5
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
25.4 MySQL 函数

在编程中, 函数是一种组织代码的方式, 用于执行特定任务.它是一段可以被重复使用的代码块, 通常接受一些输入(参数)然后返回一个输出.函数可以帮助开发者将大型程序分解为更小的, 更易于管理的部分, 提高代码的可读性和可维护性.函数在编程语言和SQL中都扮演着非常重要的角色.以下是对函数的一些详细解释:* 1. 代码重用和模块化: 函数允许我们将一段常用的代码块封装起来, 并在需要时多次调用.这避免了代码的重复, 并使得代码结构更清晰, 更易于阅读和维护.
复制链接

扫一扫