**JAVA学习笔记Day16（数据库二）

函数

--当前日期
select sysdate from dual;
select current_date from dual;

--2天后的日期
select sysdate+2 from dual;
--员工入职后3天的日期
select hiredate,hiredate-3 from emp;

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

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

--LAST_DAY(d) 返回的所在月份的最后一天
--当前月份最后一个的日期  
select last_day(sysdate) from dual;

--入职月份的最后一天
select last_day(hiredate) from emp;

-- months_between(date1,date2) 返回date1和date2之间月的数
--员工入职时间
select months_between(sysdate,hiredate) from emp; --月

--next_day(sysdate,'星期一') 下周星期一
--下个周一
select next_day(sysdate,'星期一') from dual;
--下个周四  即将要过的下一个周四
select next_day(sysdate,'星期四') from dual;

--to_date(c,m)  字符串以指定格式转换为日期
select to_date('2018-05-15 2:38:33','yyyy-mm-dd hh12:mi:ss') from dual;
select to_date('2018年05月15日 2:38:33','yyyy-mm-dd hh12:mi:ss') from dual;

--to_char(d,m)  日期以指定格式转换为字符串

-当前日期
select sysdate from dual;
select current_date from dual;

--2天后的日期
select sysdate+2 from dual;
--员工入职后3天的日期
select hiredate,hiredate-3 from emp;

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

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

--LAST_DAY(d) 返回的所在月份的最后一天
--当前月份最后一个的日期  
select last_day(sysdate) from dual;

--入职月份的最后一天
select last_day(hiredate) from emp;

-- months_between(date1,date2) 返回date1和date2之间月的数
--员工入职时间
select months_between(sysdate,hiredate) from emp; --月

--next_day(sysdate,'星期一') 下周星期一
--下个周一
select next_day(sysdate,'星期一') from dual;
--下个周四  即将要过的下一个周四
select next_day(sysdate,'星期四') from dual;

--to_date(c,m)  字符串以指定格式转换为日期
select to_date('2018-05-15 2:38:33','yyyy-mm-dd hh12:mi:ss') from dual;
select to_date('2018年05月15日 2:38:33','yyyy"年"mm"月"dd"日" hh12:mi:ss') from dual;

--to_char(d,m)  日期以指定格式转换为字符串
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh12:mi:ss') from dual;

--组函数 count() sum() max() min() avg()
--count(字段|*|1)
--计算所有员工的个数
select count(empno) from emp;
select count(*) from emp;
select count(1) from emp;

--求有员工存在的部门个数
select count(distinct deptno) from emp;
select count(distinct 1) from emp;
select count(deptno) from dept where deptno in(select distinct deptno from emp);

--求一个公司这个月的薪资开销
select sum(sal) from emp;

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

--求公司最高薪资
select max(sal) from emp;

--求公司最低薪资
select min(sal),max(sal),ename from emp;

select min(sal) from emp;

--求公司薪资最低的人和薪资
select ename, sal from emp where sal = (select min(sal) from emp);

--求30部门的平均薪资
select avg(sal) from emp where deptno=30;

--求薪资高于平均薪资的员工姓名
select ename,sal from emp where sal>(select avg(sal) from emp);

-- 查出比本部门平均工资高的员工信息
--查询10部门的平均薪资
select avg(sal) from emp where deptno=10;
select *
  from emp e1
 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);
 
--分组 group by 分组字段
--select *|字段.. from 表名 where 行过滤条件  group by 分组字段  having 组过滤信息 order by 排序字段;
--执行流程:from--where--group by--having--select--order by
--select ename en from emp where en='SMITH';
select deptno from emp group by deptno;

-- 找出20部门和30部门的最高工资 
select max(sal) from emp group by deptno having deptno in(20,30);

-- 求出每个部门的平均工资
select avg(sal) from emp group by deptno;

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal) from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal) from emp where sal>1000 group by deptno having deptno in(20,30);
select avg(sal) from emp where sal>1000 and deptno in(20,30) group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--查询 最低平均工资的部门编号
--先查出最低的部门平均薪资
select min(avg(sal))  from emp group by deptno;
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);


----查看 高于本部门平均薪水员工姓名
select *
  from emp e
 where exists
 (select deptno
          from (select deptno, avg(sal) avgsal from emp group by deptno) e2
         where e.deptno = e2.deptno
           and e.sal > avgsal);

---- 统计每个部门的员工数，和部门编号
-- 查询每个工种的最高工资以及工种
---- 查询平均工资在1500到2000之间的部门平均工资和部门编号

-- 查出比本部门平均工资高的员工信息
--查询10部门的平均薪资
select avg(sal) from emp where deptno=10;
select *
  from emp e1
 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);
 
--分组 group by 分组字段
--select *|字段.. from 表名 where 行过滤条件  group by 分组字段  having 组过滤信息 order by 排序字段;
--执行流程:from--where--group by--having--select--order by
--select ename en from emp where en='SMITH';
select deptno from emp group by deptno;

-- 找出20部门和30部门的最高工资 
select max(sal) from emp group by deptno having deptno in(20,30);

-- 求出每个部门的平均工资
select avg(sal) from emp group by deptno;

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal) from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal) from emp where sal>1000 group by deptno having deptno in(20,30);
select avg(sal) from emp where sal>1000 and deptno in(20,30) group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--查询 最低平均工资的部门编号
--先查出最低的部门平均薪资
select min(avg(sal))  from emp group by deptno;
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);


----查看 高于本部门平均薪水员工姓名
select *
  from emp e
 where exists
 (select deptno
          from (select deptno, avg(sal) avgsal from emp group by deptno) e2
         where e.deptno = e2.deptno
           and e.sal > avgsal);

-- 统计每个部门的员工数，和部门编号
select deptno,count(1) from emp group by deptno;
-- 查询每个工种的最高工资以及工种
select job,max(sal) from emp group by job;
---- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select avg(sal),deptno from emp group by deptno having avg(sal) between 1500 and 2000 ;


--decode(字段,字段值1,值2,字段值2,值3..,默认值)根据某个字段进行判断
--打印所用的部门名称,如果10,添加一个伪列,显示对应部门的大写名称
select deptno,decode(deptno,10,'十',20,'二十',30,'三十','其他') from dept;

--给20部门的员工涨薪10%,打印员工信息
select ename,deptno,sal,decode(deptno,20,sal*1.1,sal) from emp;
--case when then else end
select ename,
       sal,
       deptno,
       (case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.08
         when 30 then
          sal * 1.15
         else
          sal * 1.2
       end) raisesal
  from emp;

--测试数据
create table tb_student(
 id number(4) ,
 name varchar2(20),
 course varchar2(20),
 score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;

select * from tb_student;


--使用一条sql语句，查询每门课都大于80分的学生姓名
select name
  from tb_student
 group by name
having min(score) > 80 and count(course) = (select count(distinct course)
                                              from tb_student);
--数据 : name
--来源 : tb_student
--条件 : 每门课程(这个人所有课程最小分数)>80分   课程个数=3(select count(distinct course) from tb_student)
--查询课程数
select count(distinct course) from tb_student;
--求每个人课程最小分数
select min(score) from tb_student group by name;

--行转列
select name ,   from tb_student group by name;