MySQL数据库函数详解：增强数据处理能力

# 1. MySQL函数概述**

MySQL函数是一种预定义的代码块，用于执行特定操作或计算。它们可以简化和增强数据处理任务，减少查询的复杂性和提高性能。

MySQL函数种类繁多，包括字符串函数、数值函数、聚合函数、条件函数和系统函数。这些函数可以用于各种目的，例如：

* 字符串操作：格式化、搜索、替换和比较字符串。
* 数值计算：执行四则运算、三角函数和舍入操作。
* 聚合数据：计算总和、平均值和计数等聚合值。
* 条件求值：根据条件返回不同的值。
* 系统信息：获取有关数据库版本、用户和连接的信息。

# 2. 字符串函数

字符串函数是 MySQL 中用来处理和操作字符串数据的强大工具。它们提供了广泛的功能，包括长度和字符操作、字符串搜索和替换、字符串格式化、字符集转换以及排序和比较。

### 2.1 字符串处理函数

#### 2.1.1 长度和字符操作函数

**LENGTH() 函数**

LENGTH(string)

**参数说明：**

* `string`：要计算长度的字符串。

**逻辑分析：**

`LENGTH()` 函数返回指定字符串的字符数，包括空格。

**示例：**

SELECT LENGTH('Hello World');
-- 输出：11

**CHAR_LENGTH() 函数**

CHAR_LENGTH(string)

**参数说明：**

* `string`：要计算长度的字符串。

**逻辑分析：**

`CHAR_LENGTH()` 函数与 `LENGTH()` 函数类似，但它不计算空格的长度。

**示例：**

SELECT CHAR_LENGTH('Hello World');
-- 输出：10

**SUBSTRING() 函数**

SUBSTRING(string, start, length)

**参数说明：**

* `string`：要提取子字符串的字符串。
* `start`：子字符串的起始位置，从 1 开始。
* `length`：子字符串的长度。

**逻辑分析：**

`SUBSTRING()` 函数从指定字符串中提取一个子字符串。

**示例：**

SELECT SUBSTRING('Hello World', 6, 5);
-- 输出：World

#### 2.1.2 字符串搜索和替换函数

**INSTR() 函数**

INSTR(string, substring)

**参数说明：**

* `string`：要搜索子字符串的字符串。
* `substring`：要搜索的子字符串。

**逻辑分析：**

`INSTR()` 函数返回子字符串在指定字符串中首次出现的索引位置。如果子字符串不存在，则返回 0。

**示例：**

SELECT INSTR('Hello World', 'World');
-- 输出：7

**REPLACE() 函数**

REPLACE(string, old_string, new_string)

**参数说明：**

* `string`：要替换字符串的字符串。
* `old_string`：要替换的旧字符串。
* `new_string`：要替换的新字符串。

**逻辑分析：**

`REPLACE()` 函数将指定字符串中的所有旧字符串替换为新字符串。

**示例：**

SELECT REPLACE('Hello World', 'World', 'Universe');
-- 输出：Hello Universe

#### 2.1.3 字符串格式化函数

**CONCAT() 函数**

CONCAT(string1, string2, ..., stringN)

**参数说明：**

* `string1`, `string2`, ..., `stringN`：要连接的字符串。

**逻辑分析：**

`CONCAT()` 函数将多个字符串连接成一个字符串。

**示例：**

SELECT CONCAT('Hello', ' ', 'World');
-- 输出：Hello World

**FORMAT() 函数**

FORMAT(number, format_string)

**参数说明：**

* `number`：要格式化的数字。
* `format_string`：指定数字格式的格式字符串。

**逻辑分析：**

`FORMAT()` 函数将数字格式化为指定格式的字符串。

**示例：**

SELECT FORMAT(12345.6789, '99999.99');
-- 输出：12345.68

### 2.2 字符集和排序函数

#### 2.2.1 字符集转换函数

