oracle心得3--多表查詢@分組查詢@子查詢講解與案例分析@經典練習題

1.笛卡爾集和叉集

笛卡爾集會在下面條件下產生:省略連線條件、連線條件無效、所有表中的所有行互相連線。

為了避免笛卡爾集， 可以在 WHERE 加入有效的連線條件。在實際執行環境下，應避免使用全笛卡爾集。

使用CROSS JOIN 子句使連線的表產生叉集。叉集和笛卡爾集是相同的。

2.Oracle連線型別:

Equijoin：等值連線

Non-equijoin：不等值連線

Outer join：外連線

Self join：自連線

使用表名字首在多個表中區分相同的列。在不同表中具有相同列名的列可以用表的別名加以區分。

使用別名可以簡化查詢。使用表名字首可以提高執行效率。如果使用了表的別名，則不能再使用表的真名

連線 n個表,至少需要 n-1個連線條件。 例如：連線三個表，至少需要兩個連線條件。

3.內連線和外連線

內連線: 合併具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行。

外連線: 兩個表在連線過程中除了返回滿足連線條件的行以外還返回左（或右）表中不滿足條件的行，這種連線稱為左（或右） 外聯接。沒有匹配的行時, 結果表中相應的列為空(NULL). 外連線的 WHERE 子句條件類似於內部連結, 但連線條件中沒有匹配行的表的列後面要加外連線運算子, 即用圓括號括起來的加號(+).

在SQL:內連線只返回滿足連線條件的資料；兩個表在連線過程中除了返回滿足連線條件的行以外還返回左（或右）表中不滿足條件的行，這種連線稱為左（右） 外聯接。

兩個表在連線過程中除了返回滿足連線條件的行以外還返回兩個表中不滿足條件的行 ，這種連線稱為滿外聯接。

自然連線：NATURAL JOIN 子句，會以兩個表中具有相同名字的列為條件建立等值連線。

在表中查詢滿足等值條件的資料。

如果只是列名相同而資料型別不同，則會產生錯誤。

使用 USING 子句建立連線

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

使用ON 子句建立連線

自然連線中是以具有相同名字的列為連線條件的;可以使用 ON 子句指定額外的連線條件。這個連線條件是與其它條件分開的。ON 子句使語句具有更高的易讀性。

4.分組函式

分組函式就是：作用於一組資料，並對一組資料返回一個值。

組函式型別（組函式忽略空值，NVL函式使分組函式無法忽略空值。）

AVG 、COUNT 、MAX 、MIN 、STDDEV(標準方差)、SUM、COUNT(expr) 返回 expr不為空的記錄總數、COUNT(DISTINCT expr) 返回 expr非空且不重複的記錄總數。

可以使用GROUP BY 子句將表中的資料分成若干組；在SELECT列表中所有未包含在組函式中的列都應該包含在 GROUP BY 子句中。

包含在 GROUP BY子句中的列不必包含在SELECT 列表中

非法使用組函式：

所用包含於SELECT 列表中，而未包含於組函式中的列都必須包含於 GROUP BY 子句中。不能在 WHERE子句中使用組函式（注意）。

可以在 HAVING子句中使用組函式。

過濾分組： HAVING 子句；使用 HAVING 過濾分組:

1）.  行已經被分組。

2）.  使用了組函式。

3）.  滿足HAVING 子句中條件的分組將被顯示。

例子：

SQL> select count(deptno)from emp;

 

COUNT(DEPTNO)                                                                   

-------------                                                                  

           14                                                                  

 

SQL> select count(distinct deptno)from emp;

 

COUNT(DISTINCTDEPTNO)                                                          

---------------------                                                          

                    3                                                           

 

SQL> select avg(nvl(comm,0)) from emp;

 

AVG(NVL(COMM,0))                                                               

----------------                                                               

      157.142857                                                                

 

SQL> select avg(comm) from emp;

 

 AVG(COMM)                                                                     

----------                                                                     

       550            

 

