扫码加入数据分析学习群

-- 常用函数

select abs(-32);

select floor(1.54);

select floor(-1.54);

select ceiling(1.54);

select ceiling(-1.54);

select round(1.54);

select round(1.54,1);

-- 字符串函数

select concat('CDA','数据', '分析');

select concat('CDA',null, '分析');-- null

select instr('CDA', 'A');

select instr('CDADA', 'A');-- 返回3，找到以后就再也不找了

select instr('数据分析', 'CDA');-- 找不到就是0

select left('CDA数据分析', 3);

select right('CDA数据分析', 4);

select mid('CDA数据分析', 4, 2);-- MID(str,pos,len) 返回字符串str的位置pos起len个字

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

select substring('CDA数据分析',1,3);

select substring('CDA数据分析',1);-- 提取1开始所有字符

select ltrim(' CDA数据分析');

select rtrim('CDA数据分析 ');

select trim(' CDA数据分析 ');-- 删除两边空格

select replace('CDA数据分析', 'CDA', 'cda');

select upper('cda');

select lower('CDA');

-- 数学函数

-- 练习：查询各部门员工人数占比（保留两位小数）

select deptno,round(count(empno)/(select count(*) from emp),2)

from emp

group by deptno;

-- 练习：查询各部门员工人数占比（以百分比显示）

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

from emp

group by deptno;

-- 日期函数

select date('20200722');-- 2020-07-22

select date('2020-07-22 12:00:00');-- 2020-07-22

select week('2020-01-01');-- 输出结果是0，2020年第一周也是2019年最后一周

select week('2020-01-01',1);-- 第一周开始计算

select week('2020-07-22');

select year('20-01-01'); -- 返回指定日期的年份(范围在1000到9999)

select month('2020-07-01');-- 返回指定日期的月份(范围在1到12)

select day('2020-01-31');-- 返回指定日期的日(范围在1到31)

select hour('2020-01-01 12:00:00');-- 返回的是0-23的数值

-- date_add和adddate 用法一样

select date_add("2020-01-01",interval 1 day);-- 加一天

select date_add("2020-01-01",interval 2 year);

select date_add("2020-01-01",interval 2 month);

select date_add("2020-01-01",interval -2 month);-- 可以是加负数的

-- date_sub和subdate 用法一样

select date_sub("2020-01-01", interval 1 day);-- 减一天

select date_sub("2020-01-01", interval 1 year);

select date_sub("2020-01-01", interval 1 month);

select date_format('20-01-01 12:00:00','%Y-%m');-- 结果：2020-01 根据format字符串格式化date值 在format字符串中可用标志符

select date_format('20-01-01 12:00:00','%m');-- 结果：01

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

select curdate();-- 以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)

select curtime();-- 以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)

select curdate() + 0;-- 结果：20200708

select now();-- 以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字 符串或数字)

select curtime() + 0;-- 结果：155430

select datediff('2020-7-22','2020-7-5');-- 结果：17 返回结束日expr1和起始日expr2之间的天数

select unix_timestamp();-- 1594200486 返回一个unix时间戳(从'1970-01-01 00:00:00'开始的秒数,date默认值为当前时间)

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

select from_unixtime(1577808000);-- 2020-01-01 00:00:00

select from_unixtime(1594200486);-- 2020-07-08 17:28:06 以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符 串或数字

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

select

ename,

hiredate,

floor(datediff(date(20200722),hiredate)/365) as 工龄

from emp;

select * from emp;

-- 分组合并函数

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

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

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

select deptno,group_concat(distinct ename order by sal desc separator '//')-- 可以按工资降序合并,separator指定合并分隔符，默认是,

from emp

group by deptno;

-- 逻辑函数

/*IFNULL(expression, alt_value)

判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个 参数的值*/

-- ifnull函数：查询每位员工的实发工资(基本工资+提成,没有提成计为0)

select *, ifnull(comm,0)+sal

from emp;

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

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

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

select *,

case when sal>=3000 then '高'

when sal<1500 then '低'

else '中'

end as 工资级别

from emp;

-- 开窗函数

/*开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 滑动窗口>])

rows between 1 preceding and 1 following 窗口范围是分区中的当前行、前一行、后一行一共三 行记录。

rows between 1 preceding and current row 窗口范围是分区中的前一行、当前行一共两行记录。

rows between current row and 1 following 窗口范围是分区中的当前行、后一行一共两行记录。

rows unbounded preceding 窗口范围是分区中的第一行到当前行。

rows between unbounded preceding and current row 窗口范围是分区中的第一行到当前行。

rows between current row and unbounded following 窗口范围是分区中的当前行到最后一行。

rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行。*/

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

-- 计算所有员工平均工资

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) as sum_sal from emp;-- 当over中指定排序但是没有指定滑动窗口时，默认计算的是分区内的第一行

-- 查询各部门员工的按照入职日期前一行后一行移动平均工资

select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) as avg_sal from emp;

-- 当over中指定了滑动窗口，计算的是滑动窗口内的所有行

/*

动态窗口函数：first_value() / last_value()/nth_value()/聚合函数用于开窗

如没有指定排序和滑动窗口范围，默认计算的是分区内的所有记录。

指定分区和排序后，如没有指定滑动窗口范围，默认计算的是分区内的第一行到当前行。

静态窗口函数：row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() /

lead()/ntile()

*/

-- 序号函数

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

select*,row_number()over(order by sal desc)

from emp;

/*

row_number()

显示分区中不重复不间断的序号

dense_rank()

显示分区中重复不间断的序号

rank()

显示分区中重复间断的序号

*/

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

select *,

row_number() over(partition by deptno order by sal desc) as 工资排名1,

dense_rank() over(partition by deptno order by sal desc) as 工资排名2,

rank() over(partition by deptno order by sal desc) as 工资排名3

from emp;