甲骨文基本知识2

关联查询


笛卡尔积
指做关联操作的每个表的每一行都和其他表的每一行组合，假设两个表的记录条数分别是x和y，笛卡尔积将返回x*y条记录
select count(*) from emp ;
select count(*) from dept ;


select emp.ename , dept.dname from emp ,. dept 




等值连接
连接查询中最常见的一种，通常是在有主外键关联关系的表之间建立，并将连接条件设定为有关系的列，使用“=”连接相关的表。


查询职员的姓名、职位以及所在部门的名字和所在城市
select e.ename , e.job , d.dname ,d.loc
from emp e, dept d 
where e.deptno = d.deptno ;




内连接
返回所有满足条件的记录
select e.ename , e.job , d.dname ,d.loc
from emp e, dept d 
where e.deptno = d.deptno ;
或者
select e.ename , e.job , d.dname ,d.loc 
from emp e join dept d 
on (e.deptno = d.deptno) ;






外连接


不仅返回满足条件的记录，还将返回不满足条件的记录
select table1.column , table2.column 
from table1 left | right | full [outer] join table2 
on table1.column1 = table2.column2 ;


select e.ename , e.job , d.dname ,d.loc 
from emp e left outer join dept d 
on (e.deptno = d.deptno) ;
select e.ename , e.job , d.dname ,d.loc 
from emp e right outer join dept d 
on (e.deptno = d.deptno) ;


查询某用户下某表的约束名称（在dba角色下查询）
select constraint_name from dba_constraints where owner = 'SCOTT' AND table_name= 'EMP' ;


查询当前用户下的表约束
select   constraint_name,   constraint_type , search_condition,   r_constraint_name 
from   user_constraints   where   table_name   =   upper('emp'); 


删除emp表中的外键约束
alter table emp
drop constraint FK_DEPTNO ;


INSERT INTO "SCOTT"."EMP" (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) VALUES ('8888', 'lg', 'BOSS', '7839', TO_DATE('1981-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4500', '50')




自连接
是一种特殊的连接查询，数据的来源是同一个表，也就是说关联关系来自单表中的多个列。
表中的列参照同一个表中的其他列的情况称作自参照表
自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接


查出每个职员的经理名字，以及他们的职员编号
select worker.empno w_empno , worker.ename w_ename , manager.empno m_empno , manager.ename m_ename 
from emp worker join emp manager 
on worker.mgr = manager.empno ;
 spool f:\a.txt;
-----------------------------------------------------------------------------------------------------------------------------------------------


子查询
  子查询在where子句中
  在select查询中,在where查询条件中的限制条件不是一个确定的值,而是来自于另一个查询的结果
  为了给查询提供数据而首先执行的查询语句,叫子查询
   子查询是嵌入在其他sql语句中的select语句,大部分出现在where子句中
   子查询嵌入的语句称作主查询,或者父查询
   主查询可以是select语句也可以是其他的sql语句中
    根据返回结果的不同,子查询可以分为单行子查询,多行子查询,多列子查询
     查找和scott员工同职位的员工
select ename from emp where job =(select job from emp where ename ='SCOTT');
     
rownum 
被称作为伪列,用于返回标识行数据顺序的数字
 
select rownum,empno,ename,sal from emp;
查询出emp表第三条以后的数据(必须使用子查询)
      select ename from(select rownum rm,e.* from emp e)where rm>3;
 查询出emp表第三列到第七条数据
 使用子查询进行分页
 '分页策略:每次只取一页数据,每次换页,去下一页数据
 在oracle中利用rownum的功能可用来进行分页,结社结果集共有105条数据,每页20条,那么:
page1:1-20
page2:21-40
.......
pageN:pagesize*(currentpage-1)+1  -pagesize*currentpage
按照薪水倒序排列,取出结果集中第8到第十条记录
 
 
 
select * from (select rownum rn,e.* from (select empno,ename,sal from emp order by sal desc ) e ) where rn between 8 and 10;
===========================================================================================
  查询职员表,根据职员的职位计算奖金额,管理者按照工资1.2倍,办事员1.1倍工资,销售员1.05倍工资,如果不是这三类职位的,则取工资值
   
   decode函数解决此类问题
    decode(column,search1,result1,search2,result2................default_result);
年薪的50%,40,30,10
   select ename,sal,comm,job,(sal*12),decode(job,'MANAGER',1.5*(sal+nvl(comm,0))*12,'CLERK',1.4*(sal+nvl(comm,0))*12,'SALESMAN',1.3*(sal+nvl(comm,0))*12 ,1.1*12*(sal+nvl(comm,0))) yearbonus from emp;


 等价处理
 select ename,job,sal,case job when 'MANAGER'then 1.2*sal
                               when 'ANALYST'then 1.1*sal
  when 'SALESMAN'then  1.05*sal
  else sal end bonus from emp;


多列子查询
select employee_id,manager_id,department_id from employees where (manager_id,department_id) IN (select manager_id,department_id from employees where employee_id) IN(100,188))
   and employee_id NOT IN(166,155);
   
 exists操作符
  检查子查询中是否满足条件的行
  1--如果在子查询中存在满足条件的行,返回true,就不再子查询中继续查找
  2--如果子查询中不存在满足条件的行,返回false,继续在子查询中查找