Oracle 日期函数练习 随便写写

select sysdate from dual;
select round(sysdate - hiredate) from emp;

select * from emp;

select round(1.8) from dual; --round 四舍五入
select trunc(1.8) from dual; -- trunc 抹零。 抹去小数 取整

select trunc(months_between(sysdate, hiredate)) from emp; --months_between 两个日期直接相差多少月

select trunc(sysdate - hiredate) from emp; --两个日期直接相差多少天 并且 抹去小数

select add_months(sysdate, 12) from dual;

select to_char(hiredate, 'dd') 日 from emp;
select hiredate from emp;

select to_char(sysdate ,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate ,'yyyy/mm/dd hh12:mi:ss') from dual;

select ename ,to_char(sal,'99,999') from emp;

select to_number('12') + to_number('10') from dual;

select to_date('1999-09-09 12:01:01','yy-mm-dd hh24:mi:ss') from dual;

select to_char(sal*12) +to_char(comm) from emp where comm is not null;

select * from emp where comm is not null;

select ename ,sal*12+ nvl(comm,'0') from emp;

select decode(1*3,3,'my is three', '1', 'my is one', '其他') from dual;

select * from emp;
select ename,decode(job,'CLERK','业务员','SALESMAN','销售','其他职位') from emp;

select * from emp e,dept d where e.deptno=d.deptno;

select * from emp;

select E.ENAME,E1.EMPNO,E1.ENAME from emp E,EMP E1 WHERE E.MGR=E1.EMPNO ;
SELECT * FROM EMP;

select E.ENAME,E1.EMPNO,E1.ENAME ,DNAME from emp E,EMP E1 ,DEPT D WHERE E.MGR=E1.EMPNO AND E.DEPTNO=D.DEPTNO ORDER BY DNAME ;

SALGRADE salgrade
select E.ENAME,E.SAL,E1.EMPNO,E1.ENAME ,E1.SAL,DNAME from emp E,EMP E1 ,DEPT D WHERE E.MGR=E1.EMPNO AND E.DEPTNO=D.DEPTNO ORDER BY DNAME ;

select * from salgrade,emp,dept;
select * from salgrade;

select e.ename,e.sal,d.dname,s.* from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal

select e.ename,e.sal,d.dname,s.* from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal >=s.losal and e.sal<=s.hisal

--emp 员工表 dept 部门表 salgrade 工资等级表
select e.ename,e.sal,d.dname,s.* ,e1.ename,e1.sal,s1.grade from emp e,dept d,salgrade s ,emp e1,salgrade s1 where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=e1.empno
and e1.sal between s1.losal and s1.hisal;