开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 7 天，点击查看活动详情
MySQL的函数分为两类：
- 系统预定义的函数，可以直接调用
- 用户自定义函数，需要自己声明，然后才能使用
系统预定义的函数又可分为两大类：
- 单行函数：一行记录计算完得到的结果还是一行
- 多行函数（聚合函数，分组函数）：一行或多行记录通过单行函数计算完，结果的记录数会减少
分组函数
- AVG(x):求平均值
- SUM(x):求总和
- MAX(x):求最大值
- MIN(x):求最小值
- COUNT(x):统计记录数 注：
- count( * )是对满足条件的（如果没有where条件，就是对所有记录）记录累加数量
- count(常量值)等价于count(* )
- count(字段名/表达式)只统计非null值的记录数
# 查询全公司的平均薪资值
SELECT AVG(salary) FROM t_employee;
# 查询全公司员工的实发工资的平均值
# 实发工资 = salary *(1+奖金比例)
select avg(salary *(1 + ifnull(commission_pct,0))) from t_employee; -- 奖金比例为空则用0计算
# 查一个月公司要给员工开多少工资
select sum(salary) from t_employee; -- 不考虑奖金
select sum(salary *(1 + ifnull(commission_pct,0))) from t_employee; -- 考虑奖金
# 查询全工资最高和最低的工资
select max(salary),min(salary) from t_employee;
# 统计全公司的员工总数
select count(*) from t_employee;
# 查询所有男员工的最高薪资
select max(salary) from t_employee where gender = '男';
# 查询所有男员工的人数
select count(*) from t_employee where gender = '男';
单行函数
单行函数做了解即可。因为有很多
比如：数学函数、字符串函数、日期时间函数、加密函数、系统信息函数、条件判断函数、其他函数及窗口函数等
数学函数
- ABS(x)： 返回x的绝对值
- CEIL(x)： 返回大于x的最小整数值
- FLOOR(x)： 返回小于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次方
# 查询全公司的平均薪资，显示小数点后2位
select round(avg(salary),2) from t_employee;
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,...,Sn) | 连接S1,S2,...,Sn为一个字符串 |
| CONCAT_WS(S,S1,S2,...,Sn) | 同CONCAT(S1,S2...)函数，但每个字符串之间要加上s |
| CHAR_LENGTH(s) | 返回字符串s的字符数 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| LOCATE(str1,str)或POSITION(str1 in str)或INSTR(str,str1) | 返回子字符串str1在str中的开始位置 |
| UPPER(S)或UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s)或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(s,n) | 返回字符串s最左边的n个字符 |
| RIGHT(s,n) | 返回字符串s最右边的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([BOTH]s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM([LEADING] s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM([TRAILING] s1 FROM s) | 去掉字符串s结尾处的s1 |
| INSERT(str,index,len,instr) | 将字符串str从index位置开始len个字符的替换为字符串instr |
| REPLACE(str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
| REPEAT(str,n) | 返回str重复n次的结果 |
| REVERSE(s) | 将字符串反转 |
| STRCMP(s1,s2) | 比较字符串s1，s2 |
| SUBSTRING(s,index,len) | 返回从字符串s的index位置截取len个字符 |
| SUBSTRING_INDEX(str,分隔符,count) | 如果count是正数，从左往右数，负数则从右往左数第几个分隔符，正数保留左边，负数保留右边 |
👇下方代码为部分函数演示
select concat('hello','world','MySQL'); -- 拼接字符串
select concat_ws('-','hello','world','MySQL'); -- 从第二个字符串开始，每个字符串拼接时，之间要加上第一个字符串
select length('hello'); -- 返回字节个数，注意一个字符1个字节，一个汉字是3个字节
select char_length('中国'); -- 返回字符个数
#LOCATE(str1,str)或POSITION(str1 in str)或INSTR(str,str1)
select position('o' in 'hello'); -- 结果为5，是返回的第几个而不是下标
#返回某个字符左边/右边的几个字符
select left('hello',3),right('hello',3); -- 返回hello左边和右边各三个字符
#TRIM系列
select trim(' hello world '); -- 删除左右两边空格
select ltrim(' hello world '); -- 删除左空格
select rtrim(' hello world '); -- 删除右空格
select trim(both 'x' from 'xxxxxhelloxxxxxworldxxxx'); -- 删除左右两边的'x'这个字符
select trim(leading 'x' from 'xxxxxhelloxxxxxworldxxxx');-- 删除左边的'x'这个字符
select trim(trailing 'x' from 'xxxxxhelloxxxxxworldxxxx');-- 删除右边的'x'这个字符
#分割
select substring_index('www.baidu.com','.','1');
select substring_index('www.baidu.com','.','-2');
日期时间函数
| 函数 | 功能描述 |
|---|---|
| CURDATE()或CURRENT_DATE() | 返回当前系统日期 |
| CURTIME()或CURRENT_TIME() | 返回当前系统时间 |
| NOW()/SYSDATE()/CURRENT_TIMESTAMP()/ LOCALTIME()/LOCALTIMESTAMP() | 返回当前系统日期时间 |
| UTC_DATE()/UTC_TIME() | 返回当前UTC日期值/时间值 |
| UNIX_TIMESTAMP(date) | 返回一个UNIX时间戳 |
| YEAR(date)/MONTH(date)/DAY(date)/ HOUR(time)/MINUTE(time)/SECOND(time) | 返回具体的时间值 |
| EXTRACT(type FROM date) | 从日期中提取一部分值 |
| DAYOFMONTH(date)/DAYOFYEAR(date) | 返回一月/年中第几天 |
| WEEK(date)/WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFWEEK() | 返回周几，注意，周日是1，周一是2，…周六是7 |
| WEEKDAY(date) | 返回周几，注意，周一是0，周二是1，…周日是6 |
| DAYNAME(date) | 返回星期，MONDAY,TUESDAY,…SUNDAY |
| MONTHNAME(date) | 返回月份，January,… |
| DATEDIFF(date1,date2)/TIMEDIFF(time1,time2) | 返回date1-date2的日期间隔/返回time1-time2的时间间隔 |
| DATE_ADD(date,INTERVAL expr type)或ADDDATE/DATE_SUB/SUBDATE | 返回与给定日期相差INTERVAL时间段的日期 |
| ADDTIME(time,expr)/SUBTIME(time,expr) | 返回给定时间加上/减去expr的时间值 |
| DATE_FORMAT(datetime,fmt)/ TIME_FORMAT(time,fmt) | 按照字符串fmt格式化日期datetime值/时间time值 |
| STR_TO_DATE(str,fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
| GET_FORMAT(val_type,format_type) | 返回日期时间字符串的显示格式 |
函数中日期时间类型说明
| 参数类型 | 描述 | 参数类型 | 描述 |
|---|---|---|---|
| YEAR | 年 | YEAR_MONTH | 年月 |
| MONTH | 月 | DAY_HOUR | 日时 |
| DAY | 日 | DAY_MINUTE | 日时分 |
| HOUR | 时 | DAY_SECOND | 日时分秒 |
| MINUTE | 分 | HOUR_MINUTE | 时分 |
| SECOND | 秒 | HOUR_SECOND | 时分秒 |
| WEEK | 星期 | MINUTE_SECOND | 分秒 |
| QUARTER | 一刻 |
函数中format参数说明
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03，…） |
| %b | 缩写的月名（Jan.,Feb.,…） | %c | 数字表示月份（1,2,3…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示表示月中的天数（01,02,…） |
| %e | 数字形式表示月中的天数（1,2,3,…） | %p | AM或PM |
| %H | 两位数字表示小数，24小时制（01,02,03,…） | %h和%I | 两位数字表示小时，12小时制（01,02,03,…） |
| %k | 数字形式的小时，24小时制（1,2,3,…） | %l | 数字表示小时，12小时制（1,2,3,…） |
| %i | 两位数字表示分钟（00,01,02,…） | %S和%s | 两位数字表示秒（00,01,02,…） |
| %T | 时间，24小时制（hh:mm:ss） | %r | 时间，12小时制（hh:mm:ss）后加AM或PM |
| %W | 一周中的星期名称（Sunday,…） | %a | 一周中的星期缩写（Sun.,Mon.,Tues.,…） |
| %w | 以数字表示周中的天数（0=Sunday,1=Monday,…） | %j | 以3位数字表示年中的天数（001,002,…） |
| %U | 以数字表示的的第几周（1,2,3,…） 其中Sunday为周中的第一天 | %u | 以数字表示年中的年份（1,2,3,…） 其中Monday为周中第一天 |
| %V | 一年中第几周（01~53），周日为每周的第一天，和%X同时使用 | %X | 4位数形式表示该周的年份，周日为每周第一天，和%V同时使用 |
| %v | 一年中第几周（01~53），周一为每周的第一天，和%x同时使用 | %x | 4位数形式表示该周的年份，周一为每周第一天，和%v同时使用 |
| %% | 表示% |
GET_FORMAT函数中val_type 和format_type参数说明
| 值类型 | 格式化类型 | 显示格式字符串 |
|---|---|---|
| DATE | EUR | %d.%m.%Y |
| DATE | INTERVAL | %Y%m%d |
| DATE | ISO | %Y-%m-%d |
| DATE | JIS | %Y-%m-%d |
| DATE | USA | %m.%d.%Y |
| TIME | EUR | %H.%i.%s |
| TIME | INTERVAL | %H%i%s |
| TIME | ISO | %H:%i:%s |
| TIME | JIS | %H:%i:%s |
| TIME | USA | %h:%i:%s %p |
| DATETIME | EUR | %Y-%m-%d %H.%i.%s |
| DATETIME | INTERVAL | %Y%m%d %H%i%s |
| DATETIME | ISO | %Y-%m-%d %H:%i:%s |
| DATETIME | JIS | %Y-%m-%d %H:%i:%s |
| DATETIME | USA | %Y-%m-%d %H.%i.%s |
#查询当前日期、时间、日期时间
select curdate(),curtime(),now();
select utc_date(),utc_time(); -- 本初子午线的日期、时间
#查询这个月过生日的员工
select ename,birthday -- 查的是员工的名字和生日
from t_employee -- 从t_employee表查
where month(birthday) = month(curdate()); -- 把所有员工的生日月份取出来看是否等于当前月份
#查询40岁以上的员工
select ename,birthday
from t_employee
where year(curdate()) - year(birthday) >= 40;
#查询员工的姓名和生日中的 年和月
select ename,extract(year_month from birthday) -- 无连接符
from t_employee;
#看今天和员工的入职日期间隔
select ename,hiredate,datediff(curdate(),hiredate)
from t_employee;
#查询入职超过5年的员工
select ename,hiredate,datediff(curdate(),hiredate)/365
from t_employee
where datediff(curdate(),hiredate) > 365*5;
select DATE_FORMAT(curdate(),'%y-%c-%e');
加密函数
往数据库里输入密码时，往往需要加密
| 函数 | 用法 |
|---|---|
| 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。 |
insert into t_user values('chai',md5('123456'));就是把密码123456用md5加密，后续直接看表的时候就会看到返回的MD5值
#查用户chai，密码是123456
select * from t_user where username = 'chai' and password=md5('123456'); -- 因为用md5加密的
系统信息函数
| 函数 | 用法 |
|---|---|
| database() | 返回当前数据库名 |
| version() | 返回当前数据库版本 |
| user() | 返回当前登录用户名 |
select version();
select user();
select database();
条件判断函数
| 函数 | 功能 |
|---|---|
| IF(value,t,f) | 如果value是真，返回t,否则返回f |
| IFNULL(value1,value2) | 如果value1不为空，返回value1,否则返回value2 |
| CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … ELSE resultn END | 依次判断条件，哪个条件满足了，就返回对应的result,所有条件都不满足就返回ELSE的result。如果没有单独的ELSE子句，当所有WHEN后面的条件都不满足时则返回NULL值结果。等价于Java中if...else if.... |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 … ELSE 值n END | 判断表达式expr与哪个常量值匹配，找到匹配的就返回对应值，都不匹配就返回ELSE的值。如果没有单独的ELSE子句，当所有WHEN后面的常量值都不匹配时则返回NULL值结果。等价于Java中switch....case |
#查询员工，如果薪资高于15K，显示高薪，否则显示普通
select ename,salary,if(salary > 15000,'高薪','普通')
from t_employee;
#查询奖金比例为NULL的员工的数量
select count(*) from t_employee where commission_pct is null;
select sum(if(commission_pct is null , 1 ,0)) from t_employee;
#显示员工的姓名和奖金比例，如果奖金比例为NULL显示为0
select ename ,ifnull(commission_pct,0) from t_employee;
#查询员工编号，姓名，薪资，等级，等级根据薪资判断，
#如果薪资大于20000，显示“羡慕级别”，
#如果薪资15000-20000，显示“努力级别”，
#如果薪资10000-15000，显示“平均级别”
#如果薪资10000以下，显示“保底级别”
select eid,ename,salary,
case
when salary > 20000 then '羡慕级别'
when salary > 15000 then '努力级别'
when salary > 10000 then '普通级别'
else '保底级别'
end as "等级"
from t_employee;
#在“t_employee”表中查询入职7年以上的
#员工姓名、工作地点、轮岗的工作地点数量情况。
/*
计算工作地点的数量，转换为求 work_place中逗号的数量+1。
work_place中逗号的数量 = work_place的总字符数 - work_place去掉,的字符数
work_place去掉, ，使用replace函数
*/
select ename,work_place,
case (char_length(work_place) - char_length(replace(work_place,',',' ')) + 1)
WHEN 1 THEN '只在一个地方工作'
WHEN 2 THEN '在两个地方来回奔波'
WHEN 3 THEN '在三个地方流动'
ELSE '频繁出差'
END AS "工作地点数量情况"
from t_employee;
窗口函数
窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据进行实时分析处理。窗口函数是每条记录都会分析，有几条记录执行完还是几条，因此也属于单行函数。
| 函数分类 | 函数 | 功能描述 |
|---|---|---|
| 序号函数 | ROW_NUMBER() | 顺序排序，每行按照不同的分组逐行编号，例如：1,2,3,4 |
| RANK() | 并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序并跳过重复序号，例如：1,1,3 | |
| DENSE_RANK() | 并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序不跳过重复序号，例如：1,1,2 | |
| 分布函数 | PERCENT_RANK() | 排名百分比，每行按照公式（rank-1）/ （rows-1）进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数 |
| CUME_DIST() | 累积分布值，表示每行按照当前分组内小于等于当前rank值的行数 / 分组内总行数 | |
| 前后函数 | LAG（expr，n） | 返回位于当前行的前n行的expr值 |
| LEAD（expr，n） | 返回位于当前行的后n行的expr值 | |
| 首尾函数 | FIRST_VALUE（expr） | 返回当前分组第一行的expr值 |
| LAST_VALUE（expr） | 返回当前分组每一个rank最后一行的expr值 | |
| 其他函数 | NTH_VALUE（expr，n） | 返回当前分组第n行的expr值 |
| NTILE（n） | 用于将分区中的有序数据分为n个等级，记录等级数 |
窗口函数的语法格式如下
函数名([参数列表]) OVER ()
函数名([参数列表]) OVER (子句)
over关键字用来指定窗口函数的窗口范围。如果OVER后面是空（），则表示SELECT语句筛选的所有行是一个窗口。OVER后面的（）中支持以下4种语法来设置窗口范围。
- WINDOW：给窗口指定一个别名；
- PARTITION BY子句：一个窗口范围还可以分为多个区域。按照哪些字段进行分区/分组，窗口函数在不同的分组上分别处理分析；
- ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后结果进行分析处理；
- FRAME子句：FRAME是当前分区的一个子集，FRAME子句用来定义子集的规则。
#查询员工表，给每一条记录编号
select row_number() over (),eid,ename
from t_employee;
#查询员工表，按照部门分组，按照薪资排序。给记录编号
select row_number() over (partition by did order by salary) as r1,
rank() over (partition by did order by salary) as r2,
dense_rank() over (partition by did order by salary) r3,
did,salary,eid,ename
from t_employee;
#查询每个部门薪资最低的员工
select * from
(select dense_rank() over (partition by did order by salary) r3, -- 把括号里的查询内容视为一张新表
did,salary,eid,ename
from t_employee) temp
where r3 = 1;
#查询每一个员工他前面一个员工的薪资
select ename,salary,lag(salary,1) over ()
from t_employee;
