文章目录
- 聚合函数
- 数学函数
- 字符串函数
- 日期函数
- 控制流函数
- 窗口函数
聚合函数
- 包括一些基本的如：count，sum，min，max，avg等
- group_concat() 函数: 实现行的合并，首先根据group by指定的列进行分组，并且用分隔符进行分隔，然后对每一组中的值进行连接，返回一个字符串结果。
-- 将所有员工名字合并为一行select group_concat(ename) from emp; -- 分隔符默认为逗号-- 将所有员工名字合并为一行，并指定分隔符select group_concat(ename separator ';') from emp; -- 指定分隔符为分号-- 将每个部门的员工名字合并为一行select a.deptno, group_concat(ename separator ';') from emp a group by a.deptno;-- 将每个部门的员工名字合并为一行，并按照工资排序select a.deptno, group_concat(ename order by a.sal separator ';') from emp a group by a.deptno;
数学函数
- 常见的有：abs：绝对值，mod：取余数，ceil：向上取整. floor：向下取整，round：四舍五入. greatest：返回列表最大值，least：返回列表最小值. max：字段最大值，min：字段最小值. pi：圆周率，rand()：0到1随机数. power：幂运算，sqrt：开平方. truncate：截断
字符串函数
- 获取字符串字符个数
select char_length('hello world');
length() 函数: 获取字符串字节长度select length('你好'); - 字符串拼接
select concat(ename, job) from emp;
指定分隔符合并字符串
select concat(ename, ':', job) from emp;select concat_ws(',', ename, job) from emp;
- 返回字符串(如aaa)在列表中第一次出现的位置
select field('aaa', 'aaa', 'bbb', 'ccc'); - 去除字符串的空格（左：ltrim，右：rtrim，两端空格：trim）
select ltrim(' aaa');select rtrim('aaa ');
- 字符串截取
select mid('hello world', 2, 5); -- 截取从2开始，长度为5的字符串 - 获取字符串在字符中出现的位置
select position('lo' in 'hello world'); - 替换字符串
select replace('hello world', 'l', '*'); - 字符串反转
select reverse('hello world'); - 返回字符串后几个字符
select right('hello world', 5); - 字符串比较
select strcmp('hello', 'world'); - 字符串截取
select substr('hello world', 2, 5); -- 截取从2开始，长度为5的字符串 - 大小写转换
select lower('HELLO');select upper('hello');
日期函数
- 获取时间戳（毫秒值）：
select unix_timestamp(); - 将日期字符串转换为毫秒值：
select unix_timestamp('2024-9-11 20:39:27'); - 将毫秒值转换为日期字符串：
select from_unixtime(1536704982, '%Y-%m-%d %H:%i:%s'); - 获取当前日期：
select curdate(); -- 或select current_date(); - 获取当前时间：
获取当前时间 - 获取当前日期时间：
select current_timestamp(); - 从日期字符串中获取年月日：
select date('2018-9-11 20:39:27'); - 获取日期之间的天数差值：
select datediff(current_date(), '2023-2-18'); - 获取日期之间的秒数差值：
select timediff(current_time(), '12:30:59'); - 日期格式化：
select date_format('2018-9-1 2:39:7', '%Y-%m-%d %H:%i:%s'); - 日期减法：
select date_sub(current_date(), interval 10 day); -- (second, minute, hour, day, week, month, quarter, year) - 日期加法：
select date_add(current_date(), interval 10 day); - 从日期中获取指定部分：
select extract(year from current_timestamp()); -- (second, minute, hour, day, week, month, quarter, year)select month(current_timestamp());
- 获取给定日期的月的最后一天：
select last_day('2018-9-1'); - 获取指定年份过了一定天数的日期：
select makedate(2024, 65); - 根据日期获取信息：
select monthname(current_timestamp()); -- 获取月份select dayname(current_timestamp()); -- 获取星期几select dayofmonth(current_timestamp()); -- 获取月份中的第几天select dayofweek(current_timestamp()); -- 获取星期几，1为周日select dayofyear(current_timestamp()); -- 获取一年中的第几天select week(current_timestamp()); -- 获取一年中的第几周
控制流函数
- if逻辑判断语句
-- 1:if函数: 判断表达式是否为真，如果为真则返回第二个参数，否则返回第三个参数select if(10 > 5, 'true', 'false'); -- trueuse test1;select *, if(a.sal > 2500, '高薪', '低薪') from emp a;-- 2:ifnull函数: 判断表达式是否为null，如果为null则返回第二个参数，否则返回第一个参数select ifnull(null, 'default'); -- defaultselect *, ifnull(a.comm, 0) from emp a; -- 显示奖金，如果为null则显示0-- 3:nullif函数: 判断两个表达式是否相等，如果相等则返回null，否则返回第一个参数select nullif(10, 5); -- 10select nullif(10, 10); -- null
- case when语句（类似于c语言的switch case）
select a.ename, casewhen a.sal > 3000 then '高薪'when a.sal > 1500 and a.sal <= 3000 then '中薪'else '低薪'end as 工资等级from emp a;
窗口函数
分区：将数据分为不同的组，每一组分别执行函数。
排序：根据指定的列进行排序。
- 序号函数：row_number()、rank()、dense_rank()
row_number()：序号不重复
rank()：序号重复不连续
dense_rank()：序号重复连续
- 按照部门分组，按照工资倒序排序
selecta.deptno,a.ename,a.sal,row_number() over(partition by a.deptno order by a.sal desc) as 工资排名1,rank() over(partition by a.deptno order by a.sal desc) as 工资排名2,dense_rank() over(partition by a.deptno order by a.sal desc) as 工资排名3from emp a;
- 获取每个部门的工资排名前2的员工（子查询）
select * from(selecta.deptno,a.ename,a.sal,dense_rank() over(partition by a.deptno order by a.sal desc) as rnfrom emp a) as bwhere b.rn <= 2;
- 对所有员工进行全局排序（不分组）
selecta.deptno,a.ename,a.sal,dense_rank() over(order by a.sal desc) as 工资排名1from emp a;
- 开窗聚合函数
如下选择：sum()、avg()、max()、min()、count()
n preceding: 往前n行. current row: 当前行. unbounded following: 最后一行. n following: 往后n行.
以sum函数为例：
selecta.deptno,a.ename,a.sal,sum(a.sal) over(partition by a.deptno order by a.hiredate) as c1, -- 获取每个部门工资的总和(默认从每个分组的第一行加到当前行)sum(a.sal) over(partition by a.deptno order by a.hiredate rows between 3 preceding and 1 following) as c2, -- 从当前行的前3行到当前行的后1行累加sum(a.sal) over(partition by a.deptno order by a.hiredate rows between current row and unbounded following) as c3 -- 从当前行到最后一行累加from emp a;
- 分布函数
percent_rank()：(rank - 1) / (总行数 - 1)
cume_dist()：获取每个部门小于等于该字段的人的比例
selecta.deptno,a.ename,a.sal,rank() over(partition by a.deptno order by a.sal desc ) as c1, -- 获取每个部门工资的排名percent_rank() over(partition by a.deptno order by a.sal) as c2, -- (rank - 1) / (总行数 - 1)cume_dist() over(partition by a.deptno order by a.sal) as c3 -- 获取每个部门小于等于该工资的人的比例from emp a;
- 前后函数
LAG(col, n) 往前第n行数据
LEAD(col, n) 往后第n行数据.
应用场景：查询前一名同学的成绩和当前同学的成绩的差值
selecta.deptno,a.ename,a.sal,a.hiredate,lag(a.hiredate, 1, '2000-01-01') over(partition by a.deptno order by a.hiredate) as time1, -- 获取每个部门前1名同学的入职时间,默认为2001-01-01lead(a.hiredate, 1) over(partition by a.deptno order by a.hiredate) as time2 -- 获取每个部门后1名同学的from emp a;
- 头尾函数
FIRST_VALUE(col) 获取分组内排序后第一行数据的值
LAST_VALUE() 获取分组内排序后最后一行的数据
selecta.deptno,a.ename,a.sal,a.hiredate,first_value(a.sal) over (partition by a.deptno order by a.hiredate) as first, -- 截止到当前，获取每个部门按照入职日期排序的工资的第一名last_value(a.sal) over (partition by a.deptno order by a.hiredate) as last -- 截止到当前，获取每个部门按照入职日期排序的工资的最后一名from emp a;
- 其他函数
NTH_VALUE(col, n) 获取分组内排序后第n行的数据
NTILE(n) 将分组内数据按顺序平分成n组
selecta.deptno,a.ename,a.sal,a.hiredate,nth_value(a.sal, 2) over (partition by a.deptno order by a.hiredate) as nth, -- 截止到当前，获取每个部门按照入职日期排序的第二个员工的工资ntile(3) over (partition by a.deptno order by a.sal) as nt -- 截止到当前，将每个部门的工资按照升序分成3组from emp a;
取出每一个部门的第一组员工(子查询)
select * from(selecta.deptno,a.ename,a.sal,a.hiredate,ntile(3) over (partition by a.deptno order by a.sal) as ntfrom emp a) as bwhere b.nt = 1;