oracle心得4--集合查詢@oracle中的多表連線@案例分析

1.簡單介紹集合查詢

   關鍵字

並集：union（返回兩個集合去掉重複元素後的所有記錄）

union all （返回兩個集合的所有記錄，包括重複的）

交集：intersect  （返回同時屬於兩個集合的記錄）

差集：minus  (屬於第一個集合，但不屬於第二個集合的記錄)

    select語句中引數型別和個數要一致;可以使用括號改變集合執行的順序;集合運算採用第一個語句的表頭作為表頭;Order by子句 只能出現在最後一個語句上，第一個select語句接受的列名稱或者別名 （查詢結果的列名）order by ** 必須與第一個select的列名一致

   案例分析

--並集

select employee_id,job_id from employees

union

select employee_id,job_id from job_history;

--所有並集

select employee_id,job_id from employees

unionall

select employee_id,job_id from job_history;

 

--交集

select employee_id,job_id from employees

intersect

select employee_id,job_id from job_history;

 

--差集

select ename,sal from emp where sal between700and1300

minus

select ename,sal from emp where sal between1201and1400

     前面介紹的查詢還侷限在資料庫的一張表內。但在實際應用中，我們經常需要在多張表中查詢資料或者需要對錶中的資料進行分類、彙總等。這就需要較為複雜的高階查詢

2.表關係

多個表之間關係：與mysq一樣存在三種表關係：一對多|(多對一)  、多對多 、一對一 ；

關係的完整性約束：實體完整性、參照完整性、用於定義的完整性。 必須滿足實體完整性和參照完整性.實體完整性：規定了欄位|屬性的約束。參照完整性：關係與關係之間的引用某個欄位的約束、外來鍵；

備註：實體完整性及參照完整性是任何關聯式資料庫必須滿足的條件。

使用者定義完整性：舉例：在學生表中學生的年齡不能夠大於60（使用者自定義的條件）

3.連線查詢

    Oracle中的連線與mysql一樣，同樣存在自連線查詢、外連線（包括左外連線、右外連線、全外連線）、還有內連線（等值連線、不等連線、自然連線）；下面將詳細介紹。

在emp表與dept表之間存在著多對一的關聯關係（現實中還有其他的關聯）,往往我們希望查詢出更多資訊,這時候我們就要用到連線查詢。

//查詢員工及部門的詳細資訊  但是會產生一個笛卡爾積的效果

SQL> select * from emp,dept;

//怎麼避免笛卡爾積呢?加入where查詢條件引用關係的比較

SQL> select * from emp e,dept d  where e.deptno=d.deptno;

//注意 以下寫法是有問題的：ORA-00918: 未明確定義列

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

備註說明：deptno在兩個表中都存在，所以一定要使用字首區分。

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

綜上所述 建立連線查詢時應遵循如下規則：

from子句應當包括所有的表名；where子句應定義連線條件兩個表1一個等值條件 三個表 2個等值條件…依次類推。連線 n個表,至少需要 n-1個連線條件。 例如：連線三個表，至少需要兩個連線條件。當列名為多個表共有時,列名必須被限制。

使用join連線查詢

      語法： From table1 join_type table2 on join_condition

Join_type連線型別分類如下：

內連線：

內連線根據所使用的比較方式不同,把內連線分為了：

等值連線：在連線條件中使用等於號(=)運算子比較被連線列的列值，其查詢結果中列出被連線表中的所有列，包括其中的重複列。例：select * from emp e inner join dept d on e.deptno = d.deptno;

不等連線：在連線條件使用除等於運算子以外的其它比較運算子比較被連線的列的列值。這些運算子包括>、>=、<=、<、!>、!<和<>即！=。 例：select * from emp e inner join dept d on e.deptno>d.deptno;

自然連線：注意區分與自連線查詢的區別；在連線條件中使用等於(=)運算子比較被連線列的列值，但它使用選擇列表指出查詢結果集合中所包括的列，並刪除連線表中的重複列。例：select * from emp natural join dept;

備註：Distinct是去掉重複的行,而自然連線是去掉重複的列。

