1.知識點：可以對照下面的錄屏進行閱讀
SQL> --等值連接 SQL> --查詢員工信息： 員工號 姓名 月薪 部門名稱 SQL> select empno,ename,sal,dname 2 from emp,dept 3 where emp.deptno=dept.deptno; SQL> --不等值連接 SQL> --查詢員工信息: 姓名 月薪 工資級別 SQL> select ename,sal,grade 2 from emp e,salgrade s 3 where e.sal between s.losal and s.hisal; SQL> --外連接 SQL> -- 外連接解決的問題：當條件不成立時，任然希望在結果中包含不成立的記錄 SQL> -- 左外連接: where d.deptno=e.deptno 當不成立時，等號左邊代表的表的信息任然被包含 SQL> -- 寫法: where d.deptno=e.deptno(+) SQL> -- 右外連接: where d.deptno=e.deptno 當不成立時，等號右邊代表的表的信息任然被包含 SQL> -- 寫法：where d.deptno(+)=e.deptno SQL> --按部門統計員工人數： 部門號 部門名稱 人數 SQL> --錯誤的列子：沒有員工的部門，沒有顯示 SQL> select d.deptno,d.dname,count(e.empno) 2 from dept d,emp e 3 where d.deptno=e.deptno 4 group by d.deptno,d.dname; SQL> --上面這個例子改正版：使用了左外連接 SQL> select d.deptno,d.dname,count(e.empno) 2 from dept d,emp e 3 where d.deptno=e.deptno(+) 4 group by d.deptno,d.dname; SQL> --自連接 SQL> --查詢員工信息：***的老板是*** SQL> select e.ename||'的老板是'||b.ename 2 from emp e,emp b 3 where e.mgr=b.empno; SQL> --核心：利用表的別名，將同一張表視為多張表 SQL> --使用層次查詢的原因：自連接不太適合操作大表 SQL> --層次查詢：對同一張表的前後兩次操作，進行連接 SQL> --層次查詢：遍歷一棵樹 SQL> select level,empno,ename,mgr --level：偽列，Oracle自動加上的列，在層次查詢中表示節點的深度 2 from emp 3 connect by prior empno=mgr --連接條件：上一次查詢的empno = 下一次查詢的mgr 4 start with mgr is null --從根開始遍歷整個樹（在這個例子中，根即為老板號為空） 5 order by 1;
2.在Sqlplus下實際執行的結果錄屏：
SQL> host cls
SQL> --等值連接
SQL> --查詢員工信息： 員工號 姓名 月薪 部門名稱
SQL> desc dept
名稱 是否為空? 類型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select empno,ename,sal,dname
2 from emp,dept
3 where emp.deptno=dept.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
EMPNO ENAME SAL DNAME
---------- ---------- ----- --------------
7900 JAMES 950 SALES
7902 FORD 3000 RESEARCH
7934 MILLER 1300 ACCOUNTING
已選擇14行。
SQL> ed
已寫入 file afiedt.buf
1 select empno,ename,sal,dname
2 from emp e,dept d
3* where e.deptno=d.deptno
SQL> /
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
EMPNO ENAME SAL DNAME
---------- ---------- ----- --------------
7900 JAMES 950 SALES
7902 FORD 3000 RESEARCH
7934 MILLER 1300 ACCOUNTING
已選擇14行。
SQL> host cls
SQL> --不等值連接
SQL> --查詢員工信息: 姓名 月薪 工資級別
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select ename,sal,grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
ENAME SAL GRADE
---------- ----- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
ENAME SAL GRADE
---------- ----- ----------
SCOTT 3000 4
FORD 3000 4
KING 5000 5
已選擇14行。
SQL> host cls
SQL> --外連接
SQL> --按部門統計員工人數： 部門號 部門名稱 人數
SQL> select d.deptno,d.dname,count(e.empno)
2 from dept d,emp e
3 where d.deptno=e.deptno
4 group by d.deptno,d.dname;
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
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> 左外連接: where d.deptno=e.deptno 當不成立時，等號左邊代表的表的信息任然被包含
SQL> 寫法: where d.deptno=e.deptno(+)
SQL> 右外連接: where d.deptno=e.deptno 當不成立時，等號右邊代表的表的信息任然被包含
SQL> 寫法：where d.deptno(+)=e.deptno
SQL> */
SQL> select d.deptno,d.dname,count(e.empno)
2 from dept d,emp e
3 where d.deptno=e.deptno(+)
4 group by d.deptno,d.dname;
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
SQL> ed
已寫入 file afiedt.buf
1 select d.deptno,d.dname,count(e.empno)
2 from dept d,emp e
3 where d.deptno=e.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> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
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
E.ENAME||'的老板是'||B.ENAME
----------------------------
JONES的老板是KING
SMITH的老板是FORD
已選擇13行。
SQL> --自連接：利用表的別名，將同一張表視為多張表
SQL> --層次查詢
SQL> --自連接不太適合操作大表
SQL>
SQL>
SQL>
SQL> --層次查詢: 對同一張表的前後兩次操作，進行連接
SQL> select level,empno,ename,mgr
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null
5 order by 1;
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
2 7698 BLAKE 7839
2 7782 CLARK 7839
3 7902 FORD 7566
3 7521 WARD 7698
3 7900 JAMES 7698
3 7934 MILLER 7782
3 7499 ALLEN 7698
3 7788 SCOTT 7566
3 7654 MARTIN 7698
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
3 7844 TURNER 7698
4 7876 ADAMS 7788
4 7369 SMITH 7902
已選擇14行。
SQL> spool off
