oracle04--查询

关联查询

笛卡尔积
指做关联操作的每个表的每一行都和其他表的每一行组合,假设两个表的记录数分别是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 l right full[outer] join table2
on table1.column1 = table2.column2;

select e.ename, e.job, d.dname, d.loc
from emp e left 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;


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

子查询
子查询在where子句中
在select查询中，在where查询条件中的限制条件不是一个确定的值而是来自于另外一个查询的结果
为了给查询提供数据而首先执行的查询语句叫子查询
子查询是嵌入在其他sql语句中的select语句，大部分时候出现在where子句中
子查询嵌入的语句称作主查询或者父查询
主查询可以是select语句，也可以说是其他的sql语句中

根据返回结果的不同，子查询可以分为单行子查询、多行子查询和多列子查询

查找和SCOTT同职位的员工
select * 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 ename
from (select rownum rm, e.* from emp e)
where rm>3;

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

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

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

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

查询emp表，根据职员的职位计算奖金额，管理者1.2倍工资、办事员1.1倍工资、销售员1.05倍工资，
如果不是这三个职位，则取工资值

年薪的50%, 40%, 30%, 10%
select ename,job,sal,decode(job,'MANAGER',(sal*12+decode(comm,null,0,comm))*0.5,'CLERK',(sal*12+decode(comm,null,0,comm))*0.4,'SALESMAN',(sal*12+decode(comm,null,0,comm))*0.3,(sal*12+decode(comm,null,0,comm))*0.1) bonus from emp;


decode函数解决此类问题
decode(column,search1,result2,search2,result2...dafault_result)
select ename,job,sal,decode(job,'MANAGER',sal*1.2,'CLERK',sal*1.1,'SALESMAN',sal*1.05,sal) bonus from emp;
等价处理
select ename,job,
    case job when 'MANAGER' then sal*1.2
            when 'CLERK' then sal*1.1
            when 'SALESMAN' then sal*1.05
            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));
    
exists操作符
检查子查询中是否满足条件的行
1--如果在子查询中存在满足条件的行，返回true，就不再子查询中继续查找
2--如果子查询中不存在满足条件的行，返回flase，继续在子查询中查找


Oracle(thin)数据库连接:
1.Class.forName("oracle.jdbc.driver.OracleDriver");
2.DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:[DataBase Name]","scott","tiger");
本机专用:
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");

Oracle(OCI driver)数据库连接:
1.Class.forName("oracle.jdbc.driver.OracleDriver");
2.DriverManager.getConnection("jdbc:oracle:oci8:@localhost:1521:[DataBase Name]","scott","tiger");