目錄

一.基本查詢回顧

二. 多表查詢

三.自連接

四.子查詢

1.單行子查詢

2.多行子查詢?

3.多列子查詢

4.在from子句中使用子查詢

5.合并查詢

---

一.基本查詢回顧

準備數(shù)據(jù)庫：

查詢工資高于500或崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J：

?按照部門號升序而雇員的工資降序排序：

?使用年薪進行降序排序：

?顯示工資最高的員工的名字和工作崗位：

?顯示工資高于平均工資的員工信息

顯示每個部門的平均工資和最高工資 ：

?顯示平均工資低于2000的部門號和它的平均工資：

顯示每種崗位的雇員總數(shù)，平均工資 ：

二. 多表查詢

實際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表查詢。本節(jié)我們用一個簡單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來演示如何進行多表查詢。

顯示雇員名、雇員工資以及所在部門的名字因為上面的數(shù)據(jù)來自EMP和DEPT表，因此要聯(lián)合查詢:

例如：

select * from emp,dept;

這種將多個表的數(shù)據(jù)進行組和窮舉，我們稱為笛卡爾積。

但是我們發(fā)現(xiàn)表里面有很多不合法的數(shù)據(jù)，所以我們需要將數(shù)據(jù)篩選出合法的數(shù)據(jù)。

其實我們只要emp表中的deptno = dept表中的deptno字段的記錄：

select * from emp,dept where emp.deptno=dept.deptno;

?顯示部門號為10的部門名，員工名和工資：

select dname,ename,sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

?顯示各個員工的姓名，工資，及工資級別：

select ename,sal,losal,hisal,grade from emp,salgrade where sal between losal and hisal;

三.自連接

自連接是指在同一張表連接查詢.

案例：
顯示員工FORD的上級領(lǐng)導的編號和姓名（mgr是員工領(lǐng)導的編號--empno）

子查詢 ：

select empno,ename from emp where emp.empno=(select mgr from emp where ename = 'FORD');

使用多表查詢（自查詢）：

select t1.ename,t2.empno  from emp t1,emp t2 where t1.ename='FORD' and t1.mgr=t2.empno ;

四.子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢.

1.單行子查詢

顯示SMITH同一部門的員工:

2.多行子查詢?

返回多行記錄的子查詢:

in關(guān)鍵字

例如? a in { b },將a在b中篩選出來。

查詢和10號部門的工作崗位相同的雇員的名字，工崗位，資，部門號，但是不包含10自己的

select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10;

?all關(guān)鍵字

all{集合}，表示某一個集合中的全部元素。

顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號：

select ename,sal,deptno from emp where sal >  all(select sal from emp where deptno=30);

any關(guān)鍵字

any{集合} 集合中的任意一個。

顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工）：

select ename,sal,deptno from emp where sal >  any(select sal from emp where deptno=30);

?3.多列子查詢

單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句。

案例：查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMITH本人

4.在from子句中使用子查詢

子查詢語句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個子查詢當做一個臨時表使用。

案例：顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資

select ename,deptno,sal,tmp.myavg from emp,(select avg(sal) myavg,deptno dt from emp group by deptno) tmp where sal>myavg and deptno = dt ;

查找每個部門工資最高的人的姓名、工資、部門、最高工資 ：

select ename,deptno,sal,maxsal,tmp.dt from emp,(select max(sal) maxsal,deptno dt from emp group by deptno) tmp where deptno=tmp.dt and sal=tmp.maxsal;

顯示每個部門的信息（部門名，編號，地址）和人員數(shù)量 ：

select dept.dname,dept.deptno,dept.loc,total from (select count(*) total ,deptno from emp group by deptno) tmp,dept where tmp.deptno=dept.deptno;

?5.合并查詢

在實際應用中，為了合并多個select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all

union：

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中的重復行。

案例：將工資大于2500或職位是MANAGER的人找出來

 select empno,ename,job,sal from emp where sal > 2500 union select empno,ename,job,sal from emp where job='MANAGER';

union all：

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，不會去掉結(jié)果集中的重復行。

select empno,ename,job,sal from emp where sal > 2500 union all select empno,ename,job,sal,sal from emp where job='MANAGER';