Oracle 10g資料庫基礎之基本查詢語句-下-連接配接&子查詢

--資料參考：張烈張建中《資料庫管理者教育訓練講義》

實驗18：表的連接配接查詢

該實驗的目的是掌握基本的聯合查詢.

表的連接配接

我們要從多張表中要得到資訊，就得以一定的條件将表連接配接在一起查詢。

Cartesian（笛卡兒）連接配接

當多張表在一起查詢時，沒有給定正确的連接配接條件，結果是第一張表的所有行和第二張表的所有行進行矩陣相乘，得到n*m行的結果集。

一般來說笛卡兒連接配接不是我們需要的結果。

但表如果有一行的情況下，結果有可能正确。

SQL> select ename,dname from emp,dept;

ENAME DNAME

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

SMITH ACCOUNTING

ALLEN ACCOUNTING

……..

SMITH RESEARCH

ALLEN RESEARCH

SMITH SALES

ALLEN SALES

SMITH OPERATIONS

ALLEN OPERATIONS

56 rows selected

SQL>

結果為每個員工在每個部門上了一次班，4*14=56，這并不是我們想得到的結果。

要避免笛卡兒連接配接一定要給定一個正确的連接配接條件。

等值連接配接

在連接配接中給定一個相等的連接配接條件。

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

當列的名稱在兩張表内重複的時候，要加表的字首來區分，避免不明确的定義。

表的别名

1。便于書寫

2。将同名的表區分

3。一旦定義了别名，表的本名就無效

4。隻在該語句内有效

5。定義方式為表名後緊跟别名，用空各間隔。

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

SQL99的書寫方式

select ename,dname from emp e join dept d on (e.deptno=d.deptno)；

效率是相同的，SQL99是國标

列的别名，為了區分相同的列的名稱，這是别名的本質。

SQL> select ename,dname,e.deptno,d.deptno

from emp e,dept d

where e.deptno=d.deptno;

上述顯示有兩個列名稱都叫deptno,我們無法區分。

SQL> select ename,dname,e.deptno "員工表",d.deptno "部門表"

from emp e,dept d

where e.deptno=d.deptno;

不等連接配接

連接配接條件不是一個相等的條件。

SQL> select ename,sal,grade

from emp,salgrade

where sal between LOSAL and hisal;

外鍵連接配接

将一張表有，而另一張表沒有的行也顯示出來。

SQL> select ename,dname,emp.deptno from emp,dept

where emp.deptno=dept.deptno;

這句話不會顯示40号部門，因為40部門沒有員工。

SQL> select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno;

+号的意思為将沒有員工的部門，用NULL來比對

+号不能同時放在等号的兩邊，隻能出現在一邊。

自連接配接

表的一列和同一個表的另一列作為連接配接的條件。

SQL> select w.ename "下級" ,m.ename "上級"

from emp w,emp m

where w.mgr=m.empno(+);

其中“下級”和“上級”為列的别名。區分相同的列。

W和m 為表的别名。區分相同的表。别名的本質。

（+）為了将沒有上級的人也顯示。

過濾結果

想在結果中過濾去一些内容請用and運算。

where w.mgr=m.empno(+)

and w.deptno=30;

實驗19：sql99規則的表連接配接操作

該實驗的目的是掌握新的ORACLE表之間的聯合查詢文法.

SQL99規則的書寫格式

. Nature(自然)連接配接

這是SQL99規則。

所有同名的列都作為等值條件。

同名的列的資料類型必須比對。

列的名稱前不能加表的字首。

SQL> select ename,deptno,dname from emp natural join dept;

Using指定列的連接配接

當有多列同名，但想用其中某一列作為連接配接條件時使用。

SQL> select ename,deptno ,dname from emp join dept using (deptno);

SQL99的外鍵連接配接

SQL99寫法

select ename,dname,dept.deptno

from dept left outer join emp

on(dept.deptno=emp.deptno);

9I前的寫法

select ename,dname from emp,dept

where emp.deptno(+)=dept.deptno;

SQL> select ename,dname,dept.deptno

from dept left outer join emp

on(dept.deptno=emp.deptno);

知識點

1。笛卡兒連接配接

2。等值連接配接

3。不等連接配接

4。外鍵連接配接

5。自連接配接

6。SQL99的書寫格式

實驗20：子查詢

該實驗的目的是掌握子查詢的文法和概念.

 子查詢

誰的工資最多

簡單子查詢

1。先于主查詢執行。

2。主查詢調用了子查詢的結果。

3。注意列的個數和類型要比對。

4。子查詢傳回多行要用多行關系運算操作。

5。子查詢要用括号括起來。

SQL> --查詢工資總和高于10号部門工資總和的部門。

SQL> select deptno,sum(sal)

from emp

group by deptno

having sum(sal)>(select sum(sal) from emp where deptno=10);

SQL> --查詢每個部門的最大工資是誰。

SQL> select deptno,ename,sal from emp

where (deptno,sal) in

(select deptno,max(sal) from emp group by deptno);

子查詢傳回多行，用=不可以，得用in。

子查詢傳回多列，是以對比的列也要比對。

Any 和all操作

SQL> select ename,sal from emp where sal<any(1000,2000);

小于2000就可以

SQL> select ename,sal from emp where sal<all(1000,2000);

必須小于1000

小于all小于最小，大于all大于最大

SQL> select ename,sal,deptno from emp

where sal<all(select avg(sal) from emp group by deptno);

小于any小于最大，大于any大于最小

select ename,sal,deptno from emp

where sal>any(select avg(sal) from emp group by deptno)

From子句中的子查詢

查詢工資大于本部門平均工資的員工。

select ename,e.deptno,sal,asal

from emp e,

(select deptno ,avg(sal) asal from emp group by deptno) a

where e.deptno=a.deptno and sal>asal;

A為視圖，為什麼要使用别名asal,因為表達式不能當列的名稱，别名的本質使用方法是使非法的合法化。

互相關聯的子查詢

select ename,sal ,deptno

from emp o

where sal>(select avg(sal) from emp where deptno=o.deptno) ;

先運作主查詢，得到第一行，将DEPTNO傳入到子查詢，由子查詢求出AVG（SAL），在判定主查詢的行是否符合查詢的條件。

執行計劃是将子查詢看作視圖的關聯。這叫做SQL的自動改寫。

Exists操作

找上司，其中3是常量，你寫什麼都可以。

當子查詢有行時，Exists傳回true

查到行後就不再繼續查詢

當子查詢沒有行時為假，Exists傳回false

select ename,empno,mgr from emp o

where exists(select 3 from emp where mgr=o.empno);

《完》

--xjzhujunjie

本文轉自xjzhujunjie 51CTO部落格，原文連結:http://blog.51cto.com/xjzhujunjie/868102

Oracle 10g資料庫基礎之基本查詢語句-下-連接配接&子查詢

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List

Oracle 10g資料庫基礎之基本查詢語句-下-連接配接&amp;子查詢

繼續閱讀

Oracle 10g資料庫基礎之基本查詢語句-下-連接配接&子查詢