数据库学习6-连接查询

/*
  集合操作
*/
--并集 
--union all 合并集合
SELECT tt1,tt2,tt3 FROM test1 
  UNION ALL
SELECT * FROM test2;
--union   合并集合且去重复
SELECT tt1,tt2,tt3 FROM test1
  UNION
SELECT * FROM test2;

--交集
SELECT tt1,tt2,tt3 FROM test1
  INTERSECT
SELECT * FROM test2;

--差集 第一个集合减去交集并且去重复
SELECT tt1,tt2,tt3 FROM test1
   MINUS
SELECT * FROM test2;

--rownum
SELECT ROWNUM,emp.* FROM emp WHERE ROWNUM<6;

--通过伪列rownum和集合操作符minus完成分页查询
SELECT ROWNUM,emp.* FROM emp 
  WHERE ROWNUM<11 
    MINUS
SELECT ROWNUM,emp.* FROM emp 
  WHERE ROWNUM<3;
  
/**
  连接(联表)查询
*/  
--内连接(INNER JOIN或join): 符合连接条件的记录
SELECT * FROM emp INNER JOIN dept 
       ON emp.deptno=dept.deptno; --连接条件
       
SELECT * FROM emp e,dept d 
       WHERE e.deptno = d.deptno;       

--查询员工编号，姓名，工作，入职日期，薪水，部门名称
SELECT empno,ename,job,hiredate,sal,dname
    FROM emp t1 INNER JOIN dept t2 
       ON t1.deptno = t2.deptno;   
       
SELECT empno,ename,job,hiredate,sal,dname
    FROM emp t1 JOIN dept t2 
       ON t1.deptno = t2.deptno;   
       
--外连接
--左外连接(left outer join或left join):符合条件的加上左边不符合条件的记录
SELECT * FROM emp e LEFT OUTER JOIN dept d
    ON e.deptno = d.deptno;  
    
SELECT * FROM emp e LEFT JOIN dept d
    ON e.deptno = d.deptno;
    
SELECT * FROM emp e,dept d
    WHERE e.deptno = d.deptno(+); 
    
SELECT * FROM dept d,emp e
    WHERE e.deptno = d.deptno(+);  

SELECT * FROM emp e,dept d
    WHERE d.deptno(+) = e.deptno;          
--右外连接(right outer join或right join):符合条件的加上右边不符合条件的记录
SELECT * FROM emp e RIGHT OUTER JOIN dept d
    ON e.deptno = d.deptno;
    
SELECT * FROM emp e RIGHT JOIN dept d
    ON e.deptno = d.deptno; 
    
SELECT * FROM emp e ,dept d
    WHERE e.deptno(+) = d.deptno;    
--全连接(full join)
SELECT * FROM emp e FULL JOIN dept d
    ON e.deptno = d.deptno;      
    
--查询工作类型是CLERK的员工姓名，工作，薪水，部门名
SELECT ename,job,sal,dname
   FROM emp t1, dept t2
     WHERE t1.deptno = t2.deptno
       AND job = 'CLERK';  
       
SELECT ename,job,sal,dname 
   FROM emp t1 JOIN dept t2 
     ON t1.deptno = t2.deptno 
      WHERE job = 'CLERK';      
      
--查询员工名字和领导名字
SELECT t1.ename,t2.ename FROM emp t1,emp t2 
    WHERE t1.mgr = t2.empno;                            

练习：

1.  查询所有员工的名称，部门编号及部门名称


2.  列出所有雇员的姓名、部门名称和薪金


3.  显示有提成的员工的信息：
    名字、提成、所在部门名称、所在地区的名称


4.  列出所有“CLERK”（办事员）的姓名及其部门名称


5.  列出部门名称和这些部门的雇员，
    同时列出那些没有雇员的部门


6.  列出所有雇员的姓名及其直接上级的姓名


7.  列出入职日期(雇佣日期)早于其直接上级的所有雇员


8.  列出分配有雇员数量的所有部门的详细信息，
    即使是分配有0个雇员