--==========================

--SQL基礎-->多表查詢

--==========================

/*

一、多表查詢

????簡言之，根據特定的連接條件從不同的表中獲取所需的數據

?

????笛卡爾集的產生條件：

???????省略連接條件

???????連接條件無效

???????第一個表中的所有行與第二個表中的所有行相連接

???????????

二、多表查詢語法：*/

????SELECT?table1.column,?table2.column

????FROM?table1,?table2

????WHERE?table1.column1?=?table2.column2;

????/*

????但要注意where?不要省了，省略where?即為笛卡爾集，而且where?條件要有效，

????兩張表間有一個相同的字段，才好進行有效的多表查詢

?

????查詢時列名前，加表名或表別名前輟（如果字段在兩個表中是唯一的可以不加）

?

????為了簡化SQL書寫，可為表名定義別名，格式：from?表名別名

????如：from emp e,dept d

???

????建議使用表的別名及表前綴，使用表別名可以簡化查詢，而使用表前綴則可以提高查詢性能

?

????例：查詢每個員工的工號，姓名，工資，部門名和工作地點*/

????select?empno,ename,sal,dname,loc?from?emp,dept

????where?emp.deptno=dept.deptno;

?

/*

三、多表連接類型：

????從數據顯示方式來講有：內連接和外連接。

????內連接：只返回滿足連接條件的數據。

????外連接：除了返回滿足連接條的行以外，還返回左（右）表中，不滿足條件的行，

???????稱為左（右）連接

?

????內連接*/

????select?empno,ename,sal,dname,loc?from?emp,dept

????where?emp.deptno=dept.deptno;??--(Oracle 8i?及以前的寫法)

?

????--內連接的另一種寫法：

????select?empno,ename,job,sal,dept.deptno,dname,loc

????from?emp?join?dept?on?(emp.deptno=dept.deptno);?-- (SQL 99的寫法)

?

????/*

????外連接：

????兩個表的查詢中，使用外連接可以查詢另一個表或者兩個中不滿足連接條件的數據。

????外連接的符號是（＋），（＋）要放在字段名后。（＋）對面的那個表，會全部顯示。

???

????外連接語法*/

????????SELECT?table1.column,?table2.column???--右外連接

???????FROM?table1,?table2

???????WHERE?table1.column(+)?=?table2.column;

??????

???????SELECT?table1.column,?table2.column???--左外連接

???????FROM?table1,?table2

???????WHERE?table1.column?=?table2.column(+);

?

????--例：

????select?empno,ename,job,sal,dept.deptno,dname,loc

????from?emp,dept

????where?emp.deptno(+)=dept.deptno;?-- (Oracle 8i?及以前的寫法)

?

????--另一種寫法（右連接）：?????-- (SQL 99的寫法)

????select?empno,ename,job,sal,dept.deptno,dname,loc

????from?emp?right?join?dept?on?(emp.deptno=dept.deptno);

?

????--左連接：????????????????????(SQL 99的寫法)

????select?empno,ename,job,sal,dept.deptno,dname,loc

????from?emp?left?join?dept?on?(emp.deptno=dept.deptno);

?

????--全連接（滿連接）????????????(SQL 99的寫法)

???select?empno,ename,job,sal,d.deptno,dname,loc

????from?emp e?full?join?dept d?on?(e.deptno=d.deptno);

?

????/*

????自然連接????????????????????(SQL 99的寫法)

????以兩個表具有相同的字段的所有列為基礎，可采用自然連接（natural join)

????它將選擇兩個表中那些在所有匹配的列中值相等的行。

????如果列具有相同的名稱，但數據類型能夠不同，則會報錯*/

????select?empno,ename,job,sal,deptno,dname,loc

????from?emp natural?join?dept;

?

????/*

????自連接：

????將自身表的一個鏡像當另一個表來對待。*/

????select?...?from?emp e,emp d??...

???

????--下面列出SQL 99的語法供參考

????SELECT?table1.column,?table2.column

????FROM?table1

????[CROSS JOIN table2]?|

????[NATURAL JOIN table2]?|

????[JOIN table2 USING (column_name)]?|

????[JOIN table2

????ON(table1.column_name = table2.column_name)]?|

????[LEFT|RIGHT|FULL OUTER JOIN table2

????ON (table1.column_name = table2.column_name)];

???

????--在下面的語法中

????table1.column?????--指明從中檢索數據的表和列

????CROSS?JOIN????????--返回兩個表的笛卡爾集

????NATURAL?JOIN??????--根據相同的列名連接兩個表

????JOIN?table

????USING column_name?--根據列名執行等值連接

????JOIN?table?ON

