一、日期函数：

二、字符串函数学习

三、窗口函数

1.序号函数

2.开窗聚合函数

2.分布函数

3.前后函数

4.头尾函数

5.其他函数

一、日期函数：

1.日期、时间获取：

```
select curdate();#当前日期；
```

select current_date(); #当前日期,不常用；

```
select curtime();#当前时间；
```

select current_time();#当前时间，不常用；

```
select now();#当前日期时间
```

select current_timestamp();#当前日期时间

select localtime();#当前日期时间；

```
select sysdate();#系统日期时间；
```

select unix_timestamp(now()); #获取指定日期的时间戳；

select from_unixtime()#获取unix时间戳的日期值；

2.年、季度、月、周、日、工作日、时、分、秒、获取

```
select year(now());#获取年；
```

```
select quarter(now());#获取季度；
```

select month(now()); #获取数值月份；

select monthname(now());#获取英文月份；

```
select day(now());  #获取天；
```

select dayofyear(now());#获取当天是当年的第几天；

select dayofmonth(now());#获取当天是当月的第几天；

select dayofweek(now());#获取当天是当周的第几天；返回数字1~7之间，返回1代表星期天，返回2代表星期一，以此类推；

select dayname(now());#获取当天是星期几；

select week(now());  #返回当天是当年的第几周；

select weekday(now());#获取当天是当周的第几天，返回数字0~6之间，返回0代表星期一，返回1代表星期二，以此类推；

select hour(now());#获取当前时间；

select minute(now());#获取当前时间分钟；

select second(now());#获取当前时间秒；

3.还可以使用下面函数实现年月日时分秒的获取：

select extract(year from now()) 年,extract(month from now()) 月,extract(day from now())  日,extract(hour from now()) 时,extract(minute from now())  分,extract(second from now())  秒;

4.计算日期与时间函数：

select to_days(now());#从0000年01月01日开始到指定日期相隔天数；

select from_days(734993);#从0000年01月01日开始多少天后的对应日期；

select datediff(now(),'2022-01-01')#两日期相隔天数

5.指定日期前后时间计算

select adddate(now(),5);#获取指定日期n天后的日期；

select subdate(now(),5);#获取指定日期n天前的日期；

select addtime(now(),5);#获取指定日期n秒后的日期；

select subtime(now(),5);#获取指定日期n秒前的日期；

扩展：

select adddate(curdate(),interval '2,3' year_month);#返回距今2年3个月后的日期；

select adddate(curdate(),interval '1' month);#返回1个月后的日期；

select adddate(curtime(),interval '1,10' hour_minute);#返回1小时10分钟后的时间；

select adddate(curtime(),interval '1' month);#返回1分钟后的时间；

6.date_format函数使用：

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

```
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')
```

```
SELECT DATE_FORMAT(NOW(),'%Y%m%d')
```

二、字符串函数学习

1.合并字符串：concat(),concat_ws();

select concat('My','SQL');  #合并字符串

select concat('My','SQL',null);#参数中如果有null值，则合并后也是null值；

select concat(year(curdate()),'-',month(curdate()),'-',day(curdate()));

select concat_ws('-',year(curdate()),month(curdate()),day(curdate())); #带分隔符的合并函数

2.比较字符串：strcmp();

select strcmp('abc','abd'),strcmp('abc','abc'),strcmp('abd','abc');

#由于abc小于abd，返回-1；abc等于abc，返回0；abd大于abc，返回1.

3.获取字符串长度：length(),char_length();

select length('zhangxueyou'),length('张学友');#英文字符占一个字节，中文字符占两个字节；length获取的是字符串占用字节的长度；

select char_length('zhangxueyou'),char_length('张学友');#char_length获取的是本身字符串个数；

4.字符串大小写转换：所有字母转化成大写：upper()与ucase(),所有字母转化成小写：lower()与lcase();

select 'mysql',upper('mysql'),ucase('mysql');

select 'MYSQL',lower('MYSQL'),lcase('MYSQL');

5.返回字符串位置函数：find_in_set(str1,str2):返回str1在str2中的位置；

select find_in_set('mysql','mysql,sql server,db2');

6.返回指定字符串位置函数：field(str,str1,str2):返回除str外第一个与str匹配的字符串的位置；

select field('zhang','ming','yan','zhang');

