oracle-高级查询

Oracle中的复杂查询

一 多表查询

         多表查询：是指基于两个和两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足你的需求，在这种情况下需要使用到多张表一起进行查询。

    语法：

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…]

FROM 表名称 [别名], [表名称 [别名] ,…]

[WHERE 条件(S)]

[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];

 

         示例：查询雇员名，雇员工资及所在的部门的名字。

1）select * from emp,dept

 

 

分析：

（1）select * from emp 查询结果有14条数据。

（2）select * from dept查询结构有4条数据。

（3）通过上图，select * from emp,dept 查询结果有14*4条数据。

         上述操作称为笛卡尔积，即在进行多表查询的时候不带任何条件。如何消除笛卡尔积，最简单的方式是采用关联字段的形式，emp表和dept表之间现在存在了deptno的关联字段，所以现在可以从这个字段上的判断开始。

         PS：

　　  A．避免笛卡尔集，多表查询的条件至少不能少于（表的个数-1）

         B．一般采用关联字段进行判断

（4）通过分析，需要在查询语句中添加条件：

select * from emp,dept where emp.deptno=dept.deptno。

PS：当在查询之中，不同的表中有了相同字段名称的时候，访问这些字段必须加上表名称，即“表.字段”。

select * from emp,dept where emp.deptno=dept.deptno

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno

为表起别名

select ename,sal,dname from emp e,dept d where e.deptno=d.deptno

解题思路：

范例：查询出每一位雇员的编号、姓名、职位、部门名称、位置

1、确定所需要的数据表：

emp表：可以查询出雇员的编号、姓名、职位；

dept表：可以查询出部门名称和位置；

2、确定表的关联字段：emp.deptno=dept.deptno；

第一步：查询出每一位雇员的编号、姓名、职位

SELECT e.empno, e.ename, e.job

FROM emp e;

第二步：为查询中引入部门表，同时需要增加一个消除笛卡尔积的条件

SELECT e.empno, e.ename, e.job, d.dname, d.loc

FROM emp e, dept d

WHERE e.deptno=d.deptno;

问题：查询员工表中，每个员工的工资等级，列出员工姓名，工资和工资等级

select e.ename,e.sal,s.grade from emp e,salgrade s

where e.sal between s.losal and s.hisal;

自连接

查询所有比自己领导入职早的员工的姓名和上级领导的姓名

select w.ename as 员工姓名,m.ename as 上级领导姓名

from emp w,emp m where w.mgr=m.empno and w.hiredate<m.hiredate;

1.1 内连接查询（inner join）

内连接查询：列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。

语法： 

select 列名1,.. from 表1 [inner] join 表2 on 条件 where 条件;

 注意：on后面是连接条件

 内连接分2种：

（1）等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

查询出每一位雇员所有信息及所在部门信息

select  *  from emp e  join  dept  d   on  e.deptno=d.deptno

 

（2）不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

    select * from emp e inner join dept d on e.deptno>d.deptno;

 

问题：查询每个员工的部门名称，列出员工姓名和部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

问题：查询部门10中每个员工的工资等级

select e.ename,s.grade from emp e

inner join salgrade s

on e.sal between s.losal and s.hisal --连接条件

where e.deptno=10;                   --查询条件

问题：查询emp表中部门名称为ACCOUNTING的员工的姓名，和部门位置

自然连接

select e.ename,d.loc from emp e,dept d

where e.deptno=d.deptno and d.dname='ACCOUNTING';

内连接

select e.ename,d.loc from emp e inner join dept d

on e.deptno=d.deptno

where d.dname='ACCOUNTING';

 

问题：查询高于自己部门平均工资的员工的信息，列出部门平均工资

--（1）获得每个部门的平均工资

select deptno,avg(sal) avg_sal from emp group by deptno;   --x

--(2) 将x表和emp表做表连接

select e.*,x.* from emp e,x where e.deptno=x.deptno and e.sal>x.avg_sal;

--(3)替换x

select e.*,x.* from

emp e,(select deptno,avg(sal) avg_sal from emp group by deptno)x

