MySQL的函数

函数其实就是方法，就是别人封装好的东西

熟能生巧，加油！！！完整代码在最后。

一、聚合函数 - group_concat()

就是对数据进行分组然后合并

二、数学函数

函数很多，大家至少看一遍，有一个大概印象！

函数很多，不一定要全部记住，只需要记住他的用法，当你使用的时候直接过来查看即可

select abs(表达式或者字段) from 表;

 

三、字符串函数

四、日期函数

日期函数比较多，但是好多重复的，大家有一个印象就好，用的时候在拿出来看看就好

五、控制流函数-if逻辑判断函数

if逻辑判断语句

结合表的数据，判断表中的列是否满足条件

对于null的处理

case when语句

六、窗口函数

聚合函数是把每一组的数据都返回一个值，窗口函数既可以实现聚合函数的功能，又可以把原有的数据保留

序号函数(row_number,rank,dense_rank)

三个序号函数意义都一样，只是细节上有所区别

可以实现分组排序，并添加序号

仔细观察row_number,rank,dense_rank序号的区别

row_number按照高低排序

rank会出现并列的情况，比如两个人并列第一，下一个直接就是第三

dense_rank会出现并列的情况，并列不影响排序号

partition by 省略掉就是全局排序，全局排序就是不分组

开窗聚合函数（sum avg max min）

avg，max，min和sum语法一样，都是反应的从第几行到第几行的数据，不是单单只指一行或一列

 

分布函数（cume_dist）

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

就是计算小于等于我的数量除以总共的数量

分布函数（percent_rank)

不是很常用

前后函数（lag和lead）

返回当前行的前n行（lag(expr,n))或后n行(lead(expr,n))的expr的值

头尾函数（first_value和last_value)

返回第一个（first_value(exper))或最后一个(last_value(exper))   exper的值

 

其他函数（nth_value和ntile）

 nth_value返回窗口中第n个exper的值，exper可以是表达式，也可以是列名

ntile 将分区中的有序数据分为n个等级，记录等级数

 

七、完整代码

create database mydb4;
use mydb4;
create table emp(
  emp_id int primary key auto_increment comment '编号',
  emp_name char(20) not null default '' comment '姓名',
  salary decimal(10,2) not null default 0 comment '工资',
  department char(20) not null default '' comment '部门'
  );
insert into emp(emp_name,salary,department) values('酷酷酷',5000,'财务部'),('看了看',5800,'财务部'),('多对多',6200,'财务部'),('哈哈哈',5700,'人事部'),('已解决',6700,'人事部'),('研究院',5200,'人事部'),('卡密码',7500,'销售部'),('书不不',7200,'销售部'),('阿发啊',7800,'销售部');

-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp;

-- 指定分隔符合并
select group_concat(emp_name separator ';') from emp;

-- 指定排序方式和分隔符
select department,group_concat(emp_name separator ';') from emp group by department;

select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;

select abs(-10);
select abs(10);

-- 向上取整
select ceil(1.1); -- 2
select ceil(1.0); -- 1
-- 向下取整
select floor(1.1); -- 1
select floor(1.0);  -- 1

select greatest(1,2,3);  -- 3

select least(1,2,3); -- 1

select mod(5,2); -- 1

select pow(2,3);  -- 8

select rand();

select rand() * 100;
select floor(rand() * 100);

select round(1.533);
select round(1.533,2);

use mydb2;
select category_id,round(avg(price),2) from product group by category_id;

select truncate(3.1415,3);

-- 1.获取字符串字符个数
select char_length('hello');
select char_length('你好吗');

-- length取长度，返回的单位是字节，一个汉字占三个字节
select length('hello');   -- 5
select length('你好吗');  -- 3

-- 2.字符串合并，合并的时候没有分隔符
select concat('hello','world');

-- 3. 字符串合并指定分隔符进行字符串合并
select concat_ws('-','hello','world');

-- 4.返回字符串在列表中的位置，有出现多个返回第一次出现的位置
select field('aaa','aaa','bbb','ccc'); -- 1
select field('bbb','aaa','bbb','ccc'); -- 2 

