第一部分：基础知识 6. 函数 --[MySQL轻松入门教程]

MySQL 提供了丰富的内置函数，涵盖了字符串处理、数值计算、日期时间操作、聚合分析以及控制流等多个方面。这些函数可以帮助用户更高效地进行数据查询和处理。

1.字符串函数

MySQL 提供了丰富的字符串函数来帮助用户处理和操作字符串数据。下面是一些常用的 MySQL 字符串函数及其简要说明：

1. CONCAT(str1, str2, ...)

   • 将多个字符串连接成一个字符串。
   • 示例：SELECT CONCAT('Hello', ' ', 'World') AS greeting;

2. CONCAT_WS(separator, str1, str2, ...)

   • 使用指定的分隔符将多个字符串连接起来。
   • 示例：SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Orange');

3. INSERT(str, pos, len, newstr)

   • 在给定位置开始，用 newstr 替换 str 中长度为 len 的子串。
   • 示例：SELECT INSERT('abcdefg', 2, 3, 'xyz');

4. LOWER(str) 或 LCASE(str)

   • 将字符串转换为小写。
   • 示例：SELECT LOWER('HELLO WORLD');

5. UPPER(str) 或 UCASE(str)

   • 将字符串转换为大写。
   • 示例：SELECT UPPER('hello world');

6. LEFT(str, len)

   • 返回字符串最左边的 len 个字符。
   • 示例：SELECT LEFT('hello', 2);

7. RIGHT(str, len)

   • 返回字符串最右边的 len 个字符。
   • 示例：SELECT RIGHT('hello', 3);

8. SUBSTRING(str, pos, len) 或 SUBSTR(str, pos, len)

   • 从位置 pos 开始提取长度为 len 的子串。
   • 示例：SELECT SUBSTRING('hello', 2, 3);

9. REPLACE(str, from_str, to_str)

   • 将字符串 str 中的所有 from_str 替换为 to_str。
   • 示例：SELECT REPLACE('www.mysql.com', 'w', 'Ww');

10. TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

    • 移除字符串开头、结尾或两边的指定字符（默认为空格）。
    • 示例：SELECT TRIM(' hello ');

11. REVERSE(str)

    • 反转字符串。
    • 示例：SELECT REVERSE('hello');

12. LENGTH(str)

    • 返回字符串的字节长度。
    • 示例：SELECT LENGTH('你好'); （注意中文字符在某些字符集下可能占多字节）

13. CHAR_LENGTH(str)

    • 返回字符串的字符数。
    • 示例：SELECT CHAR_LENGTH('你好');

14. LOCATE(substr, str[, pos]) 或 POSITION(substr IN str)

    • 返回子串 substr 在字符串 str 中第一次出现的位置。
    • 示例：SELECT LOCATE('bar', 'foobarbar');

15. INSTR(str, substr)

    • 返回子串 substr 在字符串 str 中第一次出现的位置。
    • 示例：SELECT INSTR('foobarbar', 'bar');

这些函数可以帮助你执行各种字符串处理任务，如文本格式化、搜索、替换等。根据你的具体需求，选择合适的函数可以极大地简化查询和数据处理工作。

2.数值函数

MySQL 提供了一系列数值函数，用于执行数学计算和处理数值数据。以下是一些常用的 MySQL 数值函数及其简要说明：

1. ABS(X)

   • 返回 X 的绝对值。
   • 示例：SELECT ABS(-32);

2. CEIL(X) 或 CEILING(X)

   • 返回不小于 X 的最小整数值。
   • 示例：SELECT CEIL(1.23);

3. FLOOR(X)

   • 返回不大于 X 的最大整数值。
   • 示例：SELECT FLOOR(1.89);

4. ROUND(X[, D])

   • 返回参数 X 四舍五入后的值，可选参数 D 指定保留的小数位数。
   • 示例：SELECT ROUND(1.298, 1);

