oracle学习第四天

关联查询

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

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

查询某用户下某表的约束名称（在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 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 jion 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 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;

子查询
子查询在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 * from emp where rownum>3
解决方案:(必须使用子查询)
select * from (select rownum rm,e.* from emp e) where rm>3;

查询出emp表 第三条到第七条数据
select * from (select rownum rm,e.* from emp e) where rm between 3 and 7;

-------------------------------------------------------------------------------------------------------

使用子查询进行分页
分页策略,每次只取一页的数据,每次换页,取下一页的数据
在oracle中利用rownum的功能可用来进行分页,假设结果集共有105条数据,每页20条数据,那么,
page1:1-20 (currentpage-1)*pagesize+1 pagesize*currentpage
page2:21-40
...
pageN:(n-1)*20+1,n*20

按照薪水倒序排列,取出结果集中第8-10条记录
select * from (select rownum rm,sal from (select * from emp order by sal desc)) where rm between 8 and 10;

select * from (select rownum rn,e.* from (select * 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)
select decode(sal,MANAGER,1.2*sal,CLERK,1.1*sal,SALES,1.05*sal,default sal) from emp;//错的
select ename,job,sal,decode(job,'MANAGER',1.2*sal,'CLERK',1.1*sal,'SALESMAN',1.05*sal,sal) bonus from emp;

年薪的50% 办事员40% 销售30% 其他10%
select decode(job,'MANAGER',(sal+nvl(comm,0))*12*0.5,'CLERK',(sal+nvl(comm,0))*12*0.4,'SALESMAN',(sal+nvl(comm,0))*12*0.3,(sal+nvl(comm,0))*12*0.1) bonus from emp;

等价处理,
select ename,job,sal,
case job when 'MANAGER' then 1.2*sal
when 'CLERK' 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(110,188))
and employee_id NOT IN (166,155);

exists操作符
检查子查询中是否满足条件的行
1--如果在子查询中存在满足条件的行,返回true,就不再子查询中继续查找
2--如果子查询中不存在满足条件的行,就返回false,继续在子查询中查找

java和oracle连接,实现emp表分页查询