一.连表查询
当要查询的数据来自于不同的表中,可以使用连表查询
1.92语法
表和表之间使用,连接
select 数据 from 数据来源1,数据来源2,数据来源3…
笛卡尔积 (对乘)
(1)内连接(内部做满足条件显示不满足不显示)和外链接(有的表中的数据不满足条件也可以显示)
连接条件: where中定义连接条件 等值连接 非等值连接
注意:同名字段必须指明出处
select 数据 from 数据来源1,… where 行过滤条件|表连接条件 group by 分组字段… having 组过滤条件 order by 排序字段…;
–执行流程: from —> where --> group by —> having —> select —> order by
--等值连接:两个表的连接字段,不定义是同名字段或者主外键关系的,数据类型需要保持一致
--查询员工的信息和所在的部门信息
select * from emp, dept where emp.deptno = dept.deptno;
select * from emp e, dept d where e.deptno = d.deptno;
select * from emp , dept where emp.ename = dept.dname;
--select 数据 from 数据来源1,.... where 行过滤条件|表连接条件 group by 分组字段.. having 组过滤条件 order by 排序字段..;
--执行流程: from --- where--group by ---having ---select ---order by
--查询30部门的员工信息和所在部门信息
select empno,ename,d.deptno,dname from emp e,dept d where e.deptno = d.deptno and e.deptno!=30 order by e.deptno desc; --先连接后判断,相对效率较低,连接数据多
--先过滤后连接
--select * from (30部门员工信息) e,(30部门的部门信息) d;
select * from (select * from emp where deptno = 30) e,(select * from dept where deptno=30) d;
--非等值连接
--查询2500工资所在的等级信息
select * from salgrade;
select grade from salgrade where 2500 between losal and hisal;
--查询的所有的员工的信息以及对应的薪资等级
--数据:员工信息,薪资等级
--来源: emp,salgrade
--条件:sal between losal and hisal;
select * from emp,salgrade where emp.sal between losal and hisal;
--30部门的,薪资>1500的员工信息,所在的部门信息,工资等级信息
select *
from emp e, dept d, salgrade s
where e.deptno = d.deptno
and sal between losal and hisal
and e.deptno = 30
and sal > 1500;
(2)外连接:左外连接和有外连接接
主表:主表中的数据无论是否满足条件都会显示
做表连接的时候,要满足连接条件才能显示,如果想要某张表中的数据无论是否满足条件都显示,可以使用外链接
查询所有员工信息以及上级经理人信息 员工表作为主表
emp e1,emp e2 主表在左边就是左连接,主表在右边就是右连接
-- emp e1,emp e2 主表在左边就是左连接,主表在右边就是右连接
select * from emp e1,emp e2 where e1.mgr= e2.empno(+); --左连接
select * from emp e2,emp e1 where e1.mgr= e2.empno(+); --右连接
2.99语法
select 数据 from 数据来源1 join 数据来源2;
笛卡尔积 (对乘) cross join
--select 数据 from 数据来源1 join 数据来源2;
--笛卡尔积 对乘 cross join
select * from emp e cross join dept d; --99
select * from emp,dept; --92
1)等值连接
自动做等值连接
注意: 同名字段不要使用限定词
(1)自然连接
natural join 自动帮你做等值连接 同名字段|主外键关系
--(inner) join 内连接
--等值连接
--自动做等值连接
--注意: 同名字段不要使用限定词
--自然连接 natural join 自动帮你做等值连接 同名字段|主外键关系
select empno,ename,deptno,dname from emp e natural inner join dept d;
--join using(字段) 指明对哪一个字段做等值连接
select empno,ename,deptno,dname from emp inner join dept using(deptno);
-- 数据来源1 join 数据来源2 on 连接条件(等值|非等值) 同名字段需要指明出处
select empno,ename,emp.deptno,dname from emp join dept on emp.deptno = dept.deptno;
--非等值连接
--员工信息和工资等级
select * from emp e inner join salgrade s on e.sal between losal and hisal;
select *
from emp
join dept
on emp.deptno = dept.deptno
join salgrade
on sal between losal and hisal
where emp.deptno = 30;
(2)外链接
left join | right join
--外链接 left join | right join
--主表
--所有员工信息和上级信息
select * from emp e1 left join emp e2 on e1.mgr = e2.empno;
--全连接
select 1 no, 'a' "name" from dual union select 2 no, 'b' "name" from dual;
select 1 no, 'c' "name" from dual union select 3 no, 'd' "name" from dual;
--内连接 满足条件显示
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;
--左外链接 a表中的数据满不满足条件都显示
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
left join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;
--右外链接 b表中的数据满不满足条件都显示
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
right join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;
--全链接 a,b表中的数据满不满足条件都显示
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;