5. TRUNCATE(X, D)

   • 返回数字 X 截断为 D 小数位的结果。如果 D 是负数，则截断（归零）在小数点左侧。
   • 示例：SELECT TRUNCATE(1.999, 1);

6. MOD(N, M) 或者使用 % 运算符

   • 返回 N 被 M 除后的余数。
   • 示例：SELECT MOD(29, 3); 或 SELECT 29 % 3;

7. POW(X, Y) 或 POWER(X, Y)

   • 返回 X 的 Y 次幂。
   • 示例：SELECT POW(2, 3);

8. SQRT(X)

   • 返回非负数 X 的平方根。
   • 示例：SELECT SQRT(16);

9. RAND() 和 RAND(N)

   • 返回一个0到1之间的随机浮点值。RAND(N) 使用 N 作为种子值。
   • 示例：SELECT RAND();

10. SIGN(X)

    • 返回参数 X 的符号，-1、0 或 1（负数、零或正数）。
    • 示例：SELECT SIGN(-320);

11. CONV(N, from_base, to_base)

    • 在不同数制之间转换数字 N。from_base 和 to_base 是基数，范围是2到36。
    • 示例：SELECT CONV('A', 16, 10); （将十六进制的 ‘A’ 转换为十进制）

12. PI()

    • 返回圆周率 π 的值。
    • 示例：SELECT PI();

13. EXP(X)

    • 返回 e 的 X 次幂 (e^X)。
    • 示例：SELECT EXP(1);

14. LOG(B, X) 或 LOG(X)

    • 如果指定两个参数，则返回以 B 为底 X 的对数；如果只有一个参数，则返回自然对数。
    • 示例：SELECT LOG(2, 8);

15. LOG2(X)

    • 返回 X 的以2为底的对数。
    • 示例：SELECT LOG2(8);

16. LOG10(X)

    • 返回 X 的以10为底的对数。
    • 示例：SELECT LOG10(100);

这些数值函数可以用来执行各种数学运算，从简单的四则运算到更复杂的数学问题求解。根据你的具体需求选择适当的函数可以帮助你有效地进行数值数据的处理和分析。

3.日期和时间函数

MySQL 提供了多种日期和时间函数，用于处理日期时间数据。以下是一些常用的 MySQL 日期和时间函数及其简要说明：

1. NOW() 或 CURRENT_TIMESTAMP

   • 返回当前的日期和时间。
   • 示例：SELECT NOW();

2. CURDATE() 或 CURRENT_DATE

   • 返回当前日期。
   • 示例：SELECT CURDATE();

3. CURTIME() 或 CURRENT_TIME

   • 返回当前时间。
   • 示例：SELECT CURTIME();

4. DATE(expr)

   • 提取日期部分。
   • 示例：SELECT DATE('2024-06-15 09:30:25');

5. TIME(expr)

   • 提取时间部分。
   • 示例：SELECT TIME('2024-06-15 09:30:25');

6. YEAR(date), MONTH(date), DAY(date) 等

   • 分别提取给定日期中的年份、月份、日等。
   • 示例：SELECT YEAR('2024-06-15'), MONTH('2024-06-15'), DAY('2024-06-15');

7. HOUR(time), MINUTE(time), SECOND(time) 等

   • 分别提取给定时间中的小时、分钟、秒等。
   • 示例：SELECT HOUR('09:30:25'), MINUTE('09:30:25'), SECOND('09:30:25');

8. DATEDIFF(expr1, expr2)

   • 计算两个日期之间的天数差。
   • 示例：SELECT DATEDIFF('2024-06-15', '2024-06-01');

9. TIMEDIFF(expr1, expr2)

   • 计算两个时间值或日期时间值之间的时间差。
   • 示例：SELECT TIMEDIFF('10:05:30', '09:00:00');

10. ADDDATE(date, INTERVAL expr unit) 和 DATE_ADD(date, INTERVAL expr unit)

    • 向日期添加指定的时间间隔。
    • 示例：SELECT ADDDATE('2024-06-15', INTERVAL 10 DAY);