????table1.column_name???--根據ON?子句中的條件執行等值連接

????=?table2.column_name

????LEFT/RIGHT/FULL?OUTER

???

????/*

????使用using子句創建連接

????????如果幾個列具有相同的名稱，但是數據類型不匹配，那么可以使用using子句來修改natural join

???????子句以指定要用于等值連接的列

???????在多個列匹配時，使用using?子句只匹配一個列

???????在引用列中不要使用表名或別名

???????natural join?和using?子句是互不相容的?*/

??????

????????--例：

???????SELECT?l.city,?d.department_name

???????FROM?locations l?JOIN?departments d USING?(location_id)

???????WHERE?location_id?=?1400;

???????--下面的語句無效，因為where?子句中限定了location_id

???????SELECT?l.city,?d.department_name

???????FROM?locations l?JOIN?departments d USING?(location_id)

???????WHERE?d.location_id?=?1400;

???????ORA-25154:?column?part?of?USING clause cannot have qualifier

??????

???????--注意：

???????--???兩個表中名稱相同的列在使用時不能有任何限定符，這一限制同樣適用于natural join

/*

四、演示：??????*/?

--笛卡爾集

????SQL>?select?empno,ename,dname?from?emp,dept;

?

????????EMPNO ENAME??????DNAME

????---------- ---------- --------------

?????????7369 SMITH??????ACCOUNTING

?????????7499 ALLEN??????ACCOUNTING

?????????7521 WARD???????ACCOUNTING

?????????7566 JONES??????ACCOUNTING

?????????7654 MARTIN?????ACCOUNTING

?????????7698 BLAKE??????ACCOUNTING

?????????7782 CLARK??????ACCOUNTING

?????????7788 SCOTT??????ACCOUNTING

?????????7839 KING???????ACCOUNTING

?????????7844 TURNER?????ACCOUNTING

?????????7876 ADAMS??????ACCOUNTING

?????????--中間結果省略

?????????56 rows selected.????

--使用cross join?實現交叉連接，即笛卡爾集????

????SQL>?select?empno,ename,dname?from?emp

??????2??cross?join?dept;

?

????????EMPNO ENAME??????DNAME

????---------- ---------- --------------

?????????7369 SMITH??????ACCOUNTING

?????????7499 ALLEN??????ACCOUNTING

?????????7521 WARD???????ACCOUNTING

?????????7566 JONES??????ACCOUNTING

?????????7654 MARTIN?????ACCOUNTING

?????????7698 BLAKE??????ACCOUNTING

?????????7782 CLARK??????ACCOUNTING

?????????7788 SCOTT??????ACCOUNTING

?????????7839 KING???????ACCOUNTING

?????????7844 TURNER?????ACCOUNTING

?????????7876 ADAMS??????ACCOUNTING

?????

--等值連接(Oracle?寫法)

????SQL>?select?empno,ename,dname?from?emp,dept?where?emp.deptno?=?dept.deptno;

?

????????EMPNO ENAME??????DNAME

????---------- ---------- --------------

?????????7369 SMITH??????RESEARCH

?????????7499 ALLEN??????SALES

?????????7876 ADAMS??????RESEARCH

?????????--部分結果省略

?????????7902 FORD???????RESEARCH

?????????7934 MILLER?????ACCOUNTING

?

????14 rows selected.

?

--等值連接(SQL 99?寫法)

????SQL>?select?e.empno,e.ename,d.dname?from?emp??e

??????2??inner?join?dept??d

??????3??on?e.deptno?=?d.deptno;

?

????????EMPNO ENAME??????DNAME

????---------- ---------- --------------

?????????7369 SMITH??????RESEARCH

?????????7499 ALLEN??????SALES

?????????7876 ADAMS??????RESEARCH

???????????--部分結果省略

?????????7902 FORD???????RESEARCH

?????????7934 MILLER?????ACCOUNTING

?

????14 rows selected.

?

--注意：表別名不支持as?用法

????SQL>?select?e.empno,e.ename,d.dname?from?emp?as?e

??????2??inner?join?dept??d

??????3??on?e.deptno?=?d.deptno;

????select?e.empno,e.ename,d.dname?from?emp?as?e

???????????????????????????????????????*

????ERROR at line 1:

????ORA-00933:?SQL command?not?properly ended

?

--等值連接并增加條件

????SQL>?select?e.empno,e.ename,d.dname?from?emp??e,

??????2??dept d

??????3??where?d.deptno?=?e.deptno

??????4????and?e.ename?=?'SCOTT';

?

????????EMPNO ENAME??????DNAME

????---------- ---------- --------------

?????????7788 SCOTT??????RESEARCH