SQL> select deptno,job,sum(sal) from emp group by deptno,job order by deptno asc,sum(sal) asc;

 

    DEPTNO JOB         SUM(SAL)                                                

---------- --------- ----------                                                

        10 CLERK           1300                                                

        10 MANAGER         2450                                                

        10 PRESIDENT       5000                                                

        20 CLERK           1900                                                

        20 MANAGER         2975                                                

        20 ANALYST         6000                                                

        30 CLERK            950                                                

        30 MANAGER         2850                                                

        30 SALESMAN        5600                                                

 

 

5．子查詢

子查詢 (內查詢) 在主查詢之前一次執行完成。子查詢的結果被主查詢使用 (外查詢)。子查詢要包含在括號內。 將子查詢放在比較條件的右側。

單行操作符對應單行子查詢，多行操作符對應多行子查詢。

首先執行子查詢。向主查詢中的HAVING 子句返回結果

非法使用子查詢：多行子查詢使用單行比較符；子查詢中的空值問題：子查詢不返回任何行

6.案例分析（使用者在hr/hr下面操作）

查詢和Zlotkey相同部門的員工姓名和僱用日期

  

select initcap(concat(last_name,first_name)) as "姓名",hire_date from employees where department_id = (Select department_id from employees where last_name = 'Zlotkey');

 

查詢工資比公司平均工資高的員工的員工號，姓名和工資。

 

  select employee_id,initcap(concat(last_name,first_name)) as "姓名",salary from employees where salary >( select avg(salary) from employees) ;

 

查詢各部門工資比本部門平均工資高的員工的員工號,姓名和工資

 

select employee_id,initcap(concat(last_name,first_name)) as "姓名",salary from (select department_id,avg(salary) avgsalary from employees group by department_id) s join employees  e on e.department_id=s.department_id and e.salary>s.avgsalary;

 

查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名

 

select e.employee_id, initcap(concat(last_name,first_name)) as "姓名"  from(select employee_id, initcap(concat(last_name,first_name)) as "姓名"  from employees where initcap(concat(last_name,first_name)) like '%u%') s join employees e on e.employee_id=s.employee_id;

 

查詢在部門的location_id為1700的部門工作的員工的員工號

寫法一：

select employee_id from employees e,departments d,locations l where e.department_id = d.department_id and d.location_id = l.location_id and l.location_id = 1700;

 

寫法二：

select employee_id from employees e join departments d on e.department_id = d.department_id

join locations l on  d.location_id = l.location_id and l.location_id = 1700;

 

查詢管理者是King的員工姓名和工資

 

select initcap(concat(e.last_name,e.first_name)) as "姓名",e.salary,e.manager_id from employees e,employees m where e.manager_id=m.manager_id and m.last_name='King';

 

7.切換到scott表做練習：

找到員工表中工資最高的前三名，如下格式：

 

select rownum,d.* from (select empno,ename,sal from emp order by sal desc )d where rownum<=3;

 

統計每年入職的員工個數,效果如下格式

 

方法一：

select

(select count(*) from emp) total,

(select count(*) from emp where to_char(hiredate, 'yyyy')='1980')"1980",

(select count(*) from emp where to_char(hiredate, 'yyyy')='1981')"1981",

(select count(*) from emp where to_char(hiredate, 'yyyy')='1982')"1982",

(select count(*) from emp where to_char(hiredate, 'yyyy')='1987')"1987",

(select count(*) from emp where to_char(hiredate, 'yyyy')='1990') "1990"

from emp  where rownum=1;

 

方法二：

select count(*) total,

sum(Decode(to_char(hiredate, 'yyyy'), '1980', 1, 0))  "1980",

sum(Decode(to_char(hiredate, 'yyyy'), '1981', 1, 0))  "1981",

sum(Decode(to_char(hiredate, 'yyyy'), '1982', 1, 0))  "1982",

sum(Decode(to_char(hiredate, 'yyyy'), '1987', 1, 0))  "1987",

sum(Decode(to_char(hiredate, 'yyyy'), '1990', 1, 0))  "1990"

from emp;