MySQL数据库学习笔记（三）：单行函数与多行函数
函数
大量的MySQL数据处理函数(取自尚硅谷公开资料)。
1. 数值函数
| 函数名 | 作用 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(X) | 返回X的符号。正数返回1，负数返回-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(x)，CEILING(x) | 无论正数负数，向大于x一侧最近整数取整 |
| FLOOR(x) | 无论正数负数，向小于x一侧的最近整数取整 |
| LEAST(e1,e2,e3…) | 取最小值 |
| GREATEST(e1,e2,e3…) | 取最大值 |
| MOD(x,y) | 返回X除以Y后的余数 |
| RAND() | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 |
| ROUND(x) | 四舍五入，只保留整数 |
| ROUND(x,y) | x四舍五入，保留y位小数 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL |
2. 三角函数
| 函数名 | 作用 |
|---|---|
| RADIANS(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
三角函数以弧度作为参数：包括SIN(X)、COS(X)、TAN(X)、COT(X)、ASIN(X)、ACOS(X)、ATAN(X)，如果定义域方法，结果返回NULL。
此外还有一个特殊函数:ATAN2(m,n)，描述直角坐标(n,m)相对于原点的角度范围，结果在-π到π，例如 ATAN2(1,1) = π/4、ATAN2(1,0) = π/2、ATAN2(0,-1) = π；
当n不为0时，相对于计算arctan(m/n)，且该函数能处理n=0情况（结果±π/2或0）。
3. 基本初级函数
| 函数名 | 作用 |
|---|---|
| POW(x,y)，POWER(x,y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方，e=2.718.... |
| LN(X)，LOG(X) | e为底 |
| LOG2(X) | 2为底 |
| LOG10(X) | 10为底 |
定义域方法均为NULL。
4. 进制转换
| 函数名 | 作用 |
|---|---|
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
5. 字符串函数
CHAR_LENGTH(s)和LENGTH(s)区别：对于一般的编码，二者结果是一致的，特殊的编码，例如UTF8的中文编码，使用三字节UTF-8编码表示一个中文字符，因此CHAR_LENGTH('你好')会返回2，LENGTH('你好')会返回6，在不同的DBMS中，LENGTH的定义可能还有不同。
SQL字符串索引从1开始，而非0；
| 函数名 | 作用 |
|---|---|
| ASCII(S) | 返回字符串第一个字符的ASCII码 |
| CHAR_LENGTH(s)/CHARACTER_LENGTH(s) | 返回字符数 |
| LENGTH(str) | 返回字节数目 |
| CONCAT(s1,s2,......,sn) | 连接s1,s2,......,sn为一个字符串 |
| CONCAT_WS(x,s1,s2,......,sn) | 连接s1,s2,......,sn为一个字符串，且每个字符间使用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(substrIN str)、INSTR(str,substr)相同。未找到，返回0 |
| ELT(m,s1,s2,…,sn) | 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s) | 返回s反转后的字符串 |
| NULLIF(value1,value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |
6. 条件选择函数
| 函数名 | 作用 |
|---|---|
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| IFNULL(value1, value2) | 如果value1不为NULL，返回value1，否则返回value2 |
case多条件选择
case when... then...
[ELSE,END]语句：省略ELSE其他情况显示NULL。相当于if、else语句。
1
2
3
4
5
6SELECT last_name,salary,CASE
WHEN salary >=5000 THEN '农场主'
WHEN salary >=2500 THEN '大牛马'
WHEN salary >=1500 then '中牛马'
ELSE '小牛马' END '别名'
FROM employees;
Case+字段简化，但只能等值查询：相当于switch语句 1
2
3
4
5
6SELECT last_name,CASE department_id
WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
WHEN 30 THEN salary*1.3
ELSE salary*1.4 END '别名'
FROM employees;
7. 日期与日期解析函数
1 | SELECT CURDATE() FROM DUAL; --2024-10-23，同CURDATE() |
格式化解析略。
8. 加密函数
| 函数名 | 作用 |
|---|---|
| PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密 |
| MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL |
| SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全 |
| ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
| DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
9. 杂项函数
| 函数名 | 作用 |
|---|---|
| FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位 |
| CONV(value,from,to) | 将value的值进行不同进制之间的转换 |
| INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 |
| INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 |
| BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |
| CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code |
MySQL运维信息略
聚合函数(多行函数)
聚合函数实际上就是多行函数，与单行函数的区别是它允许接收多个记录信息，但最终函数返回值仍然只有一项，MySQL函数不支持返回多个结果。
常用聚合函数
平均值：AVG(字段)
总和：SUM(字段)
平均值/总和针对数值型数据，如果处理字符型数据会返回0；AVG只会在非NULL字段中计算平均值，一些需要总体平均值的场合并不完全适用。
最大值：MAX(字段)
最小值：MIN(字段)
最大值/最小值能处理数值、字符数据，字符按ASCII码比较。
- 计算非NULL记录个数：COUNT(字段)，如果为字段记录非NULL才计数，如果字段常量，返回的是表的所有记录数（行数）。
COUNT的效率和存储引擎相关：
MyISAM引擎：COUNT(*)、COUNT(1)、COUNT(字段)均为O(1)，因为其维护了行数计数器，能直接查询行数情况。
InnoDB引擎：COUNT(*)=COUNT(1)>COUNT(字段)，前二者只需要遍历行数，无需查看行内容，而COUNT(字段)还需要查看内容保证不为NULL，且可能使用二级索引优化查询。
注意：在Oracle中，聚合函数可以嵌套，但是MySQL8并不支持嵌套聚合函数，例如最低平均工资MIN(AVG(salary))是错误的用法，正确写法见《多表查询与子查询》中多行子查询一节。
GROUP BY语句
SQL支持将一个表的数据先进行分组，再在分组进行查询，使用GROUP BY语句的SELECT字段应该满足任意一种条件：
1. 出现在 GROUP BY 子句中
2. 被聚合函数包围（如 COUNT(), SUM(), AVG() 等）
但这个结论并不绝对，例如多表连接后，如果Group
By+外表主键，本身就具有唯一性，那么select其他字段也是允许的，例如：
1
2
3
4
5
6
7
8
9-- 选择平均工资最高的工资信息，工资来自表employees，工作信息来自表jobs
SELECT j.*,AVG(salary) -- 虽然select含job的其他列，但合法，因为Group By已经唯一
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
GROUP BY job_id -- group by只需要外键job_id
HAVING AVG(salary)>=ALL(
SELECT AVG(salary) FROM employees
GROUP BY job_id
);
其中NULL也会被单独当成某一类。 1
2
3SELECT department_id,AVG(salary) --计算每个部门平均工资
FROM employees
GROUP BY department_id;
WITH ROLLUP与ORDER BY冲突
GROUP BY department_id WITH ROLLUP关键字会将整个分组（例如全部部门id）看成是一大组，额外再输出一行数据，注意如果使用ORDER
BY对数据进行排序，有WITH
ROLLUP情况下排序会报错，因为一大组的数据不应该参与每个部门的排序。
此外，使用了分组后，参与ORDER BY排序的字段应该要么来自聚合函数，要么来自GROUD BY字段，才能进行。
HAVING语句
当过滤条件是聚合函数时，不能使用WHERE关键字，应该替代使用HAVING关键字，也因此HAVING一般和GROUP BY一起使用，因为二者常常结合处理和筛选分组数据，且HAVING需要写在GROUP BY后面。
给出部门最大工资大于10000的所有部门及其最高薪资： 1
2
3
4
5SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000
ORDER BY MAX(salary) DESC;
当需求既需要普通筛选，也需要聚合函数筛选，可以使用HAVING统一过滤，也可以使用WHERE+HAVING逐一过滤，但注意顺序：
1
2
3
4
5
6
7
8--way 1
WHERE condition1
GROUP BY ...
HAVING condition2
-- way 2
GROUP BY...
HAVING condition1 AND condition2
只有需要了解各个部门的情况，可能才需要GROUP BY和HAVING的方式，如果只是希望了解最值的部门，使用子查询等效率可能更高。