常用函数

1.字符串函数

select concat('CDA','数据','分析'); -- 结果：CDA数据分析

select instr('CDACDSEA','a'); -- 结果：3（返回第一个a出现的位置）

select left('CDA数据分析',3); -- 结果：CDA

select right('CDA数据分析',4); -- 结果：数据分析

select mid('CDA数据分析',4,4); -- 结果：数据分析

select substring('CDA数据分析',4,4); -- 结果：数据分析

select ltrim(' CDA数据分析 '); -- 左边去空格

select rtrim(' CDA数据分析 '); -- 又边去空格

select trim(' CDA数据分析 '); -- 两头去空格

select replace('CDA数据分析', 'CDA', 'cda'); -- 将cda替换CDA

-- select repeat('CDA',3); -- 将CDA复制写3遍

-- 查询各部门的员工人数占比：各部门人数/总人数(以百分比显示)

select deptno,concat((count(empno)/(select count(empno) from emp))*100,'%') 人数占比

from emp

group by deptno;

-- 练习：将每位员工的姓名首字母转换为大写

select empno,replace(ename,left(ename,1),upper(left(ename,1))) from emp;

2.数学函数

select abs(-32); -- 绝对值

select floor(1.23);

select floor(-1.23); -- 取下限

select ceiling(1.23);

select ceiling(-1.23); -- 取上限

select round(1.58,1);

select round(1.58); -- 取小数，满足四舍五入

select rand();

select rand(123); -- 随机取数

-- 查询各部门的员工人数占比：各部门人数/总人数(以百分比显示)(保留2位小数)

select deptno,concat(round(count(empno)/(select count(empno) from emp)*100,2) ,'%') 人数占比

from emp

group by deptno;

3.日期函数

select date('20200101');

select date('2001001');

select date('2020-01-01 12:00:00'); -- 标准日期格式

select week('2019-01-01',0); -- 0-53周

select week('2019-01-01',1); -- 1-52周

select month('2020-06-03'); -- 月

select quarter('2020-06-03'); -- 季度

select date_add("2020-06-03",interval 4 day); -- 加上对应的时间：day/month/year

select date_sub("2020-01-01", interval 1 month); -- 减去对应的时间:day/month/year

select date_format('20-01-01 12:00:00','%Y-%m-%d');

select date_format('1993-01-01 12:00:00','%y-%m-%d'); -- 按固定格式取日期

select curdate(); -- 获取当前电脑日期

select curtime(); -- 获取当前电脑时间

select now(); -- 获取当前电脑日期和时间

select datediff('2020-6-3','2020-5-1'); -- 两个日期之间差的天数

select empno,ename,job,hiredate,datediff(curdate(),hiredate)/365 工龄 from emp;

select unix_timestamp(); -- 时间戳（从'1970-01-01 00:00:00'开始的秒数,date默认值为当前时间）

select unix_timestamp('2020-01-01');

select from_unixtime(1577808000); -- 将时间戳转化成日期

-- 练习：查询每位员工的工龄（年）：ename,hiredate,工龄

select empno,hiredate,floor(datediff(curdate(),hiredate)/365) 工龄 from emp;

4.分组合并函数(重要)

-- GROUP_CANCAT([distinct] str [order by str asc/desc] [separator])

-- 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

-- 练习：查询各部门的员工姓名

select deptno,group_concat(distinct ename order by sal separator '/')  

from emp  

group by deptno; -- -- 可以去重、排序、改分割符合（默认是','分割，可以用separator改变分割符合）

5.逻辑函数

select ename,sal,comm,ifnull(comm,0) from emp;

-- if函数：查询每位员工的工资级别：3000及以上为高，1500-3000为中，1500及以下为低

select * ,if(sal>=3000,'高',if(sal<=1500,'低','中')) 工资级别 from emp;

-- 逻辑表达式 case when ...then... else ... end

select *,case when sal>=3000 then '高'

when sal<=1500 then '低'

else '中'

end 工资级别

from emp;

6.开窗函数(本质还是聚合函数)

-- 语法：开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])

-- 聚合函数用于开窗函数

-- 查询所有员工的平均工资

select * from emp;

select avg(sal) 平均工资 from emp;

select *,avg(sal) over() 平均工资 from emp;

-- 开窗函数，当over中没有指定分区、排序和滑动窗口时，默认将整个表作为一个区，默认计算的是所有值

-- 查询所有部门员工的平均工资

select deptno,avg(sal) 平均工资 from emp group by deptno; -- 普通做法

select *,avg(sal) over(partition by deptno) 平均工资 from emp; -- 开窗函数做法

-- 当over中指定了分区,但是没有指定排序和滑动窗口时,默认计算的是当前分区内的所有值

-- 查询各部门的累计工资

select *,sum(sal) over(partition by deptno order by hiredate) 累计工资 from emp;

-- 当over中指定分区和排序,但是没有指定滑动窗口,默认计算的是分区内第一行到当前行的值

select *,sum(sal) over(partition by deptno ) 累计工资 from emp;

-- 查询各部门按入职日期计算移动平均工资(当前行跟上下一行的平均值）

select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) 移动平均工资 from emp;

-- 当over中指定分区、排序和滑动窗口，那么计算的就是分区内滑动窗口的值

7. 序号函数

-- row_number() 显示分区中不重复不间断的序号 (静态窗口)

-- dense_rank() 显示分区中重复不间断的序号

-- rank() 显示分区中重复间断的序号

-- 查询所有员工的工资排名

select * ,row_number() over(order by sal desc ) 排名 from emp;

-- 查询各部门员工的工资排名

select * ,row_number() over(partition by deptno order by sal desc ) 排名1,

rank() over(partition by deptno order by sal desc ) 排名2,

dense_rank() over(partition by deptno order by sal desc ) 排名3

from emp;

8. 查询数据导出保存的方法，保存在安全路径下

-- into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/t1.csv' -- (t1.csv t1是保存的文件名称 CSV是保存的类型)

-- fields terminated by ',' -- ','行内数据用逗号隔开

-- lines terminated by '\r\n'; -- '\r\n'回车换行 (行与行保存时的符合定义)

-- 如保存查询各部门员工的工资排名的结果数据

select * ,row_number() over(partition by deptno order by sal desc ) 排名1,

rank() over(partition by deptno order by sal desc ) 排名2,

dense_rank() over(partition by deptno order by sal desc ) 排名3

from emp

into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/t1.csv'

fields terminated by ','

lines terminated by '\r\n';