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

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 联结条件

表1，表2可互换位置，不影响结果，顺序不一样

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 ;

每一个表都与另一个表匹配一次

查工资等级

select ename,job,sal,grade,dname from emp

inner join dept on emp.deptno=dept.deptno

inner join salgrade on sal between losal and hisal;

再筛选排序

select ename,job,sal,grade,dname from emp

inner join dept on emp.deptno=dept.deptno

inner join salgrade on sal between losal and hisal

where grade>=2

order by dname;

统计每个工资级别都有多少人

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

inner join salgrade on sal between losal and hisal

group by grade;

每个部门平均工资

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；