oracle语句书写常见问题
1.包含空值的表达式都为空
例：select a,b,a*12,a*12+b from test 若a为null，则a*12和a*12+b为null，若b为null，则a*12+b也为null。
nvl和nvl2滤空函数，nvl(a,b)判断a是否为Null，如果为null，返回b，否则返回a；nvl2(a,b,c)判断a是否为null，如果为null，返回c，不为null返回b。
2.null永远不=null,判断一个值是否为null。应该用 is null 或者is not null
例子：select * from emp where a=null 执行失败
3.oracle连接符 || 相当于java中对String的相加
例子： select 'Hello'||' World' from dual;
dual为oracle中内置的伪表。oracle中from后必须跟随一张表名，若select属性不跟任何表相关，则使用from dual；
4.oracle中大小写敏感，mysql中大小写不敏感。
例子：select * from emp where a="King" 和select * from emp where a = "KING" 的结果是不一样的，而在mysql中结果相同。
5.oracle中日期格式敏感。where中日期过滤条件必须符合预定义的日期格式。默认的格式是DD-MON-RR
6.between .. and .. 包含边界，小值在前，大值在后。
7.如果集合中有null值，不能使用not in，可以使用in
例子：select * from emp where deptno not in(10,20,null);查询不出数据
8.模糊查询转义字符的例子
例子：select * from emp where ename like '%\_%' escape '\'
9.oracle的事务是自动开启的，mysql的事务手动开启
10.where语句的解析顺序是从右往左的
例子：where condition1 and condition2;先执行condition2.
尽量把容易false的语句写在后面，效率更高。
11.order by 后面可以跟列名，表达式，别名，序号
例子：order by sal;order by sal*12;order by 年薪;order by 4;
12.order by 排序时，如果排序的列有null值，默认null显示在后面，oracle中null值最大
例子：order by conn desc nulls last;
如果不加nulls last，conn为null的记录为显示在最上方，加上nulls last,含有null值的记录便会出现在最下方
————————————————————————————————单行函数————————————————————————————————————
1.字符串函数 -转大小写函数：lower("Hello World")转小写，upper("Hello World")转大写，initcap("hello world");首字母大写
例子：上述结果为hello world,HELLO WORLD,Hello World
-字符串截取函数:一、substr(a,b);从字符串a中，第b位开始取
例子：select substr('Hello World',4) from dual; 结果为 lo World
二、substr(a,b,c)；从a中，第b位开始取，取c位
例子：select substr('Hello World',1,3) from dual;结果为Hel
-字符串长度函数：length(a)；取字符串的字符个数；lengthb(a)；取字符串的字节个数；
例子：select length('北京'),lengthb('北京') from dual;结果为 字符为2，字节为4
-查找字符串函数：instr(a,b);在a中，查找b,返回b在a中的下标，若找不到则返回0；
例子：select instr('Hello World','ll') from dual;结果为3
-字符串填充：lpad 左填充；rpad ;右填充
例子：select lpad('abcd',10,'*') from dual;结果为 ******abcd
-去掉字符串前后指定的字符：trim(a from b);
例子：select trim('H' from 'Hello WorldH') from dual; 结果为ello World
-字符串替换：replace
例子：select replace(‘Hello World’,'l','*') from dual;结果为He**o Wor*d;
2.字符函数 -四舍五入： round(a,b)
例子：select round(45.926,2),round(45.926,1) from dual;结果为45.96,45.9;
-截断：trunc(a,b);
例子：select trunc(45.926,2),trunc(45.926,1) from dual;结果为45.92,48.9
-求余：MOD(a,b) ;求a%b
例子：select mod(1600,300) from dual;结果为100
3.日期函数 mysql中date类型只有日期，datetime日期时间都有
oracle中只有date型数据，有日期和时间相当于mysql中的datetime
-查询当前时间：sysdate；是oracle中的关键字
例子：select sysdate from dual;
-时间格式化函数：to_char();
例子:select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;结果为2018-07-18 20:07:55
-时间计算
例子:select (sysdate-1)昨天，sysdate 今天,(sysdate+1)明天 from dual;
months_between(a,b);返回两个日期相差的月份。
例子：select months_between(sysdate,hiredate) from emp;
add_months(a,b);返回a之后b个月的日期
例子：select add_months(sysdate,30) from dual;
next_day(a,b)从a日期开始，返回下一个b日期的时间。应用：每个星期一自动备份表中数据
例子：select next_day(sysdate,'星期三') from dual;
round()日期的四舍五入;trunc()日期的截断
例子:select round(sysdate,'month') from dual;select trunc(sysdate,'month')
4.转换函数 -to_char();to_number();to_date();显示类型转换函数。
5.通用函数 nullif(a,b) 当a=b的时候，返回null；否则返回a
SQL> select nullif('abc','abc') 值 from dual;
值
---
SQL> select nullif('abc','abcd') 值 from dual;
值
---
abc
SQL> --coalesce 从左到右 找到第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;
COMM SAL 第一个不为null的值
---------- ---------- ------------------
800 800
300 1600 300
500 1250 500
2975 2975
1400 1250 1400
6.case when条件表达式
SQL> select ename,job,sal 涨前,
2 case job when 'PRESIDENT' then sal+1000
3 when 'MANAGER' then sal+800
4 else sal+400
5 end 涨后
6 from emp;
—————————————————————————多行函数————————————————————
1.组函数会自动滤空。avg(comm)相当于sum(comm)/count(comm);
comm中为null的记录过滤。
2.select 后不在分组函数里的字段，都应写在group by后面，否则无法执行。
3.如果可以使用where语句，尽量不使用having语句。
4.group by 语句的增强 break on skip 2;group by rollup(a,b);
——————————————————————————多表查询——————————————————————
SQL> /*
SQL> 希望把某些不成立的记录（40号部门），任然包含在最后的结果中 ---> 外连接
SQL> 左外连接: 当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中
SQL> 写法:where e.deptno=d.deptno(+)
SQL> 右外连接: 当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中
SQL> 写法:where e.deptno(+)=d.deptno
SQL> */
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.dname;
部门号 部门名称 人数
---------- -------------- ----------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
层次查询
SQL> --自连接不适合操作大表
SQL> --层次查询
SQL> select level,empno,ename,mgr
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null
5 order by 1;
——————————————————————————————子查询————————————————————————
SQL> /*
SQL> 注意的问题：1
SQL> 1、括号
SQL> 2、合理的书写风格
SQL> 3、可以在主查询的where select having from 后面使用子查询
SQL> 4、不可以在group by使用子查询
SQL> 5、强调from后面的子查询
SQL> 6、主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
SQL> 7、一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序
SQL> 8、一般先执行子查询，再执行主查询；但相关子查询例外
SQL> 9、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10、子查询中的null
SQL> */
oracle connect by用法篇
转：https://blog.csdn.net/wang_yunj/article/details/51040029
