1.子查詢傳回的結果是一個值 --單行子查詢
使用 = > < <= >= <> between...and...
查詢與scott同一個職位的員工資訊
select *
from emp
where job=(select job from emp where ename='SCOTT');
查詢與smith同一個部門的員工資訊
select *
from emp
where deptno=(select deptno from emp where ename='SMITH');
查詢比Ford工資高的員工資訊
select *
from emp
where sal>(select sal from emp where ename='FORD');
查詢比ADAMS入職晚的員工資訊
select * from emp
where hiredate>(select hiredate from emp where ename='ADAMS');
2.子查詢傳回的結果是多個值 --多行子查詢
使用in
not in
查詢與FORD同一部門或與CLARK同一部門的員工資訊
select * from emp
where deptno in (select deptno from emp where ename='FORD' or ename='CLARK');
查詢與CLARK和SMITH不同工作的員工資訊
select * from emp
where job not in (select job from emp where ename='CLARK' or ename='SMITH');
any --任何一個
all --全部
查詢比部門30中工資高的員工資訊
select * from emp
where sal > any (select sal from emp where deptno=30);
select * from emp
where sal > (select min(sal) from emp where deptno=30);
查詢比部門30中工資都高的員工資訊
select * from emp
where sal > all (select sal from emp where deptno=30);
select * from emp
where sal > (select max(sal) from emp where deptno=30);
查詢與Smith同一個部門，并且工作相同的員工資訊
select * from emp
where deptno=(select deptno from emp where ename='SMITH')
and job=(select job from emp where ename='SMITH');
select * from emp
where (deptno,job)=(select deptno,job from emp where ename='SMITH') ;
将子查詢作為臨時表和其他表連接配接
注意：要為目前子查詢和子查詢中的字段起别名
查詢每個部門工資最高的員工資訊
select * from emp e,(select max(sal) as max_sal,deptno dno from emp group by deptno) t
where e.sal=t.max_sal and e.deptno=t.dno;
查詢每個部門最早入職的員工資訊
select * from emp e,(select min(hiredate) min_hiredate,deptno dno from emp group by deptno) t
where t.min_hiredate=e.hiredate and t.dno=e.deptno;
查詢每個部門的員工數，部門名稱，部門位址
select num,dname,loc from dept d,(select count(*) num,deptno dno from emp group by deptno) t
where t.dno=d.deptno;
分頁查詢：
不同資料庫的處理方式不同
mysql ------limit關鍵字
sqlserver ------top關鍵字
oracle ------rownum僞列
從m到n
1.确定查詢條件
2.設定n
3.設定m
分頁查詢的文法
select b.*
from (select t.*,rownum as num
from (select * from 表名 where 條件) t
where rownum <=n) b
where num>m;
查詢員工表中前六條資料
select * from emp where rownum<=6;
查詢員工表中第3—7條資料
select * from emp where rownum>= 3 and rownum<=7;--錯誤
select * from emp where rownum between 3 and 7; --錯誤
查詢員工表中名字帶s的第2-4名員工資訊
select e.*
from (select t.*,rownum as num
from (select * from emp where ename like '%S%') t
where rownum <=4) e
where num>=2;
select t.*
from (select rownum num,emp.* from emp where ename like '%S%' and rownum<=4) t
where num>=2;