MySQL从入门到高级 --- 6.函数

已于 2024-05-17 13:33:45 修改
阅读量2.2k 收藏 26
点赞数 90
分类专栏： MySQL从入门到高级 文章标签： mysql 数据库
于 2024-05-13 15:02:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhongziqia/article/details/138803192
版权

文章目录

第六章：

6.函数

作用：提高代码重用性和隐藏实现细节

分类：

  • 聚合函数

  • 数学函数

  • 字符串函数

  • 日期函数

  • 控制流函数

  • 窗口函数

6.1 聚合函数
  • group_concat()

作用：根据group by指定的列进行分组，并用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果，实现行的合并

语法：group_concat ([distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’] )

separator为一个字符串值，默认为逗号

在这里插入图片描述

6.2 数学函数
函数名功能
ABS(x)返回x的绝对值
CEIL(x)返回大于或等于x的最小整数
FLOOR(x)返回小于或等于x的最大整数
GREATEST(expr1, expr2, expr3, …)返回列表中的最大值
LEAST(expr1, expr2, expr3, …)返回列表中的最小值

在这里插入图片描述

函数名功能
MAX(expression)返回字段expression中的最大值
MIN(expression)返回字段expression中的最小值
MOD(x,y)返回x除以y后的余数
PI()返回圆周率 3.141593
POW(x,y)返回x的y次方

在这里插入图片描述

函数名功能
RAND()返回0到1的随机值
ROUND(x)返回离x最近的整数，遵循四舍五入规则
ROUND(x,y)返回指定位数的小数，遵循四舍五入规则
TRUNCATE(x,y)返回数值x保留到小数点后y位的值，不遵循四舍五入规则

在这里插入图片描述

6.3 字符串函数
函数名功能
CHAR_LENGTH(s)返回字符串s的字符数
CHARACTER_LENGTH(s)返回字符串s的字符数
CONCAT(s1,s2,s3,…)s1，s2等多个字符串合并成一个字符串
CONCAT_WS(s1,s2,s3,…)同CONCAT函数，每个字符串之间加上x,x可以是分隔符
FIELD(s,s1,s2,s3,…)返回第一个字符串s在字符串列表(s1,s2,…)中的位置

在这里插入图片描述

函数名功能
LTRIM(s)去除字符串s开始处的空格
MID(s,n,len)从字符串s的n位置开始截取长度为len的子字符串，同SUBSTRING函数
POSITION(s1 IN s)从字符串s中获取s1的开始位置
REPLACE(s,s1,s2)字符串s2替代字符串s中的字符串s1
REVERSE(s)字符串s的顺序反过来

在这里插入图片描述

函数名功能
RIGHT(s,n)返回字符串s的后n个字符
RTRIM(s)去除字符串s结尾处的空格
STRCMP(s1,s2)比较s1和s2，若相等返回0，s1>s2返回1，s1<s2返回-1
SUBSTR(s,start,len)从字符串s的start位置开始截取长度为len的子字符串
SUBTRING(s,start,len)从字符串s的start位置开始截取长度为len的子字符串

在这里插入图片描述

函数名功能
TRIM(s)去除字符串s开始和结尾处的空格
UCASE(s)字符串转换为大写
UPPER(s)字符串转换为大写
LCASE(s)字符串转换为小写
LOWER(s)字符串转换为小写

在这里插入图片描述

6.4 日期函数
函数名功能
UNIX_TIMESTAMP()返回从1970-01-01 00:00:00到当前毫秒值
UNIX_TIMESTAMP(DATE_STRING)将制定日期转为毫秒值时间戳
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])将毫秒值时间戳转为指定格式日期
CURDATE()返回当前日期
CURRENT_DATE()返回当前日期

在这里插入图片描述

函数名功能
TIMEDIFF(time1, time2)计算时间差值
DATE_FORMAT(d, f)按表达式f的要求显示日期d
STR_TO_DATE(string, format_mask)将字符串转为日期
DATE_SUB(date, INTERVAL expr type)函数从日期减去指定的时间间隔
在这里插入图片描述

在这里插入图片描述

