Mysql(5)系统预定义函数

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

• 系统预定义函数
• • 单行函数
  • • 常用数学函数
    • 常用字符串函数
    • 加密函数
    • 常用系统信息函数
    • 条件判断函数
  • 分组函数
  • 窗口函数
  • • 一、函数分类概述
    • 二、单行函数
    • • 1. 常用数学函数
      • 2. 常用字符串函数
      • 3. 加密函数
      • 4. 系统信息函数
      • 5. 条件判断函数
    • 三、分组函数（聚合函数）
    • 四、窗口函数（MySQL 8.0+）
    • 五、高频易错点总结（重点）

---

系统预定义函数

函数：代表一个独立的可复用的功能。
MySQL中的函数必须有返回值，参数可以有可以没有。
MySQL中函数分为：
（1）系统预定义函数：MySQL数据库管理软件提供的函数，直接用就可以，任何数据库都可以用公共的函数。
单行函数：表示会对表中的每一行记录分别计算，有n行得到还是n行结果。如数学函数、字符串函数、日期时间函数、条件判断函数、窗口函数等。
分组函数：或者又称为聚合函数，多行函数，表示会对表中的多行记录一起做一个“运算”，得到一个结果。如求平均值的avg，求最大值的max，求最小值的min，求总和sum，求个数的count等。
（2）用户自定义函数：由开发人员自己定义的，通过CREATE FUNCTION语句定义，是属于某个数据库的对象。

单行函数

常用数学函数

函数	说明
abs(x)	绝对值
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	x模y
rand()	返回0~1的随机值
round(x,y)	返回参数x的四舍五入的有y位的小数的值
truncate(x,y)	返回数字x截断为y位小数的结果
format(x,y)	强制保留小数点后y位，整数部分超过三位的时候以逗号分割，并且返回的结果是文本类型
sqrt(x)	x的平方根
pow(x,y)	x的y次方

示例

use atguigu;--在t_employee表中查询员工无故旷工一天扣多少钱--分别使用ceil,floor,round,truncate函数--假设本月工作日总天数是22天--旷工一天扣的钱=salary/22select ename,salary/22,ceil(salary/22),floor(salary/22),round(salary/22,2),truncate(salary/22,2)fromt_employee;--查询公司平均薪资，并对平均薪资分别--分别使用ceil,floor,round,truncate函数 select avg(salary),ceil(avg(salary)),floor(avg(salary)),round(avg(salary),2),truncate(avg(salary),2)fromt_employee;

单行函数

多行函数avg处理了，所以只剩一行了

常用字符串函数

函数	说明
concat(s1,s2,…)	拼接字符串
concat(a,s1,s2,…)	在字符串间加上a拼接字符串
char_length(s)	s的字符数
length(s)	s的字节数，与字符集有关
locate(s,str) 或 instr(str,s)	返回s在str中的开始位置
upper(s) 或 ucase(s)	所有字母转大写
lower(s) 或 lcase(s)	所有字母转小写
left(s,n)	返回最左边的n个字符
right(s,n)	返回最右边的n个字符
lpad(str,len,pad)	用pad从左边填充str直到长度达到len
rpad(str,len,pad)	用pad从右边填充str直到长度达到len
ltrim(s)	去掉s左侧空格
rtrim(s)	去掉s右侧空格
trim(s)	去掉s两侧空格
trim([both] s from str)	去掉str两侧的s
trim([leading] s from str)	去掉str左侧的s
trim([trailing] s from str)	去掉str右侧的s
insert(str,index,len,instr)	str从index位置开始的len个字符替换为instr
replace(str,a,b)	str中的a全部替换为b
repeat(s,n)	返回s重复n次的结果
reverse(s)	反转字符串
strcmp(s1,s2)	比较s1,s2
substring(str,index,len)	str从index位置截取len个字符
substring_index(str,分隔符,count)	如果count是正数，那么从左往右数，截取第n个分隔符的左边的全部内容。例如，substring_index(“www.atguigu.com”,“.”,1)是"www"。如果count是负数，那么从右边开始数，截取第n个分隔符右边的所有内容。例如，substring_index(“www.atguigu.com”,“.”,-1)是"com"。

示例：

