oracle基础笔记

1、处理空值(NULL)：使用nvl(comm,0) 为NULL时使用0填充。
2、不等操作：A!B。
3、通配符：'_'和'%'
4、转义字符为：'\'
5、between and 等同于>= ... <=,前面的值是小值，后面是大值。
6、当与NULL进行比较时，不要使用=、<、>操作符，不会报错，但总是返回False，使用 is null判断
   如：select * from user where name is null;
7、使用group by 进行多列分组：
	如:select deptno,job,avg(sal),max(sal)
	from emp 
	group by deptno,job
8、使用having 子句限制分组结果，having个group by一起使用。
9、当使用group by 子句生成统计结果时，只会生成相关列的数据统计信息，而不会生成小计和总计。当使用
ROLLUP操作符时，自爱保留原有统计结果的基础上，还会还会生成横向小计。
当使用CUBE操作符时，会生成横向小计和总计。
select deptno as 部门编号,job,as 岗位,avg(sal) as 平均工资
from emp
group by rollup(deptno,job);

select deptno as 部门编号,job,as 岗位,avg(sal) as 平均工资
from emp
group by cube(deptno,job);
grouping子句返回0则统计结果使用了该列，返回1则没有使用。
rollup使用复合列：
在rollup才走附中使用复合列，在emp表中显示特定部门特定岗位的工资总额以及所有雇员的工资总额：
select deptno,job,sum(sal)
from emp
group by rollup((deptno,job))
在emp表中显示特定部门特定岗位的工资总额以及所有雇员的工资总额：
select deptno,job,sum(sal)
from emp
group by cube((deptno,job))


10、grouping sets：
显示部门平均工资和岗位平均工资：
select deptno,job,avg(sal)
from emp
group by grouping sets(deptno,job)
11、在SCOTT模式下，检索emp表中所有数据，并按照部门编号，员工编号
排序：
select deptno,empno,ename
from emp
order by deptno,empno
12、不建议使用order by。
多列排序：
select ename,deptno,sal
from emp
order by deptno,sal desc
13、内连接：
select columns_list
from table_name1 inner join yable_name2
on join_condition;

select e.empno as 员工编号 ,e.enme as 员工名称,d.dname as 部门
from emp e inner join dept d
on e.deptno=d.deptno;

14、外连接：
	左外连接：
		select e.empno,e.ename,e.job,d.deptno,d.dname
		from emp e left join dept d
		on e.deptno=d.deptno
	外连接还有一种形式：
	(+)
	select e.empno,e.ename,e.job,d.deptno,d.dname
		from emp e ,dept d
		where e.deptno =d.deptno(+)
		等同于上面的连接语句，(+)不能与on in 操作符一起使用。
		外连接的(+)一定要放在缺少信息的一边。全连接：
	full join
	LEFT/RIGHT/FULL OUTER JOIN
15、自连接：
16、交叉连接：cross join 笛卡尔积
自然连接：natural join 类似内连接，不需要连接条件。
17、多表关联查询的效率高于子查询。
子查询必须用()括起来，子查询中不能有order by子句，子查询允许嵌套，但不能超过255层。
18、关联子查询：
	在emp表中，使用关联子查询检索工资大于同职位的平均工资的员工信息：
	select empno,ename,sal
	from emp f
	where sal>(select avg(sal) from emp where job=f.job)
	order by job;
	外层查询使用f.job字段为内层查询提供职位名称。
19、in和exists的区别：https://www.cnblogs.com/iceword/archive/2011/02/15/1955337.html
	select a.* from A a
	where exists(select 1 from B b where a.id=b.id)

	以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.
	它的查询过程类似于以下过程

	List resultSet=[];
	Array A=(select * from A)

	for(int i=0;i<A.length;i++) {
	   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回
		   resultSet.add(A[i]);
	   }
	}
	return resultSet;

	当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.
	如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
	如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.
	再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

	结论:exists()适合B表比A表数据大的情况

	当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.
	
	
20、插入数据时通过DEFAULT提供数据：
	insert into dept values(60,'MARKET',DEFAULT)
21、批量插入：
	insert into dept2
		select * 
		from dept
		where dept.deptno>2
22、更新数据：
	update emp
		set sal=sal*2
		where ename='zhangsan'
	使用DEFAULT更新：
	update emp
		set job=DEFAULT
		where ename='SCOTT'
		
23、使用子查询更新数据，必须保证select返回单一的值，否则会报错：
	update emp
		set sal=(select avg(sal) 
			from emp where job ='MANAGER'
			)
		where sal<2000;
24、TRUNCATE：
	使用truncate可以删除表中所有记录，比delete快得多，但是不会产生回滚记录，执行了truncate语句的操作无法使用rollback语句撤销。
	truncate table jobs_temp;
25、特殊数据类型：
%Type
声明一个与emp表中job列数据类型完全相同的数据类型：
	var_job emp.job%Type;

26、RECORD类型：
	type record_type is record(
		var_name varchar2(20),
		var_job varchar2(20),
		var_sal number
	);
使用时，select ename,job,sal
			into record_type
			from emp
			where empno=5678;
			--输出
			dbms_output.put_line(record_type.var_name||record_type.var_job||record_type.var_sal);

27、在游标总可以使用行类型ROWTYPE：
	row_emp emp%rowtype;  

	select *
	into row_emp
	from emp where empno=7890;
	
28、存储行类型的集合type my_user_tab is table of my_user%rowtype ：
	
	declare  
	  cursor row_user  
	  is select *   
	  from my_user;  
	  type my_user_tab is table of my_user%rowtype;  
	   /*  
	   定义和表my_user行对象一致的集合类型cur_row_user，  
	  用于存放批量得到的数据  
	  */  
	  cur_row_user my_user_tab;     
		
	  begin  
		  open row_user;  
		  loop  
			/*从结果集中提取数据，每次提取两行*/  
			fetch row_user bulk collect into cur_row_user limit 2;  
			/*遍历集合cur_row_user中的数据*/  
			for i in 1..cur_row_user.count loop  
				dbms_output.put_line(cur_row_user(i).user_id||'----'||cur_row_user(i).name||'----'||cur_row_user(i).age);  
			end loop;  
			exit when row_user%notfound;  
			 
		  end loop;  
		  close row_user;  
	  end;  

29、常量：constant：
	con_day constant integer:=365;
30、PLSQL中变量系统默认初始化为NULL。
31、PLSQL中布尔表达式的值只有TRUE，FALSE，NULL，表达式TRUE AND NULL的值为NULL