**CONVERT() 函数**

CONVERT(string, to_charset, from_charset)

**参数说明：**

* `string`：要转换的字符串。
* `to_charset`：要转换到的字符集。
* `from_charset`：要转换的字符集。

**逻辑分析：**

`CONVERT()` 函数将指定字符串从一种字符集转换为另一种字符集。

**示例：**

SELECT CONVERT('你好', 'utf8', 'gbk');
-- 输出：你好

#### 2.2.2 排序和比较函数

**COLLATE() 函数**

COLLATE(string, collation)

**参数说明：**

* `string`：要排序的字符串。
* `collation`：要使用的排序规则。

**逻辑分析：**

`COLLATE()` 函数根据指定的排序规则对字符串进行排序。

**示例：**

SELECT COLLATE('Hello', 'utf8_bin');
-- 输出：Hello

**BINARY() 函数**

BINARY(string)

**参数说明：**

* `string`：要进行二进制比较的字符串。

**逻辑分析：**

`BINARY()` 函数将字符串转换为二进制值，以便进行二进制比较。

**示例：**

SELECT BINARY('Hello') = BINARY('hello');
-- 输出：false

# 3. 数值函数

### 3.1 数值处理函数

#### 3.1.1 四则运算函数

**ABS() 函数**

* **功能：**返回一个数的绝对值。
* **语法：** `ABS(x)`
* **参数：**
* `x`：要计算绝对值的数值。
* **返回：**一个绝对值。
* **示例：**

SELECT ABS(-10); -- 返回 10

**ACOS() 函数**

* **功能：**返回一个数的反余弦值。
* **语法：** `ACOS(x)`
* **参数：**
* `x`：要计算反余弦值的数值。
* **返回：**一个反余弦值（弧度）。
* **示例：**

SELECT ACOS(0.5); -- 返回 1.0471975511965976

**ASIN() 函数**

* **功能：**返回一个数的反正弦值。
* **语法：** `ASIN(x)`
* **参数：**
* `x`：要计算反正弦值的数值。
* **返回：**一个反正弦值（弧度）。
* **示例：**

SELECT ASIN(0.5); -- 返回 0.5235987755982988

**ATAN() 函数**

* **功能：**返回一个数的反正切值。
* **语法：** `ATAN(x)`
* **参数：**
* `x`：要计算反正切值的数值。
* **返回：**一个反正切值（弧度）。
* **示例：**

SELECT ATAN(1); -- 返回 0.7853981633974483

#### 3.1.2 三角函数和对数函数

**COS() 函数**

* **功能：**返回一个数的余弦值。
* **语法：** `COS(x)`
* **参数：**
* `x`：要计算余弦值的数值（弧度）。
* **返回：**一个余弦值。
* **示例：**

SELECT COS(PI() / 3); -- 返回 0.5

**SIN() 函数**

* **功能：**返回一个数的正弦值。
* **语法：** `SIN(x)`
* **参数：**
* `x`：要计算正弦值的数值（弧度）。
* **返回：**一个正弦值。
* **示例：**

SELECT SIN(PI() / 4); -- 返回 0.7071067811865475

**TAN() 函数**

* **功能：**返回一个数的正切值。
* **语法：** `TAN(x)`
* **参数：**
* `x`：要计算正切值的数值（弧度）。
* **返回：**一个正切值。
* **示例：**

SELECT TAN(PI() / 6); -- 返回 0.5773502691896257

**LOG() 函数**

* **功能：**返回一个数以指定基数的对数。
* **语法：** `LOG(x, base)`
* **参数：**
* `x`：要计算对数的数值。
* `base`：对数的基数（可选，默认为 10）。
* **返回：**一个对数值。
* **示例：**

SELECT LOG(100, 10); -- 返回 2
SELECT LOG(100); -- 返回 2（默认基数为 10）

#### 3.1.3 舍入和取整函数

**ROUND() 函数**

