内容有点多，专心的写，厚积薄发，你才是王者，嘿嘿

以下介绍的所有函数，均既可以在查询中使用，又可以在插入数据中使用。

dual表是一个 MySQL 内置的一个不存在的虚表，方便做测试用的。

写作环境：

MacOS、MySQL8.0

写SQL工具：Navicat15.0和电脑自带终端

写作软件：Typora

作者：西园公子：www.zwjjiaozhu.top

1、统计函数

用来测试代码例子的表t9，表内容如下，本章所有操作均是使用该表。

mysql> SELECT * FROM t9;
+-----------------+------+---------+---------+
| name            | math | chinese | english |
+-----------------+------+---------+---------+
| 关羽            | NULL |      58 |       0 |
| 宋江            |   98 |      96 |      82 |
| 我是及时雨      |   78 |      86 |      92 |
| 宋无缺          |   98 |      96 |      92 |
+-----------------+------+---------+---------+

1.1 Count （统计数量）

1）COUNT(*)：统计满足条件的行数量，*表示匹配所有。

小例子1：统计数学在 90 到 100 之间的行数量？

mysql> SELECT COUNT(*) AS "数学高于70分" FROM t9 WHERE math BETWEEN 90 AND 100;
+-------------------+
| 数学高于70分        |
+-------------------+
|                 2 |
+-------------------+

2）count(column_name)：统计满足列column_name不为NULL的数量。

小例子2：统计数学不为空的行数量？

mysql> SELECT COUNT(math) AS "数学有分数的" FROM t9;
+--------------------+
| 数学有分数的         |
+--------------------+
|                  3 |
+--------------------+

mysql> SELECT COUNT(*) AS "数学有分数的" FROM t9;
+--------------------+
| 数学有分数的         |
+--------------------+
|                  4 |
+--------------------+

1.2 Sum（求和）

SUM(column_name)：求出所有满足条件的列（column_name）对应的数值相加的总和。

小例子3：求出满足数学在 90 到 100 之间所有行的英语之和？

mysql> SELECT SUM(english) as '90以上的英语总和' FROM t9 WHERE math BETWEEN 90 AND 100;
+-------------------------+
| 90以上的英语总和          |
+-------------------------+
|                     174 |
+-------------------------+

1.3 Avg（求平均值）

AVG(column_name)：求出所有满足条件的列（column_name）对应的数值相加总和的平均值。

小例子4：求出满足数学在 90 到 100 之间所有行的英语之和的平均值？

mysql> SELECT AVG(english) as '英语平均值' FROM t9 WHERE math BETWEEN 90 AND 100;
+-----------------+
| 英语平均值        |
+-----------------+
|         87.0000 |
+-----------------+

1.4 Max/Min（求最大和最小）

MAX([distinct] column_name)：求出所有满足条件的列（column_name）对应的数值相的最大值。

MIN(column_name)：求出所有满足条件的列（column_name）对应的数值相的最小值。

mysql> SELECT MAX(chinese),MIN(chinese) FROM t9;
+--------------+--------------+
| MAX(chinese) | MIN(chinese) |
+--------------+--------------+
|           96 |           58 |
+--------------+--------------+

2、分组

超级重点，必须要掌握！

为什么重要呢？那就是大多数的操作均是在建立在

mysql> SELECT * FROM t9;
+-----------------+------+---------+---------+
| name            | math | chinese | english |
+-----------------+------+---------+---------+
| 关羽            | NULL |      58 |       0 |
| 宋江            |   98 |      96 |      82 |
| 我是及时雨      |   78 |      86 |      92 |
| 宋无缺          |   98 |      96 |      92 |
+-----------------+------+---------+---------+

1）GROUP BY column_name：按照 column_name 为一组进行分组

小例子5：按照英语分数进行分组，求出每组的英语总和、英语数量、英语的平均值？

mysql> SELECT SUM(english), COUNT(english), AVG(english) FROM t9 GROUP BY english;
+--------------+----------------+--------------+
| SUM(english) | COUNT(english) | AVG(english) |
+--------------+----------------+--------------+
|            0 |              1 |       0.0000 |
|           82 |              1 |      82.0000 |
|          184 |              2 |      92.0000 |
+--------------+----------------+--------------+

2）HAVING ：指定分组的过滤条件，通常与GROUP BY配合使用。

小例子6：按照英语分数进行分组，且英语总和大于100分，求出每组的英语总和、数学总和？