6.4.1 日期格式
描述描述
%a缩写星期名
%b缩写月名
%c月，数值
%D带有英文前缀的月中的天
%d月的天，数值(00-31)
%e月的天，数值(0-31)
%f微秒
%H小时(00-23)
%h小时(01-12)
%I小时(01-12)
%i分钟，数值(00-59)
%j年的天(001-366)
%k小时(0-23)
%l小时(1-12)
%M月名
%m月，数值(00-12)
%pAM 或 PM
%r时间，12-小时(hh:mm:ss AM 或PM)
%S秒(00-59)
%s秒(00-59)
%T时间，24-小时(hh:mm:ss)
%U周(00-53)星期日是一周的第一天
%u周(00-53)星期一是一周的第一天
%V周(01-53)星期日是一周的第一天，与%X使用
%v周(01-53)星期一是一周的第一天，与%x使用
%W星期名
%w周的天(0=星期日, 6=星期六)
%X年，其中的星期日是周的第一天，4位，与%V使用
%x年，其中的星期一是周的第一天，4位，与%v使用
%Y年，4位
%y年，2位
6.5 控制流函数
6.5.1 if逻辑判断语句
格式含义
IF(expr, v1, v2)若表达式expr成立，返回结果v2，否则返回结果v2
IFNULL(v1, v2)若v1的值不为NULL，返回v1，否则返回v2
ISNULL(expression)判断表达式是否为NULL
NULLIF(expr1, expr2)若字符串expr1与expr2字符串相等返回NULL，否则返回expr1

在这里插入图片描述

6.5.2 case when语句

格式：

CASE expression

WHEN conditon1 THEN result1

WHEN conditon2 THEN result2

....

WHEN conditonN THEN resultN

ELSE result

END

含义：CASE表示函数开始，END表示函数结束。若condition1成立，返回result1，condition2成立，返回result2,当全部不成立返回result，而当有一个成立后，后面将不再执行

在这里插入图片描述

6.6 窗口函数

窗口函数又被称为开窗函数。

非聚合窗口函数对于聚合函数来说，聚合函数是一组数据计算后返回单个值，非聚合函数一次指挥处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

语法：

windows_function (expr) OVER(
    PARTITION BY ...    
    ORDER BY ...
    frame_clause
)

  • windows_function：窗口函数名

  • expr：参数

  • OVER：包含三个选项

  1. 分区(PARTITION BY)：用于将数据行拆分成多个分区。若省略PARTITION BY，所有数据作为一个组进行计算

  2. 排序(ORDER BY)：用于指定分区内的排序方式

  3. 窗口大小(frame_clause)：用于在当前分区内指定一个计算窗口

6.6.1 序号函数

  • ROW_NUMBER()

  • RANK()

  • DENSE_RANK()

作用：实现分组排序，并添加序号

语法：

row_number() | rank() | dense_rank() over(
    partition by ...
    order by ...
)

create table emp(
    dname varchar(20),
    ename varchar(20),
    eid varchar(20),
    sal double
);

insert into emp values('技术部', '101', 'xiaoming', '3500');
insert into emp values('技术部', '101', 'xiaowang', '4000');
insert into emp values('技术部', '101', 'xiaoli', '3600');


insert into emp values('运营部', '102', 'xiaohua', '3000');
insert into emp values('运营部', '102', 'xiaohong', '3100');
insert into emp values('运营部', '102', 'xiaolu', '3300');

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

-- 求出每个部门薪水排前两名的员工，分组求TOPN
select * from (select dname,ename,sal, dense_rank() over(partition by dname order by sal desc) as rn from emp)t where t.rn <= 2;

-- 对所有员工进行全局排序 
select dname,ename,sal,dense_rank() over(partition by dname order by sal desc) as rn from emp;
6.6.2 开窗聚合函数

SUM, AVG, MIN, MAX

  • 在窗口中每条记录动态地应用聚合函数，可动态计算在指定地窗口内的各种聚合函数值
create table emp(
    dname varchar(20),
    ename varchar(20),
    eid varchar(20),
    sal double
);

insert into emp values('技术部', '101', 'xiaoming', '3500');
insert into emp values('技术部', '101', 'xiaowang', '4000');
insert into emp values('技术部', '101', 'xiaoli', '3600');


insert into emp values('运营部', '102', 'xiaohua', '3000');
insert into emp values('运营部', '102', 'xiaohong', '3100');
insert into emp values('运营部', '102', 'xiaolu', '3300');

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

-- 求出每个部门薪水排前两名的员工，分组求TOPN
select * from (select dname,ename,sal, dense_rank() over(partition by dname order by sal desc) as rn from emp)t where t.rn <= 2;

-- 对所有员工进行全局排序 
select dname,ename,sal,dense_rank() over(partition by dname order by sal desc) as rn from emp;

