MySQL的函数 ╰半夏微凉° 2024-05-23 22:28 1阅读 0赞 ## MySQL的函数 ## #### 概述 #### 在MySQL中,为了提高代码重用性和隐藏实现细节,MySQL提供了很多函数。函数可以理解为别人封装好的模板代码。 #### 分类 #### 在MySQL中,函数非常多,主要可以分为以下几类: * 聚合函数 * 数学函数 * 字符串函数 * 日期函数 * 控制流函数 * 窗口函数 ### 一、聚合函数 ### 在MySQL中,聚合函数主要由:count,sum,min,max,avg,这些聚合函数我们之前都学过,不再重复。这里我们学习另外一个函数:group\_concat(),该函数用户实现行的合并。 group\_concat()函数首先根据group by指定的列进行分组,并且用分隔符分隔,将同一个分组中的值连接起来,返回一个字符串结果。 格式: group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符']) 说明: (1)使用distinct可以排除重复值; (2)如果需要对结果中的值进行排序,可以使用order by子句; (3)separator是一个字符串值,默认为逗号。 操作: 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; ![img][] -- 指定分隔符合并 select department,group_concat(emp_name separator ';' ) from emp group by department; ![img][img 1] 操作: -- 指定排序方式和分隔符 select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department; ![img][img 2] ### 二、数学函数 ### ![img][img 3] 操作 ![img][img 4] ![img][img 5] ### 三、字符串函数 ### ![img][img 6] ![img][img 7] ![img][img 8] ![img][img 9] ### 四、日期函数 ### ![img][img 10] ![img][img 11] ![img][img 12] ![img][img 13] ![img][img 14] ![img][img 15] ![img][img 16] ![img][img 17] ![img][img 18] ![img][img 19] ### 五、控制流函数 ### ▶ if逻辑判断语句 ![img][img 20] ▶ case when语句 ![img][img 21] ▶ case when语句 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 when payType=1 then '微信支付' when payType=2 then '支付宝支付' when payType=3 then '银行卡支付' else '其他支付方式' end as payTypeStr from orders; -- 方式2 select * , case payType when 1 then '微信支付' when 2 then '支付宝支付' when 3 then '银行卡支付' else '其他支付方式' end as payTypeStr from orders; ### 六、窗口函数 ### MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数,与Oracle 窗口函数类似,属于MySQL的一大特点. 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数。 ![img][img 22] 分类 ![img][img 23] 另外还有开窗聚合函数: SUM,AVG,MIN,MAX 语法结构 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选项用于在当前分区内指定一个计算窗口,也就是一个与当前行相关的数据子集。 #### 序号函数 #### 序号函数有三个:ROW\_NUMBER()、RANK()、DENSE\_RANK(),可以用来实现分组排序,并添加序号。 格式 row_number()|rank()|dense_rank() over ( partition by ... order by ... ) ▶操作 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 rn from employee; ![img][img 24] ▶操作 -- 对每个部门的员工按照薪资排序,并给出排名 rank select dname, ename, salary, rank() over(partition by dname order by salary desc) as rn from employee; ![img][img 25] ▶操作 -- 对每个部门的员工按照薪资排序,并给出排名 dense-rank select dname, ename, salary, dense_rank() over(partition by dname order by salary desc) as rn from employee; ![img][img 26] ▶操作 --求出每个部门薪资排在前三名的员工- 分组求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 ![img][img 27] ▶操作 -- 对所有员工进行全局排序(不分组) -- 不加partition by表示全局排序 select dname, ename, salary, dense_rank() over( order by salary desc) as rn from employee; ![img][img 28] #### 开窗聚合函数- SUM,AVG,MIN,MAX #### 在窗口中每条记录动态地应用聚合函数(SUM()、AVG()、MAX()、MIN()、COUNT()),可以动态计算在指定的窗口内的各种聚合函数值。 操作 select dname, ename, salary, sum(salary) over(partition by dname order by hiredate) as pv1 from employee; select cookieid,createtime,pv, sum(pv) over(partition by cookieid) as pv3 from itcast_t1; -- 如果没有order by排序语句 默认把分组内的所有数据进行sum操作 概念 select dname, ename, salary, sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1 from employee; select dname, ename, salary, sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1 from employee; 操作 select dname, ename, salary, sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c1 from employee; select dname, ename, salary, sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1 from employee; #### 分布函数- CUME\_DIST和PERCENT\_RANK #### * 用途:分组内小于、等于当前rank值的行数 / 分组内总行数 * 应用场景:查询小于等于当前薪资(salary)的比例 操作 select dname, ename, salary, cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组 cume_dist() over(partition by dept order by salary) as 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 */ ![img][img 29] **介绍-PERCENT\_RANK** * 用途:每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数 * 应用场景:不常用 操作 select dname, ename, salary, rank() over(partition by dname order by salary desc ) as rn, percent_rank() over(partition by dname order by salary desc ) as rn2 from employee; /* rn2: 第一行: (1 - 1) / (6 - 1) = 0 第二行: (1 - 1) / (6 - 1) = 0 第三行: (3 - 1) / (6 - 1) = 0.4 */ ![img][img 30] #### 前后函数-LAG和LEAD #### 介绍 * 用途:返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值 * 应用场景:查询前1名同学的成绩和当前同学成绩的差值 操作 -- lag的用法 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 */ ![img][img 31] 操作 -- 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; ![img][img 32] **头尾函数-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; ![img][img 33] **其他函数-NTH\_VALUE(expr, n)、NTILE(n)** * 用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名 * 应用场景:截止到当前薪资,显示每个员工的薪资中排名第2或者第3的薪资 操作 -- 查询每个部门截止目前薪资排在第二和第三的员工信息 select dname, ename, hiredate, salary, nth_value(salary,2) over(partition by dname order by hiredate) as second_score, nth_value(salary,3) over(partition by dname order by hiredate) as third_score from employee ![img][img 34] 介绍-NTILE * 用途:将分区中的有序数据分为n个等级,记录等级数 * 应用场景:将每个部门员工按照入职日期分成3组 操作 -- 根据入职日期将每个部门的员工分成3组 select dname, ename, hiredate, salary, ntile(3) over(partition by dname order by hiredate ) as rn from employee; ![img][img 35] 操作 -- 取出每个部门的第一组员工 select * from ( SELECT dname, ename, hiredate, salary, NTILE(3) OVER(PARTITION BY dname ORDER BY hiredate ) AS rn FROM employee )t where t.rn = 1; ![img][img 36] [img]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/32b2c6cbcfa641b98597d6ead2588ea7.png [img 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/e0d74e2661084ba6bcf0e001649f743a.png [img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/37c99091dd974b09a79aff39f27d4415.png [img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/3037cdc5b1cf4d3ba848d7353fe3d85f.png [img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/b192fd4fb332454cbbf3872e50e28861.png [img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/16aeba593d34426c98be872c41985291.png [img 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/60dd390ebf8f47559a0b6721561cf3be.png [img 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/68449c0c396e4fd6a9c7d77434280792.png [img 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/036743cf3fb44f9eaca5c1b1c50fd463.png [img 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/164c4dbea83a4244ac34061eb0837a47.png [img 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/2cb2dcdb138f45d28e860849e0f0f0ab.png [img 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/5e6d303039bf404ea7e703db4a7f1a3e.png [img 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/60599953306c4cf3800de348e61e725a.png [img 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/6adb2045f40a4da3b3c454cbc1a08733.png [img 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/f0cd01ffa042450fbdbeefc44bc2fc46.png [img 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/a53136fd4d694512b1ea95ebe46d5405.png [img 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/18255ee48ecf4e5f876fdd5a9b8af229.png [img 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/4c7e6b61cf1049b08a8daf12ae974c90.png [img 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/ef3bd52df4dc4860969e80e0b0397423.png [img 19]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/dc67f91b7dca44409a02f9a6b69bf5be.png [img 20]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/0f1ff31275ce4453a5b4f0928476692f.png [img 21]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/09bc0209320d40e58cbbf1de7ea3a0a7.png [img 22]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/e2387afc37474dcf84b26d9e55cc03ca.png [img 23]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/d0cd083faa174d2b98fa675c6ee54c65.png [img 24]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/e85c1da7b16d4168822c200f700c5eea.png [img 25]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/706f41c8965c4eb0b53240af45830c90.png [img 26]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/9f57b6c248be4656bf723203c5e74ded.png [img 27]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/ee8808cc741047ec82014492eb1ec653.png [img 28]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/09221a8416aa4bfdb06c63d3881ceed2.png [img 29]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/ae35f32f09934c079545239e3b7d738c.png [img 30]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/f4a914505f9e43e8a33d86b16ffb63a4.png [img 31]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/a59fab44e3124d7a9e803f5ff912131a.png [img 32]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/164ddc78accf44fe800b39107e62634e.png [img 33]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/d35571408ad240239b9239f8f445f3e1.png [img 34]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/c06cad61d623460a9f73227120f5756f.png [img 35]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/a3560e36cd2e45cd93c58f1bfad6308a.png [img 36]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/ac46037ac04c431e9b2e33b59e56591b.png
还没有评论,来说两句吧...