11. SUBDATE(date, INTERVAL expr unit) 和 DATE_SUB(date, INTERVAL expr unit)

    • 从日期减去指定的时间间隔。
    • 示例：SELECT SUBDATE('2024-06-15', INTERVAL 10 DAY);

12. STR_TO_DATE(str, format)

    • 按照指定格式将字符串转换为日期。
    • 示例：SELECT STR_TO_DATE('15/06/2024', '%d/%m/%Y');

13. DATE_FORMAT(date, format)

    • 根据指定的格式格式化日期或时间值。
    • 示例：SELECT DATE_FORMAT('2024-06-15 09:30:25', '%W %M %Y');

14. UNIX_TIMESTAMP([date]) 和 FROM_UNIXTIME(unix_timestamp[, format])

    • UNIX_TIMESTAMP 将日期时间转换为 Unix 时间戳（自1970-01-01以来的秒数），而 FROM_UNIXTIME 则是反向操作。
    • 示例：SELECT UNIX_TIMESTAMP('2024-06-15 09:30:25');
    • 示例：SELECT FROM_UNIXTIME(1718676625, '%Y-%m-%d %H:%i:%s');

15. PERIOD_ADD(P, N) 和 PERIOD_DIFF(P1, P2)

    • PERIOD_ADD 向表示为YYMM或YYYYMM的时期 P 添加 N 个月。
    • PERIOD_DIFF 计算两个表示为YYMM或YYYYMM的时期之间的月份数差异。
    • 示例：SELECT PERIOD_ADD(202406, 5);
    • 示例：SELECT PERIOD_DIFF(202406, 202306);

这些日期和时间函数可以帮助你执行各种与时间相关的计算和操作，如日期间的计算、日期格式化、日期时间的增减等。根据你的具体需求选择适当的函数可以让你更高效地处理日期时间数据。

4.聚合函数

MySQL 提供了多种聚合函数，用于对数据集进行统计分析。这些函数可以用来计算一组值的总和、平均数、计数等。以下是 MySQL 中常用的聚合函数及其简要说明：

1. COUNT([DISTINCT] expr)

   • 计算行数或非空表达式的数量。
   • COUNT(*) 计算所有行的数量，包括NULL值。
   • COUNT(expr) 计算expr非NULL结果的数量。
   • COUNT(DISTINCT expr) 计算expr不同非NULL结果的数量。
   • 示例：SELECT COUNT(*) FROM orders;
   • 示例：SELECT COUNT(order_id) FROM orders;
   • 示例：SELECT COUNT(DISTINCT customer_id) FROM orders;

2. SUM([DISTINCT] expr)

   • 返回给定列中所有数值的总和。
   • 如果列中有NULL，则忽略它们。
   • DISTINCT 可选参数确保只考虑不同的值。
   • 示例：SELECT SUM(amount) FROM sales;
   • 示例：SELECT SUM(DISTINCT amount) FROM sales;

3. AVG([DISTINCT] expr)

   • 返回给定列中所有数值的平均值。
   • 忽略NULL值。
   • DISTINCT 可选参数确保只考虑不同的值。
   • 示例：SELECT AVG(price) FROM products;
   • 示例：SELECT AVG(DISTINCT price) FROM products;

4. MAX([DISTINCT] expr)

   • 返回给定列中的最大值。
   • 对于字符类型，它返回按字母顺序最后的值。
   • DISTINCT 参数是可选的，并且通常不使用，因为它不影响结果。
   • 示例：SELECT MAX(salary) FROM employees;

5. MIN([DISTINCT] expr)

   • 返回给定列中的最小值。
   • 对于字符类型，它返回按字母顺序最先的值。
   • DISTINCT 参数是可选的，并且通常不使用，因为它不影响结果。
   • 示例：SELECT MIN(salary) FROM employees;

6. GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

   • 将多行字符串连接成一个字符串结果，常用于分组查询。
   • 可以指定排序和分隔符。
   • 示例：SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users GROUP BY department;

