Home

hogen

Follow Excellence. Success will chase you.

Home About Github Email

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】一个员工工资【低<】的员工信息(多行子查询,使用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看来,<any就等于<集合中最大的那个值

子查询:

select * 
from emp 
where sal <any (
              select sal 
              from emp 
              where deptno = 20
            ); 

查询工资比30号部门【所有all】员工【低<】的员工信息(多行子查询,使用all关键字)

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

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

子查询:

select * 
from emp 
where sal <all (
              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 A

select * 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
Home About Github Email