Oracle数据库学习

Oracle数据库学习第三章

本章学习要点：

• 多表查询

• 分页查询

多表查询

1)子查询 - 将一个查询包含到另一个查询中
<1>如：EMP表中，查询与“SCOTT”在同一个部门的雇员信息
原来我们是先查询SCOTT所在的部门即：select deptno from emp where ename=’SCOTT’
然后通过返回的部门号在查找员工信息：select * from emp where deptno=20;
如果用子查询则是如下即一个查询中套用另一个查询其中(A) =代表是一个值
select * from emp where deptno=(select deptno from emp where ename=’SCOTT’)

<2>另一个例子：查询编号>7900的员工的职位相同的雇员信息
原来我们先查询 select job from emp where empno>7900;找出编号大于7900的员工职位，发现返回
的值有2个，这时我们发现无法再通过“=”去设置条件了
select * from emp where( job=ANALYST or CLERK)；这样是报错的，对于这种同一种类的条件我们不再使用“=而是用“in””修改后为
select *from emp where job in(select job from emp where empno>7900);

<3>EMP表中，查询雇员薪水大于3000的部门名称(部门名称位于DEPT表中)
select dname from dept where deptno in (select deptno from emp e where e.sal>3000)
select dname from dept d where exists (select * from emp where d.deptno = emp.deptno and sal>3000)
前者则是之前方法的再次使用，后者则是关键字“EXISTS”的运用，这个关键字判断，两表之间是否存在相同的部门序号切满足薪水大于3000，如若有则返回结果。

2)连 接 - 合并多个数据表中的列
(1)内连接
<1>语法
/* SELECT ……
FROM 表1 INNER JOIN 表2
ON ……*/

/* SELECT ……
FROM 表1，表2
WHERE ……*/

<2>等值连接
select * from emp; –14
select *from dept; –4
–等值是 14(由于有EMP为主，所以返回的是EMP中相同数据量)
select ename,job,sal,dept.dname from emp inner join dept on dept.deptno = emp.deptno
以EMP表为主，通过两表共有的DEPTNO来查询数据，返回共同数据
select ename,job,sal,dept.dname from emp , dept where dept.deptno = emp.deptno
以EMP表为主，通过两表共有的DEPTNO来查询数据，返回共同数据
<3> 不等值连接
–不等值是 =14*4-14=42（由于以EMP为主表，EMP中每条数据都要与DEPT中的4条数据做比较，上面等值的有14条，不等值的就是14*4-14=42）
“<>”,”!=”为不等号
select ename,job,sal,dept.dname from emp inner join dept on dept.deptno <> emp.deptno
select ename,job,sal,dept.dname from emp inner join dept on dept.deptno != emp.deptno
select ename,job,sal,dept.dname from emp , dept where dept.deptno != emp.deptno
<4>自然连接
–查询当前员工时领导
select distinct e1.ename from emp e1 inner join emp e2 on e1.empno = e2.mgr;(distinct过滤重复信息)
–简写:
select distinct e1.ename from emp e1, emp e2 where e1.empno = e2.mgr;

(2)外连接
1)左外连接

/* SELECT ……
FROM 表1 left JOIN 表2
ON ……*/
–以左表为基准,如果查询没有相关联的数据填充为Null值
–14
select * from emp left join dept on emp.deptno = dept.deptno;

–能交换表的位置 (会影响的查询结果)
–15
select * from dept left join emp on emp.deptno = dept.deptno;

2)右外连接
/* SELECT ……
FROM 表1 right JOIN 表2
ON ……*/
–15
select * from emp right join dept on emp.deptno = dept.deptno;

–能交换表的位置 (会影响的查询结果)
–14
select * from dept right join emp on emp.deptno = dept.deptno;

(3)自连接

(4)交叉连接

3)联 合 - 合并多个数据表中的行

集合操作符将两个查询的结果组合成一个结果 ===>表的结构必须相同,否则不能操作!!!
–复制新表
create table myemp as select * from emp where empno in (7788,7900);

select * from myemp; –2
–union 并集 (过滤重复的数据)—-14
select * from emp –14
union
select * from myemp;

–union all 并集 (所有的数据)——16
select * from emp –14
union all
select * from myemp; –2

–INTERSECT 交集 (取相同的部分)——2
select * from emp –14
intersect
select * from myemp; –2

–minus 补集 (14-2)
select * from emp –14
minus
select * from myemp; –2

分页查询

什么是Oracle伪列？

Oracle 中伪列就像一个表列，但是它并没有存储在表中
伪列可以从表中查询，但不能插入、更新和删除它们的值

常用的伪列有哪些？

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，
可以使用 ROWID 伪列快速地定位表中的一行

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数
select e.*,rownum from emp e;

–如何获取雇员表中薪水最高的前5人？
select ee.,rownum from (select from emp order by sal desc) ee
where rownum<=5;

SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE rownum<=10;
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE rownum>=3;
伪列出了可以大于等于1 其他都不能用大于

–分页
–1)每页显示5个 pageSize=5;
–2)总个数是14条 pageCount=14;
–3)总页数 3 pageTotal = (14%5==0)?(14/5):(14/5+1);
–4)当前页1 currentPage=1;

–第1页 rownum<=5 ==>pageSize*currentPage
select * from
(select ee.,rownum r from (select from emp order by sal desc) ee where rownum<=5 )
where r>0 –r>0 ==>(currentPage-1)*pageSize

–第2页
select * from
(select ee.,rownum r from (select from emp order by sal desc) ee where rownum<=10 )
where r>5

–第3页
select * from
(select ee.,rownum r from (select from emp order by sal desc) ee where rownum<=15 )
where r>10