7.返回子字符串想匹配的开始位置：locate(),position(),instr()
(1)locate(str1,str2)
(2)position(str1 in str2)
(3)instr(str,str1)

select locate('sql','mysql'),position('sql' in 'mysql'),instr('mysql','sql');

8.返回指定位置的字符串的ELT()函数：ELT(n,str1,str2)

```
select ELT(1,'zhang','xue','yan');
```

9.从左边或者右边截取字符串left(),right()

select left('zhangmingyan',5),right('zhangmingyan',3);

10.截取指定位置和长度的字符串：substring(str,num,len),mid(str,num,len)

select substring('zhangmingyan',6,4),mid('zhangmingyan',6,4);

11.去除字符串开始处空格：ltrim()

select char_length('  zhangmingyan'),char_length(ltrim('  zhangmingyan'));

12.去除字符串结尾出空格：rtrim()

select char_length('zhangmingyan  '),char_length(rtrim('zhangmingyan  '));

13.去除字符串首位空格：trim()

select char_length('  zhangmingyan  '),char_length(trim('  zhangmingyan  '));

14.替换字符串replace(原字符串,子字符串,新字符串),insert(原字符串,替换位置,替换长度,新字符串)

select insert('zhangmingyan1197',13,4,'23');

select replace('zhangmingyan1197','1197','23');

三、窗口函数

建表语句：

create table employee
(dname    varchar(20) comment '部门名',eid      varchar(20) comment '员工id',ename    varchar(20) comment '员工姓名',hiredate date comment '入职日期',salary   double comment '薪资'
);insert into employee
values ('研发部', '1001', '刘备', '2021-11-01', 3000);
insert into employee
values ('研发部', '1002', '关羽', '2021-11-02', 5000);
insert into employee
values ('研发部', '1003', '张飞', '2021-11-03', 7000);
insert into employee
values ('研发部', '1004', '赵云', '2021-11-04', 7000);
insert into employee
values ('研发部', '1005', '马超', '2021-11-05', 4000);
insert into employee
values ('研发部', '1006', '黄忠', '2021-11-06', 4000);
insert into employee
values ('销售部', '1007', '曹操', '2021-11-01', 2000);
insert into employee
values ('销售部', '1008', '许褚', '2021-11-02', 3000);
insert into employee
values ('销售部', '1009', '典韦', '2021-11-03', 5000);
insert into employee
values ('销售部', '1010', '张辽', '2021-11-04', 6000);
insert into employee
values ('销售部', '1011', '徐晃', '2021-11-05', 9000);
insert into employee
values ('销售部', '1012', '曹洪', '2021-11-06', 6000);

窗口函数学习：

```
/**语法结构：window_function ( expr ) OVER (PARTITION BY ...ORDER BY ...frame_clause)
```
其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：
分区（PARTITION BY）
PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
排序（ORDER BY）
OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
以及窗口大小（frame_clause）。
frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

1.序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。
打序号方式不一样
row_number()|rank()|dense_rank() over (
partition by ...
order by ...
)
*/

对每个部门的员工按照薪资降序排序，并给出排名

select dname,ename,salary,# 窗口函数执行完，会多出一列# row_number：同薪资按序号排名，序号连续row_number() over (partition by dname order by salary desc) as rowNumber,# rank：同薪资序号相同，下一薪资序号隔断，序号不连续rank() over (partition by dname order by salary desc)       as ranks,# dense_rank：同薪资序号相同，序号连续dense_rank() over (partition by dname order by salary desc) as denseRank
from employee;

求出每个部门薪资排在前三名的员工（分组求TopN问题）

select *
from (select dname,ename,salary,dense_rank() over (partition by dname order by salary desc ) as denseRankfrom employee) as dr
where dr.denseRank <= 3;

对所有员工进行全局排序（不分组），不加partition by表示全局排序

select dname,ename,salary,rank() over (order by salary desc ) as dr
from employee;

2.开窗聚合函数

/**
开窗聚合函数-sum，avg，min，max，count
在窗口中每条记录动态地应用聚合函数（sum()、avg()、max()、min()、count()），
可以动态计算在指定的窗口内的各种聚合函数值。
*/

select dname,ename,hiredate,salary,# 从第一个值开始累加到当前行（包含当前行）sum(salary) over (partition by dname order by hiredate) as sum
from employee;