use atguigu;--在t_employee表中查询员工姓名ename和电话tel--并使用concat函数，concat_ws函数 select concat(ename,tel),concat_ws('-',ename,tel)fromt_employee;--在t_employee表中查询薪资高于15000的男员工姓名--并把姓名处理成 张xx 的样式--left(s,n)函数表示取字符串s最左边的n个字符--而rpad(str,len,pad)函数表示在字符串str的右边填充pad使得字符串长度达到lenselect rpad(left(ename,1),3,'x'),salaryfromt_employee where salary>15000andgender='男';--在t_employee表中查询薪资高于10000的男员工姓名，姓名包含的字符数和占用的字节数 select ename,char_length(ename)as占用字符数,length(ename)as占用字节数量fromt_employee where salary>10000andgender='男';--在t_employee表中查询薪资高于10000的男员工姓名和邮箱email--并把邮箱名 @ 字符之前的字符串截取出来--MySQL中substring函数截取字符串，位置从1开始 select ename,email,substring(email,1,position('@'inemail)-1)fromt_employee where salary>10000andgender='男';--trim()默认是去掉前后空白符 select trim(' hello world ');select concat('[',trim(' hello world '),']');--去掉前后的&select trim(both'&'from'&&&&hello world&&&&');select trim(leading'&'from'&&&&hello world&&&&');select trim(trailing'&'from'&&&&hello world&&&&');

加密函数

函数	说明
password(str)	返回字符串str的加密版本，41位长的字符串（MySQL8不再支持）
md5(str)	返回字符串str的md5值，也是一种加密方式
sha(str)	返回字符串str的sha算法加密字符串，40位十六进制值的密码字符串
sha2(str,hash_length)	返回字符串str的sha算法加密字符串，密码字符串的长度是hash_length/4。hash_length可以是224、256、384、512、0，其中0等同于256

示例

use atguigu;--当用户需要对数据进行加密时--比如做登录功能时，给用户的密码加密等 select md5('123456'),sha('123456'),sha2('123456',0);select char_length(md5('123456')),char_length(sha('123456')),char_length(sha2('123456',0));drop tableifexists t_user;create table t_user(idintprimary key auto_increment,username varchar(20),password varchar(100));insert into t_user values(null,"chai",md5("123456"));select*fromt_user where username="chai"andpassword="123456";# 使用这种方式是查不到的select*fromt_user where username="chai"andpassword=md5("123456");# 使用同样的方式加密，能查到，也就是说数据库中存储的也是加密之后的drop tableifexists t_user;

常用系统信息函数

函数	说明
database()	当前数据库名
version()	当前数据库版本
user()	当前登录用户名

条件判断函数

示例

use atguigu;--条件判断函数不是筛选记录的函数--而是根据条件不同显示不同的结果的函数--如果薪资大于20000，显示高薪，否则显示正常 select ename,salary,if(salary>20000,'高薪','正常')fromt_employee;--计算实发工资。实发工资=薪资+薪资*奖金比例 select ename,salary,commission_pct,salary+salary*commission_pctas实发工资fromt_employee;--如果commission_pct是，计算完结果是NULL select ename,salary,commission_pct,salary+salary*ifnull(commission_pct,0)as实发工资fromt_employee;--查询员工编号，姓名，薪资，等级，等级根据薪资判断--如果薪资大于20000，显示 羡慕级别--如果薪资15000-20000，显示 努力级别--如果薪资10000-15000，显示 平均级别--如果薪资10000以下，显示 保底级别 select eid,ename,salary,casewhen salary>20000then'羡慕级别'when salary>15000then'努力级别'when salary>10000then'平均级别'else'保底级别'endas"等级"fromt_employee;--在t_employee表中查询入职7年以上的员工姓名、工作地点、轮岗的工作地点数量情况--计算工作地点的数量可以转换为求work_place中逗号的数量+1--work_place中逗号的数量=work_place的总字符数-work_place去掉逗号的字符数--使用replace函数去掉work_place中逗号 select work_place,char_length(work_place)-char_length(replace(work_place,",",""))+1as工作地点数量fromt_employee;select ename,work_place,casechar_length(work_place)-char_length(replace(work_place,",",""))+1when1then'只在一个地方工作'when2then'在两个地方来回奔波'when3then'在三个地方流动'else'频繁出差'endas"工作地点数量情况"fromt_employee where datediff(curdate(),hiredate)>365*7;