mysql> SELECT SUM(english) AS sum_e, SUM(math) AS sum_m FROM t9 GROUP BY english HAVING sum_e>100;
+-------+-------+
| sum_e | sum_m |
+-------+-------+
|   184 |   176 |
+-------+-------+

3、字符串函数

3.1 Charset（字符集）

charset(column_name)：返回列字符段对应的字符集。

mysql> SELECT CHARSET(`name`) FROM t9;
+-----------------+
| CHARSET(`name`) |
+-----------------+
| utf8mb3         |
| utf8mb3         |
| utf8mb3         |
| utf8mb3         |
+-----------------+

3.2 Concat（拼接字符串）

concat(column_name, str, ……)：拼接字符串，可以拼接列字段。

小例子7：对表中的每个字段都拼接出形如 “数学：98，英语：82” 并输出？

mysql> SELECT CONCAT('数学：', math, '，英语：',  english) FROM t9;
+-----------------------------------------------------+
| CONCAT('数学：', math, '，英语：',  english)          |
+-----------------------------------------------------+
| NULL                                                |
| 数学：98，英语：82                                    |
| 数学：78，英语：92                                    |
| 数学：98，英语：92                                    |
+-----------------------------------------------------+

mysql> SELECT CONCAT('love', 'MySQL') FROM DUAL;  # loveMySQL

1.3 Length（计算字节数）

length(str)：返回字符串 str 的字节数。

# length
SELECT LENGTH('章') FROM DUAL;  # 3
SELECT LENGTH('zz') FROM DUAL;  # 2
SELECT LENGTH(1) FORM DUAL;  # ❌ 只可以是字符串

注：汉字一般是 3 个字节，一个字母是 1 个字节。

3.4 Replace（替换字符串函数）

replace(str, old, new)：将字符串 str 中的 old 字符串替换成 new 字符串。

mysql> SELECT REPLACE('喜欢编程吗', '编程', 'MySQL') AS `replace` FROM DUAL;
+----------------+
| replace        |
+----------------+
| 喜欢MySQL吗     |    
+----------------+

3.5 Substring（切片取子串）

substring(str, position, len)：返回字符串 str 的从位置$[positon, position+len]$的子串。

SELECT SUBSTRING('Love MySQL', 1, 4) FROM DUAL; # LoveSELECT SUBSTRING('Love MySQL', 6) FROM DUAL;  # MySQL

注：如果不设置 len 的值则默认是到最后。

3.6 Left|right（从首尾取子串）

left(str, len)：从最左边开始取 len 长度的字符串并返回。right(str, len)：从最右边开始取 len 长度的字符串并返回。

SELECT LEFT('Love MySQL', 4) FROM DUAL;   # Love
SELECT RIGHT('Love MySQL', 5) FROM DUAL;  # MySQL

3.7 Lcase|ucase（字符串大小写）

lcase(str)：转成小写，ucase(str)：转成大写。

# lcase、ucase
mysql> SELECT LCASE('LOVE'), UCASE('love') FROM DUAL;
+---------------+---------------+
| LCASE('LOVE') | UCASE('love') |
+---------------+---------------+
| love          | LOVE          |
+---------------+---------------+

3.8 Ltrim|rtrim|trim（去除首尾空格）

ltrim(str)：去除 str 最左边的空格，rtrim(str)：去除 str 最右边的空格，trim(str)：去除 str 首尾两端的空格。

SELECT LTRIM(' LOVE '), RTRIM(' LOVE '), TRIM(' LOVE ') FROM DUAL;
#  `LOVE `, ` LOVE`, `LOVE`
mysql> SELECT LENGTH(LTRIM(' LOVE ')), LENGTH(RTRIM(' LOVE ')), LENGTH(TRIM(' LOVE ')) FROM DUAL;
+-------------------------+-------------------------+------------------------+
| LENGTH(LTRIM(' LOVE ')) | LENGTH(RTRIM(' LOVE ')) | LENGTH(TRIM(' LOVE ')) |
+-------------------------+-------------------------+------------------------+
|                       5 |                       5 |                      4 |
+-------------------------+-------------------------+------------------------+

3.9 Strcmp（比较两字符串的大小）

strcmp(str1, str2)：逐字符比较 str1 和 str2 的大小。

mysql> SELECT STRCMP('ab', 'bc'), STRCMP('bc', 'ab') FROM DUAL;
+--------------------+--------------------+
| STRCMP('ab', 'bc') | STRCMP('bc', 'ab') |
+--------------------+--------------------+
|                 -1 |                  1 |
+--------------------+--------------------+

注：后面 str2 的比前一个 str1 大则返回 1 ， 否则返回 -1