select dname,ename,hiredate,salary,# 没有order by，默认把分组内所有的数据进行sum操作sum(salary) over (partition by dname) as sum
from employee;

# 指定范围

select dname,ename,hiredate,salary,sum(salary) over (partition by dname order by hiredate# 从第一行到当前行# unbounded preceding：第一行# current row：当前行# 不写默认此操作rows between unbounded preceding and current row) as sum
from employee;

select dname,ename,hiredate,salary,sum(salary) over (partition by dname order by hiredate# 从当前行开始，累加向上3行(不包含当前行)到当前行rows between 3 preceding and current row) as sum
from employee;

select dname,ename,hiredate,salary,sum(salary) over (partition by dname order by hiredate# 从当前行开始，累加向上3行到向下1行rows between 3 preceding and 1 following) as sum
from employee;

select dname,ename,hiredate,salary,sum(salary) over (partition by dname order by hiredate# 从当前行开始，累加到最后1行rows between current row and unbounded following) as sum
from employee;

2.分布函数

# 分布函数-cume_dist和percent_rank
# cume_dist用途：分组内小于、等于当前rank值的行数 / 分组内总行数

查询小于等于当前薪资（salary）的比例

select dname,ename,hiredate,salary,cume_dist() over (order by salary)                    rn1,cume_dist() over (partition by dname order by salary) rn2
from employee;

/*
rn1: 没有partition,所有数据均为1组，总行数为12，
第一行：小于等于3000的行数为3，因此，3/12=0.25
第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/

-- percent_rank
/*
用途：每行按照公式(rank - 1) / (rows - 1)进行计算。
其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数
应用场景：不常用
*/

select dname,ename,hiredate,salary,rank() over (partition by dname order by salary desc)         rn,percent_rank() over (partition by dname order by salary desc) pr
from employee;

/*
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4
*/

3.前后函数

# 前后函数-lag和lead
# 返回位于当前行的前n行（lag(expr, n)）或后n行（lead(expr, n)）的expr的值
# 应用场景：查询前1名同学的成绩和当前同学成绩的差值

select dname,ename,hiredate,salary,lag(hiredate, 1, '2000-01-01') over (partition by dname order by hiredate) as last_1_time,lag(hiredate, 2) over (partition by dname order by hiredate)               as last_2_time
from employee;

/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'
第一行，往上1行为null,因此取默认值 '2000-01-01'
第二行，往上1行值为第一行值，2021-11-01
第三行，往上1行值为第二行值，2021-11-02
last_2_time: 指定了往上第2行的值，为指定默认值
第一行，往上2行为null
第二行，往上2行为null
第四行，往上2行为第二行值，2021-11-01
第七行，往上2行为第五行值，2021-11-02
*/

lead

select dname,ename,hiredate,salary,lead(hiredate, 1, '2000-01-01') over (partition by dname order by hiredate) as last_1_time,lead(hiredate, 2) over (partition by dname order by hiredate)               as last_2_time
from employee;

4.头尾函数

# 头尾函数-first_value和last_value
# 用途：截止到当前，返回第一个（first_value(expr)）或最后一个（last_value(expr)）expr的值
# 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

注意，如果不指定order by，则进行排序混乱，会出现错误的结果

select dname,ename,hiredate,salary,first_value(salary) over (partition by dname order by hiredate) as first,last_value(salary) over (partition by dname order by hiredate)  as last
from employee;

5.其他函数

# nth_value(expr, n)
# 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
# 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

查询每个部门截止目前薪资排在第二和第三的员工信息

select dname,ename,hiredate,salary,nth_value(salary, 2) over (partition by dname order by hiredate) as second,nth_value(salary, 3) over (partition by dname order by hiredate) as third
from employee;

# ntile(n)
# 用途：将分区中的有序数据分为n个等级，记录等级数
# 应用场景：将每个部门员工按照入职日期分成3组

根据入职日期将每个部门的员工分成3组

select dname,ename,hiredate,salary,ntile(3) over (partition by dname order by hiredate ) as nt
from employee;

-取出每个部门的第一组员工

select *
from (select dname,ename,hiredate,salary,ntile(3) over (partition by dname order by hiredate ) as rnfrom employee) t
where t.rn = 1;

with t as(select dname,ename,hiredate,salary,ntile(3) over (partition by dname order by hiredate ) as rnfrom employee)
select *
from t
where t.rn = 1;