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