外連線：

內連線的查詢結果都是滿足連線條件的記錄。但是，有時我們也希望輸出那些不滿足連線條件的記錄的資訊。比如，我們想知道這個部門中所有員工的情況,也包括沒有員工的部門，這時就需要使用外連線。外連線是隻限制一張表中的資料必須滿足連線條件，而另一張表中的資料可以不滿足連線條件的連線方式。3種外連線：

　　1) 左外連線（LEFT OUTER JOIN）

　　如果在連線查詢中，連線表左端的表中所有的記錄都列出來，並且能在右端的表中找到匹配的記錄，那麼連線成功。如果在右端的表中，沒能找到匹配的記錄，那麼對應的記錄是空值（NULL）。這時，查詢語句使用關鍵字 LEFT OUTER JOIN，也就是說，左外連線的含義是限制連線關鍵字右端的表中的資料必須滿足連線條件，而不關左端的表中的資料是否滿足連線條件，均輸出左端表中的內容（即輸出左邊表的全部和右邊表的滿足條件的部分）。

　　例如：要查詢所有部門的員工資訊查詢語句為

SQL> select * from dept d left outer join emp e on e.deptno=d.deptno order by d.deptno;　　左外連線查詢中左端表中的所有記錄的資訊都得到了保留。

   備註：部門表中記錄保留,如果部門中沒有員工,部門顯示 員工記錄用null補充。

　　2）右外連線（RIGHT OUTER JOIN）

　　右外連線與左外連線相反，只是右端表中的所有元組都列出，限制左端表的資料必須滿足連線條件，而不管右端表中的資料是否滿足連線條件，均輸出表中的內容。

　　例如：同上例內容，查詢語句為

SQL> select * from emp e right outer join dept d on e.deptno=d.deptno order by d.deptno;

　　右外連線查詢中右端表中的所有元組的資訊都得到了保留。

　　3）全外連線（FULL OUTER JOIN）

　　全外連線查詢的特點是左、右兩端表中的記錄都輸出，如果沒能找到匹配的記錄，就使用NULL來代替。

　　例如：同左外連線例子內容，查詢語句為

　　SQL> select * from emp e full outer join dept d on e.deptno=d.deptno order by d.deptno;

　　全外連線查詢中所有表中的元組資訊都得到了保留。

交叉聯接：

　交叉連線即笛卡兒乘積，是指兩個關係中所有記錄的任意組合。一般情況下，交叉查詢是沒有實際意義的。

SQL> select * from emp e cross join dept d;

備註：可以新增where子句篩選出有意義的資料。建議不使用。

自連線查詢：

如果在一個連線查詢中，涉及到的兩個表都是同一個表，這種查詢稱為自連線查詢；而內自然連線查詢的兩個表資料，關鍵字是natural。同一張表在FROM字句中多次出現，為了區別該表的每一次出現，需要為表定義一個別名。自連線是一種特殊的內連線，它是指相互連線的表在物理上為同一張表，但可以在邏輯上分為兩張表。

　　例如：要求檢索出員工編號為7369的上司的詳細資訊，查詢語句為;select e1.* from emp e inner join emp e1 on e.mgr=e1.empno where e.empno=7369;

注意：對於連線查詢中使用到的 inner outer是可以省略的。但為規範最好不要省略。

提高部分：

外連線的符號是 (+);左邊寫加號代表左邊滿足條件的（加號是一個或多個意思嘛！），和右邊的全部；右邊寫加號正好相反；正好與外連線的左右命名相反，左外連線是在右邊寫加號，而右外連線是在左邊寫加號，注意理解；下面一個例字：。

SQL> select * from emp e inner join dept d on e.deptno(+)=d.deptno;

在NATURAL JOIN 子句建立等值連線時，可以使用 USING 子句指定等值連線中需要用到的列。 使用 USING 可以在有多個列滿足條件時進行選擇。 不要給選中的列中加上表名字首或別名。NATURAL JOIN 和 USING 子句經常同時使用。

SQL> select * from emp e  join dept d  using(deptno);