SQL语句和SQL函数

--查询出全部数据（不能重复）
select * from scott.emp
       union all
select * from scott.emp;
--查询出全部数据（能重复）
select * from scott.emp
       union all
select * from scott.emp;
--查询出公共部分数据
select * from scott.emp
        intersect
select * from scott.emp;
--查询出除去公共部分的数据
select * from scott.emp
       minus
select * from scott.emp;
--'||'是连接字符的
select ename||'的工资是'|| sal from scott.emp where deptno=10;
--当前日期到指定日期之间相差的月数
select months_between(sysdate,to_date('201805','yyyymm')) m from dual;
--当前日期向前或者向后推几个月的日期
select add_months(sysdate,-2) from dual;
--当前日期的下个星期几的日期
select next_day(sysdate,7) from dual;
select next_day(sysdate,'星期六') from dual;
--给各个范围的工资加工资
create table emp2 as select * from scott.emp;
--查询时修改但数据并未真正改变过
select ename, case
        when sal>=800 and sal <=1000 then sal*1.5
        when sal>1000 and sal <=2000 then sal*1.3
        when sal>2000 and sal <=3000 then sal*1.2
        else sal*1.05
        end from emp2;
--数据真的修改了
update emp2 set sal=
       case
        when sal>=800 and sal <=1000 then sal*1.5
        when sal>1000 and sal <=2000 then sal*1.3
        when sal>2000 and sal <=3000 then sal*1.2
        else sal*1.05
        end;
select * from emp2 where months_between(hiredate,to_date('198105','yyyymm'))<=0;