where e.deptno=x.deptno and e.sal>x.avg_sal;

 

1.2 自然连接查询（natural join）

自然连接是在笛卡尔积R×S中选出同名属性上符合相等条件元组，再进行投影，去掉重复的同名属性，组成新的关系。

语法：

select 列名1,.. from 表1 natural  join 表2  where 条件;

 

示例：

查询出每一位雇员所有信息及所在部门信息

select * from emp natural join dept; 自连接查询

 

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。

示例：

查询所有员工名字及其上级的名字。

 

 

select e1.ename,e2.ename manager from emp e1,emp e2 where e1.mgr=e2.empno;

查询'FORD'的上级信息

select e2.* from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename=’FORD’;

 

1.3 外连接查询（outer join）

外连接指返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接))、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。

外连接分为三种：左外连接、右外连接和完全外连接。

下面示例以该表为基础：

 

1.3.1 左外连接(left join)

概念：返回包括左表中的所有记录和右表中联结字段相等的记录。

语法：

select 列名1,列名2,.. from 表1 left [outer] join 表2 on 条件;

或者

select列名1,列名2,.. from 表1,表2 where 条件1=条件2(+);

 

示例：

查询所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空

select stu.id,name,grade from stu left join exam on stu.id=exam.id;

select stu.id,name,grade from stu,exam where stu.id=exam.id(+);

 

 

1.3.2 右外连接(right join)

概念：返回包括右表中的所有记录和左表中联结字段相等的记录；

语法：

select 列名1,列名2,.. from 表1 right [outer] join 表2 on 条件;

或者

select列名1,列名2,.. from 表1,表2 where 条件1(+)=条件2;

 注意：左表 right join 右表

示例：

查询所有成绩，如果没有名字匹配，显示空。

select stu.id,name,grade from stu right join exam on stu.id=exam.id;

select stu.id,name,grade from stu,exam where stu.id(+)=exam.id;

 

1.3.3 完全外连接（full join）

概念：返回查询结果等于左外连接和右外连接的和。

语法：

select 列名1,列名2,.. from 表1 full [outer] join 表2 on 条件;

示例：

查询所有成绩，如果没有名字匹配，显示空。

select stu.id,name,grade from stu full join exam on stu.id=exam.id;

 

二 子查询

子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询；分为单行子查询、多行子查询、多列子查询。

2.1 单行子查询

单行子查询是指只返回一行数据的子查询语句。

示例：

1.查询公司之中工资最低的雇员的完整信息

select * from emp

where sal=(select min(sal) from emp);

2.查询与SMITH同一部门的所有员工

select * from emp

where deptno=(select deptno from emp where ename='SMITH');

问题：查询和scott工作相同的员工姓名，不包含scott在内

select ename from emp where

job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';

查询所有在ACCOUNTING部门的员工的工号和姓名，按照姓名升序排列

select empno,ename from emp

where deptno=(select deptno from dept where dname='ACCOUNTING')

order by ename asc;

 

--查询blake的上级领导的姓名和工资

select ename,sal from emp

where empno=(select mgr from emp where ename='BLAKE');

查询薪水高于部门30的最低薪水的员工信息

select * from emp where sal>(select min(sal) from emp where deptno=30);

查询哪些部门最低薪水高于部门30的最低薪水，列出这些部门薪水最低的员工信息

--(1)查询哪些部门最低薪水高于部门30的最低薪水  --x

select deptno,min(sal) from emp

group by deptno

having min(sal)>(select min(sal) from emp where deptno=30);

--(2)将emp表与x做表连接

select e.* from emp e left join x on e.deptno=x.deptno;

--(3)替换：

select e.*,x.* from

(select deptno,min(sal) min_sal from emp

group by deptno

having min(sal)>(select min(sal) from emp where deptno=30))x

inner join emp e

on e.sal=x.min_sal;

 

--查询和SALESMAN同部门，但是不是SALESMAN的员工的信息

select * from emp where

deptno in (select distinct deptno from emp where job='SALESMAN')