?????

--非等值連接

--查詢雇員的姓名、薪水、級別且部門為的記錄

????SQL>?select?ename,sal,grade

??????2??from?emp,salgrade

??????3??where?sal?between?losal?and?hisal

??????4????and?emp.deptno?=?20;

?

????ENAME?????????????SAL??????GRADE

????---------- ---------- ----------

????SCOTT????????????3000??????????4

????FORD?????????????3000??????????4

????JONES????????????2975??????????4

????ADAMS????????????1100??????????1

????SMITH?????????????800??????????1?

?

--使用SQL 99寫法實現上述功能

????SQL>?select?e.ename,e.sal,s.grade

??????2??from?emp e

??????3????join?salgrade s

??????4??????on?e.sal?between?losal?and?hisal

??????5??????and?e.deptno?=?20;

?

????ENAME?????????????SAL??????GRADE

????---------- ---------- ----------

????SCOTT????????????3000??????????4

????FORD?????????????3000??????????4

????JONES????????????2975??????????4

????ADAMS????????????1100??????????1

????SMITH?????????????800??????????1

?

--右外連接

--注意：右外連接時，加號在等號的左邊

--可以看到，左表emp中的列有為空值的

????SQL>?select?e.ename,e.deptno,d.dname??

??????2??from?emp e,dept d

??????3??where?e.deptno(+)?=?d.deptno;

?

????ENAME??????????DEPTNO DNAME

????---------- ---------- --------------

????CLARK??????????????10 ACCOUNTING

????KING???????????????10 ACCOUNTING

????MILLER?????????????10 ACCOUNTING

????JONES??????????????20 RESEARCH

????FORD???????????????20 RESEARCH

????ADAMS??????????????20 RESEARCH

????SMITH??????????????20 RESEARCH

????SCOTT??????????????20 RESEARCH

????WARD???????????????30 SALES

????TURNER?????????????30 SALES

????ALLEN??????????????30 SALES

?

????ENAME??????????DEPTNO DNAME

????---------- ---------- --------------

????JAMES??????????????30 SALES

????BLAKE??????????????30 SALES

????MARTIN?????????????30 SALES

???????????????????????OPERATIONS

?

????15 rows selected.

?

--使用SQL 99寫法實現右外連接

????SQL>?select?e.ename,e.deptno,d.dname

??????2??from?emp e??????

??????3????right?join?dept d

??????4??????on?e.deptno?=?d.deptno?;

?

????ENAME??????????DEPTNO DNAME

????---------- ---------- --------------

????CLARK??????????????10 ACCOUNTING

????KING???????????????10 ACCOUNTING

????MILLER?????????????10 ACCOUNTING

????JONES??????????????20 RESEARCH

????FORD???????????????20 RESEARCH

????ADAMS??????????????20 RESEARCH

????SMITH??????????????20 RESEARCH

????SCOTT??????????????20 RESEARCH

????WARD???????????????30 SALES

????TURNER?????????????30 SALES

????ALLEN??????????????30 SALES

?

????ENAME??????????DEPTNO DNAME

????---------- ---------- --------------

????JAMES??????????????30 SALES

????BLAKE??????????????30 SALES

????MARTIN?????????????30 SALES

???????????????????????OPERATIONS

?

--左外連接

--注意：左外連接時，加號在等號的右邊

????SQL>?select?d.dname,e.ename,e.deptno

??????2??from?dept d,emp e

??????3??where?d.deptno?=?e.deptno(+)

??????4??order?by?d.deptno;

?

????DNAME??????????ENAME??????????DEPTNO

????-------------- ---------- ----------

????ACCOUNTING?????CLARK??????????????10

????ACCOUNTING?????KING???????????????10

????ACCOUNTING?????MILLER?????????????10

????RESEARCH???????JONES??????????????20

????RESEARCH???????FORD???????????????20

????RESEARCH???????ADAMS??????????????20

????RESEARCH???????SMITH??????????????20

????RESEARCH???????SCOTT??????????????20

????SALES??????????WARD???????????????30

????SALES??????????TURNER?????????????30

????SALES??????????ALLEN??????????????30

?

????DNAME??????????ENAME??????????DEPTNO

????-------------- ---------- ----------

????SALES??????????JAMES??????????????30

????SALES??????????BLAKE??????????????30

????SALES??????????MARTIN?????????????30

????OPERATIONS

?

????15 rows selected.

?

--使用SQL 99寫法實現左外連接???????????????

????SQL>?select?d.dname,e.ename,e.deptno

??????2??from?dept d

??????3????left?join?emp e

??????4??????on?d.deptno?=?e.deptno

