发帖那个网站好 做装修的,成都网站建设 外包,北京定制网站开发,wordpress建站安全吗文章目录 第六章#xff1a;6.函数6.1 聚合函数6.2 数学函数6.3 字符串函数6.4 日期函数6.4.1 日期格式 6.5 控制流函数6.5.1 if逻辑判断语句6.5.2 case when语句 6.6 窗口函数6.6.1 序号函数6.6.2 开窗聚合函数6.6.3 分布函数6.6.4 前后函数6.6.5 头尾函数6.6.6 其他函数6.7 … 文章目录 第六章6.函数6.1 聚合函数6.2 数学函数6.3 字符串函数6.4 日期函数6.4.1 日期格式 6.5 控制流函数6.5.1 if逻辑判断语句6.5.2 case when语句 6.6 窗口函数6.6.1 序号函数6.6.2 开窗聚合函数6.6.3 分布函数6.6.4 前后函数6.6.5 头尾函数6.6.6 其他函数6.7 练习 第六章
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.141593POW(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,…)s1s2等多个字符串合并成一个字符串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中的字符串s1REVERSE(s)字符串s的顺序反过来 函数名功能RIGHT(s,n)返回字符串s的后n个字符RTRIM(s)去除字符串s结尾处的空格STRCMP(s1,s2)比较s1和s2若相等返回0s1s2返回1s1s2返回-1SUBSTR(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的要求显示日期dSTR_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否则返回结果v2IFNULL(v1, v2)若v1的值不为NULL返回v1否则返回v2ISNULL(expression)判断表达式是否为NULLNULLIF(expr1, expr2)若字符串expr1与expr2字符串相等返回NULL否则返回expr1 6.5.2 case when语句
格式
CASE expression
WHEN conditon1 THEN result1WHEN conditon2 THEN result2....WHEN conditonN THEN resultNELSE resultEND
含义CASE表示函数开始END表示函数结束。若condition1成立返回result1condition2成立返回result2,当全部不成立返回result而当有一个成立后后面将不再执行 6.6 窗口函数
窗口函数又被称为开窗函数。
非聚合窗口函数对于聚合函数来说聚合函数是一组数据计算后返回单个值非聚合函数一次指挥处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时可将窗口范围内的数据输入到聚合函数中并不改变行数。
语法
windows_function (expr) OVER(PARTITION BY ... ORDER BY ...frame_clause
)windows_function窗口函数名 expr参数 OVER包含三个选项 分区(PARTITION BY)用于将数据行拆分成多个分区。若省略PARTITION BY所有数据作为一个组进行计算 排序(ORDER BY)用于指定分区内的排序方式 窗口大小(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 rn2from 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
fromdept 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)t2where rn 1)t3 where a.deptno t3.deptno;-- 查询员工比所属领导薪资高的员工个人信息
create view test_view asselect a.ename ename,a.sal esal,b.ename mgrname,b.sal mgrsal, a.deptno fromemp a,emp b where a.mgr b.deptnoand a.sal b.sal;select * from dept a join test_view b on a.deptno b.deptno;