and job<>'SALESMAN';

         练习：

       （1）查询与SMITH同一部门的所有员工（不包括SMITH本人）

       （2）查询出基本工资比ALLEN低的全部雇员信息

2.2 多行子查询

         多行子查询指返回多行数据的子查询。主要使用3种操作符：in、all、any，其中ALL和ANY运算符必须与比较运算符（=,>,>=,<,<=,<>）结合使用。

多行子查询中使用in操作符（等于任何一个）

         示例：

       查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

select ename,job,sal,deptno from emp

where job in (select distinct job from emp where deptno=10);

--ANY

-->any:大于最小的

--<any：小于最大的

--查询比任何一个SALESMAN的薪水高但不是SALESMAN的员工信息。

select * from emp where sal>any(select sal from emp where job='SALESMAN') and job<>'SALESMAN';

 

--all

-->all:大于最大的

--<all:小于最小的

--查询比所有一个SALESMAN的薪水高但不是SALESMAN的员工信息。

select * from emp where sal>all(select sal from emp where job='SALESMAN') and job<>'SALESMAN';

select * from emp where sal>(select max(sal) from emp where job='SALESMAN') and job<>'SALESMAN';

多行子查询中使用all操作符（和所有值比较）

         示例：

         查询工资比部门30的所有员工的工资高的员工信息

select * from emp where sal >all(select sal from emp where deptno=30);

或

select * from emp where sal > (select max(sal) from emp where deptno=30);

 多行子查询中使用any操作符（和任何值比较）

         示例：

         查询工资比部门30的任意一个员工的工资高的员工信息

select * from emp where sal >any(select sal from emp where deptno=30);

或

select * from emp where sal >(select min(sal) from emp where deptno=30);

2.3 多列子查询

         多列子查询指查询返回多个列数据的子查询语句。

    示例：

         查询与smith的部门和岗位完全相同的所有雇员

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

2.4 在from子句中使用子查询

当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，而且必需给予查询指定别名。

示例：

如何显示高于自己部门平均工资的员工的信息？

（1）查询每个部门的平均工资

select avg(sal) myavg, deptno from emp group by deptno

（2）将（1）查询结果作为一个临时表

select e.*,t1.myavg

from emp e, (select avg(sal) myavg, deptno from emp group by deptno) t1

where e.deptno = t1.deptno and e.sal > t1.myavg

order by e.deptno;

         思考：查询每个部门工资最高的员工信息？

三 合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符union,union all,intersect,minus

 

3.1 union取并集

union操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

示例：

select ename,sal,job from emp where sal>3500 union

select ename,sal,job from emp where job='MANAGER';

 

 

 

3.2 union all取所有

union all操作符与union相似，但是它不会取消重复行，而且不会排序。

示例：

select ename,sal,job from emp where sal>3500 union all

select ename,sal,job from emp where job='MANAGER';

 

 

3.3 intersect取交集

intersect操作符用于取得两个结果集的交集。

select ename,sal,job from emp where sal>3500 intersect

select ename,sal,job from emp where job='MANAGER';

3.4 minus取差集

minus操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。

select ename,sal,job from emp where sal>3500 minus

select ename,sal,job from emp where job='MANAGER';

五 分页查询

5.1 Oracle分页查询

   oracle中的表，除了我们建表时设计的各个字段，其实还有两个字段分别是ROWID（行标示符）和ROWNUM（行号），即使我们使用DESCRIBE命令查看表的结构，也是看不到这两个列的描述的，因为，他们其实是只在数据库内部使用的，所以也通常称他们为伪列（pseudo column）。

rownum：顾名思义就是行数/行号；它会根据返回记录生成一个序列化的数字。

rowid：就是编码/编号/唯一识别号，所以他是类似“AAAR8gAAEAAAAErAAK”的编号，注意它是没有先后顺序的，也就是说它和数据入库时间没有任何关系。rowid我们一般用不到，Oracle数据库内部使用它来存储行的物理位置，是一个18位的数字，采用base-64编码。

下面以emp表为例分析分页查询。

