oracle的笔记,凑合着看吧!3 比较杂烩的一些东西

insert all 多表插入
        他可以同时操作多个表,将多条insert语句共同协作一句sql完成;
        语法: insert all   into 表名 values(列名) (可以跟搜索语句);

 

表连接:

 

表连接分为左联接,右连接,全连接;
 
 左联接
     select * from emp a left join dept b on a.deptno = b.deptno
 右连接
    select * from emp a right join dept b on a.deptno = b.deptno
 全连接
     select * from emp a full join dept b on a.deptno = b.deptno
 自连接
     select t.ename from emp join emp t on emp.EMPNO = t.MGR
    
 
92表连接语法:
 select * from emp e,dept d where  e.deptno = d.deptno
    
99年新语法:
 cross 交叉,cross join 交叉连接;
  表连接 :
    select * from emp cross join dept;
  左联接 :
      select * from emp left cross join dept;

 

 

 

集合查询主要就三种:并集,交集,差集;

    并集,不重复union:将两个查询语句的结果集合斌,但是显示条件确实必须一样的,如"select ename,dname ";
        select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='SALES'
        union
        select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='RESEARCH'
       
    并集,重复:union all
        select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='SALES'
        union all
        select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='RESEARCH'
       
    交集,intersect
        用法如上;
    差集,满足前者,而不满足后者;minus
        用法如上;