3.10 instr（索引子串的位置）

instr(str, substr)：返回子串 substr 在 str 中的首位置。

mysql> SELECT INSTR('loveabcd', 'ab') FROM DUAL;
+-------------------------+
| INSTR('loveabcd', 'ab') |
+-------------------------+
|                       5 |
+-------------------------+

注：索引是从 1 开始计数

4、数学函数

4.1 Abs（取绝对值）

abs(num)：返回数字类型的 num 的绝对值。

SELECT ABS(-23.884) FROM DUAL;   # 23.884
SELECT ABS(1) FROM DUAL;   # 1

4.2 进制转换

bin(num)：十进制转 2 进制，oct(num)：十进制转 8 进制，hex(num)：十进制转 16 进制。（binary、octonary、hexadecimal）

# 十进制转2进制、十进制转8进制、十进制转16进制
mysql> SELECT BIN(10), OCT(10), HEX(10) FROM DUAL;
+---------+---------+---------+
| BIN(10) | OCT(10) | HEX(10) |
+---------+---------+---------+
| 1010    | 12      | A       |
+---------+---------+---------+

conv(num, from_base, to_base)：将数字 num 从 from_base 进制转成 to_base 进制。（convert）

mysql> SELECT CONV(12, 8, 10) AS "8_to_10", CONV('A', 16, 10) AS "16_to_10",  CONV(12, 8, 16) AS "8_to_16" FROM DUAL;
+---------+----------+---------+
| 8_to_10 | 16_to_10 | 8_to_16 |
+---------+----------+---------+
| 10      | 10       | A       |
+---------+----------+---------+

4.3 上下取整

ceiling(num)：向上取整，取大于 num 的最小整，floor(num)：向下取整，取小于 num 的最大整数。

mysql> SELECT CEILING(1.4), FLOOR(-1.3) FROM DUAL;
+--------------+-------------+
| CEILING(1.4) | FLOOR(-1.3) |
+--------------+-------------+
|            2 |          -2 |
+--------------+-------------+

4.4 Format（格式化数字）

fromat(num, num_places)：将数字 num 转成保留 num_places 位小数的数（四舍五入）

mysql> SELECT FORMAT(1.3, 4), FORMAT(1.314559, 4) FROM DUAL;
+----------------+---------------------+
| FORMAT(1.3, 4) | FORMAT(1.314559, 4) |
+----------------+---------------------+
| 1.3000         | 1.3146              |
+----------------+---------------------+

4.5 Mod（取余数）

mod(num, num2)：返回 num 除以 num2 的余数。

mysql> SELECT MOD(12, 5), MOD(13, 2) FROM DUAL;
+------------+------------+
| MOD(12, 5) | MOD(13, 2) |
+------------+------------+
|          2 |          1 |
+------------+------------+

4.7 Greatest|least（求最大和最小）

greatest(value1, value2, ...)：返回里面的最大值，least(value1, value2, ...)：返回里面的最小值。

其中参数 value 可以是混合参数：

• 如果有一个参数是 NULL ，则立即返回 NULL，不做任何比较。
• 如果参数是数字和字符串混合的，则按照数字进行进行比较。

mysql> SELECT GREATEST(12, 3, 4), LEAST(6, 2.6, 4) FROM DUAL;
+--------------------+------------------+
| GREATEST(12, 3, 4) | LEAST(6, 2.6, 4) |
+--------------------+------------------+
|                 12 |              2.6 |
+--------------------+------------------+

小例子8：使用greatest方法求出，每一个同学的英语、数学、68 中的最大值，并输出？

mysql> SELECT GREATEST(math, 68, chinese) '混合最大' FROM t9;
+--------------+
| 混合最大      |
+--------------+
|         NULL |
|           98 |
|           86 |
|           98 |
+--------------+

4.8 rand（随机数）

rand(send)：若不设置 send 参数，会产生成一个 0 和 1之间的随机数，若设置 seed 则会生成一个定值

mysql> SELECT RAND() '变值', RAND(4) '定值' FROM DUAL;
+---------------------+---------------------+
| 变值                 | 定值                |
+---------------------+---------------------+
| 0.40154680088306477 | 0.15595286540310166 |
+---------------------+---------------------+

mysql> SELECT RAND() '变值', RAND(4) '定值' FROM DUAL;
+--------------------+---------------------+
| 变值                | 定值                |
+--------------------+---------------------+
| 0.8185506526553544 | 0.15595286540310166 |
+--------------------+---------------------+

总结：

内容有点多，需多多练习，熟能生巧~