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模yrand()返回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直到长度达到lenrpad(str,len,pad)用pad从右边填充str直到长度达到lenltrim(s)去掉s左侧空格rtrim(s)去掉s右侧空格trim(s)去掉s两侧空格trim([both] s from str)去掉str两侧的strim([leading] s from str)去掉str左侧的strim([trailing] s from str)去掉str右侧的sinsert(str,index,len,instr)str从index位置开始的len个字符替换为instrreplace(str,a,b)str中的a全部替换为brepeat(s,n)返回s重复n次的结果reverse(s)反转字符串strcmp(s1,s2)比较s1,s2substring(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 salary15000andgender男;--在t_employee表中查询薪资高于10000的男员工姓名姓名包含的字符数和占用的字节数 select ename,char_length(ename)as占用字符数,length(ename)as占用字节数量fromt_employee where salary10000andgender男;--在t_employee表中查询薪资高于10000的男员工姓名和邮箱email--并把邮箱名 字符之前的字符串截取出来--MySQL中substring函数截取字符串位置从1开始 select ename,email,substring(email,1,position(inemail)-1)fromt_employee where salary10000andgender男;--trim()默认是去掉前后空白符 select trim( hello world );select concat([,trim( hello world ),]);--去掉前后的select trim(bothfromhello world);select trim(leadingfromhello world);select trim(trailingfromhello 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 usernamechaiandpassword123456;# 使用这种方式是查不到的select*fromt_user where usernamechaiandpasswordmd5(123456);# 使用同样的方式加密能查到也就是说数据库中存储的也是加密之后的drop tableifexists t_user;常用系统信息函数函数说明database()当前数据库名version()当前数据库版本user()当前登录用户名条件判断函数示例use atguigu;--条件判断函数不是筛选记录的函数--而是根据条件不同显示不同的结果的函数--如果薪资大于20000显示高薪否则显示正常 select ename,salary,if(salary20000,高薪,正常)fromt_employee;--计算实发工资。实发工资薪资薪资*奖金比例 select ename,salary,commission_pct,salarysalary*commission_pctas实发工资fromt_employee;--如果commission_pct是计算完结果是NULL select ename,salary,commission_pct,salarysalary*ifnull(commission_pct,0)as实发工资fromt_employee;--查询员工编号姓名薪资等级等级根据薪资判断--如果薪资大于20000显示 羡慕级别--如果薪资15000-20000显示 努力级别--如果薪资10000-15000显示 平均级别--如果薪资10000以下显示 保底级别 select eid,ename,salary,casewhen salary20000then羡慕级别when salary15000then努力级别when salary10000then平均级别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(salarysalary*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,4rank()并列排序每行按照不同的分组进行编号同一个分组中排序字段值出现重复值时并列排序并跳过重复序号例如1,1,3dense_rank()并列稠密排序每行按照不同的分组进行编号同一个分组中排序字段值出现重复值时并列排序不跳过重复序号例如1,1,2lag()/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)asrow_num,rank()over(partition by did order by salary)asrank_num,dense_rank()over(partition by did order by salary)asds_rank_numfromt_employee where gender女;row_number是当前窗口即使同样也是按照一二三四进行排序rank()则是相同数字相同排名然后跳过dense_rank 则是相同排名相同序号但是不跳过上面这个代码中重复度很高(partition by did order by salary)所以想到使用一个变量来代替结果和上面一样select ename,did,salary,row_number()over wasrow_num,rank()over wasrank_num,dense_rank()over wasds_rank_numfromt_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自行创建。系统预定义函数又分为两类单行函数对每行记录分别处理输入 n 行输出仍然是 n 行。包括数学函数、字符串函数、日期函数、条件判断函数、窗口函数等。分组函数聚合函数对多行记录进行聚合运算通常和GROUP BY配合使用输入多行输出一行或几行。常见的有AVG()、SUM()、MAX()、MIN()、COUNT()。二、单行函数1. 常用数学函数函数说明示例ABS(x)绝对值ABS(-5)→ 5CEIL(x)向上取整CEIL(3.1)→ 4FLOOR(x)向下取整FLOOR(3.9)→ 3ROUND(x, d)四舍五入保留 d 位小数ROUND(3.14159, 2)→ 3.14TRUNCATE(x, d)直接截断不四舍五入TRUNCATE(3.99, 1)→ 3.9MOD(x,y)取模求余MOD(10,3)→ 1RAND()返回 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(你好)→ 2LENGTH(s)返回字节数与字符集有关LENGTH(你好)在 utf8mb4 中 → 6SUBSTRING(str, pos, len)从 pos 位置开始截取 len 个字符SUBSTRING(abcde, 2, 3)→ ‘bcd’SUBSTRING_INDEX(str, delim, count)按分隔符截取正数从左负数从右SUBSTRING_INDEX(abc, , -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(列)会忽略 NULLSUM()求和忽略 NULLAVG()求平均值忽略 NULLMAX()求最大值-MIN()求最小值-重要分组函数一般要和GROUP BY一起使用。四、窗口函数MySQL 8.0窗口函数也属于单行函数特点是不减少行数可以在显示明细的同时进行分组统计。窗口函数说明ROW_NUMBER()连续排序1,2,3,4…RANK()并列排序并跳过1,1,3DENSE_RANK()并列排序不跳过1,1,2LAG(expr, n)返回当前行前 n 行的值LEAD(expr, n)返回当前行后 n 行的值FIRST_VALUE(expr)返回窗口内第一个值LAST_VALUE(expr)返回窗口内最后一个值AVG()/SUM()/COUNT()可配合窗口使用语法结构函数名()OVER([PARTITIONBY分组字段][ORDERBY排序字段][ROWS/RANGEBETWEEN...AND...])五、高频易错点总结重点以下是学习本章最容易出错的地方单行函数 vs 分组函数混淆单行函数每行都处理结果行数不变。分组函数多行聚合结果行数通常减少。ONLY_FULL_GROUP_BY模式报错当SELECT中同时出现非聚合列和聚合函数时必须使用GROUP BY或窗口函数否则报 1140 错误。COUNT()的用法误区COUNT(*)和COUNT(1)统计实际行数。COUNT(列名)会忽略 NULL 值。字符串函数中CHAR_LENGTH()与LENGTH()混淆CHAR_LENGTH()字符数。LENGTH()字节数utf8mb4 中一个汉字占 3 或 4 个字节。窗口函数忘记写OVER()ROW_NUMBER()必须写成ROW_NUMBER() OVER(...)单独使用会报错。ROUND()与TRUNCATE()区别不清ROUND()是四舍五入。TRUNCATE()是直接截断不四舍五入。加密函数安全意识不足MD5、SHA已不安全实际项目中应使用SHA2() Salt盐值盐值就是一段随机字符串和密码拼接后再加密目的是让相同的密码产生不同的哈希结果从而大幅提高安全性。。CASE WHEN和CASE 表达式 WHEN混淆前者用于条件判断可写、、LIKE等。后者用于等值判断。窗口函数中PARTITION BY和GROUP BY混淆GROUP BY会压缩行数。PARTITION BY不压缩行数用于在明细中增加统计信息。日期函数中格式不规范应尽量使用标准格式2021-09-02避免使用 # 等奇怪分隔符。