??????5??order?by?d.deptno;

?

????DNAME??????????ENAME??????????DEPTNO

????-------------- ---------- ----------

????ACCOUNTING?????CLARK??????????????10

????ACCOUNTING?????KING???????????????10

????ACCOUNTING?????MILLER?????????????10

????RESEARCH???????JONES??????????????20

????RESEARCH???????FORD???????????????20

????RESEARCH???????ADAMS??????????????20

????RESEARCH???????SMITH??????????????20

????RESEARCH???????SCOTT??????????????20

????SALES??????????WARD???????????????30

????SALES??????????TURNER?????????????30

????SALES??????????ALLEN??????????????30

?

????DNAME??????????ENAME??????????DEPTNO

????-------------- ---------- ----------

????SALES??????????JAMES??????????????30

????SALES??????????BLAKE??????????????30

????SALES??????????MARTIN?????????????30

????OPERATIONS

?

????15 rows selected.????????????????

?

--自連接

????SQL>?select?e.ename?||?' works for '?||?m.ename

??????2??from?emp e,emp m

??????3??where?e.empno?=?m.mgr;

?

????E.ENAME||'WORKSFOR'||M.ENAME

????-------------------------------

????FORD works?for?SMITH

????BLAKE works?for?ALLEN

????BLAKE works?for?WARD

????KING works?for?JONES

????BLAKE works?for?MARTIN

????KING works?for?BLAKE

????KING works?for?CLARK

????JONES works?for?SCOTT

????BLAKE works?for?TURNER

????SCOTT works?for?ADAMS

????BLAKE works?for?JAMES

?

????E.ENAME||'WORKSFOR'||M.ENAME

????-------------------------------

????JONES works?for?FORD

????CLARK works?for?MILLER

?

????13 rows selected.

?

--自然連接

????SQL>?select?empno,ename,job,deptno,dname,loc

??????2??from?emp

??????3??natural?join?dept;

?

????????EMPNO ENAME??????JOB???????????DEPTNO DNAME??????????LOC

????---------- ---------- --------- ---------- -------------- -------------

?????????7369 SMITH??????CLERK?????????????20 RESEARCH???????DALLAS

?????????7499 ALLEN??????SALESMAN??????????30 SALES??????????CHICAGO

?????????7521 WARD???????SALESMAN??????????30 SALES??????????CHICAGO

?????????7566 JONES??????MANAGER???????????20 RESEARCH???????DALLAS

?????????7654 MARTIN?????SALESMAN??????????30 SALES??????????CHICAGO

?????????7698 BLAKE??????MANAGER???????????30 SALES??????????CHICAGO

?????????7782 CLARK??????MANAGER???????????10 ACCOUNTING?????NEW YORK

?????????7788 SCOTT??????ANALYST???????????20 RESEARCH???????DALLAS

?????????7839 KING???????PRESIDENT?????????10 ACCOUNTING?????NEW YORK

?????????7844 TURNER?????SALESMAN??????????30 SALES??????????CHICAGO

?????????7876 ADAMS??????CLERK?????????????20 RESEARCH???????DALLAS

?

????????EMPNO ENAME??????JOB???????????DEPTNO DNAME??????????LOC

????---------- ---------- --------- ---------- -------------- -------------

?????????7900 JAMES??????CLERK?????????????30 SALES??????????CHICAGO

?????????7902 FORD???????ANALYST???????????20 RESEARCH???????DALLAS

?????????7934 MILLER?????CLERK?????????????10 ACCOUNTING?????NEW YORK

?

????14 rows selected.

?

--使用using?子句創建連接

????SQL>?select?e.empno,e.ename,d.dname,d.loc

??????2??from?emp e

??????3??join?dept d

??????4??using?(deptno)

??????5??where?deptno?in?(20,40);

?

????????EMPNO ENAME??????DNAME??????????LOC

????---------- ---------- -------------- -------------

?????????7369 SMITH??????RESEARCH???????DALLAS

?????????7566 JONES??????RESEARCH???????DALLAS

?????????7788 SCOTT??????RESEARCH???????DALLAS

?????????7876 ADAMS??????RESEARCH???????DALLAS

?????????7902 FORD???????RESEARCH???????DALLAS

/*

五、更多*/

?

Oracle?數據庫實例啟動關閉過程

?

Oracle 10g SGA?的自動化管理

?

使用OEM,SQL*Plus,iSQL*Plus?管理Oracle實例

?

Oracle實例和Oracle數據庫(Oracle體系結構)

?

SQL?基礎-->常用函數

?

SQL基礎-->過濾和排序

?

SQL?基礎-->SELECT?查詢