複雜查詢語句的使用

1.查詢語句的使用
使用 select語句和子查詢(subquery)可以從一個或多個表,檢視,實體試圖中返回資料.
 
1.1相關子查詢
可以將子查詢(as subquery)或in或exists當成where的一個條件的一部分,這樣的查詢稱為子查詢
  .where中可以包含一個select語句的子查詢
  .where中可以包含in,exists語句
  .最多可以巢狀16層
  .層次過多會影響效能
  [例]簡單子查詢例項
  查詢是否有的專家既以研究所的名義來申請基金專案,又以大學系為單位申請專案
  (按規定只能以一個單位來申請)
  SQL> create table univ_subject
  2    (
  3       name                 varchar2(12) not null,
  4       per_id                number     not null,
  5      dept_name       varchar2(20)            
  6    );
  SQL> insert into univ_subject  values('gaoqianjing',1001,'資訊工程系');
  SQL> insert into univ_subject  values('wangbing',1002,'物理系');
  SQL> insert into univ_subject  values('liming',1003,'化學系');
  ===============
   SQL> create table  colle_subject
  2     (
  3              colle_name    varchar2(20),
  4              per_id              number
  5     );
  SQL> insert into colle_subject values('電子研究所',1001);
  SQL>  insert into colle_subject values('物理研究所',1005);
  ================
  SQL> select name,per_id,dept_name from univ_subject where per_id in
  2    (select per_id from colle_subject);

  NAME            PER_ID   DEPT_NAME
  ------------          ---------     --------------------
  gaoqianjing  1001      資訊工程系

1.2外連線
 [例]外連線例項
 招生中所有學生的資訊放在students表中,而部分有特長的學生在另一個表中stuent_skill中同樣有該學生
 的資訊。現在要全部列出所有學生,如果某個學生在表student_skill中就有其特長資訊,並顯示特長資訊,如果
 某個學生沒有特長就顯示特長問空.
 SQL>  create table students
  2    (
  3       st_id    varchar2(20),
  4       name  varchar2(10),
  5       age      number(2),
  6       tol_score   number(3)
  7    ) ;
SQL>   insert into students values('973231','wangbindu',22,501);
SQL>   insert into students values('973232','zhuzhijing',21,538);
SQL>  insert into students values('973233','gaojing',21,576);
===================

SQL>  create table student_skill
  2   (
  3      st_id  varchar2(20),
  4      skill    varchar2(20)
  5  );
SQL>  insert into student_skill values('973231','籃球');
SQL>  insert into student_skill(st_id) values('973232');
SQL>  insert into student_skill values('973233','足球');
===================

SQL>   select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)
order by a.st_id;

ST_ID                NAME             AGE TOL_SCORE SKILL
-------------------- ---------- --------- --------- ------------------  --
973231               wangbindu         22       501        籃球
973232               zhuzhijing           21       538
973233               gaojing                21       576        足球

1.3自我連線
自我連線是在同一個表或檢視內進行條件連線.
[例]自我連線例項
查詢每個僱員的名字和該僱員的經理的名字:
SQL> select e1.ename||'   work for   '||e2.ename "Employees and their Managers"
  2  from  scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

Employees and their Managers
-------------------------------------------------
SMITH      work for   FORD
ALLEN     work for   BLAKE
WARD      work for   BLAKE
JONES     work for   KING
MARTIN   work for   BLAKE
BLAKE     work for   KING
CLARK     work for   KING
SCOTT      work for   JONES
TURNER  work for   BLAKE
ADAMS     work for   SCOTT
JAMES      work for   BLAKE
FORD        work for   JONES
MILLER     work for   CLARK

1.4UNION , INTERSECT及 MINUS
UNION:            可以將兩個以上的表的相類似的查詢結果放在一起 (union all則表示返回所有的行)
具體語法:
select ...
union[all]
select...
==========