* **功能：**将一个数舍入到指定的位数。
* **语法：** `ROUND(x, d)`
* **参数：**
* `x`：要舍入的数值。
* `d`：舍入的位数。
* **返回：**一个舍入后的数。
* **示例：**

SELECT ROUND(123.456, 2); -- 返回 123.46

**CEIL() 函数**

* **功能：**将一个数向上取整。
* **语法：** `CEIL(x)`
* **参数：**
* `x`：要取整的数值。
* **返回：**一个向上取整后的数。
* **示例：**

SELECT CEIL(123.456); -- 返回 124

**FLOOR() 函数**

* **功能：**将一个数向下取整。
* **语法：** `FLOOR(x)`
* **参数：**
* `x`：要取整的数值。
* **返回：**一个向下取整后的数。
* **示例：**

SELECT FLOOR(123.456); -- 返回 123

# 4. 聚合函数

### 4.1 聚合函数概述

聚合函数用于对一组数据进行汇总和计算，生成单个结果。它们广泛应用于数据分析、统计和报表生成中。MySQL 提供了丰富的聚合函数，可满足各种数据处理需求。

聚合函数通常与 `GROUP BY` 子句结合使用，对分组后的数据进行计算。`GROUP BY` 子句将数据按指定列分组，然后聚合函数对每个组的数据进行计算。

### 4.2 单行聚合函数

单行聚合函数对单个数据行进行计算，不受 `GROUP BY` 子句的影响。常用的单行聚合函数包括：

#### 4.2.1 COUNT() 函数

`COUNT()` 函数计算指定列中非空值的个数。它可以用于统计数据集中记录的数量或计算特定条件下满足条件的记录数量。

**语法：**

COUNT(column_name)

**参数：**

* `column_name`：要计算非空值的列名。

**示例：**

SELECT COUNT(name) FROM customers;

**结果：**

该查询返回 `customers` 表中 `name` 列的非空值数量。

#### 4.2.2 SUM() 函数

`SUM()` 函数计算指定列中所有值的总和。它可以用于计算总销售额、总成本或其他数值数据的总和。

**语法：**

SUM(column_name)

**参数：**

* `column_name`：要计算总和的列名。

**示例：**

SELECT SUM(sales) FROM orders;

**结果：**

该查询返回 `orders` 表中 `sales` 列的所有值的总和。

#### 4.2.3 AVG() 函数

`AVG()` 函数计算指定列中所有值的平均值。它可以用于计算平均销售额、平均成本或其他数值数据的平均值。

**语法：**

AVG(column_name)

**参数：**

* `column_name`：要计算平均值的列名。

**示例：**

SELECT AVG(salary) FROM employees;

**结果：**

该查询返回 `employees` 表中 `salary` 列的所有值的平均值。

### 4.3 多行聚合函数

多行聚合函数对分组后的数据进行计算，受 `GROUP BY` 子句的影响。常用的多行聚合函数包括：

#### 4.3.1 GROUP BY 子句

`GROUP BY` 子句将数据按指定列分组，然后聚合函数对每个组的数据进行计算。

**语法：**

GROUP BY column_name1, column_name2, ...

**参数：**

* `column_name1`, `column_name2`, ...：要分组的列名。

**示例：**

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

**结果：**

该查询将 `employees` 表中的数据按 `department_id` 列分组，并计算每个部门的总工资。

#### 4.3.2 HAVING 子句

`HAVING` 子句用于对分组后的数据进行过滤，仅选择满足指定条件的组。

**语法：**

HAVING condition

**参数：**

* `condition`：过滤条件。

**示例：**

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

**结果：**

该查询将 `employees` 表中的数据按 `department_id` 列分组，并计算每个部门的总工资。它仅选择总工资大于 100,000 的部门。

# 5. 条件函数

### 5.1 条件函数概述

