Oracle从多个表中获取数据
1.1 【表连接】1.2 【笛卡尔积:交叉连接】1.3 【使用表的别名】1.4 【连接的类型】1.5 【等连接】1.5.1 自然连接
1.6 【非等连接】1.7 【外连接】1.7.1 【环境准备】1.7.2 【左外连接】1.7.3 【右外连接】1.7.4 【全外连接】
1.8 【自连接】1.9 【多表关联】【总结】
1.1 【表连接】
需求:查询员工的姓名、部门号、部门名称。
-- 查询员工的姓名、部门号、部门名称。
SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept
2 where emp.deptno=dept.deptno;
注意:同一列名多表使用时,要写上表名做前缀。
SCOTT@orcl>seelct ename,deptno,dname from emp,dept where emp.deptno=dept.deptno;
列定义模糊
1.2 【笛卡尔积:交叉连接】
![](https://img-blog.csdnimg.cn/img_convert/9eabf11fdd62bf0b2050c8360fb71ec8.png#averageHue=#f8f8f7&id=oEsoY&originHeight=248&originWidth=654&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept;
![](https://img-blog.csdnimg.cn/img_convert/b1beb84f6aba77a6f10cc8bc25a497dc.png#averageHue=#080605&id=VTtOa&originHeight=386&originWidth=905&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 表连接不要忘记写 **where **条件。
1.3 【使用表的别名】
![](https://img-blog.csdnimg.cn/img_convert/072993cd2800dcec67ae7e2c5c49d915.png#averageHue=#f5f5f5&id=FjTmW&originHeight=302&originWidth=650&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d
2 where e.deptno=d.deptno;
![](https://img-blog.csdnimg.cn/img_convert/6c57497a3b1ff702e10465b5b774e292.png#averageHue=#070605&id=huwr3&originHeight=479&originWidth=870&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
1.4 【连接的类型】
分类标准不同
1.5 【等连接】
传统 **Oracle **连接语法:
SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept
2 where emp.deptno=dept.deptno;
**SQL1999 **语法(标准 SQL)
SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname
2 from emp inner join dept
3 on ( emp.deptno=dept.deptno);
![](https://img-blog.csdnimg.cn/img_convert/b1ade6a4b8a5910694aad5456cedea64.png#averageHue=#070605&id=FBIPH&originHeight=500&originWidth=844&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept,dname
2 from emp inner join dept
3 using(deptno);
连接列名称相同时,可以使用 using,但列名前不能加修饰
SCOTT@orcl>select emp.ename,deptno,dept.dname
2 from emp inner join dept
3 using(deptno);
![](https://img-blog.csdnimg.cn/img_convert/5ff294738e40dcda60f8c488c47ba336.png#averageHue=#070605&id=FmY46&originHeight=500&originWidth=712&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
1.5.1 自然连接
![](https://img-blog.csdnimg.cn/img_convert/d08be5e9667aac31648707b4dc320ca5.png#averageHue=#f5f4f4&id=rsk2g&originHeight=239&originWidth=668&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select emp.ename,deptno,dept.dname
2 from emp natural join dept;
![](https://cdn.nlark.com/yuque/0/2023/png/300262/1681183720179-df24b1c2-ab56-4525-890c-1a06d1029548.png#averageHue=%23080706&id=ZpDJ7&originHeight=473&originWidth=672&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
1.6 【非等连接】
需求:查询员工的工资以及对应的工资等级
-- 查询员工的工资以及对应的工资等级
SCOTT@orcl>select e.empno,e.ename,e.sal,s.losal,s.grade
2 from emp e,salgrade s
3 where e.sal>=s.losal and e.salselect e.empno,e.ename,e.sal,s.losal,s.hisal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
![](https://img-blog.csdnimg.cn/img_convert/5ec33bf3a7c6eff5e95e71b78b368710.png#averageHue=#080706&id=GH4D5&originHeight=500&originWidth=861&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select e.ename,e.sal,s.losal,s.hisal,s.grade from emp e join salgrade s
2 on (e.sal between s.losal and s.hisal)
3 where deptno in(10,20);
1.7 【外连接】
![](https://img-blog.csdnimg.cn/img_convert/a4a2271203a13aff2cef23c1071c9e33.png#averageHue=#f4f3f3&id=ORz1q&originHeight=321&originWidth=677&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
1.7.1 【环境准备】
1.7.2 【左外连接】
需求:列出所有员工及其对应部门信息, 包括没有部门的员工。传统 **Oracle **连接语法:
SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno(+);
**SQL:1999 **语法:
SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
2 from emp e left outer join dept d on(e.deptno=d.deptno);
1.7.3 【右外连接】
需求:列出所有部门及其对应的员工信息, 包括没有员工的部门。传统 **Oracle **连接语法
SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d
2 where e.deptno(+)=d.deptno;
**SQL:1999 **语法:
SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
2 from emp e right outer join dept d on(e.deptno=d.deptno);
1.7.4 【全外连接】
需求:查询员工对应的部门信息, 包括没有部门的员工和没有员工的部门
SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
2 from emp e full outer join dept d
3 on (e.deptno=d.deptno);
1.8 【自连接】
(表里有从属关系)需求:查询员工的姓名以及其经理的姓名员工的经理号 **= **经理的员工号
SCOTT@orcl>select e.empno,e.ename,e.mgr,m.empno,m.ename
2 from emp e,emp m
3 where e.mgr=m.empno;
![](https://img-blog.csdnimg.cn/img_convert/e4294fea6b2d1a6ea7d1427fe708d322.png#averageHue=#090807&id=s4Wxf&originHeight=478&originWidth=781&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select e.empno,e.ename,e.mgr,m.empno,m.ename
2 from emp e,emp m where e.mgr=m.empno(+);
1.9 【多表关联】
需求:查询员工的姓名、部门名称、工资等级
-- 查询员工的姓名、部门名称、工资等级
SCOTT@orcl>select e.ename,d.dname,s.grade
2 from emp e,dept d,salgrade s
3 where (e.sal between s.losal and s.hisal) and (e.deptno=d.deptno);
![](https://img-blog.csdnimg.cn/img_convert/17c5f1d9d24f32142b9b23c8b6d1acf7.png#averageHue=#060504&id=RglWt&originHeight=498&originWidth=874&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
SCOTT@orcl>select e.ename,d.dname,s.grade
2 from emp e join dept d on (e.deptno=d.deptno)
3 join salgrade s on (e.sal between s.losal and s.hisal);
![](https://img-blog.csdnimg.cn/img_convert/396b83491bd4741d5eb5a66bf406ed46.png#averageHue=#060504&id=jcJjn&originHeight=497&originWidth=915&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
【总结】
等值连接、不等值连接、外连接、自连接笛卡尔积:忽略了连接条件左外连接:列出所有员工的部门信息,包括没有部门的员工。
e.deptno=d.deptno(+);
传统 **oracle **连接语法
left outer join dept d on (e.deptno=d.deptno);
**SQL:1999 **连接语法右外连接:列出所有部门的员工信息,包括没有员工的部门。
e.deptno(+)=d.deptno;
right outer join dept d on (e.deptno=d.deptno);
全外连接
full outer join dept d on (e.deptno=d.deptno);
注意:同一个外连接查询需求,使用左外连接或者右外连接都可以实现!左外连接是把左边表里数据显示完整,包括不符合连接条件的行;右外连接是把右边表里数据显示完整,包括不符合连接条件的行。如何区分左右:传统 **oracle **连接语法看 **where **子句中比较符,标准 **SQL **看 **from **子句中 join。
【OCP】051-7 join using/join on051-121 表连接 join using / natural join
|