问题1：对emp表中所有雇员信息以分页的形式显示，每页显示3条数据，现在查询第2页数据。

         分析：每页显示3条数据，现查询第2页数据，也就是查询第4条-第6条数据。

         select * from emp where rownum<=6 and rownum >=4;

       查询之后没有发现任何结果，为什么呢？

原因很简单，Oracle机制就是这样的：ROWNUM是oracle系统顺序分配给查询返回的结果的行的编号，返回的第一行分配的是1，不符合>=4的条件，所以第一行被去掉，之前的第二行变为新的第一行（即这个行号不是写死的，可以理解为是动态的），如此下去，一直到最后一行，条件始终没法满足，所以就一条数据也查不出来。

因此，解决方法也就很明显了，我们只要将行号查询出来生成一个结果集，然后再从这个结果集中，选择行号大于我们设定的那个值就可以了。

select * from (select emp.*,rownum rn from emp where rownum<=6) e where e.rn>=4

 

 

问题2：查询emp表中雇员工资最高前5位

select emp.*,rownum from emp where rownum<=5 order by sal desc;

查询之后发现，结果并不正确，为什么呢？

原因是：使用ROWNUM时，只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM。而对非主键字段OBJECT_NAME进行排序时，oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序。

因此，我们只有先按照排序生成一个临时表，然后再从这个临时表中选择行号。

select e.*,rownum rn from (select * from emp order by sal desc) e where rownum<=5;

 

问题3：结合1和2，将emp表按照工资从高到低排序，每页显示3条数据，现查询第3页信息

select * from

(select e1.*,rownum rn from

(select * from emp order by sal desc) e1

where rownum<=9) e2

where e2.rn>=7;

总结以上3个问题，得出oracle分页查询语法模版：

select t2.*  from

 ( select t1.*, rownum rn  from

(select *  from 表名)  t1

where rownum<=大范围(取到多少条数据) ) t2

where rn>=小范围(从第几条数据开始取);

PS：

oracle分页查询是通过三层筛选法进行查询的。每一次都可以带where条件来对要查询的信息进行筛选。

5.2 mysql分页查询

语法：

select * from 表名 where 条件 limit start,count;

解析：

start：从第几条开始

count：取几条数据

练习题

1. 1.     根据下列表回答问题。（共25分）

员工表(emp)结构如下：

 

Empno（主键）	Ename	Job	Mgr	Hiredate	Sal	Comm	Deptno
员工编号	姓名	岗位	上级领导编号	入职日期	基本工资	奖金	部门编号

 

部门表(dept)结构如下：

 

Deptno	Dname	Loc
部门编号	部门名称	部门位置

 

数据如下：

 

（1）   查询所有在“ACCOUNTING”部门的员工号、姓名，并且按姓名升序排序。（3分）

子查询：Select  empno,ename from emp where deptno=(select deptno from dept where dname=’Accounting’) order by name;

内连接：select empno, ename from emp e, dept d where e.deptno = d.deptno and d.dname = 'ACCOUNTING' order by ename;

（2）   将emp表中添加一个字段Eremark,字段的数据类型为varchar2(40)（3分）

Alter table emp add eremark varchar2(40);

（3）   给每个工资在2000元到3000元的员工增加1000元工资。（3分）

Update emp set sal=sal+1000 where sal between 2000 and 3000;

（4）   查询出和10号部门员工工作岗位相同的雇员的名字、岗位、工资和部门号。（4分）

Select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);

（5）   查询高于自己部门平均工资的员工信息，并显示出平均工资。（6分）

子查询：Select emp.*,e1.asa from emp join (select avg(sal) asa,deptno from emp group by deptno) e1 on emp.deptno=e1.deptno where emp.sal>e1.asa;

连接：select e.*, s.a  from emp e, (select avg(sal) a , deptno from emp group by deptno ) s where s.deptno = e.deptno and e.sal > s.a;

 

（6）   查询所有比自己领导入职早的员工的姓名及其上级领导的姓名。（6分）

select e.ename, w.ename from emp e, emp w where e.mgr = s.empno and e.hiredate < w.hiredate;