oracle心得4--集合查詢@oracle中的多表串連@案例分析____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);