-- 若没有ORDER BY排序语句， 默认把分组内所有数据进行sum操作
select dname,ename,sal,sum(sal) over(partition by dname order by sal desc) as p1 from emp;
select dname,ename,sal,sum(sal) over(partition by dname ) as p2 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between 3 preceding and current row) as p3 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between 3 preceding and 1 following) as p4 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between current row and unbounded following) as p5 from emp;
select dname,ename,sal,max(sal) over(partition by dname order by sal desc) as p5 from emp;
6.6.3 分布函数

CUME_DIST 和 PERECENT_RANK

  • CUME_DIST：用于分组内小于、等于当前rank值的行数/分组内总行数，应用于查询小于等于当前薪资(sal)比例
create table emp(
    dname varchar(20),
    ename varchar(20),
    eid varchar(20),
    sal double
);

insert into emp values('技术部', '101', 'xiaoming', '3500');
insert into emp values('技术部', '101', 'xiaowang', '4000');
insert into emp values('技术部', '101', 'xiaoli', '3600');
insert into emp values('技术部', '101', 'xiaoni', '3500');
insert into emp values('技术部', '101', 'xiaona', '3400');
insert into emp values('技术部', '101', 'xiaone', '3300');
insert into emp values('技术部', '101', 'xiaonl', '3200');

insert into emp values('运营部', '102', 'xiaohua', '3000');
insert into emp values('运营部', '102', 'xiaohong', '3100');
insert into emp values('运营部', '102', 'xiaolu', '3300');
insert into emp values('运营部', '102', 'xiaolo', '3800');
insert into emp values('运营部', '102', 'xiaola', '3700');
insert into emp values('运营部', '102', 'xiaole', '3600');
insert into emp values('运营部', '102', 'xiaolt', '3000');

select dname,ename,sal, 
    cume_dist() over(order by sal) as rn1, -- 没有partition语句 所有数位于一组
    cume_dist() over(partition by ename order by sal) as rn2 
from emp;
  • PERECENT_RANK：用于每行按照公式(rank-1) / (rows-1)进行计算，rank为rank()函数产生的序号，rows为当前窗口的记录总行数。不常用。
select dname,ename,sal,
  rank() over(partition by dname order by sal desc) as rn,
    percent_rank() over(partition by dname order by sal desc) as rn2
 from emp;
6.6.4 前后函数