分组函数

分组函数有合并计算过程。调用完分组函数后，结果的行数变少，可能得到一行，可能得到少数几行。
常用的分组函数：

函数	说明
avg(x)	平均值
sum(x)	求和
max(x)	最大值
min(x)	最小值
count(x)	计数

示例

use atguigu;--统计t_employee表的员工的数量 select count(*)fromt_employee;select count(1)fromt_employee;select count(eid)fromt_employee;select count(commission_pct)fromt_employee;/*count(*)或count(常量值)：都是统计实际的行数 count(字段/表达式)：统计时忽略NULL值*/--找出t_employee表中最高的薪资值 selectmax(salary)fromt_employee;--找出t_employee表中最低的薪资值 selectmin(salary)fromt_employee;--统计t_employee表中平均薪资值 select avg(salary)fromt_employee;--统计所有人的薪资总和 selectsum(salary)fromt_employee;selectsum(salary+salary*ifnull(commission_pct,0))fromt_employee;--找出年龄最小、最大的员工的出生日期 selectmin(birthday),max(birthday)fromt_employee;--查询最新入职的员工的入职日期 selectmax(hiredate)fromt_employee;分组函数一般和group by子句结合在一起使用，例如--查询每一个部门的平均薪资 select did,round(avg(salary),2)fromt_employee group by did;

分组函数一般和group by子句结合在一起使用，例如

--查询每一个部门的平均薪资 select did,round(avg(salary),2)fromt_employee group by did;

相当于先分组，对每一个组使用多行函数统计得到一个结果

窗口函数

窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据进行实时分析处理。窗口函数是每条记录都会分析，有几条记录执行完还是几条，因此也属于单行函数。

窗口函数	说明
row_number()	顺序排序，每行按照不同的分组逐行编号，例如：1,2,3,4
rank()	并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序并跳过重复序号，例如：1,1,3
dense_rank()	并列稠密排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序不跳过重复序号，例如：1,1,2
lag()/lead()	访问窗口中当前行前/后一定偏移量的值
first_value()/last_value()	访问窗口中第一个或最后一个值
sum()/avg()/count()/max()/min()	求和/平均值/计数/最大值/最小值

窗口函数的语法格式如下：
函数名(参数列表) over(
[partition by column]
[order by column]
[rows between and ]
)

over关键字用来指定窗口函数的窗口范围。如果over后面是空（），则表示select语句筛选的所有行是一个窗口。over后面的（）支持以下语法来设置窗口范围：
window：给窗口指定一个别名
partition by：一个窗口范围还可以分为多个区域。按照哪些字段进行分区/分组，窗口函数在不同的分组上分别处理分析
order by：按照哪些字段进行排序，窗口函数将按照排序后结果进行分析处理
rows/range between and ：在计算窗口函数时，指定哪些行/值将被包含在计算范围内，和用于定义窗口范围：
unbounded preceding：窗口从分区的第一行开始
n preceding：当前行之前的n行
current row：当前行
n following： 当前行之后的n行
unbounded following：窗口到分区的最后一行

select did,ename,salary,avg(salary)over()fromt_employee;

若是再加上一个分组

select did,ename,salary,avg(salary)over(partition by did)fromt_employee;

分组之后若是再加上一个按照薪资来进行排序则

select did,ename,salary,avg(salary)over(partition by did order by salary)fromt_employee;

但是加上这个order之后，这个统计的时候,最后一列会出现问题（统计的只是当前行的前n行,若是不想如此，也就需要圈定统计范围，没加这个order之前默认row就是下面这个between unbounded preceding and unbounded following 但是当加上这个order之后默认就改变了）

select did,ename,salary,avg(salary)over(partition by did order by salary rows between unbounded precedingandunbounded following)fromt_employee;

示例
GROUP BY 是“分组并压缩数据”
PARTITION BY 是“分组但不减少数据行”
–计算每一个部门的平均薪资与全公司的平均薪资的差值

