oracle职工工资数据表四表联动,oracle 学习之基础篇(四)：多表查询

员工表emp和部门表dept的笛卡尔集(笛卡尔集表=列数之和，行数之积，笛卡尔集表内中有些数据是不符合要求的)select emp.ename,dept.dname

from emp,dept;

使用等值连接/内连接(只能使用=号)，显示员工的编号，姓名，部门名，使用表别名简化select emp.empno,emp.ename,dept.dname,dept.deptno

from emp,dept

where emp.deptno = dept.deptno;

使用非等值连接(不能使用=号，其它符号可以，例如：>=，<=，<>，betwen and等)，显示员工的编号，姓名，月薪，工资级别select e.empno,e.ename,e.sal,s.grade

from emp e,salgrade s

where e.sal between s.losal and s.hisal;内连接查询：只能查询出符合条件的记录外连接查询：既能查询出符合条件的记录，也能根据一方强行将另一个方查询出来

使用外连接，按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数部门号 部门名 人数

10 ACCOUNTING 3

20 RESEARCH 5

30 SALES 6

40 OPERATIONS 0

左外连接[是oracle专用的，不是SQL99规则]：

select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"

from dept,emp

where dept.deptno = emp.deptno(+)

group by dept.deptno,dept.dname;

右外连接：

select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"

from dept,emp

where emp.deptno(+) = dept.deptno

group by dept.deptno,dept.dname;等值连接/非等值连接/内连接：只会查询出多张表中，根据某个字段匹配，符合条件的记录，不符合条件的记录是不会存在的

使用左外连接，按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数，且按人数降序排列select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"

from dept,emp

where dept.deptno = emp.deptno(+)

group by dept.deptno,dept.dname

order by 3 desc;

使用自连接，显示”SMITH的上级是FORD”这种格式select users.ename || '的上级是' ||boss.ename

from emp users,emp boss

where users.mgr = boss.empno;

--只有13条记录，不含有KING

基于上述问题，将KING的上级是“”显示出来select users.ename || '的上级是' ||boss.ename

from emp users,emp boss

where users.mgr = boss.empno(+);

14条记录

--注意：自连接也用到内连接和外连接

oracle 子查询

查询工资比WARD高的员工信息

第一：查询WARD的工资?select sal from emp where ename = 'WARD';

第二：查询工资比1250高的员工信息？select * from emp where sal > 1250;

子查询：select *

from emp

where sal > (

select sal

from emp

where ename = 'WARD'

);

查询部门名为’SALES’的员工信息(方式一：子查询)

第一：查询部门名为’SALES’的编号？select deptno from dept where dname = 'SALES';

第二：查询部门号为30的员工信息？ select * from emp where deptno = 30;

子查询：select *

from emp

where deptno = (

select deptno

from dept

where dname = 'SALES'

);

查询部门名为’SALES’的员工信息(方式二：多表查询)select emp.*

from dept,emp

where (dept.deptno=emp.deptno) and (dept.dname='SALES');

查询每个员工编号,姓名，部门名,工资等级(三表查询，这三张表并无外健关联)select e.empno,e.ename,d.dname,s.grade

from emp e,dept d,salgrade s

where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

查询工资最低的员工信息(单行子查询，使用=号)

第一：查询出工资最低是多少？select min(sal) from emp;

第二：查询工资为800的员工信息？select * from emp where sal = 800;

子查询：select *

from emp

where sal = (

select min(sal)

from emp

);

查询部门名为’ACCOUNTING’或’SALES’的员工信息(多行子查询，使用in关键字)

第一：查询部门名为’ACCOUNTING’或’SALES’的部门编号？select deptno from dept where dname in ('ACCOUNTING','SALES');

第二：查询部门号为10或30号的员工信息？select * from emp where deptno in (10,30);

子查询：select *

from emp

where deptno in (

select deptno

from dept

where dname in ('ACCOUNTING','SALES')

);

查询工资比20号部门【任意any】一个员工工资【低

第一：查询20号部门的所有工资？select sal from emp where deptno = 20;

第二：查询工资比(800,2975,3000,1100,3000)任意一个低的员工信息？select * from emp where sal < any (800,2975,3000,1100,3000);在oracle看来，

子查询：select *

from emp

where sal

select sal

from emp

where deptno = 20

);

查询工资比30号部门【所有all】员工【低

第一：查询出30部门所有员工的工资？select sal from emp where deptno = 30;

第二：查询工资比(1600,1250,1250,2850,1500,950)中所有的工资都低的员工信息？select * from emp where sal

子查询：select *

from emp

where sal

select sal

from emp

where deptno = 30

);

oracle 集合查询

使用并集运算，查询20号部门或30号部门的员工信息select * from emp where deptno = 20

union

select * from emp where deptno = 30;

注意：

union：二个集合中，如果都有相同的，取其一

union all：二个集合中，如果都有相同的，都取

使用set time/timing on，打开时间的开关set time on;

set time off;

使用set tim/timing off，关闭时间的开关set timing on;

set timint off;

使用交集运算[intersect]，查询工资在1000-2000和1500-2500之间的员工信息(方式一)select * from emp where sal between 1000 and 2000

intersect

select * from emp where sal between 1500 and 2500;

用where行过滤，查询工资在1000-2000和1500-2500之间的员工信息(方式二)select *

from emp

where (sal between 1000 and 2000) and (sal between 1500 and 2500);

使用差集运算[minus]，查询工资在1000-2000，但不在1500-2500之间的员工信息(方式一)select * from emp where sal between 1000 and 2000

minus

select * from emp where sal between 1500 and 2500;

使用where行过滤，查询工资在1000-2000，但不在1500-2500之间的员工信息(方式二)select *

from emp

where (sal between 1000 and 2000) and (sal not between 1500 and 2500);

集合查询的细节：

1)集合操作时，必须确保集合列数是相等select empno,ename,sal,comm from emp where deptno = 20

union

select empno,ename,sal from emp where deptno = 30;错

2)集合操作时，必须确保集合列类型对应相同select empno,ename,sal,comm from emp where deptno = 20

union

select empno,ename,sal,hiredate from emp where deptno = 30;错

3)A union B union C = C union B union Aselect * from emp where deptno = 10

union

select * from emp where deptno = 20

union

select * from emp where deptno = 30;

4)当多个集合操作时，结果的列名由第一个集合列名决定select empno "编号",ename "姓名",sal "薪水" from emp where deptno = 20

union

select empno,ename,sal from emp where deptno = 10;当多表查询，子查询，集合查询都能完成同样任务时，按如下优化方案选择：多表查询->子查询->集合查询

hogen

2017-09-09