函数是指一段可以直接被另一段程序调用的程序或代码。
活动地址：CSDN21天学习挑战赛
一、数学函数
select 函数 (参数)；
select abs(-9); -- 绝对值
select ceil(2.3); -- 向上取整
select floor(2.5); -- 向下取整
select mod(3,6); -- 取模
select rand(); -- 0-1随机数
select round(2.4125); -- 四舍五入
select round(2.4125,3); -- 精度为y，四舍五入
select truncate(2.345,1); -- 精度为y,x不会四舍五入
select greatest(1,2,3,4,5,6); -- 取列表最大值
select least(1,2,3,4); -- 取列表最小值
select max(); -- 字段最大值
select min(); -- 字段最小值
select pow(2,3); -- x的y次方二、字符串函数
-- 字符串合并
select concat('hello','world');
-- 指定合并分隔符
select concat_ws('-','hello','world','mysql');
-- 小写变大写
select ucase('aaa');
select upper('aGa');
-- 大写变小写
select lcase('AAA');
select lower('AAA');
-- 去除字符串两端空格
select trim(' a ');
select ltrim(' a');
select rtrim('a ');
-- 字符串截取
select substr('abcdfg',2,3); -- 第2个位置截取3个字符
select substring('abcdfg',2,3); -- 从第2个位置截取3个字符
select mid('abcdefgh',2,3); -- 从第2个位置截取3个字符
-- 填充
select lpad('01',5,'-');
select rpad('01',5,'-');
-- 长度
select char_length('qwe'); -- 2
select char_length('你好'); -- 2
-- 返回单位是字节3
select length('qq'); -- 2
select length('你好'); -- 6
-- 返回字符串第一次在列表出现的位置
select field('cd','ab','cd','cd','cd');
-- 获取子串出现位置
select position('ad' in 'ededad')
-- 字符串替换
select replace('helloello','llo','eed');
-- 字符串翻转
select reverse('abcd');
-- 返回字符串后几个字符
select right('abcdefg',2);
-- 字符串比较
select strcmp('hello','word');三、日期函数
-- 获取当前时间
select now();
-- 获取当前年月日
select curdate();
select current_date();
-- 获取当前时分秒
select current_time();
select curtime();
-- 获取年月日时分秒
select current_timestamp();
-- 获取日期差值
select DATEDIFF('2022-12-12','2022-12-21');
-- 获取时间差值
select TIMEDIFF('12:12:12','03:12:12');
-- 日期进行加法
select date_add('2022-12-12',interval 2 day);
select date_add('2022-12-12',interval 2 month);
-- 日期进行减法
select date_sub('2022-12-12',interval 2 day);
select date_sub('2022-12-12',interval 2 year);
-- 当前时间戳
select unix_timestamp();
-- 指定时间戳
select unix_timestamp('2022-12-12');
-- 根据时间戳转化为指定格式日期
select from_unixtime(1670774400,'%y-%m-%d %h:%i:%s');
-- 从日期字符串中获取年月日
select date('2022-12-21 12:12:12');
-- 日期格式化
select date_format('2022-2-2 9:2:3','%y-%m-%d %h:%i:%s');
-- 字符串转化为日期
-- select str_to_date('2022-12-12 03:12:12','%y-%m-%d %H:%i:%s');
-- 日期获取小时/年份...
select extract(hour from '2022-12-21 12:12:12');
select extract(year from '2022-12-21 12:12:12');
select extract(month from '2022-12-21 12:12:12');
select year('2022-12-21 12:12:12');
select month('2022-12-21 12:12:12');
select day('2022-12-21 12:12:12');
select quarter('2022-12-21 12:12:12'); -- 季度
-- 获取给定日期的最后一天
select last_day('2022-12-12');
-- 获取指定年份和天数的日期
select makedate('2021',43);
-- 获取当年月周第几天
select DAYOFYEAR('2022-12-21');
select DAYOFWEEK('2022-12-21');
select dayofmonth('2022-12-21');
-- 获取当年第几周
select week('2022-12-21');
-- 获取当前年当前周
select yearweek('2022-02-21');
......四、流程函数
-- case匹配when,成功执行
select
case 3
when 1 then '你好'
when 2 then '不好'
when 3 then '很好'
else
'其他'
end info;
-- 直接执行when表达式,表达式成立就执行
select
case
when 1!=1 then '你好'
when 2>1 then '不好'
when 3 then '很好'
else
'其他'
end info_1;
use mydb4;
create table order_1(
oid int primary key,
price double,
paytype int -- 支付类型1.微信 2.支付宝 3.银行卡 4.其他
);
insert into order_1 values(1,1234,1),(2,14,1),(3,1234,3),(4,1234,4),(5,34,2);
select *,
case paytype
when 1 then 'wx'
when 2 then 'zfb'
when 3 then 'yhk'
else 'qt'
end type
from order_1;五、窗口函数
select 字段名，.... 函数名() over()(partition by 字段名 order by salary 排序方式) as 字段名 from 表名;
-- 数据准备
CREATE TABLE employee(
dname varchar(20),-- 部门名
eid varchar(20),
ename varchar(20),
hiredate date, -- 入职日期
salary double -- 薪资
);
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',500);
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('研发部','1086','黄忠','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);1、序号函数
为所选字段进行排序
row_number(字段)：数据不会并列
rank(字段)：数据会并列而且断层
dense_rank(字段)：数据会并列而且连续
-- 给每个部门的员工按照薪资排序，并且给出排名
select
dname,
ename,
salary,
-- 数据不会并列
row_number() over(partition by dname order by salary desc) r1,
-- 数据会并列而且断层
rank() over(partition by dname order by salary desc) r2,
-- 数据会并列而且连续
dense_rank() over(partition by dname order by salary desc) r3
from employee;
-- 求出每个部门薪资排在前3的员工 分组求topn
select *
from
(
select
dname,ename,salary,
row_number() over(partition by dname order by salary desc) r
from employee
) t
where t.r <= 3;2、聚合函数
sum()、 avg()、 max/()、 min（）
-- sum avg max min
-- 按部门分组，按照入职日期排序后累加工资
select
*,
sum(salary) over(partition by dname order by hiredate) as c1,
-- 不排序的话，默认会累加组内所有
sum(salary) over(partition by dname) as c2,
-- 控制累加范围 开始到当前
sum(salary) over(partition by dname rows between unbounded preceding and current row) c3,
-- 累加当前和前3条
sum(salary) over(partition by dname rows between 3 preceding and current row) c4,
-- 累加前3到后1
sum(salary) over(partition by dname rows between 3 preceding and
1 following) c5,
-- 累加当前行到最后
sum(salary) over(partition by dname rows between current row and unbounded following) c6
from employee;3、分布函数
cume_dist()：返回小于等于当前rank值的行数/总行数
percent_rank()：(rank-1)/(分组内行数-1)
select
dname,
ename,
salary,
hiredate,
-- 返回小于等于当前rank值的行数/总行数
cume_dist() over(order by salary) rn1,
-- 返回分组内小于等于当前薪资的比例
cume_dist() over(partition by dname order by salary) rn2,
rank() over(partition by dname order by salary desc) rn3,
-- (rank-1)/(分组内行数-1)
percent_rank() over(partition by dname order by salary desc) rn4
from employee;4、前后函数
lag(字段，n，默认值)：获取前n行字段值，没有则为默认值
lead(字段，n，默认值)：获取后n行字段值，没有则为默认值
默认值可以省略
select
dname,
ename,
salary,
hiredate,
-- 获取前1行hiredate值，没有默认2002-02-02
lag(hiredate,1,'2002-02-02') over(partition by dname order by salary) time1,
-- 默认值可以省略
lag(hiredate,3) over(partition by dname order by salary) time2,
-- 获取后1行hiredate值，没有默认2002-02-02
lead(hiredate,1,'2002-02-02') over(partition by dname order by salary) time3
from employee;5、头尾函数
first_value(字段)：返回当前第一个
last_value(字段)：返回当前最后一个
select
dname,
ename,
hiredate,
salary,
-- 返回当前第一个
first_value(salary) over(partition by dname order by hiredate) first,
-- 返回当前最后一个
last_value(salary) over(partition by dname order by hiredate) last
from employee;6、其他函数
nth_value(字段,n)：返回当前按照字段排序的第n个元素该字段值
ntile(n)：将表内数据从上至下进行分组
select
dname,
ename,
hiredate,
salary,
-- 返回当前按照薪资排序的第1个员工薪资
nth_value(salary,1) over(partition by dname order by salary) one,
-- 返回当前按照薪资排序的第3个员工薪资
nth_value(salary,3) over(partition by dname order by salary) three
from employee;
select
dname,
ename,
hiredate,
salary,
-- 分组
ntile(2) over(partition by dname order by salary) tow,
ntile(3) over(partition by dname order by salary) three
from employee;
-- 获取每个部门第一组的员工
select
*
from(
select
dname,
ename,
hiredate,
salary,
-- 分组
ntile(2) over(partition by dname order by salary) nt
from employee
) t
where t.nt = 1;
本文含有隐藏内容，请 开通VIP 后查看