-- 5.去除字符串左边空格
select ltrim('   aaaaaaa');
-- y右边
select rtrim('aaaaaa             ');
-- 去除两边空格
select trim('    qqqqq     ');

-- 6.字符串截取
select mid('helloworld',2,3);  -- 从第二个字符开始截取长度为3

-- 7、 获取字符串a在字符串中出现的位置，出现好多次返回第一次出现的时候
select position('abc' in 'helabcloabcworld');

-- 8、字符串替换
select replace('helloaaaworld','aaa','bbb');

-- 9、字符串翻转
select reverse('hello');

-- 10、返回字符串后几个字符
select right('hello',3);  -- 返回后三个字符

-- 11、字符串比较
select strcmp('hello','world');

-- 12、字符串截取
select substr('hello',2,3);

-- 13、将小写转大写
select ucase('hello');
select upper('helllo');

-- 14、将大写转小写
select lcase('AAAA');
select lower('AAA');

use test1;
select ename,substr(hiredate,1,4) from emp;

-- 日期函数
-- 1、 获取时间戳（毫秒值）
select unix_timestamp();

-- 2、将一个日期字符串转为毫秒值,表示这一时刻到1970的毫秒值
select unix_timestamp('2021-12-21 08:08:08');

-- 3、将时间戳毫秒值转为指定格式的日期
select from_unixtime(1640045288,'%Y-%m-%d %H:%i:%s'); 
-- '%Y-%m-%d %H:%i:%s'  年月日时分秒

-- 4、获取当前的年月日
select curdate();
select current_date();

-- 5、获取当前的时分秒
select current_time();
select curtime();

-- 6、获取年月日和时分秒
select current_timestamp();

-- 7、从日期字符串中获取年月日,格式要符合标准
select date('2022-12-12 12:34:56');

-- 8、获取日期之间的差值(天级)
select datediff('2021-12-22','2021-2-22');
select datediff(current_date(),'2008-08-08');

-- 9、获取时间的差值（秒级）
select timediff('12:12:34','10:18:56');

-- 10、日期格式化
select date_format('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');

-- 11、将字符串转为日期
select str_to_date('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');

-- 12、将日期进行减法
select date_sub('2021-10-01',interval 2 day);

-- 13、将日期进行加法，可以加日，天，周各种各样的
select date_add('2021-10-01',interval 2 week);

-- 14、从日期中获取小时
select extract(hour from '2021-01-02 11:14:12');
select extract(year from '2021-01-02 11:14:12');
select extract(month from '2021-01-02 11:14:12');

-- 15、获取给定日期的最后一天
select last_day('2021-08-13');

-- 16、获取指定年份和天数的日期
select makedate('2021',53);

-- 17、根据日期获取年月日，时分秒
select year('2021-12-13 11:12:13');
select month('2021-12-13 11:12:13');
select quarter('2021-12-13 11:12:13');  -- 季度

-- 18 、根据日期获取信息
select monthname('2021-12-13 11:12:13'); -- 获取月份的英文
select dayname('2021-12-13 11:12:13');  -- 获取周几英文
select dayofmonth('2021-12-13 11:12:13'); -- 当月的第几天
select dayofweek('2021-12-13 11:12:13');  -- 周几，1是星期日，2是星期1
select dayofyear('2021-12-13 11:12:13');  -- 一年的第几天

select week('2021-12-13 11:12:13');
select week('2021-12-31 11:12:13');
select week('2021-1-1 11:12:13');
select yearweek('2021-3-01');
select now();

-- 控制流函数
-- if   !!!
select if(1>0,'正确','错误');
use mydb3;
select *,if(score >= 85,'优秀','及格') flag from score;

-- ifnull   !
select ifnull(11,0);
select ifnull(NULL,0);
use test1;
select *,ifnull(comm,0) from emp;

-- isnull
select isnull(5);
select isnull(NULL);
select isnull(null);

-- nullif
select nullif(12,12);  -- null
select nullif(12,22);  -- 12