条件函数用于根据特定条件返回不同的值。它们在数据处理中非常有用，可以简化复杂的查询并提高代码的可读性。

### 5.2 CASE函数

CASE函数是一种多路分支语句，根据给定的条件返回不同的值。它有两种形式：简单CASE函数和搜索CASE函数。

#### 5.2.1 简单CASE函数

语法：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

* **condition1、condition2...**：要评估的条件。
* **result1、result2...**：当相应条件为真时返回的值。
* **default_result**：当所有条件都为假时返回的默认值（可选）。

例如：

SELECT CASE
    WHEN age < 18 THEN '未成年'
    WHEN age >= 18 AND age < 65 THEN '成年'
    ELSE '老年'
END AS age_group
FROM users;

#### 5.2.2 搜索CASE函数

语法：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

* **expression**：要评估的表达式。
* **value1、value2...**：要匹配的特定值。
* **result1、result2...**：当相应值匹配时返回的值。
* **default_result**：当所有值都不匹配时返回的默认值（可选）。

例如：

SELECT CASE gender
    WHEN 'M' THEN '男性'
    WHEN 'F' THEN '女性'
    ELSE '未知'
END AS gender_description
FROM users;

### 5.3 IFNULL()函数和COALESCE()函数

IFNULL()函数和COALESCE()函数用于处理空值。它们返回第一个非空值，如果没有非空值，则返回指定的默认值。

#### 5.3.1 IFNULL()函数

语法：

IFNULL(expression, default_value)

* **expression**：要评估的表达式。
* **default_value**：当expression为NULL时返回的默认值。

例如：

SELECT IFNULL(name, '无名') AS user_name
FROM users;

#### 5.3.2 COALESCE()函数

语法：

COALESCE(expression1, expression2, ..., default_value)

* **expression1、expression2...**：要评估的表达式。
* **default_value**：当所有表达式都为NULL时返回的默认值（可选）。

COALESCE()函数可以评估多个表达式，直到找到一个非空值，然后返回该非空值。如果所有表达式都为NULL，则返回指定的默认值。

例如：

SELECT COALESCE(address, city, country) AS location
FROM users;

# 6. 系统函数**

系统函数提供有关MySQL数据库系统和服务器状态的信息。这些函数对于监视和诊断数据库性能、管理用户和连接以及获取系统变量的值非常有用。

### **6.1 系统信息函数**

**6.1.1 版本信息函数**

| 函数 | 描述 |
|---|---|
| `VERSION()` | 返回MySQL服务器版本号 |
| `VERSION_COMMENT()` | 返回MySQL服务器版本注释 |
| `CURRENT_USER()` | 返回当前连接用户 |

**示例：**

SELECT VERSION();
-- 输出：8.0.28

**6.1.2 用户信息函数**

| 函数 | 描述 |
|---|---|
| `USER()` | 返回当前连接用户的用户名 |
| `SYSTEM_USER()` | 返回创建当前连接的系统用户 |
| `CURRENT_ROLE()` | 返回当前连接的角色 |

**示例：**

SELECT USER();
-- 输出：root

### **6.2 连接和状态函数**

**6.2.1 连接信息函数**

| 函数 | 描述 |
|---|---|
| `CONNECTION_ID()` | 返回当前连接的ID |
| `LAST_INSERT_ID()` | 返回上一个INSERT或UPDATE语句中插入或更新的最后一条记录的ID |
| `FOUND_ROWS()` | 返回SELECT语句中匹配的行数 |

**示例：**

SELECT CONNECTION_ID();
-- 输出：1

**6.2.2 状态信息函数**

| 函数 | 描述 |
|---|---|
| `@@global.max_connections` | 返回服务器允许的最大连接数 |
| `@@session.tx_isolation` | 返回当前连接的事务隔离级别 |
| `@@autocommit` | 返回当前连接的自动提交状态 |

**示例：**

SELECT @@global.max_connections;
-- 输出：151