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
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: 傳回兩個表中相同的資訊
intersect
MINUS : 傳回一個表中出現的資訊
minus
[例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;
[例3]MINUS操作執行個體
列出沒有特長學生的學号
select st_id from students
select st_id from student_skill;
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
JONES 7566 7839
KING 7839
MARTIN 7654 7698
BLAKE 7698 7839
BLAKE 7698 7839
CLARK 7782 7839
SCOTT 7788 7566
JONES 7566 7839
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)