Oracle第六天 表链接

多表连接

左外连接的+写在右边

SQL>select e.last_name,  d.deparment_name from employees  e, department d  where

右外连接的+写在左边

SQL>select e.last_name,  d.deparment_name from employees  e, department d  where  

 

Student                             score

Stuo_no  sname             stu_no    score

30                                     28

 

 

From students,score

Where s.no(+)=s.no

 

 

 

emp： dept:

empno ename deptno deptno dname

100 abc 10 10 sales

101 def 10 20 market

102 xyz 20 30 it

103 opq null

 

for emp in 100 .. 103

  for dept in 10 .. 30

    emp.deptno=dept.deptno

 

100         abc         10              10          sales

101         def         10              10          sales

102         xyz         20              20          market

 

 

订单表:

CustID  StoreID     ProdID  ChannelID

100 S100        P100    C100

 

客户表：

CustID  name  creditlevel

100         abc   

 

地址表：

CustID  adress

100         bj

100         tj

 

获取如下信息，准备工作：

employees:

员工总数：107

SQL> select count(*) from employees;

有部门的员工数：106

SQL> select count(*) from employees where department_id is not null;

SQL> select count(department_id) from employees;

没有部门的员工数：1

SQL> select count(*) from employees where department_id is null;

 

departments：

部门总数：27

SQL> select count(*) from departments;

有员工的部门数：11

SQL> select count(distinct department_id) from employees;

没有员工的部门数：16

SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);

 

for dept in 1..27

  for emp in 1..107

   dept.deptid不在emp表中出现

 

select count(*)

from employees e, departments d

where e.department_id(+)=d.department_id

and e.employee_id is null;

 

select count(*)

from departments d

where not exists

(select 1 from employees where department_id=d.department_id);

 

select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;

 

内连接：106(106, 11)

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id;

 

select e.last_name, d.department_name

from employees e join departments d on e.department_id=d.department_id;

 

左外连接：107（106+1）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from departments d, employees e

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from employees e left outer join departments d

on e.department_id=d.department_id;

 

右外连接：122（106+16）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id;

 

完全外连接：123（106+1+16）

select e.last_name, d.department_name

from employees e full outer join departments d

on e.department_id=d.department_id;

 

多表连接的扩展：

n张表连接：

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

 

select e.last_name, d.department_name, l.city

from employees e join departments d on e.department_id=d.department_id

               join locations l on d.location_id=l.location_id;

 

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id(+)

and d.location_id=l.location_id(+);

 

select e.last_name, d.department_name, l.city

from employees e left outer join departments d on e.department_id=d.department_id

               left outer join locations l on d.location_id=l.location_id;

 

练习：

查询所有员工姓名，部门名称，部门所属城市(city)，国家(country)和区域(region)名称，对于空值用“无”代替。(N/A)

（使用oracle和sql99的语法）

select e.last_name, d.department_name, l.city, c.country_name, r.region_name

from employees e, departments d, locations l, countries c, regions r

where e.department_id=d.department_id(+)

and d.location_id=l.location_id(+)

and l.country_id=c.country_id(+)

and c.region_id=r.region_id(+);

 

select e.last_name e.last_name, d.department_name, l.city, c.country_name, r.region_name

from employees e

left outer join departments d on e.department_id=d.department_id

left outer join locations l on d.location_id=l.location_id

left outer join countries c on l.country_id=c.country_id

left outer join regions r on c.region_id=r.region_id;

 

自连接：

empid ename mgrid

100 abc

101 def 100

102 xyz 100

 

emp: mgr:

empid ename mgrid empid mgrname

100 abc 100 abc

101 def 100

102 xyz 100

 

 

101 def 100 100 abc

102 xyz 100 100 abc

 

select emp.ename, mgr.mgrname

from emp, mgr

where emp.mgrid=mgr.empid

 

emp: mgr:

empid ename mgrid empid ename mgrid

100 abc 100 abc

101 def 100 101 def 100

102 xyz 100 102 xyz 100

select e.last_name, m.last_name

from employees e, employees m

where e.manager_id=m.employee_id;

 

有经理的员工数：106

SQL> select count(*) from employees where manager_id is not null;

没有经理的员工数：1

SQL> select count(*) from employees where manager_id is null;

练习：

显示所有员工姓名和经理姓名，没有经理的显示“无”。

select e.last_name, nvl(m.last_name, 'N/A')

from employees e, employees m

where e.manager_id=m.employee_id(+);

 

不等值连接：

conn scott/tiger

select e.ename, sg.grade

from emp e, salgrade sg

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

 

练习：

找出工资大于所在部门平均工资的员工姓名。

create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;

select e.last_name, e.salary, asd.avg_sal

from employees e, avg_sal_dept asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

 

select e.last_name, e.salary, asd.avg_sal

from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;