LAG和LEAD

  • 用于返回位于当前行的前n行(LAG(expr, n)或后n行(LEAD(expr, n)的expr值，应用于查询前1名同学的成绩和当前同学成绩的差值
 -- LAG 
 select dname,ename,sal,
    lag(sal,1,3500) over(partition by dname order by sal ) as last_1_sal,
    lag(sal,2) over(partition by dname order by sal ) as last_2_sal
from emp;

-- LEAD 
 select dname,ename,sal,
    lead(sal,1,3500) over(partition by dname order by sal ) as last_1_sal,
    lead(sal,2) over(partition by dname order by sal ) as last_2_sal
from emp;
6.6.5 头尾函数

FIRST_VALUE和LAST_VALUE

  • 用于返回第一个(FIRST_VALUE)或最后一个(LAST_VALUE)的expr值。应用于截止到目前，按入职编号排序查询第一个入职和最后一个入职的员工薪资
select dname, ename, sal, eid, -- 若不指定order by 将会排序混乱，出现错误的结果
    first_value(sal) over(partition by dname order by eid) as first,
    last_value(sal) over(partition by dname order by eid) as last 
from emp;
6.6.6 其他函数

NTH_VALUE(expr, n)和NTILE(n)

  • 用于返回窗口中第n个expr值。应用于截止到当前薪资，显示每个员工中薪资排第2或3的薪资
-- NTH_VALUE()
-- 查询各部门截止目前薪资排第2或3的员工个人信息
select dname, ename, sal, eid, -- 若不指定order by 将会排序混乱，出现错误的结果
    nth_value(sal,2) over(partition by dname order by eid) as second_sal,
    nth_value(sal,3) over(partition by dname order by eid) as thrid_sal 
from emp;

-- NTILE()
-- 根据入职编号将各部门员工分3组
select dname, ename, sal, eid, -- 若不指定order by 将会排序混乱，出现错误的结果
    ntile(3) over(partition by dname order by eid) as rn
from emp;

-- 取出各部门的第一组员工
select 
    * 
    from (select dname, ename, sal, eid, ntile(3) 
    over(partition by dname order by eid) 
    as rn from emp)t 
where t.rn = 1;
6.7 练习
-- 查询各部门平均薪水最高的部门名
select 
 a.deptno,a.dname,a.location,avg_sal
from
    dept a,
    (
    select *
    from 
    (
    select 
        *,
        rank() over(order by avg_sal desc ) rn 
    from
        (
        select deptno,avg(sal) avg_sal from emp group by deptno
        )t1
        )t2
    where rn = 1
    )t3 
    where a.deptno = t3.deptno;


--  查询员工比所属领导薪资高的员工个人信息
create view test_view 
    as
    select 
        a.ename ename,
        a.sal esal,
        b.ename mgrname,
        b.sal mgrsal, 
        a.deptno 
    from
        emp a,
        emp b 
    where 
        a.mgr = b.deptno
        and a.sal > b.sal;

select * from dept a join test_view b on a.deptno = b.deptno;
子期cod
关注
  • 90
    点赞
  • 踩
  • 26
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
完整版 MySQL8.0从入门到精通 MySQL数据库教程 全套PPT课件资源集合 共26个章节 含全部源代码.rar
12-18
完整版 MySQL8.0从入门到精通 MySQL数据库教程 第03章 数据库的基本操作（共6页）.ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第04章 数据表的基本操作（共28页）.ppt 完整版 MySQL8.0从入门到精通 MySQL...
吐血总结《Mysql从入门到入魔》，图文并茂
热门推荐
向日葵的专属太阳
09-04 2万+
文章目录1. 查询1.1 查询多列1.2 检索唯一值1.3 限制输出1.4 注释2. ORDER BY 排序2.1 单列排序3.2 多列排序3.3 指定排序方向4. WHERE 过滤数据4.1 WHERE 子句操作符4.2 范围值检查4.3 空值检查5. 高级数据过滤5.1 WHERE 组合过滤5.2 IN 操作符5.3 NOT 操作符6. 通配符过滤6.1 % 通配符6.2 _ 通配符7. 创建计算字段7.1 计算字段7.2 拼接字段7.3 执行算数计算8. 函数8.1 常用文本处理函数8.2 日期和时间
PHP5与MySQL5从入门到精通.rar
07-15
1.1　简介 ...1.3.3　加载扩展库 ...第2章　网站开发语法基础 ...第3章　函数 3.1　用户自定义函数 ...第9章　MySQL快速入门 第10章　MySQL数据库管理 第11章　MySQL5进阶 第12章　文章发布系统 第13章　电子商务系统
mysql5.5从入门到精通.pdf
12-29
mysql入门到精通的不错文档，里面包含存储过程函数等高级应用讲解。
MySQL入门到进阶sql脚本案例资源合集.zip
08-11
这个资源合集是一个从MySQL入门到进阶的SQL脚本案例集合。它涵盖了各种难度级别的案例，帮助读者从初学者逐步提升到进阶水平。 在这个资源合集中，你可以找到许多不同类型的SQL脚本案例，包括创建数据库和表、插入...
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
07-17
│ 第二课MySQL入门介绍.pdf │ 第五课MySQL常用函数介绍.pdf │ 第八课InnoDB内核.pdf │ 第六课SQL高级应用.pdf │ 第十一课MySQL表分区8.0.pdf │ 第十七课Elasticsearch分享-张亚V4.pdf │ 第十三课MySQL5.7高...
MySQL 高级 - 第十一章 | 索引优化与查询优化
最新发布
xhmico的博客
06-07 1013
上篇：第十章、性能分析工具的使用本文内容主要源于：bilibili-尚硅谷-MySQL高级篇都有哪些纬度可以进行数据库调优？简言之：关于数据库调优的知识点非常分散，不同 ，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。虽然 查询优化的技术很多，但是大体方向上完全可以分为 和 两大块。学员表插 万条， 班级表插 万条步骤1：建表 步骤2：设置参数命令开启：允许创建函数设置 步骤3：创建函数随机产生字符串，保证每条数据都不同 随机产生班级编号 步骤4：创建存储过程创建往 表中插入数据的存储
8. MySQL 存储过程、存储函数、触发器
普罗米修斯的博客
06-04 965
SHOW CREATE 语句能查询存储过程的集体定义。基本语法SHOW CREATE PROCEDURE 存储过程名;实例使用 SHOW CREATE 查询名为 showstuscore 的存储过程的状态。查询结果显示了存储过程的定义和字符集信息等。基本语法DECLARE 关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；
chat4-Server端保存聊天消息到mysql
gulanga5的博客
06-02 838
本文档描述了Server端接收到Client的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql服务端为当前客户端创建一个线程，此线程接收当前客户端的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql本文档主要总结了将聊天消息保存到mysql!!!),;import;import;import;/**用druid连接池来连接数据库的工具类*/DBUtil。
java中mysql5和mysql8数据库连接方式
Alfred_tfk的博客
06-04 400
java中mysql5和mysql8数据库连接方式。
mysql 索引和null值的关系
ray的博客
06-03 263
设计索引时，应充分考虑NULL值对索引效率和查询优化的潜在影响。对于可能包含大量NULL值的列，需要仔细权衡是否创建索引，以平衡查询性能与存储开销。：索引选择性衡量索引中不同值的比例，高选择性代表索引效率高。含有大量NULL值的索引会降低选择性，可能影响优化器对索引使用的决策。：索引包含NULL值会占用额外空间，因为每条索引记录需标记NULL状态，轻微增加索引大小和维护开销。等）且值为NULL，索引可能不会被使用，因为NULL与其他值的比较（除非。：在含有NULL值的索引列上，使用。
MySQL SQL优化
qq_66292058的博客
06-02 1298
本篇主要介绍SQL优化的相关内容。对于每一次来自客户端的请求，我们通常都需要访问一次数据库，而访问数据库又是比较耗时的，因此我们如果能够掌握一定的SQL优化的技巧并熟练运用的话，就能大大缩减一条请求的响应时间，下面让我们来了解一下常见的SQL优化技巧。
10. MySQL 用户
普罗米修斯的博客
06-05 828
通常标准的发行版不支持 ssl，读者可以使用 SHOW VARIABLES LIKE “have_openssl” 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED，那么则不支持 ssl 加密功能。即使 password_expired 为“Y”即用户密码过期，用户也可以使用密码登录 MySQL，但是不允许做任何操作。例如，如果数据库管理员觉得某个用户不应该拥有 DELETE 权限，那么就可以删除 DELETE 权限。user 表中的安全列。
MySQL迁移达梦数据库避坑
奈川直子
06-05 310
dbms_lob.substr函数是Oracle数据库提供的一个函数，用于从LOB（Large Object）类型的列中截取子字符串。在这个查询语句中，它被用来处理RECEIVER列的值，将其转化为可比较的字符串形式，然后与给定的#{receiver}值进行比较。但是在dm8中不可以用String类型的查询条件进行查询，需要转换类型。MySQL中为blob类型，迁移到dm8同样为blob类型。在Mysql中blob可以使用String类型当作查询条件。但是使用dm8数据库执行查询列表方法时，语法冲突。
mysql之过滤分组
JustinMars的博客
06-03 561
在MySQL中，过滤分组数据通常使用GROUP BY结合HAVING子句和WHERE子句。GROUP BY子句用于将结果集按一个或多个列进行分组，而HAVING子句用于过滤分组后的数据。WHERE子句用于过滤分组前的数据。GROUP BY。
【MySQL】聊聊order by 是如何排序的
日拱一卒，不期速成！
06-02 443
【MySQL】聊聊order by 是如何排序的
Mysql疑难报错排查 - Field ‘XXX‘ doesn‘t have a default value
战斧的博客
06-06 578
java.sql.SQLException: Field 'xxxx' doesn't have a default value 报错排查与修复
【MySQL】MySQL 图形化界面 - 使用说明（MySQL Workbench）
Elena's Blog
06-03 517
Navicat，SQLyog 这些软件都不错，不过都需要收费，当然也有破解版。下面用，它是官方提供的工具。
MySQL有哪些锁？
qq_45981373的博客
06-03 585
在学校里学数据库的内容，我们会发现一个问题，就是老师不会讲到数据库的锁这一块来，但是我们面试的时候会被问到关于锁的知识点，所以我们今天开始学习关于锁的知识。首先Mysql锁有三种，分别是全局锁、表级锁和行级锁。
mysql从入门到精通李波
07-27
总之，通过《MySQL从入门到精通李波》，读者可以系统地学习MySQL数据库的基本知识和高级技巧，从而成为一名熟练的MySQL专家。无论是初学者还是有一定经验的开发人员，都可以通过这本书提升自己的数据库技能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值