在t_employee表中查询女员工姓名，部门编号，薪资
– 查询结果按照部门编号分组后在按薪资升序排列
– 并分别使用row_number()、rank()、dense_rank()三个序号函数给每一行记录编序号,

select ename,did,salary,gender,row_number()over(partition by did order by salary)as"row_num",rank()over(partition by did order by salary)as"rank_num",dense_rank()over(partition by did order by salary)as"ds_rank_num"fromt_employee where gender='女';

row_number是当前窗口，即使同样也是按照一二三四进行排序
rank()则是相同数字相同排名，然后跳过
dense_rank 则是相同排名相同序号，但是不跳过

上面这个代码中重复度很高，(partition by did order by salary)，所以想到使用一个变量来代替，结果和上面一样

select ename,did,salary,row_number()over was"row_num",rank()over was"rank_num",dense_rank()over was"ds_rank_num"fromt_employee where gender='女'window was(partition by did order by salary);

select ename,salary,lag(ename,1,'-')over(order by salary)as'上一位姓名',#LAG(expr[,N[,default]]) 当前行的上一行列出来，若是没有使用-代替lag(salary,1,0)over(order by salary)as'上一位薪资',lead(ename)over(order by salary)as'下一位姓名',#LEAD(expr[,N[,default]])当前行的上一行列出来，lead(salary)over(order by salary)as'下一位薪资',first_value(salary)over(order by salary rows between unbounded precedingandunbounded following)as'首位薪资',#当前窗口的第一个薪资last_value(ename)over(order by salary rows between unbounded precedingandunbounded following)as'末位姓名'fromt_employee;

总结

---

一、函数分类概述

MySQL 中的函数分为两大类：

• 系统预定义函数（内置函数）：MySQL 官方提供的，可直接使用。
• 用户自定义函数：由开发者使用CREATE FUNCTION自行创建。

系统预定义函数又分为两类：

1. 单行函数：对每行记录分别处理，输入 n 行，输出仍然是 n 行。包括数学函数、字符串函数、日期函数、条件判断函数、窗口函数等。
2. 分组函数（聚合函数）：对多行记录进行聚合运算，通常和GROUP BY配合使用，输入多行，输出一行或几行。常见的有AVG()、SUM()、MAX()、MIN()、COUNT()。

---

二、单行函数

1. 常用数学函数

函数	说明	示例
ABS(x)	绝对值	ABS(-5)→ 5
CEIL(x)	向上取整	CEIL(3.1)→ 4
FLOOR(x)	向下取整	FLOOR(3.9)→ 3
ROUND(x, d)	四舍五入保留 d 位小数	ROUND(3.14159, 2)→ 3.14
TRUNCATE(x, d)	直接截断，不四舍五入	TRUNCATE(3.99, 1)→ 3.9
MOD(x,y)	取模（求余）	MOD(10,3)→ 1
RAND()	返回 0~1 之间的随机数	RAND()
FORMAT(x,y)	格式化数字，保留 y 位小数，返回字符串	FORMAT(123456.789, 2)→ ‘123,456.79’

2. 常用字符串函数

函数	说明	示例
CONCAT(s1,s2,...)	拼接字符串	CONCAT('a','b')→ ‘ab’
CONCAT_WS(sep,s1,s2,...)	用指定分隔符拼接	CONCAT_WS('-','a','b')→ ‘a-b’
CHAR_LENGTH(s)	返回字符个数	CHAR_LENGTH('你好')→ 2
LENGTH(s)	返回字节数（与字符集有关）	LENGTH('你好')在 utf8mb4 中 → 6
SUBSTRING(str, pos, len)	从 pos 位置开始截取 len 个字符	SUBSTRING('abcde', 2, 3)→ ‘bcd’
SUBSTRING_INDEX(str, delim, count)	按分隔符截取（正数从左，负数从右）	SUBSTRING_INDEX('a@b@c', '@', -1)→ ‘c’
LEFT(s,n)/RIGHT(s,n)	取最左/最右 n 个字符	-
LPAD/RPAD(str,len,pad)	左/右填充	LPAD('5', 5, '0')→ ‘00005’
TRIM([BOTH/LEADING/TRAILING] s FROM str)	去除首尾或指定字符	TRIM(BOTH '&' FROM '&&abc&&')→ ‘abc’
REPLACE(str,old,new)	替换字符串	REPLACE('abc','b','x')→ ‘axc’
UPPER(s)/LOWER(s)	转大写 / 转小写	-