7. BIT_AND(expr)

   • 返回给定集合中所有位的按位与操作的结果。
   • 示例：SELECT BIT_AND(flags) FROM settings;

8. BIT_OR(expr)

   • 返回给定集合中所有位的按位或操作的结果。
   • 示例：SELECT BIT_OR(flags) FROM settings;

9. BIT_XOR(expr)

   • 返回给定集合中所有位的按位异或操作的结果。
   • 示例：SELECT BIT_XOR(flags) FROM settings;

10. STDDEV_POP(expr) 和 STDDEV_SAMP(expr)

    • 分别计算总体标准差（STDDEV_POP）和样本标准差（STDDEV_SAMP）。
    • 示例：SELECT STDDEV_POP(score) FROM students;

11. VAR_POP(expr) 和 VAR_SAMP(expr)

    • 分别计算总体方差（VAR_POP）和样本方差（VAR_SAMP）。
    • 示例：SELECT VAR_SAMP(score) FROM students;

这些聚合函数通常与 GROUP BY 语句一起使用，以便对数据进行分组并为每个组计算汇总信息。通过组合使用这些函数，你可以执行复杂的数据分析任务，从简单的统计到更高级的数据挖掘。

5.控制流函数

MySQL 提供了一些控制流函数，这些函数允许你在 SQL 查询中实现条件逻辑。这使得你可以根据不同的条件执行不同的操作或返回不同的结果。以下是 MySQL 中常用的控制流函数及其简要说明：

1. IF(expr, true_val, false_val)

   • 如果 expr 为真（非零且非NULL），则返回 true_val；否则返回 false_val。
   • 示例：SELECT IF(1 > 2, 'Yes', 'No');

2. CASE 表达式

   • CASE 可以有两种形式：

     • 简单 CASE：将表达式与多个值进行比较。

       CASE exprWHEN val1 THEN result1[WHEN val2 THEN result2 ...][ELSE else_result]
       END
       

     • 搜索 CASE：检查多个条件。

       CASEWHEN condition1 THEN result1[WHEN condition2 THEN result2 ...][ELSE else_result]
       END
       

   • 示例：SELECT CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS status FROM persons;

3. IFNULL(expr1, expr2)

   • 如果 expr1 不是 NULL，则返回 expr1；否则返回 expr2。
   • 示例：SELECT IFNULL(NULL, 'Default Value');

4. NULLIF(expr1, expr2)

   • 如果 expr1 = expr2，则返回 NULL；否则返回 expr1。
   • 这个函数对于防止除以0错误等情况非常有用。
   • 示例：SELECT NULLIF(1, 1); 返回 NULL。

5. COALESCE(expr1, expr2, ..., exprN)

   • 返回第一个非 NULL 的表达式的值。如果所有参数都是 NULL，则返回 NULL。
   • 示例：SELECT COALESCE(NULL, NULL, 'Third Value', 'Fourth Value');

6. ELT(N, str1, str2, ...)

   • 返回给定索引 N 对应的字符串。如果 N 是 1，则返回 str1，依此类推。
   • 示例：SELECT ELT(2, 'apple', 'banana', 'orange'); 返回 ‘banana’。

7. FIELD(str, str1, str2, ...)

   • 返回 str 在列表中的位置。如果没有找到，则返回 0。
   • 示例：SELECT FIELD('banana', 'apple', 'banana', 'orange'); 返回 2。

8. GREATEST(val1, val2, ...) 和 LEAST(val1, val2, ...)

   • 分别返回输入值中的最大值和最小值。
   • 示例：SELECT GREATEST(1, 2, 3); 返回 3。
   • 示例：SELECT LEAST(1, 2, 3); 返回 1。

这些控制流函数可以在查询中用来创建更复杂的逻辑判断，并且可以根据数据的具体情况来决定返回什么样的结果。它们经常用于 SELECT 查询、触发器和存储过程中，以便于处理复杂的数据逻辑和业务规则。