-- case when语句
select 
  case 51
    when 1 then '你好'
    when 2 then 'hello'
    when 5 then 'yes'
    else
      'no'
  end as info;

select 
  case
    when 1<0 then '你好'
    when 2>1 then 'hello'
    when 5>1 then 'yes'
    else
      'no'
  end as info;

use mydb4;
-- 创建订单表
create table orders(
  oid int primary key, -- 订单id
  price double,  -- 订单价格
  payType INT  -- 支付类型（1：微信 2：支付宝 3：银行卡 4：其他）
  );
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);

-- 方式1
select
  *,
  case payType
    when 1 then '微信'
    when 2 then '支付宝'
    when 3 then '银行卡'
    ELSE
      '其他支付方式'
  end as payTypeStr
from orders;

-- 方式2
select
  *,
  case
    when payType=1 then '微信'
    when payType=2 then '支付宝'
    when payType=3 then '银行卡'
    ELSE
      '其他支付方式'
  end as payTypeStr
from orders;

-- 窗口函数
use mydb4;
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',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);

-- 对每个部门的员工按照薪资排序，并给出排名
select 
  dname,
  ename,
  salary,
  row_number() over(partition by dname order by salary desc) as rn1,
  rank() over(partition by dname order by salary desc) as rn2,
  dense_rank() over(partition by dname order by salary desc) as rn3
  from employee;

-- 求出每个部门薪资排在前三名的员工   - 分组求TOPN
select * from (
select 
  dname,
  ename,
  salary,
  dense_rank() over(partition by dname order by salary desc) as rn
  from employee
)t
where t.rn <= 3;

-- 对所有员工进行全局排序（不分组）
select 
  dname,
  ename,
  salary,
  dense_rank() over(order by salary desc) as rn
  from employee;

-- 开窗聚合函数
select 
  dname,ename,salary,hiredate,
  sum(salary) over(partition by dname order by hiredate) as c1
  from employee;
-- 就是从第一行开始，加到当前行，而且是按照分组的

select 
  dname,ename,salary,hiredate,
  sum(salary) over(partition by dname) as c1
  from employee;
-- 如果没有order by 排序语句，默认把分组内的所有数据进行sum操作

select 
  dname,ename,salary,hiredate,
  sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1
  from employee;
-- 就是从第一行开始，加到当前行，而且是按照分组的

select 
  dname,ename,salary,hiredate,
  sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1
  from employee;
-- 就是从向上三行，加到当前行，而且是按照分组的

select 
  dname,ename,salary,hiredate,
  sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c1
  from employee;
-- 就是从向上三行，加到当前行的向后一行，而且是按照分组的

select 
  dname,ename,salary,hiredate,
  sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1
  from employee;
-- 从当前行加到最后一行

select 
  dname,ename,salary,hiredate,
  avg(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1
  from employee;

select 
  dname,ename,salary,hiredate,
  max(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1
  from employee;
  
  select 
  dname,ename,salary,hiredate,
  min(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1
  from employee;

-- 分布函数
use mydb4;
select 
  dname,ename,salary,
  cume_dist() over(order by salary) as rn1,
  cume_dist() over(partition by dname order by salary) as rn2
  from employee;
-- 查询小于等于当前薪资的比例

use mydb4;
select 
  dname,ename,salary,
  rank() over(partition by dname order by salary) as rn1,
  percent_rank() over(partition by dname order by salary) as rn2
  from employee;
-- (rank - 1)/(总行数 -1)

use mydb4;
select 
  dname,ename,salary,hiredate,
  lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
  lag(hiredate,2) over(partition by dname order by hiredate) as time2
  from employee;
-- 就是返回前n行，有就输出，没有就输出默认值，没有默认值就输出null

use mydb4;
select 
  dname,ename,salary,hiredate,
  lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
  lead(hiredate,2) over(partition by dname order by hiredate) as time2
  from employee;

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;
  

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

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

-- 取出每一个部门的第一组员工
select * from (
  select 
  dname,ename,hiredate,salary,
  ntile(4) over(partition by dname order by hiredate) as nt
  from employee
  )t
where t.nt = 1;