3. 加密函数

函数	说明	备注
MD5(str)	返回 32 位 MD5 加密字符串	不安全，仅用于非敏感场景
SHA(str)	返回 40 位 SHA1 加密字符串	已不推荐
SHA2(str, hash_length)	返回 SHA2 加密字符串	推荐使用，hash_length 常用 256

注意：PASSWORD()函数在 MySQL 8.0 已废弃，不再使用。

4. 系统信息函数

• DATABASE()：当前数据库名
• VERSION()：当前 MySQL 版本
• USER()：当前登录用户

5. 条件判断函数

• IF(a, x, y)：如果 a 为真返回 x，否则返回 y。
• IFNULL(x, y)：如果 x 不为 NULL 返回 x，否则返回 y。
• CASE WHEN（搜索型 CASE）：可写复杂条件。
• CASE 表达式 WHEN（简单型 CASE）：用于等值判断。

---

三、分组函数（聚合函数）

作用：对多行数据进行聚合运算，结果行数通常会减少。

函数	说明	注意事项
COUNT()	计数	COUNT(*)、COUNT(1)统计行数；COUNT(列)会忽略 NULL
SUM()	求和	忽略 NULL
AVG()	求平均值	忽略 NULL
MAX()	求最大值	-
MIN()	求最小值	-

重要：分组函数一般要和GROUP BY一起使用。

---

四、窗口函数（MySQL 8.0+）

窗口函数也属于单行函数，特点是不减少行数，可以在显示明细的同时进行分组统计。

窗口函数	说明
ROW_NUMBER()	连续排序（1,2,3,4…）
RANK()	并列排序并跳过（1,1,3）
DENSE_RANK()	并列排序不跳过（1,1,2）
LAG(expr, n)	返回当前行前 n 行的值
LEAD(expr, n)	返回当前行后 n 行的值
FIRST_VALUE(expr)	返回窗口内第一个值
LAST_VALUE(expr)	返回窗口内最后一个值
AVG()/SUM()/COUNT()	可配合窗口使用

语法结构：

函数名()OVER([PARTITIONBY分组字段][ORDERBY排序字段][ROWS/RANGEBETWEEN...AND...])

---

五、高频易错点总结（重点）

以下是学习本章最容易出错的地方：

1. 单行函数 vs 分组函数混淆

   • 单行函数：每行都处理，结果行数不变。
   • 分组函数：多行聚合，结果行数通常减少。

2. ONLY_FULL_GROUP_BY模式报错

   • 当SELECT中同时出现非聚合列和聚合函数时，必须使用GROUP BY或窗口函数，否则报 1140 错误。

3. COUNT()的用法误区

   • COUNT(*)和COUNT(1)统计实际行数。
   • COUNT(列名)会忽略 NULL 值。

4. 字符串函数中CHAR_LENGTH()与LENGTH()混淆

   • CHAR_LENGTH()：字符数。
   • LENGTH()：字节数（utf8mb4 中一个汉字占 3 或 4 个字节）。

5. 窗口函数忘记写OVER()

   • ROW_NUMBER()必须写成ROW_NUMBER() OVER(...)，单独使用会报错。

6. ROUND()与TRUNCATE()区别不清

   • ROUND()是四舍五入。
   • TRUNCATE()是直接截断，不四舍五入。

7. 加密函数安全意识不足

   • MD5、SHA已不安全，实际项目中应使用SHA2()+ Salt（盐值）（盐值就是一段随机字符串，和密码拼接后再加密，目的是让相同的密码产生不同的哈希结果，从而大幅提高安全性。）。

8. CASE WHEN和CASE 表达式 WHEN混淆

   • 前者用于条件判断（可写>、<、LIKE等）。
   • 后者用于等值判断。

9. 窗口函数中PARTITION BY和GROUP BY混淆

   • GROUP BY会压缩行数。
   • PARTITION BY不压缩行数，用于在明细中增加统计信息。

10. 日期函数中格式不规范

    • 应尽量使用标准格式'2021-09-02'，避免使用& # @等奇怪分隔符。

---