本文深入讲解了SQL中的连接查询技术，包括等值连接、不等值连接、外连接及自连接等多种连接方式，并通过实例演示了如何使用这些连接来获取所需的数据。
SQL> --等值连接
SQL> --查询员工信息： 员工号 姓名 月薪 部门名称
SQL> select e.empno,e.ename,e.sal,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME SAL DNAME
---------- ---------- ---------- --------------
7369 SMITH 800 RESEARCH
7499 ALLEN 1600 SALES
7521 WARD 1250 SALES
7566 JONES 2975 RESEARCH
7654 MARTIN 1250 SALES
7698 BLAKE 2850 SALES
7782 CLARK 2450 ACCOUNTING
7788 SCOTT 3000 RESEARCH
7839 KING 5000 ACCOUNTING
7844 TURNER 1500 SALES
7876 ADAMS 1100 RESEARCH
已选择14行。
SQL> --不等值连接
SQL> --查询员工信息： 员工号 姓名 月薪 工资级别
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select e.empno,e.ename,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
EMPNO ENAME SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 1
7876 ADAMS 1100 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7844 TURNER 1500 3
7499 ALLEN 1600 3
7782 CLARK 2450 4
7698 BLAKE 2850 4
7566 JONES 2975 4
已选择14行。
SQL> --外连接
SQL> --按部门统计员工人数： 部门号 部门名称 人数
......
SQL> ed
已写入 file afiedt.buf
1 select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
2 from emp e,dept d
3 where e.deptno=d.deptno
4* group by d.deptno,d.dname
SQL> /
部门号 部门名称 人数
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
---问题，引出外连接概念
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp where deptno=40;
未选定行
SQL> /*
SQL> 希望： 在最后的结果中，包含某些不成立的记录
SQL> 外连接:
SQL> 左外连接: 当where e.deptno=d.deptno不成立的时候,等号左边所代表的表 任然被包含（显示完全）
SQL> 写法: where e.deptno=d.deptno(+)
SQL> 右外连接: 当where e.deptno=d.deptno不成立的时候,等号右边所代表的表 任然被包含（显示完全）
SQL> 写法:where e.deptno(+)=d.deptno
SQL> */
|
|
|
↓↓↓↓↓
SQL> --判断外连接（个人见解）
SQL> --1、首先判断左连接还是右连接（看=两边要哪个表e(emp),d(dept)完全显示)
SQL> --如果要完全显示的表在右边，那么就是右连接，如果要显示的表在左边，即左连接
SQL> --比如上面要完全显示的是d(dept)表（因为需求是显示所有部门的人数，即使没有人，也要显示出来）
SQL> --，那么where e.deptno = d.deptno就是右连接
SQL> --2、根据右连接或者左连接判断+号的位置
SQL> --如果是左连接，那么+号写在右边
SQL> --如果是右连接，那么+号写在左边
SQL> select d.deptno,d.dname,count(e.empno)
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.dname
5* order by 1
SQL> /
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
SQL> host cls
SQL> --自连接
SQL> --查询员工信息：***的老板是***
SQL> set linesize 120
SQL> col sal for 999
SQL> col sal for 9999
SQL> host cls
SQL> /
SQL> --自连接：通过表的别名，将同一张表视为多张表
SQL> select e.ename||'的老板是'||b.ename
2 from emp e,emp b --内连接
3 where e.mgr=b.empno(+); --左外连接
E.ENAME||'的老板是'||B.ENAME
----------------------------
FORD的老板是JONES
SCOTT的老板是JONES
JAMES的老板是BLAKE
TURNER的老板是BLAKE
MARTIN的老板是BLAKE
WARD的老板是BLAKE
ALLEN的老板是BLAKE
MILLER的老板是CLARK
ADAMS的老板是SCOTT
CLARK的老板是KING
BLAKE的老板是KING
KING的老板是
已选择14行。
-----又一个重点知识点：*****************
SQL> --层次查询 （遍历树,递归查询）
--level是一个伪列，它的值代表树的层数(顶层(倒树)-->最后一层 1-->n)
SQL> select level,empno,ename,sal,mgr
2 from emp
--[where condition]
3 start with mgr is null --从大boss开始
4 connect by prior empno=mgr --相当于where条件部分 mgr字段为员工的上司编号
5 order by 1; --按照level排序
LEVEL EMPNO ENAME SAL MGR
---------- ---------- ---------- ----- ----------
1 7839 KING 5000
2 7566 JONES 2975 7839
2 7698 BLAKE 2850 7839
2 7782 CLARK 2450 7839
3 7902 FORD 3000 7566
3 7521 WARD 1250 7698
3 7900 JAMES 950 7698
3 7934 MILLER 1300 7782
3 7499 ALLEN 1600 7698
3 7788 SCOTT 3000 7566
3 7654 MARTIN 1250 7698
3 7844 TURNER 1500 7698
4 7876 ADAMS 1100 7788
4 7369 SMITH 800 7902
已选择14行。
--解读：connect by prior empno=mgr
--prior 指的上一条记录，整条意思是"上一条记录的empno的这条记录的mgr"
--因为这个是从最上层的开始的，它已经没有了上层
--为了更好的理解，把本条记录（start with..) 理解为树的中间
--那么它的上一条记录，也就是它的父节点的empno是这条记录的mgr
SQL> /*
SQL> -- 执行的过程:
SQL> --1. KING: start with mgr is null ---> empno=7839 祖先
SQL> --2. where mgr = 7839; ---> 7566 7698 7782 --子类
SQL> --3. where mgr in (7566 7698 7782)*/ --子类
SQL> spool off
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
