2.2 SELECT练习 02 未完成

时间相关

查询当前时间

--ORACLE
SELECT SYSDATE() FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;
--MYSQL
SELECT SYSDATE() FROM DUAL;

2天以后的日期 ORACLE

SELECT SYSDATE+2 FROM DUAL;

所有员工入职的3天后是几号
ORACLE & MYSQL

SELECT HIREDATE "原日期",HIREDATE+3 "新日期" FROM EMP;
--这种方式实际上是错误的 是数字格式 而且可能会有逻辑上的错误
SELECT ENAME "员工姓名",HIREDATE "入职日期",ADDDATE(HIREDATE,3) "入职3天" FROM EMP;
--正确写法

查询所有员工的转正日期 ---- 3个月试用期

SELECT ENAME "员工姓名",HIREDATE "入职日期",ADDDATE(HIREDATE,30*3) "转正日期" FROM EMP;

查询所有员工到目前为止一共工作了几个月

--months_between(大日期,小日期)--ORACLE
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
--MYSQL
SELECT DATEDIFF(NOW(),HIREDATE) FROM EMP;
--显示的是相差的天数 不是想要的结果

– 查询当前月的最后一天

--ORACLE
SELECT LAST_DAY(SYSDATE)FROM DUAL;
--MYSQL
SELECT LAST_DAY(SYSDATE())FROM DUAL;

下一个星期三是几号

--ORACLE
SELECT NEXT_DAY(SYSDATE,'星期二') FROM DUAL;

字符串 转换 日期

--字符串转为日期对象  to_date('字符串','日期对象的模板')
-- 设定一个特定的时间（用一个特定的时间字符串转换为日期对象）
-- ORACLE
SELECT TO_DATE('2019/6/18 14:20:13','yyyy/mm/dd hh24:mi:ss')+2 FROM DUAL;
-- MYSQL
--SELECT DATE_FORMAT(时间,格式) ...
SELECT DATE_FORMAT('2013-03-09','%Y-%m-%d') FROM DUAL;

将日期转为特定格式的字符串

--日期对象转为字符串  to_char(日期对象,'字符串模板') ----ORACLE
SELECT TO_CHAR(SYSDATE,'yyyy"年"mm"月"dd"日" hh12:mi:ss') FROM DUAL;

给每个部门后后面添加一个伪列,如果10部门,伪列显示为十…

--decode(判断字段,值1,结果1,值2,结果2...,默认值) 判断函数
--ORACLE
DEPTNO,DECODE(DEPTNO,10,'十',20,'二十',30,'三十',40,'四十') 中文名 FROM DEPT;
--未测试

给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水

--ORACLE
SELECT ENAME,SAL "原薪水",DEPTNO, DECODE(DEPTNO=20,SAL*1.1,SAL) "涨薪后的薪水" FROM EMP;
--case when then else end 示例:
--ORALCE MYSQL通用
SELECT ENAME,SAL "原薪水",DEPTNO,(CASE deptno WHEN 20 THEN SAL * 1.1 ELSE SAL END) "涨薪后的薪水" FROM EMP;

10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍

--未完成

查询82年入职员工的信息

SELECT * FROM EMP WHERE HIREDATE BETWEEN TO_DATE('1982-01-01', 'yyyy-mm-dd') AND TO_DATE('1982-12-31', 'yyyy-mm-dd');

SELECT * FROM EMP WHERE HIREDATE >= TO_DATE('1982-01-01', 'yyyy-mm-dd') AND HIREDATE <= TO_DATE('1982-12-31', 'yyyy-mm-dd');
   
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'yyyy') LIKE '1982%';

--多行函数|组函数|组合函数|聚合函数  max() min() avg() sum() count(字段|1|*)

统计一下一共有多少个员工

SELECT COUNT(EMPNO) FROM EMP;
SELECT COUNT(1) FROM EMP;
SELECT COUNT(DEPTNO) FROM EMP;

统计一共有几个部门

SELECT COUNT(DEPTNO) FROM DEPTNO;

统计有员工存在的部门总数

SELECT COUNT(DISTINCT DEPTNO) FROM EMP;

统计20部门一共有多少人

SELECT COUNT(*) FROM EMP WHERE DEPTNO=20;

– 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

– 计算20部门每个月的工资花销
select sum(sal) from emp where deptno=20;

– 查询本公司的最高工资和最低工资 max() min()
select max(sal),min(sal) from emp;

–查看30部门的最高工资和最低工资,平均工资 avg()
select max(sal),min(sal),avg(sal),count(sal) from emp where deptno=30;
select * from emp;

–注意:
–**组函数只能和组函数和分组字段一起使用
–**组函数不能在where中使用
– 请查询出 20部门的平均工资， 部门编号
select avg(sal) from emp where deptno=20;

– 计算出所有员工的奖金总和
select sum(comm) from emp;

– 统计有奖金的员工有几个
select count(comm) from emp;
select count(1) from emp where comm is not null;

–查询 最高薪水的员工姓名， 及薪水
–查询的数据: ename,sal
–数据的来源: emp
–条件: sal=最高薪水
–最高薪水
select max(sal) from emp;
select ename,sal from emp where sal=(select max(sal) from emp);

– 查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal< (select avg(sal) from emp);

–查看 高于本部门平均薪水员工姓名
–查询的数据: ename
–数据的来源: emp
–条件: sal=最高薪水
SELECT ENAME FROM EMP WHERE
decode(deptno ,10 (decode (sal,) )
sal>(SELECT avg(SAL) FROM EMP);
SELECT avg(SAL) FROM EMP WHERE DEPTNO =10;
SELECT avg(SAL) FROM EMP WHERE DEPTNO =20;
SELECT avg(SAL) FROM EMP WHERE DEPTNO =30;
SELECT avg(SAL) FROM EMP WHERE DEPTNO =40;

select (case when deptno=10 and then
)ename from where

   
   
   
```javascript
--分组: group by 分组字段 having 过滤组信息
--select 查询的数据  from 表名|结果集 where 行记录条件 group by 分组字段1,分组字段2 having 组过滤条件 order by 排序字段;
--执行顺序: from--where--group by--having--select--order by
--有员工存在的部门编号
select distinct deptno from emp;
select deptno from emp group by deptno;

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

-- 求出每个部门的平均工资和部门编号
select max(sal),deptno from emp group by deptno;

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

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

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

--select ename e from emp where e='SMITH';  --在where 中不能使用select中的字段别名

--以部门和岗位进行分组
select avg(sal),deptno,job from emp group by deptno,job;

---查询 最低平均工资的部门编号
--所有部门的平均薪资
select avg(sal),deptno from emp group by deptno;

--最低部门的平均薪资
select min(avg(sal)) from emp group by deptno;

--根据最低部门的平均薪资找部门编号
select deptno,avg(sal)
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

--求平均薪资最低的部门名称
select dname
  from dept
 where deptno =
       (select deptno
          from emp
         group by deptno
        having avg(sal) = (select min(avg(sal)) from emp group by deptno));