INTERSECT:  返回兩個表中相同的資訊
具體語法:
select ...
intersect
select...
==========

MINUS          :  返回一個表中出現的資訊
具體語法:
select ...
minus
select...
[例1]UNION操作例項
SQL> select  st_id  from students
  2  union
  3  select  st_id  from student_skill;

ST_ID
--------------------
973231
973232
973233

[例2]INTERSECT操作例項
列出有特長的學生的學號
SQL> select st_id from students
  2  intersect
  3  select st_id from student_skill;
ST_ID
--------------------
973231
973233

[例3]MINUS操作例項
列出沒有特長學生的學號
select st_id from students
minus
select st_id from student_skill;
ST_ID
--------------------
973232

2.建立複雜的檢視
許多應用系統有統計等功能,建議最好把這些複雜語句寫成檢視.下面是幾個常用的檢視.
2.1分組檢視
[例1]簡單的分組檢視
SQL> create or replace view dept_tot as
  2  select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
  3  where a.deptno=b.deptno group by a.dname;

檢視已建立。
SQL> select * from dept_tot;

DEPT                           TOTAL_SAL
--------------                     ---------
ACCOUNTING          8750
RESEARCH              10875
SALES                        9400

[例2]帶複雜函式檢視
SQL> create or replace view itemtot as
  2  select persion,sum(amount) itemtot from ledger
  3  where actiondate between
  4  to_date('01-MAR-1901','dd-mon-yyyy') and
  5  to_date('31-MAR-1901','dd-mon-yyyy')
  6  and action in('bought','raid') group by persion;

2.2合計檢視
[例]合計函式檢視例項
SQL> create or replace view emp_no1  as
  2  select deptno,sum(sal) 工資和,sum(comm) 總和
  3  from scott.emp group by deptno;
SQL> select * from emp_no1;
DEPTNO    工資和      總和
--------- --------- ---------
       10      8750
       20     10875
       30      9400      2200

2.3組合檢視
[例]帶組合函式的檢視
SQL> create or replace view byitem as
  2  select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal
  3  from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion
  4  and actiondate between
  5  to_date('01-MAR-1901','dd-mon-yyyy') and
  6  to_date('31-MAR-1901','dd-mon-yyyy')
  7   and action in('bought','raid') ;

3.家族樹
語法:
select column from table_name start with column=value
connect by prior 父主鍵=子主鍵

3.1排除單一性和分枝
以ORACLE中的EMP表為例
[例]從頂到底列出各僱員的資訊
SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
  2  connect by prior empno=mgr;

NAME                                 EMPNO       MGR
---------                                  ---------           ---------
KING                                   7839           
    JONES                           7566            7839
          SCOTT                     7788            7566
                ADAMS              7876            7788

3.2遍歷至根
[例1]現在要從某個僱員開始向他的上級列出該僱員的層次結構
SQL> col ename for a30;
SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp
  2  start with mgr=7788 connect by prior mgr=empno;
ENAME                                MGR     EMPNO
------------------------------         ---------    ---------
ADAMS                               7788      7876
    SCOTT                           7566       7788
        JONES                       7839       7566
            KING                                        7839

[例2]列出所有僱員的層次結構
SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
  2  start with mgr is not null
  3  connect by empno=prior mgr;

ENAME                              EMPNO       MGR
------------------------------       ---------           ---------
SMITH                               7369          7902
    FORD                            7902         7566
        JONES                       7566        7839
            KING                    7839
ALLEN                               7499        7698
    BLAKE                           7698        7839
        KING                        7839
WARD                                7521        7698
    BLAKE                           7698        7839
        KING                        7839
JONES                               7566        7839
    KING                            7839
MARTIN                              7654        7698
    BLAKE                           7698         7839
        KING                        7839
BLAKE                               7698        7839
    KING                            7839
CLARK                               7782         7839
    KING                            7839
SCOTT                               7788         7566
    JONES                           7566        7839