# 多表查询 ，内部联结，外部联结，自联结

1、单表查询

select…from…

where…

group by…

order by…

2、多表查询

1、内部联结

A、

select 列名,列名 from 表名，表名

where 条件

select ename,job,dname,loc from emp,dept where emp.deptno=dept.deptno;

select ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

select ename,job,dname,loc,e.deptno from emp e,dept d where e.deptno=d.deptno;

select ename,job,dname,loc,e.deptno from emp e,dept d where e.deptno=d.deptno and job=‘CLERK’;

B、

select 列名，列名 from 表1

inner join 表2 on 联结条件

select ename,job,dname,loc from emp

inner join dept on emp.deptno=dept.deptno;

select ename,job,dname,loc from emp

inner join fept on emp.deptno=dept.deptno where job=‘CLERK’;

select ename,job,dname,loc from emp,dept ;

inner join dept on emp.deptno=dept.deptno

inner join salgrade on sal between losal and hisal;

inner join dept on emp.deptno=dept.deptno

inner join salgrade on sal between losal and hisal

order by dname;

select grade,count(*) as 人数 from emp

inner join salgrade on sal between losal and hisal

select dname,avg(sal) from emp

inner join dept on emp.deptno=dept.deptno

group by dname;

2、外部联结

select * from user_table

create table student

(stuid number(10) not null,

stuname varchar(20) not null,

gender varchar(4) not null,

phone varchar(11) null

)

create table exam

(stuid number(10) not null,

subject varchar(30) not null,

score number(5) not null

)

insert into student values(100,‘张山’,‘男’);

insert into student values(101,‘里斯’,‘男’);

insert into exam values(100,‘JAVA’,95);

insert into exam values(102,‘JAVA’,95);

select stuname,subject,score from student

inner join exam on student.stuid=exam.stuid;

select stuname,subject,score from student

left outer join exam on student.stuid=exam.stuid;

select stuname,subject,score from student

right outer join exam on student.stuid=exam.stuid;

select stuname,subject,score from student

full join exam on student.stuid=exam.stuid;

select stuname,nvl(subject,‘缺考’),nal(score,0) from student

left outer join exam on student.stuid=exam.stuid;

select stuname,subject,score from student

left outer join exam on student.stuid=exam.stuid

where subject is null;

select e.ename,e.job,m.ename as 上司职位,m.job as 上司职位 from emp e

inner join emp m on e.mgr=m.empno；

select e.ename,e.job,m.ename as 上司职位,m.job as 上司职位 from emp e

left outer join emp m on e.mgr=m.empno；

06-14 796
01-11 3930
04-15 2329
05-13 6372
06-29 1240
05-11 988
03-25 336
06-04 147
10-18 73