oracle

单行函数

日期可以做减操作。
months_between：两个日期相差的月数
add_months: 加上若干个月后的时间
last_day: 当前月的最后一天
next_day: 指定日期的下一个日期
round:也可以对日期进行四舍五入操作。

转换函数

显示转换比隐式转换性能高。

to_date('1999-01-01','yyyy-mm-dd'):按照指定格式转换成日期
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'):按指定格式转成字符串
to_number('123'):将字符串转成数字

通用函数

nvl(expr1,expr2)
nvl(expr1,expr2,expr3):当expr1不为空返回expr2 否则返回expr3
nullif(expr1,expr2):当expr1=expr2时返回null 否则返回expr1
coalesce(expr1,expr2....exprn)：找到参数列表中第一个不为空的值

条件表达式

case表达式：标准语法

select ename,job,sal 涨前工资,    case job when 'PRESIDENT' then sal+1000
                    when 'MANAGER' then sal+800
                    else sal+400
     end 涨后工资from emp;

decode函数：Oracle自己的语法

select ename,job,sal 涨前工资, decode(job,'PRESIDENT',sal+1000,                                                                      'MANAGER', sal+800,
                                                                      sal+400) 涨后工资from emp;

组函数

组函数会自动虑空，只统计非空的记录

select sum(comm)/count(*) 方式一,sum(comm)/count(comm) 方式二, avg(comm) 方式三 from emp;

  方式一     方式二     方式三                                                                                                                      
----------      ----------      ----------                                                                                                                      157.142857        550        550

嵌套滤空函数使组函数无法忽略空值

select sum(comm)/count(*) 方式一, sum(comm)/count(nvl(comm,0)) 方式二, avg(comm) 方式三 from emp;

    方式一     方式二     方式三                                                                                                                      
---------- ---------- ----------                                                                                                                      157.142857 157.142857        550

注意: 所有没有包含在select的组函数中的列，必须在group by后面

group by作用于多列：先按照第一列分组，如果相同，则第二列分组；以此类推

-- 按部门求部门的平均工资,选出大于2000的部门select deptno,avg(sal)from empgroup by deptnohaving avg(sal)>2000;-- having  过滤必须在分组后使用，having是先分组后过滤，注意性能问题。如果可以先用where 过滤后 在分组性能会更好。

group by的增强

select deptno,job,sum(sal)from empgroup by rollup(deptno,job);/*
怎么分组：
group by deptno,job
+
group by deptno
+
group by null
=
group by rollup(deptno,job)
----------------------------------
group by rollup(a,b)
=
group by a,b
 +
group by a
+
group by null
*/

多表查询

从笛卡尔积的集合中通过我们指定的条件选出正确的记录

等值链接

--查询员工信息，要求显示员工的编号，姓名，月薪和部门名称select e.empno,e.ename,e.sal,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno;

不等值连接

-- 查询员工的工资级别：编号 姓名 月薪和级别select e.empno,e.ename,e.sal,s.gradefrom emp e,salgrade swhere e.sal between s.losal and s.hisal;

外连接

当连接条件不成立时，任然希望在结果中包含某些不成立的记录

左外连接

当连接条件不成立时，等号左边所代表的表的信息仍然显示
写法: where e.deptno(+)=d.deptno
右外连接
当连接条件不成立时，等号右边所代表的表的信息仍然显示
写法： where e.deptno=d.deptno(+)

自连接： 会产生笛卡尔积 最好只用于小表

利用表的别名，将同一张表视为多张表

对于大的表，又有自连接查询的需求时，Oracle提供了层次查询。
connect by

select level,empno,ename,mgrfrom empconnect by prior empno=mgrstart with mgr is nullorder by 1;-- connect by prior empno=mgr  前一次查询的empno员工号码等于本次查询的老板号码mgr-- start with mgr is null 层次查询的起始条件。mgr老板号码为null 也就是从boss开始/*
     LEVEL      EMPNO ENAME             MGR                                                                                                           
---------- ---------- ---------- ----------                                                                                                           
         1       7839 KING                                                                                                                            
         2       7566 JONES            7839                                                                                                           
         2       7698 BLAKE            7839                                                                                                           
         2       7782 CLARK            7839                                                                                                           
         3       7902 FORD             7566                                                                                                           
         3       7521 WARD             7698                                                                                                           
         3       7900 JAMES            7698                                                                                                           
         3       7934 MILLER           7782                                                                                                           
         3       7499 ALLEN            7698                                                                                                           
         3       7788 SCOTT            7566                                                                                                           
         3       7654 MARTIN           7698                                                                                                           

     LEVEL      EMPNO ENAME             MGR                                                                                                           
---------- ---------- ---------- ----------                                                                                                           
         3       7844 TURNER           7698                                                                                                           
         4       7876 ADAMS            7788                                                                                                           
         4       7369 SMITH            7902  
*//*
第一次： where mgr is null =>  7839
第二次： where mgr = 7839  => 7566 7698  7782
第三次:  where mgr in (7566,7698,7782)
*/

作者：G鸣G
链接：https://www.jianshu.com/p/21d2748625c9