数据库修仙之路2

根据函数的返回结果，我们将函数分为单行函数和多行函数

单行函数 (一条记录返回一个结果)	日期函数 转换函数(重点) 其他函数(保证类型兼容)
多行函数(组函数. 聚合函数) (多条记录 返回一个结果 (重点))	1) 、count :统计记录数 count() -->* 或一个列名 2) 、max min: 最大值 最小值 3) 、sum：求和 4)、avg:平均值
分组	分组: group by , 将符合条件的记录 进一步的分组 过滤组:having , 过滤组信息 ，表达式 同 where 一致
行转列

 

一:单行函数

1. 日期函数

--sysdate/current_date 以date类型返回当前的日期

select sysdate from dual;

select current_date from dual;

 

--2天以后的日期

select sysdate+2 from dual;

 

--员工入职时候3天后的时间

select hiredate+3 from emp;

 

--每个员工的转正日期  3个月转正

select hiredate 入职日期,hiredate+30*3 转正日期 from emp;

 

--月份只差  add_months(date,月个数)

select hiredate 入职日期,add_months(hiredate,3) 转正日期 from emp;

 

--入职日期到现在工作的月份数  大的日期放在第一个参数

select hiredate 入职日期,months_between(sysdate,hiredate) 工作月份数 from emp;

 

--当前月份的最后一天

select last_day(sysdate) from dual;

--入职月份的最后一天

select last_day(hiredate) from emp;

 

--即将要过的下一个星期二的日期

select next_day(sysdate,'星期二') from dual;

 

1. 转换函数

--to_date(c,m) à 字符串以指定格式转换为日期   第一个参数要转换的字符串,第二个参数格式

select to_date('2019-04-09 09:44:11','yyyy-mm-dd hh24:mi:ss') 日期 from dual;

select to_date('2019年04月09日 09:44:11','yyyy"年"mm"月"dd"日" hh24:mi:ss') 日期 from dual; --如果存在中文,在中文的前后添加一对""

--to_char(d,m) à 日期以指定格式转换为字符串 括号里第二个参数为字符串

select to_char(sysdate,'yyyy"年"mm"月"') 年 from dual;

 

--查询 82年入职的员工信息

select * from emp where to_char(hiredate,'yyyy') like '%82';

select * from emp where to_char(hiredate,'yyyy')=1982;

select*from emp where hiredate between to_date('1982/01/01','yyyy/mm/dd') and  to_date('1982/12/31','yyyy/mm/dd');

 

1. 其他函数(decode函数)

--给20部门所有员工涨薪水 20%,打印所有员工的姓名,原本薪水,涨薪后的薪水

select ename ,sal ,deptno ,decode(deptno,20,sal*1.2,sal) from emp;

 

----case when then else end

select ename,sal,deptno,(case deptno when 20 then sal*1.2 else sal end) from emp;

--给10部门的员工崭新10% 20部门涨薪20% 30部门涨薪30% 40部门不涨薪

select ename,

       deptno,

       sal,

       decode(deptno, 10, sal * 1.1, 20, sal * 1.2, 30, sal * 1.3, sal)

  from emp;

 

select ename,

       deptno,

       sal,

       (case deptno

         when 10 then

          sal * 1.1

         when 20 then

          sal * 1.2

         when 30 then

          sal * 1.3

         else

          sal

       end)

  from emp;

 

二:组函数

--注意:

--组函数不能和非分组字段一起使用

--where条件中不允许使用组函数

 

1. count

--count(* |字段 |1 ) 统计个数

--一共存在多少个员工

select count(*) from emp;

select count(empno) from emp;

--其中where 1=1,由于1=1永远是成立的，返回TRUE,条件为真；所以，这条语句，就相当于select * from table，返回查询表中的所有数据。

select count(1) from emp where 1=1;

select ename,hiredate,sal,1 from emp;

select count(1) from emp;  --相当于每条数据后面有一个伪列1,对1的个数进行统计

 

--统计有员工存在的部门个数

select count(distinct deptno) from emp;  --3

select count(distinct 1) from emp; --1

--以下不行,其等于select count(deptno) from emp where deptno in(10,20,30);  而在10,20,30中emp表中共有12个元素

--select count(deptno) from emp where deptno in(select distinct deptno from emp);  --12

 

--统计20部门的员工个数

select count(1) from emp where deptno=20;

 

1. sum

--sum(字段)

--求每一个月公司总共的薪资开销

select sum(sal) from emp;

--对20部门所有员工薪资求和

select sum(sal) from emp where deptno=20;

 

--求奖金不为null的员工个数

--null值不参与计算

select count(comm) from emp;

select count(1) from emp where comm is not null;

select count(1) from emp where not comm is null;

 

1. max   min

--max()  min()

--求公司薪资最高的薪资

select max(sal) from emp;

select min(sal) from emp;

 

--求30部门薪资最大的和最小的薪资

select max(sal),min(sal) from emp where deptno=30;

 

1. avg

--avg() 平均值

--公司所有员工的平均薪资

select avg(sal) from emp;

 

 

--求最低薪资员工的姓名和薪资

--select ename,min(sal) from emp;

select min(sal) from emp;

select ename,sal from emp where sal=(select min(sal) from emp);

--求一个人的基本信息,但是这个人的薪资与最低薪资相同

select * from emp where sal = (select min(sal) from emp);

--select * from emp where sal = min(sal);

 

 

--求比公司平均薪资低的员工信息

select * from emp where sal<(select avg(sal) from emp);

--求各部门的平均薪资

select avg(sal)from emp group by deptno;

--求薪资高于本部门平均薪资的员工姓名

select ename from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno);